Clickhouse笔记（2022/更新中）

作者: 这货不是王马勺 | 来源:发表于2022-06-14 09:41 被阅读0次

一.简介

什么是Clickhouse？

Clickhouse是俄罗斯yandex公司开源的用于OLAP的列式数据库管理系统，它是用C++写的，支持SQL实时查询和大型数据库管理系统一般用于后端数仓。
Clickhouse是真正的列式存储，相比较于HBase来说，HBase的列簇可以说是伪列式存储。Clickhouse存储一个表数据的时候，就是以一张表为一个目录，一个列为一个文件进行存储的。
它不依赖任何体系，能独立作为大数据存储引擎。单机性能也非常强。

适用场景

绝代多数请求都是读请求，对数据得修改很少或者几乎没有。
数据量恒大。这个量既包括数据的行数，也包括数据的列数。也就是通常说的宽表。大部分情况下，对分布式表结构的要求是必须的。
数据通常以大的批次进行整体更新而不是单行更新。这需要有很高的数据吞吐量。
对事务的要求不是必须的。对于数据一致性的要求不会太高，通常仅要求数据的最终一致性即可。(ES查询能力强，HBase速度很快，但都不能满足关系型数据库的强一致性)

clickhouse的数据吞吐量相当大，(可认为clickhouse自己就可以顶替Hadoop用)，单机就能够存储海量的数据，能够以水平扩展方式进行扩容。对大表的查询计算处理效率也非常高，甚至很多场景下都可以拥有媲美于关系型数据库的查询效率，（基本上可认为是准实时）。官网给出的一些测试数据也大都是上千万行*数百列的数据规模。很多大规模的数据查询也都能轻松达到毫秒级别。

但是需要注意，clickhouse高性能的背后，一定伴随计算机资源的大量消耗。clickhouse对内存和CPU的占用都非常高，一个很普通的查询都可能需要消耗非常多的资源，（clickhouse会消耗大量CPU来提升自己的性能，因此clickhouse一般都是专用服务器，不会部署其他服务）。因此clickhouse的查询频率也不宜太高。过于频繁的连续或者并发查询容易导致服务直接崩溃。

综合Clickhouse的特点，他就非常适合用于后端数仓的建设，也符合clickhouse的设计目标。

二.Clickhouse安装

线上环境体验

访问官网clickhouse.com，选择Online Demo

然后就可以使用这个线上环境了。

在Welcome中可以看到有一些使用帮助：

在Playground中可以执行语句：

本地快速部署

clickhouse官方文档安装部分：
https://clickhouse.com/docs/zh/getting-started/install/
使用三台CentOS服务器安装。安装前需关闭防火墙及SELinux安全组件，并打开操作系统的文件限制。
我们采用最直观的tgz压缩包的方式进行安装。生产环境也可通过源码打包编译方式安装。不要用rpm或者deb方式。

下载
下载地址：
https://repo.clickhouse.com/tgz/stable/
这个仓库中直接包含了clickhouse的所有发布版本。可以看到clickhouse的发布版本非常频繁。第一个数字代表年份。
我们选取21.9.4.35版本。这里有四种tgz包是需要下载的：

clickhouse-server-21.9.4.35.tgz
clickhouse-common-static-dbg-21.9.4.35.tgz
clickhouse-common-static-21.9.4.35.tgz
clickhouse-client-21.9.4.35.tgz

然后分别将这四个压缩包解压并依次安装。

安装
首先安装static这个包，解压后进入install目录，执行doinst这个脚本：

tar -zxvf clickhouse-common-static-21.9.4.35.tgz
cd clickhouse-common-static-21.9.4.35
cd install/
ls
./doinst.sh

这个脚本本质其实就是复制文件到相应位置。

然后安装dbg这个包：

tar -zxvf clickhouse-common-static-dbg-21.9.4.35.tgz
cd clickhouse-common-static-dbg-21.9.4.35
cd install/
ls
./doinst.sh

然后安装server这个包：

tar -zxvf clickhouse-server-21.9.4.35.tgz
cd clickhouse-server-21.9.4.35
cd install/
ls
./doinst.sh

在server包安装过程中，需要输入一个默认用户密码。

最后安装client：

tar -zxvf clickhouse-client-21.9.4.35.tgz
cd clickhouse-client-21.9.4.35
cd install/
ls
./doinst.sh

