大数据之ClickHouse（下）_随笔

大数据之ClickHouse（下）

ClickHouse（下）

前言
一、SQL *** 作
- 1.1 Update/delete（不建议使用）- 不支持事务
- 1.2 GROUP BY *** 作
- 1.3 Alter *** 作
- 1.4 建立同表结构的表
- 1.5 物化视图
二、ClickHouse空值存储
三、数据一致性
四、 ClickHouse的Join *** 作
- 4.1 查询放大
五、ClickHouse 注意事项
总结

前言

本文介绍了大数据中使用的一种数据库ClickHouse，详细介绍了ClickHouse中的SQL *** 作、语法指令以及相关配置等信息，如果想了解ClickHouse的基本概念以及表引擎，请查阅大数据之ClickHouse（上）

一、SQL *** 作 1.1 Update/delete（不建议使用）- 不支持事务

因为ClickHouse是一个OLAP数据库，不善于做增删改查，所以实现删除和修改必须用Alter来 *** 作，并且这是很重的任务，所以尽量做批量的变更而不是频繁的小 *** 作。本质只是将新的数据放入新增分区，把旧数据打标记合并时再删除
删除（delete）
代码如下：

alter table t delete where sku_id = 'sku_001';

更新（update）
代码如下：

alter table t update cnum = 5 where id = 101;

1.2 GROUP BY *** 作

在ClickHouse中，增加了with rollupwith cubewith total 三种 *** 作；

正常情况下，group by a,b;

(1) group by a,b with rollup 相当于：
group by
group by a
group by a , b

(2) group by a, b with cube 相当于：
group by
group by a
group by b
group by a , b

(3) group by a , b with total 相当于：
group by a , b
group by

1.3 Alter *** 作

(1) 新增字段
代码如下：

alter table t add column total int8 after cunm;

(2) 修改字段类型
代码如下：

alter table t modify column total int16;

(3) 删除字段
代码如下：

alter table t delete column total;

1.4 建立同表结构的表

同表结构的表是指新建一张表(v2)，该表的数据结构和原表(v1)一致，但是数据为空表；
代码如下：

create table v2 engine = MergeTree()
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id , sku_id) 
as select * from v1 where 1=0;

1.5 物化视图

在数据库中，视图只会保存SQL的逻辑，并不会保存真正的数据，本质上不是一张表；但是在ClickHouse中定义了物化视图，该视图在普通视图的基础上，如果用户不指定会默认建立一个数据表来存储SQL执行后的数据；当调用视图时就可以直接从表中拿出数据返回视图，再由视图返回；

缺点：

➢在对历史数据进行去重 *** 作不好用；

➢如果一张表加了很多物化视图，在写这张表的时候就会很消耗资源。

二、ClickHouse空值存储

不要存空值null，因为存储了空值会降低效率，不仅会在存储数据的时候多增加一个存储null的文件，并且null列无法被索引；
解决：字符串存储空串，整形存储实际应用中无意义的值，比如-1.

三、数据一致性

ReplacingMergeTree不能保证查询书查询时没重复，只能保证最终一致性

(1) 手动的optimize，生产环境不推荐
在写入数据后，立刻执行 OPTIMIZE 强制触发新写入分区的合并动作。
代码如下：

OPTIMIZE TABLE test_a FINAL;

(2) 通过groupby
可以去重的查询语句（通过deleted字段标号来实现）
代码如下：

select * from v1 group by id having deleted = 0;

(3) 通过final查询
在查询语句后增加 FINAL 修饰符，这样在查询的过程中将会执行 Merge 的特殊逻辑（例如数据去重，预聚合等）。Final 会取最新的值
代码如下：

select * from v1 final where id = 001 limit 2;

四、 ClickHouse的Join *** 作

ClickHouse与hive（小表在左）不同的是，使用join *** 作时**，小表在右**。并且如果有过滤条件，尽量先进行过滤再进行join。

4.1 查询放大

出现在两个分布式表进行join并且没有加 global。比如有三个分区，那么分布式表会在每个分区都会有，两个分布式表进行join时，右表会进行N^2次的join（N是分区的数目），出现了查询放大的情况。

五、ClickHouse 注意事项

➢千万以上数据集进行 order by 查询时需要搭配 where 条件和 limit 语句一起使用。避免全表排序导致内存爆炸。

➢当多表联查时，查询的数据仅从其中一张表出时，可考虑用 IN *** 作而不是 JOIN;因为join的原理是右表加载到内存，再去匹配
代码如下：

insert into v2 
select * from v1 where v1.cid in (select cid from d1);

反例：
insert into v2 
select * from v1 left join d1 on v1.cid = d1.cid;

总结

至此有关ClickHouse的知识就总结完毕啦，有什么需要补充的或者有什么不足欢迎大家指正，我们共同进步。

欢迎分享，转载请注明来源：内存溢出

大数据之ClickHouse（下）