Gitlib
Ravior
2022-01-04
目录

ClickHouse使用总结

ClickHouse是一个用于联机分析(OLAP)的列式数据库管理系统(DBMS)。

# 1. 特性

# 1.1 OLAP场景的关键特征

  • 绝大多数是读请求
  • 数据以相当大的批次(> 1000行)更新，而不是单行更新;或者根本没有更新。
  • 已添加到数据库的数据不能修改。
  • 对于读取，从数据库中提取相当多的行，但只提取列的一小部分。
  • 宽表，即每个表包含着大量的列
  • 查询相对较少(通常每台服务器每秒查询数百次或更少)
  • 对于简单查询，允许延迟大约50毫秒
  • 列中的数据相对较小：数字和短字符串(例如，每个URL 60个字节)
  • 处理单个查询时需要高吞吐量(每台服务器每秒可达数十亿行)
  • 事务不是必须的
  • 对数据一致性要求低
  • 每个查询有一个大表。除了他以外，其他的都很小。
  • 查询结果明显小于源数据。换句话说，数据经过过滤或聚合，因此结果适合于单个服务器的RAM中

# 1.2 列式存储的优点

列式数据库更适合于OLAP场景(对于大多数查询而言，处理速度至少提高了100倍)

  • 针对分析类查询，通常只需要读取表的一小部分列。在列式数据库中你可以只读取你需要的数据。例如，如果只需要读取100列中的5列，这将帮助你最少减少20倍的I/O消耗。
  • 由于数据总是打包成批量读取的，所以压缩是非常容易的。同时数据按列分别存储这也更容易压缩。这进一步降低了I/O的体积。
  • 由于I/O的降低，这将帮助更多的数据被系统缓存。
  • 数据扫描时只需要扫描关心的列，减少扫描数量
  • 列的连续储存，可以实现高效率压缩

# 2. 数据库引擎

默认情况下，ClickHouse使用Atomic数据库引擎。

# 2.1 Atomic引擎

它支持非阻塞的DROP TABLE和RENAME TABLE查询和原子的EXCHANGE TABLES t1 AND t2查询。默认情况下使用Atomic数据库引擎。

# 2.2 MySQL引擎

MySQL引擎用于将远程的MySQL服务器中的表映射到ClickHouse中，并允许您对表进行INSERT和SELECT查询，以方便您在ClickHouse与MySQL之间进行数据交换 MySQL数据库引擎会将对其的查询转换为MySQL语法并发送到MySQL服务器中，因此您可以执行诸如SHOW TABLES或SHOW CREATE TABLE之类的操作。

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MySQL('host:port', ['database' | database], 'user', 'password')
1
2

# 2.3 SQLite引擎

允许连接到SQLite数据库，并支持ClickHouse和SQLite交换数据， 执行 INSERT 和 SELECT 查询。

CREATE DATABASE sqlite_database 
ENGINE = SQLite('db_path')
1
2

# 2.4 PostgreSQL引擎

允许连接到远程PostgreSQL服务。支持读写操作(SELECT和INSERT查询)，以在ClickHouse和PostgreSQL之间交换数据。 在SHOW TABLES和DESCRIBE TABLE查询的帮助下，从远程PostgreSQL实时访问表列表和表结构。

CREATE DATABASE test_database 
ENGINE = PostgreSQL('host:port', 'database', 'user', 'password'[, `use_table_cache`]);
1
2

# 2.5 Lazy引擎

在最后一次访问之后，只在RAM中保存expiration_time_in_seconds秒。只能用于Log表。 它是为存储许多小的Log表而优化的，对于这些表，访问之间有很长的时间间隔。

CREATE DATABASE testlazy ENGINE = Lazy(expiration_time_in_seconds);
1

# 3. 表引擎

ClickHouse 的表引擎提供了四个系列(Log、MergeTree、Integration、Special)大约 28 种表引擎，各有各的用途。比如 Log 系列用来做小表数据分 析，MergeTree 系列用来做大数据量分析，而 Integration 系列则多用于外表数据集成。Log、Special、Integration 系列的表引擎相对来说，应用场景有限，功能简单，应用特殊用途，MergeTree 系列表引擎又和两种特殊表引擎(Replicated，Distributed)正交形成多种具备不同功能的 MergeTree 表引擎。

