Skip to main content
Version: 13.x (Current)

Configuration

Due to licensing Data Catalog Agent requires packaging with third-party software, unless your datasources are PostgreSQL databases whose support is already included in the docker image of Data Catalog Agent.

In case only PostgreSQL is needed you can skip to the cronjob configuration section.

Datasource support

Mia's Data Catalog marketplace item upon creation, creates also a git repository containing:

  1. a CI config file
  2. a docker Dockerfile

the Dockerfile provides several build steps to include the datasource support you need. Supported datasources are:

  • Oracle Database from 11 to 21
  • PostgreSQL from 9.6 to 16
  • Mysql from 5.7 to 8.2
  • Mssql from 2017 to 2022

Upon inspection you'll notice that the Dockerfile contains targets for each database:

FROM ... AS data-catalog-agent-oracle

# rest of the file

FROM ... AS data-catalog-agent-mysql

# and so on...

Each push on the default branch or any tag starting with v will create a new docker image including the selected support. The default support is postgres but it can be changed:

  1. by editing the .gitlab-ci.yml file overriding the initial value of the DOCKER_TARGET from postgres to the database you need to support
  2. one-of change upon manually triggering the CI overriding the same variable DOCKER_TARGET

Your repository contains a README.md file which also contains a lists of operations needed to include drivers for oracle, mysql, mssql databases.

Oracle Database: add support

In the template repository, to enable Oracle Database support actions are needed:

  1. edit the value of DOCKER_TARGET variable in .gitlab-ci.yml to oracle or alternatively select it on manual run
  2. embed oracle drivers (see embed drivers)
  3. [optional] add tnsnames.ora and/or Oracle Wallet (see add tns/wallet).

Embed drivers

In the template repository, download drivers from the official page according with the version you need.

The following assets are needed:

  1. ODBC driver
  2. instantclient basiclite
# on unix
export ORACLE_CLIENT_VERSION="<VERSION>"
unzip -j instantclient-basiclite-linux.x64-${ORACLE_CLIENT_VERSION}dbru.zip "instantclient_*/lib*" -d lib/x86_64-unknown-linux-gnu/oracle
unzip -j instantclient-odbc-linux.x64-${ORACLE_CLIENT_VERSION}dbru.zip "instantclient_*/lib*" -d lib/x86_64-unknown-linux-gnu/oracle

create a symlink

export LIBSQORA_NAME=`ls lib/x86_64-unknown-linux-gnu/oracle | grep -E -i '^libsqora\.so\.'`
ln -s "${LIBSQORA_NAME}" lib/x86_64-unknown-linux-gnu/oracle/libsqora.so

Add TNS support

Oracle connectivity can be handled with tnsnames.ora files and/or wallets. The container expects such assets in the /home/agent/oracle/admin folder. The TNS_ADMIN is already set to /home/agent/oracle/admin and the container will copy the content of the oracle/admin folder in this repository.

Drop your wallet assets or tnsnames.ora file in oracle/admin to include them.

In case you want to use a wallet remind to [tune] the sqlora.net file registering the /home/agent/oracle/admin path:

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/home/agent/oracle/admin"))) SSL_SERVER_DN_MATCH=yes

MySQL: add support

In the template repository, ODBC driver for MySQL must be downloaded from the official website selecting:

  1. version 8.3.0
  2. OS Linux - Generic
  3. OS version glibc 2.28 x86_64

then run:

