[数据库] clickhouse

0. 参考资料

[1] 安装与使用
[2] 简书
[3] 基础知识
[4] 深度揭秘
[5] 教程
[6] ClickHouse SQL 的十项优化规则

1. 简介

clickhouse 是俄罗斯的“百度”Yandex公司在2016年开源的，ClickHouse®是面向列的数据库管理系统（DBMS），用于对查询进行联机分析处理（OLAP）。一款针对大数据实时分析的高性能分布式数据库，与之对应的有hadoop生态hive，Vertica和百度出品的palo。这是战斗民族继nginx后，有开源的一款“核武器”。据官方介绍，clickhouse 有以下优点：

1. 面向列式
2. 数据压缩
3. 多核并行处理
4. 分布式部署
5. 支持sql
6. 向量引擎
7. 实时写入
8. 异步复制

OLAP(数据分析)场景的特点
读多于写
不同于事务处理（OLTP）的场景，比如电商场景中加购物车、下单、支付等需要在原地进行大量insert、update、delete操作，数据分析（OLAP）场景通常是将数据批量导入后，进行任意维度的灵活探索、BI工具洞察、报表制作等。数据一次性写入后，分析师需要尝试从各个角度对数据做挖掘、分析，直到发现其中的商业价值、业务变化趋势等信息。这是一个需要反复试错、不断调整、持续优化的过程，其中数据的读取次数远多于写入次数。这就要求底层数据库为这个特点做专门设计，而不是盲目采用传统数据库的技术架构。

大宽表，读大量行但是少量列，结果集较小
在OLAP场景中，通常存在一张或是几张多列的大宽表，列数高达数百甚至数千列。对数据分析处理时，选择其中的少数几列作为维度列、其他少数几列作为指标列，然后对全表或某一个较大范围内的数据做聚合计算。这个过程会扫描大量的行数据，但是只用到了其中的少数列。而聚合计算的结果集相比于动辄数十亿的原始数据，也明显小得多。

数据批量写入，且数据不更新或少更新
OLTP类业务对于延时（Latency）要求更高，要避免让客户等待造成业务损失；而OLAP类业务，由于数据量非常大，通常更加关注写入吞吐（Throughput），要求海量数据能够尽快导入完成。一旦导入完成，历史数据往往作为存档，不会再做更新、删除操作。

无需事务，数据一致性要求低
OLAP类业务对于事务需求较少，通常是导入历史日志数据，或搭配一款事务型数据库并实时从事务型数据库中进行数据同步。多数OLAP系统都支持最终一致性。

灵活多变，不适合预先建模
分析场景下，随着业务变化要及时调整分析维度、挖掘方法，以尽快发现数据价值、更新业务指标。而数据仓库中通常存储着海量的历史数据，调整代价十分高昂。预先建模技术虽然可以在特定场景中加速计算，但是无法满足业务灵活多变的发展需求，维护成本过高。

2. 安装

2.1 centos安装

centos7安装部署clickhouse集群

2.1.1 检查操作系统是否支持

$ grep -q sse4_2 /proc/cpuinfo && echo "SSE 4.2 supported" || echo "SSE 4.2 not supported"

显示：SSE 4.2 supported，为支持。

2.2.2 开始安装

$ yum install yum-utils

$ rpm --import https://repo.yandex.ru/clickhouse/CLICKHOUSE-KEY.GPG

$ yum-config-manager --add-repo https://repo.yandex.ru/clickhouse/rpm/stable/x86_64

$ yum install clickhouse-server clickhouse-client

3. 配置

https://clickhouse.tech/docs/zh/interfaces/cli/
https://blog.csdn.net/weixin_39025362/article/details/107520523

3.1.目录结构

