java clickhouse 查询集群 clickhouse javaapi

转载

棉花糖 2023-11-16 22:37:05

文章标签 数据库 java 大数据布隆过滤器主键 文章分类 Java 后端开发

关于clickhouse入门

先来个基本介绍

一. 速度快
二. 功能多
三. 文艺范

clickhouse基本使用

clickhouse建表
基本分区与主键
索引类型

minmax
set(max_rows)
ngrambf_v1(n, size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed)
tokenbf_v1(size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed)
bloom_filter(bloom_filter([false_positive]) – 为指定的列存储布隆过滤器
注意

实际使用

1、clickhouse建表

数据导入

java直接连接
mongo关联
mysql关联
消息中间件

关于连接安全
函数

Retention

结语

先来个基本介绍

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

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

以下是ClickHouse作为分析型数据库的特点：

一. 速度快

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

100Million 数据集:

ClickHouse比Vertica约快5倍，比Hive快279倍，比MySQL快801倍。

1Billion 数据集:

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

二. 功能多

ClickHouse支持数据统计分析各种场景：

1.支持类SQL查询；

2.支持繁多库函数（例如IP转化，URL分析等，预估计算/HyperLoglog等）；

3.支持数组(Array)和嵌套数据结构(Nested Data Structure)；

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

三. 文艺范

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

而ClickHouse的缺点：

1.不支持Transaction：想快就别想Transaction；

2.聚合结果必须小于一台机器的内存大小：不是大问题；

3.缺少完整的Update/Delete操作；

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

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

set(max_rows)

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

ngrambf_v1(n, size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed)

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

tokenbf_v1(size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed)

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

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

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

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

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

注意

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

实际使用

1、clickhouse建表

建表需明确PARTITION （分区）

PRIMARY KEY （主键），clickhouse支持复合主键，但是此时只有排序功能，如果是独立主键，clickhouse支持新增覆盖主键，更新数据功能。

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

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

数据导入

java直接连接

mybatis 已经支持clickhouse，且mybatis -plus（代码生成器不支持），已支持clickhouse；但是事务区别注定原其他db不同。需自行梳理。

mongo关联

clickhouse支持直接查询mongo数据库

mysql关联

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

消息中间件

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

关于连接安全

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

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

CREATE TABLE odbc_t
(
    `int_id` Int32,
    `float_nullable` Nullable(Float32)
)
ENGINE = ODBC('DSN=mysqlconn', 'test', 'test')

但是很遗憾，阿里的商业库不支持。

函数

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

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  = 'BUTTON_READ_RETURN' , buttonCode  = 'BUTTON_READ_QUIT',buttonCode  = 'BUTTON_READ_RECORD',buttonCode  = 'BUTTON_RETURN_HOME',buttonCode  = 'BUTTON_MY_QUIT',buttonCode  = 'BUTTON_BOOK_QUIT') r
  from 
  count_button_log
  where date BETWEEN 20201118 and 20201122
  and buttonCode in ('BUTTON_READ_RETURN','BUTTON_READ_QUIT','BUTTON_READ_RECORD','BUTTON_RETURN_HOME','BUTTON_MY_QUIT','BUTTON_BOOK_QUIT')
  and behavior  = 'click'
  and channelCode = 'yoc_lwxs_KH-10'
--  and channelCode in ('yoc_lwxs_LWXS-3','yoc_lwxs_SJLR-1NEW','yoc_lwxs_ZY-17','yoc_lwxs_ZY-29','yoc_lwxs_DTYT-5','yoc_lwxs_SJLR-7','yoc_lwxs_KH-10','yoc_lwxs_YL-7','yoc_lwxs_KH-18','yoc_lwxs_KH-6','yoc_lwxs_YL-10NEW')
  group by date,channelCode,deviceNo
  )
group by date,channelCode
) t1

left join 
(
select 
date,channelCode,sum(r[2]) c1,sum(r[3]) c2,sum(r[4]) c3
from
  (select 
  date,channelCode,deviceNo,retention(1,page  = '40001' , page  = '40005',page  = '40003') r
  from count_page_log cpl 
  where page in( '40001','40005','40003')
  and date BETWEEN 20201118 and 20201122
  and channelCode = 'yoc_lwxs_KH-10'
--  and channelCode in ('yoc_lwxs_LWXS-3','yoc_lwxs_SJLR-1NEW','yoc_lwxs_ZY-17','yoc_lwxs_ZY-29','yoc_lwxs_DTYT-5','yoc_lwxs_SJLR-7','yoc_lwxs_KH-10','yoc_lwxs_YL-7','yoc_lwxs_KH-18','yoc_lwxs_KH-6','yoc_lwxs_YL-10NEW')
  group by date,channelCode,deviceNo) 
group by date,channelCode
) t2
on t1.date = t2.date and t1.channelCode = t2.channelCode
order by t1.date

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