SQL验证器使用指南

SQL验证器是Schema Tools的一个独立模块，用于验证Question-SQL对中的SQL语句是否有效。它通过执行EXPLAIN语句来检测SQL语法错误和表结构问题。

功能特性

🔍 使用PostgreSQL的EXPLAIN语句验证SQL有效性
⚡ 支持并发验证，大幅提升验证效率
🔒 只读模式运行，安全可靠
📊 详细的验证报告和统计信息
🔄 自动重试机制，处理临时网络问题
📁 支持批处理，适合大量SQL验证
🔧 灵活的配置选项
🤖 新增：LLM自动修复错误SQL（可选启用）
📝 新增：自动修改原始JSON文件，更新修复后的SQL
🗑️ 新增：自动删除无法修复的无效SQL条目
💾 新增：自动创建备份文件，保护原始数据

使用方法

1. 命令行使用

基本用法

python -m schema_tools.sql_validator \
  --db-connection "postgresql://user:pass@localhost:5432/dbname" \
  --input-file ./qs_highway_db_20240101_143052_pair.json

指定输出目录

python -m schema_tools.sql_validator \
  --db-connection "postgresql://user:pass@localhost:5432/dbname" \
  --input-file ./data.json \
  --output-dir ./validation_reports

调整验证参数

python -m schema_tools.sql_validator \
  --db-connection "postgresql://user:pass@localhost:5432/dbname" \
  --input-file ./data.json \
  --max-concurrent 10 \
  --batch-size 20 \
  --timeout 60 \
  --verbose

预检查模式

python -m schema_tools.sql_validator \
  --db-connection "postgresql://user:pass@localhost:5432/dbname" \
  --input-file ./data.json \
  --dry-run

高级选项

# 调整性能参数
python -m schema_tools.sql_validator \
  --db-connection "postgresql://user:pass@localhost:5432/dbname" \
  --input-file ./data.json \
  --max-concurrent 10 \
  --batch-size 20 \
  --timeout 60 \
  --verbose

# 基本使用（仅验证，不修改文件）
python -m schema_tools.sql_validator \
  --db-connection "postgresql://user:pass@localhost:5432/dbname" \
  --input-file ./data.json

# 启用LLM修复功能
python -m schema_tools.sql_validator \
  --db-connection "postgresql://user:pass@localhost:5432/dbname" \
  --input-file ./data.json \
  --enable-llm-repair

# 预检查模式（仅验证文件格式）
python -m schema_tools.sql_validator \
  --db-connection "postgresql://user:pass@localhost:5432/dbname" \
  --input-file ./data.json \
  --dry-run

2. 编程接口使用

import asyncio
from schema_tools import SQLValidationAgent

async def validate_sqls():
    agent = SQLValidationAgent(
        db_connection="postgresql://user:pass@localhost:5432/dbname",
        input_file="./qs_highway_db_20240101_143052_pair.json",
        output_dir="./validation_reports"
    )
    
    report = await agent.validate()
    
    print(f"验证完成:")
    print(f"  总SQL: {report['summary']['total_questions']}")
    print(f"  有效: {report['summary']['valid_sqls']}")
    print(f"  成功率: {report['summary']['success_rate']:.1%}")

asyncio.run(validate_sqls())

文件自动修改功能

功能说明

SQL验证器现在支持自动修改原始JSON文件：

修复成功的SQL：直接更新原始文件中的SQL内容
无法修复的SQL：从原始文件中删除对应的Question-SQL对
自动备份：修改前自动创建备份文件

默认行为

# 默认仅验证，不修改原文件
python -m schema_tools.sql_validator \
  --db-connection "postgresql://user:pass@localhost:5432/dbname" \
  --input-file ./data.json

执行后：

生成验证报告：sql_validation_时间戳_summary.txt
不修改原始文件

仅删除无效SQL

# 仅删除无效SQL，不进行LLM修复
python -m schema_tools.sql_validator \
  --db-connection "postgresql://user:pass@localhost:5432/dbname" \
  --input-file ./data.json \
  --modify-original-file

执行后：

创建备份文件：data.json.backup
修改原文件：data.json（删除验证失败的SQL）
生成修改日志：file_modifications_时间戳.log
生成验证报告：sql_validation_时间戳_summary.txt

启用LLM修复功能

# 启用LLM修复功能（需要同时指定文件修改参数）
python -m schema_tools.sql_validator \
  --db-connection "postgresql://user:pass@localhost:5432/dbname" \
  --input-file ./data.json \
  --enable-llm-repair \
  --modify-original-file

执行后：