• /etc/clickhouse-server：服务端的配置文件目录，包括全局配置 config.xml 和用户配置 users.xml
• /var/lib/clickhouse：默认的数据存储目录，如果是生产环境可以将其修改到空间较大的磁盘挂载路径。可以通过修改
• /etc/clickhouse-server/config.xml 配置文件中 、<tmp_path> 和<user_files_path> 标签值来设置。
• /var/log/clickhouse-server：默认的日志保存目录。同样可以通过修改/etc/clickhouse-server/config.xml 配置文件中 和 标签值来设置。
• /etc/cron.d/clickhouse-server：clickhouse server 的一个定时配置，用于恢复因异常中断的ClickHouse 服务进程。
• ~/.clickhouse-client-history (隐藏文件) 所有通过交互式命令行执行的sql历史记录。可使用ll -a命令查看

3.2.命令行参数

clickhouse-client -h localhost -d default -m -u default --password 

• –host, -h -– 服务端的host名称, 默认是localhost。您可以选择使用host名称或者IPv4或IPv6地址。
• –port – 连接的端口，默认值：9000。注意HTTP接口以及TCP原生接口使用的是不同端口。
• –user, -u – 用户名。 默认值：default。
• –password – 密码。 默认值：空字符串。
• –query, -q – 使用非交互模式查询。
• –database, -d – 默认当前操作的数据库. 默认值：服务端默认的配置（默认是default）。
• –multiline, -m – 如果指定，允许多行语句查询（Enter仅代表换行，不代表查询语句完结）。
• –multiquery, -n – 如果指定, 允许处理用;号分隔的多个查询，只在非交互模式下生效。
• –format, -f – 使用指定的默认格式输出结果。
• –vertical, -E – 如果指定，默认情况下使用垂直格式输出结果。这与–format=Vertical相同。在这种格式中，每个值都在单独的行上打印，这种方式对显示宽表很有帮助。
• –time, -t – 如果指定，非交互模式下会打印查询执行的时间到stderr中。
• –stacktrace – 如果指定，如果出现异常，会打印堆栈跟踪信息。
• –config-file – 配置文件的名称。
• –secure – 如果指定，将通过安全连接连接到服务器。
• –history_file — 存放命令历史的文件的路径。
• –param_ — 查询参数配置查询参数.

3.3.配置文件

clickhouse-client使用以下第一个配置文件：
通过–config-file参数指定。

• ./clickhouse-client.xml
• ~/.clickhouse-client/config.xml
• /etc/clickhouse-client/config.xml

配置文件示例:

<config>
    <user>username</user>
    <password>password</password>
    <secure>False</secure>
</config>

4. 常用语句

clickhouse 基础知识

4.1.创建操作

-- 创建数据库
create database test;

-- 创建第一个表
create /*temporary*/ table /*if not exists*/ test.m1 (
 id UInt16
,name String
) ENGINE = Memory
;

create table "test.m2" (
 a MATERIALIZED (b+1)
,b UInt16
) ENGINE = Memory;
insert into "test.m2" (b) values (1);
select * from "test.m2";
select a, b from "test.m2";

4.2.查询操作

SHOW DATABASES  -- 展示所有数据库
SHOW TABLES -- 展示数据库里所有表格
SHOW CREATE TABLE replacing_test -- 显示创建表replacing_test的SQL
show create database test_db -- 查看数据库test_db建库语句（详情）
desc table replacing_test -- 显示每个字段的名称及类型

4.3.插入操作

-- 连接MySql
CREATE DATABASE phenglei_dev ENGINE = MySQL('10.0.1.58:3306', 'phenglei_dev', 'lambdacal', 'yskj2407')    

-- 删除一个表
drop table if exists test.t1;

-- 插入测试数据,test.m1要加引号
insert into "test.m1" (id, name) values (1, 'abc'), (2, 'bbbb');

-- 物化列,指定 MATERIALIZED 表达式，即将一个列作为物化列处理了，这意味着这个列的值不能从insert 语句获取，只能是自己计算出来的。同时，
drop table if exists "test.m2";
create table "test.m2" (
 a MATERIALIZED (b+1)
,b UInt16
) ENGINE = Memory;
insert into "test.m2" (b) values (1);
select * from "test.m2";
select a, b from "test.m2";

