Importing Informatica Cloud Data Governance and Catalog tags in Denodo Virtual DataPort
You can translate the document:Introduction
The Informatica Cloud Data Governance and Catalog (CDGC), part of the Informatica Intelligent Data Management Cloud (IDMC) suite, is a cloud-native tool that combines data governance, catalog, and quality capabilities.
Among its functionalities, it utilizes automated data classifications to identify and manage data assets. Data classifications are equivalent to tags in Denodo and their purpose is to enhance data discoverability, ensure compliance by applying relevant policies, and improve usability through bulk curation and crowdsourcing.
Denodo Virtual DataPort and Data Catalog tags provide similar functionalities as they are used to categorize data elements for easier retrieval, aiding in data governance, security, and version control.
The objective of this article is to provide an example of how to replicate CDGC classifications in Denodo Virtual DataPort using tags. The same tags might be later brought to the Denodo Data Catalog with the sync functionality.
In Informatica CDGC, there are 2 different types of data classifications:
- Data element classification: refers to the classification of columns or fields of tables or files.
- Data entity classification: is a collection of data elements and identifies data entities contained in a data set (i.e. a table).
In our demonstration, we are interested in bringing the Data Element / Data Entity classifications to fields or tables in Denodo. Indeed, Denodo tags can similarly be assigned to fields and views.
Overall procedure
Denodo natively provides some functionality to import metadata from an external catalog. We will leverage the CREATE_TAGS_FROM_VIEW Stored Procedure that imports a set of tags in a specific schema and is also capable of assigning them to columns and views.
The required schema is specified inside the CREATE_TAGS_FROM_VIEW user documentation page and is defined by an Interface View (i_tags_definition) that we are going to implement.
Therefore the overall steps are going to be:
- Find the required information from Informatica CDGC Public REST API: CDGC Public REST API > List of API Endpoints.
- Authenticate over CDGC using the Identity Service to obtain a Session Id and a JWT Token.
- Retrieve paginated results.
- Create a JSON Data Source in Denodo to read the CDGC REST API responses. The JWT Token is passed on to this data source.
- Implementing some integration with Denodo views to provide an implementation for the i_tags_definition interface.
- Run the import Stored Procedure and check the result from the output.
- Verify the result of the import.
Below is a chart that summarizes the main steps of the implementation.
Please, note that the following operations have been excluded from the chart:
- Every occurrence of the Join with the Stored Procedure that realizes the pagination. This will be explained in the Dealing with pagination section.
- Every occurrence of the Join with the view to retrieve tokens, it will be explained in the Authentication toward the Identity Provider section.
The process to create the data sources, views and stored procedures needed to implement this integration are explained in detail in the following sections of this document. As a reference you can also use this VQL that contains the creation of all the necessary elements. Note that you will have to adapt this VQL to fit you specific environment (credentials, URLs, …).
Assumption
Informatica CDGC provides information on tables and Data Classifications defined by tables and columns. In this document, we are assuming the same tables are already created as base views in Denodo. All the CDGC Rest API responses will reference the physical table names. For this reason, we will implement a method to retrieve the base view name given an input table name.
Authentication toward the Identity Provider
Introduction & Prerequisites
The following Informatica document provides a Postman collection that can be imported in Postman and used to test the authentication and explore the listed endpoint API: CDGC Public REST API - Postman Collection Sample.
In this section, we are going to use the same authentication method in a Denodo Data Source to extract the required information. The goal is to retrieve a Session Id and a JWT Token that we can use to query the REST APIs.
The requirements are listed below:
- A CDGC valid User and Password to authenticate over the Identity Service.
- The Global Identity Service base URL. The complete list of DNS names can be found in Appendix A: DNS names for Informatica Intelligent Cloud Services services depending on your region. For instance, we are going to use https://dm-em.informaticacloud.com (European Union).
- The Client Id and the nonce value of your CDGC Rest APIs.
Retrieving the JWT Token from the Identity Service
Create a new JSON Data Source: ds_identity_service_sessionid and set the following information:
- Data Route: HTTP Client
- HTTP method: POST.
- Base URL: https://dm-em.informaticacloud.com/identity-service/api/v1/Login
- Content type: application/json
- Post body:
|
\{ "username": "@{username}", "password": "@{password}" \} |
Create the associated base view (Name bv_identity_service_sessionid) providing the Username and Password you have available. The field sessionid will contain the retrieved session id. We will need a valid one for the next step, therefore it is useful to test one execution of this Base View, providing the Username and Password inside the Execution Panel and annotating the session id.
Create a second JSON Data Source to retrieve the JWT Token. Provide the following parameters:
- Name: ds_identity_service_token
- Data route: HTTP Client
- HTTP Method: POST
- Base URL: https://dm-em.informaticacloud.com/identity-service/api/v1/jwt/Token?client_id=<your_client_id>&nonce=<your_nonce_value>
- In the HTTP Header section, add the following parameters:
- cookie: USER_SESSION=@{user_session_id}
- IDS-SESSION-ID: @{session_id}
Create the base view: the user_session_id and session_id can be both filled with the session id retrieved from the bv_identity_service_sessionid base view. Name it bv_identity_service_token.
As the last step of the authentication, we are going to create a new integration view that provides the session id to the JWT Token retrieval view and provides the credentials to the session id retrieval view.
Therefore, create a new join view, drag & drop the Views bv_identity_service_sessionid and bv_identity_service_token, and join the Identity Service’s Session Id with the Token View’s Session Id and User Session Id.
The last requisite to make the View work without user input is to provide the Username and Password parameters. For this reason, we are adding a couple of View Parameters (username_param and password_param) that will be used to specify our credentials. To do this hit the Edit View Parameters button and add the parameters with username and password as default values.
Finally, the following string has to be added to the
Where Conditions tab of the View:
|
(bv_identity_service_sessionid.password = password_param AND bv_identity_service_sessionid.username = username_param) |
Give the view a name (iv_retrieve_token) and test an execution. Check if the token is generated.
Conclusions
The iv_retrieve_token allows retrieving the JWT Token which is required by every call to the CDGC REST APIs. For this reason, every time we are executing a call to these APIs we are going to create a join view between the iv_retrieve_token view and the view that makes the call to the REST APIs endpoint.
We can organize the elements created so far in folders. Usually, the folder structure suggested by Denodo is based on the different layers based on the functional definition. However, since we are building a series of views that are only required for the authentication process we can place them in a dedicated folder. For instance:
Integration with Informatica CDGC REST APIs
Creating the Import tag interface
The user documentation guide suggests the usage of an interface to read and manage tag assignments correctly. The interface will be used by the CREATE_TAG_FROM_VIEW stored procedure or using the Virtual DataPort import tag functionality.
The interface can be created using the following VQL statement and can be pasted directly into a VQL shell:
|
CREATE OR REPLACE INTERFACE VIEW i_tags_definition ( element_type:text, database_name:text, view_name:text, column_name:text, tag:text, tag_description:text ); |
In the following section, we will build some base and derived views in order to provide an implementation for the i_tag_definition interface.
Finding the classification information
The following Informatica CDGC Rest API endpoint will be used to retrieve the list of the available classifications (data entity and data element classifications):
|
https://idmc-api.dm-em.informaticacloud.com/data360/search/v1/assets?knowledgeQuery=Classifications&segments=all |
This page returns all the classifications defined in the CDGC catalog with different information formatted in a JSON document.
We are going to use the following fields:
- "core.identity": Identifier of the classification object.
- "core.name": Name of the classification.
- "core.description": Description of the classification.
- "core.sensitivity": Sensitivity level of the classification. This field is not mandatory but we will use it (following our sample requirements) to enrich the tag description.
Results are returned by Informatica CDGC in JSON format. The classifications are returned in a “hits” array. Below is a sample of a hit:
|
{ "core.identity": "bbdd0ace-8869-45e4-8721-2ba634923d0c", "core.externalId": "d068664b-66e0-48e3-a703-e1b079891d62", "summary": { "core.location": "MCC://bbdd0ace-8869-45e4-8721-2ba634923d0c", "core.name": "Tax Residence", "core.description": "Tax residence of the customer" }, "systemAttributes": { "core.modifiedOn": 1720001836510, "core.createdBy": "ge049adxe2ihCksKeKh7au", "core.modifiedBy": "ge049adxe2ihCksKeKh7au", "core.classType": "core.DataElementClassification", "core.origin": "MCC", "type": [ "core.Classification", "core.IClass", "core.DataElementClassification" ], "core.createdOn": 1720001836510 }, "selfAttributes": { "core.sensitivity": "MEDIUM", "core.supplement.certified": [ false ], [..] |
Furthermore, we will need to know the classifications that have been assigned to some existing column or view inside the CDGC catalog. This information can be found in the classification details API endpoint by providing a specific <core.identity> as parameter:
|
https://idmc-api.dm-em.informaticacloud.com/data360/search/v1/assets/<core.identity>?scheme=internal&segments=all |
From this JSON document we will need:
- "Association": different information on the classification is available from this endpoint (organized as a list of neighbors). We are going to select only the information that indicates the column or the table with which the classification has been associated. For this purpose, the first useful filter is “association”: “core.classifiedAs”.
- “fromType” field will be also used to filter the neighbor list and extract the information on the associated object. Therefore, its values will be used to distinguish classification as follows:
- "com.infa.odin.models.relational.Column": indicates a classification assigned to a column, a Data Element classification.
- 'com.infa.odin.models.relational.Table': indicates a classification assigned to a table, a Data Entity classification.
- "From": this will be the actual name of the associated column or table.
- "fromLocation": this is the full path of the column or table. Among the information, it contains the schema name and the table or column name. We already have the associated table and column name from the ‘field’ However, when dealing with Data Element classifications (columns) we will parse this field to retrieve also the table name.
Below is an example of Data Element classification hit:
|
{ "core.identity": "bbdd0ace-8869-45e4-8721-2ba634923d0c", "core.externalId": "d068664b-66e0-48e3-a703-e1b079891d62", [..] "neighborhood": [ { "type": "com.infa.odin.models.relational.Column", "neighbors": [ { "neighbor": "cust_id", "paths": [ { "collection": [ { "association": "core.ClassifiedAs", "curationStatus": "ACCEPTED", "from": "cust_id", "to": "Tax Code", "fromType": "com.infa.odin.models.
"toType": "core.
"fromLocation": "c5663b37-fe30-3ffc-a5a5-d93a03da99ab://c5663b37-fe30-3ffc-a5a5-d93a03da99ab/INFATEST/SDBU/TRANSACTIONS/CUST_ID", "toLocation": [..] |
Data Source creation and authentication
Based on our analysis we are going to create two JSON data sources, one for each REST APIs query. They are listed below:
- ds_cdgc_api_all_classifications: this is the Data Source that will retrieve the list of classifications.
- Data Route: HTTP Client
- HTTP Method: POST
- Base URL: https://idmc-api.dm-em.informaticacloud.com/data360/search/v1/assets?knowledgeQuery=Classifications&segments=all
- Content type: application/json
- Post body:
|
\{ "from":@{PAGE_START}, "size":100 \} |
- HTTP Headers:
- Authorization: Bearer @Token
- Accept: */*
- Host: idmc-api.dm-em.informaticacloud.com
- Content-Type: application/json
- ds_cdgc_api_classification_details: this second data source will retrieve the classification details.
- Data route: HTTP Client
- HTTP Method: GET
- Base URL: https://idmc-api.dm-em.informaticacloud.com/data360/search/v1/assets/@{ASSET_ID}?scheme=internal&segments=all
- HTTP Headers: use the same header as ds_cdgc_api_all_classifications
For both data sources, we can create a base view. The required token can be acquired by executing the iv_retrieve_token view while the page_start parameter can be set to 0. Give the Base View a name, for instance:
bv_all_classifications_cdgc_api and bv_classification_details_cdgc_api.
We are almost able to execute calls to the CDGC APIs, the last step is to automate the retrieval of the token, similarly to how we did to create the base views. For this purpose, create two join views and join the iv_retrieve_token with each base view bv_all_classifications_cdgc_api and bv_classification_details_cdgc_api using the token field (token:jwt_token).
Rename the Join views. For instance: iv_all_classifications_cdgc_api_auth and iv_classification_details_cdgc_api_auth. You can also test an execution and check if the queries are producing any hits. The result is a structured JSON file, therefore we will need some transformation to be able to leverage its content in Denodo.
Dealing with pagination
The pagination method implemented by the CDGC REST APIs is not among the supported pagination strategies of Denodo. The strategies are explained here: Pagination Strategies in Denodo.
What the rest APIs are providing are two parameters from and size that we can use to control pagination. Inside the POST body of ds_cdgc_api_all_classifications Data Source, we inserted a "from":@{PAGE_START} parameter, so we can use it to iterate through the various pages.
Create a Stored Procedure to generate the various page numbers: New > Stored Procedure > VQL stored procedure with the following VQL code:
|
CREATE OR REPLACE VQL PROCEDURE sp_recursion FOLDER = '/1. authentication/5. stored procedures' (n_in IN INTEGER, n_max IN INTEGER, out_list OUT INTEGER) AS ( CURSOR my_cursor IS 'SELECT out_list from informatica_cdgc_tag_import.sp_recursion() where n_in = :my_param1 and n_max = :my_param2'; curs_r my_cursor%ROWTYPE; n_var INTEGER; ) BEGIN IF n_in >= n_max THEN RETURN ROW ( out_list) VALUES (n_in); ELSE n_var:=(n_in+100); OPEN my_cursor PARAMETERS ( my_param1, my_param2 ) VALUES ( n_var, n_max ); LOOP FETCH my_cursor INTO curs_r; RETURN ROW ( out_list) VALUES (curs_r.out_list); EXIT WHEN my_cursor%NOTFOUND; END LOOP; CLOSE my_cursor; RETURN ROW ( out_list) VALUES (n_in); END IF;
END; |
Give it a name (for instance: sp_recursion) and save. Test an execution using 0 as n_in and a large number as n_max (for instance 1000). This will generate the page_start indexes from 0 to 1000 with an offset of 100. 100 is the maximum page size in our CDGC REST APIs.
After that, create a new join view on top of iv_all_classifications_auth_with_pagination.
Drag & drop the stored procedure, insert the two values that will be stored (for instance 0 and 1000), and join page_start with out_list.
Insert the following WHERE CONDITION in the dedicated tab, it will discard the page_index without hits:
|
iv_all_classifications_cdgc_api_auth.hits is not null |
Save the view and provide a name (for instance: iv_all_classifications_auth_with_pagination). Finally, execute the view and check if you can see the hits of the query from different pages.
Go back to the Edit > Output tab, remove the fields: token, page_start, n_in, n_max, out_list that we do not need, and save.
You might want to organize these integration views in a separate folder, for instance:
Flattening
The result of the views we created is a series of Register and Arrays. We will need to manipulate classification data with relational operators that work on two-dimensional tabular views. For this reason, we are going to create two Flatten Views.
Flatten of the all_classification view
Open the iv_all_classifications_auth_with_pagination view, and create a new flatten view with Create > Flatten.
Configure the flattening at the “hits” array level by hitting the in-line icon. This operation will expand the array of Classifications.
Many Registers are still present. From the output tab select the “hits_summary” and “selfattributes” registers and click “Project subfields”.
Ultimately, it is better to perform some cleaning of the output columns we do not need. Remove all the fields except for the following:
- coreidentity
- corename
- coredescription
- coresensitivity
Edit the field ‘coredescription’ and set it to the following expression:
|
REPLACE(f_all_classifications.coredescription, '''', '') |
This is required to remove single quotes that are not tolerated by the CREATE_TAGS_FROM_VIEW Stored Procedure.
Call the View
f_all_classifications.
Flatten of the classification_details View
Open the iv_all_classifications_auth_with_pagination and, similarly, create a new Flatten View. This time, we will flatten at the “collection” array level, which contains the list of neighbors that we are going to filter.
We do not need any flattening of the Register as the required information are subfields of the collection array. Finally, delete all the columns except for:
- coreidentity_0
- association
- from_0
- fromtype
- fromlocation
Building the classification lists
The two flatten views we have just created must now be combined to obtain the details of all the classifications. The details provide information on the Data Entity and Data Element classifications. iv_all_classifications_auth_with_pagination requires a parameter to be executed and we are going to join exactly on that parameter.
Therefore, create a new join view, drag and drop f_all_classifications and f_classification_details, and configure a join between coreidentity and core_id.
Set the Join Type from the Join Conditions tab to Left Join since not every classification must have a column/view associated and, as a consequence, a classification detail.
From the Output tab: edit the from_0 field as follows:
|
lower(f_classification_details.from_0) |
Finally, remove the following output fields:
- coreidentity
- core_id
- association
Select a name, ‘iv_all_classification_with_details’ in our example, and hit Save.
This is going to be the final result:
This view contains common information on both Data Element and Data Entity Classifications. We will now differentiate between the two classification types as they must be extracted in different ways, as explained in the section
Finding the classification information.
Preparing the Data Element Classifications
Create a new Selection View over the iv_all_classification_with_details View and add the following Where Condition:
|
iv_all_classification_with_details.fromtype = 'com.infa.odin.models.relational.Column' |
In the Output tab:
- Remove the coredescription, coresensitivity, fromlocation
- Rename the from_0 field to column_name
- Add a new field “splitted_location” and insert the following formula that applies a string split based on the ‘/’ character:
|
split('/', iv_all_classification_with_details.fromlocation) |
This will be an intermediate View that we can call iv_all_data_element_work.
After that, create a new Selection View over the work view, go to the Output tab, and edit the splitted_location field. Call it source_table_name and set the following Field Expression to extract the table name from the full path:
|
lower((iv_all_data_element.splitted_location)[5].string) |
Call the view iv_all_data_element_final and test its execution to retrieve the list of Data Element classifications.
Preparing the Data Entity Classifications
As we already did for the Data Element we are going to extract the Data Entity list from the iv_all_classification_with_details. Therefore, create a new Selection View over this View and insert the following Where Condition:
|
iv_all_classification_with_details.fromtype = 'com.infa.odin.models.relational.Table' |
From the Output tab, rename the from_0 to source_table_name. Remove all the columns besides:
- corename
- source_table_name
- fromtype
Test the execution and check the list of Data Entity classifications.
Combining all classification information
We have built two views that contain the Data Element classifications and the Data Entity classifications. Based on the CREATE_TAG_FROM_VIEW stored procedure documentation we know that the interface i_tags_definition expects information structured as follows:
In summary:
- Tuples with element_type = ‘tag’: provides information on the tag to be created, including their description
- Tuples with element_type = ‘view’: provides the list of tags to be assigned on views (Data Entity classifications).
- Tuples with element_type = ‘column’: provide the list of views to be assigned to view columns (Data Element classifications).
Please, note that a tag that is assigned to a column or a view (element_type=‘column’ or ‘view’) must also be present as element_type=’tag’ if some of its information is required to be set, for instance: the description. For this reason, we are going to create a Union View with all three element_types.
From New > Union View (extended) create a new view and drag & drop the f_all_classifications, iv_all_data_element_final and iv_all_data_entity views. The extended union allows a combination of views that do not have the same exact schema, as in our case. The extra columns will be filled with null values.
Check that the field corename, fromtype and source_table_name have been associated.
The situation should be the following:
And:
We are also going to build the fields required by the i_tags_definition interface:
Delete all the fields from the Output tab and add the following with their expressions:
- element_type: the first field required by the CREATE_TAG_FROM_VIEW stored procedure is the type of element it is being imported. As anticipated we have only the ‘tag’, ‘view’ and ‘column’ type. We can check the presence of any field from the classification details view to establish the correct type of tag. Therefore, the field expression will be:
|
CASE WHEN (iv_all_data_element_final.fromtype is null ) THEN 'tag' WHEN (iv_all_data_element_final.fromtype = 'com.infa.odin.models.relational.Column') THEN 'column' WHEN (iv_all_data_element_final.fromtype = 'com.infa.odin.models.relational.Table') THEN 'view' ELSE 'ERROR' END |
- database_name: name of the virtual database where i_tags_definition resides. For instance: 'informatica_cdgc_tag_import'.
|
CASE WHEN iv_all_data_element_final.fromtype IS NULL THEN NULL ELSE 'informatica_cdgc_tag_import' END |
- source_table_name: iv_all_data_element_final.source_table_name.
- column_name: iv_all_data_element_final.column_name.
- tag: f_all_classifications.corename.
- description: as anticipated, we are adding the sensitivity to the description
|
((f_all_classifications.coredescription || ' - Sensitivity: ') || f_all_classifications.coresensitivity) |
Finally
, insert a name for the view (for instance ‘iv_all_data_classifications’) and click Save.
Executing the query you will obtain a similar result:
Retrieving the Base View name
The source_table_name field comes from the information extracted from CDGC but the CREATE_BASE_FROM_VIEW stored procedure accepts only a Denodo base view name. Hence we have to implement this substitution.
Create a new data source to connect to Denodo Virtual DataPort: New > Data Source > Denodo Virtual DataPort.
Select your Virtual DataPort version and fill in the required parameters. The database name should be the one that has the base views where the tags will be applied.
Click Create Base View and Create from Query. Insert the following VQL code:
|
select dep.view_name as bv_name, src.source_table_name from VIEW_DEPENDENCIES () DEP inner join GET_SOURCE_TABLE() SRC on src.input_database_name = dep.dep.view_database_name and src.input_view_name = dep.view_name where dep.view_database_name = '@VIRTUAL_DB_NAME' and dep.view_type = 'Base View' and dep.dependency_type = 'Jdbc Datasource' |
Finally, select a name for the base view (for instance
‘bv_retrieve_base_view’), click Ok, and try to execute it by providing the virtual database name as a parameter (e.g. ‘informatica_cdgc_tag_import’:
This view will retrieve all the base views based on JDBC data sources and their table name on the data sources in a specific virtual database. For instance:
We are going to use this view to create the final integration view that will be the implementation for our i_tags_definition interface. Create a new join view over iv_classifications_with_baseview_name and drag and drop the bv_retrieve_base_view. The join fields will be the source_table_name and the virtual database name. After that, change the join type to left, since we also have tags that are only imported and not assigned to views.
Insert also a where condition to filter out all the Data Classifications (element_type = ‘column’ or ‘view’) where the base view could not be retrieved:
|
element_type = 'tag' or bv_retrieve_base_view.bv_name is not null |
Finally, provide a name (e.g. ‘iv_classifications_with_baseview_name’) and save.
As a final step, we have to configure the implementation of the interface i_tags_definition. Open the interface and from Edit > Implementation, Drag & Drop the last integration view ‘iv_classifications_with_baseview_name’, and check that all the fields have been assigned correctly (field names should already be correct).
Test the execution of the interface and check the result.
Execute the Stored Procedure
The CREATE_TAGS_FROM_VIEW procedure has different parameters that must be set. We can use the Stored Procedure as a view and provide the parameters inside the where condition. A description of the parameters is present in the official documentation.
The mandatory parameters are the database name and the name of the interface with the elements to import: i_tags_definition.
Execute the following query in a VQL shell:
|
select *
and input_view_name = 'i_tags_definition' and input_action = 'CREATE' and input_unassign_tags_mode = 'ONLY_EXTERNAL' and input_unused_external_tags_mode = 'RETAIN' and input_incremental = false; |
The output schema of this query is similar to the i_tag_definition schema but has an additional boolean column, called assignement_status, on the right indicating if the tag was assigned to a column in case of element_type = ‘column’ or ‘view’. This status flag should be always checked to detect any error with the import (true when the import is successful).
Verify the result
Go to File > Refresh and open the Tag section of Design Studio:
As the last verification, check that the description has been imported by double-clicking on the tag.
References
AI Powered Cloud Data Management | Informatica
CREATE_TAGS_FROM_VIEW — VQL Guide (denodo.com)
CDGC Public REST API - Postman Collection Sample (informatica.com)
The information provided in the Denodo Knowledge Base is intended to assist our users in advanced uses of Denodo. Please note that the results from the application of processes and configurations detailed in these documents may vary depending on your specific environment. Use them at your own discretion.
For an official guide of supported features, please refer to the User Manuals. For questions on critical systems or complex environments we recommend you to contact your Denodo Customer Success Manager.
