🚀 Querying Remote Databases with the ADBC Scanner Community Extension for DuckDB¶

The ADBC Scanner is a new DuckDB Community Extension developed by Query.Farm that allows you to connect to and query remote databases directly from DuckDB — via the Apache Arrow Database Connectivity (ADBC) framework.

With this extension, you can connect DuckDB to any ADBC-compatible source (like GizmoSQL, Snowflake, PostgreSQL, or SQLite) and run SQL queries remotely, as if they were local tables.

You can even use ADBC Scanner from a GizmoSQL server - allowing you to connect to remote databases from within GizmoSQL itself, including to other GizmoSQL instances!

🧩 Overview¶

Extension: adbc_scanner
Author: Query.Farm
Category: Community Extension
Purpose: Query remote databases over ADBC
Supported Backends: Any database with a compatible ADBC driver
Example Driver Used: flightsql (for Arrow Flight SQL)

⚙️Setup ADBC Drivers¶

You can get ADBC Drivers for your target platform pretty easily using Columnar's dbc tool - under: "Which data system do you want to connect to?" - choose: "Flight SQL":

🧪 Example: Query GizmoSQL from DuckDB¶

You can try the extension right now against a public GizmoSQL instance hosted by GizmoData — no setup required.

1️⃣ Launch DuckDB CLI¶

duckdb

You should see:

DuckDB v1.5.4
Connected to a transient in-memory database.

2️⃣ Install and Load the Extension¶

INSTALL adbc_scanner FROM community;
LOAD adbc_scanner;

-- Run this to keep your extensions up to date...     
UPDATE EXTENSIONS;

This downloads and registers the ADBC Scanner extension for your DuckDB environment.

3️⃣ Connect to a Remote GizmoSQL Instance¶

Create a secret and a connection to the remote GizmoSQL instance:

CREATE SECRET gizmosql_secret (
     TYPE adbc,
     SCOPE 'grpc+tls://try-gizmosql-adbc.gizmodata.com:31337',
     driver 'flightsql',
     uri 'grpc+tls://try-gizmosql-adbc.gizmodata.com:31337',
     username 'adbc-scanner',
     password 'QueryDotFarmRules!123'
 );

ATTACH 'grpc+tls://try-gizmosql-adbc.gizmodata.com:31337' AS gizmosql_db (
      TYPE adbc
  );

This creates an encrypted and authenticated Flight SQL connection to the GizmoSQL service hosted by GizmoData.

Connecting with Self-Signed Certificates (TLS Skip Verify)¶

If your GizmoSQL server uses a self-signed certificate (common in development or internal environments), you can skip TLS certificate verification by adding extra_options to the secret:

CREATE SECRET gizmosql_secret (
     TYPE adbc,
     SCOPE 'grpc+tls://localhost:31337',
     driver 'flightsql',
     uri 'grpc+tls://localhost:31337',
     username 'gizmosql_user',
     password 'gizmosql_password',
     extra_options MAP {
          'adbc.flight.sql.client_option.tls_skip_verify': 'true'
      }
 );

ATTACH 'grpc+tls://localhost:31337' AS gizmosql_db (
      TYPE adbc
  );

Note: Only use tls_skip_verify for development or trusted internal environments — not in production.

4️⃣ Run a Remote Query!¶

Now you can query remote data as if it were local.

-- Make the remote instance first on the search path so you don't have to type: catalog.schema.table for each SQL statement...
USE gizmosql_db;

-- Select from the table as if it were local
SELECT *
FROM region;

Output:

┌─────────────┬─────────────┬─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ r_regionkey │   r_name    │                                                      r_comment                                                      │
│    int32    │   varchar   │                                                       varchar                                                       │
├─────────────┼─────────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│           0 │ AFRICA      │ ar packages. regular excuses among the ironic requests cajole fluffily blithely final requests. furiously express p │
│           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 regular orbits according to the special epit        │
└─────────────┴─────────────┴─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

⚙️ Under the Hood¶

The adbc_scan() function executes SQL remotely via the ADBC driver and returns results as Arrow RecordBatches.
That means:

Results are zero-copy streamed into DuckDB via Arrow IPC.
Queries can push computation to the remote side when supported.
You can join local and remote data seamlessly.

💡 Next Steps¶

Try substituting other remote queries - like this one that does predicate pushdown to the remote GizmoSQL database connection:
```
SELECT *
  FROM lineitem 
 WHERE l_linenumber = 3
 LIMIT 100;
```
Explore pushdown capabilities with complex filters or aggregations.
Combine ADBC remote tables with local Parquet, CSV, or in-memory data for hybrid analytics.
Experiment with other ADBC drivers: PostgreSQL, SQLite, Snowflake, etc.

🧠 Summary¶

The ADBC Scanner extension by Query.Farm opens up a new era of federated analytics in DuckDB —
letting you treat any remote database as a native data source, with Arrow Flight SQL performance and DuckDB simplicity.

“Query remote data at local speed — all from the DuckDB prompt.” 🦆⚡