TimescaleDB

BETA

Feature List

✓ Metadata

✓ Query Usage

✓ Data Profiler

✓ Data Quality

✓ dbt

✓ Lineage

✓ Column-level Lineage

✓ Owners

✓ Tags

✓ Stored Procedures

✓ Sample Data

✓ Auto-Classification

✕ Stored Procedures Lineage

In this section, we provide guides and references to use the TimescaleDB connector. Configure and schedule TimescaleDB metadata and profiler workflows from the OpenMetadata UI:

Requirements
Metadata Ingestion
Query Usage
Lineage
Data Profiler
Data Quality
dbt Integration
Enable Security

How to Run the Connector Externally

To run the Ingestion via the UI you’ll need to use the OpenMetadata Ingestion Container, which comes shipped with custom Airflow plugins to handle the workflow deployment. If, instead, you want to manage your workflows externally on your preferred orchestrator, you can check the following docs to run the Ingestion Framework anywhere.

External Schedulers

Get more information about running the Ingestion Framework Externally

Requirements

Note: TimescaleDB is built on PostgreSQL. Note that we only support officially supported PostgreSQL versions. You can check the version list here.

Usage and Lineage considerations

When extracting lineage and usage information from TimescaleDB we base our finding on the pg_stat_statements table. You can find more information about it on the official docs. Another interesting consideration here is explained in the following SO question. As a summary:

The pg_stat_statements has no time data embedded in it.
It will show all queries from the last reset (one can call pg_stat_statements_reset()).

Then, when extracting usage and lineage data, the query log duration will have no impact, only the query limit. Note: For usage and lineage grant your user pg_read_all_stats permission.

GRANT pg_read_all_stats TO your_user;

Python Requirements

We have support for Python versions 3.9-3.11

To run the TimescaleDB ingestion, you will need to install:

pip3 install "openmetadata-ingestion[postgres]"

IAM Authentication

In order to be able to connect via IAM, you need to have the following:

Database is configured to use IAM authentication Ensure that the RDS has IAM DB authentication enabled. Otherwise, you can click on Modify to enable it.
The user has the necessary IAM permissions Even if you use IAM to connect to TimescaleDB, you need to specify a user to prepare the connection. You need to create a user as follows:

CREATE USER iam_user WITH LOGIN;
GRANT rds_iam TO iam_user;

The AWS Role has the necessary permissions The role that is going to be used to perform the ingestion, needs to have the following permissions:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "rds-db:connect"
            ],
            "Resource": [
                "arn:aws:rds-db:eu-west-1:<aws_account_number>:dbuser:<rds_db_resource_id>/<timescale_user>"
            ]
        }
    ]
}

Otherwise, you might be finding issues such as PAM authentication failed for user “user”

Metadata Ingestion

All connectors are defined as JSON Schemas. Here you can find the structure to create a connection to TimescaleDB. In order to create and run a Metadata Ingestion workflow, we will follow the steps to create a YAML configuration able to connect to the source, process the Entities if needed, and reach the OpenMetadata server. The workflow is modeled around the following JSON Schema

1. Define the YAML Config

This is a sample config for TimescaleDB:

2. Run with the CLI

First, we will need to save the YAML file. Afterward, and with all requirements installed, we can run:

metadata ingest -c <path-to-yaml>

Note that from connector to connector, this recipe will always be the same. By updating the YAML configuration, you will be able to extract metadata from different sources.

Query Usage

The Query Usage workflow will be using the query-parser processor. After running a Metadata Ingestion workflow, we can run Query Usage workflow. While the serviceName will be the same to that was used in Metadata Ingestion, so the ingestion bot can get the serviceConnection details from the server.

1. Define the YAML Config

This is a sample config for Usage:

2. Run with the CLI

After saving the YAML config, we will run the command the same way we did for the metadata ingestion:

metadata usage -c <path-to-yaml>

Lineage

After running a Metadata Ingestion workflow, we can run Lineage workflow. While the serviceName will be the same to that was used in Metadata Ingestion, so the ingestion bot can get the serviceConnection details from the server.

1. Define the YAML Config

This is a sample config for Lineage:

You can learn more about how to configure and run the Lineage Workflow to extract Lineage data from here

2. Run with the CLI

After saving the YAML config, we will run the command the same way we did for the metadata ingestion:

metadata ingest -c <path-to-yaml>

Data Profiler

