ClickHouse课件

列式存储数据库：hbase clickhouse

简介

ClickHouse入门

ClickHouse是俄罗斯的Yandex于2016年开源的列式存储数据库（DBMS），使用C++语言编写，主要用于在线分析处理查询（OLAP），能够使用SQL查询实时生成分析数据报告。

ClickHouse的特点

行式存储如图-1所示：

图-1 行式存储

列式存储如图-2所示：

图-2 列式存储

列式储存的好处

1)对于列的聚合，计数，求和等统计操作优于行式存储。

2)由于某一列的数据类型都是相同的，针对于数据存储更容易进行数据压缩，每一列选择更优的数据压缩算法，大大提高了数据的压缩比重。

3)由于数据压缩比更好，一方面节省了磁盘空间，另一方面对于 cache 也有了更大的发挥空间。

Clickhouse的特点

1)DBMS的功能：几乎覆盖了标准SQL的大部分语法，包括DDL和DML，以及配套的各种函数，用户管理及权限管理，数据的备份与恢复。

2)多样化引擎：ClickHouse和MySQL类似，把表级的存储引擎插件化，根据表的不同需求可以设定不同的存储引擎。目前包括合并树、日志、接口和其他四大类20多种引擎。

3)高吞吐写入能力：ClickHouse采用类LSM Tree的结构，数据写入后定期在后台Compaction。通过类LSM tree的结构，ClickHouse在数据导入时全部是顺序append写，写入后数据段不可更改，在后台compaction时也是多个段merge sort后顺序写回磁盘。顺序写的特性，充分利用了磁盘的吞吐能力，即便在HDD上也有着优异的写入性能。

官方公开benchmark测试显示能够达到50MB/s~200MB/s的写入吞吐能力，按照每行100Byte估算，大约相当于50W-200W条/s的写入速度。

4)数据分区与线程级并行：ClickHouse将数据划分为多个partition，每个partition再进一步划分为多个index granularity(索引粒度)，然后通过多个CPU核心分别处理其中的一部分来实现并行数据处理。在这种设计下，单条Query就能利用整机所有CPU。极致的并行处理能力，极大的降低了查询延时，但是多个表联表查询时效率就慢了。

对于大量数据的查询，ClickHouse能够将查询任务分割成多个小任务，实现并行处理，从而提高查询效率。这种能力使得ClickHouse在处理大规模数据时表现出色。然而，ClickHouse在处理高并发的低延迟查询方面可能存在一些挑战。

一项弊端是，如果单个查询需要利用多个CPU核心，这可能会降低ClickHouse同时处理多个查询的能力。由于ClickHouse会优先为单个查询分配多个CPU核心，这可能导致在高并发情况下效率下降，因为无法同时处理多个查询。

因此，对于高QPS（Query Per Second）的查询业务，ClickHouse可能并非最佳选择。在这种情况下，可能需要考虑其他针对高并发低延迟查询优化的解决方案。性能对比

某网站精华帖，中对几款数据库做了性能对比。

1)单表查询如图-3所示：

图-3 单表查询

2)关联查询如图-4所示：

图-4 关联查询

结论:ClickHouse像很多OLAP数 据库一样，单表查询速度优于关联查询，而且ClickHouse的两者差距更为明显。

ClickHouse 的安装

准备工作

1)确定防火墙处于关闭状态。

2)CentOS 取消打开文件数限制。

在hadoop101的/etc/security/limits.conf文件的末尾加入以下内容：

*也要写上

vim /etc/security/limits.conf

* soft nofile 65536

* hard nofile 65536

* soft nproc 131072

* hard nproc 131072

在hadoop101的/etc/security/limits.d/20-nproc.conf文件的末尾加入以下内容：

vim /etc/security/limits.d/20-nproc.conf

* soft nofile 65536

* hard nofile 65536

* soft nproc 131072

* hard nproc 131072

3)执行同步操作。

xsync /etc/security/limits.conf

Xsync /etc/security/limits.d/20-nproc.conf

4)安装依赖。

clickhouse依赖于c语言包

yum install -y libtool

用于c语言连接数据库

yum install -y *unixODBC*

在hadoop102、hadoop103上执行以上操作。

5)CentOS取消SELINUX。

修改/etc/selinux/config中的SELINUX=disabled：

vim /etc/selinux/config

SELINUX=disabled

6)执行同步操作。

xsync /etc/selinux/config

7)重启三台服务器。

单机安装

官网：Fast Open-Source OLAP DBMS - ClickHouse

下载地址：Index of /clickhouse/rpm/stable/x86_64/

1)在hadoop101的/opt/software下创建clickhouse目录。

