duckdb-spanner

A DuckDB extension for querying Google Cloud Spanner databases directly from DuckDB.

Experimental: This extension is under active development. The API (function signatures, named parameters, configuration) may change without notice between versions. Do not depend on backward compatibility.

Both GoogleSQL and PostgreSQL dialect databases are supported transparently. Dialect detection is automatic on normal endpoints. When an older or custom endpoint does not support INFORMATION_SCHEMA.DATABASE_OPTIONS, pass dialect := 'googlesql' or dialect := 'postgresql' to spanner_scan or spanner_tables, or dialect 'googlesql' / dialect 'postgresql' to COPY ... FORMAT spanner.

Prerequisites

• Rust (stable)
• DuckDB CLI (for running the extension)
• Python 3 (for make extension metadata appending)
• Make
• cargo-sweep (recommended for cleaning stale build artifacts)

On macOS, prefer the bottled Homebrew binary:

brew install cargo-sweep

Known Issues and Limitations

• The DuckDB Rust binding is pinned to duckdb = "=1.10505.0", which matches DuckDB v1.5.5. The upstream Rust extension template still requires the unstable C API (USE_UNSTABLE_C_API=1 / C_STRUCT_UNSTABLE), so loadable extension binaries remain tied to the DuckDB version encoded in the extension metadata. The extension requests at least the DuckDB v1.5.0 C API at load time because it uses C API functions added in the 1.5 line. The Makefile has one canonical ABI target, DuckDB v1.5.5, for both local and distribution builds. Local metadata generation checks the detected CLI version before compiling and rejects a mismatched DUCKDB_VERSION override; an override cannot relabel a binary built for another DuckDB version.

• This project depends on the official googleapis/google-cloud-rust Spanner crates. Partitioned reads and queries use the official partition APIs; no local client fork is required.

• Extension initialization is intentionally manual rather than using #[duckdb_entrypoint_c_api]. The extension needs a raw duckdb_connection to register session config options and the COPY TO ... FORMAT spanner copy function, and duckdb-rs currently exposes those APIs only through duckdb::ffi. Initialization preflights config, scalar, table-function, and macro names through duckdb_settings() and duckdb_functions(), prepares every native C API builder before mutation, then checks every registration status and reaches Ready only after all required stages complete. Config options are the first mutation and therefore form a preflight-visible retry barrier. COPY follows because DuckDB exposes no stable public COPY-function catalog lookup and permits duplicate COPY names; putting it after config guarantees that a failed attempt cannot leave COPY as the only residue. Loading this extension claims the global COPY format name spanner. DuckDB 1.5.5 silently replaces an existing handler with that name, and the stable C API cannot detect the collision; load order therefore determines ownership. A successful COPY registration status proves that this handler was installed, not that the name was previously unused. Scalars follow before table functions so a native registration failure cannot leave a higher-level SQL surface that appears usable. duckdb-rs does not expose separate prepare or unregister operations for table functions, so a table-function failure can still leave the C registrations and earlier table functions. SQL macros are installed as one rollback-capable transaction after their dependencies, and the replacement scan is last. DuckDB exposes no stable public removal API or shared registration transaction for config, COPY, scalar, table-function, and replacement-scan registration. The macro transaction is the only stage this initializer can roll back. An unexpected later registration failure can therefore leave earlier native registrations in the database catalog. A fresh load attempt still fails deterministically because config registration precedes every later mutation and is found by preflight; function and macro residues are preflighted as additional defense. duckdb_add_replacement_scan returns no status and has no removal API, so initialization cannot verify or roll it back and installs it only after every status-returning stage succeeds. Version 0.4.0 intentionally consolidates the Rust registration API as the breaking register_spanner_extension(db, raw_con, &connection) -> Result<(), RegistrationError>. The former public split-stage helpers are no longer exposed because calling them independently could bypass preflight and leave a misleading partial surface. Both supplied connections must be in autocommit mode; registration independently detects and rejects caller-owned transactions on raw_con and the Rust connection before any mutation.

• DuckDB VARIANT is not used yet. Spanner JSON results currently map to DuckDB VARCHAR with the JSON alias. DuckDB v1.5.5 and duckdb-rs 1.10505.0 expose VARIANT metadata, and upstream DuckDB has C API support for reading VARIANT, but duckdb-rs still documents decoding VARIANT result columns as unsupported and this extension does not yet have a safe writer path for JSON-to-VARIANT. Changing JSON output to VARIANT would also be a visible result-type change.

• DuckDB GEOMETRY is not used yet. DuckDB 1.5 exposes GEOMETRY in the C API, and duckdb-rs 1.10505.0 exposes it as WKB with CRS metadata, but this extension has no current Spanner type mapping that requires geometry output.

• GoogleSQL DATETIME is unsupported until the Spanner data-plane API exposes a lossless type representation. It is never silently mapped to STRING.

