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

3. 同步配置到其他机器
4. 查看是否生效

[tpxcer@master01 root]$ ulimit -a
open files                      (-n) 65536
max user processes              (-u) 131072

5. 安装依赖

sudo yum install -y libtool
sudo yum install -y *unixODBC* 

6. CentOS 取消 SELINUX

[tpxcer@master01 root]$ sudo vim /etc/selinux/config
SELINUX=disabled 

1.2 ClickHouse 单机安装

1. 下载离线的安装包

官方安装文档 (opens new window) 离线包地址 (opens new window)

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

2. 安装包

分别安装到所有机器上

sudo rpm -ivh *.rpm
rpm -qa | grep clickhouse

3. 修改配置使其可以被远程访问

记得分发文件

[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>

4. 启动服务

-- 启动
sudo systemctl start clickhouse-server 
-- 查看状态
sudo systemctl status clickhouse-server 
-- 关闭自动启动
sudo systemctl disable clickhouse-server

用clickhouse自带的命令启动

sudo clickhouse restart

5. 连接clickhouse

clickhouse-client -m
clickhouse-client -m --ask-password

# 远程访问
clickhouse-client -m -h hostname

6. 直接查询数据

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 对描述。

1. 用法演示

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

CREATE TABLE t_enum
(
   x Enum8('hello' = 1, 'world' = 2)
)
ENGINE = TinyLog;

2. 这个 x 列只能存储类型定义中列出的值:'hello'或'world'

INSERT INTO t_enum VALUES ('hello'), ('world'), ('hello');

3. 如果需要看到对应行的数值，则必须将 Enum 值转换为整数类型

SELECT CAST(x, 'Int8') FROM t_enum;

2.2 数组

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

1. 创建数组方式 1，使用 array 函数

SELECT array(1, 2) AS x, toTypeName(x) 

2. 创建数组方式 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， 还衍生除了很多小弟，也是非常有特色的引擎。

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

2. 插入数据

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 分区(可选)

1. 作用

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

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

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

4. 数据写入与分区合并

任何一个批次的数据写入都会产生一个临时分区，不会纳入任何一个已有的分区。写入后的某个时刻(大概 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 │
└─────┴─────────┴──────────────┴─────────────────────┘

5. 指定分区合并

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

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

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

2. 插入数据

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

3. 执行并查看查看日志

clickhouse-client --send_logs_level=trace<<<'select * from t_order_mt2 where total_amount > toDecimal32(900., 2)';

4. 表的文件目录会多出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

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

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

 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

2. 表级 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。

1. 去重时机

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

2. 去重范围

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

3. 案例演示

(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

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

2. 插入数据

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

3. 查询数据

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 │
└─────┴─────────┴──────────────┴─────────────────────┘

4. 通过结果可以得到以下结论

• 以SummingMergeTree()中指定的列作为汇总数据列
• 可以填写多列必须数字列，如果不填，以所有非维度列且为数字列的字段为汇总数据列
• 以 order by 的列为准，作为维度列
• 其他的列按插入顺序保留第一行
• 不在一个分区的数据不会被聚合
• 只有在同一批次插入(新版本)或分片合并时才会进行聚合

5. 开发建议

设计聚合表的话，唯一键值、流水号可以去掉，所有字段全部是维度、度量或者时间戳。

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-server

4.2 表目录文件

1. 表schema文件目录

/var/lib/clickhouse/metadata

2. 数据文件目录

/var/lib/clickhouse/data

3. 具体数据目录

[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 语句是一种很 “重”的操作，而且不支持事务。 “重”的原因主要是每次修改或者删除都会导致放弃目标数据的原有分区，重建新分区。 所以尽量做批量的变更，不要进行频繁小数据的操作。

1. 删除操作

alter table t_order_smt delete where sku_id ='sku_001';

2. 修改操作

alter table t_order_smt update total_amount=toDecimal32(2000.00,2) where id=102;

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

5.2 alter 操作

1. 新增字段

alter table tableName add column newcolname String after col1;

2. 修改字段类型

alter table tableName modify column newcolname String;

3. 删除字段

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/

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>

3. 同步文件

[root@master01 clickhouse-server]# xsync config.xml

4. 重启服务
5. 分别建表

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

-- 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 即可。
-- 第二个参数是副本名称，相同的分片副本名称不能相同。

6. 插入数据

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

7. 测试

此时在两台机器上查询都可以获取数据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>

分片数设置

    <macros>
        <shard>01</shard> <!--不同机器放的分片数不一样-->
        <replica>rep_1_1</replica> <!--不同机器放的副本数不一样-->
    </macros>

2. 将配置同步到其他机器
3. 分别调整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>

4. 重启三台服务器上的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 │ 9000 │      0 │       │                   2 │                0 │
│ slave01  │ 9000 │      0 │       │                   1 │                0 │
│ slave02  │ 9000 │      0 │       │                   0 │                0 │
└──────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘

3 rows in set. Elapsed: 0.292 sec.

2. 可以在另外两台机器查看表是否创建成功

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 插入数据并查询

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

2. 查询数据

-- 分布式表
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 数据类型

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

2. 空值存储类型

官方已经指出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 写入和删除优化

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

处理方式：

“ 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

2. 重启 clickhouse-server

sudo clickhouse restart

3. 执行查询

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 准备测试表和数据

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;

2. 写入 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); 

3. 修改前 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)

4. 统计总数

SELECT COUNT() FROM test_a;
10500000 

12.2 手动 OPTIMIZE

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

OPTIMIZE TABLE test_a FINAL;

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 字段值。

2. 创建视图，方便测试

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;

3. 插入重复数据，再次查询

#再次插入一条数据
INSERT INTO TABLE test_a(user_id,score,create_time) VALUES(0,'AAAA',now())
#再次查询
SELECT *
FROM view_test_a WHERE user_id = 0;

4. 删除数据测试

#再次插入一条标记为删除的数据
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. 创建物化视图的限制
   (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 

2. 导入一些数据

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

参考 添加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>

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

sudo clickhouse restart

3. 访问 Web 查看

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

15.4 Grafana添加Prometheus数据源

参考 Prometheus 数据源

4. 添加监控

参考

十六、备份恢复

...todo