export MYSQL_NAME=mysql-connector-odbc-8.3.0-linux-glibc2.28-x86-64bit
tar -xvf ${MYSQL_NAME}.tar.gz ${MYSQL_NAME}/lib/libmyodbc8w.so
mv ${MYSQL_NAME}/lib/* lib/x86_64-unknown-linux-gnu/mysql

MS SQL Server: add support

No further actions beside editing .gitlab-ci.yml are required.

Cronjob Configuration

From your project select CronJobs > Create new CronJob, insert a unique name for the job, an optional description, the full name of the docker image created by your CI job. It will come in the form <URL TO REGISTRY>/<NAME OF PROJECT>/data-catalog-agent:<TAG>. Also insert a CronJob schedule using the k8s syntax.

By clicking on create, you need to configure:

  1. the config map containing the configuration of the Data Catalog Agent
  2. the cronjob yaml file

Create a new config map giving it a name of your choice (e.g., data-catalog-agent-configuration) and mount path /home/agent/.config/dc/agent/. We can now add it to the CronJob yaml manifest.

The prompted yaml for the CronJob should be edited as follows:

apiVersion: batch/v1
kind: CronJob
metadata:
...
spec:
...
jobTemplate:
spec:
...
template:
...
spec:
...
containers:
containers:
- name: ...
...
env:
- name: TNS_ADMIN # not required if it defaults to /home/agent/oracle/admin or no oracle support is needed
value: /path/to/tns/admin/folder
args: ["scan"] # add args here
volumeMounts:
- name: data-catalog-agent-configuration # reference a volume mount
mountPath: /home/agent/.config/dc/agent/
volumes:
- name: data-catalog-agent-configuration # use the name given to the config map
configMap:
name: data-catalog-agent-configuration

Now create a new file in the config map called configuration.json. This file must be a valid Data Catalog Agent configuration.

Data Catalog Agent Configuration

caution

The configuration is read from a file which must be located ad ~/.config/dc/agent/configuration.json where ~ is the user's home folder ($HOME). Such location can be overridden via the environment variable DC_AGENT_CONFIGURATION_FILEPATH which must contain a filepath to a valid configuration file.

The configuration has the following main sections:

  1. connections
  2. target
{
"connections": {
...
},
"target": {
...
}
}

Secret support

In k8s environments secrets can be injected in a running workload as an environment variable, as a standalone file or a INI key in a standalone file. Such secrets may be base64 encoded.

Data Catalog Agent configuration supports referencing such secrets inline in selected fields of the JSON configuration file. When the field supports secrets you may write a plain string or objects.

In case of a string the secret is considered plain and written in the config file. In case of an object with env guard like:

{
"type": "env",
"key": "MY_SECRET_ENV_VAR"
}

the agent will use the content of the env var MY_SECRET_ENV_VAR. An extra encoding field equal to base64 can be used to specify a pre-read decoded to use.

In case of an object with file guard like:

{
"type": "file",
"path": "/path/to/secret"
}

it will use the content of the file on such path. If the file is formatted as an ini file a key may be specified

{
"type": "file",
"path": "/path/to/secret",
"key": "CONNECTION_STRING"
}

An extra encoding field equal to base64 can be used to specify a pre-read decoded to use.

Secretable fields are marked in the following sections.

Connections

A list of entries to connect to datasources via supported integration methods. The general recipe is:

{
"connections": {
"<CONNECTION_NAME>": {
"type": "<INTEGRATION_METHOD>",
"configuration": {
// depends on the integration method
}
}
}
}

Available integration methods are:

Such drivers come often in the form of a dynamic shared object and must be embedded with the binary and available at runtime (see the template repository README file).

ODBC

PostgreSQL

To configure a PostgreSQL connection use:

{
"connections": {
"<CONNECTION_NAME>": { // 👈 pick a name
"type": "odbc",
"configuration": {
"vendor": "postgres",
"params": {
"uid": "test_user",
"pwd": "password",
"database": "<DATABASE_NAME>"
}
}
}
}
}

or use an inline ODBC connection string

{
"connections": {
"<CONNECTION_NAME>": { // 👈 pick a name
"type": "odbc",
"configuration": {
"vendor": "postgres",
"params": "DRIVER=postgres;SERVER=0.0.0.0;PORT=5432;DATABASE=db;UID=user;PWD=pwd;"
}
}
}
}

Other keys are host and port which for a PostgreSQL connection are defaulted to 0.0.0.0 and 5432. Any other configuration parameter can be appended using the key flags which must be a semicolon separated string.

Secretable fields

uid, pwd or params support secrets

Oracle

To configure an Oracle connection use:

{
"connections": {
"<CONNECTION_NAME>": { // 👈 pick a name
"type": "odbc",
"configuration": {
"vendor": "oracle",
"params": {
"uid": "test_user",
"pwd": "password",
"dbq": "<SERVICE_NAME>"
}
}
}
}
}

or use an inline ODBC connection string and also set the TNS_ADMIN environment variable to access a tnsnames.ora file where the DBQ name can be resolved

{
"connections": {
"<CONNECTION_NAME>": { // 👈 pick a name
"type": "odbc",
"configuration": {
"vendor": "oracle",
"params": {
"uid": "test_user",
"pwd": "password",
// 👇 this must be defined in your tnsnames.ora file
"dbq": "DRIVER=oracle;UID=user;PWD=p4ssw0rd;DBQ=DATABASE_NAME"
}
}
}
}
}

SERVICE_NAME is an entry of your tnsnames.ora file. Such file MUST be available when running the binary and its location MUST be exported to an environment variable unless defaulted to /home/agent/oracle/admin.

in case you don't want to add the environment variable, the content of one entry of the tnsnames.ora file can be inlined such as:

{
"connections": {
"<CONNECTION_NAME>": {
"type": "odbc",
"configuration": {
"vendor": "oracle",
"params": {
...,
"dbq": "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=5041))(CONN ..."
}
}
}
}
}

Other keys are:

caution

version MUST be explicit if "11".

Connection with an oracle wallet is also supported:

Preparation must be done on the template repository:

  1. The whole wallet content must be available under the same path of the tnsnames.ora (they usually come together)
  2. The file sqlora.net inside the wallet must be updated to reflect the TNS_ADMIN path

    WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/path/to/folder"))) SSL_SERVER_DN_MATCH=yes

Also the environment variable must be set:

  1. The TNS_ADMIN environment variable must be set explicitly unless defaulted to /home/agent/oracle/admin
Secretable fields

uid, pwd or params support secrets

MS SQL server

To configure a MS SQL Server connection use:

{
"connections": {
"<CONNECTION_NAME>": {
"type": "odbc",
"configuration": {
"vendor": "mssql",
"params": {
"uid": "test_user",
"pwd": "password",
"database": "<DATABASE_NAME>"
}
}
}
}
}

or use an inline ODBC connection string:

{
"connections": {
"<CONNECTION_NAME>": {
"type": "odbc",
"configuration": {
"vendor": "mssql",
"params": "Driver=mssql;Server=0.0.0.0,1433;Database=db;Uid=user;Pwd=p4ssw0rd;TrustServerCertificate=yes;"
}
}
}
}

Other keys are host and port which for a PostgreSQL connection are defaulted to 0.0.0.0 and 1433. Any extra connection property can be added via the key flags which will be added, as a string, to the connection, as a semicolon separated string. It is quite useful, for local development purposes to add the flag "TrustServerCertificate=yes".

Secretable fields

uid, pwd or params support secrets

MySQL

To configure a MySQL connection use:

{
"connections": {
"<CONNECTION_NAME>": {
"type": "odbc",
"configuration": {
"vendor": "mysql",
"params": {
"uid": "test_user",
"pwd": "password",
"database": "<DATABASE_NAME>"
}
}
}
}
}

or use an inline ODBC connection string:

{
"connections": {
"<CONNECTION_NAME>": {
"type": "odbc",
"configuration": {
"vendor": "mysql",
"params": "DRIVER=mysql;SERVER=0.0.0.0;PORT=3306;DATABASE=db;UID=user;PWD=p4ssw0rd;"
}
}
}
}

Other keys are host and port which for a PostgreSQL connection are defaulted to 0.0.0.0 and 3306. Any extra connection property can be added via the key flags which will be added, as a string, to the connection, as a semicolon separated string.

Secretable fields

uid, pwd or params support secrets

HTTP

Mia CRUD Service

To configure a Mia CRUD Service connection use:

{
"connections": {
"<CONNECTION_NAME>": {
"type": "http",
"configuration": {
"vendor": "mia-crud-service",
"params": {
"baseUrl": "http://mia-crud-service:3000",
"endpoint": "/-/schemas",
"headers": {
"accept": "application/x-ndjson"
}
}
}
}
}
}

The driver basically calls the /-/schemas URL to extract all the data models from MongoDB. Since the response is, of course, a JSON Schema, some information in the ddl object may be missing, like the size of the properties.

Salesforce SObjects

To configure a Salesforce SObjects connection you can use two authentication methods:

  • jwt-bearer:
    {
    "connections": {
    "<CONNECTION_NAME>": {
    "type": "http",
    "configuration": {
    "vendor": "salesforce-sobjects",
    "params": {
    "authenticationFlow": "jwt-bearer",
    "baseUrl": "https://my-subdomain.my.salesforce.com",
    "clientId": "XXXXXXXXXXXXXXXXXXXXXXXXXX",
    "privateKey": "/path/to/private-key/key.pem",
    "username": "my-user@email.com",
    "apiVersion": "59.0"
    }
    }
    }
    }
    }
  • username-password: (deprecated)
    {
    "connections": {
    "<CONNECTION_NAME>": {
    "type": "http",
    "configuration": {
    "vendor": "salesforce-sobjects",
    "params": {
    "authenticationFlow": "username-password",
    "baseUrl": "https://my-subdomain.my.salesforce.com",
    "apiVersion": "31.0",
    "clientId": "XXXXXXXXXXXXXXXXXXXXXXXXXX",
    "clientSecret": "XXXXXXXXXXXXXXXXXXXXXXXXXX",
    "username": "my-user@email.com",
    "password": "XXXXXXXXXXXXXXXXXXXXXXXXXX",
    "securityToken": "XXXXXXXXXXXXXXXXXXXXXXXXXX"
    }
    }
    }
    }
    }

The range of supported apiVersion goes from 31.0 to 59.0

The driver uses the Salesforce SObjects API to retrieve the schema from all the SObjects you have. Make sure the user you configure has the right permissions to retrieve all the SObjects you need.

If not all the SObjects are retrieved you'll have to follow these steps to expose them through the API:

For custom SObjects

  • Go to your Salesforce dashboard
  • Access Setup
  • Access the "Permission Sets" section
  • Create a new Permission Set (e.g. API for Custom Objects) with license type "Salesforce API Integration"
  • Click on "Object Settings" inside the new Permission Set, then, for each custom SObject, update the visibility in the desired way (e.g. View All, Modify All)
  • Assign the new Permission Set to the user with "Salesforce Integration" user license and "Salesforce API Only System Integrations" profile

For standard SObjects

  • Go to your Salesforce dashboard
  • Access Setup
  • Access the "Permission Sets" section
  • Create a Permission Set (e.g. API for Standard Objects) without specifying a license type
  • Click on "Object Settings" inside the new Permission Set, then, for each standard SObject, update the visibility in the desired way (e.g. View All, Modify All)
  • Assign the new Permission Set to the user with "Salesforce Integration" user license and "Salesforce API Only System Integrations" profile
JWT Authorization

To be able to connect to the Salesforce API using the JWT Bearer flow you will need to follow the next steps.

  1. Follow the steps one and two from this guide: https://help.salesforce.com/s/articleView?id=sf.connected_app_create_api_integration.htm&type=5
  2. Then you'll need to upload your self-signed certificate in PEM format (step 5 from the guide). If you don't have a certificate follow these instructions:
    1. Generate a rsa key with the command openssl genrsa -out tls/key.pem
    2. Then create a request to sign the certificate with this command openssl req -new -key tls/key.pem -out tls/sf_connect.csr
    3. Sign the request with you private key with openssl x509 -signkey tls/key.pem -in tls/sf_connect.csr -req -out tls/sf_connect.crt
    4. Upload the generated certificate (tls/sf_connect.crt)
  3. Now you need to enable the OAuth2 mechanism following the 10th step from the guide, creating a connected app and all.
  4. Finally you need to enable the JWT access with the step 14 of the guide.

Now you should have everything you need to fill out the configuration parameters. If you are using a testing instance you'll need to set the param loginUrl to https://test.salesforce.com/.

Secretable fields

clientId, username, clientSecret, password, securityToken or privateKey support secrets

Targets

There are 3 targets available:

  1. [default] stdout
  2. file
  3. mia-console

For each listed connection, after metadata is retrieved, agent sequentially sends data to the target as:

  • json for stdout and file
  • ndjson for mia-console

The final content is an array of models. Model spec is given in the form of a JSON schema.

To explicitly configure the stdout target use:

{
...,
"target": {
"type": "stdout"
}
}

To configure the file target use:

{
...,
"target": {
"type": "file"
}
}

which will save output files in the folder ./output. To override this use:

{
...,
"target": {
"type": "file",
"dir": "/path/to/dir"
}
}

To configure the mia-console target use:

{
...,
"target": {
"type": "mia-console",
"baseUrl": "https://my-server-url", // 👈 mia console base url
"projectId": "1234", // 👈 models are pushed towards the project with this id
"apiKey": "1234", // 👈 mia console api key, may vary across test/preview/prod environments
"credentials": {
// machine to machine credentials
}
}
}

credentials are used to obtain an access token. In order to do that you must provide:

{
...,
"target": {
...,
"credentials": {
"clientId": "1234",
"clientKeyId": "123",
"privateKey": {"type": "file", "path": "tls/key.pem"} // 👈 either a file system path or an rsa private key inlined with `\n`
}
}
}

on type mia-console the auth endpoint can be customized using oauthTokenEndpoint. The service endpoint hosted on mia-console can be overridden in 2 ways:

  1. customizing the revision using the field revision
  2. or customizing the overall url using the field dataCatalogEndpoint
Secretable fields

clientId, clientKeyId, or privateKey support secrets