GET_PUBLIC_VIEW_DEPENDENCIES¶

Description

The stored procedure GET_PUBLIC_VIEW_DEPENDENCIES returns the nearest lineage of a view. That is, the list of closest public views and data sources from which a view was built on.

Syntax

GET_PUBLIC_VIEW_DEPENDENCIES (
      input_view_database_name : text
    , input_view_name : text
    , input_max_depth : number
)

• input_view_database_name: name of the database whose views you want to obtain the dependencies of.

• input_view_name: name of the view you want to obtain its dependencies from.

• input_max_depth: maximum depth from which dependencies are to be obtained.

Parameters are optional:

• If input_view_database_name and input_view_name are null, the procedure returns the dependencies of all views in all databases.

• If input_view_database_name is null and input_view_name is not, the procedure returns the dependencies of all views having this name, in all databases.

• If input_view_name is null and input_view_database_name is not, the procedure returns the dependencies of all views in that database (note that if a view has a dependency with a view of another database, it is also shown).

• If input_max_depth is null then it will be set as default value 1. If input_max_depth is 0 then all dependencies will be obtained. In any other case the dependencies up to the indicated depth level will be obtained.

Each row of the result of this procedure represents a dependency between a derived view (the field “view_name”) and another element (“dependency name”), which can be a derived view, base view or a data source.

The output schema has the following fields:

• view_database_name: name of the database that the view on the field “view_name” belongs to.

• view_name: name of the derived view.

• view_identifier: identifier of the view. It is autogenerated by the stored procedure and may change between executions of the procedure. See the example below to understand how to use this value.

• view_type: type of view: Base view, Select, DataSource, etc.

• dependency_database: name of the database that the dependency belongs to.

• dependency_name: name of the element (view or data source) on which the view on the “view_name” field depends.

• dependency_type: type of element on which the view on the “view_name” field depends.

When invoking this procedure - or any other Denodo stored procedure - always filter by their input parameters if possible. The query will finish much faster and will put much less strain on the Denodo server.

For example, execute this to obtain the dependencies of a view:

SELECT *
FROM GET_PUBLIC_VIEW_DEPENDENCIES()
WHERE input_database_name = 'tpc_h'
    AND input_view_name = 'orders_of_european_customers'
         AND input_max_depth = 2

The execution engine will pass the values of “input_database_name”, “input_view_name” and “input_max_depth” to the stored procedure and the stored procedure will only calculate the dependencies of the view “orders_of_european_customers” with the maximum depth of 2.

SELECT *
FROM GET_PUBLIC_VIEW_DEPENDENCIES()
WHERE view_database_name = 'tpc_h'
    AND view_name = 'orders_of_european_customers'

This query will return the same result as the first one. However, the stored procedure will calculate the dependencies of all the views of all databases because it did not receive any value for its input parameters. In addition, it will only return dependencies with depth 1 by not specifying a value for “input_max_depth”. After the stored procedure returns all the dependencies of all the views, the execution engine will select the ones that meet the WHERE conditions.

On a “real-life” deployment of Denodo, the first query will take a few hundred milliseconds and the second one may take up several minutes.

Examples

This store procedure is very similar to VIEW_DEPENDENCIES but much more efficient in case we are interested in retrieving only the public views on which the indicated view directly depends. Therefore, we will show some examples explaining the operation of GET_PUBLIC_VIEWS_DEPENDENCIES and its differences with VIEW_DEPENDENCIES. Suppose we have the following scenario:

Sample scenario for GET_PUBLIC_VIEW_DEPENDENCIES¶

Example 1:

We want to recover public closest views on which all views depend. For this purpose, neither input_database_name, input_view_name nor input_max_depth is specified.

GET_PUBLIC_VIEW_DEPENDENCIES(null, null, null)

Important difference with VIEW_DEPENDENCIES

• Note that db2.view_d appears twice in the view tree, as a child of db2.view_f and as child of view_i. In this procedure, the dependencies of db2.view_d will appear only once in the result set, while in VIEW_DEPENDENCIES, would appear repeated as many times of occurrences of the view in the view tree or multiple view trees if processing the whole server.

• This procedure would generate 14 rows while procedure VIEW_DEPENDENCIES would output 277 rows for this small amount of metadata.

Example 2:

We want to retrieve the closest public views on which all the views of the database ‘db_1’ depend. For this we do not specify input_view_name and input_max_depth but input_database_name.

GET_PUBLIC_VIEW_DEPENDENCIES('db_1', null, null)

Note that dependencies of db_3.view_j does not appear on the output. Views on db2 appear because they are dependencies of some view on db1.

Example 3:

We want to retrieve all public views on which all the views of the database ‘db_2’ depend. For this we do not specify input_view_name but input_database_name and input_max_depth.

GET_PUBLIC_VIEW_DEPENDENCIES('db_2', null, 0)

Example 4:

In this last case we want to retrieve the public views with maximum depth of 2 on which the view ‘view_g’ of the database ‘db_1’ depends. To do this we specify input_view_name, input_database_name and input_max_depth.

GET_PUBLIC_VIEW_DEPENDENCIES('db_1', 'view_g', 2)