关于clickhouse入门

先来个基本介绍

ClickHouse是一个用于联机分析(OLAP)的列式数据库管理系统(DBMS)。由号称“俄罗斯 Google”的Yandex开发而来&＃xff0c;在2016年开源&＃xff0c;在计算引擎里算是一个后起之秀&＃xff0c;在内存数据库领域号称是最快的。由于它有几倍于GreenPlum等引擎的性能优势&＃xff0c;所以不少人都选择将其安装云服务器中使用。

ClickHouse是一个列导向数据库&＃xff0c;是原生的向量化执行引擎。它在大数据领域没有走Hadoop生态&＃xff0c;而是采用Local attached storage作为存储&＃xff0c;这样整个IO可能就没有Hadoop那一套的局限。它的系统在生产环境中可以应用到比较大的规模&＃xff0c;因为它的线性扩展能力和可靠性保障能够原生支持shard&＃43;replication这种解决方案。它还提供了一些SQL直接接口&＃xff0c;有比较丰富的原生client。

以下是ClickHouse作为分析型数据库的特点&＃xff1a;

一. 速度快

ClickHouse性能超过了市面上大部分的列式存储数据库&＃xff0c;相比传统的数据ClickHouse要快100-1000倍&＃xff0c;ClickHouse还是有非常大的优势。

100Million 数据集:

ClickHouse比Vertica约快5倍&＃xff0c;比Hive快279倍&＃xff0c;比MySQL快801倍。

1Billion 数据集:

ClickHouse比Vertica约快5倍&＃xff0c;MySQL和Hive已经无法完成任务了。

二. 功能多

ClickHouse支持数据统计分析各种场景&＃xff1a;

1.支持类SQL查询&＃xff1b;

2.支持繁多库函数&＃xff08;例如IP转化&＃xff0c;URL分析等&＃xff0c;预估计算/HyperLoglog等&＃xff09;&＃xff1b;

3.支持数组(Array)和嵌套数据结构(Nested Data Structure)&＃xff1b;

4.支持数据库异地复制部署。

三. 文艺范

不理睬Hadoop生态&＃xff0c;走自己的路。目前任何具有x86_64&＃xff0c;AArch64或PowerPC64LE CPU架构的Linux&＃xff0c;FreeBSD或Mac OS X上运行。

而ClickHouse的缺点&＃xff1a;

1.不支持Transaction&＃xff1a;想快就别想Transaction&＃xff1b;

2.聚合结果必须小于一台机器的内存大小&＃xff1a;不是大问题&＃xff1b;

3.缺少完整的Update/Delete操作&＃xff1b;

4.支持有限操作系统。

clickhouse基本使用

clickhouse建表

https://clickhouse.tech/docs/zh/engines/table-engines/mergetree-family/versionedcollapsingmergetree/#versionedcollapsingmergetree

基本分区与主键

https://clickhouse.tech/docs/zh/engines/table-engines/mergetree-family/mergetree/

索引类型

minmax

官方定义&＃xff1a;
存储指定表达式的极值&＃xff08;如果表达式是 tuple &＃xff0c;则存储 tuple 中每个元素的极值&＃xff09;&＃xff0c;这些信息用于跳过数据块&＃xff0c;类似主键。
推荐&＃xff1a;使用当日排行榜&＃xff0c;前三名等场景使用&＃xff1b;

set(max_rows)

官方定义&＃xff1a;
存储指定表达式的不重复值&＃xff08;不超过 max_rows 个&＃xff0c;max_rows&＃61;0 则表示『无限制』&＃xff09;。这些信息可用于检查 数据块是否满足 WHERE 条件。
推荐&＃xff1a;基本分类&＃xff0c;如业务中使用的buttonCode;behavior等使用&＃xff0c;限制数需主动把控&＃xff1b;

ngrambf_v1(n, size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed)

官方定义&＃xff1a;存储一个包含数据块中所有 n元短语&＃xff08;ngram&＃xff09; 的 布隆过滤器 。只可用在字符串上。
可用于优化 equals &＃xff0c; like 和 in 表达式的性能。
n – 短语长度。
size_of_bloom_filter_in_bytes – 布隆过滤器大小&＃xff0c;单位字节。&＃xff08;因为压缩得好&＃xff0c;可以指定比较大的值&＃xff0c;如 256 或 512&＃xff09;。
number_of_hash_functions – 布隆过滤器中使用的哈希函数的个数。
random_seed – 哈希函数的随机种子。
推荐&＃xff1a;如敏感词过滤&＃xff0c;类似于代码中常用布隆过滤器场景。

