Skip to content

Sources

ODBC

Load tables and query results from any database with an ODBC driver: connections, choosing tables, query tables, value sizes, memory and types.

An odbc source loads tables, views and query results from any database that has an ODBC driver. SQLLoad reads the rows as Arrow through the driver and loads them into the source’s target.

The sqlload and sqlload-cli wheels include ODBC on every platform. In a build from source (cargo install, or pip building an sdist where no wheel fits), ODBC is built only with the odbc feature (cargo install sqlload --features odbc); without it, plan refuses an odbc source.

[connections.erp]
kind = "odbc"
connection_string = "${ENV:ERP_ODBC}"
[sources.erp]
kind = "odbc"
connection = "erp"
description = "Tables of the ERP database."
include = ["sales.*"]
exclude = ["sales.tmp_*"]
write_strategy = "replace"
[sources.erp.tables."sales.items"]
description = "Items, one row per item."
cursor_column = "updated_at"
unique_key = ["id"]
write_strategy = "merge"
[sources.erp.tables.recent]
description = "Items added recently."
sql = "SELECT id, name FROM sales.items WHERE id > 2400"
$ sqlload plan -v
Plan ready 2 selected, 2 objects to create, 2 to load
to DuckDB local
Objects (2) sqlload apply
├── raw.erp__items create table
│ ├── id int -> BIGINT
│ ├── name text -> VARCHAR
│ ├── price decimal(10,2) -> DECIMAL(10,2)
│ ├── created date -> DATE
│ ├── updated_at timestamp_ntz -> TIMESTAMP
│ └── + _sqlload_load_id, _loaded_at
└── raw.erp__recent create table
├── id int -> BIGINT
├── name text -> VARCHAR
└── + _sqlload_load_id, _loaded_at
Loads (2) sqlload load
├── raw.erp__items merge (unique_key: id)
│ ├── source odbc erp sales.items
│ ├── route read as Arrow -> DuckDB takes Arrow
│ ├── reads updated_at: first run, everything
│ └── commits once, at the end of the read (updated_at isn't read in order)
└── raw.erp__recent replace
├── source odbc erp query recent
└── route read as Arrow -> DuckDB takes Arrow
✓ Plan complete 2 selected, 2 objects to create, 2 to load
Key Meaning
connection_string The driver’s connection string, such as Driver={PostgreSQL Unicode};Server=db.example.com;Database=erp;Uid=loader;Pwd=.... Use ${ENV:NAME}, since it usually holds a password.
dsn A data source name from odbc.ini, instead of connection_string. It and user can’t contain ;, { or }.
user, password With dsn, when the DSN doesn’t hold them.

Set connection_string or dsn, not both. Install the database’s ODBC driver on the machine SQLLoad runs on; SQLLoad reaches it through a driver manager:

  • Windows: the driver manager built into Windows; register drivers and DSNs as usual (ODBC Data Sources).
  • Linux and macOS wheels: the unixODBC 2.3.14 driver manager, bundled as a separate shared library, so no system unixODBC is needed. It reads drivers and system DSNs from odbcinst.ini and odbc.ini in /etc on Linux, where distribution driver packages register, and in Homebrew’s etc on macOS (/opt/homebrew/etc on Apple silicon, /usr/local/etc on Intel), or in the folder ODBCSYSINI names; user DSNs from ~/.odbc.ini. A driver registered by file name alone, as Debian and Ubuntu packages register theirs (Driver=psqlodbcw.so), is looked for in /usr/lib/<arch>-linux-gnu/odbc and /usr/lib/<arch>-linux-gnu (then /usr/lib64/odbc and /usr/lib64) on Linux, and Homebrew’s lib on macOS. unixODBC is LGPL-2.1: its notice and licence are in each wheel’s .dist-info/licenses, and its source is in the package’s sdist on PyPI.
  • cargo install --features odbc: the system’s unixODBC, which it links against: unixodbc-dev on Debian and Ubuntu, unixodbc with Homebrew.

SQLLoad writes names in ANSI double quotes ("sales"."items"). A database that doesn’t read them as names is refused when the source is read; switch it to ANSI quoting. For MySQL, add INITSTMT=SET SESSION sql_mode=CONCAT(@@SESSION.sql_mode,',ANSI_QUOTES'); to the connection string.

Driver Tested
psqlODBC (PostgreSQL Unicode) Yes, against Postgres 17 in containers on Linux and on Windows. Add BoolsAsChar=0 to the connection string so boolean columns load as bool rather than text.
Microsoft ODBC Driver 18 for SQL Server Yes, against SQL Server 2022 in containers on Linux and against SQL Server LocalDB on Windows, datetime2 and legacy datetime cursors included.
MySQL Connector/ODBC (MySQL ODBC Unicode Driver) Yes, against MySQL 8.4 in an ANSI_QUOTES session (above): the driver tests run the sqlload executable on Ubuntu 24.04 with MySQL’s Ubuntu 24.04 package of Connector/ODBC 26.10, and on Windows with MySQL’s Windows installer. Each release’s Linux x86_64 wheels also load a MySQL 8.4 table on Debian 13 through MySQL’s Debian 13 package of Connector/ODBC 9.7, the sqlload-cli wheel as is and the sqlload wheel’s sqlload with nothing set; see MySQL Connector/ODBC on Linux. On Linux, install MySQL’s package for your distribution rather than the generic glibc tarball: the tarball’s own OpenSSL fails to load once another driver, such as SQL Server’s or psqlODBC, has loaded the system one in the same run.
MariaDB Connector/ODBC 3.1 Against MySQL 8.4 by hand, not in the driver tests: it reports no primary keys there, so name each merge table’s unique_key columns instead of "{primary_key}"; see MariaDB Connector/ODBC 3.1.
OpenEdge (Progress) Tested for timestamp handling (parsing {ts} values, binding timestamp bounds and writing them as {ts} escapes), not against a live server. Run sqlload plan first: it checks that the driver takes the cursor’s bounds, and names cursor_bounds = "literals" when the driver only takes escapes; see Cursor bounds.
Others Not yet. A driver needs to describe prepared statements and list tables; run sqlload plan to see the types it reports.

MySQL’s Debian and Ubuntu packages of Connector/ODBC 9.7 and 26.10 are built for static thread-local storage that reaches into libstdc++’s (the driver’s dynamic section sets STATIC_TLS; readelf -d shows it). The dynamic loader can’t give it that once a process that loaded libstdc++ after it started has used libstdc++’s thread-locals, as a Python process with a C++ extension, SQLLoad’s included, has. unixODBC then reports the driver as missing, even though it exists:

[unixODBC][Driver Manager]Can't open lib '/usr/lib/x86_64-linux-gnu/odbc/libmyodbc9w.so' : file not found

On Linux, the sqlload command of the sqlload package starts itself again once with libstdc++ in LD_PRELOAD, so the driver loads with nothing set. Processes it starts, such as a Python source’s subprocesses, inherit that LD_PRELOAD. When libstdc++ isn’t installed, it runs as it is. SQLLOAD_NO_PRELOAD=1 turns the preload off. The sqlload-cli package’s sqlload loads the driver as is.

A Python program that imports SQLLoad and loads in its own process needs libstdc++ preloaded from its start, as does any ODBC program in such a process, pyodbc included:

LD_PRELOAD=libstdc++.so.6 python load.py

SQLLoad adds what to check under unixODBC’s message: that the driver library doesn’t exist, that it is built for static thread-local storage and needs the preload above, or else that a library it needs doesn’t load (ldd on the driver lists them).

MySQL’s Debian 13 package of Connector/ODBC 9.7 also registers its drivers under /usr/lib//odbc/, where they aren’t, so every driver manager, isql included, reports file not found. SQLLoad’s message names the installed file and the fix. Point the registration at the installed files in /etc/odbcinst.ini:

sudo sed -i 's#/usr/lib//odbc/#/usr/lib/x86_64-linux-gnu/odbc/#' /etc/odbcinst.ini

A timestamp column is read in at least microseconds whatever precision the driver describes: MySQL Connector/ODBC describes every DATETIME(n) with none.

MariaDB Connector/ODBC 3.1, the odbc-mariadb package of Debian 13 (3.1.15) among others, reports no primary key for MySQL 8.4 tables: its SQLPrimaryKeys looks for COLUMN_KEY = 'pri' in INFORMATION_SCHEMA.COLUMNS, and MySQL 8.4 compares that column case-sensitively with its value PRI (checked with 3.1.15 against MySQL 8.4.13).

SQLLoad never takes a key the driver reports unless the table asks for it with unique_key = "{primary_key}". A table that does, through this driver, fails plan, and when the database’s INFORMATION_SCHEMA lists a key the driver didn’t report, the error names it:

$ sqlload plan
…
Errors (1)
└── raw.erp__items no key to merge on
├── reason items (kind = "odbc"): unique_key = "{primary_key}", but the source reports no
│ primary key for the table. The ODBC driver reported no primary key, but the
│ database's INFORMATION_SCHEMA lists PRIMARY KEY (id), so the driver's key lookup is
│ wrong (MariaDB Connector/ODBC 3.1 finds none in MySQL 8.4)
└── fix name the key columns, such as unique_key = ["id"]
✗ Plan failed 1 error

Name the key columns on each such table, or use MySQL Connector/ODBC, which reports MySQL’s keys.

A cursor filter compares the cursor column with bound parameters, such as "updated_at" > ?, each passed as the cursor’s own type: a timestamp without a time zone as an ODBC TIMESTAMP; a date as a DATE; text as text. No database’s literal rules can change a bound, and a millisecond maximum such as 23:59:59.997 resumes exactly. A cursor column pinned to a type (columns = { ... }) compares with a quoted literal, as the pinned type may not be the column’s own, such as a varchar read as a timestamp. A numeric cursor compares with a bare number, such as "id" > 41. Any other cursor, a timestamp with a time zone for one, still compares with a quoted literal, which the database converts to the column’s type; check that yours does before relying on it. For a timestamp or date cursor, --start-cursor, --end-cursor and cursor_start also take ODBC escape values, such as {ts '2026-01-31 23:59:59.997'} or {d '2026-01-31'}.

A timestamp bound has the fractional digits the driver describes for its column: three for milliseconds (1 to 3 digits), six for microseconds (4 to 6). A bound with more digits than that, such as a --start-cursor of 10:00:00.1235 on a millisecond column, or a maximum from a driver that describes fewer digits than its values hold, keeps every digit and is never rounded, so it selects exactly the rows the value names. When the driver describes the column with no digits, as MySQL Connector/ODBC describes every DATETIME(n), or with seven or more, the bound keeps every digit. SQL Server keeps its own handling: a timestamp binds with seven digits, compared as datetime2(7), except against a legacy datetime, which stores 1/300 s ticks that its driver reads rounded to milliseconds, so its bounds go as ISO 8601 text (2026-01-31T23:59:59.997) in the column’s own precision, which SQL Server converts to the column’s type alike under every login language.

Some drivers refuse a bound value; OpenEdge’s, by one report, takes no timestamp parameter there. cursor_bounds = "literals" on an ODBC source, or on one of its table entries, writes the bounds into the query as ODBC escapes instead (the default is "parameters"):

[sources.erp]
kind = "odbc"
connection = "erp"
cursor_bounds = "literals"
Cursor Bound
Timestamp without a time zone {ts '2026-01-31 23:59:59.997'}, with the column’s fractional digits when the bound has no more (as above), else the fewest of 0, 3, 6 or 9 that keep every digit
Date {d '2026-01-31'}
Number 41, as with parameters
Text 'o''brien', in single quotes with each quote doubled

It covers every bound: after the destination’s maximum, --start-cursor, --end-cursor, cursor_start, lookback and each batch_size window. Keep the default on SQL Server: its {ts} escape is a datetime, which rounds milliseconds to 1/300 s. A text bound holding a backslash is refused under literals, as a database that treats a backslash in a string as an escape, MySQL by default, would read it as another value; bind it as a parameter instead.

plan (and load, which plans first) checks each ODBC table whose text, date or timestamp cursor binds: it runs the table’s query once with bounds that select no rows, "updated_at" > v AND "updated_at" < v AND 1 = 0, with v the planned lower bound (else the upper bound, else a fixed value of the cursor’s type), and fetches nothing. When the driver refuses the bounds but takes them written the other way, and refuses them again its own way, the table fails and the error names the setting:

Errors (1)
└── items
└── reason the ODBC driver refuses the cursor updated_at's bounds as parameters ([HY000] …) but takes them as ODBC escapes; set cursor_bounds = "literals" on source erp or its table entry

When both ways fail, the error shows both of the driver’s errors.

include and exclude are schema.table patterns, matched without regard to case (* matches any text). SQLLoad loads the tables and views the driver reports that match. For a database without schemas, the patterns match the table name alone. A [sources.erp.tables."schema.table"] entry sets options for one table; on its own, it doesn’t add the table.

A source needs at least one include pattern or one query table. Every table needs a write_strategy, from its entry or the source, and a merge table a unique_key: its columns, or "{primary_key}" for the primary key the driver reports (SQLPrimaryKeys); see Write strategies. A query table reports no key, so name its columns.

SQLLoad asks the driver only for the tables include names, with one SQLTables lookup per pattern, rather than for every table and view in the database. On a large catalog over a slow network, that listing can take most of plan’s time, and every load plans first.

include Schema and table the driver is asked for
PUB.action PUB, action
PUB.* PUB, %
sales.tmp_? sales, tmp__
events (a database without schemas) any schema, events

* becomes % and ? becomes _. A _ or % in a name stays a wildcard to the driver, so a lookup may return a few more tables. SQLLoad drops the tables the pattern doesn’t match, and applies exclude itself.

SQLLoad lists every table instead, once per source, when:

  • a pattern holds [, { or \, more than one ., or a * or ? without a .;
  • a lookup finds no table its pattern matches. include ignores case, but many drivers compare names case-sensitively: psqlODBC, and MySQL Connector/ODBC and MariaDB Connector/ODBC against MySQL on Linux. There ERP.Items finds nothing for erp.items. A table that doesn’t exist finds nothing too.

The tables plan finds, and its errors, are the same either way: a missing table still fails plan. Write the names in the catalog’s case to skip the full listing. Microsoft ODBC Driver 18 against SQL Server’s default collation compares names without regard to case, so either case works there. With RUST_LOG=info, SQLLoad logs each full listing and why:

… INFO sqlload::odbc_source::_helpers::catalog: listing every table the ODBC driver reports, as include can't be looked up pattern by pattern reason="looking up \"ERP.Items\" found no table it matches (the driver may compare names case-sensitively)"

Where the driver compares names case-sensitively, a pattern that its lookup finds a table for doesn’t also find a table whose name differs from it only in case, such as erp.Items beside erp.items. Name each such table in include.

SQLLoad reads a table’s primary key (SQLPrimaryKeys) only when the table uses it:

  • a merge table with unique_key = "{primary_key}", which merges on it;
  • a merge table that names its unique_key columns, for which plan says whether the source reports the same primary key;
  • a table without a write_strategy, whose error suggests one by its key;
  • every table that loads into ClickHouse, which can sort a created table by the key.

Other tables, such as replace, append and delete_insert tables, skip it. When the driver reports no primary key for a table set to "{primary_key}", SQLLoad also asks the database’s INFORMATION_SCHEMA for the table’s primary key and unique constraints, so that the error can name them; a database without INFORMATION_SCHEMA gets the error without them. SQLLoad asks it only for such a table.

A table entry with sql is a query table: its rows are the query’s result, and the entry’s key is its name. SQLLoad prepares the query at plan to read its columns, without running it.

cursor_column names a column of the query’s result. SQLLoad wraps the query and filters on the cursor outside it, so the query needs no placeholder. Write the query in the database’s own SQL.

where narrows a table to the rows that match a condition, written in the database’s own SQL:

[sources.erp]
where = "site = 'BE01'"
[sources.erp.tables."sales.orders"]
where = "status <> 'X' AND site = 'BE01'"

SQLLoad ANDs it into every SELECT it reads the table with, next to the cursor filter: cursor reads, batch_size windows, --reload, replace, and the key scan of load --sync-deletes. The table keeps its primary key, so unique_key = "{primary_key}" still works, and the database can use its indexes. On a source it applies to every table include matches; a table’s own where replaces it. plan checks the condition on the source, counting no rows (AND 1 = 0) and shows it on a filter line, so a typo fails plan, naming the table.

A row that later stops matching isn’t removed from a merge, append or delete_insert table; it stops being updated. replace drops it, and so does deletes with load --sync-deletes (see Rows deleted at the source).

A query table refuses where: put the condition in its sql.

Drivers read values into buffers of a fixed size per column, from the length the driver reports. A text or binary column whose buffer would be over long_column_threshold (4 KiB when omitted), or that the driver gives no length for, such as TEXT, LONGTEXT or VARCHAR(MAX), is a long column. A table with a long column is read value by value: each value at its own size, in pieces for a long one, and never cut. A table without one is read in bulk, many rows per fetch. See Memory.

max_text_size (bytes) caps every text column, and max_binary_size every binary column. They are optional. A capped column counts as long only when the cap is over the threshold. A value longer than its cap, even in a VARCHAR(10000) column capped at 8000 bytes, fails the read, naming the setting to raise; it’s never cut short. In a long column, the read stops as soon as the driver reports the value’s length or the pieces read pass the cap, so a 2 GB value over the cap isn’t read first. Text is compared as UTF-8 bytes, on Windows too, where drivers send it as UTF-16.

The driver’s own notes on the buffer size it chose for each column aren’t logged; set RUST_LOG=info to see them.

Each ODBC table has a fetch buffer: the most the rows of one fetch hold. It’s 512 MiB per table by default. Under a memory budget it’s at most a quarter of each table’s share of the budget, when that is less; the rest of the share holds the batches made from the fetches and the one being written. A source that sets max_batch_memory uses it as it is:

[sources.shop]
kind = "odbc"
connection = "shop"
max_batch_memory = "2GB"

The fetch buffer bounds the source’s fetches only, not the load’s whole memory: the batches made from them, the destination and the commits take memory besides. To measure what a load really takes, read peak_memory_bytes in the JSON report, or run once with --memory-per-table on Linux; see Finding a source that uses too much memory.

plan shows how each ODBC table fetches (fetch) and its fetch buffer, and why (buffer):

$ sqlload plan -v --memory-limit 2GiB
…
memory 2 GiB budget (--memory-limit); 38.5 MiB held before loading; DuckDB 603 MiB (memory_limit 301 MiB, 2 threads); 352 MiB per table, 4 at once
…
Loads (2) sqlload load
├── raw.shop__contacts replace
│ ├── source odbc shop public.contacts
│ ├── route read as Arrow -> DuckDB takes Arrow
│ ├── fetch up to 87.9 MiB or 65,536 rows per fetch; 1 long column (notes), so all 3 columns are read value by value
│ └── buffer 87.9 MiB fetch buffer per table (capped: a quarter of each table's 352 MiB share of the memory budget)
└── raw.shop__orders replace
├── source odbc shop public.orders
├── route read as Arrow -> DuckDB takes Arrow
├── fetch ~14.5 MiB per fetch, 65,536 rows
└── buffer 87.9 MiB fetch buffer per table (capped: a quarter of each table's 352 MiB share of the memory budget)

With a larger budget, the line gives the default and the share it fits in, such as 512 MiB fetch buffer per table (default; each table's share of the memory budget is 2.8 GiB).

  • In bulk (orders): a fetch reads as many rows as its buffers hold within the fetch buffer, at most 65,536. The fetch line shows the buffers of one fetch.

  • Value by value (contacts): the driver returns one row at a time, and SQLLoad reads each of its values. A fetch ends at 65,536 rows, or once the values it has read reach the fetch buffer. A value larger than the whole fetch buffer still loads; its fetch ends after its row, and SQLLoad logs it:

    … WARN sqlload::odbc_source::_helpers::values: a value is larger than the table's fetch buffer (max_batch_memory); it loads, and its fetch ends after its row table="notes" column=body bytes=40000000 budget=16000000

A table with a long column fetches one row at a time because reading values with SQLGetData from a block of rows breaks on common drivers: on MariaDB Connector/ODBC 3.1, every row after the first returns SQL_NO_DATA, and on Microsoft ODBC Driver 18 for SQL Server, SQLSetPos fails on the forward-only cursor. Tables without a long column keep fetching in bulk.

The fetch buffer sets only how many rows a fetch reads. Commits stay as batch_size sets them, so a smaller fetch buffer means more fetches, not different data.

When a fetch may hold more than its quarter of a table’s share, as a max_batch_memory set above it may, plan warns, and load checks again when it starts, since it may run on another machine. Here the source sets max_batch_memory = "1GB":

$ sqlload plan --memory-limit 2GiB --concurrency 2
…
Warnings (1)
└── an ODBC fetch may hold 954 MiB, more than the 176 MiB a fetch gets of each table's 703 MiB share of the memory budget; lower max_batch_memory on source shop, or raise memory_limit
Key Default Meaning
max_batch_memory 512 MiB, or a quarter of each table’s share of the memory budget when less The fetch buffer of each table: the most the rows of one fetch hold, such as "2GB" or "256MiB". It doesn’t bound the load’s whole memory.
long_column_threshold "4KiB" The buffer per value over which a text or binary column is read value by value.

Value by value is slower than bulk: SQLLoad asks the driver for each value with its own call (SQLGetData), as pyodbc reads every value (pyodbc 5.2.0, getdata.cpp). It saves memory when most values are much shorter than their column allows, as in wide tables of short notes. When the values really are long, they take the memory either way. A table with one long column is read value by value in all its columns; raise long_column_threshold above the column’s size to read it in bulk, with buffers of that size.

SQLLoad reads each column’s type as the driver describes it:

ODBC type Loads as
TINYINT, SMALLINT smallint
INTEGER, BIGINT int
NUMERIC(p,s), DECIMAL(p,s), p up to 38 decimal(p,s), read as text so no digit is lost
REAL, FLOAT, DOUBLE float
BIT bool
DATE, TIME date, time
TIMESTAMP timestamp_ntz; a timestamp with a time zone, in the session’s time zone
CHAR, VARCHAR and their wide and long forms text
BINARY, VARBINARY, LONGVARBINARY binary
Anything else, and NUMERIC above 38 digits text, as the driver writes it

A decimal value with more places than its column’s scale fails the read rather than being rounded. psqlODBC describes an unbounded numeric as NUMERIC(28,6), so pin such a column, for example columns = { amount = "decimal(38,10)" } or "text".

Pin a type to load a column another way, such as columns = { total = "decimal(18,2)" }. A value that doesn’t convert to the pinned type fails the read, naming the column. See Types.

The database user needs to read the catalog (to list tables and their keys) and SELECT on the tables and views. INFORMATION_SCHEMA lists only the constraints the user may see, so the key a driver missed is named only where it can: Postgres, for one, lists a table’s constraints only to its owner or a user with a privilege on it other than SELECT.