clickhouse使用的一点总结 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