OLAP、列式数据库

SQL

select

[WITH expr_list|(subquery)]
SELECT [DISTINCT] expr_list
[FROM [db.]table | (subquery) | table_function] [FINAL]
[SAMPLE sample_coeff]
[ARRAY JOIN ...]
[GLOBAL] [ANY|ALL|ASOF] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI] JOIN (subquery)|table (ON <expr_list>)|(USING <column_list>)
[PREWHERE expr]
[WHERE expr]
[GROUP BY expr_list] [WITH TOTALS]
[HAVING expr]
[ORDER BY expr_list] [WITH FILL] [FROM expr] [TO expr] [STEP expr]
[LIMIT [offset_value, ]n BY columns]
[LIMIT [n, ]m] [WITH TIES]
[UNION ALL ...]
[INTO OUTFILE filename]
[FORMAT format]

array join

CREATE TABLE arrays_test
(
    s String,
    arr Array(UInt8)
) ENGINE = Memory;

INSERT INTO arrays_test
VALUES ('Hello', [1,2]), ('World', [3,4,5]), ('Goodbye', []);

┌─s───────────┬─arr─────┐
│ Hello       │ [1,2]   │
│ World       │ [3,4,5] │
│ Goodbye     │ []      │
└─────────────┴─────────┘

使用 LEFT ARRAY JOIN子句：

SELECT s, arr
FROM arrays_test
LEFT ARRAY JOIN arr;

┌─s───────────┬─arr─┐
│ Hello       │   1 │
│ Hello       │   2 │
│ World       │   3 │
│ World       │   4 │
│ World       │   5 │
│ Goodbye     │   0 │
└─────────────┴─────┘

可以 ARRAY JOIN ... AS ...

distinct

去重

如果DISTINCT 和 ORDER BY 同时用，则先 DISTINCT 后 ORDER BY（DISTINCT 后的结果进行 ORDER BY 排序）

SELECT DISTINCT xxx ORDER BY xxx

format

格式化输出。
ClickHouse支持广泛的 序列化格式 可用于查询结果等。 有多种方法可以选择格式化 SELECT 的输出，其中之一是指定 FORMAT format 在查询结束时以任何特定格式获取结果集。
特定的格式方便使用，与其他系统集成或增强性能。
CK 支持几十种格式输出，如 csv、json 等等。

SELECT SearchPhrase, count() AS c FROM test.hits 
GROUP BY SearchPhrase WITH TOTALS 
ORDER BY c DESC 
LIMIT 5 
FORMAT JSON