4.4.表达式列

ALIAS 表达式列某方面跟物化列相同，就是它的值不能从 insert 语句获取。不同的是， 物化列 是会真正保存数据（这样查询时不需要再计算），
而表达式列不会保存数据（这样查询时总是需要计算），只是在查询时返回表达式的结果。

create table "test.m3" (a ALIAS (b+1), b UInt16) ENGINE = Memory;
insert into "test.m3"(b) values (1);
select * from "test.m3";
select a, b from "test.m3";
查询

SELECT
    StartURL AS URL,
    AVG(Duration) AS AvgDuration
FROM tutorial.visits_v1
WHERE StartDate BETWEEN '2014-03-23' AND '2014-03-30'
GROUP BY URL
ORDER BY AvgDuration DESC
LIMIT 10


SELECT
    sum(Sign) AS visits,
    sumIf(Sign, has(Goals.ID, 1105530)) AS goal_visits,
    (100. * goal_visits) / visits AS goal_percent
FROM tutorial.visits_v1
WHERE (CounterID = 912887) AND (toYYYYMM(StartDate) = 201403) AND (domain(StartURL) = 'yandex.ru')

5. 引擎

mergeTree子引擎

5.1.Merge

一个工具引擎，本身不保存数据，只用于把指定库中的指定多个表链在一起。
这样，读取操作可以并发执行，同时也可以利用原表的索引，但是，此引擎不支持写操作
指定引擎的同时，需要指定要链接的库及表，库名可以使用一个表达式，表名可以使用正则表达式指定

optimize table test.mergetree -- 后台自动merge或通过optimize table 
rmt_tab命令手动Merge后查询

5.1.1 MergeTree

它支持一个日期和一组主键的两层式索引，还可以实时更新数据。同时，索引的粒度可以自定义，外加直接支持采样功能

MergeTree(EventDate, (CounterID, EventDate), 8192)
MergeTree(EventDate, intHash32(UserID), (CounterID, EventDate, intHash32(UserID)), 8192)

EventDate 一个日期的列名；intHash32(UserID) 采样表达式；(CounterID, EventDate) 主键组（里面除了列名，也支持表达式），也可以是一个表达式；8192 主键索引的粒度

5.1.2 ReplacingMergeTree

该引擎和MergeTree的不同之处在于它会删除具有相同主键的重复项。数据的去重只会在合并的过程中出现。合并会在未知的时间在后台进行，所以你无法预先作出计划。有一些数据可能仍未被处理。因此，ReplacingMergeTree适用于在后台清除重复的数据以节省空间，但是它不保证没有重复的数据出现。同时ReplacingMergeTree在一定程度上可以弥补clickhouse不能对数据做更新的操作。

ReplacingMergeTree 在最后加一个"版本列",它跟时间列配合一起，用以区分哪条数据是"新的"，并把旧的丢掉(这个过程是在 merge 时处理，不是数据写入时就处理了的，平时重复的数据还是保存着的，并且查也是跟平常一样会查出来的)

CREATE TABLE rmt_tab(
	date Date,
	id UInt8,
	name String,
	version UInt8
) ENGINE=ReplacingMergeTree(version) 
PARTITION BY date 
ORDER BY (id,name) 
SAMPLE BY name

insert into rmt_tab values ('2019-07-11',1,'Jason',1);
insert into rmt_tab values ('2019-07-11',1,'Jason',1);
insert into rmt_tab values ('2019-07-11',1,'Jason',2);
insert into rmt_tab values ('2019-07-12',2,'Tom',1);
insert into rmt_tab values ('2019-07-12',2,'Tom',1);
insert into rmt_tab values ('2019-07-12',2,'Tom',2);

optimize table rmt_tab

5.1.3 SummingMergeTree