mkdir clickhouse

2)将资料/ClickHouse下4个文件上传到hadoop101的software/clickhouse目录下，如图-5所示：

图-5 资料目录

3)将安装文件同步到hadoop102、hadoop103。

xsync clickhouse

4)分别在三台机子上安装这4个rpm文件。

rpm -ivh *.rpm

5)查看安装情况。

rpm -qa|grep clickhouse

6)修改配置文件。

vim /etc/clickhouse-server/config.xml

把<listen_host>::</listen_host> 的注释打开，这样的话才能让 ClickHouse 被除本机以外的服务器访问，如图-6所示：

图-6 单机模式

7)分发配置文件。

xsync /etc/clickhouse-server/config.xml

在这个文件中，有ClickHouse 的一些默认路径配置，比较重要的数据文件路径：<path>/var/lib/clickhouse/</path>。

日志文件路径：<log>/var/log/clickhouse-server/clickhouse-server.log</log>。

8)启动Server。

systemctl start clickhouse-server

9)三台机器上关闭开机自启。

systemctl disable clickhouse-server

10)使用client连接server，如图-7所示。

clickhouse-client -m

图-7 连接客户端

-m:可以在命令窗口输入多行命令。

数据类型

整型

固定长度的整型，包括有符号整型或无符号整型。

1)整型范围：

Int8 - [-128 : 127]

Int16 - [-32768 : 32767]

Int32 - [-2147483648 : 2147483647]

Int64 - [-9223372036854775808 : 9223372036854775807]

2)无符号整型范围：

UInt8 - [0 : 255]

UInt16 - [0 : 65535]

UInt32 - [0 : 4294967295]

UInt64 - [0 : 18446744073709551615]

使用场景：个数、数量、也可以存储型id。

浮点型

Float32 - float Float64 – double

建议尽可能以整数形式存储数据。例如，将固定精度的数字转换为整数值，如时间用毫秒为单位表示，因为浮点型进行计算时可能引起四舍五入的误差。

使用场景：一般数据值比较小，不涉及大量的统计计算，精度要求不高的时候。比如保存商品的重量。

布尔型

没有单独的类型来存储布尔值。可以使用UInt8类型，取值限制为0或1。

Decimal型

有符号的浮点数，可在加、减和乘法运算过程中保持精度。对于除法，最低有效数字会被丢弃（不舍入）。

有三种声明：

1)Decimal32(s)，相当于 Decimal(9-s,s)，有效位数为1~9。

2)Decimal64(s)，相当于 Decimal(18-s,s)，有效位数为1~18。

3)Decimal128(s)，相当于 Decimal(38-s,s)，有效位数为1~38。

使用场景：一般金额字段、汇率、利率等字段为了保证小数点精度，都使用Decimal进行存储。

字符串

1)String：字符串可以任意长度的。它可以包含任意的字节集，包含空字节。

2)FixedString(N)：固定长度N的字符串，N必须是严格的正自然数。当服务端读取长度小于N的字符串时候，通过在字符串末尾添加空字节来达到N字节长度。当服务端读取长度大于N的字符串时候，将返回错误消息。与String相比，极少会使用FixedString，因为使用起来不是很方便。

使用场景：名称、文字描述、字符型编码。固定长度的可以保存一些定长的内容，比如一些编码，性别等但是考虑到一定的变化风险，带来收益不够明显，所以定长字符串使用意义有限。

时间类型

目前ClickHouse有三种时间类型:

1)Date接受年-月-日的字符串比如‘2019-12-16’。

2)Datetime接受年-月-日 时:分:秒的字符串比如‘2019-12-16 20:50:10’。

3)Datetime64接受年-月-日 时:分:秒.亚秒的字符串比如‘2019-12-16 20:50:10.66’日期类型，用两个字节存储，表示从 1970-01-01 (无符号) 到当前的日期值。

还有很多数据结构，可以参考官方文档。

数组

Array(T)：由T类型元素组成的数组。

T可以是任意类型，包含数组类型。但不推荐使用多维数组，ClickHouse对多维数组的支持有限。例如，不能在MergeTree表中存储多维数组。

1)创建数组方式1，使用array函数。

array(T)

SELECT array(1, 2) AS x, toTypeName(x);

图-8 执行结果

2)创建数组方式2：使用方括号。

[]

SELECT [1, 2] AS x, toTypeName(x);

图-9 执行结果

表引擎

表引擎的使用

表引擎是ClickHouse的一大特色。可以说，表引擎决定了如何存储表的数据。包括：

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

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

3)并发数据访问。

4)索引的使用。

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

6)数据复制参数。

