duckdb.q

Query and write DuckDB databases from q: a database is a handle, its tables are q tables. hopen `:pq:duckdb:alias:/path/db opens or creates a database and answers the alias symbol `:pq:duckdb:alias, the handle every .duckdb verb takes. h "SELECT ..." runs SQL and answers a table; hclose h closes it. `:pq:duckdb:alias:table/ names a table for get, set, upsert and qsql, with select/where/by pushed down to DuckDB. One DuckDB database lives in the process, `:pq:duckdb:main (.duckdb.main[]; q -duckdb path puts it in a file): every alias is a catalog attached to it, and a q global bound to a DuckDB table is a same-named view in it, so s)SELECT ... runs SQL over q names, live. DuckDB's httpfs carries s3:// gcs:// hf:// URLs for read0, read1, select from and set, with credentials as DuckDB secrets. h (`getx;`t) reads a table with its schema as the pair (data;schema), which set and upsert accept back. \?duckdb has more examples.

Examples

h:hopen `:pq:duckdb:demo:default:
`:pq:duckdb:demo:trade/ set ([] sym:`a`b; px:1.5 2.5)
`:pq:duckdb:demo:trade/ upsert ([] sym:enlist `c; px:enlist 3.5)
select from `:pq:duckdb:demo:trade/ where px>2
h "SELECT sym, px*2 AS px2 FROM trade"
trade:get `:pq:duckdb:demo:trade/
s)SELECT sym, count(*) FROM trade GROUP BY sym
`:pq:duckdb:demo:dow/ set select from `:https://www.timestored.com/data/sample/dowjones.csv
select max Price by 10 xbar `year$Date from `:pq:duckdb:demo:dow/
h "SELECT year(Date) AS yr, max(Price) FROM dow GROUP BY yr ORDER BY yr DESC LIMIT 3"
hclose h

Entity Summary

Entities	Short Description
.duckdb.err[handle]	The reason behind the last 'duckdb signal, first line the cause, cleared by the next call.
.duckdb.exec[handle;sql]	Run SQL on a handle and answer the result as a table; a statement without a result answers ::.
.duckdb.hdel[handle;table]	Drop a table (or view) of the handle's catalog.
.duckdb.load[handle;names]	Bind the handle's tables and views as q globals at the root, later-wins.
.duckdb.main[]	The handle of the process's own DuckDB database, `:pq:duckdb:main: in-memory, or the file q -duckdb path names.
.duckdb.onsql[row]	Called once per statement with its log row as a dict.
.duckdb.secret[name;typ;opts]	Create or replace a DuckDB secret (the credentials httpfs uses for s3:// gcs:// hf://), or drop one.
.duckdb.secrets[]	The secrets defined, without their values.
.duckdb.sqllog	The statement log: one row per statement the bridge issued, newest last.
.duckdb.sqllogmax	How many rows .duckdb.sqllog keeps: 0 keeps nothing; at the cap the older half is dropped.
.duckdb.types[]	The DuckDB-to-q type map as a table, one row per contract entry.
.duckdb.unsafeExecText[handle;sql]	Run any statement and answer DuckDB's own rendering as text, bypassing the type map: rows as DuckDB prints them, or the error message as data when the statement fails.
.duckdb.unsafeText[handle;tableName]	A table as DuckDB renders it, every column cast to VARCHAR (so nested, UUID, ENUM and BIT cells show), NULL as the text NULL, rows in insertion order.

Entity Details

.duckdb.err[handle]

The reason behind the last 'duckdb signal, first line the cause, cleared by the next call. err[] is any connection's, err[handle] that connection's.

Return:	string

Examples

@[.duckdb.exec[.duckdb.main[]];"SELECT * FROM no_such_table";{.duckdb.err[]}]

.duckdb.exec[handle;sql]

Run SQL on a handle and answer the result as a table; a statement without a result answers ::.

Examples

.duckdb.exec[.duckdb.main[];"SELECT 1 AS one, 'a' AS s"]

.duckdb.hdel[handle;table]

Drop a table (or view) of the handle's catalog. hdel `:pq:duckdb:alias:table/ is the same verb. A q global bound to it stays bound and errors on use.

Return:	table

Examples

.duckdb.exec[.duckdb.main[];"CREATE OR REPLACE TABLE u AS SELECT 1 AS a"]
.duckdb.hdel[.duckdb.main[];`u]

.duckdb.load[handle;names]

Bind the handle's tables and views as q globals at the root, later-wins. \l `:pq:duckdb:alias is the same door.

Return:	the names bound
Parameters:	names - () for none, :: for all, else the table names

Examples

.duckdb.load[.duckdb.main[];::]

.duckdb.main[]

The handle of the process's own DuckDB database, `:pq:duckdb:main: in-memory, or the file q -duckdb path names. Opened on the first call; every alias is a catalog attached to it, and .parquet and s) run here.

Return:	`:pq:duckdb:main

Examples

.duckdb.exec[.duckdb.main[];"SELECT current_database()"]

.duckdb.onsql[row]

Called once per statement with its log row as a dict. Rebind it to route the log elsewhere, or set it to (::) for none. Do not close the connection or issue transaction control from a handler: it runs mid-statement.

Examples

.duckdb.onsql:{[row] -1 row`sql;}

.duckdb.secret[name;typ;opts]

Create or replace a DuckDB secret (the credentials httpfs uses for s3:// gcs:// hf://), or drop one. opts ride verbatim as KEY value clauses of CREATE SECRET, so an unknown key is DuckDB's own refusal; the key `persistent (boolean) picks the persistent form, which outlives the process in DuckDB's own store.

Return:	name
Parameters:	opts - a symbol-keyed dict of KEY value clauses; () or (::) drops the secret instead typ - the secret type: `s3, `gcs, `r2, `huggingface ...

Examples

.duckdb.secret[`bucket;`s3;`key_id`secret`region!("AKIA...";"...";"eu-west-1")]

.duckdb.secret[`bucket;`s3;()]

.duckdb.secrets[]

The secrets defined, without their values.

col:	scope the URL prefixes the secret covers

Examples

.duckdb.secrets[]

.duckdb.sqllog

The statement log: one row per statement the bridge issued, newest last.

Examples

select time, dur, sql from .duckdb.sqllog

.duckdb.types[]

The DuckDB-to-q type map as a table, one row per contract entry.

Columns:	needs - what a schema row must carry to round-trip: none, logical, dtype or both dtype - the DuckDB DDL spelling logical - the logical type name ktype - the .duckdb.meta type char canon - whether a bare read of that DuckDB type produces this row

Examples

select from .duckdb.types[] where canon

.duckdb.unsafeExecText[handle;sql]

Run any statement and answer DuckDB's own rendering as text, bypassing the type map: rows as DuckDB prints them, or the error message as data when the statement fails. A diagnostic, not a query door.

Return:	string

Examples

.duckdb.unsafeExecText[.duckdb.main[];"SELECT [1,2,3] AS arr, {'a': 1} AS st"]

.duckdb.unsafeText[handle;tableName]

A table as DuckDB renders it, every column cast to VARCHAR (so nested, UUID, ENUM and BIT cells show), NULL as the text NULL, rows in insertion order. A text cell holding a newline or " | " misaligns its row.

Return:	a table of one string column per column of tableName

Examples

.duckdb.exec[.duckdb.main[];"CREATE OR REPLACE TABLE u AS SELECT [1,2] AS arr, 1 AS a"]
.duckdb.unsafeText[.duckdb.main[];`u]