BigQuery中从assets字段提取指定子串生成asset_code字段的方法

需求场景 #

你有一个assets字段，值为类似//bigquery.googleapis.com/projects/ABC/datasets/123的BigQuery资源路径，需要提取路径中//bigquery.googleapis.com/projects/之后、/datasets/之前的部分，生成新字段asset_code。

解决方案 #

使用BigQuery的REGEXP_EXTRACT函数，结合正则表达式精准匹配目标区间：

SELECT
  assets,
  REGEXP_EXTRACT(assets, r'//bigquery\.googleapis\.com/projects/([^/]+)/datasets/') AS asset_code
FROM
  your_table_name

正则逻辑说明 #

• //bigquery\.googleapis\.com/projects/：匹配固定前缀，其中\.是转义后的点（正则中.默认匹配任意字符，必须转义才能匹配真实的点）
• ([^/]+)：核心捕获组，[^/]表示匹配非斜杠的任意字符，+表示匹配1次或多次，精准截取两个斜杠之间的项目名称
• /datasets/：匹配固定后缀，确保只提取到该标记之前的内容

测试验证 #

可以用如下语句模拟数据验证效果：

WITH sample_data AS (
  SELECT '//bigquery.googleapis.com/projects/ABC/datasets/123' AS assets UNION ALL
  SELECT '//bigquery.googleapis.com/projects/BlaBla-something/datasets/12345' UNION ALL
  SELECT '//bigquery.googleapis.com/projects/ProjectName/datasets/6789'
)
SELECT
  assets,
  REGEXP_EXTRACT(assets, r'//bigquery\.googleapis\.com/projects/([^/]+)/datasets/') AS asset_code
FROM sample_data

运行后得到结果：

为什么substr不适合？ #

substr需要指定固定的起始位置和截取长度，但不同项目名称的长度不一致，无法用统一的数值参数适配所有场景，而正则表达式可以根据分隔符动态定位，更灵活可靠。

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