duckdb

The duckdb collection runs SQL over the tables a pipeline produces, in DuckDB's dialect, with no network access. It is the executor for queries written by ai.sql, and it runs a query you write yourself just as well -- typed into the block, saved from an earlier run, or uploaded as a .sql file.

Table names🔗

The tables connected to tables are views named t0, t1, ... in the order they are connected. The rule has no exceptions -- a single table is t0 -- so a query keeps working wherever it is used, and a query written for one pipeline runs in another.

SELECT s.state_name, sum(p.biomass) AS total_biomass
FROM t0 p JOIN t1 s USING (state_cd)
GROUP BY 1 ORDER BY 2 DESC

Tables may be Parquet or CSV. tables is optional: a query can make its own data (SELECT * FROM range(10)).

Scripts🔗

A file may hold several statements -- creating temporary tables first, say. Every statement but the last is run, and the result of the last is the output. A script whose last statement returns no rows and columns fails the block.

What a query can reach🔗

Nothing but the connected tables. The connection is locked before your SQL runs: no other files, no URLs, no extensions, no settings changes. A query that tries -- read_csv('/etc/passwd'), INSTALL httpfs, COPY ... TO anywhere but the block's own output -- fails with a permission error. This is the same connection ai.sql validates its queries on, which is why a query it accepted runs here.

The blocks size DuckDB to their sandbox, so a query over a table larger than memory spills to disk rather than being killed. Declare more memory on a pipeline that needs it by choosing a block with a larger resources.memory; these declare 8 GiB.

The header🔗

A query file may start with comment lines the block checks:

-- spade:tables 2
-- spade:duckdb 1.5
SELECT ...

spade:tables makes a query written for two tables fail with a clear message when only one is connected. spade:duckdb names the DuckDB version the query was written for; a different minor version is a warning in the log, not an error. ai.sql always writes both; a hand-written file needs neither.

Wiring🔗

query is a file and tables a collection, and a single file can fill a collection input, so a bare reference into duckdb.query_file is ambiguous. The web editor always names the input; in a hand-written pipeline, use as:

- id: "@run_query"
  name: duckdb.query_file
  inputs:
    - { block: "@write_query", output: query, as: query }
    - { block: "@pivot", output: result, as: tables }

Installation🔗

spade install file:///path/to/blocks/duckdb