ODBC Data Connector

ODBC (Open Database Connectivity) is a standard API for connecting applications to various database management systems using a common interface. To connect to any ODBC database for federated/accelerated SQL queries, specify odbc as the selector in the from value for the dataset. The odbc_connection_string parameter is required.

datasets:
  - from: odbc:path.to.my_dataset
    name: my_dataset
    params:
      odbc_connection_string: Driver={Foo Driver};Host=db.foo.net;Param=Value

An ODBC connection requires a compatible ODBC driver and valid driver configuration. ODBC drivers are available from their respective vendors. Here are a few examples:

• PostgreSQL
• MySQL
• Databricks
• AWS Athena

Non-Windows systems additionally require the installation of an ODBC Driver Manager like unixodbc.

• Ubuntu: sudo apt-get install unixodbc
• MacOS: brew install unixodbc

ODBC Connection String

The ODBC connection string requires the use of an installed and registered driver based on your system type:

• Unix systems; ODBC driver installations can be managed using unixODBC, or directly edited through /etc/odbc.ini or /etc/odbcinst.ini. For example, in the Databricks DSN Connection Setup Guide for Linux.
• Windows systems; ODBC driver installations are managed using the ODBC Data Source Administrator.

For an example Unix system with an installed PostgreSQL driver where the contents of /etc/odbcinst.ini is:

[PostgreSQL Unicode]
Description=PostgreSQL ODBC driver (Unicode version)
Driver=psqlodbcw.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

The Spice Runtime can use this driver installation where Driver={PostgreSQL Unicode} is used in the connection string, like:

datasets:
  - from: odbc:my_table
    name: my_dataset
    params:
      odbc_connection_string: Driver={PostgreSQL Unicode};Server=localhost;Port=5432;Database=postgres;Uid=myuser;Pwd=mypass

Configuration

from

The from field takes the form odbc:path.to.my.dataset where path.to.my.dataset is the table name in the ODBC-supporting server to read from.

name

The dataset name. This will be used as the table name within Spice.

Example:

datasets:
  - from: odbc:my.cool.table
    name: cool_dataset
    params: ...

SELECT COUNT(*) FROM cool_dataset;

+----------+
| count(*) |
+----------+
| 6001215  |
+----------+

The dataset name cannot be a reserved keyword.

params

datasets:
  - from: odbc:path.to.my_dataset
    name: my_dataset
    params:
      odbc_connection_string: Driver={Foo Driver};Host=db.foo.net;Param=Value

Selecting SQL Dialect

The default SQL dialect may not be supported by every ODBC connection. The sql_dialect parameter supports overriding the selected SQL dialect for a specified connection.

The runtime will attempt to detect the dialect to use for a connection based on the contents of Driver= in the odbc_connection_string. The runtime will detect the correct SQL dialect for the following connection types, when setup with a standard driver configuration:

• PostgreSQL
• MySQL
• SQLite
• Databricks
• AWS Athena

These connection types are also the supported values for overriding dialect in sql_dialect, in lowercase format: postgresql, mysql, sqlite, databricks, athena. For example, overriding the dialect for your connection to a postgresql style dialect:

datasets:
  - from: odbc:path.to.my_dataset
    name: my_dataset
    params:
      sql_dialect: postgresql
      odbc_connection_string: Driver={Foo Driver};Host=db.foo.net;Param=Value

SQLite Date, Time, and Interval Values

SQLite has no date, time, or interval types. When a connection uses the SQLite dialect, either because the Driver= value contains sqlite or because sql_dialect is sqlite, Spice keeps the following expressions out of the SQL sent to the driver and evaluates them over the rows the driver returns:

• Date, time, timestamp, duration, and interval literals, such as TIMESTAMP '2026-01-30 23:00:00', DATE '2026-01-31', and INTERVAL '1 hour'.
• A CAST into a date, time, timestamp, duration, or interval type.
• TRY_CAST, and each CAST the SQLite accelerator also evaluates in Spice. See Casts and Federation. Unlike the accelerator, ODBC also keeps DATE '1994-01-01' in Spice.

This applies to federated queries and to the filters the table scan pushes down. A filter that involves none of these expressions, such as id >= 2, still pushes down. Setting sql_dialect to a value other than sqlite for a SQLite database applies that dialect's handling instead.

Building Spice with ODBC

ODBC support is built into Spice.ai Enterprise distributions. It is not included in the open source released binaries or in the published spiceai/spiceai Docker images: to use ODBC with the open source build, checkout and compile the code with the --features odbc flag (cargo build --release --features odbc).

