# ClickHouse

# 一、安装

# 1.1 系统准备

系统:CentOS 7

192.168.235.5 master01
192.168.235.6 slave01
192.168.235.7 slave02

  1. 关闭防火墙
  2. 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
1
2
3
4
5
6
7
8
9
10
  1. 同步配置到其他机器
  2. 查看是否生效
[tpxcer@master01 root]$ ulimit -a
open files                      (-n) 65536
max user processes              (-u) 131072
1
2
3
  1. 安装依赖
sudo yum install -y libtool
sudo yum install -y *unixODBC* 
1
2
  1. CentOS 取消 SELINUX
[tpxcer@master01 root]$ sudo vim /etc/selinux/config
SELINUX=disabled 
1
2

# 1.2 ClickHouse 单机安装

  1. 下载离线的安装包

官方 (opens new window)

 clickhouse-client-21.8.5.7-2.noarch.rpm
 clickhouse-common-static-21.8.5.7-2.x86_64.rpm
 clickhouse-common-static-dbg-21.8.5.7-2.x86_64.rpm
 clickhouse-server-21.8.5.7-2.noarch.rpm
1
2
3
4
  1. 安装包

分别安装到所有机器上

sudo rpm -ivh *.rpm
rpm -qa | grep clickhouse
1
2
  1. 修改配置使其可以被远程访问

记得分发文件

[tpxcer@master01 Downloads]$  sudo vim /etc/clickhouse-server/config.xml
<listen_host>::</listen_host>
1
2
  1. 启动服务
sudo systemctl status clickhouse-server 
1

用clickhouse自带的命令启动

sudo clickhouse restart
1
  1. 连接clickhouse
clickhouse-client -m

# 远程访问
clickhouse-client -m -h hostname
1
2
3
4
  1. 直接查询数据
clickhouse-client --query "show databases;"
1

# 二、数据类型

官方文档:https://clickhouse.tech/docs/zh/sql-reference/data-types/

# 2.1 枚举类型

包括 Enum8 和 Enum16 类型。
Enum 保存 'string'= integer 的对应关系。 Enum8 用 'String'= Int8 对描述。
Enum16 用 'String'= Int16 对描述。

  1. 用法演示

创建一个带有一个枚举 Enum8('hello' = 1, 'world' = 2) 类型的列

CREATE TABLE t_enum
(
   x Enum8('hello' = 1, 'world' = 2)
)
ENGINE = TinyLog;
1
2
3
4
5
  1. 这个 x 列只能存储类型定义中列出的值:'hello'或'world'
INSERT INTO t_enum VALUES ('hello'), ('world'), ('hello');
1
  1. 如果需要看到对应行的数值,则必须将 Enum 值转换为整数类型
SELECT CAST(x, 'Int8') FROM t_enum;
1

# 2.2 数组

Array(T):由 T 类型元素组成的数组。 T 可以是任意类型,包含数组类型。 但不推荐使用多维数组,ClickHouse 对多维数组 的支持有限。例如,不能在 MergeTree 表中存储多维数组。

  1. 创建数组方式 1,使用 array 函数
SELECT array(1, 2) AS x, toTypeName(x) 
1
  1. 创建数组方式 2:使用方括号
SELECT [1, 2] AS x, toTypeName(x);
1

# 三、表引擎

# 3.1 TinyLog

以列文件的形式保存在磁盘上,不支持索引,没有并发控制。一般保存少量数据的小表, 生产环境上作用有限。可以用于平时练习测试用。

create table t_tinylog ( id String, name String) engine=TinyLog;
1

# 3.2 Memory

内存引擎,数据以未压缩的原始形式直接保存在内存当中,服务器重启数据就会消失。 读写操作不会相互阻塞,不支持索引。简单查询下有非常非常高的性能表现(超过 10G/s)。 一般用到它的地方不多,除了用来测试,就是在需要非常高的性能,同时数据量又不太大(上限大概 1 亿行)的场景

# 3.3 MergeTree

ClickHouse 中最强大的表引擎当属 MergeTree(合并树)引擎及该系列(*MergeTree) 中的其他引擎,支持索引和分区,地位可以相当于 innodb 之于 Mysql。而且基于 MergeTree, 还衍生除了很多小弟,也是非常有特色的引擎。

  1. 建表语句
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);
1
2
3
4
5
6
7
8
  1. 插入数据
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');
1
2
3
4
5
6
7