表引擎的使用方式就是必须显式在创建表时定义该表使用的引擎，以及引擎使用的相关参数。

特别注意：引擎的名称大小写敏感。

TinyLog

以列文件的形式保存在磁盘上，不支持索引，没有并发控制。一般保存少量数据的小表，生产环境上作用有限。可以用于平时练习测试用。

create table t_tinylog ( id String, name String) engine=TinyLog;

Memory

内存引擎，数据以未压缩的原始形式直接保存在内存当中，服务器重启数据就会消失。读写操作不会相互阻塞，不支持索引。简单查询下有非常非常高的性能表现（超过10G/s）。

一般用到它的地方不多，除了用来测试，就是在需要非常高的性能，同时数据量又不太大（上限大概1亿行）的场景。

MergeTree

ClickHouse 中最强大的表引擎当属MergeTree（合并树）引擎及该系列（*MergeTree）中的其他引擎，支持索引和分区，地位可以相当于innodb之于Mysql。而且基于MergeTree，还衍生出了很多家族成员，也是非常有特色的引擎。

1)建表语句。

create table t_order_mt( 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);

2)插入数据。

insert into t_order_mt 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_002',2000.00,'2020-06-01 13:00:00'),

(102,'sku_002',12000.00,'2020-06-01 13:00:00'),

(102,'sku_002',600.00,'2020-06-02 12:00:00');

1. 指定分区进行查询

SELECT * FROM t_order_mt WHERE create_time = toDateTime('2020-06-02 12:00:00');

MergeTree其实还有很多参数(绝大多数用默认值即可)，但是其中三个参数是非常重要的，也涉及了关于MergeTree的很多概念。

1)partition by 分区(可选)。

作用:学过hive的应该都不陌生，分区的目的主要是降低扫描的范围，优化查询速度。

如果不填:只会使用一个分区。

分区目录:MergeTree是以列文件+索引文件+表定义文件组成的，但是如果设定了分区那么这些文件就会保存到不同的分区目录中。

并行:分区后，面对涉及跨分区的查询统计，ClickHouse 会以分区为单位并行处理。

数据写入与分区合并:任何一个批次的数据写入都会产生一个临时分区，不会纳入任何一个已有的分区。写入后的某个时刻（大概 10-15 分钟后），ClickHouse会自动执行合并操作（等不及也可以手动通过 optimize 执行），把临时分区的数据，合并到已有分区中。

optimize table xxxx final;

例如：再次执行上面的插入操作。

insert into t_order_mt 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_002',2000.00,'2020-06-01 13:00:00'),

(102,'sku_002',12000.00,'2020-06-01 13:00:00'),

(102,'sku_002',600.00,'2020-06-02 12:00:00');

查看数据并没有纳入任何分区，如图-10所示：

图-10 执行结果

手动optimize之后：

optimize table t_order_mt final;

再次查询，如图-11所示：

图-11 执行结果

MergeTree中建表是必须有order by 。

主键的字段必须是order by 中的字段。 主键可以省略，主键可以重复。

分区可以自选是否分区。

ClickHouse中的索引默认是稀疏索引，默认8192行创建一个索引，查找使用二分查找

TTL 数据保存时长，超过则清空数据（原本数据1000，清空为0）， 一行一字段单独。

2)primary key主键(可选)。

ClickHouse中的主键，和其他数据库不太一样，它只提供了数据的一级索引，但是却不是唯一约束。这就意味着是可以存在相同primary key的数据的。

主键的设定主要依据是查询语句中的where条件。

根据条件通过对主键进行某种形式的二分查找，能够定位到对应的index granularity,避免了全表扫描。

index granularity： 直接翻译的话就是索引粒度，指在稀疏索引中两个相邻索引对应数据的间隔。ClickHouse 中的 MergeTree 默认是 8192。官方不建议修改这个值，除非该列存在大量重复值，比如在一个分区中几万行才有一个不同数据。

稀疏索引如图-12所示：

图-12 稀疏索引

稀疏索引的好处就是可以用很少的索引数据，定位更多的数据，代价就是只能定位到索引粒度的第一行，然后再进行进行一点扫描。

3)order by（必选）。

order by设定了分区内的数据按照哪些字段顺序进行有序保存。

order by是MergeTree中唯一一个必填项，甚至比primary key还重要，因为当用户不设置主键的情况，很多处理会依照order by的字段进行处理（比如后面会讲的去重和汇总）。

要求：主键必须是order by字段的前缀字段。

比如order by字段是(id,sku_id)，那么主键必须是id或者(id,sku_id)。

设置TTL的列必须是时间类型的列，且不能TTL的字段不能在order by之中

列级别TTL

