Denodo Governance Bridge - User Manual

Overview

Denodo Governance Bridge Tool exports Virtual DataPort (VDP) elements, including the lineage, to IBM® InfoSphere® Information Governance Catalog (IGC) to support governance across Denodo databases.

To achieve this Denodo Governance Bridge Tool extends IGC by registering new types of assets from the Denodo catalog:

•  Databases
•  Data Sources  
•  Base Views
•  Derived Views
•  Interface Views
•  Columns
•  Associations
•  Stored Procedures
•  Parameters
•  Folders

These Denodo assets:

• Are available in IGC via the “detailed browse” option and appear in the query tool.

• Can be be governed using Glossary Terms, Governance Rule, Data Stewards, Custom Properties and Collections.

• Support Data Lineage.

Figure 1 Denodo Governance Bridge architecture

The Denodo Governance Bridge bridges the gap between IBM IGC and Denodo VDP metadata repository. The tool loads VDP elements and the flow of data that defines the lineage, into IGC using the IGC REST API. Under the hood, Denodo Governance Bridge Tool generates XMLs from VDP elements metadata accessible through the Denodo JDBC API. This process is hidden to the user, who only interacts with the web interface.

Alternatively, the Denodo Governance Bridge offers a batch execution mode that generates only the XMLs from VDP elements metadata and does not communicate with IBM IGC server. This mode is useful in case you want to post-process the XMLs (e.g. enriching them), before submitting them to IBM IGC server. See the Export VDP elements to XML section for a more detailed explanation.

IGC Requirements

Denodo Governance Bridge requires Open IGC. Open IGC is the technology that allows to define new asset types within the Information Governance Catalog with their own names, icons, custom properties and containment relationships.

Denodo Governance Bridge requires at least:

 

• Information Server 11.3.1.2 release 19, or

• Information Server 11.5 rollup 1.1

 

as these versions are the first that allow the possibility to assign more than one parent type to an asset in Open  IGC.

More information about Open IGC is available at

http://www-01.ibm.com/support/docview.wss?uid=swg21699130

Installation

The Denodo Governance Bridge distribution consists of:

• Command-line executable scripts for Windows and Linux (/bin folder)

• A sample JSON file required for the batch mode (/conf folder), see Export VDP elements to XML section.

• A Java jar binary file (/lib folder)

For installing it just download the .zip file and extract the tool into the desired folder.

In order to run the Denodo Governance Bridge you will need Java 8 and a PATH environment variable correctly configured to run it from the console. It is also required that you define the JAVA_HOME environment variable.

After running the script in the /bin folder point your browser to http://localhost:10099 to access the home page:

         

                         Figure 2 Denodo Governance Bridge Tool home page

Enable SSL Connection

When SSL is enabled in IGC server, Denodo Governance Bridge has to trust the public key of the IGC server.

For this to happen you need to import the certificate into the trust store of the Denodo Governance Bridge.

1. Copy the certificate file from the IGC Server to the Denodo Governance Bridge Tool computer.

2. Locate the Java Runtime Environment (JRE) the Denodo Governance Bridge Tool uses.

3. Open a command line there and execute this:

keytool -importcert -alias some_alias     -file IGC_SERVER_PUBLIC_KEY.cer     -keystore jre\lib\security\cacerts      -storepass "changeit" -noprompt

Usage

Register Denodo Asset Types in IGC

The registration of Denodo asset types in IGC is done transparently, by the Denodo Governance Bridge tool before the first synchronization between a VDP database and IGC.

After this operation, Denodo asset types are displayed in the IGC browser in the Denodo Models group:

Figure 3 Denodo type assets in IGC

Synchronize VDP with IGC  

First, the Denodo Governance Bridge needs the connection details to communicate with the Denodo VDP server:

• Host

• Port

• A database the login user has privileges to connect to.

Once the authentication is complete, the user could choose the database to synchronize with IGC among all the databases the user can connect to.

Take into account that for the elements involved in the synchronization process, the login user should have:

• connect privileges over the databases

• for views and stored procedures

• write privileges in Denodo 7.0

• read privileges in Denodo 6.0

• for the rest of elements

• metadata privileges in Denodo 7.0

• read privileges in Denodo 6.0

Figure 4 VDP server connection

