四 ClickHouse引擎详解

表引擎是ClickHouse设计实现中的一大特色 ,数据表拥有何种特性、数据以何 种形式被存储以及如何被加载。ClickHouse拥有非常庞大的表引擎体 系，截至本书完成时，其共拥有合并树、外部存储、内存、文件、接口 和其他6大类20多种表引擎。而在这众多的表引擎中，又属合并树 （MergeTree）表引擎及其家族系列（*MergeTree）最为强大，在生产 环境的绝大部分场景中，都会使用此系列的表引擎。因为只有合并树系 列的表引擎才支持主键索引、数据分区、数据副本和数据采样这些特 性，同时也只有此系列的表引擎支持ALTER相关操作。

合并树家族自身也拥有多种表引擎的变种。其中MergeTree作为家 族中最基础的表引擎，提供了主键索引、数据分区、数据副本和数据采 样等基本能力，而家族中其他的表引擎则在MergeTree的基础之上各有 所长。例如ReplacingMergeTree表引擎具有删除重复数据的特性，而 SummingMergeTree表引擎则会按照排序键自动聚合数据。如果给合并树 系列的表引擎加上Replicated前缀，又会得到一组支持数据副本的表引 擎，例如ReplicatedMergeTree、ReplicatedReplacingMergeTree、 ReplicatedSummingMergeTree等。

表引擎（即表的类型）决定了：

1. 数据的存储方式和位置，写到哪里以及从哪里读取数据

2. 支持哪些查询以及如何支持。

3. 并发数据访问。

4. 索引的使用（如果存在）。

5. 是否可以执行多线程请求。

6. 数据复制参数,是否可以存储数据副本。

7. 分布式引擎 实现分布式

... ...

1 Log系列引擎

Log家族具有最小功能的[轻量级引擎。当您需要快速写入许多小表（最多约100万行）并在以后整体读取它们时，该类型的引擎是最有效的。

1.1 TinyLog引擎

最简单的表引擎，用于将数据存储在磁盘上。每列都存储在单独的压缩文件中，写入时，数据将附加到文件末尾。该引擎没有并发控制

 1、最简单的引擎
 2、没有索引，没有标记块
 3、写是追加写
 4、数据以列字段文件存储
 5、不允许同时读写

　

-- 建表 
create table test_tinylog(
    id UInt8 ,
    name String ,
    age UInt8
)engine=TinyLog ;
-- 查看表结构
desc test_tinylog ;
-- 查看建表语句 
SHOW CREATE TABLE test_tinylog ;
-- 插入数据 
insert into test_tinylog values(1,'liubei',45),(2,'guanyu',43),(3,'zhangfei',41) ;
​
SELECT *
FROM test_tinylog
​
┌─id─┬─name─────┬─age─┐
│  1 │ liubei   │  45 │
│  2 │ guanyu   │  43 │
│  3 │ zhangfei │  41 │
└────┴──────────┴─────┘

　　

查看数底层存储

[root@doit01 test_tinylog]# pwd
/var/lib/clickhouse/data/default/test_tinylog
-rw-r-----. 1 clickhouse clickhouse 29 May 19 15:29 age.bin
-rw-r-----. 1 clickhouse clickhouse 29 May 19 15:29 id.bin
-rw-r-----. 1 clickhouse clickhouse 50 May 19 15:29 name.bin
-rw-r-----. 1 clickhouse clickhouse 90 May 19 15:29 sizes.json
-- 当再次插入数据以后 , 在每个文件中追加写入的
-rw-r-----. 1 clickhouse clickhouse  58 May 19 15:31 age.bin
-rw-r-----. 1 clickhouse clickhouse  58 May 19 15:31 id.bin
-rw-r-----. 1 clickhouse clickhouse 100 May 19 15:31 name.bin
-rw-r-----. 1 clickhouse clickhouse  91 May 19 15:31 sizes.json

　　

insert into t select * from t 会将表存储结构损坏  :  删除表目录 删除元数据 

1.2 StripeLog引擎

 1、data.bin存储所有数据
 2、index.mrk 对数据建立索引
 3、size.json 数据大小
 4、并发读写

　　

create table test_stripelog(
    id UInt8 ,
    name String ,
    age UInt8
)engine=StripeLog ;
-- 插入数据 
insert into test_stripelog values(1,'liubei',45),(2,'guanyu',43),(3,'zhangfei',41) ;

查看底层数据

/var/lib/clickhouse/data/default/test_stripelog
-rw-r-----. 1 clickhouse clickhouse 167 May 19 15:43 data.bin  存储所有列的数据
-rw-r-----. 1 clickhouse clickhouse  75 May 19 15:43 index.mrk  记录数据的索引信息
-rw-r-----. 1 clickhouse clickhouse  68 May 19 15:43 sizes.json  记录文件内容的大小

1.3 Log引擎

日志与 TinyLog 的不同之处在于，«标记» 的小文件与列文件存在一起。这些标记写在每个数据块上，并且包含偏移量，这些偏移量指示从哪里开始读取文件以便跳过指定的行数。这使得可以在多个线程中读取表数据。对于并发数据访问，可以同时执行读取操作，而写入操作则阻塞读取和其它写入。Log 引擎不支持索引。同样，如果写入表失败，则该表将被破坏，并且从该表读取将返回错误。Log 引擎适用于临时数据，write-once 表以及测试或演示目的。

1、*.bin存储每个字段的数据
 2、mark.mrk 数据块标记
 3、支持多线程处理
 4、并发读写

　　

create table test_log(
    id UInt8 ,
    name String ,
    age UInt8
)engine=Log ;
insert into test_log values(1,'liubei',45),(2,'guanyu',43),(3,'zhangfei',41) ;

　　

查看数据

-rw-r-----. 1 clickhouse clickhouse  29 May 19 15:46 age.bin
-rw-r-----. 1 clickhouse clickhouse  29 May 19 15:46 id.bin
-rw-r-----. 1 clickhouse clickhouse  48 May 19 15:46 __marks.mrk
-rw-r-----. 1 clickhouse clickhouse  50 May 19 15:46 name.bin
-rw-r-----. 1 clickhouse clickhouse 120 May 19 15:46 sizes.json

　　

Log 和 StripeLog 引擎支持：

并发访问数据的锁。

INSERT 请求执行过程中表会被锁定，并且其他的读写数据的请求都会等待直到锁定被解除。如果没有写数据的请求，任意数量的读请求都可以并发执行。

并行读取数据。

在读取数据时，ClickHouse 使用多线程。 每个线程处理不同的数据块。

Log 引擎为表中的每一列使用不同的文件。StripeLog 将所有的数据存储在一个文件中。因此 StripeLog 引擎在操作系统中使用更少的描述符，但是 Log 引擎提供更高的读性能。

TinyLog 引擎是该系列中最简单的引擎并且提供了最少的功能和最低的性能。TingLog 引擎不支持并行读取和并发数据访问，并将每一列存储在不同的文件中。它比其余两种支持并行读取的引擎的读取速度更慢，并且使用了和 Log 引擎同样多的描述符。你可以在简单的低负载的情景下使用它。

2 MergeTree系列引擎

MergeTree系列的表引擎是ClickHouse数据存储功能的核心。它们提供了用于弹性和高性能数据检索的大多数功能：列存储，自定义分区，稀疏的主索引，辅助数据跳过索引等。

基本[MergeTree表引擎可以被认为是单节点ClickHouse实例的默认表引擎，因为它在各种用例中通用且实用。

除了基础表引擎MergeTree之 外，常用的表引擎还有ReplacingMergeTree、SummingMergeTree、 AggregatingMergeTree、CollapsingMergeTree和 VersionedCollapsingMergeTree。每一种合并树的变种，在继承了基 础MergeTree的能力之后，又增加了独有的特性。其名称中的“合并” 二字奠定了所有类型MergeTree的基因，它们的所有特殊逻辑，都是在 触发合并的过程中被激活的。

主要特点：

• 存储按主键排序的数据。

这使您可以创建一个小的稀疏索引，以帮助更快地查找数据。

• 如果指定了[分区键]则可以使用[分区)。

ClickHouse支持的某些分区操作比对相同数据，相同结果的常规操作更有效。ClickHouse还会自动切断在查询中指定了分区键的分区数据。这也提高了查询性能。

• 数据复制支持。

ReplicatedMergeTree表族提供数据复制。有关更多信息.

• 数据采样支持。

如有必要，可以在表中设置数据采样方法。

2.1 MergeTree引擎

MergeTree在写入一批数据时，数据总会以数据片段的形式写入磁盘，且数据片段不可修改。为了避免片段过多，ClickHouse会通过后台线程，定期合并这些数据片段，属于相同分区的数据片段会被合成 一个新的片段。这种数据片段往复合并的特点，也正是合并树名称的由来。

语法

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

　　

MergeTree表引擎除了常规参数之外，还拥有一些独有的配置选 项。接下来会着重介绍其中几个重要的参数，

（1）PARTITION BY [选填]：分区键，用于指定表数据以何种标 准进行分区。分区键既可以是单个列字段，也可以通过元组的形式使 用多个列字段，同时它也支持使用列表达式。如果不声明分区键，则 ClickHouse会生成一个名为all的分区。合理使用数据分区，可以有效 减少查询时数据文件的扫描范围，更多关于数据分区的细节会在6.2节 介绍。

（2）ORDER BY [必填]：排序键，用于指定在一个数据片段内， 数据以何种标准排序。默认情况下主键（PRIMARY KEY）与排序键相 同。排序键既可以是单个列字段，例如ORDER BY CounterID，也可以 通过元组的形式使用多个列字段，例如ORDER BY（CounterID,EventDate）。当使用多个列字段排序时，以ORDER BY（CounterID,EventDate）为例，在单个数据片段内，数据首先会以 CounterID排序，相同CounterID的数据再按EventDate排序。

（3）PRIMARY KEY [选填]：主键，顾名思义，声明后会依照主键 字段生成一级索引，用于加速表查询。默认情况下，主键与排序键 (ORDER BY)相同，所以通常直接使用ORDER BY代为指定主键，无须刻 意通过PRIMARY KEY声明。所以在一般情况下，在单个数据片段内，数 据与一级索引以相同的规则升序排列。与其他数据库不同，MergeTree 主键允许存在重复数据（ReplacingMergeTree可以去重）。

（4）SAMPLE BY [选填]：抽样表达式，用于声明数据以何种标准 进行采样。如果使用了此配置项，那么在主键的配置中也需要声明同 样的表达式，例如：

省略... 
) ENGINE = MergeTree() 
ORDER BY (CounterID, EventDate, intHash32(UserID) SAMPLE BY intHash32(UserID) 

（5）SETTINGS：index_granularity [选填]： index_granularity对于MergeTree而言是一项非常重要的参数，它表 示索引的粒度，默认值为8192。也就是说，MergeTree的索引在默认情 况下，每间隔8192行数据才生成一条索引，其具体声明方式如下所 示：

省略... 
) ENGINE = MergeTree() 
省略... 
SETTINGS index_granularity = 8192; 

8192是一个神奇的数字，在ClickHouse中大量数值参数都有它的 影子，可以被其整除（例如最小压缩块大小 min_compress_block_size:65536）。通常情况下并不需要修改此参 数，但理解它的工作原理有助于我们更好地使用MergeTree。关于索引 详细的工作原理会在后续阐述。

（6）SETTINGS：index_granularity_bytes [选填]：在19.11版本之前，ClickHouse只支持固定大小的索引间隔，由 index_granularity控制，默认为8192。在新版本中，它增加了自适应 间隔大小的特性，即根据每一批次写入数据的体量大小，动态划分间 隔大小。而数据的体量大小，正是由index_granularity_bytes参数控 制的，默认为10M(10×1024×1024)，设置为0表示不启动自适应功 能。

（7）SETTINGS：enable_mixed_granularity_parts [选填]：设 置是否开启自适应索引间隔的功能，默认开启。

（8）SETTINGS：merge_with_ttl_timeout [选填]：从19.6版本 开始，MergeTree提供了数据TTL的功能，

（9）SETTINGS：storage_policy [选填]：从19.15版本开始， MergeTree提供了多路径的存储策略，关于这部分的详细介绍，

1) 创建表

