spark 写入 Ignore spark 写入clickhouse array类型

文章目录

• 三、ClickHouse基础入门

• 1.数据类型

• 1.1 数值类型

• 1.1.1 IntX和UIntX
• 1.1.2 FloatX
• 1.1.3 Decimal

• 1.2 字符串类型

• 1.2.1 String
• 1.2.2 FixedString
• 1.2.3 UUID

• 1.3 时间类型

• 1.3.1 Date
• 1.3.2 DateTime
• 1.2.3 DateTime64

• 1.4 复杂类型

• 1.4.1 Enum
• 1.4.2 Array(T)
• 1.4.3 Tuple
• 1.4.4 Nested
• 1.4.5 Map
• 1.4.6 GEO
• 1.4.7 IPV4
• 1.4.8 Boolean和Nullable

• 2.基本语法

• 2.1 DDL基础
• 2.2 DML基础

• 2.2.1 插入数据
• 2.2.2 更新删除数据

• 2.3 分区表操作
• 2.4 视图

• 2.4.1 普通视图
• 2.4.2 物化视图

• 四、引擎详解

• 1.Log系列引擎

• 1.1 TinyLog引擎
• 1.2 StripeLog引擎
• 1.3 Log引擎

• 2.MergeTree系列引擎

• 2.1 MergeTree引擎

• 2.1.1 创建表
• 2.1.2 导入数据
• 2.1.3 合并数据
• 2.1.4 数据存储原理

• 2.2 ReplacingMergeTree

• 2.2.1 无版本参数
• 2.2.2 有版本参数
• 2.2.3 总结

• 2.3 CollapsingMergeTree
• 2.4 VersionedCollapsingMergeTree
• 2.5 SummingMergeTree
• 2.6 AggregatingMergeTree

• 3.外部存储引擎

• 3.1 HDFS引擎
• 3.2 MySQL引擎
• 3.3 File引擎
• 3.4 MySQL 数据库引擎

• 4.内存引擎

• 4.1 Memory
• 4.2 Set
• 4.3 Buffer

三、ClickHouse基础入门

1.数据类型

注意在CK中关键字严格区分大小写

ck中建表的时候 ,一定指定表引擎!

create table tb_test1(
id Int8 ,
name String
)engine=Memory;
┌─name─┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id   │ Int8   │              │                    │         │                  │                │
│ name │ String │              │                    │         │                  │                │
└──────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

1.1 数值类型

1.1.1 IntX和UIntX

以前我们常用Tinyint、Smallint、Int和Bigint指代整数的不同取值范围。而ClickHouse则直接使用Int8、Int16、Int32和Int64指代4种大小的Int类型，其末尾的数字正好表明了占用字节的大小（8位=1字节）

ClickHouse支持无符号的整数，使用前缀U表示

create table test_int(
    id Int8 ,
    age UInt8 ,
    cdId Int32
)engine=Memory ;

1.1.2 FloatX

注意: 和我以前的认知是一样的,这种数据类型在数据特别精准的情况下可能出现数据精度问题!

Select 8.0/0 -->inf 正无穷

Select -8.0/0 -->inf 负无穷

Select 0/0 -->nan 非数字

1.1.3 Decimal

如果要求更高精度的数值运算，则需要使用定点数。ClickHouse提 供了Decimal32、Decimal64和Decimal128三种精度的定点数。可以通过 两种形式声明定点：简写方式有Decimal32(S)、Decimal64(S)、

Decimal128(S)三种，原生方式为Decimal(P,S)，其中：

• P代表精度，决定总位数（整数部分+小数部分），取值范围是1 ～38；·S代表规模，决定小数位数，取值范围是0～P

在使用两个不同精度的定点数进行四则运算的时候，它们的小数点 位数S会发生变化。

在进行加法运算时，S取最大值。例如下面的查询，toDecimal64(2,4)与toDecimal32(2,2)相加后S=4：

在进行减法运算时，S取最大值。例如下面的查询，toDecimal64(2,4)与toDecimal32(2,2)相减后S=4：

在进行乘法运算时，S取最和。例如下面的查询，toDecimal64(2,4)与toDecimal32(2,2)相乘后S=4+2：

在进行除法运算时，S取最大值。例如下面的查询，toDecimal64(2,4)与toDecimal32(2,2)相除后S=4：但是要保证被除数的S大于除数的S，否则会报错

create table test_decimal(
id Int8 ,
    sal Decimal(5,2)
)engine=Memory ;

1.2 字符串类型

符串类型可以细分为String、FixedString和UUID三类。从命名来看仿佛不像是由一款数据库提供的类型，反而更像是一门编程语言的设计,没错CK语法具备编程语言的特征(数据+运算)

1.2.1 String

字符串由String定义，长度不限。因此在使用String的时候无须声明大小。它完全代替了传统意义上数据库的Varchar、Text、Clob和Blob等字符类型。String类型不限定字符集，因为它根本就没有这个概念，所以可以将任意编码的字符串存入其中。但是为了程序的规范性和可维护性，在同一套程序中应该遵循使用统一的编码，例如“统一保持UTF-8编码”就是一种很好的约定。所以在对数据操作的时候我们不在需要区关注编码和乱码问题!

1.2.2 FixedString

FixedString类型和传统意义上的Char类型有些类似，对于一些字符有明确长度的场合，可以使用固定长度的字符串。定长字符串通过FixedString(N)声明，其中N表示字符串长度。但与Char不同的是，

FixedString使用null字节填充末尾字符，而Char通常使用空格填充。比如在下面的例子中，字符串‘abc’虽然只有3位，但长度却是5，因为末尾有2位空字符填充 !

create table test_str(
name String ,
    job FixedString(4)   -- 最长4个字符
)engine=Memory ;

1.2.3 UUID

UUID是一种数据库常见的主键类型，在ClickHouse中直接把它作为一种数据类型。UUID共有32位，它的格式为8-4-4-4-12。如果一个UUID类型的字段在写入数据时没有被赋值，则会依照格式使用0填充

CREATE TABLE test_uuid
(
    `uid` UUID,
    `name` String
)
ENGINE = Memory ;
DESCRIBE TABLE test_uuid
 
┌─name─┬─type───┬
│ uid  │ UUID   │
│ name │ String │
└──────┴────────┴
insert into test_uuid select generateUUIDv4() , 'zss' ;
select * from test_uuid ;
┌──────────────────────────────────uid─┬─name─┐
│ 47e39e22-d2d6-46fd-8014-7cd3321f4c7b │ zss  │
└──────────────────────────────────────┴──────┘
 
