大数据之ClickHouse(下)

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

上一篇:mysql join和left join区别


下一篇:多线程012--线程的sleep、wait、join、yield如何使用