创建备份文件：data.json.backup
修改原文件：data.json（更新修复成功的SQL，删除无法修复的SQL）
生成修改日志：file_modifications_时间戳.log
生成验证报告：sql_validation_时间戳_summary.txt

修改日志

每次修改都会生成详细的修改日志文件：

原始JSON文件修改日志
==================================================
修改时间: 2024-01-01 15:30:45
原始文件: ./qs_highway_db_20240101_143052_pair.json
备份文件: ./qs_highway_db_20240101_143052_pair.json.backup

修改的SQL (3个):
----------------------------------------
1. 索引: 5
   问题: 查询订单与车辆数据的相关性分析？
   原SQL: SELECT EXTRACT(year FROM oper_date) AS 年份, COUNT(*) FROM bss_business_day_data GROUP BY 年份;
   新SQL: SELECT EXTRACT(year FROM oper_date)::integer AS 年份, COUNT(*) FROM bss_business_day_data GROUP BY EXTRACT(year FROM oper_date);

删除的无效项 (2个):
----------------------------------------
1. 索引: 12
   问题: 查询不存在的表数据？
   SQL: SELECT * FROM non_existent_table;
   错误: relation "non_existent_table" does not exist


## 输入文件格式

SQL验证器接受标准的Question-SQL对JSON文件，格式如下：

