ClickHouse使用外部字典来进行维度补齐

背景

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和计费策略编码获取计费周期的开始时间。