The ODBC extension allows connecting to other databases (using their ODBC drivers) and run queries with the odbc_query or copy data from DuckDB with the odbc_copy functions.

Installing and Loading

On Linux and macOS the extension requires unixODBC driver manager to be installed. See below for installation instructions.

The extension can be installed automatically, but needs to be loaded manually with:

LOAD odbc;

Usage Example

-- load extension
LOAD odbc;

-- open ODBC connection to a remote DB
SET VARIABLE conn = odbc_connect('Driver={Oracle driver};DBQ=//127.0.0.1:1521/XE', 'scott', 'tiger');

-- simple query
FROM odbc_query(getvariable('conn'), 'SELECT SYSTIMESTAMP FROM DUAL');

-- query with parameters
FROM odbc_query(getvariable('conn') 
    'SELECT CAST(? AS NVARCHAR2(2)) || CAST(? AS VARCHAR2(5)) FROM DUAL',
    params=row('🦆', 'quack'));

-- copy data into remote DB
FROM odbc_copy(getvariable('conn'),
    source_file='https://blobs.duckdb.org/nl_stations.csv',
    dest_table='NL_TRAIN_STATIONS',
    create_table=TRUE);

-- close connection
SELECT odbc_close(getvariable('conn'));

Installing the Nightly Version

ODBC extension is built using the version-independent DuckDB C API. The same binary (for the specific platform, for example: windows_amd64) can be installed and loaded on DuckDB version 1.2.0 or any newer version.

Binaries with the most recent changes, that are published to the DuckDB nightly repository, can be installed the following way:

INSTALL 'http://nightly-extensions.duckdb.org/v1.2.0/platform/odbc_scanner.duckdb_extension.gz';

The URL with the version 1.2.0 in it should be used even if you are running later version of DuckDB.

Where the platform is one of:

• linux_amd64
• linux_arm64
• linux_amd64_musl
• linux_arm64_musl
• osx_amd64
• osx_arm64
• windows_amd64
• windows_arm64

To update installed extension to the latest nightly version run:

FORCE INSTALL 'http://nightly-extensions.duckdb.org/v1.2.0/platform/odbc_scanner.duckdb_extension.gz';

Installed version (commit ID) can be checked using the following query:

FROM duckdb_extensions()
WHERE extension_name = 'odbc_scanner';

To install a version built from a specific commit run:

FORCE INSTALL 'http://nightly-extensions.duckdb.org/odbc_scanner/7_character_commit_id/v1.2.0/platform/odbc_scanner.duckdb_extension.gz';

Support Status of DBMS-Specific Types

Tier 1:

• Oracle: types coverage status
• SQL Server: types coverage status
• DB2: types coverage status

Tier 2:

• PostgreSQL: basic types covered
• MySQL/MariaDB: basic types covered
• Firebird: types coverage status

Tier 3:

• Snowflake: types coverage status
• ClickHouse: basic types covered
• Spark: basic types covered
• Arrow Flight SQL: basic types covered

Installing unixODBC Driver Manager on Linux or macOS

On Linux unixODBC can be installed using the system package manager. Depending on the Linux distribution one of the following installation commands can be used.

Debian, Ubuntu:

sudo apt-get install unixodbc

RHEL, Alma, Rocky, Amazon, Fedora:

sudo dnf install unixODBC

Alpine:

sudo apk add unixodbc

On macOS unixODBC can be installed using the Homebrew package manager:

brew install unixodbc

To use legacy x86_64 ODBC drivers under the Rosetta translator, the unixODBC must be installed using the x86_64 version of Homebrew:

arch -x86_64 /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"
/usr/local/bin/brew install unixodbc

Connection String Examples

ODBC connection can be established using a data source name in a form DSN=data_source1_name or without a configured data source in a form Driver={Driver name};parameter1=values1;....

odbc_list_drivers and odbc_list_data_sources functions can be used to find out available drivers and data sources.

Example of connection strings without a configured data source:

Oracle:

Driver={Oracle in instantclient_23_0};DBQ=//127.0.0.1:1521/XE;UID=scott;PWD=tiger;

SQL Server:

Driver={ODBC Driver 18 for SQL Server};Server=tcp:127.0.0.1,1433;UID=sa;PWD=pwd;TrustServerCertificate=Yes;Database=test_db;

DB2:

Driver={IBM DB2 ODBC DRIVER};HostName=127.0.0.1;Port=50000;Database=testdb;UID=db2inst1;PWD=pwd;

PostgreSQL:

Driver={PostgreSQL Unicode};Server=127.0.0.1;Port=5432;Username=postgres;Password=postgres;Database=test_db;

MySQL/MariaDB:

Driver={MariaDB ODBC 3.1 Driver};SERVER=127.0.0.1;PORT=3306;USER=root;PASSWORD=root;DATABASE=test_db;

Firebird:

