如何用SQLFluff提取嵌套SELECT语句并改造为CTAS结构

核心思路 #

通过SQLFluff的语法树（AST）遍历精准定位嵌套子查询节点，分别修改生成CTAS语句和引用该表的外层查询，避开正则表达式在复杂SQL场景下的局限性。

实现步骤与代码示例 #

假设目标SQL为：

select
    *
from
    (select
        a,
        b
    from
        agents)

1. 解析SQL获取语法树

from sqlfluff.core import Parser
from sqlfluff.core.parser.segments import ParenthesesSegment

# 初始化ANSI方言解析器
parser = Parser(dialect='ansi')
query = """select
    *
from
    (select
        a,
        b
    from
        agents)"""
parsed_tree = parser.parse_string(query)

2. 定位子查询节点

遍历语法树，找到FROM子句中包裹子查询的括号节点：

subquery_parentheses = None
# 遍历外层SELECT语句，定位FROM子句内的子查询括号
for segment in parsed_tree.segments:
    if segment.is_type('select_statement'):
        for select_segment in segment.segments:
            if select_segment.is_type('from_clause'):
                for from_segment in select_segment.segments:
                    if isinstance(from_segment, ParenthesesSegment):
                        subquery_parentheses = from_segment
                        break
                break
        break

# 提取括号内的子查询核心内容
inner_subquery = subquery_parentheses.segments[1]

3. 生成CTAS语句

将子查询内容拼接为CTAS格式：

ctas_table_name = 'reagents'
# 拼接CTAS语句
ctas_sql = f"create or replace table {ctas_table_name} as {inner_subquery.raw}"
print("CTAS语句：")
print(ctas_sql)
# 输出：create or replace table reagents as select a, b from agents

4. 生成修改后的外层查询

将原FROM后的子查询替换为CTAS表名：

# 复制原语法树，避免修改原始结构
outer_query_tree = parsed_tree.copy()

# 替换FROM子句中的子查询为表名
for segment in outer_query_tree.segments:
    if segment.is_type('select_statement'):
        for select_segment in segment.segments:
            if select_segment.is_type('from_clause'):
                new_from_segments = []
                for from_segment in select_segment.segments:
                    if isinstance(from_segment, ParenthesesSegment):
                        # 创建表名节点替换子查询括号
                        new_from_segments.append(parser.segment_builder.raw_segment(ctas_table_name))
                    else:
                        new_from_segments.append(from_segment)
                select_segment.segments = new_from_segments
                break
        break

# 生成外层查询SQL
outer_sql = outer_query_tree.raw
print("\n外层查询语句：")
print(outer_sql)
# 输出：select * from reagents

关键说明 #

• 该方法基于SQLFluff语法树结构，可处理多层嵌套、带WHERE/JOIN的复杂子查询场景，比正则表达式更稳定。
• 若需处理多层嵌套，可通过递归遍历语法树定位所有子查询节点，按需批量转换。

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