-------------------------UUID类型的字段默认补位0-----------------------------
insert into test_uuid (name) values('hangge') ;
┌──────────────────────────────────uid─┬─name─┐
│ 47e39e22-d2d6-46fd-8014-7cd3321f4c7b │ zss  │
└──────────────────────────────────────┴──────┘
┌──────────────────────────────────uid─┬─name───┐
│ 00000000-0000-0000-0000-000000000000 │ hangge │
└──────────────────────────────────────┴────────┘

1.3 时间类型

1.3.1 Date

Date类型不包含具体的时间信息，只精确到天，支持字符串形式写入：

CREATE TABLE test_date
(
    `id` int,
    `ct` Date
)
ENGINE = Memory ;
DESCRIBE TABLE test_date  ;
┌─name─┬─type──┬
│ id   │ Int32 │
│ ct   │ Date  │
└──────┴───────┴
insert into test_date vlaues(1,'2021-09-11'),(2,now()) ;
select id , ct from test_date ;
 
┌─id─┬─────────ct─┐
│  1 │ 2021-09-11 │
│  2 │ 2021-05-17 │
└────┴────────────┘

1.3.2 DateTime

DateTime类型包含时、分、秒信息，精确到秒，支持字符串形式写入：

create table testDataTime(ctime DateTime) engine=Memory ;
insert into testDataTime values('2021-12-27 01:11:12'),(now()) ;
select * from testDataTime ;

1.2.3 DateTime64

DateTime64可以记录亚秒，它在DateTime之上增加了精度的设置

-- 建表
CREATE TABLE test_date_time64
(
    `ctime` DateTime64
)
ENGINE = Memory ;
-- 建表
CREATE TABLE test_date_time64_2
(
    `ctime` DateTime64(2)
)
ENGINE = Memory ;
-- 分别插入数据
insert into test_date_time64 values('2021-11-11 11:11:11'),(now()) ;
insert into test_date_time64_2 values('2021-11-11 11:11:11'),(now()) ;
-- 查询数据
SELECT *
FROM test_date_time64;
┌───────────────────ctime─┐
│ 2021-11-11 11:11:11.000 │
│ 2021-05-17 10:40:51.000 │
└─────────────────────────┘
SELECT
    *, toTypeName(ctime)
FROM test_date_time64
 
┌───────────────────ctime─┬─toTypeName(ctime)─┐
│ 2021-11-11 11:11:11.000 │ DateTime64(3)     │
│ 2021-05-17 10:40:51.000 │ DateTime64(3)     │
------------------------------------------------
SELECT
    *, toTypeName(ctime)
FROM test_date_time64_2
 
┌──────────────────ctime─┬─toTypeName(ctime)─┐
│ 2021-11-11 11:11:11.00 │ DateTime64(2)     │
│ 2021-05-17 10:41:26.00 │ DateTime64(2)     │
└────────────────────────┴───────────────────┘

1.4 复杂类型

1.4.1 Enum

ClickHouse支持枚举类型，这是一种在定义常量时经常会使用的数据类型。ClickHouse提供了Enum8和Enum16两种枚举类型，它们除了取值范围不同之外，别无二致。枚举固定使用(String:Int)Key/Value键值对的形式定义数据，所以Enum8和Enum16分别会对应(String:Int8)和(String:Int16)!

create table test_enum(id Int8 , color Enum('red'=1 , 'green'=2 , 'blue'=3)) engine=Memory ;
insert into  test_enum values(1,'red'),(1,'red'),(2,'green');
也可以使用这种方式进行插入数据:
insert into test_enum values(3,3) ;

在定义枚举集合的时候，有几点需要注意。首先，Key和Value是不允许重复的，要保证唯一性。其次，Key和Value的值都不能为Null，但Key允许是空字符串。在写入枚举数据的时候，只会用到Key字符串部分，

注意: 其实我们可以使用字符串来替代Enum类型来存储数据,那么为什么是要使用枚举类型呢？这是出于性能的考虑。因为虽然枚举定义中的Key属于String类型，但是在后续对枚举的所有操作中（包括排序、分组、去重、过滤等），会使用Int类型的Value值 ,提高处理数据的效率!

• 限制枚举类型字段的值
• 底层存储的是对应的Int类型的数据
• 可以使用String

1.4.2 Array(T)

CK支持数组这种复合数据类型 , 并且数据在操作在今后的数据分析中起到非常便利的效果!数组的定义方式有两种 : array(T) [e1,e2…] , 我们在这里要求数组中的数据类型是一致的!

数组的定义
[1,2,3,4,5]
array('a' , 'b' , 'c')
[1,2,3,'hello']   -- 错误
create table test_array(
id Int8 ,
hobby Array(String)
)engine=Memory ;
insert into test_array values(1,['eat','drink','la']),(2,array('sleep','palyg','sql'));
┌─id─┬─hobby───────────────────┐
│  1 │ ['eat','drink','la']    │
│  2 │ ['sleep','palyg','sql'] │
└────┴─────────────────────────┘
select id , hobby  , toTypeName(hobby) from test_array ;
┌─id─┬─hobby───────────────────┬─toTypeName(hobby)─┐
│  1 │ ['eat','drink','la']    │ Array(String)     │
│  2 │ ['sleep','palyg','sql'] │ Array(String)     │
└────┴─────────────────────────┴───────────────────┘
 select id , hobby[2]  , toTypeName(hobby) from test_array ; -- 数组的取值 [index]  1-based

1.4.3 Tuple

元组类型由1～n个元素组成，每个元素之间允许设置不同的数据类型，且彼此之间不要求兼容。元组同样支持类型推断，其推断依据仍然以最小存储代价为原则。与数组类似，元组也可以使用两种方式定义，常规方式tuple(T)：元组中可以存储多种数据类型,但是要注意数据类型的顺序

select tuple(1,'asb',12.23) as x , toTypeName(x) ;
┌─x───────────────┬─toTypeName(tuple(1, 'asb', 12.23))─┐
│ (1,'asb',12.23) │ Tuple(UInt8, String, Float64)      │
└─────────────────┴────────────────────────────────────┘
---简写形式
SELECT
    (1, 'asb', 12.23) AS x,
    toTypeName(x)
 