Driver={Firebird ODBC Driver};Database=127.0.0.1/3050:C:/path/to/test.fdb;UID=SYSDBA;PWD=pwd;CHARSET=UTF8;

Snowflake:

Driver={SnowflakeDSIIDriver};Server=foobar-ab12345.snowflakecomputing.com;Database=SNOWFLAKE_SAMPLE_DATA;UID=username;PWD=pwd;

ClickHouse:

Driver={ClickHouse ODBC Driver (Unicode)};Server=127.0.0.1;Port=8123;

Spark:

Driver={Simba Spark ODBC Driver};Host=127.0.0.1;Port=10000;

Arrow Flight SQL (Dremio ODBC + GizmoSQL):

Driver={Dremio Flight SQL ODBC Driver};Host=127.0.0.1;Port=31337;UID=gizmosql_username;PWD=gizmosql_password;useEncryption=true;

Query Parameters

When a DuckDB query is run using prepared statement, it is possible to pass input parameters from the client code. The extension allows to forward such input parameters over ODBC API to the queries to remote databases.

2 methods of passing query parameters are supported, using either params or params_handle named argument to odbc_query function.

params argument takes a STRUCT value as an input. Struct field names are ignored, so the row() function can be used to create a STRUCT value inline:

FROM odbc_query(
  getvariable('conn'),
  '
    SELECT CAST(? AS VARCHAR2(3)) || CAST(? AS VARCHAR2(3)) FROM DUAL
  ', 
  params=row(?, ?))

If we prepare this query with duckdb_prepare(), bind foo and bar VARCHAR values to it with duckdb_bind_value() and execute it with duckdb_execute_prepared() - the input parameters foo and bar will be forwarded to the ODBC query in the remote DB.

The problem with this approach, is that DuckDB is unable to resolve parameter types (specified in the outer query) before duckdb_execute_prepared() is called - such types may be different in subsequent invocations of duckdb_execute_prepared() and there is no way to specify these types explicitly.

This will result in re-preparing the inner query in remote DB every time duckdb_execute_prepared() is called.

To avoid this problem is it possible to use 2-step parameter binding with params_handle named argument to odbc_query:

-- create parameters handle
SET VARIABLE params = odbc_create_params();

-- when 'duckdb_prepare()' is called, the inner query will be prepared in the remote DB
FROM odbc_query(
  getvariable('conn'),
  '
    SELECT CAST(? AS VARCHAR2(3)) || CAST(? AS VARCHAR2(3)) FROM DUAL
  ', 
  params_handle=getvariable('params'));

-- now we can repeatedly bind new parameters to the handle using 'odbc_bind_params()'
-- and call 'duckdb_execute_prepared()' to run the prepared query with
-- these new parameters in remote DB
SELECT odbc_bind_params(getvariable('conn'), getvariable('params'), row(?, ?));

Parameter handle is tied to the prepared statement and will be freed when the statement is destroyed.

Connections and Concurrency

DuckDB uses a multi-threaded execution engine to run parts of the query in parallel. ODBC drivers may or may not support using the same connection from different threads concurrently. To prevent possible concurrency problems the extension does not allow to use the same connection from multiple threads. For example, the following query:

FROM odbc_query(getvariable('conn'), 'SELECT ''foo'' col1 FROM DUAL')
UNION ALL
FROM odbc_query(getvariable('conn'), 'SELECT ''bar'' col1 FROM DUAL')

will fail with:

Invalid Input Error:
'odbc_query' error: ODBC connection not found on global init, id: 139760181976192

This can be avoided by using multiple ODBC connections:

FROM odbc_query(getvariable('conn1'), 'SELECT ''foo'' col1 FROM DUAL')
UNION ALL
FROM odbc_query(getvariable('conn2'), 'SELECT ''bar'' col1 FROM DUAL')

Or by disabling multi-threaded execution setting threads DuckDB option to 1.

Transaction Management

According to ODBC specification, connections to remote DB are expected to have auto-commit mode enabled by default.

As a general rule, transaction commands BEGIN TRANSACTION/COMMIT/ROLLBACK are not supposed to be sent over ODBC as SQL commands. Doing so may or may not be supported by the particular driver. Instead ODBC provides the API to manage transactions.

This API is exposed in the following functions:

• odbc_begin_transaction
• odbc_commit
• odbc_rollback

When odbc_begin_transaction is called on the connection, the auto-commit mode on this connection is disabled and an implicit transaction is started. There is currently no support for enabling auto-commit back on such connection.

After the transaction is started, call odbc_commit or odbc_rollback to complete this transaction. After the completion is performed, new implicit transaction is started on this connection automatically.

Performance

ODBC is not a high-performance API, odbc_query uses multiple API calls per-row and performs UCS-2 to UTF-8 conversion for every VARCHAR value. Besides that, query processing is strictly single-threaded.

When submitting issues related only to performance please check the performance in comparable scenarios, for example with pyodbc.