Connect ODBC Databases to DuckDB

Connect ODBC Databases to DuckDB(github.com)

11 points by rupurt 2 years ago | 8 comments

rupurt 2 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)

zX41ZdbW 2 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.

chrisjc 2 years ago | |

Can you do ADBC next? ;)

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

rupurt 2 years ago | | |

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

kermatt 2 years ago | |

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

rupurt 2 years ago | | |

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

rupurt 2 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!

- 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)