```json
[
  {
    "question": "按服务区统计每日营收趋势（最近30天）？",
    "sql": "SELECT service_name AS 服务区, oper_date AS 营业日期, SUM(pay_sum) AS 每日营收 FROM bss_business_day_data WHERE oper_date >= CURRENT_DATE - INTERVAL '30 day' AND delete_ts IS NULL GROUP BY service_name, oper_date ORDER BY 营业日期 ASC;"
  },
  {
    "question": "查看车流量最大的前10个服务区",
    "sql": "SELECT service_name AS 服务区, SUM(car_count) AS 总车流量 FROM bss_car_day_count WHERE delete_ts IS NULL GROUP BY service_name ORDER BY 总车流量 DESC LIMIT 10;"
  }
]

输出报告

默认输出

验证完成后，默认只生成一个文本摘要报告：

文件名格式：sql_validation_时间戳_summary.txt
包含验证统计信息和完整的错误SQL
便于直接查看和分析错误

可选JSON报告

如果需要程序化处理验证结果，可以启用详细JSON报告：

# 启用JSON报告
python -m schema_tools.sql_validator \
  --db-connection "postgresql://user:pass@localhost:5432/dbname" \
  --input-file ./data.json \
  --save-json

报告内容

文本摘要报告包含：

验证统计：总数、成功率、平均耗时等
SQL修复统计：修复尝试数、成功数、失败数、修复成功率
原始文件修改统计：修改的SQL数量、删除的无效项数量、修改失败数量
完整错误详情：
- 问题描述
- 错误信息
- 完整SQL语句（不截断）
- LLM修复尝试结果
- 修复后的SQL（如果成功）
- 重试次数
成功修复的SQL列表：显示原始错误和修复后的SQL

JSON报告包含：

所有验证结果的详细数据
适合程序化分析和处理

示例输出

验证过程日志

🚀 开始SQL验证...
📁 输入文件: ./qs_highway_db_20240101_143052_pair.json
🔗 数据库: postgresql://***:***@localhost:5432/highway_db
📦 处理批次 1/5 (10 个SQL)
✅ 批次 1 完成: 9/10 有效
📊 验证报告已保存: output/sql_validation_20240101_150000_summary.txt

🎉 验证完成，成功率: 90.0%
📊 详细结果: 45/50 SQL有效

文本摘要报告示例

SQL验证报告
==================================================

输入文件: ./qs_highway_db_20240101_143052_pair.json
验证时间: 2024-01-01T15:00:00
验证耗时: 2.45秒

验证结果摘要:
  总SQL数量: 50
  有效SQL: 45
  无效SQL: 5
  成功率: 90.00%
  平均耗时: 0.049秒
  重试次数: 0

SQL修复统计:
  尝试修复: 5
  修复成功: 3
  修复失败: 2
  修复成功率: 60.00%

原始文件修改统计:
  修改的SQL: 3
  删除的无效项: 2
  修改失败: 0

错误详情（共2个）:
==================================================

1. 问题: 查询订单与车辆数据的相关性分析？  
   错误: 函数 round(double precision, integer) 不存在
   LLM修复尝试: 失败
   修复失败原因: 无法识别正确的函数语法
   完整SQL:
   SELECT a.oper_date, a.order_sum AS 订单数量, b.customer_count AS 车辆数量, 
          ROUND(CORR(a.order_sum, b.customer_count), 2) AS 相关系数
   FROM bss_business_day_data a 
   JOIN bss_car_day_count b ON a.oper_date = b.count_date;
   ----------------------------------------

成功修复的SQL（共3个）:
==================================================

1. 问题: 各区域管理公司单位能耗产出对比（需结合能耗表）
   原始错误: 对于表"e",丢失FROM子句项
   修复后SQL:
   SELECT c.company_name AS 公司名称,
       ROUND(SUM(b.pay_sum)::numeric/SUM(b.order_sum)::numeric, 2) AS 单位订单产出
   FROM bss_company c
   JOIN bss_service_area sa ON c.id = sa.company_id
   JOIN bss_business_day_data b ON sa.service_area_no = b.service_no
   WHERE b.oper_date BETWEEN '2023-01-01' AND '2023-03-31'
   GROUP BY c.company_name
   ORDER BY 单位订单产出 DESC;
   ----------------------------------------

配置选项

SQL验证器的配置位于 schema_tools/config.py 中：

"sql_validation": {
    "reuse_connection_pool": True,       # 复用现有连接池
    "max_concurrent_validations": 5,     # 并发验证数
    "validation_timeout": 30,            # 单个验证超时(秒)
    "batch_size": 10,                    # 批处理大小
    "continue_on_error": True,           # 错误时是否继续
    "save_validation_report": True,      # 保存验证报告
    "readonly_mode": True,               # 启用只读模式
    "max_retry_count": 2,                # 验证失败重试次数
    "report_file_prefix": "sql_validation",  # 报告文件前缀
}

命令行参数说明

参数	类型	必需	说明
`--db-connection`	string	是	PostgreSQL数据库连接字符串
`--input-file`	string	是	输入的JSON文件路径
`--output-dir`	string	否	验证报告输出目录
`--max-concurrent`	int	否	最大并发验证数
`--batch-size`	int	否	批处理大小
`--timeout`	int	否	单个SQL验证超时时间（秒）
`--verbose`	flag	否	启用详细日志输出
`--log-file`	string	否	日志文件路径
`--dry-run`	flag	否	仅读取和解析文件，不执行验证

错误处理机制

超时处理：单个SQL验证超过配置的超时时间会被标记为失败
重试机制：网络相关错误会自动重试，最多重试2次
错误容忍：单个SQL失败不会中断整个验证流程
批处理隔离：批次间相互独立，一个批次失败不影响其他批次

性能优化建议

并发调优：根据数据库性能调整 max_concurrent_validations
批处理优化：大文件建议增加 batch_size 以减少内存占用
超时设置：复杂SQL可适当增加 validation_timeout
连接池复用：启用 reuse_connection_pool 以减少连接开销

安全说明

SQL验证器运行在只读模式下，不会修改数据库
使用EXPLAIN语句进行验证，不会执行实际的数据操作
敏感信息（如密码）在日志中会被自动隐藏

故障排除

常见问题

连接失败

解决：检查数据库连接字符串和网络连通性

权限不足

解决：确保数据库用户有SELECT权限

表不存在

解决：检查SQL中的表名是否正确，注意schema前缀

语法错误

解决：检查SQL语法，注意PostgreSQL的语法特性

调试技巧

使用 --verbose 获取详细日志
使用 --dry-run 预检查文件格式
减小 --batch-size 以便定位问题SQL
检查验证报告中的错误详情

与其他模块的集成

SQL验证器可以与Schema Tools的其他模块无缝集成：

# 先生成Question-SQL对
from schema_tools import QuestionSQLGenerationAgent

qs_agent = QuestionSQLGenerationAgent(...)
qs_report = await qs_agent.generate()

# 然后验证生成的SQL
from schema_tools import SQLValidationAgent

sql_agent = SQLValidationAgent(
    db_connection="...",
    input_file=qs_report['output_file']
)
validation_report = await sql_agent.validate()

sql_validation_guide.md 13 KB Riwayat Mentahan

SQL验证器使用指南

功能特性

使用方法

1. 命令行使用

基本用法

指定输出目录

调整验证参数

预检查模式

高级选项

2. 编程接口使用

文件自动修改功能

功能说明

默认行为

仅删除无效SQL

启用LLM修复功能

修改日志

输出报告

默认输出

可选JSON报告

报告内容

文本摘要报告包含：

JSON报告包含：

示例输出

验证过程日志

文本摘要报告示例

配置选项

命令行参数说明

错误处理机制

性能优化建议

安全说明

故障排除

常见问题

调试技巧

与其他模块的集成

sql_validation_guide.md 13 KB

Riwayat Mentahan