使用clickhouse --help命令，能显示常用指令的提示。
这些指令都在usr/bin目录下，不需要配环境变量。
clickhouse安装过程中会自动创建一个clickhouse用户，如果不是root用户进行操作的话，需要注意一下用户权限的问题。

启动
启动clickhouse，并用客户端连接：

clickhouse start
clickhouse-client

连上后可以执行show databases;等命令了：

show databases;
use default;
show tables;
use system;
show tables;

clickhouse核心的配置文件在/etc/clickhouse-server/目录下，配置.xml文件。
clickhouse运行日志在/var/log/clickhouse-server/目录下。
clickhouse数据目录在/var/lib/clickhouse/目录下，其中data子目录用于保存数据，metadata用于保存元数据。
下面也都是软链接对应了一个uuid一样的真实数据文件。

默认安装目录总结：

执行脚本：/usr/bin/
配置文件：/etc/clickhouse-server/
运行日志：/var/log/clickhouse-server/
数据目录：/var/lib/clickhouse/

这些目录是可以手动修改的，后续会说明。

连接
默认情况下，clickhouse的服务只能在本地进行连接，需要我们手动打开远程连接。
直接修改clickhouse的config.xml配置文件。
所在目录/etc/clickhouse-server/，将如下这一行注释打开(应该是文件156行)

<listen_host>::</listen_host>

然后重启clickhouse即可。(最新版本需不需要重启待验证)

clickhouse restart

远程连接hadoop03这台机器的clickhouse：

clickhouse-client -h hadoop03 -m

-m的作用是支持多行指令。

clickhouse还提供http接口
直接访问

http://hadoop03:8123

会提示ok.

还可以直接再此查询，例如：

http://hadoop03:8123/?query=show databases

查询结果会成为一个下载文件。

同样在8123端口，clickhouse还提供了jdbc驱动程序来连接。
目前官网提供了一个官方驱动包以及两个第三方驱动包。
其中官方jdbc驱动包的maven坐标是：

<dependency>
        <groupId>ru.yandex.clickhouse</groupId>
        <artifactId>clickhouse-jdbc</artifactId>
        <version>0.3.2</version>
</dependency>

引入这个驱动包后，就可以像连接其他关系型数据库一样访问clickhouse了。

Class.forName("ru.yandex.clickhouse.ClickHouseDriver");
Connection connection = 
DriverManager.getConnection("jdbc:clickhouse://hadoop01:8123/default");
...

8123提供标准连接，此外还兼容MySQL和Postgresql
9004提供MySQL的端口，可以像连MySQL一样连接clickhouse：

同理，Postgresql的默认端口是9005.

三.Clickhouse的库和表

clickhouse本身作为一个数据库，对于普通增删改查是支持的。针对数仓的使用场景又有很多高级特性。不同的SQL写法在cliclhouse上很容易体现出非常大的执行时间差别，需要引起重视。

建库

Atomic库引擎
这是Clickhouse默认的库引擎，默认的库default就是使用这种引擎，可以在建库时声明：

CREATE DATABASE test [ENGINE = Atomic];

Atomic类型的库完全由cilcihouse自己管理数据，每个数据库对应/var/lib/data/目录下的一个子目录。数据库中的每个表会分配一个唯一的uuid，数据存储在目录/var/lib/clickhouse/store/xxx/xxxyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy/，其中xxxyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy是该表的uuid。

查看test库建库语句：

show create database test;

在OS层查看test库的attach语句：

cd /var/lib/clickhouse/metadata
less test.sql

MySQL库引擎
此外还有例如MySQL引擎，用于将远程MySQL服务器中的表映射到Clickhouse种，并允许对表进行insert和select，方便在clickhouse和mysql之间进行交换。此引擎会将查询转换为MySQL语法并发送到MySQL服务器中，因此可以执行诸如show tables或show create table之类的操作。
通过MySQL引擎可以省略掉很多ETL过程。例如下面语句可以在clickhouse种创建一个名为mysqldb的库

create database if not exists mysqldb engine = MySQL('hadoop01:3306','testdb','root','root');

参数解释：第一个参数表示服务器和端口，第二个是库名，第三个是用户名，第四个是密码。
这种库引擎，clickhouse本身不存储数据，只是将请求转发给mysql。同样，clickhouse还提供了针对Postgresql、SQLLite的库引擎。
个人理解是可将MySQL引擎的库类比为dblink或linkedserver。