MergeTree 其实还有很多参数(绝大多数用默认值即可),但是三个参数是更加重要的, 也涉及了关于 MergeTree 的很多概念。

# 3.3.1 partition by 分区(可选)

  1. 作用

分区的目的主要是降低扫描的范围,优化查询速度

  1. 分区目录 MergeTree 是以列文件+索引文件+表定义文件组成的,但是如果设定了分区那么这些文件就会保存到不同的分区目录中。

  2. 并行 分区后,面对涉及跨分区的查询统计,ClickHouse 会以分区为单位并行处理。

  3. 数据写入与分区合并

任何一个批次的数据写入都会产生一个临时分区,不会纳入任何一个已有的分区。写入后的某个时刻(大概 10-15 分钟后),ClickHouse 会自动执行合并操作(等不及也可以手动 通过 optimize 执行),把临时分区的数据,合并到已有分区中。

optimize table xxxx final;
1

例如再次插入数据

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');
1
2
3
4
5
6
7

马上查询发现并未并入现有分区

┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │      1000.002020-06-01 12:00:00 │
│ 102 │ sku_002 │      2000.002020-06-01 11:00:00 │
│ 102 │ sku_002 │      2000.002020-06-01 13:00:00 │
│ 102 │ sku_002 │     12000.002020-06-01 13:00:00 │
│ 102 │ sku_004 │      2500.002020-06-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │       600.002020-06-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │      1000.002020-06-01 12:00:00 │
│ 102 │ sku_002 │      2000.002020-06-01 11:00:00 │
│ 102 │ sku_002 │      2000.002020-06-01 13:00:00 │
│ 102 │ sku_002 │     12000.002020-06-01 13:00:00 │
│ 102 │ sku_004 │      2500.002020-06-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │       600.002020-06-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

手动optimized

optimize table t_order_mt final;
1

查看结果

┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │       600.002020-06-02 12:00:00 │
│ 102 │ sku_002 │       600.002020-06-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │      1000.002020-06-01 12:00:00 │
│ 101 │ sku_001 │      1000.002020-06-01 12:00:00 │
│ 102 │ sku_002 │      2000.002020-06-01 11:00:00 │
│ 102 │ sku_002 │      2000.002020-06-01 13:00:00 │
│ 102 │ sku_002 │     12000.002020-06-01 13:00:00 │
│ 102 │ sku_002 │      2000.002020-06-01 11:00:00 │
│ 102 │ sku_002 │      2000.002020-06-01 13:00:00 │
│ 102 │ sku_002 │     12000.002020-06-01 13:00:00 │
│ 102 │ sku_004 │      2500.002020-06-01 12:00:00 │
│ 102 │ sku_004 │      2500.002020-06-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
  1. 指定分区合并
optimize table t_order_mt partition '20200621' final;
1

# 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 二级索引

  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);
1
2
3
4
5
6
7
8
9
10

其中 GRANULARITY N 是设定二级索引对于一级索引粒度的粒度。

  1. 插入数据
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');
1
  1. 执行并查看查看日志
clickhouse-client --send_logs_level=trace<<<'select * from t_order_mt2 where total_amount > toDecimal32(900., 2)';
1
  1. 表的文件目录会多出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.mrk3
1
2
3
4

# 3.3.5 TTL

TTL 即 Time To Live,MergeTree 提供了可以管理数据表或者列的生命周期的功能。

  1. 列级别 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);
1
2
3
4
5
6
7
8

插入数据(注意:根据实际时间改变)

 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');
1

手动合并,查看效果 到期后,指定的字段数据归0

  1. 表级 TTL

下面的这条语句是数据会在 create_time 之后 10 秒丢失

alter table t_order_mt3 MODIFY TTL create_time + INTERVAL 10 SECOND;
1

涉及判断的字段必须是 Date 或者 Datetime 类型,推荐使用分区的日期字段。 能够使用的时间周期

# 3.4 ReplacingMergeTree

ReplacingMergeTree 是 MergeTree 的一个变种,它存储特性完全继承 MergeTree,只是 多了一个去重的功能。 尽管 MergeTree 可以设置主键,但是 primary key 其实没有唯一约束 的功能。如果你想处理掉重复的数据,可以借助这个 ReplacingMergeTree。

  1. 去重时机

