ClickHouse分布式表
如果db不是分布式的要执行一下这个语句
create database datasets on cluster ‘cluster_5shards_1replicas’(集群名)
CREATE TABLE datasets.table_name_local on cluster 'cluster_5shards_1replicas' ( `rid` String COMMENT '业务唯一主键', `nl` String COMMENT '年龄', `kfgd_id` String COMMENT '唯一标识', `kfgd_del_flag` String COMMENT '是否删除', `kfgd_ctime` String COMMENT '更新时间', `jzd` String COMMENT '居住地', `jwrjry` String COMMENT '是否14天内境外入境人员', `jslx` String COMMENT '结算类型', `jcxm` String COMMENT '检测项目', `jcsj` String COMMENT '检测日期', `jcjieguo` String COMMENT '检测结果', `jcjg` String COMMENT '检测机构', `insert_time` String COMMENT '入库时间', `hjd` String COMMENT '户籍地', `gj` String COMMENT '国籍', `dt_source` String COMMENT '数据来源', `del_tag` String COMMENT '数据删除标记', `cyrq` DateTime COMMENT '采样日期' ) ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/{shard}/table_name_local', '{replica}', cyrq) ORDER BY rid SETTINGS index_granularity = 8192
CREATE TABLE datasets.table_name on cluster 'cluster_5shards_1replicas' (`rid` String COMMENT '业务唯一主键', `nl` String COMMENT '年龄', `kfgd_id` String COMMENT '唯一标识', `kfgd_del_flag` String COMMENT '是否删除', `kfgd_ctime` String COMMENT '更新时间', `jzd` String COMMENT '居住地', `jwrjry` String COMMENT '是否14天内境外入境人员', `jslx` String COMMENT '结算类型', `jcxm` String COMMENT '检测项目', `jcsj` String COMMENT '检测日期', `jcjieguo` String COMMENT '检测结果', `jcjg` String COMMENT '检测机构', `insert_time` String COMMENT '入库时间', `hjd` String COMMENT '户籍地', `gj` String COMMENT '国籍', `dt_source` String COMMENT '数据来源', `del_tag` String COMMENT '数据删除标记', `cyrq` DateTime COMMENT '采样日期') ENGINE = Distributed('cluster_5shards_1replicas', 'datasets', 'table_name_local', sipHash64(rid))
ClickHouse去重
非分布式表
optimize table table_name final;
分布式表
optimize table table_name on cluster cluster_5shards_1replicas final;
ClickHouse导出csv格式
带表头
clickhouse-client --password 123 --database="datasets" --query=" select * from table_name FORMAT CSVWithNames" > info.csv
不带表头
clickhouse-client --password 123 --database="datasets" --query=" select * from table_name FORMAT CSV" > info.csv
|