此外还有类似物化视图的引擎MaterializedMySQL，以及MaterializedPostgreSQL。这两个引擎都会将clickhouse服务器作为对应数据库的从库工作。通过日志(如binlog)将主库数据同步到clickhouse中。目前这两种引擎还在测试阶段，不建议在生产环境使用。

建表

数据类型

clickhouse的极简化设计在基础数据类型中显得尤为明显。

整型
clickhouse中的整型是统一表示固定长度的整数，包括有符号整型和无符号整型。统一定义为int，后面带上数字表示占用的字节数。

整型范围：

Int8-[-128:127] 占用8位，对应java中的byte
Int16-[-32768:32767] 占用16位，对应java中的short
Int32-[-2147483648:2147483647] 占用32位，对应java中的int
Int64-[-9223372036854775808:9223372036854775807] 占用64位，对应java中的long

无符号整型范围：

UInt8-[0:255]
UInt16-[0:65535]
UInt32-[0:4294967295]
UInt64-[0:18446744073709551615]

boolean布尔型
clickhouse中没有表示true或false的布尔型，通常都是直接使用UInt8

浮点型

float32 - float
float64 - double

官方建议尽量用整型存储数据，将固定精度的数字转换成整数值。例如时间用毫秒位单位保存。因为使用浮点型由精度丢失问题。例如执行select 1-0.9得到的结果将是0.09999999999999998而不是0.1
浮点型一般用户数据值比较小，不涉及大量计算，精度要求不高的场景。如商品重量。对于精度要求比较高的，比如金额，就不应使用浮点型，而应使用decimal型。

Decimal型
有符号的浮点数，可在加减和乘法运算中保持精度。对于除法，最低有效数字将被抛弃（不进行四舍五入）。
通常有三种声明：Decimal32(s)、Decimal64(s)、Decimal128(s)。
后面的s表示小数点后的数字位数。前面的32、64、128表示浮点精度，决定可以有多少个十进制数字(包含小数位)，也就代表不同的取值范围。
Decimal可以声明两个参数，一般我们就声明一个s就够了。

数据在底层会采用与自身位宽相同的有符号整数存储。而现代CPU不支持128位的数字，因此Decimal128上的操作需要由软件来进行模拟。所以Decimal128的运算速度会明显慢于Decimal32或Decimal64，因此尽量少用Decimal128。

字符型
clickhouse字符型使用string进行声明。这个字符串可以是任意长度，可以包含任意的字节集，包含空字节。因此字符串类型可替代其他数据库中的varchar、BLOB、CLOB等类型。
clickhouse没有编码概念，字符串可以是任意的字节集，按他们原本的方式进行存储和输出。对于不同的编码文本，clickhouse会有不同的处理字符串的函数。比如length函数可计算字符串包含的字节数组的长度。lengthUTF8函数是假设字符串以UTF-8编码，计算包含的Unicode字符的长度。
还有固定长度的字符串类型FixedString(N)，N就是要声明的字节数。如果字符串包含的字节数不足N，将会对字符串末尾进行空字节填充。如果字符串包含的字节数大于N，将会抛出异常。可以用来保存一些例如手机号、IP地址这类等长的规范数据。

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

create table t_emp
(
x Enum8('male'=1, 'female'=2)
)
engine = tinylog;

注：tinylog是个简化的只存储数据的引擎，没有索引，一般用于测试。
插入数据测试并查看：

insert into t_emp values ('male'),('female'),('female');
select * from t_emp;
select cast(x,'Int8') from t_emp;

美剧类型容易带来较多的维护成本，需要谨慎使用。

数组类型
类型声明：array(T)，表示一个由T类型元素组成的数组。T可以是任意类型，甚至也可以是数组类型。但是不建议使用多位数组，clickhouse对多维数组的支持有限。例如在MergeTree引擎中就不能存储多维数组。

时间类型
clickhouse有三种时间类型：

Date 可接收一个年-月-日格式的字符串，例如'2022-06-15'。
Datetime 可接收一个年-月-日时:分:秒格式的字符串。例如'2022-06-15 10:58:10'。
Datetime64 可接收一个年-月-日时:分:秒.毫秒格式的字符串。例如'2022-06-15 10:58:10.232'。