drop table if exists tb_merge_tree ;
create table tb_merge_tree(
id Int8 ,
city String ,
ctime Date 
)
engine=MergeTree()
order by id 
partition by city ;
-- 查看建表语句
│ CREATE TABLE default.tb_merge_tree
(
    `id` Int8,
    `city` String,
    `ctime` Date
)
ENGINE = MergeTree()
PARTITION BY city
ORDER BY id
SETTINGS index_granularity = 8192 │

2)导入数据

insert into tb_merge_tree values(1,'BJ',now()) ,(2,'NJ',now()),(3,'DJ',now());
insert into tb_merge_tree values(4,'BJ',now()) ,(5,'NJ',now()),(6,'DJ',now());
insert into tb_merge_tree values(7,'BJ',now()) ,(8,'NJ',now()),(9,'DJ',now());
insert into tb_merge_tree values(10,'BJ',now()) ,(11,'NJ',now()),(12,'DJ',now());
┌─id─┬─city─┬──────ctime─┐
│  9 │ DJ   │ 2021-05-19 │
└────┴──────┴────────────┘
┌─id─┬─city─┬──────ctime─┐
│  2 │ NJ   │ 2021-05-19 │
└────┴──────┴────────────┘
┌─id─┬─city─┬──────ctime─┐
│  5 │ NJ   │ 2021-05-19 │
└────┴──────┴────────────┘
┌─id─┬─city─┬──────ctime─┐
│ 12 │ DJ   │ 2021-05-19 │
└────┴──────┴────────────┘
┌─id─┬─city─┬──────ctime─┐
│  8 │ NJ   │ 2021-05-19 │
└────┴──────┴────────────┘
┌─id─┬─city─┬──────ctime─┐
│ 11 │ NJ   │ 2021-05-19 │
└────┴──────┴────────────┘
┌─id─┬─city─┬──────ctime─┐
│  1 │ BJ   │ 2021-05-19 │
└────┴──────┴────────────┘
┌─id─┬─city─┬──────ctime─┐
│  3 │ DJ   │ 2021-05-19 │
└────┴──────┴────────────┘
┌─id─┬─city─┬──────ctime─┐
│  4 │ BJ   │ 2021-05-19 │
└────┴──────┴────────────┘
┌─id─┬─city─┬──────ctime─┐
│  6 │ DJ   │ 2021-05-19 │
└────┴──────┴────────────┘
┌─id─┬─city─┬──────ctime─┐
│  7 │ BJ   │ 2021-05-19 │
└────┴──────┴────────────┘
┌─id─┬─city─┬──────ctime─┐
│ 10 │ BJ   │ 2021-05-19 │
└────┴──────┴────────────┘

　　