To build a container image, pass the feature through to the repository Dockerfile, which also installs the unixodbc Driver Manager when the feature is enabled:

docker build --build-arg CARGO_FEATURES=release,models,odbc -t spiceai-odbc:local .

Baking an image with ODBC Support

There are many dozens of ODBC adapters; this recipe covers making a custom image and configuring it to work with Spice.

The base image must already contain an ODBC-enabled spiced build: the published spiceai/spiceai images do not (see Building Spice with ODBC).

FROM spiceai-odbc:local

RUN apt update \
    && apt install --yes libsqliteodbc --no-install-recommends \
    && rm -rf /var/lib/{apt,dpkg,cache,log}

Build the container:

docker build -t spice-libsqliteodbc .

Validate that the ODBC configuration was updated to reference the newly installed driver:

$ docker run --entrypoint /bin/bash -it spice-libsqliteodbc

root@f8ceccc94d6a:/# odbcinst -j
unixODBC 2.3.11
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

root@f8ceccc94d6a:/# cat /etc/odbcinst.ini
[SQLite]
Description=SQLite ODBC Driver
Driver=libsqliteodbc.so
Setup=libsqliteodbc.so
UsageCount=1

[SQLite3]
Description=SQLite3 ODBC Driver
Driver=libsqlite3odbc.so
Setup=libsqlite3odbc.so
UsageCount=1

test.db

To fully test the image, make an example SQLite database (test.db) and spicepod on your host:

$ sqlite3 test.db
SQLite version 3.43.2 2023-10-10 13:08:14
Enter ".help" for usage hints.
sqlite> create table spice_test (name text not null);
sqlite> insert into spice_test values ("Lala");
sqlite> insert into spice_test values ("Hopper");
sqlite> insert into spice_test values ("Linus");

spicepod.yaml

Make sure that the DRIVER parameter matches the name of the driver section in odbcinst.ini.

version: v1
kind: Spicepod
name: sqlite
datasets:
  - from: odbc:spice_test
    name: spice_test
    mode: read
    acceleration:
      enabled: false
    params:
      odbc_connection_string: DRIVER={SQLite3};SERVER=localhost;DATABASE=test.db;Trusted_connection=yes

All together now:

$ docker run -p8090:8090 -p50051:50051 -v $(pwd)/spicepod.yaml:/spicepod.yaml -v $(pwd)/test.db:/test.db -it spice-libsqliteodbc --http=0.0.0.0:8090 --flight=0.0.0.0:50051
$ spice sql

Welcome to the interactive Spice.ai SQL Query Utility! Type 'help' for help.

show tables; -- list available tables
sql> show tables;
+------------+
| table_name |
+------------+
| spice_test |
+------------+

Query took: 0.059305583 seconds. 1/1 rows displayed.
sql> select * from spice_test;
+--------+
| name   |
+--------+
| Hopper |
| Lala   |
| Linus  |
+--------+

Query took: 1.8504053329999999 seconds. 3/3 rows displayed.

Examples

Connecting to an SQLite database

version: v1
kind: Spicepod
name: sqlite
datasets:
  - from: odbc:spice_test
    name: spice_test
    mode: read
    acceleration:
      enabled: false
    params:
      odbc_connection_string: DRIVER={SQLite3};SERVER=localhost;DATABASE=test.db;Trusted_connection=yes

Connecting to Postgres

Ensure that the Postgres ODBC driver is installed. On Unix systems, this will create an entry in /etc/odbcinst.ini similar to:

[PostgreSQL Unicode]
Description=PostgreSQL ODBC driver (Unicode version)
Driver=psqlodbcw.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

Then, in your spicepod.yaml the odbc_connection_string parameter can be used for the ODBC connection string:

version: v1
kind: Spicepod
name: odbc-demo
datasets:
  - from: odbc:taxi_trips
    name: taxi_trips
    params:
      odbc_connection_string: Driver={PostgreSQL Unicode};Server=localhost;Port=5432;Database=spice_demo;Uid=postgres

For a complete walkthrough that installs an ODBC driver and connects to a MySQL server, see the ODBC Data Connector cookbook recipe.

Secrets

Spice integrates with multiple secret stores to help manage sensitive data securely. For detailed information on supported secret stores, refer to the secret stores documentation. Additionally, learn how to use referenced secrets in component parameters by visiting the using referenced secrets guide.

Cookbook

• A cookbook recipe to configure ODBC as a data connector in Spice. ODBC Data Connector