• Some DuckDB vector writes still cross unsafe API boundaries: fast UTF-8 string vector writes use raw C API escape hatches because safe duckdb-rs wrappers are missing for the exact operation (src/convert.rs), and fixed-size vector slices use duckdb-rs unsafe APIs (src/ddl.rs, src/convert.rs). Keep the layout checks and local safety comments when touching those paths.

• database_role (fine-grained access control) is not yet supported as a named parameter. The official Rust Spanner client does not expose creator_role for session creation (the underlying BatchCreateSessionsRequest.session_template supports it, but the client hardcodes it to None).

• COPY TO ... FORMAT spanner supports scalar columns, DuckDB LIST/ARRAY source columns mapped to Spanner ARRAY targets, and DuckDB STRUCT source columns mapped to Spanner JSON targets.

• Nested ARRAY results, including ARRAY fields inside ARRAY<STRUCT<...>>, reserve child-vector storage explicitly and can exceed DuckDB's standard vector size up to the 64 MiB flattened-result budget.

• Results are streamed via an internal channel. Memory usage is bounded regardless of result set size.

• parallelism_mode controls the partitioned API (required for Data Boost). auto falls back to a single non-partitioned query/read only when partitioned execution fails before any row is delivered; required propagates every partition error; off skips partition APIs entirely. Partitions are executed concurrently, each with its own session from the pool.

• COPY TO ... FORMAT spanner commits each batch_size chunk independently; a mid-COPY failure leaves earlier batches committed. Use idempotent write modes (e.g. insert_or_update) when retrying.

• Named parameters with empty string values (project := '') are treated as absent (see bind_utils::get_named_string). database_path cannot be combined with project/instance/database named parameters.

Installation

Build from Source

Clone the repository with submodules:

git clone --recurse-submodules https://github.com/apstndb/duckdb-spanner.git
cd duckdb-spanner

Build the extension with metadata (recommended):

make extension

Or build with cargo directly (without extension metadata):

cargo build --features loadable-extension --release

The built extension is spanner.duckdb_extension (via make extension) or target/release/libduckdb_spanner.dylib (macOS) / libduckdb_spanner.so (Linux).

Published Release Assets

Release assets are promoted from one successful main distribution run at the exact annotated release-tag commit. Dispatch the Stage Release Assets workflow at that tag ref, providing the tag and source run ID. The workflow requires its dispatch ref, tag, source run, and empty draft release to agree on that commit, smoke-loads five platform artifacts on matching native DuckDB v1.5.5 runners, packages each verified binary as the sole canonical member of a platform ZIP, generates SHA256SUMS for those archives, and attaches them to the draft. The additional windows_amd64_mingw artifact is best-effort, matching DuckDB's upstream support tier. GitHub's Windows runner and Python DuckDB wheel use the incompatible MSVC platform, so CI validates the MinGW artifact's metadata and PE import table instead. Release staging fails if it gains an unreviewed DLL dependency or a dynamic MinGW runtime dependency. Publish the release only after the staging workflow and draft inspection succeed. Published assets are never rebuilt or replaced by a release event; releases created outside this flow receive no automatic assets.

Each platform archive includes its target platform in the asset name, for example spanner-v1.5.5-linux_amd64.zip. Every archive contains exactly one file named spanner.duckdb_extension. Keep that canonical filename when extracting it: DuckDB derives the extension initialization symbol from the filename. Verify the ZIP against SHA256SUMS, extract it into an empty directory, and load that sole member without renaming it.

Loading the Extension

This is an unsigned extension, so DuckDB must be started with the -unsigned flag:

duckdb -unsigned

Then load the extension:

LOAD 'path/to/spanner.duckdb_extension';

Or set the option programmatically before loading:

SET allow_unsigned_extensions = true;
LOAD 'path/to/spanner.duckdb_extension';

For a quick build-and-launch workflow:

make duckdb

make duckdb requires DUCKDB_BIN to report DuckDB v1.5.5 and loads the extension into that CLI. Use DUCKDB_BIN=/path/to/duckdb make duckdb to select a specific CLI. DUCKDB_VERSION=v1.5.5 is accepted as an explicit metadata value; other versions fail before the extension is built.

Authentication

This extension uses Application Default Credentials (ADC). For local development with end-user credentials, install the Google Cloud SDK and run:

gcloud auth application-default login

Connection targets use an endpoint_mode profile. The default profile uses the official Spanner endpoint with ADC and the SDK's default TLS. The other profiles are:

Mode	Credentials and transport	Endpoint source
default	ADC and default TLS	SDK default; no endpoint
emulator	Anonymous and plaintext by default	endpoint, or SPANNER_EMULATOR_HOST
custom	ADC and HTTPS	Required endpoint
omni	Anonymous and HTTPS by default; explicit http:// is allowed	Required endpoint

With no explicit endpoint_mode, an endpoint or SPANNER_EMULATOR_HOST selects emulator. This preserves endpoint-only compatibility. In emulator mode, an explicit endpoint takes precedence over SPANNER_EMULATOR_HOST.

For emulator access, no authentication is required. Either set the environment variable or pass endpoint directly:

