Denodo Governance Bridge - User Manual

Download original document


Overview

Denodo Governance Bridge exports Denodo Platform elements to IBM® InfoSphere® Information Governance Catalog (IGC) or Collibra, to support governance across Denodo databases.

To accomplish this aim, the Denodo Governance Bridge has two modules: Denodo Governance Bridge for IGC and Denodo Governance Bridge for Collibra

Denodo Governance Bridge for IGC

The Denodo Governance Bridge for IGC exports Virtual DataPort (VDP) elements, including the lineage, to IBM® InfoSphere® Information Governance Catalog (IGC).

To achieve this, the Denodo Governance Bridge for IGC, using the Open IGC API, extends IGC by registering new types of assets from the Denodo catalog:

  • Database-icon.gif Databases
  • DataSource-icon.gif Data Sources  
  • BaseView-icon.gif Base Views
  • DerivedView-icon.gif Derived Views
  • InterfaceView-icon.gif Interface Views
  • Column-icon.gif Columns
  • Association-icon.gif Associations
  • StoredProcedure-bigIcon.gif Stored Procedures
  • Parameter-bigIcon.gif Parameters
  • Folder-icon.gif Folders

These Denodo assets:

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

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

  • Support Data Lineage.

Governance Tool.png

Figure 1 Architecture of the Denodo Governance Bridge for IGC

The Denodo Governance Tool bridges the gap between IBM IGC and Denodo VDP metadata repository.

It offers two execution modes:

  1. Interactive mode. The tool loads VDP elements and the flow of data that defines the lineage, into IGC using the IGC REST API with Basic Authentication. Under the hood, the Denodo Governance Bridge for IGC generates XMLs from the VDP elements metadata accessible through the Denodo JDBC API. This process is hidden to the user, who only interacts with the web interface.

See the Synchronize Denodo VDP with IGC: interactive mode section for a more detailed explanation.

  1. Batch mode. The tool generates the XMLs from the VDP elements and their lineage, but it 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, manually, to IBM IGC server.

See the Export VDP elements to XML: batch  mode section for a more detailed explanation.

Denodo Governance Bridge for Collibra

Denodo Governance Bridge for Collibra retrieves metadata from Denodo Platform, transforms and upserts it to a Collibra Platform instance as assets and complex relations.

The following metadata is fetched from Denodo Platform:

  • Database-icon.gif    Databases
  • Folder-icon.gif    Folders
  • DataSource-icon.gif    Data Sources
  • BaseView-icon.gif    Base Views
  • DerivedView-icon.gif    Derived Views
  • InterfaceView-icon.gif    Interface Views
  • Column-icon.gif    Columns

To carry out this integration, Denodo Governance Bridge for Collibra needs the creation of a  metamodel that is necessary to provide a structure for representing Denodo elements as Collibra assets. The table below shows how the data is mapped:

Denodo

Collibra Platform Asset Type

Database

Schema

Folder

Denodo Folder

Data Source

Denodo Data Source

Base View

Table

Derived View

Database View (Table type attribute: Derived View)

Interface View

Database View (Table type attribute: Interface View)

Materialized View

Database View (Table type attribute: Materialized View)

Private View

Database View (Table type attribute: Private View)

Column

Column

The following images show a Denodo database sample and its representation in Collibra Platform:

                      Figure 2 Denodo Platform database sample

 

Figure 3 Diagram example in Collibra Platform result of a Denodo Governance Bridge synchronization

It is important to highlight that Denodo Governance Bridge for Collibra is compatible with Collibra Edge since the 20240123 version. This means that a synchronization performed via Edge can be completed with the metadata imported using the Denodo Governance Bridge for Collibra and vice versa. Nevertheless, the changes required to achieve compatibility, aimed at matching structures and naming conventions in Edge, break the backward compatibility of the Denodo Governance Bridge for Collibra.

Requirements