# 3.1 合并树MergeTree

Clickhouse 中最强大的表引擎当属 MergeTree （合并树）引擎及该系列（*MergeTree）中的其他引擎。 MergeTree 系列的引擎被设计用于插入极大量的数据到一张表当中。数据可以以数据片段的形式一个接着一个的快速写入，数据片段在后台按照一定的规则进行合并。相比在插入时不断修改（重写）已存储的数据，这种策略会高效很多。

ClickHouse

# 3.1.1 ReplacingMergeTree(替换合并树)

ReplacingMergeTree会删除排序键值相同的重复项。 数据的去重只会在数据合并期间进行。合并会在后台一个不确定的时间进行，因此你无法预先作出计划。有一些数据可能仍未被处理。尽管你可以调用 OPTIMIZE 语句发起计划外的合并，但请不要依靠它，因为 OPTIMIZE 语句会引发对数据的大量读写。 因此，ReplacingMergeTree 适用于在后台清除重复的数据以节省空间，但是它不保证没有重复的数据出现。

# 3.1.2 SummingMergeTree(求和合并树)

SummingMergeTree把所有具有相同主键的行合并为一行，该行包含了被合并的行中具有数值数据类型的列的汇总值。如果主键的组合方式使得单个键值对应于大量的行，则可以显著的减少存储空间并加快数据查询的速度。

# 3.2 日志Log

日志引擎适用于相对较小的表（建议最多1,000,000行），性能方面会有很大提升。

  • TinyLog
  • Log
  • StripeLog

# 3.3 继承

# 3.3.1 MySQL

MySQL 引擎可以对存储在远程 MySQL 服务器上的数据执行 SELECT 查询。

CREATE TABLE mysql_table
(
    key UInt64,
    data String
) ENGINE = MySQL('host:port', 'database', 'table', 'user', 'password'[, replace_query, 'on_duplicate_clause']);
1
2
3
4
5

# 3.3.2 MongoDB

MongoDB 引擎是只读表引擎，允许从远程 MongoDB 集合中读取数据(SELECT查询)。引擎只支持非嵌套的数据类型。不支持 INSERT 查询。

CREATE TABLE mongo_table
(
    key UInt64,
    data String
) ENGINE = MongoDB('mongo1:27017', 'test', 'simple_table', 'testuser', 'clickhouse');
1
2
3
4
5

# 3.3.3 Kafka

# 3.3.4 RabbitMQ

# 3.3.5 HDFS

# 3.3.6 Hive

# 3.4 特殊

# 3.4.1 物化视图

数据库中的视图(View)指的是通过一张或多张表查询出来的逻辑表 ，本身只是一段SQL的封装并不存储数据。

而物化视图(Materialized View) 与普通视图不同的地方在于它是一个查询结果的数据库对象 (持久化存储)，非常趋近于表；物化视图是数据库中的预计算逻辑 + 显式缓存，典型的空间换时间思路，所以用得好的话，它可以避免对基础表的频繁查询并复用结果，从而显著提升查询的性能。

ClickHouse中的物化视图可以挂接在任意引擎的基础表上，而且会自动更新数据，它可以借助MergeTree家族引擎 (SummingMergeTree、Aggregatingmergetree 等)，得到一个实时的预聚合，满足快速查询；但是对更新与删除操作支持并不好，更像是个插入触发器。

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db.]table_name [ON CLUSTER] [TO[db.]name] [ENGINE = engine] [POPULATE] AS SELECT ...
1

POPULATE关键字决定了物化视图的更新策略：

  • 若有POPULATE则在创建视图的过程会将源表已经存在的数据一并导入，类似于create table ... as
  • 若无POPULATE则物化视图在创建之后没有数据

ClickHouse 官方并不推荐使用populated，因为在创建视图过程中插入表中的数据并不会写入视图，会造成数据的丢失。

