Feature requests worth making

Every long-time user of a database keeps a list of the things they keep wanting. This one has been kept for years and re-checked against ClickHouse 26.4 for this article. The useful part is the sorting: it turns out that many wishes were granted, some have a good workaround, and a few are still open and worth voicing.

Already granted

• Parametrised views. “A user-defined, parametrised SQL table function.” The view form exists: CREATE VIEW v AS SELECT ... WHERE x = {x:Type} and SELECT * FROM v(x = ...). The global, database-free form is still open; see Table functions as a first-class abstraction.
• Moving a table to another database. RENAME TABLE a.t TO b.t moves it, with no copy. The wished-for MOVE TABLE is just a different spelling of something that works.
• Materialized views feeding any engine. The wish was a view that targets a SummingMergeTree table. CREATE MATERIALIZED VIEW ... TO target_table has long accepted any target engine.
• FROM table SELECT columns. Granted in 22.12; see the request and LIMIT could do more.

Open, with a good workaround

“I just want to insert the changed columns of a wide row”

A table with a hundred columns, most of them empty or nullable. New data arrives in pieces: one source fills columns 1 to 10, another fills 11 to 30. The wish: insert the partial row and have the table merge it into one row, like an update, keeping the latest value of each column (a ReplacingMergeTree that does not replace the whole row).

ReplacingMergeTree keeps one whole row per key, so a partial row would blank the other columns. The workaround is AggregatingMergeTree with SimpleAggregateFunction(anyLast, ...) per column. The function ignores NULL, so a null in a new row does not overwrite an older value:

CREATE TABLE wide
(
    id UInt32,
    a  SimpleAggregateFunction(anyLast, Nullable(String)),
    b  SimpleAggregateFunction(anyLast, Nullable(UInt32))
)
ENGINE = AggregatingMergeTree ORDER BY id;

INSERT INTO wide VALUES (1, 'x', NULL);
INSERT INTO wide VALUES (1, NULL, 5);

OPTIMIZE TABLE wide FINAL;
SELECT * FROM wide;                                         -- 1  x  5

-- without OPTIMIZE (merges are asynchronous), always aggregate on read
SELECT id, anyLast(a), anyLast(b) FROM wide GROUP BY id;    -- 1  x  5

The general form of this wish is a native upsert; the discussion is in UPSERT on MergeTree. The ergonomic request is a purpose-built engine, so that the schema does not need an aggregate wrapper on every column and the read side does not need a GROUP BY. Until then the pattern works.

“Throw this error when this limit is reached” (throwError)

When your software loads data with INSERT ... SELECT, you want to test that it behaves when the query fails halfway: the memory limit, the row limit, the sort limit. The expected reaction is to delete the half-written data in the target and alert someone. To test this you must make ClickHouse fail the way it would fail in production.

The wish was a way to say, raise error X when limit Y is hit (the filed request for a specific error code is here), to rehearse that path deterministically, for example (hypothetical):

-- hypothetical, not implemented
WITH throwIfProfileRestrictionReached(241, 'custom message for MEMORY_LIMIT_EXCEEDED')
INSERT INTO target
SELECT toStartOfHour(ts), stddevSamp(v), sum(w)
FROM source
GROUP BY 1
SETTINGS max_memory_usage = 1000;

On 26.4 there is no throwError function (SELECT throwError(999) is an unknown function), and the nearest tool is throwIf with a custom error code (see Lesser-known ClickHouse functions). The more faithful way to rehearse the failure is to cause the real one: a tiny limit makes the real error happen mid-query, the same way production would.

SELECT sum(number) FROM numbers(10000000) GROUP BY number % 100000
SETTINGS max_memory_usage = 1000;
-- Code: 241. DB::Exception: Query memory limit exceeded ... (MEMORY_LIMIT_EXCEEDED)

That gives your loader a genuine 241 after the query has started and begun writing, which is what the test wants. What the wish adds is precision (which limit, what message, after how many rows), and that is still missing.

Global variables

A place to store values that every query can read. ClickHouse has no user variables across sessions, but a view of constants gets there; see Server-wide constants with a view.

Still open

Remote databases and remote tables

-- hypothetical, not implemented
CREATE DATABASE my_remote ENGINE = RemoteDatabase('host:9000', 'user', '<password>', 'database');
SHOW TABLES IN my_remote;
CREATE TABLE my_remote_table ENGINE = RemoteTable('host:9000', 'user', '<password>', 'database.table');

ClickHouse can mount a whole PostgreSQL or MySQL database as a database engine, and read any ClickHouse table with the remote() table function (a snippet). What it cannot do is mount a remote ClickHouse database: there is no such engine on 26.4. The asymmetry is the wish: if the engine can show a PostgreSQL schema as local tables, it should be able to show a ClickHouse one.

Merge policy as a first-class object

For some tables, “eventually merged” is not good enough. A summing table that is queried constantly is correct only after merges, so reads must aggregate. In a workload where each insert touches only the last few partitions, merging the new part into the existing ones is cheap, and the system could do it as part of the insert:

-- hypothetical, not implemented
CREATE MERGE POLICY always_merged AS merge_order = 'before_insert';

CREATE TABLE totals (...) ENGINE = SummingMergeTree SETTINGS merge_policy = 'always_merged';

On 26.4 you can only tune the existing selector (some of those settings are marked Experimental in system.merge_tree_settings); you cannot declare a policy like this.

Housekeeping

• SHOW CREATE TABLE still prints SETTINGS index_granularity = 8192, the default, on tables that never set it. It is noise in every diff of generated DDL. Hiding settings equal to defaults would make schema comparison nicer.
• A one-call table summary. A PEEK or SHOW TABLE that gives engine, size, rows, parts, keys and comment in one result, instead of a trip to five system tables.
• Typo-aware DDL. If a setting is almost spelled right, ClickHouse could say so and, in a safe non-destructive context, apply the obvious correction behind a flag.

Wishes that became issues

Several items above, and many smaller ones, were filed on GitHub and have their own write-ups with the maintainers’ answers: UPSERT on MergeTree, CREATE OR REPLACE for tables, an alias for a table, DESCRIBE as a subquery, query tags in the query log and the UDF scope question. This page is the list; those pages are the outcomes.

How to use a list like this

Check it against the current release before you write an issue (the first third of this one has since been answered). Phrase each item as a use case and a failure, not as syntax: the maintainers can find a better syntax than yours, but they cannot guess your problem.