3)合并数据

optimize table tb_merge_tree final ;  一次性按照分区合并所有的数据
SELECT *
FROM tb_merge_tree
┌─id─┬─city─┬──────ctime─┐
│  3 │ DJ   │ 2021-05-19 │
│  6 │ DJ   │ 2021-05-19 │
│  9 │ DJ   │ 2021-05-19 │
│ 12 │ DJ   │ 2021-05-19 │
└────┴──────┴────────────┘
┌─id─┬─city─┬──────ctime─┐
│  2 │ NJ   │ 2021-05-19 │
│  5 │ NJ   │ 2021-05-19 │
│  8 │ NJ   │ 2021-05-19 │
│ 11 │ NJ   │ 2021-05-19 │
└────┴──────┴────────────┘
┌─id─┬─city─┬──────ctime─┐
│  1 │ BJ   │ 2021-05-19 │
│  4 │ BJ   │ 2021-05-19 │
│  7 │ BJ   │ 2021-05-19 │
│ 10 │ BJ   │ 2021-05-19 │
└────┴──────┴────────────┘
CK内部会自动的合并分区的数据, 也会删除多余的文件夹中的数据

4)数据存储原理
# 合并前
drwxr-x---. 2 clickhouse clickhouse 223 May 19 16:48 0b1654565b11c57ce8e06fba0d990406_11_11_0
drwxr-x---. 2 clickhouse clickhouse 223 May 19 16:48 0b1654565b11c57ce8e06fba0d990406_2_2_0
drwxr-x---. 2 clickhouse clickhouse 223 May 19 16:48 0b1654565b11c57ce8e06fba0d990406_5_5_0
drwxr-x---. 2 clickhouse clickhouse 223 May 19 16:48 0b1654565b11c57ce8e06fba0d990406_8_8_0
drwxr-x---. 2 clickhouse clickhouse 223 May 19 16:48 4ac8f272bc049477e80a3f42338ca531_12_12_0
drwxr-x---. 2 clickhouse clickhouse 223 May 19 16:48 4ac8f272bc049477e80a3f42338ca531_3_3_0
drwxr-x---. 2 clickhouse clickhouse 223 May 19 16:48 4ac8f272bc049477e80a3f42338ca531_6_6_0
drwxr-x---. 2 clickhouse clickhouse 223 May 19 16:48 4ac8f272bc049477e80a3f42338ca531_9_9_0
drwxr-x---. 2 clickhouse clickhouse   6 May 19 16:48 detached
drwxr-x---. 2 clickhouse clickhouse 223 May 19 16:48 e35d0ca9d946a627c9fc98b8f80391ce_10_10_0
drwxr-x---. 2 clickhouse clickhouse 223 May 19 16:48 e35d0ca9d946a627c9fc98b8f80391ce_1_1_0
drwxr-x---. 2 clickhouse clickhouse 223 May 19 16:48 e35d0ca9d946a627c9fc98b8f80391ce_4_4_0
drwxr-x---. 2 clickhouse clickhouse 223 May 19 16:48 e35d0ca9d946a627c9fc98b8f80391ce_7_7_0
-rw-r-----. 1 clickhouse clickhouse   1 May 19 16:48 format_version.txt
# 合并后 
drwxr-x---. 2 clickhouse clickhouse 223 May 19 16:48 0b1654565b11c57ce8e06fba0d990406_11_11_0
drwxr-x---. 2 clickhouse clickhouse 223 May 19 16:49 0b1654565b11c57ce8e06fba0d990406_2_11_1
drwxr-x---. 2 clickhouse clickhouse 223 May 19 16:48 0b1654565b11c57ce8e06fba0d990406_2_2_0
drwxr-x---. 2 clickhouse clickhouse 223 May 19 16:48 0b1654565b11c57ce8e06fba0d990406_5_5_0
drwxr-x---. 2 clickhouse clickhouse 223 May 19 16:48 0b1654565b11c57ce8e06fba0d990406_8_8_0
drwxr-x---. 2 clickhouse clickhouse 223 May 19 16:48 4ac8f272bc049477e80a3f42338ca531_12_12_0
drwxr-x---. 2 clickhouse clickhouse 223 May 19 16:49 4ac8f272bc049477e80a3f42338ca531_3_12_1
drwxr-x---. 2 clickhouse clickhouse 223 May 19 16:48 4ac8f272bc049477e80a3f42338ca531_3_3_0
drwxr-x---. 2 clickhouse clickhouse 223 May 19 16:48 4ac8f272bc049477e80a3f42338ca531_6_6_0
drwxr-x---. 2 clickhouse clickhouse 223 May 19 16:48 4ac8f272bc049477e80a3f42338ca531_9_9_0
drwxr-x---. 2 clickhouse clickhouse   6 May 19 16:48 detached
drwxr-x---. 2 clickhouse clickhouse 223 May 19 16:48 e35d0ca9d946a627c9fc98b8f80391ce_10_10_0
drwxr-x---. 2 clickhouse clickhouse 223 May 19 16:48 e35d0ca9d946a627c9fc98b8f80391ce_1_1_0
drwxr-x---. 2 clickhouse clickhouse 223 May 19 16:49 e35d0ca9d946a627c9fc98b8f80391ce_1_10_1
drwxr-x---. 2 clickhouse clickhouse 223 May 19 16:48 e35d0ca9d946a627c9fc98b8f80391ce_4_4_0
drwxr-x---. 2 clickhouse clickhouse 223 May 19 16:48 e35d0ca9d946a627c9fc98b8f80391ce_7_7_0
-rw-r-----. 1 clickhouse clickhouse   1 May 19 16:48 format_version.txt
-- 进入到一个分区目录中查看
-rw-r-----. 1 clickhouse clickhouse 385 May 19 17:12 checksums.txt
-rw-r-----. 1 clickhouse clickhouse  38 May 19 17:12 city.bin
-rw-r-----. 1 clickhouse clickhouse  48 May 19 17:12 city.mrk2
-rw-r-----. 1 clickhouse clickhouse  74 May 19 17:12 columns.txt
-rw-r-----. 1 clickhouse clickhouse   1 May 19 17:12 count.txt
-rw-r-----. 1 clickhouse clickhouse  34 May 19 17:12 ctime.bin
-rw-r-----. 1 clickhouse clickhouse  48 May 19 17:12 ctime.mrk2
-rw-r-----. 1 clickhouse clickhouse  30 May 19 17:12 id.bin
-rw-r-----. 1 clickhouse clickhouse  48 May 19 17:12 id.mrk2
-rw-r-----. 1 clickhouse clickhouse   6 May 19 17:12 minmax_city.idx
-rw-r-----. 1 clickhouse clickhouse   3 May 19 17:12 partition.dat
-rw-r-----. 1 clickhouse clickhouse   2 May 19 17:12 primary.idx

 

 

