ClickHouse从入门到放弃
配置
安装
以Centos7系统为例，首先添加官方存储库
sudo yum install -y yum-utils
sudo yum-config-manager --add-repo https://packages.clickhouse.com/rpm/clickhouse.repo
sudo yum install -y clickhouse-server clickhouse-client
设置用户名和密码
生成密文和明文密码
PASSWORD=$(base64 < /dev/urandom | head -c8); echo "$PASSWORD";
echo -n "$PASSWORD" | sha256sum | tr -d '-'
这样可以得到两行数据，第一行是密码明文，第二行是密码密文
然后编辑users.xml
vim /etc/clickhouse-server/users.xml
将password改成以下内容，将密文填进去
<password_sha256_hex>密码密文</password_sha256_hex>
再找到 config.xml，允许所有人访问
<listen_host>0.0.0.0</listen_host>
启动
启动服务，日志文件在/var/log/clickhouse-server/目录下
sudo /etc/init.d/clickhouse-server start
这个时候本地连接需要指定端口 127.0.0.1
sudo clickhouse-client --host='127.0.0.1' --port='9000' --user='default' --password='明文密码'
客户端连接命令如下,参数如下:
- --host 主机
- --port 端口
- --user 用户名
- -- password 密码
clickhouse-client
数据类型
整形
包括有符号整形和无符号整形
有符号代表有正数有负数
无符号代表只有正数
这里的int8 代表 8个bit ，1byte=8bit
| ck类型 | 范围 | 符号 | 对应java数据类型 |
|---|---|---|---|
| int8 | -128 ~ 127 | 有符号 | byte |
| int16 | -32768 ~32767 | 有符号 | short |
| int32 | -2147483648 ~ 2147483647 | 有符号 | int |
| int64 | -9223372036854775808 ~ 9223372036854775807 | 有符号 | long |
| Uint8 | 0 ~ 255 | 无符号 | |
| Uint16 | 0 ~ 65535 | 无符号 | |
| Uint32 | 0 ~ 4294967295 | 无符号 | |
| Uint64 | 0 ~ 18446744073709551615 | 无符号 |
浮点型
java中 float占4个字节,Float32是32个bit，也就是4字节
| ck类型 | 对应java数据类型 |
|---|---|
| Float32 | float |
| Float64 | double |
布尔型
没有单独的类型来存储布尔值，可以使用Uint8类型，取值限制为0和1
Decimal型
使用场景：一般金额字段、汇率、利率等为了保证小数点精度使用Decimal型存储
有三种声明
- Decimal32(s)相当于Decimal(9-s,s),有效位数为1~9
- Decimal64(s)相当于Decimal(18-s,s),有效位数为1~18
- Decimal128(s)相当于Decimal(38-s,s),有效位数为1~38
举例:假设小数为123.123123123，使用Decimal32(5)==>整数+小数一共9位，小数部分有5位,也就是 123.12312
时间类型
三种类型
- Date 接收格式 yyyy-MM-dd 比如 '2023-07-20'
- Datetime接收格式 yyyy-MM-dd HH:mm:ss 比如 '2023-07-20 12:00:00'
- Datetime64接收格式 yyyy-MM-dd HH:mm:ss.SSS 比如 '2023-07-20 12:00:00.123'
数组
Array(T):由T类型元素组成的数组，不能在MergeTree引擎中使用数组
创建方法:
使用array函数创建
select array(1, 2) as x, toTypeName(x);
使用方括号
select [1, 2] as x, toTypeName(x);
表引擎
MergeTree
ClickHouse中最强大的表引擎当属MergeTree（合并树）引擎及该系列（*MergeTree）中的其他引擎，支持索引和分区，地位可以相当于innodb之于Mysql
建表语句
create table t_order_mt(
id UInt32,
sku_id String,
total_amount Decimal(16,2),
create_time Datetime
) engine = MergeTree
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,sku_id);
插入数据
insert into t_order_mt values
(101,'sku_001',1000.00,'2020-06-01 12:00:00') ,
(102,'sku_002',2000.00,'2020-06-01 11:00:00'),
(102,'sku_004',2500.00,'2020-06-01 12:00:00'),
(102,'sku_002',2000.00,'2020-06-01 13:00:00'),
(102,'sku_002',12000.00,'2020-06-01 13:00:00'),
(102,'sku_002',600.00,'2020-06-02 12:00:00');
参数解释
primary key 代表主键可以重复，可以看到下图中存在主键重复的情况
partition by代表分区，我们是按照create_time进行分区的，可以看到上图中分成两块
order by 代表排序，我们是按照id和sku_id进行排序，也就是说如果id相同则按照sku_id进行排序规则。注:只有同分区进行排序
primary key分区(可选)
分区的目的主要是降低扫描的范围，优化查询速度，如果不填，只会使用一个分区。
我们可以解析一下clickhouse的目录结构，默认路径/var/lib/clickhouse
metadata
表结构信息路径，进入目录会发现有表示库名的文件
随便进入一个目录查看，会发现有表的sql文件
查看sql内容,发现就是我们建表的sql语句
data
数据信息路径，进入目录后发现同样有代表库名和表名的文件目录，然后随机进入一个表目录的文件夹
20200601_1_1_0 和 20200602_2_2_0 分别代表分区内容，我们是按照create_time进行分区数据中存在两个时间 20200601 和 20200602 ，即存在两个分区。分区目录命名格式：PartitionId_MinBlockNum_MaxBlockNum_Level，分表代表分区值、最小分区块编号、最大分区块编号、合并层级
挑选一个分区目录进入，可以看到以下目录结构
data.bin： 存放数据的文件
data.mrk3： 标记文件，标记文件在idx索引文件和bin数据文件之间起到了桥梁作用
count.txt： 共有多少条数据，在执行count(*) 语句的时候会返回当前txt内容，ck会在插入语句的时候计算数据量,此count.txt展示的是当前分区的数量
default_compression_codec.txt： 默认压缩格式
columns.txt：列的信息
primary.idx：主键的索引文件
checksums.txt：恢复检查文件
数据写入与分区合并
任何一个批次的数据写入都会产生一个临时分区，不会纳入任何一个已有的分区。写入后的某个时刻（大概10-15分钟后），ClickHouse会自动执行合并操作（等不及也可以手动通过optimize执行），把临时分区的数据，合并到已有分区中
optimize table xxxx final;
执行插入操作
insert into t_order_mt values
(101,'sku_001',1000.00,'2020-06-01 12:00:00'),
(102,'sku_002',2000.00,'2020-06-01 11:00:00'),
(102,'sku_004',2500.00,'2020-06-01 12:00:00'),
(102,'sku_002',2000.00,'2020-06-01 13:00:00'),
(102,'sku_002',12000.00,'2020-06-01 13:00:00'),
(102,'sku_002',600.00,'2020-06-02 12:00:00');
查看数据并没有纳入任何分区
查看分区目录文件,新增了两个分区文件
执行合并分区操作
optimize table t_order_mt final;
再次查看分区已经被合并了
查看分区文件，多了两个分区合并后的文件
分区值为 20200601,最小分区编号为1最大为3，合并一次，所以第一个生成的文件名为 20200601_1_3_1,在真正合并的时候，只保留合并后的文件，其他的会被清除
primary key主键（可选）
ClickHouse中的主键，和其他数据库不太一样，它只提供了数据的一级索引，但是却不是唯一约束。这就意味着是可以存在相同primary key的数据
主键用到的索引是稀疏索引，稀疏索引中两个相邻索引对应数据的间隔默认为8192,官方不建议修改这个值，除非该列存在大量重复值，比如在一个分区中几万行才有一个不同数据。
稀疏索引采用类似于二分查找的思想
order by（必须）
order by设定了分区内的数据按照哪些字段顺序进行有序保存
order by是MergeTree中唯一一个必填项，甚至比primary key还重要，因为当用户不设置主键的情况，很多处理会依照order by的字段进行处理
要求：主键必须是order by字段的前缀字段
比如order by字段是(id,sku_id)，那么主键必须是id或者(id,sku_id)
注：当order by未被设置，那么主键的稀疏索引没有任何意义
二级索引
目前在ClickHouse的官网上二级索引的功能在v20.1.2.4之前是被标注为实验性的，在这个版本之后默认是开启的
在20.1.2.4版本之前要使用二级索引需要增加设置
set allow_experimental_data_skipping_indices=1;
二级索引建表语句
create table t_order_mt2(
id UInt32,
sku_id String,
total_amount Decimal(16,2),
create_time Datetime,
INDEX a total_amount TYPE minmax GRANULARITY 5
) engine =MergeTree
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id, sku_id);
INDEX a total_amount TYPE minmax GRANULARITY 5拆分语法就是:
- a代表索引名称
- minmax 代表索引类型
- GRANULARITY 代表索引粒度
索引粒度是建立在一级索引的基础上，如下图，【1,8193】算是一个，以下就是代表粒度五
数据TTL
MergeTree提供了可以管理数据表或者列的生命周期的功能
注:不能过期主键列
列级别的TTL-建表时指定
过期时间可以设置秒(SECOND)、天(DAY)、小时(HOUR)、周(WEEK)、月(MONTH)
建表时指定过期时间，以create_time的时间加10秒将total_amount列的数据清空。
create table t_order_mt3(
id UInt32,
sku_id String,
total_amount Decimal(16,2) TTL create_time+interval 10 SECOND,
create_time Datetime
) engine =MergeTree
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id, sku_id);
执行查询发现 total_amount列中的数据被清空
列级别的TTL-已经存在的表
ALTER TABLE t_order_mt3 MODIFY COLUMN total_amount Decimal(16,2) TTL create_time + INTERVAL 10 SECOND;
表级别的TLL-建表时指定
参数详解:
- DELETE 删除过期数据(默认方式)
- TO DISK 'aaa' 移动过期数据到磁盘 aaa
- TO VOLUME 'bbb' 移动过期数据到磁盘bbb
- GROUP BY 聚合过期行
create table t_order_mt3(
id UInt32,
sku_id String,
total_amount Decimal(16,2),
create_time Datetime
) engine =MergeTree
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id, sku_id)
TTL create_time+interval 10 SECOND DELETE;
插入数据
insert into t_order_mt3 values
(106,'sku_001',1000.00,'2023-08-28 17:01:00'),
(107,'sku_002',2000.00,'2023-08-28 17:01:00'),
(110,'sku_003',600.00,'2023-08-28 17:01:00');
表级别的TTL-已经存在的表
ALTER TABLE t_order_mt3 MODIFY TTL create_time + INTERVAL 10 SECOND;
ReplacingMergeTree
ReplacingMergeTree是MergeTree的一个变种，它存储特性完全继承MergeTree，只是多了一个去重的功能
建表语句
create table t_order_rmt(
id UInt32,
sku_id String,
total_amount Decimal(16,2) ,
create_time Datetime
) engine =ReplacingMergeTree(create_time)
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id, sku_id);
注：ReplacingMergeTree()填入的参数为版本字段，重复数据保留版本字段值最大的。如果不填版本字段，默认按照插入顺序保留最后一条
插入数据
insert into t_order_rmt values
(101,'sku_001',1000.00,'2020-06-01 12:00:00'),
(102,'sku_002',2000.00,'2020-06-01 11:00:00'),
(102,'sku_004',2500.00,'2020-06-01 12:00:00'),
(102,'sku_002',2000.00,'2020-06-01 13:00:00'),
(102,'sku_002',12000.00,'2020-06-01 13:00:00'),
(102,'sku_002',600.00,'2020-06-02 12:00:00');
可以看到帮我们做了去重
分析得出结论
- 去重只在同分区之间进行
- 实际上是使用order by字段作为唯一键进行去重
- 重复的数据保留取版本字段值最大的
- 如果版本字段相同则按插入顺序保留最后一条
SummingMergeTree
对于不查询明细，只关心以维度进行汇总聚合结果的场景。如果只使用普通的MergeTree的话，无论是存储空间的开销，还是查询时临时聚合的开销都比较大
ClickHouse为了这种场景，提供了一种能够预聚合的引擎SummingMergeTree
建表语句
create table t_order_smt(
id UInt32,
sku_id String,
total_amount Decimal(16,2) ,
create_time Datetime
) engine =SummingMergeTree(total_amount)
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,sku_id );
插入数据
insert into t_order_smt values
(101,'sku_001',1000.00,'2020-06-01 12:00:00'),
(102,'sku_002',2000.00,'2020-06-01 11:00:00'),
(102,'sku_004',2500.00,'2020-06-01 12:00:00'),
(102,'sku_002',2000.00,'2020-06-01 13:00:00'),
(102,'sku_002',12000.00,'2020-06-01 13:00:00'),
(102,'sku_002',600.00,'2020-06-02 12:00:00');
查询
得出结论
- 以
SummingMergeTree()中指定的列作为汇总数据列 - 可以填写多列必须数字列，如果不填，以所有非维度列（除了order by的列之外）且为数字列的字段为汇总数据列
- 以order by的列为准，作为维度列
- 其他的列按插入顺序保留第一行
- 不在一个分区的数据不会被聚合
- 只有在同一批次插入（新版本）或分片合并时才会进行聚合
SQL操作
sql操作基本上与MySQL一致，下面说一下不一致的地方
更新和删除
ClickHouse提供了Delete和Update的能力，这类操作被称为Mutation查询，它可以看做Alter的一种
虽然可以实现修改和删除，但是和一般的OLTP数据库不一样，Mutation语句是一种很重的操作，而且不支持事务
重的原因主要是每次修改或者删除都会导致放弃目标数据的原有分区，重建新分区
所以尽量做批量的变更，不要进行频繁小数据的操作
删除操作
alter table t_order_smt delete where sku_id ='sku_001';
查询一下表是否删除
再看一下数据目录文件，发现生成了一个新的文件用来记录修改的数据，在下一次合并分区时，清空原来的数据
修改操作
alter table t_order_smt update total_amount=toDecimal32(2000.00,2) where id=102;
查询一下表是否更新
再看数据目录发现文件发生了变化
由于操作比较重，所以Mutation语句分两步执行，同步执行的部分其实只是进行新增数据、新增分区和并把旧分区打上逻辑上的失效标记。直到触发分区合并的时候，才会删除旧数据释放磁盘空间，一般不会开放这样的功能给用户，由管理员完成
多维分析函数
clickhouse中分为 rollup（上卷）、cube（多维分析）、total（总计）三个函数。
假设维度是 a,b
rollup
-- 按a和b的组合分组
SELECT a, b, SUM(some_column) as sum_column
FROM your_table
GROUP BY a, b
UNION ALL
-- 只按a分组
SELECT a, NULL as b, SUM(some_column) as sum_column
FROM your_table
GROUP BY a;
-- 总汇总
UNION ALL
SELECT NULL as a, NULL as b, SUM(some_column) as sum_column FROM your_table;
ck语法中使用
# 清空表中的数据
alter table t_order_mt delete where 1=1;
# 插入数据
cube
-- 按a和b的组合分组
SELECT a, b, SUM(some_column) as sum_column
FROM your_table
GROUP BY a, b
UNION ALL
-- 只按a分组
SELECT a, NULL as b, SUM(some_column) as sum_column
FROM your_table
GROUP BY a
UNION ALL
-- 只按b分组
SELECT NULL as a, b, SUM(some_column) as sum_column
FROM your_table
GROUP BY b
UNION ALL
-- 总汇总
SELECT NULL as a, NULL as b, SUM(some_column) as sum_column
FROM your_table;
total
-- 按a和b的组合分组
SELECT a, b, SUM(some_column) as sum_column
FROM your_table
GROUP BY a, b
-- 总汇总
SELECT NULL as a, NULL as b, SUM(some_column) as sum_column
FROM your_table;
集群
副本引擎
在clickhouse中，副本的操作流程如下，在副本a中写入数据会通过zookeeper自动同步到b中,流程如下：
由于依赖于zookeeper集群，所以需要配置zookeeper集群。
在 zoo.cfg中添加以下内容
server.1=hadoop102:2188:2888
server.2=hadoop103:2188:2888
server.3=hadoop104:2188:2888
编写运行脚本名称为zk.sh
#!/bin/bash
case $1 in
"start"){
for i in hadoop102 hadoop103 hadoop104
do
echo -------------------------------- $i zookeeper 启动 ---------------------------
ssh $i "/opt/wl/zookeeper/apache-zookeeper-3.8.3-bin/bin/zkServer.sh start"
done
}
;;
"stop"){
for i in hadoop102 hadoop103 hadoop104
do
echo -------------------------------- $i zookeeper 停止 ---------------------------
ssh $i "/opt/wl/zookeeper/apache-zookeeper-3.8.3-bin/bin/zkServer.sh stop"
done
}
;;
"status"){
for i in hadoop102 hadoop103 hadoop104
do
echo -------------------------------- $i zookeeper 状态 ---------------------------
ssh $i "/opt/wl/zookeeper/apache-zookeeper-3.8.3-bin/bin/zkServer.sh status"
done
}
;;
esac
执行命令启动集群
./zk.sh start
准备完zookeeper环境后，修改clickhouse的配置文件config.xml，添加以下内容
<zookeeper incl="zookeeper-servers" optional="true" />
<include_from>/etc/clickhouse-server/config.d/metrika.xml</include_from>
然后在/etc/clickhouse-server/config.d 路径下创建metrika.xml
<?xml version="1.0"?>
<yandex>
<zookeeper-servers>
<node index="1">
<host>hadoop102</host>
<port>2181</port>
</node>
<node index="2">
<host>hadoop103</host>
<port>2181</port>
</node>
<node index="3">
<host>hadoop104</host>
<port>2181</port>
</node>
</zookeeper-servers>
</yandex>
然后同步 这两个文件到其他两个节点，同步脚本如下
#!/bin/bash
#1. 判断参数个数
if [ $# -lt 1 ]
then
echo Not Enough Arguement!
exit;
fi
#2. 遍历集群所有机器
for host in hadoop103 hadoop104
do
echo ==================== $host ====================
#3. 遍历所有目录，挨个发送
for file in $@
do
#4. 判断文件是否存在
if [ -e $file ]
then
#5. 获取父目录
pdir=$(cd -P $(dirname $file); pwd)
#6. 获取当前文件的名称
fname=$(basename $file)
ssh $host "mkdir -p $pdir"
rsync -av $pdir/$fname $host:$pdir
else
echo $file does not exists!
fi
done
done
执行同步命令
xsync config.xml
xsync metrika.xml
由于修改了配置文件，需要重启服务
sudo /etc/init.d/clickhouse-server restart
在hadoop102、103、104三台机器上创建表，因为副本只会同步数据并不会同步结构
create table t_order_rep (
id UInt32,
sku_id String,
total_amount Decimal(16,2),
create_time Datetime
) engine = ReplicatedMergeTree('/clickhouse/tables/01/t_order_rep','rep_102')
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,sku_id);
ReplicatedMergeTree中第一个参数的标准格式为/clickhouse/tables/{shard}/table_name,要相同，第二个参数不相同。在103机器上则是 ReplicatedMergeTree('/clickhouse/tables/01/t_order_rep','rep_103')。
在102机器上执行插入语句
insert into t_order_rep values
(101,'sku_001',1000.00,'2020-06-01 12:00:00'),
(102,'sku_002',2000.00,'2020-06-01 12:00:00'),
(103,'sku_004',2500.00,'2020-06-01 12:00:00'),
(104,'sku_002',2000.00,'2020-06-01 12:00:00'),
(105,'sku_003',600.00,'2020-06-02 12:00:00');
查询103和104节点
然后在103中插入数据，查询102 和104
insert into t_order_rep values (106,'sku_006',6000.00,'2020-06-02 12:00:00');
结论：clickhouse中不存在主从的关系，都是互为副本
分片集群
副本虽然能够提高数据的可用性，降低丢失风险，但是每台服务器实际上必须容纳全量数据，对数据的横向扩容没有解决。
要解决数据水平切分的问题，需要引入分片的概念。通过分片把一份完整的数据进行切分，不同的分片分布到不同的节点上，再通过 Distributed 表引擎把数据拼接起来一同使用。
Distributed 表引擎本身不存储数据，有点类似于 MyCat 之于 MySql，成为一种中间件，通过分布式逻辑表来写入、分发、路由来操作多台节点不同分片的分布式数据。
注意：ClickHouse 的集群是表级别的，实际企业中，大部分做了高可用，但是没有用分片，避免降低查询性能以及操作集群的复杂性。
集群写入流程(3分片2副本共六个节点)
s1，s2，s3代表的是分片，（r1，r2）、（r3，r4）、（r5，r6）互为副本。
集群读取流程(3分片2副本共六个节点)
集群分片配置
配置的位置还是在之前的/etc/clickhouse-server/config.d/metrika.xml，内容如下
注：也可以不创建外部文件，直接在 config.xml 的<remote_servers>中指定
<yandex>
<remote_servers>
<gmall_cluster> <!-- 集群名称-->
<shard> <!--集群的第一个分片-->
<internal_replication>true</internal_replication>
<!--该分片的第一个副本-->
<replica>
<host>hadoop101</host>
<port>9000</port>
</replica>
<!--该分片的第二个副本-->
<replica>
<host>hadoop102</host>
<port>9000</port>
</replica>
</shard>
<shard> <!--集群的第二个分片-->
<internal_replication>true</internal_replication>
<replica> <!--该分片的第一个副本-->
<host>hadoop103</host>
<port>9000</port>
</replica>
<replica> <!--该分片的第二个副本-->
<host>hadoop104</host>
<port>9000</port>
</replica>
</shard>
<shard> <!--集群的第三个分片-->
<internal_replication>true</internal_replication>
<replica> <!--该分片的第一个副本-->
<host>hadoop105</host>
<port>9000</port>
</replica>
<replica> <!--该分片的第二个副本-->
<host>hadoop106</host>
<port>9000</port>
</replica>
</shard>
</gmall_cluster>
</remote_servers>
</yandex>
配置三节点版本集群及副本
还是以上步骤，新建配置文件为metrika-shard.xml
<?xml version="1.0"?>
<yandex>
<remote_servers>
<gmall_cluster> <!-- 集群名称-->
<shard> <!--集群的第一个分片-->
<internal_replication>true</internal_replication>
<replica> <!--该分片的第一个副本-->
<host>hadoop102</host>
<port>9000</port>
<!-- user 和password 可选 -->
<user>用户名</user>
<password>密码</password>
</replica>
<replica> <!--该分片的第二个副本-->
<host>hadoop103</host>
<port>9000</port>
<!-- user 和password 可选 -->
<user>用户名</user>
<password>密码</password>
</replica>
</shard>
<shard> <!--集群的第二个分片-->
<internal_replication>true</internal_replication>
<replica> <!--该分片的第一个副本-->
<host>hadoop104</host>
<port>9000</port>
<!-- user 和password 可选 -->
<user>用户名</user>
<password>密码</password>
</replica>
</shard>
</gmall_cluster>
</remote_servers>
<zookeeper-servers>
<node index="1">
<host>hadoop102</host>
<port>2181</port>
</node>
<node index="2">
<host>hadoop103</host>
<port>2181</port>
</node>
<node index="3">
<host>hadoop104</host>
<port>2181</port>
</node>
</zookeeper-servers>
<macros>
<shard>01</shard> <!--不同机器放的分片数不一样-->
<replica>rep_1_1</replica> <!--不同机器放的副本数不一样-->
</macros>
</yandex>
三节点则是只有102有副本，修改103和104的配置文件如下
重启clickhouse服务
创建本地表
➢ 会自动同步到 hadoop103 和 hadoop104 上
➢ 集群名字要和配置文件中的一致
➢ 分片和副本名称从配置文件的宏定义中获取
create table order_local on cluster gmall_cluster (
id UInt32,
sku_id String,
total_amount Decimal(16,2),
create_time Datetime
) engine
=ReplicatedMergeTree('/clickhouse/tables/{shard}/order_local','{replica}')
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,sku_id);
查看另外两个机器上是否创建成功
创建分布式表
create table order_distribute on cluster gmall_cluster
(
id UInt32,
sku_id String,
total_amount Decimal(16,2),
create_time
Datetime
)engine = Distributed(gmall_cluster,default,order_local,hiveHash(sku_id));
参数含义：
Distributed（集群名称，库名，本地表名，分片键）
分片键必须是整型数字，所以用 hiveHash 函数转换，也可以 rand()
在hadoop102上插入数据
insert into order_distribute values
(201,'sku_001',1000.00,'2020-06-01 12:00:00') ,
(202,'sku_002',2000.00,'2020-06-01 12:00:00'),
(203,'sku_004',2500.00,'2020-06-01 12:00:00'),
(204,'sku_002',2000.00,'2020-06-01 12:00:00'),
(205,'sku_003',600.00,'2020-06-02 12:00:00');
查看分布式表数据，三个节点查询数据一致
查看本地表数据分布情况
hadoop102和hadoop103互为副本所以数据一致。
查看执行计划
基本语法
EXPLAIN [AST | SYNTAX | PLAN | PIPELINE] [setting = value, ...] SELECT ... [FORMAT ...]
PLAN：用于查看执行计划，默认值。
- header 打印计划中各个步骤的 head 说明，默认关闭，默认值 0;
- description 打印计划中各个步骤的描述，默认开启，默认值 1；
- actions 打印计划中各个步骤的详细信息，默认关闭，默认值 0。
AST ：用于查看语法树;
SYNTAX：用于优化语法;
PIPELINE：用于查看 PIPELINE 计划。
- header 打印计划中各个步骤的 head 说明，默认关闭;
- graph 用 DOT 图形语言描述管道图，默认关闭，需要查看相关的图形需要配合graphviz 查看；
- actions 如果开启了 graph，紧凑打印打，默认开启。
语法优化
建表时优化
数据类型
1.建表时能用数值型或日期时间型表示的字段就不要用字符串
2.官方已经指出 Nullable 类型几乎总是会拖累性能，因为存储 Nullable 列时需要创建一个额外的文件来存储 NULL 的标记，并且 Nullable 列无法被索引。因此除非极特殊情况，应直接使用字段默认值表示空。
分区和索引
分区粒度根据业务特点决定，不宜过粗或过细。一般选择按天分区，也可以指定为 Tuple()，以单表一亿数据为例，分区大小控制在 10-30 个为最佳。
必须指定索引列，ClickHouse 中的索引列即排序列，通过 order by 指定，一般在查询条件中经常被用来充当筛选条件的属性被纳入进来,且查询越频繁的字段越要靠前；可以是单一维度，也可以是组合维度的索引；通常需要满足高级列在前、查询频率大的在前原则；还有基数特别大的不适合做索引列，如用户表的 userid 字段；通常筛选后的数据满足在百万以内为最佳。
配置优化
cpu资源
| 配置 | 描述 |
|---|---|
| background_pool_size | 后台线程池的大小，merge 线程就是在该线程池中执行，该线程池不仅仅是给 merge 线程用的，默认值 16，允许的前提下建议改成 cpu 个数的 2 倍（线程数）。 |
| background_schedule_pool_size | 执行后台任务（复制表、Kafka 流、DNS 缓存更新）的线程数。默认 128，建议改成 cpu 个数的 2 倍（线程数）。 |
| background_distributed_schedule_pool_size | 设置为分布式发送执行后台任务的线程数，默认 16，建议改成 cpu个数的 2 倍（线程数）。 |
| max_concurrent_queries | 最大并发处理的请求数(包含 select,insert 等)，默认值 100，推荐 150(不够再加)~300。 |
| max_threads | 设置单个查询所能使用的最大 cpu 个数，默认是 cpu 核数 |
内存资源
| 配置 | 描述 |
|---|---|
| max_memory_usage | 此参数在 users.xml 中,表示单次 Query 占用内存最大值，该值可以设置的比较大，这样可以提升集群查询的上限。保留一点给 OS，比如 128G 内存的机器，设置为 100GB。 |
| max_bytes_before_external_group_by | 一般按照 max_memory_usage 的一半设置内存，当 group 使用内存超过阈值后会刷新到磁盘进行。因为 clickhouse 聚合分两个阶段：查询并及建立中间数据、合并中间数据，结合上一项，建议 50GB。 |
| max_bytes_before_external_sort | 当 order by 已使用 max_bytes_before_external_sort 内存就进行溢写磁盘(基于磁盘排序)，如果不设置该值，那么当内存不够时直接抛错，设置了该值 order by 可以正常完成，但是速度相对存内存来说肯定要慢点(实测慢的非常多，无法接受)。 |
| max_table_size_to_drop | 此参数在 config.xml 中，应用于需要删除表或分区的情况，默认是50GB，意思是如果删除 50GB 以上的分区表会失败。建议修改为 0，这样不管多大的分区表都可以删除。 |
查询优化
Prewhere 替代 where
Prewhere 和 where 语句的作用相同，用来过滤数据。不同之处在于 prewhere 只支持*MergeTree 族(合并树)系列引擎的表，首先会读取指定的列数据，来判断数据过滤，等待数据过滤之后再读取 select 声明的列字段来补全其余属性。
当查询列明显多于筛选列时使用 Prewhere 可十倍提升查询性能，Prewhere 会自动优化执行过滤阶段的数据读取方式，降低 io 操作。
where：先将所有数据所有行取出，然后用 where 后的条件进行匹配
Prewhere：先将进行 Prewhere 后的所有字段的列取出，然后将查询语句中使用到的字段，进行补全，然后再使用 Prewhere 的条件进行匹配，从而少读取了一些用不到的字段
但是某些场景即使开启优化，也不会自动转换成 prewhere，需要手动指定 prewhere：
- 使用常量表达式
- 使用默认值为 alias 类型的字段
- 包含了 arrayJOIN，global In，global Not In 或者 indexHint 的查询
- select 查询的列字段和 where 的谓词相同
- 使用了主键字段
列裁剪与分区裁剪
数据量太大时应避免使用 select * 操作，查询的性能会与查询的字段大小和数量成线性表换，字段越少，消耗的 io 资源越少，性能就会越高。
反例：
select * from datasets.hits_v1;
正例：
select WatchID,
JavaEnable,
Title,
……
from datasets.hits_v1;
分区裁剪就是只读取需要的分区，在过滤条件中指定。
select WatchID,
JavaEnable,
Title,
……
from datasets.hits_v1
where EventDate='2014-03-23';
uniqCombined 替代 distinct
性能可提升 10 倍以上，uniqCombined 底层采用类似 HyperLogLog 算法实现，能接收 2%左右的数据误差，可直接使用这种去重方式提升查询性能。Count(distinct )会使用 uniqExact精确去重。
不建议在千万级不同数据上执行 distinct 去重查询，改为近似去重 uniqCombined
反例：
select count(distinct rand()) from hits_v1;
正例：
SELECT uniqCombined(rand()) from datasets.hits_v1
使用物化视图
用in代替join
- ClickHouse 处理 Join 的逻辑：将右表完全加载到内存，然后遍历右表的数据，判断与左表是右否匹配的数据
当多表联查时，查询的数据仅从其中一张表出时，可考虑用 IN 操作而不是 JOIN
大小表 JOIN
多表 join 时要满足小表在右的原则，右表关联时被加载到内存中与左表进行比较，ClickHouse 中无论是 Left join 、Right join 还是 Inner join 永远都是拿着右表中的每一条记录到左表中查找该记录是否存在，所以右表必须是小表。
分布式表使用 GLOBAL
两张分布式表上的 IN 和 JOIN 之前必须加上 GLOBAL 关键字，右表只会在接收查询请求的那个节点查询一次，并将其分发到其他节点上。如果不加 GLOBAL 关键字的话，每个节点都会单独发起一次对右表的查询，而右表又是分布式表，就导致右表一共会被查询 N²次（N是该分布式表的分片数量），这就是查询放大，会带来很大开销。
数据一致性
查询 CK 手册发现，即便对数据一致性支持最好的 Mergetree，也只是保证最终一致性
准备测试数据和表
创建表
CREATE TABLE test_a(
user_id UInt64,
score String,
deleted UInt8 DEFAULT 0,
create_time DateTime DEFAULT toDateTime(0)
)ENGINE= ReplacingMergeTree(create_time)
ORDER BY user_id;
写入1000万数据
INSERT INTO TABLE test_a(user_id,score)
WITH(
SELECT ['A','B','C','D','E','F','G']
)AS dict
SELECT number AS user_id, dict[number%7+1] FROM numbers(10000000);
修改前 50 万 行数据，修改内容包括 name 字段和 create_time 版本号字段
INSERT INTO TABLE test_a(user_id,score,create_time)
WITH(
SELECT ['AA','BB','CC','DD','EE','FF','GG']
)AS dict
SELECT number AS user_id, dict[number%7+1], now() AS create_time FROM
numbers(500000);
统计总数
SELECT COUNT() FROM test_a;
10500000
通过Group by 去重
执行去重的查询
SELECT
user_id ,
argMax(score, create_time) AS score,
argMax(deleted, create_time) AS deleted,
max(create_time) AS ctime
FROM test_a
GROUP BY user_id
HAVING deleted = 0;
函数说明：
argMax(field1，field2):取 field2 最大值所在行的 field1 字段值
创建视图
CREATE VIEW view_test_a AS
SELECT
user_id ,
argMax(score, create_time) AS score,
argMax(deleted, create_time) AS deleted,
max(create_time) AS ctime
FROM test_a
GROUP BY user_id
HAVING deleted = 0;
插入重复数据再次查询
#再次插入一条数据
INSERT INTO TABLE test_a(user_id,score,create_time)
VALUES(0,'AAAA',now())
#再次查询
SELECT *
FROM view_test_a
WHERE user_id = 0;
删除数据测试
#再次插入一条标记为删除的数据
INSERT INTO TABLE test_a(user_id,score,deleted,create_time)
VALUES(0,'AAAA',1,now());
#再次查询，刚才那条数据看不到了
SELECT *
FROM view_test_a
WHERE user_id = 0;
物化视图
概述
ClickHouse 的物化视图是一种查询结果的持久化，它确实是给我们带来了查询效率的提升。用户查起来跟表没有区别，它就是一张表，它也像是一张时刻在预计算的表，创建的过程它是用了一个特殊引擎，加上后来 as select，就是 create 一个 table as select 的写法。
“查询结果集”的范围很宽泛，可以是基础表中部分数据的一份简单拷贝，也可以是多表 join 之后产生的结果或其子集，或者原始数据的聚合指标等等。所以，物化视图不会随着基础表的变化而变化，所以它也称为快照（snapshot）
物化视图与普通视图的区别
普通视图不保存数据，保存的仅仅是查询语句，查询的时候还是从原表读取数据，可以将普通视图理解为是个子查询。
物化视图则是把查询的结果根据相应的引擎存入到了磁盘或内存中，对数据重新进行了组织，你可以理解物化视图是完全的一张新表。
优缺点
优点：查询速度快，要是把物化视图这些规则全部写好，它比原数据查询快了很多，总的行数少了，因为都预计算好了。
缺点：它的本质是一个流式数据的使用场景，是累加式的技术，所以要用历史数据做去重、去核这样的分析，在物化视图里面是不太好用的。在某些场景的使用也是有限的。而且如果一张表加了好多物化视图，在写这张表的时候，就会消耗很多机器的资源，比如数据带宽占满、存储一下子增加了很多。
基本语法
CREATE [MATERIALIZED] VIEW [IF NOT EXISTS] [db.]table_name [TO[db.]name] [ENGINE = engine] [POPULATE] AS SELECT ...
创建条件
- 必须指定物化视图的 engine 用于数据存储
- TO [db].[table]语法的时候，不得使用 POPULATE
- 查询语句(select）可以包含下面的子句： DISTINCT, GROUP BY, ORDER BY, LIMIT…
- 物化视图的 alter 操作有些限制，操作起来不大方便
- 若物化视图的定义使用了 TO [db.]name 子语句，则可以将目标表的视图 卸载 DETACH 再装载 ATTACH
更新
-
物化视图的数据更新
-
物化视图创建好之后，若源表被写入新数据则物化视图也会同步更新
-
POPULATE 关键字决定了物化视图的更新策略：
若有 POPULATE 则在创建视图的过程会将源表已经存在的数据一并导入，类似于 create table … as
若无
POPULATE 则物化视图在创建之后没有数据，只会在创建只有同步之后写入 源表的数据
clickhouse 官方并不推荐使用
POPULATE，因为在创建物化视图的过程中同时写入 的数据不能被插入物化视图。 -
物化视图不支持同步删除，若源表的数据不存在（删除了）则物化视图的数据仍然保留
-
物化视图是一种特殊的数据表，可以用 show tables 查看
-
物化视图数据的删除
-
物化视图的删除
案例实操
建表
create table visit(
EventDate Date,
UserID UInt64,
Url String
)ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventDate)
ORDER BY (intHash64(UserID))
SETTINGS index_granularity = 8192
插入数据
INSERT INTO visit (EventDate, UserID, Url)
with (SELECT ['2021-01-01','2021-01-02','2021-01-03','2021-01-04']) as dates
SELECT
toDate(dates[number%3+1]),
rand64(),
generateUUIDv4()
FROM numbers(100);
创建物化视图
CREATE MATERIALIZED VIEW visit_view
(
EventDate Date,
UserID UInt64,
countUserId UInt64
)
ENGINE =SummingMergeTree
PARTITION BY toYYYYMM(EventDate)
ORDER BY (EventDate, intHash64(UserID))
AS
SELECT
uniq(UserID) as countUserId,
UserID,
EventDate
FROM
visit
WHERE
EventDate >= '2021-01-02'
GROUP BY
UserID,
EventDate;
##或者可以用下列语法，表 A 可以是一张 mergetree 表
CREATE MATERIALIZED VIEW 物化视图名 TO 表 A AS SELECT FROM 表 B;
由于创建视图没有加上 POPULATE关键字，所以此时视图没有数据
导入增量数据
INSERT INTO visit (EventDate, UserID, Url)
SELECT
toDate('2021-01-02'),
rand64(),
generateUUIDv4(20)
查看视图
导入历史数据
INSERT INTO visit_view
SELECT
UserID,
EventDate,
uniq(UserID)
FROM visit
WHERE EventDate = '2021-01-01'
GROUP BY UserID,EventDate
查询视图
MaterializeMySQL 引擎
概述
MySQL 的用户群体很大，为了能够增强数据的实时性，很多解决方案会利用 binlog 将数据写入到 ClickHouse。为了能够监听 binlog 事件，我们需要用到类似 canal 这样的第三方中间件，这无疑增加了系统的复杂度。
ClickHouse 20.8.2.3 版本新增加了 MaterializeMySQL 的 database 引擎，该 database 能映 射 到 MySQL 中 的 某 个 database ， 并 自 动 在 ClickHouse 中 创 建 对 应 的ReplacingMergeTree。ClickHouse 服务做为 MySQL 副本，读取 Binlog 并执行 DDL 和 DML 请求，实现了基于 MySQL Binlog 机制的业务数据库实时同步功能。
使用细则
- DDL 查询
MySQL DDL 查询被转换成相应的 ClickHouse DDL 查询（ALTER, CREATE, DROP, RENAME）。如果 ClickHouse 不能解析某些 DDL 查询，该查询将被忽略。 - 数据复制
MaterializeMySQL 不支持直接插入、删除和更新查询，而是将 DDL 语句进行相应转换：
MySQL INSERT 查询被转换为 INSERT with _sign=1。
MySQL DELETE 查询被转换为 INSERT with _sign=-1。
MySQL UPDATE 查询被转换成 INSERT with _sign=1 和 INSERT with _sign=-1。 - _SELECT 查询
如果在 SELECT 查询中没有指定_version，则使用 FINAL 修饰符，返回_version 的最大值对应的数据，即最新版本的数据。
如果在 SELECT 查询中没有指定_sign，则默认使用 WHERE _sign=1，即返回未删除状态（_sign=1)的数据。 - 索引转换
ClickHouse 数据库表会自动将 MySQL 主键和索引子句转换为ORDER BY 元组。
ClickHouse 只有一个物理顺序，由 ORDER BY 子句决定。如果需要创建新的物理顺序，请使用物化视图。
案例实操
mysql开启binlog和GTID模式
打开打开/etc/my.cnf,在[mysqld]下添加
server-id=1
log-bin=mysql-bin
binlog_format=ROW
# 如果如果 clickhouse 使用的是 20.8 prestable 之后发布的版本，那么 MySQL 还需要配置开启 GTID 模式
gtid-mode=on
enforce-gtid-consistency=1 # 设置为主从强一致性
log-slave-updates=1 # 记录日志
重启mysql服务
sudo systemctl restart mysqld
准备mysql表和数据
CREATE DATABASE testck;
CREATE TABLE `testck`.`t_organization` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`code` int NOT NULL,
`name` text DEFAULT NULL,
`updatetime` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY (`code`)
) ENGINE=InnoDB;
INSERT INTO testck.t_organization (code,name,updatetime) VALUES(1000,'Realinsight',NOW());
INSERT INTO testck.t_organization (code,name,updatetime) VALUES(1001, 'Realindex',NOW());
INSERT INTO testck.t_organization (code,name,updatetime) VALUES(1002,'EDT',NOW());
开启clickhouse物化引擎
set allow_experimental_database_materialized_mysql=1
创建复制管道
ClickHouse 中创建MaterializeMySQL 数据库
CREATE DATABASE test_binlog ENGINE = MaterializeMySQL('hadoop102:3306','testck','root','1111');
其中 4 个参数分别是 MySQL 地址、databse、username 和 password
查看数据
查看发现有mysql的表
查看数据
select * from t_organization;
删除数据
mysql删除数据
DELETE FROM t_organization where id = 2;
查看clickhouse发现id为2的被删除
select * from t_organization;
在刚才的查询中增加 _sign 和 _version 虚拟字段
select *,_sign,_version from t_organization order by _sign desc,_version desc;
结论：在查询时，对于已经被删除的数据，_sign=-1，ClickHouse 会自动重写 SQL，将 _sign =1 的数据过滤掉;对于修改的数据，则自动重写 SQL，为其增加 FINAL 修饰符。
优化建议
- 必须建分区
- 查询频繁一定要建立索引，在order by 后面
- 表中数据太大可以进行分表、分数据
- 分布式表一定要对数据进行分片，分片键选择inthas64()或者inthash32()
监控工具
安装Prometheus 和Grafana
Prometheus 下载地址：https://prometheus.io/download/
Grafana 下载地址：https://grafana.com/grafana/download
修改prometheus.yml,在 scrape_configs 配置项下添加配置：
- job_name: clickhouse-1
static_configs:
- targets: ['localhost:9363']
启动prometheus服务
nohup ./prometheus --config.file=prometheus.yml > ./prometheus.log 2>&1 &
访问http://hadoop102:9090/，点击 status->target
安装并启动Grafana
#安装
sudo yum install -y https://dl.grafana.com/enterprise/release/grafana-enterprise-10.2.2-1.x86_64.rpm
#启动服务
#刚安装完需要重载systemd配置：
systemctl daemon-reload
#启动服务：
systemctl start grafana-server
#查看状态：
systemctl status grafana-server
#设置开机启动：
systemctl enable grafana-server.service
打开 web：http://hadoop102:3000,默认用户名和密码：admin
修改clickhouse配置文件
编辑/etc/clickhouse-server/config.xml，打开以下配置
<prometheus>
<endpoint>/metrics</endpoint>
<port>9363</port>
<metrics>true</metrics>
<events>true</events>
<asynchronous_metrics>true</asynchronous_metrics>
</prometheus>
重启clickhouse服务
Grafana 集成 Prometheus
添加数据源
选择Prometheus，填写地址
保存
导入模版文件地址：Dashboards | Grafana Labs
groupBitmap解决数据量大去重慢问题
用途
官方说的是计算基数，其实就是不统计重复个数
参数为Uint类型,就是不是负数的整数
返回值为Uint64类型
案例
测试数据
userId
1
1
2
3
SELECT groupBitmap(userId) as num FROM t;
结果
num
3
想要知道bitmap存储的是什么东西,直接查是看不出来的,需要转为数组才可以,比如
select bitmapToArray(uv) from label_crowd_collection_base_bit_map;
结果如下
创建测试表,使用bitmap存储就会比较节约空间
create table label_bit_map
(
label_code String comment '标签名称',
label_name String comment '标签名称',
uid AggregateFunction(groupBitmap, UInt64) comment 'uid'
)
engine = AggregatingMergeTree PARTITION BY label_code
ORDER BY label_code
SETTINGS index_granularity = 8192;
往表中插入 age 一千万条数据
INSERT INTO label_bit_map (label_code, label_name, uid)
SELECT 'age', '年龄', groupBitmapState(toUInt64(rand()%1000000000))
FROM numbers(10000000);
往表中插入sex 一千万条数据
INSERT INTO label_bit_map (label_code, label_name, uid)
SELECT 'sex', '性别', groupBitmapState(toUInt64(rand()%1000000000))
FROM numbers(10000000);
四秒完成
completed in 4 s 720 ms
交并(取交集)
# bitmapCardinality(groupBitmapAndState(uid)) 等价于 groupBitmapAnd(uid)
# 在22.5版本以下，groupBitmapAndState/groupBitmapOrState/groupBitmapXorState 使用在分布式表中会出现以下问题 https://github.com/ClickHouse/ClickHouse/pull/32529
select bitmapCardinality(groupBitmapAndState(uid)) from label_bit_map where label_code in('sex','age')
取并集
select bitmapCardinality(groupBitmapOrState(uid)) from label_bit_map where label_code in('sex','age')
取具体数量
select bitmapToArray(groupBitmapAndState(uid)) from label_bit_map where label_code in('sex','age')
具体可以参考官方文档:https://clickhouse.com/docs/zh/sql-reference/functions/bitmap-functions
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· winform 绘制太阳，地球，月球 运作规律
· AI与.NET技术实操系列（五）：向量存储与相似性搜索在 .NET 中的实现
· 超详细：普通电脑也行Windows部署deepseek R1训练数据并当服务器共享给他人
· 【硬核科普】Trae如何「偷看」你的代码？零基础破解AI编程运行原理
· 上周热点回顾（3.3-3.9）
2022-06-14 Spark