Asking for tags on every logged query

system.query_log answers “what ran, how long, how much memory” very well. It answers “on behalf of whom” poorly. The identity ClickHouse authenticates is the database user, and that is rarely the identity you care about. A web application connects as one account, app_reader, and behind it sit thousands of end users, dozens of dashboards and a handful of background jobs. When the cluster is slow, the question is never “which account?” but “which dashboard, which user segment, which job?”

I filed #36218 in April 2022 after seeing the same workaround in several unrelated places: teams write a middleware that copies system.query_log into a table of their own with extra columns (end_user, segment, feature) and build their load analysis there. When that pattern keeps being rebuilt, something belongs in the product. I asked for a tags Map(String, String) column, filled from something the client sends with the query, so the question becomes:

SELECT tags['dashboard'] AS dashboard, count() AS queries, sum(query_duration_ms) AS total_ms
FROM system.query_log
WHERE type = 'QueryFinish'
GROUP BY dashboard
ORDER BY total_ms DESC;

I also floated making it JSON rather than Map, with the caveat that I might be asking too much.

What the tracker holds

Very little, and that is the finding. The issue carries the labels feature, minor (priority), comp-system-table and external (the author is not a ClickHouse organization member). There is one reply, from UnamedRus the next day, and my answer a day after that. No maintainer has commented, no PR references it, and I found no other issue or PR about query tags in query_log when I searched the repository for “query tags”, “query_log tags” and “log_comment”. It is open, undecided, four years on. Anything below about why is therefore my analysis, not the maintainers’ reasoning, and I mark it as such.

UnamedRus suggested a trick: put JSON into log_comment and add a materialized column over it,

ALTER TABLE system.query_log ADD COLUMN tags Object('JSON') MATERIALIZED log_comment;

I called it “a really good hack” and “still a workaround”, which is the whole disagreement in a sentence. It deserves a longer look than I gave it then, because both halves of it have changed.

What the table already has

From the query_log documentation and QueryLog.cpp, the columns that say something about who:

Column	Where the value comes from	Granularity
user, initial_user	authentication	one per database account
quota_key	the client, when a quota is keyed	one string
client_name, interface	the client library	per tool
http_user_agent, http_referer, forwarded_for	HTTP headers	per HTTP client
log_comment	the log_comment setting	any string up to max_query_size (262144 here)
Settings	settings changed for this query	Map(LowCardinality(String), LowCardinality(String))

Only the first row is identity the server vouches for. Everything else is a claim made by the client. That distinction is the first real design tension in the request: a tags column would be the most flexible and the least trustworthy column in a table people use for chargeback and incident forensics.

The second is that Settings already is a per-query Map. Every setting changed for a query is logged there (log_query_settings defaults to 1; I checked on 26.4). A client can already attach structure through a channel the server parses, transports on every protocol and, for settings, can constrain. Custom settings are the intended way to carry your own keys, but they are off by default; on 26.4 without the server option:

Code: 115. DB::Exception: Unknown setting 'SQL_dashboard'. (UNKNOWN_SETTING)

They are enabled with the server option custom_settings_prefixes (for example SQL_), which is a server configuration change, not a code change. I could not enable it on clickhouse local, and I did not run a server for this page, so I cannot show you the resulting Settings map. The code that fills the column walks the query’s settings object, so I expect custom ones to appear; treat that as untested.

Alternatives, and what each costs

log_comment, set per session or per query. It is an ordinary setting, so every client can set it and the query can carry it inline:

SET log_comment = '{"app":"dash","user":"u42","n":7}';
SELECT getSetting('log_comment') AS lc, JSONExtract(lc, 'Map(String,String)') AS tags FORMAT Vertical;

Row 1:
──────
lc:   {"app":"dash","user":"u42","n":7}
tags: {'app':'dash','user':'u42','n':'7'}

That is the Map I asked for, made at read time, with numbers stringified. Two properties matter for dashboards that mix comments: a comment that is not JSON does not error, it yields an empty map (JSONExtract('just a comment', 'Map(String,String)') returned {}), and nested values come back as raw JSON text ({"a":{"b":1},"c":[1,2]} gives a -> {"b":1}, c -> [1,2]). Anything else that writes log_comment for its own reasons, such as the test suites that use it to find their own queries, silently drops out of the grouping.

If you do not want JSON, extractKeyValuePairs parses a flat convention. The default key-value delimiter is a colon, and the delimiter is a parameter:

SELECT extractKeyValuePairs('job:nightly team:data') AS tags, tags['team'];
SELECT extractKeyValuePairs('job=nightly team=data', '=') AS tags;

{'job':'nightly','team':'data'}	data
{'job':'nightly','team':'data'}