当合并 SummingMergeTree 表的数据片段时，ClickHouse 会把所有具有相同主键的行合并为一行，该行包含了被合并的行中具有数值数据类型的列的汇总值。如果主键的组合方式使得单个键值对应于大量的行，则可以显著的减少存储空间并加快数据查询的速度，对于不可加的列，会取一个最先出现的值

SummingMergeTree 就是在 merge 阶段把数据sum求和；sum求和的列可以指定，不可加的未指定列，会取一个最先出现的值

CREATE TABLE smt_tab(
	date Date,
	id UInt8,
	name String,
	a UInt16
) ENGINE=SummingMergeTree(a) 
PARTITION BY date 
ORDER BY (id,name) 
SAMPLE BY name

insert into smt_tab (date,id,name,a) values ('2019-12-12',1,'Jason',1)
insert into smt_tab (date,id,name,a) values ('2019-12-12',1,'Jason',2)
insert into smt_tab (date,id,name,a) values ('2019-12-12',1,'Jason',3)
insert into smt_tab (date,id,name,a) values ('2019-12-12',1,'wzz',3)
insert into smt_tab (date,id,name,a) values ('2019-12-12',2,'Jason',3)
insert into smt_tab (date,id,name,a) values ('2019-12-12',1,'Jason',4)
insert into smt_tab (date,id,name,a) values ('2020-12-12',1,'Jason',5)
insert into smt_tab (date,id,name,a) values ('2020-12-12',1,'Jason',6)

select * from smt_tab
optimize table smt_tab

5.1.4 AggregatingMergeTree

该引擎继承自 MergeTree，并改变了数据片段的合并逻辑。 ClickHouse 会将相同主键的所有行（在一个数据片段内）替换为单个存储一系列聚合函数状态的行。
可以使用 AggregatingMergeTree 表来做增量数据统计聚合，包括物化视图的数据聚合。
引擎需使用 AggregateFunction 类型来处理所有列。
如果要按一组规则来合并减少行数，则使用 AggregatingMergeTree 是合适的。
对于AggregatingMergeTree不能直接使用insert来查询写入数据。一般是用insert select。但更常用的是创建物化视图

drop table if exists test.aggregatingmergetree;
create table test.aggregatingmergetree(
	sdt Date, 
	dim1 String, 
	dim2 String, 
	dim3 String, 
	measure1 UInt64
) ENGINE=MergeTree(sdt, (sdt, dim1, dim2, dim3), 8192);

-- 创建一个物化视图，使用 AggregatingMergeTree
drop table if exists test.aggregatingmergetree_view;

create materialized view test.aggregatingmergetree_view
ENGINE = AggregatingMergeTree(sdt,(dim2, dim3), 8192)
as
select sdt,dim2, dim3, count(measure1) as uv
from test.aggregatingmergetree
group by sdt,dim2, dim3;

insert into test.aggregatingmergetree (sdt, dim1, dim2, dim3, measure1) values ('2018-06-10', 'aaaa', 'a', '10', 1);
insert into test.aggregatingmergetree (sdt, dim1, dim2, dim3, measure1) values ('2018-06-10', 'aaaa', 'a', '10', 2);
insert into test.aggregatingmergetree (sdt, dim1, dim2, dim3, measure1) values ('2018-06-10', 'aaaa', 'b', '20', 3);
insert into test.aggregatingmergetree (sdt, dim1, dim2, dim3, measure1) values ('2018-06-10', 'bbbb', 'b', '30', 4);
insert into test.aggregatingmergetree (sdt, dim1, dim2, dim3, measure1) values ('2018-06-10', 'cccc', 'b', '20', 5);
insert into test.aggregatingmergetree (sdt, dim1, dim2, dim3, measure1) values ('2018-06-10', 'cccc', 'c', '10', 6);
insert into test.aggregatingmergetree (sdt, dim1, dim2, dim3, measure1) values ('2018-06-10', 'dddd', 'c', '20', 7);
insert into test.aggregatingmergetree (sdt, dim1, dim2, dim3, measure1) values ('2018-06-10', 'dddd', 'a', '10', 8);

