USER MANUALS

VIEW_DEPENDENCIES

Description

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

This procedure allows you to obtain programmatically the same information that the Administration Tool displays in the Tree View dialog of the derived views.

Syntax

VIEW_DEPENDENCIES (
      input_view_database_name : text
    , input_view_name : text
    , include_tags : boolean
)

  • 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.

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).

  • include_tags: specifies if results must contain the tag dependencies with the views and their columns (false by default).

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. I.e. Base view, Select, Join, Interface, etc. When the type of the view is join, the value of this field includes the following information, in this order:

    • Type of join: it can be “Inner”, “Left outer”, “Right outer” or “Full outer”.

    • Join method: it can be “Any”, “Hash”, “Nested”, “Nested parallel” or “Merge”. “Any” means that the user did not select a method to execute the join when creating the view so the Execution Engine will automatically select the method at runtime.

    • Order of the join: it can be “Any”, “Ordered” or “Reverseorder”. “Any” means that the user did not select an order to execute the join when creating the view so the Execution Engine will select the order at runtime.

  • private_view: true if the view on the field “view_name” is private; false otherwise.

    A view is private when “view_name” represents a join, minus, intersect view of another join/minus/intersect view and the view was internally by Virtual DataPort and not by the user.

    For example, if you create a derived view called J_V that joins three views: (V1 JOIN V2) JOIN V3, internally Virtual DataPort creates a join view of V1 and V2. J_V will be the result of joining this internal view with V3. In this example, the join of V1 and V2 is a private view and J_V is public.

    The information about private views allows you to know in which order a join/minus/intersect view of more than two views is performed.

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

    The value of this field will always be the same as “view_database_name”.

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

  • dependency_identifier: internal identifier of the element (view or data source) on which the view on the “view_name” field depends.

    It is autogenerated by the stored procedure and may change between executions of the procedure.

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

  • depth: distance between the view of the field “view_name” and the dependency.

    When a row represents a direct dependency, the value of this field is 1. Direct dependencies are dependencies between views that directly depend on each other. E.g. a base view and a projection view created over this base view have a direct dependency.

    When a row represents a transitive dependency, the value of this field is greater than 1. Transitive dependencies are dependencies between views that depend on each other, but there are other views in between. Let us say that we have created a selection view called P_V created over a base view BV1. In this example, there is a transitive dependency between P_V and the data source of the view BV1.


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:

Correct way of invoking the procedure “VIEW_DEPENDENCIES”
SELECT *
FROM VIEW_DEPENDENCIES()
WHERE input_view_database_name = 'tpc_h'
    AND input_view_name = 'orders_of_european_customers'
    AND position('Datasource' IN dependency_type) > 0

The execution engine will pass the values of “input_view_database_name” and “input_view_name” to the stored procedure and the stored procedure will only calculate the dependencies of the view “orders_of_european_customers”. Then, the execution engine will evaluate the condition “position(‘Datasource’ IN dependency_type) > 0” over the rows that represent the dependencies of this view.

Wrong way of invoking the procedure “VIEW_DEPENDENCIES”
SELECT *
FROM VIEW_DEPENDENCIES()
WHERE view_database_name = 'tpc_h'
    AND view_name = 'orders_of_european_customers'
    AND position('Datasource' IN dependency_type) > 0

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. 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.

Privileges Required

The information returned by the procedure changes depending on the type of user that executes the procedure:

  • Administrators: the procedure returns information about all the dependencies.

  • Administrators of a database: the procedure returns information about all the views of that database. However, it does not return any information about the dependencies of views of other databases.

  • Users that have METADATA privileges over a database: the procedure returns information about the dependencies of all views of that database. However, it does not return dependencies between views and data sources.

  • Users that have METADATA privileges over some views of a database: the procedure returns information about the dependencies of the fields of the views on which the user has the METADATA privilege granted. If the user does not have METADATA privilege granted over the dependency, the value of the field “dependency_type” will be “No Privileges”. The procedure does not return the dependencies between views and data sources.

Examples

The following examples assume that the user executing these queries is an administrator or an administrator of the database of the views mentioned in the example. Otherwise, this procedure does not return information about the data sources.

Example 1

Let us say you have a view “orders_of_european_customers” (database “tpc_h”), execute this to obtain the dependencies of this view:

SELECT DISTINCT dependency_type
    , dependency_database_name
    , dependency_name
FROM view_dependencies()
WHERE input_view_database_name = 'tpc_h'
    AND input_view_name = 'orders_of_european_customers'
    AND private_view = false

    -- The views whose name start with "_" are private views.
    AND substr(dependency_name FROM 1 FOR 1) <> '_';

Example 2

Let us say you have a view “orders_of_european_customers” (database “tpc_h”), execute this to obtain the data sources from which the data of this view is obtained:

SELECT DISTINCT dependency_database_name AS data_source_database
    , dependency_name AS data_source
FROM VIEW_DEPENDENCIES()
WHERE input_view_database_name = 'tpc_h'
    AND input_view_name = 'orders_of_european_customers'
    AND position('Datasource' IN dependency_type) > 0
    AND depth = 1;

Example 3

Let us say that we have created a view called customer_invoicing like this:

(client_df LEFT OUTER JOIN invoice) INNER JOIN product

The following image is a screenshot of the result of executing

SELECT view_name, view_identifier, view_type, private_view, dependency_name,
dependency_identifier, dependency_type, depth
FROM VIEW_DEPENDENCIES()
WHERE input_view_database_name = 'customer360'
    AND input_view_name = 'customer_invoicing';

For formatting purposes, some columns are not displayed.

Sample result of executing the procedure VIEW_DEPENDENCIES

Sample result of executing the procedure VIEW_DEPENDENCIES

To understand the result, take into account the following:

  • The rows #5 to #9 represent the dependencies of a private view (the field “private” is true in these rows). This private view is the left outer join between client and invoice.

  • The rows #11 and #12 till the last one describe the dependencies of the view product. Note that all of them have the same “view_identifier”. All the dependencies of a view have the same “view_identifier”.

  • The rows whose value of “dependency_identifier” is 9 represent the direct or transitive dependencies with the XML data source “invoicing”:

    • Row #2 represents the direct dependency between the base view “invoicing” and the XML data source. The dependency is direct because the value of the “depth” field is 1.

    • Row #4, the transitive dependency between the view “invoice” and the XML data source. This dependency is also transitive because “depth” is 2.

    • Row #9, the transitive dependency between the private view and the XML data source. The dependency is transitive because “depth” is greater than 1.

Add feedback