您好,欢迎访问代理记账网站
  • 价格透明
  • 信息保密
  • 进度掌控
  • 售后无忧

[数据库] clickhouse

目录

    • 0. 参考资料
    • 1. 简介
    • 2. 安装
      • 2.1 centos安装
    • 3. 配置
      • 3.1.目录结构
      • 3.2.命令行参数
      • 3.3.配置文件
    • 4. 常用语句
      • 4.1.创建操作
      • 4.2.查询操作
      • 4.3.插入操作
      • 4.4.表达式列
    • 5. 引擎
      • 5.1.Merge
        • 5.1.1 MergeTree
        • 5.1.2 ReplacingMergeTree
        • 5.1.3 SummingMergeTree
        • 5.1.4 AggregatingMergeTree
        • 5.1.5 CollapsingMergeTree
      • 5.2.TinyLog
      • 5.3.Memory
    • 6. 物化视图
    • 7. 其他

0. 参考资料

[1] 安装与使用
[2] 简书
[3] 基础知识
[4] 深度揭秘
[5] 教程

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
}

分享:

低价透明

统一报价,无隐形消费

金牌服务

一对一专属顾问7*24小时金牌服务

信息保密

个人信息安全有保障

售后无忧

服务出问题客服经理全程跟进