ClickHouse相关学习

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