rupurt / odbc-scanner-duckdb-extension Goto Github PK

View Code? Open in Web Editor NEW

63.0 4.0 3.0 130 KB

A DuckDB extension to read data directly from databases supporting the ODBC interface

License: MIT License

CMake 2.78% Makefile 6.47% Shell 2.12% Python 6.50% C++ 67.13% JavaScript 4.95% Nix 10.06%

db2 duckdb mariadb mssql mysql odbc oracle postgres columnar-database vector-engine

odbc-scanner-duckdb-extension's Introduction

ODBC Scanner DuckDB Extension

A DuckDB extension to read data directly from databases supporting the ODBC interface

odbc_scan

ODBCSYSINI=${PWD} ODBCINSTINI=.odbcinst.ini ODBCINI=.odbc.ini ./build/release/duckdb

D select * from odbc_scan(
    'Driver={db2 odbctest};Hostname=localhost;Database=odbctest;Uid=db2inst1;Pwd=password;Port=50000',
    'DB2INST1',
    'PEOPLE'
);
┌──────────────┬───────┬───────────────┐
│     NAME     │  AGE  │    SALARY     │
│   varchar    │ int32 │ decimal(20,2) │
├──────────────┼───────┼───────────────┤
│ Lebron James │    37 │        100.10 │
│ Spiderman    │    25 │        200.20 │
│ Wonder Woman │    22 │        300.30 │
│ David Bowie  │    69 │        400.40 │
└──────────────┴───────┴───────────────┘

Supported Databases

This extension is tested and known to work with the ODBC drivers of the following databases.

Database	Tests	Linux x86_64	Linux arm64	OS X x86_64	OS X aarch64
IBM Db2	odbc_scan_db2	`[x]`	`[ ]`	`[x]`	`[ ]`
MSSQL	odbc_scan_msql	`[ ]`	`[ ]`	`[ ]`	`[ ]`
Oracle	odbc_scan_oracle	`[ ]`	`[ ]`	`[ ]`	`[ ]`
Postgres	odbc_scan_postgres	`[x]`	`[x]`	`[x]`	`[x]`
MySQL	odbc_scan_mysql	`[ ]`	`[ ]`	`[ ]`	`[ ]`
MariaDB	odbc_scan_mariadb	`[ ]`	`[ ]`	`[ ]`	`[ ]`
Snowflake	odbc_scan_snowflake	`[ ]`	`[ ]`	`[ ]`	`[ ]`
BigQuery	odbc_scan_big_query	`[ ]`	`[ ]`	`[ ]`	`[ ]`
MongoDB	odbc_scan_mongo_db	`[ ]`	`[ ]`	`[ ]`	`[ ]`

If you have tested the extension against other databases let us know by opening an issue or creating a pull request with a set of tests.

Connection String and DSN Formats

For a full list of supported values read the connection string documentation.

ODBC Drivers

Drivers can be downloaded from most vendors. This package manages ODBC drivers through the odbc-drivers-nix flake. If you'd like to have nix download and manage drivers and their dependencies follow the instructions in the README.

Development

This repository manages development dependencies such drivers and shared libraries with nix. It assumes you have it installed.

All development and test tasks should be run within a nix shell

nix develop -c $SHELL

The odbc-scanner-duckdb-extension is built with a clang toolchain. To enable clangd LSP support run the .clangd generator nix application.

nix run .#generate-dot-clangd

To build the extension with the official DuckDB cmake toolchain and clangd run the build nix application which will link to the correct version of unixodbc.

nix run .#build
./build/release/duckdb

To use ODBC DSN's with driver paths managed by the odbc-drivers-nix flake run the generate nix apps.

nix run .#generate-odbc-ini
nix run .#generate-odbcinst-ini

Test

Run the official DuckDB cmake builder with nix to ensure unixodbc is linked correctly

docker compose up
nix run .#test

Installing the deployed binaries