export SPANNER_EMULATOR_HOST=localhost:9010

Use endpoint_mode := 'emulator' when the profile should be explicit. custom requires an HTTPS endpoint and ADC. omni uses anonymous credentials and HTTPS unless its endpoint explicitly starts with http://. Explicit non-emulator modes (default, custom, and omni) reject SPANNER_EMULATOR_HOST; unset it before selecting one. Omni TLS uses the system trust store. Custom CA certificates and mTLS are not currently configurable through the pinned official Rust client builder.

admin_endpoint (or spanner_admin_endpoint) overrides the admin endpoint used by DDL and operation helpers. Emulator mode preserves the data endpoint's scheme and maps the conventional data port 9010 to 9020; custom and Omni modes use the data endpoint for admin requests by default. Custom admin requests use ADC and HTTPS, while Omni admin requests are anonymous and use the data endpoint's scheme. An explicit admin endpoint must use HTTPS for authenticated default and custom profiles. Real Spanner uses its authenticated Database Admin client when no emulator admin endpoint applies.

Getting Started

Configure the Database

Set session-level defaults so you don't need to specify the database on every query:

SET spanner_project = 'myproj';
SET spanner_instance = 'myinst';
SET spanner_database = 'mydb';

Or use a full resource path:

SET spanner_database_path = 'projects/myproj/instances/myinst/databases/mydb';

Run a SQL Query

SELECT * FROM spanner_query('SELECT Id, Name FROM Users WHERE Age > 20');

Read a Table

SELECT * FROM spanner_scan('Users');

Explicit Database Path

You can also pass the database path directly as a named parameter:

SELECT * FROM spanner_query(
    'SELECT Id, Name FROM Users',
    database_path := 'projects/myproj/instances/myinst/databases/mydb'
);

-- Or use individual components (overrides config defaults)
SELECT * FROM spanner_scan(
    'Users',
    project := 'myproj',
    instance := 'myinst',
    database := 'mydb'
);

With the Spanner Emulator

export SPANNER_EMULATOR_HOST=localhost:9010

-- endpoint is inferred from SPANNER_EMULATOR_HOST
SET spanner_database_path = 'projects/test/instances/test/databases/test';
SELECT * FROM spanner_query('SELECT * FROM Users');

-- Or make the emulator profile explicit
SELECT * FROM spanner_query(
    'SELECT * FROM Users',
    database_path := 'projects/test/instances/test/databases/test',
    endpoint_mode := 'emulator',
    endpoint := 'localhost:9010'
);

For a custom authenticated endpoint:

SELECT * FROM spanner_query(
    'SELECT * FROM Users',
    database_path := 'projects/p/instances/i/databases/d',
    endpoint_mode := 'custom',
    endpoint := 'spanner.example.com:443'
);

For Spanner Omni, select anonymous authentication explicitly. DDL and operation helpers use the same endpoint unless admin_endpoint is provided:

SET spanner_database_path = 'projects/p/instances/i/databases/d';
SET spanner_endpoint_mode = 'omni';
SET spanner_endpoint = 'omni.example.com:443';

SELECT * FROM spanner_query('SELECT * FROM Users');

Repository CI does not provision a Spanner Omni deployment. Before production rollout, validate query and DDL operations against the target deployment, including its system trust-store and TLS configuration.

Table Functions

spanner_query

Runs arbitrary Spanner SQL. Implemented as a table macro that wraps spanner_query_raw, accepting a STRUCT for params and automatically converting it to JSON.

SELECT * FROM spanner_query(
    'SELECT * FROM Users WHERE Age > @min_age',
    params := {'min_age': spanner_value(21::BIGINT)},
    exact_staleness_secs := 10,
    parallelism_mode := 'required',
    use_data_boost := true
);

spanner_scan

Reads all rows from a table using the Spanner Read API.

SELECT * FROM spanner_scan('Users', index := 'UsersByName');

Replacement Scan

When session-level database defaults are configured, quoted table names with the spanner: prefix are rewritten to spanner_scan.

SET spanner_database_path = 'projects/p/instances/i/databases/d';
SELECT * FROM "spanner:Users";

Replacement scans intentionally support only the table name. Use spanner_scan(...) directly when you need options such as index, timestamp bounds, priority, or Data Boost.

spanner_tables

Lists Spanner base tables from INFORMATION_SCHEMA.TABLES. Each row contains table_schema, table_name, table_type, and parent_table_name; the parent is NULL for non-interleaved tables and views. The zero-argument form preserves the base-table-only result set. Use table_type := 'VIEW' to list views. It accepts the same database identification parameters and config defaults as spanner_query and spanner_scan.

SELECT * FROM spanner_tables();

-- Filters are applied by Spanner before rows are returned to DuckDB.
SELECT * FROM spanner_tables(
    table_type := 'VIEW',
    schema := 'public',
    table_name := 'active_users'
);

-- Override automatic discovery for an older or custom endpoint.
SELECT * FROM spanner_tables(dialect := 'googlesql');

