Skip to content

v2.4.0-ce — Stream any query result to partitioned Parquet in the lake

Released 2026-10-27 · GitHub release

v2.4.0-ce adds COPY ... TO LAKE: materialize the result of any SELECT or WITH query as plain, Hive-partitioned foreign Parquet in your own object store — in a single SQL statement, with no export pipeline and no second system. It also makes a fresh install answer database questions on its chat model out of the box, adds native model eviction/swap, and folds in eight fixes from a field report. Community Edition, free, seamless (drop-in) upgrade from v2.3.x.

Why it matters

"Land this transformed slice of the database in S3 as Parquet, partitioned by region" is normally a second system — an orchestrator, a warehouse UNLOAD, or hand-rolled Python — with its own schedule, credentials and copy of your schema. COPY ... TO LAKE makes it one statement that streams straight to the writer and round-trips with CREATE EXTERNAL TABLE.

COPY query results to lake

Materialize a SELECT/WITH read query as plain foreign Parquet under a configured lake destination.

COPY (<SELECT or WITH query>)
TO LAKE '<relative/path>'
WITH (CONNECTION = '<destination>', PARTITION_BY = (col, ...),
      MODE = 'overwrite_partitions',   -- or 'append'
      COMPRESSION = 'zstd',            -- snappy | gzip | lz4 | none
      ROW_GROUP_SIZE = 122880, FILE_TARGET_MB = 128);
Aspect Behavior
Source Streamed to the writer; not subject to the REST SELECT response cap.
Result job_id, run_id, rows_written, files_written, bytes_written, partitions_touched.
Partitioning PARTITION_BY columns become Hive directories and are omitted from data files.
Modes overwrite_partitions replaces only produced partitions (empty result deletes nothing); append adds files.
Types All output fields OPTIONAL; exact decimals + supported temporal types stay typed.
Statistics Row-group stats, page indexes, high-cardinality bloom filters emitted.
Jobs Inspect/cancel via /v1/lake/copy/jobs and /v1/lake/runs.

Round-trip

CREATE EXTERNAL TABLE permit_matches STORED AS PARQUET
LOCATION 'matches/' PARTITIONED BY (metro TEXT)
WITH (CONNECTION = 'werkor_lake');

SELECT COUNT(*) FROM permit_matches WHERE metro = 'nyc';  -- prunes to the nyc partition

Authorization

Requires a freshly authenticated principal with effective full data-write access, source-database access, and an explicit destination write_allowlist grant. An empty write_allowlist (including on upgraded destinations) disables COPY. Grants are relative to the destination root; '/' explicitly grants all non-managed child locations. Absolute paths, arbitrary S3 URLs, managed-table ancestry, read-only / table-restricted credentials and active transactions are all refused. Agent operating privileges alone grant no COPY authority.

Limitations

  • One-shot export — not a writable external table or continuous materialized view.
  • Plain object-store listing is not a multi-file transaction: concurrent readers may observe a transient mixture while complete new files replace old ones.
  • Multi-partition publication is not a single table-wide transaction; cancellation after publication begins can leave completed partitions (recovery reconciles the journal).
  • Derived column-alias lists over a non-VALUES relation are unsupported; COPY fails closed.

LLM / inference

  • database_ask default allowlist is now empty (= any installed model). A fresh install answers database questions on the configured chat model (qwen2.5:7b on ≥8 GiB) with no config edits. A non-empty approved_models is an optional operator restriction; generated SQL is always gated by read-only validation + table grants.
  • Native model eviction/swap: evict idle LRU instead of a reservation-full 503, honor keep_alive=0 (unload), never evict an in-flight model, keep /api/ps accurate.
  • Ollama-compat: /api/version is pre-auth; /api/tags works for table-scoped keys.

Fixes

  • Restricted-read (RAG) gate now allows CURRENT_DATE, DATE literals and INTERVAL (these read no extra data); writes/DDL/impure functions still denied.
  • database_ask declines honestly instead of surfacing an unresolved placeholder slot.
  • SUM(INTEGER) returns BIGINT on both row and columnar paths.
  • Guard so a raw tool-call JSON blob can never be surfaced as a chat answer.
  • Learning Loop console: real database names in the Database dropdown; plan view unwraps correctly (no white-screen); read-only authorization checks no longer append to the audit chain, so VERIFY walks to completion and reports intact / partial / tampered distinctly.

Upgrade

Drop-in. On-disk format is back-compatible (storage-upgrade gate passed on the release build). docker pull synapcores/community:v2.4.0-ce.