创建测试表。

create table t_order_mt3( id UInt32,

sku_id String,

total_amount Decimal(16,2) TTL create_time+interval 10 SECOND,

create_time Datetime

) engine =MergeTree

partition by toYYYYMMDD(create_time) primary key (id)

order by (id, sku_id);

插入数据（注意：根据实际时间改变）。

insert into t_order_mt3 values (106,'sku_001',1000.00,'2023-09-20 15:15:30'),

(107,'sku_002',2000.00,'2023-09-20 16:23:30'),

(110,'sku_003',600.00,'2023-09-21 16:00:00');

手动合并，查看效果。到期后，指定的字段数据归0，如图-13所示：

图-13 执行结果

表级TTL

下面的这条语句是数据会在create_time之后10秒丢失。

alter table t_order_mt3 MODIFY TTL create_time + INTERVAL 10 SECOND;

涉及判断的字段必须是Date或者Datetime类型，推荐使用分区的日期字段。能够使用的时间周期：

SECOND

MINUTE

HOUR

DAY

WEEK

MONTH

QUARTER

YEAR

ReplacingMergeTree

ReplacingMergeTree是MergeTree的一个变种，它存储特性完全继承 MergeTree，只是多了一个去重的功能。尽管MergeTree可以设置主键，但是 primary key 其实没有唯一约束的功能。如果你想处理掉重复的数据，可以借助这个 ReplacingMergeTree。

1)去重时机：数据的去重只会在合并的过程中出现。合并会在未知的时间在后台进行，所以你无法预先作出计划。有一些数据可能仍未被处理。

2)去重范围：如果表经过了分区，去重只会在分区内部进行去重，不能执行跨分区的去重。所以 ReplacingMergeTree 能力有限， ReplacingMergeTree 适用于在后台清除重复的数据以节省空间，只会在各个分区内部进行去重，不能保证整个表中的数据都是唯一的。

去重是根据Order by 中的字段去重

案例演示

1)创建表。

create table t_order_rmt( 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);

ReplacingMergeTree()填入的参数为版本字段，重复数据保留版本字段值最大的。如果不填版本字段，默认按照插入顺序保留最后一条。

2)向表中插入数据。

insert into t_order_rmt 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_002',2000.00,'2020-06-01 13:00:00'),

(102,'sku_002',12000.00,'2020-06-01 13:00:00'),

(102,'sku_002',600.00,'2020-06-02 12:00:00');

3)执行第一次查询，结果如图-14所示。

select * from t_order_rmt;

图-14 执行结果

4)手动合并。

OPTIMIZE TABLE t_order_rmt FINAL;

5)再执行一次查询，结果如图-15所示。

select * from t_order_rmt;

图-15 执行结果

通过测试得到结论

1)去重不能跨分区。

2)只有同一批插入（新版本）或合并分区时才会进行去重。

3)认定重复的数据保留版本字段值最大的。

4)如果版本字段相同则按插入顺序保留最后一笔。

SummingMergeTree

对于不查询明细，只关心以维度进行汇总聚合结果的场景。如果只使用普通的MergeTree的话，无论是存储空间的开销，还是查询时临时聚合的开销都比较大。

ClickHouse为了这种场景，提供了一种能够“预聚合”的引擎SummingMergeTree。

案例演示

1)创建表。

create table t_order_smt( 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 );

2)插入数据。

insert into t_order_smt 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_002',2000.00,'2020-06-01 13:00:00'),

(102,'sku_002',12000.00,'2020-06-01 13:00:00'),

(102,'sku_002',600.00,'2020-06-02 12:00:00');

3)执行第一次查询，结果如图-16所示。

select * from t_order_smt;

图-16 执行结果

4)手动合并。

OPTIMIZE TABLE t_order_smt FINAL;

5)再执行一次查询，结果如图-17所示。

select * from t_order_smt;

图-17 执行结果

通过结果得到结论

1)以SummingMergeTree（）中指定的列作为汇总数据列。

2)可以填写多列必须数字列，如果不填，以所有非维度列且为数字列的字段为汇总数据列。

3)以order by的列为准，作为维度列。

4)其他的列按插入顺序保留第一行。

5)不在一个分区的数据不会被聚合。

6)只有在同一批次插入(新版本)或分片合并时才会进行聚合。

SQL 操作

基本上来说传统关系型数据库（以MySQL为例）的SQL语句，ClickHouse基本都支持，这里不会从头讲解SQL语法，只介绍ClickHouse与标准SQL（MySQL）不一致的地方。

Insert

基本与标准SQL（MySQL）一致。

1)标准。

insert into [table_name] values(…),(….)

