/postgresql2sp

PostgreSQL转存储过程

Primary LanguagePython

Postgresql与GreenPlum：自动将SQL语句封装为存储过程

背景

在大型数据清洗和复杂业务流程中，SQL代码经常需要分为多个小步骤来执行。当这些代码很长，达到数百甚至数千行时，复用它们需要修改许多参数，复用脚本不方便。为解决此问题，可以将这些代码改写成存储过程，从而将参数控制在可管理的范围内。但手动改写和调试这些长SQL为存储过程是一项极其繁琐和耗时的任务。因此，我希望通过Python自动化这个过程，希望提高10倍以上效率，可以节约时间，减少错误，还有非常重要的一点是节约我们的宝贵的注意力。

可行性研究

可以将存储过程分解为以下三个部分：

  1. 开始：这包括定义参数和初始化过程，例如：

    • 创建存储过程
    • 添加功能名称、参数等注释
    • 使用DECLARE声明变量
    • 使用BEGIN开始存储过程

  2. 中间：这是存储过程的主体，包括：

    • 将SQL语句封装为变量
    • 使用RAISE通知执行状态
    • 执行封装后的SQL

  3. 结尾：这包括选择语言、添加存储过程注释等，例如：

    • 发送完成的通知
    • 添加return、END标签
    • 选择存储过程语言（例如plpgsql、python）
    • 修改存储过程所有者
    • 添加存储过程注释

考虑到开始和结尾部分的内容基本固定，可以创建一个模板，只需要替换中间部分即可。因此具备可行性。

执行步骤

  1. 创建固定的模板，用于存储过程的开头和结尾。
  2. 替换中间的SQL脚本，需要注意：
    • 单引号转义
    • 时间、日期和表名的替换
    • 将SQL封装为变量
  3. 合并所有部分。

SQL格式要求建议

为确保转换过程的准确性，SQL使用需要满足以下格式要求：

  • 每段SQL语句都应以分号结尾。
  • 在“create table as”语句前，应有“drop if exists”语句。
  • 在“insert into”语句前，应有“delete from”语句。
  • 时间查询条件推荐使用“between and”格式，以方便程序识别。
  • 有时间、日期偏移相关的脚本，用注释打上偏移或offset字样，程序会选择偏移相关的参数。

程序架构设计

文件结构：

  • main.py: 主执行脚本。
  • config_files/: 存放其他必要文件
    • 正则替换参数.csv: 正则替换参数
    • SP_Snippet_Begin.sql: SQL模板
  • file_utils/: 文件操作相关的文件夹。
    • reader.py: 读取文件相关的函数。
    • writer.py: 写入文件相关的函数。
  • sql_processors/: SQL处理文件夹，包含与SQL处理相关的模块。
    • replacer.py: 与SQL内容替换相关的函数。
    • encapsulator.py: 封装SQL的函数。
    • generator.py: 与生成存储过程相关的函数。
  • results/:默认结果路径，可通过界面选择其他文件夹路径，结果文件格式为SP_sp的名称。
  • original_files/:存放sql或txt原始脚本文件，如SQL_text.sql。可通过按钮自定义选择

用法教程

  1. 运行main.py，弹出GUI界面
python main.py
  1. 图形界面填写相关信息
  2. 点击运行
  3. 在结果文件夹找到转为存储过程（SP）的脚本

总结

将SQL语句转换为存储过程具有一定挑战性，目前大部分常见SQL转SP都可以自动处理。这个项目是一步一步做起来的，我自己有相关需求场景，碰到问题一步步优化。期间，碰到的主要难点有以下3个：

  1. 难点一在于抽象总结存储过程步骤。
  2. 难点二在于正则表达式编写，SQL语句写法样式很多，非常考验文本抽取识别信息的能力，对正则表达式要求很高。
  3. 难点三在于很多细节调优，完成一版自己可用的程序相对容易，要对外发布使用就需要解决那行不太重要但也常见的问题。

目前语句可完成90%的SQL转存储过程操作，对于那些不能自动处理的特殊情况，转换后可以进行手动调整即可。

Changelog

2024-04-07 改为GUI图形交互界面，方便更多人使用

2023-08-22 修改 encapsulator.py，解决不封闭语句块(如有create无drop语句)无法正确识别或识别后输出顺序不对情况

2023-08-22 大福优化，修改 encapsulator.py，解决不能识别连续多个drop语句、多个analyze语句

2023-08-22 重构为模块化代码易于维护

2023-08-14 脚本大部分是线性的和过程性的，重构函数，易于维护和扩展。

2023-02-08 优化，新增逐行替换的复杂版本，打上偏移标识别，可识别是否需要时间日期偏移。路径使用pathlib库把Windows路径转为Unix样式通用路径

2023-02-06 优化，新增全部文本替换的简单版本，正则替换整理成csv文件，读取循环匹配。时间日期匹配可采用>=、>、<、<=样式，可满足一部分人采用此种样式的替换

2022-06-20 创建，只能匹配between and 样式时间，可完成70%常规语句转化，可用但不完善，如正则写死不太灵活，识别日期、时间的样式较少