clickhouse使用的一点总结

clickhouse据说是用在大数据量的olap场景列式存储数据库,也有幸能够用到它在实际场景中落地。本篇就来说说简单的使用心得吧。

1. 整体说明
架构啥的,就不多说了,列式存储、大数据量、高性能。参见官方文档地址: https://clickhouse.com/docs/en/
对于使用者而言,除了泛泛而谈的架构之外,更多的是如何使用的问题。
从整体而言,clickhouse的使用方法基本遵守普通的sql规范,所以基本上只要你会写sql,对于普通的增删改查就问题不大了。也就是说应对业务而言,问题并不大了。
比如: create table...; select xx from t; insert into table xx... ; alter table xx update x=xx...; (当然了,这个用法差异有点大); alter table xx delete where x=xx...(同理);

2. 存储引擎简说
一个数据库的最大特点,应该就是其存储引擎或者说存储方式。而这在clickhouse体现得更加明显,其拥有超级多的存储引擎,不管你用不用得上,反正可选范围很大。
其中,我们最常用或者最简单可使用的是 MergeTree 系列,简单来说是归并树的存储结构,查询肯定是很快的,另外,它也适用于大数据量的存储。所以,一般就选择这玩意就行了。当然,它下面有很多的子类,需要根据作出相应的改变。
比如: ReplicatedMergeTree 代表有多节点存储数据,这对于高可用查询是必须的(针对任意节点的查询也是必须的)。
AggregatingMergeTree 代表当前节点是一种按主键聚合的数据分片方式。
单就MergeTree引擎而言,如果想要有比较优化的应用或者比较特殊的需求,则必须要亲自再去细细翻阅clickhouse的官方文档了,太多选择是真苦恼啊。
其他存储引擎,比如 Log系列,则更少场景会使用到,一般当作临时表用时,可以考虑。其他的如 File, 则可以算是被当作解析器来使用。。。
总之,要全面理解ck的存储引擎,实非易事,除非深度使用它。

3. clickhouse中的主键
clickhouse中,其实并没有明确说一定要有主键之类的话,只是在创建表时,会默认以排序字段作为主键。
它的主键的作用,一定程度上相当于普通索引,这可能也是为什么它没有明确叫主键的原因,因为不需要唯一但有利于查找。
但它还是有Primary Key 的定义。

4. curd sql
我们只说最简单的方式,但其实clickhouse中,有一个非常大的特点就是,它的sql非常之多样,灵活,不管你用不用得上,反正就是功能很多。而且文档也是呵呵的。

-- 创建表, 值得说明的是,它可以非常复杂的过期策略 CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] ( name1 [type1] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|ALIAS expr1] [compression_codec] [TTL expr1], name2 [type2] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|ALIAS expr2] [compression_codec] [TTL expr2], PRIMARY KEY(expr1[, expr2,...])], ... ) ENGINE = MergeTree comment 'xxx' PARTITION BY toYYYYMM(d) TTL d + INTERVAL 1 MONTH [DELETE], d + INTERVAL 1 WEEK TO VOLUME 'aaa', d + INTERVAL 2 WEEK TO DISK 'bbb'; -- 更新和删除,这语法够独特的,据说是为了让大家少用这种功能而设计的,厉害了 ALTER TABLE [db.]table UPDATE column1 = expr1 [, ...] WHERE filter_expr ALTER TABLE [db.]table [ON CLUSTER cluster] DELETE WHERE filter_expr -- 查询,有很多独特的用法,如WITH [WITH expr_list|(subquery)] SELECT [DISTINCT [ON (column1, column2, ...)]] expr_list [FROM [db.]table | (subquery) | table_function] [FINAL] [SAMPLE sample_coeff] [ARRAY JOIN ...] [GLOBAL] [ANY|ALL|ASOF] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI] JOIN (subquery)|table (ON )|(USING ) [PREWHERE expr] [WHERE expr] [GROUP BY expr_list] [WITH ROLLUP|WITH CUBE] [WITH TOTALS] [HAVING expr] [ORDER BY expr_list] [WITH FILL] [FROM expr] [TO expr] [STEP expr] [LIMIT [offset_value, ]n BY columns] [LIMIT [n, ]m] [WITH TIES] [SETTINGS ...] [UNION...] [INTO OUTFILE filename [COMPRESSION type] ] [FORMAT format] -- 数据插入,可以作排除性插入语法 INSERT INTO [db.]table [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23), ... INSERT INTO [db.]table [(c1, c2, c3)] SELECT ... INSERT INTO insert_select_testtable (* EXCEPT(b)) Values (2, 2); -- 执行计划查询,这对于了解其内部机制很有帮助,它的执行计划非常详细,不过看起来也有点吓人 EXPLAIN [AST | SYNTAX | PLAN | PIPELINE] [setting = value, ...] SELECT ... [FORMAT ...]


