Skip to main content

Commons library for ingesting RDBMS metadata into Google Cloud Data Catalog

Project description


Common resources for Data Catalog RDBMS connectors.

Python package PyPi License Issues

Disclaimer: This is not an officially supported Google product.

Table of Contents

1. Installation

Install this library in a virtualenv using pip. virtualenv is a tool to create isolated Python environments. The basic problem it addresses is one of dependencies and versions, and indirectly permissions.

With virtualenv, it's possible to install this library without needing system install permissions, and without clashing with the installed system dependencies. Make sure you use Python 3.6+.

1.1. Mac/Linux

pip3 install virtualenv
virtualenv --python python3.6 <your-env>
source <your-env>/bin/activate
<your-env>/bin/pip install google-datacatalog-rdbms-connector

1.2. Windows

pip3 install virtualenv
virtualenv --python python3.6 <your-env>
<your-env>\Scripts\pip.exe install google-datacatalog-rdbms-connector

2. Install from source

2.1. Get the code

git clone
cd datacatalog-connectors-rdbms/google-datacatalog-rdbms-connector

2.2. Virtualenv

Using virtualenv is optional, but strongly recommended.

2.2.1. Install Python 3.6
2.2.2. Create and activate a virtualenv
pip3 install virtualenv
virtualenv --python python3.6 <your-env>
source <your-env>/bin/activate
2.2.3. Install
pip install .

3. Developer environment

3.1. Install and run YAPF formatter

pip install --upgrade yapf

# Auto update files
yapf --in-place --recursive src tests

# Show diff
yapf --diff --recursive src tests

# Set up pre-commit hook
# From the root of your git project.
curl -o
chmod a+x
mv .git/hooks/pre-commit

3.2. Install and run Flake8 linter

pip install --upgrade flake8
flake8 src tests

3.3. Install the package in editable mode (i.e. setuptools “develop mode”)

pip install --editable .

3.4. Run the unit tests

python test

4. Setting up the RDBMS on a new connector

To set up the RDBMS connector to work with a Relational Database 3 files are needed.

  • metadata_definition.json
  • metadata_query.sql
  • Extending the metadata_scraper class and implementing your rdbms connection method: _create_rdbms_connection

for the metadata_definition file your have fields available for 3 levels:

  • table_container_def
  • table_def
  • column_def

If you want working examples please take a look at the already implemented connectors for: Oracle, Teradata, MySQL, PostgreSQL, Greenplum, Redshift and SQLServer.

For the metadata_defition target fields you have the following options as target:

Level Target Description Mandatory
table_container_def creator Creator of the Table Container. N
table_container_def owner Owner of the Table Container. N
table_container_def update_user Last user that updated the Table Container. N
table_container_def desc Table Container Description. N
table_def num_rows Number of rows contained in the Table. N
table_def creator Creator of the Table. N
table_def owner Owner of the Table. N
table_def update_user Last user that updated the Table. N
table_def desc Table Description. N
table_def table_size_MB Table size, in MB. N

If those fields are configured they will be used to create Tags.

For columns they are used to create the Data Catalog Entry schema, two target fields are used:

Level Target Description Mandatory
column_def type Column type. Y
column_def desc Column description. N

4.1 Add support for optional queries

You can use user configuration file and execute optional queries to scrape additional metadata from a database. You would need to do the following:

  • Create additional SQL queries. At the moment, support for two optional queries are implemented in this common RDBMS package:
    • Statement for refreshing metadata (e.g. ANALYZE)
    • Query to scrape number of rows in each table
  • Extend the query_assembler class and implement a methods for getting optional queries: _get_refresh_statement and _get_path_to_num_rows_query.
  • Implement _get_query_assembler and _execute_refresh_query on the extension of the matadata_scraper class.

You can see working examples of implementing optional queries in the connectors code for PostgreSQL and MySQL. Please also refer to these samples to see how user configuration file ingest_cfg.yaml should look like.

4.2 Add support for SQL Objects

You can use the SQL Objects mechanism from the SQL connector to ingest SQL objects such as Functions, Stored Procedures, Views, Materialized Views, and so on. This mechanism uses naming convention to locate a SQL query and a metadata definition file.

You can see working examples of implementing SQL Objects in the test sources. To enable it you need 3 configuration files:

  1. ingest_cfg.yaml

      - name: 'functions'
        enabled: True
      - name: 'stored_procedures'
        enabled: True

    The ingest_cfg.yaml file must be located at the connection execution directory. You can find a sample in the SAP HANA connector.

    You can specify a list of SQL Objects with a flag to enable/disable the SQL Objects mechanism. It will be only considered for objects whose enabled flag is set to True.

    The related query and metadata_definition files are located by the value. From the above sample, the function SQL Object requires two files: query_functions_sql_object.sql and metadata_definition_functions_sql_object.sql.

    And the stored_procedures SQL Object requires: query_stored_procedures_sql_object.sql and metadata_definition_stored_procedures_sql_object.sql.

  2. query_functions_sql_object.sql
    Implement a query file that matches the SQL Object name, with the following pattern: query_{name}_sql_object.sql this file must be located at the connector config directory. You can find a sample in the SAP HANA connector.

  3. metadata_definition_functions_sql_object.json
    Implement a metadata definition file that matches the SQL Object name, with the following pattern:metadata_definition_{}_sql_object.sql this file must be located at the connector config directory. You can find a sample in the SAP HANA connector.

    These are the required attributes for metadata_definition file:

    Field Description
    key Key used to lookup the config files
    type SQL Object type
    name Name of the SQL Object
    fields Fields definition scraped from query

    The fields attributes needs to implement the source and target definitions:

    Field Description
    fields.source Name of the field scraped by the query Object with target field attributes Name of the field when translated to Data Catalog attributes Type of Data Catalog model, can be (tag or entry) Type of the field to be translated to Data Catalog attributes

The SQL Object will be skipped if any of the 3 configuration files are missing.

Project details

Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

Built Distribution

google_datacatalog_rdbms_connector-0.12.2-py2.py3-none-any.whl (44.3 kB view hashes)

Uploaded Python 2 Python 3

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page