Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Connect ODBC Databases to DuckDB

github.com

Post reply on HN
Print view View original

1–9 of 9 posts

Re: Connect ODBC Databases to DuckDB

#2

Post by rupurt »

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)

Re: Connect ODBC Databases to DuckDB

#3

Post by kermatt »

rupurt wrote: post #2

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…

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

Re: Connect ODBC Databases to DuckDB

#4

Post by rupurt »

kermatt wrote: post #3
rupurt wrote: post #2

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…

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

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!

Re: Connect ODBC Databases to DuckDB

#5

Post by zX41ZdbW »

rupurt wrote: post #2

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…

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.

Re: Connect ODBC Databases to DuckDB

#6

Post by rupurt »

kermatt wrote: post #3
rupurt wrote: post #2

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…

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

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

Re: Connect ODBC Databases to DuckDB

#7

Post by chrisjc »

rupurt wrote: post #2

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…

Can you do ADBC next? ;)

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

Re: Connect ODBC Databases to DuckDB

#8

Post by rupurt »

chrisjc wrote: post #7
rupurt wrote: post #2

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…

Can you do ADBC next? ;) https://arrow.apache.org/docs/format/ADBC.html

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