duckdb.q

Query and write DuckDB databases from q: a database is a handle, its tables are q tables. .pq.hopen_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. .duckdb.getx[h;`t] reads a table with its schema as the pair (data;schema), which set and upsert accept back. \?duckdb has more examples.

Examples

h:.pq.hopen_duckdb[`demo;":memory:"]
`: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.count[c;t]	The row count of a table of the handle's catalog, counted by DuckDB.
.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.execx[c;sql]	SQL's answer with its DuckDB schema, as the pair (data;schema); sql as .duckdb.exec takes it, parameters included.
.duckdb.get[c;t]	A table of the handle's catalog as a q table.
.duckdb.getx[c;t]	A table with its DuckDB schema, as the pair (data;schema) that set and upsert accept back.
.duckdb.hdel[handle;table]	Drop a table (or view) of the handle's catalog.
.duckdb.lastpush	The .sql.translate answer (sqlTree;params;schema;qTree) of the last linked select that ran a pushed statement, qTree :: when DuckDB answered it whole; :: when the last select pushed nothing (pushdown off, nothing to move, or a refusal).
.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.maxrows	The row count above which a qSQL select on a q-origin table of a handle needs a where term .sql.translate moves into DuckDB: without one it is 'limit before any query runs, the table, its count and this threshold in .duckdb.err[].
.duckdb.meta[c;t]	The meta of a table of the handle's catalog, read from DuckDB without fetching a row.
.duckdb.onsql[row]	Called once per statement with its log row as a dict.
.duckdb.params[sql]	The $name parameters a statement (string) declares, distinct and in first-occurrence order; $1 and ?
.duckdb.pushdown	Whether a qSQL select on a q-origin table of the handle runs what .sql.translate can move (where terms, limit, order; the select phrase and by whole when every item translates) inside DuckDB before q runs the rest, read on every select; off, every select pulls the whole table.
.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.set[c;t;d]	Create or replace a table of the handle's catalog from a q table, or from the pair .duckdb.getx answers.
.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 ::. sql is a string, or the pair (string;dict) binding each $name by name or (string;list) binding $1, $2 ... by position; values are atoms or strings (a null is SQL NULL), DuckDB's refusal is 'duckdb with the reason in .duckdb.err[], anything else 'type. The triple (string;params;schema), params () for none and schema the table getx answers, reads the result as .duckdb.get reads a table.

Examples

.duckdb.exec[.duckdb.main[];("SELECT 'a' AS s";();([] col:enlist `s; dtype:enlist "VARCHAR"; logical:enlist `symbol))]

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

.duckdb.exec[.duckdb.main[];("SELECT $a + $b AS n";`a`b!1 2)]

.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.maxrows

The row count above which a qSQL select on a q-origin table of a handle needs a where term .sql.translate moves into DuckDB: without one it is 'limit before any query runs, the table, its count and this threshold in .duckdb.err[]. The count is the one taken when the table was bound, read afresh after the bridge's own writes; get stays unlimited.

.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.params[sql]

The $name parameters a statement (string) declares, distinct and in first-occurrence order; $1 and ? are named by number.

Examples

.duckdb.params "SELECT $x FROM t WHERE s='$y' AND n>$z"

.duckdb.pushdown

Whether a qSQL select on a q-origin table of the handle runs what .sql.translate can move (where terms, limit, order; the select phrase and by whole when every item translates) inside DuckDB before q runs the rest, read on every select; off, every select pulls the whole table. A statement DuckDB refuses stays in .duckdb.sqllog and the select pulls instead.

.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]