The Data Profiler workflow will be using the orm-profiler processor. After running a Metadata Ingestion workflow, we can run the Data Profiler workflow. While the serviceName will be the same to that was used in Metadata Ingestion, so the ingestion bot can get the serviceConnection details from the server.

1. Define the YAML Config

This is a sample config for the profiler:

You can learn more about how to configure and run the Profiler Workflow to extract Profiler data and execute the Data Quality from here

2. Run with the CLI

After saving the YAML config, we will run the command the same way we did for the metadata ingestion:

metadata profile -c <path-to-yaml>

Note now instead of running ingest, we are using the profile command to select the Profiler workflow.

Data Profiler

Find more information about the Data Profiler here

Auto Classification

The Auto Classification workflow will be using the orm-profiler processor. After running a Metadata Ingestion workflow, we can run the Auto Classification workflow. While the serviceName will be the same to that was used in Metadata Ingestion, so the ingestion bot can get the serviceConnection details from the server.

1. Define the YAML Config

This is a sample config for the Auto Classification Workflow:

2. Run with the CLI

After saving the YAML config, we will run the command the same way we did for the metadata ingestion:

metadata classify -c <path-to-yaml>

Now instead of running ingest, we are using the classify command to select the Auto Classification workflow.

Data Quality

Adding Data Quality Test Cases from yaml config

When creating a JSON config for a test workflow the source configuration is very simple.

source:
  type: TestSuite
  serviceName: <your_service_name>
  sourceConfig:
    config:
      type: TestSuite
      entityFullyQualifiedName: <entityFqn>

The only sections you need to modify here are the serviceName (this name needs to be unique) and entityFullyQualifiedName (the entity for which we’ll be executing tests against) keys. Once you have defined your source configuration you’ll need to define te processor configuration.

processor:
  type: "orm-test-runner"
  config:
    forceUpdate: <false|true>
    testCases:
      - name: <testCaseName>
        testDefinitionName: columnValueLengthsToBeBetween
        columnName: <columnName>
        parameterValues:
          - name: minLength
            value: 10
          - name: maxLength
            value: 25
      - name: <testCaseName>
        testDefinitionName: tableRowCountToEqual
        parameterValues:
          - name: value
            value: 10

The processor type should be set to "orm-test-runner". For accepted test definition names and parameter value names refer to the tests page.

Note that while you can define tests directly in this YAML configuration, running the workflow will execute ALL THE TESTS present in the table, regardless of what you are defining in the YAML.This makes it easy for any user to contribute tests via the UI, while maintaining the test execution external.

You can keep your YAML config as simple as follows if the table already has tests.

processor:
  type: "orm-test-runner"
  config: {}

Key reference:

forceUpdate: if the test case exists (base on the test case name) for the entity, implements the strategy to follow when running the test (i.e. whether or not to update parameters)
testCases: list of test cases to add to the entity referenced. Note that we will execute all the tests present in the Table.
name: test case name
testDefinitionName: test definition
columnName: only applies to column test. The name of the column to run the test against
parameterValues: parameter values of the test

The sink and workflowConfig will have the same settings as the ingestion and profiler workflow.

Full `yaml` config example

source:
  type: TestSuite
  serviceName: MyAwesomeTestSuite
  sourceConfig:
    config:
      type: TestSuite
      entityFullyQualifiedName: MySQL.default.openmetadata_db.tag_usage
#     testCases: ["run_only_this_test_case"] # Optional, if not provided all tests will be executed

processor:
  type: "orm-test-runner"
  config:
    forceUpdate: false
    testCases:
      - name: column_value_length_tagFQN
        testDefinitionName: columnValueLengthsToBeBetween
        columnName: tagFQN
        parameterValues:
          - name: minLength
            value: 10
          - name: maxLength
            value: 25
      - name: table_row_count_test
        testDefinitionName: tableRowCountToEqual
        parameterValues:
          - name: value
            value: 10

sink:
  type: metadata-rest
  config: {}
workflowConfig:
  openMetadataServerConfig:
    hostPort: <OpenMetadata host and port>
    authProvider: <OpenMetadata auth provider>

How to Run Tests

To run the tests from the CLI execute the following command

metadata test -c /path/to/my/config.yaml

Advanced Configuration

Connection Options (Optional): Enter the details for any additional connection options that can be sent to database during the connection. These details must be added as Key-Value pairs. Connection Arguments (Optional): Enter the details for any additional connection arguments such as security or protocol configs that can be sent to database during the connection. These details must be added as Key-Value pairs.