┌─x───────────────┬─toTypeName(tuple(1, 'asb', 12.23))─┐
│ (1,'asb',12.23) │ Tuple(UInt8, String, Float64)      │
└─────────────────┴────────────────────────────────────┘
注意:建表的时候使用元组的需要制定元组的数据类型
CREATE TABLE test_tuple (
c1 Tuple(UInt8, String, Float64)
) ENGINE = Memory;
 
(1,2,3,'abc')
tuple(1,2,3,'abc')
col  Tuple(Int8,Int8,String)    -- 定义泛型

1.4.4 Nested

Nested是一种嵌套表结构。一张数据表，可以定义任意多个嵌套类型字段，但每个字段的嵌套层级只支持一级，即嵌套表内不能继续使用嵌套类型。对于简单场景的层级关系或关联关系，使用嵌套类型也是一种不错的选择。

create table test_nested(
    uid Int8 ,
    name String ,
    props Nested(
        pid Int8,
        pnames String ,
        pvalues String
    )
)engine = Memory ;
desc test_nested ;
┌─name──────────┬─type──────────┬
│ uid           │ Int8          │
│ name          │ String        │
│ props.pid     │ Array(Int8)   │
│ props.pnames  │ Array(String) │
│ props.pvalues │ Array(String) │
└───────────────┴───────────────┴

嵌套类型本质是一种多维数组的结构。嵌套表中的每个字段都是一个数组，并且行与行之间数组的长度无须对齐。需要注意的是，在同一行数据内每个数组字段的长度必须相等。

insert into test_nested values(1,'hadoop',[1,2,3],['p1','p2','p3'],['v1','v2','v3']);
-- 行和行之间的属性的个数可以不一致 ,但是当前行的Nested类型中的数组个数必须一致
insert into test_nested values(2,'spark',[1,2],['p1','p2'],['v1','v2']);
SELECT *
FROM test_nested
 
┌─uid─┬─name───┬─props.pid─┬─props.pnames─────┬─props.pvalues────┐
│   1 │ hadoop │ [1,2,3]   │ ['p1','p2','p3'] │ ['v1','v2','v3'] │
└─────┴────────┴───────────┴──────────────────┴──────────────────┘
┌─uid─┬─name──┬─props.pid─┬─props.pnames─┬─props.pvalues─┐
│   2 │ spark │ [1,2]     │ ['p1','p2']  │ ['v1','v2']   │
└─────┴───────┴───────────┴──────────────┴───────────────┘
SELECT
    uid,
    name,
    props.pid,
    props.pnames[1]
FROM test_nested;
┌─uid─┬─name───┬─props.pid─┬─arrayElement(props.pnames, 1)─┐
│   1 │ hadoop │ [1,2,3]   │ p1                            │
└─────┴────────┴───────────┴───────────────────────────────┘
┌─uid─┬─name──┬─props.pid─┬─arrayElement(props.pnames, 1)─┐
│   2 │ spark │ [1,2]     │ p1                            │
└─────┴───────┴───────────┴───────────────────────────────┘

create table test_nested(
id Int8 ,
name String  ,
scores Nested(
       seq  UInt8 ,
       sx Float64 ,
       yy Float64 ,
       yw Float64
       )
)engine = Memory ;
 
insert into test_nested values (1,'wbb',[1,2,3],[11,12,13],[14,14,11],[77,79,10]);
insert into test_nested values (2,'taoge',[1,2],[99,10],[14,40],[77,11]);
-- 注意 每行中的数组的个数一致  行和行之间可以不一直被
 
┌─id─┬─name─┬─scores.seq─┬─scores.sx──┬─scores.yy──┬─scores.yw──┐
│  1 │ wbb  │ [1,2,3]    │ [11,12,13] │ [14,14,11] │ [77,79,10] │
└────┴──────┴────────────┴────────────┴────────────┴────────────┘
┌─id─┬─name──┬─scores.seq─┬─scores.sx─┬─scores.yy─┬─scores.yw─┐
│  2 │ taoge │ [1,2]      │ [99,10]   │ [14,40]   │ [77,11]   │
└────┴───────┴────────────┴───────────┴───────────┴───────────┘
SELECT
    name,
    scores.sx
FROM test_nested;
┌─name─┬─scores.sx──┐
│ wbb  │ [11,12,13] │
└──────┴────────────┘
┌─name──┬─scores.sx─┐
│ taoge │ [99,10]   │
└───────┴───────────┘

1.4.5 Map

需要在21.3+版本才支持

https://repo.yandex.ru/clickhouse/rpm/testing/x86_64/ 下载新版本的CK安装包

https://github.com/ClickHouse/ClickHouse/pull/15806 中描述了添加Map类型的issue

而且该功能为实验性功能，需要设置参数开启该功能

SET allow_experimental_map_type=1;

1.4.6 GEO

• Point

SET allow_experimental_geo_types = 1;
CREATE TABLE geo_point (p Point) ENGINE = Memory();
INSERT INTO geo_point VALUES((10, 10));
SELECT p, toTypeName(p) FROM geo_point;
┌─p───────┬─toTypeName(p)─┐
│ (10,10) │ Point         │
└─────────┴───────────────┘

• Ring

SET allow_experimental_geo_types = 1;
CREATE TABLE geo_ring (r Ring) ENGINE = Memory();
INSERT INTO geo_ring VALUES([(0, 0), (10, 0), (10, 10), (0, 10)]);
SELECT r, toTypeName(r) FROM geo_ring;
 
┌─r─────────────────────────────┬─toTypeName(r)─┐
│ [(0,0),(10,0),(10,10),(0,10)] │ Ring          │
└───────────────────────────────┴───────────────┘

• Polygon

SET allow_experimental_geo_types = 1;
CREATE TABLE geo_polygon (pg Polygon) ENGINE = Memory();
INSERT INTO geo_polygon VALUES([[(20, 20), (50, 20), (50, 50), (20, 50)], [(30, 30), (50, 50), (50, 30)]]);
SELECT pg, toTypeName(pg) FROM geo_polygon;

• MultiPolygon

SET allow_experimental_geo_types = 1;
CREATE TABLE geo_multipolygon (mpg MultiPolygon) ENGINE = Memory();
INSERT INTO geo_multipolygon VALUES([[[(0, 0), (10, 0), (10, 10), (0, 10)]], [[(20, 20), (50, 20), (50, 50), (20, 50)],[(30, 30), (50, 50), (50, 30)]]]);
SELECT mpg, toTypeName(mpg) FROM geo_multipolygon;

1.4.7 IPV4

域名类型分为IPv4和IPv6两类，本质上它们是对整型和字符串的进一步封装。IPv4类型是基于UInt32封装的

