块流用于处理数据
Block流操作有两组顶层接口
IBlockInputStream接口总共有60多个实现类,这些实现类大致可以分为三类
普通函数(Functions)
聚合函数(Aggregate Functions)
ClickHouse的集群由分片 ( Shard ) 组成,而每个分片又通过副本 ( Replica ) 组成。
这种分层的概念,在一些流行的分布式系统中十分普遍
基本数据类型
整数Int8、Int16、Int32 和 Int64
浮点数 Float32 和 Float64
定点数 Decimal32、Decimal64 和Decimal128
布尔 UInt8 限制值为0或1
字符串
String
FixedString
UUID
32位,格式8-4-4-4-12,如果未被赋值,则用0填充
CREATE TABLE UUID_TEST (
c1 UUID,
c2 String
) ENGINE = Memory;
–第一行UUID有值
INSERT INTO UUID_TEST SELECT generateUUIDv4(),‘t1’
–第二行UUID没有值
INSERT INTO UUID_TEST(c2) VALUES(‘t2’)
日期时间
Date: 2020-02-02 精确到天
CREATE TABLE Date_TEST (
c1 Date
) ENGINE = Memory
–以字符串形式写入
INSERT INTO Date_TEST VALUES(‘2019-06-22’)
SELECT c1, toTypeName(c1) FROM Date_TEST
DateTime: 2020-02-02 20:20:20 精确到秒
CREATE TABLE Datetime_TEST (
c1 Datetime
) ENGINE = Memory
–以字符串形式写入
INSERT INTO Datetime_TEST VALUES(‘2019-06-22 00:00:00’)
SELECT c1, toTypeName(c1) FROM Datetime_TEST
DateTime64: 2020-02-02 20:20:20.335 精确到亚秒,可以设置精度
CREATE TABLE Datetime64_TEST (
c1 Datetime64(2)
) ENGINE = Memory
–以字符串形式写入
INSERT INTO Datetime64_TEST VALUES(‘2019-06-22 00:00:00’)
SELECT c1, toTypeName(c1) FROM Datetime64_TEST
复合类型
数组
创建数据:array(T)或[],类型必须相同
SELECT array(1, 2) as a , toTypeName(a)
SELECT [1, 2, null] as a , toTypeName(a)
CREATE TABLE Array_TEST (
c1 Array(String)
) engine = Memory
元组
由多个元素组成,允许不同类型
创建数据:(T1, T2, …),Tuple(T1, T2, …)
SELECT tuple(1,‘a’,now()) AS x, toTypeName(x)
SELECT (1,2.0,null) AS x, toTypeName(x)
CREATE TABLE Tuple_TEST (
c1 Tuple(String,Int8)
) ENGINE = Memory;
枚举类型
ClickHouse提供了Enum8和Enum16两种枚举类型,它们除了取值范围不同之外,别无二致
枚举固定使用(String:Int)Key/Value键值对的形式定义数据,所以Enum8和Enum16分别会对应(String:Int8)和(String:Int16)
用(String:Int) Key/Value键值对的形式定义数据,键值对不能同时为空,不允许重复,key允许
为空字符串,需要看到对应的值进行转换
CREATE TABLE Enum_TEST (
c1 Enum8(‘ready’ = 1, ‘start’ = 2, ‘success’ = 3, ‘error’ = 4)
) ENGINE = Memory;
–正确语句
INSERT INTO Enum_TEST VALUES(‘ready’);
INSERT INTO Enum_TEST VALUES(‘start’);
–错误语句
INSERT INTO Enum_TEST VALUES(‘stop’);
嵌套类型
Nested(Name1 Type1,Name2 Type2,…)
相当于表中嵌套一张表,插入时相当于一个多维数组的格式,一个字段对应一个数组
CREATE TABLE nested_test (
name String,
age UInt8 ,
dept Nested(
id UInt8,
name String
)
) ENGINE = Memory;
–行与行之间,数组长度无须对齐
INSERT INTO nested_test VALUES (‘bruce’ , 30 , [10000,10001,10002], [‘研
发部’,‘技术支持中心’,‘测试部’]);
INSERT INTO nested_test VALUES (‘bruce’ , 30 , [10000,10001], [‘研发
部’,‘技术支持中心’]);
其他类型
Nullable(TypeName)
只能与基础数据类型搭配使用,表示某个类型的值可以为NULL;Nullable(Int8)表示可以存储Int8类型的值,没有值时存NULL
CREATE TABLE Null_TEST (
c1 String,
c2 Nullable(UInt8)
) ENGINE = TinyLog;
–通过Nullable修饰后c2字段可以被写入Null值:
INSERT INTO Null_TEST VALUES (‘nauu’,null)
INSERT INTO Null_TEST VALUES (‘bruce’,20)
SELECT c1 , c2 ,toTypeName(c2) FROM Null_TEST
注意
Domain
Pv4 使用 UInt32 存储。如 116.253.40.133
IPv6 使用 FixedString(16) 存储。如 2a02:aa08:e000:3100::2
CREATE TABLE IP4_TEST (
url String,
ip IPv4
) ENGINE = Memory;
INSERT INTO IP4_TEST VALUES (‘www.nauu.com’,‘192.0.0.0’)
SELECT url , ip ,toTypeName(ip) FROM IP4_TEST
数据库起到了命名空间的作用,可以有效规避命名冲突的问题,也为后续的数据隔离提供了支撑。任何一张数据表,都必须归属在某个数据库之下
操作语法
CREATE DATABASE IF NOT EXISTS db_name [ENGINE = engine]
SHOW DATABASES
DROP DATABASE [IF EXISTS] db_name
数据库引擎
Ordinary:默认引擎
Dictionary:字典引擎
Memory:内存引擎
Lazy:日志引擎
MySQL:MySQL引擎
ClickHouse目前提供了三种最基本的建表方法
常规定义方法
CREATE TABLE [IF NOT EXISTS] [db_name.]table_name (
name1 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
name2 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
省略…
) ENGINE = engine
CREATE TABLE hits_v1 (
Title String,
URL String ,
EventTime DateTime
) ENGINE = Memory;
复制其他表的结构
CREATE TABLE [IF NOT EXISTS] [db_name1.]table_name AS [db_name2.]
table_name2 [ENGINE = engine]
–创建新的数据库
CREATE DATABASE IF NOT EXISTS new_db
–将default.hits_v1的结构复制到new_db.hits_v1
CREATE TABLE IF NOT EXISTS new_db.hits_v1 AS default.hits_v1 ENGINE =
TinyLog
通过SELECT子句的形式创建
CREATE TABLE [IF NOT EXISTS] [db_name.]table_name ENGINE = engine AS
SELECT …
CREATE TABLE IF NOT EXISTS hits_v1_1 ENGINE = Memory AS SELECT * FROM
hits_v1
删除表
和大多数数据库一样,使用DESC查询可以返回数据表的定义结构
如果想删除一张数据表,则可以使用下面的DROP语句
临时表
创建临时表的方法是在普通表的基础之上添加TEMPORARY关键字
CREATE TEMPORARY TABLE [IF NOT EXISTS] table_name (
name1 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
name2 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
)
特点
分区表
数据分区(partition)和数据分片(shard)是完全不同的两个概念
数据分区是针对本地数据而言的,是数据的一种纵向切分。而数据分片是数据的一种横向切分
案例
CREATE TABLE partition_v1 (
ID String,
URL String,
EventTime Date
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventTime)
ORDER BY ID
INSERT INTO partition_v1 VALUES
(‘A000’,‘www.nauu.com’, ‘2019-05-01’),
(‘A001’,‘www.brunce.com’, ‘2019-06-02’)
SELECT table,partition,path from system.parts WHERE table =
‘partition_v1’
数据表操作
追加新字段
ALTER TABLE tb_name ADD COLUMN [IF NOT EXISTS] name [type]
[default_expr] [AFTER name_after]
ALTER TABLE testcol_v1 ADD COLUMN OS String DEFAULT ‘mac’
ALTER TABLE testcol_v1 ADD COLUMN IP String AFTER ID
修改字段类型
ALTER TABLE tb_name MODIFY COLUMN [IF EXISTS] name [type] [default_expr]
ALTER TABLE testcol_v1 MODIFY COLUMN IP IPv4
修改备注
ALTER TABLE tb_name COMMENT COLUMN [IF EXISTS] name ‘some comment’
ALTER TABLE testcol_v1 COMMENT COLUMN ID ‘主键ID’
DESC testcol_v1
删除已有字段
ALTER TABLE tb_name DROP COLUMN [IF EXISTS] name
ALTER TABLE testcol_v1 DROP COLUMN URL
清空数据表
TRUNCATE TABLE [IF EXISTS] [db_name.]tb_name
TRUNCATE TABLE db_test.testcol_v2
普通视图
物化视图
CREATE [MATERIALIZED] VIEW [IF NOT EXISTS] [db.]table_name [TO[db.]name]
[ENGINE = engine] [POPULATE] AS SELECT .
物化视图创建好之后,如果源表被写入新数据,那么物化视图也会同步更新
数据的写入
数据的删除和修改
ClickHouse提供了DELETE和UPDATE的能力,这类操作被称为Mutation查询,它可以看作ALTER语句的变种
虽然Mutation能最终实现修改和删除,但不能完全以通常意义上的UPDATE和DELETE来理解
概述
创建方式
语法
配置选项
PARTITION BY [选填]:分区键,用于指定表数据以何种标准进行分区
ORDER BY [必填]:排序键,用于指定在一个数据片段内,数据以何种标准排序
PRIMARY KEY [选填]:主键,顾名思义,声明后会依照主键字段生成一级索引,用于加速表查询
SAMPLE BY [选填]:抽样表达式,用于声明数据以何种标准进行采样
SETTINGS
index_granularity [选填]
存储格式
数据会按照分区目录的形式保存到磁盘之上
一张数据表的完整物理结构分为3个层级,依次是数据表目录、分区目录及各分区下具体的数据文件
数据分区规则
MergeTree数据分区的规则由分区ID决定,而具体到每个数据分区所对应的ID,则是由分区键的取值决定的
针对取值数据类型的不同,分区ID的生成逻辑目前拥有四种规则
不指定分区键
使用整型
使用日期类型
使用其他类型
分区目录命名
一个完整分区目录的命名公式
PartitionID_MinBlockNum_MaxBlockNum_Level
分区目录合并
MergeTree的分区目录并不是在数据表被创建之后就存在的,而是在数据写入过程中被创建的
其次,它的分区目录在建立之后也并不是一成不变的
新目录名称的合并方式遵循规则
概述
稀疏索引
primary.idx文件内的一级索引采用稀疏索引实现
稠密索引中每一行索引标记都会对应到一行具体的数据记录。
稀疏索引中每一行索引标记对应的是一段数据,而不是一行。
仅需使用少量的索引标记就能够记录大量数据的区间位置信息,且数据量越大优势越为明显
索引粒度
索引规则
索引查询过程
概述
粒度
分类
minmax
set
ngrambf_v1
tokenbf_v1
列式存储
每列对应一个bin数据文件
优势
存储方式
数据压缩
一个压缩数据块由头信息和压缩数据两部分组成
数据写入过程
优势
压缩会丢失块的位置信息,用数据标记可解决
数据标记
生成规则
工作方式
MergeTree在读取数据时,必须通过标记数据的位置信息才能够找到所需要的数据。整个查找过程大致可以分为读取压缩数据块和读取数据两个步骤
数据理解
1B*8192=8192B,64KB=65536B,65536/8192=8
头信息固定由9个字节组成,压缩后大小为8个字节
12016=8+12000+8
读取压缩数据块
读取数据
数据标记与数据压缩
概述
多对一
一对一
一对多
写入数据
查询数据
数据TTL
顾名思义,它表示数据的存活时间
可以为某个列字段或整张表设置TTL
多路径存储策略
19.15版本之前,MergeTree只支持单路径存储,所有的数据都会被写入config.xml配置中path指定的路径下
19.15版本开始,MergeTree实现了自定义存储策略的功能,支持以数据分区为最小移动单元,将分区目录写入多块磁盘目录
存储策略
默认策略
JBOD策略
HOT/COLD策略
将存储磁盘分为HOT与COLD两类区域
为了数据去重而设计的,它能够在合并分区时删除重复的数据
ReplacingMergeTree是以分区为单位删除重复数据的
数据立方体
AggregatingMergeTree更为常见的应用方式是结合物化视图使用,将它作为物化视图的表引擎
一种通过以增代删的思路,支持行级数据修改和删除的表引擎
通过定义一个sign标记位字段,记录数据行的状态
如果sign标记为1,则表示这是一行有效的数据;如果sign标记为-1,则表示这行数据需要被删除
当CollapsingMergeTree分区合并时,同一数据分区内,sign标记为1和-1的一组数据会被抵消删除
这种1和-1相互抵消的操作,犹如将一张瓦楞纸折叠了一般
折叠规则
特点
折叠数据并不是实时触发的,和所有其他的MergeTree变种表引擎一样,这项特性也只有在分区合并的时候才会体现
只有相同分区内的数据才有可能被折叠
CollapsingMergeTree对于写入数据的顺序有着严格要求
MergeTree表引擎向下派生出6个变种表引擎
概述
HDFS
Mysql
JDBC
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]
再次执行SELECT查询会发现kafka_table数据表空空如也,这是因为Kafka表引擎在执行查询之后就会移动offset,导致数据无法重复读取。
解决方法
File
概述
将数据全量放在内存中,对于表引擎来说是一把双刃剑
Memory
Set
Set表引擎是拥有物理存储的,数据首先会被写至内存,然后被同步到磁盘文件中
当服务重启时,它的数据不会丢失,当数据表被重新装载时,文件数据会再次被全量加载至内存
Set表引擎具有去重的能力,在数据写入的过程中,重复的数据会被自动忽略
Set表引擎的存储结构由两部分组成
[num].bin数据文件:保存了所有列字段的数据
tmp临时目录:数据文件首先会被写到这个目录,当一批数据写入完毕之后,数据文件会被移出此目录
Join
Join表引擎可以说是为JOIN查询而生的,它等同于将JOIN查询进行了一层简单封装
join_strictness:连接精度
join_type:连接类型
join_key:连接键
TinyLog
TinyLog是日志家族系列中性能最低的表引擎,它的存储结构由数据文件和元数据两部分组成
StripeLog
StripeLog表引擎的存储结构由固定的3个文件组成
data.bin:数据文件
index.mrk:数据标记
sizes.json:元数据文件
Log
Log表引擎结合了TinyLog表引擎和StripeLog表引擎的长处,是日志家族系列中性能最高的表引擎
由3个部分组成
[column].bin:数据文件
marks.mrk:数据标记
sizes.json:元数据文件
Merge
Merge表引擎就如同一层使用了门面模式的代理,它本身不存储任何数据,也不支持数据写入
ClickHouse支持CTE(Common Table Expression,公共表表达式),以增强查询语句的表达
在改用CTE的形式后,可以极大地提高语句的可读性和可维护性
With的四种使用方法
定义变量
调用函数
定义子查询
在子查询中重复使用WITH
FROM子句表示从何处读取数据,目前支持如下3种形式
在ClickHouse中,并没有数据库中常见的DUAL虚拟表,取而代之的是system.one。
在FROM子句后,可以使用Final修饰符
SAMPLE子句能够实现数据采样的功能,使查询仅返回采样数据而不是全部数据,从而有效减少查询负载
SAMPLE子句的采样机制是一种幂等设计,也就是说在数据不发生变化的情况下,使用相同的采样规则总是能够返回相同的数据
SAMPLE子句只能用于MergeTree系列引擎的数据表,并且要求在CREATE TABLE时声明SAMPLEBY抽样表达式
支持如下3种用法
SAMPLE factor
SAMPLE rows
SAMPLE factor OFFSET n
ARRAY JOIN子句允许在数据表的内部,与数组或嵌套类型的字段进行JOIN操作,从而将一行数组展开为多行
在一条SELECT语句中,只能存在一个ARRAY JOIN(使用子查询除外)。目前支持INNER和LEFT两种
INNER ARRAY JOIN
LEFT ARRAY JOIN
概述
连接精度
决定了JOIN查询在连接数据时所使用的策略,目前支持ALL、ANY和ASOF三种类型。如果不主动声明,则默认是ALL。
all
any
asof
连接类型
Inner
OUTER
Cross
WHERE子句基于条件表达式来实现数据过滤
果过滤条件恰好是主键字段,则能够进一步借助
索引加速查询
PREWHERE目前只能用于MergeTree系列的表引擎,它可以看作对WHERE的一种优化
其作用与WHERE相同,均是用来过滤数据。
ClickHouse实现了自动优化的功能,会在条件合适的情况下将WHERE替换为PREWHERE
聚合查询
能配合WITH ROLLUP、WITHCUBE和WITH TOTALS三种修饰符获取额外的汇总信息
WITH ROLLUP
WITH CUBE
WITH TOTALS
运行于ORDER BY之后和LIMIT之前,能够按照指定分组,最多返回前n行数据(如果数据少于n行,则按实际数量返回)
常用于TOP N的查询场景。LIMIT BY的常规语法如下
LIMIT子句用于返回指定的前n行数据,常用于分页场景
三种语法形式
使用LIMIT子句时有一点需要注意,如果数据跨越了多个分区,在没有使用ORDER BY指定全局顺序的情况下,每次LIMIT查询所返回的数据有可能不同。如果对数据的返回顺序敏感,则应搭配ORDER BY一同使用。