ClickHouse基础入门篇

发表于 更新于 分类于

扩展知识面宽度

ClickHouse特点

列式存储

1
2
相对于行存,列存对于列的聚合计数求和等统计操作更优
某一列数据类型相同,数据存储可以进行数据压缩,提高缓存效果

DBMS的功能

1
覆盖标准SQL的大部分语法以及函数

多样化引擎

1
2
3
与MySQL类似,表级别存储引擎插件化,根据不同需求可以设定不同的存储引擎
例如:
    合并树,日志,接口和其他(四大类20多种引擎)

高吞吐写入能力

1
2
采用LSM Tree结构,数据写入后定期后台合并
所以CK在数据导入是全是顺序写入,写入后不可更改

数据分区与线程级并行

1
2
3
4
5
6
7
CK将数据划分多个Partition,每个Partition再进一步划分为多个Index Granularity(索引粒度)
通过多个CPU核心分别处理其中的一部分来实现并行数据处理
单条Query利用了整机所有CPU

缺点:
    对于大数据量查询能平行处理
    但是不利于并发多条查询,高QPS的查询业务,CK不是强项

性能对比

1
2
3
4
高QBS不适用
CK尽可能避免JOIN操作,尽可能作为一个宽表使用
CK的JOIN实现:
    将右表加载入内存,逐条进行匹配操作,没有大小表概念

CK安装

Mac单机版

1
2
3
4
5
6
7
8
9
10
11
12
git clone --recursive https://github.com/ClickHouse/ClickHouse.git

# 可能需要进行重复拉取
rm -rf contrib/datasketches-cpp
git submodule update --init --recursive

cd ClickHouse/build
cmake -DCMAKE_C_COMPILER=$(brew --prefix llvm)/bin/clang -DCMAKE_CXX_COMPILER=$(brew --prefix llvm)/bin/clang++ -DCMAKE_BUILD_TYPE=RelWithDebInfo ..
cmake --build . --config RelWithDebInfo
cd ../
./build/programs/clickhouse-server --config-file ./programs/server/config.xml
./build/programs/clickhouse-client

Linux离线单机

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 必要的rpm包
clickhouse-client-*.rpm
clickhouse-common-static-*.rpm
clickhouse-common-static-dbg-*.rpm
clickhouse-server-*.rpm

rpm -ivh *.rpm
# 可能需要配置密码
bin/  -->  /usr/bin
conf/ -->  /etc/clickhouse-server
lib/  -->  /var/lib/clickhouse
log/  -->  /var/log/clickhouse-server

cd /etc/clickhouse-server
    config.xml(服务配置)
    users.xml(参数配置)
vi config.xml
<listen_host>::</listen_host>

# 启动
sudo systemctl status clickhouse-server

表引擎

TinyLog

1
2
3
4
以列文件的形式存储在磁盘上,不支持索引,没有并发控制
只用于测试环境

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

Memory

1
2
基于内存,重启数据就会消失,不支持索引,简单查询性能极高
一般测试,或需要极高简单查询性能的场景

MergeTree

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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
合并树,支持索引与分区

示例
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);
注意: 这里的主键id是可以重复的,order by新版本可选的,没有设置的话会自动给你生成
主键和OrderBy必须有一个存在

MergeTree是以列文件+索引文件+表定义文件组成的,但是如果设定了分区那么这些文件就会保存到不同的分区目录中
以分区为单元做并行处理

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

---

# 数据目录
data/default/t_order_mt/20200601_1_1_0
bin文件 数据文件
mrk文件 标记文件
    标记文件在idx索引文件和bin数据文件之间起桥梁作用
    以mrk2结尾的文件,表示该表启用了自适应索引间隔
primary.idx文件 主键索引文件,加快查询效率
minmax_create_time.idx 分区键的最大最小值
checksums.txt 校验文件,校验各个文件的正确性,存放各个文件的size以及hash值

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
    合并的层级,被合并的次数
    合并次数越多,层级值越大

---

# 分区合并
再次插入上面数据信息,会发现数据目录变化为以下结构
20200601_1_1_0
20200601_3_3_0
20200602_2_2_0
20200602_4_4_0
进行合并操作
optimize table t_order_mt final;(全部分区合并)
optimize table t_order_mt partition '20200601' final;(指定分区合并)
注意: 合并之后原有数据目录并不会立即删除

---

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

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