The Denodo Governance Bridge for IGC and the Denodo Governance Bridge for Collibra require Java 8 or later and the environment variables JAVA_HOME and PATH correctly configured.

IGC Requirements

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

The Denodo Governance Bridge for IGC 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 two folders that contains the distribution for each module:

  • denodo-igc-governance-bridge-<version> for the Denodo Governance Bridge for IGC.
  • denodo-collibra-governance-bridge-<version> for the Denodo Governance Bridge for Collibra.

Installation of the Denodo Governance Bridge for IGC

The distribution of the Denodo Governance Bridge for IGC consists of:

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

  • The IGC bundle file that defines the Denodo asset types (/bundle-igc folder)

  • Configuration files: application.properties and log4j2.xml (/conf folder)

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

  • Java libraries (/lib folder)
  • Governance Bridge application jar: denodo-igc-governance-bridge-<version>-jar

  • Denodo driver jar: denodo-vdp-jdbcdriver-dist-<version>-full.jar 

If you need to use a different Denodo driver version than the one that is distributed, you have to replace this jar with the Denodo driver of the proper version.

In order to install the Denodo Governance Bridge for IGC, just download the .zip file and extract the tool into the desired folder.

After running the script denodo-igc-governance-bridge.sh|bat in the /bin folder, point your browser to http://localhost:10099 to access the home page:

       

 Figure 4 Home page of the Denodo Governance Bridge for IGC

Note that there are, in the /bin folder, some deprecated scripts ( denodo-governance-bridge.sh|bat, denodo-export-governance-bridge.sh|bat) in order to avoid compatibility issues. They will be removed in future versions.

Enable SSL Connection

SSL in IGC server

When SSL is enabled in the IGC server, the Denodo Governance Bridge for IGC 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 for IGC.

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

  1. Locate the Java Runtime Environment (JRE) the Denodo Governance Bridge for IGC uses.

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

SSL in the Denodo Governance Bridge for IGC

Configuring the Denodo Governance Bridge for IGC with HTTPs requires two steps:

  1. Obtaining an SSL certificate

  1. Configuring SSL in Denodo GraphQL Service

In this section we focus on the second step, as it is the one that affects the Governance Bridge configuration.

For enabling HTTPS in the Denodo Governance Bridge for IGC you have to add Spring Boot server.ssl.* properties to the config/application.properties file. Here is an example:

# custom port instead of the default 10099

server.port=8443

# path to the key store that holds the SSL certificate

server.ssl.key-store=path_to_keystore.jks

# password used to generate the certificate

server.ssl.key-store-password=secret

Installation of the Denodo Governance Bridge for Collibra

The distribution of the Denodo Governance Bridge for Collibra consists of:

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

  • Configuration files: application.properties and log4j2.xml (/conf folder)

  • A CMA file, denodo-collibra-metamodel.cma, that can be used to set up the Collibra Platform metamodel (/conf folder). See the Create Collibra metamodel section.

  • A sample JSON file, input.json, required for the execution using the script for synchronize (/conf folder). See the Synchronization script subsection under the Synchronize Denodo VDP with Collibra Platform instance section.

  • Java libraries (/lib folder)
  • Denodo Governance Bridge application jar:
    denodo-collibra-governance-bridge-<version>-jar

  • Denodo driver jar: denodo-vdp-jdbcdriver-<version>-full.jar

If you need to use a different Denodo driver version from the one that is distributed, you have to replace this jar by the Denodo driver of the proper version.

In order to install the Denodo Governance Bridge for Collibra, just download the .zip file and extract the tool into the desired folder.

Before running the Denodo Governance Bridge for Collibra, the user has to review and complete the application.properties file. It has some default properties that can be reset and some connection information that should be setted. See the section on the Configuration of the Denodo Governance Bridge for Collibra for more detailed information.

After completing the configuration and running the script denodo-collibra-governance-bridge.sh|bat available in the /bin folder, you can trigger the application.