```sql
{
        "meta":
        [
                {
                        "name": "range(5)",
                        "type": "Array(UInt8)"
                }
        ],

        "data":
        [
                {
                        "'hello'": "hello",
                        "multiply(42, number)": "0",
                        "range(5)": [0,1,2,3,4]
                }
        ],
        "rows": 1,
        "rows_before_limit_at_least": 1
}

省略 format 则使用默认格式。 为 HTTP接口 和 命令行客户端 在批处理模式下，默认格式为 TabSeparated. 对于交互模式下的命令行客户端，默认格式为 PrettyCompact。

-- TabSeparated
2014-03-17      1406958
2014-03-18      1383658
2014-03-19      1405797
2014-03-20      1353623
2014-03-21      1245779

-- PrettyCompact
┌─number─┐
│      0 │
│      1 │
└────────┘

from

FROM 后接表、子查询、表函数。
FINAL 在返回结果之前，ClickHouse 会执行完全合并数据（ClickHouse 插入时，不立刻进行合并，而是系统选择时机合并）。FINAL 适用于 MergeTree引擎。
现在使用了FINAL的 SELECT 不再是单线程，可以多线程，会快一些，但是仍然存在缺陷：

在查询执行期间合并数据。
查询与 FINAL 除了读取查询中指定的列之外，还读取主键列。

所以FINAL的执行速度慢于类似的查询，应该尽量避免使用FINAL。常用的方法是使用时假设 MergeTree 的后台合并过程还没进行，并通过应用聚合解决（例如去重）。

group by

GROUP BY 子句将 SELECT 查询结果转换为聚合模式。
表中选择的每个列必须用于键表达式或聚合函数内，但不能同时使用。
group by 中把 NULL 当成一个值。

having

过滤由 GROUP BY 产生的聚合结果。
类似于 WHERE。不同的是 WHERE 在聚合前进行，HAVING 在聚合后进行。
必须有 GROUP BY 才能使用 HAVING。

join

通过一个或多个表的公共值合并一个或多个表的一些列，来产生新表。

语法：

SELECT <expr_list>
FROM <left_table>
[GLOBAL] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI|ANY|ASOF] JOIN <right_table>
(ON <expr_list>)|(USING <column_list>) ...

（inner） join 只保存匹配的行
left (outer） join 除了匹配的行，还返回左表的不匹配的行
right (outer) join 除了匹配的行，还返回右表的不匹配的行
full (outer) join 除了匹配的行，还返回左边、右表的不匹配的行
cross join 笛卡尔积
self join
asof join 模糊匹配

分布式联接（同分布式IN）

Performance 表现
1.JOIN 的右表在 WHERE 之前、聚合之前执行
2.重复执行 JOIN 右边的子查询相等的查询语句，每次都会重新计算子查询，子查询的结果不缓存，除非使用 JOIN 引擎，一个在内存中的引擎。
3.IN比JOIN高效

Memory Limitation
默认情况下，ClickHouse 使用 hash join 算法。 ClickHouse 获取 right_table 并在 RAM 中为它创建一个 hash table。 如果启用了 join_algorithm = ‘auto’，那么在内存消耗达到一定阈值后，ClickHouse 会回退到 merge join 算法.

三种数据库 join 方式
1.Hash join的工作方式是将一个表（通常是小一点的那个表）做hash运算，将列数据存储到hash列表中，从另一个表中抽取记录，做hash运算，到hash 列表中找到相应的值，做匹配。
2.Nested loops 工作方式是从一张表中读取数据，访问另一张表（通常是索引）来做匹配，nested loops适用的场合是当一个关联表比较小的时候，效率会更高。
3.Merge Join 是先将关联表的关联列各自做排序，然后从各自的排序表中抽取数据，到另一个排序表中做匹配，因为merge join需要做更多的排序，所以消耗的资源更多。 通常来讲，能够使用merge join的地方，hash join都可以发挥更好的性能。

limit

limit m 取前 m 行
limit n, m 跳过前 n 行再取 m 行，等于 limit m offset n。
limit … with ties：如果 limit n，第 n + 1 行的排序字段的值等于第 n 行，同样返回第 n + 1行，以此类推，直到第 n + m 行排序字段的值不等于第 n 行。

limit by

SELECT * FROM limit_by ORDER BY id, val LIMIT 2 BY id

┌─id─┬─val─┐
│  1 │  10 │
│  1 │  11 │
│  2 │  20 │
│  2 │  21 │
└────┴─────┘

SELECT * FROM limit_by ORDER BY id, val LIMIT 1, 2 BY id

┌─id─┬─val─┐
│  1 │  11 │
│  1 │  12 │
│  2 │  21 │
└────┴─────┘

in

有 IN、NOT IN、GLOBAL IN、GLOBAL NOT IN
IN 的右侧是单列或元祖

SELECT UserID IN (123, 456) FROM ...
SELECT (CounterID, UserID) IN ((34, 123), (101500, 456)) FROM ...

IN运算符左侧和右侧的列应具有相同的类型
如果 单列 IN (常量1，常量2…)，系统将使用索引处理查询。

涉及分布式表的join/in（分布式子查询）

• 当使用正常的JOIN（/IN），将原本的查询语句完整地发给集群的每一个服务器。每个服务器单独计算子查询。
• 使用GLOBAL JOIN（/GLOBAL IN）时，接收请求的那一个服务器将子查询结果计算出来，并将该临时表（计算结果）发给集群的每一个服务器。每个服务器直接以该临时表为子查询结果。

原 SQL：

SELECT uniq(UserID) FROM `distributed_table`

接收请求的服务器转发给集群的每一个服务器的 SQL 其实是：

SELECT uniq(UserID) FROM `local_table`

每个服务器单独计算结果，并将结果返回给接收请求的服务器，在接收请求的服务器上合并结果，并将最终结果返回给客户端。

原 SQL：

SELECT uniq(UserID) FROM `distributed_table` 
WHERE CounterID = 101500 AND UserID IN
(SELECT UserID FROM `local_table` WHERE CounterID = 34)

普通的IN，集群的每一个服务器执行的 SQL 其实是：

SELECT uniq(UserID) FROM `local_table` 
WHERE CounterID = 101500 AND UserID IN
(SELECT UserID FROM `local_table` WHERE CounterID = 34)

IN后的结果集由每个服务器（基于自己服务器上的 local_table）单独计算。
这种做法需要将有关 UserID 的这个 local_table 在每个服务器存储一份，否则结果不准确。
称这种用法为“local IN”

原 SQL：

SELECT uniq(UserID) FROM `distributed_table` 
WHERE CounterID = 101500 AND UserID IN
(SELECT UserID FROM `distributed_table` WHERE CounterID = 34)

集群的每一个服务器执行的 SQL 其实是

SELECT uniq(UserID) FROM `local_table` 
WHERE CounterID = 101500 AND UserID IN
(SELECT UserID FROM `distributed_table` WHERE CounterID = 34)

普通的IN后接distributed_table，会导致每个服务器单独计算：

SELECT UserID FROM `distributed_table` WHERE CounterID = 34

为了计算这个 distributed_table 上的结果，每个服务器都会向所有服务器发送：

SELECT UserID FROM `lcoal_table` WHERE CounterID = 34

（如果集群有100个服务器，为了这个子查询，以上这个 SQL 会执行10000次！！！）
这种情况下，应始终使用 GLOBAL IN而不是IN：

SELECT uniq(UserID) FROM `distributed_table` 
WHERE CounterID = 101500 AND UserID GLOBAL IN
(SELECT UserID FROM `distributed_table` WHERE CounterID = 34)

GLOBAL IN 会使得接收请求的那一个服务器执行：

SELECT UserID FROM `distributed_table` WHERE CounterID = 34

这会使得集群的每个服务器执行一次：

SELECT UserID FROM `local_table` WHERE CounterID = 34

各自的计算结果返回给接收请求的那一个服务器，在该服务器内部作合并。（如果集群有100个服务器，为了这个子查询，以上这个 SQL 只会执行100次）用临时表 _data1 代替查询 SQL 的一部分，发给集群的所有服务器去执行：

SELECT uniq(UserID) FROM `distributed_table` 
WHERE CounterID = 101500 AND UserID GLOBAL IN
`_data1`

这比普通IN更优化。但是，记住以下几点：

1.计算子查询并创建临时表时，不保证结果的唯一性（不去重）。为减少网络传输的数据量，可以在子查询中指定 DISTINCT。（不需为普通 IN 做这个）
2.GLOBAL IN时尽量避免使用大型数据集，因为临时表将被发送给集群的所有服务器，无论网络传输消耗多大。
3.将数据传输到远程服务器时，无法配置网络带宽限制。 您可能会使网络过载。
4.尝试跨服务器存放数据，从而在常规时候不需使用 GLOBAL IN。
5.如果需要频繁使用 GLOBAL IN，建议规划 ClickHouse 集群的分布，使得单个副本组（a single group of replicas）在一个数据中心内，使副本之间具有快速网络，以便可以完全在单个数据中心内处理查询。

ps: GLOBAL IN后接local_table也不是没有意义的，比如 local_table 只存储于接收请求的这个服务器，而又希望所有服务器使用 local_table 的子查询结果。

sample

用于 SELECT 语句。

sample n
n 如果小于1，就取 n 比例的数据进行查询。比如 sample 1/2 或 sample 0.5，就取50%的数据的样本还行查询。聚合结果不会自动修正（比如当 n = 1/2，聚合结果不会 *2）。
n 如果是足够大的整数，表示查询的一个样本在 n 行。比如 n = 1000000，就取样本为1000000行。

SAMPLE K OFFSET M
sample 1/10 offset 1/2
在后 1/2 中取 1/10 的数据。

union

UNION ALL (不去重)直接合并
UNION DISTINCT 并集(去重合并)
CK 不支持单个 “UNION” 的语法，用 “UNION DISTINCT” 代替

with

WITH '2019-08-01 15:23:00' as ts_upper_bound
SELECT *
FROM hits
WHERE
    EventDate = toDate(ts_upper_bound) AND
    EventTime <= ts_upper_bound

WITH 子句的结果可以在 SELECT 查询中使用

optimize 优化

optimize 会触发执行一次 merge 合并操作。

Syntax 语法

OPTIMIZE TABLE [db.]name 
[ON CLUSTER cluster] 
[PARTITION partition | PARTITION ID 'partition_id'] 
[FINAL] 
[DEDUPLICATE [BY expression]]

只有 MergeTree 引擎系列、 MaterializedView 引擎和 Buffer 引擎支持 OPTIMIZE 查询。
当 OPTIMIZE 用于 MergeTree 中的 ReplicatedReplacingMergeTree 时，有参数 replication_alter_partitions_sync 决定合并所有副本都执行合并或当前副本执行合并。

• 如果 OPTIMIZE 执行 merge 合并失败（由于某种原因），不会主动通知客户端（可设置）。
• 如果指定了 PARTITION，只会对指定的 partition 分区进行 OPTIMIZE。
• 如果指定了 FINAL，即使所有数据已经在一个数据部分（part）里了，也会执行 OPTIMIZE 进行 merge。即使执行了并发合并 concurrent merges，也会执行 OPTIMIZE 进行 merge。
• 如果指定了 DEDUPLICATE，完全相同的行（除非指定了 by-clause）将被去重（每个列字段都相同）。仅仅适用于 MergeTree 引擎。

ps: CK 存储的物理结构

一个 partition (分区) 有多个 part (数据片段)
一个 part 有多个 block (数据块) (仅当选择跳数索引时，有 block，并以 block 维度进行索引标记)
一个 block 有多个 granule (颗粒)
============================================================================
partition 可以理解为表的多行(这些行都满足同个分区条件)
part 可以理解为 partition 的部分行，保存的可能是这部分行的一列或全部列(即整行)，取决于 Wide 或 Compact 模式
granule 可以理解为 part 的部分行，最少是1行，是数据查询时的最小不可分割数据集。granule 的第一行通过该行的主键 (排序键) 的值进行标记。每个 part 有一个索引文件，保存了这个 part 的多个 granules 的标记值 (每个 granule 的第一行的主键 (排序键) 的值 )。

表引擎

MergeTree

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

主要特点：
1.按主键排序
2.如果指定了 partition key，可以使用分区。查询中指定了分区键时 ClickHouse 会自动截取分区数据，有效提高了查询效率。（分而治之，将各个 partition 的计算结果聚合）

建表

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, ...]

• ENGINE - 引擎名和参数。 ENGINE = MergeTree(). MergeTree 引擎没有参数。
• ORDER BY - 排序键。可以是列的元组或任意表达式。例如: ORDER BY (CounterID, EventDate)。如果没有使用 PRIMARY KEY 显式指定的主键，ClickHouse 会使用排序键作为主键。如果不需要排序，可以使用 ORDER BY tuple()。
• PARTITION BY — 分区键 ，可选项。如果要按月分区，可以使用表达式 toYYYYMM(date_column) ，这里的 date_column 是一个 Date 类型的列。分区名的格式会是 “YYYYMM” 。
• PRIMARY KEY - 如果要 选择与排序键不同的主键，在这里指定，可选项。
  默认情况下主键跟排序键（由 ORDER BY 子句指定）相同。
  因此，大部分情况下不需要再专门指定一个 PRIMARY KEY 子句。
• SAMPLE BY - 可用于抽样的表达式，可选项。
  如果要用抽样表达式，主键中必须包含这个表达式。例如：
  SAMPLE BY intHash32(UserID)
• TTL - 指定行存储的持续时间并定义数据片段在硬盘和卷上的移动逻辑的规则列表
  表达式中必须存在至少一个 Date 或 DateTime 类型的列，比如：
  TTL date + INTERVAl 1 DAY（行的 date 字段的值 + 1天）
  可选的规则为[DELETE|TO DISK ‘xxx’|TO VOLUME ‘xxx’]。直接移除过期行（DELETE），将数据片段（如果数据片段中的所有行都满足表达式的话）移动到指定的磁盘（TO DISK ‘xxx’) 或 卷（TO VOLUME ‘xxx’）。默认是 DELETE。可以在列表中指定多个规则，但最多只能有一个DELETE的规则。
• SETTINGS — 控制 MergeTree 行为的额外参数，可选项.

示例配置

CREATE table t()
ENGINE MergeTree() 
PARTITION BY toYYYYMM(EventDate) 
ORDER BY (CounterID, EventDate, intHash32(UserID)) 
SAMPLE BY intHash32(UserID) 
SETTINGS index_granularity=8192

按月分区
按 UserID hash 的抽样表达式，使得可以对该表中每个 CounterID 和 EventDate 的数据伪随机分布。如果您在查询时指定了 SAMPLE 子句（SELECT CounterID, EventDate SAMPLE BY intHash32(UserID) ）。 ClickHouse会返回对于用户子集的一个均匀的伪随机数据采样。
index_granularity 指定了索引粒度。索引中相邻的『标记』间的数据行数。默认值8192。

数据存储（核心）

表由按主键 (排序键) 排序的数据片段（data part）组成。
当数据被插入时，会创建多个由插入数据生成的按主键的字典序排序的数据片段。

例如，主键是 (CounterID, Date) 时，片段中数据首先按 CounterID 排序，具有相同 CounterID 的部分按 Date 排序。

不同 partition 分区的数据会分为不同的数据片段。（绝不会有同个数据片段的数据属于两个 partition 分区）。ClickHouse 会（异步）合并同一 partition 分区的数据片段以便更高效存储。
合并不能保证相同主键的行在一个数据片段中。

数据片段可以以 Wide 或 Compact 格式存储。在 Wide 格式下，每一列都会在文件系统中存储为单独的文件，在 Compact 格式下所有列都存储在一个文件中。Compact 格式可以提高插入量少插入频率频繁时的性能（只用写一个文件）。

每个数据片段被逻辑的分割成颗粒（granules）。颗粒是 ClickHouse 中进行数据查询时的最小不可分割数据集。ClickHouse 不会对每一行（只有1列，实际上是 cell）或每个值进行分割，所以每个颗粒保存的是整数行。每个颗粒的第一行通过该行的主键值进行标记。

ClickHouse 会为每个数据片段创建一个索引文件来存储这些标记。（每个数据片段保存多个颗粒，每个颗粒有一个标记）。对于每列，无论它是否包含在主键当中，ClickHouse 都会存储类似标记。这些标记让您可以在列文件中直接找到数据。（标记中存储每个列的值）
颗粒的大小可以由表引擎参数配置。如果行的大小超过颗粒的最大大小，每个颗粒直接存储一个行。

主键和索引在查询中的表现

我们以 (CounterID, Date) 以主键。排序好的索引的图示会是下面这样：

全部数据  :[----------------------------------------------------------------]
CounterID:[aaaaaaaaaaaaaaaaaabbbbcdeeeeeeeeeeeeefgggggggghhhhhhhhhiiiiiiiii]
Date:     [1111111222222233331233211111222222333211111112122222223111112223]
标记:      |      |      |      |      |      |      |      |      |       |
         a,1    a,2    a,3    b,3    e,2    e,3    g,1    h,2    i,1     i,3
标记号:    0      1      2      3      4      5      6      7      8       9

用于标记的表达式元组（如：a，1）就是主键的元组（CounterID, Date）

如果指定查询如下：
CounterID in (‘a’, ‘h’)，服务器会读取标记号在 [0, 3) 和 [6, 8) 区间中的数据。
CounterID IN (‘a’, ‘h’) AND Date = 3，服务器会读取标记号在 [1, 3) 和 [7, 8) 区间中的数据。
Date = 3，服务器会读取标记号在 [1, 9] 区间中的数据。

从上面的例子可以看出，使用索引通常比全表扫描更高效。
稀疏索引会引起额外的数据读取，稀疏索引常驻内存。
ClickHouse 的主键不唯一，可以插入主键相同的多行。

选择与 sort key 排序键不同的 primary key 主键。

sort key 排序键：用于对数据片段中的行进行排序
primary key 主键：写入索引文件的每个标记位置。written in the index file for each mark。
但是，主键元组必须是排序键元组的前缀。

例如
排序键 (CounterID, Date), 不设主键，则默认主键等于排序键为 (CounterID, Date)。part 保存数据时的排序为 (CounterID, Date)，part 对应的索引文件保存的标记为 (CounterID, Date)。
排序键 (CounterID, Date), 主键 (CounterID)。part 保存数据时的排序为 (CounterID, Date)，part 对应的索引文件保存的标记为 (CounterID)。
ps: 因此，大部分情况下不需要再建表时专门指定一个 PRIMARY KEY 主键。

索引和分区在查询中的应用

对于 SELECT 查询，ClickHouse 分析能否使用索引。
如果 WHERE/PREWHERE 子句具有下面表达式（作为 WHERE 子句的部分或全部），则可以使用索引：

进行相等/不相等的比较
对主键列或分区列进行 IN 运算（用主键索引剪主键列，用分区键剪不必要的分区）
前缀 like
（部分）函数运算
以上表达式的逻辑运算

例如，在如下引擎配置下

   ENGINE MergeTree() PARTITION BY toYYYYMM(EventDate) ORDER BY (CounterID, EventDate) SETTINGS index_granularity=8192

这种情况下，这些查询

SELECT count() FROM table WHERE EventDate = toDate(now()) AND CounterID = 34
SELECT count() FROM table WHERE EventDate = toDate(now()) AND (CounterID = 34 OR CounterID = 42)
SELECT count() FROM table WHERE ((EventDate >= toDate('2014-01-01') AND EventDate <= toDate('2014-01-31')) OR EventDate = toDate('2014-05-01')) AND CounterID IN (101500, 731962, 160656) AND (CounterID = 101500 OR EventDate != toDate('2014-05-01'))

ClickHouse 会依据主键索引剪掉不符合的数据，依据按月分区的分区键剪掉那些不包含符合数据的分区。

跳数索引

允许若干个颗粒 pronules 组合成一个大的块 block，对这些大块写入索引信息，这样有助于使用where筛选时跳过大量不必要的数据，减少SELECT需要读取的数据量。
跳数索引有多种索引类型。

并发数据访问

表的并发访问，ck 使用多版本机制。不会加锁。
表的读是自动并行的。

列和表的 TTL

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

TTL time_column
TTL time_column + interval

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

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

列级 TTL
设置列级 TTL 时，当行的该列字段过期时，会将该行的该列字段的值替换成该列数据类型的默认值。如果一个数据片段（part）中的所有行的该列字段都过期了，会删除该列的数据文件。

CREATE TABLE example_table
(
    d DateTime,
    a Int TTL d + INTERVAL 1 MONTH,
    b Int TTL d + INTERVAL 1 MONTH,
    c String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(d)
ORDER BY d;

表级 TTL
用于移除过期行，或转移数据片段到磁盘或卷，或做聚合。转移数据片段必须该数据片段的所有行都满足 TTL 条件。

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) ...]] ]

什么都不加，默认为 DELETE。
建表示例：

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',
    d + INTERVAL 1 WEEK DELETE WHERE toDayOfWeek(d) = 1;--删除过期行中周一的数据行

删除数据。
当 ck 进行数据片段（part）合并时，会顺便删除过期的数据。
当 ck 发现过期的数据时，它将会执行一个计划外的合并。如果在合并的过程中进行 SELECT 查询，可能会查到过期的数据。为了避免这种情况，可以在 SELECT 之前使用 OPTIMIZE。

使用多个块设备（Multiple Block Devices）进行数据存储

MergeTree 系列表引擎可以将数据存储在多个块设备上。这对某些潜在上可以划分为“冷”和“热”的表来说很有用。最经常被查询的数据只占用很小的空间（数据量很少），而大量的详尽的历史数据则很少被使用。如果有多块磁盘可用，“热”数据可以放在快速的磁盘上（比如 NVMe 固态硬盘或内存），“冷”数据可以放在较慢的磁盘上（如机械硬盘）。

ReplicatedReplacingMergeTree

MergeTree 本身是一种引擎，MergeTree 也是一个引擎族，MergeTree 下边有各种 xxxMergeTree，各种 xxxMergeTree 在合并时有特定的规则。比如 ReplicatedReplacingMergeTree 在合并时，对于排序键相同的多条记录，删除旧的行，只保留最新的一行。

数据副本

只有 MergeTree 系列的表支持数据副本

ReplicatedMergeTree
ReplicatedSummingMergeTree
ReplicatedReplacingMergeTree
ReplicatedAggregatingMergeTree
ReplicatedCollapsingMergeTree
ReplicatedVersionedCollapsingMergetree
ReplicatedGraphiteMergeTree

副本是表级别的，不是服务器级别的，所以，服务器里可以同时有复制表和非复制表。（有的表有多个副本，有的表没有副本）

INSERT 和 ALTER 语句操作数据的会在压缩的情况下被复制。
而 CREATE，DROP，ATTACH，DETACH 和 RENAME 语句只会在单个服务器上执行，不会被复制。

CREATE TABLE 在运行此语句的服务器上创建一个新的可复制表。如果此表已存在其他服务器上，则给该表添加新副本。
DROP TABLE 删除运行此查询的服务器上的副本。
The RENAME 重命名一个副本。换句话说，可复制表不同的副本可以有不同的名称。

处于复制状态时，只有要插入的源数据通过网络传输。进一步的数据转换（合并）会在所有副本上以相同的状态进行。

创建复制表：
在表引擎前面加上Replicated前缀，比如 ReplicatedMergeTree。

Replicated*MergeTree 参数:

• zoo_path：ZooKeeper 中该表的路径（
• replica_name：ZooKeeper 中该表的副本名称

示例：

CREATE TABLE table_name
(
   EventDate DateTime,
   CounterID UInt32,
   UserID UInt32
) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/table_name', '{replica}')
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, EventDate, intHash32(UserID))
SAMPLE BY intHash32(UserID)

如上例所示，这些参数可以包含宏替换的占位符，即大括号的部分。它们会被替换为配置文件里 ‘macros’ 那部分配置的值。示例：

<macros>
    <layer>05</layer>
    <shard>02</shard>
    <replica>example05-02-1.yandex.ru</replica>
</macros>

Replicated*MergeTree 的参数中的 zoo_path，对每个可复制表是独立的。不同 shard 分片上表要有不同的路径。
/clickhouse/tables 是推荐使用的公共前缀。
{layer}-{shard} 是分片标识部分。在此示例中，由于 Yandex.Metrica 集群使用了两级分片，所以它是由两部分组成的。但对于大多数情况来说，你只需保留 {shard} 占位符即可，它会替换展开为分片标识。
table_name 是该表在 ZooKeeper 中的名称。使其与 ClickHouse 中的表名相同比较好。 这里它被明确定义，跟 ClickHouse 表名不一样，它并不会被 RENAME 语句修改。你可以在前面添加一个数据库名称 table_name 。例如：db_name.table_name。
{replica} 副本名称被用于区分一个 shard 中的不同表副本。因此，同一 shard 分区中的不同表副本的副本名称不同。

xxMergeTree 和 Replicated**MergeTree 通过一定步骤可以互相转化。

自定义分区键（custom partitioning key）

官方文档: https://clickhouse.com/docs/zh/engines/table-engines/mergetree-family/custom-partitioning-key/.
MergeTree 系列的表（包括表副本）可以支持分区。基于 MergeTree表 的物化视图也支持分区。
分区是一张表根据指定的规则划分成的一些逻辑数据集。可以按任意标准进行划分，比如日期上按月、日期上按天、按事件类型划分。为了减少需要操作的数据，每个分区都是分开存储的。访问数据时，ck 尽量访问这些分区的最小子集。
分区是在建表时通过 partition by子句来指定的。分区键可以是表中列的任意表达式。例如，指定按月分区，表达式为 toYYYYMM(date_column)。

What is merge？
新数据插入到表中时，这些数据会存储为按主键排序的独立的部分 part（块 chunk，不是 block）。插入后 10-15 分钟，一个partition 内的多个 parts 会合并成一个 part。
When inserting new data to a table, this data is stored as a separate part (chunk) sorted by the primary key. In 10-15 minutes after inserting, the parts of the same partition are merged into the entire part.

partition 不宜过多，分区不宜太细，否则会因为文件系统中文件数量过多和需要打开的文件描述符过多，影响 SELECT 的效率。

Distributed 分布式引擎

分布式引擎本身不存储数据，但可以在多个服务器上进行分布式查询。

shard 分片：包含数据的不同部分的服务器。（某分布式表分布在3台服务器上，这3台服务器就是3个 shard 分片）要读取数据的所有部分，需要访问数据的所有分片。
replica 副本：存储数据副本的服务器。要读取数据的所有部分（所有 shards 切片），每个部分（shard 切片）可能会有多个 replicas 副本，只需访问其中一个 replica 副本即可。

一个 cluster 集群包含多个 shards 分片，一个 shard 分片包含多个 replicas 副本。

Distributed(remote_group, database, table [, sharding_key])

remote_group /etc/clickhouse-server/config.xml 中 remote_servers 参数
database 是各服务器中的库名
table 是表名
sharding_key 是一个寻址表达式，可以是一个列名，也可以是像 rand() 之类的函数调用，它与 remote_servers 中的 weight 共同作用，决定在写时往哪个 shard 写。

最后

以上就是英俊马里奥为你收集整理的ClickHouseSQL表引擎的全部内容，希望文章能够帮你解决ClickHouseSQL表引擎所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。