Clickhouse入门

一、什么是ClickHouse?

1.OLAP数据库、ROLAP模型
完整的列式DBMS，支持SQL、DDL、DML语句。数十种表引擎，内置数百个函数。
2.接口丰富
提供TCP、HTTP底层访问接口，提供JDBC、CLI等封装接口，支持Java、Python、NodeJs等众多第三方接口。
3.在线查询
允许在运行时创建表和数据库，加载数据和运行查询，而不需要重新配置和启动服务器。
实时应答，无需预处理，也支持立方体预聚合。
4.分布式
MPP架构，支持集群模式，支持数据分区、分片、副本。
5.高性能
线性扩展，列式存储、高压缩、向量化引擎，秒杀一切的性能。单机部署，即拥有高性能。
6.安全可靠
熔断机制、防误删机制
7.权限
客户端接入权限，资源访问权限，操作访问权限，数据行级权限。
8.一切都是表
面向表编程，包括代理访问外部资源(例如zookeeper、文件等)。

缺点
1.不支持事务，不支持高并发。元数据管理需要人工干预维护。
2.不支持真正的删除/更新操作。
3.不支持高并发，官方建议QPS为100，可以通过修改配置文件增加连接数。
4.不支持二级索引。
5.SQL满足80%以上的语法，支持的JOIN SQL不完美，不擅长多表JOIN(效率也不高)，不支持窗口函数。
6.尽量做千条及以上批量写入，避免逐行或者小批量增删改查操作。因为clickhouse底层会不断的做异步数据合并，
  影响查询性能;
7.Clickhouse快因为采用了并行处理机制，一个查询也会使用多个CPU执行，所以不支持高并发的使用场景。


应用场景：
1.绝大多数请求都是用于读访问，要求实时返回结果。
2.读取数据时，会从数据库中提取出大量的行，但只用到小部分列，列式存储。
3.表很宽，即表中包含大量的列，列值是比较小的数值和短字符串。查询频率相抵较低。
4.每次查询只会查询一个大表，除了一个大表，其余都是小表。

二、为什么列式存储更适合OLAP?
1.OLAP分析场景往往需要读取大量行少数列，在行式数据中，数据按照行连续存储，不参与查询的列在IO时也要全部读出。
而列存模式下，只需要相关列，极大地减少了IO COST。
2.同一列的数据属于同一类型，压缩效果显著(高达十倍甚至更高的压缩比)。同时不同的数据类型列选择最适合的压缩算法。
  高压缩比，意味着内存能够存放更多数据，系统CACHE效果更好。
总结，ClickHouse对CPU占用较大，因为广泛采用数据压缩就要占用大量的CPU压缩和解压缩。

三、向量化引擎
对内存中的列式数据，一个batch调用一次SIMD指令(而非每一行调用一次)，不仅减少了函数调用次数、降低了cache miss，
而且可以充分发挥SIMD指令的并行能力，大幅缩短了计算耗时。向量执行引擎，通常能够带来数倍的性能提升。
SIMD指单指令多数据流，能够复制多个操作数，并把它们打包在大型寄存器的一组指令集。以同步方式，在同一时间内执行同一条指令。

四、数据分片及分区
数据分片是将数据进行横向切分，ClickHouse支持分片，而分片依赖集群。
每个集群由1到多个分片组成，而每个分片则对应ClickHouse的1个服务节点，分片的数量上限取决于节点数量。(1个分片只能对应1个服务节点)

Clickhouse支持PARTITION BY子句，在建表时可以指定任意合法表达式进行数据分区操作。支持日期范围、列表分区。

五、引擎
包括数据库引擎、以及表引擎。

1.数据库引擎。
包括MySQL、Lazy、MaterializeMySQL、Atomic、PostgreSQL等数据库引擎。默认情况下，ClickHouse使用Atomic数据库引擎。
MySQL：MySQL引擎用于将远程的MySQL服务器中的表映射到ClickHouse中，并允许您对表进行INSERT和SELECT查询，以方便您在ClickHouse与MySQL之间进行数据交换。
PostgreSQL：允许连接到远程PostgreSQL服务。支持读写操作(SELECT和INSERT查询)，以在ClickHouse和PostgreSQL之间交换数据。
Lazy：它是为存储许多小的Log表而优化的，对于这些表，访问之间有很长的时间间隔。
MaterializeMySQL：ClickHouse服务器作为MySQL副本工作。它读取binlog并执行DDL和DML查询。这个功能是实验性的。

2.表引擎
不同的引擎决定了表数据的存储特点，位置及数据的操作行为。
常用的也是官方推荐的存储引擎是MergeTree系列，如果需要数据副本的话可以使用ReplicatedMergeTree系列，
相当于MergeTree的副本版本。读取集群数据需要使用分布式表引擎Distribute。

