SQL backends

For SQL sources, omniload reflects the table with SQLAlchemy and then reads its rows through one of dlt’s table-loader backends, selected with --sql-backend:

Backend

Rows arrive as

Connection

Notes

sqlalchemy

Python rows

the SQLAlchemy driver in the source URI

The one scd2 and query: sources use.

pyarrow

Arrow tables built from Python rows

the SQLAlchemy driver

The default for most sources.

connectorx

Arrow tables

ConnectorX, from the source URI

Needs connectorx.

adbcbridge

Arrow record batches straight from the database’s ODBC driver

adbcBridge, an ADBC driver over ODBC

Needs omniload[adbcbridge] and an ODBC driver manager.

Whatever the backend, SQLAlchemy still builds the query, so --incremental-key, --interval-start/--interval-end, --sql-limit and --sql-exclude-columns work the same way for all of them, and the append and merge strategies keep their cursor state as usual.

adbcbridge: ADBC over ODBC

pip install 'omniload[adbcbridge]'

The adbcbridge backend executes the compiled query through the database’s ODBC driver via adbcBridge and yields Arrow record batches without a Python row in between. It needs an ODBC driver manager (unixODBC on Linux and macOS, the built-in one on Windows) and the database’s ODBC driver. The omniload container image already ships unixODBC with the PostgreSQL (psqlodbc) and Microsoft SQL Server (msodbcsql18) drivers, so these two need nothing more:

omniload ingest \
    --source-uri 'postgresql://user:password@localhost:5432/shop' \
    --source-table 'public.orders' \
    --dest-uri 'duckdb:///warehouse.duckdb' \
    --dest-table 'shop.orders' \
    --sql-backend adbcbridge

omniload ingest \
    --source-uri 'mssql://user:password@localhost:1433/shop?driver=ODBC+Driver+18+for+SQL+Server&TrustServerCertificate=yes' \
    --source-table 'dbo.orders' \
    --dest-uri 'duckdb:///warehouse.duckdb' \
    --dest-table 'shop.orders' \
    --sql-backend adbcbridge

The ODBC connection string

The backend derives the ODBC connection string from the source URI:

Source URI

ODBC connection string

postgresql://u:p@host:5432/db?sslmode=require

Driver={PostgreSQL Unicode};Server=host;Port=5432;Database=db;Uid=u;SSLmode=require;Pwd=p;

mssql://u:p@host:1433/db?driver=ODBC+Driver+18+for+SQL+Server&TrustServerCertificate=yes

Driver={ODBC Driver 18 for SQL Server};Server=host,1433;Database=db;Uid=u;Pwd=p;TrustServerCertificate=yes;

crate://crate@host:4200/

Driver={PostgreSQL Unicode};Server=host;Port=5432;Database=doc;Uid=crate;

mysql://u:p@host:3306/db

Driver={MySQL ODBC 9.4 Unicode Driver};Server=host;Port=3306;Database=db;User=u;Password=p;

CrateDB is reached over its PostgreSQL wire protocol, so a crate:// URI that names the HTTP port (4200), or no port, maps to port 5432; any other port is taken as the wire-protocol port. The mssql:// URI’s own driver parameter names the driver, and its other parameters (TrustServerCertificate, Encrypt, …) are passed through.

Two overrides cover everything else:

  • OMNILOAD_ODBC_DRIVER_POSTGRESQL, OMNILOAD_ODBC_DRIVER_MSSQL, OMNILOAD_ODBC_DRIVER_MYSQL, OMNILOAD_ODBC_DRIVER_CRATEDB: the driver’s registered name or the path to its library, when it is not registered under the default name above.

  • --sql-odbc-uri (or OMNILOAD_SQL_ODBC_URI): the whole ODBC connection string, for any database with an ODBC driver, a DSN, or a non-default port. The source URI is still used for reflection and query compilation.

omniload ingest \
    --source-uri 'crate://crate@localhost:4200/' \
    --source-table 'doc.orders' \
    --dest-uri 'duckdb:///warehouse.duckdb' \
    --dest-table 'shop.orders' \
    --sql-backend adbcbridge \
    --sql-odbc-uri 'Driver={PostgreSQL Unicode};Server=localhost;Port=15432;Database=doc;Uid=crate;'

What to expect

  • Column types come from the ODBC driver’s description of the result, at the precision the column declares: a PostgreSQL TIMESTAMP(0) arrives as a second-precision timestamp, TIMESTAMPTZ(3) as milliseconds with a UTC zone.

  • --page-size sets the Arrow batch size the driver hands back.

  • An ODBC driver the driver manager cannot find fails before the first row, with a message naming OMNILOAD_ODBC_DRIVER_<FAMILY> and --sql-odbc-uri; register the driver in odbcinst.ini or point either of those at its library.

  • scd2 is rejected with this backend, as with pyarrow and connectorx: dlt computes the row hash SCD2 needs only for Python rows. A query: source is read with sqlalchemy whatever backend is named.

  • adbcBridge’s compatibility matrix records, per database and ODBC driver, what the route reads and writes correctly.