做ClickHouse有一段时间的人都会有这种体会导入导出这种听起来最基础的操作真到生产环境里处理起来坑比想象中多得多。前阵子帮一个团队做数据迁移几千万行订单数据要从业务库同步到ClickHouse本来以为一条INSERT语句搞定结果在格式、分区、内存上限上来回折腾。这篇文章就把我这些年用下来的导入导出方法完整整理一遍从文件导入、MySQL同步、Kafka实时接入到导出给下游系统、做备份恢复每块都给出能直接抄走的方案。不管是刚接触ClickHouse的新手还是已经在集群上维护数据管线的同学应该都能从中找到自己需要的东西。1. 先想清楚数据形态决定导入导出方案1.1 导入导出不是“搬数据”三个字那么简单很多人把导入导出理解为“复制粘贴”实际上ClickHouse的导入导出本质是一套序列化与反序列化的过程源数据要经过格式解析、类型映射、压缩传输最后写入分区目录或转成目标文件。任何一个环节不匹配轻则字段错位重则全部报错。我习惯的做法是先把场景分清楚。一种是批量交换型离线导出一份大文件或者把以前的老数据一次性灌进来这种场景对吞吐要求高对延迟完全不敏感。另一种是业务同步型从MySQL等业务库持续同步增量数据或者从Kafka接实时流这种场景更在意稳定性和资源占用不能因为一次同步把集群拖垮。两种场景下工具选择和参数配置逻辑是完全不同的。另外一个容易忽略的点是导入导出不只是“从A到B”它往往还牵扯到数据治理问题。比如从业务库同步过来源库字段没加索引、时间字段混着时区、NULL值写法不统一这些都会在导入时报错或产生脏数据。我的建议是在动手之前先把源数据的结构摸清楚至少用工具预览一下文件的前几行不要直接全量灌。1.2 一张表理清常见数据来源与出口整理了这几年最常用的数据来源和对应工具可以直接对着选型数据来源/去向典型场景推荐工具/方式本地CSV/TSV/JSON文件离线导入批量数据、数据迁移clickhouse-client FORMAT或clickhouse-localMySQL/PostgreSQL等业务库全量/增量同步mysql()/postgresql()表函数或MaterializedMySQLKafka消息队列实时埋点、日志接入Kafka表引擎 物化视图S3/OSS对象存储数据湖、归档文件批量加载s3()表函数支持Parquet/CSV/JSON其他ClickHouse集群跨集群复制、迁移remote()表函数或导出文件再导入导出给业务/报表系统生成报表文件、供大数据平台消费SELECT INTO OUTFILE或客户端管道重定向备份与恢复日常备份、灾难恢复ALTER TABLE FREEZE或Native格式全量导出这张表只是方向性判断具体到每种方式怎么做下面逐块展开。2. 文件导入最常用的手段也最容易踩坑2.1 从CSV到JSONEachRowFORMAT选型的细节文件导入最基础的姿势就是clickhouse-client配合FORMAT比如导入一个CSV文件clickhouse-client --query INSERT INTO order_db.orders FORMAT CSV orders.csv这里有个关键点如果没有指定列名文件里字段的顺序必须和表结构完全一致。比如表有五个字段文件第一列会对应第一个字段第二列对应第二个字段以此类推。一旦顺序不对数据就会串列而且不一定报错可能只是数字变成了0、String变成了空字符串这种脏数据最难排查。如果文件是CSV格式还需要注意NULL的表示方式。ClickHouse在CSV里约定NULL用\N表示空字符串就表示空字符串。很多从MySQL导出的文件里NULL就是空导入后就成了空字符串而不是NULL这在聚合统计时会出问题。建议导出源数据时统一把NULL转成\N或者在导入后用SQL再清洗一轮。另一个常见问题是文件第一行带列名。如果直接用CSV格式导入第一行会被当成真实数据解析然后报类型错误。两个解决办法要么用CSVWithNames格式要么加上--format_csv_skip_first_line1参数跳过首行。我推荐前者因为带列名还能顺带检查列顺序对不对。除了CSVJSONEachRow也是高频格式适合日志数据clickhouse-client --query INSERT INTO log_db.events FORMAT JSONEachRow events.jsonJSONEachRow要求文件的每一行都是一个完整的JSON对象字段名和表字段名对应不需要严格按顺序。这种格式比较宽松但解析成本比CSV高导入速度会慢一些。如果追求极致的导入性能CSV和TSV仍然是首选。2.2 大文件别硬扛拆文件和错误容忍参数生产环境里导入上GB的CSV是常态如果一条命令直接把整个文件塞进去很容易撞上内存限制或者解析错误导致整个导入回滚。我的经验是数据文件超过500MB就拆拆成多个100MB左右的小文件然后循环导入或者用xargs并行导入。拆文件本身也很简单Linux下用split命令split -l 1000000 -d orders.csv orders_part_这会按每个文件100万行拆分输出orders_part_00、orders_part_01这样的文件。拆完之后再循环导入for f in orders_part_*; do clickhouse-client --query INSERT INTO order_db.orders FORMAT CSV $f done这样有个额外好处即使某一个分片文件有坏行不会影响其他文件。配合错误容忍参数导入就更稳了。这几个参数是生产环境必备clickhouse-client \ --query INSERT INTO order_db.orders FORMAT CSV \ --input_format_skip_unknown_fields1 \ --input_format_allow_errors_num1000 \ --input_format_allow_errors_ratio0.05 orders_part_00input_format_skip_unknown_fields作用是如果文件里出现了表结构中不存在的字段直接跳过而不报错这在多团队协作时非常有用因为源端很可能加了字段但ClickHouse表还没来得及加。input_format_allow_errors_num和input_format_allow_errors_ratio则允许一定数量的坏行跳过比如1000行或者5%的比例适合清洗不彻底的数据源。注意这两项会掩盖真实的脏数据所以用的时候要克制最好导入完运行一次校验。2.3 clickhouse-local不启动服务也能玩转文件很多新人不知道clickhouse-local这个工具它其实就是一个不依赖服务端的ClickHouse单机版本可以直接对本地文件执行SQL。我经常用它来干两件事导入前预览文件、导入后快速校验数据。先看文件长什么样clickhouse-local \ --structure order_id UInt64, user_id UInt64, amount Decimal(10,2), status String, order_date DateTime \ --input-format CSV \ --query SELECT * FROM table LIMIT 10 \ --file orders.csv这里的table是clickhouse-local对输入文件的默认表名。执行之后就能像查表一样看到前10行字段类型不匹配在一开始就暴露了。还可以直接做格式转换比如把CSV转成Parquetclickhouse-local \ --structure order_id UInt64, user_id UInt64, amount Decimal(10,2), status String, order_date DateTime \ --input-format CSV \ --output-format Parquet \ --query SELECT * FROM table \ --file orders.csv orders.parquet这个工具的价值在于它把“先看数据再导数据”的流程大大简化了。以前我得先建表、导入、查询发现不对再删表重来现在直接在文件层面完成校验省了很多事。3. 从其他系统和消息队列同步不止一种接法3.1 用MySQL表函数做全量迁移和增量拉取如果数据源在MySQL用mysql()表函数是最快上手的方案不需要额外的同步工具直接在ClickHouse里写SQL就能把数据拉过来。全量迁移的写法很简单INSERT INTO order_db.orders SELECT order_id, user_id, amount, status, order_date FROM mysql( 192.168.1.10:3306, business_db, orders, readonly_user, readonly_password );这条SQL执行完MySQL里的orders表数据就全部灌进了ClickHouse。做全量迁移时我建议分批拉不要一条SQL干到底尤其是几千万行的表。可以按主键或时间范围加WHERE条件分成几百个批次循环执行。增量同步的方案也可以基于表函数自己实现。比如在源表里加一个update_time字段每次同步都只拉超过上次记录时间点的数据。虽然不如专门的同步组件自动化但对于大多数中小团队来说完全够用了。官方还有个MaterializedMySQL引擎可以实现MySQL到ClickHouse的自动同步但生产环境里用之前要做好充分的测试它依赖binlog解析源库的binlog格式、权限配置都有硬性要求并不适合所有场景。3.2 Kafka表引擎实时导入的最小可用配置Kafka接入是ClickHouse做实时数仓的核心路径。官方推荐的方案是Kafka表引擎加物化视图思路是先建一张Kafka引擎表作为数据流的“暂存区”再建物化视图把数据实时写入真正的MergeTree表。建Kafka引擎表的语法CREATE TABLE kafka_orders ( order_id UInt64, user_id UInt64, amount Decimal(10, 2), status String, order_date DateTime ) ENGINE Kafka() SETTINGS kafka_broker_list 192.168.1.20:9092, kafka_topic_list orders_topic, kafka_group_name clickhouse_orders_group, kafka_format JSONEachRow;再建目标表CREATE TABLE order_db.orders ( order_id UInt64, user_id UInt64, amount Decimal(10, 2), status String, order_date DateTime ) ENGINE MergeTree PARTITION BY toYYYYMM(order_date) ORDER BY (order_date, order_id);最后建物化视图把两边串起来CREATE MATERIALIZED VIEW mv_orders TO order_db.orders AS SELECT * FROM kafka_orders;这里有几个细节值得注意。kafka_group_name对应Kafka消费组如果多个ClickHouse副本都指向同一个消费组消息会分摊消费如果每个副本用不同消费组那每条消息都会被多个副本各自消费一遍这通常不是你要的效果。kafka_format要和Producer端写入的序列化格式保持一致最常见的是JSONEachRow和CSV。Kafka引擎表有几个“坑”是我实际踩过的。第一Kafka引擎表本身不能直接SELECT到数据因为消息是边进边消费的直接SELECT基本什么都查不到数据都通过物化视图转走了所以排查问题要去看目标表。第二消费延迟时要留意目标表的写入性能如果MergeTree的merge跟不上海量小批量写入会出现TOO_MANY_PARTS的报错这时候要给目标表调大parts_to_delay_insert和parts_to_throw_insert或者降低Kafka消费并行度再不行就调整生产端的批量大小。第三Kafka引擎表不负责消息堆积管理消费端出问题消息会在Kafka里积压ClickHouse不提供类似“从上次位点重新消费”的自动机制需要借助Kafka管理工具重置消费位点。3.3 从S3和对象存储批量导入数据进了数据湖或者归档到对象存储再想导进ClickHouses3()表函数是最直接的工具。它支持CSV、Parquet、ORC、JSONEachRow等格式甚至不需要先把文件下载到本地。INSERT INTO order_db.orders SELECT * FROM s3( https://my-bucket.s3.amazonaws.com/data/orders/*.parquet, aws_access_key_id, aws_secret_access_key, Parquet );路径里支持通配符*和?比如orders_2024_*.parquet就能匹配一批文件。这里要特别提醒一点如果你的bucket里有大量文件一次SELECT会全量扫描匹配到的对象导入前最好先用一个小的前缀路径测试一下确认通配符范围符合预期。关于AK/SK的硬编码问题官方其实不推荐在SQL里明文写密钥建议把访问凭据配置在config.xml中然后通过--s3_access_key_id等命令行参数或者环境变量引用避免密钥散落在业务代码里。如果ClichHouse集群部署在云内网也可以走实例角色免密访问S3相比硬编码密钥安全得多。4. 导出不只是SELECT INTO OUTFILE4.1 两种文件导出的写法别搞混文件落在哪导出和导入一样也有两种主流姿势。第一种是SQL里的INTO OUTFILEclickhouse-client --query \ SELECT * FROM order_db.orders INTO OUTFILE /data/export/orders.csv FORMAT CSV注意这里有个绝大多数人都会踩的坑INTO OUTFILE写出的文件落在ClickHouse服务端的磁盘上而不是你执行命令的客户端机器上。如果你在本地连的是远程集群文件会出现在远程服务器的/data/export/目录不是你以为的自己电脑上的路径。我刚开始用的时候在这个问题上卡了半小时一直找不到文件。第二种是用客户端重定向文件落在执行命令的机器上clickhouse-client --query \ SELECT * FROM order_db.orders FORMAT CSV orders.csv两种方式各有适用场景。服务器端写文件适合大数据量导出因为数据不经过客户端网络传输效率高但需要服务端有目录写权限而且要记得处理完之后把文件取走或者定期清理。客户端重定向适合中小数据量数据在本地方便直接给下游使用。如果导出文件很大管道加gzip压缩是个好习惯clickhouse-client --query \ SELECT * FROM order_db.orders FORMAT CSV | gzip orders.csv.gz或者服务端导出时直接写.gz后缀ClickHouse会根据扩展名自动压缩clickhouse-client --query \ SELECT * FROM order_db.orders INTO OUTFILE /data/export/orders.csv.gz FORMAT CSV压缩能大幅减少磁盘占用和后续传输时间代价是导出时多花一点CPU实测对千亿级集群上的大结果集导出来说性价比非常高。4.2 导出格式选型给下游的数据长什么样导出格式选择的基本逻辑是“看下游是谁”。业务方要Excel能打开的数据多半给CSV数据要继续进数仓或Spark做分析给Parquet最合适对接日志系统或API消费JSONEachRow更通用。目标下游推荐格式备注业务运营/报表ExcelCSV注意分隔符和编码Excel需UTF-8 BOM数仓/Hadoop/SparkParquet列式存储保留类型信息体积更小日志系统/消息队列JSONEachRow每行独立JSON方便逐条消费ClickHouse自身的备份迁移Native内部二进制格式导入导出速度最快归档长期存储Parquet或ORC压缩比高生态兼容性好比如导Parquet给数仓clickhouse-client --query \ SELECT * FROM order_db.orders FORMAT Parquet orders.parquet如果只是ClickHouse实例之间的迁移Native格式是效率之王因为它是ClickHouse内部序列化格式类型信息完整保留导入时完全不需要重新解析和类型转换。可以用FORMAT Native导入导出速度比CSV快好几倍。4.3 跨集群数据复制和备份场景的导出思路很多团队做集群迁移或双活时需要在两个ClickHouse集群之间搬数据。除了导出文件再导入这种笨办法remote()表函数可以在一条SQL里完成跨集群复制INSERT INTO order_db.orders SELECT * FROM remote( old-cluster-host:9000, order_db.orders, default, password );这条SQL把远端集群表的数据拉回本地写入。注意remote()表函数走的是ClickHouse原生TCP协议不需要额外开任何接口配置好两端账号权限即可。如果两个集群网络不通就只能用导出文件再导入的方式文件介质可以用对象存储中转。日常备份还有一个官方快照机制ALTER TABLE ... FREEZE会把当前分区目录的硬链接快照放到备份目录。它的特点是速度快、不影响线上查询适合做定期的物理备份。恢复时用ALTER TABLE ... ATTACH PARTITION从detached目录把分区重新挂载回来。这个方案不依赖任何外部工具是ClickHouse运维上非常实用的兜底手段。5. 实操演练一个电商订单表的导入导出全流程5.1 建表与准备测试数据空谈方法没用拿一个电商订单表走一遍完整流程最有说服力。先建表CREATE TABLE order_db.orders ( order_id UInt64, user_id UInt64, amount Decimal(10, 2), status String, order_date DateTime ) ENGINE MergeTree PARTITION BY toYYYYMM(order_date) ORDER BY (order_date, order_id);模拟生成一批测试数据我习惯用脚本生成CSV字段按表结构顺序排列一行一条。生成600万行文件大小大概400MB左右足够看出性能差异。数据里故意混入几行“脏数据”测试错误容忍参数的效果比如amount字段写成非数字、order_date少一个字段后续导入时才能看到实际表现。5.2 分段导入600万行数据并调速文件生成好后先用split拆分然后循环导入。导入前对比一下不调参数直接导一个600MB的文件和拆分后带错误容忍参数导入两者的差别非常明显。直接导大文件时如果中间遇到坏行整个批次会回滚而且容易触发内存限制拆分后导入即使某个分片失败重试成本也低很多。循环导入期间我习惯开着ClickHouse的系统表观察写入情况SELECT table, partition, rows, bytes_on_disk FROM system.parts WHERE table orders ORDER BY partition;这样可以实时看到每个分区的数据量确认数据确实按toYYYYMM(order_date)落到了正确的月份分区里。这一步很重要因为分区逻辑如果写错比如时间字段用的是字符串没有解析成DateTime那么所有数据可能都堆在同一个分区后续查询性能直接打折。分段导入还有一个调优点--max_insert_block_size。这个参数控制每次插入的块大小默认值是1048576行实际导入时如果单次插入块太大merge压力会很大。一般建议调成10万到20万行左右让写入更平滑clickhouse-client \ --query INSERT INTO order_db.orders FORMAT CSV \ --max_insert_block_size100000 \ --input_format_allow_errors_num1000 orders_part_00如果文件本身就按日期排序每个分片文件只包含一个月的分区导入时不会再触发跨分区问题。但如果一个文件里混了好几个月份的数据一条INSERT插入了多个分区系统会按照max_partitions_per_insert_block限制检查默认100个分区超过就报Too many partitions。遇到这种情况要么按分区重新拆分文件要么适当调大这个限制。调优之后600万行导入时间通常在几秒到十几秒之间具体取决于磁盘类型和CPU性能。ClickHouse的写入能力本来就是列式存储里数一数二的瓶颈往往不在导入本身而在源文件的解析和网络传输上这里的经验是源文件用CSV而不是复杂嵌套JSON解析速度会快很多。5.3 导出到CSV并验证行数与字段数据导进来之后再看看导出去。导出到CSV文件clickhouse-client --query \ SELECT * FROM order_db.orders INTO OUTFILE /data/export/orders_export.csv FORMAT CSV然后在服务端检查文件行数wc -l /data/export/orders_export.csv这里有个小小陷阱如果表里的数据本身包含换行符或\N用wc -l统计行数会不准。导出的数据可能与源文件行数不一致。遇到这种情况应该用SQL的count精确核对SELECT count() FROM order_db.orders;再用clickhouse-local把导出文件读出来统计clickhouse-local \ --structure order_id UInt64, user_id UInt64, amount Decimal(10,2), status String, order_date DateTime \ --input-format CSV \ --query SELECT count() FROM table \ --file /data/export/orders_export.csv两边的count对得上才能确认导出成功。这个校验习惯我强烈建议保留不用怕麻烦数据管道出了问题再回头排查成本高得多。6. 参数调优与常见问题排查实录6.1 导入导出速度真正相关的几个参数调优不能靠玄学得清楚每个参数控制什么。我把实际工作中最影响导入导出效果的参数整理成了表格参数默认值作用调优建议max_insert_block_size1048576单次插入块行的最大值大批量导入时可调低至10万~20万避免merge抖动max_threadsCPU核数导入解析/查询的并发线程数高并发导出时适当调低避免打满CPUmax_memory_usage0不限制单查询内存上限大文件导入报内存超限时要么调大要么拆文件max_partitions_per_insert_block100一次INSERT可涉及的最大分区数跨多月文件导入时应调大或拆分文件input_format_allow_errors_num0允许的格式错误最大行数清洗不彻底的源数据可设置1000左右input_format_skip_unknown_fields0跳过未知字段源端字段频繁变动建议设为1network_compression_methodLZ4客户端与服务端传输压缩算法广域网传输可换成ZSTD压缩率更高但更吃CPU这些参数不是越大越好也不是越小越好关键在于匹配具体场景。比如max_threads导入大文件时并发确实能提速但集群同时还在跑业务查询时过高的导入并发会把CPU打满影响线上查询响应所以生产环境中我喜欢把导入任务安排在业务低峰并对并发做限流。6.2 导入报错的排查实录把实际工作中遇到的典型报错和解决思路整理如下每一条都是真金白银踩出来的。报错一Cannot parse input: expected...这是最经典的解析失败错常见于CSV字段数量对不上或类型不符。排查思路是先用head看前几行用clickhouse-local预览通常一眼就能看出问题。如果是文件里混了坏行可以先用--input_format_allow_errors_num兜底导入后跑一遍数据质量检查把异常数据清理掉。报错二Memory limit exceeded大文件导入时常见。核心原因有两个要么文件一次性解析进内存要么数据本身单行特别大。解决办法首先是拆文件别用一条INSERT扛整个文件其次看一下集群的内存配额设置如果多个任务同时在跑内存配额被占满可以错峰导入。如果单行就是很大的JSON考虑换CSV格式或者把大字段单独拆列。报错三Too many partitions一条INSERT涉及的唯一值过多超过max_partitions_per_insert_block。比如按天分区的表你一次性导入三个月的文件分区数轻松过百。解决方法是把文件按日期维度拆开分批导入每个批次只涉及少数几个分区。报错四TOO_MANY_PARTS写入速度远大于后台merge速度时会出现。典型场景是Kafka接入后目标表持续高频写入同时分区数量也不多导致parts堆积。解决思路包括调大parts_to_delay_insert和parts_to_throw_insert降低写入频率或者检查分区键设计是否过于细粒度。这个报错本质上不是导入本身的问题而是写入节奏和merge能力不匹配。6.3 导出的隐性坑时区、NULL、编码导出到文件看着简单但有几个隐蔽问题容易让人怀疑人生。第一个是时区。ClickHouse的DateTime在内部按UTC存储导出CSV时会按服务端的时区设置输出如果服务端时区是UTC而下游业务在UTC8导出的时间字段就差了8小时。解决方法是导出前显式转换SELECT order_id, user_id, amount, status, toDateTime(order_date, Asia/Shanghai) AS order_date_local FROM order_db.orders INTO OUTFILE /data/export/orders_cst.csv FORMAT CSV;导出后最好抽查几条时间数据确认偏移量符合预期。第二个是NULL的表示。导出CSV后NULL字段默认变成\N但很多下游系统并不知道这个约定会把\N当成普通字符串造成数据错误。如果下游是Excel或普通文本处理工具导出时先把NULL转成空字符串SELECT order_id, ifNull(user_id, 0) AS user_id, ...第三个是编码问题。ClickHouse默认UTF-8如果数据里混入非法编码或者Excel打开CSV出现乱码通常是在CSV文件头加BOM或者导出前用replaceAll清洗特殊字符。给运营同事的CSV文件我一般会用Excel可以正确识别UTF-8的方式处理一下不然发过去就是一堆乱码来回沟通的成本比重新导一次高得多。还有一个容易忽略的点是重复数据。跨集群用remote()同步数据时如果中途失败重跑很容易重复写入。ClickHouse没有原生的主键去重机制所以导入前最好先确认目标表里没有相同主键的数据或者用ReplacingMergeTree这类带去重能力的表引擎再配合幂等写入从源头规避重复。根据我个人实际操作的体会导入导出这件事最大的敌人不是ClickHouse本身而是源数据的不确定性。你永远不知道下一个文件里会混进什么奇怪的字符、缺失的字段、错误的时区。所以我现在不管做什么数据接入第一件事永远是先小样本预览再定格式、定参数、定校验规则。把这套流程固化下来之后数据管线稳定很多半夜被叫起来排查问题的次数也少了很多。