HDFS

    The parameter is the whole file URI in HDFS.
    The format parameter specifies one of the available file formats. To perform
    SELECT queries, the format must be supported for input, and to perform
    INSERT queries – for output. The available formats are listed in the
    Formats section.
    The path part of URI may contain globs. In this case the table would be readonly.

    Example:

    1. Set up the hdfs_engine_table table:

    1. CREATE TABLE hdfs_engine_table (name String, value UInt32) ENGINE=HDFS('hdfs://hdfs1:9000/other_storage', 'TSV')

    2. Fill file:

    1. INSERT INTO hdfs_engine_table VALUES ('one', 1), ('two', 2), ('three', 3)

    3. Query the data:

    1. ┌─name─┬─value─┐
    2. │ one  │     1 │
    4. └──────┴───────┘
    • Reads and writes can be parallel

    Multiple path components can have globs. For being processed file should exists and matches to the whole path pattern. Listing of files determines during SELECT (not at CREATE moment).

    • * — Substitutes any number of any characters except / including empty string.
    • ? — Substitutes any single character.
    • {some_string,another_string,yet_another_one} — Substitutes any of strings .
    • {N..M} — Substitutes any number in range from N to M including both borders.

    Constructions with {} are similar to the table function.

    Example

    1. Suppose we have several files in TSV format with the following URIs on HDFS:
    • ‘hdfs://hdfs1:9000/some_dir/some_file_1’
    • ‘hdfs://hdfs1:9000/some_dir/some_file_2’
    • ‘hdfs://hdfs1:9000/another_dir/some_file_1’
    • ‘hdfs://hdfs1:9000/another_dir/some_file_2’
    • ‘hdfs://hdfs1:9000/another_dir/some_file_3’
    1. There are several ways to make a table consisting of all six files:
    1. CREATE TABLE table_with_range (name String, value UInt32) ENGINE = HDFS('hdfs://hdfs1:9000/{some,another}_dir/some_file_{1..3}', 'TSV')

    Another way:

    Table consists of all the files in both directories (all files should satisfy format and schema described in query):

    1. CREATE TABLE table_with_asterisk (name String, value UInt32) ENGINE = HDFS('hdfs://hdfs1:9000/{some,another}_dir/*', 'TSV')

    If the listing of files contains number ranges with leading zeros, use the construction with braces for each digit separately or use ?.

    Example

    Create table with files named file000, file001, … , file999:

    1. CREATE TABLE big_table (name String, value UInt32) ENGINE = HDFS('hdfs://hdfs1:9000/big_dir/file{0..9}{0..9}{0..9}', 'CSV')
    • — Path to the file.
    • _file — Name of the file.

    See Also