Skip to main content

Microsoft SQL Server

For context on getting started with ingestion, check out our metadata ingestion guide.

Setup#

To install this plugin, run pip install 'acryl-datahub[mssql]'.

We have two options for the underlying library used to connect to SQL Server: (1) python-tds and (2) pyodbc. The TDS library is pure Python and hence easier to install, but only PyODBC supports encrypted connections.

Capabilities#

This plugin extracts the following:

  • Metadata for databases, schemas, views and tables
  • Column types associated with each table/view
  • Table, row, and column statistics via optional SQL profiling

Quickstart recipe#

Check out the following recipe to get started with ingestion! See below for full configuration options.

For general pointers on writing and running a recipe, see our main recipe guide.

source:  type: mssql  config:    # Coordinates    host_port: localhost:1433    database: DemoDatabase
    # Credentials    username: user    password: pass
sink:  # sink configs
Example: using ingestion with ODBC and encryption

This requires you to have already installed the Microsoft ODBC Driver for SQL Server. See https://docs.microsoft.com/en-us/sql/connect/python/pyodbc/step-1-configure-development-environment-for-pyodbc-python-development?view=sql-server-ver15

source:  type: mssql  config:    # Coordinates    host_port: localhost:1433    database: DemoDatabase
    # Credentials    username: admin    password: password
    # Options    use_odbc: "True"    uri_args:      driver: "ODBC Driver 17 for SQL Server"      Encrypt: "yes"      TrustServerCertificate: "Yes"      ssl: "True"
sink:  # sink configs

Config details#

Note that a . is used to denote nested fields in the YAML recipe.

As a SQL-based service, the Athena integration is also supported by our SQL profiler. See here for more details on configuration.

FieldRequiredDefaultDescription
usernameMSSQL username.
passwordMSSQL password.
host_port"localhost:1433"MSSQL host URL.
databaseMSSQL database.
database_aliasAlias to apply to database when ingesting.
use_odbcFalseSee https://docs.sqlalchemy.org/en/14/dialects/mssql.html#module-sqlalchemy.dialects.mssql.pyodbc.
uri_args.<uri_arg>Arguments to URL-encode when connecting. See https://docs.microsoft.com/en-us/sql/connect/odbc/dsn-connection-string-attribute?view=sql-server-ver15.
env"PROD"Environment to use in namespace when constructing URNs.
options.<option>Any options specified here will be passed to SQLAlchemy's create_engine as kwargs.
See https://docs.sqlalchemy.org/en/14/core/engines.html#sqlalchemy.create_engine for details.
table_pattern.allowList of regex patterns for tables to include in ingestion.
table_pattern.denyList of regex patterns for tables to exclude from ingestion.
table_pattern.ignoreCaseTrueWhether to ignore case sensitivity during pattern matching.
schema_pattern.allowList of regex patterns for schemas to include in ingestion.
schema_pattern.denyList of regex patterns for schemas to exclude from ingestion.
schema_pattern.ignoreCaseTrueWhether to ignore case sensitivity during pattern matching.
view_pattern.allowList of regex patterns for views to include in ingestion.
view_pattern.denyList of regex patterns for views to exclude from ingestion.
view_pattern.ignoreCaseTrueWhether to ignore case sensitivity during pattern matching.
include_tablesTrueWhether tables should be ingested.
include_viewsTrueWhether views should be ingested.

Compatibility#

Coming soon!

Questions#

If you've got any questions on configuring this source, feel free to ping us on our Slack!