select * from test.aggregatingmergetree
select * from test.aggregatingmergetree_view

-- 按 dim2 和 dim3 聚合 count(measure1)
select dim2, dim3, count(measure1) from test.aggregatingmergetree group by dim2, dim3;

-- 按 dim2 聚合 UV
select dim2, uniq(dim1) from test.aggregatingmergetree group by dim2;

-- 手动触发merge
OPTIMIZE TABLE test.aggregatingmergetree_view;
select * from test.aggregatingmergetree_view;

-- 查 dim2 的 uv
select dim2, uniqMerge(uv) from test.aggregatingmergetree_view group by dim2 order by dim2;

5.1.5 CollapsingMergeTree

CollapsingMergeTree 会异步的删除（折叠）这些除了特定列 Sign 有 1 和 -1 的值以外，其余所有字段的值都相等的成对的行。没有成对的行会被保留。该引擎可以显著的降低存储量并提高 SELECT 查询效率。
CollapsingMergeTree引擎有个状态列sign，这个值1为”状态”行，-1为”取消”行，对于数据只关心状态列为状态的数据，不关心状态列为取消的数据

使用场景:

大数据中对于数据更新很难做到，比如统计一个网站或TV的在用户数，更多场景都是选择用记录每个点的数据，再对数据进行一定聚合查询。而clickhouse通过CollapsingMergeTree就可以实现，所以使得CollapsingMergeTree大部分用于OLAP场景

CREATE TABLE cmt_tab(
	sign Int8,
	date Date,
	name String,
	point String
) ENGINE=CollapsingMergeTree(sign)
 PARTITION BY date 
 ORDER BY (name) 
 SAMPLE BY name

insert into cmt_tab(sign,date,name,point) values (1,'2019-12-13','cctv','100000')
insert into cmt_tab(sign,date,name,point) values (-1,'2019-12-13','cctv','100000')
insert into cmt_tab(sign,date,name,point) values (1,'2019-12-13','hntv','10000')
insert into cmt_tab(sign,date,name,point) values (-1,'2019-12-13','hntv','10000')
insert into cmt_tab(sign,date,name,point) values (1,'2019-12-13','hbtv','11000')
insert into cmt_tab(sign,date,name,point) values (-1,'2019-12-13','hbtv','11000')
insert into cmt_tab(sign,date,name,point) values (1,'2019-12-14','cctv','200000')
insert into cmt_tab(sign,date,name,point) values (1,'2019-12-14','hntv','15000')
insert into cmt_tab(sign,date,name,point) values (1,'2019-12-14','hbtv','16000')

select * from cmt_tab

5.2.TinyLog

最简单的一种引擎，每一列保存为一个文件，里面的内容是压缩过的，不支持索引
这种引擎没有并发控制，所以，当你需要在读，又在写时，读会出错。并发写，内容都会坏掉。

应用场景:
a. 基本上就是那种只写一次
b. 然后就是只读的场景。
c. 不适用于处理量大的数据，官方推荐，使用这种引擎的表最多 100 万行的数据

drop table if exists test.tinylog;
create table test.tinylog (a UInt16, b UInt16) ENGINE = TinyLog;
insert into test.tinylog(a,b) values (7,13);

此时/var/lib/clickhouse/data/test/tinylog保存数据的目录结构：

├── a.bin
├── b.bin
└── sizes.json

a.bin 和 b.bin 是压缩过的对应的列的数据， sizes.json 中记录了每个 *.bin 文件的大小

5.3.Memory

内存引擎，数据以未压缩的原始形式直接保存在内存当中，服务器重启数据就会消失
可以并行读，读写互斥锁的时间也非常短
不支持索引，简单查询下有非常非常高的性能表现