('job=nightly team=data' with no delimiter argument gave {}, so that one is easy to get wrong.) The advantage over JSON is a shorter string and no quoting; the cost is that the format is yours to defend.

A materialized column on the log. This is UnamedRus’s trick, and the original no longer runs: on 26.4 the type is gone (Unknown data type family: Object. (UNKNOWN_TYPE)). The modern versions work, shown here on a stand-in table with the system.query_log columns that matter, since clickhouse local has no query_log:

CREATE TABLE ql_demo (type String, query_duration_ms UInt64, log_comment String,
  tags Map(String, String) MATERIALIZED JSONExtract(log_comment, 'Map(String,String)'))
ENGINE = MergeTree ORDER BY tuple();
INSERT INTO ql_demo (type, query_duration_ms, log_comment) VALUES
 ('QueryFinish', 120, '{"dashboard":"sales","user":"u1"}'), ('QueryFinish', 900, '{"dashboard":"sales","user":"u2"}'),
 ('QueryFinish', 40, '{"dashboard":"ops","user":"u1"}'), ('QueryFinish', 15, ''), ('ExceptionBeforeStart', 0, 'free text');

SELECT tags['dashboard'] AS dashboard, count() AS queries, sum(query_duration_ms) AS total_ms
FROM ql_demo WHERE type = 'QueryFinish' GROUP BY dashboard ORDER BY total_ms DESC;

sales	2	1020
ops	1	40
	1	15

The same works with a JSON column (tags JSON MATERIALIZED log_comment::JSON, then tags.dashboard), and ALTER TABLE ... ADD COLUMN ... MATERIALIZED over existing rows evaluates on read. Note the empty-key group: untagged queries are in the result, and you will want WHERE dashboard != ''.

The problem is not the SQL, it is where you run it. system.query_log is not your table. SystemLog::prepareTable compares the table’s CREATE statement with the one the server expects, and when they differ it logs that the table “has obsolete or different structure” and renames the existing one to query_log_0 (then _1, …), creating a fresh, standard table. Reading the source, an ALTER on a system log therefore looks like it lasts until the next time the server prepares the table, after which your column lives on in a rotated table and the live one has no tags. I did not run a server to watch this happen, so check it on a test node before relying on either reading. The robust alternative is to leave the table alone and put the extraction in a view:

CREATE VIEW query_log_tagged AS
SELECT *, JSONExtract(log_comment, 'Map(String,String)') AS tags FROM system.query_log;

I only syntax-checked that statement with clickhouse format, since it needs a server. A view survives rotation. It pays the JSON parse on every read rather than once at insert, which for the volumes in query_log rarely matters.

query_id conventions. The client picks the id, so dash-sales-<uuid> is searchable with startsWith. But the id is also the handle other features use to find and kill a query (the replace_running_query setting, default 0, exists because ids collide), so the tag lives inside a value that has other jobs, and a prefix carries one dimension at most.

Pinning a tag server-side. For the dimension you can trust, bind it to the account and lock it, so the client cannot rewrite it. Syntax-checked with clickhouse format, not run:

CREATE USER app_reader SETTINGS log_comment = 'app_reader' CONST;

That works per account, which is exactly the granularity the request was trying to get past. See Users, roles and settings profiles for CONST.

Why I think it stalled, and why that is defensible

This section is opinion. A tags column asks the server to take a position on three things it currently avoids. It would fix a schema contract in a table whose schema the server owns and rotates. It would put an unbounded, client-controlled Map on the write path of every query, where today the cost is one string. And it would need a transport: a new client option, or an HTTP header, or an overloaded log_comment, each of which has to work on the native protocol, HTTP and the Postgres/MySQL wire interfaces. Against that, the existing log_comment already crosses every protocol, and SQL can turn a string into a map at read time. A minor priority label is consistent with “there is a workable path already”.

The counter-argument, which is why I still care, is the one in the first paragraph: a workaround that every team re-derives is a missing convention. Today nothing documents a shared tags name, a recommended encoding for log_comment, or that custom settings are the structured option. The cheapest useful step would be documentation, not a column.

Where it stands

Status on 2026-10-14: open, no maintainer decision, no linked work. On 26.4 the pieces to build your own are better than in 2022: JSON and Map are first-class, JSONExtract and extractKeyValuePairs do the parsing, and log_comment works on every client. What is still missing is the part I asked for, a documented column so tools and saved queries share one name. For the JSON side of the workaround see Working with JSON in ClickHouse, and for what to do with the attributed data once you have it, Tracking query and resource trends and Most resource-intensive queries.

The pattern is general. ClickHouse tends to expose a primitive (a string setting) and leave structure to SQL at read time, and system tables are a contract the server controls, not a place to extend. A feature request that wants to turn a convention into a schema runs into both at once.