Configuration of the Denodo Governance Bridge for Collibra

The application.properties file, available in the /conf folder, allows the user to set the properties required to run the application.

Collibra connection properties

  • collibra.url: URL of the Collibra Platform instance
  • collibra.username: username for the Collibra Platform instance
  • collibra.password: password for the Collibra Platform instance

Denodo connection properties

  • denodo.driver-class-name: the class name of the Denodo JDBC driver
  • denodo.host: Denodo host name
  • denodo.username: username used to connect to Denodo Platform
  • denodo.password: password used to connect to Denodo Platform
  • denodo.url: Denodo JDBC connection URL
  • denodo.restful.base-url: URL where the Denodo RESTful web service is deployed

Collibra custom constants

These properties are used to map the custom types, required by the application to represent  the Denodo elements in Collibra (see the Create Collibra metamodel section for further information), to their Collibra Platform ID.

If the user setup the Collibra Platform instance using the included CMA file, these values can be left as is. Otherwise, please check the Mapping Collibra Platform type identifiers to custom constants subsection in the Create Collibra  metamodel section.

Usage

Denodo Governance Bridge for IGC

Register Denodo Asset Types in IGC

In the interactive mode, the Denodo Governance Bridge for IGC registers in IGC the Denodo asset types transparently, before the first synchronization between a VDP database and IGC.

In the batch mode, the user should register, manually, the Denodo asset types before submitting the XML files to IGC.

For this, go to https://<IGC-SERVER>/ibm/iis/igc-rest-explorer, click on bundle and choose the POST /bundles/ to register the Denodo assets: the file is located in the bundle-igc folder of the distribution of the Denodo Governance Bridge for IGC.

Figure 5  Register bundle in IGC Rest explorer

After the registration (manual or transparent), Denodo asset types are displayed in the IGC browser in the Denodo Models group:

Figure 6 Denodo type assets in IGC

Synchronize Denodo VDP with IGC: interactive mode

First of all, the Denodo Governance Bridge for IGC 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 a database to synchronize with IGC among all the databases the user can connect to.

  • Login/password

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 7 VDP server connection

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

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

Figure 8 Databases listing

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

Figure 9 Database elements tree

Figure 10 Database elements selection

Step 3. The tool informs the users about the transitive dependencies for the selected elements. These dependencies will also be 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:
  • impala_web_logs
  • hbase_blocklistip

  • DataSources:
  • impala_ds
  • hbase_ds

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

Figure 11 Transitive dependencies are also synchronized

Step 4. Users identify which IGC Database assets are connected to Denodo JDBC DataSources to complete their lineage path. This is an optional step.

For Denodo JDBC BaseViews and Columns it is possible to identify which other Database assets that have been formerly imported into the Information Server Repository, participate in the lineage report. These other Database assets are Host, Database, Database Schema, Database Table, View and Database Column.

Figure 12 IGC Database assets

To achieve this, the user can identify the Hosts  and the Databases in IGC, that are related with each Denodo DataSource of type JDBC that will be exported, as the Denodo Governance Bridge for IGC cannot deduce this information from the JDBC URI of Denodo DataSources.

Figure 13 Map Denodo JDBC assets with IGC Database assets

For each Denodo JDBC DataSource that the user maps to IGC Host  and Database  assets, the Denodo Governance Bridge for IGC will export the lineage from its Denodo JDBC BaseViews to IGC Database Tables  (or Views ) and from its Denodo Columns to IGC Database Columns--only for Denodo BaseViews that were not built using a SQL statement in Denodo VDP.

Important

When the property enable.virtual.assets of the conf/application.properties file is false (the default value), the IGC Hosts  and Databases  entered in this step must refer to existing assets in IGC. In the same way, Database Tables , Views  and Database Columns referred by Denodo JDBC BaseViews and Columns, must exist in IGC and belong to those Hosts/Databases; otherwise the IGC REST API will complain with the following error:

 