Then, the process to synchronize a Denodo VDP model with IGC is a four-step process.

Step 1. Users select which database in the VDP server they are interested in.

Figure 5 Databases listing

Step 2. The tool inspects the database and let users choose the elements to export to IGC.

Figure 6 Database elements tree

Figure 7 Database elements selection

Step 3. The tool informs users about which are the transitive dependencies for the selected elements. These dependencies will be also exported to IGC.

In the picture below we can see that the derived view web_logs_all_with_blokinfo depends of:

• Derived views: 

• web_logs_all
• linux_web_logs
• nonlinux_web_logs_impala

• Base views:

• web_logs
• web_logs_impala
• hbase_cloudera_blocklistip

• DataSources:

• hive_ds
• impala_ds
• hbase_cloudera_ds

All these VDP elements will be present in IGC at the end of the process.

Figure 8 Transitive dependencies are also synchronized

Step 4. Users give the details to connect to an IGC server.

The Update Denodo bundle version in IGC? checkbox should only be checked, for performance reasons, the first time a synchronization process is executed after installing a Governance Bridge Tool update.

The registration of the Denodo bundle in IGC is done, transparently, by the Governance Bridge tool before the first synchronization between a VDP database and IGC. Later updates of the Governance Bridge Tool could need to modify the Denodo bundle: this checkbox forces the update of the Denodo bundle in IGC.

Figure 9 IGC server connection

In the end, the tool shows all the elements, the selected and the dependent ones, that were synchronized to the IGC.

The flow of data that defines the lineage is not explicitly displayed but it will also be loaded in the IGC server.

Figure 10 Synchronization complete

Let’s show the synchronization process result in the IGC side.

The image below shows the details of the web_logs_all_with_blockinfo derived view:

• the database it belongs to

• the columns assets that it contains

• the VQL expression that defined it

• etc.

Figure 11 Derived view in IGC

And, these two images below display the data lineage for the same view, web_logs_all_with_blockinfo.

Figure 12 Derived view lineage in IGC

Figure 13  After drilling down on the derived view lineage

Data lineage view in IGC is similar to the Data lineage view available in the VDP Admin Tool. The following image shows the VDP data lineage for the web_logs_all_with_blockinfo derived view.

Figure 14  Data lineage view in VDP

Export VDP elements to XML

There is an alternative use case for the Denodo Governance Bridge that does not involve user interaction with its web interface.

This batch execution mode generates only the XMLs from VDP elements metadata; it does not communicate with the IBM IGC server. This mode is useful in case you want to post-process the XMLs (e.g. enriching them with additional data), before submitting them to the IBM IGC server.

For example, in case you want IGC to track the lineage between VDP elements and other IGC elements representing their sources of data (databases, tables, columns), that the Governance Bridge is not aware of, Denodo Governance Bridge offers a web service endpoint that exports the assets and flows to XML.

After that, you can enrich these XMLs, in an ad-hoc process, with data about their source elements (databases, tables, columns). And, finally, submit these XMLs to the IBM IGC server.

For this batch mode you need that the Denodo Governance Bridge Tool is up and running. Then,  execute the script you will find in the bin directory of the distribution:

$ cd denodo-governance-bridge-<VERSION> $ bin/denodo-export-governance-bridge.sh conf/input.json

This script uses curl for invoking the export service endpoint of the Denodo Governance Bridge. You can check if you have curl installed in your system using the command:

$ curl --version

        

If curl is not there you can install it from https://curl.haxx.se/dlwiz/.

The denodo-export-governance-bridge.sh|bat script needs a JSON file with the configuration:

input.json sample file

{  "vdpServerUrl":"//host:port/db",  "login":"userlogin",  "password":"ENC(userpassword)",  "outputFile":"dir/file.xml",  "startDateISO8601":"2019-06-04T14:20:00+02:00",  "endDateISO8601":"date in ISO 8601 format" }

The parameters in the JSON file are:

• vdpServerUrl (required): //host:port/database. All the database elements will be exported, filtered by date if any is supplied.

• login (required): the login user; must have privileges to connect to the database that is being exported.

• password (required): the password user. It could be encrypted or clear. See the How to encrypt passwords section for a more detailed explanation.

• outputFile (required): the file and directory where the XML files will be written.

