在 ClickHouse 中插入和导出 SQL 数据
ClickHouse 可以通过多种方式轻松集成到 OLTP 数据库基础设施中。一种方法是使用 SQL 导出在其他数据库和 ClickHouse 之间传输数据。
创建 SQL 导出
可以使用 SQLInsert 以 SQL 格式导出数据。ClickHouse 将以 INSERT INTO <table name> VALUES(...
形式写入数据,并使用 output_format_sql_insert_table_name
设置选项作为表名
SET output_format_sql_insert_table_name = 'some_table';
SELECT * FROM some_data
INTO OUTFILE 'dump.sql'
FORMAT SQLInsert
可以通过禁用 output_format_sql_insert_include_column_names
选项来省略列名
SET output_format_sql_insert_include_column_names = 0
现在我们可以将 dump.sql 文件提供给另一个 OLTP 数据库
mysql some_db < dump.sql
我们假设 some_table
表存在于 some_db
MySQL 数据库中。
某些 DBMS 可能对单个批处理中可以处理的值数量有限制。默认情况下,ClickHouse 将创建 65k 个值的批次,但这可以使用 output_format_sql_insert_max_batch_size
选项进行更改
SET output_format_sql_insert_max_batch_size = 1000;
导出值集合
ClickHouse 具有 Values 格式,它类似于 SQLInsert,但省略了 INSERT INTO table VALUES
部分,仅返回一组值
SELECT * FROM some_data LIMIT 3 FORMAT Values
('Bangor_City_Forest','2015-07-01',34),('Alireza_Afzal','2017-02-01',24),('Akhaura-Laksam-Chittagong_Line','2015-09-01',30)
从 SQL 导出导入数据
要读取 SQL 导出,请使用 MySQLDump
SELECT *
FROM file('dump.sql', MySQLDump)
LIMIT 5
┌─path───────────────────────────┬──────month─┬─hits─┐
│ Bangor_City_Forest │ 2015-07-01 │ 34 │
│ Alireza_Afzal │ 2017-02-01 │ 24 │
│ Akhaura-Laksam-Chittagong_Line │ 2015-09-01 │ 30 │
│ 1973_National_500 │ 2017-10-01 │ 80 │
│ Attachment │ 2017-09-01 │ 1356 │
└────────────────────────────────┴────────────┴──────┘
默认情况下,ClickHouse 将跳过未知列(由 input_format_skip_unknown_fields 选项控制),并处理导出中找到的第一个表的数据(如果将多个表导出到单个文件)。DDL 语句将被跳过。要从 MySQL 导出加载数据到表中(mysql.sql 文件)
INSERT INTO some_data
FROM INFILE 'mysql.sql' FORMAT MySQLDump
我们还可以从 MySQL 导出文件自动创建表
CREATE TABLE table_from_mysql
ENGINE = MergeTree
ORDER BY tuple() AS
SELECT *
FROM file('mysql.sql', MySQLDump)
在这里,我们基于 ClickHouse 自动推断的结构创建了一个名为 table_from_mysql
的表。ClickHouse 会根据数据检测类型,或者在 DDL 可用时使用 DDL
DESCRIBE TABLE table_from_mysql;
┌─name──┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ path │ Nullable(String) │ │ │ │ │ │
│ month │ Nullable(Date32) │ │ │ │ │ │
│ hits │ Nullable(UInt32) │ │ │ │ │ │
└───────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
其他格式
ClickHouse 引入了对多种格式的支持,包括文本格式和二进制格式,以涵盖各种场景和平台。在以下文章中探索更多格式以及使用它们的方法
- CSV 和 TSV 格式
- Parquet
- JSON 格式
- Regex 和模板
- 原生和二进制格式
- SQL 格式
另请查看 clickhouse-local - 一个便携式全功能工具,用于处理本地/远程文件,而无需 ClickHouse 服务器。