（1）出于便捷性的考量，例如IPv4类型支持格式检查，格式错误的IP数据是无法被写入的，例如：

INSERT INTO IP4_TEST VALUES (‘www.51doit.com’,‘192.0.0’)

Code: 441. DB::Exception: Invalid IPv4 value.

（2）出于性能的考量，同样以IPv4为例，IPv4使用UInt32存储，相比String更加紧凑，占用的空间更小，查询性能更快。IPv6类型是基于FixedString(16)封装的，它的使用方法与IPv4别无二致, 在使用Domain类型的时候还有一点需要注意，虽然它从表象上看起来与String一样，但Domain类型并不是字符串，所以它不支持隐式的自动类型转换。如果需要返回IP的字符串形式，则需要显式调用 IPv4NumToString或IPv6NumToString函数进行转换。

create table test_domain(
id Int8 ,
ip IPv4
)engine=Memory ;
insert  into test_domain values(1,'192.168.133.2') ;
insert  into test_domain values(1,'192.168.133') ; 在插入数据的会进行数据的检查所以这行数据会报错
-- Exception on client:
-- Code: 441. DB::Exception: Invalid IPv4 value.
-- Connecting to database doit1 at localhost:9000 as user default.
-- Connected to ClickHouse server version 20.8.3 revision 54438.

1.4.8 Boolean和Nullable

ck中没有Boolean类型 ,使用1和0来代表true和false

Nullable 某种数据类型允许为null , 或者是没有给值的情况下模式是NULL

create table test_null(
id  Int8 ,
age Int8
)engine = Memory ;
 
create table test_null2(
id  Int8 ,
age Nullable(Int8)
)engine = Memory ;

2.基本语法

2.1 DDL基础

• 建表

目前只有MergeTree、Merge和Distributed这三类表引擎支持 ALTER查询,所以在进行alter操作的时候注意表的引擎!

注意在建表的时候一般要求指定表的引擎

CREATE TABLE tb_test1
(
    `id` Int8,
    `name` String
)
ENGINE = Memory() ;
-- 只有 MergeTree支持表结构的修改
-- MergeTree一定指定主键和排序字段  order by 代表两个含义
CREATE TABLE test_alter1
(
    `id` Int8,
    `name` String
)
ENGINE = MergeTree()
order by id ;
-- 查看建表语句  查看引擎类型参数值
show  create table test_alter1 ;
-----------------------------------
CREATE TABLE doit23.test_alter1
(
    `id` Int8,
    `name` String
)
ENGINE = MergeTree()
ORDER BY id
SETTINGS index_granularity = 8192;

• 修改表结构

-- 查看表结构
desc tb_test1 ;
┌─name─┬─type───┬
│ id   │ Int8   │
│ name │ String │
└──────┴────────┴
-- 添加字段
alter table tb_test1 add column age UInt8 ;-- 报错 , 因为修改的表引擎是内存引擎,不支持表结构的修改
-- 创建一张MergeTree引擎的表
CREATE TABLE tb_test2
(
    `id` Int8,
    `name` String
)
ENGINE = MergeTree()
ORDER BY id ;
┌─name─┬─type───┬
│ id   │ Int8   │
│ name │ String │
└──────┴────────┴
-- 添加字段
alter table tb_test2 add column age UInt8 ;
┌─name─┬─type───┬
│ id   │ Int8   │
│ name │ String │
│ age  │ UInt8  │
└──────┴────────┴
alter table tb_test2 add column gender String after name ;
┌─name───┬─type───┬
│ id     │ Int8   │
│ name   │ String │
│ gender │ String │
│ age    │ UInt8  │
└────────┴────────┴
-- 删除字段
alter table tb_test2 drop column age ;
-- 修改字段的数据类型
alter  table  tb_test2 modify column  gender UInt8 default 0 ;
┌─name───┬─type───┬─default_type─┬─default_expression─┬
│ id     │ Int8   │              │                    │
│ name   │ String │              │                    │
│ gender │ UInt8  │ DEFAULT      │ 0                  │
└────────┴────────┴──────────────┴────────────────────┴
-- 作为一个优秀的程序员,表的字段使用注释一种良好的习惯, 所以建议大家在操作的时候使用注释来描述字段的意义
-- 修改 / 添加字段的注释  内部使用的编码默认是UTF8
alter table tb_test2 comment column name '用户名' ;
┌─name───┬─type───┬─default_type─┬─default_expression─┬─comment─┬
│ id     │ Int8   │              │                    │         │
│ name   │ String │              │                    │ 用户名  │
│ gender │ UInt8  │ DEFAULT      │ 0                  │         │
└────────┴────────┴──────────────┴────────────────────┴─────────┴

• 移动表

在Linux系统中，mv命令的本意是将一个文件从原始位置A移动到目标位置B，但是如果位 置A与位置B相同，则可以变相实现重命名的作用。ClickHouse的RENAME查询就与之有着异曲同工之妙，RENAME语句的完整语法如下所示：

-- 修改表名
rename table tb_test1 to t1 ;
-- 修改多张表名
rename table tb_test2 to t2 , t1 to tt1 ;
-- 移动表到另一数据库中
rename table t2 to test1.t ;
-- 查看数据库下的所有的表
show tables ;
show tables from db_name ;

• 设置表属性

-- 设置列的默认值
create table tb_test3(
   id Int8 ,
    name String comment '用户名' ,
    role String comment '角色' default 'VIP'
)engine = Log ;
┌─name─┬─type───┬─default_type─┬─default_expression─┬
│ id   │ Int8   │              │                    │
│ name │ String │              │                    │
│ role │ String │ DEFAULT      │ 'VIP'              │
└──────┴────────┴──────────────┴────────────────────┴
insert into tb_test3 (id , name) values(1,'HANGGE') ;
SELECT *
FROM tb_test3 ;
┌─id─┬─name───┬─role─┐
│  1 │ HANGGE │ VIP  │
└────┴────────┴──────┘

2.2 DML基础

2.2.1 插入数据

INSERT语句支持三种语法范式，三种范式各有不同，可以根据写入的需求灵活运用。

① 第一种方式

使用VALUES格式的常规语法

INSERT INTO [db.]table [(c1, c2, c3…)] VALUES (v11, v12, v13…), (v21, v22, v23…), ...

其中，c1、c2、c3是列字段声明，可省略。VALUES后紧跟的是由元组组成的待写入数据，通过下标位 与列字段声明一一对应。数据支持批量声明写入，多行数据之间使用逗号分隔

