文章目录
- OLAP常见架构分类
- OLAP实现技术的演进
- ClickHouse的发展历程
- ClickHouse核心功能
- 默认访问端口
- 数据定义
- 数据字典
- MergeTree原理
- MergeTree系列表引擎
- 其他常见表引擎
联机事务处理(OLTP)系统——联机分析(OLAP)系统
数据库——数据仓库:即通过引入一个专门用于分析类场景的数据库,将分散的数据统一汇聚到一处
OLAP名为联机分析,又可以称为多维分析,指的是通过多种不同的维度审视数据,进行深层次分析。直接一点理解,维度就好比是一张数据表的字段,而多维分析则是基于这些字段进行聚合查询。
OLAP常见架构分类
- ROLAP(Relational OLAP,关系型OLAP)。直接使用关系模型构建,数据模型常使用星型模型或者雪花模型。
- ——对数据的实时处理能力要求很高,数据量大时难以保证效率
- MOLAP(Multidimensional OLAP,多维型OLAP)。它的出现是为了缓解ROLAP性能问题。MOLAP使用多维数组的形式保存数据,借助预先聚合结果,用空间换取时间,最终提升查询性能
- ——维度预处理可能会导致数据的膨胀;有一定的滞后性,不能实时进行数据分析。而且,立方体只保留了聚合后的结果数据,导致无法查询明细数据。
- 实现步骤:
- 对需要分析的数据进行建模,框定需要分析的维度字段;
- 通过预处理的形式,对各种维度进行组合并事先聚合;
- 将聚合结果以某种索引或者缓存的形式保存起来。
- HOLAP(Hybrid OLAP,混合架构的OLAP)。这种思路可以理解成ROLAP和MOLAP两者的集成。
OLAP实现技术的演进
传统关系型数据库阶段
主要基于以Oracle、MySQL为代表的一众关系型数据实现。
在ROLAP架构下,直接使用这些数据库作为存储与计算的载体;
在MOLAP架构下,借助物化视图的形式实现数据立方体。
——在数据体量大、维度数目多的情况下都存在严重的性能问题,甚至存在根本查询不出结果的情况。
大数据技术阶段
以ROLAP架构为例,使用Hive和SparkSQL这类新兴技术
——在面向海量数据的处理性能方面已经优秀很多,但直接作为面向终端用户的在线查询系统还是太慢了。
再看MOLAP架构,依托MapReduce或Spark等新兴技术作为计算引擎,其预聚合结果的存储载体也转向HBase这类高性能分布式数据库。
——主流MOLAP架构能够在亿万级数据的体量下,实现毫秒级的查询响应时间,但依然存在维度爆炸、数据同步实时性不高的问题。
ClickHouse的发展历程
MySQL时期——ROLAP
MySQL,MyISAM表引擎。MyISAM表引擎使用B+树结构存储索引,而数据则使用另外单独的存储文件。
——数据并行、随机地写入MySQL集群,在磁盘中是完全随机存储的,并且会产生大量的磁盘碎片。
自研发的Metrage——MOLAP
- 首先,在数据模型层面,它使用Key-Value模型(键值对)代替了关系模型;
- 其次,在索引层面,它使用LSM树代替了B+树;
- 最后,在数据处理层面,由实时查询的方式改为了预处理的方式。
LSM树:
先在内存中构建出一棵小树,构建时进行数据排序(数据有序性),完毕即算写入成功(通过预写日志的形式,防止因内存故障而导致的数据丢失),当内存中小树的数量达到某个阈值时,就会借助后台线程将小树刷入磁盘并生成一个小的数据段。
写入动作只发生在内存中,写入性能高。
在每个数据段中,数据局部有序,所以能够进一步使用稀疏索引来优化查询性能。
借助LSM树索引,可使得Metrage引擎在软硬件层面同时得到优化(磁盘顺序读取、预读缓存、
稀疏索引等),最终有效提高系统的综合性能。
自研发的OLAPServer——HOLAP(Metrage+OLAPServer)
- 数据模型:关系模型
- 存储结构:与MyISAM表引擎类似,分为了索引文件和数据文件两个部分。
- 索引方面,使用了LSM树所使用到的稀疏索引。
- 数据文件上,沿用了LSM树中数据段的思想,即数据段内数据有序,借助稀疏索引定位数据段。
- 引入了列式存储的思想,将索引文件和数据文件按照列字段的粒度进行了拆分,每个列字段各自独立存储,进一步减少数据读取的范围。
从功能的完备性角度来看,如果说MySQL可以称为数据库管理系统(DBMS),那么OLAPServer只能称为数
据库——缺失一些基本的功能。如,只有一种数据类型,没有DBMS应有的基本管理功能。
Clickhouse——ROLAP
Click Stream,Data WareHouse,简称ClickHouse——基于页面的点击事件流,面向数据仓库进行OLAP分析。
ClickHouse核心功能
DBMS功能
·DDL(数据定义语言):可以动态地创建、修改或删除数据库、表和视图
·DML(数据操作语言):可以动态查询、插入、修改或删除数据。
·权限控制:可以按照用户粒度设置数据库或者表的操作权限
·数据备份与恢复:提供了数据备份导出与导入恢复机制
·分布式管理:提供集群模式,能够自动管理多个数据库节点。
列式存储与数据压缩
列式存储:减少查询时所需扫描的数据量(比如获取某些字段时,不用先获取整行,而是直接获取目标字段)
数据压缩:数据中的重复项越多,则压缩率越高,则数据体量越小,则数据在网络中的传输越快。同一列字段的数,拥有相同的数据类型和现实语义,重复项的可能性自然就更高。
向量化执行引擎
利用CPU的SIMD指令,用单条指令操作多条数据,在CPU寄存器层面实现数据的并行操作。
关系模型与SQL查询
群众基础高,更易用,利于传统关系型数据库的迁移
多样化的表引擎
根据实际业务场景的要求,选择合适的表引擎使用。
多线程与分布式
向量化执行是通过数据级并行的方式提升了性能,多线程处理就是通过线程级并行的方式实现了性能的提升。
相比基于底层硬件实现的向量化执行SIMD,线程级并行通常由更高层次的软件层面控制。
多主架构
ClickHouse采用Multi-Master多主架构,集群中的每个节点角色对等,功能相同,客户端访问任意一个节点都能得到相同的效果,天然规避了单点故障的问题,非常适合用于多数据中心、异地多活的场景。
(HDFS、Spark、HBase和Elasticsearch这类分布式系统,采用Master-Slave主从架构,由一个管控节点作为Leader统筹全局。)
在线查询
在大体量数据、复杂查询的场景下,也能够做到极快响应,且无须对数据进行任何预处理加工。
数据分片与分布式查询
数据分片是将数据进行横向切分,这是一种在面对海量数据的场景下,解决存储和查询瓶颈的有效手段,是一种分治思想的体现。
ClickHouse支持分片,而分片则依赖集群。每个集群由1到多个分片组成,而每个分片只对应ClickHouse的1个服务节点。分片的数量上限取决于节点数量。
提供了本地表(Local Table)与分布式表(Distributed Table)的概念。一张本地表等同于一份数据的分片。而分布式表本身不存储任何数据,它是本地表的访问代理,能够代理访问多个数据分片,从而实现分布式查询。
默认访问端口
ClickHouse的底层访问接口支持TCP和HTTP两种协议,其中,TCP协议拥有更好的性能,其默认端口为9000,主要用于集群间的内部通信及CLI客户端;而HTTP协议则拥有更好的兼容性,可以通过REST服务的形式被广泛用于JAVA、Python等编程语言的客户端,其默认端口为8123。
数据定义
数据类型
基本类型只有数值、字符串和时间三种类型(没有Boolean类型,但可以使用整型的0或1替代)
数值
正无穷:inf,负无穷:-inf,非数字:nan
字符串类型
String:不定长
FixedString:定长
UUID:如果一个UUID类型的字段在写入数据时没有被赋值,则会依照格式使用0填充,如00000000-0000-0000-0000-000000000000
时间类型
DateTime:精确到秒
DateTime64:精确到亚秒
Date:精确到天
复合类型
- 数组Array:两种定义方式,array(T)或[T],如
SELECT array(1, 2) as a , [1, 2] as b
,在定义表字段时,需要指定明确的元素类型,例如:CREATE TABLE Array_TEST ( c1 Array(String) ) engine = Memory
- 元组Tuple:元组类型由1~n个元素组成,每个元素之间允许设置不同的数据类型,且彼此之间不要求兼容。两种方式定义,tuple(T)或(T),如
SELECT tuple(1,'a',now()) AS x, (1,2.0,null) AS y
,在定义表字段时,需要指定明确的元素类型,例如:CREATE TABLE Tuple_TEST ( c1 Tuple(String,Int8) ) engine = Memory
- 枚举Enum:Enum8和Enum16,除取值范围不同外别无二致。枚举固定使用(String:Int)Key/Value键值对的形式定义数据,所以Enum8和Enum16分别会对应(String:Int8)和(String:Int16)。Key和Value不允许重复,值都不能为Null,但Key允许是空字符串。定义:
CREATE TABLE Enum_TEST ( c1 Enum8('ready' = 1, 'start' = 2, 'success' = 3, 'error' = 4) ) ENGINE = Memory;
。在写入枚举数据的时候,只会用到Key字符串
部分,例如:INSERT INTO Enum_TEST VALUES('ready');
- 嵌套Nested
特殊类型
- Nullable:准确说是修饰符,定义字段时使用该修饰符则字段可为NULL:
CREATE TABLE Null_TEST ( c2 Nullable(UInt8) ) ENGINE = TinyLog;
- 只能和基础类型搭配使用,不能用于数组和元组这些复合类型;
- 不能作为索引字段;
- Nullable会使查询和写入性能变慢。正常情况下,每个列字段的数据会被存储在对应的[Column].bin文件中,被Nullable类型修饰后,会额外生成一个[Column].null.bin文件专门保存它的Null值。这意味着在读取和写入数据时,需要一倍的额外文件操作。
- Domain域名类型:分为IPv4和IPv6两类,IPv4类型是基于UInt32封装的,支持格式检查(格式错误的IP数据无法被写入),
CREATE TABLE IP4_TEST (ip IPv4) ENGINE = Memory;INSERT INTO IP4_TEST VALUES ('192.0.0.0')
;IPv6类型是基于FixedString(16)封装的,使用方法与IPv4别无二致。- 表象上看与String一样,但Domain类型并不是字符串,所以它不支持隐式的自动类型转换。如果需要返回IP的字符串形式,则需要显式调用IPv4NumToString或IPv6NumToString函数进行转换。
数据库
定义
CREATE DATABASE IF NOT EXISTS db_name [ENGINE = engine]
默认数据库的实质是物理磁盘上、位于安装目录下的、以数据库名命名的一个文件目录,执行默认数据库创建命令后,除了在安装目录创建同名文件夹,在metadata路径下也会一同创建用于恢复数据库的DB_TEST.sql文件
5种引擎
·Ordinary:默认引擎,使用时无须刻意声明。在此数据库下可以使用任意类型的表引擎。
·Dictionary:字典引擎,此类数据库会自动为所有数据字典创建它们的数据表。
·Memory:内存引擎,用于存放临时数据。此类数据库下的数据表只会停留在内存中,不会涉及任何磁盘操作,当服务重启后数据会被清除。
·Lazy:日志引擎,此类数据库下只能使用Log系列的表引擎。
·MySQL:MySQL引擎,此类数据库下会自动拉取远端MySQL中的数据,并为它们创建MySQL表引擎的数据表。
数据表
定义
1 直接新建
CREATE TABLE [IF NOT EXISTS] [db_name.]table_name (
name1 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
name2 [type] [DEFAULT|MATERIALIZED|ALIAS expr], 省略…
) ENGINE = engine
2 复制其他表结构
CREATE TABLE [IF NOT EXISTS][db_name1.]table_name AS [db_name2.] table_name2 [ENGINE = engine]
3 复制其他表结构和数据
CREATE TABLE [IF NOT EXISTS][db_name.]table_name ENGINE = engine AS SELECT …
表字段默认值
三种定义方法:
DEFAULT、MATERIALIZED、ALIAS。
- 在数据写入时,只有DEFAULT类型可以出现在INSERT语句中,MATERIALIZED和ALIAS都不能被显式赋值;
- 在数据查询时,只有DEFAULT类型的字段可以通过
SELECT *
返回。而MATERIALIZED和ALIAS不能; - 在数据存储时,只有DEFAULT和MATERIALIZED类型的字段才支持持久化,而ALIAS的取值总是需要依靠计算产生,数据不会落到磁盘。
默认值可以设置为表内其他字段的值:
CREATE TABLE dfv_v1 (id String, c1 DEFAULT 1000, c2 String DEFAULT c1) ENGINE = TinyLog
修改默认值
ALTER TABLE [db_name.]table MODIFY COLUMN col_name DEFAULT value
临时表
与普通表区别:
- 创建时带TEMPORARY关键字;
- 只支持Memory表引擎,如果会话结束,数据表就会被销毁;
- 不属于任何数据库,库同名时优先级是大于普通表;
- 更多被运用在ClickHouse的内部。
CREATE TEMPORARY TABLE [IF NOT EXISTS] table_name (
name1 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
name2 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
)
分区表
数据分区(partition)和数据分片(shard)是完全不同的两个概念
数据分区是针对本地数据而言的,是数据的一种纵向切分。而数据分片是数据的一种横向切分。
借助数据分区,在查询过程中能够跳过不必要的数据目录,提升查询的性能。
合理地利用分区特性,还可以变相实现数据的更新操作,因为数据分区支持删除、替换和重置操作。
目前只有合并树(MergeTree)家族系列的表引擎才支持数据分区。
两种视图
普通视图:只是一层单纯的SELECT查询映射,不存储数据
CREATE VIEW [IF NOT EXISTS][db_name.]view_name AS SELECT ...
物化视图:支持表引擎,本质是一张数据表,拥有独立存储,创建后,如果源表被写入新数据,那么物化视图也会同步更新,但不支持同步删除,源表删除了数据,在物化视图中仍会保留。
CREATE [MATERIALIZED] VIEW [IF NOT EXISTS][db.]table_name [TO[db.]name][ENGINE = engine] [POPULATE] AS SELECT .
如果使用了POPULATE修饰符,会连带将源表中已存在的数据一并导入,如同执行了SELECT INTO一般;如果不使用POPULATE修饰符,那么物化视图在创建之后是没有数据的,只会同步在此之后被写入的数据
数据表的基本操作
目前只有MergeTree、Merge和Distributed这三类表引擎支持ALTER查询
追加新字段
ALTER TABLE tb_name ADD COLUMN [IF NOT EXISTS] name [type][default_expr] [AFTER name_after]
修改数据类型
ALTER TABLE tb_name MODIFY COLUMN [IF EXISTS] name [type][default_expr]
修改字段备注
ALTER TABLE tb_name COMMENT COLUMN [IF EXISTS] name 'some comment'
删除字段
ALTER TABLE tb_name DROP COLUMN [IF EXISTS] name
重命名/移动数据表到其他库
数据表移动的目标数据库和原始数据库必须处在同一个服务节点内,而不能是集群中的远程节点。
RENAME TABLE [db_name11.]tb_name11 TO [db_name12.]tb_name12, [db_name21.]tb_name21 TO [db_name22.]tb_name22, ...
清空表数据
TRUNCATE TABLE [IF EXISTS][db_name.]tb_name
数据分区的基本操作
查询分区信息
system.parts系统表专门用于查询数据表的分区信息。例如查询数据表partition_v2的分区状况,其中partition_id或者name等同于分区的主键:
SELECT partition_id,name,table,database FROM system.parts WHERE table = 'partition_v2'
删除指定分区
合理地设计分区键并利用分区的删除功能,就能够达到数据更新的目的(删掉指定分区,在重新写入对应该分区的数据)
ALTER TABLE tb_name DROP PARTITION partition_expr
复制分区数据
ClickHouse支持将A表的分区数据复制到B表,这项特性可以用于快速数据写入、多表间数据同步和备份等场景,它的完整语法如下:
ALTER TABLE B REPLACE PARTITION partition_expr FROM A
需要满足两个前提条件:
·两张表需要拥有相同的分区键;
·它们的表结构完全相同。
重置分区某字段数据为默认值
如果数据表某一列的数据有误,需要将其重置为初始值,此时可以使用下面的语句实现:
ALTER TABLE tb_name CLEAR COLUMN column_name IN PARTITION partition_expr
卸载分区
通过DETACH语句卸载。
分区被卸载后,它的物理数据并没有删除,而是被转移到了当前数据表目录的detached子目录下。
ALTER TABLE tb_name DETACH PARTITION partition_expr
装载分区
通过ATTACH 语句装载。
反向操作,将detached子目录下的某个分区重新装载回去。
卸载与装载这一对伴生的操作,常用于分区数据的迁移和备份场景。
ALTER TABLE tb_name ATTACH PARTITION partition_expr
备份与还原分区
分布式DDL执行(批量对每个服务器节点执行DDL)
CREATE、ALTER、DROP、RENMAE及TRUNCATE这些DDL语句,都支持分布式执行。
加上ON CLUSTER cluster_name
声明即可。
CREATE TABLE partition_v3 ON CLUSTER ch_cluster(
ID String,
URL String,
EventTime Date
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventTime)
ORDER BY ID
Insert数据写入
1、常规:
INSERT INTO [db.]table [(c1, c2, c3…)] VALUES (v11, v12, v13…), (v21, v22, v23…), ...`\
2、使用指定格式
INSERT INTO [db.]table [(c1, c2, c3…)] FORMAT format_name data_set
如:
INSERT INTO partition_v2 FORMAT CSV \
'A0017','www.nauu.com', '2019-10-01' \
'A0018','www.nauu.com', '2019-10-01'
3、使用select
INSERT INTO [db.]table [(c1, c2, c3…)] SELECT ...
Alter表数据的删除与修改
ClickHouse的DELETE和UPDATE被称为Mutation查询。
Mutation语句是一种“很重”的操作,更适用于批量数据的操作;
不支持事务,无法回滚;
异步执行,具体执行进度需要通过system.mutations系统表查询。
ALTER TABLE [db_name.]table_name DELETE WHERE filter_expr
ALTER TABLE [db_name.]table_name UPDATE column1 = expr1 [, ...] WHERE filter_expr
数据字典
数据字典能够有效地帮助我们消除不必要的JOIN操作(例如根据ID转名称),优化SQL查询,为查询性能带来质的提升。
内置字典
只有一种内置字典——Yandex.Metrica字典,用在ClickHouse自家产品,设计意图是快速存取geo地理数据。
由于版权原因,Yandex并没有将geo地理数据开放出来,需要遵照它的字典规范自行导入数据。
外部扩展字典
以插件形式注册到ClickHouse,由用户自行定义数据模式及数据来源。
拥有7种类型,其中flat、hashed和range_hashed依次拥有最高的性能。
“扩展字典”配置文件
扩展字典以配置文件的方式注册到clickhouse。
单个字典配置文件内可以定义多个字典,每一个字典由一组dictionary元素定义,在dictionary元素之下又分为5个子元素,均为必填项。完整的配置结构如下所示:
<?xml version="1.0"?>
<dictionaries>
<dictionary>
<name>dict_name</name>
<structure>
<!—字典的数据结构 -->
</structure>
<layout>
<!—在内存中的数据格式类型 -->
</layout>
<source>
<!—数据源配置 -->
</source>
<lifetime>
<!—字典的自动更新频率 -->
</lifetime>
</dictionary>
</dictionaries>
name:字典名称
字典的名称,是字典的唯一标识,必须全局唯一,不可重复。
structure:字典的数据结构
由键值key和属性attribute两部分组成,分别描述字典的数据标识和字段属性。
<dictionary>
<structure>
<!— <id> 或 <key> -->
<id>
<!—Key属性-->
</id>
<attribute>
<!—字段属性-->
</attribute>
...
</structure>
</dictionary>
key
key用于定义字典的键值,类似数据库的表主键,分为数值型和复合型两类。
(1)数值型:由UInt64整型定义,支持flat、hashed、range_hashed和cache类型的字典
<id>
<!—名称自定义-->
<name>Id</name>
</id>
(2)复合型:使用Tuple元组定义,可以由1到多个字段组成,类似数据库中的复合主键,仅支持complex_key_hashed、complex_key_cache和ip_trie类型的字典。其
<key>
<attribute>
<name>field1</name>
<type>String</type>
</attribute>
<attribute>
<name>field2</name>
<type>UInt64</type>
</attribute>
省略…
</key>
attribute
attribute用于定义字典的属性字段,字典可以拥有1到多个属性
字段。它的完整定义方法如下所示:
<structure>
省略…
<attribute>
<name>Name</name>
<type>DataType</type>
<!—空字符串-->
<null_value></null_value>
<expression>generateUUIDv4()</expression>
<hierarchical>true</hierarchical>
<injective>true</injective>
<is_object_id>true</is_object_id>
</attribute>
省略…
</structure>
layout:字典的类型
扩展字典的类型有7种,既决定了其数据在内存中的存储结构,也决定了该字典支持的key键类型。
单数值型key键:flat、hashed、range_hashed、cache
复合型key键:complex_key_hashed、complex_key_cache、ip_trie
-
flat:性能最高的字典类型,flat字典的数据在内存中使用数组结构保存,数组的初始大小为1024,上限为500 000,这意味着它最多只能保存500 000行数据。
<layout><flat/></layout>
-
hashed:字典数据在内存中通过散列结构保存,无存储上限。
<layout><hashed/></layout>
-
range_hashed:hashed字典的变种,增加了指定时间区间的特性,数据会以散列结构存储并按照时间排序。时间区间通过
<structure>
中的range_min和range_max元素指定,所指定的字段必须是Date或者DateTime类型。-
<layout> <range_hashed/> </layout> <structure> <id> <name>id</name> </id> <range_min> <name>start</name> </range_min> <range_max> <name>end</name> </range_max> <attribute> <name>price</name> <type>Float32</type> <null_value></null_value> </attribute> </structure>
-
-
cache:在内存中以定长的向量数组cells保存,数组长度由size_in_cells指定,size_in_cells的取值必须是2的整数倍,如若不是,则会自动向上取为2的倍数的整数。cache字典与其他字典不同,并不会一次性将所有数据载入内存,而是先在cells数组中检查该数据是否已被缓存,如果没有,再从源头加载数据并缓存到cells中。所以cache字典是性能最不稳定的字典。如果cache字典使用本地文件作为数据源,则source必须使用executable的形式设置。
<layout><cache><size_in_cells>10000</size_in_cells></cache></layout>
-
complex_key_hashed:在功能方面与hashed字典完全相同,只是将单个数值型key替换成了复合型。
-
complex_key_cache:cache字典的特性完全相同,只是将单个数值型key替换成了复合型。
-
ip_trie:虽为复合型key的字典,但只能指定单个String类型的字段,用于指代IP前缀。
-
<structure> <!—虽然是复合类型,但是只能设置单个String类型的字段 --> <key> <attribute> <name>prefix</name> <type>String</type> </attribute> </key> ...
-
source:字典的数据源
决定字典中数据从何处加载,有文件、数据库、其他三类数据来源。
文件
-
本地文件:
-
<source> <file> <path>/data/dictionaries/organization.csv</path> <format>CSV</format> </file> </source>
-
-
可执行文件:属于本地文件的变种,它需要通过cat命令访问数据文件。对于cache和complex_key_cache类型的字典,必须使用此类型的文件数据源。
-
<source> <executable> <command>cat /data/dictionaries/organization.csv</ command> <format>CSV</format> </executable> </source>
-
-
远程文件:post请求方式访问文件,支持HTTP与HTTPS协议。
-
<source> <http> <url>http://10.37.129.6/organization.csv</url> <format>CSV</format> </http> </source>
-
数据库
-
MySql:
-
<source> <mysql> <port>3306</port> <user>root</user> <password></password> <replica> <host>10.37.129.2</host> <priority>1</priority> </replica> <db>test</db> <table>t_organization</table> <!-- <where>id=1</where> <invalidate_query>SQL_QUERY</invalidate_query> --> </mysql> </source>
-
·replica:数据库host地址,支持MySQL集群
·where:查询table时的过滤条件,非必填项。
·invalidate_query:指定一条SQL语句,用于在数据更新时判断是否需要更新,非必填项。
-
-
ClickHouse:
-
<source> <clickhouse> <host>10.37.129.6</host> <port>9000</port> <user>default</user> <password></password> <db>default</db> <table>t_organization</table> <!-- <where>id=1</where> <invalidate_query>SQL_QUERY</invalidate_query> --> </clickhouse> </source>
-
-
MongoDB
-
<source> <mongodb> <host>10.37.129.2</host> <port>27017</port> <user></user> <password></password> <db>test</db> <collection>t_organization</collection> </mongodb> </source>
-
其他
通过ODBC的方式连接PostgreSQL和MS SQLServer数据库作为数据源。
lifetime:字典的更新时间
扩展字典支持数据在线更新更新频率由配置文件中的lifetime元素指定,单位为秒:
<lifetime>
<min>300</min>
<max>360</max>
</lifetime>
min与max分别指定了更新间隔的上下限,ClickHouse会在这个时间区间内随机触发更新动作,当min和max都是0的时候,将禁用字典更新。
对于cache字典而言,lifetime还代表了它的缓存失效时间。
更新操作不影响字典的使用
字典内部拥有版本的概念,在数据更新的过程中,旧版本的字典将持续提供服务,只有当更新完全成功之后,新版本的字典才会替代旧版本。所以更新操作或者更新时发生的异常,并不会对字典的使用产生任何影响。
previous标识:判断字典是否需要更新
部分数据源能够依照标识判断,只有在源数据发生实质变化后才实施更新动作。
- 文件数据源:previous值来自系统文件的修改时间,这
- MySQL(InnoDB)、ClickHouse和ODBC:previous值来源于invalidate_query中定义的SQL语句的返回结果
- MySQL(MyISAM):用MyISAM表引擎的数据表支持通过SHOW TABLE STATUS命令查询修改时间,在invalidate_query中使用以下sql:
SHOW TABLE STATUS WHERE Name = 't_organization'
- 其他数据源:无法依照标识判断是否跳过更新,只要满足lifetime的时间要求,就会执行更新动作。
手动触发更新
触发所有字典更新:SYSTEM RELOAD DICTIONARIES
触发指定字典更新:SYSTEM RELOAD DICTIONARY [dict_name]
“扩展字典”基本操作
字典基本信息查询
SELECT name, type, key, attribute.names, attribute.types, source FROM system.dictionaries
在system.dictionaries系统表内,其主要字段的含义分别如下。
·name:字典的名称,在使用字典函数时需要通过字典名称访问数据。
·type:字典所属类型。
·key:字典的key值,数据通过key值定位。
·attribute.names:属性名称,以数组形式保存。
·attribute.types:属性类型,以数组形式保存,其顺序与attribute.names相同。
·bytes_allocated:已载入数据在内存中占用的字节数。
·query_count:字典被查询的次数。
·hit_rate:字典数据查询的命中率。
·element_count:已载入数据的行数。
·load_factor:数据的加载率。
·source:数据源信息。
·last_exception:异常信息,需要重点关注。如果字典在加载过程中产生异常,那么异常信息会写入此字段。last_exception是获取字典调试信息的主要方式。
数据查询
在正常情况下,字典数据只能通过字典函数获取,如dictGet(‘dict_name’,‘attr_name’,key)函数:
SELECT dictGet('test_flat_dict','name',toUInt64(1))
如果字典使用了复合型key,则需要使用元组作为参数传入:
SELECT dictGet('test_ip_trie_dict', 'asn', tuple(IPv4StringToNum('82.118.230.0')))
ClickHouse还提供了一系列以dictGet为前缀的字典函数,使用方法与dictGet大同小异:
·获取整型数据的函数:dictGetUInt8、dictGetUInt16、dictGetUInt32、
dictGetUInt64、dictGetInt8、dictGetInt16、dictGetInt32、dictGetInt64。
·获取浮点数据的函数:dictGetFloat32、dictGetFloat64。
·获取日期数据的函数:dictGetDate、dictGetDateTime。
·获取字符串数据的函数:dictGetString、dictGetUUID。
字典表
字典表是使用Dictionary表引擎的数据表,可通过字典表读取字典,创建示例:
CREATE TABLE tb_test_flat_dict (
id UInt64,
code String,
name String
) ENGINE = Dictionary(test_flat_dict);
使用DDL创建字典
从19.17.4.11版本开始
CREATE DICTIONARY test_dict(
id UInt64,
value String
)
PRIMARY KEY id
LAYOUT(FLAT())
SOURCE(FILE(PATH '/usr/bin/cat' FORMAT TabSeparated))
LIFETIME(1)
MergeTree原理
只有MergeTree系列的表引擎才支持主键索引、数据分区、数据副本和数据采样这些特性
只有MergeTree系列的表引擎支持ALTER相关操作。
如果给MergeTree系列的表引擎加上Replicated前缀,又会得到一组支持数据副本的表引擎,例如ReplicatedMergeTree、ReplicatedReplacingMergeTree、ReplicatedSummingMergeTree等。
MergeTree的创建
CREATE TABLE [IF NOT EXISTS] [db_name.]table_name (
name1 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
name2 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
省略...
) ENGINE = MergeTree()
[PARTITION BY expr]
[ORDER BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[SETTINGS name=value, 省略...]
MergeTree的存储结构
MergeTree的数据按分区目录的形式、以数据片段的方式写入磁盘,数据片段不可修改,CK通过后台线程,定期合并属于相同分区的数据片段。
分区目录在数据写入时才创建。
同一个分区可能存在多个分区目录,不同批次数据的写入(一批为一次INSERT语句),哪怕属于相同分区,也会生成不同的分区目录。直到后台线程合并同分区的多个目录为一个新目录后,旧目录也不会立即被删除(默认8min后)。
如图,一张数据表的完整物理结构分为三部分: 数据表目录、分区目录、各分区下具体的数据文件。
- checksums.txt:校验文件,二进制格式存储。它保存了余下各类文件(primary.idx、count.txt等)的size大小及size的哈希值,用于快速校验文件的完整性和正确性。
- columns.txt:列信息文件,明文存储了此数据分区下的列字段信息。
- count.txt:计数文件,明文存储了当前数据分区目录下数据的总行数。
- primary.idx:一级索引文件,二进制格式存储稀疏索引,一张MergeTree表只能声明一次一级索引(通过ORDER BY或者PRIMARY KEY)。
- [Column].bin:数据文件,压缩格式存储某一列的数据,以列字段名称命名(例如EventDate.bin等)。
- [Column].mrk:列字段标记文件,二进制格式存储.bin数据文件中数据的偏移量信息。标记文件与稀疏索引
对齐(均按照index_granularity的粒度间隔),又与.bin文件一一对应(每个.bin都有一个对应的.mrk文件),所以建立了primary.idx稀疏索引与.bin数据文件之间的映射关系。primary.idx对应.mrk,.mrk对应.bin
。MergeTree中的每个列字段都有与其对应的.mrk标记文件(例如EventDate.mrk等)。标记文件使得 ClickHouse 可以并行的读取数据。这意味着 SELECT 请求返回行的顺序是不可预测的,可查询时用orderby排序。 - [Column].mrk2:如果使用了自适应大小的索引间隔,则标记文件会以.mrk2命名。它的工作原理和作用与.mrk标记文件相同。
- partition.dat与minmax_[Column].idx:使用了分区才会生成这两个文件,均使用二进制格式,partition.dat保存当前分区下分区表达式最终生成的值,minmax索引记录当前分区下分区字段对应原始数据的最小和最大值。例如EventTime字段对应的原始数据为2019-05-01、2019-05-05,分区表达式为PARTITION BY toYYYYMM(EventTime)。partition.dat中保存的值将会是2019-05,而minmax索引中保存的值将会是2019-05-012019-05-05。
- skp_idx_[Column].idx与skp_idx_[Column].mrk:如果在建表语句中声明了二级索引,则会额外生成相应的二级索引与标记文件,使用二进制存储。
分区目录创建、合并、删除过程
MergeTree分区[PARTITION BY expr]
分区键支持使用任何一个或一组字段表达式声明,其业务语义可以是年、月、日或者组织单位等。
四种分区规则
(1)不指定分区键:如果不使用PARTITION BY声明,则分区ID默认取名为all,所有的数据都会被写入这个all分区。
(2)使用整型:如果分区键取值属于整型,且无法转换为日期类型YYYYMMDD格式,则直接按照该整型的字符形式输出,作为分区ID的取值。
(3)使用日期类型:如果分区键取值属于日期类型,或者是能够转换为YYYYMMDD格式的整型,则使用按照YYYYMMDD进行格式化后的字符形式输出,并作为分区ID的取值。
(4)使用其他类型:如果分区键取值既不属于整型,也不属于日期类型,例如String、Float等,则通过128位Hash算法取其Hash值作为分区ID的取值。
(5)使用元组类型:如果通过元组的方式使用多个分区字段,则分区ID依旧是根据上述规则生成的,只是多个ID之间通过“-”符号依次拼接。如PARTITION BY (length(Code),EventTime)
在下图数据下分区ID为2-20190501 2-20190611
分区目录命名规则
公式:PartitionID_MinBlockNum_MaxBlockNum_Level
例如:201905_1_1_0
(1)PartitionID:分区ID。
(2)MinBlockNum和MaxBlockNum:最小数据块编号与最大数据块编号。 与数据压缩块无关,这里的BlockNum是一个整型的自增长编号,从1开始,每当新创建一个分区目录时,n++。对于一个新的分区目录而言,MinBlockNum与MaxBlockNum取值一样,同等于n,例如201905_1_1_0、201906_2_2_0以此类推。但是也有例外,当分区目录发生合并时,对于新产生的合并目录MinBlockNum与MaxBlockNum有着另外的取值规
则。
(3)Level:合并的层级,可以理解为某个分区被合并过的次数。Level计数并不是全局累加的,其初始值均为0,以分区为单位,如果相同分区发生合并动作,则在相应分区内计数累积加1。
分区目录的合并
前面说到,分区目录在数据写入时才创建,同一个分区可能存在多个分区目录,不同批次数据的写入(一批为一次INSERT语句),哪怕属于相同分区,也会生成不同的分区目录。直到后台线程合并同分区的多个目录为一个新目录后,旧目录也不会立即被删除(默认8min后),但是旧的分区目录已不再是激活状态(active=0),所以在数据查询时,它们会被自动过滤掉。
合并后的新目录命名:
·MinBlockNum:取同一分区内所有目录中最小的MinBlockNum值。
·MaxBlockNum:取同一分区内所有目录中最大的MaxBlockNum值。
·Level:取同一分区内最大Level值并加1。
一级索引
MergeTree定义主键后,会依据index_granularity间隔(默认8192行),为数据表
生成一级索引并保存至primary.idx文件内,索引数据按照PRIMARY KEY排序。
使用稀疏索引实现
primary.idx文件内的一级索引采用稀疏索引实现。
稀疏索引的优势:用少量的索引标记记录大量数据的区间位置信息,数据量越大优势越为明显,占用空间小——所以primary.idx内的索引数据常驻内存,取用速度自然极快
索引粒度index_granularity
在新版本中,ClickHouse提供了自适应粒度大小的特性
数据以索引粒度(默认8192)被标记成多个小的区间,其中每个区间最多8192行数据。
一级索引primary.idx和数据标记.mrk的间隔粒度相同,彼此对齐。而.bin数据文件也会依照间隔粒度生成压缩数据块。
索引数据的生成规则
每间隔index_granularity行数据生成一条索引记录,其索引值依据声明的主键字段的值获取。
如下图,使用CounterID作为主键,则每间隔8192行数据就会取一次CounterID的值作为索引值,索引数据最终会被写入primary.idx文件进行保存。
如果使用多个主键,例如ORDER BY(CounterID,EventDate),则每间隔8192行可以同时取CounterID与EventDate两列的值作为索引值,
能够看出,MergeTree对于稀疏索引的存储是非常紧凑的,索引值前后相连,按照主键字段顺序紧密地排列在一起。
索引查询原理
二级索引
INDEX index_name expr TYPE index_type(...) GRANULARITY granularity
又称跳数索引,是指数据片段按照粒度index_granularity
分割成小块后,将上述SQL的granularity数量的小块组合成一个大的块,对这些大块写入索引信息,帮助查询时减少数据扫描的范围。
如果在建表语句中声明了跳数索引,则会额外生成相应的索引与标记文件(skp_idx_[Column].idx与skp_idx_[Column].mrk)。
跳数索引类型
minmax
存储一段数据内(一个索引范围的数据内)指定表达式的最大最小极值(如果表达式是tuple
,则存储tuple
中每个元素的极值),这些信息用于跳过数据块,类似主键。set(max_rows)
存储指定表达式的不重复值(不超过max_rows
个,max_rows=0
则表示『无限制』)。这些信息可用于检查数据块是否满足WHERE
条件。ngrambf_v1(n, size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed)
存储一个包含数据块中所有 n元短语(ngram) 的布隆过滤器。只可用在字符串上。
只能够提升in、notIn、like、equals和notEquals查询表达式的性能。n
– token长度,依据n的长度将数据切割为token短语。size_of_bloom_filter_in_bytes
– 布隆过滤器大小,字节为单位。(因为压缩得好,可以指定比较大的值,如 256 或 512)。number_of_hash_functions
– 布隆过滤器中使用的哈希函数的个数。random_seed
– 哈希函数的随机种子。
tokenbf_v1(size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed)
tokenbf_v1会自动按照非字符的、数字的字符串分割token,其他的跟ngrambf_v1
类一样
数据的存储
各列独立存储
先用LZ4(默认)、ZSTD、Multiple和Delta等算法压缩;其次,数据会事先依照ORDER BY的声明排序;最后,数据是以压缩数据块的形式被组织并写入.bin文件中的。
压缩数据块
一个压缩数据块由头信息和压缩数据两部分组成。头信息固定使用9位字节表示,具体由1个UInt8(1字节)整型和2个UInt32(4字节)整型组成,分别代表使用的压缩算法类型、压缩后的数据大小和压缩前的数据大小,
每个压缩数据块的体积,按照其压缩前的数据字节大小,都被严格控制在64KB~1MB,其上下限分别由min_compress_block_size(默认65536)与max_compress_block_size(默认1048576)参数指定。
而一个压缩数据块最终的大小,则和一个间隔(index_granularity)内数据的实际大小相关
所以,一个.bin文件是由1至多个压缩数据块组成的,每个压缩块大小在64KB~1MB之间。多个压缩数据块之间,按照写入顺序首尾相接,紧密地排列在一起
虽然数据被压缩后能够有效减少数据大小,降低存储空间并加速数据传输效率,但数据的压缩和解压动作,其本身也会带来额外的性能损耗。所以需要控制被压缩数据的大小。
在具体读取某一列数据时(.bin文件),首先需要将压缩数据加载到内存并解压,通过压缩数据块,可以在不读取整个.bin文件的情况下将读取粒度降低到压缩数据块级别
数据标记
数据标记的生成规则
数据标记是衔接一级索引和数据的桥梁。
标记文件使得 ClickHouse 可以并行的读取数据。这意味着 SELECT 请求返回行的顺序是不可预测的,可查询时用orderby排序。
数据标记和索引区间是对齐的,均按照index_granularity的粒度间隔,只需简单通过索引区间的下标编号就可以直接找到对应的数据标记。
数据标记文件也与.bin文件一一对应。即每一个列字段[Column].bin文件都有一个与之对应的[Column].mrk数
据标记文件,用于记录数据在.bin文件中的偏移量信息。
一行标记数据用一个元组表示,元组内包含两个整型数值的偏移量信息,分别表示在此段数据区间内,在对应的.bin压缩文件中,压缩数据块的起始偏移量;以及将该数据压缩块解压后,其未压缩数据的起始偏移量。
如图6-18所示,每一行标记数据都表示了一个片段的数据(默认8192行)在.bin压缩文件中的读取位置信息。标记数据并不能常驻内存,而是使用LRU(最近最少使用,即淘汰最久未使用的数据)缓存策略加快其取用速度。
数据标记的工作方式
(1)读取压缩数据块: 在查询某一列数据时,MergeTree无须一次性加载整个.bin文件,而是可以根据需要,只加载特定的压缩数据块。而这项特性需要借助标记文件中所保存的压缩文件中的偏移量。
(2)读取数据: 在读取解压后的数据时,MergeTree并不需要一次性扫描整段解压数据,它可以根据需要,以index_granularity的粒度加载特定的一小段。为了实现这项特性,需要借助标记文件中保存的解压数据块中的偏移量。
总结分区、索引、标记、压缩
数据写入过程
生成分区目录——生成primary.idx一级索引、二级索引、,mrk数据标记、.bin压缩数据文件
伴随着每一批数据的写入,都会生成一个新的分区目录。在后续的某一时刻,属于相同分区的目录会依照规则合并到一起;接着,按照index_granularity索引粒度,会分别生成primary.idx一级索引(如果声明了二级索引,还会创建二级索引文件)、每一个列字段的.mrk数据标记和.bin压缩数据文件。
从分区目录201403_1_34_3能够得知,该分区数据共分34批写入,期间发生过3次合并。
在数据写入的过程中,依据index_granularity的粒度,依次为每个区间的数据生成索引、标记和压缩数据块。
索引和标记区间是对齐的,而标记与压缩块则根据区间数据大小的不同,会生成多对一、一对一和一对多三种关系。
数据查询过程
在最理想的情况下,MergeTree依次借助分区索引、一级索引和二级索引,将数据扫描范围缩至最小。然后再借助数据标记,将需要解压与计算的数据范围缩至最小。
如果没有指定WHERE条件,或WHERE条件没有匹配到任何索引(分区索引、一级索引和二级索引),那么MergeTree就不能预先减小数据范围,此时将会扫描所有分区目录,以及目录内索引段的最大区间。但是MergeTree仍能借助数据标记,以多线程的形式同时读取多个压缩数据块,以提升性能。
数据标记与压缩数据块的对应关系
压缩数据块的划分,与一个间隔(index_granularity)内的数据大小相关,每个压缩数据块的体积都被严格控制在64KB~1MB,而一个间隔(index_granularity)的数据,又只会产生一行数据标记。所以,数据标记和压缩数据块之间会产生三种不同的对应关系:
一个间隔的数据大小<64KB——数据标记:压缩数据块=n:1
64KB<=一个间隔的数据大小<=1MB——数据标记:压缩数据块=1:1
1MB<一个间隔的数据大小——数据标记:压缩数据块=1:n
MergeTree系列表引擎
MergeTree
TTL数据存活时间
TTL即Time To Live,可以为某个列字段或整张表设置TTL。
当时间到达时,如果是列字段级别的TTL,则会删除这一列的数据;如果是表级别的TTL,则会删除表中所有与过去的数据(整行删除);如果同时设置了列级别和表级别的TTL,则会以先到期的那个为主。
需要表里有某个DateTime或Date类型的字段,通过对这个时间字段的INTERVAL操作,来表述TTL的过期时间,如:
TTL time_col + INTERVAL 1 MONTH
上述语句表示数据的存活时间是time_col字段的时间值的1月之后。
INTERVAL完整的操作包括SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER和YEAR。
列级别TTL
在定义表的时候声明TTL,也可以对已存在的表字段进行声明。
TTL可以修改,但不能取消。
主键字段不能被声明TTL。
CREATE TABLE ttl_table_v1(
id String,
create_time DateTime,
code String TTL create_time + INTERVAL 10 SECOND,
type UInt8 TTL create_time + INTERVAL 10 SECOND
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(create_time)
ORDER BY id
上例列字段code与type均被设置了TTL,它们的存活时间是在create_time的取值基础之上向后延续10秒。
修改/添加列级别TTL:
ALTER TABLE ttl_table_v1 MODIFY COLUMN code String TTL create_time + INTERVAL 1 DAY
表级别TTL
在定义表的时候声明TTL,也可以对已存在的表字段进行声明。
TTL可以修改,但不能取消。
CREATE TABLE ttl_table_v2(
id String,
create_time DateTime,
code String TTL create_time + INTERVAL 1 MINUTE,
type UInt8
)ENGINE = MergeTree
PARTITION BY toYYYYMM(create_time)
ORDER BY create_time
TTL create_time + INTERVAL 1 DAY
修改/添加表级别TTL:
ALTER TABLE ttl_table_v2 MODIFY TTL create_time + INTERVAL 3 DAY
TTL的运行机理
如果一张MergeTree表被设置了TTL表达式,那么每写入一批数据时,都会基于INTERVAL表达式的计算结果为这个分区生成名为ttl.txt的文件放在分区目录中。
只有在MergeTree合并分区时,才会触发删除TTL过期数据的逻辑。
如果一个分区内某一列数据因为TTL到期全部被删除了,那么在合并之后生成的新分区目录中,将不会包含这个列字段的数据文件(.bin和.mrk)。
在选择删除的分区时,会使用贪婪算法,尽可能找到会最早过期的,同时年纪又是最老的分区(合并次数更多,MaxBlockNum更大的)。
文件内容示例:
ttl format version: 1
{"columns":[{"name":"code","min":1557478860,"max":1557651660}],"table":{"min":1557565200,"max":1557738000}}
- ·columns用于保存列级别TTL信息;
- ·table用于保存表级别TTL信息;
- ·min和max则保存了当前数据分区内,TTL指定日期字段的最小值、最大值加上INTERVAL表达式后的时间戳。例如最小/最大的create_time+INTERVAL 1 DAY
TTL使用贴士
(1)TTL默认的合并频率由MergeTree的merge_with_ttl_timeout参数控制,默认86400秒,即1天。它维护的是一个专有的TTL任务队列。有别于MergeTree的常规合并任务,如果这个值被设置的过小,可能会带来性能损耗。
(2)除了被动触发TTL合并外,也可以使用optimize命令强制触发合并。例如,触发一个分区合并:optimize TABLE table_name
,触发所有分区合并:optimize TABLE table_name FINAL
。
(3)ClickHouse目前虽然没有提供删除TTL声明的方法,但是提供了控制全局TTL合并任务的启
停方法:SYSTEM STOP/START TTL MERGES
。
多路径存储策略
19.15版本之前,MergeTree只支持单路径存储,所有的数据都会被写入config.xml配置中path指定的路径下。
从19.15版本开始,MergeTree实现了自定义存储策略的功能,支持以数据分区为最小移动单元,将分区目录写入多块磁盘目录。
有三类存储策略。
- ·默认策略:MergeTree原本的存储策略,无须任何配置,所有分区会自动保存到config.xml配置中path指定的路径下。
- ·JBOD策略:这种策略适合服务器挂载了多块磁盘,但没有做RAID的场景。JBOD的全称是Just a Bunch of Disks,它是一种轮询策略,每执行一次INSERT或者MERGE,所产生的新分区会轮询写入各个磁盘。这种策略的效果类似RAID 0,可以降低单块磁盘的负载,在一定条件下能够增加数据并行读写的性能。如果单块磁盘发生故障,则会丢掉应用JBOD策略写入的这部分数据。(数据的可靠性需要利用副本机制保障,这部分内容将会在后面介绍副本与分片时介绍。)
- ·HOT/COLD策略:这种策略适合服务器挂载了不同类型磁盘的场景。将存储磁盘分为HOT与COLD两类区域。HOT区域使用SSD这类高性能存储媒介,注重存取性能;COLD区域则使用HDD这类高容量存储媒介,注重存取经济性。数据在写入MergeTree之初,首先会在HOT区域创建分区目录用于保存数据,当分区数据大小累积到阈值时,数据会自行移动到COLD区域。而在每个区域的内部,也支持定义多个磁盘,所以在单个区域的写入过程中,也能应用JBOD策略。
ReplacingMergeTree
MergeTree拥有主键,但没有唯一键约束,也就是主键可以重复。
而ReplacingMergeTree能够在合并分区时删除重复的数据。
- (1)使用ORBER BY排序键(非PRIMARY KEY)作为判断重复数据的唯一键。
- (2)只有在合并分区的时候才会触发删除重复数据的逻辑。
- (3)以数据分区为单位删除重复数据。当分区合并时,同一分区内的重复数据会被删除;不同分区之间的重复数据不会被删除。
- (4)在进行数据去重时,因为分区内的数据已经基于ORBER BY进行了排序,所以能够找到那些相邻的重复数据。
- (5)数据去重策略有两种:
- ·如果没有设置ver版本号,则保留同一组重复数据中的最后一行。
- ·如果设置了ver版本号,则保留同一组重复数据中ver字段取值最大的那一行。
创建ReplacingMergeTree表与创建普通MergeTree表无异,只需要替换Engine:
ENGINE = ReplacingMergeTree(ver)
其中,ver是选填参数,会指定一个UInt*、Date或者DateTime类型的字段作为版本号,这个参数决定了数据去重时所使用的算法。
SummingMergeTree
适用场景:终端用户只需查询数据的汇总结果,不关心明细数据,并且数据的汇总条件是预先明确的(GROUP BY条件明确,且不会随意改变)。
同时指定主键和排序键时,PRIMARY KEY必须是ORDER BY的前缀,如PRIMARY KEY A ORDER BY (A,B)
在修改ORDER BY时,只能①在现有的基础上减少字段;②如果是新增排序字段,则只能添加通过ALTER ADD COLUMN新增的字段:ALTER TABLE table_name MODIFY ORDER BY (A,B)
- (1)用ORBER BY排序键作为聚合数据的条件Key。
- (2)只有在合并分区的时候才会触发汇总的逻辑。
- (3)以数据分区为单位来聚合数据。当分区合并时,同一数据分区内聚合Key相同的数据会被合并汇总,而不同分区之间的数据则不会被汇总。
- (4)如果在定义引擎时指定了columns汇总列(非主键的数值类型字段),则SUM汇总这些列字段;如果未指定,则聚合所有非主键的数值类型字段。
- (5)在进行数据汇总时,因为分区内的数据已经基于ORBER BY排序,所以能够找到相邻且拥有相同聚合Key的数据。
- (6)在汇总数据时,同一分区内,相同聚合Key的多行数据会合并成一行。其中,汇总字段会进行SUM计算;对于那些非汇总字段,则会使用第一行数据的取值。
- (7)支持嵌套结构,但列字段名称必须以Map后缀结尾。嵌套类型中,默认以第一个字段作为聚合Key(order by的效果)。除第一个字段以外,任何名称以Key、Id或Type为后缀结尾的字段,都将和第一个字段一起组成复合Key——这里说的后缀都区分大小写。
创建:ENGINE = SummingMergeTree((col1,col2,…))
,(col1,col2,…)为选填参数,表示需要被汇总结果的字段,如果是单个字段可以不加括号
对于(7),示例如下,嵌套结构字段nestMap以“Map”为后缀,其第一个字段为id,而符合组成符合key的后缀的字段有Key,所以nestMap的id、key会作为复合key用于聚合,而剩余的数值字段val会被汇总:
CREATE TABLE summing_table_nested(
id String,
nestMap Nested(
id UInt32,
Key UInt32,
val UInt64
),
create_time DateTime
)ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(create_time)
ORDER BY id
AggregatingMergeTree
在合并分区时,按预先定义的条件聚合数据,将同一分组下的多行数据按预先设置的函数聚合成一行,并通过二进制的格式存入表内。
定义:ENGINE = AggregatingMergeTree()
- (1)用ORBER BY排序键作为聚合数据的条件Key。
- (2)使用AggregateFunction字段类型定义聚合函数的类型以及聚合的字段。
- (3)只有在合并分区的时候才会触发聚合计算的逻辑。
- (4)以数据分区为单位来聚合数据。当分区合并时,同一数据分区内聚合Key相同的数据会被合并计算,而不同分区之间的数据则不会被计算。
- (5)在进行数据计算时,因为分区内的数据已经基于ORBER BY排序,所以能够找到那些相邻且拥有相同聚合Key的数据。
- (6)在聚合数据时,同一分区内,相同聚合Key的多行数据会合并成一行。对于那些非主键、非AggregateFunction类型字段,则会使用第一行数据的取值。
- (7)AggregateFunction类型的字段使用二进制存储,在写入数据时,需要调用State函数;而在查询数据时,则需要调用相应的Merge函数。其中,*表示定义时使用的聚合函数。
- (8)AggregatingMergeTree通常作为物化视图的表引擎,与普通MergeTree搭配使用。
示例,以id,city为聚合键,code、value为需要预先计算的字段:
CREATE TABLE agg_table(
id String,
city String,
code AggregateFunction(uniq,String),
value AggregateFunction(sum,UInt32),
create_time DateTime
)ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(create_time)
ORDER BY (id,city)
PRIMARY KEY id
读写时要用聚合函数对应的Merge、State函数,如:
INSERT INTO TABLE agg_table SELECT 'A000','wuhan',uniqState('code1'),sumState(toUInt32(100)),'2019-08-10 17:00:00';
SELECT id,city,uniqMerge(code),sumMerge(value) FROM agg_table GROUP BY id,city;
一般不用在物理表上,而是用在物化视图,对物理表写入,对物化视图读取(*Merge()):
CREATE TABLE agg_table_basic(
id String,
city String,
code String,
value UInt32
)ENGINE = MergeTree()
PARTITION BY city
ORDER BY (id,city);
CREATE MATERIALIZED VIEW agg_view
ENGINE = AggregatingMergeTree()
PARTITION BY city
ORDER BY (id,city)
AS SELECT
id,
city,
uniqState(code) AS code,
sumState(value) AS value
FROM agg_table_basic
GROUP BY id, city;
INSERT INTO TABLE agg_table_basic
VALUES('A000','wuhan','code1',100),('A000','wuhan','code2',200),('A000','zhuhai', 'code1',200);
SELECT id, sumMerge(value), uniqMerge(code) FROM agg_view GROUP BY id, city;
CollapsingMergeTree
折叠合并树以新增代替修改删除,通过定义一个Int8类型的sign标记位字段,记录数据行的状态,sign=1表示这是一行有效的数据,sign=-1表示需要被删除,分区合并时,同一数据分区内,sign标记为1和-1的一组数据会被抵消删除。
ENGINE = CollapsingMergeTree(sign)
,sign用于指定一个Int8类型的标志位字段。
示例,删除就是写入主键相同sign相反的行数据,修改就是先删除再写入:
CREATE TABLE collpase_table(
id String,
code Int32,
create_time DateTime,
sign Int8
)ENGINE = CollapsingMergeTree(sign)
PARTITION BY toYYYYMM(create_time)
ORDER BY id
折叠数据时,遵循以下规则——所以要实现折叠效果,对数据的写入顺序就有要求,sign=1要在sign=-1:
- ·如果sign=1比sign=-1的数据多,则保留最后一行sign=1的数据。
- ·如果sign=1比sign=-1的数据少,则保留第一行sign=-1的数据。
- ·如果sign=1和sign=-1的数据行一样多,并且最后一行是sign=1,则保留第一行sign=-1和最后一行sign=1的数据。
- ·如果sign=1和sign=-1的数据行一样多,并且最后一行是sign=-1,则什么也不保留。
- ·其余情况,ClickHouse会打印警告日志,但不会报错,在这种情形下,查询结果不可预知。
其他总结:
- (1)与ReplacingMergeTree、SummingMergeTree、AggregatingMergeTree一样用ORBER BY排序键作为聚合数据的条件Key、只有在合并分区的时候才会触发折叠逻辑、以数据分区为单位来聚合数据、在进行数据计算时因为分区内的数据已经基于ORBER BY排序所以能够找到那些相邻且拥有相同聚合Key的数据。
- (2)由于折叠是在合并分区时触发的,合并前还是会查到旧数据,解决方案有二:
- 在查询数据之前,使用optimize TABLE table_name FINAL命令强制分区合并,但是这种方法效率极低,在实际生产环境中慎用。
- 改变我们的查询方式。如原始SQL是:
SELECT id,SUM(code),COUNT(code),AVG(code),uniq(code) FROM collpase_table GROUP BY id
,则修改后是:SELECT id,SUM(code * sign),COUNT(code * sign),AVG(code * sign),uniq(code * sign) FROM collpase_table GROUP BY id HAVING SUM(sign) > 0
VersionedCollapsingMergeTree
VersionedCollapsingMergeTree表引擎的作用与CollapsingMergeTree完全相同
不同之处:VersionedCollapsingMergeTree对数据的写入顺序没有要求,在同一个分区内,任意顺序的数据都能够完成折叠操作。——通过版本号实现
定义
需要指定sign标记字段,和UInt8类型的ver版本号字段:
ENGINE = VersionedCollapsingMergeTree(sign,ver)
如:
CREATE TABLE ver_collpase_table(
id String,
code Int32,
create_time DateTime,
sign Int8,
ver UInt8
)ENGINE = VersionedCollapsingMergeTree(sign,ver)
PARTITION BY toYYYYMM(create_time)
ORDER BY id
原理
VersionedCollapsingMergeTree会自动将ver字段作为排序条件并增加到ORDER BY的末端。以上面的ver_collpase_table表为例,在每个数据分区内,数据会按照ORDER BY id,ver DESC排序。所以无论写入时数据的顺序如何,在折叠处理时,都能回到正确的顺序。
ReplicatedMergeTree系列
如果给MergeTree系列的表引擎加上Replicated前缀,又会得到一组支持数据副本的表引擎,例如ReplicatedMergeTree、ReplicatedReplacingMergeTree、ReplicatedSummingMergeTree等。
ReplicatedMergeTree在MergeTree能力的基础之上增加了分布式协同的能力,其借助ZooKeeper的消息日志广播功能,实现了副本实例之间的数据同步功能。
其他常见表引擎
外部存储类型-HDFS
注意
删除ck上的hdfs引擎表并不会删除对应的HDFS文件
使用前的准备
假设HDFS环境已经准备就绪,在使用HDFS表引擎之前需:
- 关闭HDFS的Kerberos认证(因为HDFS表引擎目前还不支持Kerberos);
- 在HDFS上创建用于存放文件的目录:
hadoop fs -mkdir /clickhouse
- 在HDFS上给ClickHouse用户授权。例如,为默认用户clickhouse授权的方法如下:
hadoop fs -chown -R clickhouse:clickhouse /clickhouse
定义HDFS表引擎
ENGINE = HDFS(hdfs_uri,format)
·hdfs_uri表示HDFS的文件存储路径;
·format表示文件格式(指ClickHouse支持的文件格式,常见的有CSV、TSV和JSON等)。
两种使用方式
-
·既负责读文件,又负责写文件。即HDFS文件的创建与查询均使用HDFS数据表
-
下例会在hdfs指定目录下创建名为hdfs_table1,往表insert时,hdfs的该文件会按csv格式写入数据:
-
CREATE TABLE hdfs_table1( id UInt32, code String, name String )ENGINE = HDFS('hdfs://hdp1.nauu.com:8020/clickhouse/hdfs_table1','CSV')
-
-
·只负责读文件,文件写入工作则由其他外部系统完成。
- 支持使用通配符、数字区间等方式读取指定路径的文件,并将文件内容映射到hdfs引擎表:
- ①使用绝对路径:读取指定路径的单个文件,例如/clickhouse/hdfs_table1。
- ②使用
*
通配符:匹配所有字符(n个字符),例如读取/clickhouse/hdfs_table2/下的所有文件:ENGINE = HDFS('hdfs://hdp1.nauu.com:8020/clickhouse/hdfs_table2/*','CSV')
- ③使用
?
通配符:匹配单个字符,HDFS('hdfs://hdp1.nauu.com:8020/clickhouse/hdfs_table2/organization_?.csv','CSV')
- ④使用{M…N}数字区间:匹配指定数字的文件,如匹配1~3的数字:
HDFS('hdfs://hdp1.nauu.com:8020/clickhouse/hdfs_table2/organization_{1..3}.csv','CSV')
外部存储类型-MySQL
支持与MySQL数据库中的数据表建立映射,并通过SQL向其发起SELECT和INSERT操作
不支持任何UPDATE和DELETE操作
定义
ENGINE = MySQL('host:port', 'database', 'table', 'user', 'password'[, replace_query, 'on_duplicate_clause'])
其中各参数的含义分别如下:
- ·host:port表示MySQL的地址和端口。
- ·database表示数据库的名称。
- ·table表示需要映射的表名称。
- ·user表示MySQL的用户名。
- ·password表示MySQL的密码。
- ·replace_query默认为0,对应MySQL的REPLACE INTO语法。如果将它设置为1,则会用REPLACE INTO代替INSERT INTO。 关于REPLACE INTO,表示插入数据时,根据主键或唯一索引判断,发现数据已存在,则删除旧数据再插入新输入,否则直接插入,要求该表有主键或唯一索引
- ·on_duplicate_clause默认为0,对应MySQL的ON DUPLICATE KEY语法。如果需要使用该设置,则必须将replace_query设置成0。on dupdate key update 语句基本功能是:当表中没有原来记录时,就插入,有的话就更新。
外部存储类型-JDBC
JDBC表引擎不仅可以对接MySQL数据库,还能够与PostgreSQL、SQLite和H2数据库对接。
**需要依赖名为clickhouse-jdbc-bridge的查询代理服务。**它的项目地址为https://github.com/ClickHouse/clickhouse-jdbc-bridge 。
使用前的准备
-
安装并启动clickhouse-jdbc-bridge代理服务:
java -jar ./clickhouse-jdbc-bridge-1.0.jar --driver-path /chbase/jdbc-bridge --listen-host ch5.nauu.com
- ·–driver-path用于指定放置数据库驱动的目录,例如要代理查询PostgreSQL数据库,则需要将它的驱动jar放置到这个目录。
·–listen-host用于代理服务的监听端口,通过这个地址访问代理服务,ClickHouse的jdbc_bridge配置项与此参数对应。
- ·–driver-path用于指定放置数据库驱动的目录,例如要代理查询PostgreSQL数据库,则需要将它的驱动jar放置到这个目录。
-
在config.xml全局配置中增加代理服务的访问地址:
-
…… <jdbc_bridge> <host>ch5.nauu.com</host> <port>9019</port> </jdbc_bridge> </yandex>
-
-
创建jdbc引擎表
ENGINE = JDBC('jdbc:url', 'database', 'table')
url表示需要对接的数据库的驱动地址;database表示数据库名称;table则表示对接的数据表的名称
jdbc函数
jdbc函数也能够通过clickhouse-jdbc-bridge代理访问其他数据库,例如执行
下面的语句:
SELECT id,name FROM jdbc('jdbc:postgresql://ip:5432/dolphinscheduler?user=test&password=test, '', 't_ds_process_definition')
外部存储类型-KAFKA
kafka支持 最多一次、最少一次、恰好一次三层语义,但是目前ClickHouse还不支持kafka的恰好一次(Exactly once)语义,因为这需要应用端与Kafka深度配合才能实现。
定义
ENGINE = Kafka()
SETTINGS
kafka_broker_list = 'host:port,... ',
kafka_topic_list = 'topic1,topic2,...',
kafka_group_name = 'group_name',
kafka_format = 'data_format'[,]
[kafka_row_delimiter = 'delimiter_symbol']
[kafka_schema = '']
[kafka_num_consumers = N]
[kafka_skip_broken_messages = N]
[kafka_commit_every_batch = N]
其中,带有方括号的参数表示选填项,参数作用:
- ·kafka_broker_list:Broker服务的地址列表,多个地址之间使用逗号分隔
- ·kafka_topic_list:订阅的消息主题的名称列表,多个主题之间使用逗号分隔,例如’topic1,topic2’。多个主题中的数据均会被消费。
- ·kafka_group_name:消费组的名称
- ·kafka_format:用于解析消息的数据格式,在消息的发送端,必须按照此格式发送消息。数据格式必须是ClickHouse提供的格式之一,例如TSV、JSONEachRow和CSV等。
- ·kafka_row_delimiter:表示判定一行数据的结束符,默认值为’\0’。
- ·kafka_schema:对应Kafka的schema参数。
- ·kafka_num_consumers:表示消费者的数量,默认值为1。一个kafka Partition分区只能使用一个消费者。
- ·kafka_skip_broken_messages:当表引擎按照预定格式解析数据出现错误时,允许跳过失败的数据行数,默认值为0,即不允许任何格式错误的情形发生。在此种情形下,只要Kafka主题中存在无法解析的数据,数据表都将不会接收任何数据。如果将其设置为非0正整数,例如kafka_skip_broken_messages=10,表示只要Kafka主题中存在无法解析的数据的总数小于10,数据表就能正常接收消息数据,而解析错误的数据会被自动跳过。
- ·kafka_commit_every_batch:表示执行Kafka commit的频率,默认值为0,即当一整个Block数据块完全写入数据表后才执行Kafka commit。如果将其设置为1,则每写完一个Batch批次的数据就会执行一次Kafka commit(一次Block写入操作,由多次Batch写入操作组成)。
修改KAFKA默认配置
Kafka表引擎底层负责与Kafka通信的部分,是基于librdkafka实现的,它提供了许多自定义的配置参数,如(auto.offset.reset=largest),更多的自定义参数可以在如下地址找到:
https://github.com/edenhill/librdkafka/blob/master/CONFIGURATION.md 。
在ClickHouse的全局设置中,提供了一组Kafka标签,专门用于定义librdkafka的自定义参数。不过需要注意的是,librdkafka的原生参数使用了点连接符,在ClickHouse中需要将其改为下划线的形式,例如:
<kafka>
//librdkafka中,此参数名是auto.offset.reset
<auto_offset_reset>smallest</auto_offset_reset>
</kafka>
KAFKA引擎正确使用方式
Kafka表引擎在执行查询之后就会删除表内的数据!!
正确使用方式是:kafka引擎表+物化视图+其他引擎物理表
- Kafka引擎表A,负责拉取Kafka中的数据。
- 任意引擎的数据表B,作为面向终端用户的查询表,通常是MergeTree系列。
- 物化视图C,负责将表A的数据实时同步到表B。
示例
CREATE TABLE kafka_queue(
id UInt32,
code String,
name String
) ENGINE = Kafka()
SETTINGS
kafka_broker_list = 'hdp1.nauu.com:6667',
kafka_topic_list = 'sales-queue',
kafka_group_name = 'chgroup',
kafka_format = 'JSONEachRow',
kafka_skip_broken_messages = 100
CREATE TABLE kafka_table (
id UInt32,
code String,
name String
) ENGINE = MergeTree()
ORDER BY id
CREATE MATERIALIZED VIEW consumer TO kafka_table
AS SELECT id,code,name FROM kafka_queue
停止kafka引擎表的数据同步
方法1,删除同步kafka数据的物化视图:DROP TABLE consumer
方法2,卸载该物化视图:DETACH TABLE consumer
在卸载了视图之后,如果想要再次恢复,可以使用装载命令:
ATTACH MATERIALIZED VIEW consumer TO kafka_table(
id UInt32,
code String,
name String
)
AS SELECT id, code, name FROM kafka_queue
外部存储类型-File
File表引擎能够直接读取本地文件的数据。
常见使用场景:
- 读取由其他系统生成的数据文件,如果外部系统直接修改了文件,则变相达到了数据更新的目的;
- 将ClickHouse数据导出为本地文件;
- 它还可以用于数据格式转换等场景。
- ……
定义
ENGINE = File(format)
format表示文件中的数据格式,其类型必须是ClickHouse支持的数据格式,例如TSV、CSV和JSONEachRow等。
File表引擎的数据文件只能保存在config.xml配置中由path指定的路径下。
每张File数据表在服务端均由目录和文件组成,其中目录以表的名称命名,数据文件则固定以data.format命名,如data.csv
使用
CREATE TABLE file_table (
name String,
value UInt32
) ENGINE = File("CSV")
上例将在配置文件指定路径下创建名为file_table的目录,此时目录下还没有文件,insert向表写数据后,目录下多一个文件data.CSV
也可以先手动创建文件,再创建表:
//切换到clickhouse用户,以确保ClickHouse有权限读取目录和文件
# su clickhouse
//创建表目录
# mkdir /chbase/data/default/file_table1
//创建数据文件
# mv /chbase/data/default/file_table/data.CSV /chbase/data/default/file_table1
内存类型-Memory
Memory表引擎直接将数据保存在内存中,数据既不会被压缩也不会被格式转换,当ClickHouse服务重启的时候,Memory表内的数据会全部丢失。
CREATE TABLE memory_1 (
id UInt64
)ENGINE = Memory()
多用于测试表,或Ck内部使用。
内存类型-Set
Set表引擎的数据首先会被写至内存,然后被同步到磁盘文件中。服务重启时,它的数据不会丢失,当数据表被重新装载时,文件数据会再次被全量加载至内存。
具有去重能力,写入时自动忽略重复数据。
虽然支持正常的INSERT写入,但不能直接使用SELECT进行查询,只能间接作为IN查询的右侧条件被查询使用。
Set表引擎的存储结构
由两部分组成,它们分别是:
- ·[num].bin数据文件:保存了所有列字段的数据。其中,num是一个自增id,从1开始。伴随着每一批数据的写入(每一次INSERT),都会生成一个新的.bin文件,num也会随之加1。
- ·tmp临时目录:数据文件首先会被写到这个目录,当一批数据写入完毕之后,数据文件会被移出此目录。
使用
CREATE TABLE set_1 (
id UInt8
)ENGINE = Set()
正确的查询方法是将Set表引擎作为IN查询的右侧条件,例如:
SELECT arrayJoin([1, 2, 3]) AS a WHERE a IN set_1
内存类型-Join
Join表引擎主要用于JOIN查询。
其底层实现与Set表引擎共用了大部分的处理逻辑,所以两者很相似,例如,
- Join表引擎的存储也由[num].bin数据文件和tmp临时目录两部分组成;
- 数据首先会被写至内存,然后被同步到磁盘文件。
但是,Join表引擎能够被直接查询使用。
使用
ENGINE = Join(join_strictness, join_type, key1[, key2, ...])
·join_strictness:连接精度,它决定了JOIN查询在连接数据时所使用的策略,目前支持ALL、ANY和ASOF三种类型。
·join_type:连接类型,它决定了JOIN查询组合左右两个数据集合的策略,它们所形成的结果是交集、并集、笛卡儿积或其他形式,目前支持INNER、OUTER和CROSS三种类型。当join_type被设置为ANY时,在数据写入时,join_key重复的数据会被自动忽略。
·join_key:连接键,它决定了使用哪个列字段进行关联。
示例
CREATE TABLE id_join_tb1(
id UInt8,
price UInt32,
time Datetime
) ENGINE = Join(ANY, LEFT, id)
CREATE TABLE join_tb1(
id UInt8,
name String,
time Datetime
) ENGINE = Log
SELECT id,name,price FROM join_tb1 LEFT JOIN id_join_tb1 USING(id)
join函数
Join表引擎除了可以直接使用SELECT和JOIN访问之外,还可以通过join函数访问,例如:
SELECT joinGet('id_join_tb1', 'price', toUInt8(1))
内存类型-Buffer
Buffer表引擎完全使用内存装载数据,当服务重启之后数据清空。
作用:充当缓冲区
场景:高并发写入时,将Buffer表作为数据写入的缓冲区,数据首先被写入Buffer表,满足预设条件时,Buffer表自动将数据刷新到目标表,如图:
定义
ENGINE = Buffer(database, table, num_layers, min_time, max_time, min_rows, max_rows, min_bytes, max_bytes)
基础参数
·database:目标表的数据库。
·table:目标表的名称,Buffer表内的数据会自动刷新到目标表。
·num_layers:可以理解成线程数,Buffer表会按照num_layers的数量开启线程,以并行的方式将数据刷新到目标表,官方建议设为16。
阈值参数
Buffer表并不是实时刷新数据的,只有在阈值条件满足时它才会刷新。
阈值条件由以下参数组成:
·min_time和max_time:时间条件的最小和最大值,单位为秒,从第一次向表内写入数据的时候开始计算;
·min_rows和max_rows:数据行条件的最小和最大值;
·min_bytes和max_bytes:数据体量条件的最小和最大值,单位为字节。
刷新数据的条件
满足以下任意一个,Buffer表就会刷新数据:
·如果三组条件中所有的最小阈值都已满足,则触发刷新动作;
·如果三组条件中至少有一个最大阈值条件满足,则触发刷新动作;
·如果写入的一批数据的数据行大于max_rows,或者数据体量大于max_bytes,则数据直接被写入目标表。
注意:上述三组条件在每一个num_layers中都是单独计算的
日志类型
如果使用的数据量很小(100万以下),面对的数据查询场景也比较简单,并且是“一次”写入多次查询的模式,那么使用日志家族系列的表引擎将会是一种不错的选择。一些共性特征。例如:它们均不支持索引、分区等高级特性;不支持并发读写,当针对一张日志表写入数据时,针对这张表的查询会被阻塞,直至写入动作结束;但它们也同时拥有切实的物理存储,数据会被保存到本地文件中。
TinyLog
TinyLog是日志家族系列中性能最低的表引擎,它的存储结构由数据文件(.bin)和元数据(sizes.json)两部分组成。其中,数据文件是按列独立存储的,也就是说每一个列字段都拥有一个与之对应的.bin文件。
TinyLog既不支持分区,也没有.mrk标记文件。由于没有标记文件,它自然无法支持.bin文件的并行读取操作。
只适合在非常简单的场景下使用。
sizes.json元数据文件内使用JSON格式记录了每个.bin文件内对应的数据大小的信息。
示例:
CREATE TABLE tinylog_1 (
id UInt64,
code UInt64
)ENGINE = TinyLog()
StripeLog
StripeLog表引擎的存储结构由固定的3个文件组成:
- ·一个data.bin:数据文件,所有的列字段数据使用同一个文件保存
- ·一个index.mrk:数据标记,保存了数据在data.bin文件中的位置信息。
- ·一个sizes.json:元数据文件,记录了data.bin和index.mrk大小的信息。
相比TinyLog而言,StripeLog拥有更高的查询性能(拥有.mrk标记文件,支持并行查询),同时其使用了更少的文件描述符(所有数据使用同一个文件保存)。
CREATE TABLE spripelog_1 (
id UInt64,
price Float32
)ENGINE = StripeLog()
Log
日志家族系列中性能最高的表引擎。
由3个部分组成:
·[column].bin:数据文件,数据文件按列独立存储,每一个列字段都拥有一个与之对应的.bin文件。
·一个marks.mrk:数据标记,统一保存了数据在各个[column].bin文件中的位置信息。
·一个sizes.json:元数据文件,记录了[column].bin和marks.mrk大小的信息。
由于拥有数据标记且各列数据独立存储,所以Log既能够支持并行查询,又能够按列按需读取。
CREATE TABLE log_1 (
id UInt64,
code UInt64
)ENGINE = Log()
接口类型
自身不存储数据
Merge
Merge表引擎本身不存储任何数据,也不支持数据写入,而是异步、并行地查询任意数量的数据表,并合并为一个结果集。
被代理查询的数据表被要求处于同一个数据库内,且拥有相同的表结构,但是它们可以使用不同的表引擎以及不同的分区定义(对于MergeTree而言)。
定义:
ENGINE = Merge(database, table_name)
database表示数据库名称;table_name表示数据表的名称,它支持使用正则表达式,例如^test表示合并查询所有以test为前缀的数据表。
示例:
假设有表字段相同的数据表test_table_1、test_table_2,以下merge表可合并查询这两张表的数据:
CREATE TABLE test_table_all as test_table_2018 ENGINE = Merge(currentDatabase(), '^test_table_')
SELECT _table,* FROM test_table_all WHERE _table = 'test_table_2018'
“_table”是虚拟字段,表示被合并的表的表名,可当做普通字段操作
Dictionary
Dictionary表引擎是数据字典的一层代理封装,它可以取代字典函数(dictGet系列),让用户通过数据表查询字典。
字典内的数据被加载后,会全部保存到内存中
定义:
ENGINE = Dictionary(dict_name)
CREATE TABLE tb_test_flat_dict (
id UInt64,
code String,
name String
)Engine = Dictionary(test_flat_dict);ENGINE = Dictionary(dict_name)
Dictionary引擎类型的数据库
如果字典的数量很多,逐一为它们创建各自的Dictionary表未免过于烦琐。这时候可以使用Dictionary引擎类型的数据库,ck会自动将已加载的数据字典注册成该库中的字典引擎表
CREATE DATABASE test_dictionaries ENGINE = Dictionary
Distributed分布式表引擎
其他类型
Live View
Live View是一种特殊的视图,类似事件监听器,能够将一条SQL查询结果作为监控目标,当目标数据增加时,Live View可以及时发出响应。
若要使用Live View,首先需要将allow_experimental_live_view参数设置为1,可以执行如下语句确认参数是否设置正确:SELECT name, value FROM system.settings WHERE name LIKE '%live_view%'
示例:
-- 创建一个live view
CREATE LIVE VIEW lv_origin AS SELECT COUNT(*) FROM origin_table1
-- 开始监听
WATCH lv_origin
Null表引擎
如果向Null表写入数据,系统会正确返回,但是Null表不会保存数据,如果用户向Null表发起查询,那么它将返回一张空表。
在使用物化视图的时候,如果不希望保留源表的数据,那么将源表设置成Null引擎将会是极好的选择。
CREATE TABLE null_table1(
id UInt8
) ENGINE = Null
CREATE MATERIALIZED VIEW view_table10
ENGINE = TinyLog
AS SELECT * FROM null_table1
现在向null_table1写入数据,会发现数据被顺利同步到了视图view_table10中,而源表null_table1依然空空如也。
URL表引擎
URL表引擎的作用等价于HTTP客户端,它可以通过HTTP/HTTPS协议,直接访问远端的REST服务。
执行SELECT查询时,底层会将其转换为GET请求的远程调用。
执行INSERT查询时,转换为POST请求。
定义:
ENGINE = URL('url', format)
其中,url表示远端的服务地址,而format则是ClickHouse支持的数据格式,如TSV、CSV和JSON等。