
本文介绍如何使用 sqlglot 的优化器模块(qualify_columns 和 traverse_scope)准确解析任意复杂 SQL(含 CTE、子查询、JOIN、WHERE 子句等),自动关联列名到其物理来源(数据库、表名),并标注其所属语法作用域,最终输出结构化 DataFrame。
本文介绍如何使用 sqlglot 的优化器模块(`qualify_columns` 和 `traverse_scope`)准确解析任意复杂 sql(含 cte、子查询、join、where 子句等),自动关联列名到其物理来源(数据库、表名),并标注其所属语法作用域,最终输出结构化 dataframe。
在数据治理、血缘分析、动态元数据采集等场景中,仅靠正则或简单 AST 遍历难以可靠识别列的真实来源——尤其当 SQL 包含嵌套子查询、多层别名、隐式 JOIN 或 WHERE 中的 SELECT 时,传统方法极易丢失表/库上下文,导致 table 或 database 字段为空。而 sqlglot.optimizer 提供的语义感知能力,正是解决这一问题的关键。
核心思路是:先通过 qualify_columns 对原始 AST 进行列名标准化(即为每个 Column 节点绑定其可解析的源表和数据库信息),再利用 traverse_scope 按作用域(Scope)逐层遍历,从每个作用域的 sources 映射中安全提取物理表对象及其属性。
以下为完整、健壮的实现方案:
from sqlglot.optimizer.qualify_columns import qualify_columns
from sqlglot.optimizer.scope import traverse_scope
from sqlglot import parse, exp
import pandas as pd
def parse_query(sql_query: str, dialect: str = "postgres") -> pd.DataFrame:
"""
解析 SQL 查询,提取所有列的物理来源(库、表、列名)及其所在语法作用域(如 CREATE 表名、子查询别名等)
Args:
sql_query: 待解析的 SQL 字符串
dialect: SQL 方言(如 'postgres', 'snowflake', 'tsql'),影响解析准确性
Returns:
pd.DataFrame: 包含列、表、数据库、作用域四列的去重结果
"""
df_list = []
# 支持多语句(如批量 DDL + DML)
for ast in parse(sql_query, read=dialect):
# 关键步骤:语义补全 —— 为每个 Column 节点注入其所属 Table 和 Database 信息
try:
ast = qualify_columns(ast, schema=None) # schema 可选传入 dict 实现更精确推断
except Exception as e:
raise ValueError(f"SQL 语义补全失败,请检查语法是否符合 {dialect} 方言: {e}")
# 推导当前语句的顶层作用域标识(如 CREATE TABLE TRD → section='TRD';SELECT → section='SELECT')
if isinstance(ast, exp.Create):
section = ast.this.name.upper() if hasattr(ast.this, 'name') else "CREATE"
elif isinstance(ast, exp.With):
section = ast.expressions[0].alias_or_name.upper() if ast.expressions else "WITH"
else:
section = str(ast.key).upper()
physical_columns = []
# 遍历每个逻辑作用域(Scope),每个 Scope 对应一个独立的 FROM/JOIN 上下文
for scope in traverse_scope(ast):
for column in scope.columns:
# column.table 是别名(可能为空),scope.sources 是 {alias: table_expr} 映射
table_expr = scope.sources.get(column.table)
# 安全提取物理表名与库名(兼容无别名、CTE、子查询等场景)
if isinstance(table_expr, exp.Table):
db_name = table_expr.db.name if hasattr(table_expr, 'db') and table_expr.db else None
table_name = table_expr.name
elif isinstance(table_expr, exp.Subquery):
# 子查询本身无物理表,但其内部可能有真实表;此处记录为 subquery_<alias>
table_name = f"subquery_{column.table}" if column.table else "anonymous_subquery"
db_name = None
else:
# CTE 或其他非 Table 类型,降级为别名
table_name = column.table or "unknown"
db_name = None
physical_columns.append((
section,
db_name,
table_name,
column.name
))
# 构建并去重 DataFrame
df = pd.DataFrame(
physical_columns,
columns=["query_section", "database", "table", "column"]
).drop_duplicates().reset_index(drop=True)
df_list.append(df)
return pd.concat(df_list, ignore_index=True) if df_list else pd.DataFrame(
columns=["query_section", "database", "table", "column"]
)✅ 关键优势说明:
-
qualify_columns自动处理列名歧义:例如TR.REQUEST_ID和P17.REQUEST_ID在不同 JOIN 表中被正确绑定; -
traverse_scope天然支持嵌套作用域:WHERE 中的子查询、ON 条件、CTE 均生成独立 Scope,确保MY_TNG_REQUESTS的列不会被误标为外层表; - 严格区分逻辑别名(
column.table)与物理表名(table_expr.name),避免返回P17而非FSICS_IPSS_TRNG_REQUESTS_DET; - 兼容主流方言(PostgreSQL、Snowflake、T-SQL 等),只需指定
dialect参数; - 错误防御强:对非法 SQL 抛出明确提示,不静默失败。
⚠️ 注意事项:
- 若 SQL 含跨库引用(如
db1.schema1.tbl1 JOIN db2.schema2.tbl2),需传入schema参数(dict结构)以提升qualify_columns推断精度; -
exp.Subquery类型不直接提供.db属性,故子查询内列的database默认为None,如需进一步解析,可递归调用traverse_scope; - 对于
UNION多分支查询,各分支独立作用域,列将按实际来源分别记录。
运行示例 SQL 即可获得与预期完全一致的结果(含 REQUEST_ID 在 MY_TNG_REQUESTS 中的正确映射),且代码简洁、可维护性高,是构建 SQL 元数据引擎的推荐范式。

