可为空
绝大部分基础类型都可通过在前面添加一个Nullable()声明来允许接收Null空值。例如Nullable(Int8)类型的列可以存储Int8类型的值，没有值的行将存储NULL。
对int类型来说，插入空值会则会显示0（待验证）。
空值不能进行计算，结果仍为null（待验证）。
nullable的字段不能包含在索引当中。
并且nullable总是对性能产生影响，应避免使用nullable。例如对于字符串，可以使用空字符代替null。对于整型数据，可以用无意义的-1来表示null等。

表引擎

类比mysql的innodb和myisam，clickhouse也提供了非常多的引擎，而且种类更多功能更强。表引擎决定了一个表的所有数据属性，包括：

数据的存储方式和位置，写到哪里以及从哪读取数据
支持哪些查询以及如何支持
并发数据访问
索引的使用(如果存在)
是否可以执行多线程请求
数据复制参数

MergeTree类
适用于高负载任务的最通用和功能最强大的表引擎。这些引擎的共同特点是可以快速插入数据并进行后续的后台数据处理（OLAP）。
MergeTree系列引擎支持数据复制(使用Replicated*的引擎版本)，分区，和一些其他引擎不支持的功能。

MergeTree
ReplacingMergeTree
SummingMergeTree
AggregatingMergeTree
CollapsingMergeTree
VersionedCollaposingMergeTree
GraphiteMergeTree

日志类
具有最小功能的轻量级引擎。当需要快速写入许多小表(最多100万行)并在以后整体读取它们时，该类引擎是最有效的。
（没有索引、没有并发控制）

TinyLog
StripeLog
Log

集成引擎
用于与其他的数据存储与处理系统集成的引擎

Kafka
MySQL
ODBC
JDBC
HDFS

特定功能的引擎

Distributed
MaterializedView
Dictionary
Merge
File
Null
Set
Join
URL
View
Memory
Buffer

虚拟列
不要使用虚拟列，如果需要使用的话，提前处理好，放到表中作为一个字段。

MergeTree

MergeTree引擎是clickhouse的核心。
基于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,...]

主要通过engine指定表引擎，然后下面指定一些相关参数。
例：

create table t_stock(
id UInt32,
sku_id String,
total_amount Decimal(16,2),
create_time Datetime
) engine = MergeTree()
partition by to YYYYMMDD(create_time)
primary key (id)
order by (id,sku_id);

对于mergeTree，settings中的大部分参数都有默认值，可以不用设置。重点关注以下几个配置：partition by(分区键)，primary key(主键)以及order by(排序键)。

partition by 分区键
分区键作用：
主要是降低数据扫描的范围，优化查询速度。
例如上述例子，按时间进行分区，当查询的where条件中指定了日期条件，就只需要扫描对应日期的数据，而不用进行全表扫描了。
使用分区后，涉及到跨分区的查询操作，clickhouse将会以分区为单位进行并行处理。在clickhouse中这是一个可选项，如果不填相当于只用一个分区。
分区表的数据目录：
MergeTree引擎默认是以列文件+索引文件+表定义文件共同描述一个表。
这些文件都在clickhouse的本地数据磁盘当中（默认在/var/lib/clickhouse目录）。如果设定了分区，那么这些文件都会保存在不同的分区目录中。

如图所示，20200601是分区，第一个1是最小的分区块，第二个1是最大的分区块，（每个分区都包含一定的数据块），最后的0表示数据合并的次数。

进入一个分区目录查看，
data.bin文件就是实际存储数据的文件，因为列式存储所以压缩比高，
primary.idx是索引文件，
minmax_create_time.idx也是索引文件，
colums.txt是列信息，
count.txt是记录条目数，因此执行count的时候直接读取文件而不去扫描数据，所以很快，

分区合并：

optimize table t_stock final;

有时候如果不手动合并，依靠clickhouse自己合并会比较慢。但是optimize语句会引发大量读写，所以手动合并一般也是在业务不繁忙期。
过期的数据还在磁盘，clickhouse在大合并的时候才会删除。

order by 排序键
order by 排序键，指定分区内的数据按照哪些字段排序进行有序保存。这是MergeTree中唯一的一个必选项。
数据有序保存对于clickhouse底层的数据处理是相当重要的，在海量数据场景下，实现快速检索、去重、汇总等计算都离不开数据有序性的支持。这里需要注意的是，clickhouse的数据是分区内部有序的。因为clickhouse对于数据的处理就是以分区为最小维度的。
分区键的设置对于主键也是有影响的。在clickhouse中，若不设置主键，他就会以排序键来对数据进行检索等数据处理。这里需要注意，如果设置主键，主键必须是order by的前缀字段。例如order by排序键设置为(id,sku_id)，那么主键只能是id或者是(id,sku_id)。

