Overview

Purpose of this app is to parse SQL statements and calculate what are the most popular columns and tables in your database. 

The result is propagated into DGC thus helping your users to pay attention to the most frequently used entities.

Prerequisites

  1. Oracle database (we extract query log from from v$sql)
  2. download Mule Anypoint Studio and Collibra Connect
  3. follow installation instructions (Collibra domain is not required): https://community.collibra.com/docs/connect/1.4/#Installation/to_installing-dev-environment.htm

App import

To import "SQLdep occurrences integration" project into Anypoint studio:

  1. unzip sqldep-occurrences-to-collibra.zip  somewhere on disk
  2. in Anypoint studio, click File → Import... → General → Existing Project into Workspace 
  3. click "Browse..." button at "Select root directory" field and select sqldep-occurrences-to-collibra  directory from the unzipped archive
  4. click "Finish" button to import the project

You should now see sqldep-occurrences-to-collibra  project in Package Explorer view. Main Mule flow file is implementation.xml  under src/main/app  directory.

Configuration

Before first run of the flow, you need to set properties listed bellow. You can do so in directory src/main/resources  and either edit default.properties file or create a new text file with name configuration.properties.

Connection to an Oracle DB instance – source of DB log

  • db.host  – host name
  • db.port  – database port
  • db.user  – user name
  • db.password  – password
  • db.name  – source database name

Connection to SQLdep server

Collibra DGC connection

  • dgc.config.user  – DGC user who will be used during import
  • dgc.config.password – password
  • dgc.config.baseApplicationUrl  – base URL of the DGC instance

Custom DGC entities

  • dgc.domain.id  – ID of target DGC domain for imported data (Technology Asset Doman)
  • dgc.db.relation.schema – default value is "Database contains/is part of Schema" relation. In older older DGC versions this relation is not pre-created and you need to do it manually: https://community.collibra.com/docs/user/5.5/#Assets/Relations/ta_create-relation-type.htm
  • dgc.direct.occurrences , dgc.indirect.occurrences and dgc.column.popularity – IDs of custom attributes for storing occurrences counts. Create these similarly as the relation above

Log reading SQL

  • log.sql – optionally customize SQL statement for retrieving query log

Display Occurrences in DGC

To make the custom occurrences attributes visible in DGC on a page of a column asset:

Associate new attribute with the Column asset type

  1. in DGC, go to Settings → Asset Types 
  2. find "Column" and click on it
  3. select Global Assignments → Characteristics  and click on the lonely Edit button on the right
  4. click "Add characteristic" button which has just appeared
  5. in the dialog, select your custom occurrences attributes
  6. change Minimum cardinality to 1 (like this it will always be visible on the page)

Modify Column view

  1. select any imported Column in target domain and click on the green "Add characteristics" button on the left
  2. select your custom occurrences attributes one by one
  3. Save the view

Flow Run

Now you can run the integration flow in Anypoint studio. Please note that we will upsert  any column or table which we find in SQL statements.

  1. right-click the implementation.xml  file and select Run As →Mule Application 
  2. after a while, you should see in Anypoint studio Console that application sqldep-occurrences-to-collibra  has status DEPLOYED
  3. to start the integration flow run, enter http://localhost:8081/loadDbLog (if you didn’t change the http.host  and http.port  properties) into your web browser

You should see in your browser window "Success" along with a link to target DGC domain. Otherwise an error will be printed. Error details will be in the Console of Anypoint studio.

Performance

Analysis of a single query takes about 0.5 second. It might take a while to process your complete query log. 

You can check processing state here -- https://app.sqldep.com/dashboard/ 

Support

In case you run into troubles do not hesitate to contact us at friendly.support@sqldep.com

Did this answer your question?