PeachQ 0.89: q tables in DuckDB, SQL beside qSQL, and Kafka¶
PeachQ 0.89 keeps q tables in DuckDB, where qSQL and SQL both query them. Kafka topics now stream straight into q tables, and q publishes back.
What's new¶
- External tables in DuckDB: qSQL on a DuckDB table runs inside DuckDB, and the same table answers SQL.
- SQL with q values as parameters, from a handle, an
s)line or a function. - Kafka: subscribe topics into q tables, keep bad messages, publish JSON or q IPC bytes.
- DuckDB 2.0, up from 1.5.5. On x86-64 it needs a CPU with AVX2; without
one, PeachQ reports
'duckdbwith the reason rather than crashing. .hashadds xxHash, rapidhash and MurmurHash3, agreeing with DuckDB's hash functions;.cryptoadds digests, HMAC and random bytes over the system's OpenSSL.- The API reference now covers every q library,
including
.kafka,.sqland.s. - From the 0.88 updates since our last post:
sum,avg,min,maxandreverseof small vectors are about 100x faster per call (select sum w by kon 7.8M rows drops from 21 s to 1.8 s), and HTTPS works on Windows with no setup, verifying against the system's root store. - q conformance reaches 89.0%, up from 88.0% at 0.88, and Windows matches Linux on all 1242 measured suites.
DuckDB: external tables, qSQL and SQL¶
Store a q table in a DuckDB file, bind it as an external table, and a plain qSQL
select on it runs inside DuckDB. Only the answer comes back into q:
q)t:get `:pq:duckdb:mkt:trade/
q)select hi:max price, vol:sum size by sym from t
| sym | hi | vol |
| symbol | float | long |
|========|-------|------|
| AAPL | 228.4 | 870 |
| IBM | 183 | 550 |
| MSFT | 416.1 | 950 |
q)-1 exec last sql from .duckdb.sqllog;
SELECT sym, coalesce(max(price), $pq1) AS hi, CAST(coalesce(sum(size), $pq2) AS BIGINT) AS vol FROM trade GROUP BY sym ORDER BY sym
An external table is a q table whose data stays in DuckDB. qSQL and SQL both work on it, and writes such as inserts go through to DuckDB. That gives you DuckDB's SQL and its access to files over HTTPS and on S3, with the HTTP, AWS and DuckLake extensions bundled in the download, while the same table answers qSQL with your q types.
A select on an external table is translated to SQL and run in DuckDB: where terms, grouping, the common aggregates (count, sum, average, min, max, first, last, weighted averages and sums, correlation, covariance and the deviations), row limits and the row index. Anything DuckDB cannot answer exactly as q would runs in q on the rows that come back, so the answer is always q's.
.duckdb.sqllog shows every statement that ran, .duckdb.maxrows refuses an
unfiltered select on a large table before anything is fetched, and
.duckdb.pushdown:0b turns the translation off. The file holds ordinary DuckDB
tables, so the duckdb command line and other DuckDB 2.0 clients can read it too.
SQL runs through a DuckDB handle, an s) line or .s.ex. (sql;dict) binds
$name markers and (sql;list) binds $1 or ?, list values included, so a
q function can pass its arguments straight into SQL:
bigTrades:{[lo] h ``sql SELECT sym, sum(size) AS vol FROM trade WHERE size >= $lo GROUP BY sym ORDER BY sym``}
DuckDB is where PeachQ storage is heading: tables in an open format that other tools can read. PeachQ's own reader still opens splayed tables written by kdb+, so existing on-disk data can be copied across. This area is new and still settling, so names and options may change between releases. SQL and DuckDB storage walks through it from download to query, and the new SQL page in Language basics lists every way in.
Kafka topics into q tables¶
A :pq:kafka: handle subscribes and publishes. .kafka.sub lands JSON messages
in a q table, keeps bad messages with their offset and reason instead of
dropping them, and can leave committing offsets to you. A topic reads like a
table, and q can publish back as JSON or as q IPC bytes. The download with
DuckDB carries librdkafka 2.12.1 beside q, so there is nothing to install.
Kafka shows two q processes and a Redpanda broker doing all of it in a three-pane recording.