Skip to content

🧲 Querying GizmoSQL with the Columnar ADBC Extension for DuckDB

The ADBC extension is a DuckDB Community Extension developed by Columnar (the team behind the dbc driver installer) that connects DuckDB to any system with an ADBC driver — including GizmoSQL, via the native GizmoSQL ADBC driver.

It complements Query.Farm's ADBC Scanner extension: the Columnar extension is connection-profile-based (reusable TOML profiles, no credentials in your SQL) and additionally supports writes — INSERT, COPY, and CREATE TABLE AS (SELECT ...) against the attached database.

🧩 Overview

  • Extension: adbc
  • Author: Columnar (columnar-tech/duckdb-adbc-client)
  • Category: Community Extension
  • Purpose: Read from and write to remote databases over ADBC, via connection profiles
  • Supported statements: catalog lookups, SELECT, INSERT, COPY, CREATE TABLE AS (SELECT ...)
  • Driver used here: gizmosql (the native GizmoSQL ADBC driver)

⚙️ Setup

1️⃣ Install the GizmoSQL ADBC driver

The GizmoSQL driver isn't available via dbc yet — install it from the gizmosql-adbc release artifacts as shown in the ADBC Scanner guide (see Setup: the GizmoSQL ADBC Driver): copy the shared library into your ADBC driver directory and generate gizmosql.toml from the bundled manifest template. Once registered, the driver loads by name: gizmosql.

Docker users: the -adbc GizmoSQL image variants (e.g. gizmodata/gizmosql:latest-slim-adbc) ship with the driver preinstalled in /etc/adbc/drivers.

2️⃣ Create a connection profile

The Columnar extension connects through ADBC connection profiles — TOML files stored in:

  • Linux: ~/.config/adbc/profiles/
  • macOS: ~/Library/Application Support/ADBC/Profiles/
  • Windows: %LOCALAPPDATA%\ADBC\Profiles\

Create gizmosql_demo.toml in that directory, pointing at the public GizmoSQL instance hosted by GizmoData:

profile_version = 1
driver = "gizmosql"

[Options]
uri = "gizmosql://try-gizmosql-adbc.gizmodata.com:31337"
username = "adbc-scanner"
password = "QueryDotFarmRules!123"

gizmosql:// URIs use TLS by default (append ?transport=tcp for plaintext). For a development server with a self-signed certificate, add:

"adbc.flight.sql.client_option.tls_skip_verify" = "true"

🧪 Example: Query GizmoSQL from DuckDB

1️⃣ Install and load the extension

INSTALL adbc FROM community;
LOAD adbc;

2️⃣ Ad-hoc queries with read_adbc

read_adbc() executes the SQL on the remote GizmoSQL server and streams the results back as Arrow record batches — ideal when you want remote filtering/aggregation (full pushdown, since the query text runs remotely):

SELECT *
  FROM read_adbc('profile://gizmosql_demo',
                 'SELECT * FROM region ORDER BY r_regionkey');

Output:

┌─────────────┬─────────────┬──────────────────────────────────────────────────────────────────────┐
│ r_regionkey │   r_name    │                              r_comment                               │
│    int32    │   varchar   │                               varchar                                │
├─────────────┼─────────────┼──────────────────────────────────────────────────────────────────────┤
│           0 │ AFRICA      │ ar packages. regular excuses among the ironic requests cajole fluf…  │
│           1 │ AMERICA     │ s are. furiously even pinto bea                                      │
│           2 │ ASIA        │ c, special dependencies around                                       │
│           3 │ EUROPE      │ e dolphins are furiously about the carefully                         │
│           4 │ MIDDLE EAST │  foxes boost furiously along the carefully dogged tithes. slyly re…  │
└─────────────┴─────────────┴──────────────────────────────────────────────────────────────────────┘

3️⃣ Attach GizmoSQL as a database

ATTACH 'profile://gizmosql_demo' AS gizmosql_db (TYPE adbc);
USE gizmosql_db.main;

SELECT r_regionkey, r_name FROM region ORDER BY r_regionkey;

Once attached, the remote GizmoSQL catalog behaves like a local DuckDB database — SHOW ALL TABLES, joins against local data, and (with write permissions on the server) INSERT / COPY / CREATE TABLE AS all work.

Note: attached-table scans do not push predicates/projections to the server — use read_adbc() when you want the heavy lifting done remotely.

🔁 GizmoSQL to GizmoSQL

As with the ADBC Scanner, this extension also works inside a GizmoSQL server (GizmoSQL runs DuckDB under the hood) — INSTALL adbc FROM community in a GizmoSQL session and attach another GizmoSQL instance by profile. The -adbc Docker image variants have the driver preinstalled; place profile TOML files where the server's user can read them (~/.config/adbc/profiles/ for the container user, or a directory you mount).