Instead, when the property enable.virtual.assets of the conf/application.properties file is true if some of the IGC Database assets referred by Denodo assets does not exist in IGC a virtual asset will be created in IGC.

Virtual assets are only displayed in lineage reports and in the Usage Information section of the assets Details page. The icons for virtual assets are lighter-colored icons than the ones which are used for assets in the catalog.

Step 5. Users give the details to connect to an IGC server, using Basic Authentication under the hood.

The registration of the Denodo bundle in IGC is done transparently by the Governance Bridge, before the first synchronization between a VDP database and IGC. Later updates of the Governance Bridge could need to modify the Denodo bundle; the Update Denodo bundle version in IGC? checkbox forces the update of the Denodo bundle in IGC. This checkbox should only be checked the first time a synchronization process is executed after installing a Governance Bridge update.

Figure 14 IGC server connection

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

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

Figure 15 Synchronization complete

Let’s show the synchronization process result on 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 16 Derived view in IGC

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

Figure 17 Derived view lineage in IGC

Figure 18  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 19  Data lineage view in VDP

Export VDP elements to XML: batch mode

The batch mode of the Denodo Governance Bridge for IGC offers a service endpoint that exports the assets and flows to XML, but it does not communicate with the IBM IGC server. This execution mode does not involve user interaction with its web interface.

This mode is useful in case you want to post-process the XMLs (e.g. enriching them with additional data in an ad-hoc process), before submitting them to the IBM IGC server.

For this batch mode you need that the Denodo Governance Bridge for IGC is up and running (script denodo-igc-governance-bridge.sh|bat). Then,  execute the script denodo-igc-export-governance-bridge.sh|bat you will find in the bin directory of the distribution:

$ cd denodo-governance-bridge-<VERSION>

$ bin/denodo-igc-export-governance-bridge.sh conf/input.json

This script uses curl for invoking the export service endpoint of the Denodo Governance Bridge for IGC. 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-igc-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(encrypted_password)",

 "outputFile":"dir/file.xml",

 "startDateISO8601":"2019-06-04T14:20:00+02:00",

 "endDateISO8601":"date in ISO 8601 format",

 "mappings":

   [

     {"datasource": "denodo_datasource1", "host":"igc_host1", 

         "database":"igc_database1"},

     {"datasource": "denodo_datasource2", "host":"igc_host2", 

         "database":"igc_database2"}

   ]

}

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, that 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 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-10-23-124942.xml

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

Note that the user account running the Denodo Governance Bridge for IGC needs write privileges in the output folder.

  • startDateISO8601 (optional): The tool will export VDP elements  that were created or modified after the specified date. The date  should be specified in ISO 8601 format, e.g. 2019-06-04T14:20:00+02:00.

  • endDateISO8601 (optional): The tool will export VDP elements that were created or modified before the specified date. 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 modified between the two intervals will be exported.

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

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

If neither startDateISO8601 nor endDateISO8601 are provided, all the VDP elements of the database will be exported.

  • mappings (optional): the IGC Host  and the IGC Database  that are related with each Denodo DataSource of type JDBC that will be exported. This information is required for linking Denodo lineage with other existing assets as the Governance Bridge cannot deduce this information from the JDBC URI of the Denodo DataSource.

This field is mandatory if you want to complete the lineage path between Denodo JDBC DataSources and other IGC Database assets that have been formerly imported into the Information Server Repository. These other Database assets are Host , Database , Database Schema , Database Table , View  and Database Column .

For each Denodo JDBC DataSource that the user maps to IGC Host  and Database  assets, the Denodo Governance Bridge for IGC will export the lineage from its Denodo JDBC BaseViews to IGC Database Tables  (or Views ) and from its Denodo Columns  to IGC Database Columns--only for Denodo BaseViews that were not built using a SQL statement in Denodo VDP.

Important