5. 本地与集群
虽然clickhouse号称是大数据量实时分析数据库,但它不绝对的使用分布式存储。而是构造了两个名词供选择,即本地表与分布式表。
本地表顾名思义就是,只是存储在本地机器上的表。这种本地表,只应对某些场景,比如你连接的节点永远是同一个机器时,可以使用,此时它和mysql之类的数据库是一样的,存储容量和性能都是单机的,但有点值得注意的是当它与分布表进行关联查询时,可能会有你意想不到的结果:报错。
分布式表,就是说它可以每个节点上都能查询到。这是我们理解的真正的大数据量的分布式存储,我也不关心数据存储在哪里。只要能给到正确的结果就行。实际上,分布表的背后,是一个个的本地表。不过,它一般会要求有副本存储机制。
但是很无赖的是,我们无法直接创建分布式表,而是要先创建本地表,然后再以此为基础创建对应的分布式表。即一个建表工作,我们需要做两遍才能完成。
【clickhouse使用的一点总结】而且,对于数据的写入,你可以往本地表写,也可以往分布式表写。虽然入口的确变多了,但也给了大家很迷惑的感觉。
-- 创建人群原始表 create table loc_tab1 ON CLUSTER ck_cluster ( xx String comment 'xx', version_no String comment 'version, for update' ) ENGINE = ReplicatedMergeTree partition by xx order by xx ; -- 创建分布式表 CREATE TABLE distributed_tab2 AS loc_tab1 ENGINE = Distributed(ck_cluster, currentDatabase(), loc_tab1, rand());


6. bitmap数据操作参考
bitmap数据由于其有着超高性能的交差运算能力,以及节省存储空间的能力,被我们某些场景应用,如码值数据表。但是为构建bitmap数据,则往往要做比较多的前置工作,而且由于bitmap的数据压缩,可能会无法应对复杂场景,这些都需要提前评估。
-- 创建原始bitmap表 create table loc_tab1_bitmap ON CLUSTER ck_cluster ( xx String comment 'xx', uv AggregateFunction(groupBitmap, UInt64), version_no String comment 'version, for update' ) ENGINE = ReplicatedMergeTree partition by xx order by xx ; -- 创建分布式表 CREATE TABLE distributed_tab2_bitmap AS loc_tab1_bitmap ENGINE = Distributed(ck_cluster, currentDatabase(), loc_tab1_bitmap, rand()); -- 插入人群bitmap表数据, 往本地表插数据,往分布式表读数据 -- 读取的数据来源表,一般也会要求是一个分布宽表,而且其作用如果只是为了构建bitmap数据,则会有一个用后即删的动作 insert into loc_tab1_bitmap select xx, groupBitmapState(toUInt64OrZero(uid)) as uv,version_no from dist_data_raw group by xx,version_no; -- 读取参考,求两个bitmap数据的交集,并到另一个表中做group by with intersect_tab as ( select arrayJoin(bitmapToArray(bitmapAnd(user1, user2))) as uidfrom (select uv as user1, 1 as join_id from distributed_tab2_bitmapwhere xx = '1') t1inner join (select uv as user2, 1 as join_id from distributed_tab2_bitmapwherexx = '2') t2on t1.join_id = t2.join_id ),a1 as (select x2, toUInt64(xx) as uid from distributed_tb3)select x2,count(1) as cnt from a1 right join intersect_tab a2 on a1.uid = a2.uid group by x2 order by cnt desc


    推荐阅读