primary key 主键
主键的作用是为了加快数据检索的。clickhouse中的主键与其他数据库有点不太一样，他并不要求主键的数据具有唯一性。

在clickhouse的metadata文件中保存的关于t_stock表的sql语句。而在那个sql文件当中，clickhouse在我们自定义的建表语句之后，加了一个默认参数index granularity，制定了值是8192。这是clickhouse中主键的一个重要作用。
index granularity即索引粒度，是指在稀疏索引中两个相邻索引对应数据的间隔。clickhouse给出的默认值是8192。官方不建议修改这个值，只有一种情况可能需要调整，那就是数据中有非常大重复值。

稀疏索引概念类似于redis中的跳表skiplist。也就是在构建索引时并不记录每一个主键的数值。而是按照一定的稀疏度，记录几个节点的索引数据。这些记录的数据，就保存在分区所在的数据目录中。
在一级索引基础上还可以建立二级索引。

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 to YYYYMMDD(create_time)
primary key (id)
order by (id,sku_id);

在total_amout列上，就设定了一个类型为minmax的二级索引（其他常见的索引类型还有bloomfliter），名字为secondlndex。建立二级索引的时候还指定了一个GRANULARITY参数，表示对一级索引进行聚合的粒度。

TTL 数据存活时间
即Time to live。可以指定行存储的持续时间。MergeTree可以针对表或者列声明数据存活时间。设置TTL需要指定一个表达式来表示数据的存活时间。表达式中必须存在至少一个表示时间的Date或DateTime类型的列。比如TTL date + Interval 1 DAY。也就是说说，存活时间必须跟数据相关联。

列级TTL：
可以在列上直接声明TTL规则。例如下面的语句就可以声明total_amount字段的存活时间为create_time创建时间后的10秒钟。

create table example_table
(
d datetime,
a int TTL d + interval 1 month,
b int TTL d + interval 1 month,
c string
)
engine = MergeTree
Partition by toYYYYMM(d)
order by d
;

当列中的值过期时，clickhouse会将他们替换成该列数据类型的默认值。如果某个数据块中列的所有值都过期了，那么clickhouse会从文件系统中的数据块中直接删除这一列。
列级TTL不能用于主键。

此外还有表级别的TTL，可以指定过期时的动作。默认行为是delete，即删除过期的行。

sample by 数据抽样
数据抽样同样用于大数据分析，可以极大提升数据分析的性能。采样修饰符只能用在MergeTree的表中才有效，并且抽样表达式指定的列，必须包含在主键中。进行了采样声明后，就可以在查询时进行采样查询。
例如，官方提供的测试数据集hits_v1，在表声明时指定了采样规则

sample by intHash32(UserID)

接下来就可以在查询时指定采样效率

select title,count(*) as pageviews
from hits_v1
sample 0.1 #代表采样10%的数据，也可以时具体的条数
where counterID=57
group by title
order by pageviews desc limit 1000

这个采样查询是在满足条件的结果集中随机抽取10%的数据。

ReplacingMergeTree

这个表引擎与MergeTree的不同之处在于他会删除排序值相同的重复项。这个去重功能在实际开发中用得很多，适用于在后台清除重复得数据以节省空间，但是它不能保证没有重复得数据出现，仍然需要optimize后或者依靠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],
...
) engine = ReplacnigMergeTree([ver])
[partition by expr]
[order by expr]
[sample by expr]
[settings name=value,...]

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

在ReplacingMergeTree(合并)去重的时候不会删这条数据，而是在原有基础上新增一条，只不过版本比较高。
clickhouse在设计中时没有锁的。

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

optimize table t_test final;

MergeTree是clickhouse最常用也是最强大的表引擎族，这一族表引擎提供了大数据下的数据快速插入、管理以及检索功能。
MergeTree一系列表引擎底层都使用类似LSM树的方式提供数据的快速读写功能，这跟HBase很像。，即新的数据(包括删改的数据)并不会影响原有数据，而是会记录在一个新开辟的临时数据块中。查询时会通过版本号查询最新的一条结果。新开辟的数据需要等到后台进行数据合并时才会进入主数据中。数据合并是在后台某个不确定的时间点进行，当然也可以手动触发数据合并。