When the property enable.virtual.assets of the conf/application.properties file is false (the default value), the IGC Hosts  and Databases  entered in the input.json file must refer to existing assets in IGC. In the same way, Database Tables , Views  and Database Columns  referred by Denodo JDBC BaseViews and Columns, must exist in IGC and belong to those Hosts/Databases; otherwise when submitting the XML files to the IGC server, the IGC REST API will complain with the following error:

{

  "errors": [

    {

       "code": "CDIGC1072E",

       "message": "Tried uploading flows of a flow unit which is  

                   missing from the metadata server. Asset

                   ID=\"ID243\".",

       "explanation": "A flow unit asset must get imported before

                       uploading its flows.",

       "userResponse": "First, import this flow unit, and then

                        try to upload its flows again."

    }

  ]

}

 

Instead, when the property enable.virtual.assets of the conf/application.properties file is true if some of the IGC Database assets referred by Denodo assets does not exist in IGC a virtual asset will be created in IGC.

Virtual assets are only displayed in lineage reports and in the Usage Information section of the assets Details page. The icons for virtual assets are lighter-colored icons than the ones which are used for assets in the catalog.

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-igc-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-igc-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 submit XMLs to the IBM IGC server

Before submitting the XMLs files to the IBM IGC server the Denodo assets should have been registered in IGC. See Register Denodo Asset Types in IGC section for a detailed explanation.

Assets should be uploaded before flows to IGC. To submit the assets, go to https://<IGC-SERVER>/ibm/iis/igc-rest-explorer, click on bundles, choose POST /bundles/assets and paste the content of the file generated by the exportation process that ends with the suffix: _assets_timestamp.

  • REST endpoint is https://<IGC-SERVER>/ibm/iis/igc-rest/v1/bundles/assets.

Figure 20  Upload assets in IGC Rest explorer

To submit the flows, go to https://<IGC-SERVER>/ibm/iis/igc-rest-explorer, click on flows, choose POST /flows/uploads and paste the content of the file generated by the exportation process that ends with the suffix: _flows_timestamp.

  • REST endpoint is https://<IGC-SERVER>/ibm/iis/igc-rest/v1/flows/upload.

Figure 21  Upload flows in IGC Rest explorer

Denodo Governance Bridge for Collibra

Create Collibra metamodel

The Denodo Governance Bridge for Collibra needs a specific metamodel on the Collibra Platform instance. This expected metamodel includes some out-of-the-box types but it also includes custom types that the user should create before running this application.

This Denodo-Collibra integration includes a CMA file that can be used to prepare the required metamodel. However, this section will outline the custom elements that should be available on the Collibra Platform instance. This allows the user to create the metamodel on his own, without a CMA file.

Domain

The user must create a Domain or Domains with the Physical Data Dictionary type where the Denodo integration assets will be upserted.

The CMA creates a root-level community named Denodo-Collibra Integration that has a domain named Denodo Data Dictionary. Note that only one domain (Denodo-Collibra Integration ) is included in the CMA. However, this integration supports separate domains for each Denodo database.

Asset Types

Asset Type

Description

Parent Asset Type

Denodo Folder

Represents a Denodo folder

BI Folder

Denodo Data Source

Represents a Denodo data source

BI Data Source

Attribute Types

Attribute Type

Description

Kind

Dependency Type

The dependency (e.g. Union) between two views

Text

Multiplicity

The multiplicity of the column-to-column associations

Text

Left Role Description

The left role description of the column-to-column associations

Text

Right Role Description

The right role description of the column-to-column associations

Text

Cache Status

The cache status of the respective view

Text

View Status

The status of the respective view (available since Denodo 8.0)

Text

Relation Types

Head

Role

Co-Role

Tail

Schema

contains

is part of

Denodo Folder

Complex Relation Types

Head

Leg 1

Leg 2

Attributes

Description

Denodo View

Dependency

Name

Name

Dependency Type

