BigQuery中如何在UNNEST语句内实现数组元素的类型转换
实现方式
查询内实时转换(零表结构调整)
如果不需要修改原有表的存储结构,仅在当前查询中实现整数匹配,可以在原有逻辑基础上增加类型适配逻辑,处理生产环境常见的脏值场景(拆分元素带空格、空串、非数字无效值):
SELECT campaign, event_list, -- 实时生成ARRAY<INT64>类型的integer_event_list ARRAY( SELECT SAFE_CAST(TRIM(ev) AS INT64) FROM UNNEST(SPLIT(event_list, ",")) ev WHERE TRIM(ev) != '' ) AS integer_event_list FROM `adobe_analytics.raw_data_20200*` WHERE campaign IS NOT NULL -- 直接用整数1完成匹配,替代原字符串"1"的判断逻辑 AND 1 IN UNNEST( ARRAY( SELECT SAFE_CAST(TRIM(ev) AS INT64) FROM UNNEST(SPLIT(event_list, ",")) ev WHERE TRIM(ev) != '' ) )
实现细节说明:
- 用
SAFE_CAST而非直接CAST:Adobe原始采集的event_list常夹带非数字标识、空值,直接CAST会触发类型转换报错导致整个查询失败,SAFE_CAST遇到无法转换的值会自动返回NULL,不影响查询执行 - 增加
TRIM处理:拆分后的元素经常带前后空格(比如采集的event_list格式为"1, 2,3"),不做去空格处理会导致" 1"这类值CAST失败变成NULL,漏匹配有效数据 - 过滤空串:拆分过程中会因连续逗号产生空字符串元素,提前过滤可以减少无意义的NULL值,不影响匹配准确性
永久新增integer_event_list列
如果后续查询会频繁用到整数事件数组,不需要每次重复写转换逻辑,可以通过BigQuery计算列功能,直接给表新增ARRAY<INT64>类型的integer_event_list列,列值自动根据原event_list字段计算,不需要手动跑ETL刷数:
-- 对每个分区执行以下语句(通配符表不支持直接ALTER,建议逐表调整) ALTER TABLE `adobe_analytics.raw_data_20200*` ADD COLUMN integer_event_list ARRAY<INT64> AS ( ARRAY( SELECT SAFE_CAST(TRIM(ev) AS INT64) FROM UNNEST(SPLIT(event_list, ",")) ev WHERE TRIM(ev) != '' ) )
调整完成后,后续查询就可以直接按照你期望的简化逻辑编写,不需要重复写转换步骤:
SELECT campaign, event_list, integer_event_list FROM `adobe_analytics.raw_data_20200*` WHERE campaign IS NOT NULL AND 1 IN UNNEST(integer_event_list)
如果单表数据量极大,每次实时计算整数数组的查询成本偏高,可以将该计算列改为物化列,预先把历史分区的转换结果持久化存储,进一步降低查询耗时。
内容的提问来源于stack exchange,提问作者Imad
相关产品推荐
相关产品推荐

