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.
driver
- The name of the driver to use on your system. Note that this is only considered if allow_driver_in_extra is set to True in airflow config section providers.odbc (by default it is not considered). Note: if setting this config from env vars, use AIRFLOW__PROVIDERS_ODBC__ALLOW_DRIVER_IN_EXTRA=true.

Note

If setting allow_driver_extra to True, this allows users to set the driver via the Airflow Connection’s extra field. By default this is not allowed. If enabling this functionality, you should make sure that you trust the users who can edit connections in the UI to not use it maliciously.

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 18 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+18+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 18 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