创建clickhouse 分布式表方式 及 分布式表数据增加、更新、删除、查询

最新推荐文章于 2024-07-06 23:52:56 发布
最新推荐文章于 2024-07-06 23:52:56 发布
阅读量1.7w 收藏 88
点赞数 34
分类专栏： clickhouse 列式存储 文章标签： 大数据 clickhouse
本文为博主原创文章，未经博主允许不得转载。
本文链接：https://blog.csdn.net/weixin_32265569/article/details/113796490
版权

创建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;

文章最后，给大家推荐一些受欢迎的技术博客链接：

  1. JAVA相关的深度技术博客链接
  2. Flink 相关技术博客链接
  3. Spark 核心技术链接
  4. 设计模式 —— 深度技术博客链接
  5. 机器学习 —— 深度技术博客链接
  6. Hadoop相关技术博客链接
  7. 超全干货--Flink思维导图，花了3周左右编写、校对
  8. 深入JAVA 的JVM核心原理解决线上各种故障【附案例】
  9. 请谈谈你对volatile的理解？--最近小李子与面试官的一场“硬核较量”
  10. 聊聊RPC通信，经常被问到的一道面试题。源码+笔记，包懂
  11. 深入聊聊Java 垃圾回收机制【附原理图及调优方法】

欢迎扫描下方的二维码或 搜索 公众号“大数据高级架构师”，我们会有更多、且及时的资料推送给您，欢迎多多交流！

                                           

       

不埋雷的探长
关注
  • 34
    点赞
  • 踩
  • 88
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 8
    评论