数据的去重只会在合并的过程中出现。合并会在未知的时间在后台进行,所以你无法预 先作出计划。有一些数据可能仍未被处理。

  1. 去重范围

如果表经过了分区,去重只会在分区内部进行去重,不能执行跨分区的去重。 所以 ReplacingMergeTree 能力有限, ReplacingMergeTree 适用于在后台清除重复的数 据以节省空间,但是它不保证没有重复的数据出现。

  1. 案例演示

(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);
1
2
3
4
5
6
7
8

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');
1

(3) 查看合并后的数据

SELECT *
FROM t_order_rmt

Query id: 53c3311e-35bb-4ba3-ac25-f6671694bb93

┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │       600.002020-06-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │      1000.002020-06-01 12:00:00 │
│ 102 │ sku_002 │     12000.002020-06-01 13:00:00 │
│ 102 │ sku_004 │      2500.002020-06-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
1
2
3
4
5
6
7
8
9
10
11
12
13

(4) 得出结论

  • 实际上是使用 order by 字段作为唯一键
  • 去重不能跨分区
  • 只有同一批插入(新版本)或合并分区时才会进行去重
  • 认定重复的数据保留,版本字段值最大的
  • 如果版本字段相同则按插入顺序保留最后一笔

# 3.5 SummingMergeTree

对于不查询明细,只关心以维度进行汇总聚合结果的场景。如果只使用普通的 MergeTree 的话,无论是存储空间的开销,还是查询时临时聚合的开销都比较大。 ClickHouse 为了这种场景,提供了一种能够“预聚合”的引擎 SummingMergeTree

  1. 创建表
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 );
1
2
3
4
5
6
7
8
9
  1. 插入数据
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');
1
  1. 查询数据
SELECT *
FROM t_order_smt

Query id: 0bec1899-c5db-4809-acfd-18522a57cb21

┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │       600.002020-06-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │      1000.002020-06-01 12:00:00 │
│ 102 │ sku_002 │     16000.002020-06-01 11:00:00 │
│ 102 │ sku_004 │      2500.002020-06-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
1
2
3
4
5
6
7
8
9
10
11
12
13
  1. 通过结果可以得到以下结论
  • 以SummingMergeTree()中指定的列作为汇总数据列
  • 可以填写多列必须数字列,如果不填,以所有非维度列且为数字列的字段为汇总数据列
  • 以 order by 的列为准,作为维度列
  • 其他的列按插入顺序保留第一行
  • 不在一个分区的数据不会被聚合
  • 只有在同一批次插入(新版本)或分片合并时才会进行聚合

# 四、文件目录

# 4.1 ClickHouse各文件目录

    bin/    ===>  /usr/bin/ 
    conf/   ===>  /etc/clickhouse-server/
    lib/    ===>  /var/lib/clickhouse 
    log/    ===>  /var/log/clickhouse-server
1
2
3
4

# 4.2 表目录文件

  1. 表schema文件目录
/var/lib/clickhouse/metadata
1
  1. 数据文件目录
/var/lib/clickhouse/data
1
  1. 具体数据目录