（1）partition：分区目录，余下各类数据文件（primary.idx、 [Column].mrk、[Column].bin等）都是以分区目录的形式被组织存放 的，属于相同分区的数据，最终会被合并到同一个分区目录，而不同分 区的数据，永远不会被合并在一起。

（2）checksums.txt：校验文件，使用二进制格式存储。它保存了余下各类文件(primary.idx、count.txt等)的size大小及size的哈希值，用于快速校验文件的完整性和正确性。

（3）columns.txt：列信息文件，使用明文格式存储。用于保存此数据分区下的列字段信息，例如：

（4）count.txt：计数文件，使用明文格式存储。用于记录当前数 据分区目录下数据的总行数

（5）primary.idx：一级索引文件，使用二进制格式存储。用于存放稀疏索引，一张MergeTree表只能声明一次一级索引（通过ORDER BY 或者PRIMARY KEY）。借助稀疏索引，在数据查询的时能够排除主键条 件范围之外的数据文件，从而有效减少数据扫描范围，加速查询速度。

（6）[Column].bin：数据文件，使用压缩格式存储，默认为LZ4压缩格式，用于存储某一列的数据。由于MergeTree采用列式存储，所以每一个列字段都拥有独立的.bin数据文件，并以列字段名称命名（例如 CounterID.bin、EventDate.bin等）。

（7）[Column].mrk：列字段标记文件，使用二进制格式存储。标记文件中保存了.bin文件中数据的偏移量信息。标记文件与稀疏索引对齐，又与.bin文件一一对应，所以MergeTree通过标记文件建立了 primary.idx稀疏索引与.bin数据文件之间的映射关系。即首先通过稀疏索引（primary.idx）找到对应数据的偏移量信息（.mrk），再通过 偏移量直接从.bin文件中读取数据。由于.mrk标记文件与.bin文件一一对应，所以MergeTree中的每个列字段都会拥有与其对应的.mrk标记文件（例如CounterID.mrk、EventDate.mrk等）。

（8）[Column].mrk2：如果使用了自适应大小的索引间隔.则标记 文件会以.mrk2命名。它的工作原理和作用与.mrk标记文件相同。

（9）partition.dat与minmax_[Column].idx：如果使用了分区键，例如PARTITION BY EventTime，则会额外生成partition.dat与 minmax索引文件，它们均使用二进制格式存储。partition.dat用于保 存当前分区下分区表达式最终生成的值；而minmax索引用于记录当前分 区下分区字段对应原始数据的最小和最大值。例如EventTime字段对应 的原始数据为2019-05-01、2019-05-05，分区表达式为PARTITION BY toYYYYMM(EventTime)。partition.dat中保存的值将会是2019-05，而 minmax索引中保存的值将会是2019-05-012019-05-05。

在这些分区索引的作用下，进行数据查询时能够快速跳过不必要的 数据分区目录，从而减少最终需要扫描的数据范围。

（10）skp_idx[Column].idx与skp_idx[Column].mrk：如果在建 表语句中声明了二级索引，则会额外生成相应的二级索引与标记文件， 它们同样也使用二进制存储。二级索引在ClickHouse中又称跳数索引， 目前拥有minmax、set、ngrambf_v1和tokenbf_v1四种类型。这些索引 的最终目标与一级稀疏索引相同，都是为了进一步减少所需扫描的数据 范围，以加速整个查询过程。

 

 

2.2 ReplacingMergeTree

这个引擎是在 MergeTree 的基础上，添加了“处理重复数据”的功能，该引擎和MergeTree的不同之处在于它会删除具有相同(区内)排序一样的重复项。数据的去重只会在合并的过程中出现。合并会在未知的时间在后台进行，所以你无法预先作出计划。有一些数据可能仍未被处理。因此，ReplacingMergeTree 适用于在后台清除重复的数据以节省空间，但是它不保证没有重复的数据出现。

1 无版本参数

drop table if  exists test_replacingMergeTree1 ;
create table test_replacingMergeTree1(
    oid Int8 ,
    ctime DateTime ,
    cost Decimal(10,2)
)engine = ReplacingMergeTree()
order by oid 
partition by toDate(ctime) ;
​
-- 插入数据 
insert into test_replacingMergeTree1 values(3,'2021-01-01 11:11:11',30) ;
insert into test_replacingMergeTree1 values(1,'2021-01-01 11:11:14',40) ;
insert into test_replacingMergeTree1 values(1,'2021-01-01 11:11:11',10);
insert into test_replacingMergeTree1 values(2,'2021-01-01 11:11:11',20) ;
insert into test_replacingMergeTree1 values(1,'2021-01-02 11:11:11',41) ;
​
-- 优化合并
optimize table test_replacingMergeTree1 final ;
┌─oid─┬───────────────ctime─┬──cost─┐
│   1 │ 2021-01-02 11:11:11 │ 41.00 │
└─────┴─────────────────────┴───────┘
┌─oid─┬───────────────ctime─┬──cost─┐
│   1 │ 2021-01-01 11:11:11 │ 10.00 │
│   2 │ 2021-01-01 11:11:11 │ 20.00 │
│   3 │ 2021-01-01 11:11:11 │ 30.00 │
└─────┴─────────────────────┴───────┘
由于系统对CK的操作是多线程执行的, 所以不能保证数据插入的顺序 , 就可能出现数据删除错乱的现象
-- 主键oid  排序字段两个  验证去重规则是按主键还是排序字段
drop table if  exists test_replacingMergeTree2 ;
create table test_replacingMergeTree2(
    oid Int8 ,
    ctime DateTime ,
    cost Decimal(10,2)
)engine = ReplacingMergeTree()
primary key oid
order by (oid ,ctime)
partition by toDate(ctime) ;
​
insert into test_replacingMergeTree2 values(1,'2021-01-01 11:11:11',10) ;
insert into test_replacingMergeTree2 values(1,'2021-01-01 11:11:11',20) ;
insert into test_replacingMergeTree2 values(1,'2021-01-01 11:11:11',30);
insert into test_replacingMergeTree2 values(1,'2021-01-01 11:11:12',40) ;
insert into test_replacingMergeTree2 values(1,'2021-01-01 11:11:13',50) ;
-- 由此可见 去重并不是根据主键,而知根据区内排序相同的数据会被删除
┌─oid─┬───────────────ctime─┬──cost─┐
│   1 │ 2021-01-01 11:11:11 │ 30.00 │
│   1 │ 2021-01-01 11:11:12 │ 40.00 │
│   1 │ 2021-01-01 11:11:13 │ 50.00 │
└─────┴─────────────────────┴───────┘

　　

