如何用正则提取SQL语句中指定关键词前的数据集与表名

需求：从格式不规范的SQL查询语句中分离出数据集名和表名，要求通过字符串中的点进行拆分，且该点所在的表引用需位于from、join、drop table、into这些关键词之后。

输入数据示例 #

期望输出 #

每个符合条件的数据集和表名需与原查询对应，单条查询含多个目标时分行显示：

• 原查询：select "a"."col_1" , "b"."col_2" from "advp"."accounts" a inner join "advp"."finance" b
  • 数据集：advp，表名：accounts
  • 数据集：advp，表名：finance
• 原查询：drop table "gqd"."customers"
  • 数据集：gqd，表名：customers
• 原查询：insert into "cvb"."stores" a where "a"."cols" = "some value"
  • 数据集：cvb，表名：stores
• 原查询：drop table "dataset"."table_name"
  • 数据集：dataset，表名：table_name

尝试过的方法（未成功） #

select query, Regrex_extract_all(query, r'([a-zA-Z0-9]+)\"\.\"') as dataset from table

解决方案 #

核心思路是利用正则表达式的正向预查，先匹配指定关键词的位置，再提取其后的数据集和表名。以下是适配常用SQL引擎的实现：

1. Spark SQL / Hive SQL #

使用regexp_extract_all函数结合正向预查正则，匹配关键词后的带引号的数据集和表名：

SELECT 
    query,
    -- 拆分提取数据集名
    regexp_extract_all(query, r'(?i)(?:from|join|drop\s+table|into)\s*\"([^\"]+)\"\.\"([^\"]+)\"', 1) AS datasets,
    -- 拆分提取表名
    regexp_extract_all(query, r'(?i)(?:from|join|drop\s+table|into)\s*\"([^\"]+)\"\.\"([^\"]+)\"', 2) AS tables
FROM your_table;

• 正则说明：
  • (?i)：忽略大小写，适配SQL关键词的大小写变体（如FROM/from）
  • (?:from|join|drop\s+table|into)：匹配目标关键词（非捕获组，避免多余匹配结果）
  • \s*：匹配关键词后的任意空白字符
  • \"([^\"]+)\"\.\"([^\"]+)\"：匹配带双引号的数据集名和表名，分别捕获两组内容

2. PostgreSQL #

PostgreSQL使用regexp_matches函数，需结合unnest展开多匹配结果：

SELECT 
    t.query,
    m.dataset,
    m.table_name
FROM your_table t,
LATERAL (
    SELECT 
        (regexp_matches(t.query, r'(?i)(?:from|join|drop\s+table|into)\s*"([^"]+)"\."([^"]+)"', 'g'))[1] AS dataset,
        (regexp_matches(t.query, r'(?i)(?:from|join|drop\s+table|into)\s*"([^"]+)"\."([^"]+)"', 'g'))[2] AS table_name
) m;

• 'g'参数表示全局匹配，提取所有符合条件的结果

3. 处理无引号的情况（可选） #

如果存在不带双引号的表引用（如from advp.accounts），可修改正则兼容两种格式：

-- Spark SQL示例，兼容带/不带引号的情况
regexp_extract_all(query, r'(?i)(?:from|join|drop\s+table|into)\s*(?:\"?([^\".\"]+)\"?)\.(?:\"?([^\".\"]+)\"?)', 1)

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