clickhouse分布式表维护

 

1.分布式表新增字段，本地表也需要新增字段

ALTER TABLE tb_test_all02 ON CLUSTER default ADD COLUMN name1 String;

本地表查看是否自动新增字段
本地表是不会自动新增字段的
CREATE TABLE db_hxl.tb_test_local02
(
    `id` Int32,
    `name` String,
    `timestamp` DateTime DEFAULT now(),
    `day` Date DEFAULT now()
)
ENGINE = MergeTree
PARTITION BY day
ORDER BY id
SETTINGS index_granularity = 8192 

这个是写入数据会报错误
INSERT INTO tb_test_all02 (id, name) values(11,'name1');
INSERT INTO tb_test_all02 (id, name) values(12,'name12');

Received exception from server (version 24.8.11):
Code: 16. DB::Exception: Received from 192.168.1.102:9000. DB::Exception: No such column name1 in table db_hxl.tb_test_local02 (f2487d32-5aa8-4421-8e9b-848ea40c7221). (NO_SUCH_COLUMN_IN_TABLE)

select *会报错误
select * from tb_test_all02;
Received exception from server (version 24.8.11):
Code: 47. DB::Exception: Received from 192.168.1.102:9000. DB::Exception: Identifier '__table1.name1' cannot be resolved from table with name __table1. In scope SELECT __table1.id AS id, __table1.name AS name, __table1.timestamp AS timestamp, __table1.day AS day, __table1.name1 AS name1 FROM db_hxl.tb_test_local02 AS __table1. Maybe you meant: ['__table1.name']. (UNKNOWN_IDENTIFIER)

查具体字段不会报错:
select id,name,timestamp,day from tb_test_all02;

查新增字段会报错误
select name1 from tb_test_all02;

那么本地表也需要添加字段
ALTER TABLE tb_test_local02 ON CLUSTER default ADD COLUMN name1 String;

 

2.分布式表删除字段

ALTER TABLE tb_test_all02 ON CLUSTER default drop COLUMN name1;

查询分布式表--正常
select * from tb_test_all02; 
查询本地表--正常
select * from tb_test_local02; 

写入数据到分布表--正常
INSERT INTO tb_test_all02 (id, name) values(15,'name15');

写入本地表(带没有删除的字段)--正常
INSERT INTO tb_test_local02 (id, name,name1) values(16,'name16','name16');

写入本地表(不带删除的字段)--正常
INSERT INTO tb_test_local02 (id, name) values(17,'name17');