背景
ClickHouse中对Join实现的不是很好，不适合大表关联，推崇大宽表的概念。因为默认情况下，ClickHouse使用 Hash Join算法。 ClickHouse采取 <right_table> 并在RAM中为其创建哈希表。 在某个内存消耗阈值之后，ClickHouse回退到合并联接算法。所以在使用ClickHouse在大数据量的情况下尽量少用Join，通过冗余大宽表的方式来进行字段冗余。但是有些场景，比如维度补齐的场景，在维度表数据量不大的情况下，我们是可以使用join方式的，当然还会有更好点的方式，待我慢慢道来。
业务场景
存在事实表：ods_device_log（设备上报日志），维度表：产品表（dim_product）,账户资费配置表（dim_billing_policy）。
一个比较简单的需求,想统计计费周期内设备的计量数据：上报消息数。
ods_device_log表结构如下：
`product_key` String comment “产品Key，通过产品Key获取所属账户信息”,
`device_key` String comment “设备Key”,
`data_time` DateTime comment “数据上报时间”,
`log_info` String comment “上报数据内容”
产品表结构如下：
`org_id` Int64 comment “产品所属的账户ID”,
`product_key` String comment “产品Key”,
`product_name` String comment “产品名称”
账户资费配置表如下：
`org_id` Int64 comment “产品所属的账户ID”,
` policy_code` String comment “资费编码”,
` policy_name` String comment “资费名称”,
`billing_start_time` DateTime comment “计费周期开始时间”,
`billing_end_time` DateTime comment “计费周期结束时间”
通过Join来实现
SQL还是比较简单的，使用countIf函数可以比较方便的统计出计量数据。SQL如下:
select
countIf(a.data_time>c.billing_start_time)
from
ods_device_log a
left join dim_product b on a.product_key = b.product_key
left join dim_billing_policy c on b.org_id =c.org_id
where
a.data_time >= toDateTime('2021-12-01 00:00:00')
and a.data_time <toDateTime('2021-12-02 00:00:00')
GROUP by
a.product_key,
a.device_key;
更好的一种实现方式
对于体量比较小的维表，我们可以通过外部字典的方式来替换Join方式。由于JOIN 每个查询都会重新访问关联表。可以将维度表建成外部字典表。字典表会周期同步维度信息。然后通过字典函数获取维度数据。参考SQL如下：
select
countIf(a.data_time > dictGetDateTime('dim_billing_policy','billing_start_time',
tuple(dictGetInt64('dim_product','org_id',tuple(a.product_key)),'00-00-01'))
) num
from
ods_device_log a
where
a.data_time >= toDateTime('2021-12-01 00:00:00')
and a.data_time <toDateTime('2021-12-02 00:00:00')
GROUP by
a.product_key,
a.device_key;
说明：字典表dim_billing_policy主键为org_id、policy_code。字典表dim_product主键为product_key。首先通过产品product_key获取账户ID，然后再通过账户ID和计费策略编码获取计费周期的开始时间。
网友评论