专栏目录
ClickHouse分布式表Distributed实操
探索大数据世界 - 深入解析数据存储、分布式计算与人工智能
07-17 3320
ClickHouse分布式表Distributed实操
ClickHouse创建分布式表
m0_74823264的博客
10-10 509
Clickhouse采用了特殊的引擎设计结构和各种方案保证其查询和存储的高效。分布式的创建和使用还有很多的细节等待去深挖。
ClickHouse 分布式部署、分布式表创建及数据迁移指南
ycloud
05-29 2038
Quantum Insights 的部署将基于一个高可用的分布式 ClickHouse 集群，以实现对大规模数据的高效处理和查询。
ClickHouse 数据插入、更新与删除操作 SQL
m0_67391907的博客
07-31 1830
数据操作语言DML，包括SQL数据更改语句，它修改存储的数据，但不修改数据模型，例如数据库模式或数据库表结构。DML语言常见的语法模式如下。
[AIGC] ClickHouse分布式表与本地表的区别及如何查询所有本地表记录
最新发布
程序员三木的博客
07-06 1125
在大规模数据处理和分析场景中，ClickHouse是一种高性能的列式数据库管理系统。ClickHouse支持分布式表和本地表两种表类型，本文将介绍这两种表类型的区别，并探讨如何建表以查询所有本地表的记录。
Flink 相关的深度技术博客链接
认知 行动 坚持
08-24 1405
Flink相关的深度技术博客链接 超全干货--Flink思维导图，花了3周左右编写、校对 大解密：Flink恰巧语义一次消费，怎么保证 Flink 容错机制 —— CheckPoint【含示例源码】 文章最后，给大家推荐一些受欢迎的技术博客链接： Flinak 相关技术博客链接 Hadoop相关技术博客链接 Spark核心技术链接 JAVA相关的深度技术博客链接 超全干货--Flink思维导图，花了3周左右编写、校对 深入JAVA 的JVM核心原理解决线上各种故障【附案例】 请...
为新登录分配了特殊权限。_船岸一线 | 甲板轮机携手为厨房管道“清肠”侧记——记“新武汉”轮一次特殊的支部活动...
weixin_28713947的博客
01-11 291
12月22日，在“新武汉”轮上召开了一次特殊的支部活动，甲板轮机党员干部科学决策、联手协作，将严重堵塞的下水道疏通，为厨房解决了后顾之忧。谈心谈话现端倪近日里，“新武汉”轮多名船员兄弟发现以往总是乐呵呵地大厨“大老刘”话少了，经常气喘吁吁地端着一大盆洗完的菜从楼下走来。经过多次询问，这名老党员才不好意思地告诉大家：“厨房的下水道不太好使，每次用水都会溢出来，为了不弄脏地面，我只能去楼下用...
Clickhouse创建数据库和创建表、增加表列
工作中学习，学习中进步
05-19 5447
创建表 # CREATE DATABASE [IF NOT EXISTS] database_name; CREATE DATABASE qx_tj; 创建表 # CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] ( name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [compression_codec] [TTL expr1], name2 [type2] [D
clickhouse 增加表属性
weixin_44049709的博客
04-26 1241
clickhouse 增加表属性 ALTER TABLE [db].[table] ADD COLUMN [属性] [属性类型] 加上 on CLUSTER [集群名称] 分布式表和本地表都执行一下
ClickHouse 简单介绍包括分布式表，子表，ttl，主键
flyingbird
05-23 2278
介绍 ClickHouse是一款由俄罗斯IT人员开发的一款开源的列式数据库管理系统。clickehouse是一款OLAP分析型数据库，以快取胜。至于为什么快，网络上各位大牛已经做了介绍，此处不再叙述。本文主要从使用角度来介绍关于CH的分布式表，ttl，主键等信息。 分布式表 分布式表是一种逻辑上的表，并不真正存储数据。查询时，请求直接下发分布式表，分布式表会下发到各个子表进行查询，之后结果进行汇总、合并，极大的加快了检索效率。它的创建和普通字表创建语法相似，主要是引擎类型不同。语法具体如下：
【踩坑日常】解决 ClickHouse 分布式表重建引发的元数据异常问题Existing table metadata in ZooKeeper differs in primary key.
Abner_G的博客
01-29 991
在线上分布式 ClickHouse 数据表结构需要调整，这要求删除现有的分布式表，并重新创建（DDL 发生变化）。然而，删除表后重新创建时可能会出现异常。
ClickHouse集群环境下的分布式建库建表，基本数据操作数据，自定义文件格式数据导入
whiteBearClimb的博客
12-11 6261
万丈高楼平地起，经过前几篇的学习折磨，默认都是已经搭建起ClickHouse集群环境的了，现在这篇文件是简单介绍基本使用。 注意ClickHouse的分布式，也摆脱不了CAP的束缚，所以数据写入都是异步的，做数据同步都是先写入本地磁盘，再同步到其他分片区，yum安装方式的可以在/var/lib/clickhouse/data/database/table/查看ClickHouse的数据是否同步完成。 （1）分布式建库： 其实也就是在集群中建库，让所有节点都同步创建：create database jojo_
ClickHouse 新增字段
文文x的博客
12-08 973
【代码】ClickHouse 新增。
Clickhouse中对表的基本操作（增加、删除、修改列等）
moose_killer的博客
04-08 8620
1.CK中添加列：ALTER TABLE 表名称 ON 集群名称 ADD COLUMN 列名称 类型 DEFAULT 注解 eg：ALTER TABLE 表名称 ON 集群名称 ADD COLUMN lvl Int32 DEFAULT 'lvl'2.CK中修改注释：alter table 表名称 ON 集群名称 COMMENT COLUMN 列名称 注解; alter table adm_sch_d05_content_live_service_di_local ON 集群名称 COMMENT COLUMN
clickhouse分布式表
08-24
ClickHouse是一个开源的列式数据库管理系统，支持高性能的分布式数据处理和分析。在ClickHouse中，分布式表是数据在多个节点上分片存储和处理的一种方式。 创建一个分布式表可以通过在ClickHouse中定义表的引擎类型为"Distributed"来实现。分布式表由多个本地表（Local Table）组成，每个本地表存储数据的一个子集。 以下是创建一个ClickHouse分布式表的示例： ```sql CREATE TABLE my_distributed_table ( id UInt32, name String ) ENGINE = Distributed('my_cluster', 'default', 'my_table', rand()); -- 'my_cluster'是ClickHouse集群中的逻辑集群名称 -- 'default'是本地表名称的前缀，在本例中为'default'加上分片索引 -- 'my_table'是本地表的名称，可以与分布式表的名称相同 -- 'rand()'定义了如何将数据分片存储在不同的节点上，这里使用随机函数进行分片 -- 创建本地表 CREATE TABLE default.my_table ON CLUSTER my_cluster ( id UInt32, name String ) ENGINE = MergeTree() ORDER BY id; ``` 在上述示例中，通过在`ENGINE`参数中指定为`Distributed`，并提供逻辑集群名称、本地表名称前缀和本地表名称来创建了一个分布式表。然后使用`CREATE TABLE`语句创建了本地表，并指定了该本地表的存储引擎为`MergeTree`。 分布式表的查询和操作与普通表类似，但数据将自动在集群中的不同节点上进行分布式处理和存储。
评论 8
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

不埋雷的探长

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值