Airflow Summit 2021 is coming July 8-16. Register now!

ODBC Connection

The odbc connection type provides connection to ODBC data sources including MS SQL Server.

Enable with pip install apache-airflow[odbc].

System prerequisites

This connection type uses pyodbc, which has some system dependencies, as documented on the pyodbc wiki.

You must also install a driver:

Configuring the Connection

To use the hook OdbcHook you must specify the driver you want to use either in Connection.extra or as a parameter at hook initialization.

Host (required)

The host to connect to.

Schema (optional)

Specify the schema name to be used in the database.

Login (required)

Specify the user name to connect.

Password (required)

Specify the password to connect.

Extra (optional)

Any key / value parameters supplied here will be added to the ODBC connection string.

Additionally there a few special optional keywords that are handled separately.

  • connect_kwargs
    • key-value pairs under connect_kwargs will be passed onto pyodbc.connect as kwargs

  • sqlalchemy_scheme
    • This is only used when get_uri is invoked in get_sqlalchemy_engine(). By default, the hook uses scheme mssql+pyodbc. You may pass a string value here to override.

Note

You are responsible for installing an ODBC driver on your system.

The following examples demonstrate usage of the Microsoft ODBC driver.

For example, consider the following value for extra:

{
  "Driver": "ODBC Driver 17 for SQL Server",
  "ApplicationIntent": "ReadOnly",
  "TrustedConnection": "Yes"
}

This would produce a connection string containing these params:

DRIVER={ODBC Driver 17 for SQL Server};ApplicationIntent=ReadOnly;TrustedConnection=Yes;

See DSN and Connection String Keywords and Attributes for more info.

Example connection URI for use with environment variables etc:

export AIRFLOW_CONN_MSSQL_DEFAULT='mssql-odbc://my_user:XXXXXXXXXXXX@1.1.1.1:1433/my_database?Driver=ODBC+Driver+17+for+SQL+Server&ApplicationIntent=ReadOnly&TrustedConnection=Yes'

If you want to pass keyword arguments to pyodbc.connect, you may supply a dictionary under connect_kwargs.

For example with extra as below, pyodbc.connect will be called with autocommit=False and ansi=True.

{
  "Driver": "ODBC Driver 17 for SQL Server",
  "ApplicationIntent": "ReadOnly",
  "TrustedConnection": "Yes",
  "connect_kwargs": {
    "autocommit": false,
    "ansi": true
  }
}

See pyodbc documentation for more details on what kwargs you can pass to connect

Was this entry helpful?