2 有版本参数

• 版本字段可以是数值

• 版本字段可以是时间

drop table if  exists test_replacingMergeTree3 ;
create table test_replacingMergeTree3(
    oid Int8 ,
    ctime DateTime ,
    cost Decimal(10,2)
)engine = ReplacingMergeTree(ctime)
order by oid 
partition by toDate(ctime) ;
​
insert into test_replacingMergeTree3 values(1,'2021-01-01 11:11:11',10) ;
insert into test_replacingMergeTree3 values(1,'2021-01-01 11:11:12',20) ;
insert into test_replacingMergeTree3 values(1,'2021-01-01 11:11:10',30);
insert into test_replacingMergeTree3 values(1,'2021-01-01 11:11:19',40) ;
insert into test_replacingMergeTree3 values(1,'2021-01-01 11:11:13',50) ;
-- 合并数据以后 保留的是时间最近的一条数据
┌─oid─┬───────────────ctime─┬──cost─┐
│   1 │ 2021-01-01 11:11:19 │ 40.00 │
└─────┴─────────────────────┴───────┘

　　

总结:

（1）使用ORDER BY排序键作为判断重复数据的唯一依据。

（2）只有在合并分区的时候才会触发删除重复数据的逻辑。

（3）以数据分区为单位删除重复数据。当分区合并时，同一分区内的重复数据会被删除；不同分区之间的重复数据不会被删除。

（4）在进行数据去重时，因为分区内的数据已经基于ORBER BY进行了排序，所以能够找到那些相邻的重复数据。

（5）数据去重策略有两种：

1. 如果没有设置ver版本号，则保留同一组重复数据中的最后一行。

2. 如果设置了ver版本号，则保留同一组重复数据中ver字段取值 最大的那一行。

2.3 CollapsingMergeTree

CollapsingMergeTree就是一种通过以增代删的思路，支持行级数 据修改和删除的表引擎。它通过定义一个sign标记位字段，记录数据行 的状态。如果sign标记为1，则表示这是一行有效的数据；如果sign标 记为-1，则表示这行数据需要被删除。当CollapsingMergeTree分区合 并时，同一数据分区内，sign标记为1和-1的一组数据会被抵消删除。 这种1和-1相互抵消的操作，犹如将一张瓦楞纸折叠了一般。这种直观 的比喻，想必也正是折叠合并树（CollapsingMergeTree）名称的由来，

多行的排序相同的状态为1的数据会折叠成一行

ENGINE = CollapsingMergeTree(sign)

drop table if exists tb_cps_merge_tree1 ;
CREATE TABLE tb_cps_merge_tree1
(
    user_id UInt64,
    name String,
    age UInt8,
    sign Int8
)
ENGINE = CollapsingMergeTree(sign)
ORDER BY user_id;
-- 插入数据 
insert into tb_cps_merge_tree1 values(1,'xiaoluo',23,1),(2,'xiaoyu',24,1),(3,'xiaofeng',25,1) ;
insert into tb_cps_merge_tree1 values(1,'xiaoluo_',23,-1),(2,'xiaoyu_',24,-1),(3,'xiaofeng2',25,1) ;
-- 合并优化
optimize table tb_cps_merge_tree1 ;
-- 实现了数据的删除和已经存在数据的更新
SELECT *
FROM tb_cps_merge_tree1
┌─user_id─┬─name──────┬─age─┬─sign─┐
│       3 │ xiaofeng2 │  25 │    1 │
└─────────┴───────────┴─────┴──────┘
​
CollapsingMergeTree虽然解决了主键相同的数据即时删除的问题，但是状态持续变化且多线程并行写入情况下，状态行与取消行位置可能乱序，导致无法正常折叠。只有保证老的状态行在在取消行的上面, 新的状态行在取消行的下面! 但是多线程无法保证写的顺序!
​
drop table if exists tb_cps_merge_tree2 ;
CREATE TABLE tb_cps_merge_tree2
(
    user_id UInt64,
    name String,
    age UInt8,
    sign Int8
)
ENGINE = CollapsingMergeTree(sign)
ORDER BY user_id;
insert into tb_cps_merge_tree2 values(1,'xiaoluo_',23,-1),(2,'xiaoyu_',24,-1),(3,'xiaofeng2',25,1) ;
insert into tb_cps_merge_tree2 values(1,'xiaoluo',23,1),(2,'xiaoyu',24,1),(3,'xiaofeng',25,1) ;
-- 合并优化
optimize table tb_cps_merge_tree2 ;
┌─user_id─┬─name─────┬─age─┬─sign─┐
│       1 │ xiaoluo_ │  23 │   -1 │
│       1 │ xiaoluo  │  23 │    1 │
│       2 │ xiaoyu_  │  24 │   -1 │
│       2 │ xiaoyu   │  24 │    1 │
│       3 │ xiaofeng │  25 │    1 │
└─────────┴──────────┴─────┴──────┘
假如有相同的排序数据,并且状态都是1,可以实现数据的更新 ,如果我们不能保证折叠的行在状态行的下面 ,数据无法保证可以正常删除
----查询正确的数据 
select 
tb_cps_merge_tree2.*
from
tb_cps_merge_tree2
join
(
select 
user_id ,
sum(sign) as sum_sign
from 
tb_cps_merge_tree2
group by user_id
having sum_sign = 1
 )t
 on tb_cps_merge_tree2.user_id =t.user_id ;
 
 ┌─user_id─┬─name─────┬─age─┬─sign─┐
│       3 │ xiaofeng │  25 │    1 │
└─────────┴──────────┴─────┴──────┘

　　

2.4 VersionedCollapsingMergeTree

为了解决CollapsingMergeTree乱序写入情况下无法正常折叠(删除)问题，VersionedCollapsingMergeTree表引擎在建表语句中新增了一列Version，用于在乱序情况下记录状态行与取消行的对应关系。主键(排序)相同，且Version相同、Sign相反的行，在Compaction时会被删除。与CollapsingMergeTree类似， 为了获得正确结果，业务层需要改写SQL，将count()、sum(col)分别改写为sum(Sign)、sum(col * Sign)。

drop table if exists tb_vscmt ;
CREATE TABLE tb_vscmt
(
    uid UInt64,
    name String,
    age UInt8,
    sign Int8,
    version UInt8
)
ENGINE = VersionedCollapsingMergeTree(sign, version)
ORDER BY uid;
​
INSERT INTO tb_vscmt VALUES (1001, 'ADA', 18, -1, 1);
INSERT INTO tb_vscmt VALUES (1001, 'ADA', 18, 1, 1),(101, 'DAD', 19, 1, 1),(101, 'DAD', 11, 1, 3); 
INSERT INTO tb_vscmt VALUES(101, 'DAD', 11, 1, 2) ;
-- 可以保证要删除的数据会被删除, 没有折叠标记的数据会被保留
optimize table tb_vscmt ; 
┌─uid─┬─name─┬─age─┬─sign─┬─version─┐
│ 101 │ DAD  │  19 │    1 │       1 │
│ 101 │ DAD  │  11 │    1 │       2 │
│ 101 │ DAD  │  11 │    1 │       3 │
└─────┴──────┴─────┴──────┴─────────┘
版本不一致的数据不会被折叠删除
┌──uid─┬─name─┬─age─┬─sign─┬─version─┐
│  101 │ DAD  │  19 │    1 │       1 │
│  101 │ DAD  │  11 │    1 │       2 │
│  101 │ DAD  │  11 │    1 │       3 │
│ 1001 │ ADA  │  18 │   -1 │       1 │
│ 1001 │ ADA  │  18 │    1 │       2 │
└──────┴──────┴─────┴──────┴─────────┘

　　

