ck 官网: https://clickhouse.com/
docker 安装ck参考: https://hub.docker.com/r/clickhouse/clickhouse-server/
1. 简介
1. 简单介绍
ClickHouse 是俄罗斯的Yandex于2016 年开源的列式存储数据库(DBMS: Database Management System), 使用c++ 语言编写,主要用于在线分析处理查询(OLAP: Online Analytical Processing)。
ck覆盖了标准sql的大部分语法,包括DDM和DML,以及配套的各种函数,用户管理及权限管理,数据的备份与恢复。
ck和MySQL 类似,把表级的存储引擎插件化,根据表的不同需求可以设定不同的存储引擎。目前包括合并树、日志、接口等20多种。
2. 特点
- 列式存储
以下面的表格为例:
id | name | age |
1 | 张三 | 18 |
2 | 李四 | 17 |
3 | 王五 | 20 |
1》采用行式存储时,数据在磁盘的存储结构可能如下:
好处是查询某个人的所有属性时,可以通过一次磁盘查找加顺序读取就可以。但是想查所有人的年龄时,就需要不停的找,或者全表扫描才行。
2》采用列式存储时,数据在磁盘上的组织结构为:
这时想查所有人的年龄只需把年龄那一列查出来就可以了。
3》 列式存储优点
对于列的聚合、计数、求和等统计操作原因优于行式存储
由于某一列的数据类型是相同的,对数据存储上更容易压缩;节省空间,对cache 也有更好
4》 高吞吐写入能力
ck 采用类似LSMTree(Log Structured Merge Tree, 是一种分层,有序,面向磁盘的数据结构,其核心思想是充分了利用了,磁盘批量的顺序写要远比随机写性能高出很多) 的结构, 数据写入后定期在后台Compaction。 通过类LSM Tree的结构,CK在数据导入时全部是顺序append 写,写入后数据段不可更改,在后台compaction后也是多个段merge sort后顺序写回磁盘。
5》数据分区与线程级并行
ck将数据划分为多个partition,每个partition 再进一步划分为多个index granularity(索引粒度), 然后通过多个CPU 核心分别处理其中的一部分来实现并行数据处理。在这种设计下,单条query就能利用整机所有CPU。 极致的并行处理能力,极大的降低了查询延时。
所以,ck 即使对于大量数据的查询也能够化整为零平行处理。 但是有一个弊端就是对于单条查询使用多CPU, 就不利于同时并发多条查询。 所以对于高qps的查询业务,ck 并不是强项。
2. 安装
基于docker 直接安装
1》 安装clickhouse-server
docker run -d --name clickhouse-server1 --ulimit nofile=262144:262144 clickhouse/clickhouse-server
2》进入容器
docker exec -it clickhouse-server1 bash
3》在容器内部进行测试: 连接到服务之后查看数据库
root@25f56c441b1a:/# clickhouse-client
ClickHouse client version 21.12.3.32 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 21.12.3 revision 54452.
25f56c441b1a :) show databases;
SHOW DATABASES
Query id: 3fa99c37-25c1-44a9-b372-f4b5995965b7
┌─name───────────────┐
│ INFORMATION_SCHEMA │
│ default │
│ information_schema │
│ system │
└────────────────────┘
4 rows in set. Elapsed: 0.001 sec.
25f56c441b1a :) select 1-1
SELECT 1 - 1
Query id: d3351080-3fe1-4023-bb56-8d2842385ce8
┌─minus(1, 1)─┐
│ 0 │
└─────────────┘
1 rows in set. Elapsed: 0.001 sec.
3. 数据类型
- 整型
固定长度的整型,包括有符号整型和无符号整型。
整型范围: -2^(n-1) ~ 2^(n-1)-1
Int8、Int16、Int32、Int64
无符号整型范围:(o ~ 2^n-1)
UInt8、UInt16、UInt32、UInt64
- 浮点型
Float32 - float
Float64 - double
- 布尔类型
没有单独的类型来存储布尔值,建议使用UInt8, 取值限制为[0 | 1]
- Decimal
有符号的浮点数,可在加减乘除算法中保持精度。有如下三种声明:
Decimal(P,S), Decimal32(S), Decimal64(S), Decimal128(S)
P - 精度。有效范围:[1:38],决定可以有多少个十进制数字(包括分数)
S - 规模。有效范围:[0:P],决定数字的小数部分中包含的小数位数。
- 字符串
1.String 字符串可以任意长度的。它可以包含任意的字节集,包含空字节。因此,字符串类型可以代替其他 DBMSs 中的 VARCHAR、BLOB、CLOB 等类型。
2.FixedString(N) :定长字符串,类似于mysql的char 类型。不同的是:
- 如果字符串包含的字节数少于`N’,将对字符串末尾进行空字节填充。 mysql是填空格,且查询时删除空格。当做数据查询时,ClickHouse不会删除字符串末尾的空字节。 如果使用WHERE子句,则须要手动添加空字节以匹配FixedString的值。
SELECT * FROM FixedStringTable WHERE a = 'b\0' # 我们存的定长2的单个b,匹配需要自己加空字节
- 如果字符串包含的字节数大于
N
,将抛出Too large value for FixedString(N)
异常。
- 枚举类型
包括Enum8 和 Enum16 类型。 Enum 保存 'string'= integer 的对应关系。在 ClickHouse 中,尽管用户使用的是字符串常量,但所有含有 Enum 数据类型的操作都是按照包含整数的值来执行。这在性能方面比使用 String 数据类型更有效。
Enum8 用 'String'= Int8 对描述。
Enum16 用 'String'= Int16 对描述。
--- 建表
CREATE TABLE t_enum
(
x Enum8('hello' = 1, 'world' = 2)
)
ENGINE = TinyLog
--- 插入
INSERT INTO t_enum VALUES ('hello'), ('world'), ('hello')
--- 查询
SELECT * FROM t_enum
┌─x─────┐
│ hello │
│ world │
│ hello │
└───────┘
--- 查询转为int
SELECT CAST(x, 'Int8') FROM t_enum
┌─CAST(x, 'Int8')─┐
│ 1 │
│ 2 │
│ 1 │
└─────────────────┘
---查询中创建枚举值
SELECT toTypeName(CAST('a', 'Enum8(\'a\' = 1, \'b\' = 2)'))
┌─toTypeName(CAST('a', 'Enum8(\'a\' = 1, \'b\' = 2)'))─┐
│ Enum8('a' = 1, 'b' = 2) │
└──────────────────────────────────────────────────────┘
- uuid 类型
要生成UUID值,ClickHouse提供了 generateuidv4 函数。 如果插入时不指定UUID值,UUID值将用零填充(00000000-0000-0000-0000-000000000000)
CREATE TABLE t_uuid (x UUID, y String) ENGINE=TinyLog
INSERT INTO t_uuid SELECT generateUUIDv4(), 'Example 1'
8. 日期和时间
Date 日期:用两个字节存储,表示从 1970-01-01 (无符号) 到当前的日期值。值的范围: [1970-01-01, 2149-06-06]。
Datetime: 接收年月日 时分秒的字符串
Datetime64 接收年月日时分秒亚秒的字符串, 比如: 的字符串比如‘2019-12-16 20:50:10.66
- 其他
其他还有数组类型、Map类型、Geo、Tuple元组类型。
4.表引擎
表引擎决定了如何存储表的数据。包括:
- 数据的存储方式和位置,写到哪里以及从哪里读取数据
- 支持那些查询以及如何支持
- 并发数据访问
- 索引的使用
- 是否可以执行多线程请求
- 数据复制参数
必须显示地在创建表时定义该表使用的引擎,以及引擎使用的相关参数。
1. TinyLog
以文件的形式保存在磁盘上,不支持索引,没有并发控制。一般保存少量数据的小表,生产环境上作用优先。可以用于平时练习测试:
25f56c441b1a :) create table t_tinylog ( id String, name String) engine=TinyLog;
CREATE TABLE t_tinylog
(
`id` String,
`name` String
)
ENGINE = TinyLog
Query id: d4ac3598-dfa7-483b-9f08-b31514d7071d
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 21.12.3 revision 54452.
Ok.
0 rows in set. Elapsed: 0.006 sec.
25f56c441b1a :) show create table t_tinylog;
SHOW CREATE TABLE t_tinylog
Query id: 1085fc7f-b8f9-4b9c-8f6c-587d6b78f839
┌─statement─────────────────────────────────────────────────────────────────────────┐
│ CREATE TABLE default.t_tinylog
(
`id` String,
`name` String
)
ENGINE = TinyLog │
└───────────────────────────────────────────────────────────────────────────────────┘
1 rows in set. Elapsed: 0.002 sec.
2. memory
内存引擎,数据以未压缩的形式直接保存在内存当中,服务器重启数据就会消失。读写操作不会相互阻塞,不支持索引。简单查询下有非常高的性能表现(超过10G/S)。
3. MergeTree(合并树)
ck中最强大的引擎就是MergeTree以及该系列中的其他引擎。支持索引和分区,低位相当于mysql的innodb。
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')
- 查询
ac9033c7171a :) select * from t_order_mt;
SELECT *
FROM t_order_mt
Query id: 12fcf25a-b344-48b1-879c-21f9380e9272
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │ 600 │ 2020-06-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │ 1000 │ 2020-06-01 12:00:00 │
│ 102 │ sku_002 │ 2000 │ 2020-06-01 11:00:00 │
│ 102 │ sku_002 │ 2000 │ 2020-06-01 13:00:00 │
│ 102 │ sku_002 │ 12000 │ 2020-06-01 13:00:00 │
│ 102 │ sku_004 │ 2500 │ 2020-06-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
6 rows in set. Elapsed: 0.002 sec.
1. partition by 分区(可选)
1》作用:降低扫描的范围,优化查询速度
2》默认:使用一个分区
3》分区目录:MergeTree 是以列文件+索引文件+表定义文件组成的,但是如果设定了分区那么文件会保存到不同的分区目录中
4》并行:分区后,面对涉及跨分区的查询统计,ck会以分区为单位并行出咯
5》数据写入与分区合并:任何一个批次的数据写入都会产生一个临时分区,不会纳入任何一个已有的分区。写入后的某个时刻(大概10-15分钟后),ck会自动执行合并操作(当然可以手动通过optimize 执行),把临时分区的数据合并到已有分区
optimize table xxx 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')
再次查询:(发现没有进入分区)
ac9033c7171a :) select * from t_order_mt;
SELECT *
FROM t_order_mt
Query id: 25dff719-964c-4416-827c-cbb8e9f42754
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │ 600 │ 2020-06-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │ 600 │ 2020-06-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │ 1000 │ 2020-06-01 12:00:00 │
│ 102 │ sku_002 │ 2000 │ 2020-06-01 11:00:00 │
│ 102 │ sku_002 │ 2000 │ 2020-06-01 13:00:00 │
│ 102 │ sku_002 │ 12000 │ 2020-06-01 13:00:00 │
│ 102 │ sku_004 │ 2500 │ 2020-06-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │ 1000 │ 2020-06-01 12:00:00 │
│ 102 │ sku_002 │ 2000 │ 2020-06-01 11:00:00 │
│ 102 │ sku_002 │ 2000 │ 2020-06-01 13:00:00 │
│ 102 │ sku_002 │ 12000 │ 2020-06-01 13:00:00 │
│ 102 │ sku_004 │ 2500 │ 2020-06-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
12 rows in set. Elapsed: 0.004 sec.
optimize 后再次查询
ac9033c7171a :) optimize table t_order_mt final;
OPTIMIZE TABLE t_order_mt FINAL
Query id: ef8da207-8575-4806-a14e-584d94456de1
Ok.
0 rows in set. Elapsed: 0.003 sec.
ac9033c7171a :) select * from t_order_mt;
SELECT *
FROM t_order_mt
Query id: a92090be-36c3-4e7c-b727-cc46929897e3
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │ 600 │ 2020-06-02 12:00:00 │
│ 102 │ sku_002 │ 600 │ 2020-06-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │ 1000 │ 2020-06-01 12:00:00 │
│ 101 │ sku_001 │ 1000 │ 2020-06-01 12:00:00 │
│ 102 │ sku_002 │ 2000 │ 2020-06-01 11:00:00 │
│ 102 │ sku_002 │ 2000 │ 2020-06-01 13:00:00 │
│ 102 │ sku_002 │ 12000 │ 2020-06-01 13:00:00 │
│ 102 │ sku_002 │ 2000 │ 2020-06-01 11:00:00 │
│ 102 │ sku_002 │ 2000 │ 2020-06-01 13:00:00 │
│ 102 │ sku_002 │ 12000 │ 2020-06-01 13:00:00 │
│ 102 │ sku_004 │ 2500 │ 2020-06-01 12:00:00 │
│ 102 │ sku_004 │ 2500 │ 2020-06-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
12 rows in set. Elapsed: 0.002 sec.
2. primary key 主键(可选)
ck的主键和其他数据库不太已有,只提供了数据的一级索引,但是却不是唯一约束。意味着可以存在相同primary key的数据。
主键的设定主要依据是查询语句中的where条件。
根据条件通过对主键进行某种形式的二分查找,能够定位到对应的index granularity(索引粒度,指在稀疏索引中两个相邻索引对应数据的建个,ck默认的mergeTree是8192),避免了全表扫描。
稀疏索引:简单的说就是间接性的建立索引。 比如1,2,3,4,5,6 建立索引1,3,6。好处就是可以用很少的索引数据,定位更多的数据,代价就是只能定位到索引粒度的第一行,然后再进行点扫描。
3. order by (必选)
order by 设定了分区内的数据按照哪些字段顺序进行有序保存。是MergeTree 中唯一必选项,用户不设置主键情况下,很多处理会依照order by 的字段进行处理(比如去重和汇总)。
要求:主键必须是order by字段的前缀字段。比如order by字段是(id, sku_id), 那么主键必须是id 或者 (id, sku_id)
4. 数据ttl
time to live,MergeTree 提供了可以管理数据表或者列的生命周期的功能。
(1)列级别TTL
1》创建测试表
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)
2》插入数据
insert into t_order_mt3 values
(106,'sku_001',1000.00,'2022 08 05 07:20:30'),
(107,'sku_002',2000.00,'2022 08 05 07:20:30'),
(110,'sku_003',600.00,'2022 08 06 19:20:30')
3》手动合并
optimize table t_order_mt3 final;
4》查询
查询后106、107 两行的total_amount 属性变为0
5》表级TTL
alter table t_order_mt4 modify ttl create_time + interval 10 second
涉及判断的字段必须是Date 或者Datetime 类型,推荐使用分区的日期字段。
插入数据:
insert into t_order_mt3 values
(106,'sku_001',1000.00,'2022 08 05 11:29:30')
查询: 当超过创建时间10s后,整条数据消失
4. ReplacingMergeTree
ReplacingMergeTree是MergeTree 的一个变种,它存储特性完全继承MergeTree,只是多了去重功能。MergeTree对主键不做唯一约束,这个是可以做到基于主键去重。会根据ORDER BY所声明的表达式去重
- 去重时机:只会出现在合并的过程中。
- 去重范围:如果经过了分区,只会在分区内部去重,不会跨分区(也就是只能保证通分区没有重复数据)
- 测试:
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》查询
ac9033c7171a :) select * from t_order_rmt
SELECT *
FROM t_order_rmt
Query id: da9a22da-6d49-4ecc-b279-b6e9bf023c3e
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │ 600 │ 2020-06-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │ 1000 │ 2020-06-01 12:00:00 │
│ 102 │ sku_002 │ 12000 │ 2020-06-01 13:00:00 │
│ 102 │ sku_004 │ 2500 │ 2020-06-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
4 rows in set. Elapsed: 0.002 sec.
5. SummingMergeTree
对于不查询明细,只关心以维度进行汇总聚合结果的场景可以用该引擎。可以理解为预聚合。会根据ORDER BY所声明的表达式对指定字段sum
也是只针对当前分区进行聚合,且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》执行查询
ac9033c7171a :) select * from t_order_smt;
SELECT *
FROM t_order_smt
Query id: 5f4f0695-93a5-4393-8fec-0523f4374dc8
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │ 600 │ 2020-06-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id───┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_ 001 │ 1000 │ 2020-06-01 12:00:00 │
│ 102 │ sku_002 │ 16000 │ 2020-06-01 11:00:00 │
│ 102 │ sku_004 │ 2500 │ 2020-06-01 12:00:00 │
└─────┴──────────┴──────────────┴─────────────────────┘
补充: OLAP和OLTP区别
联机事务处理(On-Line Transaction Processing,OLTP) - 传统的关系型数据库的主要应用,主要是基本的、日常的事务处理,记录即时的增、删、改、查,比如在银行存取一笔款,就是一个事务交易
联机分析处理(On-Line Analytical Processing,OLAP) - OLAP即联机分析处理,是数据仓库的核心部心,支持复杂的分析操作,侧重决策支持,并且提供直观易懂的查询结果
【当你用心写完每一篇博客之后,你会发现它比你用代码实现功能更有成就感!】