clickhouse数据导出备份和恢复

 

1.导出/导入(导出文件大小与原始大小一致):
clickhouse-client --query="select * from db_test.app_message_80" >/tmp/app_message_80.tsv

查看表大小
SELECT
    database,
    table,
    sum(rows) AS `总行数`,
    formatReadableSize(sum(data_uncompressed_bytes)) AS `原始大小`,
    formatReadableSize(sum(data_compressed_bytes)) AS `压缩大小`,
    round((sum(data_compressed_bytes) / sum(data_uncompressed_bytes)) * 100., 2) AS `压缩率/%`
FROM system.parts
WHERE table = 'app_message_80'
GROUP BY
    database,
    table
ORDER BY database ASC

Query id: 0eb5dc3c-fa4a-4833-99c3-6662263f777c

┌─database─┬─table──────────┬──总行数─┬─原始大小─┬─压缩大小───┬─压缩率/%─┐
│ db_test  │ app_message_80 │ 3232703 │ 1.11 GiB │ 238.86 MiB │     21.1 │
└──────────┴────────────────┴─────────┴──────────┴────────────┴──────────┘

1 rows in set. Elapsed: 0.005 sec. 

导出文件大小(1.3GB)
[root@localhost data]# ls -al /tmp/app_message_80.tsv
-rw-rw-r-- 1 root root 1331188647 Jan 13 17:46 /tmp/app_message_80.tsv

压缩后文件大小(200M),发现压缩后跟在clickhouse中的占用的查不多

[root@localhost tmp]# ls -al app_message_80.tsv.gz
-rw-rw-r-- 1 root root 215100950 Jan 13 17:46 app_message_80.tsv.gz

 

导入(事先truncate表,注意FORMAT后面大写,多个文件可以使用cat /tmp/app_message*):
cat /tmp/app_message_80.tsv | clickhouse-client --query="insert into db_test.app_message_80 FORMAT TSV"