2.5 SummingMergeTree

假设有这样一种查询需求：终端用户只需要查询数据的汇总结果，不关心明细数据，并且数据的汇总条件是预先明确的（GROUP BY 条件明确，且不会随意改变）。

对于这样的查询场景，在ClickHouse中如何解决呢？最直接的方 案就是使用MergeTree存储数据，然后通过GROUP BY聚合查询，并利用 SUM聚合函数汇总结果。这种方案存在两个问题。

1. 存在额外的存储开销：终端用户不会查询任何明细数据，只关心汇总结果，所以不应该一直保存所有的明细数据。

2. 存在额外的查询开销：终端用户只关心汇总结果，虽然 MergeTree性能强大，但是每次查询都进行实时聚合计算也是一种性能消耗。

SummingMergeTree就是为了应对这类查询场景而生的。顾名思义，它能够在合并分区的时候按照预先定义的条件聚合汇总数据，将同一分组下的多行数据汇总合并成一行，这样既减少了数据行，又降低了后续汇总查询的开销。

提示:

ORDER BY (A、B、C、D) 
PRIMARY KEY A 
这种强制约束保障了即便在两者定义不同的情况下，主键仍然是排序键的前缀，不会出现索引与数据顺序混乱的问题。
在定义表的主键的时候,我们会考虑主键上的索引快速查找数据
ORDER BY (B、C) PRIMARY KEY A   这种是错误的!

　　

drop table summing_table ;
CREATE TABLE summing_table( 
id String, 
city String, 
sal UInt32, 
comm Float64, 
ctime DateTime 
)ENGINE = SummingMergeTree() 
PARTITION BY toDate(ctime) 
ORDER BY (id, city) 
PRIMARY KEY id ;
-- 插入素具 
insert into summing_table
values 
(1,'shanghai',10,20,'2021-06-12 01:11:12'),
(1,'shanghai',20,30,'2021-06-12 01:11:12'),
(3,'shanghai',10,20,'2021-11-12 01:11:12'),
(3,'Beijing',10,20,'2021-11-12 01:11:12') ;
​
optimize table summing_table ;
​
┌─id─┬─city─────┬─sal─┬─comm─┬───────────────ctime─┐
│ 3  │ Beijing  │  10 │   20 │ 2021-11-12 01:11:12 │
│ 3  │ shanghai │  10 │   20 │ 2021-11-12 01:11:12 │
└────┴──────────┴─────┴──────┴─────────────────────┘
┌─id─┬─city─────┬─sal─┬─comm─┬───────────────ctime─┐
│ 1  │ shanghai │  30 │   50 │ 2021-06-12 01:11:12 │
└────┴──────────┴─────┴──────┴─────────────────────┘

　　

上面的例子中没有指定sum的字段 ,那么表中符合要求的所有的数值字段都会进行求和 ,我们可以在建表的时候执行求和的字段

drop table summing_table2 ;
CREATE TABLE summing_table2( 
id String, 
city String, 
money UInt32, 
num UInt32, 
ctime DateTime 
)ENGINE = SummingMergeTree(money) 
PARTITION BY toDate(ctime) 
ORDER BY city ;
--每个城市每天的销售总额 
insert into summing_table2 values(1,'BJ',100,11,now()),
(2,'BJ',100,11,now()),
(3,'BJ',100,11,now()),
(4,'NJ',100,11,now()),
(5,'NJ',100,11,now()),
(6,'SH',100,11,now()),
(7,'BJ',100,11,'2021-05-18 11:11:11'),
(8,'BJ',100,11,'2021-05-18 11:11:11') ;
​
SELECT * 
FROM summing_table2 ;
┌─id─┬─city─┬─money─┬─num─┬───────────────ctime─┐
│ 1  │ BJ   │   300 │  11 │ 2021-05-19 21:53:49 │
│ 4  │ NJ   │   200 │  11 │ 2021-05-19 21:53:49 │
│ 6  │ SH   │   100 │  11 │ 2021-05-19 21:53:49 │
└────┴──────┴───────┴─────┴─────────────────────┘
┌─id─┬─city─┬─money─┬─num─┬───────────────ctime─┐
│ 7  │ BJ   │   200 │  11 │ 2021-05-18 11:11:11 │
└────┴──────┴───────┴─────┴─────────────────────┘
SELECT city ,money
FROM summing_table2 ;
┌─city─┬─money─┐
│ BJ   │   300 │
│ NJ   │   200 │
│ SH   │   100 │
└──────┴───────┘
┌─city─┬─money─┐
│ BJ   │   200 │
└──────┴───────┘​

支持嵌套格式的求和操作

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 ; 

　　

总结:

（1）用ORBER BY排序键作为聚合数据的条件Key。

（2）只有在合并分区的时候才会触发汇总的逻辑。

（3）以数据分区为单位来聚合数据。当分区合并时，同一数据分区内聚合Key相同的数据会被合并汇总，而不同分区之间的数据则不会被汇总。

（4）如果在定义引擎时指定了columns汇总列（非主键的数值类 型字段），则SUM汇总这些列字段；如果未指定，则聚合所有非主键的数值类型字段。

（5）在进行数据汇总时，因为分区内的数据已经基于ORBER BY排序，所以能够找到相邻且拥有相同聚合Key的数据。

（6）在汇总数据时，同一分区内，相同聚合Key的多行数据会合并成一行。其中，汇总字段会进行SUM计算；对于那些非汇总字段，则会使用第一行数据的取值。

（7）支持嵌套结构，但列字段名称必须以Map后缀结尾。嵌套类 型中，默认以第一个字段作为聚合Key。除第一个字段以外，任何名称 以Key、Id或Type为后缀结尾的字段，都将和第一个字段一起组成复合 Key。

2.5 AggregatingMergeTree

AggregatingMergeTree就有些许数据立方体的意思，它能够在合并分区的时候，按照预先定义的条件聚合数据。同时，根据预先定义的聚合函数计算数据并通过二进制的格式存入表内。将同一分组下的多行数据聚合成一行，既减少了数据行，又降低了后续聚合查询的开销。可以说，AggregatingMergeTree 是SummingMergeTree的升级版，它们的许多设计思路是一致的，例如同时定义 ORDER BY与PRIMARY KEY的原因和目的。但是在使用方法上，两者存在明显差异，应该说AggregatingMergeTree的定义方式是MergeTree家族中最为特殊的一个。