Foreground Timeout Contract

Active foreground callbacks use finite defaults so DuckDB does not silently wait forever for a stalled Spanner endpoint.

• Query/scan schema planning, spanner_tables discovery, COPY client setup, and COPY target-schema discovery each have a 60-second bound. Client creation also retains its existing 15-second connection bound.
• spanner_query and spanner_scan streams default to a 900-second (15-minute) idle bound per next-batch request. Set spanner_stream_idle_timeout_secs from 1 through 31536000 seconds to override it, or set it to 0 to disable the stream idle timeout. This is an idle policy, not a statement-wide deadline.

An expired foreground wait returns a timeout error and cancels the submitted runtime work; it is never reported as a clean end of stream. Disabling the idle timer does not alter producer errors or an already-latched timeout. DDL keeps its documented operation policy below, and each COPY write batch keeps the existing bounded transaction retry policy because a timeout there can leave a commit outcome unknown.

Config Options

Session-level defaults can be set via SET statements. These are used when the corresponding named parameter is not specified.

Config Option	Type	Description
spanner_project	VARCHAR	Default Google Cloud project ID
spanner_instance	VARCHAR	Default Spanner instance ID
spanner_database	VARCHAR	Default Spanner database ID
spanner_database_path	VARCHAR	Default full database resource path (projects/P/instances/I/databases/D)
spanner_endpoint	VARCHAR	Default gRPC endpoint
spanner_endpoint_mode	VARCHAR	Default connection profile: default, emulator, custom, or omni
spanner_admin_endpoint	VARCHAR	Default admin endpoint for DDL/operations; mode determines credentials and default scheme
spanner_stream_idle_timeout_secs	BIGINT	Per-next-batch query/scan idle timeout in seconds (default 900, 0 disables, maximum 31536000)

Database Resolution

The database is resolved in the following order (first match wins):

1. project/instance/database named args (mixed with config fallback for omitted components) — if any component is specified via named arg, all three must resolve or an error is raised
2. database_path named arg — full resource path
3. spanner_project/spanner_instance/spanner_database config — session-level component defaults
4. spanner_database_path config — session-level full resource path default

Named args override config values for each individual component. For example:

SET spanner_project = 'myproj';
SET spanner_instance = 'myinst';
SET spanner_database = 'default_db';

-- Uses myproj/myinst/default_db
SELECT * FROM spanner_query('SELECT 1');

-- Overrides just the database component
SELECT * FROM spanner_query('SELECT 1', database := 'other_db');

Named Parameters

Named parameters are inspired by BigQuery's EXTERNAL_QUERY and CloudSpannerProperties.

Both functions accept the following named parameters:

Parameter	Type	Default	Description
database_path	VARCHAR	(config)	Full database resource path (projects/P/instances/I/databases/D)
project	VARCHAR	(config)	Google Cloud project ID
instance	VARCHAR	(config)	Spanner instance ID
database	VARCHAR	(config)	Spanner database ID
endpoint	VARCHAR	(config)	Profile endpoint; emulator uses plaintext by default, custom uses HTTPS with ADC, and Omni uses HTTPS by default
endpoint_mode	VARCHAR	(config)	default, emulator, custom, or omni; without it, endpoint/SPANNER_EMULATOR_HOST selects emulator
parallelism_mode	VARCHAR	off	'auto' (pre-row fallback), 'required' (no fallback), or 'off' (single API only)
use_parallelism	BOOLEAN	false	Legacy compatibility option: true maps to 'auto', false maps to 'off'
use_data_boost	BOOLEAN	false	Enable Data Boost; requires parallelism_mode := 'required'
max_parallelism	INTEGER	(default)	Positive partition-count hint and local partition-worker cap
exact_staleness_secs	BIGINT		Read at an exact staleness (seconds ago)
max_staleness_secs	BIGINT		Read with bounded staleness (at most N seconds ago)
read_timestamp	VARCHAR		Read at a specific timestamp (RFC 3339, e.g., '2024-01-15T10:30:00Z')
min_read_timestamp	VARCHAR		Read at a timestamp no earlier than this (RFC 3339)
priority	VARCHAR		Request priority: 'low', 'medium', or 'high'

At most one timestamp bound parameter can be specified. If none is set, Spanner uses a strong read (the default).

When both parallelism_mode and use_parallelism are supplied, parallelism_mode takes precedence. use_data_boost := true requires parallelism_mode := 'required', so Data Boost never falls back to a non-Data-Boost request. max_parallelism must be positive and requires a partitioned mode; it limits both Spanner's partition-count hint and local concurrent partition execution. parallelism_mode := 'off' uses the single query/read API.

spanner_query additionally accepts:

Parameter	Type	Description
params	STRUCT	Query parameters (see Query Parameters)

spanner_scan additionally accepts:

Parameter	Type	Description
index	VARCHAR	Secondary index name to use for the read
dialect	VARCHAR	Database dialect: 'googlesql' or 'postgresql' (auto-detected if omitted)

