 .. Licensed to the Apache Software Foundation (ASF) under one
    or more contributor license agreements.  See the NOTICE file
    distributed with this work for additional information
    regarding copyright ownership.  The ASF licenses this file
    to you under the Apache License, Version 2.0 (the
    "License"); you may not use this file except in compliance
    with the License.  You may obtain a copy of the License at

 ..   http://www.apache.org/licenses/LICENSE-2.0

 .. Unless required by applicable law or agreed to in writing,
    software distributed under the License is distributed on an
    "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
    KIND, either express or implied.  See the License for the
    specific language governing permissions and limitations
    under the License.

.. _howto/connection/odbc:

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 <https://github.com/mkleehammer/pyodbc>`_, which has some system
dependencies, as documented on the `pyodbc wiki <https://github.com/mkleehammer/pyodbc/wiki/Install>`_.

You must also install a driver:

* `MS SQL ODBC drivers <https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15>`_.

* `Exasol ODBC drivers <https://docs.exasol.com/connect_exasol/drivers/odbc/odbc_linux.htm>`_.


Configuring the Connection
--------------------------

To use the hook :py:class:`~airflow.providers.odbc.hooks.odbc.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
          :py:meth:`~airflow.hooks.dbapi.DbApiHook.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 <https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15>`_.

    For example, consider the following value for ``extra``:

    .. code-block:: json

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

    This would produce a connection string containing these params:

    .. code-block::

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

    See `DSN and Connection String Keywords and Attributes <https://docs.microsoft.com/en-us/sql/connect/odbc/dsn-connection-string-attribute?view=sql-server-ver15>`_
    for more info.

    Example connection URI for use with environment variables etc:

    .. code-block:: bash

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

    .. code-block:: json

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

    See `pyodbc documentation <https://github.com/mkleehammer/pyodbc/wiki/Module>`_ for more details on what
    kwargs you can pass to ``connect``