# 3.4.1.1 使用场景

假设有一个日志表login_user_log来记录每次登录的用户信息，现在需要按用户所属地为维度来统计每天的登录次数。 PS：这种 只有新增记录，没有更新删除的记录表就非常适合使用 物化视图 来优化统计性能

正常的聚合 SQL 如下：city 为用户所属地，login_date 为登录时间

select city, login_date, count(1) login_cntfrom login_user_loggroup by city, login_date
1

增加物化视图后的架构如下图所示： ClickHouse

  • 建表 创建基础表：基础表使用 SummingMergeTree 引擎，进行预聚合处理
CREATE TABLE login_user_log_base
(
    city String,
                login_date Date,
    login_cnt UInt32
)ENGINE = SummingMergeTree()ORDER BY (city, login_date)
1
2
3
4
5
6

SummingMergeTree 表引擎主要用于只关心聚合后的数据，而不关心明细数据的场景，它能够在合并分区的时候按照预先定义的条件聚合汇总数据，将同一分组下的多行数据汇总到一行，可以显著的 减少存储空间并加快数据查询的速度。

  • 创建物化视图 用户在创建物化视图时，通过 AS SELECT ... 子句从源表中查询需要的列，十分灵活
CREATE MATERIALIZED VIEW if not exists login_user_log_mv 
TO login_user_log_base 
AS SELECT city, login_date, count(1) login_cntfrom login_user_loggroup by city, login_date
1
2
3

使用TO关键字关联物化视图与基础表，需要自己初始化历史数据。

  • 查询统计结果 使用物化视图查询
SELECT city, login_date, sum(login_cnt) cntfrom login_user_log_mvgroup by city, login_date
1

注意：在使用物化视图（SummingMergeTree 引擎）的时候，也需要按照聚合查询来写 sql，因为虽然 SummingMergeTree 会自己预聚合，但是并不是实时的，具体执行聚合的时机并 不可控。

# 3.4.1.2 总结

  • 物化视图是一种空间换时间的预聚合方式，聚合后的数据将存储在新表中，一般于SummingMergeTree，AggregatingMergeTree等聚合引擎一起使用。
  • 物化视图因为是写入触发器，所以as select只对每批次的insert data有效果，所以即使是where条件也是对这批写入数据起效果（https://clickhouse.tech/docs/en/sql-reference/statements/create/view/#materialized）
  • 物化视图只有在原表insert的时候才会触发
  • POPULATE关键字，不建议使用，会把原始表中的已存在数据全部物化一遍，老数据的同步，建议直接insert到mv中
  • 多表join生成物化视图，左表插入数据时才更新
  • 源表数据的改变不会影响物化视图，如update, delete, drop partition

