ClickHouse应用(一)

最近参与的项目一直使用到ClickHouse，所以趁最近比较闲来对ClickHouse来做个总结。首先，来谈谈，我使用了这么多天ClickHouse的感受吧：

ClickHouse查询超快，ClickHouse是列式存储数据库，同时ClickHouse支持bitmap这样的压缩算法，大大提升了查询速度。 ClickHouse支持几十亿数据进行秒级运算
ClickHouse支持向量处理，Clickhouse支持array数据格式
ClickHouse支持多类复杂的数据格式，同一般的数据库相比较，ClickHouse除了支持普通的数据类型之外，还支持嵌套-Nested ，Json ，Array ， Bitmap等格式
支持多类型表
- Kafka引擎表 - Clickhouse可以直接接入Kafka的数据到ClickHouse中
- Mysql引擎表 - 和Mysql数据库的表进行同步
- MergeTree家族
- MergeTree ：普通引擎表
- replacingMergeTree ：主要用于去重数据
- AggregatingMergeTree : 用于增量数据的更新
- SummingMergeTree : 主要用于数据的预聚合

虽然，ClickHouse优点很多，但是还是感觉有很多不方便的地方，应该是ClickHouse的作用主要是进行实时点击场景吧，其和传统的HDFS并不相同吧，ClickHouse还是有以下的缺点：

不支持高并发，虽然ClickHouse查询速度超快，但是并不支持高并发，官方建议qps为100，可以通过修改配置文件增加连接数，但是在服务器足够好的情况下；
ClickHouse 没法支持事务，不支持真正意义的删除和更新
ClickHouse 无法做到向 HDFS一样，通过shell脚本来定时触发sql任务， ClickHouse面向的是实时场景，在计算时可以搭配物化视图预聚合进行操作，虽然物化视图很强大，但是也不是万能的 😂
Clickhouse中删除数据是异步的，正常来说ClickHouse并不赞同删除超过50多G的表或者视图，其实正常来说，一般也不会删除这么大的表和物化视图，但是可能难免有这样的需求，此使再删大表，就会发现，虽然drop table table_name当时执行成功了，但是在短时间内还是会查到这张表，同时这个表里还是有数据的，这与ClickHouse底层的Attention有关系。

大致谈了一下自己使用ClickHouse的感受，接下来从建表入手具体谈一下ClickHouse的应用

建表

普通表

单机

create table table_name (
col_1 String ,
col_2 Nullable(String) ,
col_3 UInt64,
col_4 Nullable(UInt64) ,
col_5 DateTime ,
col_6 Date
) ENGINE = MergeTree() | AggregatingMergeTree() | SummingMergeTree()  -- 三个里面选择一个即可
PARTITION BY (toDate(col_5))
ORDER BY (col_1, col_2)
;

集群（单副本）

-- 本地表
create table table_name on CLUSTER '{cluster}' (
col_1 String ,
col_2 Nullable(String) ,
col_3 UInt64,
col_4 Nullable(UInt64) ,
col_5 DateTime ,
col_6 Date
) ENGINE = MergeTree() | AggregatingMergeTree() | SummingMergeTree()
PARTITION BY (toDate(col_5))
ORDER BY (col_1, col_2)
;

-- 分布式表
CREATE TABLE table_name_distribute ON CLUSTER default
AS table_name
ENGINE = Distributed(default , default_database, table_name , rand())
;

集群（双副本）