spanner_tables accepts the same dialect parameter. An explicit value bypasses metadata discovery; invalid values fail rather than falling back to a guess.

spanner_ddl

Executes a DDL statement synchronously and waits for completion.

SELECT * FROM spanner_ddl('CREATE TABLE Users (Id INT64 NOT NULL, Name STRING(MAX)) PRIMARY KEY (Id)');

To batch multiple statements into a single UpdateDatabaseDdl request (as Spanner natively supports), pass a LIST<VARCHAR> with statements := [...]:

SELECT * FROM spanner_ddl(statements := [
    'ALTER TABLE Users ADD COLUMN Email STRING(MAX)',
    'ALTER TABLE Users ADD COLUMN Age INT64'
]);

The statements are sent as one Spanner longrunning operation. Spanner applies the DDL statements in order and returns operation-level success or failure; it does not guarantee transactional rollback of earlier completed statements in a batch.

Returns one row with operation_name (VARCHAR), done (BOOLEAN), and duration_secs (DOUBLE).

spanner_ddl_async

Submits a DDL statement, or a statements := [...] batch like spanner_ddl, and returns immediately without waiting for completion.

SELECT * FROM spanner_ddl_async('CREATE INDEX UsersByName ON Users(Name)');

Returns one row with operation_name (VARCHAR) and done (BOOLEAN).

spanner_operations

Lists DDL operations for a database.

On real Spanner this uses the Database Admin ListDatabaseOperations API with normal authenticated admin clients. On the emulator it falls back to the generic google.longrunning.Operations/ListOperations API because the emulator does not implement ListDatabaseOperations.

SELECT * FROM spanner_operations();

Returns rows with name (VARCHAR), done (BOOLEAN), metadata_type (VARCHAR), error_code (INTEGER), and error_message (VARCHAR). error_code/error_message are NULL unless the operation finished with an error.

An optional filter parameter can be used:

SELECT * FROM spanner_operations(filter := 'done=true');

All DDL functions accept the same database identification parameters as spanner_query and the following endpoint parameters:

Parameter	Type	Default	Description
endpoint	VARCHAR	(config)	Data endpoint; its interpretation follows endpoint_mode
endpoint_mode	VARCHAR	(config)	default, emulator, custom, or omni
admin_endpoint	VARCHAR	(config)	Override the mode-derived admin endpoint for DDL and operation helpers

See Authentication for admin credentials, schemes, and emulator port mapping.

DDL and operation timeout contract

DDL/admin waits use fixed, finite defaults:

• Admin client and connection setup is bounded to 15 seconds. Completed clients retain the eight most recently used endpoint identities. At most four distinct endpoint identities may initialize concurrently; another unique endpoint fails fast at admission, while callers for an endpoint already in flight join its single shared initialization.
• Each admin HTTP/RPC attempt, including response-body reads, is bounded to 30 seconds. Retryable read-only requests and real Spanner RPCs are limited to 10 attempts and 60 seconds total, with bounded exponential backoff and jitter. For real DDL submission, HTTP 408, HTTP 429, every HTTP 5xx response, and no-status timeout/I/O/transport failures are ambiguous; other HTTP 4xx responses fail immediately with their status/body context.
• spanner_ddl has a 30-minute overall deadline covering client setup, submission, and operation polling. This matches the Google Cloud Workflows Spanner updateDdl default. Some schema updates can take hours; use spanner_ddl_async and inspect spanner_operations when a longer server-side operation is expected.
• spanner_ddl_async is bounded only through submission; the returned server-side operation continues independently.
• spanner_operations has a 2-minute whole-list deadline, a 1,000-page limit, and rejects repeated/cyclic page tokens.

DDL submissions carry a unique operation ID and report its deterministic operation name in correlated errors. For real Spanner, an ambiguous final submission error triggers one operation lookup; a found operation is returned, while a failed lookup reports an explicit ambiguous outcome instead of hiding the uncertainty. The emulator can mutate schema before registering operationId, so its PATCH is retried only after a definitive pre-acceptance schema-contention rejection. Emulator timeouts, transport failures, HTTP 429, and every HTTP 5xx response trigger one deterministic operation lookup and are never followed by another PATCH; if the lookup fails, the error identifies the ambiguous operation and preserves both failure details. A polling deadline error names the elapsed bound; reaching it does not cancel an already-submitted Spanner operation.

The pinned duckdb-rs table-function init API does not expose the current query's interruption state. These waits therefore cannot react immediately to duckdb_interrupt; the extension does not use unsafe private DuckDB access, and every wait remains bounded by the limits above.

COPY TO (Write to Spanner)

Write data to Spanner tables using the COPY statement with FORMAT spanner.

COPY my_table TO 'SpannerTable' (FORMAT spanner);

Options:

