Google BigQuery中存储SELECT查询结果的数组变量类型定义

需求背景 #

出于实际使用需要，需保存EXECUTE IMMEDIATE的执行结果，后续作为CTE复用。由于EXECUTE IMMEDIATE无法直接在CTE中使用，选择将结果存入变量的实现方案。
本次操作的目标表my_ga4.analytics_9999.events_20220101为标准GA4数据表，需要确认数组变量的元素类型声明方式。
初始待补全的代码如下：

DECLARE event_example ARRAY<???>; -- 待补全类型定义位置

EXECUTE IMMEDIATE FORMAT(
    "%s", (
        SELECT platform, event_name, event_params FROM `my_ga4.analytics_9999.events_20220101` LIMIT 10
    )
) INTO event_example;

尝试编写的自定义类型定义代码如下：

DECLARE event_example ARRAY<
    STRUCT<
        platform STRING,
        event_name STRING,
        event_params ARRAY<
            STRUCT<
                key STRING,
                value STRUCT<
                    string_value STRING,
                    int_value    INT64,
                    double_value FLOAT64,
                    float_value  FLOAT64
                >
            >
        >
    >
>;

解答说明 #

• 上述手动编写的STRUCT类型定义存在字段缺失问题，不符合GA4 events表的标准Schema结构，直接运行会触发类型不匹配错误。
• 最简便的合规声明方式无需手动逐一枚举STRUCT字段，BigQuery支持直接引用目标表的行类型，自动匹配所有字段结构，声明语法为：

-- 直接绑定目标表行结构，无需手动写字段定义
DECLARE event_example ARRAY<STRUCT<`my_ga4.analytics_9999.events_20220101`>>;

• 如果仅需要存储查询返回的platform/event_name/event_params三个字段，手动定义STRUCT时需要补全缺失字段：GA4标准Schema中event_params.value结构体还包含set_timestamp_micros INT64字段，补全后才是合规类型，示例如下：

DECLARE event_example ARRAY<
    STRUCT<
        platform STRING,
        event_name STRING,
        event_params ARRAY<
            STRUCT<
                key STRING,
                value STRUCT<
                    string_value STRING,
                    int_value    INT64,
                    double_value FLOAT64,
                    float_value  FLOAT64,
                    set_timestamp_micros INT64 -- 原有定义缺失的字段
                >
            >
        >
    >
>;

• 原有代码中EXECUTE IMMEDIATE的写法存在语法错误，FORMAT("%s", 嵌套子查询)无法正确拼接SQL语句，修正后的完整可运行代码如下：

DECLARE event_example ARRAY<STRUCT<`my_ga4.analytics_9999.events_20220101`>>;

EXECUTE IMMEDIATE """
    SELECT platform, event_name, event_params 
    FROM `my_ga4.analytics_9999.events_20220101` 
    LIMIT 10
""" INTO event_example;

-- 后续复用为CTE的示例写法
-- WITH temp_cte AS (SELECT * FROM UNNEST(event_example))
-- SELECT * FROM temp_cte;

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