使用正则表达式提取BigQuery SQL中的数据集与表名

需求说明 #

现有一张存储BigQuery所有执行过的查询语句的表，需新增dataset和table两列，拆分语句中以点分隔的数据集名与表名，支持处理包含嵌套子查询的场景。

示例数据 #

解决方案 #

1. 提取单条查询中的第一个关联表（主查询表）

使用BigQuery正则提取函数，匹配from或create table后的表名格式，提取数据集和表名：

SELECT
  Queries,
  -- 提取完整表名（可选）
  REGEXP_EXTRACT(Queries, r'(?:from|create\s+table)\s+`?([a-zA-Z0-9_]+)\.([a-zA-Z0-9_]+)`?') AS full_table,
  -- 提取数据集名称
  REGEXP_EXTRACT(Queries, r'(?:from|create\s+table)\s+`?([a-zA-Z0-9_]+)\.') AS dataset,
  -- 提取表名
  REGEXP_EXTRACT(Queries, r'(?:from|create\s+table)\s+`?[a-zA-Z0-9_]+\.([a-zA-Z0-9_]+)`?') AS table
FROM
  `your-project.your-dataset.your-table`

2. 提取查询中所有关联表（含子查询中的表，拆分为多行）

如果需要捕获查询中所有出现的表（比如嵌套子查询里的表），可结合REGEXP_EXTRACT_ALL和UNNEST将多表拆分为独立行：

WITH extracted_tables AS (
  SELECT
    Queries,
    -- 匹配所有from/create table后的表名对
    REGEXP_EXTRACT_ALL(Queries, r'(?:from|create\s+table)\s+`?([a-zA-Z0-9_]+)\.([a-zA-Z0-9_]+)`?') AS table_pairs
  FROM
    `your-project.your-dataset.your-table`
)
SELECT
  Queries,
  -- 拆分数据集名称
  SPLIT(table_pair, '.')[OFFSET(0)] AS dataset,
  -- 拆分表名
  SPLIT(table_pair, '.')[OFFSET(1)] AS table
FROM
  extracted_tables,
  UNNEST(table_pairs) AS table_pair

正则说明 #

• (?:from|create\s+table)：非捕获组，匹配from关键字或create table短语，兼容查询和建表语句
• \s+：匹配一个或多个空格，处理语句中的空格格式差异
• `?：匹配可选的反引号（BigQuery中表名可加反引号包裹）
• ([a-zA-Z0-9_]+)：捕获数据集/表名，支持BigQuery标识符的合法字符

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