Complex relation type used to provide the Denodo views

lineage

source

target

Asset Type

Asset type

Table (1:N)

Table (1:1)

Denodo Column

Association

Name

Name

Multiplicity

Left Role Description

Right Role Description

Complex relation type used to represent the column-to-

column associations

source

target

Asset Type

Asset Type

Column (1:1)

Column (1:1)

Scope

A custom scope should be created in Collibra Data Intelligence Cloud in order to refer to it in the assignments created during the set up configuration. It must also be added to the domain or domains where the Denodo assets are upserted.

The CMA file creates Denodo-Collibra Integration Scope

Assignments

The following scoped assignments should be created:

Asset Type

Add

Schema

Relations: contains Denodo Folder

Denodo Folder

Relations: is part of Schema

Table

Attributes: URL, Cache Status, View Status

Relations: Denodo View Dependency

Database View

Attributes: URL, Cache Status, View Status

Relations: Denodo View Dependency

Column

Relations: Denodo Column Association

Mapping Collibra Platform type identifiers to custom constants

Once the metamodel is created without using the CMA file, the custom constants identifiers available in the application.properties file have to be updated.

The table below shows the mapping between Collibra Platform type and custom constants:

Collibra Platform type

Custom constant

Denodo Folder

customconstant.denodo.folder

Denodo Data Source

customconstant.denodo.data.source

Dependency Type

customconstant.denodoview.complexrelation.dependencytype

Multiplicity

customconstant.denodoassociation.complexrelation.multiplicity

Left Role Description

customconstant.denodoassociation.complexrelation.leftroledescription

Right Role Description

customconstant.denodoassociation.complexrelation.rightroledescription

Cache Status

customconstant.cache.status

View Status

customconstant.view.status

Schema contains/is part of Denodo Folder

customconstant.schema.contains.denodofolder

Denodo View Dependency

customconstant.denodoview.dependency

Denodo View Dependency: source

customconstant.denodoview.complexrelation.source

Denodo View Dependency: target

customconstant.denodoview.complexrelation.target

Denodo Column Association

customconstant.denodocolumn.association

Denodo Column Association: source

customconstant.denodoassociation.complexrelation.source

Denodo Column Association: target

customconstant.denodoassociation.complexrelation.target

Synchronize Denodo VDP with Collibra Platform instance

Once the Denodo Governance Bridge for Collibra is up and running (script denodo-collibra- governance-bridge.sh|bat) it offers four methods that trigger the synchronization of Denodo Platform metadata with Collibra Platform.

Note that due to the compatibility of Denodo Governance Bridge for Collibra with Collibra Edge, available since the 20240123 version, there is a mandatory property to specify the Collibra Edge connection name created to communicate with the Denodo instance where there is the data to be synchronized. This property should be specified in each synchronization. Depending on the method used to trigger the process it can be set on the request body or using properties defined in the application.properties file. See the corresponding section for more detailed information.

The Collibra Edge connection, whose name is required, can be created in Collibra after performing the synchronization using Denodo Governance Bridge for Collibra. If it is created with the name specified in the synchronization carried out with Denodo Governance Bridge for Collibra and the same pair (Denodo database, Collibra domain id) is specified in both synchronizations, the metadata will be synchronized via Edge and Denodo Governance Bridge for Collibra. Nevertheless, note that the changes required to achieve compatibility, aimed at matching structures and naming conventions in Edge, break the backward compatibility of the Denodo Governance Bridge for Collibra.

Service endpoint

The Denodo Governance Bridge for Collibra offers an endpoint service that should be requested using an HTTPS POST method:

 https://<server-host>:<server.port>/api/sync

The server.port is 8442 by default but the user can configure it in the application.properties file.

The request body should be a JSON object defining the Edge connection name of the Collibra Edge connection created to communicate with the Denodo instance where there is the data to be synchronized and, for each database, the name and the respective Collibra domain ID that should be used. Example:

