Use DuckDB (v1.4.5+ or v1.5.4+) to query Snowflake, Databricks, BigQuery, PostgreSQL, MySQL, or any other system with an ADBC driver.
ADBC (Arrow Database Connectivity) is a universal data-access API built on Apache Arrow, an efficient, columnar data format that almost every data system supports natively.
By building on Arrow, ADBC enables:
- Lightning fast (zero-copy) data transfer between column-oriented analytical databases, bypassing the slow column-to-row and row-to-column conversions typical of legacy row-based APIs like ODBC or JDBC.
- Seamless interoperability with a large and growing ecosystem of Arrow-compatible systems.
Then you can install the extension from DuckDB by running:
INSTALL adbc FROM community;
LOAD adbc;Click here for instructions on how to build the extension from source with cmake and ninja.
# Clone the repo and its dependencies
git clone --recurse-submodules git@github.com:columnar-tech/duckdb-adbc-client.git
cd duckdb-adbc-client
# Build the extension from source
GEN=ninja make release
# Run DuckDB with the ADBC extension auto-loaded
./build/release/duckdb dbc is a command-line tool that makes it easy to install and manage ADBC drivers.
Click here for instructions on how to install dbc..
# shell
curl -LsSf https://dbc.columnar.tech/install.sh | sh
# brew
brew install columnar-tech/tap/dbc
# uv
uv tool install dbc
# pipx
pipx install dbc
# powershell
powershell -ExecutionPolicy ByPass -c irm https://dbc.columnar.tech/install.ps1 | iex
# winget
winget install dbcAfter installing dbc, you can run dbc install <system> to install a driver for a new system.
dbc install sqliteTo easily manage connection information for each system, you can create a connection profile for each driver (i.e., mydb.toml):
profile_version = 1
driver = "sqlite"
[Options]
uri = "./games.sqlite"The last step is to save your profile in the correct location:
# Linux
mv mydb.toml ~/.config/adbc/profiles/
# macOS
mv mydb.toml ~/Library/Application Support/ADBC/Profiles/
# Windows
move "mydb.toml" "%LOCALAPPDATA%\ADBC\Profiles\"We showcase the functionality of the ADBC extension using a games database.
You can download the games database as a SQLite file with:
curl -o games.sqlite "https://data.columnar.tech/games.sqlite"To read data through ADBC you can call the read_adbc table function by providing a URI to a connection profile and a SQL query.
-- Install the extension
D INSTALL adbc FROM community;
-- Load it
D LOAD adbc;
-- Read from the ADBC database using read_adbc
D SELECT * FROM read_adbc('profile://mydb', 'SELECT * FROM games');
┌───────┬────────────┬─────────────────────┬─────────┬─────────┬─────────────┬─────────────┬────────────┐
│ id │ name │ inventor │ year │ min_age │ min_players │ max_players │ list_price │
│ int64 │ varchar │ varchar │ varchar │ int64 │ int64 │ int64 │ varchar │
├───────┼────────────┼─────────────────────┼─────────┼─────────┼─────────────┼─────────────┼────────────┤
│ 1 │ Monopoly │ Elizabeth Magie │ 1903 │ 8 │ 2 │ 6 │ 19.99 │
│ 2 │ Scrabble │ Alfred Mosher Butts │ 1938 │ 8 │ 2 │ 4 │ 17.99 │
│ 3 │ Clue │ Anthony E. Pratt │ 1944 │ 8 │ 2 │ 6 │ 9.99 │
│ 4 │ Candy Land │ Eleanor Abbott │ 1948 │ 3 │ 2 │ 4 │ 7.99 │
│ 5 │ Risk │ Albert Lamorisse │ 1957 │ 10 │ 2 │ 5 │ 29.99 │
└───────┴────────────┴─────────────────────┴─────────┴─────────┴─────────────┴─────────────┴────────────┘To create a persistent connection to an ADBC database, you can run the ATTACH command and then query the ADBC database as if it were a local DuckDB database. We currently support catalog lookups, as well as SELECT, INSERT, COPY, and CREATE TABLE AS (SELECT ...) (CTAS) statements.
-- Create a persistent connection to the SQLite database
D ATTACH 'profile://mydb' AS mydb (TYPE adbc);
-- Set the default schema
D USE mydb.main;
-- Display all tables in the attached ADBC database
D SHOW ALL TABLES;
┌──────────┬─────────┬─────────┬───────────────────────────────┬────────────────────────────────┬───────────┐
│ database │ schema │ name │ column_names │ column_types │ temporary │
│ varchar │ varchar │ varchar │ varchar[] │ varchar[] │ boolean │
├──────────┼─────────┼─────────┼───────────────────────────────┼────────────────────────────────┼───────────┤
│ mydb │ main │ games │ [id, name, inventor, year, │ [BIGINT, VARCHAR, VARCHAR, │ false │
│ │ │ │ min_age, min_players, │ VARCHAR, BIGINT, BIGINT, │ │
│ │ │ │ max_players, list_price] │ BIGINT, VARCHAR] │ │
└──────────┴─────────┴─────────┴───────────────────────────────┴────────────────────────────────┴───────────┘
-- Read directly from the attached ADBC table
D SELECT * FROM games;
┌───────┬────────────┬─────────────────────┬─────────┬─────────┬─────────────┬─────────────┬────────────┐
│ id │ name │ inventor │ year │ min_age │ min_players │ max_players │ list_price │
│ int64 │ varchar │ varchar │ varchar │ int64 │ int64 │ int64 │ varchar │
├───────┼────────────┼─────────────────────┼─────────┼─────────┼─────────────┼─────────────┼────────────┤
│ 1 │ Monopoly │ Elizabeth Magie │ 1903 │ 8 │ 2 │ 6 │ 19.99 │
│ 2 │ Scrabble │ Alfred Mosher Butts │ 1938 │ 8 │ 2 │ 4 │ 17.99 │
│ 3 │ Clue │ Anthony E. Pratt │ 1944 │ 8 │ 2 │ 6 │ 9.99 │
│ 4 │ Candy Land │ Eleanor Abbott │ 1948 │ 3 │ 2 │ 4 │ 7.99 │
│ 5 │ Risk │ Albert Lamorisse │ 1957 │ 10 │ 2 │ 5 │ 29.99 │
└───────┴────────────┴─────────────────────┴─────────┴─────────┴─────────────┴─────────────┴────────────┘
-- Insert into the ADBC database
D INSERT INTO games (SELECT 6, 'Battleship', 'Clifford Von Wickler', 1931, 7, 2, 2, 12.99);
D SELECT * FROM games;
┌───────┬────────────┬──────────────────────┬─────────┬─────────┬─────────────┬─────────────┬────────────┐
│ id │ name │ inventor │ year │ min_age │ min_players │ max_players │ list_price │
│ int64 │ varchar │ varchar │ varchar │ int64 │ int64 │ int64 │ varchar │
├───────┼────────────┼──────────────────────┼─────────┼─────────┼─────────────┼─────────────┼────────────┤
│ 1 │ Monopoly │ Elizabeth Magie │ 1903 │ 8 │ 2 │ 6 │ 19.99 │
│ 2 │ Scrabble │ Alfred Mosher Butts │ 1938 │ 8 │ 2 │ 4 │ 17.99 │
│ 3 │ Clue │ Anthony E. Pratt │ 1944 │ 8 │ 2 │ 6 │ 9.99 │
│ 4 │ Candy Land │ Eleanor Abbott │ 1948 │ 3 │ 2 │ 4 │ 7.99 │
│ 5 │ Risk │ Albert Lamorisse │ 1957 │ 10 │ 2 │ 5 │ 29.99 │
│ 6 │ Battleship │ Clifford Von Wickler │ 1931 │ 7 │ 2 │ 2 │ 12.99 │
└───────┴────────────┴──────────────────────┴─────────┴─────────┴─────────────┴─────────────┴────────────┘
-- Create a local table in DuckDB of the inventors of each game
D CREATE TABLE memory.inventors AS (SELECT id, inventor FROM games);
-- Create a new table in the attached ADBC database (SQLite) of the inventors
D CREATE TABLE game_inventors(id, inventor) AS (SELECT * FROM memory.inventors);
D SELECT * FROM game_inventors;
┌───────┬──────────────────────┐
│ id │ inventor │
│ int64 │ varchar │
├───────┼──────────────────────┤
│ 1 │ Elizabeth Magie │
│ 2 │ Alfred Mosher Butts │
│ 3 │ Anthony E. Pratt │
│ 4 │ Eleanor Abbott │
│ 5 │ Albert Lamorisse │
│ 6 │ Clifford Von Wickler │
└───────┴──────────────────────┘By default, ATTACH delimits all SQL queries with double quotes (i.e., SELECT * FROM "schema"."table"). The DELIMITER option adds support for systems with different schema/table delimiters (i.e., [schema].[table] for SQL Server).
D ATTACH 'profile://mydb' AS mydb (TYPE adbc, DELIMITER '[]');To perform arbitrary operations via ADBC, you can call adbc_execute.
D CALL adbc_execute('profile://mydb', 'DROP TABLE games');
┌─────────┐
│ Success │
│ boolean │
├─────────┤
│ true │
└─────────┘DuckDB caches schema and table metadata from ADBC databases locally. To clear the cached metadata (i.e., after a remote update), you can call adbc_clear_cache.
D CALL adbc_clear_cache();
┌─────────┐
│ Success │
│ boolean │
├─────────┤
│ true │
└─────────┘The ADBC extension only supports autocommit mode. In this mode, queries take effect immediately upon execution.
The ADBC extension does not currently perform predicate or projection pushdown for attached ADBC tables.
See Issue #1 and Issue #2 for more details.
To push down projections or predicates, you can directly call read_adbc with a SQL query.
D USE memory;
D CREATE MACRO read_mydb(query) AS TABLE SELECT * FROM read_adbc('profile://mydb', query);
D SELECT inventor FROM read_mydb('SELECT inventor FROM games WHERE name = ''Monopoly''');
┌─────────────────┐
│ inventor │
│ varchar │
├─────────────────┤
│ Elizabeth Magie │
└─────────────────┘The ADBC extension does not currently support connecting to another DuckDB database using the DuckDB or Quack ADBC drivers.
The ADBC extension does not currently support concurrent ADBC operations within a single process.
By default, mixing ADBC reads and writes in the same SQL statement will throw an error to prevent potential concurrency bugs. To override this warning and enable mixing ADBC reads and writes, you can set adbc_mix_reads_writes to true.
D USE mydb.main;
D INSERT INTO games (SELECT * FROM games);
Not implemented Error: ...
D SET adbc_mix_reads_writes = true;
D INSERT INTO games (SELECT * FROM games);
D To materialize all input rows to an INSERT or CTAS and prevent concurrency bugs when mixing ADBC reads and writes, you can set adbc_materialize_insert_rows to true.
D SET adbc_materialize_insert_rows = true;To learn more about the internal design of the ADBC extension for tuning, you can read DESIGN.md.
Internally, the ADBC extension creates connections to perform SQL statements. To avoid repeatedly creating and destroying connections, each attached ADBC database maintains a connection pool. The pool is initially empty and grows as SQL statements create new connections, up to a default limit of 50. Once the pool is full, SQL statements create ephemeral connections, that are destroyed immediately after execution. To adjust the ADBC connection pool limit for each attached database, you can modify the value of adbc_connection_pool_size.
D SET adbc_connection_pool_size = 100;When performing INSERT and CREATE TABLE AS (SELECT ...) statements, the extension uses ADBC's bulk ingest API. To avoid materializing all input rows at once, the ADBC extension inserts batches of rows at a time. Internally, one thread appends rows to an in-memory buffer, and then another thread empties the buffer and inserts via ADBC. Increasing the buffer size may improve performance, but slow down query cancellation. The buffer is full when it exceeds 50% of the available memory or contains adbc_insert_buffer_size chunks of 2048 rows each. The default value of adbc_insert_buffer_size is 1000, resulting in a buffer of 1000 x 2048 rows (i.e., ~2M rows). To adjust the insert buffer size, you can modify the value of adbc_insert_buffer_size.
D SET adbc_insert_buffer_size = 10000;