四.Clickhouse的数据操作

（在clickhouse中列名是区分大小写的）

导入导出
使用clickhouse首先需要有数据。我们之前也通过insert into语句造了一些测试数据，但是这种方式，在clickhouse中是非常不推荐的。一方面，insert语句插入数据，效率太低。clickhouse是面向海量数据进行查询分析，insert语句很难用来形成海量的数据。另一方面，clickhouse最常用的MergeTree表引擎，会将新插入的数据放到一个临时的分区当中，后续需要进行数据合并。频繁的insert操作会产生大量的临时分区，增加数据合并的性能消耗。所以，clickhouse中通常情况下都是通过数据文件进行大批量的导出导入操作来产生的。
最常用的数据导入导出方式是通过clickhouse-client客户端写入或读取csv文件来完成。
例如导出数据到csv文件：

clickhouse-client -h 127.0.0.1 --database="defalut" --query="select * from t_stock FORMAT CSV" > t_stock.csv

从csv文件导入数据：

clickhouse-client -h 127.0.0.1 --database="default" --query="insert into t_stock FORMAT CSV" < ./test.csv

另外，官方也提供了一个clickhouse-copier工具来专门对clickhouse数据进行备份与恢复。
同时，官方也提供了大量高质量的数据集可供测试。因此我们需要将这些高质量的数据集导入到clickhouse中，这样对于学习clickhouse是非常方便高效的。
官方数据集参见：https://clickhouse.com/docs/zh/getting-started/example-datasets/
这里面有些非常庞大的数据集，比如GitHub Events数据集，包含了31亿行数据。数据包有75G，而clickhouse保存这些数据，需要硬盘空间超过200G。所以官方这些数据集对于测试，绝对是够用的。
最常用的数据集还是线上测试数据库中用到的数据，也就是Yandex.Metric Data数据集。数据集包含两张表hits_v1和visits_v1。数据集可以从官方网站上下载。参见 https://clickhouse.com/docs/zh/getting-started/example-datasets/metrica/ 。而这个官方文件的导入过程相当简单粗暴，那就是直接转移数据文件。

# 导入hits_v1表 
tar -xvf hits_v1.tar -C /var/lib/clickhouse
 # 导入vits_v1表
 tar -xvf visits_v1.tar -C /var/lib/clickhouse 
# 解压出来的文件分配给clickhouse用户 -- 可选 
chown -R clickhouse:clickhouse /var/lib/clickhouse 
# 重启clickhouse服务 
clickhouse restart

重启完成后，就可以在clickhouse中查到一个datasets数据以及hits_v1和visits_v1两张表。hits_v1表使用的是MergeTree引擎，拥有800W+的数据。visits_v1表使用的是CollapsingMergeTree引擎，拥有160W+的数据。
实际上这个导入的过程也给我们演示了clickhouse底层数据的文件结构。
clickhouse的底层文件结构相比其他数据库，也是非常的简单粗暴的。

元数据保存在metadata目录下。
datasets库对应 metadata目录下的 datasets.sql文件以及datasets目录
表名对应metadata/datasets目录下的.sql文件
数据保存在data目录下。
表数据就对应data目录下表名对应的文件夹中。
在表的数据目录 data/datasts/hits_v1/201403-10-18_2目录中，每个列对
应一个.bin文件和.mrk文件。

这里需要关注下的是metadata目录下的sql语句。例如查看hits_v1表的声明文
件：/var/lib/clickhouse/metadata/datasets/hits_v1.sql，看到他的内容如下:

ATTACH TABLE hits_v1
 ( 
WatchID UInt64, ... #省略字段名
 )
ENGINE = MergeTree() 
PARTITION BY toYYYYMM(EventDate) 
ORDER BY (CounterID, EventDate, intHash32(UserID)) 
SAMPLE BY intHash32(UserID) 
SETTINGS index_granularity = 8192

可以看到，就是通过一个简单的Attach语句直接将表信息加载到数据库中的。这个Attach指令并不在磁盘上实际产生数据，而是假设数据已经在正确的目录下了。这样通过执行Attach指令，clickhouse服务就将会识别对应表的存在。通过Attach语句，也可以加载默认目录以外的目录。通常是用在服务启动时加载数据信息。
而与Attach语句对应的就是DETACH语句，是将表信息进行解绑。

