数仓如何递归查询视图依赖?

💡 原文中文,约3700字,阅读约需9分钟。
📝

内容提要

本文介绍了使用with recursive递归查询的方法来实现查询视图的层级依赖关系。通过建立基表和视图,并使用复杂的组合查询,可以得到视图的依赖关系。然而,这种查询方法不直观且不友好。因此,可以使用with recursive语法来实现递归查询,将查询结果保存为视图,从而实现直观的依赖关系表示。最后,总结了查询视图的重要性和希望本文对读者有所帮助。

🔎

延伸解读

Postgres生态中视图依赖查询的痛点

在Postgres生态中,视图依赖关系没有现成的系统视图或函数可直接查询,必须组合pg_depend和pg_rewrite等系统表编写复杂SQL。相比之下,Oracle和MySQL分别通过USER_DEPENDENCIES和INFORMATION_SCHEMA.VIEWS即可轻松获取。这种差异导致在GaussDB(DWS)等基于Postgres的数据库中,DBA需要自行构建查询方案,增加了日常维护和影响分析的难度。

WITH RECURSIVE如何简化层级依赖展示

文章利用WITH RECURSIVE将非递归部分(基表)与递归部分(视图间关联)结合,通过拼接path字段直观展示依赖路径。递归终止条件为relkind='r'(基表),每次递归用refobjname与objname内关联,逐步向上追溯。最终将查询保存为视图,避免每次编写复杂组合查询,提升了可读性和复用性。

递归查询的适用性与注意事项

该方法适用于GaussDB(DWS) 8.1.0及以上版本,且需注意过滤系统对象(如objid>=16384)。递归查询依赖系统表结构,若版本升级导致pg_depend或pg_rewrite变化,可能需调整SQL。此外,递归深度受限于视图层级,若存在循环依赖(理论上视图不应有),可能导致无限递归,需确保依赖关系为有向无环图。

❓

Q&A

在Postgres生态中,为什么查询视图的依赖关系比较麻烦?

因为Postgres没有现成的查询方法,需要针对系统表pg_depend和pg_rewrite编写复杂的组合查询才能得知视图的依赖关系,而Oracle和MySQL分别通过查询USER_DEPENDENCIES和INFORMATION_SCHEMA.VIEWS即可轻易查出。

如何用with recursive递归查询视图的层级依赖关系?

先通过组合查询pg_depend等系统表得到视图依赖关系并保存为视图(如PUBLIC.gs_view_dependency),然后使用WITH RECURSIVE定义CTE,非递归部分查询relkind='r'的基表作为起点,递归部分用refobjname与objname内关联并拼接path,最终将CTE保存为视图即可。

WITH RECURSIVE语句的执行步骤是怎样的?

执行non-recursive term,结果作为recursive term中对result的引用并放入临时working table;然后重复执行:用working table的内容替换递归的自引用,执行recursive term,并用该结果替换working table,直到working table为空。

在递归查询视图依赖时,如何直观地表示依赖路径?

在递归部分通过拼接path字段,用'->'连接每个节点,例如cte.path ||' -> '|| b.refobjname,从而形成类似a->b->c的依赖路径,直观展示视图与基表或其他视图的层级关系。

查询视图的层级依赖关系有什么实际用处?

查询视图的层级依赖关系对数据管理非常重要,可以帮助更好地组织和管理数据,避免因视图依赖关系复杂而导致的困惑。

如何将递归查询视图依赖的结果保存为视图以便后续使用?

可以将整个WITH RECURSIVE查询语句通过CREATE VIEW语句保存为视图,例如CREATE VIEW PUBLIC.gs_view_table_dependency AS ...,这样以后就可以直接查询该视图来获取依赖关系。

🏷️

标签

➡️

继续阅读