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

本文分享自华为云社区《GaussDB(DWS)如何递归查询视图依赖》，作者：半岛里有个小铁盒。

1. 前言

适用版本：【8.1.0（及以上）】

本文通过介绍 with recursive 递归查询的办法来实现查询视图的层级依赖关系

2. 实现简介

对于 postgres 生态来说，视图的依赖关系没有现成的查询方法，需要对系统表 pg_depend 及 pg_rewrite 编写复杂的组合查询才能得知，而对于 Oracle 和 MySql，该需求都较易实现，分别查询 USER_DEPENDENCIES 和 INFORMATION_SCHEMA.VIEWS 即可轻易查出，因此在 pg 生态来说有必要编写一个直观的视图来查看各个视图与基表或与其他视图的层级依赖关系。本文通过 with recursive 递归查询的办法来实现视图的层级依赖关系查询。效果如下：

首先建立两个基表 t1、t2，表结构随意，再建立一系列的视图进行层级关联，关联关系也随意。本文的建表及视图语句如下：

create table t1 (a int, b int) distribute by hash(a);create table t2 (a int, b int) distribute by hash(a);create view v1 as select * from t1;create view v2 as select * from v1;create view v3 as select * from v2;create view v4 as select * from v3;create view v5 as select * from t2;create view v6 as select * from v5;create view v7 as select * from v6;create view v8 as select * from v2;

实际的局点中，用户的视图依赖关系肯定要比这复杂得多，本文仅讲解原理。对于上述的视图依赖关系，可以通过如下查询得出：

SELECT c.ev_class::regclass::varchar AS objname, pc.oid::regclass::varchar AS refobjname, pc.relkind AS relkind     FROM pg_depend a,pg_depend b,pg_class pc,pg_rewrite c    WHERE a.refclassid=1259      AND a.classid=2618      AND b.deptype='i'      AND a.objid=b.objid      AND a.classid=b.classid      AND a.refclassid=b.refclassid      AND a.refobjid<>b.refobjid      AND pc.oid=a.refobjid      AND c.oid=b.objid      AND (a.objid>=16384 or a.refobjid>=16384)      GROUP BY c.ev_class,pc.oid,pc.relkind  ORDER BY relkind;

其中倒数第二行的 16384 表示过滤掉系统对象，relkind 表示上级依赖对象的类型，r 说明依赖于基表，v 表示依赖另一个视图。其查询结果如下：

可见这种查询并不直观，只能通过肉眼分析得出递归的依赖关系，对用户并不友好。莫急，先将上述查询保存为视图，例如起名为 PUBLIC.gs_view_dependency。接下来我们来学习一下 with recursive 语法的使用方法，从 pg 官网可以 get 到的知识是，WITH 语句通常被称为通用表表达式（Common Table Expressions）或者 CTEs。WITH 语句作为一个辅助语句依附于主语句，WITH 语句和主语句都可以是 SELECT，INSERT，UPDATE，DELETE 中的任何一种语句。WITH 语句还可以通过增加 RECURSIVE 修饰符来引入它自己，从而实现递归：

WITH RECURSIVE 语句包含了两个部分：

• （非递归部分）non-recursive term，即上图中的 union all 前面的部分

• （递归部分）recursive term，即上图中 union all 后面的部分

执行步骤如下：

执行 non-recursive term。（如果使用的是 union 而非 union all，则需对结果去重）其结果作为 recursive term 中对 result 的引用，同时将这部分结果放入临时的 working table 中

重复执行如下步骤，直到 working table 为空：用 working table 的内容替换递归的自引用，执行 recursive term，（如果使用 union 而非 union all，去除重复数据），并用该结果（如果使用 union 而非 union all，则是去重后的结果）替换 working table

因此，照葫芦画瓢，我们可以先给这个 CTE 查询起个名字，例如：

WITH RECURSIVE get_view_dependencyAS( ... )

然后，我们可以把非递归部分写成：

SELECT a.objname, a.refobjname, a.refobjname path FROM PUBLIC.gs_view_dependency a where a.relkind = 'r'

因为查找到类型为’r’的基表的时候，递归就应该结束了。并且我们需要将 refobjname 组装成一条 path 来实现依赖路径的直观表示。因为是递归，所以我们需要不止一次的调用 PUBLIC.gs_view_dependency，因此我们给它起个别名 a。而递归的条件显而易见，应该是用 refobjname 来与 objname 做内关联，因此我们可以写作：

INNER JOIN get_view_dependency cte ON b.refobjname=cte.objname

其中 cte 就用来指代上一次的递归查询结果，而递归的退出条件就应该是最开始写的类型为’r’的情况。其中 b 就应该是递归部分每次调用的 PUBLIC.gs_view_dependency。而我们除了要查询 objname 和 refobjname 外，更重要的是要组成一个 path，而 path 每个节点最好通过一个’->'来直观表示，因此递归部分我们就可以写成：

SELECT b.objname, b.refobjname, cte.path ||' -> '|| b.refobjname path FROM PUBLIC.gs_view_dependency bINNER JOIN get_view_dependency cte ON b.refobjname=cte.objname

因此，整个 CTE 递归查询便可以写成：

WITH RECURSIVE get_view_dependencyAS(SELECT a.objname, a.refobjname, a.refobjname path FROM PUBLIC.gs_view_dependency a where a.relkind = 'r'UNION ALLSELECT b.objname, b.refobjname, cte.path ||' -> '|| b.refobjname path FROM PUBLIC.gs_view_dependency bINNER JOIN get_view_dependency cte ON b.refobjname=cte.objname)SELECT * FROM get_view_dependency;

为了方便以后的使用，我们可以给它起个名字保存起来，例如 CREATE VIEW PUBLIC.gs_view_table_dependency AS …。效果就是文章开头所示那样，便大功告成。

3. 总结

在数据库中，查询视图是一种非常有用的工具，它可以帮助我们更好地组织和管理数据。然而，查询视图的层级依赖关系可能会让人感到困惑。本文将介绍一种通过 with recursive 递归查询的办法来实现查询视图的层级依赖关系的方法，希望对你有所帮助。

点击关注，第一时间了解华为云新鲜技术~