How to get the connection URIs for all Denodo Virtual DataPort data sources

Goal

This document describes how to create a view in Denodo Virtual DataPort, to get the connection information of all the different data sources, regardless of the type and number of data sources.

Content

We are going to use the Denodo Web Design Studio to create all the elements required for this functionality, the Denodo Virtual DataPort Administrator Tool can be used too.

To start this process, we will create a JDBC data source using the Denodo Virtual DataPort 9.0 adapter. Use the local machine as a host on the URI, and point to the database with the data sources that we are going to access, as shown in this image:

In Denodo, we can list all the data source properties using the DESC DATASOURCE command. This command needs to receive two parameters, the data source type and the name.

For the data source type, there are the allowed values:

CUSTOM | DF | ESSBASE | JDBC | JSON | LDAP | ODBC | OLAP | SALESFORCE | SAPBWBAPI | SAPERP | WS | XML

A JDBC data source and a JSON data source have completely different properties and the DESC command will return different information depending on the data source type. Therefore,  we will need to create a base view for every type of data source that we want to be able to get the connection info.

In this document, we are going to show how to get the connection information for JDBC, JSON, and WS data sources, but the steps are the same for any type of data source.

Having our data source ds_denodo as an origin, we need to create a base view from a SQL Query, using the DESC command, for each type of data source.

For example, for the JDBC data sources, we create the base view with the following command

A pop-up will appear asking for a data source name and type, we need to provide ‘JDBC’ as the type value, and a valid JDBC data source name, to be able to create the Base View

We are going to do the same for the WS and JSON data sources, using the same command, and just changing the values on the “Interpolation variable” pop-up when creating the Base View.

After this, we will have three base views, one for each type of data source:

• bv_getproperties_jdbc
• bv_getproperties_json
• bv_getproperties_ws

But they are not ready to work yet, because the “Delegation as subquery” option needs to be disabled, otherwise, the execution will try to use the DESC command as part of a SELECT statement and it will fail.

To modify the “Delegation as subquery” option in Virtual DataPort Administration Tool, open the base view, go to the Options menu, and select the tab “Search methods”. In this tab click on “(wrapper source configuration)”

A “Source configuration” pop up will appear, where you will see the “Delegate SQL sentence as subquery” property. Select “No” and save the changes. This action needs to be done in every base view we create with the DESC command.

However, in Design Studio, you can change this property only through the VQL command, like in the example mentioned below:

Note that the name of the wrapper to change is located on the Options > Search methods tab of each base view.

After configuring our base views, we need to standardize the information they return, so we can work with them together. For this, we need to create for each one a Selection View on top.

Select the base view and click on the three dots button on the right side of the base view, then select New > Selection.

In this Selection Views, we need to apply a where clause to set the corresponding data source type. Open the “Where Conditions” tab and set the valid value to the field “type”.

NOTE: Make sure to enter the value for the type as lower case.

Also, we are going to edit the Output of the view, to set the same fields for every selection view that we are going to use.

Having all the fields of every selection view with the same name, allows Denodo to detect that they are the same and to create the relationships between them automatically when we create a union view later.

We are going to configure these fields for each base view, setting the corresponding fields of the origin source:

• database_name: the name of the Denodo virtual database where the data source is located.
• name: will have the name of the corresponding datasource
• connection_data: for this field we are going to map the field of each base view that contains the connection information of each type of data source (databaseuri for JDBC, dataroute for JSON, wsdlroute for WS, etc).
• type: the corresponding type of the data source.

Once we make this, we will have this three selection views created for our base views:

• iv_getproperties_jdbc
• iv_getproperties_json
• iv_getproperties_ws

Now we will create a Union View, that is going to get the data from all the different types of data sources in one single view.

Create a new Union (extended) view on the Virtual DataPort Admin Tool and drag the selection views that we have just created.

Because they all have the same field names and types, the field associations will be automatically done.

Next, to retrieve all the data sources of our database, we can use the GET_ELEMENTS() stored procedure, filtering by “datasource”:

This execution will return the name and type of every data source created in our database.

First of all, we create a new base view from query in our original Denodo data source, using the GET_ELEMENTS() procedure:

Now we create a Join View, and drag this new view and our Union view and link the database_name,  name and type fields of both, as shown in this image:

If we execute our new join view we will see the connection URIs for all our data sources:

Because of how we have created these views, the Denodo execution engine will execute the base views only when their type matches with the data source type using a partitioned union and will optimize the executions.

References

Create Base Views From Query With Parameters

Creating Derived Views

GET_ELEMENTS

Describing Catalog Elements