删改
在clickhouse中，对数据的修改和删除是非常"重"的操作，因为对应的目标数据需要放弃原有的分区，重建新的临时分区，然后还要进行大量的合并。在语句执行过程中，只是将原有的数据打上逻辑上的删除标记，然后新增数据放入新的分区。
直到触发分区合并的时候，才会删除旧的数据。频繁的update和delete操作会加大服务器的负担。
在clickhouse中，数据变更操作被称为Mutation查询，被作为alter指令的一部分，即对表进行变更。实际上官方文档就没有update和delete语句，通常这类Mutation操作都要交给运维人员来完成。普通用户只需要关注数据的查询和分析，尽量避免不必要的数据变更操作。

--删除
alter table t_stock delete where sku_id='sku_001';
--更新
alter table t_stock update total_amount=toDecimal132(2000.00,2) where id=2;

查询
标准SQL部分：

支持子查询
支持各种join，但不建议使用。因为join无法使用缓存。且clickhouse执行join的方式是将后面的表全部加载到内存中执行，优化不是很好。表较大时性能影响很明显。
支持with关键字创建临时表。例如：

with(
select sum(bytes)
from system.parts
where active
) as total_disk_usage
select (sum(bytes)/total_disk_usage) * 100 as table_disk_usage,
table
from system.parts
group by table
order by table_disk_usage desc
limit 10

另外，在group by操作上，clickhouse还支持with rollup\with cube\withtotals，来进行统计聚合。

-- 按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还提供了非常丰富的特性函数，可自行查阅。

五.集群

其实clickhouse的单机性能已经非常强了，底层的数据压缩效率是很高的。（不同于ES、hadoop上来就要求搭集群。）
clickhouse的集群主要有两个作用，一个是数据副本，另一个是分布式表。
其中数据副本相对比较重要，而分布式表大部分场景下是不需要的。例如之前看过官方提供的github evnets数据集30多亿的数据用200G硬盘就存下来了。
而且clickhouse应优先考虑单机，因为分布式表反而会消耗网络资源，降低查询速度。

数据副本

数据副本概述

clickhouse用zookeeper作为中间协调者，clickhouse副本是平等的，客户端既可以往副本a写，也可以往副本b写，客户端只管自己发出的请求返回结果。副本写入数据是有网络延时的，是异步复制，可能会出现丢失数据的情况。clickhouse只保证单个数据块的写入是原子的，而不能保证所有数据写入是原子的。一个数据块的大小可根据max_insert_block_size=1048576进行分块。对于replicatedMergeTree系列的表引擎，数据块写入时是会去重的，一个同样的insert语句多次重复执行但数据库块只会执行一次，这是为了防止用户重复插入数据或者网络波动等原因造成的数据重发。

数据副本搭建

1.启动一个zookeeper集群
如果没有配置zookeeper的话依然可以创建复制表，但是这些复制表都是只读。另外官方建议，不要在clickhouse的服务器上运行zookeeper，因为zookeeper对数据延时非常敏感，而clickhouse可能会占用所有可用的系统资源。
注意：当前版本的clickhouse要求zookeeper版本不低于3.4.5，并且官方对zookeeper的优化配置也给出了指导意见。
具体参考：https://clickhouse.com/docs/zh/operations/tips/

2.在clickhouse中进行配置
打开clickhouse的配置文件/etc/clickhouse-server/config.xml。在配置文件指定zookeeper集群地址，然后重启服务器。在730行左右找到标签进行配置(或者文档内直接搜/zookeeper，记得将配置的注释删掉)，例如：

然后clickhouse restart重启生效。

3.创建复制表
例：

create table t_stock_replicated
(
`id` UInt32,
`sku_id` String,
`total_amount` Decimal(16,2),
`create_time` DateTime
)
ENGINE = ReplicateMergeTree('/clickhouse/tables/t_stock','hadoop01')
PARTITION BY toYYYYMMDD(create_time)
PRIMARY KEY id
ORDER BY (id,sku_id);

在ReplicateMergeTree中有两个参数，前一个是zookeeper的路径，是要配置一样的；后一个是备份的名字，每台配置不同。

分布式表

仍然是通过zookeeper实现。
在xml文件中找到remote_servers，其中test_shard_localhost的位置是配置集群名字的；下面的每个shard是每个分片。
查看集群：

show clusters;

六.优化

配置优化

查询优化

参考：https://www.bilibili.com/video/BV1xg411w7AP?p=2