② 第二种方式

-- 静态数据: cat user.txt
-- 1,zss,23,BJ,M
-- -- 2,lss,33,NJ,M
-- 3,ww,21,SH,F
create table test_load1(
    id UInt8 ,
    name String ,
    age UInt8 ,
    city String ,
    gender String
)engine=Log ;
-- 将数据导入到表中
cat user.txt  | clickhouse-client  -q 'insert into default.test_load1 format CSV'
clickhouse-client  -q 'insert into default.test_load1 format CSV'  <  user.txt
上面的两种方式都可以将数据导入到表中
-- 我们还可以执行数据行属性的分割符
clickhouse-client --format_csv_delimiter=',' -q 'insert into default.test_load1 format CSV'  <   user.txt

③ 第三种方式

INSERT INTO [db.]table [(c1, c2, c3…)] SELECT ...

虽然VALUES和SELECT子句的形式都支持声明表达式或函数，但是表达式和函数会带来额外的性能开销，从而导致写入性能的下降。所以如果追求极致的写入性能，就应该尽可能避免使用它们。

create table log3 as log2 ;
Insert into log3 select * from log2 ;

ClickHouse内部所有的数据操作都是面向Block数据块的，所以INSERT查询最终会将数据转换为Block数据块。也正因如此，INSERT语句在单个数据块的写入过程中是具有原子性的。在默认的情况下，每个数据块最多可以写入1048576行数据（由max_insert_block_size参数控制）。也就是说，如果一条INSERT语句写入的数据少于max_insert_block_size行，那么这批数据的写入是具有原子性的，即要么全部成功，要么全部失败。需要注意的是，只有在ClickHouse服务端处理数据的时候才具有这种原子写入的特性，例如使用JDBC或者HTTP接口时。因为max_insert_block_size参数在使用CLI命令行或 者INSERT SELECT子句写入时是不生效的。

2.2.2 更新删除数据

ClickHouse提供了DELETE和UPDATE的能力，这类操作被称为Mutation查询，它可以看作ALTER语句的变种。虽然Mutation能最终实现修改和删除，但不能完全以通常意义上的UPDATE和DELETE来理解，我们必须清醒地认识到它的不同：首先，Mutation语句是一种“很重”的操作，更适用于批量数据的修改和删除；其次，它不支持事务，一旦语句被提交执行，就会立刻对现有数据产生影响，无法回滚；最后， Mutation语句的执行是一个异步的后台过程，语句被提交之后就会立即返回。所以这并不代表具体逻辑已经执行完毕，它的具体执行进度需要通过system.mutations系统表查询。注意数据的修改和删除操作是使用用MergeTree家族引擎:

只有MergeTree引擎的数据才能修改

删除分区数据

-- 创建表
create table test_muta(
    id UInt8 ,
    name String ,
    city String
)engine=MergeTree()
partition  by city
order by id ;
-- 导入数据
clickhouse-client  -q 'insert into test_muta format CSV' < data.csv
-- 删除分区数据
alter table test_muta drop  partition 'SH' ;

条件删除数据

alter table test_muta delete where id=3 ;  -- 一定加条件

条件更新数据

ALTER TABLE [db_name.]table_name UPDATE column1 = expr1 [, ...] WHERE filter_expr
ALTER TABLE test_ud
UPDATE name = 'my', job = 'teacher' WHERE id = '2' ;
 
alter table test_muta update name='李思思'  where id=3 ;

但是注意的时候一定指定where条否则会报错,这种语法的where条件也可以使用子查询 ;

2.3 分区表操作

目前只有MergeTree系列 的表引擎支持数据分区,分区的基本概念和意义和hive中的意义一样,这里不过多赘述!

区内排序 , 合并 ,去重

