You can translate the document:

Goal

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

Content

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

To start this process, we will create a JDBC data source using the Denodo Virtual DataPort 8.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 info for JDBC, JSON and WS data sources, but the steps are the same for any type of datasource.

Having our datasource 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

DESC DATASOURCE @type @name

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 exact same command, and just changing the values on the “Edit variable values” 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.

In order to do this, 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.

You can also change this property directly with this VQL command:

ALTER WRAPPER JDBC <wrapper_name>

SOURCECONFIGURATION

(DELEGATESQLSENTENCEASSUBQUERY = false)

Note that the name of the wrapper to change is located on the Options 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 right click on the name, 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”.

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:

  • 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, in order to retrieve all the data sources of our database, we can use the GET_ELEMENTS() stored procedure, filtering by “datasource”:

SELECT name, subtype FROM get_elements() WHERE type = '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 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 its type matches with the data source type using a partitioned union and will optimize the executions.

NOTE: When executing this final view, if you have data sources of some type that do not have their respective views added, the execution of the view is going to retrieve information for the incorporated data sources, but it will show an error message. This is because these data sources cannot be found using the types that are configured. For example, if you have configured the views for JDBC, WS and JSO, but you have a XML datasource, the view will try to access this data source also but it will return an error for it.

References

Create Base Views From Query With Parameters

Creating Derived Views — Virtual DataPort Administration Guide

GET_ELEMENTS — VQL Guide

Describing Catalog Elements — VQL Guide

Questions

Ask a question

You must sign in to ask a question. If you do not have an account, you can register here