{

   "edgeConnectionName":"denodoEdgeConnection",

   "databaseDomains":{

          "denodo_database_01":"b6d78bb4-8905-4f6c-88a7-039540eeeff3",

          "denodo_database_02":"b6d78bb4-8905-4f6c-88a7-039540eeeff3"

   }

}

Accordingly, the Content-Type header must be added:

 Content-Type: application/json

The output shows information about created and un/modified asset counts. Example:

{

    "Column": {

        "created": 15,

        "un/modified": 95

    },

    "Database View": {

        "created": 0,

        "un/modified": 1

    },

    "Database": {

        "created": 0,

        "un/modified": 1

    },

    "Table": {

        "created": 5,

        "un/modified": 11

    },

    "Denodo Data Source": {

        "created": 1,

        "un/modified": 1

    },

    "Schema": {

        "created": 0,

        "un/modified": 1

    },

    "Denodo Folder": {

        "created": 1,

        "un/modified": 2

    }

}

After the synchronization, the user will have the Denodo metadata available in the Collibra Platform instance:

Figure 22  Domain in Collibra Platform instance with Denodo Platform metadata

Synchronization script

In the bin directory of the distribution you will find the script execute the script denodo-collibra-synchronize-governance-bridge.sh|bat. You can execute it in order to trigger the synchronization:

$ cd denodo-collibra-governance-bridge-<VERSION>

$ bin/denodo-collibra-synchronize-governance-bridge.sh conf/input.json

This script uses curl for invoking the synchronization service endpoint of the Denodo Governance Bridge for Collibra. 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-collibra-synchronize-governance-bridge.sh|bat script needs a JSON file with a JSON object defining the Edge connection name of the Collibra Edge connection created to communicate with the Denodo instance where there is the data to be synchronized and, for each database, the name and the respective Collibra domain ID that should be used.

input.json sample file

{

   "edgeConnectionName":"denodoEdgeConnection",

   "databaseDomains":{

          "denodo_database_01":"b6d78bb4-8905-4f6c-88a7-039540eeeff3",

          "denodo_database_02":"b6d78bb4-8905-4f6c-88a7-039540eeeff3"

   }

}


The output shows information about created and un/modified asset counts. Example:

$ bin/denodo-collibra-synchronize-governance-bridge.sh conf/input.json

HTTP/1.1 200

Content-Type: application/json

Transfer-Encoding: chunked

Date: Fri, 30 Jun 2023 09:49:12 GMT

{"Column":{"created":99,"un/modified":0},"Table":{"created":3,"un/modified":0},"Database":{"created":0,"un/modified":1},"Schema":{"created":1,"un/modified":0},"Denodo Folder":{"created":2,"un/modified":0},"Denodo Data Source":{"created":2,"un/modified":0}}

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

Collibra Platform Workflow

Whenever the user wants a Collibra Workflow to communicate with the Denodo Governance Bridge for Collibra, they can create a user task for the integration to pick it up.

The Collibra Workflow can be a simple workflow that creates a user task called Sync Data User Task. This workflow should be configured to be applied to specific domain types that reside under a particular Collibra Community. After that, the user can start the workflow by simply pressing a button on the domain:

Figure 23 Sync Data User Task button

Check the Collibra Workflow Event-Driven Triggering for Spring Boot Integrations documentation, available in the Collibra Marketplace, for more detailed information.

Note that the user has to configure, in the application.properties file, the trigger.collibra.workflow properties:

  • trigger.collibra.workflow.enabled: true to enable the Collibra Workflow triggering
  • trigger.collibra.workflow.polling-frequency.ms: frequency in milliseconds used by the Denodo Governance Bridge for Collibra to check for Collibra Workflow triggers
  • trigger.collibra.workflow.user-task: name of the Collibra Workflow user task that should be used
  • trigger.collibra.workflow.edge.connection.name: name of the Collibra Edge connection created to communicate with the Denodo instance where there is the data to be synchronized
  • trigger.collibra.workflow.database: Denodo database name that should be processed once the Collibra Workflow is triggered using the Sync Data User Task
  • trigger.collibra.workflow.domain.id: ID of the Collibra domain that must be used when the Collibra Workflow is triggered using the Sync Data User Task

