clickhouse 创建和删除本地表和分区表注意事项

行走在云端z

已于 2025-02-25 13:59:27 修改

阅读量3.4k

点赞数 1

分类专栏： clickhouse 文章标签： servlet java jvm 1024程序员节

于 2022-10-24 11:26:12 首次发布

本文链接：https://blog.youkuaiyun.com/pang_2899/article/details/126643341

版权

clickhouse 专栏收录该内容

9 篇文章

订阅专栏

1、创建本地表和分区表

在分区集群环境下，在创建本地表和分区表时需要添加'on cluster cluster_name'以便同步到所有的数据节点，否则需要再所有的节点上执行相同的建表语句。

CREATE TABLE data_analysis.t_device_chained_device_status_local_test ON cluster data_analysis_cluster
(
`day` Date COMMENT '统计日期',
`model_code` String COMMENT '型号编码',
`model_name` String COMMENT '型号名称',
`province_code` String COMMENT '绑定省code',
`province_name` String COMMENT '绑定省名称',
`city_code` String COMMENT '绑定城市名称',
`city_name` String COMMENT '绑定城市名称',
`county_code` String COMMENT '绑定县code',
`county_name` String COMMENT '绑定县名称',
`activation_num` UInt64 COMMENT '激活数(历史)',
`active_num` UInt64 COMMENT '活跃数',
`online_num` UInt64 COMMENT '在线数',
`activation_incr_num` Int64 COMMENT '新增激活数(当天)',
`insert_time` DateTime DEFAULT now() COMMENT '插入时间'
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/data_analysis/t_device_chained_device_status_local_test', '{replica}')
PARTITION BY toYYYYMMDD(day)
ORDER BY (insert_time, model_code)
SETTINGS index_granularity = 8192
COMMENT '设备状态表(链式跟踪)-ODS' ;

Query id: 4952c19f-ad64-4bb6-8c43-96ccf6ae013b

┌─host──────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ 10.205.243.42 │ 9000 │ 0 │ │ 3 │ 0 │
│ 10.205.243.42 │ 9001 │ 0 │ │ 2 │ 0 │
│ 10.205.243.41 │ 9000 │ 0 │ │ 1 │ 0 │
│ 10.205.243.43 │ 9000 │ 0 │ │ 0 │ 0 │
└───────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘

CREATE TABLE data_analysis.t_device_chained_device_status_test ON cluster data_analysis_cluster
(
`day` Date COMMENT '统计日期',
`model_code` String COMMENT '型号编码',
`model_name` String COMMENT '型号名称',
`province_code` String COMMENT '绑定省code',
`province_name` String COMMENT '绑定省名称',
`city_code` String COMMENT '绑定城市名称',
`city_name` String COMMENT '绑定城市名称',
`county_code` String COMMENT '绑定县code',
`county_name` String COMMENT '绑定县名称',
`activation_num` UInt64 COMMENT '激活数(历史)',
`active_num` UInt64 COMMENT '活跃数',
`online_num` UInt64 COMMENT '在线数',
`activation_incr_num` Int64 COMMENT '新增激活数(当天)',
`insert_time` DateTime DEFAULT now() COMMENT '插入时间'
)
ENGINE = Distributed('data_analysis_cluster', 'data_analysis', 't_device_chained_device_status_local_test', hiveHash(model_code))
COMMENT '设备状态表(链式跟踪)-ODS';

#删除表（如果需要创新创建同名的表时，需要在删除表的SQL 后面添加 sync 关键字，用来删除zk中的表信息，否则在创建表时报错）
drop table data_analysis.t_device_chained_device_status_local_test ON CLUSTER data_analysis_cluster;

DROP TABLE data_analysis.t_device_chained_device_status_local_test ON CLUSTER haigeek_data_analysis_cluster

Query id: fe87e730-fa74-4160-b750-8353bde80a1e

┌─host──────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ 10.205.243.42 │ 9000 │ 0 │ │ 3 │ 0 │
│ 10.205.243.42 │ 9001 │ 0 │ │ 2 │ 0 │
│ 10.205.243.43 │ 9000 │ 0 │ │ 1 │ 0 │
│ 10.205.243.41 │ 9000 │ 0 │ │ 0 │ 0 │
└───────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘

# 再次创建表时，报错如下：

CREATE TABLE data_analysis.t_device_chained_device_status_local_test ON CLUSTER data_analysis_cluster
(
`day` Date COMMENT '统计日期',
`model_code` String COMMENT '型号编码',
`model_name` String COMMENT '型号名称',
`province_code` String COMMENT '绑定省code',
`province_name` String COMMENT '绑定省名称',
`city_code` String COMMENT '绑定城市名称',
`city_name` String COMMENT '绑定城市名称',
`county_code` String COMMENT '绑定县code',
`county_name` String COMMENT '绑定县名称',
`activation_num` UInt64 COMMENT '激活数(历史)',
`active_num` UInt64 COMMENT '活跃数',
`online_num` UInt64 COMMENT '在线数',
`activation_incr_num` Int64 COMMENT '新增激活数(当天)',
`insert_time` DateTime DEFAULT now() COMMENT '插入时间'
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/data_analysis/t_device_chained_device_status_local_test', '{r eplica}')
PARTITION BY toYYYYMMDD(day)
ORDER BY (insert_time, model_code)
SETTINGS index_granularity = 8192
COMMENT '设备状态表(链式跟踪)-ODS'；

Query id: ee1ce424-73f1-4192-b7ee-14e42c002c83

┌─host──────────┬─port─┬─status─┬─error───────────────────────────────────────────────────────────────────────────────────────────── ──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─num_host s_remaining─┬─num_hosts_active─┐
│ 10.205.243.42 │ 9000 │ 253 │ Code: 253, e.displayText() = DB::Exception: Replica /clickhouse/tables/01/data_analysis/ t_device_chained_device_status_local_test/replicas/example01-01-2 already exists (version 21.8.13.6 (official build)) │ 3 │ 0 │
│ 10.205.243.42 │ 9001 │ 253 │ Code: 253, e.displayText() = DB::Exception: Replica /clickhouse/tables/02/data_analysis/ t_device_chained_device_status_local_test/replicas/example01-02-2 already exists (version 21.8.13.6 (official build)) │ 2 │ 0 │
│ 10.205.243.43 │ 9000 │ 253 │ Code: 253, e.displayText() = DB::Exception: Replica /clickhouse/tables/02/data_analysis/ t_device_chained_device_status_local_test/replicas/example01-02-1 already exists (version 21.8.13.6 (official build)) │ 1 │ 0 │
│ 10.205.243.41 │ 9000 │ 253 │ Code: 253, e.displayText() = DB::Exception: Replica /clickhouse/tables/01/data_analysis/ t_device_chained_device_status_local_test/replicas/example01-01-1 already exists (version 21.8.13.6 (official build)) │ 0 │ 0 │
└───────────────┴──────┴────────┴─────────────────────────────────────────────────────────────────────────────────────────────────── ──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴───────── ────────────┴──────────────────┘
→ Progress: 0.00 rows, 0.00 B (0.00 rows/s., 0.00 B/s.) 0%
4 rows in set. Elapsed: 0.113 sec.

Received exception from server (version 21.8.13):
Code: 253. DB::Exception: Received from localhost:9000. DB::Exception: There was an error on [10.205.243.42:9000]: Code: 253, e.disp layText() = DB::Exception: Replica /clickhouse/tables/01/operation_data_analysis/t_device_chained_device_status_local_test/repli cas/example01-01-2 already exists (version 21.8.13.6 (official build)).

#1、出现这个错误需要等待几分钟后再进行创建就会成功。

drop table data_analysis.t_device_chained_device_status_local_test ON CLUSTER data_analysis_cluster sync　;

DROP TABLE data_analysis.t_device_chained_device_status_local_test ON CLUSTER data_analysis_cluster NO DELAY

Query id: 7a9b0b1b-dd4f-4ef2-879c-7607ec000d50

4 rows in set. Elapsed: 0.114 sec.

CREATE TABLE data_analysis.t_ods_device_chained_device_status_local_test ON cluster data_analysis_cluster　(　 `day` Date COMMENT '统计日期',　 `model_code` String COMMENT '型号编码',　 `model_name` String COMMENT '型号名称',　 `province_code` String COMMENT '绑定省code',　 `province_name` String COMMENT '绑定省名称',　 `city_code` String COMMENT '绑定城市名称',　 `city_name` String COMMENT '绑定城市名称',　 `county_code` String COMMENT '绑定县code',　 `county_name` String COMMENT '绑定县名称',　 `activation_num` UInt64 COMMENT '激活数(历史)',　 `active_num` UInt64 COMMENT '活跃数',　 `online_num` UInt64 COMMENT '在线数',　 `activation_incr_num` Int64 COMMENT '新增激活数(当天)',　 `insert_time` DateTime DEFAULT now() COMMENT '插入时间'　)　ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/data_analysis/t_device_chained_device_status_local_test', '{replica}')　PARTITION BY toYYYYMMDD(day)　ORDER BY (insert_time, model_code)　SETTINGS index_granularity = 8192　COMMENT '设备状态表(链式跟踪)-ODS' ;

CREATE TABLE data_analysis.t_ods_device_chained_device_status_local_test ON CLUSTER data_analysis_cluster
(
`day` Date COMMENT '统计日期',
`model_code` String COMMENT '型号编码',
`model_name` String COMMENT '型号名称',
`province_code` String COMMENT '绑定省code',
`province_name` String COMMENT '绑定省名称',
`city_code` String COMMENT '绑定城市名称',
`city_name` String COMMENT '绑定城市名称',
`county_code` String COMMENT '绑定县code',
`county_name` String COMMENT '绑定县名称',
`activation_num` UInt64 COMMENT '激活数(历史)',
`active_num` UInt64 COMMENT '活跃数',
`online_num` UInt64 COMMENT '在线数',
`activation_incr_num` Int64 COMMENT '新增激活数(当天)',
`insert_time` DateTime DEFAULT now() COMMENT '插入时间'
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/data_analysis/t_device_chained_device_status_local_test', '{replica}')
PARTITION BY toYYYYMMDD(day)
ORDER BY (insert_time, model_code)
SETTINGS index_granularity = 8192
COMMENT '设备状态表(链式跟踪)-ODS'

Query id: 9c98d94a-fc6c-4882-b04c-a0b13dcd9216

┌─host──────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ 10.25.23.42 │ 9000 │ 0 │ │ 3 │ 0 │
│ 10.25.23.42 │ 9001 │ 0 │ │ 2 │ 0 │
│ 10.25.23.43 │ 9000 │ 0 │ │ 1 │ 0 │
│ 10.25.23.41 │ 9000 │ 0 │ │ 0 │ 0 │
└───────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘

4 rows in set. Elapsed: 0.113 sec.