file 

Creates a table from a file. This table function is similar to url and hdfs ones.

file function can be used in SELECT and INSERT queries on data in File tables.

Syntax

file(path, format, structure)

Input parameters

  • path — The relative path to the file from user_files_path. Path to file support following globs in readonly mode: *, ?, {abc,def} and {N..M} where N, M — numbers, 'abc', 'def' — strings.
  • format — The format of the file.
  • structure — Structure of the table. Format: 'column1_name column1_type, column2_name column2_type, ...'.

Returned value

A table with the specified structure for reading or writing data in the specified file.

Examples

Setting user_files_path and the contents of the file test.csv:

$ grep user_files_path /etc/clickhouse-server/config.xml
    <user_files_path>/var/lib/clickhouse/user_files/</user_files_path>

$ cat /var/lib/clickhouse/user_files/test.csv
    1,2,3
    3,2,1
    78,43,45

Getting data from a table in test.csv and selecting first two rows from it:

SELECT * FROM file('test.csv', 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32') LIMIT 2;
┌─column1─┬─column2─┬─column3─┐
│       1 │       2 │       3 │
│       3 │       2 │       1 │
└─────────┴─────────┴─────────┘

Getting the first 10 lines of a table that contains 3 columns of UInt32 type from a CSV file:

SELECT * FROM file('test.csv', 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32') LIMIT 10;

Inserting data from a file into a table:

INSERT INTO FUNCTION file('test.csv', 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32') VALUES (1, 2, 3), (3, 2, 1);
SELECT * FROM file('test.csv', 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32');
┌─column1─┬─column2─┬─column3─┐
│       1 │       2 │       3 │
│       3 │       2 │       1 │
└─────────┴─────────┴─────────┘

Globs in Path 

Multiple path components can have globs. For being processed file should exists and matches to the whole path pattern (not only suffix or prefix).

  • * — 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 'some_string', 'another_string', 'yet_another_one'.
  • {N..M} — Substitutes any number in range from N to M including both borders.

Constructions with {} are similar to the remote table function).

Example

Suppose we have several files with the following relative paths:

  • 'some_dir/some_file_1'
  • 'some_dir/some_file_2'
  • 'some_dir/some_file_3'
  • 'another_dir/some_file_1'
  • 'another_dir/some_file_2'
  • 'another_dir/some_file_3'

Query the amount of rows in these files:

SELECT count(*) FROM file('{some,another}_dir/some_file_{1..3}', 'TSV', 'name String, value UInt32');

Query the amount of rows in all files of these two directories:

SELECT count(*) FROM file('{some,another}_dir/*', 'TSV', 'name String, value UInt32');

Example

Query the data from files named file000, file001, … , file999:

SELECT count(*) FROM file('big_dir/file{0..9}{0..9}{0..9}', 'CSV', 'name String, value UInt32');

Virtual Columns 

  • _path — Path to the file.
  • _file — Name of the file.

See Also

Original article

Rating: 5 - 2 votes

Was this content helpful?
★★★★★