dbt+BigQuery报错：不允许按STRUCT类型表达式分组问题排查

我是dbt和BigQuery新手，搭建了一个从3张表取数的简单数据管道。在BigQuery中直接运行的关联分组查询可正常得到结果，但通过dbt转换后执行，出现报错“Grouping by expressions of type STRUCT is not allowed”。怀疑dbt将status表转换为STRUCT类型，附上相关代码及配置：

原BigQuery查询 #

select sta.status, concat(dri.forename, ' ' ,dri.surname) driver_name, count(sta.status) as number_of_fails
    from `dts_staging_f1_data.results` res
    inner join `dts_staging_f1_data.status` sta on sta.statusId = res.statusId
    inner join `dts_staging_f1_data.drivers` dri on dri.driverId = res.driverId
    where res.statusId not in (1, 11, 12, 13, 14, 15, 16, 17, 18, 19, 45, 50, 128,53,55,58,88,111,112,113,114,115,116,117,118,119,120,122,123,124,125,127,133,134)
    group by sta.status, dri.forename, dri.surname
    having count(sta.status) > 1
    order by count(sta.status) desc, sta.status ;

dbt生成的查询 #

with results as (
    select statusId, driverId from `omitted-project-id`.`dts_staging_f1_data`.`results`
),
 ref_status as (
    select status,statusId from `omitted-project-id`.`dts_staging_f1_data`.`status`
),
drivers as (
    select driverId,forename, surname from `omitted-project-id`.`dts_staging_f1_data`.`drivers`
),

final as (
    select ref_status.status, concat(drivers.forename, ' ', drivers.surname) as driver_name, count(ref_status.status) as number_of_fails
    from results
    inner join ref_status using (statusId)
    inner join drivers using (driverId)
    where results.statusId not in (1, 11, 12, 13, 14, 15, 16, 17, 18, 19, 45, 50, 128,53,55,58,88,111,112,113,114,115,116,117,118,119,120,122,123,124,125,127,133,134)
    group by ref_status.status, drivers.forename, drivers.surname
    order by count(ref_status.status) desc, ref_status.status 
)

select * from final

models.yml配置 #

version: 2

sources:
  - name: src-bq-de-f1-project
    schema: dts_staging_f1_data
    tables: 
      - name: results
      - name: status
      - name: drivers

问题原因 #

报错的核心是**status是BigQuery的保留关键字**，直接引用ref_status.status时，BigQuery会误将其解析为STRUCT类型（而非字段），导致分组操作被禁止。

解决方法 #

方式1：给status字段起别名 #

在ref_status的CTE中将status重命名为非保留字，后续查询统一使用别名：

with results as (
    select statusId, driverId from `omitted-project-id`.`dts_staging_f1_data`.`results`
),
 ref_status as (
    select status as status_desc, statusId from `omitted-project-id`.`dts_staging_f1_data`.`status`
),
drivers as (
    select driverId,forename, surname from `omitted-project-id`.`dts_staging_f1_data`.`drivers`
),

final as (
    select ref_status.status_desc, concat(drivers.forename, ' ', drivers.surname) as driver_name, count(ref_status.status_desc) as number_of_fails
    from results
    inner join ref_status using (statusId)
    inner join drivers using (driverId)
    where results.statusId not in (1, 11, 12, 13, 14, 15, 16, 17, 18, 19, 45, 50, 128,53,55,58,88,111,112,113,114,115,116,117,118,119,120,122,123,124,125,127,133,134)
    group by ref_status.status_desc, drivers.forename, drivers.surname
    order by count(ref_status.status_desc) desc, ref_status.status_desc 
)

select * from final

方式2：用反引号包裹保留字字段 #

在所有引用status字段的地方加反引号，明确告知BigQuery这是字段名：

with results as (
    select statusId, driverId from `omitted-project-id`.`dts_staging_f1_data`.`results`
),
 ref_status as (
    select `status`, statusId from `omitted-project-id`.`dts_staging_f1_data`.`status`
),
drivers as (
    select driverId,forename, surname from `omitted-project-id`.`dts_staging_f1_data`.`drivers`
),

final as (
    select ref_status.`status`, concat(drivers.forename, ' ', drivers.surname) as driver_name, count(ref_status.`status`) as number_of_fails
    from results
    inner join ref_status using (statusId)
    inner join drivers using (driverId)
    where results.statusId not in (1, 11, 12, 13, 14, 15, 16, 17, 18, 19, 45, 50, 128,53,55,58,88,111,112,113,114,115,116,117,118,119,120,122,123,124,125,127,133,134)
    group by ref_status.`status`, drivers.forename, drivers.surname
    order by count(ref_status.`status`) desc, ref_status.`status` 
)

select * from final

额外建议 #

• 尽量避免用BigQuery保留关键字作为表名或字段名，减少解析类问题
• 在dbt中引用source时，使用{{ source('src-bq-de-f1-project', 'status') }}语法，dbt会自动处理保留字的反引号包裹：

ref_status as (
    select `status`, statusId from {{ source('src-bq-de-f1-project', 'status') }}
)

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