Google BigQuery中能否合并同一行同类型的sketch列？

问题场景 #

在Google BigQuery中，数据集包含两列存储相同类型sketch的字段（sketchA和sketchB），示例输入如下：

col1 | col2 | sketchA|sketchB
_______________________________
c1  | c2    |sketch1 | sketch5
c3  |  c4   |sketch2 | null
c3  | c5    |sketch3 | null
c3  |c6     |null.   |sketch6
c2  |c6     |sketch7 |  sketch4
c5  |c9     |null    |sketch8

需要将这两列的sketch合并，规则为：同一行两列均有sketch时合并两者，仅存在一个时直接使用该sketch，期望输出如下：

col1 | col2 | sketchMerge
___________________
c1  | c2    |merge(sketch5, sketch1)
c3  |  c4   |sketch2
c3  | c5    |sketch3
c3  |c6     |sketch6
c2  |c6     |merge(sketch7, sketch4)
c5  |c9     |sketch8

解决方案 #

BigQuery针对不同类型的sketch提供了原生合并函数，可通过以下方式实现需求：

核心逻辑 #

• 当sketchA和sketchB均非空时，调用对应类型的合并函数合并两者
• 仅一列非空时，直接取该列的sketch值

具体SQL实现 #

以常用的HLL基数统计sketch为例，使用HLL_MERGE_PARTIAL函数实现：

SELECT
  col1,
  col2,
  CASE
    WHEN sketchA IS NOT NULL AND sketchB IS NOT NULL THEN HLL_MERGE_PARTIAL([sketchA, sketchB])
    WHEN sketchA IS NOT NULL THEN sketchA
    ELSE sketchB
  END AS sketchMerge
FROM
  `your-project.your-dataset.your-table`;

针对其他类型的sketch，替换对应的合并函数即可：

• Count-Min Sketch：CMS_MERGE_PARTIAL
• Quantiles Sketch：QUANTILES_MERGE_PARTIAL
• Reservoir Sampling Sketch：RESERVOIR_MERGE_PARTIAL

注意事项 #

• 必须保证sketchA和sketchB为完全相同类型的sketch，否则合并操作会报错
• 若使用自定义类型的sketch，需提前创建对应的合并UDF（用户自定义函数）

内容的提问来源于stack exchange，提问作者Chhavi Bansal