ClickHouse常见SQL语法和常见合并数引擎Demo(二)

是小王同学啊~

已于 2022-11-12 11:35:26 修改

阅读量3k

点赞数 3

分类专栏： ClickHouse 文章标签： java sql mybatis 数据库

于 2022-08-12 10:51:37 首次发布

本文链接：https://blog.csdn.net/wnn654321/article/details/125920177

版权

ClickHouse 专栏收录该内容

5 篇文章 3 订阅

订阅专栏

一、ClickHouse常用的数据类型

二、 ClickHouse常见SQL语法

三、ClickHous分片-分区-副本分别是什么

四、ClickHouse常见引擎和合并树引擎

4.1 ClickHouse的MergeTree表引擎特性

4.2 合并树MergeTree建表和数据demo实战

4.3 ClickHouse的ReplacingMergeTree表引擎特性

4.4去重合并树ReplaceMergeTree建表demo实战

4.5 聚合引擎SummingMergeTree特性

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排序键】相同，其他的非聚合字段不相同，在聚合时会【保留最初】的那条数据，新插入的数据对应的那个字段值会被舍弃
在合并分区的时候按照预先定义的条件聚合汇总数据，将同一分区下的【相同排序】的多行数据汇总合并成一行，既减少了数据行节省空间，又降低了后续汇总查询的开销