Option	Type	Default	Description
database_path	VARCHAR	(config)	Full database resource path
project	VARCHAR	(config)	Google Cloud project ID
instance	VARCHAR	(config)	Spanner instance ID
database	VARCHAR	(config)	Spanner database ID
endpoint	VARCHAR	(config)	Profile endpoint; emulator is plaintext by default, custom is HTTPS with ADC, and Omni is HTTPS by default
endpoint_mode	VARCHAR	(config)	default, emulator, custom, or omni
dialect	VARCHAR	(auto-detected)	Database dialect: 'googlesql' or 'postgresql'; bypasses metadata discovery
mode	VARCHAR	insert_or_update	Mutation mode: insert, update, insert_or_update, or replace
batch_size	VARCHAR	1000	Rows per independent commit (1 to 80,000)
columns	VARCHAR[]	(none)	Target Spanner column names in source-column order

batch_size must be between 1 and 80,000. The upper bound matches Spanner's absolute mutations-per-commit ceiling, not a generally safe row count: each affected cell and secondary index entry counts toward that ceiling. Wide or indexed tables need a lower value. The mutation buffer grows with actual input rather than reserving the requested size up front.

Each batch commits independently; COPY is not atomic across batches. After writing begins, an error reports rows_written for confirmed commits and states that earlier batches remain committed. A cancellation or deadline can leave the final batch's commit outcome unknown. The default insert_or_update mode is idempotent and remains the safest choice when retrying after a partial failure.

The file path argument specifies the target Spanner table name. Source columns are mapped to the table's writable Spanner columns by position. Generated columns (INFORMATION_SCHEMA.IS_GENERATED != 'NEVER') are computed by Spanner and reject writes, so they are excluded from the target column list — the source column count must match the number of non-generated columns, not the total column count.

When source order differs from the target table, use the native DuckDB list-valued columns option. Names are matched case-insensitively, must be unique, and must name exactly one writable target column per source column:

COPY (SELECT source_value, source_id, source_name) TO 'Users' (
    FORMAT spanner,
    columns ['Value', 'Id', 'Name']
);

DuckDB's COPY C API exposes source types but not automatically discovered source aliases, so aliases cannot currently drive mapping automatically. Use columns [...] whenever source aliases or ordering differ from the writable Spanner columns.

COPY TO ... FORMAT spanner supports scalar columns, DuckDB LIST/ARRAY source columns mapped to Spanner ARRAY targets, and DuckDB STRUCT source columns mapped to Spanner JSON targets.

-- Write query results to a Spanner table
COPY (SELECT Id, Name, Age FROM source_data) TO 'Users' (FORMAT spanner);

-- Specify mutation mode
COPY my_table TO 'Users' (FORMAT spanner, mode 'insert');

-- With explicit database path
COPY my_table TO 'Users' (
    FORMAT spanner,
    database_path 'projects/p/instances/i/databases/d',
    dialect 'googlesql'
);

Query Parameters

The params parameter accepts a STRUCT mapping parameter names to values. Two helper macros format values for Spanner type compatibility.

spanner_value(val) -- Auto-Detect Type

Infers the Spanner type from DuckDB's typeof() and converts the value to a Spanner-compatible format.

spanner_value(42::BIGINT)                           -- {"value":42,"type":"INT64"}
spanner_value('2024-01-15'::DATE)                   -- {"value":"2024-01-15","type":"DATE"}
spanner_value('\xDEAD'::BLOB)                       -- {"value":"3kFE","type":"BYTES"}
spanner_value('2024-06-15T10:30:00Z'::TIMESTAMPTZ)  -- {"value":"2024-06-15T10:30:00.000000Z","type":"TIMESTAMP"}
spanner_value(json('{"active":true}'))              -- {"$duckdb_spanner_json_format":"json-text-v1","type":"JSON","value":"{\"active\":true}"}
spanner_value(NULL::BIGINT)                         -- {"value":null,"type":"INT64"}

See DuckDB to Spanner type mapping for the full conversion table.

spanner_typed(val, type_name) -- Explicit Type

For cases where auto-detection is insufficient, specify the Spanner type name directly. The type name is validated when the query binds its parameters; an unknown or malformed name returns an error and is never silently sent as STRING.

spanner_typed(NULL, 'INT64')    -- {"value":null,"type":"INT64"}
spanner_typed('abc', 'STRING')  -- {"value":"abc","type":"STRING"}

Plain Values

Values without spanner_value() or spanner_typed() wrappers are passed as raw JSON. Types are inferred from the JSON representation (number, string, boolean, null). spanner_params() preserves DECIMAL and wide integer fields as typed NUMERIC strings, and preserves non-finite FLOAT/DOUBLE fields as typed special values, because those values cannot be represented safely as plain JSON numbers. Its output is a Spanner parameter transport envelope, not a plain JSON mirror of the input STRUCT; callers that inspect the JSON directly must accept these typed objects. An untyped SQL NULL has no lossless Spanner type and is rejected by spanner_value(); cast it or use spanner_typed(NULL, 'TYPE').

-- Mix plain and typed values
params := {'id': 1, 'name': spanner_value('Alice')}

Type Mapping

DuckDB to Spanner (Query Parameters)

