ClickHouse 数据迁移与导入导出 详解

ClickHouse 数据迁移与导入导出 是日常运维和数据集成中的核心操作。ClickHouse 提供了多种灵活的方式，适用于不同场景：从小批量测试到海量数据批量导入，再到实时流式摄入。

本篇将系统、详细地讲解 数据导入与导出的常用方法，并附上实战示例和最佳实践。

---

📥 一、数据导入方式详解

1. INSERT INTO ... VALUES —— 小批量测试（不推荐生产）

✅ 用途

快速插入少量数据，用于测试建表、查询逻辑。

✅ 语法

INSERT INTO table_name (col1, col2) VALUES (val1, val2), (val3, val4);

✅ 示例

INSERT INTO user_log (user_id, event_date, action) 
VALUES 
(1001, '2024-04-01', 'login'),
(1002, '2024-04-01', 'click');

⚠️ 注意事项

• 性能差：每条 INSERT 都是一个“数据部分（Part）”，频繁插入导致大量小 Parts，影响查询性能。
• 不适合批量导入：仅用于测试或调试。
• 建议：每次插入至少 1000 行以上，减少 Parts 数量。

---

2. INSERT INTO ... SELECT —— 表间数据迁移（推荐）

✅ 用途

从一张表（本地或远程）查询数据并插入另一张表，适用于：

• 数据聚合写入
• 表结构转换
• 增量同步

✅ 语法

INSERT INTO target_table 
SELECT * FROM source_table WHERE condition;

✅ 示例：聚合后写入

INSERT INTO daily_stats 
SELECT 
    event_date,
    action,
    COUNT(*) AS cnt,
    AVG(duration) AS avg_dur
FROM user_log 
WHERE event_date = '2024-04-01'
GROUP BY event_date, action;

✅ 示例：跨数据库复制

INSERT INTO db2.user_log 
SELECT * FROM db1.user_log WHERE event_date >= '2024-04-01';

✅ 优势

• 高效：批量写入，生成少量大 Parts
• 支持复杂逻辑（JOIN、子查询、函数）
• 可结合 WHERE 实现增量导入

✅ 最佳实践

• 避免全表 INSERT ... SELECT 无条件操作（数据量大时慢）
• 使用 LIMIT 或分区条件控制数据量
• 导入后执行 OPTIMIZE TABLE ... FINAL 强制合并（可选）

---

3. 命令行导入（clickhouse-client + 文件）

✅ 用途

从本地文件（CSV、TSV、JSON 等）导入数据，适合批量导入。

✅ 常用方式 1：管道导入 CSV

clickhouse-client --query="INSERT INTO user_log FORMAT CSV" < data.csv

✅ data.csv 内容：

1001,2024-04-01,login,30
1002,2024-04-01,click,15

✅ 常用方式 2：指定分隔符（TSV）

clickhouse-client --query="INSERT INTO user_log FORMAT TSV" < data.tsv

✅ 常用方式 3：从命令输出导入

cat data.json | clickhouse-client --query="INSERT INTO events FORMAT JSONEachRow"

✅ 支持的格式（FORMAT）

格式	说明
CSV / CSVWithNames	逗号分隔
TSV / TSVWithNames	制表符分隔
JSONEachRow	每行一个 JSON 对象
Parquet	列式文件，高效导入
ORC, Avro, Native	其他大数据格式

✅ 优势

• 简单直接，无需中间表
• 支持压缩文件（.gz, .bz2）
• 可脚本化、自动化

✅ 示例：导入压缩 CSV

zcat data.csv.gz | clickhouse-client --query="INSERT INTO user_log FORMAT CSV"

---

4. 使用 Kafka 引擎实现实时流式导入（生产级推荐）

✅ 用途

对接 Kafka 消息队列，实现实时数据摄入，适用于日志、事件流、监控等场景。

✅ 步骤

(1) 创建 Kafka 引擎表（消息接口）

CREATE TABLE kafka_queue (
    user_id UInt32,
    action String,
    event_time DateTime
) ENGINE = Kafka
SETTINGS 
    kafka_broker_list = 'kafka1:9092',
    kafka_topic_list = 'user_events',
    kafka_group_name = 'clickhouse_group',
    kafka_format = 'JSONEachRow';

(2) 创建目标表（持久化）

CREATE TABLE user_log ENGINE = ReplicatedMergeTree() 
ORDER BY (event_time, user_id)
AS SELECT * FROM kafka_queue LIMIT 0;