The export process will generate two files:

• one for the assets, adding the suffix "_assets_timestamp"

• one for the flows, adding the suffix "_flows_timestamp"

E.g.: if the outputFile is C:/export/denodo_sakila.xml. The result files will be:

• C:/export/denodo_sakila_assets_2019-05-23-124942.xml

• C:/export/denodo_sakila_flows_2019-05-23-124942.xml

Note that the user account running the Denodo Governance Bridge application needs write privileges to write the XML files in the output folder.

• startDateISO8601 (optional): the date should be specified in ISO 8601 format, e.g. e.g. 2019-06-04T14:20:00+02:00

• endDateISO8601 (optional): the date should be specified in ISO 8601 format, e.g. 2019-06-05T14:20:00+02:00

When providing a value for startDateISO8601 and endDateISO8601, the VDP elements created or modificated between the two intervals will be exported.

If startDateISO8601 is not provided, all the VDP elements that were created or modificated before endDateISO8601 will be exported.

If endDateISO8601 is not provided, all the VDP elements that were created or modificated after startDateISO8601 will be exported.

The output of the script includes the HTTP response headers. You can check the HTTP status code to see if the export process was OK:  

$ bin/denodo-export-governance-bridge.sh conf/input.json HTTP/1.1 200 Content-Type: text/plain;charset=UTF-8 Content-Length: 21 Date: Tue, 8 June 2019 15:44:24 GMT Successfully exported

Or it failed:

$ bin/denodo-export-governance-bridge.sh conf/input.json HTTP/1.1 500 Content-Type: application/json;charset=UTF-8 Transfer-Encoding: chunked Date: Tue, 8 June 2019 15:58:35 GMT Connection: close {"timestamp":1559059115009,"status":500,"error":"Internal Server Error","exception":"org.springframework.jdbc.CannotGetJdbcConnectionException","message":"Could not get JDBC Connection; nested exception is java.sql.SQLException: authentication error: Database 'test' not found","path":"/export"}

How to encrypt passwords

The Denodo Governance Bridge expects encrypted input passwords to appear surrounded by ENC(...). You can compute these values using the Jasypt CLI tools, and use the DENODO_EXPORT_ENCRYPTION_PASSWORD environment variable or Java system property.

This way, you can use encrypted passwords in the JSON file:

... "password":"ENC(s2FdirMK4QORq1HZ6tcTTQ==)" ...

 

These are the steps for encrypting passwords:

1. Download Jasypt CLI tools.

2. Choose an encryption password, e.g., mypassword.

3. Go to jasypt/bin.

4. Run encrypt.bat with the input parameter and password parameter:

• input parameter - this is the string you want to encrypt.

• password parameter - this is the password that Jasypt is going to use to encrypt and decrypt the input parameter.

Your command should look like this:

        

Take note of the output. Example output: zrass64ls4LIx5hdFoXXyA==.

5. Open your input.json file, replace the password you want to encrypt with  the output from Step 4: ENC(zrass64ls4LIx5hdFoXXyA==).

Before Jasypt: "password":"admin" After Jasypt "password":"ENC(zrass64ls4LIx5hdFoXXyA==)"

6. Add an environment variable, or Java system property to the Denodo Governance Bridge start script, with the name DENODO_EXPORT_ENCRYPTION_PASSWORD, and value of mypassword, but use your real encryption password.

7. Run the denodo-export-governance-bridge.sh|bat script.

Limitations

View deletion

Successive exports of the same database (or folder) accomplished by the Denodo Governance Bridge Tool do not delete views in IGC that no longer exist in Denodo VDP. In these cases the user should delete these views manually in IGC.

Lineage between Denodo elements and other IGC elements

The Denodo Governance Bridge exclusively manages Denodo elements. It is not aware of the presence of metadata elements for the Denodo data sources (such as database tables) or the Denodo client applications, both of which could be present in IGC or not, and managed in ways that might not be recognizable by external tools such as the Governance Bridge.

Associations among metadata elements imported from Denodo and from other data storage/integration systems that might also be present in an IGC installation should be managed using other metadata management tools that may have visibility on the complete set of IGC metadata.

If this limitation affects your scenario, see the Export VDP elements to XML section for an alternative that could fit your needs.