Postgres物化视图依赖关系管理器
pg-materialize的Python项目详细描述
pg_materialize是一个实用程序，用于从包含Materialized Viewsql定义的目录中生成postgresql创建和刷新脚本。它使用psqlparse将sql转换为解析树，通过生成dag来标识哪些物化视图依赖于其他视图，并生成构造和刷新这些视图的正确顺序。递归地遍历源目录，正确地处理跨模式视图，并从最终输出中筛选出无关的事务语法块。每次后续运行的文件都用后缀YYYYMMDD-HHMMSS.sql标记时间戳。
支持的Python版本
- python 2.7版
- Python3.3+
安装
如果您的系统上有pip，您可以简单地安装或升级python库：
pip install -U pg_materialize
或者，您可以从PyPI下载源发行版，将其取消存档，然后运行：
python setup.py install
注意：上述两种方法都将pg_materialize安装为系统范围的包。您可以考虑使用virtualenv来创建独立的python环境。
用法
示例命令：
pg_materialize -v \ -i ~/Projects/my_project/src \ -o ~/Projects/my_project/output \ -p _mv \ -I invites
示例输出：
Found 97 Scripts in /Users/ali/Projects/my_project/src Identified 169 Materialized Views, Containing 90 View Dependencies Materialized View Dependencies: 'public.users_mv': ['public.user_addresses_mv', 'public.user_invites_mv'], 'public.orders_mv': ['public.payment_methods_mv'] Selecting 97 Materialized Views for Refresh Successfully Saved Creation Script to ~/Projects/my_project/output/create-20170824-120626.sql Successfully Saved Refresh Script to ~/Projects/my_project/output/refresh-20170824-120626.sql Process Complete!
示例创建脚本：
BEGIN;-- ~/Projects/my_project/src/public/user_addresses.sql CREATEMATERIALIZEDVIEWIFNOTEXISTSpublic.user_addresses_mvAS(SELECT*FROMpublic.user_addressesWHEREcreated_at>=CURRENT_DATE-INTERVAL'6 MONTHS')WITHDATA;-- ~/Projects/my_project/src/public/user_invites.sql CREATEMATERIALIZEDVIEWIFNOTEXISTSpublic.user_invites_mvAS(SELECT*FROMpublic.user_invitesWHEREcreated_at>=CURRENT_DATE-INTERVAL'6 MONTHS')WITHDATA;-- ~/Projects/my_project/src/public/payment_methods.sql CREATEMATERIALIZEDVIEWIFNOTEXISTSpublic.payment_methods_mvAS(SELECT*FROMpublic.payment_methodsWHEREcreated_at>=CURRENT_DATE-INTERVAL'6 MONTHS')WITHDATA;-- ~/Projects/my_project/src/public/users.sql CREATEMATERIALIZEDVIEWIFNOTEXISTSpublic.users_mvAS(SELECT*FROMpublic.usersWHEREcreated_at>=CURRENT_DATE-INTERVAL'6 MONTHS')WITHDATA;-- ~/Projects/my_project/src/public/orders.sql CREATEMATERIALIZEDVIEWIFNOTEXISTSpublic.orders_mvAS(SELECT*FROMpublic.ordersWHEREcreated_at>=CURRENT_DATE-INTERVAL'6 MONTHS')WITHDATA;COMMIT;
刷新脚本示例：
BEGIN;REFRESHMATERIALIZEDVIEWCONCURRENTLYpublic.user_addresses_mv;REFRESHMATERIALIZEDVIEWCONCURRENTLYpublic.payment_methods_mv;REFRESHMATERIALIZEDVIEWCONCURRENTLYpublic.users_mv;REFRESHMATERIALIZEDVIEWCONCURRENTLYpublic.orders_mv;COMMIT;
选项
pg_materialize接受以下命令行参数。
-d
--dry-run
Analyzes dependencies without actually generating the output files.
-i
--input-dir
The directory for the PostgreSQL scripts to analyze.
-I
--ignore-refresh
Regex pattern to match when ignoring refresh on Materialized Views (i.e. ^{tt11}$ for ^{tt12}$).
-o
--output_dir
The directory for the output creation and refresh scripts.
-p
--pattern
Materialized View regex pattern to match (i.e. ^{tt17}$ for ^{tt18}$).
-v
--verbose
Enables verbose logging.
欢迎加入QQ群-->： 979659372