create table table_name on CLUSTER '{cluster}' (
col_1 String ,
col_2 Nullable(String) ,
col_3 UInt64,
col_4 Nullable(UInt64) ,
col_5 DateTime ,
col_6 Date
) ENGINE = replacingMergeTree(/clickhouse/tables/cdp.xmp_track_new/{shard}', '{replica}') | replacingAggregatingMergeTree() | replacingSummingMergeTree()
PARTITION BY (toDate(col_5))
ORDER BY (col_1, col_2)
;


-- 分布式表 （逻辑表）
CREATE TABLE table_name_distribute ON CLUSTER default
AS table_name
ENGINE = Distributed(default , default_database, table_name , rand())
;

在建表的时候，主要关注以下几个概念：

分布式表

对于，单机版的ClickHouse很好理解，就是在ClickHouse建了一张普通表，没有分布式和本地表概念之分

对于，集群版来说，则需要通过 on CLUSTER '{cluster}'来在每个节点上建表，向本地表导入数据的话，ClickHouse会将数据分散在不同的节点存储，因此，如果直接select本地表只能查看到一半的数据；此使则需要分布式表，分布式表是一个逻辑上的表, 可以理解为数据库中的视图, 一般查询都查询分布式表. 分布式表引擎会将我们的查询请求路由本地表进行查询, 然后进行汇总最终返回给用户；因此我们在ClickHouse中除了最终查询的表是分布式表之外，中间计算过程则用本地表进行；

分区/ 分片

partition by 后面接的是该表的分区，例：partition by (col_1, col_2)，和hive中的partition有相同的含义，其主要按照具体的字段来对数据进行分区/分片存放，但是有点不太一样的是，CK里面的partition是建表是的一个字段，建表的时候指定以该字段进行分区分片，但是需要注意一点的是，在往该表中写入数据的时候，分区越多其写入速度会变慢；

排序/ 主键

order by 类似于主键的作用，不过其和Mysql不一样的是其没有主键约束，索引的含义更多一点，同时需要注意的时候，order by (字段) 后接的字段必须是非空类型，对于Null值类型的字段并不支持用于索引中，同样的Nullable类型的字段也不行。

物化视图

数据库中的视图（view）是从一张或多张数据库表查询导出的虚拟表，反映基础表中数据的变化，且本身不存储数据。而物化视图（materialized view）则和普通视图有很大的区别。

物化视图是查询结果集的一份持久化存储，其和普通视图不一样的是，普通视图并不存储数据，而物化视图则可以存储数据，其趋于一张表。物化视图可以是原始表的一个简单copy, 也可以是原始表的简单聚合结果集，也可以是通过一些处理的结果集，其会随着原始更新而发生变化，这种更新仅限于insert操作，对于update 和 delete 并不会及时发生变化，而需要手动去进行更新，但是物化视图还是应用于多种场景：

接入kafka外表的数据，可以通过物化视图将kafka的数据进行相应的处理，最终导入到一张表中
适用于预聚合场景，但是这个地方的预聚合-仅限于简单的一些count , sum , uniq, max , min 这些操作，对于更复杂的场景可不太适合，利用物化视图进行预聚合的时候，可以联合countState, sumState,uniqState ,maxState ,minState 以及countMerge , sumMerge , uniqMerge , maxMerge,minMerge 进行操作。利用这种预聚合的能力，来实现实时点击流的计算。

使用物化视图，主要是利用空间来换时间的概念，来快速的进行查询，但是物化视图还是有如下几个限制：

join 场景，当要对多个表进行关联的时候，物化视图并不会里面更新，而是当左表有更新的时候才会发生相应的更新，当如果左边不变右边新插入数据，物化视图则不会立马更新。
物化视图不能立马感应源表的更新和删除操作，需要手动进行更新
必须指定物化视图的engine 用于数据存储
TO [db].[table]语法的时候，不得使用POPULATE。但是需要注意的是使用POPULATE可能会引起大批量数据的读写，导致数据库连接不上等问题😂，不要问为什么，说多了都是累
查询语句(select)可以包含下面的子句：DISTINCT, GROUP BY, ORDER BY, LIMIT…
物化视图的alter操作有些限制，操作起来不大方便。
若物化视图的定义使用了TO [db.]name 子语句，则可以将目标表的视图卸载 DETACH 在装载 ATTACH

创建物化视图

使用TO [db].[table]命令

使用TO [db].[table]这种语法建表时，只需要指定物化视图的表名db.view_table_name，写入数据目标表的表名db.goal_table_name 以及读取表的逻辑select 语句

CREATE MATERIALIZED VIEW db.view_table_name
TO db.goal_table_name
AS
SELECT
*
FROM
db.orign_table_name

使用POPULATE

使用POPULATE语法建表时，需要指定物化视图对应的引擎，以及分区和索引 ,此使物化视图和一张普通表没什么太大的区别

CREATE MATERIALIZED VIEW db.view_table_name
ENGINE = AggregatingMergeTree()
PARTITION BY (stat_day )
order by (stat_day)
POPULATE
AS
SELECT
*
from
db.origin_table_name

利用物化视图进行预聚合运算

方式一，直接把物化视图作为最终要查询的表，即用POPULATE进行建表，利用物化视图来求pv & uv , countState & uniqState则会把uid进行汇总，在最终进行查询的时候，再利用countMerge & uniqMerge 函数将用户idmerge在一起，此种用法还可以应用于多项目的pv & uv的计算。

CREATE MATERIALIZED VIEW db.view_table_name
ENGINE = AggregatingMergeTree()
PARTITION BY ( stat_day)
ORDER BY (stat_day)
POPULATE
AS
select
toDate(now()) as run_day ,
project as project_id,
toDate(server_time) as stat_day ,
countState(uid) as pv ,
uniqState(uid) as uv
from
db.origin_table_name
group by
project,toDate(server_time)
;

假设要求，多个项目共同的pv & uv ，则可以使用上面的物化视图进行结果查询，使用以下查询语句即可，对了切忌不要直接select * from db.view_table_name查询物化视图，因为countState这些函数都存的是乱码。

select
countMerge(pv) as pv ,
uniqMerge(uv) as uv
from
db.view_table_name
where
project_id in ('11','22','33')

方式二，即用TO [db].[table_name] 将结果存入到对应的目标表中，和上面一致，再不赘述

外表

clickhouse可以通过一些引擎来直接连接其他数据源的数据，就我最常用的两种来举例说明，有兴趣的朋友看官网

kafka外表

直接配置相关信息来连接实时接收kafka的数据, 具体的参数看官网吧，此处接收kafka的数据时候，如果字段固定的话，可以直接使用JSONEachRow,不过采用以下两种建表方式，如果字段不固定，则直接使用JSONAsString。

JSONAsString建表

CREATE TABLE db.kafka_base
(
`eventStr` String
)
ENGINE = Kafka
SETTINGS kafka_broker_list = 'host:9092',
kafka_topic_list = 'topic',
kafka_group_name = 'group',
kafka_format = 'JSONAsString',
kafka_row_delimiter = '\n',
kafka_schema = '',
kafka_num_consumers =1,
kafka_skip_broken_messages=10
;

JSONEachRow建表 需要将所有的字段都列出来

CREATE TABLE db.kafka_base
(
'a' String ,
'b' String ,
'c' String
)
ENGINE = Kafka
SETTINGS
kafka_broker_list = 'host:9092',
kafka_topic_list = 'topic1',
kafka_group_name = 'group1',
kafka_format = 'JSONEachRow',
kafka_row_delimiter = '\n',
kafka_schema = '',
kafka_num_consumers = 1

Mysql

CREATE TABLE db.mysql_base
(
't1' String ,
't2' String
) ENGINE = MySQL('host:port', 'database', 'table', 'user', 'password'[, replace_query, 'on_duplicate_clause']);

参数设置

host:port ：MySQL server的地址。
database ：MySQL数据库名称。
table ： MySQL表名。
user ： MySQL用户名。
password ： MySQL用户密码。
replace_query ：将INSERT INTO查询转换为REPLACE INTO查询的标识。如果replace_query=1，查询将被替换。
on_duplicate_clause ：将ON DUPLICATE KEY 'on_duplicate_clause’表达式添加到INSERT查询中。

总的来说，一旦踏入Clickhouse地界，无数个坑在等你，当然也有惊喜，Over !!!

posted @ 2021-08-02 15:16 菜鸟码代码阅读(1486) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

菜鸟码代码

ClickHouse应用(一)

建表

普通表

物化视图

外表

kafka外表

Mysql

公告