ClickHouse 26.9 Refreshes Append-Only Views Incrementally

A refreshable materialized view that copies an events table somewhere else used to re-run its whole query on every refresh. ClickHouse 26.9, released on September 23, adds APPEND INCREMENTAL, so each refresh reads only the rows committed since the last one. The release (56 new features, 135 performance optimizations, 464 bug fixes) also brings CREATE TOKEN, LIMIT ... AFTER/UNTIL and a DISTINCT that spills to disk. The October newsletter lists the same highlights.

Copy a MergeTree table into Iceberg with APPEND INCREMENTAL

The view needs a cursor to know what is new. ClickHouse uses the block number and block offset columns, so the CREATE VIEW docs require a single plain MergeTree source table with enable_block_number_column = 1 and enable_block_offset_column = 1. The release post's example source table also adds minmax indexes on those columns:

CREATE TABLE order_events
(
    event_id UInt64,
    event_time DateTime,
    order_id UInt64,
    event_type LowCardinality(String),
    country LowCardinality(String),
    amount Decimal(10, 2)
)
ORDER BY (event_time, order_id, event_id)
SETTINGS
    enable_block_number_column = 1,
    enable_block_offset_column = 1,
    add_minmax_index_for_block_number_column = 1,
    add_minmax_index_for_block_offset_column = 1,
    part_minmax_index_columns = 'with_block_number_offset';

Next, an Iceberg target and the view. The post uses IcebergLocal to keep the demo on one machine, so swap in your real lake engine from the docs.

SET allow_insert_into_iceberg = 1;

CREATE TABLE lake_order_events
(
    event_id UInt64,
    event_time DateTime,
    order_id UInt64,
    event_type String,
    country String,
    amount Decimal(10, 2)
)
ENGINE = IcebergLocal('lake_order_events', 'Parquet');

CREATE MATERIALIZED VIEW order_events_to_iceberg
REFRESH EVERY 1 HOUR APPEND INCREMENTAL
TO lake_order_events
AS
SELECT event_id, event_time, order_id, event_type, country, amount
FROM order_events;

In the post, the first refresh added 4 records. After three more inserts, the next refresh appended exactly those 3, for a total of 7. For Iceberg targets the cursor lives in the snapshot summary and is committed in the same snapshot as the data. It advances only when the append succeeds, and a restart resumes from it.

Trigger a refresh and check the history yourself:

SYSTEM REFRESH VIEW order_events_to_iceberg;
SYSTEM WAIT VIEW order_events_to_iceberg;

SELECT
    made_current_at, operation,
    summary['added-records'] AS added_records,
    summary['total-records'] AS total_records,
    summary['clickhouse.refresh-cursor'] != '' AS has_refresh_cursor
FROM system.iceberg_history
WHERE table = 'lake_order_events'
ORDER BY made_current_at;

The docs set hard limits. The query rejects JOIN, UNION, subqueries, views and table functions, and you cannot add or remove APPEND or INCREMENTAL on an existing view. Switching modes means recreating it.

Hand scripts and agents a credential that expires

CREATE TOKEN generates a random secret, adds it to the current user as an extra sha256_password method, and shows it once. It is meant for scripts, CI jobs and agents that should not hold the user's main password. The CREATE TOKEN docs give the syntax.

-- once, as an admin
GRANT CREATE TOKEN ON *.* TO pipeline_user;

-- as pipeline_user: one hour, read-only on one table
CREATE TOKEN
VALID FOR INTERVAL 1 HOUR
GRANTS (SELECT ON analytics.order_events)
FORMAT TSVRaw;

Without a VALID clause the token lasts 30 minutes (create_token_default_ttl_seconds). GRANTS can only narrow what the user already has. The post shows an INSERT with a select-only token failing with ACCESS_DENIED.

Gotchas from the docs:

• Expiry and GRANTS apply at login. Open sessions keep running.
• There is no ON CLUSTER, and the GRANTS limit is enforced only on the node that receives the query.
• No statement drops one token. ALTER USER ... RESET AUTHENTICATION METHODS TO NEW keeps only the newest method.
• Keep CREATE VIEW and CREATE TABLE out of the grants. Objects like refreshable views outlive the token and run with the creator's rights.

Slice logs around an incident with LIMIT AFTER and UNTIL

AFTER <condition> starts output at the first matching row, inclusive. UNTIL <condition> stops output at a matching row, exclusive. ALL re-arms the boundary every time it matches. The post's example returns every server error plus the next two requests:

SELECT timestamp, path, status
FROM nginx_logs
WHERE ip = '91.243.160.31'
  AND timestamp >= '2019-01-24 00:00:00'
  AND timestamp < '2019-02-03 00:00:00'
ORDER BY timestamp, ip, path, method, status, response_bytes
LIMIT 3 AFTER status >= 500 ALL;

The ORDER BY is spelled out fully so the row order is deterministic, which the boundaries depend on.

Let DISTINCT spill to disk

Set max_bytes_before_external_distinct and DISTINCT writes its hash set to temporary files instead of dying with MEMORY_LIMIT_EXCEEDED. With max_bytes_ratio_before_external_distinct = 0.5, spilling starts at half of available memory. In the post, SELECT DISTINCT number FROM numbers_mt(100_000_000) failed under a 150 MB limit, then finished at about 105 MiB peak with spilling on. Spilling still needs memory to merge files: at a 100 MB limit the same query failed while reading spilled data back.

What to try first in ClickHouse 26.9 on Monday

1. Pick one append-only table you already copy out on a schedule, such as events, audit rows or agent traces.
2. On a non-production 26.9 instance, create a copy with the block-number settings and an APPEND INCREMENTAL view into a scratch target.
3. Insert a few rows, run SYSTEM REFRESH VIEW, and confirm only the new rows land.
4. Replace one shared password in a scheduled script with a CREATE TOKEN that has a short VALID FOR and a narrow GRANTS.
5. Rewrite your favorite incident query with LIMIT ... AFTER.

If your lake or reporting copy is fed by append-only AI logs and traces, incremental refresh cuts the rescan cost, and short-lived scoped tokens mean an agent's leaked credential expires on its own.