Hive QL转BigQuery SQL报错：UNNEST内不允许使用array_agg

原Hive查询 #

create table d_bu as
select
itapp_id,
it_acronym,
concat_ws(',',sort_array(split(concat_ws(":", collect_set(it_unit)),':'))) as merged_BU,
case when lower(domain) in ('csms','ucmdb') then 'yes' when lower(line_of_business) in ('psbms','retail','hosted') then 'yes' else 'no' end as heritage
from db.table_bkp
group by itapp_id,
it_acronym,
domain,
line_of_business;

错误的BigQuery转换尝试 #

你给出的转换代码执行时触发错误 Aggregate function array_agg is not allowed under Unnest，错误代码如下：

CREATE TABLE db.d_bu
 AS
  SELECT
    table_bkp.itapp_id,
    table_bkp.it_acronym,
    array_to_string(ARRAY (
     SELECT
       v
      FROM
       UNNEST(split(array_to_string(array_agg(DISTINCT table_bkp.it_unit), ':'), ':')) AS v
     ORDER BY
      v
    ), ',') AS merged_bu,
    CASE
     WHEN lower(table_bkp.domain) IN(
      'csms', 'ucmdb'
     ) THEN 'yes'
     WHEN lower(table_bkp.line_of_business) IN(
      'psbms', 'retail', 'hosted'
     ) THEN 'yes'
     ELSE 'no'
    END AS heritage
   FROM
    db.table_bkp
   GROUP BY 1, 2, table_bkp.domain, table_bkp.line_of_business
;

错误原因 #

问题出在嵌套子查询中，你在UNNEST的上下文里直接调用了外层的聚合函数array_agg，BigQuery不允许在这种嵌套子查询中引用未提前计算的聚合结果。原Hive逻辑是先收集去重的it_unit用冒号拼接，拆分后排序再用逗号拼接，其实可以用BigQuery的原生数组函数更简洁实现，无需嵌套子查询。

修正后的BigQuery查询 #

利用BigQuery的ARRAY_AGG(DISTINCT ...)直接生成去重数组，再用ARRAY_SORT排序，最后通过ARRAY_TO_STRING转成逗号分隔的字符串，完全匹配原Hive逻辑：

CREATE TABLE db.d_bu
AS
SELECT
  itapp_id,
  it_acronym,
  ARRAY_TO_STRING(ARRAY_SORT(ARRAY_AGG(DISTINCT it_unit)), ',') AS merged_bu,
  CASE
    WHEN LOWER(domain) IN ('csms', 'ucmdb') THEN 'yes'
    WHEN LOWER(line_of_business) IN ('psbms', 'retail', 'hosted') THEN 'yes'
    ELSE 'no'
  END AS heritage
FROM db.table_bkp
GROUP BY itapp_id, it_acronym, domain, line_of_business;

这样既避免了嵌套子查询的问题，又简化了代码逻辑，执行效率也更高。

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