2-3 基于 EMR OLAP 的开源实时数仓解决方案之 ClickHouse 事务实现.pdf

1、基于基于EMR OLAP的开源实时数仓解的开源实时数仓解决方案之决方案之ClickHouse事务实现事务实现吴雪扬高级开发工程师|01现状现状02整体方案整体方案03测试结果测试结果04Sharding Key优化优化目录目录CONTENT|05未来规划未来规划现状01|为什么需要 ClickHouse 写事务|许多用户通过 Flink+ClickHouse 构建“用户画像”、“实时 BI 报表”等业务，有较高的数据准确性要求Flink Exactly Once 需要 Sink 端支持ClickHouse 社区暂时没有对事务的支持ClickHouse 当前写入机制|按照 Paritition

2、拆分 Block写入拆分后的 Partitioned Block 成为临时 Data Part重命名这个临时 Data Part 为正式的 Data Part加入到 MergeTreeData 的 Data Part index 中，并对用户可见整体方案02|整体流程|ClickHouse 事务状态机|ClickHouse 写事务处理|测试结果03|并发对写事务的性能影响|数据大小对写事务性能的影响|Flink Exactly Once 性能|Sharding Key 优化04|case1:计算UV场景，将相同 uid 写入到同一 shard 分片|CREATE TABLE user_acti

3、on ON CLUSTER cluster_emr(Uid UInt32,Action String)ENGINE=ReplicatedMergeTree(/ssb/layer-shard/user_action,replica)ORDER BY(Uid);CREATE TABLE user_action_all ON CLUSTER cluster_emr(Uid UInt32,Action String)ENGINE=Distributed(cluster_emr,default,user_action,Uid);INSERT INTO user_action_all(Uid,Action

4、)SELECTnumber,randomPrintableASCII(16)FROM numbers(100000000);case1:计算UV场景，将相同 uid 写入到同一 shard 分片|select uniqExact(Uid)from user_action_all SETTINGS distributed_group_by_no_merge=0;select sum(par_uv)as uv from(select uniqExact(Uid)as par_uvfrom user_action_all)SETTINGS distributed_group_by_no_merge=

5、1;case1:计算UV场景，将相同 uid 写入到同一 shard 分片|case2:IN 子查询|create table orders on cluster cluster_emr(uid UInt32,date Date,skuId UInt32,order_revenue UInt8)ENGINE=ReplicatedMergeTree(/clickhouse/tables/layer-shard/default/orders,replica)Order by dateCREATE TABLE orders_all ON CLUSTER cluster_emr(uid UInt32,

6、date Date,skuId UInt32,order_revenue UInt8)ENGINE=Distributed(cluster_emr,default,orders,uid)insert into orders_all(uid,date,skuId,order_revenue)select rand(1)%80000000,toDate(2020-01-01)+rand(2)%30,rand(3)%1000,rand(4)%200 from numbers(300000000)case2:IN 子查询|