MerTree引擎
Clickhouse 中最强大的表引擎当属 MergeTree(合并树)引擎及该系列(*MergeTree)中的其他引擎。
MergeTree系列的引擎被设计用于插入极大量的数据到一张表当中。数据可以以数据片段的形式一个接着一个的快速写入，
数据片段在后台按照一定的规则进行合并。相比在插入时不断修改(重写)已存储的数据，这种策略会高效很多。
主要特点：
(1)存储的数据按主键排序。这使得您能够创建一个小型的稀疏索引来加快数据检索。
(2)支持分区，查询中指定了分区键时ClickHouse会自动截取分区数据，有效增加了查询性能。
(3)支持数据副本。(ReplicatedMergeTree系列的表提供了数据副本功能)
(4)支持数据采样

数据存储：
表由按主键排序的数据片段(DATA PART)组成。
当数据被插入到表中时，会创建多个数据片段并按主键的字典序排序。例如，主键是(CounterID, Date)时，
片段中数据首先按CounterID排序，具有相同CounterID的部分按Date排序。
不同分区的数据会被分成不同的片段，ClickHouse在后台合并数据片段以便更高效存储。
不同分区的数据片段不会进行合并。合并机制并不保证具有相同主键的行全都合并到同一个数据片段中。
数据片段可以以Wide或Compact格式存储。在Wide格式下，每一列都会在文件系统中存储为单独的文件，在Compact格式下所有列都存储在
一个文件中。Compact格式可以提高插入量少插入频率频繁时的性能。
(注意，默认仍然是以Wide列单独存储方式，除非设置了参数)。
数据存储格式由min_bytes_for_wide_part和 min_rows_for_wide_part表引擎参数控制。如果数据片段中的字节数或行数少于相应的设置值，
数据片段会以Compact格式存储，否则会以Wide格式存储。
每个数据片段被逻辑的分割成颗粒(granules)。颗粒是ClickHouse中进行数据查询时的最小不可分割数据集。
ClickHouse不会对行或值进行拆分，所以每个颗粒总是包含整数个行。每个颗粒的第一行通过该行的主键值进行标记。
ClickHouse 会为每个数据片段创建一个索引文件来存储这些标记。对于每列，无论它是否包含在主键当中，ClickHouse都会存储类似标记。
颗粒的大小通过表引擎参数index_granularity和index_granularity_bytes控制。颗粒的行数的在 [1, index_granularity]范围中，这取决于行的大小。
如果单行的大小超过了index_granularity_bytes设置的值，那么一个颗粒的大小会超过 index_granularity_bytes。在这种情况下，颗粒的大小等于该行的大小。


六、安装
检查当前CPU是否支持SSE 4.2的命令:
grep -q sse4_2 /proc/cpuinfo && echo "SSE 4.2 supported" || echo "SSE 4.2 not supported"

https://packagecloud.io/Altinity/clickhouse 下载安装以下内容：
clickhouse-common-static-20.8.3.18-1.el7.x86_64.rpm
clickhouse-server-20.8.3.18-1.el7.x86_64.rpm
clickhouse-client-20.8.3.18-1.el7.x86_64.rpm
clickhouse-server-common-20.8.3.18-1.el7.x86_64.rpm