To install your extension binaries from S3, you will need to do two things. Firstly, DuckDB should be launched with the allow_unsigned_extensions option set to true. How to set this will depend on the client you're using. Some examples:

CLI:

duckdb -unsigned

Python:

con = duckdb.connect(':memory:', config={'allow_unsigned_extensions' : 'true'})

NodeJS:

db = new duckdb.Database(':memory:', {"allow_unsigned_extensions": "true"});

Secondly, you will need to set the repository endpoint in DuckDB to the HTTP url of your bucket + version of the extension you want to install. To do this run the following SQL query in DuckDB:

SET custom_extension_repository='bucket.s3.eu-west-1.amazonaws.com/<your_extension_name>/latest';

Note that the /latest path will allow you to install the latest extension version available for your current version of DuckDB. To specify a specific version, you can pass the version instead.

After running these steps, you can install and load your extension using the regular INSTALL/LOAD commands in DuckDB:

INSTALL 'build/release/extension/odbc_scanner/odbc_scanner.duckdb_extension';
LOAD 'build/release/extension/odbc_scanner/odbc_scanner.duckdb_extension';

License

odbc-scanner-duckdb-extension is released under the MIT license

odbc-scanner-duckdb-extension's People

Contributors

Stargazers

Watchers

Forkers

kayrnt coloraven

odbc-scanner-duckdb-extension's Issues

Add Support for Conditional OS X x86 Build When Database Doesn't Have aarch64 ODBC Driver

Write to ODBC Database

Add Predicate Push Down

Add Support for Absolute Cursors

Add Support for Relative Cursors

Add Benchmarks

Datatype mappings

I was wondering if there is a way to enforce schema with data type mapping specially when it comes down to nested types. e.g arrays and jsonarray as duckb list. jsonb and json as struct data type.

Add Nix Flake for IBM Db2 Driver

Discover Catalog on Attach

Assistance with building the extension

Hello,

I am hoping you can assist me with a build issue. I cloned the odbc scanner git repo and I am attempting to build the odbc scanner extension following the instructions, but I have encountered the error below. I also tried cloning the duckdb source into the /home/cwhelan/duckdb/odbc-scanner-duckdb-extension/duckdb folder and then built successfully with the provided nix command, but no odbc scanner extension binary seemed to be created. Any assistance you can provide would be greatly appreciated.

cwhelan@localhost:~/duckdb/odbc-scanner-duckdb-extension$ nix develop -c $SHELL
cwhelan@localhost:~/duckdb/odbc-scanner-duckdb-extension$ nix run .#generate-dot-clangd
cwhelan@localhost:~/duckdb/odbc-scanner-duckdb-extension$ nix run .#build
mkdir -p build/release && \
cmake -G "Ninja" -DFORCE_COLORED_OUTPUT=1 -DDUCKDB_OOT_EXTENSION_NAMES="odbc_scanner" -DDUCKDB_OOT_EXTENSION_ODBC_SCANNER_PATH="/home/cwhelan/duckdb/odbc-scanner-duckdb-extension/" -DDUCKDB_OOT_EXTENSION_ODBC_SCANNER_SHOULD_LINK="TRUE" -DDUCKDB_OOT_EXTENSION_ODBC_SCANNER_INCLUDE_PATH="/home/cwhelan/duckdb/odbc-scanner-duckdb-extension/src/include" -DODBC_CONFIG=/nix/store/lzi1paqcnailv7x94g81s324853ja8pa-unixODBC-2.3.11/bin/odbc_config -DEXTENSION_STATIC_BUILD=1 -DCMAKE_BUILD_TYPE=Release -DEXTENSION_STATIC_BUILD=1 -DBUILD_TPCH_EXTENSION=1 -DBUILD_PARQUET_EXTENSION=1   -S ./duckdb/ -B build/release && \
cmake --build build/release --config Release
CMake Error: The source directory "/home/cwhelan/duckdb/odbc-scanner-duckdb-extension/duckdb" does not appear to contain CMakeLists.txt.
Specify --help for usage, or press the help button on the CMake GUI.
make: *** [Makefile:44: release] Error 1

