ClickHouse介绍
ClickHouse介绍【优缺点】
ClickHouse 是俄罗斯搜索巨头 Yandex 公司早 2016年 开源的一个极具 " 战斗力 " 的实时数据分析
数据库,开发语言为C++,是一个用于联机分析 (OLAP:Online Analytical Processing) 的列式数据
库管理系统(DBMS:Database Management System),简称 CK,工作速度比传统方法快100-1000
倍,ClickHouse 的性能超过了目前市场上可比的面向列的DBMS。 每秒钟每台服务器每秒处理数
亿至十亿多行和数十千兆字节的数据
优点
-
真正的面向列的DBMS(ClickHouse是一个DBMS,而不是一个单一的数据库。它允许在运行时创建表和数据库、加载数据和运行查询,而无需重新配置和重新启动服务器)
-
数据压缩(一些面向列的DBMS(INFINIDB CE 和 MonetDB)不使用数据压缩。但是,数据压缩确实是提高了性能)
-
磁盘存储的数据(许多面向列的DBMS(SPA HANA和GooglePowerDrill))只能在内存中工作。但即使在数千台服务器上,内存也太小了。)
-
多核并行处理(多核多节点并行化大型查询)
-
在多个服务器上分布式处理(在clickhouse中,数据可以驻留在不同的分片上。每个分片都可以用于容错的一组副本,查询会在所有分片上并行处理)
-
SQL支持(ClickHouse sql 跟真正的sql有不一样的函数名称。不过语法基本跟SQL语法兼容,支持
JOIN/FROM/IN 和JOIN子句及标量子查询支持子查询) -
向量化引擎(数据不仅按列式存储,而且由矢量-列的部分进行处理,这使得开发者能够实现高CPU
性能) -
实时数据更新(ClickHouse支持主键表。为了快速执行对主键范围的查询,数据使用合并树(MergeTree)进行递增排序。由于这个原因,数据可以不断地添加到表中)
-
支持近似计算(统计全国到底有多少人?143456754 14.3E)
-
数据复制和对数据完整性的支持(ClickHouse使用异步多主复制。写入任何可用的复本后,数据将分发到所有剩余的副本。系统在不同的副本上保持相同的数据。数据在失败后自动恢复)
缺点
- 没有完整的事务支持,不支持Transaction想快就别Transaction
- 缺少完整Update/Delete操作,缺少高频率、低延迟的修改或删除已存在数据的能力,仅用于批量删除或修改数据。
- 聚合结果必须小于一台机器的内存大小
- 支持有限操作系统,正在慢慢完善
- 不适合Key-value存储,不支持Blob等文档型数据库
存储系统架构
组件 | 介绍 |
---|---|
field | 列中具体的值 |
column | 一列数据 |
DataType | 用来描述一列数据的类型 |
Block | 数据colume、datatype列名的组合体,称为Block,表操作的对象就是Block |
BlockStream | 负责block数据的读取写出 |
Format | 想客户端展示数据的方式 |
读写IO | 有一个缓冲区负责数据的读写 |
数据表table | 多个列的结合体,读取数据的时候以表为单位,操作的时候以BlockStream操作Block |
解析器Parser | 对sql语句进行解析 |
解释器Interpreter | 解析SQL语句 |
函数Functions | 单行函数,组函数 |
Cluster与Replication | 分片 ( Shard )、副本 ( Replica ) |
临时表
ClickHouse也有临时表的概念,创建临时表的方法是在普通表的基础之上添加TEMPORARY关键字
- 特点
- 它的生命周期是会话绑定的,所以它只支持Memory表引擎,如果会话结束,数据表就会被销毁;
- 临时表不属于任何数据库,所以在它的建表语句中,既没有数据库参数也没有表引擎参数。
- 临时表的优先级是大于普通表的。当两张数据表名称相同的时候,会优先读取临时表的数据
ClickHouse删除修改表内容
ClickHouse提供了DELETE和UPDATE的能力,这类操作被称为Mutation查询,它可以看作ALTER语句的变种
- Mutation语句是一种“很重”的操作,更适用于批量数据的修改和删除;
- 它不支持事务,一旦语句被提交执行,就会立刻对现有数据产生影响,无法回滚;
- Mutation语句的执行是一个异步的后台过程,语句被提交之后就会立即返回。
-
DELETE语句的完整语法
ALTER TABLE [db_name.]table_name DELETE WHERE filter_expr ALTER TABLE partition_v2 DELETE WHERE ID = 'A003'
-
UPDATE语句的完整语法
ALTER TABLE [db_name.]table_name UPDATE column1 = expr1 [, ...] WHERE filter_expr ALTER TABLE partition_v2 UPDATE URL = 'www.wayne.com',OS = 'mac' WHERE ID IN(10,20,30)
一:表引擎之MergeTree
- 表引擎是ClickHouse设计实现中的一大特色
- ClickHouse拥有非常庞大的表引擎体系,其共拥有合并树、外部存储、内存、文件、接口和其他6大类20多种表引擎。
- 合并树家族自身也拥有多种表引擎的变种。其中MergeTree作为家族中最基础的表引擎,提供了主键索引、数据分区、数据副本和数据采样等基本能力,而家族中其他的表引擎则在MergeTree的基础之上各有所长。
创建和存储的方式
- MergeTree在写入一批数据时,数据总会以数据片段的形式写入磁盘,且数据片段不可修改。
- 为了避免片段过多,ClickHouse会通过后台线程,定期合并这些数据片段,属于相同分区的数据片段会被合成一个新的片段。
- 这种数据片段往复合并的特点,也正是合并树名称的由来
创建方式
-- 创建一张表,使用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]
--表示索引的粒度,默认值为8192,MergeTree的索引在默认情况下,每间隔8192行数据才生成一条索引,
[SETTINGS name=value, 省略...]
存储方式
- partition:分区目录,余下各类数据文件(primary.idx、[Column].mrk、[Column].bin等)都是以分区目录的形式被组织存放的,属于相同分区的数据,最终会被合并到同一个分区目录,而不同分区的数据,永远不会被合并在一起。
- checksums.txt:校验文件,使用二进制格式存储。它保存了余下各类文件(primary.idx、
- count.txt等)的size大小及size的哈希值,用于快速校验文件的完整性和正确性。
- columns.txt:列信息文件,使用明文格式存储。用于保存此数据分区下的列字段信息
- count.txt:计数文件,使用明文格式存储。用于记录当前数据分区目录下数据的总行数,
- primary.idx:一级索引文件,使用二进制格式存储。用于存放稀疏索引,一张MergeTree表只能声明一次一级索引(通过ORDERBY或者PRIMARY KEY)。借助稀疏索引,在数据查询的时能够排除主键条件范围之外的数据文件,从而有效减少数据扫描范围,加速查询速度。
- [Column].bin:数据文件,使用压缩格式存储,默认为LZ4压缩格式,用于存储某一列的数据。由于MergeTree采用列式存储,所以每一个列字段都拥有独立的.bin数据文件,并以列字段名称命名(例如CounterID.bin、EventDate.bin等)。
- [Column].mrk:列字段标记文件,使用二进制格式存储。标记文件中保存了.bin文件中数据的偏移量信息。标记文件与稀疏索引对齐,又与.bin文件一一对应,所以MergeTree通过标记文件建立了primary.idx稀疏索引与.bin数据文件之间的映射关系。即首先通过稀疏索引
- (primary.idx)找到对应数据的偏移量信息(.mrk),再通过偏移量直接从.bin文件中读取数据。由于.mrk标记文件与.bin文件一一对应,所以MergeTree中的每个列字段都会拥有与其对应的.mrk标记文件(例如CounterID.mrk、EventDate.mrk等)。
- [Column].mrk2:如果使用了自适应大小的索引间隔,则标记文件会以.mrk2命名。它的工作原理和作用与.mrk标记文件相同。
- partition.dat与minmax_[Column].idx:如果使用了分区键,例如PARTITION BY
- EventTime,则会额外生成partition.dat与minmax索引文件,它们均使用二进制格式存储。
- partition.dat用于保存当前分区下分区表达式最终生成的值;而minmax索引用于记录当前分区下分区字段对应原始数据的最小和最大值。
- skp_idx[Column].idx与skp_idx[Column].mrk:如果在建表语句中声明了二级索引,则会额外生成相应的二级索引与标记文件,它们同样也使用二进制存储。二级索引在ClickHouse中又称跳数索引,目前拥minmax、set、ngrambf_v1和tokenbf_v1四种类型。这些索引的最终目标与一级稀疏索引相同,都是为了进一步减少所需扫描的数据范围,以加速整个查询过程。
分区
分区规则
-
MergeTree数据分区的规则由分区ID决定,而具体到每个数据分区所对应的ID,则是由分区键的取值决定的
-
分区键支持使用任何一个或一组字段表达式声明,其业务语义可以是年、月、日或者组织单位等任何一种规则。
-
针对取值数据类型的不同,分区ID的生成逻辑目前拥有四种规则:
- 不指定分区键:
-
如果不使用分区键,即不使用PARTITION BY声明任何分区表达式,则分区ID默认取名为all,所有的数据都会被写入这个all分区。
- 使用整型:
-
如果分区键取值属于整型(兼容UInt64,包括有符号整型和无符号整型),且无法转换为日期类型YYYYMMDD格式
- 则直接按照该整型的字符形式输出,作为分区ID的取值。
-
使用日期类型:
- 如果分区键取值属于日期类型,或者是能够转换为YYYYMMDD格式的整型
- 则使用按照YYYYMMDD进行格式化后的字符形式输出,并作为分区ID的取值。
- 使用其他类型:
- 如果分区键取值既不属于整型,也不属于日期类型
- 例如String、Float等,则通过128位Hash算法取其Hash值作为分区ID的取值
- 如果分区键取值属于日期类型,或者是能够转换为YYYYMMDD格式的整型
分区目录
x1_ [MinBlockNum]x2_ [MaxBlockNum]x3_ [lLeve]x4格式
x1表示分区目录id
x2和x3表示分区中数据块的最小编号和最大编号
x4是表示这个分区合并了几次
分区目录合并
MergeTree的分区目录和传统意义上其他数据库有所不同
- 在其他某些数据库的设计中,追加数据后目录自身不会发生变化,只是在相同分区目录中追加新的数据文件。
- 而MergeTree完全不同,伴随着每一批数据的写入(一次INSERT语句),MergeTree都会生成一批新的分区目录
- 即便不同批次写入的数据属于相同分区,也会生成不同的分区目录。也就是说,对于同一个分区而言,也会存在多个分区目录的情况。
- 在之后的某个时刻(写入后的10~15分钟,也可以手动执行optimize查询语句)
- ClickHouse会通过后台任务再将属于相同分区的多个目录合并成一个新的目录。已经存在的旧分区目录并不会立即被删除,而是在之后的某个时刻通过后台任务被删除(默认8分钟)
合并规则
- MinBlockNum:取同一分区内所有目录中最小的MinBlockNum值。
- MaxBlockNum:取同一分区内所有目录中最大的MaxBlockNum值。
- Level:取同一分区内最大Level值并加1
索引
一级索引
- 使用稀疏索引的方式
- MergeTree的主键使用PRIMARY KEY定义,待主键定义之后,MergeTree会依据index_granularity间隔(默认8192行),为数据表生成一级索引并保存至primary.idx文件内,索引数据按照PRIMARYKEY排序
- 索引文件查看命令【od -An -i -w4 primary.idx】
查询过程
- MarkRange
- MarkRange在ClickHouse中是用于定义标记区间的对象
- MergeTree按照index_granularity的间隔粒度,将一段完整的数据划分成了多个小的间隔数据段,一个具体的数据段即是一个MarkRange
- MarkRange与索引编号对应,使用start和end两个属性表示其区间范围
- 举例分析
- 对于查询的数据拆分为N份【默认8份】每份数据就是一个
MarkRange
- 对于查询的条件,和每个MarkRange的start和end对比
- 如果不存在交集,则直接通过剪枝算法优化此整段MarkRange。
- 如果存在交集,且MarkRange步长大于8(end-start),则将此区间进一步拆分成8个子区间,并重复此规则,继续做递归交集判断。
- 如果存在交集,且MarkRange不可再分解(步长小于8),则记录MarkRange并返回
- 合并MarkRange根据条件查询到的区间:将最终匹配的MarkRange聚在一起,合并它们的范围
- 对于查询的数据拆分为N份【默认8份】每份数据就是一个
二级索引方式
-
minmax:
- minmax索引记录了一段数据内的最小和最大极值,其索引的作用类似分区目录的minmax索引,能够快速跳过无用的数据区间
-
set:
- set索引直接记录了声明字段或表达式的取值(唯一值,无重复),其完整形式为set(max_rows),其中max_rows是一个阈值,表示在一个index_granularity内,索引最多记录的数据行数。
-
ngrambf_v1:
- ngrambf_v1索引记录的是数据短语的布隆表过滤器,只支持String和FixedString数据类型。
- ngrambf_v1只能够提升in、notIn、like、equals和notEquals查询的性能
- 其完整形式
- ngrambf_v1(n,size_of_bloom_filter_in_bytes,number_of_hash_functions,random_seed)。
这些参数是一个布隆过滤器的标准输入,如果你接触过布隆过滤器,应该会对此十分熟悉。它们具体的含义如下: - n:token长度,依据n的长度将数据切割为token短语。
- size_of_bloom_filter_in_bytes:布隆过滤器的大小。
- number_of_hash_functions:布隆过滤器中使用Hash函数的个数。
- random_seed:Hash函数的随机种子。
- ngrambf_v1(n,size_of_bloom_filter_in_bytes,number_of_hash_functions,random_seed)。
-
tokenbf_v1:
- tokenbf_v1索引是ngrambf_v1的变种,同样也是一种布隆过滤器索引。
- tokenbf_v1除了短语token的处理方法外,其他与ngrambf_v1是完全一样的。
- tokenbf_v1会自动按照非字符的、数字的字符串分割token
数据存储
列式存储
数据压缩
数据标记
数据读写流程
二:MergeTree Family
1.Merge Tree
数据TTL
多路径存储
2.Replacing Merge Tree
3.SummingMergeTree
4.AggregatingMergeTree
5.CollapsingMergeTree
6.VersionedCollapsingMergeTree
三:常见类型表引擎
外部存储
外部存储表引擎直接从其他的存储系统读取数据
例如直接读取HDFS的文件或者MySQL数据库的表。
这些表引擎只负责元数据管理和数据查询,而它们自身通常并不负责数据的写入,数据文件直接由外部系统提供
HDFS
--在HDFS上创建用于存放文件的目录
hadoop fs -mkdir /clickhouse
--在HDFS上给ClickHouse用户授权
hadoop fs -chown -R clickhouse:clickhouse /clickhouse
--hdfs_uri表示HDFS的文件存储路径,format表示文件格式
ENGINE = HDFS(hdfs_uri,format)
CREATE TABLE hdfs_table1(
id UInt32,
code String,
name String
)ENGINE = HDFS('hdfs://node01:8020/clickhouse/hdfs_table1','CSV');
INSERT INTO hdfs_table1 SELECT number,concat('code',toString(number)),concat('n',toString(number)) FROM numbers(5)
--其他方式,只能读取
ENGINE =HDFS('hdfs://hdp1.nauu.com:8020/clickhouse/hdfs_table2/*','CSV')
ENGINE =HDFS('hdfs://hdp1.nauu.com:8020/clickhouse/hdfs_table2/organization_{1..3}.csv','CSV')
ENGINE =HDFS('hdfs://hdp1.nauu.com:8020/clickhouse/hdfs_table2/organization_?.csv','CSV')
Mysql
-- 设置引擎
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,则会用
-- REPLACEINTO代替INSERT INTO。
-- on_duplicate_clause默认为0,对应MySQL的ON DUPLICATE KEY语法。如果需要使用该设置,则必须将replace_query设置成0
操作方式
CREATE TABLE mysql_dept(
deptno UInt32,
dname String,
loc String
)ENGINE = MySQL('192.168.88.101:3306', 'scott', 'dept', 'root',
'123456');
SELECT * FROM mysql_dept
INSERT INTO TABLE mysql_dept VALUES (50,'干饭部','207')
--目前MySQL表引擎不支持任何UPDATE和DELETE操作
Kafka
目前ClickHouse还不支持恰好一次(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:表示订阅消息主题的名称列表,多个主题之间使用逗号分隔。
- kafka_group_name:表示消费组的名称,表引擎会依据此名称创建Kafka的消费组。
- kafka_format:表示用于解析消息的数据格式,在消息的发送端,必须按照此格式发送消息。数据格式必须是ClickHouse提供的格式之一
- 例如TSV、JSONEachRow和CSV等。
- 选填参数:
- kafka_row_delimiter:表示判定一行数据的结束符,默认值为’\0’。
- kafka_schema:对应Kafka的schema参数。
- kafka_num_consumers:表示消费者的数量,默认值为1。
- kafka_skip_broken_messages:当表引擎按照预定格式解析数据出现错误时,允许跳过失败的数据行数,默认值为0,即不允许任何格式错误的情形发生。
- kafka_commit_every_batch:表示执行Kafka commit的频率
- 默认值为0,即当一整个Block数据块完全写入数据表后才执行Kafka commit。
- 如果将其设置为1,则每写完一个Batch批次的数据就会执行一次Kafka commit
操作方式
CREATE TABLE kafka_table(
id UInt32,
code String,
name String
) ENGINE = Kafka()
SETTINGS
kafka_broker_list = 'node01:9092',
kafka_topic_list = 'topic_clickhouse',
kafka_group_name = 'clickhouse',
kafka_format = 'TabSeparated',
kafka_skip_broken_messages = 10;
--使用Java代码产生数据
public static void main(String[] args) throws InterruptedException {
for (int i = 0; i < 1000; i++) {
YjxKafkaUtil.sendMsg("topic_clickhouse", i + "\tcode\tname");
Thread.sleep(1000);
}
}
--再次执行SELECT查询会发现kafka_table数据表空空如也,这是因为Kafka表引擎在执行查询之后就会移动offset,导致数据无法重复读取
-- 首先是Kafka数据表A,它充当的角色是一条数据管道,负责拉取Kafka中的数据。
-- 接着是另外一张任意引擎的数据表B,它充当的角色是面向终端用户的查询表,在生产环境中通常是MergeTree系列。
-- 最后,是一张物化视图C,它负责将表A的数据实时同步到表B
CREATE TABLE kafka_queue(
id UInt32,
code String,
name String
) ENGINE = Kafka()
SETTINGS
kafka_broker_list = 'node01:9092',
kafka_topic_list = 'topic_clickhouse',
kafka_group_name = 'clickhouse',
kafka_format = 'TabSeparated',
kafka_skip_broken_messages = 10;
CREATE TABLE kafka_view (
id UInt32,
code String,
name String
) ENGINE = MergeTree()
ORDER BY id;
CREATE MATERIALIZED VIEW consumer TO kafka_view AS SELECT id,code,name FROM kafka_queue;
File
- File表引擎能够直接读取本地文件的数据,通常被作为一种扩充手段来使用。
- File表引擎的定义参数中,并没有包含文件路径这一项。所以,File表引擎的数据文件只能保存在config.xml配置中由path指定的路径下。
- 每张File数据表均由目录和文件组成,其中目录以表的名称命名,而数据文件则固定以data.format
命名
--自动创建
CREATE TABLE file_table (
name String,
value Int32
) ENGINE = File("CSV")
INSERT INTO file_table VALUES ('one', 1), ('two', 2), ('three', 3)
--插入数据 自动创建
# /var/lib/clickhouse/data/yjxxt/file_table///data.CSV
--手动创建
//创建表目录
# mkdir /chbase/data/default/file_table1
//创建数据文件
# mv /chbase/data/default/file_table/data.CSV
/chbase/data/default/file_table1
ATTACH TABLE file_table1(
name String,
value UInt32
)ENGINE = File(CSV)
INSERT INTO file_table1 VALUES ('four', 4), ('five', 5)
内存类型
- 将数据全量放在内存中,对于表引擎来说是一把双刃剑:
- 一方面,这意味着拥有较好的查询性能;
- 另一方面,如果表内装载的数据量过大,可能会带来极大的内存消耗和负担
Memory
- Memory表引擎直接将数据保存在内存中,数据既不会被压缩也不会被格式转换,数据在内存中保存的形态与查询时看到的如出一辙。
- 当ClickHouse服务重启的时候,Memory表内的数据会全部丢失。
- 当数据被写入之后,磁盘上不会创建任何数据文件
CREATE TABLE memory_1 (
id UInt64
)ENGINE = Memory()
set
-
Set表引擎是拥有物理存储的,数据首先会被写至内存,然后被同步到磁盘文件中。
-
所以当服务重启时,它的数据不会丢失,当数据表被重新装载时,文件数据会再次被全量加载至内存。
-
Set表引擎具有去重的能力,在数据写入的过程中,重复的数据会被自动忽略。
-
Set表引擎的存储结构由两部分组成,它们分别是:
- [num].bin数据文件:保存了所有列字段的数据。其中,num是一个自增id,从1开始。伴随着每一批数据的写入(每一次INSERT),都会生成一个新的.bin文件,num也会随之加1。
- tmp临时目录:数据文件首先会被写到这个目录,当一批数据写入完毕之后,数据文件会被移出此目录
-
操作方式
-
CREATE TABLE set_1 ( id UInt8 )ENGINE = Set(); INSERT INTO TABLE set_1 SELECT number FROM numbers(10) SELECT arrayJoin([1, 2, 3]) AS a WHERE a IN set_1
-
Join
Join表引擎可以说是为JOIN查询而生的,它等同于将JOIN查询进行了一层简单封装。在Join表引擎的底层实现中,它与Set表引擎共用了大部分的处理逻辑,所以Join和Set表引擎拥有许多相似之处。
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 join_tb1(
id UInt8,
name String,
time Datetime
) ENGINE = Log
INSERT INTO TABLE join_tb1 VALUES (1,'ClickHouse','2019-05-0112:00:00'),(2,'Spark', '2019-05-01 12:30:00')
CREATE TABLE id_join_tb1(
id UInt8,
price UInt32,
time Datetime
) ENGINE = Join(ANY, LEFT, id)
INSERT INTO TABLE id_join_tb1 VALUES (1,100,'2019-05-01 11:55:00'),(1,105,'2019-05-01 11:10:00')
日志类型
TinyLog
TinyLog是日志家族系列中性能最低的表引擎,它的存储结构由数据文件和元数据两部分组成。
数据文件是按 列独立存储的,也就是说每一个列字段都拥有一个与之对应的.bin文件。
TinyLog既不支持分区,也没有.mrk标记文件
由于没有标记文件,它自然无法支持.bin文件的并行读取操作,所以它只适合在非常简单的场景下使用
操作方式
CREATE TABLE tinylog_1 (
id UInt64,
code UInt64
)ENGINE = TinyLog()
INSERT INTO TABLE tinylog_1 SELECT number,number+1 FROM numbers(100)
StripeLog
StripeLog表引擎的存储结构由固定的3个文件组成,它们分别是:
data.bin:数据文件,所有的列字段使用同一个文件保存,它们的数据都会被写入data.bin。
index.mrk:数据标记,保存了数据在data.bin文件中的位置信息。利用数据标记能够使用多个线程,以并行的方式读取data.bin内的压缩数据块,从而提升数据查询的性能。
sizes.json:元数据文件,记录了data.bin和index.mrk大小的信息
操作方式
CREATE TABLE spripelog_1 (
id UInt64,
price Float32
)ENGINE = StripeLog()
INSERT INTO TABLE spripelog_1 SELECT number,number+100 FROM numbers(1000)
Log
- Log表引擎结合了TinyLog表引擎和StripeLog表引擎的长处,是日志家族系列中性能最高的表引擎。
- Log表引擎的存储结构由3个部分组成:
- [column].bin:数据文件,数据文件按列独立存储,每一个列字段都拥有一个与之对应的.bin文件。
- marks.mrk:数据标记,统一保存了数据在各个[column].bin文件中的位置信息。利用数据标记能够使用多个线程,以并行的方式读取.bin内的压缩数据块,从而提升数据查询的性能。
- sizes.json:元数据文件,记录了[column].bin和__marks.mrk大小的信息
操作类型
CREATE TABLE log_1 (
id UInt64,
code UInt64
)ENGINE = Log()
INSERT INTO TABLE log_1 SELECT number,number+1 FROM numbers(200)
接口类型
Merge
Merge表引擎就如同一层使用了门面模式的代理,它本身不存储任何数据,也不支持数据写入。
- 它的作用就如其名,即负责合并多个查询的结果集。
- Merge表引擎可以代理查询任意数量的数据表,这些查询会异步且并行执行,并最终合成一个结果集返回
ENGINE = Merge(database, table_name)
-- database表示数据库名称;
-- table_name表示数据表的名称,它支持使用正则表达式
操作方式
CREATE TABLE test_table_2018(
id String,
create_time DateTime,
code String
)ENGINE = MergeTree
PARTITION BY toYYYYMM(create_time)
ORDER BY id;
CREATE TABLE test_table_2019(
id String,
create_time DateTime,
code String
)ENGINE = Log;
CREATE TABLE test_table_all as test_table_2018
ENGINE = Merge(currentDatabase(), '^test_table_')
insert into test_table_2018 select number,'2018-01-01','08' from numbers(3);
insert into test_table_2019 select number,'2019-01-01','09' from numbers(3);
SELECT _table,* FROM test_table_all;
数据查询方式
-- 查询的格式
[WITH expr |(subquery)]
SELECT [DISTINCT] expr
[FROM [db.]table | (subquery) | table_function] [FINAL]
[SAMPLE expr]
[[LEFT] ARRAY JOIN]
[GLOBAL] [ALL|ANY|ASOF] [INNER | CROSS | [LEFT|RIGHT|FULL [OUTER]] ]
JOIN (subquery)|table ON|USING columns_list
[PREWHERE expr]
[WHERE expr]
[GROUP BY expr] [WITH ROLLUP|CUBE|TOTALS]
[HAVING expr]
[ORDER BY expr]
[LIMIT [n[,m]]
[UNION ALL]
[INTO OUTFILE filename]
[FORMAT format]
[LIMIT [offset] n BY columns]
With子句
SELECT pow(pow(2, 2), 3)
-- 等于
WITH pow(2, 2) AS a SELECT pow(a, 3)
使用方式
-
定义变量
WITH 10 AS start SELECT number FROM system.numbers WHERE number > start LIMIT 5
-
调用函数
WITH SUM(data_uncompressed_bytes) AS bytes SELECT database , formatReadableSize(bytes) AS format FROM system.columns GROUP BY database ORDER BY bytes DESC
-
定义子查询
WITH ( SELECT SUM(data_uncompressed_bytes) FROM system.columns ) AS total_bytes SELECT database , (SUM(data_uncompressed_bytes) / total_bytes) * 100 AS database_disk_usage FROM system.columns GROUP BY database ORDER BY database_disk_usage DESC
-
子查询中重复使用
WITH (round(database_disk_usage)) AS database_disk_usage_v1 SELECT database,database_disk_usage, database_disk_usage_v1 FROM ( --嵌套 WITH (SELECT SUM(data_uncompressed_bytes) FROM system.columns) AS total_bytes SELECT database , (SUM(data_uncompressed_bytes) / total_bytes) * 100 AS database_disk_usage FROM system.colum GROUP BY database ORDER BY database_disk_usage DESC )
From子句
-
数据表中取数据
SELECT WatchID FROM hits_v1
-
子查询取数据
SELECT MAX_WatchID FROM (SELECT MAX(WatchID) AS MAX_WatchID FROM hits_v1)
-
表函数中取数据
SELECT number FROM numbers(5)
Sample子句
-
SAMPLE子句能够实现数据采样的功能,使查询仅返回采样数据而不是全部数据,从而有效减少查询负载。
-
SAMPLE子句的采样机制是一种幂等设计,也就是说在数据不发生变化的情况下,使用相同的采样规则总是能够返回相同的数据,所以这项特性非常适合在那些可以接受近似查询结果的场合使用。
-
SAMPLE子句只能用于MergeTree系列引擎的数据表,并且要求在CREATE TABLE时声明SAMPLEBY抽样表达式
-
--Sample Key声明的表达式必须也包含在主键的声明中 --Sample Key必须是Int类型,如若不是,ClickHouse在进行CREATE TABLE操作时也不会报 错 CREATE TABLE hits_v1 ( CounterID UInt64, EventDate DATE, UserID UInt64 ) ENGINE = MergeTree() PARTITION BY toYYYYMM(EventDate) ORDER BY (CounterID, intHash32(UserID)) SAMPLE BY intHash32(UserID)
-
-
使用方式
-
SAMPLE factor
-
SAMPLE factor表示按因子系数采样,其中factor表示采样因子,它的取值支持0~1之间的小数。
-
如果factor设置为0或者1,则效果等同于不进行数据采样。
-
SELECT CounterID FROM hits_v1 SAMPLE 0.1 SELECT count() * 10 FROM hits_v1 SAMPLE 0.1 SELECT CounterID, _sample_factor FROM hits_v1 SAMPLE 0.1 LIMIT 2 SELECT count() * any(_sample_factor) FROM hits_v1 SAMPLE 0.1
-
-
SAMPLE rows
-
SAMPLE rows表示按样本数量采样,其中rows表示至少采样多少行数据,它的取值必须是大于1的整数。
-
如果rows的取值大于表内数据的总行数,则效果等于rows=1
-
SELECT count() FROM hits_v1 SAMPLE 10000 SELECT CounterID,_sample_factor FROM hits_v1 SAMPLE 100000 LIMIT 1
-
-
SAMPLE factor OFFSET n
-
SAMPLE factor OFFSET n表示按因子系数和偏移量采样,其中factor表示采样因子,n表示偏移多少数据后才开始采样,它们两个的取值都是0~1之间的小数。
-
SELECT CounterID FROM hits_v1 SAMPLE 0.4 OFFSET 0.5 SELECT CounterID,_sample_factor FROM hits_v1 SAMPLE 1/10 OFFSET 1/2
-
-
Array Join 子句
-
ARRAY JOIN子句允许在数据表的内部,与数组或嵌套类型的字段进行JOIN操作,从而将一行数组展开为多行。接下来让我们看看它的基础用法
-
CREATE TABLE query_v1( title String, value Array(Int8) ) ENGINE = Log INSERT INTO query_v1 VALUES ('food', [1,2,3]), ('fruit', [3,4]),('meat', []) SELECT title,value FROM query_v1
-
-
在一条SELECT语句中,只能存在一个ARRAY JOIN(使用子查询除外)。目前支持INNER和LEFT两种
-
INNER ARRAY JOIN
-
ARRAY JOIN在默认情况下使用的是INNER JOIN策略
-
SELECT title,value FROM query_v1 ARRAY JOIN value SELECT title,value,v FROM query_v1 ARRAY JOIN value AS v
-
从查询结果可以发现,最终的数据基于value数组被展开成了多行,并且排除掉了空数组。
-
在使用ARRAY JOIN时,如果为原有的数组字段添加一个别名,则能够访问展开前的数组字段
-
-
LEFT ARRAY JOIN
-
ARRAY JOIN子句支持LEFT连接策略
-
SELECT title,value,v FROM query_v1 LEFT ARRAY JOIN value AS v; SELECT title,value,v ,arrayMap(x -> x * 2,value) as mapv,v_1 FROM query_v1 LEFT ARRAY JOIN value AS v , mapv as v_1;
-
在改为LEFT连接查询后,可以发现,在INNER JOIN中被排除掉的空数组出现在了返回的结果集中。
-
当同时对多个数组字段进行ARRAY JOIN操作时,查询的计算逻辑是按行合并而不是产生笛卡儿积
-
-
Join子句
JOIN子句可以对左右两张表的数据进行连接
JOIN的语法包含连接精度和连接类型两部分
JOIN查询还可以根据其执行策略被划分为本地查询和远程查询
连接精度
连接精度决定了JOIN查询在连接数据时所使用的策略,目前支持ALL、ANY和ASOF三种类型。如果不主动声明,则默认是ALL
测试sql
CREATE TABLE dept (
DEPTNO UInt32,
DNAME String,
LOC String
) ENGINE= MergeTree
order by DEPTNO;
INSERT INTO dept VALUES ('10', 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES ('20', 'RESEARCH', 'DALLAS');
INSERT INTO dept VALUES ('30', 'SALES', 'CHICAGO');
INSERT INTO dept VALUES ('40', 'OPERATIONS', 'BOSTON');
CREATE TABLE emp (
EMPNO UInt32,
ENAME String,
JOB String,
MGR UInt32,
HIREDATE Date,
SAL Float64,
COMM Float64,
DEPTNO UInt32
) ENGINE= MergeTree
order by EMPNO;
INSERT INTO `emp` VALUES ('7369', 'SMITH', 'CLERK', '7902', '1980-12-17', '800', null, '20');
INSERT INTO `emp` VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', '1981-02-20', '1600', '300', '30');
INSERT INTO `emp` VALUES ('7521', 'WARD', 'SALESMAN', '7698', '1981-02-22', '1250', '500', '30');
INSERT INTO `emp` VALUES ('7566', 'JONES', 'MANAGER', '7839', '1981-04-02', '2975', null, '20');
INSERT INTO `emp` VALUES ('7654', 'MARTIN', 'SALESMAN', '7698', '1981-09-28', '1250', '1400', '30');
INSERT INTO `emp` VALUES ('7698', 'BLAKE', 'MANAGER', '7839', '1981-05-01', '2850', null, '30');
INSERT INTO `emp` VALUES ('7782', 'CLARK', 'MANAGER', '7839', '1981-06-09', '2450', null, '10');
INSERT INTO `emp` VALUES ('7788', 'SCOTT', 'ANALYST', '7566', '1987-07-13', '3000', null, '20');
INSERT INTO `emp` VALUES ('7839', 'KING', 'PRESIDENT', null, '1981-11-17', '5000', null, '10');
INSERT INTO `emp` VALUES ('7844', 'TURNER', 'SALESMAN', '7698', '1981-09-08', '1500', '0', '30');
INSERT INTO `emp` VALUES ('7876', 'ADAMS', 'CLERK', '7788', '1987-07-13', '1100', null, '20');
INSERT INTO `emp` VALUES ('7900', 'JAMES', 'CLERK', '7698', '1981-12-03', '950', null, '30');
INSERT INTO `emp` VALUES ('7902', 'FORD', 'ANALYST', '7566', '1981-12-03', '3000', null, '20');
INSERT INTO `emp` VALUES ('7934', 'MILLER', 'CLERK', '7782', '1982-01-23', '1300', null, null);
-
all
-
如果左表内的一行数据,在右表中有多行数据与之连接匹配,则返回右表中全部连接的数据。而判断连接
-
匹配的依据是左表与右表内的数据,基于连接键(JOIN KEY)的取值完全相等(equal),等同于left.key=right.key
-
select EMPNO,ENAME,DEPTNO,DNAME from emp all INNER JOIN dept d onemp.DEPTNO = d.DEPTNO;
-
-
any
-
如果左表内的一行数据,在右表中有多行数据与之连接匹配,则仅返回右表中第一行连接的数据
-
select distinct DEPTNO,DNAME from emp all INNER JOIN dept d on emp.DEPTNO = d.DEPTNO; select DEPTNO,DNAME from emp any INNER JOIN dept d on emp.DEPTNO = d.DEPTNO;
-
-
asof
-
ASOF是一种模糊连接,它允许在连接键之后追加定义一个模糊连接的匹配条件asof_column。
-
最终返回的查询结果符合连接条件a.id=b.id AND a.time>=b.time,且仅返回了右表中第一行连接匹配的数据。
-
ASOF支持使用USING的简写形式,USING后声明的最后一个字段会被自动转换成asof_colum模糊连接条件
-
asof_colum必须是整型、浮点型和日期型这类有序序列的数据类型;
-
asof_colum不能是数据表内的唯一字段
-
select emp.*,dept.* from emp asof INNER JOIN dept using (DEPTNO,SAL); --相当于 select * from emp all INNER JOIN dept d on emp.DEPTNO = d.DEPTNO where emp.SAL>=d.SAL;
-
连接类型
- Inner
- INNER JOIN表示内连接,在查询时会以左表为基础逐行遍历数据,然后从右表中找出与左边连接的行,它只会返回左表与右表两个数据集合中交集的部分,其余部分都会被排除
- OUTER
- OUTER JOIN表示外连接,它可以进一步细分为左外连接(LEFT)、右外连接(RIGHT)和全外连接(FULL)三种形式。根据连接形式的不同,其返回数据集合的逻辑也不尽相同。
- Cross
- CROSS JOIN表示交叉连接,它会返回左表与右表两个数据集合的笛卡儿积
WHERE与PREWHERE子句
- WHERE子句基于条件表达式来实现数据过滤。如果过滤条件恰好是主键字段,则能够进一步借助索引加速查询
- WHERE子句是一条查询语句能否启用索引的判断依据
- PREWHERE目前只能用于MergeTree系列的表引擎,它可以看作对WHERE的一种优化,其作用与WHERE相同,均是用来过滤数据。
- 使用PREWHERE时,首先只读取PREWHERE指定的列字段数据,用于数据过滤的条件判断。
- 待数据过滤之后再读取SELECT声明的列字段以补全其余属性。
- ClickHouse实现了自动优化的功能,会在条件合适的情况下将WHERE替换为PREWHERE。
- 如果想开启这项特性,需要将optimize_move_to_prewhere设置为1
GROUP BY子句
GROUP BY又称聚合查询
聚合查询目前还能配合WITH ROLLUP、WITHCUBE和WITH TOTALS三种修饰符获取额外的汇总信息
WITH ROLLUP
ROLLUP能够按照聚合键从右向左上卷数据,基于聚合函数依次生成分组小计和总计。
- 如果设聚合键的个数为n,则最终会生成小计的个数为n+1
SELECT table, name, SUM(bytes_on_disk) FROM system.parts GROUP BY table,name WITH ROLLUP ORDER BY table
WITH CUBE
CUBE会像立方体模型一样,基于聚合键之间所有的组合生成小计信息。如果设聚合键的个数为n,则最终小计组合的个数为2的n次方
WITH TOTALS
使用TOTALS修饰符后,会基于聚合函数对所有数据进行总计
SELECT database, SUM(bytes_on_disk),COUNT(table) FROM system.parts GROUP BY database WITH TOTALS
Having子句
HAVING子句需要与GROUP BY同时出现,不能单独使用。它能够在聚合计算之后实现二次过滤数据
ORDER BY子句
ORDER BY在使用时可以定义多个排序键,每个排序键后需紧跟ASC(升序)或DESC(降序)来确定排列顺序。如若不写,则默认为ASC(升序)
LIMIT BY子句
-
LIMIT BY子句和大家常见的LIMIT所有不同,它运行于ORDER BY之后和LIMIT之前,能够按照指定分组,最多返回前n行数据(如果数据少于n行,则按实际数量返回)
-
常用于TOP N的查询场景。LIMIT BY的常规语法如下:
- LIMIT n BY express
-
--查询返回数据占磁盘空间最大的前3张表 SELECT database,table,MAX(bytes_on_disk) AS bytes FROM system.parts GROUP BY database,table ORDER BY database ,bytes DESC LIMIT 3 BY database --- SELECT database,table,MAX(bytes_on_disk) AS bytes FROM system.parts GROUP BY database,table ORDER BY bytes DESC LIMIT 3 OFFSET 1 BY database
LIMIT子句
- LIMIT n
- SELECT number FROM system.numbers LIMIT 10
- LIMIT n OFFSET m
- SELECT number FROM system.numbers LIMIT 10 OFFSET 5
- LIMIT m,n
会基于聚合函数对所有数据进行总计
SELECT database, SUM(bytes_on_disk),COUNT(table) FROM system.parts GROUP BY database WITH TOTALS
Having子句
HAVING子句需要与GROUP BY同时出现,不能单独使用。它能够在聚合计算之后实现二次过滤数据
ORDER BY子句
ORDER BY在使用时可以定义多个排序键,每个排序键后需紧跟ASC(升序)或DESC(降序)来确定排列顺序。如若不写,则默认为ASC(升序)
LIMIT BY子句
-
LIMIT BY子句和大家常见的LIMIT所有不同,它运行于ORDER BY之后和LIMIT之前,能够按照指定分组,最多返回前n行数据(如果数据少于n行,则按实际数量返回)
-
常用于TOP N的查询场景。LIMIT BY的常规语法如下:
- LIMIT n BY express
-
--查询返回数据占磁盘空间最大的前3张表 SELECT database,table,MAX(bytes_on_disk) AS bytes FROM system.parts GROUP BY database,table ORDER BY database ,bytes DESC LIMIT 3 BY database --- SELECT database,table,MAX(bytes_on_disk) AS bytes FROM system.parts GROUP BY database,table ORDER BY bytes DESC LIMIT 3 OFFSET 1 BY database
LIMIT子句
- LIMIT n
- SELECT number FROM system.numbers LIMIT 10
- LIMIT n OFFSET m
- SELECT number FROM system.numbers LIMIT 10 OFFSET 5
- LIMIT m,n
- SELECT number FROM system.numbers LIMIT 5 ,10