spanner_value() maps DuckDB types to Spanner types. Values are serialized as JSON internally:

DuckDB Type	Spanner Type	JSON Value
BOOLEAN	BOOL	boolean
TINYINT, SMALLINT, INTEGER, BIGINT	INT64	number
FLOAT	FLOAT32	number, or "NaN"/"Infinity"/"-Infinity"
DOUBLE	FLOAT64	number, or "NaN"/"Infinity"/"-Infinity"
VARCHAR	STRING	string
DATE	DATE	string
TIMESTAMP, TIMESTAMP_S, TIMESTAMP_MS	TIMESTAMP	string (UTC RFC 3339, up to microsecond precision)
TIMESTAMP_NS	TIMESTAMP	string (UTC RFC 3339, nanosecond precision)
TIMESTAMP WITH TIME ZONE	TIMESTAMP	string (normalized to UTC, RFC 3339)
BLOB	BYTES	string (base64 encoded)
HUGEINT, UHUGEINT, UBIGINT, DECIMAL	NUMERIC	string (preserves the source digits; accepted range depends on the database dialect)
UUID	UUID	string
INTERVAL	INTERVAL	string (ISO 8601 duration)
JSON	JSON	tagged canonical JSON text (preserves JSON strings and JSON null distinctly from SQL NULL)
TIME	STRING	string (HH:MM:SS.ffffff)
TIME_NS	STRING	string (HH:MM:SS.nnnnnnnnn)
BIT	BYTES	string (base64 encoded)
T[]	ARRAY<T>	JSON array (elements follow scalar conversion rules)

The $duckdb_spanner_json_format field versions the parameter envelope, not the JSON payload. JSON object keys remain unrestricted; untagged low-level JSON envelopes keep their existing pre-serialized-string/structural-value behavior.

TIMESTAMP_NS preserves nanoseconds in the outbound Spanner parameter. Spanner TIMESTAMP query results map to DuckDB TIMESTAMP WITH TIME ZONE, whose current conversion path has microsecond precision, so a nanosecond round trip truncates the final three fractional digits.

NUMERIC bounds are enforced by Spanner after the target database dialect is known. GoogleSQL NUMERIC has precision 38 and scale 9; PostgreSQL-dialect NUMERIC supports arbitrary precision up to Spanner's documented limits. For arbitrary-precision values in GoogleSQL, use an explicit STRING target.

spanner_value() also supports array types:

spanner_value([1, 2, 3])             -- {"value":[1,2,3],"type":"ARRAY<INT64>"}
spanner_value(['a', 'b'])            -- {"value":["a","b"],"type":"ARRAY<STRING>"}
spanner_value([true, false])         -- {"value":[true,false],"type":"ARRAY<BOOL>"}

STRUCT values and nested LIST/ARRAY element types are not supported. A top-level LIST or ARRAY must contain a supported scalar type. spanner_params() also rejects plain LIST/ARRAY fields; wrap them with spanner_value() or spanner_typed() so their element type is explicit.

Spanner to DuckDB (Query Results)

Spanner Type	DuckDB Type
BOOL	BOOLEAN
INT64	BIGINT
FLOAT32	FLOAT
FLOAT64	DOUBLE
NUMERIC (GoogleSQL)	DECIMAL(38,9)
numeric (PostgreSQL)	VARCHAR
STRING	VARCHAR
JSON	VARCHAR (aliased as JSON)
BYTES	BLOB
DATE	DATE
TIMESTAMP	TIMESTAMP WITH TIME ZONE
UUID	UUID
INTERVAL	INTERVAL
ARRAY<T>	LIST(T)
STRUCT<...>	STRUCT(...)
PROTO	BLOB
ENUM	BIGINT

GoogleSQL NUMERIC uses the compatible fixed DECIMAL(38,9) representation. PostgreSQL numeric is returned as its exact string representation because it can be NaN or exceed DuckDB DECIMAL's 38-digit precision and 9-digit scale. Cast those VARCHAR results explicitly when a value fits the desired DuckDB DECIMAL type.

Spanner INTERVAL values can carry nanosecond fractional seconds. DuckDB INTERVAL stores microseconds, so inbound conversion truncates the final three fractional digits toward zero, matching the inbound TIMESTAMP precision policy described above.

Testing

SQLLogicTest (primary)

