创建clickhouse 分布式表方式 及分布式表数据增加、更新、删除、查询
1、建表语句
(1)、本地表:分别在每个节点都建立本地表,或者使用 on cluster【在其中一个节点执行即可】
CREATE TABLE city_local on cluster ck_cluster_name (
`fdate` Int64,
`city_code` Int32,
`city_name` String,
`total_cnt` Int64
) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/city_local', '{replica}')
PARTITION BY fdate
ORDER BY (fdate, city_code, city_name)
SETTINGS index_granularity = 8192, storage_policy = 'ssd_to_hdd';
说明:{} 里边表示配置文件对应的变量
(2)、分布式表,本身不存储数据,详细了解请阅读官方文档。在其中一个节点执行即可。
分布式表一般加_all,第一个参数是集群名称,第二个参数是数据库名称,第三个参数是对应的本地表,第四个参数是随机分布数据。
-- 推荐使用方式一,因为这样只需要重新创建一个all与对应local表的映射关系即可
-- 方式一:
-- 示例1:
CREATE TABLE IF NOT EXISTS test_db.city_all ON CLUSTER ck_cluster_name AS test_db.city_local
ENGINE = Distributed(ck_cluster_name, test_db, city_local, fdate);
-- 示例2:
CREATE TABLE IF NOT EXISTS test_db.city_all ON CLUSTER ck_cluster_name AS test_db.city_local
ENGINE = Distributed(ck_cluster_name, test_db, city_local, rand());
-- 方式二:
CREATE TABLE city_all on cluster ck_cluster_name (
`fdate` Int64,
`city_code` Int32,
`city_name` String,
`total_cnt` Int64
) ENGINE = Distributed(ck_cluster_name, test_db, city_local, rand())
2、分布式表插入数据
insert into city_all (fdate, city_code, city_name, total_cnt) values (20210131, 4000, 'guangzhou', 420000);
insert into city_all (fdate, city_code, city_name, total_cnt) values (20210131, 5000, 'shenzhan', 55000);
insert into city_all (fdate, city_code, city_name, total_cnt) values (20210131, 6000, 'huizhou', 65000);
insert into city_all (fdate, city_code, city_name, total_cnt) values (20210131, 7000, 'huizhou', 75000);
3、分布式表查询数据
-- 插入数据后,查询数据
select * from city_all;
┌────fdate─┬─city_code─┬─city_name─┬─total_cnt─┐
│ 20210131 │ 5000 │ shenzhan │ 55000 │
└──────────┴───────────┴───────────┴───────────┘
┌────fdate─┬─city_code─┬─city_name─┬─total_cnt─┐
│ 20210131 │ 7000 │ huizhou │ 75000 │
└──────────┴───────────┴───────────┴───────────┘
┌────fdate─┬─city_code─┬─city_name─┬─total_cnt─┐
│ 20210131 │ 6000 │ huizhou │ 65000 │
└──────────┴───────────┴───────────┴───────────┘
┌────fdate─┬─city_code─┬─city_name─┬─total_cnt─┐
│ 20210131 │ 4000 │ guangzhou │ 420000 │
└──────────┴───────────┴───────────┴───────────┘
4、分布式表更新数据
-- 更新数据【生效】,通过local表来更新 同时指定上集群名称;如果通过all来更新则不支持会报错
ALTER TABLE city_local ON CLUSTER ck_cluster_name UPDATE total_cnt = 2222 WHERE city_name = 'huizhou';
-- 通过all来更新会报错,An error occurred before execution: Code: 371, e.displayText() = DB::Exception: Table 'city_all' isn't replicated, but shard #3 is replicated according to its cluster definition (version 20.2.1.2183 (official build))
-- ALTER TABLE city_all ON CLUSTER ck_cluster_name UPDATE total_cnt = 3333 WHERE city_name = 'huizhou';
SELECT * FROM city_all;
┌────fdate─┬─city_code─┬─city_name─┬─total_cnt─┐
│ 20210131 │ 4000 │ guangzhou │ 420000 │
└──────────┴───────────┴───────────┴───────────┘
┌────fdate─┬─city_code─┬─city_name─┬─total_cnt─┐
│ 20210131 │ 6000 │ huizhou │ 2222 │
└──────────┴───────────┴───────────┴───────────┘
┌────fdate─┬─city_code─┬─city_name─┬─total_cnt─┐
│ 20210131 │ 7000 │ huizhou │ 2222 │
└──────────┴───────────┴───────────┴───────────┘
┌────fdate─┬─city_code─┬─city_name─┬─total_cnt─┐
│ 20210131 │ 5000 │ shenzhan │ 55000 │
└──────────┴───────────┴───────────┴───────────┘
5、分布式表删除数据
-- 删除数据【生效】,通过local表来删除 同时指定上集群名称;如果通过all来删除则不支持会报错
ALTER TABLE city_local ON CLUSTER ck_cluster_name DELETE WHERE city_name = 'huizhou';
-- An error occurred before execution: Code: 371, e.displayText() = DB::Exception: Table 'city_all' isn't replicated, but shard #3 is replicated according to its cluster definition (version 20.2.1.2183 (official build))
-- ALTER TABLE city_all ON CLUSTER ck_cluster_name DELETE WHERE city_name = 'huizhou';
SELECT * FROM city_all;
┌────fdate─┬─city_code─┬─city_name─┬─total_cnt─┐
│ 20210131 │ 5000 │ shenzhan │ 55000 │
└──────────┴───────────┴───────────┴───────────┘
┌────fdate─┬─city_code─┬─city_name─┬─total_cnt─┐
│ 20210131 │ 4000 │ guangzhou │ 420000 │
└──────────┴───────────┴───────────┴───────────┘
-- 插入数据后,查询数据
SELECT * FROM city_all
┌────fdate─┬─city_code─┬─city_name─┬─total_cnt─┐
│ 20210131 │ 5000 │ shenzhan │ 55000 │
└──────────┴───────────┴───────────┴───────────┘
┌────fdate─┬─city_code─┬─city_name─┬─total_cnt─┐
│ 20210131 │ 4000 │ guangzhou │ 420000 │
└──────────┴───────────┴───────────┴───────────┘
6、分布式表添加字段
-- 添加历史字段
-- mysql 添加字段
-- ALTER TABLE test_table add age int DEFAULT NULL COMMENT '年龄' after name;
-- 生效
alter table city_local ON CLUSTER ck_cluster_name add column history Int32;
-- 同时支持指定在某个字段后面添加字段
alter table city_local ON CLUSTER ck_cluster_name add column history Int32 after city_code;
-- 不生效,报错:An error occurred before execution: Code: 371, e.displayText() = DB::Exception: Table 'city_all' isn't replicated,
-- but shard #3 is replicated according to its cluster definition (version 20.2.1.2183 (official build)).
alter table city_all ON CLUSTER ck_cluster_name add column history Int32 COMMENT '字段描述';
-- 尝试将city_all 删除重建
drop table test_db.city_all ON CLUSTER ck_cluster_name;
-- 重建all表,方式一:【可行】
CREATE TABLE IF NOT EXISTS test_db.city_all ON CLUSTER ck_cluster_name AS test_db.city_local
ENGINE = Distributed(ck_cluster, test_db, city_local, rand());
┌────fdate─┬─city_code─┬─city_name─┬─total_cnt─┬─history─┐
│ 20210131 │ 5000 │ shenzhan │ 55000 │ 0 │
└──────────┴───────────┴───────────┴───────────┴─────────┘
┌────fdate─┬─city_code─┬─city_name─┬─total_cnt─┬─history─┐
│ 20210131 │ 4000 │ guangzhou │ 420000 │ 0 │
└──────────┴───────────┴───────────┴───────────┴─────────┘
-- 重建all表,方式二:【可行】
CREATE TABLE city_all on cluster ck_cluster_name (
`fdate` Int64,
`city_code` Int32,
`city_name` String,
`total_cnt` Int64,
`history` Int32
) ENGINE = Distributed(ck_cluster_name, test_db, city_local, rand())
┌────fdate─┬─city_code─┬─city_name─┬─total_cnt─┬─history─┐
│ 20210131 │ 5000 │ shenzhan │ 55000 │ 0 │
└──────────┴───────────┴───────────┴───────────┴─────────┘
┌────fdate─┬─city_code─┬─city_name─┬─total_cnt─┬─history─┐
│ 20210131 │ 4000 │ guangzhou │ 420000 │ 0 │
└──────────┴───────────┴───────────┴───────────┴─────────┘
-- 查询表的创建语句
show create table city_local;
show create table city_all;
-- 新增字段后,查询数据
select * from city_all;
7、分布式表新增字段后,插入数据验证
-- 新增字段后,插入数据,并不对新字段赋值,验证可行。新增history字段默认为0
insert into city_all (fdate, city_code, city_name, total_cnt) values (20210131, 7000, 'foshan', 75000);
┌────fdate─┬─city_code─┬─city_name─┬─total_cnt─┬─history─┐
│ 20210131 │ 7000 │ foshan │ 75000 │ 0 │
└──────────┴───────────┴───────────┴───────────┴─────────┘
┌────fdate─┬─city_code─┬─city_name─┬─total_cnt─┬─history─┐
│ 20210131 │ 4000 │ guangzhou │ 420000 │ 0 │
└──────────┴───────────┴───────────┴───────────┴─────────┘
┌────fdate─┬─city_code─┬─city_name─┬─total_cnt─┬─history─┐
│ 20210131 │ 5000 │ shenzhan │ 55000 │ 0 │
└──────────┴───────────┴───────────┴───────────┴─────────┘
-- 新增字段后,插入数据,对新字段赋值
insert into city_all (fdate, city_code, city_name, total_cnt, history) values (20210131, 7000, 'dongguan', 85000, 2021);
-- 新增字段后,查询数据
select * from city_all;
┌────fdate─┬─city_code─┬─city_name─┬─total_cnt─┬─history─┐
│ 20210131 │ 4000 │ guangzhou │ 420000 │ 0 │
└──────────┴───────────┴───────────┴───────────┴─────────┘
┌────fdate─┬─city_code─┬─city_name─┬─total_cnt─┬─history─┐
│ 20210131 │ 7000 │ dongguan │ 85000 │ 2021 │
└──────────┴───────────┴───────────┴───────────┴─────────┘
┌────fdate─┬─city_code─┬─city_name─┬─total_cnt─┬─history─┐
│ 20210131 │ 5000 │ shenzhan │ 55000 │ 0 │
└──────────┴───────────┴───────────┴───────────┴─────────┘
┌────fdate─┬─city_code─┬─city_name─┬─total_cnt─┬─history─┐
│ 20210131 │ 7000 │ foshan │ 75000 │ 0 │
└──────────┴───────────┴───────────┴───────────┴─────────┘
-- 对分布式all表更新操作【不生效】An error occurred before execution: Code: 371, e.displayText() = DB::Exception: Table 'city_all' isn't replicated, but shard #1 is replicated according to its cluster definition (version 20.2.1.2183 (official build))
ALTER TABLE city_all ON CLUSTER ck_cluster_name UPDATE total_cnt = 3333 WHERE city_name = 'foshan';
-- 对分布式all表删除数据【不生效】An error occurred before execution: Code: 371, e.displayText() = DB::Exception: Table 'city_all' isn't replicated, but shard #1 is replicated according to its cluster definition (version 20.2.1.2183 (official build))
ALTER TABLE city_all ON CLUSTER ck_cluster_name DELETE WHERE city_name = 'foshan'
┌────fdate─┬─city_code─┬─city_name─┬─total_cnt─┬─history─┐
│ 20210131 │ 5000 │ shenzhan │ 55000 │ 0 │
└──────────┴───────────┴───────────┴───────────┴─────────┘
┌────fdate─┬─city_code─┬─city_name─┬─total_cnt─┬─history─┐
│ 20210131 │ 7000 │ dongguan │ 85000 │ 2021 │
└──────────┴───────────┴───────────┴───────────┴─────────┘
┌────fdate─┬─city_code─┬─city_name─┬─total_cnt─┬─history─┐
│ 20210131 │ 7000 │ foshan │ 75000 │ 0 │
└──────────┴───────────┴───────────┴───────────┴─────────┘
┌────fdate─┬─city_code─┬─city_name─┬─total_cnt─┬─history─┐
│ 20210131 │ 4000 │ guangzhou │ 420000 │ 0 │
└──────────┴───────────┴───────────┴───────────┴─────────┘
8 删除字段
-- 删除字段【可行】
alter table test_db.city_local ON CLUSTER ck_cluster_name drop column history;
9 删除分布式表【生效】
drop table city_local on cluster ck_cluster_name;
drop table city_all on cluster ck_cluster_name;
文章最后,给大家推荐一些受欢迎的技术博客链接:
- JAVA相关的深度技术博客链接
- Flink 相关技术博客链接
- Spark 核心技术链接
- 设计模式 —— 深度技术博客链接
- 机器学习 —— 深度技术博客链接
- Hadoop相关技术博客链接
- 超全干货--Flink思维导图,花了3周左右编写、校对
- 深入JAVA 的JVM核心原理解决线上各种故障【附案例】
- 请谈谈你对volatile的理解?--最近小李子与面试官的一场“硬核较量”
- 聊聊RPC通信,经常被问到的一道面试题。源码+笔记,包懂
- 深入聊聊Java 垃圾回收机制【附原理图及调优方法】
欢迎扫描下方的二维码或 搜索 公众号“大数据高级架构师”,我们会有更多、且及时的资料推送给您,欢迎多多交流!