Import and export to files
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 |
<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
-
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.
-
Create a table with the relevant structure:
CREATE TABLE test_table (a Int32, b String, c Int32) ENGINE = MergeTree ORDER BY a;
-
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';
-
Check that data has loaded into the table:
SELECT * FROM test_table;
┌─a─┬─b───┬───c─┐ │ 1 │ aaa │ 100 │ │ 2 │ bbb │ 200 │ │ 3 │ ccc │ 300 │ └───┴─────┴─────┘
-
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';
-
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
-
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.
-
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
-
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 |
<compr_type> |
Compression method. Possible values are |
<compr_level> |
Compression level for the specified method. Possible values are positive integers from the following ranges:
|
<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 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
-
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.
-
Fill the table with test data:
INSERT INTO file_table_out VALUES('a', 1), ('b', 2), ('c', 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