tokenbf_v1(size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed)

官方定义:跟 ngrambf_v1 类似&＃xff0c;不同于 ngrams 存储字符串指定长度的所有片段。它只存储被非字母数字字符分割的片段。
推荐: 如ip4段需使用ip三段做分组

bloom_filter(bloom_filter([false_positive]) – 为指定的列存储布隆过滤器

官方定义&＃xff1a;可选的参数 false_positive 用来指定从布隆过滤器收到错误响应的几率。取值范围是 (0,1)&＃xff0c;默认值&＃xff1a;0.025

支持的数据类型&＃xff1a;Int*, UInt*, Float*, Enum, Date, DateTime, String, FixedString, Array, LowCardinality, Nullable。

以下函数会用到这个索引&＃xff1a; equals, notEquals, in, notIn, has
推荐&＃xff1a;带错误几率的布隆过滤器&＃xff0c;即可以使用不错错误率&＃xff0c;增加速率。
比ngrambf_v1少了几个参数&＃xff0c;更像原始布隆过滤器

注意

索引的函数支持在官网标注很清楚&＃xff1b;与mysql不完全相同

实际使用

1、clickhouse建表

建表需明确PARTITION &＃xff08;分区&＃xff09;

PRIMARY KEY &＃xff08;主键&＃xff09;&＃xff0c;clickhouse支持复合主键&＃xff0c;但是此时只有排序功能&＃xff0c;如果是独立主键&＃xff0c;clickhouse支持新增覆盖主键&＃xff0c;更新数据功能。

ORDER BY &＃xff08;排序&＃xff09;可类似简单索引使用&＃xff0c;mongodb中的索引就是用排序来建立的。order by只能减少&＃xff0c;不能新增。即建表时已固定。如果使用主键&＃xff0c;
主键必须在order by 第一位&＃xff1b;order by 的列必须非空。

TTL expr [DELETE|TO DISK ‘aaa’|TO VOLUME ‘bbb’], …
clickhouse支持数据超时设计&＃xff0c;但是目前没有想到合适场景。

数据导入

java直接连接

mybatis 已经支持clickhouse&＃xff0c;且mybatis -plus&＃xff08;代码生成器不支持&＃xff09;&＃xff0c;已支持clickhouse&＃xff1b;但是事务区别注定原其他db不同。需自行梳理。

mongo关联

clickhouse支持直接查询mongo数据库

mysql关联

当时看到有文档clickhouse 直接读取bin-log &＃xff0c;但是现在找不到了。个人建议不要直接读取mysql-bin-log。 因为clickhouse 不支持频繁插入&＃xff0c;会出现丢数据情况。
clickhouse mysql引擎可以直接关联远程数据库。但是只支持做关联&＃xff0c;新增数据&＃xff0c;删除数据&＃xff0c;修改数据暂时不支持。

消息中间件

kafka&＃xff0c;clickhouse有直接的kafka引擎&＃xff0c;简单说可以把消息投递到kafka&＃xff0c;
然后解析json&＃xff0c;定时分发到不同消费者。

关于连接安全

clickhouse的查询可以直接看到 ddl语句。
那么我们建立的mysql连接就是暴露的。
clickhouse官网给出了方案:

$ cat /etc/odbc.ini
[mysqlconn]
DRIVER &＃61; /usr/local/lib/libmyodbc5w.so
SERVER &＃61; 127.0.0.1
PORT &＃61; 3306
DATABASE &＃61; test
USERNAME &＃61; clickhouse
PASSWORD &＃61; clickhouse


CREATE TABLE odbc_t
(&＃96;int_id&＃96; Int32,&＃96;float_nullable&＃96; Nullable(Float32)
)
ENGINE &＃61; ODBC(&＃39;DSN&＃61;mysqlconn&＃39;, &＃39;test&＃39;, &＃39;test&＃39;)


但是很遗憾&＃xff0c;阿里的商业库不支持。

函数

clickhouse提供了丰富的统计函数&＃xff0c;先推荐一个&＃xff0c;其余慢慢体会。

Retention

retention(cond1, cond2, …, cond32);
直接附案例

select
t1.date "日期",t1.channelCode "渠道",t2.c1 "阅读页UV",t1.c1 "阅读页顶部返回按钮点击",t1.c2 "阅读页顶部关闭按钮点击",t1.c3 "阅读页阅读记录按钮点击",t1.c4 "阅读页返回首页按钮点击",t2.c2 "个人中心页面",t1.c5 "我的顶部退出按钮点击",t2.c3 "书城uv",t1.c6 "书城顶部退出按钮点击"
from
(
select
date ,channelCode,sum(r[2]) c1,sum(r[3]) c2,sum(r[4]) c3,sum(r[5]) c4,sum(r[6]) c5,sum(r[7]) c6
from (select date,channelCode,deviceNo ,retention(1,buttonCode &＃61; &＃39;BUTTON_READ_RETURN&＃39; , buttonCode &＃61; &＃39;BUTTON_READ_QUIT&＃39;,buttonCode &＃61; &＃39;BUTTON_READ_RECORD&＃39;,buttonCode &＃61; &＃39;BUTTON_RETURN_HOME&＃39;,buttonCode &＃61; &＃39;BUTTON_MY_QUIT&＃39;,buttonCode &＃61; &＃39;BUTTON_BOOK_QUIT&＃39;) rfrom count_button_logwhere date BETWEEN 20201118 and 20201122and buttonCode in (&＃39;BUTTON_READ_RETURN&＃39;,&＃39;BUTTON_READ_QUIT&＃39;,&＃39;BUTTON_READ_RECORD&＃39;,&＃39;BUTTON_RETURN_HOME&＃39;,&＃39;BUTTON_MY_QUIT&＃39;,&＃39;BUTTON_BOOK_QUIT&＃39;)and behavior &＃61; &＃39;click&＃39;and channelCode &＃61; &＃39;yoc_lwxs_KH-10&＃39;
-- and channelCode in (&＃39;yoc_lwxs_LWXS-3&＃39;,&＃39;yoc_lwxs_SJLR-1NEW&＃39;,&＃39;yoc_lwxs_ZY-17&＃39;,&＃39;yoc_lwxs_ZY-29&＃39;,&＃39;yoc_lwxs_DTYT-5&＃39;,&＃39;yoc_lwxs_SJLR-7&＃39;,&＃39;yoc_lwxs_KH-10&＃39;,&＃39;yoc_lwxs_YL-7&＃39;,&＃39;yoc_lwxs_KH-18&＃39;,&＃39;yoc_lwxs_KH-6&＃39;,&＃39;yoc_lwxs_YL-10NEW&＃39;)group by date,channelCode,deviceNo)
group by date,channelCode
) t1left join
(
select
date,channelCode,sum(r[2]) c1,sum(r[3]) c2,sum(r[4]) c3
from(select date,channelCode,deviceNo,retention(1,page &＃61; &＃39;40001&＃39; , page &＃61; &＃39;40005&＃39;,page &＃61; &＃39;40003&＃39;) rfrom count_page_log cpl where page in( &＃39;40001&＃39;,&＃39;40005&＃39;,&＃39;40003&＃39;)and date BETWEEN 20201118 and 20201122and channelCode &＃61; &＃39;yoc_lwxs_KH-10&＃39;
-- and channelCode in (&＃39;yoc_lwxs_LWXS-3&＃39;,&＃39;yoc_lwxs_SJLR-1NEW&＃39;,&＃39;yoc_lwxs_ZY-17&＃39;,&＃39;yoc_lwxs_ZY-29&＃39;,&＃39;yoc_lwxs_DTYT-5&＃39;,&＃39;yoc_lwxs_SJLR-7&＃39;,&＃39;yoc_lwxs_KH-10&＃39;,&＃39;yoc_lwxs_YL-7&＃39;,&＃39;yoc_lwxs_KH-18&＃39;,&＃39;yoc_lwxs_KH-6&＃39;,&＃39;yoc_lwxs_YL-10NEW&＃39;)group by date,channelCode,deviceNo)
group by date,channelCode
) t2
on t1.date &＃61; t2.date and t1.channelCode &＃61; t2.channelCode
order by t1.date


比传统全left join 优雅不知道多少倍。
但是其实这个函数更适合做提取包含多个属性的用户。
此处retention(1&＃61;1,…)
是为了保证第一位一定返回1&＃xff0c;否则_&＃xff01;&＃xff01;自己感受~&＃xff01;

结语

clickhouse的查询性能确实比mysql强大。
但是他内存查询&＃xff0c;不支持事务的特性注定他只能用于分析统计数据。
这应该是一种趋势&＃xff0c;毕竟hbase太庞大了。
最终说一句&＃xff1a;都是产品爸爸&＃xff0c;运营爸爸逼我们学的。