Looker Studio连接含数组的BigQuery表时遇重复别名错误

问题场景 #

在Looker Studio（原Google Data Studio）连接BigQuery时遇到如下错误：

Query error: Duplicate alias paid_per_stats found at [line number]

连接的是包含嵌套数组的表，在BigQuery连接器中为两个周期分别创建了p1_paid_skus和p2_paid_skus两个不同别名，但在报表中创建跨周期对比的计算字段时，表加载失败并抛出重复别名错误。单独使用任一周期的相关字段时一切正常，推测Looker Studio并未使用完整字段路径作为别名，仅取嵌套字段的最后一部分导致冲突。

连接器代码 #

select 
a.period_start_dt = date(@p1) as is_p1,
case when a.period_start_dt = date(@p1) then a.paid_skus else null end as p1_paid_skus, 
case when a.period_start_dt = date(@p2) then a.paid_skus else null end as p2_paid_skus

from campaign_kpis a
where (a.period_start_dt = date(@p1) or a.period_start_dt = date(@p2))

a.paid_skus的Schema片段 #

-- other stuff
period_start_dt date,
paid_skus array<struct<
    sku_id string,
    paid_per_stats array<struct<
        sample_dt date,
        kpi1 int,
        kpi2 int
        >>
    >>

触发错误的计算字段示例 #

(sum(p1_paid_skus.paid_per_stats.kpi1)-sum(p2_paid_skus.paid_per_stats.kpi1))/sum(p2_paid_skus.paid_per_stats.kpi1)

解决思路 #

方案1：重命名嵌套数组字段，避免别名冲突 #

修改BigQuery连接器的SQL查询，在生成p1_paid_skus和p2_paid_skus时，将内部的paid_per_stats字段分别重命名为唯一的别名，比如p1_paid_per_stats和p2_paid_per_stats：

select 
a.period_start_dt = date(@p1) as is_p1,
-- 构造p1的嵌套结构并重命名内层数组
case when a.period_start_dt = date(@p1) then 
  array(
    select as struct 
      sku_id, 
      paid_per_stats as p1_paid_per_stats 
    from unnest(a.paid_skus)
  ) 
else null end as p1_paid_skus,
-- 构造p2的嵌套结构并重命名内层数组
case when a.period_start_dt = date(@p2) then 
  array(
    select as struct 
      sku_id, 
      paid_per_stats as p2_paid_per_stats 
    from unnest(a.paid_skus)
  ) 
else null end as p2_paid_skus

from campaign_kpis a
where (a.period_start_dt = date(@p1) or a.period_start_dt = date(@p2))

之后在Looker Studio的计算字段中，使用更新后的字段路径：

(sum(p1_paid_skus.p1_paid_per_stats.kpi1)-sum(p2_paid_skus.p2_paid_per_stats.kpi1))/sum(p2_paid_skus.p2_paid_per_stats.kpi1)

方案2：扁平化嵌套数组，直接使用顶层字段 #

在BigQuery查询中提前展开所有嵌套数组，将多维结构转为扁平化的表结构，这样Looker Studio无需处理嵌套字段的别名问题：

select 
a.period_start_dt = date(@p1) as is_p1,
case when a.period_start_dt = date(@p1) then sku.sku_id else null end as p1_sku_id,
case when a.period_start_dt = date(@p1) then stats.kpi1 else null end as p1_kpi1,
case when a.period_start_dt = date(@p1) then stats.kpi2 else null end as p1_kpi2,
case when a.period_start_dt = date(@p2) then sku.sku_id else null end as p2_sku_id,
case when a.period_start_dt = date(@p2) then stats.kpi1 else null end as p2_kpi1,
case when a.period_start_dt = date(@p2) then stats.kpi2 else null end as p2_kpi2
from campaign_kpis a
cross join unnest(a.paid_skus) as sku
cross join unnest(sku.paid_per_stats) as stats
where (a.period_start_dt = date(@p1) or a.period_start_dt = date(@p2))

此时计算字段可以直接使用扁平化后的字段：

(sum(p1_kpi1)-sum(p2_kpi1))/sum(p2_kpi1)

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