NGINE = AggregatingMergeTree() 

　　

AggregatingMergeTree没有任何额外的设置参数，在分区合并时，在每个数据分区内，会按照ORDER BY聚合。而使用何种聚合函数，以及针对哪些列字 段计算，则是通过定义AggregateFunction数据类型实现的。在insert和select时，也有独特的写法和要求：写入时需要使用-State语法，查询时使用-Merge语法。

AggregateFunction(arg1 , arg2) ;

参数一 聚合函数

参数二 数据类型

sum_cnt AggregateFunction(sum, Int64) ;

先创建原始表 ---插入数据---> 创建预先聚合表 --通过Insert的方式导入数据, 数据会按照指定的聚合函数聚合预先数据!

　

 

 

-- 1)建立明细表
CREATE TABLE detail_table
(id UInt8,
 ctime Date,
 money UInt64
) ENGINE = MergeTree() 
PARTITION BY toDate(ctime) 
ORDER BY id;

-- 2)插入明细数据
INSERT INTO detail_table VALUES(1, '2021-08-06', 100);
INSERT INTO detail_table VALUES(1, '2021-08-06', 100);
INSERT INTO detail_table VALUES(2, '2021-08-07', 200);
INSERT INTO detail_table VALUES(2, '2021-08-07', 200);

-- 3)建立预先聚合表，
-- 注意：其中UserID一列的类型为：AggregateFunction(uniq, UInt64)
CREATE TABLE agg_table
(id UInt8,
ctime Date,
money AggregateFunction(sum, UInt64)
) ENGINE = AggregatingMergeTree() 
PARTITION BY  toDate(ctime) 
ORDER BY id;

-- 4) 从明细表中读取数据，插入聚合表。
-- 注意：子查询中使用的聚合函数为 uniqState， 对应于写入语法<agg>-State
INSERT INTO agg_table
select id, ctime, uniqState(money)
from detail_table
group by id, ctime ;

-- 不能使用普通insert语句向AggregatingMergeTree中插入数据。
-- 本SQL会报错：Cannot convert UInt64 to AggregateFunction(uniq, UInt64)
INSERT INTO agg_table VALUES(1, '2020-08-06', 1);

-- 5) 从聚合表中查询。
-- 注意：select中使用的聚合函数为uniqMerge，对应于查询语法<agg>-Merge
SELECT
id, ctime ,
uniqMerge(uid) AS state 
FROM agg_table 
GROUP BY id, ctime;

使用物化视图同步聚合数据

-- 建立明细表
CREATE TABLE orders
(
    uid UInt64,
    money UInt64,
    ctime Date,
    Sign Int8
)
ENGINE = CollapsingMergeTree(Sign)
ORDER BY uid;
​
--插入数据 
insert into orders values(1,100,toDate(now()),1) ;
insert into orders values(1,100,toDate(now()),1) ;
insert into orders values(1,100,toDate(now()),1) ;
insert into orders values(2,200,toDate(now()),1) ;
insert into orders values(2,200,toDate(now()),1) ;
insert into orders values(2,200,toDate(now()),1) ;
-- 将聚合逻辑创建成物化视图
CREATE MATERIALIZED VIEW orders_agg_view
ENGINE = AggregatingMergeTree() 
PARTITION BY toDate(ctime)
ORDER BY uid 
populate
as select 
uid ,
ctime ,
sumState(money) as mm  -- 注意别名 
from 
orders
group by uid , ctime;
​
-- 查询物化视图数据
select uid,ctime,sumMerge(mm) from orders_agg_view group by uid, ctime ;
​
-- 更新明细数据, 物化视图中的数据实时计算更新
insert into orders values(1,100,toDate(now()),1);
┌─uid─┬──────ctime─┬─sumMerge(mm)─┐
│   2 │ 2021-05-19 │          400 │
│   1 │ 2021-05-19 │          200 │
└─────┴────────────┴──────────────┘
┌─uid─┬──────ctime─┬─sumMerge(mm)─┐
│   2 │ 2021-05-19 │          400 │
│   1 │ 2021-05-19 │          300 │
└─────┴────────────┴──────────────┘
┌─uid─┬──────ctime─┬─sumMerge(mm)─┐
│   2 │ 2021-05-19 │          400 │
│   1 │ 2021-05-19 │          400 │
└─────┴────────────┴──────────────┘

　　

总结:

（1）用ORBER BY排序键作为聚合数据的条件Key。

（2）使用AggregateFunction字段类型定义聚合函数的类型以及聚合的字 段。

（3）只有在合并分区的时候才会触发聚合计算的逻辑。

（4）以数据分区为单位来聚合数据。当分区合并时，同一数据分区内聚合 Key相同的数据会被合并计算，而不同分区之间的数据则不会被计算。

（5）在进行数据计算时，因为分区内的数据已经基于ORBER BY排序，所以 能够找到那些相邻且拥有相同聚合Key的数据。

（6）在聚合数据时，同一分区内，相同聚合Key的多行数据会合并成一 行。对于那些非主键、非AggregateFunction类型字段，则会使用第一行数据的 取值。

（7）AggregateFunction类型的字段使用二进制存储，在写入数据时，需 要调用State函数；而在查询数据时，则需要调用相应的Merge函数。其中，* 表示定义时使用的聚合函数。

（8）AggregatingMergeTree通常作为物化视图的表引擎，与普通 MergeTree搭配使用。

 

