Blog· Tested on ClickHouse 26.4

Which materialized view wrote this row?

When several materialized views feed one table, nothing tells you which one produced a row. I asked for a _view virtual column; the request is open, and the workaround is a literal.

Source: github.com

When several materialized views write into one target table, a row does not say which view produced it. That hurts when debugging odd values, monitoring per-view throughput, reprocessing one bad source, or auditing a number. I asked for a _view virtual column, in the family of _table and _part, that resolves inside a view’s SELECT to the running view’s name.

Nothing so far. The issue is open with the labels feature, comp-materialized-view and external, no comments and no linked pull request.

On 26.4 using it in a view’s SELECT is rejected at CREATE time:

Code: 47. DB::Exception: Unknown expression identifier `_view` ... (UNKNOWN_IDENTIFIER)

The workaround is a source_view String column on the target and a literal in each view:

CREATE MATERIALIZED VIEW mv_cpu TO metrics_all AS
SELECT ts, sensor_id, metric, value, 'mv_cpu' AS source_view
FROM metrics WHERE metric = 'cpu';

It works, but it is manual: a view copy-pasted from another keeps the old string. On a server, system.query_views_log records what each view did per insert, which helps monitoring but not the provenance of an individual row. Basics of fan-in are in materialized views basics.

feature-requestmaterialized-viewsvirtual-columns

Discussion

Comments live in GitHub Discussions. Sign in with GitHub to join in.