ClickHouse表引擎
表引擎介绍
表引擎是 ClickHouse 的一大特色。可以说, 表引擎决定了如何存储表的数据。包括:
- 数据的存储方式和位置,写到哪里以及从哪里读取数据。
- 支持哪些查询以及如何支持。
- 并发数据访问。
- 索引的使用(如果存在)。
- 是否可以执行多线程请求。
- 数据复制参数。
表引擎的使用方式就是必须显式在创建表时定义该表使用的引擎,以及引擎使用的相关
参数。
表引擎大致有外部集成引擎(MySQL、MongoDB和HDFS等)、Log、MergeTree和其他引擎(Distribution、Merege和Memory等)。
外部集成引擎像所有其他的表引擎一样,使用CREATE TABLE或ALTER TABLE查询语句来完成配置。然后从用户的角度来看,配置的集成看起来像查询一个正常的表,但对它的查询是代理给外部系统的。这种透明的查询是这种方法相对于其他集成方法的主要优势之一,比如外部字典或表函数,它们需要在每次使用时使用自定义查询方法。
生产环境绝大部分情况使用MergeTree系列的表引擎,所以下面只重点介绍MergeTree表引擎
其他表引擎
TinyLog
以列文件的形式保存在磁盘上,不支持索引,没有并发控制。一般保存少量数据的小表,
生产环境上作用有限。可以用于平时练习测试用。
例如:
`create table t_tinylog ( id String, name String) engine=TinyLog;`
Memory
内存引擎,数据以未压缩的原始形式直接保存在内存当中,服务器重启数据就会消失。
读写操作不会相互阻塞,不支持索引。简单查询下有非常非常高的性能表现(超过 10G/s)。
一般用到它的地方不多,除了用来测试,就是在需要非常高的性能,同时数据量又不太
大(上限大概 1 亿行)的场景。
MergeTree
ClickHouse 中最强大的表引擎当属 MergeTree(合并树)引擎及该系列(MergeTree)
中的其他引擎, 支持索引和分区, 地位可以相当于 innodb 之于 Mysql。 而且基于 MergeTree,
建表语法:
CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
...
INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2,
...
PROJECTION projection_name_1 (SELECT <COLUMN LIST EXPR> [GROUP BY] [ORDER BY]),
PROJECTION projection_name_2 (SELECT <COLUMN LIST EXPR> [GROUP BY] [ORDER BY])
) ENGINE = MergeTree()
-- 可以看到分区和主键的指定并不是必须的,但是order by字段是必须的
ORDER BY expr
[PARTITION BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr
[DELETE|TO DISK 'xxx'|TO VOLUME 'xxx' [, ...] ]
[WHERE conditions]
[GROUP BY key_expr [SET v1 = aggr_func(v1) [, v2 = aggr_func(v2) ...]] ] ]
[SETTINGS name=value, ...]
创建测试用表
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比较重要的时order by、partition by和primary key指定的三个字段,下面分别从这三个方面介绍MergeTree存储和查询机制
partition by
首先在上图可以看到,查询的结果是按照分区指定字段分隔开的,MergeTree很多机制都是在分区内进行的,比如SummingMergeTree的聚合以及ReplacingMergeTree的去重
Hive同样有分区机制,而分区的目的主要是降低扫描的范围,优化查询速度,如果不填,就只使用一个分区。
而Hive的分区是在HDFS上通过存放在不同的文件夹实现的,ClickHouse不经由HDFS存储,但实际上存储也类似于Hive,不同分区的数据也是存放在不同的文件夹下。
而ClickHouse在分区后,还会面对涉及跨分区的查询统计, 以分区为单位并行处理,加快查询速度。
文件存储机制
ClickHouse的默认数据存放路径位于/var/lib/clickhouse
路径下,首先看下该路径下有什么文件
值得关注的就是data
和metadata
目录,分别存放了相关的数据和元数据信息
首先看metadata
目录:
结构比较简单,metadata下是以库命名的文件目录,再下级则是以表名命名的文件
而文件的内容实际上就是建表语句的信息
再来看data
目录下的内容:
目录结构和metadata
类似,都是按照库->表的结构存放
接下来到表对应的目录下:
这里的前两个实际就是两个分区对应的文件夹,文件夹的命名规则为:分区名_分区块最小编号_分区块最大编号_合并层级
,下面的detached目录为卸载信息,与前面看到的attach相反,而format_version.txt是格式版本信息。
下面详细介绍下分区目录的名称规则:
- 第一个段是分区的名称,有以下几种情况:
当未定义分区时,会默认生成一个all的分区,全部数据保存在该分区下
使用整型作为分区键时,使用整型数字对应的字符串作为分区名
使用日期作为分区键时,使用日期对应的字符串作为分区名
其他类型的分区键,将通过128位的Hash算法取Hash值作为分区名 - 第二和第三段分别为分区块最小和最大编号
- 第四段是合并层级,即被合并的次数,起到的作用可以类似理解为HBase的版本号
下面继续插入数据,并合并一次,观察目录变化:
-- 再插入一次上面的数值
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');
可以看到同一个分区的数据也被分隔开显示在了不同位置
这是因为新插入的分区会放在临时分区内,在当数据被合并时才会移到对应分区内(大概会在写入后10-15分钟后),当然也可以手动执行合并操作:optimize table xxxx final;
此时再看文件目录:
可以看到新生成了两个目录存放新插入的一批数据,新目录的编号对应递增
现在执行手动合并命令:
optimize table t_order_mt final; -- 对全部分区执行合并操作,这里先执行这条命令
optimize table t_order_mt partition '20200601' final; -- 对指定分区执行合并操作
执行完成后再查询表数据可以看到分区确实已经被合并了
再到表级目录下看到又增加了两个目录:
合并后的分区目录最小和最大编号发生了变化,且合并层级增加了1,此时查询数据时就只会从合并层级大的分区内查询结果了。
原本的两个分区目录并没有立即被删除,而是保留了下来,实际新分区目录的形成等同于标记旧目录的过期,这两个目录会在后续合并中被删除掉。
看过分区的生成机制,再到分区目录下:
data.bin
就是数据文件(旧版本中会按照每个列保存,同时对应的mrk文件也会每个列保存一个文件)
data.mrk3
是标记文件,标记数据的位置(可以类比Kafka的数据保存机制)
count.txt
保存的是数据条数,因此count(*)结果会很快得出
columns.txt
保存的是列信息:
primary.idx
是主键的索引文件,用于加快查询效率 稀疏索引
minmax_create_time.idx
保存分区键的最大和最小值
partition.dat
和minmax_create_time.idx一样都是分区文件
checksums.txt
是校验文件,校验各个文件的正确性,存放各个文件的size和hash值
primary key
ClickHouse 中的主键,和其他数据库不太一样, 它只提供了数据的一级索引,但是却不
是唯一约束。 这就意味着是可以存在相同 primary key 的数据的。
主键的设定主要依据是查询语句中的 where 条件。
根据条件通过对主键进行某种形式的二分查找,能够定位到对应的 index granularity,避
免了全表扫描。
index granularity: 直接翻译的话就是索引粒度,指在稀疏索引中两个相邻索引对应数
据的间隔(每间隔8129条数据做一个索引)。 ClickHouse 中的 MergeTree 默认是 8192。官方不建议修改这个值,除非该列存在大量重复值,比如在一个分区中几万行才有一个不同数据。
稀疏索引:
稀疏索引的好处就是可以用很少的索引数据,定位更多的数据,代价就是只能定位到索
引粒度的第一行,然后再进行进行一点扫描。
注意:由于底层实际是通过order by做排序,因此primary key指定的字段必须是order by字段的前缀(例如order by a,b,c 那么primary key可以是(a),(a,b),(a,b,c)或者不指定,但是不可以是(b,c))
order by
order by是MergeTree建表时唯一必须指定的字段,设定了分区内的数据按照哪些字段顺序进行有序保存,甚至比 primary key 还重要,因为当用户不设置主键的情况,很多处理会依照 order by 的字段进行处理。
要求:主键必须是 order by 字段的前缀字段。
比如 order by 字段是 (id,sku_id) 那么主键必须是 id 或者(id,sku_id)
二级索引
二级索引可以理解为为原本的order by产生的稀疏索引再创建索引,这个场景比较容易理解,随着数据的增多,索引范围变大,索引数据变多,对应的查询速度就会减慢,此时就可以建立二级索引保证查询的速度
需要注意的是,该功能在v20.1.2.4版本之前是被标注为实验性的,如需使用需要将设置打开:set allow_experimental_data_skipping_indices=1;
建表时指定二级索引:
create table t_order_mt2(
id UInt32,
sku_id String,
total_amount Decimal(16,2),
create_time Datetime,
-- a为二级索引名称
-- total_amount是创建二级索引的列
-- minmax是二级索引类型,会保存索引范围内最大和最小值
-- 5是索引粒度,即5个一级索引创建1个二级索引
Index a total_amount Type minmax Granularity 5
) engine=MergeTree
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,sku_id);
创建完成后,可以在分区目录下看到生成了对应的二级索引文件(skp_idx_a.idx
和skp_idx_a.mrk3
):
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);
当系统时间到指定时间(系统时间大于create_time+10s)时,该列的值就会被修改回默认值
表级别
建表时指定
CREATE TABLE example_table
(
d DateTime,
a Int
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(d)
ORDER BY d
TTL d + INTERVAL 1 MONTH [DELETE],
d + INTERVAL 1 WEEK TO VOLUME 'aaa',
d + INTERVAL 2 WEEK TO DISK 'bbb';
建表后修改
alter table t_order_mt3 MODIFY TTL create_time + interval 10 SECOND;
表级别TTL当时间达到设定时间时单行数据过期会被删除
TTL不止会删除数据,同时还有其他操作:
DELETE - 删除指定行数据(默认操作)
RECOMPRESS codec_name - recompress data part with the codec_name;
TO DISK ‘aaa’ - 移动数据到磁盘aaa
TO VOLUME ‘bbb’ - 移动数据到磁盘bbb
GROUP BY - 聚合指定行数据
ReplacingMergeTree
ReplacingMergeTree 是 MergeTree 的一个变种,它存储特性完全继承 MergeTree,只是
多了一个去重(根据order by的字段)的功能。 尽管 MergeTree 可以设置主键,但是 primary key 其实没有唯一约束的功能。如果需要处理掉重复数据,可以借助ReplacingMergeTree。
去重时机
数据的去重只会在合并的过程中出现。 合并会在未知的时间在后台进行,所以你无法预
先作出计划。有一些数据可能仍未被处理。
只保证最终一致性
去重范围
如果表经过了分区,去重只会在分区内部进行去重,不能执行跨分区的去重。
所以 ReplacingMergeTree 能力有限, ReplacingMergeTree 适用于在后台清除重复的数
据以节省空间,但是它不保证没有重复的数据出现
建表语法:
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');
当前版本数据插入时做一次去重
上述插入数据操作重复执行一次,再查询数据:
可以看到此时重复的数据已经被插入了
此时执行手动合并操作后再查询结果:
重复数据被删除掉了
总结:
- 实际上是使用 order by 字段作为唯一键
- 去重不能跨分区
- 只有同一批插入(新版本)或合并分区时才会进行去重
- 认定重复的数据保留,版本字段值最大的
- 如果版本字段相同则按插入顺序保留最后一条
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) -- 指定聚合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的字段做预聚合
总结:
- 以 SummingMergeTree()中指定的列作为汇总数据列
- 可以填写多列必须数字列,如果不填,以所有非维度列且为数字列的字段为汇总数据列
- 以 order by 的列为准,作为维度列(group by)
- 其他的列按插入顺序保留第一行
- 只在分区内聚合数据
- 只有在同一批次插入(新版本)或分片合并时才会进行聚合
如何要对SummingMergeTree表查询根据order by字段聚合后的结果,仍需要使用group by 语法查询,因为一方面存储插入数据还未被合并的情况,另一方面SummingMergeTree只会在分区内进行合并,分区间的并不会合并
由于除了维度列和聚合列之外,其他列数据在被聚合时只会保留第一行数据,可能会导致数据丢失情况,所以设计聚合表的话,唯一键值、流水号可以去掉,所有字段全部是维度、度量或者时间戳。