4.3 ClickHouse的ReplacingMergeTree表引擎特性
4.4去重合并树ReplaceMergeTree建表demo实战
4.6 聚合引擎SummingMergeTree建表demo实战
本篇重点讲解了CK常用数据类型，CK常见SQL语法，和CK应该重点关注的引擎及其Demo
一、ClickHouse常用的数据类型
常用的几种:
数值类型,整型 Int64 IntX X是位的意思，1Byte字节=8bit位
浮点型(存在精度损失问题)
mysql里面的float类型 对应ck里面的Float32
mysql里面的double类型 对应ck里面的Float64
Decimal类型
一般需要要求更高的精度的数值运算(金额、利率等)，则需要使用定点数
Clickhouse提供了Decimal32，Decimal64，Decimal128三种精度的定点数
用Decimal(P,S)来定义：
P代表精度,表示总位数(整数部分 + 小数部分)
S代表规模,表示小数位数
例子：Decimal(10,2) 小数部分2位，整数部分 8位（10-2)
字符串类型 UUID
字符串可以任意长度的。它可以包含任意的字节集，包含空字节
字符串类型可以代替其他 DBMSs中的 VARCHAR、BLOB、CLOB 等类型
CREATE TABLE t_uuid (x UUID, y String) ENGINE=TinyLog
INSERT INTO t_uuid SELECT generateUUIDv4(), 'Example 1'
时间类型 Date
日期类型，用两个字节存储，表示从 1970-01-01 (无符号) 到当前的日期值，支持字符串形式写入时间戳类型 DateTime
用四个字节（无符号的）存储 Unix 时间戳，支持字符串形式写入
时间戳类型值精确到秒
值的范围: [1970-01-01 00:00:00, 2106-02-07 06:28:15]
二、 ClickHouse常见SQL语法
参考官方中文文档 :选择查询 | ClickHouse Docs
创建表:
CREATE TABLE wnn_shop.clickstream3 (
customer_id String,
time_stamp Date,
click_event_type String,
page_code FixedString(20),
source_id UInt64,
money Decimal(2,1),
is_new Bool
)
ENGINE = MergeTree()
ORDER BY (time_stamp)查看表结构
DESCRIBE wnn_shop.clickstream3
插入和查询
更新：
更新之前，这个语句查询结果是空的:
update语句后，再执行这个语句
SELECT database, table, command, create_time, is_done FROM system.mutations LIMIT 20 用来判断语句是否执行完成 ，更新/删除
更新和删除:
在OLAP数据库中，可变数据（Mutable data）通常是不被欢迎的，早期ClickHouse是不支持，后来版本才有
不支持事务，建议批量操作，不要高频率小数据量更新删除
删除和更新是一个异步操作的过程，语句提交立刻返回，但不一定已经完成了
判断是否完成
SELECT database, table, command, create_time, is_done FROM system.mutations LIMIT 20
注意:
每次更新或者删除，会废弃目标数据的原有分区，而重建新分区
举例:
如果只更新一条数据，那么需要重建一个分区
如果更新100条数据，而这100条可能落在3个分区上，则需重建3个分区
相对来说一次更新一批数据的整体效率远高于一次更新一行
三、ClickHous分片-分区-副本分别是什么
什么是ClickHouse的分区
分区是表的分区，把一张表的数据分成N多个区块，分区后的表还是一张表，数据处理还是由自己来完成
PARTITION BY，指的是一个表按照某一列数据（比如日期）进行分区，不同分区的数据会写入不同的文件中
建表时加入partition概念，可以按照对应的分区字段，允许查询在指定了分区键的条件下，尽可能的少读取数据create table wnn_shop.order_merge_tree(
id UInt32,
sku_id String,
out_trade_no String,
total_amount Decimal(16,2),
create_time Datetime
) engine =MergeTree()
partition by toYYYYMMDD(create_time)
order by (id,sku_id)
primary key (id);
注意:
不是所有的表引擎都可以分区，合并树(MergeTree) 系列的表引擎才支持数据分区，Log系列引擎不支持
什么是ClickHouse的分片
Shard 分片是把数据库横向扩展到多个物理节点上的一种有效的方式
复用了数据库的分区概念，相当于在原有的分区下作为第二层分区，ClickHouse会将数据分为多个分片，并且分布到不同节点上，再通过 Distributed 表引擎把数据拼接起来一同使用
Sharding机制使得ClickHouse可以横向线性拓展，构建大规模分布式集群，但需要避免数据倾斜问题
什么是ClickHouse的副本
两个相同数据的表, 作 用是为了数据备份与安全，保障数据的高可用性，
即使一台 ClickHouse 节点宕机，那么也可以从其他服务器获得相同的数据
类似Mysql主从架构，主节点宕机，从节点也能提供服务
分片之间的数据是不相同的，副本之间的数据是相同的。分片主要目的是实现数据的水平切分，副本是防止数据丢失，冗余数据存储
总结：
数据分区-允许查询在指定了分区键的条件下，尽可能的少读取数据
数据分片-允许多台机器/节点同并行执行查询，实现了分布式并行计算
四、ClickHouse常见引擎和合并树引擎
ClickHouse中一个非常重要的概念—表引擎(table engine)
回想一下，mysql的表引擎, 不同的存储引擎提供不同的存储机制、索引方式、事务操作等功能
ClickHouse提供了多种的表引擎，不同的表引擎也代表有不同的功能
表引擎存在多个不同的系列：
Log系列:
最小功能的轻量级引擎，当需要快速写入许多小表并在以后整体读取它们时效果最佳，一次写入多次查询
种类:TinyLog、StripLog、Log
MergeTree系列【王炸重点】
CLickhouse最强大的表引擎，有多个不同的种类
适用于高负载任务的最通用和功能最强大的表引擎，可以快速插入数据并进行后续的后台数据处理
支持主键索引、数据分区、数据副本等功能特性和一些其他引擎不支持的其他功能
种类:MergeTree、ReplacingMergeTree、SummingMergeTree、AggregatingMergeTree
CollapsingMergeTree、VersionedCollapsingMergeTree、GraphiteMergeTree
4.1 ClickHouse的MergeTree表引擎特性
ClickHouse 中文文档:MergeTree | ClickHouse Docs 以下内容来自官方文档
中文名叫合并树，MergeTree 系列的引擎被设计用于插入极大量的数据到一张表当中。
数据可以以【数据片段】的形式一个接着一个的快速写入，数据片段在后台按照一定的规则进行合并。
相比在插入时不断修改（重写）已存储的数据，这种策略会高效很多,这种数据片段反复合并的特性，也正是合并树名称的由来
特点:
ClickHouse 不要求主键唯一，所以可以插入多条具有相同主键的行。
如果指定了【分区键】则可以使用【分区】，可以通过PARTITION 语句指定分区字段，合理使用数据分区，可以有效减少查询时数据文件的扫描范围
在相同数据集的情况下 ClickHouse 中某些带分区的操作会比普通操作更快，查询中指定了分区键时 ClickHouse 会自动截取分区数据，这也有效增加了查询性能
语法:
语法解析:
【必填】ENGINE - 引擎名和参数。 ENGINE = MergeTree(). MergeTree 引擎没有参数
【必填】ORDER BY — 排序键，可以是一组列的元组或任意的表达式
例如: ORDER BY (CounterID, EventDate) 。如果没有使用 PRIMARY KEY 显式指定的主键， ClickHouse 会使用排序键作为主键
如果不需要排序，可以使用 ORDER BY tuple()
【选填】PARTITION BY — 分区键 ，可选项
要按月分区，可以使用表达式 toYYYYMM(date_column) ，这里的 date_column 是一个 Date 类 型的列, 分区名的格式会是 "YYYYMM"
分区的好处是降低扫描范围提升速度，不填写默认就使用一个分区
【选填】PRIMARY KEY -主键，作为数据的一级索引，但是不是唯一约束，和其他数据库区分
如果要 选择与排序键不同的主键，在这里指定，可选项,
默认情况下主键跟排序键（由 ORDER BY 子句指定）相同。
大部分情况下不需要再专门指定一个 PRIMARY KEY
注意:PRIMARY KEY 主键必须是 order by 字段的前缀字段。
主键和排序字段这两个属性只设置一个时，另一个默认与它相同， 当两个都设置时，PRIMARY KEY必须为ORDER BY的前缀
比如ORDER BY (CounterID, EventDate)，那主键需要是（CounterID ）或 (CounterID, EventDate)
4.2 合并树MergeTree建表和数据demo实战
建表:
create table wnn_shop.order_merge_tree(
id UInt32,
sku_id String,
out_trade_no String,
total_amount Decimal(16,2),
create_time Datetime
) engine =MergeTree()
order by (id,sku_id)
partition by toYYYYMMDD(create_time)
primary key (id);
步骤:写入数据-->进入ck docker容器内部-->ck客户端连接-->查询分区情况
1.写入数据：
insert into wnn_shop.order_merge_tree values
(1,'sku_1','aabbcc',5600.00,'2022-07-01 16:00:00') ,
(2,'sku_2','23241',4.02,'2022-07-01 17:00:00'),
(3,'sku_3','542323',55.02,'2022-07-01 18:00:00'),
(4,'sku_1','54222',2000.3,'2022-07-01 19:00:00'),
(5,'sku_2','53423',120.2,'2022-07-01 19:00:00'),
(6,'sku_4','65432',600.01,'2022-07-02 11:00:00');2.进入docker 容器内部
docker exec -it bd3ee7404cc3 /bin/bash bd3ee7404cc3是container id
3. ck 客户端连接 clickhouse-client
4. 查询表:
实际上 合并之前的数据长这样 是按照时间进行分区的：
再进行数据的写入：重复1的动作
这时候我们再来看一下实际的分区：
原因：新的数据写入会有临时分区产生，不加入已有分区
写入完成后经过一定时间（10到15分钟），ClickHouse会自动化执行合并操作，将临时分区的数据合并到已有分区当中
optimize的合并操作是在后台执行的，无法预测具体执行时间点，除非是手动执行
通过手工合并( optimize table xxx final; )
在数据量比较大的情况，尽量不要使用该命令，执行optimize要消耗大量时间
看下表中展示的详情，可得出id列是可以重复的，同时是按照id,sku_id进行排序的。
4.3 ClickHouse的ReplacingMergeTree表引擎特性
中文文档:ReplacingMergeTree | ClickHouse Docs
MergeTree的拓展，该引擎和 MergeTree 的不同之处在它会删除【排序键值】相同重复项,根据OrderBy字段
数据的去重只会在数据合并期间进行。合并会在后台一个不确定的时间进行，因此你无法预先作出计划。
有一些数据可能仍未被处理，尽管可以调用 OPTIMIZE 语句发起计划外的合并，但请不要依靠它，因为 OPTIMIZE 语句会引发对数据的大量读写
因此，ReplacingMergeTree 适用于在后台清除重复的数据以节省空间，但是它不保证没有重复的数据出现
注意去重访问:如果是有多个分区表，只在分区内部进行去重，不会跨分区
建表语法:
参数:
ver — 版本列。类型为 UInt*, Date 或 DateTime。可选参数。
在数据合并的时候，ReplacingMergeTree 从所有具有相同排序键的行中选择一行留下：
如果 ver 列未指定，保留最后一条。
如果 ver 列已指定，保留 ver 值最大的版本
总结:
如何判断数据重复
在去除重复数据时，是以ORDER BY排序键为基准的，而不是PRIMARY KEY
若排序字段为两个，则两个字段都相同时才会去重
何时删除重复数据
在执行分区合并时触发删除重复数据，optimize的合并操作是在后台执行的，无法预测具体执行时间点，除非是手动执行
不同分区的重复数据不会被去重
ReplacingMergeTree是以分区为单位删除重复数据的，在相同的数据分区内重复的数据才会被删除，而不同数据分区之间的重复数据依然不能被删除的删除策略
ReplacingMergeTree() 填入的参数为版本字段，重复数据就会保留版本字段值最大的。
如果不填写版本字段，默认保留插入顺序的最后一条数据
4.4去重合并树ReplaceMergeTree建表demo实战
建表:
ver表示的列只能是UInt*,Date和DateTime 类型
删除策略
ReplacingMergeTree() 填入的参数为版本字段，重复数据就会保留版本字段值最大的。
如果不填写版本字段，默认保留插入顺序的最后一条数据
表语句:
create table wnn_shop.order_relace_merge_tree(
id UInt32,
sku_id String,
out_trade_no String,
total_amount Decimal(16,2),
create_time Datetime
) engine =ReplacingMergeTree(id)
order by (sku_id)
partition by toYYYYMMDD(create_time)
primary key (sku_id);插入数据:
insert into wnn_shop.order_relace_merge_tree values
(1,'sku_1','aabbcc',5600.00,'2023-03-01 16:00:00') ,
(2,'sku_2','23241',4.02,'2023-03-01 17:00:00'),
(3,'sku_3','542323',55.02,'2023-03-01 18:00:00'),
(4,'sku_5','54222',2000.3,'2023-04-01 19:00:00'),
(5,'sku_6','53423',120.2,'2023-04-01 19:00:00'),
(6,'sku_7','65432',600.01,'2023-04-02 11:00:00');
insert into wnn_shop.order_relace_merge_tree values
(11,'sku_1','aabbcc',5600.00,'2023-03-01 16:00:00') ,
(21,'sku_2','23241',4.02,'2023-03-01 17:00:00'),
(31,'sku_3','542323',55.02,'2023-03-01 18:00:00'),
(41,'sku_5','54222',2000.3,'2023-04-01 19:00:00'),
(51,'sku_8','53423',120.2,'2023-04-01 19:00:00'),
(61,'sku_9','65432',600.01,'2023-04-02 11:00:00');分别插入2条语句后，查看分区情况:
手动合并后的分区情况:
2023-3.1号分区中 重复的sku被合并，留下了id大的sku数据
2023-4.1号分区中 重复的sku_5被合并了，留下了id大的41号数据，去掉了id小的4号数据
4.5 聚合引擎SummingMergeTree特性
中文文档：SummingMergeTree | ClickHouse Docs
该引擎继承自 MergeTree，区别在于，当合并 SummingMergeTree 表的数据片段时，ClickHouse 会把所有具有 相同OrderBy排序键 的行合并为一行，该行包含了被合并的行中具有数值类型的列的汇总值。
类似group by的效果，这个可以显著的减少存储空间并加快数据查询的速度
获取汇总值，不能直接 select 对应的字段，而需要使用 sum 进行聚合，因为自动合并的部分可能没进行，会导致一些还没来得及聚合的临时明细数据少
表语法:
SummingMergeTree 的参数
columns 包含了将要被汇总的列的列名的元组。可选参数。
所选的【列必须是数值类型】，具有 相同OrderBy排序键 的行合并为一行
如果没有指定 columns，ClickHouse 会把非维度列且是【数值类型的列】都进行汇总
4.6 聚合引擎SummingMergeTree建表demo实战
建表:
create table wnn_shop.order_summing_merge_tree(
id UInt32,
sku_id String,
out_trade_no String,
total_amount Decimal(16,2),
create_time Datetime
) engine =SummingMergeTree(total_amount)
order by (id,sku_id)
partition by toYYYYMMDD(create_time)
primary key (id);插入数据:
insert into wnn_shop.order_summing_merge_tree values
(1,'sku_1','aabbcc',5600.00,'2023-03-01 16:00:00') ,
(2,'sku_2','23241',4.02,'2023-03-01 17:00:00'),
(3,'sku_3','542323',55.02,'2023-03-01 18:00:00'),
(4,'sku_5','54222',2000.3,'2023-04-01 19:00:00'),
(5,'sku_6','53423',120.2,'2023-04-01 19:00:00'),
(6,'sku_7','65432',600.01,'2023-04-02 11:00:00');
insert into wnn_shop.order_summing_merge_tree values
(1,'sku_1','aabbccbb',5600.00,'2023-03-01 23:09:00')分区合并验证:
登录容器，连接命令 clickhouse-client
写入完成后经过一定时间（10到15分钟），ClickHouse会自动化执行合并操作，
将临时分区的数据合并到已有分区当中，并进行去重
也可以通过手工合并( optimize table xxx final; )
用在后台清除重复的数据，但是它不保证没有重复的数据出现。
select * from wnn_shop.order_summing_merge_tree
手动合并:
2023-03-01 23:09:00的数据被合并到2023-03-01分区中，该条数据因排序键id,sku_id一样，所以保留最初的那一条 ，该条被丢掉。同时total_amount的值累加聚合
查询汇总数据:
不同分区的是没法做合并，所以查询的时候，要加上group by 和sum
select sku_id,sum(total_amount) from wnn_shop.order_summing_merge_tree group by sku_id
总结:
SummingMergeTree是根据什么对数据进行合并的:【ORBER BY排序键相同】作为聚合数据的条件Key的行中的列进行汇总，将这些行替换为包含汇总数据的一行记录** 跨分区内的相同排序key的数据是否会进行合并**:
以数据分区为单位来聚合数据，同一数据分区内相同ORBER BY排序键的数据会被合并汇总，而不同分区之间的数据不会被汇总如果没有指定聚合字段，会怎么聚合:没有指定聚合字段，则会用非维度列，且是数值类型字段进行聚合
对于非汇总字段的数据，该保留哪一条:
如果两行数据除了【ORBER BY排序键】相同，其他的非聚合字段不相同，在聚合时会【保留最初】的那条数据，新插入的数据对应的那个字段值会被舍弃
在合并分区的时候按照预先定义的条件聚合汇总数据，将同一分区下的【相同排序】的多行数据汇总合并成一行，既减少了数据行节省空间，又降低了后续汇总查询的开销
3207
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