应用场景:
a. 进行测试
b. 在需要非常高的性能，同时数据量又不太大（上限大概 1 亿行）的场景

6. 物化视图

ClickHouse物化视图

-- 创建cpu搜集表
drop table if exists cpu;
CREATE TABLE cpu(
    user_rate MATERIALIZED (user/total),
	date Date,
	node_name String,
	node_ip String,
	user Float64,
	free Float64,
    total Float64
) ENGINE MergeTree() 
PARTITION BY date 
ORDER BY (node_name,node_ip);


-- 创建物化视图，计算每个时间的总使用量
drop table if exists record;
CREATE MATERIALIZED VIEW record
ENGINE = SummingMergeTree() 
PARTITION BY date ORDER BY (date)
POPULATE AS
SELECT * FROM cpu;


-- 统计节点名称列表
drop table if exists node;
CREATE MATERIALIZED VIEW node
ENGINE = ReplacingMergeTree()
PARTITION BY node_name
 ORDER BY (node_name)   
POPULATE AS
SELECT node_name FROM cpu;


show tables


insert into cpu (date, node_name,node_ip,user,free,total) values ('2021-01-27', '机器a', '10.0.1.144',2,3,5);
insert into cpu (date, node_name,node_ip,user,free,total) values ('2021-01-27', '机器a', '10.0.1.144',3,4,7);
insert into cpu (date, node_name,node_ip,user,free,total) values ('2021-01-27', '机器b', '10.0.1.133',3,2,5);
insert into cpu (date, node_name,node_ip,user,free,total) values ('2021-01-27', '机器b', '10.0.1.133', 2,2,4);
insert into cpu (date, node_name,node_ip,user,free,total) values ('2021-01-28', '机器a', '10.0.1.144', 2,2,4);
insert into cpu (date, node_name,node_ip,user,free,total) values ('2021-01-28', '机器a', '10.0.1.144', 3,2,5);

SELECT user_rate,* FROM cpu_basic_tab
select * from  cpu_basic_tab
select * from  jojo_record_mv

若指定了POPULATE关键字，会把表中现有数据存储到视图中，否则只会写入创建视图之后的数据，例如你表A已经有10W条数据，如果不加POPULATE，创建出来的视图0条数据，如果加了，它就把原来的10W条数据都给你加进新创建的视图里面。注意⚠️：如果你第一次创建没加，不删除视图的情况加了POPULATE关键字再执行一次，不会改变原来的视图内容，还是空空如也。

不建议使用POPULATE关键字，因为在创建视图过程中插入表中的数据并不会写入视图，会造成数据的丢失。例如你创建该视图的期间，有100条数据插入源表，那么这100条数据肯定不会纳入你的视图中（当然也可以自己改源码做优化，定义一个同步数据的偏移量，视图创建完成后再检查一次偏移量是否对等，不对等就从记录偏移量开始读取新数据录入到视图中）。

7. 其他

ClickHouse的数据类型

func (dao *MemoryCKDao) QueryByTime(ctx context.Context, gSession *gorm.DB, start, end int64, nodename string) []*TimeData {
   var db *gorm.DB
   var allNodeMemoryAveDatas []*TimeData                                                                                      // 所有节点在每个时刻点的平均值
   db = gSession.Table("memory").Where("nano_time >= ? AND nano_time <= ?", start, end).Group("nano_time").Order("nano_time") // 找出符合时间段内并以时间分组
   if nodename == "all" {
      db.Select("nano_time as time,toFloat64(100*sum(used)/sum(total)) as node_use,toFloat64(100*sum(available)/sum(total)) as node_free").Scan(&allNodeMemoryAveDatas)
   } else {
      db.Where("node_name= ?", nodename).Select("nano_time as time,toFloat64(100*sum(used)/sum(total)) as node_use, toFloat64(100*sum(available)/sum(total)) as node_free").Scan(&allNodeMemoryAveDatas)
   }
   return allNodeMemoryAveDatas
}