create table test_partition1(
id String ,
ctime DateTime
)engine=MergeTree()
partition by toYYYYMM(ctime)
order by (id) ;
-- 查看建表语句
│ CREATE TABLE default.test_partition1
(
    `id` String,
    `ctime` DateTime
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(ctime)
ORDER BY id
SETTINGS index_granularity = 8192 │
-- 插入数据
insert into test_partition1 values(1,now()) ,(2,'2021-06-11 11:12:13') ;
-- 查看数据
SELECT *
FROM test_partition1 ;
┌─id─┬───────────────ctime─┐
│ 2  │ 2021-06-11 11:12:13 │
└────┴─────────────────────┘
┌─id─┬───────────────ctime─┐
│ 1  │ 2021-05-19 13:38:29 │
└────┴─────────────────────┘
--  查看表中的分区
ClickHouse内置了许多system系统表，用于查询自身的状态信息。 其中parts系统表专门用于查询数据表的分区信息。
SELECT
    name,
    table,
    partition
FROM system.parts
WHERE table = 'test_partition1' ;
┌─name─────────┬─table───────────┬─partition─┐
│ 202105_1_1_0 │ test_partition1 │ 202105    │
│ 202106_2_2_0 │ test_partition1 │ 202106    │
└──────────────┴─────────────────┴───────────┘
insert into test_partition1 values(1,now()) ,(2,'2021-06-12 11:12:13') ;
 
┌─name─────────┬─table───────────┬─partition─┐
│ 202105_1_1_0 │ test_partition1 │ 202105    │
│ 202105_3_3_0 │ test_partition1 │ 202105    │
│ 202106_2_2_0 │ test_partition1 │ 202106    │
│ 202106_4_4_0 │ test_partition1 │ 202106    │
└──────────────┴─────────────────┴───────────┘
-- 删除分区
alter table test_partition1 drop partition '202105' ;
删除分区以后 , 分区中的所有的数据全部删除
SELECT
    name,
    table,
    partition
FROM system.parts
WHERE table = 'test_partition1'
┌─name─────────┬─table───────────┬─partition─┐
│ 202106_2_2_0 │ test_partition1 │ 202106    │
│ 202106_4_4_0 │ test_partition1 │ 202106    │
└──────────────┴─────────────────┴───────────┘
SELECT *
FROM test_partition1
┌─id─┬───────────────ctime─┐
│ 2  │ 2021-06-12 11:12:13 │
└────┴─────────────────────┘
┌─id─┬───────────────ctime─┐
│ 2  │ 2021-06-11 11:12:13 │
└────┴─────────────────────┘
 
-- 复制分区
clickHouse支持将A表的分区数据复制到B表，这项特性可以用于快速数据写入、多表间数据同步和备份等场景，它的完整语法如下：
ALTER TABLE  B  REPLACE PARTITION partition_expr FROM A
不过需要注意的是，并不是任意数据表之间都能够相互复制，它们还需要满足两个前提 条件：
·两张表需要拥有相同的分区键
·它们的表结构完全相同。
create table test_partition2  as  test_partition1 ;
show  create table test_partition2 ;  -- 查看表2的建表语句
│ CREATE TABLE default.test_partition2
    (
        `id` String,
        `ctime` DateTime
    )
    ENGINE = MergeTree()
    PARTITION BY toYYYYMM(ctime)
    ORDER BY id
    SETTINGS index_granularity = 8192 │   -- 两张表的结构完全一致
-- 复制一张表的分区到另一张表中
SELECT *
FROM test_partition2
┌─id─┬───────────────ctime─┐
│ 2  │ 2021-06-12 11:12:13 │
└────┴─────────────────────┘
┌─id─┬───────────────ctime─┐
│ 2  │ 2021-06-11 11:12:13 │
└────┴─────────────────────┘
┌─id─┬───────────────ctime─┐
│ 2  │ 2021-06-21 11:12:13 │
└────┴─────────────────────┘
----------------------------
alter table test_partition2  replace  partition '202106' from  test_partition1
SELECT
    name,
    table,
    partition
FROM system.parts
WHERE table = 'test_partition2'
┌─name─────────┬─table───────────┬─partition─┐
│ 202106_2_2_0 │ test_partition2 │ 202106    │
│ 202106_3_3_0 │ test_partition2 │ 202106    │
│ 202106_4_4_0 │ test_partition2 │ 202106    │
└──────────────┴─────────────────┴───────────┘
 
-- 重置分区数据
如果数据表某一列的数据有误，需要将其重置为初始值，如果设置了默认值那么就是默认值数据,如果没有设置默认值,系统会给出默认的初始值,此时可以使用下面的语句实现：
ALTER TABLE tb_name CLEAR COLUMN column_name IN PARTITION partition_expr ;
注意: 不能重置主键和分区字段
示例:
alter  table test_rep clear column name in partition '202105' ;
 
-- 卸载分区
表分区可以通过DETACH语句卸载，分区被卸载后，它的物理数据并没有删除，而是被转移到了当前数据表目录的detached子目录下。而装载分区则是反向操作，它能够将detached子目录下的某个分区重新装载回去。卸载与装载这一对伴生的操作，常用于分区数据的迁移和备份场景
 
┌─id─┬─name─┬───────────────ctime─┐
│  1 │      │ 2021-05-19 13:59:49 │
│  2 │      │ 2021-05-19 13:59:49 │
└────┴──────┴─────────────────────┘
┌─id─┬─name─┬───────────────ctime─┐
│  3 │ ww   │ 2021-04-11 11:12:13 │
└────┴──────┴─────────────────────┘
alter table test_rep detach partition '202105' ;
┌─id─┬─name─┬───────────────ctime─┐
│  3 │ ww   │ 2021-04-11 11:12:13 │
└────┴──────┴─────────────────────┘
-- 装载分区
alter table test_rep attach partition '202105' ;
┌─id─┬─name─┬───────────────ctime─┐
│  1 │      │ 2021-05-19 13:59:49 │
│  2 │      │ 2021-05-19 13:59:49 │
└────┴──────┴─────────────────────┘
┌─id─┬─name─┬───────────────ctime─┐
│  3 │ ww   │ 2021-04-11 11:12:13 │
└────┴──────┴─────────────────────┘
-- 记住，一旦分区被移动到了detached子目录，就代表它已经脱离了ClickHouse的管理，ClickHouse并不会主动清理这些文件。这些分区文件会一直存在，除非我们主动删除或者使用ATTACH语句重新装载

2.4 视图

2.4.1 普通视图

ClickHouse拥有普通和物化两种视图，其中物化视图拥有独立的存储，而普通视图只是一层简单的查询代理

CREATE VIEW [IF NOT EXISTS] [db_name.]view_name AS SELECT ...

普通视图不会存储任何数据，它只是一层单纯的SELECT查询映射，起着简化查询、明晰语义的作用，对查询性能不会有任何增强。

create view test3_view as select id , upper(name) , role from tb_test3 ;
┌─name────────────┐
│ tb_test3        │
│ test3_view      │
│ test_partition1 │
│ test_partition2 │
│ test_rep        │
│ tt1             │
└─────────────────┘
drop view test3_view ;   -- 删除视图

2.4.2 物化视图

物化视图支持表引擎，数据保存形式由它的表引擎决定，创建物化视图的完整语法如下所示

create materialized view mv_log  engine=Log populate as select * from log ;

物化视图创建好之后，如果源表被写入新数据，那么物化视图也会同步更新。

POPULATE修饰符决定了物化视图的初始化策略：

• 如果***使用了POPULATE***修饰符，那么在创建视图的过程中，会***连带将源表中已存在的数据一并导入***，如同执行了INTO SELECT 一般
• 如果***不使用POPULATE***修饰符，那么***物化视图在创建之后是没有数据的***，它只会同步在此之后被写入源表的数据

物化视图目前并不支持同步删除，如果在源表中删除了数据，物化视图的数据仍会保留。

create materialized view test3_view engine = Log populate as select * from tb_test3 ;
-- 建表的时候同步数据 , 当数据更新以后 物化视图中的数据会同步更新 , 但是当删除数据以后, 物化视图中的数据不会被删除
SELECT *
FROM test3_view ;
┌─id─┬─name───┬─role─┐
│  1 │ HANGGE │ VIP  │
│  2 │ BENGE  │ VIP  │
│  3 │ PINGGE │ VIP  │
└────┴────────┴──────┘
-- 向源表中擦混入数据
SELECT *
FROM test3_view
 
┌─id─┬─name──┬─role─┐
│  4 │ TAOGE │ VIP  │
└────┴───────┴──────┘
┌─id─┬─name───┬─role─┐
│  1 │ HANGGE │ VIP  │
│  2 │ BENGE  │ VIP  │
│  3 │ PINGGE │ VIP  │
└────┴────────┴──────┘
-- 删除源表中的数据 , 物化视图中的数据 不会变化  ****
-- 注意: 数据删除语法只适用于MergeTree引擎的表 基本语法如下
ALTER TABLE db_name.table_name DROP PARTITION '20210601'
ALTER TABLE db_name.table_name DELETE WHERE day = '20210618'
ALTER TABLE <table_name> UPDATE col1 = expr1, ... WHERE <filter>

show tables;其实物化视图就是一种特殊的表

四、引擎详解

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

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

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

• 数据的存储方式和位置，写到哪里以及从哪里读取数据
• 支持哪些查询以及如何支持。
• 并发数据访问。
• 索引的使用（如果存在）。
• 是否可以执行多线程请求。
• 数据复制参数,是否可以存储数据副本。
• 分布式引擎 实现分布式

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

• PARTITION BY [选填]：分区键，用于指定表数据以何种标 准进行分区。分区键既可以是单个列字段，也可以通过元组的形式使 用多个列字段，同时它也支持使用列表达式。如果不声明分区键，则 ClickHouse会生成一个名为all的分区。合理使用数据分区，可以有效 减少查询时数据文件的扫描范围，更多关于数据分区的细节会在6.2节 介绍。
• ORDER BY [必填]：排序键，用于指定在一个数据片段内， 数据以何种标准排序。默认情况下主键（PRIMARY KEY）与排序键相 同。排序键既可以是单个列字段，例如ORDER BY CounterID，也可以 通过元组的形式使用多个列字段，例如ORDER BY（CounterID,EventDate）。当使用多个列字段排序时，以ORDER BY（CounterID,EventDate）为例，在单个数据片段内，数据首先会以 CounterID排序，相同CounterID的数据再按EventDate排序。
• PRIMARY KEY [选填]：主键，顾名思义，声明后会依照主键 字段生成一级索引，用于加速表查询。默认情况下，主键与排序键 (ORDER BY)相同，所以通常直接使用ORDER BY代为指定主键，无须刻 意通过PRIMARY KEY声明。所以在一般情况下，在单个数据片段内，数 据与一级索引以相同的规则升序排列。与其他数据库不同，MergeTree 主键允许存在重复数据（ReplacingMergeTree可以去重）。
• SAMPLE BY [选填]：抽样表达式，用于声明数据以何种标准 进行采样。如果使用了此配置项，那么在主键的配置中也需要声明同 样的表达式，例如：

-- 建表语句
) ENGINE = MergeTree()
ORDER BY (CounterID, EventDate, intHash32(UserID) SAMPLE BY intHash32(UserID)

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

-- 建表语句
) ENGINE = MergeTree()
省略...
SETTINGS index_granularity = 8192;

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

