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 -vPlan ready 2 selected, 2 objects to create, 2 to loadto 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 loadConnection
Section titled “Connection”| 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.iniandodbc.iniin/etcon Linux, where distribution driver packages register, and in Homebrew’setcon macOS (/opt/homebrew/etcon Apple silicon,/usr/local/etcon Intel), or in the folderODBCSYSINInames; 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/odbcand/usr/lib/<arch>-linux-gnu(then/usr/lib64/odbcand/usr/lib64) on Linux, and Homebrew’slibon 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-devon Debian and Ubuntu,unixodbcwith Homebrew.
Drivers
Section titled “Drivers”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 Connector/ODBC on Linux
Section titled “MySQL Connector/ODBC on Linux”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 foundOn 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.pySQLLoad 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.iniA 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
Section titled “MariaDB Connector/ODBC 3.1”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 errorName the key columns on each such table, or use MySQL Connector/ODBC, which reports MySQL’s keys.
Cursor bounds
Section titled “Cursor bounds”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.
Literal bounds
Section titled “Literal bounds”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.
Checked at plan
Section titled “Checked at plan”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 entryWhen both ways fail, the error shows both of the driver’s errors.
Choosing tables
Section titled “Choosing tables”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.
How the driver is asked
Section titled “How the driver is asked”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.
includeignores case, but many drivers compare names case-sensitively: psqlODBC, and MySQL Connector/ODBC and MariaDB Connector/ODBC against MySQL on Linux. ThereERP.Itemsfinds nothing forerp.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.
When keys are read
Section titled “When keys are read”SQLLoad reads a table’s primary key (SQLPrimaryKeys) only when the table uses it:
- a
mergetable withunique_key = "{primary_key}", which merges on it; - a
mergetable that names itsunique_keycolumns, for whichplansays 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.
Query tables
Section titled “Query tables”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.
Filtering rows
Section titled “Filtering rows”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.
Value sizes
Section titled “Value sizes”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.
Memory
Section titled “Memory”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. Thefetchline 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.
Permissions
Section titled “Permissions”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.