[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值。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

# 五、SQL

# 5.1 Update 和 Delete

ClickHouse 提供了 Delete 和 Update 的能力,这类操作被称为 Mutation 查询,它可以看 做 Alter 的一种。 虽然可以实现修改和删除,但是和一般的 OLTP 数据库不一样,Mutation 语句是一种很 “重”的操作,而且不支持事务。 “重”的原因主要是每次修改或者删除都会导致放弃目标数据的原有分区,重建新分区。 所以尽量做批量的变更,不要进行频繁小数据的操作。

  1. 删除操作
alter table t_order_smt delete where sku_id ='sku_001';
1
  1. 修改操作
alter table t_order_smt update total_amount=toDecimal32(2000.00,2) where id=102;
1

由于操作比较“重”,所以 Mutation 语句分两步执行,同步执行的部分其实只是进行 新增数据新增分区和并把旧分区打上逻辑上的失效标记。直到触发分区合并的时候,才会删 除旧数据释放磁盘空间,一般不会开放这样的功能给用户,由管理员完成。

# 5.2 alter 操作

  1. 新增字段
alter table tableName add column newcolname String after col1;
1
  1. 修改字段类型
alter table tableName modify column newcolname String;
1
  1. 删除字段
alter table tableName drop column newcolname;
1

# 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
1
2
3

更多支持格式参照: https://clickhouse.tech/docs/en/interfaces/formats/

# 六、副本

副本的目的主要是保障数据的高可用性,即使一台 ClickHouse 节点宕机,那么也可以从 其他服务器获得相同的数据。 https://clickhouse.tech/docs/en/engines/table-engines/mergetree-family/replication/

  1. 启动zookeeper集群
  2. 修改/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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
  1. 同步文件
[root@master01 clickhouse-server]# xsync config.xml
1
  1. 重启服务
  2. 分别建表

副本只能同步数据,不能同步表结构,所以我们需要在每台机器上自己手动建表

-- 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 即可。
-- 第二个参数是副本名称,相同的分片副本名称不能相同。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
  1. 插入数据
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');
1
2
3
4
5
6
  1. 测试

此时在两台机器上查询都可以获取数据select * from t_order_rep2

# 七、分片集群

副本虽然能够提高数据的可用性,降低丢失风险,但是每台服务器实际上必须容纳全量 数据,对数据的横向扩容没有解决。 要解决数据水平切分的问题,需要引入分片的概念。通过分片把一份完整的数据进行切 分,不同的分片分布到不同的节点上,再通过 Distributed 表引擎把数据拼接起来一同使用。 Distributed 表引擎本身不存储数据,有点类似于 MyCat 之于 MySql,成为一种中间件,通过分布式逻辑表来写入、分发、路由来操作多台节点不同分片的分布式数据。

三台机器 master01,slave01,slave02

# 7.1 集群及副本规划(2 个分片,只有第一个分片有副本)

  1. 编辑/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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

分片数设置

    <macros>
        <shard>01</shard> <!--不同机器放的分片数不一样-->
        <replica>rep_1_1</replica> <!--不同机器放的副本数不一样-->
    </macros>
1
2
3
4
  1. 将配置同步到其他机器
  2. 分别调整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>
1
2
3
4
5
6
7
8
9
10
11
12
13
  1. 重启三台服务器上的ClickHouse服务

# 7.2 master01上执行建表语句

  1. 在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 │ 90000 │       │                   20 │
│ slave01  │ 90000 │       │                   10 │
│ slave02  │ 90000 │       │                   00 │
└──────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘

3 rows in set. Elapsed: 0.292 sec.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
  1. 可以在另外两台机器查看表是否创建成功

# 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 │ 90000 │       │                   20 │
│ slave01  │ 90000 │       │                   10 │
│ slave02  │ 90000 │       │                   00 │
└──────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘

3 rows in set. Elapsed: 0.136 sec.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

# 7.4 插入数据并查询

  1. 插入数据
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');
1
2
3
4
5
6
  1. 查询数据
-- 分布式表
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.002020-06-01 12:00:00 │
│ 203 │ sku_004 │      2500.002020-06-01 12:00:00 │
│ 204 │ sku_002 │      2000.002020-06-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 201 │ sku_001 │      1000.002020-06-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 205 │ sku_003 │       600.002020-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.002020-05-31 21:00:00 │
│ 203 │ sku_004 │      2500.002020-05-31 21:00:00 │
│ 204 │ sku_002 │      2000.002020-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.002020-06-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 201 │ sku_001 │      1000.002020-06-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

# 八、Explain 查看执行计划

... todo

# 九、建表优化

# 9.1 数据类型

  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);
1
2
3
4
5
6
7
8
  1. 空值存储类型

官方已经指出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;
1
2
3

# 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)
1
2
3
4
5
6
7

# 9.3 表参数

Index_granularity 是用来控制索引粒度的,默认是 8192,如非必须不建议调整。 如果表中不是必须保留全量历史数据,建议指定 TTL(生存时间值),可以免去手动过期 历史数据的麻烦,TTL 也可以通过 alter table 语句随时修改。(参考前面TTL)

# 9.4 写入和删除优化

  1. 尽量不要执行单条或小批量删除和插入操作,这样会产生小分区文件,给后台 Merge 任务带来巨大压力
  2. 不要一次写入太多分区,或数据写入太快,数据写入太快会导致 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
1
2
3
4
5
6

处理方式:

“ 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 准备测试用表

  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
1
2
3
4
5
6
  1. 重启 clickhouse-server
sudo clickhouse restart
1
  1. 执行查询
clickhouse-client --query "SELECT COUNT(*) FROM datasets.hits_v1"
clickhouse-client --query "SELECT COUNT(*) FROM datasets.visits_v1"
1
2