• SETTINGS：index_granularity_bytes [选填]：在19.11版本之前，ClickHouse只支持固定大小的索引间隔，由 index_granularity控制，默认为8192。在新版本中，它增加了自适应 间隔大小的特性，即根据每一批次写入数据的体量大小，动态划分间 隔大小。而数据的体量大小，正是由index_granularity_bytes参数控 制的，默认为10M(10×1024×1024)，设置为0表示不启动自适应功 能。
• SETTINGS：enable_mixed_granularity_parts [选填]：设 置是否开启自适应索引间隔的功能，默认开启。
• SETTINGS：merge_with_ttl_timeout [选填]：从19.6版本 开始，MergeTree提供了数据TTL的功能，
• SETTINGS：storage_policy [选填]：从19.15版本开始， MergeTree提供了多路径的存储策略，关于这部分的详细介绍，

2.1.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.1.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   │
└────┴──────┴───────────┘

2.1.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内部会自动的合并分区的数据, 也会删除多余的文件夹中的数据

2.1.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

目录结构如下：

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

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

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

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

⑤ primary.idx：一级索引文件，使用二进制格式存储。用于存放稀疏索引，一张MergeTree表只能声明一次一级索引（通过ORDER BY 或者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索引用于记录当前分 区下分区字段对应原始数据的最小和最大值。例如EventTime字段对应 的原始数据为2019-05-01、2019-05-05，分区表达式为PARTITION BY toYYYYMM(EventTime)。partition.dat中保存的值将会是2019-05，而 minmax索引中保存的值将会是2019-05-012019-05-05。在这些分区索引的作用下，进行数据查询时能够快速跳过不必要的 数据分区目录，从而减少最终需要扫描的数据范围。

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

2.2 ReplacingMergeTree

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

2.2.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.2.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 │
└─────┴─────────────────────┴───────┘

2.2.3 总结

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

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

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

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

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

• 如果没有设置ver版本号，则保留同一组重复数据中的最后一行。
• 如果设置了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 │
└─────────┴──────────┴─────┴──────┘

查看CollapsingMergeTree官方的算法说明

For each resulting data part ClickHouse saves:

1. The first “cancel” and the last “state” rows, if the number of “state” and “cancel” rows matches and the last row is a “state” row.
2. The last “state” row, if there are more “state” rows than “cancel” rows.
3. The first “cancel” row, if there are more “cancel” rows than “state” rows.
4. None of the rows, in all other cases.

Also when there are at least 2 more “state” rows than “cancel” rows, or at least 2 more “cancel” rows then “state” rows, the merge continues, but ClickHouse treats this situation as a logical error and records it in the server log. This error can occur if the same data were inserted more than once.

总结CollapsingMergeTree合并时的折叠算法如下，对于同一分区内order by字段相同的数据：

• 如果 sign = 1 和 sign = -1 的数据条数相同时，且最后一条数据的sign = 1，那么将保留 sign = -1 的第一条数据 以及 sign = 1 的最后一条数据
• 如果 sign = 1 的数据条数 大于 sign = -1 的数据，那么将保留最后一条 sign = 1 的数据
• 如果 sign = -1 的数据条数 大于 sign = 1 的数据，那么将保留第一条 sign = -1 的数据
• 除了上述几种情况，均不会保留任何数据

另外，应当注意，当 sign = 1 和 sign = -1 的数据条数相差大于等于2的时候，CollapsingMergeTree虽然会继续合并，但是ClickHouse会认为这是一个逻辑错误，并将该错误记录到日志中。

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.6 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();

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

插如数据 ;
但是这种表不允许我们直接查询

CREATE TABLE x
(
  `id` Int8,
  `name` String
)
ENGINE =Set
insert into x values(1,'zss'),(4,'ww') ;

正确的查询方法是将Set表引擎作为IN查询的右侧条件

select * from x where (id,name) in test_set ;

CREATE TABLE test_set_source
(
    `id` Int8,
    `name` String,
    `age` Int8
)
ENGINE = Log ;
insert into test_set_source values(1,'lss',21),(2,'ww',33),(3,'zl',11) ;
-- 以set表中的数据为依据 筛选数据
select * from test_set_source where id in  test_set;
注意 :  in的条件个表的字段一致