Cron Scheduler

The synchronization, using the Denodo Governance Bridge for Collibra, can be triggered using a cron scheduler. To accomplish this, there are two properties in the application.properties file:

  • trigger.scheduler.cron.enabled: true if the user wants to use this triggering mode
  • trigger.scheduler.cron.expression: cron expression to establish the frequency when the synchronization should be automatically triggered. This cron expression has 6 fields:

  1. The seconds at which it should be triggered. (Valid values are: 0 to 59)
  2. The minute at which it should be triggered. (Valid values are: 0 to 59)
  3. The hour at which it should be triggered. (Valid values are: 0 to 23)
  4. The day of the month at which it should be triggered. (Valid values are: 1 to 31)
  5. The month at which it should be triggered. (Valid values are: 1 to 12, or JAN-DEC)
  6. The day of the week at which it should be triggered. (Valid values are: 0 to 7, or MON-SUN)        

        Example:        

trigger.scheduler.cron.enabled=true

trigger.scheduler.cron.expression=0 0 9-17 * * MON-FRI

Based on this cron configuration example, the Denodo Governance Bridge for Collibra would be triggered on the hour nine-to-five on weekdays.

For more information about the cron expression, please refer to the Spring Boot documentation

Note that this synchronization mode will use the information setting in the collibra.edge.connection.name and collibra.domain.ids properties of the application.properties file:

collibra.edge.connection.name=edgeConnectionName

collibra.domain.ids={\

    "Denodo_database_01": "12d2a285-6dc3-4368-ba87-6a9295377fce",\

    "Denodo_database_02": "08245c84-10bc-4a33-a36b-8f71715a123f"\

}

How to encrypt passwords

The Denodo Governance Bridge expects encrypted passwords in the input.json (Denodo Governance Bridge for IGC) or application.properties (Denodo Governance for Collibra) file, 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, to communicate the encryption password to the Denodo Governance Bridge.

This way, you can use encrypted passwords in the input.json file:

...

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

...

Or in the application.properties file:

...

password=ENC(s2FdirMK4QORq1HZ6tcTTQ==)

...

 

These are the steps for encrypting passwords:

  1. Download Jasypt CLI tools.

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

  1. Go to jasypt/bin.

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

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

Example in the input.json file:

Before Jasypt:

"password":"admin"

After Jasypt

"password":"ENC(zrass64ls4LIx5hdFoXXyA==)"

        Example in the application.properties file:        

Before Jasypt:

password=admin

After Jasypt

password=ENC(zrass64ls4LIx5hdFoXXyA==)

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

Limitations

Denodo Governance Bridge for IGC

View deletion

Successive exports of the same database (or folder) accomplished by the Denodo Governance Bridge for IGC 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 for IGC exclusively manages Denodo elements and, in case of Denodo JDBC elements, their relation with native IGC Database assets. It is not aware of the presence of other metadata elements for the Denodo data source, elements that could be present in IGC or not, and managed in ways that might not be recognizable by external tools such as the Denodo Governance Bridge for IGC.

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

Denodo Governance Bridge for Collibra

Lineage

The Denodo Governance Bridge for Collibra shows relations between Denodo elements that give rise to Denodo Derived Views.

Figure 24 Denodo View Dependency example

However, this application does not yet have data lineage from the data sources.

Sample data

The Denodo Governance Bridge for Collibra transforms and upserts Denodo Platform metadata to a Collibra Platform instance but it does not collect sample data. You can use Edge in order to establish a connection to Denodo using the JDBC driver and then register, profile and classify Denodo data via Edge.