Connect ODBC Databases to DuckDB

https://github.com/rupurt/odbc-scanner-duckdb-extension

by rupurt • 3 years ago

11 8 3 years ago

rupurt4• 3 years ago

I've created an ODBC DuckDB extension to query any database that has an ODBC driver. It's modeled after the fantastic official Postgres scanner extension https://github.com/duckdblabs/postgres_scanner.

It supports fetching rowsets in batches to minimize network overhead and defaults to the default DuckDB vector size of 2048.

I've tested it against the IBM DB2 & Postgres ODBC drivers and will continue to test and add support for all major databases. If you've got one you'd like to see let me know in the comments.

I've got plenty of improvements in the pipeline including:

  - Write to ODBC database
  - Predicate push down
  - Automatic catalog discovery
  - Performance benchmarks
  - Configurable batch size
  - Relative & absolute cursors
  - OS X builds using rosetta for databases without native aarch64 drivers (IBM/Oracle)

kermatt• 3 years ago

Dependency on unixODBC? Wondering if this could used on Windows.

rupurt4• 3 years ago

Definitely. I will need to link against the ODBC library Windows ships with. I don't have a Windows machine so wasn't testing against it.

It's now on the list of improvements!

rupurt4• 3 years ago

https://github.com/rupurt/odbc-scanner-duckdb-extension/issu...

zX41ZdbW• 3 years ago

I recommend to model after ClickHouse ODBC integration: https://clickhouse.com/docs/en/sql-reference/table-functions...

It already has the items from your list, like the predicate push-down. Plus, it is memory-safe, thanks to clickhouse-odbc-bridge which runs as a separate process and does not poison the main server process's address space.

PS. I'm one of developers of the ODBC integration.

chrisjc2• 3 years ago

Can you do ADBC next? ;)

https://arrow.apache.org/docs/format/ADBC.html

rupurt4• 3 years ago

If there's demand ;) Would be handy for some of the newer DB's like Snowflake.

Connect ODBC Databases to DuckDB

Related Stories