2)从表到表的插入。

insert into [table_name] select a,b,c from [table_name_2]

Update 和 Delete

ClickHouse提供了Delete和Update的能力，这类操作被称为Mutation查询，它可以看做Alter的一种。

虽然可以实现修改和删除，但是和一般的OLTP数据库不一样，Mutation语句是一种很“重”的操作，而且不支持事务。

“重”的原因主要是每次修改或者删除都会导致放弃目标数据的原有分区，重建新分区。所以尽量做批量的变更，不要进行频繁小数据的操作。

1)删除操作。

alter table t_order_smt delete where sku_id ='sku_001';

2)修改操作。

alter table t_order_smt update total_amount=toDecimal32(2000.00,2) where id

=102;

由于操作比较“重”，所以Mutation语句分两步执行，同步执行的部分其实只是进行新增数据新增分区和并把旧分区打上逻辑上的失效标记。直到触发分区合并的时候，才会删除旧数据释放磁盘空间，一般不会开放这样的功能给用户，由管理员完成。

查询操作

ClickHouse基本上与标准SQL差别不大。

1)支持子查询。

2)支持CTE(Common Table Expression公用表表达式with子句)。

3)支持各种JOIN，但是JOIN操作无法使用缓存，所以即使是两次相同的JOIN语句，ClickHouse也会视为两条新SQL。

4)窗口函数(官方正在测试中...)。

5)不支持自定义函数。

alter 操作

同MySQL的修改字段基本一致。

1. 新增字段。
2. first  第一个字段

alter table tableName add column newcolname String after col1;

2)修改字段类型。

alter table tableName modify column colname NewType;

3)删除字段。

alter table tableName drop column newcolname;

导出数据

方式一：交互式

select * from tableName into outfile 'path/file'

方式二：非交互式

clickhouse-client  --database bdName -u default --password password --query='select * from tableName' > abc

更多支持格式参照：

Formats for Input and Output Data | ClickHouse Docs

第三方工具

简介

方法提供的第三方工具蛮多的，它们提供了一些ClickHouse的接口。它可以是可视化界面、命令行界面或API，比如：Client libraries、Integrations、GUI、Proxies等等。

默认用户：  密码无

需要设定端口号吗？

DataGrip的使用

这里简要说一下GUI工具，也很多，这里提一个DataGrip，和Idea都是同一个母公司——JetBrains开发出来了，大家可以下载尝试一下，比较简单。

下载地址：DataGrip: The Cross-Platform IDE for Databases & SQL by JetBrains

Clickhouse整合

操作原理也相对比较简单，就是基于jdbc的方式写入clickhouse即可。

Clickhouse整合spark

导入依赖：

<properties>
    <maven.compiler.source>1.8</maven.compiler.source>
    <maven.compiler.target>1.8</maven.compiler.target>
    <encoding>UTF-8</encoding>
    <scala.version>2.11.12</scala.version>
    <scala.compat.version>2.11</scala.compat.version>
    <flink.version>1.9.1</flink.version>
</properties>

<dependencies>
    <dependency>
        <groupId>org.apache.flink</groupId>
        <artifactId>flink-scala_2.11</artifactId>
        <version>${flink.version}</version>
    </dependency>
    <dependency>
        <groupId>org.apache.flink</groupId>
        <artifactId>flink-streaming-scala_2.11</artifactId>
        <version>${flink.version}</version>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.12</version>
    </dependency>
    <dependency>
        <groupId>ru.yandex.clickhouse</groupId>
        <artifactId>clickhouse-jdbc</artifactId>
        <version>0.2.4</version>
        <exclusions>
            <exclusion>
                <groupId>com.fasterxml.jackson.core</groupId>
                <artifactId>jackson-databind</artifactId>
            </exclusion>
        </exclusions>
    </dependency>
    <dependency>
        <groupId>org.apache.spark</groupId>
        <artifactId>spark-sql_2.11</artifactId>
        <version>2.4.7</version>
    </dependency>