注意:官方的 tar 包,包含了建库、建表语句、数据内容,这种方式不需要手动建库、建表,最方便。 hits_v1 表有 130 多个字段,880 多万条数据 visits_v1 表有 180 多个字段,160 多万条数据

... todo

# 十一、查询优化

... todo

# 十二、数据一致性

我们在使用 ReplacingMergeTree、SummingMergeTree 这类表引擎的时候,会出现短暂 数据不一致的情况。 在某些对一致性非常敏感的场景,通常有以下几种解决方案。

# 12.1 准备测试表和数据

  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;
1
2
3
4
5
6
7
8
9
10
  1. 写入 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); 
1
2
3
4
5
  1. 修改前 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)
1
2
3
4
5
6
  1. 统计总数
SELECT COUNT() FROM test_a;
10500000 
1
2

# 12.2 手动 OPTIMIZE

在写入数据后,立刻执行 OPTIMIZE 强制触发新写入分区的合并动作。

OPTIMIZE TABLE test_a FINAL;
1

# 12.3 通过 Group by 去重

  1. 执行去重的查询
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 字段值。
1
2
3
4
5
6
7
8
9
  1. 创建视图,方便测试
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;
1
2
3
4
5
6
7
8
9
  1. 插入重复数据,再次查询
#再次插入一条数据
INSERT INTO TABLE test_a(user_id,score,create_time) VALUES(0,'AAAA',now())
#再次查询
SELECT *
FROM view_test_a WHERE user_id = 0;
1
2
3
4
5
  1. 删除数据测试
#再次插入一条标记为删除的数据
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;
1
2
3
4
5
6

# 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
2
  1. 创建物化视图的限制
    (1)必须指定物化视图的 engine 用于数据存储
    (2)TO [db].[table]语法的时候,不得使用 POPULATE。
    (3)查询语句(select)可以包含下面的子句: DISTINCT, GROUP BY, ORDER BY, LIMIT...
    (4)物化视图的 alter 操作有些限制,操作起来不大方便。
    (5)若物化视图的定义使用了 TO [db.]name 子语句,则可以将目标表的视图卸载DETACH 再装载 ATTACH
  2. 物化视图的数据更新
    (1)物化视图创建好之后,若源表被写入新数据则物化视图也会同步更新
    (2)POPULATE 关键字决定了物化视图的更新策略:
    ◼ 若有 POPULATE 则在创建视图的过程会将源表已经存在的数据一并导入,类似于 create table ... as
    ◼ 若无 POPULATE 则物化视图在创建之后没有数据,只会在创建只有同步之后写入 源表的数据
    ◼ clickhouse 官方并不推荐使用 POPULATE,因为在创建物化视图的过程中同时写入 的数据不能被插入物化视图。
    (3)物化视图不支持同步删除,若源表的数据不存在(删除了)则物化视图的数据仍然保留
    (4)物化视图是一种特殊的数据表,可以用 show tables 查看 (5)物化视图数据的删除:
    (6)物化视图的删除:

# 案例实操

对于一些确定的数据模型,可将统计指标通过物化视图的方式进行构建,这样可避免查询时重复计算的过程,物化视图会在有新数据插入时进行更新。

准备测试用表和数据

  1. 建表
#建表语句
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 
1
2
3
4
5
6
7
8
9
10
11
12
13
  1. 导入一些数据
INSERT INTO hits_test
   SELECT
   EventDate,
   CounterID,
   UserID,
   URL,
   Income
FROM datasets.hits_v1
limit 10000;
1
2
3
4
5
6
7
8
9

创建物化视图

... 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 安装配置

参考 添加ClickHouse监控

# 15.2 Grafana 安装配置

参考 Grafana

# 15.3 ClickHouse配置

  1. 修改配置文件,添加以下内容
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>
1
2
3
4
5
6
7
8
9

如果有多个 CH 节点,分发配置。 2) 重启 ClickHouse

sudo clickhouse restart
1
  1. 访问 Web 查看

浏览器打开: http://master01:9363/metrics 看到信息说明 ClickHouse 开启 Metrics 服务成功。

# 15.4 Grafana添加Prometheus数据源

参考 Prometheus 数据源

  1. 添加监控

参考

# 十六、备份恢复

...todo

更新时间: 9/26/2021, 6:34:35 PM