Extension CI SQLLogicTest files live in test/sql/. make test (alias for make test_release) builds the loadable extension, starts or reuses the Spanner emulator, seeds the test database (tests/setup_sqllogic_db.sh), and runs all test/sql/*.test files.

make configure          # once: venv + duckdb_sqllogictest-python
make release test_release
make test               # alias for test_release

Requires Docker (Colima on macOS). Emulator tests use SPANNER_EMULATOR_HOST (default localhost:9010).

Rust Integration Tests

Rust integration tests use testcontainers to start the emulator automatically. You only need a running Docker daemon:

cargo test

.cargo/config.toml sets RUST_TEST_THREADS=4 to keep Docker/testcontainers resource usage bounded during concurrent test runs.

Library-Mode Tests

Rust integration tests call the one-shot register_spanner_extension orchestrator, matching the loadable initializer's preflight and stage ordering.

Cleaning Stale Build Artifacts

This repository includes cargo-sweep-based cleanup targets for large debug builds:

# Build the debug loadable extension first, then sweep artifacts older than 3 days
make build-sweep

# Preview artifacts older than 3 days
make sweep-dry-run

# Delete artifacts older than 3 days
make sweep

Override the retention window with SWEEP_DAYS:

make sweep SWEEP_DAYS=7

Advanced

spanner_query_raw -- Low-Level Table Function

The underlying table function that spanner_query wraps. Use this when:

• You have a pre-built JSON string for params (e.g., from application code)
• Table macro expansion causes issues in your client library or tooling

SELECT * FROM spanner_query_raw(
    'SELECT * FROM Users WHERE Age > @min_age',
    database_path := 'projects/myproj/instances/myinst/databases/mydb',
    params := '{"min_age": {"value": 21, "type": "INT64"}}'
);

The params parameter is a VARCHAR containing a JSON object. Use spanner_params() to build it from a STRUCT:

SELECT * FROM spanner_query_raw(
    'SELECT * FROM Users WHERE Age > @min_age',
    params := spanner_params({'min_age': spanner_value(21::BIGINT)})
);

Registered Names

This extension registers the following names into the global DuckDB namespace.

Table functions and macros:

Name	Kind	Description
spanner_query	table macro	Wraps spanner_query_raw with ergonomic params (see Table Functions)
spanner_query_raw	table function	Execute Spanner SQL (see Low-Level Table Function)
spanner_scan	table function	Read a Spanner table (see Table Functions)
spanner_tables	table function	List Spanner tables or views from INFORMATION_SCHEMA.TABLES
"spanner:Table"	replacement scan	Shorthand for spanner_scan('Table') using configured session defaults
spanner_ddl	table macro	Execute DDL synchronously (see spanner_ddl)
spanner_ddl_async	table macro	Submit DDL asynchronously (see spanner_ddl_async)
spanner_operations	table macro	List DDL operations (see spanner_operations)
spanner	copy function	Write to Spanner via COPY TO (see COPY TO)
spanner_value(val)	scalar function	Auto-detect Spanner type from DuckDB type (see Query Parameters)
spanner_typed(val, type_name)	scalar function	Explicit Spanner type wrapper (see Query Parameters)
spanner_params(s)	scalar function	Convert a STRUCT to a JSON params string for spanner_query_raw
interval_to_iso8601(i)	scalar function	Convert a DuckDB INTERVAL to an ISO 8601 duration string (e.g., 'P1Y3M', 'PT2H30M')

Config options (see Config Options):

Name	Type	Description
spanner_project	VARCHAR	Default Google Cloud project ID
spanner_instance	VARCHAR	Default Spanner instance ID
spanner_database	VARCHAR	Default Spanner database ID
spanner_database_path	VARCHAR	Default full database resource path
spanner_endpoint	VARCHAR	Default gRPC endpoint
spanner_endpoint_mode	VARCHAR	Default connection profile
spanner_admin_endpoint	VARCHAR	Default admin endpoint for DDL/operations
spanner_stream_idle_timeout_secs	BIGINT	Query/scan idle timeout seconds (default 900, 0 disables, maximum 31536000)

Convenience Macro Pattern

With config options, convenience macros are often unnecessary. Just set your defaults once:

SET spanner_project = 'myproj';
SET spanner_instance = 'myinst';
SET spanner_database = 'mydb';
SET spanner_endpoint_mode = 'emulator';
SET spanner_endpoint = 'localhost:9010';

SELECT * FROM spanner_query('SELECT * FROM Users');
SELECT * FROM spanner_query(
    'SELECT * FROM Users WHERE Age > @min',
    params := {'min': spanner_value(21::BIGINT)}
);

If you need a reusable macro that overrides specific config values:

CREATE MACRO my_query(
    sql, params := NULL, use_parallelism := NULL, parallelism_mode := NULL,
    use_data_boost := NULL, max_parallelism := NULL,
    exact_staleness_secs := NULL, max_staleness_secs := NULL,
    read_timestamp := NULL, min_read_timestamp := NULL,
    endpoint_mode := NULL,
    priority := NULL
) AS TABLE
SELECT * FROM spanner_query(
    sql,
    database_path := 'projects/myproj/instances/myinst/databases/mydb',
    endpoint_mode := endpoint_mode,
    endpoint := 'localhost:9010',
    params := params,
    use_parallelism := use_parallelism,
    parallelism_mode := parallelism_mode,
    use_data_boost := use_data_boost,
    max_parallelism := max_parallelism,
    exact_staleness_secs := exact_staleness_secs,
    max_staleness_secs := max_staleness_secs,
    read_timestamp := read_timestamp,
    min_read_timestamp := min_read_timestamp,
    priority := priority
);