1. 为什么说导入导出是ClickHouse使用者的第一道坎ClickHouse这几年在大数据圈子里热度一直很高尤其是做用户行为分析、日志存储、指标聚合这些场景它几乎是绕不开的默认选项。但很多人一开始上手就被卡在了同一个地方数据怎么进去、数据怎么出来。MySQL用惯了的人习惯拿INSERT一条条怼或者靠navicate之类的图形工具导SQL结果到了ClickHouse这里发现根本不灵。不是ClickHouse不好用而是它的设计逻辑跟传统OLTP数据库压根不是一回事你得顺着它的脾气来。我在实际项目里帮团队搭过好几套ClickHouse集群也接过不少“为什么写入这么慢”“导出个上千万行的数据总是超时”的现场问题。说实话绝大多数性能事故都不是ClickHouse本身的问题而是导入导出方式没选对。数据导入导出在ClickHouse里不是简单的搬运工角色它直接影响分区的健康度、合并线程的压力、查询性能的稳定性甚至会拖垮整个集群的写入链路。所以这期我把ClickHouse最常见的导入导出套路、参数选择、踩坑点一次性梳理出来希望能帮你少走几周弯路。这套东西适合谁看呢数据开发工程师、大数据运维、后端转数仓的朋友只要你需要把业务数据灌进ClickHouse或者把ClickHouse里的分析结果给到下游报表系统、算法团队这篇文章都适用。新手可以先按最简单的方式跑通全流程有经验的人可以直接翻到参数调优和问题排查部分重点看我标注的“禁区”和“替代方案”。1.1 先理解ClickHouse的存储基座再谈导入导出不理解MergeTree就不要谈ClickHouse的导入导出。为什么因为所有导入数据最终都会落进MergeTree表而MergeTree的工作方式决定了你导入数据时的姿势。MergeTree家族是ClickHouse的核心存储引擎。数据写入时并不是直接覆盖进一个巨大的文件里的而是先生成一个个不可变的“part”数据分片目录。每个part包含一组列文件后台会有一个后台合并线程不断把这些小part合并成大part这个过程叫作merge。你导入数据的方式直接决定了part生成的粒度你要是每秒插100行那就是疯狂生成微型part后台merge根本来不及合并最后你会在日志里看到无休止的“Waiting for background merges”或者直接报Too many parts。我习惯用一个类比MergeTree的写入机制就像往仓库里搬箱子每个part就是一只箱子。你一次搬一万个整齐的大箱子肯定比丢一万只指甲盖大小的小盒子进去要高效得多。后台merge像是一个分拣员他每小时能处理固定数量的箱子你扔小盒子的速度超过他的分拣能力仓库就会爆仓。所以ClickHouse官方一直强调数据导入一定要用批量、大块的方式单次写入的数据量越大、批次数越少整体写入性能越好查询合并压力也越小。还有一个概念需要提前点一下就是“分区”。你在建表时用的PARTITION BY表达式比如按天toYYYYMMDD(event_time)决定了数据的物理组织方式。导入数据时会按照分区键把数据路由到对应分区目录。如果导入的数据覆盖了太多分区每次写入要打开和刷新的分区文件会明显变多写入速度也会下降。这点在后面的导入优化里会再次提到。1.2 导入导出在数据链路中的完整位置我们说的“导入导出”其实覆盖了几个不同层面的操作一是数据从业务库、日志平台、消息队列进到ClickHouse这属于数据接入二是ClickHouse表之间的数据搬迁比如从临时表洗数据到正式表三是把查出来或导出来的数据交给下游比如给Excel给报表给人看或者做跨集群迁移、备份恢复。这三个场景虽然都叫导入导出但技术路线完全不同踩坑点也完全不同。我见过不少同学把“从CSV灌数据”和“从Kafka同步百万级QPS数据”当成同一件事来看结果设置同一套参数两边都不讨好。所以下面我按数据量级和实时性拆开来讲批量离线导入用文件、INSERT SELECT这种方式高吞吐实时导入走Kafka引擎或分布式表写入导出主要考虑文件格式、分批策略和压缩方式。搞清楚你属于哪一类场景再往下看对应的操作不然容易看混。2. 数据导入从单条写入到百万行批量入库2.1 为什么不推荐逐行INSERT批量写入背后的参数逻辑先给一个最典型的反例。业务方说有几百万条订单数据要入库研发同学写了个循环一条一条执行INSERT INTO orders VALUES(...)发现每秒只能写几百行跑来问我ClickHouse为什么这么慢。这不是ClickHouse慢是用法错了。ClickHouse作为列式存储数据库它的强项是每批处理海量数据而不是单条事务。每条INSERT在ClickHouse内部都会至少产生一个part如果顺手写了optimize还会触发合并part数量一多后台merge线程就会成为瓶颈。我从生产集群的监控里看过太多例子写入TPS只有500但system.merges里排队等待合并的part列表一直在100以上后台线程忙得冒烟查询速度也一起被拖慢。正确的姿势是一次性批量写入。ClickHouse官方推荐的插入块大小在10万到100万行之间。这里有两个关键参数跟写入有关需要知道它们的作用参数名默认值作用与建议min_insert_block_size_rows1048576按行数触发生成块的最小阈值。导入时建议保持默认不要调得太小否则会频繁落盘生成小partmax_insert_block_size1048576单次INSERT最大块大小。如果一条INSERT语句携带的数据量极大会被拆分这个参数决定拆分的块大小async_insert0开启异步插入时服务端会把多条小写入合并成大块再落盘适合无法改变写入方代码的场景async_insert_max_data_size1048576字节异步插入积累多少数据后统一落盘单位是字节不是行数再说一个实用的概念insert_deduplication_token和insert_deduplicate。这是对写入去重的支持但多数人用不上先不展开。你只要记住单次INSERT时尽量用一条语句带上多行VALUES或者用clickhouse-client从文件批量导入而不是循环单条插入。在实际项目中我见过有人把逐行INSERT改成每批5000行拼接后写入速度从500行/秒直接提升到3万行/秒这就是批量插入的威力。2.2 文件导入实操clickhouse-client把CSV一键灌进去文件导入是最常见、也最好理解的方式。假设你手上有一份events.csv字段顺序跟表的字段顺序一致表结构是这样的CREATE TABLE events ( event_time DateTime, user_id UInt64, event_name String, cost Float64 ) ENGINE MergeTree PARTITION BY toYYYYMMDD(event_time) ORDER BY (user_id, event_time);把CSV数据导入这张表最简单的命令clickhouse-client \ --host localhost \ --port 9000 \ --query INSERT INTO events FORMAT CSV \ events.csv这里有个非常关键但容易被新手忽略的点FORMAT CSV必须紧跟表名之后、闭合括号之前它的作用是告诉服务端“你接下来收到的数据是CSV格式”然后clickhouse-client会把stdin里的内容原样传给服务端并按CSV解析。如果你写成INSERT INTO events VALUES FORMAT CSV大概率会报语法错误。TSV制表符分隔格式同理把FORMAT CSV换成FORMAT TSV即可。如果你导出的文件是带表头的也不要慌FORMAT CSVWithNames直接支持自动跳过首行。比如clickhouse-client \ --query INSERT INTO events FORMAT CSVWithNames \ events_with_header.csv如果是压缩文件比如.csv.gzClickHouse会通过文件扩展名自动识别压缩格式不需要手动解压。我在导数据时经常直接用gzip压缩过的历史数据文件既节省磁盘又减少网络IO时间。需要说明的是文件导入考验的是磁盘IO和网络带宽瓶颈通常不在ClickHouse本身。我试过用SSD磁盘导入一个2.3GB的CSV文件压缩前大概3800万行整个过程大约花了40秒这个速度在大多数场景下都够用了。还有一个进阶技巧临时要用文件里的部分字段或者想对数据做转换可以组合使用clickhouse-local。比如把CSV里某几列做过滤再导入一条命令就能搞定cat events.csv | clickhouse-local \ --structure event_time DateTime, user_id UInt64 \ --query SELECT event_time, user_id FROM table WHERE event_time 2024-01-01 \ --output-format CSV这个工具不需要启动服务端直接在本机就能跑用来做数据预览、格式转换、ETL清洗都特别方便。我通常会先用它快速看一眼文件里的数据有没有脏值再决定用哪种FORMAT导入避免直接把脏数据倒进生产表。2.3 写入性能调优我调完这些参数之后导入速度直接翻倍先声明一点不要为了追求参数好看就乱调生产环境稳定大于一切。下面这几个设置是我在多个集群上验证过、可控且有效的方案。第一确认客户端侧插入块大小。min_insert_block_size_rows默认就是1048576行一般情况下不用动。但如果你的批量INSERT SELECT一次性查出来的数据量特别大服务端会自动拆分成多个块每个块的字节数受max_insert_block_size控制。有人把max_insert_block_size改得很小比如1000行企图“降低内存占用”结果part数量暴涨写入变慢这属于典型的方向性错误。我建议保持默认值除非你有真实的单条INSERT内存溢出问题。第二考虑开启async_insert。如果数据源确实无法做到批量发送比如是某个第三方SDK逐条上报的那就在服务端打开异步插入clickhouse profile async_insert1/async_insert async_insert_max_data_size104857600/async_insert_max_data_size /profile /clickhouse或者你在连接串的user profile里临时指定clickhouse-client --async_insert1 --async_insert_max_data_size104857600开启后ClickHouse会先把多次小写入缓冲在内存里等累计到一定量默认1MB也太小生产我可以调大一些比如100MB再统一落盘。这个特性在ClickHouse 21.x之后变得稳定实测单条上报场景开启后吞吐能提高一个数量级。但要注意开启异步插入后数据是先进内存的如果服务端异常重启这部分未落盘的数据会丢失。对实时性要求特别高的业务建议评估一下丢失窗口是否可接受。第三合理控制分区覆盖范围。这一点很多人忽略。导入数据的分区跨度越大比如一次导入一整年365天的分区数据ClickHouse需要同时打开、写入几百个分区目录性能会明显下降。所以做历史数据回填的时候我通常按天或者按周把文件拆开分多次导入而不是一次灌一整年。看起来操作次数多了但总耗时反而是下降的。第四导入期间观察后台merge情况。写入过程中可以并行开一个终端随时查system.mergesSELECT table, sum(rows_merged) AS merged_rows, count() AS merging_parts FROM system.merges WHERE database default GROUP BY table;如果这个表的merge积压非常严重说明part生成速度超过了合并速度。这时候要么降低写入并发要么调大background_pool_size和background_merges_mutations_concurrency_ratio但这两个是服务端全局参数改动影响面大需要结合集群负载来定。我的原则是先在导入手法上解决问题批次更大、分区更集中实在不行才动服务端参数。2.4 实时导入Kafka引擎与物化视图的配合离线文件导入只是其中一条腿生产环境里真正的“大数据导入”大头其实来自Kafka。ClickHouse原生支持Kafka引擎表但很多人一开始会把Kafka引擎当成普通表来用往里面查询——这其实是个误区。Kafka引擎表可以理解成一个“管道入口”它本身不存储数据只负责从Kafka指定topic消费消息。你真正要查的数据得靠物化视图把Kafka表里的数据流转到一张真正的MergeTree表。标准做法-- 第一步创建Kafka引擎表作为数据入口 CREATE TABLE events_kafka ( event_time DateTime, user_id UInt64, event_name String, cost Float64 ) ENGINE Kafka SETTINGS kafka_broker_list kafka1:9092,kafka2:9092, kafka_topic_list events, kafka_group_name clickhouse_events_group, kafka_format CSV, -- 也常使用JSONEachRow kafka_num_consumers 4; -- 第二步创建真实的MergeTree目标表 CREATE TABLE events ( event_time DateTime, user_id UInt64, event_name String, cost Float64 ) ENGINE MergeTree PARTITION BY toYYYYMMDD(event_time) ORDER BY (user_id, event_time); -- 第三步创建物化视图自动把Kafka表的数据流转到目标表 CREATE MATERIALIZED VIEW events_mv TO events AS SELECT * FROM events_kafka;这样一旦Kafka topic里有新消息ClickHouse就会自动消费并写入目标表。kafka_format可以根据消息体选JSONEachRow、CSV或者Avro配合kafka_skip_broken_messages参数可以容忍少量坏消息避免一个脏数据导致整个消费卡死。使用Kafka导入时有几个问题需要注意一是kafka_num_consumers不要盲目调大它需要和topic分区数匹配比如topic只有3个分区你设10个consumer也没用多了反而平白增加线程切换开销二是物化视图的写入会占MergeTree表的写入配额如果消费速率过高同样会引发part积压三是Kafka消费是有延迟监控的system.kafka_lag里直接能看到每个consumer的lag情况建议接入监控告警。这部分我曾经写过一个专门的同步框架核心逻辑就是用三张表Kafka入口表、临时缓冲MergeTree表、目标正式表做两段式写入目标表通过INSERT INTO target SELECT ... FROM buffer批量刷入。这样既保证了Kafka到ClickHouse的准实时性又把写入压力集中成大块对小集群特别友好。当然大部分场景没必要这么复杂直接用Kafka引擎物化视图就够了。3. 数据导出把数据安全地送出ClickHouse3.1 最朴素的导出INTO OUTFILE FORMAT组合导出的入门操作是从SELECT查询结果生成一份文件。ClickHouse里最直接的方式是SQL层面的INTO OUTFILESELECT * FROM events WHERE event_time 2024-01-01 INTO OUTFILE /tmp/events_202401.csv FORMAT CSVWithNames;这里有几个细节需要注意。第一INTO OUTFILE后面的路径是相对于运行clickhouse-client的那台服务器而言的不是相对于你本地电脑。如果客户端和服务端是同一台机器当然没问题但远程连的时候文件会落在服务端机器上很多人在这里摸不着头脑。第二FORMAT不能省略而且它决定了文件的列分隔符和表头。常用的有CSV、CSVWithNames、TSV、JSONEachRow如果需要保留类型精度也可以用Native二进制格式。第三如果查询结果很大服务端默认会限制导出查询的时间你可能需要临时调大max_execution_time或者用更合理的分批策略。INTO OUTFILE的底层实现就是把查询结果按指定格式流式写到文件里文件落盘时也可以直接压缩。举个例子导出Parquet格式并gzip压缩SELECT * FROM events INTO OUTFILE /tmp/events.parquet FORMAT Parquet;ClickHouse会根据文件后缀自动选择压缩编码如果后缀是.gz就用gzip.zst用zstd你不需要手动做二次压缩。注意Parquet格式本身自带压缩我导出测试后看到文件只有原始CSV的1/5大小对后续做其他大数据平台的对接特别友好。另外如果你只是想把结果导出到客户端本地而不是服务端磁盘有一个更简单的办法不用INTO OUTFILE直接clickhouse-client \ --query SELECT * FROM events WHERE event_time 2024-01-01 FORMAT CSVWithNames \ /local/path/local_events.csv这时候stdout重定向是发生在你本地shell里的文件当然就在本机。我平时给产品临时拉数就喜欢用这种重定向方式不用去服务端翻文件也不用再scp拷一遍。3.2 大数据量导出分页查询不如日期分段遇到千万级甚至上亿行的导出需求很多人本能地会想到LIMIT分页。但对于ClickHouse来说LIMIT OFFSET这种分页方式在数据量特别大时性能非常差因为它在内部要处理所有被跳过的行。更推荐的导出策略是按时间或者按某个分布均匀的维度字段来做切片。比如导出全年的数据可以这样循环执行for month in 01 02 03 04 05 06 07 08 09 10 11 12; do clickhouse-client \ --query SELECT * FROM events WHERE toYYYYMM(event_time) 2024${month} FORMAT Parquet \ /data/export/events_2024${month}.parquet done这个方案的优点是每个批次的数据量可控导出过程中单次查询占用的内存和临时磁盘都可控而且即使中间某个月失败了只需要重跑那一个月不用整年数据从头再来。我建议每个批次的导出数据量控制在200万到500万行之间单文件大小控制在几百MB以内这样下游系统接收起来也舒服。如果单条SQL查询本身要扫描的数据量巨大比如要跨月聚合之后的结果集特别大还可以用SELECT ... SETTINGS来调整本次查询的资源限制比如提高max_threads、max_memory_usage。但要注意导出时max_threads太小会浪费集群能力太大又可能导致内存短暂飙升一般默认的4到8个线程就差不多。再说一个我常用的导出到对象存储的办法利用s3表函数把查询结果直接写到S3或者MinIO上。INSERT INTO FUNCTION s3( http://minio:9000/bucket/events/2024_{1..12}.parquet, s3_access_key, s3_secret_key, Parquet ) SELECT * FROM events WHERE event_time 2024-01-01;ClickHouse会按{1..12}自动写入12个Parquet分片对下游Spark或Presto引擎读取特别友好。而且因为写的是Parquet字段类型和数据压缩都有保障不会像CSV那样出现类型漂移。这也是我目前推荐度最高的导出方式数据量大、目标又是数据湖或者数仓平台时直接往对象存储上导省去中间文件传输环节。3.3 备份与跨集群迁移文件导入导出之外的“正规军”如果你要做的不是“给人看的数据”而是“整个表/库的备份”那导出成CSV甚至Parquet都不够格。CSV只保留了文字形态的数据字段类型、分区信息、压缩编码、索引元数据全部丢失恢复起来很痛苦。正确的备份姿势是使用ClickHouse官方推荐的物理层手段。一个靠谱的办法是FREEZE 手动拷贝分区。ALTER TABLE events FREEZE会在shadow目录里为当前所有part创建硬链接然后你把shadow目录拷到备份机器上需要恢复时再用ALTER TABLE events ATTACH PARTITION把它挂回去。这套方案能保证数据的一致性但操作门槛高不适合大多数人。更省心的是社区流行的clickhouse-backup工具它封装了FREEZE、打包、上传S3、恢复等流程一条命令就能完成完整表备份clickhouse-backup create --tables default.events clickhouse-backup list clickhouse-backup restore default.events如果用docker方式部署的ClickHouse记得把容器里的/var/lib/clickhouse和/etc/clickhouse-server目录挂载出来这样clickhouse-backup直接操作宿主机目录不会踩到文件权限不一致的坑。跨集群迁移还有一个办法是使用remote表函数INSERT INTO target_db.events SELECT * FROM remote(old-cluster-host:9000, source_db, events, default, password);这条语句相当于从远端读取数据转入本集群。它的好处是不落中间文件全程流式处理坏处是源端查询如果太慢整个INSERT会话会一直挂着需要控制好单次迁移的数据量。我在迁移亿级表时通常会配合WHERE条件分段执行比如按小时或者按天切片每次1000万行左右监控到迁移速度明显下降时再做微调。4. 常见问题与排查技巧实录4.1 Too many parts不是“等一下就好”那么简单在生产环境里我见到最多的导入错误就是Code: 252. Too many parts (300). Merges are processing significantly slower than inserts。很多人看到这个错误特别慌以为集群快挂了其实它表达的意思是后台合并线程处理不过来了part堆积量超过了安全阈值。如果只是偶发一次通常等一下merge线程追上来就好不一定是故障。但如果你反复看到这个报错就要开始查根因了。第一步看system.partsSELECT table, partition, count() AS part_count FROM system.parts WHERE active AND table events GROUP BY table, partition ORDER BY part_count DESC LIMIT 20;正常情况下活跃part数每个分区在个位数到几十之间。如果某个分区的part数长期在好几百说明这个分区的写入频率太高且批次太碎。解决办法是加大批量比如每批至少10万行、开启async_insert聚合小写入、用Buffer引擎或物化视图做写入合并。也可以临时调大max_parts_in_total或parts_to_throw_insert但这只是止痛药不解决根本问题。我自己只会在数据回填高峰的短暂窗口里调大parts_to_throw_insert回填完马上调回默认值。还要注意一个容易忽略的点OPTIMIZE TABLE events FINAL可以强制触发合并但它会让当前线程一直阻塞到合并完成大表上可能运行很久不要在生产高峰期对千万行以上的表执行否则整个表的写入和查询都会被拖到地上。4.2 导出文件字段错位、乱码和类型漂移CSV导出最怕的不是跑不动而是导出来的文件给下游后对不上字段。这类问题的根源往往是表结构里的字段顺序跟你导出的查询列顺序不一致。比如表里有10个字段你写SELECT cost, user_id FROM events导出CSV文件第一列却是cost而下游同事以为是user_id一对接就崩。我的建议是导出时别写SELECT *写明确字段列表文件名或者导出SQL注释里标明列顺序如果必须让下游自动见名知义直接用FORMAT JSONEachRow或者Parquet带schema的文件格式不要用CSV裸奔。乱码问题大多是字符集不一致引起的。ClickHouse默认UTF-8如果你的原始数据是GBK编码的文件导入前需要先转码iconv -f GBK -t UTF-8 source_gbk.csv source_utf8.csv clickhouse-client --query INSERT INTO events FORMAT CSV source_utf8.csv不转码直接导入ClickHouse会按UTF-8解析中文变成一片乱码而且这个错误往往是静默的它不会报错只是落库后看起来像天书。所以导入后我例行会用SELECT count() FROM events WHERE event_name 或抽查几条中文记录来验证编码。类型漂移问题主要出现在强类型字段上。比如CSV里cost列写的是12.5元但表定义是Float64ClickHouse在导入时会尝试把字符串转成数字失败的话整行会报错。我的做法是导入前用clickhouse-local或者Python先做一轮数据清洗把单位符号、空格、特殊字符都处理干净再灌库。不要指望ClickHouse的自动类型转换帮你兜底它只做了很基础的类型宽容复杂业务清洗一定在导入之前完成。4.3 导出查询超时、内存飙高怎么办导出操作本质上也是一次查询它同样受max_execution_time、max_memory_usage这些profile参数限制。遇到“查询被取消因为超过了限制”这类错误可以先在会话里临时放宽限制SELECT * FROM events INTO OUTFILE /tmp/events_full.csv FORMAT CSV SETTINGS max_execution_time 0, max_memory_usage 100000000000;max_execution_time0表示不限制执行时间max_memory_usage按字节数设置100GB一般够用了。注意这不是让你在集群管理后台全局开这么大而是针对当前查询会话临时生效导出完就失效。如果内存还是不够更合理的思路是减小单次查询的数据规模。比如导出大表时不要试图一次SELECT全表改成按分区循环导出或者用sample by或sipHash64(主键) % N把数据切成多片并行导出。这种做法可以利用多台机器并行能力让每个导出子任务只处理全量的1/N内存压力和超时问题都会迎刃而解。另外导出结果集特别大时clickhouse-client端也可能成为瓶颈。它会把服务端返回的流式数据不断落盘到本地如果本地磁盘是机械盘写速会明显拖慢整个流程。这时优先输出.parquet或者开压缩减少本地写盘的字节量能有效缓解。4.4 问题速查实战中最高频的5个坑症状根因解法写入明明没报错但表里查不到数据分区键写错数据进了其他分区检查PARTITION BY表达式按分区查询确认数据落点导入CSV时部分行报错整个导入中断脏数据混入类型转换失败用kafka_skip_broken_messages或input_format_skip_unknown_fields跳过坏行先清洗再导入Too many parts持续告警写入批次太小、part生成过快加大批次、开启async_insert或临时调大parts_to_throw_insert导出的CSV用Excel打开中文乱码UTF-8编码被Excel按GBK解析导出后加BOM头或在文件开头插入sep,或转成GBK跨机查询时INTO OUTFILE文件找不到文件落在服务端而不是客户端改用stdout重定向或明确指定服务端路径并提供访问方式表格里最后一条我特别想多说一句INTO OUTFILE在集群架构下还有一个隐藏问题。如果查询走的是分布式表每个分片都会在各自服务器上生成一部分文件而不是合并后的单个文件。你看到服务端本地只写了一份文件时先确认查询到底命中多少个分片。要合并所有分片的导出文件可以用clickhouse-copier或者先通过分布式表SELECT ... INTO OUTFILE把数据汇总到发起节点再写文件。关于导入导出的整体方案我在多个团队里推行的原则是能用文件批量导入就不要逐条插入能走Kafka异步聚合就不要裸写接口能导出成带schema的格式就不要用CSV裸奔能分片分批就不要一次拉全量。只要守住这四条ClickHouse的导入导出几乎不会给你“惊喜”。有朋友问过我现在ClickHouse版本迭代这么快这些方式会不会过时。我的看法是底层MergeTree的part机制和批量写入原则三五年内不会变变的只是帮你聚合小写入的新函数和新参数。你只要把根本原理搞懂任何新版本里看到类似参数都能立刻明白它是干嘛用的。根据我个人的经验上手ClickHouse的人最花时间的阶段就是前两周的导入导出调试把这篇里的套路和坑位走一遍后面基本就是一片平地了。