GRANULARITY N是设定二级索引对于一级索引粒度的粒度
可以理解为一级索引有大量重复值,按这个total_amount进行跨步查询

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

---

# TTL
列级别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);

TTL设置字段不能是主键ID,必须是Datetime
insert into t_order_mt3 values (101,'sku_001',1000.00,now()),(102,'sku_001',2000.00,now()+interval 1 minute),(103,'sku_001',3000.00,now());

表级别TTL
alter table t_order_mt3 MODIFY TTL create_time + INTERVAL 10 SECOND;

# 这里有一些疑问没有解开,设置了TTL并没有生效
插入多条记录又有的可以生效,有的不行,很是奇怪
最终的现象就是最后一条插入数据ttl不生效,插入新的数据之后,旧数据ttl全部生效

ReplacingMergeTree

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
分区内进行重复数据合并
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');

SummingMergeTree

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
分区内聚合,同一批次插入或分片合并时聚合
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的列为准,作为维度列
其他的列按插入顺序保留第一行

副本

副本写入流程

1
2
3
4
client写入数据请求发送给clickhouse其中一个server-a
server-a提交写入日志,通过zk进行请求同步,其他server-b收到写入日志
server-b从server-a下载副本文件
这一块和大多数大数据框架类似

配置

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
方式一
clickhouse-server/config.d 目录下创建一个名为 metrika.xml的配置文件
<?xml version="1.0"?>
<yandex>
    <zookeeper-servers>
        <node index="1">
            <host>HOST1</host>
            <port>2181</port>
        </node>
        <node index="2">
            <host>HOST2</host>
            <port>2181</port>
        </node>
        <node index="3">
            <host>HOST3</host>
            <port>2181</port>
        </node>
    </zookeeper-servers>
</yandex>

修改config.xml
<zookeeper incl="zookeeper-servers" optional="true" />
<include_from>CK_PATH/clickhouse-server/config.d/metrika.xml</include_from>

方式二
直接在config.xml中指定<zookeeper>

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','rep_102')
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,sku_id);

ReplicatedMergeTree(分片ZK_PATH,副本名称<相同分片名称不能相同>)

注意:
副本只能同步数据,不能同步表结构,需要自己手动建表
并且建表表引擎需要选ReplicatedMergeTree(只支持MergeTree Family)

分片集群

写入读取流程

1
2
3
4
5
6
写入
    分片自身管理副本同步

读取
    优先选择errors_count小的副本
    errors_count相同的有随机,顺序,随机(优先第一顺位),host名称近似等4种选择方式

配置

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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
3分片2副本配置

配置的位置还是在之前的clickhouse-server/config.d/metrika.xml
也可以配置在config.xml的<remote_server>
<yandex>
    <remote_servers>
        <gmall_cluster> <!-- 集群名称--> 
            <shard> <!--集群的第一个分片-->
                <internal_replication>true</internal_replication>
                <!--该分片的第一个副本-->
                <replica> 
                    <host>HOST1</host>
                    <port>9000</port>
                </replica>
                <!--该分片的第二个副本-->
                <replica> 
                    <host>HOST2</host>
                    <port>9000</port>
                </replica>
            </shard>
            <shard> <!--集群的第二个分片--> 
                <internal_replication>true</internal_replication>
                <replica> <!--该分片的第一个副本-->
                    <host>HOST3</host>
                    <port>9000</port>
                </replica>
                <replica> <!--该分片的第二个副本-->
                    <host>HOST4</host>
                    <port>9000</port>
                </replica>
            </shard>
            <shard> <!--集群的第三个分片-->
                <internal_replication>true</internal_replication>
                <replica> <!--该分片的第一个副本-->
                    <host>HOST5</host>
                    <port>9000</port>
                </replica>
                <replica> <!--该分片的第二个副本-->
                    <host>HOST6</host>
                    <port>9000</port>
                </replica>
            </shard>
        </gmall_cluster>
    </remote_servers>
    
    <macros>
        <shard>01</shard> <!--不同机器放的分片数不一样-->
        <replica>rep_1_1</replica> <!--不同机器放的副本数不一样-->
    </macros>
</yandex>

本地表创建
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);
集群名字要和配置文件中的一致
分片和副本名称从配置文件的宏定义中获取

使用Distribute分布式表
create table st_order_mt_all 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));

Distributed(集群名称,库名,本地表名,分片键)
分片键必须是整型数字,所以用hiveHash函数转换,也可以 rand()