Inserting and dumping SQL data in ClickHouse
ClickHouse can be easily integrated into OLTP database infrastructures in many ways. One way is to transfer data between other databases and ClickHouse using SQL dumps.
Creating SQL dumps
Data can be dumped in SQL format using SQLInsert. ClickHouse will write data in INSERT INTO <table name> VALUES(... form and use output_format_sql_insert_table_name settings option as a table name:
Column names can be omitted by disabling output_format_sql_insert_include_column_names option:
Now we can feed dump.sql file to another OLTP database:
We assume that the some_table table exists in the some_db MySQL database.
Some DBMSs might have limits on how much values can be processes within a single batch. By default, ClickHouse will create 65k values batches, but that can be changed with the output_format_sql_insert_max_batch_size option:
Exporting a set of values
ClickHouse has Values format, which is similar to SQLInsert, but omits an INSERT INTO table VALUES part and returns only a set of values:
Inserting data from SQL dumps
To read SQL dumps, MySQLDump is used:
By default, ClickHouse will skip unknown columns (controlled by input_format_skip_unknown_fields option) and process data for the first found table in a dump (in case multiple tables were dumped to a single file). DDL statements will be skipped. To load data from MySQL dump into a table (mysql.sql file):
We can also create a table automatically from the MySQL dump file:
Here we've created a table named table_from_mysql based on a structure that ClickHouse automatically inferred. ClickHouse either detects types based on data or uses DDL when available:
Other formats
ClickHouse introduces support for many formats, both text, and binary, to cover various scenarios and platforms. Explore more formats and ways to work with them in the following articles:
And also check clickhouse-local - a portable full-featured tool to work on local/remote files without the need for ClickHouse server.