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 rollup\with cube\with 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的知识就总结完毕啦,有什么需要补充的或者有什么不足欢迎大家指正,我们共同进步。