Import and export to files

Elena Dvoryadkina

Import from files

ADQM supports the following functionality to import data into tables from files.

FROM INFILE

To load data into a table from a local file or multiple files at once (compressed files are also supported), you can use the FROM INFILE clause when running the INSERT INTO query in the clickhouse-client command-line client. For this, files should be located in the folder from which clickhouse-client is launched. The basic syntax is:

INSERT INTO <table_name> [(<column_list>)] FROM INFILE '<file_name.ext>' [COMPRESSION '<compr_type>'] [FORMAT <input_format>];

The FROM INFILE clause uses the following arguments.

<file_name.ext>

Name of the file (with the extension) from which data should be loaded into the ADQM table

<compr_type>

File compression method. Possible values are none, gzip, deflate, br, xz, zstd, lz4, bz2. By default, the compression method is detected by the extension of the file name

<input_format>

Input data format — see formats in the Input column of a table in the Formats for Input and Output Data article. By default, the format is automatically detected by the file extension.

For examples on how to choose formats to import JSON data depending on its structure, see the JSON data type article

Example

  1. Create two local test files (input_1.csv and input_2.csv) with data to be later inserted into ADQM tables:

    1,"aaa",100
2,"bbb",200
3,"ccc",300
    4,"ddd",400
5,"eee",500
6,"fff",600

    Start clickhouse-client from the folder where these files are located.

  2. Create a table with the relevant structure:

    CREATE TABLE test_table (a Int32, b String, c Int32) ENGINE = MergeTree ORDER BY a;

  3. Run the following query to import data from one of the files into the created table:

    INSERT INTO test_table FROM INFILE 'input_1.csv';

  4. Check that data has loaded into the table:

    SELECT * FROM test_table;
    ┌─a─┬─b───┬───c─┐
│ 1 │ aaa │ 100 │
│ 2 │ bbb │ 200 │
│ 3 │ ccc │ 300 │
└───┴─────┴─────┘

  5. To insert data from both files into the table at once, pass input_*.csv as the file name:

    INSERT INTO test_table FROM INFILE 'input_*.csv';

  6. Check the import result:

    SELECT * FROM test_table;
    ┌─a─┬─b───┬───c─┐
│ 1 │ aaa │ 100 │
│ 2 │ bbb │ 200 │
│ 3 │ ccc │ 300 │
│ 4 │ ddd │ 400 │
│ 5 │ eee │ 500 │
│ 6 │ fff │ 600 │
└───┴─────┴─────┘
┌─a─┬─b───┬───c─┐
│ 1 │ aaa │ 100 │
│ 2 │ bbb │ 200 │
│ 3 │ ccc │ 300 │
└───┴─────┴─────┘

The File table engine

You can load data into ADQM from a file via the File table engine, which uses a file to store data. Data modified directly within a file on a disk is automatically imported into the associated table of the File type when the SELECT query is executed. When creating a table based on this engine, pass one of the formats for input data as the engine’s argument — see available formats in the Input column of the Formats for Input and Output Data table. Note that the File table engine does not allow you to explicitly specify the path to the file for storing data — it uses the directory specified via the Data path parameter on the configuration page of the ADQMDB service (the default is /var/lib/clickhouse/).

Example

  1. Create a file_table table using the File engine:

    CREATE TABLE file_table (name String, value UInt32) ENGINE=File(TabSeparated);

    ADQM will create the /var/lib/clickhouse/data/default/file_table directory.

  2. Manually create the /var/lib/clickhouse/data/default/file_table/data.TabSeparated file and fill it with test data:

    $ sudo cat > /var/lib/clickhouse/data/default/file_table/data.TabSeparated
a     1
b     2

    If you want to export data from the table into this file in the future, assign the clickhouse owner to it with the following command:

    $ sudo chown clickhouse:clickhouse /var/lib/clickhouse/data/default/file_table/data.TabSeparated

  3. Query data from file_table:

    SELECT * FROM test_table;
    ┌─name─┬─value─┐
│ a    │     1 │
│ b    │     2 │
└──────┴───────┘

The file table function

The file table function also allows you to import data from a file into a table — see an example in the Table functions article.

Command-line redirection

You can import data into a table from a file directly in the clickhouse-client command line, for example:

$ clickhouse-client -q "INSERT INTO test_table FORMAT CSV" < input_2.csv

Export to files

You can export data from ADQM to files in the following ways.

INTO OUTFILE

To save the result of the SELECT query to a file, use the INTO OUTFILE clause when executing the query in the clickhouse-client command-line client. The basic syntax is:

SELECT <expr_list>
[FROM <table_name>]
...
INTO OUTFILE '<file_name.ext>' [AND STDOUT] [COMPRESSION '<compr_type>' [LEVEL <compr_level>]]
[FORMAT <output_format>];

The INTO OUTFILE clause uses the following arguments.

<file_name.ext>

Name of the file (with the extension) where the SELECT query result should be saved. The file will be created in the folder from which clickhouse-client is launched. If a file with the specified name already exists, the query will fail

<compr_type>

Compression method. Possible values are none, gzip, deflate, br, xz, zstd, lz4, bz2. By default, the compression method is detected by the file extension

<compr_level>

Compression level for the specified method. Possible values are positive integers from the following ranges:

  • [1, 12] — for the lz4 method;

  • [1, 22] — for the zstd method;

  • [1, 9] — for other compression methods.

<output_format>

Output data format — see formats supported for output in the Formats for Input and Output Data article of the ClickHouse documentation. By default, the format is detected by the file extension, or the TabSeparated format is used if the format could not be detected by the file extension.

To get examples of formats for JSON data export, refer to the JSON data type article

If the AND STDOUT clause is used in a query, data saved to a file will also be displayed on standard output. If compression is applied, the plaintext will be displayed on standard output.

Example of the SELECT …​ INTO OUTFILE query:

SELECT * FROM test_table INTO OUTFILE 'output.csv';

The File table engine

Another way to send data from ADQM to a file is to use the File table engine. When creating a table of this type, specify one of the formats for data output as the File engine’s argument — this format will be used for data export that is performed along with the INSERT queries. You can find available output formats in the Formats for Input and Output Data article.

Example

  1. Create a file_table_out table based on the File engine:

    CREATE TABLE file_table_out (name String, value UInt32) ENGINE=File(TabSeparated);

    ADQM will create the /var/lib/clickhouse/data/default/file_table_out directory, where /var/lib/clickhouse/ is the directory for storing ADQM data, which is specified via the Data path parameter on the ADQMDB service’s configuration page.

  2. Fill the table with test data:

    INSERT INTO file_table_out VALUES('a', 1), ('b', 2), ('c', 3);

  3. Make sure that the data.TabSeparated file is created in the /var/lib/clickhouse/data/default/file_table_out directory, and this file contains data from the file_table_out table:

    a     1
b     2
c     3

The file table function

You can also use the file table function to export data from ADQM to a file — see an example in the Table functions article.

Command-line redirection

The following query creates the redirected_output.csv file in the folder from which clickhouse-client is launched and writes the result of the specified SELECT query into it (if such file already exists, data will be overwritten):

$ clickhouse-client --query "SELECT * from test_table" --format CSV > redirected_output.csv
Found a mistake? Seleсt text and press Ctrl+Enter to report it