#安装
rpm -ivh ./*.rpm

#启动clickhouse
service clickhouse-server start
#重启
clickhouse restart

#配置文件：
/etc/clickhouse-server/config.xml
#工作目录(默认的数据存储目录)：
/var/lib/clickhouse/
#clickhouse的元数据目录：
/var/lib/clickhouse/metadata
#clickhouse的数据目录：
/var/lib/clickhouse/data
查看端口号：
netstat -nltp | grep 9000
默认保存日志的目录，(通常会修改，将数据保存到大容量磁盘路径中)：
/var/log/cilckhouse-server


#/etc/clickhouse-server/config.xml配置文件
#jdbc连接端口
<http_port>8123</http_port>  
#clickhouse-client连接端口
<tcp_port>9000</tcp_port>

<listen_host></listen_host>


#命令行客户端连接到服务(-m可支持多行命令)
clickhouse-client -m
#连接指定服务器的clickhouse
clickhouse-client -h hadoop03
#退出命令
exit;
quit;
#查询
select 1
#显示所有的数据库
show databases;
#查看当前的数据库
select currentDatabase();

#通过dbeaver连接
JDBC URL: jdbc:clickhouse://ip:8123

七：常用命令以及SQL
1.创建olapDB数据库
create database olapDB if not exists ;
2.使用olapDB数据库(区分大小写)
use olapDB;
3.创建表(内存引擎，server重启后数据就没有了)
create table tb_a(
id Int32,name String,age UInt8,gender String
)engine = Memory();
4.查看表信息
show tables;
desc tableName;
5.插入数据
insert into tb_a values(1,'张三',23,'M');
insert into tb_a values(2,'李四',17,'FMale');
select * from tb_a;


2.数据类型
数值类型分为整型、浮点数、和精准浮点型三类。
整型：
ClickHouse直接使用Int8、Int16、Int32和Int64代表4种大小的Int类型，其末尾的数字正好表明了占用字节的大小。
无符号的整数包括UInt8、UInt16、UInt32和UInt64.

浮点数：
Float32、Float64

精准浮点型(金额类建议使用此类型)：
Decimal
示例Decimal32(2), 32代表十进制数字以及小数位的总位数，2代表小数位位数。

字符串类型：
String、
FixedString[N],N为要声明的字节数，若包含的字节数不足N，会在末尾进行空字节填充。(用得不多)

枚举类型：
包含Enum8和Enum16两种类型。Enum保存'string'=integer的对应关系。
在clickhouse中，尽管用户插入的是字符串常量，但操作都是按照整数的值来执行的，这在性能方面比使用String数据类型更有效。
Enum后面的8和16是对应的整数值integer的位宽。
例如：创建一个带枚举类型的表。
CREATE TABLE T_ENUM
(
X Enum8('hello' = 1, 'world' = 2)
)engine = TinyLog

insert into t_enum values('hello'),('world'),('hello');

这里虽然用户插入的是hello、world，但实际上后台都是使用的整型数字。

数组类型：
SELECT ARRAY(1,2) AS X, toTypeName(x);

时间类型：
Date：可以接受一个年-月-日的格式字符串，例如'2021-10-13'
Datetime：可以接受一个年-月-日 时:分:秒的字符串。例如2021-10-13 20:50:10
Datetime64: 可以接受一个年-月-日 时:分:秒:毫秒的字符串。例如2021-10-13 20:50:10.232

可为空类型
绝大部分的基础类型，可以通过在前面添加一个Nullable()声明允许接受Null空值。例如Nullable(Int8)类型的列
可以存储Int8类型的值，没有值的行将存储NULL.


七、Clickhouse表引擎
1.合并树：MergeTree(主要使用)
2.内存：Memory、Set、Join、Buffer(加载时会把数据访问内存)
3.日志：TinyLog、StripeLog、Log
4.接口：Merge、Dictionary、Distributed(分库分表的中间件)
5.外部存储：HDFS、MySql、JDBC
6.其他：Live View、NULL

MergeTree表引擎家族：
Replicated(支持数据副本)+
Replacing\Summing\Aggregating\Collapsing\VersionedCollapsing\Graphite+
MergeTree 基础表引擎

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
    ...
    INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
    INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
) ENGINE = MergeTree()
ORDER BY expr
[PARTITION BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'], ...]
[SETTINGS name=value, ...]



表引擎决定了：
1.数据的存储方式和位置，写到哪里以及从哪里读取。
2.支持哪些查询以及如何支持。
3.并发数据访问
4.索引的使用
5.是否可以执行多线程请求
6.数据复制参数


四类表引擎
1.mergeTree
MergeTree 系列的引擎被设计用于插入极大量的数据到一张表当中。数据可以以数据片段的形式一个接着一个的
快速写入，数据片段在后台按照一定的规则进行合并。相比在插入时不断修改（重写）已存储的数据，这种策略会高效很多。
新的数据(包括更新以及删除的数据)并不影响原有数据，而会记录在一个新开辟的临时数据块中。查询时通过版本号查询
最新的一条结果。新开辟的数据需要等到后台进行数据合并时，才会进入主数据中。
数据合并时在后台某个不确定的时间点进行的，当然可以手动触发数据合并。





主要特点:
存储的数据按主键排序。这使得您能够创建一个小型的稀疏索引来加快数据检索。
如果指定了分区键的话，可以使用分区。在相同数据集和相同结果集的情况下ClickHouse中某些带分区的操作
会比普通操作更快。查询中指定了分区键时 ClickHouse 会自动截取分区数据。这也有效增加了查询性能。
支持数据副本。
ReplicatedMergeTree 系列的表提供了数据副本功能。更多信息，请参阅数据副本一节。
支持数据采样。
需要的话，您可以给表设置一个采样方法。
该类型的引擎：

2.日志引擎
这些引擎是为了需要写入许多小数据量(少于一百万行)的表的场景而开发的。
这系列的引擎有：
StripeLog
日志
TinyLog
主要特点：
数据存储在磁盘上。
写入时将数据追加在文件末尾。
不支持突变操作。
不支持索引。
非原子地写入数据。

3.集成引擎
ClickHouse 提供了多种方式来与外部系统集成，包括表引擎。像所有其他的表引擎一样，使用CREATE TABLE或ALTER TABLE查询语句来完成配置。然后从用户的角度来看，配置的集成看起来像查询一个正常的表，但对它的查询是代理给外部系统的。这种透明的查询是这种方法相对于其他集成方法的主要优势之一，比如外部字典或表函数，它们需要在每次使用时使用自定义查询方法。

以下是支持的集成方式:

ODBC
JDBC
MySQL
MongoDB
HDFS
S3
Kafka
EmbeddedRocksDB
RabbitMQ
PostgreSQL

4. 用于其他特定功能的引擎 
该类型的引擎：
Distributed
MaterializedView
Dictionary
Merge
File
Null
Set
Join
URL
View
Memory
Buffer

5.虚拟列
虚拟列是表引擎组成的一部分，它在对应的表引擎的源代码中定义。
不能在 CREATE TABLE 中指定虚拟列，并且虚拟列不会包含在 SHOW CREATE TABLE 和 DESCRIBE TABLE 的查询结果中。虚拟列是只读的，所以不能向虚拟列中写入数据。
如果想要查询虚拟列中的数据，必须在SELECT查询中包含虚拟列的名字。SELECT * 不会返回虚拟列的内容。
若创建的表中有一列与虚拟列的名字相同，那么虚拟列将不能再被访问。为了避免这种列名的冲突，虚拟列的名字一般都以下划线开头。

八、MergeTree引擎
create table t_stock(
id UInt32,
sku_id String,
total_amount Decimal(16,2),
create_time Datetime
)engine=MergeTree()
partition by toYYYYMMDD(create_time)
primary key(id)
order by(id,sku_id);

insert into t_stock values
(101,'sku_002',2000,'2020-06-01 11:00:00'),
(102,'sku_004',2500,'2020-06-01 12:00:00'),
(103,'sku_002',2000,'2020-06-02 13:00:00'),
(104,'sku_002',12000,'2020-06-03 13:00:00'),
(105,'sku_002',600,'2020-06-04 12:00:00');

partition by 分区键：
主要是降低数据扫描的范围，优化查询速度。若不填写，相当于只用了一个分区。
分区表的数据目录：
MergeTree引擎默认是以列文件+索引文件+表定义文件共同描述一个表。这些文件都在clickhouse
本地磁盘(var/lib/clickhouse目录)。如果设定了分区，这些文件会保存在不同的分区目录中。

order by 排序键
指定分区内的数据按照哪些字段排序进行有序保存。这个MergeTree中唯一的必填项。
数据有序保存对于clickhouse是相当重要的，可以实现快速检索、去重、汇总等。注意clickhouse的数据在分区内局部有序。
若不设置表的主键，会以排序键对数据进行检索等数据处理。若设置主键，必须是order by的前缀字段。例如order by排序键设置为(id,sku_id)，那么主键只能是id或者(id,sku_id)。

primary key主键
作用是为了加快数据检索的，但并不要求主键的数据具有唯一性。
index granularity 索引粒度
指的在稀疏索引中两个相邻索引对应数据的间隔。默认值是8192.
稀疏索引，并不记录每一个主键的数值，而是按照一定的稀疏度，记录几个节点的索引数据。
而这些记录的数据，就保存在分区所在的数据目录中。
稀疏索引的好处是可以减少数据的检索次数，每次根据一个主键进行数据查找时，可以根据稀疏索引
确定数据所在的范围，然后再在选定的范围内进行逐行扫描，快速定位到目标数据。

注意：索引的性能会受到索引字段数据分布的影响，设计表的时候需要充分考虑业务数据的实际情况，避免使用区分度很低的字段做索引。
同时索引粒度(index_granularity)也是一个重要的属性，ClickHouse 默认8192，这个值在大多数的情况下都能提供良好的性能。

二级索引
相当于给一级索引再建立一个索引。二级索引的目的同样也是为了加快数据检索速度。
create table t_stock_2(
id UInt32,
sku_id String,
total_amount Decimal(16,2),
create_time Datetime,
INDEX secondIndex total_amount TYPE minmax GRANULARITY 5
)engine = MergeTree
partition by toYYYYMMDD(create_time)
primary key(id)
order by(id,sku_id);

例如对于一级索引，granularity粒度为4，就会划分为[1,3],[3,6],[6,9],[9,12]这样一些区间。而二级索引按照3的粒度，就会将三个区间聚合在一起，形成[1,9],[9,19]这样区间。当对数据进行检索时，就可以先按照二级索引确定一个初略的范围，再按照一级索引确定数据遍历的范围。


/metadata : 元数据
/metadata/数据库名 : 存放建表语句

/data/数据库名/表名：表的数据相关文件
#20200601_1_1_0
其中20200601为分区键，_1为此目录的数据块的最小编号，_1为此目录数据块的最大编号，_0为合并的次数。
#{column}.bin：列数据的存储文件，以列名+bin为文件名，默认设置采用lz4压缩格式。
每一列都会有单独的文件(新版本需要指定 SETTINGS min_rows_for_wide_part = 0, min_bytes_for_wide_part = 0 参数来强制跳过 Compact format)。
#{column}.mrk2
索引文件与数据文件的关联(data.mrk3与primary.idx)
列数据的标记信息，记录了数据块在 bin 文件中的偏移量。标记文件首先与列数据的存储文件对齐，
记录了某个压缩块在bin文件中的相对位置；其次与索引文件对齐，记录了稀疏索引对应数据在列存储文件中的位置。
clickhouse 将首先通过索引文件定位到标记信息，再根据标记信息直接从.bin 数据文件中读取数据。
#primary.idx
主键索引文件，用于存放稀疏索引的数据。通过查询条件与稀疏索引能够快速的过滤无用的数据，减少需要加载的数据量。
#minmax_create_time.idx
根据分区键，创建了分区键(create_time)的最大最小值的索引，当查询指定日期时，快速查询对应数据块。
#columns.txt
表的列信息
#count.txt
记录数(该区块的数据记录数)

分区合并
MergeTree引擎底层使用一种类似于LSM树的结构来保存数据。任何一次对数据的修改都会临时产生一个分区，而不会修改已有的分区。写入后的某个时刻，clickhouse会在后台自动执行合并操作。这个时间间隔大概在10~15分钟左右，
可以执行手工合并指令(即快速将内存中的数据写入磁盘中)。

insert into t_stock values
(101,'sku_002',2000,'2020-06-01 14:00:00'),
(102,'sku_004',2500,'2020-06-01 15:00:00'),
(103,'sku_002',2000,'2020-06-01 16:00:00'),
(104,'sku_004',12000,'2020-06-01 17:00:00');

select * from t_stock;(查询数据会发现有20200601有两个分区，操作系统目录也有两个)

optimize table t_stock final;

TTL数据存活时间
TTL即Time To Live。可以用来指定行存储的持续时间。MergeTree可以针对表或者列声明数据存活时间。设置TTL需要指定一个表达式来表示数据的存活时间，表达式中必须存在一个表示时间的Date或者DateTime类型的列。比如TTL date+INTERVAL 1 DAY。
当列中的值过期时，clickhouse会将它们替换成该列数据类型的默认值。若某个数据块中列所有值都过期了，那么clickhouse会直接删除此列。列式TTL不能用于主键。

列级TTL
例如声明total_amount字段存活时间为create_time创建时间后的10秒钟。
create table example_table(
d DateTime,
a Int TTL d+ INTERAL 1 MONTH,
b Int TTL d+ INTERAL 1 MONTH,
c String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(d)
ORDER BY d;

表级别TTL
除了设置一个过期表达式之外，可以配置一个数据移除规则。完整的声明指令如下：
定义clickhouse如何移除过期数据
Delete：删除过期的行 默认行为
TO DISK 'aaa'-将数据块移动到磁盘'aaa'
TO VOLUME 'bbb' -将数据块移动到卷'bbb'
GROUP BY  -聚合过期的行
例如：
CREATE TABLE example_table
(
d DateTime,
a Int
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(d)
ORDER BY d
TTL d+INTERVAL 1 MONTH[DELETE],
d+INTERVAL 1 WEEK TO VOLUME 'aaa',
d+INTERVAL 2 WEEK TO DISK 'bbb';

数据抽样用于大数据分析，可以极大提升数据分析的性能。采样修饰符只能用在MergeTree的表中才有效，并且抽样表达式指定的列，必须包含在主键中。进行采样声明后，就可以在查询时
进行采样查询。
SAMPLE BY intHash32(UserID)
查询：
SELECT TITLE,COUNT(*) AS pageViews
FROM hints_v1
sample 0.1 # 代表采用10%的数据，可以是具体的条数。
这样采样查询是在满足条件的结果集中随机抽取10%的数据。

九、ReplacingMergeTree
该引擎和MergeTree的不同之处在于它会删除排序键值相同的重复项。
数据的去重只会在数据合并期间进行。合并会在后台一个不确定的时间进行，因此你无法预先作出计划。
有一些数据可能仍未被处理。尽管你可以调用 OPTIMIZE 语句发起计划外的合并，但请不要依靠它，
因为 OPTIMIZE 语句会引发对数据的大量读写。
因此，ReplacingMergeTree适用于在后台清除重复的数据以节省空间，但是它不保证没有重复的数据出现。
建表语法：
CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = ReplacingMergeTree([ver])
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

ReplacingMergeTree的参数
ver — 版本列。类型为 UInt*, Date 或 DateTime。可选参数。
在数据合并的时候，ReplacingMergeTree 从所有具有相同排序键的行中选择一行留下：
如果 ver 列未指定，保留最后一条。
如果 ver 列已指定，保留 ver 值最大的版本。

注意几个重点：
1.ReplacingMergeTree是按照order by指定的排序键作为判断重复的标准。
2.去重只限定在一个分区中，不能跨区去重。
3.对于判断为重复的数据，保留版本字段最大的一条数据，若没有指定或者版本值也有重复的，只会保留最后插入的一条数据。
4.ReplacingMergeTree并不能始终保证数据是完全去重的。数据去重只会发生在同一批插入数据以及后台数据合并这两个时机。



例如：
create table t_stock_merge(
id UInt32,
sku_id String,
total_amount Decimal(16,2),
create_time Datetime
)
ENGINE=ReplacingMergeTree(create_time)
PARTITION BY toYYYYMMDD(create_time)
PRIMARY KEY id
ORDER BY (id,sku_id);

insert into t_stock_merge values
(101,'sku_001',1000.00,'2020-06-01 12:00:00'),
(102,'sku_002',2000.00,'2020-06-01 11:00:00'),
(102,'sku_004',2500.00,'2020-06-01 12:00:00'),
(102,'sku_001',2000.00,'2020-06-01 13:00:00'),
(102,'sku_001',12000.00,'2020-06-01 13:00:00'),
(102,'sku_001',600.00,'2020-06-02 12:00:00');

select * from t_stock_merge;

--执行数据合并，查看数据已合并
optimize table t_stock_merge final;
select * from t_stock_merge;

十、SummingMergeTree
它在MergeTree的基础上，进行数据合并时，会把具有相同主键的行合并成一行，新合并的行包含了被合并的行中
具有数值数据类型的列的汇总值。如果主键的组合方式使得单个键值对应于大量的行，则可以显著的减少存储空间并加快数据查询的速度。
在建表时，通过指定参数，表明按照哪个列进行聚合分析。
参数注意：
包含了将要被汇总的列的列名的元组。可选参数。所选的列必须是数值类型，并且不可位于主键中

create table t_stock_sum(
id UInt32,
sku_id String,
total_amount Decimal(16,2),
create_time Datetime
)
ENGINE=SummingMergeTree(total_amount)
PARTITION BY toYYYYMMDD(create_time)
PRIMARY KEY (id)
ORDER BY (id,sku_id);

insert into t_stock_sum values
(101,'sku_001',1000.00,'2020-06-01 12:00:00'),
(102,'sku_002',2000.00,'2020-06-01 11:00:00'),
(102,'sku_004',2500.00,'2020-06-01 12:00:00'),
(102,'sku_001',2000.00,'2020-06-01 13:00:00'),
(102,'sku_001',12000.00,'2020-06-01 13:00:00'),
(102,'sku_001',600.00,'2020-06-02 12:00:00');

select * from t_stock_sum;

汇总的通用规则 
列中数值类型的值会被汇总。这些列的集合在参数columns中被定义。
如果用于汇总的所有列中的值均为0，则该行会被删除。
如果列不在主键中且无法被汇总，则会在现有的值中任选一个。
主键所在的列中的值不会被汇总。
ClickHouse 会按片段合并数据，不同的数据片段中会包含具有相同主键的行。
AggregatingMergeTree是对合并逻辑逻辑进行定制。

十一、数据修改
数据修改对应update和delete操作。在clickhouse中，数据的修改和删除时非常"重"的操作。
在语句执行过程中，将原有数据打上逻辑上的删除标记，然后新增数据放入新的分区，直到触发分区合并的时候，才会
删除旧的数据。频繁的update和delete操作会加大服务器的负担。建议通过表引擎来完成数据的修改操作，比如Replacing MergeTree完成去重。

--删除操作
alter table t_stock delete where sku_id='sku_004';
--修改操作
alter table t_stock update total_amount=toDecimal32(1000.00,2) where id = 101;



十二、数据查询
在标准SQL查询这一块：
支持子查询
支持各种JOIN查询。但是不建议使用。因为JOIN操作无法使用缓存。并且clickhouse执行join操作的方式是将
后面的表全部加载到内存中执行，优化不是很好。表很大时性能影响非常明显。
支持With关键字创建临时表。
表函数：
例如numbers函数，可以生成一组连续的整数，在测试时非常有用。
select * from numbers(10); --产生一组0~10的整数 
select * from numbers(10,20); --从10开始，产生20个整数
select toDate('2021-01-01') + number as d from numbers(365); -- 产生2021- 01-01 ~ 2021-12-31的日期序列

generateRandom函数则可以产生一组随机值。
SELECT * FROM generateRandom('a Array(Int8), d Decimal32(4), c Tuple(DateTime64(3), UUID)', 1, 10, 2) LIMIT 3;

clickhouse中还有一系列的表函数，可以直接读取远程数据库中的数据。比如file文件，hdfs文件，jdbc数据，postgresql，
甚至直接访问远程服务器上的文件等。

聚合函数：
clickhouse提供了大量的聚合函数，除了count，min，max，sum等这些常见的聚合函数外，甚至还包括了corr皮尔逊相
关系数，rankCorr斯皮尔曼相关系数，simpleLinearRegression一维线性回归等机器学习中常用的聚合函数。
在group by操作上，clickhouse还支持with rollup\with cube\with totals，来进行统计聚合。
-- 按gourp by 的顺序，从右至左逐个去掉维度进行聚合。依次按照 (id,sku_id),(id),()分 组，对total_amount进行求和 
select id,sku_id,sum(total_amount) from t_stock group by id,sku_id with rollup; 
-- 按照goup by 的字段，互相组合进行聚合 
select id,sku_id,sum(total_amount) from t_stock group by id,sku_id with cube; 
-- 只按照group by的全字段，以及所有数据一起聚合。只按照 (id,sku_id),()两个分组进行 求和
select id,sku_id,sum(total_amount) from t_stock group by id,sku_id with totals;


十三、集群机制
clickhouse的单机性能通常已经非常优秀了，底层的数据压缩效率很高。
官方提供的Github Evnets数据集三十亿数据，用200G的硬盘就存下来。单机查询性能若CPU配置足够好的话，查询速度可以达到秒级。
clickhouse提供了集群机制的支持，有两个作用，一个是数据副本，将数据冗余到另外的机器上，用以保证高可用。二是分布式表，
将一个表的数据分散到多个节点保存。
数据副本比较重要，而分布式表，大部分场景下是不需要的，因为clickhouse单机性能通常已经非常优秀了，底层的数据压缩效率很高的。
采用分布式表反而会消耗网络资源，降低查询速度。
clickhouse只有MergeTree系列的表可支持副本，表引擎为Replicated*MergeTree.
副本是表级别的，不是整个服务器级别的。所以服务器里可以同时有复制表和非复制表。
副本机制对于select查询是没有影响的，查询复制表和非复制表的速度是一样的。而写入数据时，clickhouse的集群没有主从之分，在任何一个节点上写数据都会同步到其他节点上，
大家都是平等的。只不过配置了复制表后，insert以及alter操作会同步到对应的副本机器中。对于复制表，每个Insert语句会往Zookeeper中写入十来条记录，
相比非复制表，写ZK会导致Insert语句的延迟时间略长。但是，在clickhouse建议的每秒不超过一个Insert语句的执行频率下，这个延迟时间不会有太大的影响。
副本不依赖分片。每个分片有它自己的独立副本。
对于INSERT和ALTER语句操作数据的会在压缩的情况下被复制，而CREATE,DROP,ATTACH,DETACH和RENAME语句只会在单个服务器上执行，不会被复制。
要使用副本，需在配置文件中设置 ZooKeeper 集群的地址。
/etc/clickhouse-server/config.xml例如：
<zookeeper>
    <node index="1">
        <host>example1</host>
        <port>2181</port>
    </node>
    <node index="2">
        <host>example2</host>
        <port>2181</port>
    </node>
    <node index="3">
        <host>example3</host>
        <port>2181</port>
    </node>
</zookeeper>

clickhouse集群数据同步步骤：
1.client写入数据到clickhouse某个节点。
2.写入完成后，节点异步提交写入日志到zookeeper集群中。
3.zookeeper集群收到写入日志，发送到其他集群节点中。
4.其他集群节点从目标副本下载新数据。

数据副本需要经过网络传输，所以副本中写入数据是有延迟的，默认情况下，clickhouse对于Insert语句，只会等待一个副本
写入成功后就会返回。如果有多个副本的情况下，clickhouse是有可能丢失数据的。写入数据时，clickhouse只保证单个数据块的写入是原子的，
而不能保证所有的数据写入是原子的。一个数据块的大小可以根据max_insert_block_size=1048576行进行分块。
数据块写入时是会去重的，一个同样的Insert语句多次重复执行，数据库块只会执行一次。这是为了防止用户重复插入数据或者网络波动等原因造成的数据重发。
这个去重机制只对应Replicated*MergeTree系列的表引擎，普通的MergeTree是不带这个去重功能的。
官方建议，不要在clickhouse所在的服务器上运行zookeeper。因为zookeeper对数据延迟非常敏感，而clickhouse可能会占用所有可用的系统资源。
注意当前版本的clickhouse要求zookeeper版本不低于3.4.5。

创建复制表,与MergeTree表引擎建表语句基本是一样的，只不过要加上Replicated。
ReplicatedMergeTree中传入两个参数，一个参数是ZooKeeper中该表的路径，一个参数是ZooKeeper中的该表的副本名称(使其与ClickHouse中的表名相同比较好).
create table t_stock_replicated
(
id UInt32,
sku_id String,
total_amount Decimal(16,2),
create_time DateTime
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/t_stock','hadoop01')
PARTITION BY toYYYYMMDD(create_time)
PRIMARY KEY id
ORDER BY (id,sku_id);

注意在其他集群节点上也需要新建对应的表。第二个参数要修改为hadoop02
create table t_stock_replicated
(
id UInt32,
sku_id String,
total_amount Decimal(16,2),
create_time DateTime
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/t_stock','hadoop02')
PARTITION BY toYYYYMMDD(create_time)
PRIMARY KEY id
ORDER BY (id,sku_id);

十四、分布式表
副本机制能够提高数据的可用性，降低丢失数据的风险，但每台服务器上都需要容纳全量的数据，没有解决数据的横向扩容的问题。
在clickhouse中，可以通过水平切分的方式，将完整的数据集切分成不同的分片。这些分片只保存一部分数据，分布在不同的节点上。
再通过Distributed表引擎将数据拼接起来作为一个完整的表使用。Distributed表引擎本身不存储数据，就有点像ShardingSphere与
MySql，只是一个中间件，通过分布式逻辑表来写入、分发、路由操作多台节点不同分片的分布式数据。
clickhouse的单机性能很强的，很多企业在实际运用过程中，会配置副本机制来做高可用，但通常不分片，这样会降低查询时的性能消耗。

clickhouse分布式表使用实例
1、同样需要zookeeper支持。
2、在clickhouse中配置集群。
同样是打开clickhouse的配置文件 /etc/clickhouse-server/config.xml，在配置文件中配置一个分布式集群，然后重启服务器。
在590行左右找到标签进行配置。
<remote_servers>
    <logs>
        <shard>
            <!-- Optional. Shard weight when writing data. Default: 1. -->
            <weight>1</weight>
            <!-- Optional. Whether to write data to just one of the replicas. Default: false (write data to all replicas). -->
            <internal_replication>false</internal_replication>
            <replica>
                <host>example01-01-1</host>
                <port>9000</port>
            </replica>
            <replica>
                <host>example01-01-2</host>
                <port>9000</port>
            </replica>
        </shard>
        <shard>
            <weight>2</weight>
            <internal_replication>false</internal_replication>
            <replica>
                <host>example01-02-1</host>
                <port>9000</port>
            </replica>
            <replica>
                <host>example01-02-2</host>
                <secure>1</secure>
                <port>9440</port>
            </replica>
        </shard>
    </logs>
</remote_servers>

在这一段配置中，配置了一个名为logs的集群，他由两个分片shard组成，每个分片只包含两个副本replica。
3、使用分片表
在建表时,通过on cluster关键字指定集群名
CREATE TABLE t_stock_local on cluster logs
( 
 id UInt32, 
 sku_id String, 
 total_amount Decimal(16, 2), 
 create_time DateTime 
)ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/t_stock','{replica}') 
PARTITION BY toYYYYMMDD(create_time)
PRIMARY KEY id ORDER BY (id, sku_id);

4、使用Distributed表引擎创建分布式表
CREATE TABLE t_stock_distributed on cluster logs
( 
 id UInt32, 
 sku_id String, 
 total_amount Decimal(16, 2), 
 create_time DateTime 
)ENGINE = Distributed(logs,default, t_stock_local,hiveHash(sku_id));
在Destributed引擎中,需要指定几个参数,依次是:集群名称、库名、本地表名、分片键。