Skip to main content
MySQL

MySQL

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

​
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

​
Python Requirements

We have support for Python versions 3.9-3.11
To run the MySQL ingestion, you will need to install: 
pip3 install "openmetadata-ingestion[mysql]"

​
Metadata

Note that We support MySQL (version 8.0.0 or greater) and the user should have access to the INFORMATION_SCHEMA table. By default a user can see only the rows in the INFORMATION_SCHEMA that correspond to objects for which the user has the proper access privileges. 
-- Create user. If <hostName> is omitted, defaults to '%'
-- More details https://dev.mysql.com/doc/refman/8.0/en/create-user.html
CREATE USER '<username>'[@'<hostName>'] IDENTIFIED BY '<password>';
-- Grant select on a database
GRANT SELECT ON world.* TO '<username>';
-- Grant select on a database
GRANT SELECT ON world.* TO '<username>';
-- Grant select on a specific object
GRANT SELECT ON world.hello TO '<username>';

​
Lineage & Usage

To extract lineage & usage you need to enable the query logging in mysql and the user used in the connection needs to have select access to the mysql.general_log. 
-- Enable Logging
SET GLOBAL general_log='ON';
set GLOBAL log_output='table';
-- Grant SELECT on log table
GRANT SELECT ON mysql.general_log TO '<username>'@'<host>';

​
1. Define the YAML Config

This is a sample config for MySQL Lineage:
1

Source Configuration - Source Config

​
Source Configuration - Source Config

You can find all the definitions and types for the sourceConfig here.
2

queryLogDuration

queryLogDuration: Configuration to tune how far we want to look back in query logs to process lineage data in days.
3

parsingTimeoutLimit

parsingTimeoutLimit: Configuration to set the timeout for parsing the query in seconds.
4

filterCondition

filterCondition: Condition to filter the query history.
5

resultLimit

resultLimit: Configuration to set the limit for query logs.
6

queryLogFilePath

queryLogFilePath: Configuration to set the file path for query logs.
7

databaseFilterPattern

databaseFilterPattern: Regex to only fetch databases that matches the pattern.
8

schemaFilterPattern

schemaFilterPattern: Regex to only fetch tables or databases that matches the pattern.
9

tableFilterPattern

tableFilterPattern: Regex to only fetch tables or databases that matches the pattern.
10

overrideViewLineage

overrideViewLineage: Set the ‘Override View Lineage’ toggle to control whether to override the existing view lineage.
11

processViewLineage

processViewLineage: Set the ‘Process View Lineage’ toggle to control whether to process view lineage.
12

processQueryLineage

processQueryLineage: Set the ‘Process Query Lineage’ toggle to control whether to process query lineage.
13

processStoredProcedureLineage

processStoredProcedureLineage: Set the ‘Process Stored ProcedureLog Lineage’ toggle to control whether to process stored procedure lineage.
14

threads

threads: Number of Threads to use in order to parallelize lineage ingestion.
15

Sink Configuration

​
Sink Configuration

To send the metadata to OpenMetadata, it needs to be specified as type: metadata-rest.
16

Workflow Configuration

​
Workflow Configuration

The main property here is the openMetadataServerConfig, where you can define the host and security provider of your OpenMetadata installation. For a simple, local installation using our docker containers, this looks like:
  • You can learn more about how to configure and run the Lineage Workflow to extract Lineage data from here

​
Profiler & Data Quality

Executing the profiler workflow or data quality tests, will require the user to have SELECT permission on the tables/schemas where the profiler/tests will be executed. More information on the profiler workflow setup can be found here and data quality tests here.

​
Metadata Ingestion

All connectors are defined as JSON Schemas. Here you can find the structure to create a connection to MySQL. 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 MySQL:

​
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.

​
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

​
Securing MySQL Connection with SSL in OpenMetadata

To establish secure connections between OpenMetadata and MySQL, navigate to the Advanced Config section. Here, you can provide the CA certificate used for SSL validation by specifying the caCertificate. Alternatively, if both client and server require mutual authentication, you’ll need to use all three parameters: ssl_key, ssl_cert, and ssl_ca. In this case, ssl_cert is used for the client’s SSL certificate, ssl_key for the private key associated with the SSL certificate, and ssl_ca for the CA certificate to validate the server’s certificate. 
      sslConfig:
            caCertificate: "/path/to/ca_certificate"
            sslCertificate: "/path/to/your/ssl_cert"
            sslKey: "/path/to/your/ssl_key"

​
dbt Integration

dbt Integration

Learn more about how to ingest dbt models