4.3 Buffer

Buffer表引擎完全使用内存装载数据，不支持文件的持久化存储，所以当服务重启之后，表内的数据会被清空。Buffer表引擎不是为了面向查询场景而设计的，它的作用是充当缓冲区的角色。假设有这样一种场景，我们需要将数据写入目标MergeTree表A，由于写入的并发数很高，这可能会导致MergeTree表A的合并速度慢于写入速度（因为每一次INSERT都会生成一个新的分区目录）。此时，可以引入Buffer表来缓解这类问题，将Buffer表作为数据写入的缓冲区。数据首先被写入Buffer表，当满足预设条件时，Buffer表会自动将数据刷新到目标表

ENGINE = Buffer(database, table, num_layers, min_time, max_time, min_rows, max_rows, min_bytes, max_bytes)

其中，参数可以分成基础参数和条件参数两类，首先说明基础参数的作用：

• database：目标表的数据库。
• table：目标表的名称，Buffer表内的数据会自动刷新到目标表。
• num_layers：可以理解成线程数，Buffer表会按照num_layers的数量开启线程，以并行的方式将数据刷新到目标表，官方建议设为16。

Buffer表并不是实时刷新数据的，只有在阈值条件满足时它才会刷新。阈值条件由三组最小和最大值组成。接下来说明三组极值条件参数的具体含义：

• min_time和max_time：时间条件的最小和最大值，单位为秒，从第一次向表内写入数据的时候开始计算；
• min_rows和max_rows：数据行条件的最小和最大值；
• min_bytes和max_bytes：数据体量条件的最小和最大值，单位为字节。

根据上述条件可知，Buffer表刷新的判断依据有三个，满足其中任意一个，Buffer表就会刷新数据，它们分别是：

1. 如果三组条件中所有的最小阈值都已满足，则触发刷新动作；
2. 如果三组条件中至少有一个最大阈值条件满足，则触发刷新动作；

还有一点需要注意，上述三组条件在每一个num_layers中都是单独计算的。假设num_layers=16，则Buffer表最多会开启16个线程来响应数据的写入，它们以轮询的方式接收请求，在每个线程内，会独立进行上述条件判断的过程。也就是说，假设一张Buffer表的

-- max_bytes=100000000（约100 MB），num_layers=16，那么这张Buffer表能够同时处理的最大数据量约是1.6 GB。
create table xx(
id Int64
)engine=Log ;
CREATE TABLE buffer_to_xx AS memory_1
ENGINE = Buffer(default, xx, 16, 10, 100, 10000, 1000000, 10000000, 100000000) ;

此时，buffer_to_xx内有数据，而目标表memory_1是没有的，因为目前不论从时间、数据行还是数据大小来判断，没有一个达到了最大阈值。所以在大致100秒之后，数据才会从buffer_to_xx刷新到xx。

可以在ClickHouse的日志中发现相关记录信息：

INSERT INTO TABLE buffer_to_xx SELECT number FROM numbers(1000001) ;

数据直接被插入到表中

创建一个具有与’merge.hits’相同结构的’merge.hits_buffer’表，并使用Buffer引擎。写入此表时，数据会缓冲在RAM中，然后再写入“ merge.hits”表。创建了16个缓冲区。如果经过了100秒，或者已写入一百万行，或者已写入100 MB数据，则将刷新其中每个数据；或者同时经过10秒并写入10,000行和10 MB数据。例如，如果只写了一行，则无论如何，在100秒后它将被刷新。但是，如果已写入许多行，则将更快地刷新数据。

当服务器停止时，使用DROP TABLE或DETACH TABLE，缓冲区数据也将刷新到目标表。

您可以在数据库名称和表名称的单引号中设置空字符串。这表明没有目标表。在这种情况下，当达到数据刷新条件时，只需清除缓冲区。这对于将数据窗口保留在内存中可能很有用。

从缓冲区表读取数据时，将从缓冲区和目标表（如果有的话）中处理数据。

请注意

• 缓冲区表不支持索引。换句话说，缓冲区中的数据已被完全扫描，这对于大型缓冲区而言可能很慢。（对于下级表中的数据，将使用其支持的索引。）
• 如果“缓冲区”表中的列集与从属表中的列集不匹配，则插入两个表中都存在的列子集。
• 如果类型与缓冲区表和从属表中的任一列都不匹配，则会在服务器日志中输入错误消息，并清除缓冲区。
• 如果刷新缓冲区时从属表不存在，也会发生相同的情况。
• 如果需要对下级表和Buffer表运行ALTER，建议先删除Buffer表，对下级表运行ALTER，然后再次创建Buffer表。
• 如果服务器异常重启，缓冲区中的数据将会丢失。
• FINAL和SAMPLE对于缓冲区表不能正常工作。这些条件将传递到目标表，但不用于处理缓冲区中的数据。如果需要这些功能，建议从目标表读取时仅使用缓冲区表进行写入。
• 将数据添加到缓冲区时，缓冲区之一被锁定。如果同时从表执行读取操作，则会导致延迟。
• 插入到缓冲区表中的数据可能以不同的顺序和不同的块最终出现在从属表中。因此，很难使用Buffer表正确地写入CollapsingMergeTree。为了避免出现问题，可以将“ num_layers”设置为1。
• 如果目标表被复制，则写入缓冲区表时，复制表的某些预期特性会丢失。数据部分的行顺序和大小的随机变化会导致重复数据删除退出工作，这意味着不可能对复制表进行可靠的“仅一次”写入。

由于这些缺点，我们仅建议在极少数情况下使用Buffer表。

当在一个单位时间内从大量服务器接收到太多INSERT且无法在插入之前对数据进行缓冲的情况下，将使用Buffer表，这意味着INSERT不能足够快地运行。

注意，即使一次插入缓冲区表也没有意义。这样只会产生每秒几千行的速度，而插入更大的数据块则每秒会产生一百万行以上的速度（请参阅“性能”一节）。

-- ① 创建一个目标表
create table tb_user(uid Int8 , name String) engine=TinyLog ;
-- ② 创建一个缓存表
CREATE TABLE tb_user_buffer AS tb_user ENGINE = Buffer(merge, hits, 16, 10, 100, 10000, 1000000, 10000000, 100000000) ;
-- ③ 向缓存表中插入数据
insert into tb_user_buffer2 values(1,’Yang’),(2,'Haha') ,(3,'ADA') ;
-- ④ 等待以后查看目标表中的数据
select * from tb_user ;