该查询尝试使用[MergeTree]系列中的表引擎初始化表的未计划的数据部分合并。[MaterializedView和[Buffer]引擎OPTMIZE也支持。不支持其他表引擎。

当OPTIMIZE与使用[ReplicatedMergeTree]表引擎，ClickHouse创造了合并，并等待所有节点上执行（如果该任务replication_alter_partitions_sync已启用设置）。

· 如果OPTIMIZE由于任何原因未执行合并，则不会通知客户端。要启用通知，请使用[optimize_throw_if_noop]设置。

· 如果指定PARTITION，则仅优化指定的分区。[如何设置分区表达式]。

· 如果指定FINAL，即使所有数据已经在一个部分中，也会执行优化。

· 如果指定DEDUPLICATE，则将对完全相同的行进行重复数据删除（比较所有列），这仅对MergeTree引擎有意义。

 

3 外部存储引擎

3.1 HDFS引擎

Clickhouse可以直接从HDFS中指定的目录下加载数据 , 自己根本不存储数据, 仅仅是读取数据

ENGINE = HDFS(hdfs_uri,format) 
·hdfs_uri表示HDFS的文件存储路径； 
·format表示文件格式（指ClickHouse支持的文件格式，常见的有 CSV、TSV和JSON等）。

我们一般期望的是数据有其他方式写入到HDFS系统中, 使用CK的HDFS引擎加载处理分析数据.

这种形式类似Hive的外挂表，由其他系统直接将文件写入HDFS。通过HDFS表引擎的hdfs_uri和format参数分别与HDFS的文件路径、文件格式建立映射。其中，hdfs_uri支持以下几种常见的配置方法：

• 绝对路径：会读取指定路径的单个文件，例如/clickhouse/hdfs_table1。

• 通配符：匹配所有字符，例如路径为/clickhouse/hdfs_table/，则会读取/click-house/hdfs_table路径下的所有文件。

• ?通配符：匹配单个字符，例如路径为/clickhouse/hdfs_table/organization?.csv，则会读取/clickhouse/hdfs_table路径下与organization?.csv匹配的文件，其中?代表任意一个合法字符。

• {M..N}数字区间：匹配指定数字的文件，例如路径为/clickhouse/hdfs_table/organization_{1..3}.csv，则会读取/clickhouse/hdfs_table/路径下的文件organization_1.csv、organization_2.csv和organization_3.csv。

create table test_hdfs1(
id Int8 ,
name String ,
age Int8
)engine=HDFS('hdfs://linux01:8020/ck/test1/*' ,CSV) ;
​
创建文件,将文件上传到指定的目录下
1.txt
1,zss,21
2,ww,22
​
2.txt 
3,lss,33
4,qaa,32
​
3.txt 
5,as,31
6,ghf,45
​
--匹配单个字符 
create table test_hdfs2(
id Int8 ,
name String ,
age Int8
)engine=HDFS('hdfs://linux01:8020/ck/test1/?.txt' ,CSV) ;
​
-- 匹配数字之间的文件
create table test_hdfs3(
id Int8 ,
name String ,
age Int8
)engine=HDFS('hdfs://linux01:8020/ck/test1/a_{1..2}.txt' ,CSV) ;

　　

3.2 mysql引擎

MySQL表引擎可以与MySQL数据库中的数据表建立映射，并通过SQL向其发起远程查询， 包括SELECT和INSERT

它的声明方式如下：

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

·on_duplicate_clause默认为0，对应MySQL的ON DUPLICATE KEY语法。如果需要使用该设置，则必须将replace_query设置成0。

那么在正式使用MySQL引擎之前首先当前机器要有操作MySQL数据的权限 ,开放MySQL的远程连连接权限操作如下:

1)  set global validate_password_policy=0;
2)  set global validate_password_length=1;   这个两个设置以后 密码很简单不会报错
3)  grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option;
4)  flush privileges;

　　

-- 在mysql中建表 
create table tb_x(id int, name varchar(25), age int) ;
insert into tb_x values(1,'zss',23),(2,'lss',33) ;
-- 在clickhouse中建表 
CREATE TABLE tb_mysql
(
    `id` Int8,
    `name` String,
    `age` Int8
)
ENGINE = MySQL('doit01:3306', 'test1', 'tb_x', 'root', 'root');
-- 查看数据
-- 插入数据
insert into tb_mysql values(3,'ww',44) ;

​

• 支持查询数据

• 支持插入数据

• 不支持删除和更新操作

3.3 File引擎

File表引擎能够直接读取本地文件的数据，通常被作为一种扩充手段来使用。例如：它可以读取由其他系统生成的数据文件，如果外部系统直接修改了文件，则变相达到了数据更新的目的；它可以将 ClickHouse数据导出为本地文件；它还可以用于数据格式转换等场景。除此以外，File表引擎也被应用于clickhouse-local工具

ENGINE = File(format)

　　

drop table if exists test_file1 ;
create table test_file1(
id String ,
name String ,
age UInt8
)engine=File("CSV") ;

　　

在默认的目录下回生成一个文件夹 , 文件夹中可以写入文件 ,但是文件的名字必须是data.CSV

insert into  test_file1 values('u001','hangge',33) ; 

　　

file表函数

-- 去指定的路径下加载本地的数据

select * from file('/ck/user.txt','CSV','id Int8 , name String ,gender String,age UInt8') ;

默认加载的是特定的文件夹,数据一定要在指定的文件夹下才会被加载

修改默认的数据加载的文件夹

vi /etc/clickhouse-server/config.xml 
/path  n下一个 
    <!-- Directory with user provided files that are accessible by 'file' table function. -->
    <user_files_path>/</user_files_path>

重启服务

service clickhouse-server restart 

　　

 

3.4 MySQL 数据库引擎

语法

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

　　

create database db_ck_mysql engine=MySQL('doit01:3306','test1','root','root') ;

　　

常用于数据的合并 , 加载mysql中的数据和ck中的数据合并 , 不做数据的修改和建表

4 内存引擎

接下来将要介绍的几款表引擎，都是面向内存查询的，数据会从内存中被直接访问，所以它们被归纳为内存类型。但这并不意味着内存类表引擎不支持物理存储，事实上，除了Memory表引擎之外，其余的几款表引擎都会将数据写入磁盘，这是为了防止数据丢失，是一种故障恢复手段。而在数据表被加载时，它们会将数据全部加载至内存，以供查询之用。将数据全量放在内存中，对于表引擎来说是一把双刃剑：一方面，这意味着拥有较好的查询性能；而另一方面，如果表内装载的数据量过大，可能会带来极大的内存消耗和负担!

4.1 Memory

Memory表引擎直接将数据保存在内存中，数据既不会被压缩也不会被格式转换，数据在内存中保存的形态与查询时看到的如出一辙。 正因为如此，当ClickHouse服务重启的时候，Memory表内的数据会全部丢失。所以在一些场合，会将Memory作为测试表使用，很多初学者在学习ClickHouse的时候所写的Hello World程序很可能用的就是Memory表。由于不需要磁盘读取、序列化以及反序列等操作，所以Memory表引擎支持并行查询，并且在简单的查询场景中能够达到与MergeTree旗鼓相当的查询性能（一亿行数据量以内）。Memory表的创建方法如下所示:

CREATE TABLE memory_1 ( 
id UInt64 
)ENGINE = Memory() ;

　　

Memory表更为广 泛的应用场景是在ClickHouse的内部，它会作为集群间分发数据的存储载体来使用。例如在分布式IN查询的场合中，会利用Memory临时表保存IN子句的查询结果，并通过网络将它传输到远端节点。

4.2 Set

Set表引擎是拥有物理存储的，数据首先会被写至内存，然后被同步到磁盘文件中。所以当服务重启时，它的数据不会丢失，当数据表被重新装载时，文件数据会再次被全量加载至内存。众所周知，在Set

数据结构中，所有元素都是唯一的。Set表引擎具有去重的能力，在数据写入的过程中，重复的数据会被自动忽略。然而Set表引擎的使用场景既特殊又有限，它虽然支持正常的INSERT写入，但并不能直接使用SELECT对其进行查询，Set表引擎只能间接作为IN查询的右侧条件被查询使用

Set表引擎的存储结构由两部分组成，它们分别是：

[num].bin数据文件：保存了所有列字段的数据。其中，num是 一个自增id，从1开始。伴随着每一批数据的写入（每一次INSERT），都会生成一个新的.bin文件，num也会随之加1。

tmp临时目录：数据文件首先会被写到这个目录，当一批数据写入完毕之后，数据文件会被移出此目录。

create table test_set(
id Int8 ,
name String
)engine=Set();

发现在数据库的目录下是还有对应的目录的,可见数据会被存储到磁盘上的