目录
0. 参考资料
[1] 安装与使用
[2] 简书
[3] 基础知识
[4] 深度揭秘
[5] 教程
[6] ClickHouse SQL 的十项优化规则
1. 简介
clickhouse 是俄罗斯的“百度”Yandex公司在2016年开源的,ClickHouse®是面向列的数据库管理系统(DBMS),用于对查询进行联机分析处理(OLAP)。一款针对大数据实时分析的高性能分布式数据库,与之对应的有hadoop生态hive,Vertica和百度出品的palo。这是战斗民族继nginx后,有开源的一款“核武器”。据官方介绍,clickhouse 有以下优点:
- 面向列式
- 数据压缩
- 多核并行处理
- 分布式部署
- 支持sql
- 向量引擎
- 实时写入
- 异步复制
OLAP(数据分析)场景的特点
读多于写
不同于事务处理(OLTP)的场景,比如电商场景中加购物车、下单、支付等需要在原地进行大量insert、update、delete操作,数据分析(OLAP)场景通常是将数据批量导入后,进行任意维度的灵活探索、BI工具洞察、报表制作等。数据一次性写入后,分析师需要尝试从各个角度对数据做挖掘、分析,直到发现其中的商业价值、业务变化趋势等信息。这是一个需要反复试错、不断调整、持续优化的过程,其中数据的读取次数远多于写入次数。这就要求底层数据库为这个特点做专门设计,而不是盲目采用传统数据库的技术架构。
大宽表,读大量行但是少量列,结果集较小
在OLAP场景中,通常存在一张或是几张多列的大宽表,列数高达数百甚至数千列。对数据分析处理时,选择其中的少数几列作为维度列、其他少数几列作为指标列,然后对全表或某一个较大范围内的数据做聚合计算。这个过程会扫描大量的行数据,但是只用到了其中的少数列。而聚合计算的结果集相比于动辄数十亿的原始数据,也明显小得多。
数据批量写入,且数据不更新或少更新
OLTP类业务对于延时(Latency)要求更高,要避免让客户等待造成业务损失;而OLAP类业务,由于数据量非常大,通常更加关注写入吞吐(Throughput),要求海量数据能够尽快导入完成。一旦导入完成,历史数据往往作为存档,不会再做更新、删除操作。
无需事务,数据一致性要求低
OLAP类业务对于事务需求较少,通常是导入历史日志数据,或搭配一款事务型数据库并实时从事务型数据库中进行数据同步。多数OLAP系统都支持最终一致性。
灵活多变,不适合预先建模
分析场景下,随着业务变化要及时调整分析维度、挖掘方法,以尽快发现数据价值、更新业务指标。而数据仓库中通常存储着海量的历史数据,调整代价十分高昂。预先建模技术虽然可以在特定场景中加速计算,但是无法满足业务灵活多变的发展需求,维护成本过高。
2. 安装
2.1 centos安装
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. 常用语句
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. 引擎
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. 物化视图
-- 创建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. 其他
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
}