</dependencies>
<build>

        <sourceDirectory>src/main/scala</sourceDirectory>

        <testSourceDirectory>src/test/scala</testSourceDirectory>

        <plugins>

            <!-- 指定编译scala的插件 -->

            <plugin>

                <groupId>net.alchim31.maven</groupId>

                <artifactId>scala-maven-plugin</artifactId>

                <version>3.2.2</version>

                <executions>

                    <execution>

                        <goals>

                            <goal>compile</goal>

                            <goal>testCompile</goal>

                        </goals>

                        <configuration>

                            <args>

                                <arg>-dependencyfile</arg>

                                <arg>${project.build.directory}/.scala_dependencies</arg>

                            </args>

                        </configuration>

                    </execution>

                </executions>

            </plugin>

            <plugin>

                <groupId>org.apache.maven.plugins</groupId>

                <artifactId>maven-surefire-plugin</artifactId>

                <version>2.18.1</version>

                <configuration>

                    <useFile>false</useFile>

                    <disableXmlReport>true</disableXmlReport>

                    <includes>

                        <include>**/*Test.*</include>

                        <include>**/*Suite.*</include>

                    </includes>

                </configuration>

            </plugin>

            <plugin>

                <groupId>org.apache.maven.plugins</groupId>

                <artifactId>maven-shade-plugin</artifactId>

                <version>2.3</version>

                <executions>

                    <execution>

                        <phase>package</phase>

                        <goals>

                            <goal>shade</goal>

                        </goals>

                        <configuration>

                            <filters>

                                <filter>

                                    <artifact>*:*</artifact>

                                    <excludes>

                                        <exclude>META-INF/*.SF</exclude>

                                        <exclude>META-INF/*.DSA</exclude>

                                        <exclude>META-INF/*.RSA</exclude>

                                    </excludes>

                                </filter>

                            </filters>

                            <transformers>

                                <transformer

                                        implementation="org.apache.maven.plugins.shade.resource.ManifestResourceTransformer">

                                    <mainClass>chapter7.SparkStreamingDriverHAOps</mainClass>

                                </transformer>

                            </transformers>

                        </configuration>

                    </execution>

                </executions>

            </plugin>

        </plugins>

    </build>

sparkcore方式

创建Person表：

create table person(id int, name varchar(30), age int) engine = TinyLog;

核心代码：

import java.sql.DriverManager
import org.apache.spark.{SparkConf, SparkContext}
case class Person(id: Int, name: String, age: Int)
object SparkCore2ClickHouseApp {
    def main(args: Array[String]): Unit = {
        val conf = new SparkConf()

.setMaster("local[*]")

.setAppName("SparkCore2ClickHouse")
        val sc = new SparkContext(conf)
        sc.setLogLevel("WARN")
        val persons = sc.parallelize(List(
                Person(1, "zhangsan", 13),
                Person(2, "lisi", 14),
                Person(3, "wangwu", 15),
                Person(4, "zhaoliu", 16),
                Person(5, "zhouqi", 17)))
        persons.foreachPartition(persons => {

//连接clickhouse用9000端口（什么时候用？本地client连接用，少），但是9000和hdfs的重复，之后同时用到时需要更改。 datagrip连接用8123啊
            val url = "jdbc:clickhouse://hadoop101:8123/default"
            val user = "default"
            val password = null
            val connection = DriverManager.getConnection(url, user, password)
            val sql = "insert into person values(?, ?, ?)"
            val ps = connection.prepareStatement(sql)
            persons.foreach(person => {
                ps.setInt(1, person.id)
                ps.setString(2, person.name)
                ps.setInt(3, person.age)
                ps.addBatch()})
            ps.executeBatch()
            ps.close()
            connection.close()
             })
        sc.stop()
         }
}

sparksql方式：

import org.apache.spark.SparkContext
import org.apache.spark.sql.{SaveMode, SparkSession}

object SparkSql2ClickHouseApp {
    def main(args: Array[String]): Unit = {
        val spark: SparkSession = SparkSession.builder()

.appName("SparkSql2ClickHouse")

.master("local[*]")

.getOrCreate()
        val sc: SparkContext = spark.sparkContext
        sc.setLogLevel("WARN")
        import spark.implicits._
        val persons = spark.createDataset(List(
        Person(6, "lilei", 23),
        Person(7, "hanmeimei", 24),
        Person(8, "xiaohong", 25)
        ) )

//sql写入模式的区别
        persons.write.mode(SaveMode.Append).format("jdbc")
        .option("url", "jdbc:clickhouse://hadoop101:8123/default")
        .option("user", "default")
        .option("dbtable", "person")
         //不加以下俩个配置，会报警告：WARN jdbc.JdbcUtils: Requested isolation level 1, but transactions are unsupported
        // 因为clickhouse不支持事务，所以可以关闭事务
        .option("isolationLevel", "NONE")
        //设置并发数为1，避免乱序
        .option("numPartitions", "1")
        .save()
        spark.stop()
    }
}

Clickhouse整合flink

自定义Sink：

import org.apache.flink.configuration.Configuration
import org.apache.flink.streaming.api.functions.sink.{RichSinkFunction, SinkFunction}
import java.sql.{Connection, DriverManager, PreparedStatement}

case class Person(id: Int, name: String, age: Int)
class ClickHouseSink extends RichSinkFunction[Person] {
    private val url = "jdbc:clickhouse://hadoop101:8123/default"
    private val user = "default"
    private val password: String = null
    private var connection:Connection = null
    private var ps:PreparedStatement = null
    override def open(parameters: Configuration): Unit = {
        connection = DriverManager.getConnection(url, user, password)
        val sql = "insert into person values(?, ?, ?)"
        ps = connection.prepareStatement(sql)
          }
    override def invoke(person: Person, context: SinkFunction.Context[_]): Unit = {
        ps.setInt(1, person.id)
        ps.setString(2, person.name)
        ps.setInt(3, person.age)
        ps.execute()
          }
    override def close(): Unit = {
        ps.close()
        connection.close()
    }
}

核心代码：

import org.apache.flink.streaming.api.scala._

object Flink2ClickhouseApp {
    def main(args: Array[String]): Unit = {
        val env = StreamExecutionEnvironment.getExecutionEnvironment
        val inputs = env.fromCollection(List(
            Person(9, "jack", 17),
            Person(10, "tom", 18),
            Person(11, "lucy", 19)))
        inputs.addSink(new ClickHouseSink())
        env.execute("Flink2ClickhouseApp")
    }
}

副本

副本的目的主要是保障数据的高可用性，即使一台ClickHouse节点宕机，那么也可以从其他服务器获得相同的数据。

Data Replication | ClickHouse Docs

副本写入流程

写入流程如图-18所示:

图-18 写入流程

配置步骤

1)启动zookeeper集群。

2)在hadoop101的/etc/clickhouse-server/config.d目录下创建一个名为metrika.xml的配置文件，内容如下：

注：也可以不创建外部文件，直接在config.xml中指定<zookeeper>。

<?xml version="1.0"?>

<yandex>

<zookeeper-servers>

<node index="1">

<host>hadoop101</host>

<port>2181</port>

</node>

<node index="2">

<host>hadoop102</host>

<port>2181</port>

</node>

<node index="3">

<host>hadoop103</host>

<port>2181</port>

</node>

</zookeeper-servers>

</yandex>

3)同步到hadoop102和hadoop103上。

xsync /etc/clickhouse-server/config.d/metrika.xml

4)在hadoop101的/etc/clickhouse-server/config.xml中增加。

<zookeeper incl="zookeeper-servers" optional="true" />

<include_from>/etc/clickhouse-server/config.d/metrika.xml</include_from>

图-19 配置文件

5)同步到hadoop102和hadoop103上。

xsync /etc/clickhouse-server/config.xml

6)分别在hadoop102和hadoop103上启动ClickHouse服务。

clickhouse restart

注意：因为修改了配置文件，如果以前启动了服务需要重启。

注意：我们演示副本操作只需要在hadoop101和hadoop102两台服务器即可，上面的操作，我们hadoop103可以你不用同步，我们这里为了保证集群中资源的一致性，做了同步。

7)在hadoop101和hadoop102上分别建表。

副本只能同步数据，不能同步表结构，所以我们需要在每台机器上自己手动建表。

hadoop101：

create table t_order_re ( id UInt32,

sku_id String,

total_amount Decimal(16,2),

create_time Datetime

) engine =ReplicatedMergeTree('/clickhouse/table/01/t_order','rep_001') partition by toYYYYMMDD(create_time)

primary key (id) order by (id,sku_id);

hadoop102：

create table t_order_re ( id UInt32,

sku_id String,

total_amount Decimal(16,2),

create_time Datetime

) engine =ReplicatedMergeTree('/clickhouse/table/01/t_order','rep_002') partition by toYYYYMMDD(create_time)

primary key (id) order by (id,sku_id);

参数解释

ReplicatedMergeTree中，第一个参数是分片的zk_path一般按照：/clickhouse/table/{shard}/{table_name}的格式写，如果只有一个分片就写 01 即可。

第二个参数是副本名称，相同的分片副本名称不能相同。

在hadoop101上执行insert语句，结果如图-20所示：

insert into t_order_re values (101,'sku_001',1000.00,'2020-06-01 12:00:00'),

(102,'sku_002',2000.00,'2020-06-01 12:00:00'),

(103,'sku_004',2500.00,'2020-06-01 12:00:00'),

(104,'sku_002',2000.00,'2020-06-01 12:00:00'),

(105,'sku_003',600.00,'2020-06-02 12:00:00');

图-20 插入数据

在hadoop102上执行select，可以查询出结果，如图-21所示，说明副本配置正确。

图-21 执行结果

分片集群

副本虽然能够提高数据的可用性，降低丢失风险，但是每台服务器实际上必须容纳全量数据，对数据的横向扩容没有解决。

要解决数据水平切分的问题，需要引入分片的概念。通过分片把一份完整的数据进行切分，不同的分片分布到不同的节点上，再通过Distributed表引擎把数据拼接起来一同使用。

Distributed表引擎本身不存储数据，有点类似于 MyCat 之于 MySql，成为一种中间件， 通过分布式逻辑表来写入、分发、路由来操作多台节点不同分片的分布式数据。

注意：ClickHouse的集群是表级别的，实际企业中，大部分做了高可用，但是没有用分片，避免降低查询性能以及操作集群的复杂性。

集群写入流程（3 分片 2 副本共 6 个节点）

写入流程如图-22所示：

图-22 写入流程

集群读取流程（3 分片 2 副本共 6 个节点）

读取流程如图-23所示：

图-23 读取流程

集群搭建

数据副本需要是指定引擎，否则还是单机模式。

配置的位置可以在之前的/etc/clickhouse-server/config.d/metrika.xml，内容如下。

注：也可以不创建外部文件，直接在config.xml的<remote_servers>中指定。

hadoop101配置如下：

<yandex>

    ...省略...

    <listen_host>::</listen_host>

    <remote_servers>

        <perftest_3shards_1replicas>

            <shard>

                <replica>

                    <host>hadoop101</host>

                    <port>9000</port>

                </replica>

            </shard>

            <shard>

                <replica>

                    <host>hadoop102</host>

                    <port>9000</port>

                </replica>

            </shard>

            <shard>

                <replica>

                    <host>hadoop103</host>

                    <port>9000</port>

                </replica>

            </shard>

        </perftest_3shards_1replicas>

    </remote_servers>

    <!-- zookeeper相关配置 -->

    <zookeeper-servers>

        <node>

            <host>hadoop101</host>

            <port>2181</port>

        </node>

        <node>

            <host>hadoop102</host>

            <port>2181</port>

        </node>

        <node>

            <host>hadoop103</host>

            <port>2181</port>

        </node>

    </zookeeper-servers>

    <!-- 复制标识的配置，也称为宏配置，这里唯一标识一个副本名称，每个实例都要配置并且都是唯一的 -->

    <macros>

<shard>01</shard>

<replica>01</replica>

    </macros>  

    ...省略...    

</yandex>

hadoop102配置如下：

<yandex>

    ...省略...

    <listen_host>::</listen_host>

    <remote_servers>

        <perftest_3shards_1replicas>

            <shard>

                <replica>

                    <host>hadoop101</host>

                    <port>9000</port>

                </replica>

            </shard>

            <shard>

                <replica>

                    <host>hadoop102</host>

                    <port>9000</port>

                </replica>

            </shard>

            <shard>

                <replica>

                    <host>hadoop103</host>

                    <port>9000</port>

                </replica>

            </shard>

        </perftest_3shards_1replicas>

    </remote_servers>

    <!-- zookeeper相关配置 -->

    <zookeeper-servers>

        <node>

            <host>hadoop101</host>

            <port>2181</port>

        </node>

        <node>

            <host>hadoop102</host>

            <port>2181</port>

        </node>

        <node>

            <host>hadoop103</host>

            <port>2181</port>

        </node>

    </zookeeper-servers>

    <!-- 复制标识的配置，也称为宏配置，这里唯一标识一个副本名称，每个实例都要配置并且都是唯一的 -->

    <macros>

<shard>02</shard>

<replica>01</replica>

    </macros>  

    ...省略...    

</yandex>

hadoop103配置如下：

    <remote_servers>

        <perftest_3shards_1replicas>

            <shard>

                <replica>

                    <host>hadoop101</host>

                    <port>9000</port>

                </replica>

            </shard>

            <shard>

                <replica>

                    <host>hadoop102</host>

                    <port>9000</port>

                </replica>

            </shard>

            <shard>

                <replica>

                    <host>hadoop103</host>

                    <port>9000</port>

                </replica>

            </shard>

        </perftest_3shards_1replicas>

    </remote_servers>

    <!-- zookeeper相关配置 -->

<zookeeper-servers>

        <node>

            <host>hadoop101</host>

            <port>2181</port>

        </node>

        <node>

            <host>hadoop102</host>

            <port>2181</port>

        </node>

        <node>

            <host>hadoop103</host>

            <port>2181</port>

        </node>

    </zookeeper-servers>

    <!-- 复制标识的配置，也称为宏配置，这里唯一标识一个副本名称，每个实例都要配置并且都是唯一的 -->

    <macros>

<shard>03</shard>

<replica>01</replica>

    </macros>