# 4 建表语句

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
) ENGINE = MergeTree()
ORDER BY expr
[PARTITION BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'], ...]
[SETTINGS name=value, ...]
1
2
3
4
5
6
7
8
9
10
11
12
13
14

ClickHouse

  • ENGINE - 引擎名和参数。 ENGINE = MergeTree(). MergeTree 引擎没有参数。
  • ORDER BY — 排序键。
    • 可以是一组列的元组或任意的表达式。 例如: ORDER BY (CounterID, EventDate) 。
    • 如果没有使用 PRIMARY KEY 显式指定的主键，ClickHouse 会使用排序键作为主键。
    • 如果不需要排序，可以使用 ORDER BY tuple(). 参考 选择主键
  • PARTITION BY — 分区键 ，可选项。
    • 大多数情况下，不需要分使用区键。即使需要使用，也不需要使用比月更细粒度的分区键。
    • 分区不会加快查询（这与 ORDER BY 表达式不同）。永远也别使用过细粒度的分区键。
    • 不要使用客户端指定分区标识符或分区字段名称来对数据进行分区（而是将分区字段标识或名称作为 ORDER BY 表达式的第一列来指定分区）。
    • 要按月分区，可以使用表达式 toYYYYMM(date_column) ，这里的 date_column 是一个 Date 类型的列。分区名的格式会是 "YYYYMM" 。
  • PRIMARY KEY - 如果要 选择与排序键不同的主键，在这里指定，可选项。
    • 默认情况下主键跟排序键（由 ORDER BY 子句指定）相同。 因此，大部分情况下不需要再专门指定一个 PRIMARY KEY 子句。
  • SAMPLE BY - 用于抽样的表达式，可选项。
  • TTL - 指定行存储的持续时间并定义数据片段在硬盘和卷上的移动逻辑的规则列表，可选项。
    • 表达式中必须存在至少一个 Date 或 DateTime 类型的列，比如：TTL date + INTERVAl 1 DAY
  • 规则的类型 DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'指定了当满足条件（到达指定时间）时所要执行的动作：移除过期的行，还是将数据片段（如果数据片段中的所有行都满足表达式的话）移动到指定的磁盘（TO DISK 'xxx') 或 卷（TO VOLUME 'xxx'）。默认的规则是移除（DELETE）。可以在列表中指定多个规则，但最多只能有一个DELETE的规则。
  • SETTINGS — 控制 MergeTree 行为的额外参数，可选项：
    • index_granularity — 索引粒度。索引中相邻的『标记』间的数据行数。默认值8192 。参考数据存储。
    • index_granularity_bytes — 索引粒度，以字节为单位，默认值: 10Mb。如果想要仅按数据行数限制索引粒度, 请设置为0(不建议)。
    • ....

# 4.1 分区

分区是在一个表中通过指定的规则划分而成的逻辑数据集。可以按任意标准进行分区，如按月，按日或按事件类型。为了减少需要操作的数据，每个分区都是分开存储的。访问数据时，ClickHouse 尽量使用这些分区的最小子集。

CREATE TABLE visits
(
    VisitDate Date,
    Hour UInt8,
    ClientID UUID
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(VisitDate)
ORDER BY Hour;
1
2
3
4
5
6
7
8
9

可以通过 system.parts 表查看表片段和分区信息。system.parts 表执行 SELECT：

select partition, name, active  from system.parts where table='game_log';
1

ClickHouse

  • partition 列存储分区的名称。此示例中有两个分区：201901 和 201902。在 ALTER … PARTITION 语句中你可以使用该列值来指定分区名称。
  • name 列为分区中数据片段的名称。在 ALTER ATTACH PART 语句中你可以使用此列值中来指定片段名称。
  • active 列为片段状态。1 代表激活状态；0 代表非激活状态。非激活片段是那些在合并到较大片段之后剩余的源数据片段。损坏的数据片段也表示为非活动状态。

查看片段和分区信息的另一种方法是进入表的目录：/var/lib/clickhouse/data/<database>/<table>/。例如：

/var/lib/clickhouse/data/default/visits$ ls -l
total 40
drwxr-xr-x 2 clickhouse clickhouse 4096 Feb  1 16:48 201901_1_3_1
drwxr-xr-x 2 clickhouse clickhouse 4096 Feb  5 16:17 201901_1_9_2
drwxr-xr-x 2 clickhouse clickhouse 4096 Feb  5 15:52 201901_8_8_0
drwxr-xr-x 2 clickhouse clickhouse 4096 Feb  5 15:52 201901_9_9_0
drwxr-xr-x 2 clickhouse clickhouse 4096 Feb  5 16:17 201902_10_10_0
drwxr-xr-x 2 clickhouse clickhouse 4096 Feb  5 16:17 201902_11_11_0
drwxr-xr-x 2 clickhouse clickhouse 4096 Feb  5 16:19 201902_4_11_2
drwxr-xr-x 2 clickhouse clickhouse 4096 Feb  5 12:09 201902_4_6_1
drwxr-xr-x 2 clickhouse clickhouse 4096 Feb  1 16:48 detached
1
2
3
4
5
6
7
8
9
10
11

detached 目录存放着使用 DETACH 语句从表中卸载的片段。损坏的片段不会被删除而是也会移到该目录下。服务器不会去使用detached目录中的数据片段。因此你可以随时添加，删除或修改此目录中的数据

# 4.2 ttl

TTL用于设置值的生命周期，它既可以为整张表设置，也可以为每个列字段单独设置。表级别的 TTL 还会指定数据在磁盘和卷上自动转移的逻辑。 TTL 表达式的计算结果必须是 日期 或 日期时间 类型的字段。

示例：

TTL time_column
TTL time_column + interval
1
2

要定义interval, 需要使用 时间间隔 操作符。

TTL date_time + INTERVAL 1 MONTH
TTL date_time + INTERVAL 15 HOUR
1
2

# 4.2.1 列 TTL

当列中的值过期时, ClickHouse会将它们替换成该列数据类型的默认值。如果数据片段中列的所有值均已过期，则ClickHouse 会从文件系统中的数据片段中删除此列。 TTL子句不能被用于主键字段。

# 4.2.2 表 TTL

表可以设置一个用于移除过期行的表达式，以及多个用于在磁盘或卷上自动转移数据片段的表达式。当表中的行过期时，ClickHouse 会删除所有对应的行。对于数据片段的转移特性，必须所有的行都满足转移条件。

TTL expr
    [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'][, DELETE|TO DISK 'aaa'|TO VOLUME 'bbb'] ...
    [WHERE conditions]
    [GROUP BY key_expr [SET v1 = aggr_func(v1) [, v2 = aggr_func(v2) ...]] ]
1
2
3
4

TTL 规则的类型紧跟在每个 TTL 表达式后面，它会影响满足表达式时（到达指定时间时）应当执行的操作：

  • DELETE - 删除过期的行（默认操作）;
  • TO DISK 'aaa' - 将数据片段移动到磁盘 aaa;
  • TO VOLUME 'bbb' - 将数据片段移动到卷 bbb.
  • GROUP BY - 聚合过期的行 4.3 冷热数据分开存储 MergeTree 系列表引擎可以将数据存储在多个块设备上。这对某些可以潜在被划分为“冷”“热”的表来说是很有用的。最新数据被定期的查询但只需要很小的空间。相反，详尽的历史数据很少被用到。如果有多块磁盘可用，那么“热”的数据可以放置在快速的磁盘上（比如 NVMe 固态硬盘或内存），“冷”的数据可以放在相对较慢的磁盘上（比如机械硬盘）。 数据片段是 MergeTree 引擎表的最小可移动单元。属于同一个数据片段的数据被存储在同一块磁盘上。数据片段会在后台自动的在磁盘间移动，也可以通过 ALTER 查询来移动。

用户可以通过 ALTER TABLE … MOVE PART|PARTITION … TO VOLUME|DISK … 强制移动一个数据片段或分区到另外一个卷，所有后台移动的限制都会被考虑在内。这个查询会自行启动，无需等待后台操作完成。如果没有足够的可用空间或任何必须条件没有被满足，用户会收到报错信息。 数据移动不会妨碍到数据复制。也就是说，同一张表的不同副本可以指定不同的存储策略。 在后台合并和数据变异之后，旧的数据片段会在一定时间后被移除 (old_parts_lifetime)。在这期间，他们不能被移动到其他的卷或磁盘。也就是说，直到数据片段被完全移除，它们仍然会被磁盘占用空间计算在内。

# 5. 函数

# 5.1 arrayJoin函数

普通函数不会更改结果集的行数，而只是计算每行中的值（map）。 聚合函数将多行压缩到一行中（fold或reduce）。 arrayJoin函数获取每一行并将他们展开到多行（unfold）。

此函数将数组作为参数，并将该行在结果集中复制数组元素个数。 除了应用此函数的列中的值之外，简单地复制列中的所有值;它被替换为相应的数组值。

示例:

SELECT arrayJoin([1, 2, 3] AS src) AS dst, 'Hello', src
┌─dst─┬─\'Hello\'─┬─src─────┐
│   1 │ Hello     │ [1,2,3] │
│   2 │ Hello     │ [1,2,3] │
│   3 │ Hello     │ [1,2,3] │
└─────┴───────────┴─────────┘
1
2
3
4
5
6
7

# 6. SQL示例

# 6.1 创表语句

-- ClickHouse Build Log Table
CREATE TABLE IF NOT EXISTS bi_log_oss (
    pid String,
    log_date Date DEFAULT toDate(log_time),
    log_time DateTime,
    from_time DateTime,
    reg_time Int32,
    reg_date Int32,
    data_type String,
    sdk_type Enum8('h5' = 1, 'app' = 2),
    utag Int8,
    event_value4 String,
    load_time Int32,
    load_type Int8,
    nav_app_id String,
    nav_action Int8,
    nav_wx_id Int32,
    nav_wx_pos String,
    nav_wx_ids Array(Int32),
    nav_type Int8,
    ping_ttl Int64,
    share_id Int32,
    share_image_id Int32,
    share_action Int32,
    stage_id Int32,
    stage_start_time Int32,
    stage_cost_time Int32,
    stage_action Int8,
    level Int32,
    ab_groups Array(Int32),
    trace_id String,
    purchase_amount Float64,
    purchase_coin UInt64,
    purchase_status Int8,
)
ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(log_date)
ORDER BY (log_date, data_type, pid, reg_date)
TTL log_date + INTERVAl 185 DAY DELETE
SETTINGS index_granularity=8192;
-- ClickHouse Build Log Table
CREATE TABLE IF NOT EXISTS bi_pay_log (
    pid String,
    log_date Date DEFAULT toDate(log_time),
    log_time DateTime,
    order_no String,
    status UInt8,
    pay_type UInt8,
    amount Float64,
    trade_no String,
    paid_at UInt32,
    os String,
    ip UInt32
)
ENGINE = ReplacingMergeTree()
PARTITION BY toYYYYMMDD(log_date)
ORDER BY (log_date, pid, order_no)
SETTINGS index_granularity=8192;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

# 6.2 Select

# 6.2.1 FINAL 修饰符

当 FINAL 被指定，ClickHouse会在返回结果之前完全合并数据，从而执行给定表引擎合并期间发生的所有数据转换。

它适用于从使用 MergeTree-引擎族. 还支持:

  • Replicated 版本 MergeTree 引擎
  • View, Buffer, Distributed，和 MaterializedView 在其他引擎上运行的引擎，只要是它们底层是 MergeTree-引擎表即可。 现在使用 FINAL 修饰符 的 SELECT 查询启用了并发执行, 这会快一点。但是仍然存在缺陷。 max_final_threads 设置使用的最大线程数限制。

# 6.3 OPTIMIZE

OPTIMIZE TABLE [db.]name [ON CLUSTER cluster] [PARTITION partition | PARTITION ID 'partition_id'] [FINAL] [DEDUPLICATE] 此查询尝试初始化 MergeTree家族的表引擎的表中未计划合并数据部分。 该 OPTMIZE 查询也支持 MaterializedView 和 Buffer 引擎。 不支持其他表引擎。 当 OPTIMIZE 与 ReplicatedMergeTree 家族的表引擎一起使用时，ClickHouse将创建一个合并任务，并等待所有节点上的执行（如果 replication_alter_partitions_sync 设置已启用）。

# 6.4 Alter

  • 添加列
ALTER TABLE visits ADD COLUMN browser String AFTER user_id
1
  • 删除列
ALTER TABLE visits DROP COLUMN browser
1
  • 删除数据
ALTER TABLE [db.]table [ON CLUSTER cluster] DELETE WHERE filter_expr
1

这是一个不为经常使用而设计的繁重操作。

# 删除数据
ALTER TABLE bi_log_oss delete where from_time ='2021-04-20 18:35:00' and log_date ='2021-04-20';
1
2
#ClickHouse
上次更新: 2022/12/01, 11:09:34
大数据基础知识总结
Docker入门总结

← Docker入门总结→

最近更新
01
常用游戏反外挂技术总结
11-27
02
Golang开发实践万字总结
11-11
03
Redis万字总结
10-30
更多文章>
Theme by Vdoing | Copyright © 2011-2022 Ravior | 粤ICP备17060229号-3 | MIT License