(3) 创建物化视图（自动写入）

CREATE MATERIALIZED VIEW mv_kafka_to_user_log
TO user_log
AS SELECT * FROM kafka_queue;

✅ 效果：Kafka 中每新增一条消息，自动写入 user_log 表。

✅ 优势

• 实时性高（秒级延迟）
• 解耦生产与消费
• 支持高吞吐、多消费者

✅ 适用场景

• 用户行为分析
• 日志收集系统
• 实时数仓 ETL

---

📤 二、数据导出方式详解

1. SELECT ... INTO OUTFILE —— 导出到文件

✅ 用途

将查询结果导出为本地文件（在 ClickHouse 服务端）。

✅ 语法

SELECT * FROM table INTO OUTFILE 'file.csv' FORMAT CSV;

✅ 示例

SELECT user_id, action, duration 
FROM user_log 
WHERE event_date = '2024-04-01'
INTO OUTFILE '/var/lib/clickhouse/user_log_20240401.csv'
FORMAT CSVWithNames;

⚠️ 注意事项

• 文件路径是 ClickHouse 服务端的路径，不是客户端
• 需确保目录可写（通常为 /var/lib/clickhouse/）
• 支持格式：CSV, TSV, JSONEachRow, Parquet 等

✅ 导出压缩文件

INTO OUTFILE '/path/data.csv.gz' FORMAT CSV

自动使用 GZIP 压缩。

---

2. 命令行导出（推荐客户端使用）

✅ 方式 1：重定向输出

clickhouse-client --query="SELECT * FROM user_log FORMAT CSVWithNames" > data.csv

✅ 方式 2：导出为 JSON

clickhouse-client --format=JSON --query="SELECT * FROM user_log" > data.json

✅ 方式 3：结合压缩

clickhouse-client --query="SELECT * FROM user_log FORMAT Parquet" | gzip > data.parquet.gz

✅ 优势：灵活，可在客户端操作，适合自动化脚本。

---

3. 导出到外部系统（高级）

方法	示例
导出到 S3	INSERT INTO FUNCTION s3('https://...', 'CSV') SELECT ...
导出到 HDFS	INSERT INTO FUNCTION hdfs('hdfs://...', 'CSV') SELECT ...
导出到 MySQL	INSERT INTO mysql('host:3306', 'db', 'tbl', 'u', 'p') SELECT ...

✅ 用于数据归档、备份、跨系统同步。

---

🧩 三、其他导入方式（补充）

方法	说明
clickhouse-local	在无服务模式下处理文件并导入
clickhouse-copier	集群间数据复制工具（大表迁移）
HTTP 接口	curl -sS "http://host:8123/?query=INSERT%20INTO..." --data-binary @file.csv
第三方工具	Logstash, Fluentd, Airbyte, Flink CDC

---

✅ 四、导入导出最佳实践

场景	推荐方式
小批量测试	INSERT ... VALUES
批量导入 CSV	clickhouse-client --query="INSERT ... FORMAT CSV" < file.csv
表间迁移	INSERT INTO ... SELECT
实时流式导入	Kafka Engine + Materialized View
大数据量导出	clickhouse-client --query="SELECT ..." > file.csv
自动化脚本	命令行 + FORMAT
跨系统同步	外部引擎（MySQL/S3/HDFS）
集群间迁移	clickhouse-copier

---

⚠️ 五、常见问题与注意事项

问题	解决方案
导入太慢	批量导入（>1000 行/次），避免高频小写
文件格式错误	使用 FORMAT CSVWithNames 并检查列顺序
权限不足	确保 ClickHouse 有文件读写权限
Kafka 消费延迟	检查 system.kafka_consumers，调整 num_consumers
内存不足	减小 max_insert_block_size，分批导入
数据乱序	ClickHouse 不保证写入顺序，查询时用 ORDER BY

---

🎯 六、总结：ClickHouse 数据迁移核心方法

操作	推荐命令
小量测试	INSERT INTO ... VALUES (...)
表间迁移	INSERT INTO ... SELECT ...
CSV 导入	clickhouse-client --query="INSERT ... FORMAT CSV" < file.csv
实时摄入	Kafka Engine + MV
数据导出	SELECT ... INTO OUTFILE 'file.csv' FORMAT CSV 或命令行重定向

🎯 核心原则：

• 批量导入，避免小写
• 格式匹配，避免解析错误
• 生产环境优先使用 Kafka 或批量导入
• 导出优先使用客户端重定向