In case you are using Single-Sign-On (SSO) for authentication, add the authenticator details in the Connection Arguments as a Key-Value pair as follows: "authenticator" : "sso_login_url"

The sslConfig and sslMode are used to configure the SSL (Secure Sockets Layer) connection between your application and the TimescaleDB server. TimescaleDB (PostgreSQL) will require only rootCertificate i.e caCertificate. caCertificate: This is the path to the CA (Certificate Authority) certificate file. This file is used to verify the server’s certificate. sslMode: This field controls whether a secure SSL/TLS connection will be negotiated with the server. There are several modes you can choose: disable: No SSL/TLS encryption will be used; the data sent over the network is not encrypted. allow: The driver will try to negotiate a non-SSL connection but if the server insists on SSL, it will switch to SSL. prefer (the default): The driver will try to negotiate an SSL connection but if the server does not support SSL, it will switch to a non-SSL connection. require: The driver will try to negotiate an SSL connection. If the server does not support SSL, the driver will not fall back to a non-SSL connection. verify-ca: The driver will negotiate an SSL connection and verify that the server certificate is issued by a trusted certificate authority (CA). verify-full: The driver will negotiate an SSL connection, verify that the server certificate is issued by a trusted CA and check that the server host name matches the one in the certificate.

Securing TimescaleDB Connection with SSL in OpenMetadata

To configure SSL for secure connections between OpenMetadata and a TimescaleDB database, TimescaleDB (PostgreSQL) offers various SSL modes, each providing different levels of connection security. When running the ingestion process externally, specify the SSL mode to be used for the TimescaleDB connection, such as prefer, verify-ca, allow, and others. Once you’ve chosen the SSL mode, provide the CA certificate for SSL validation (caCertificate). Only the CA certificate is required for SSL validation in TimescaleDB (PostgreSQL).

For IAM authentication, it is recommended to select the allow mode or another SSL mode that aligns with your specific needs.

      sslMode: disable #allow prefer require verify-ca verify-full
      sslConfig:
            caCertificate: "/path/to/ca/certificate"

dbt Integration

You can learn more about how to ingest dbt models’ definitions and their lineage here.

Connectors

Connector

Ingestion

Run the TimescaleDB Connector Externally

TimescaleDB

How to Run the Connector Externally

External Schedulers

Requirements

Usage and Lineage considerations

Python Requirements

IAM Authentication

Metadata Ingestion

1. Define the YAML Config

2. Run with the CLI

Query Usage

1. Define the YAML Config

2. Run with the CLI

Lineage

1. Define the YAML Config

2. Run with the CLI

Data Profiler

1. Define the YAML Config

2. Run with the CLI

Data Profiler

Auto Classification

1. Define the YAML Config

2. Run with the CLI

Data Quality

Adding Data Quality Test Cases from yaml config

Key reference:

Full `yaml` config example

How to Run Tests

Advanced Configuration

Securing TimescaleDB Connection with SSL in OpenMetadata

dbt Integration

Connectors

Connector

Ingestion

TimescaleDB

​How to Run the Connector Externally

External Schedulers

​Requirements

​Usage and Lineage considerations

​Python Requirements

​IAM Authentication

​Metadata Ingestion

​1. Define the YAML Config

​2. Run with the CLI

​Query Usage

​1. Define the YAML Config

​2. Run with the CLI

​Lineage

​1. Define the YAML Config

​2. Run with the CLI

​Data Profiler

​1. Define the YAML Config

​2. Run with the CLI

Data Profiler

​Auto Classification

​1. Define the YAML Config

​2. Run with the CLI

​Data Quality

​Adding Data Quality Test Cases from yaml config

​Key reference:

​Full yaml config example

​How to Run Tests

​Advanced Configuration

​Securing TimescaleDB Connection with SSL in OpenMetadata

​dbt Integration

How to Run the Connector Externally

Requirements

Usage and Lineage considerations

Python Requirements

IAM Authentication

Metadata Ingestion

1. Define the YAML Config

2. Run with the CLI

Query Usage

1. Define the YAML Config

2. Run with the CLI

Lineage

1. Define the YAML Config

2. Run with the CLI

Data Profiler

1. Define the YAML Config

2. Run with the CLI

Auto Classification

1. Define the YAML Config

2. Run with the CLI

Data Quality

Adding Data Quality Test Cases from yaml config

Key reference:

Full `yaml` config example

How to Run Tests

Advanced Configuration

Securing TimescaleDB Connection with SSL in OpenMetadata

dbt Integration