ClickHouse
ClickHouse
一、安装
1.1 系统准备
系统：CentOS 7
192.168.235.5 master01
192.168.235.6 slave01
192.168.235.7 slave02
- 关闭防火墙
- CentOS 取消打开文件数限制
[tpxcer@master01 ~]$ sudo vim /etc/security/limits.conf
* soft nofile 65536
* hard nofile 65536
* soft nproc 131072
* hard nproc 131072
[tpxcer@master01 ~]$ sudo vim /etc/security/limits.d/20-nproc.conf
* soft nofile 65536
* hard nofile 65536
* soft nproc 131072
* hard nproc 131072- 同步配置到其他机器
- 查看是否生效
[tpxcer@master01 root]$ ulimit -a
open files (-n) 65536
max user processes (-u) 131072- 安装依赖
sudo yum install -y libtool
sudo yum install -y *unixODBC*- CentOS 取消 SELINUX
[tpxcer@master01 root]$ sudo vim /etc/selinux/config
SELINUX=disabled1.2 ClickHouse 单机安装
- 下载离线的安装包
clickhouse-client-22.2.2.1-2.noarch.rpm
clickhouse-common-static-22.2.2.1-2.x86_64.rpm
clickhouse-common-static-dbg-22.2.2.1-2.x86_64.rpm
clickhouse-server-22.2.2.1-2.noarch.rpm- 安装包
分别安装到所有机器上
sudo rpm -ivh *.rpm
rpm -qa | grep clickhouse- 修改配置使其可以被远程访问
记得分发文件
[tpxcer@master01 Downloads]$ sudo vim /etc/clickhouse-server/config.xml
-- 取消以下行注释
<listen_host>::</listen_host>
-- 这里注意下数据存放路径可能要调整 /opt/hdp-data/ch1/
<path>/var/lib/clickhouse/</path>
-- 在注意下日志文件路径
<log>/var/log/clickhouse-server/clickhouse-server.log</log>
<errorlog>/var/log/clickhouse-server/clickhouse-server.err.log</errorlog>- 启动服务
-- 启动
sudo systemctl start clickhouse-server
-- 查看状态
sudo systemctl status clickhouse-server
-- 关闭自动启动
sudo systemctl disable clickhouse-server用clickhouse自带的命令启动
sudo clickhouse restart- 连接clickhouse
clickhouse-client -m
clickhouse-client -m --ask-password
# 远程访问
clickhouse-client -m -h hostname- 直接查询数据
clickhouse-client --query "show databases;"二、数据类型
官方文档：https://clickhouse.tech/docs/zh/sql-reference/data-types/
2.1 枚举类型
包括 Enum8 和 Enum16 类型。
Enum 保存 'string'= integer 的对应关系。
Enum8 用 'String'= Int8 对描述。
Enum16 用 'String'= Int16 对描述。
- 用法演示
创建一个带有一个枚举 Enum8('hello' = 1, 'world' = 2) 类型的列
CREATE TABLE t_enum
(
x Enum8('hello' = 1, 'world' = 2)
)
ENGINE = TinyLog;- 这个 x 列只能存储类型定义中列出的值:'hello'或'world'
INSERT INTO t_enum VALUES ('hello'), ('world'), ('hello');- 如果需要看到对应行的数值，则必须将 Enum 值转换为整数类型
SELECT CAST(x, 'Int8') FROM t_enum;2.2 数组
Array(T):由 T 类型元素组成的数组。 T 可以是任意类型，包含数组类型。 但不推荐使用多维数组，ClickHouse 对多维数组 的支持有限。例如，不能在 MergeTree 表中存储多维数组。
- 创建数组方式 1，使用 array 函数
SELECT array(1, 2) AS x, toTypeName(x)- 创建数组方式 2:使用方括号
SELECT [1, 2] AS x, toTypeName(x);三、表引擎
3.1 TinyLog
以列文件的形式保存在磁盘上，不支持索引，没有并发控制。一般保存少量数据的小表， 生产环境上作用有限。可以用于平时练习测试用。
create table t_tinylog ( id String, name String) engine=TinyLog;3.2 Memory
内存引擎，数据以未压缩的原始形式直接保存在内存当中，服务器重启数据就会消失。 读写操作不会相互阻塞，不支持索引。简单查询下有非常非常高的性能表现(超过 10G/s)。 一般用到它的地方不多，除了用来测试，就是在需要非常高的性能，同时数据量又不太大(上限大概 1 亿行)的场景
3.3 MergeTree
ClickHouse 中最强大的表引擎当属 MergeTree(合并树)引擎及该系列(*MergeTree) 中的其他引擎，支持索引和分区，地位可以相当于 innodb 之于 Mysql。而且基于 MergeTree， 还衍生除了很多小弟，也是非常有特色的引擎。
- 建表语句
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');MergeTree 其实还有很多参数(绝大多数用默认值即可)，但是三个参数是更加重要的， 也涉及了关于 MergeTree 的很多概念。
注意，主键不唯一
3.3.1 partition by 分区(可选)
- 作用
分区的目的主要是降低扫描的范围，优化查询速度
分区目录 MergeTree 是以列文件+索引文件+表定义文件组成的，但是如果设定了分区那么这些文件就会保存到不同的分区目录中。
并行 分区后，面对涉及跨分区的查询统计，ClickHouse 会以分区为单位并行处理。
数据写入与分区合并
任何一个批次的数据写入都会产生一个临时分区，不会纳入任何一个已有的分区。写入后的某个时刻(大概 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');马上查询发现并未并入现有分区
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │ 1000.00 │ 2020-06-01 12:00:00 │
│ 102 │ sku_002 │ 2000.00 │ 2020-06-01 11: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_004 │ 2500.00 │ 2020-06-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │ 600.00 │ 2020-06-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │ 1000.00 │ 2020-06-01 12:00:00 │
│ 102 │ sku_002 │ 2000.00 │ 2020-06-01 11: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_004 │ 2500.00 │ 2020-06-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │ 600.00 │ 2020-06-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘手动optimized
optimize table t_order_mt final;查看结果
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │ 600.00 │ 2020-06-02 12:00:00 │
│ 102 │ sku_002 │ 600.00 │ 2020-06-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │ 1000.00 │ 2020-06-01 12:00:00 │
│ 101 │ sku_001 │ 1000.00 │ 2020-06-01 12:00:00 │
│ 102 │ sku_002 │ 2000.00 │ 2020-06-01 11: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 │ 2000.00 │ 2020-06-01 11: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_004 │ 2500.00 │ 2020-06-01 12:00:00 │
│ 102 │ sku_004 │ 2500.00 │ 2020-06-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘- 指定分区合并
optimize table t_order_mt partition '20200621' final;3.3.2 primary key 主键(可选)
ClickHouse 中的主键，和其他数据库不太一样，它只提供了数据的一级索引，但是却不是唯一约束。这就意味着是可以存在相同 primary key 的数据的。 主键的设定主要依据是查询语句中的 where 条件。 根据条件通过对主键进行某种形式的二分查找，能够定位到对应的index granularity,避 免了全表扫描。 index granularity: 直接翻译的话就是索引粒度，指在稀疏索引中两个相邻索引对应数 据的间隔。ClickHouse 中的 MergeTree 默认是 8192。官方不建议修改这个值，除非该列存在大量重复值，比如在一个分区中几万行才有一个不同数据。
稀疏索引:
稀疏索引的好处就是可以用很少的索引数据，定位更多的数据，代价就是只能定位到索 引粒度的第一行，然后再进行进行一点扫描。
3.3.3 order by(必选)
order by 设定了分区内的数据按照哪些字段顺序进行有序保存。 order by 是 MergeTree 中唯一一个必填项，甚至比 primary key 还重要，因为当用户不 设置主键的情况，很多处理会依照 order by 的字段进行处理(比如后面会讲的去重和汇总)。
主键必须 order by 字段的前缀字段。 比如 order by 字段是 (id,sku_id) 那么主键必须是 id 或者(id,sku_id)
3.3.4 二级索引
- 创建测试表
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);其中 GRANULARITY N 是设定二级索引对于一级索引粒度的粒度。
- 插入数据
insert into t_order_mt2 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');- 执行并查看查看日志
clickhouse-client --send_logs_level=trace<<<'select * from t_order_mt2 where total_amount > toDecimal32(900., 2)';- 表的文件目录会多出skp_idx_x.x的索引文件
[root@master01 20200602_2_2_0]# pwd
/var/lib/clickhouse/data/default/t_order_mt2/20200602_2_2_0
[root@master01 20200602_2_2_0]# ls
checksums.txt columns.txt count.txt data.bin data.mrk3 default_compression_codec.txt minmax_create_time.idx partition.dat primary.idx skp_idx_a.idx skp_idx_a.mrk33.3.5 TTL
TTL 即 Time To Live，MergeTree 提供了可以管理数据表或者列的生命周期的功能。
- 列级别 TTL
创建测试表
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);插入数据(注意:根据实际时间改变)
insert into t_order_mt3 values (106,'sku_001',1000.00,'2020-06-12 22:52:30'), (107,'sku_002',2000.00,'2020-06-12 22:52:30'), (110,'sku_003',600.00,'2020-06-13 12:00:00');手动合并，查看效果 到期后，指定的字段数据归0
- 表级 TTL
下面的这条语句是数据会在 create_time 之后 10 秒丢失
alter table t_order_mt3 MODIFY TTL create_time + INTERVAL 10 SECOND;涉及判断的字段必须是 Date 或者 Datetime 类型，推荐使用分区的日期字段。 能够使用的时间周期
3.4 ReplacingMergeTree
ReplacingMergeTree 是 MergeTree 的一个变种，它存储特性完全继承 MergeTree，只是 多了一个去重的功能。 尽管 MergeTree 可以设置主键，但是 primary key 其实没有唯一约束 的功能。如果你想处理掉重复的数据，可以借助这个 ReplacingMergeTree。
- 去重时机
数据的去重只会在合并的过程中出现。合并会在未知的时间在后台进行，所以你无法预先作出计划。有一些数据可能仍未被处理。
- 去重范围
如果表经过了分区，去重只会在分区内部进行去重，不能执行跨分区的去重。 所以 ReplacingMergeTree 能力有限， ReplacingMergeTree 适用于在后台清除重复的数 据以节省空间，但是它不保证没有重复的数据出现。
- 案例演示
(1)创建表
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()填入的参数为版本字段，重复数据保留版本字段值最大的。 如果不填版本字段，默认按照插入顺序保留最后一条。
(2)向表中插入数据
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');(3) 查看合并后的数据
SELECT *
FROM t_order_rmt
Query id: 53c3311e-35bb-4ba3-ac25-f6671694bb93
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │ 600.00 │ 2020-06-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │ 1000.00 │ 2020-06-01 12:00:00 │
│ 102 │ sku_002 │ 12000.00 │ 2020-06-01 13:00:00 │
│ 102 │ sku_004 │ 2500.00 │ 2020-06-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘(4) 得出结论
实际上是使用 order by 字段作为唯一键- 去重不能跨分区
- 只有同一批插入(新版本)或合并分区时才会进行去重
- 认定重复的数据保留，版本字段值最大的
- 如果版本字段相同则按插入顺序保留最后一笔
3.5 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');- 查询数据
SELECT *
FROM t_order_smt
Query id: 0bec1899-c5db-4809-acfd-18522a57cb21
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │ 600.00 │ 2020-06-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │ 1000.00 │ 2020-06-01 12:00:00 │
│ 102 │ sku_002 │ 16000.00 │ 2020-06-01 11:00:00 │
│ 102 │ sku_004 │ 2500.00 │ 2020-06-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘- 通过结果可以得到以下结论
- 以SummingMergeTree()中指定的列作为汇总数据列
- 可以填写多列必须数字列，如果不填，以所有非维度列且为数字列的字段为汇总数据列
- 以 order by 的列为准，作为维度列
其他的列按插入顺序保留第一行- 不在一个分区的数据不会被聚合
- 只有在同一批次插入(新版本)或分片合并时才会进行聚合
- 开发建议
设计聚合表的话，唯一键值、流水号可以去掉，所有字段全部是维度、度量或者时间戳。
6）问题
能不能直接执行以下 SQL 得到汇总值
select total_amount from XXX where province_name=’’ and create_date=’xxx’不行，可能会包含一些还没来得及聚合的临时明细如果要是获取汇总值，还是需要使用 sum 进行聚合，这样效率会有一定的提高，但本身 ClickHouse 是列式存储的，效率提升有限，不会特别明显。
select sum(total_amount) from province_name=’’ and create_date=‘xxx’四、文件目录
4.1 ClickHouse各文件目录
bin/ ===> /usr/bin/
conf/ ===> /etc/clickhouse-server/
lib/ ===> /var/lib/clickhouse
log/ ===> /var/log/clickhouse-server4.2 表目录文件
- 表schema文件目录
/var/lib/clickhouse/metadata- 数据文件目录
/var/lib/clickhouse/data- 具体数据目录
[root@master01 20200602_2_4_1]# ll
total 36
-rw-r----- 1 clickhouse clickhouse 259 Sep 7 15:19 checksums.txt
-rw-r----- 1 clickhouse clickhouse 118 Sep 7 15:19 columns.txt
-rw-r----- 1 clickhouse clickhouse 1 Sep 7 15:19 count.txt
-rw-r----- 1 clickhouse clickhouse 151 Sep 7 15:19 data.bin
-rw-r----- 1 clickhouse clickhouse 144 Sep 7 15:19 data.mrk3
-rw-r----- 1 clickhouse clickhouse 10 Sep 7 15:19 default_compression_codec.txt
-rw-r----- 1 clickhouse clickhouse 8 Sep 7 15:19 minmax_create_time.idx
-rw-r----- 1 clickhouse clickhouse 4 Sep 7 15:19 partition.dat
-rw-r----- 1 clickhouse clickhouse 8 Sep 7 15:19 primary.idx
[root@master01 20200602_2_4_1]# pwd
# 实际数据文件目录
/var/lib/clickhouse/data/default/t_order_mt/20200602_2_4_1
# 分区编号，上面的20200602_2_4_1
PartitionId_MinBlockNum_MaxBlockNum_Level
分区值_最小分区块编号_最大分区块编号_合并层级
=》PartitionId
数据分区ID生成规则
数据分区规则由分区ID决定，分区ID由PARTITION BY分区键决定。根据分区键字段类型，ID生成规则可分为：
未定义分区键
没有定义PARTITION BY，默认生成一个目录名为all的数据分区，所有数据均存放在all目录下。
整型分区键
分区键为整型，那么直接用该整型值的字符串形式做为分区ID。
日期类分区键
分区键为日期类型，或者可以转化成日期类型。
其他类型分区键
String、Float类型等，通过128位的Hash算法取其Hash值作为分区ID。
=》MinBlockNum
最小分区块编号，自增类型，从1开始向上递增。每产生一个新的目录分区就向上递增一个数字。
=》MaxBlockNum
最大分区块编号，新创建的分区MinBlockNum等于MaxBlockNum的编号。
=》Level
合并的层级，被合并的次数。合并次数越多，层级值越大。
# 其他文件
bin文件：数据文件
mrk文件：标记文件
标记文件在 idx索引文件 和 bin数据文件 之间起到了桥梁作用。
以mrk2结尾的文件，表示该表启用了自适应索引间隔。
primary.idx文件：主键索引文件，用于加快查询效率。
minmax_create_time.idx：分区键的最大最小值。
checksums.txt：校验文件，用于校验各个文件的正确性。存放各个文件的size以及hash值。五、SQL
5.1 Update 和 Delete
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 语句分两步执行，同步执行的部分其实只是进行 新增数据新增分区和并把旧分区打上逻辑上的失效标记。直到触发分区合并的时候，才会删 除旧数据释放磁盘空间，一般不会开放这样的功能给用户，由管理员完成。
5.2 alter 操作
- 新增字段
alter table tableName add column newcolname String after col1;- 修改字段类型
alter table tableName modify column newcolname String;- 删除字段
alter table tableName drop column newcolname;5.3 导出数据
clickhouse-client --query "select * from t_order_mt where
create_time='2020-06-01 12:00:00'" --format CSVWithNames>
/opt/module/data/rs1.csv更多支持格式参照: https://clickhouse.tech/docs/en/interfaces/formats/
六、副本
副本的目的主要是保障数据的高可用性，即使一台 ClickHouse 节点宕机，那么也可以从 其他服务器获得相同的数据。 https://clickhouse.tech/docs/en/engines/table-engines/mergetree-family/replication/
- 启动zookeeper集群
- 修改
/etc/clickhouse-server/config.xml中被注释掉的zookeeper配置
<zookeeper>
<node>
<host>master01</host>
<port>2181</port>
</node>
<node>
<host>slave01</host>
<port>2181</port>
</node>
<node>
<host>slave02</host>
<port>2181</port>
</node>
</zookeeper>- 同步文件
[root@master01 clickhouse-server]# xsync config.xml- 重启服务
- 分别建表
副本只能同步数据，不能同步表结构，所以我们需要在每台机器上自己手动建表
-- master01
create table t_order_rep2
(
id UInt32,
sku_id String,
total_amount Decimal(16,2),
create_time Datetime
) engine=ReplicatedMergeTree('/clickhouse/table/01/t_order_rep','master01')
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,sku_id);
-- slave02
create table t_order_rep2 (
id UInt32,
sku_id String,
total_amount Decimal(16,2), create_time Datetime
) engine =ReplicatedMergeTree('/clickhouse/table/01/t_order_rep','slave02')
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,sku_id);
-- 参数解释
-- ReplicatedMergeTree 中， 第一个参数是分片的zk_path一般按照:/clickhouse/table/{shard}/{table_name} 的格式写，如果只有一个分片就写 01 即可。
-- 第二个参数是副本名称，相同的分片副本名称不能相同。- 插入数据
insert into t_order_rep2 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');- 测试
此时在两台机器上查询都可以获取数据select * from t_order_rep2
七、分片集群
副本虽然能够提高数据的可用性，降低丢失风险，但是每台服务器实际上必须容纳全量 数据，对数据的横向扩容没有解决。 要解决数据水平切分的问题，需要引入分片的概念。通过分片把一份完整的数据进行切 分，不同的分片分布到不同的节点上，再通过 Distributed 表引擎把数据拼接起来一同使用。 Distributed 表引擎本身不存储数据，有点类似于 MyCat 之于 MySql，成为一种中间件，通过分布式逻辑表来写入、分发、路由来操作多台节点不同分片的分布式数据。
三台机器 master01,slave01,slave02
7.1 集群及副本规划(2 个分片，只有第一个分片有副本)
- 编辑
/etc/clickhouse-server/config.xml文件
<remote_servers>
<gmall_cluster> <!-- 集群名称-->
<shard> <!--集群的第一个分片-->
<internal_replication>true</internal_replication>
<replica> <!--该分片的第一个副本-->
<host>master01</host>
<port>9000</port>
</replica>
<replica> <!--该分片的第二个副本-->
<host>slave01</host>
<port>9000</port>
</replica>
</shard>
<shard> <!--集群的第二个分片-->
<internal_replication>true</internal_replication>
<replica> <!--该分片的第一个副本-->
<host>slave02</host>
<port>9000</port>
</replica>
</shard>
</gmall_cluster>
</remote_servers>分片数设置
<macros>
<shard>01</shard> <!--不同机器放的分片数不一样-->
<replica>rep_1_1</replica> <!--不同机器放的副本数不一样-->
</macros>- 将配置同步到其他机器
- 分别调整slave01,slave02的macros配置
# slave01
[root@slave01 clickhouse-server]# vim /etc/clickhouse-server/config.xml
<macros>
<shard>01</shard>
<replica>rep_1_2</replica>
</macros>
# slave02
[root@slave02 ~]# vim /etc/clickhouse-server/config.xml
<macros>
<shard>02</shard>
<replica>rep_2_1</replica>
</macros>- 重启三台服务器上的ClickHouse服务
7.2 master01上执行建表语句
- 在master01上建表
- 会自动同步到slave01和slave02上
- 集群名字要和配置文件中的一致
- 分片和副本名称从配置文件的宏定义中获取
CREATE TABLE st_order_mt ON CLUSTER gmall_cluster
(
`id` UInt32,
`sku_id` String,
`total_amount` Decimal(16, 2),
`create_time` Datetime
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/st_order_mt', '{replica}')
PARTITION BY toYYYYMMDD(create_time)
PRIMARY KEY id
ORDER BY (id, sku_id)
Query id: abc33f5c-6338-4872-9b51-375e5a343475
┌─host─────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ master01 │ 9000 │ 0 │ │ 2 │ 0 │
│ slave01 │ 9000 │ 0 │ │ 1 │ 0 │
│ slave02 │ 9000 │ 0 │ │ 0 │ 0 │
└──────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘
3 rows in set. Elapsed: 0.292 sec.- 可以在另外两台机器查看表是否创建成功
7.3 在 master01 上创建 Distribute 分布式表
-- Distributed(集群名称，库名，本地表名，分片键)
CREATE TABLE st_order_mt_all2 ON CLUSTER gmall_cluster
(
`id` UInt32,
`sku_id` String,
`total_amount` Decimal(16, 2),
`create_time` Datetime
)
ENGINE = Distributed(gmall_cluster, default, st_order_mt, hiveHash(sku_id))
Query id: 0942014a-64e7-4a41-b45a-7745b2c7a951
┌─host─────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ master01 │ 9000 │ 0 │ │ 2 │ 0 │
│ slave01 │ 9000 │ 0 │ │ 1 │ 0 │
│ slave02 │ 9000 │ 0 │ │ 0 │ 0 │
└──────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘
3 rows in set. Elapsed: 0.136 sec.7.4 插入数据并查询
- 插入数据
insert into st_order_mt_all2 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');- 查询数据
-- 分布式表
master01 :) SELECT * FROM st_order_mt_all2;
SELECT *
FROM st_order_mt_all2
Query id: a1d31358-da99-41b8-9460-8124b14c6b95
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 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 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 201 │ sku_001 │ 1000.00 │ 2020-06-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 205 │ sku_003 │ 600.00 │ 2020-06-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
-- 本地表 slave01
slave01 :) select * from st_order_mt;
SELECT *
FROM st_order_mt
Query id: eb607d3f-6b88-44bc-8813-18bacadb94bf
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 202 │ sku_002 │ 2000.00 │ 2020-05-31 21:00:00 │
│ 203 │ sku_004 │ 2500.00 │ 2020-05-31 21:00:00 │
│ 204 │ sku_002 │ 2000.00 │ 2020-05-31 21:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
-- 本地表 slave02
SELECT *
FROM st_order_mt
Query id: 24e84ec5-ee01-481a-9200-c8272a68c80d
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 205 │ sku_003 │ 600.00 │ 2020-06-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 201 │ sku_001 │ 1000.00 │ 2020-06-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘八、Explain 查看执行计划
... todo
九、建表优化
9.1 数据类型
- 时间字段的类型
建表时能用数值型或日期时间型表示的字段就不要用字符串，全 String 类型在以 Hive 为中心的数仓建设中常见，但 ClickHouse 环境不应受此影响。 虽然 ClickHouse 底层将 DateTime 存储为时间戳 Long 类型，但不建议存储 Long 类型， 因为 DateTime 不需要经过函数转换处理，执行效率高、可读性好。
create table t_type2(
id UInt32,
sku_id String,
total_amount Decimal(16,2) , create_time Int32
) engine =ReplacingMergeTree(create_time)
partition by toYYYYMMDD(toDate(create_time)) –-需要转换一次，否则报错
primary key (id)
order by (id, sku_id);- 空值存储类型
官方已经指出Nullable类型几乎总是会拖累性能，因为存储Nullable列时需要创建一个 额外的文件来存储 NULL 的标记，并且 Nullable 列无法被索引。因此除非极特殊情况，应直 接使用字段默认值表示空，或者自行指定一个在业务中无意义的值(例如用-1 表示没有商品 ID)。 官网说明:https://clickhouse.tech/docs/zh/sql-reference/data-types/nullable/
CREATE TABLE t_null(x Int8, y Nullable(Int8)) ENGINE TinyLog;
INSERT INTO t_null VALUES (1, NULL), (2, 3);
SELECT x + y FROM t_null;9.2 分区和索引
分区粒度根据业务特点决定，不宜过粗或过细。一般选择按天分区，也可以指定为 Tuple()， 以单表一亿数据为例，分区大小控制在 10-30 个为最佳。 必须指定索引列，ClickHouse 中的索引列即排序列，通过 order by 指定，一般在查询条 件中经常被用来充当筛选条件的属性被纳入进来;可以是单一维度，也可以是组合维度的索 引;通常需要满足高级列在前、查询频率大的在前原则;还有基数特别大的不适合做索引列， 如用户表的 userid 字段;通常筛选后的数据满足在百万以内为最佳。
-- 比如官方案例的 hits_v1 表
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, EventDate, intHash32(UserID))
-- visits_v1 表
PARTITION BY toYYYYMM(StartDate)
ORDER BY (CounterID, StartDate, intHash32(UserID), VisitID)9.3 表参数
Index_granularity 是用来控制索引粒度的，默认是 8192，如非必须不建议调整。 如果表中不是必须保留全量历史数据，建议指定 TTL(生存时间值)，可以免去手动过期 历史数据的麻烦，TTL 也可以通过 alter table 语句随时修改。(参考前面TTL)
9.4 写入和删除优化
- 尽量不要执行单条或小批量删除和插入操作，这样会产生小分区文件，给后台 Merge 任务带来巨大压力
- 不要一次写入太多分区，或数据写入太快，数据写入太快会导致 Merge 速度跟不 上而报错，一般建议每秒钟发起 2-3 次写入操作，每次操作写入 2w~5w 条数据(依服务器 性能而定)
写入过快报错，报错信息:
1. Code: 252, e.displayText() = DB::Exception: Too many parts(304).
Merges are processing significantly slower than inserts
2. Code: 241, e.displayText() = DB::Exception: Memory limit (for query)
exceeded:would use 9.37 GiB (attempt to allocate chunk of 301989888
bytes), maximum: 9.31 GiB处理方式：
“ Too many parts 处理 ” :使用 WAL 预写日志，提高写入性能。 in_memory_parts_enable_wal 默认为 true 在服务器内存充裕的情况下增加内存配额，一般通过 max_memory_usage 来实现 在服务器内存不充裕的情况下，建议将超出部分内容分配到系统硬盘上，但会降低执行 速度，一般通过 max_bytes_before_external_group_by、max_bytes_before_external_sort 参数 来实现。
9.5 常见配置
... todo
十、ClickHouse 语法优化规则
ClickHouse 的 SQL 优化规则是基于RBO(RuleBasedOptimization)，下面是一些优化规则
10.1 准备测试用表
- 上传官方的数据集 将 visits_v1.tar 和 hits_v1.tar 上传到虚拟机，解压到 clickhouse 数据路径下
// 解压到 clickhouse 数据路径
sudo tar -xvf hits_v1.tar -C /var/lib/clickhouse
sudo tar -xvf visits_v1.tar -C /var/lib/clickhouse
//修改所属用户
sudo chown -R clickhouse:clickhouse /var/lib/clickhouse/data/datasets
sudo chown -R clickhouse:clickhouse /var/lib/clickhouse/metadata/datasets- 重启 clickhouse-server
sudo clickhouse restart- 执行查询
clickhouse-client --query "SELECT COUNT(*) FROM datasets.hits_v1"
clickhouse-client --query "SELECT COUNT(*) FROM datasets.visits_v1"注意:官方的 tar 包，包含了建库、建表语句、数据内容，这种方式不需要手动建库、建表，最方便。 hits_v1 表有 130 多个字段，880 多万条数据 visits_v1 表有 180 多个字段，160 多万条数据
... todo
十一、查询优化
... todo
十二、数据一致性
我们在使用 ReplacingMergeTree、SummingMergeTree 这类表引擎的时候，会出现短暂 数据不一致的情况。 在某些对一致性非常敏感的场景，通常有以下几种解决方案。
12.1 准备测试表和数据
- 创建表
-- user_id 是数据去重更新的标识;
-- create_time 是版本号字段，每组数据中 create_time 最大的一行表示最新的数据;
-- deleted 是自定的一个标记位，比如 0 代表未删除，1 代表删除数据。
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;
1050000012.2 手动 OPTIMIZE
在写入数据后，立刻执行 OPTIMIZE 强制触发新写入分区的合并动作。
OPTIMIZE TABLE test_a FINAL;12.3 通过 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_time 得到修改后的 score 字段值。- 创建视图，方便测试
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;12.4 通过 FINAL 查询
在查询语句后增加 FINAL 修饰符，这样在查询的过程中将会执行 Merge 的特殊逻辑(例 如数据去重，预聚合等)。 但是这种方法在早期版本基本没有人使用，因为在增加 FINAL 之后，我们的查询将会变 成一个单线程的执行过程，查询速度非常慢。 在 v20.5.2.7-stable 版本中，FINAL 查询支持多线程执行，并且可以通过 max_final_threads 参数控制单个查询的线程数。但是目前读取 part 部分的动作依然是串行的。 FINAL 查询最终的性能和很多因素相关，列字段的大小、分区的数量等等都会影响到最 终的查询时间，所以还要结合实际场景取舍。 参考链接:https://github.com/ClickHouse/ClickHouse/pull/10463
... todo
十三、物化视图
ClickHouse 的物化视图是一种查询结果的持久化，它确实是给我们带来了查询效率的提 升。用户查起来跟表没有区别，它就是一张表，它也像是一张时刻在预计算的表，创建的过 程它是用了一个特殊引擎，加上后来 as select，就是 create 一个 table as select 的写法。 “查询结果集”的范围很宽泛，可以是基础表中部分数据的一份简单拷贝，也可以是多 表 join 之后产生的结果或其子集，或者原始数据的聚合指标等等。所以，物化视图不会随着 基础表的变化而变化，所以它也称为快照(snapshot)
13.1 概念
物化视图与普通视图的区别
普通视图不保存数据，保存的仅仅是查询语句，查询的时候还是从原表读取数据，可以 将普通视图理解为是个子查询。物化视图则是把查询的结果根据相应的引擎存入到了磁盘 或内存中，对数据重新进行了组织，你可以理解物化视图是完全的一张新表。
优缺点
优点:查询速度快，要是把物化视图这些规则全部写好，它比原数据查询快了很多，总 的行数少了，因为都预计算好了。
缺点:它的本质是一个流式数据的使用场景，是累加式的技术，所以要用历史数据做去 重、去核这样的分析，在物化视图里面是不太好用的。在某些场景的使用也是有限的。而且 如果一张表加了好多物化视图，在写这张表的时候，就会消耗很多机器的资源，比如数据带 宽占满、存储一下子增加了很多。
基本语法
也是 create 语法，会创建一个隐藏的目标表来保存视图数据。也可以 TO 表名，保存到 一张显式的表。没有加 TO 表名，表名默认就是 .inner.物化视图名
CREATE [MATERIALIZED] VIEW [IF NOT EXISTS] [db.]table_name [TO[db.]name]
[ENGINE = engine] [POPULATE] AS SELECT ...- 创建物化视图的限制
(1)必须指定物化视图的 engine 用于数据存储
(2)TO [db].[table]语法的时候，不得使用 POPULATE。
(3)查询语句(select)可以包含下面的子句: DISTINCT, GROUP BY, ORDER BY, LIMIT...
(4)物化视图的 alter 操作有些限制，操作起来不大方便。
(5)若物化视图的定义使用了 TO [db.]name 子语句，则可以将目标表的视图卸载DETACH 再装载 ATTACH - 物化视图的数据更新
(1)物化视图创建好之后，若源表被写入新数据则物化视图也会同步更新
(2)POPULATE 关键字决定了物化视图的更新策略:
◼ 若有 POPULATE 则在创建视图的过程会将源表已经存在的数据一并导入，类似于 create table ... as
◼ 若无 POPULATE 则物化视图在创建之后没有数据，只会在创建只有同步之后写入 源表的数据
◼ clickhouse 官方并不推荐使用 POPULATE，因为在创建物化视图的过程中同时写入 的数据不能被插入物化视图。
(3)物化视图不支持同步删除，若源表的数据不存在(删除了)则物化视图的数据仍然保留
(4)物化视图是一种特殊的数据表，可以用 show tables 查看 (5)物化视图数据的删除:
(6)物化视图的删除:
案例实操
对于一些确定的数据模型，可将统计指标通过物化视图的方式进行构建，这样可避免查询时重复计算的过程，物化视图会在有新数据插入时进行更新。
准备测试用表和数据
- 建表
#建表语句
CREATE TABLE hits_test (
EventDate Date,
CounterID UInt32,
UserID UInt64,
URL String,
Income UInt8
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, EventDate, intHash32(UserID))
SAMPLE BY intHash32(UserID)
SETTINGS index_granularity = 8192- 导入一些数据
INSERT INTO hits_test
SELECT
EventDate,
CounterID,
UserID,
URL,
Income
FROM datasets.hits_v1
limit 10000;创建物化视图
... todo
十四、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 机制的业务数据库实时同步功能。
... todo
十五、监控
15.1 Prometheus 安装配置
15.2 Grafana 安装配置
参考 Grafana
15.3 ClickHouse配置
- 修改配置文件，添加以下内容
vim /etc/clickhouse-server/config.xml
<prometheus>
<endpoint>/metrics</endpoint>
<port>9363</port>
<metrics>true</metrics>
<events>true</events>
<asynchronous_metrics>true</asynchronous_metrics>
<status_info>true</status_info>
</prometheus>如果有多个 CH 节点，分发配置。 2) 重启 ClickHouse
sudo clickhouse restart- 访问 Web 查看
浏览器打开: http://master01:9363/metrics 看到信息说明 ClickHouse 开启 Metrics 服务成功。
15.4 Grafana添加Prometheus数据源
- 添加监控
参考
十六、备份恢复
...todo