Greenplum SQL迁移HiveSQL时STRING_AGG函数不兼容问题求助

核心替换逻辑 #

Greenplum的STRING_AGG(字段, 分隔符)功能在Hive中可以通过concat_ws + collect_list组合函数实现，二者行为完全等价：

• 不需要去重时用collect_list，和原STRING_AGG默认行为一致
• 需要去重时替换为collect_set

适配后完整SQL #

select 
    data_date, 
    subscriber_id, 
    msisdn, 
    product, 
    validity,
    concat_ws('xx', collect_list(d0)) d0,
    concat_ws('', collect_list(d1)) d1,
    concat_ws('', collect_list(d2)) d2,
    concat_ws('', collect_list(d3)) d3,
    concat_ws('', collect_list(d4)) d4,
    concat_ws('', collect_list(d5)) d5,
    concat_ws('', collect_list(d6)) d6,
    concat_ws('', collect_list(d7)) d7,
    concat_ws('', collect_list(d8)) d8,
    concat_ws('', collect_list(d9)) d9,
    concat_ws('', collect_list(d10)) d10,
    concat_ws('', collect_list(d11)) d11
from tmp_subscription_base_02
group by data_date, subscriber_id, msisdn, product, validity

额外适配说明 #

• 若需要指定拼接顺序，Hive 2.3及以上版本支持在聚合函数中指定排序规则，示例：concat_ws('', collect_list(d1 order by 排序字段 asc))
• 若原逻辑需要保留NULL值对应的空占位，可在聚合前用nvl函数处理字段：concat_ws('', collect_list(nvl(d1, '')))
• 若拼接结果出现重复值需要去重，将collect_list替换为collect_set即可

内容的提问来源于stack exchange，提问作者Developer Rajinikanth