When I cloned the duckdb repo and put it in the duckdb folder I noticed that the variables being specified to build the odbc scanner extension are apparently not being used.

cwhelan@localhost:~/duckdb/odbc-scanner-duckdb-extension$ nix run .#build
mkdir -p build/release && \
cmake -G "Ninja" -DFORCE_COLORED_OUTPUT=1 -DDUCKDB_OOT_EXTENSION_NAMES="odbc_scanner" -DDUCKDB_OOT_EXTENSION_ODBC_SCANNER_PATH="/home/cwhelan/duckdb/odbc-scanner-duckdb-extension/" -DDUCKDB_OOT_EXTENSION_ODBC_SCANNER_SHOULD_LINK="TRUE" -DDUCKDB_OOT_EXTENSION_ODBC_SCANNER_INCLUDE_PATH="/home/cwhelan/duckdb/odbc-scanner-duckdb-extension/src/include" -DODBC_CONFIG=/nix/store/lzi1paqcnailv7x94g81s324853ja8pa-unixODBC-2.3.11/bin/odbc_config -DEXTENSION_STATIC_BUILD=1 -DCMAKE_BUILD_TYPE=Release -DEXTENSION_STATIC_BUILD=1 -DBUILD_TPCH_EXTENSION=1 -DBUILD_PARQUET_EXTENSION=1   -S ./duckdb/ -B build/release && \
cmake --build build/release --config Release
-- The C compiler identification is Clang 15.0.7
-- The CXX compiler identification is Clang 15.0.7
-- Detecting C compiler ABI info
-- Detecting C compiler ABI info - done
-- Check for working C compiler: /nix/store/7hknjc39fp3ag93g2b8xq01mxr2dbrv5-clang-wrapper-15.0.7/bin/clang - skipped
-- Detecting C compile features
-- Detecting C compile features - done
-- Detecting CXX compiler ABI info
-- Detecting CXX compiler ABI info - done
-- Check for working CXX compiler: /nix/store/7hknjc39fp3ag93g2b8xq01mxr2dbrv5-clang-wrapper-15.0.7/bin/clang++ - skipped
-- Detecting CXX compile features
-- Detecting CXX compile features - done
-- Performing Test CMAKE_HAVE_LIBC_PTHREAD
-- Performing Test CMAKE_HAVE_LIBC_PTHREAD - Success
-- Found Threads: TRUE
-- Found Git: /nix/store/73mc3hjk8c0fa5njc9ynm5ngqmv04ysx-git-2.41.0/bin/git (found version "2.41.0")
-- git hash 6095ea51ea, version v0.9.2-dev235
-- Load extension 'parquet' from '/home/cwhelan/duckdb/odbc-scanner-duckdb-extension/duckdb/extensions'
-- Load extension 'jemalloc' from '/home/cwhelan/duckdb/odbc-scanner-duckdb-extension/duckdb/extensions'
-- Extensions linked into DuckDB: [parquet, jemalloc]
-- Configuring done (12.6s)
-- Generating done (0.6s)
CMake Warning:
  Manually-specified variables were not used by the project:

    BUILD_PARQUET_EXTENSION
    BUILD_TPCH_EXTENSION
    DUCKDB_OOT_EXTENSION_NAMES
    DUCKDB_OOT_EXTENSION_ODBC_SCANNER_INCLUDE_PATH
    DUCKDB_OOT_EXTENSION_ODBC_SCANNER_PATH
    DUCKDB_OOT_EXTENSION_ODBC_SCANNER_SHOULD_LINK
    ODBC_CONFIG


-- Build files have been written to: /home/cwhelan/duckdb/odbc-scanner-duckdb-extension/build/release
[401/401] Linking CXX executable tools/sqlite3_api_wrapper/test_sqlite3_api_wrapper