时间戳

首页 > 解决方案 > 如何从 SQL 查询中的 CREATE/UPDATE/INSERT 语句中提取表名？

sql - 如何从 SQL 查询中的 CREATE/UPDATE/INSERT 语句中提取表名？

问题描述

我正在尝试解析从存储在表列中的以下 sql 查询创建、插入或更新的表。

让我们调用表列query。以下是一些示例数据，用于演示数据外观的变化。

with sample_data as (
  select 1 as id, 'CREATE TABLE tbl1 ...' as query union all
  select 2 as id, 'CREATE OR REPLACE TABLE tbl1 ...' as query union all
  select 3 as id, 'DROP TABLE IF EXISTS tbl1; CREATE TABLE tbl1 ...' as query union all
  select 4 as id, 'INSERT /*some comment*/ INTO tbl2 ...' as query union all
  select 5 as id, 'INSERT /*some comment*/ INTO tbl2 ...' as query union all
  select 6 as id, 'UPDATE tbl3 SET col1 = ...' as query union all
  select 7 as id, '/*some garbage comments*/ UPDATE tbl3 SET col1 = ...' as query union all  
  select 8 as id, 'DELETE tbl4 ...' as query
),

以下是查询的格式（我们正在尝试提取table_name）：

#1 some optional statements like drop table 创建some comments or optional statement like OR REPLACE表table_name everything else

# 2some optional statements like drop table 插入some commentssome comments table_name

#3 some optional statements like drop table 更新some comments table_name everything else

标签： sqlregexparsinggoogle-bigquerydata-extraction

解决方案

正则表达式

为了构造一个合适的正则表达式，让我们从以下相对简单/可读的版本开始：

((CREATE( OR REPLACE)?|DROP) TABLE( IF EXISTS)?|UPDATE|DELETE|INSERT INTO) ([^\s\/*]+)

上面的所有空格都可以替换为“至少一个空格字符”，即\s+. 但我们也需要允许评论。对于看起来像/*anything*/正则表达式\/\*.*\*\/ 的评论（其中评论字符被转义\并且“任何东西”.*在中间）。鉴于可能有多个这样的注释，可选地用空格分隔，我们最终得到(\s*\/\*.*\*\/\s*?)*\s+. 将其插入任何有空间的地方都会给出：

((CREATE((\s*\/\*.*\*\/\s*?)*\s+OR(\s*\/\*.*\*\/\s*?)*\s+REPLACE)?|DROP)(\s*\/\*.*\*\/\s*?)*\s+TABLE((\s*\/\*.*\*\/\s*?)*\s+IF(\s*\/\*.*\*\/\s*?)*\s+EXISTS)?|UPDATE|DELETE|INSERT(\s*\/\*.*\*\/\s*?)*\s+INTO)(\s*\/\*.*\*\/\s*?)*\s+([^\s\/*]+)

需要进一步改进：括号中的表达式已用于选择，例如(CHOICE1|CHOICE2)。但是这种语法将它们包括为捕获组。实际上，我们只需要一个捕获组作为表名，因此我们可以通过排除所有其他捕获组?:，例如(?:CHOICE1|CHOICE2)。这给出了：

(?:(?:CREATE(?:(?:\s*\/\*.*\*\/\s*?)*\s+OR(?:\s*\/\*.*\*\/\s*?)*\s+REPLACE)?|DROP)(?:\s*\/\*.*\*\/\s*?)*\s+TABLE(?:(?:\s*\/\*.*\*\/\s*?)*\s+IF(?:\s*\/\*.*\*\/\s*?)*\s+EXISTS)?|UPDATE|DELETE|INSERT(?:\s*\/\*.*\*\/\s*?)*\s+INTO)(?:\s*\/\*.*\*\/\s*?)*\s+([^\s\/*]+)

在线正则表达式演示

这是与您的示例一起使用的演示：Regex101 demo

SQL

REGEXP_EXTRACT的 Google BigQuery 文档说它将返回与捕获组匹配的子字符串。所以我希望这样的事情能够奏效：

with sample_data as (
  select 1 as id, 'CREATE TABLE tbl1 ...' as query union all
  select 2 as id, 'CREATE OR REPLACE TABLE tbl1 ...' as query union all
  select 3 as id, 'DROP TABLE IF EXISTS tbl1; CREATE TABLE tbl1 ...' as query union all
  select 4 as id, 'INSERT /*some comment*/ INTO tbl2 ...' as query union all
  select 5 as id, 'INSERT /*some comment*/ INTO tbl2 ...' as query union all
  select 6 as id, 'UPDATE tbl3 SET col1 = ...' as query union all
  select 7 as id, '/*some garbage comments*/ UPDATE tbl3 SET col1 = ...' as query union all  
  select 8 as id, 'DELETE tbl4 ...' as query
)

SELECT
  *, REGEXP_EXTRACT(query, r"(?:(?:CREATE(?:(?:\s*\/\*.*\*\/\s*?)*\s+OR(?:\s*\/\*.*\*\/\s*?)*\s+REPLACE)?|DROP)(?:\s*\/\*.*\*\/\s*?)*\s+TABLE(?:(?:\s*\/\*.*\*\/\s*?)*\s+IF(?:\s*\/\*.*\*\/\s*?)*\s+EXISTS)?|UPDATE|DELETE|INSERT(?:\s*\/\*.*\*\/\s*?)*\s+INTO)(?:\s*\/\*.*\*\/\s*?)*\s+([^\s\/*]+)") AS table_name
FROM sample_data;

（以上内容未经测试，如果有任何问题，请在评论中告诉我。）

推荐阅读