Run the MSSQL Connector Externally - OpenMetadata Documentation

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

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

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

MSSQL User must grant SELECT privilege to fetch the metadata of tables and views.

-- Create a new user
-- More details https://learn.microsoft.com/en-us/sql/t-sql/statements/create-user-transact-sql?view=sql-server-ver16
CREATE USER Mary WITH PASSWORD = '********';
-- Grant SELECT on table
GRANT SELECT TO Mary;

View Definitions

View lineage requires the VIEW DEFINITION (database-level) or VIEW ANY DEFINITION (server-level) permission. Without it, SQL Server returns each view’s definition as NULL and raises no error, so views are ingested without their SQL and no view lineage is created. Table metadata is not affected.

-- Database-level: grant to the database user, in every database you ingest
GRANT VIEW DEFINITION TO Mary;
-- Or server-level: grant to the login (not the contained user) to cover all databases
GRANT VIEW ANY DEFINITION TO [login_name];

Usage & Lineage consideration

To perform the query analysis for Usage and Lineage computation, the connector reads query history from SQL Server’s Query Store when it is enabled on a database. Query Store exposes this history through sys.query_store_query, sys.query_store_query_text, sys.query_store_plan, and sys.query_store_runtime_stats. Query Store keeps a durable, on-disk record of executed queries. The plan-cache Dynamic Management Views (DMVs) (sys.dm_exec_cached_plans, sys.dm_exec_query_stats, and sys.dm_exec_sql_text) are evicted on server restart, memory pressure, or plan recompilation, and can silently return incomplete history. Query Store is auto-detected per database. No configuration is needed. When ingestAllDatabases is enabled, each database is read from its own best source independently: databases with Query Store use it, and the rest fall back to the plan-cache DMVs, so one database without Query Store never downgrades the others.

Tip: Enable Query Store on the databases you ingest, for reliable and durable Usage and Lineage history.

To check whether Query Store is enabled on a database, and how it is currently operating:

SELECT actual_state_desc, query_capture_mode_desc, size_based_cleanup_mode_desc
FROM sys.database_query_store_options;

To enable it:

ALTER DATABASE [YourDatabase] SET QUERY_STORE = ON
(
    QUERY_CAPTURE_MODE = ALL,
    CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 30 )
);

The default QUERY_CAPTURE_MODE on SQL Server 2019 and later is AUTO, which can skip queries that only run a few times, such as a one-off data migration statement. Setting it to ALL ensures every query is captured for lineage. Set STALE_QUERY_THRESHOLD_DAYS to cover at least your ingestion lookback window, so queries are not purged before they are read. Query Store’s default SIZE_BASED_CLEANUP_MODE purges the oldest queries to stay within MAX_STORAGE_SIZE_MB, which reduces the risk of Query Store switching to read-only. Under heavy write load, cleanup can still fall behind, and Query Store can switch to read-only temporarily until it catches up.

Query Store needs two permissions together. The database-level grant below lets sys.database_query_store_options and Query Store’s other metadata views succeed, but the query text comes from sys.query_store_query_text, which needs the server-level grant. Without the server-level grant, usage and lineage queries can run without error while returning no query text.

Database-level, for Query Store metadata: VIEW DATABASE STATE (SQL Server 2016-2019) or VIEW DATABASE PERFORMANCE STATE (SQL Server 2022 and later).

GRANT VIEW DATABASE STATE TO YourUser;
-- SQL Server 2022 and later: GRANT VIEW DATABASE PERFORMANCE STATE TO YourUser;

Server-level, for the query text and the plan-cache DMV fallback: VIEW SERVER STATE (SQL Server 2019 and earlier) or VIEW SERVER PERFORMANCE STATE (SQL Server 2022 and later).

GRANT VIEW SERVER STATE TO YourUser;
-- SQL Server 2022 and later: GRANT VIEW SERVER PERFORMANCE STATE TO YourUser;

Exact grant names and tiers can differ on Azure SQL Database and Azure SQL Managed Instance. Check sys.database_query_store_options and the Microsoft documentation for your instance.

Python Requirements

We have support for Python versions 3.9-3.11

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

pip3 install "openmetadata-ingestion[mssql]"

Metadata Ingestion

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

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

dbt Integration

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

​How to Run the Connector Externally

External Schedulers

​Requirements

​View Definitions

​Usage & Lineage consideration

​Python Requirements

​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

​dbt Integration

How to Run the Connector Externally

Requirements

View Definitions

Usage & Lineage consideration

Python Requirements

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

dbt Integration