ClickHouse DB::Exception: Query is not supported in materialized view

The "DB::Exception: Query is not supported in materialized view" error in ClickHouse occurs when a CREATE MATERIALIZED VIEW statement contains a query type that cannot be used in a materialized view definition. The QUERY_IS_NOT_SUPPORTED_IN_MATERIALIZED_VIEW error code is raised at DDL time, preventing the view from being created.

Impact

The materialized view is not created. No data processing pipeline is established. This blocks the setup of real-time aggregation or transformation workflows that depend on materialized views. The error occurs at creation time, so no data is lost or corrupted.

Common Causes

1. Using UNION / UNION ALL in the materialized view's SELECT — historically the most common trigger. Older ClickHouse versions reject set operations in an incremental MV definition (support was added in more recent versions).
2. Using other set operations such as INTERSECT or EXCEPT in the MV definition
3. Attempting to use INSERT INTO ... SELECT as the MV definition rather than a plain SELECT
4. Query forms that cannot be evaluated in the incremental, per-insert-block model of materialized views

Note: ORDER BY and LIMIT in the inner SELECT are generally accepted by ClickHouse (they are applied per inserted block and are usually meaningless in an incremental MV) — they typically do not raise this specific error. The error is mainly associated with unsupported query structures like UNION.

Troubleshooting and Resolution Steps

1. Review the error message to identify which specific clause or query feature is unsupported:

   DB::Exception: Query is not supported in materialized view: <specific detail>
   

2. Remove the unsupported query structure from the MV definition. For example, replace a UNION ALL with separate materialized views that write into the same target table:

   -- This may fail on older versions:
   CREATE MATERIALIZED VIEW mv TO target AS
   SELECT a AS x FROM source_a
   UNION ALL
   SELECT b AS x FROM source_b;
   
   -- Instead, create one MV per branch into a shared target table:
   CREATE MATERIALIZED VIEW mv_a TO target AS SELECT a AS x FROM source_a;
   CREATE MATERIALIZED VIEW mv_b TO target AS SELECT b AS x FROM source_b;
   

3. For aggregations, use an aggregating target engine and keep the inner SELECT simple, deferring ordering and limiting to query time:

   CREATE MATERIALIZED VIEW mv
   ENGINE = AggregatingMergeTree()
   ORDER BY col1
   AS SELECT col1, countState() AS cnt
   FROM source_table
   GROUP BY col1;
   
   -- Query the MV with ORDER BY and LIMIT at read time
   SELECT col1, countMerge(cnt) AS cnt
   FROM mv
   GROUP BY col1
   ORDER BY cnt DESC
   LIMIT 100;
   

4. If you need window functions, compute them at query time against the MV's stored aggregates rather than in the MV definition.

5. For complex transformations not supported in MVs, consider using a two-stage approach:

   -- Stage 1: Simple MV for data capture
   CREATE MATERIALIZED VIEW mv_stage1
   ENGINE = MergeTree() ORDER BY (timestamp)
   AS SELECT * FROM source_table;
   
   -- Stage 2: Query with complex logic
   CREATE VIEW mv_stage2 AS
   SELECT *, row_number() OVER (PARTITION BY group_col ORDER BY timestamp) AS rn
   FROM mv_stage1;
   

6. Check ClickHouse documentation for the specific version you are running, as supported features in MVs have expanded over time.

Best Practices

• Keep materialized view definitions as simple as possible: filtering, aggregation, and column selection work well; complex operations should be deferred to query time.
• Use AggregatingMergeTree or SummingMergeTree as the target engine for MVs that perform aggregations.
• Define ordering on the target table, not in the MV's SELECT query.
• Test MV definitions in a development environment before deploying to production.
• Use regular views for complex query logic that includes window functions, ORDER BY, or LIMIT.
• When migrating from other databases, remember that ClickHouse MVs are incremental (triggered on INSERT), not periodically refreshed snapshots.

Frequently Asked Questions

Q: Can I use JOINs in a materialized view?
A: Yes, ClickHouse supports JOINs in MV definitions, but with limitations. The MV is triggered only by inserts into the left-hand table of the JOIN. The right-hand table is read at insert time, so it must be relatively small or the join must be efficient. Dictionary joins are often preferred.

Q: What about refreshable materialized views?
A: ClickHouse supports refreshable materialized views (using CREATE MATERIALIZED VIEW ... REFRESH EVERY ...) which execute the full query periodically. These support a broader set of query features since they are not incremental. This can be a good alternative if you need ORDER BY, LIMIT, or window functions in the view.

Q: Can I ALTER a materialized view to fix its definition?
A: You cannot directly alter the SELECT query of a materialized view. You need to drop it and recreate it with the corrected definition. If you need to preserve the existing data, first detach the view, rename the underlying table, create the new view, and then migrate the data.