ClickHouse DB::Exception: Query not supported in window view

The "DB::Exception: Query not supported in window view" error in ClickHouse is raised when you attempt to use a query construct that is incompatible with a window view definition. The error code is QUERY_IS_NOT_SUPPORTED_IN_WINDOW_VIEW. Window views in ClickHouse provide time-based windowing for streaming aggregation, and they impose restrictions on the kinds of queries and operations that can be expressed within them.

Impact

This error blocks the creation or execution of the window view. Consequences include:

• Inability to create a window view with the desired query logic
• Streaming aggregation pipelines that depend on the window view will not function
• Development delays while restructuring queries to fit within window view constraints

Common Causes

1. Using unsupported JOIN types -- Window views have limited support for joins. Complex multi-table joins or certain join algorithms may not be allowed.
2. Subqueries in the window view definition -- Nested subqueries or CTEs within the window view's inner query are generally not supported.
3. Non-aggregating queries -- Window views are designed for aggregation over time windows. Queries without GROUP BY or aggregate functions may be rejected.
4. Multiple window functions -- Defining more than one tumble or hop window in a single view is not supported.
5. Unsupported SQL clauses -- Using HAVING, ORDER BY, LIMIT, or DISTINCT within the window view inner query can trigger this error.
6. Incorrect window function usage -- Using tumble() or hop() with wrong arguments, or using regular window functions (like row_number() OVER) instead of the streaming window functions.

Troubleshooting and Resolution Steps

1. Review the window view syntax: A valid window view typically looks like:

   CREATE WINDOW VIEW wv TO destination_table
   AS SELECT
       tumble(timestamp_col, INTERVAL 5 MINUTE) AS window_id,
       count() AS cnt,
       sum(value) AS total
   FROM source_table
   GROUP BY
       tumble(timestamp_col, INTERVAL 5 MINUTE);
   

   Ensure your query follows this pattern -- a SELECT with aggregate functions and a GROUP BY that includes the window function.

2. Remove unsupported clauses: Strip out any HAVING, ORDER BY, LIMIT, or DISTINCT clauses from the inner query:

   -- This will likely fail:
   CREATE WINDOW VIEW wv TO dest
   AS SELECT tumble(ts, INTERVAL 1 MINUTE) AS w, count() AS c
   FROM src
   GROUP BY w
   HAVING c > 10  -- not supported
   ORDER BY c;    -- not supported
   

   Move filtering logic to a downstream materialized view or the destination table instead.

3. Simplify joins: If your query includes joins, try restructuring so the window view reads from a single source table. Pre-join the data using a separate materialized view:

   -- Pre-join into an intermediate table
   CREATE MATERIALIZED VIEW enriched TO enriched_table
   AS SELECT a.ts, a.value, b.category
   FROM events a
   JOIN dim_categories b ON a.cat_id = b.id;
   
   -- Then build the window view on the intermediate table
   CREATE WINDOW VIEW wv TO dest
   AS SELECT tumble(ts, INTERVAL 5 MINUTE) AS w, category, sum(value) AS total
   FROM enriched_table
   GROUP BY tumble(ts, INTERVAL 5 MINUTE), category;
   

4. Ensure you are using the correct window function: Window views require tumble() or hop() -- not SQL window functions:

   -- Wrong: SQL window function
   CREATE WINDOW VIEW wv TO dest
   AS SELECT row_number() OVER (PARTITION BY ...) ...;
   
   -- Right: streaming window function
   CREATE WINDOW VIEW wv TO dest
   AS SELECT tumble(ts, INTERVAL 10 MINUTE) AS w, count() AS c
   FROM src GROUP BY w;
   

5. Check ClickHouse version compatibility: Window views are an experimental feature. Ensure your ClickHouse version supports the specific syntax you are using:

   SELECT version();
   

   Also verify that the experimental feature is enabled:

   SET allow_experimental_window_view = 1;
   

Best Practices

• Keep window view inner queries as simple as possible -- aggregate from a single table, group by the window function and any necessary dimensions.
• Push complex transformations (joins, enrichment, filtering) into upstream materialized views that feed into the window view's source table.
• Use tumble() for fixed non-overlapping windows and hop() for sliding windows, and avoid mixing them in a single view.
• Always test window view definitions in a development environment before deploying to production, since the feature is experimental and behavior may change between versions.
• Monitor the system.query_log for errors related to window views to catch issues early in your pipeline.

Frequently Asked Questions

Q: Are window views stable in ClickHouse?
A: Window views are an experimental feature. You need to enable them with SET allow_experimental_window_view = 1. Their behavior and supported syntax may change across ClickHouse releases.

Q: Can I use a window view with Kafka engine tables?
A: Yes, window views can read from Kafka engine tables, which is one of their primary streaming use cases. The source table can be a Kafka table, and the window view aggregates the incoming stream into time-based windows.

Q: What is the difference between a window view and a materialized view with toStartOfInterval?
A: A materialized view with toStartOfInterval() processes each inserted batch independently. A window view maintains state and emits results only when the time window closes, which is more suitable for true streaming aggregation with watermark-based triggering.

Q: Can I ALTER a window view to change the query?
A: No. You need to drop and recreate the window view to change its inner query definition. Plan your schema carefully before creating window views in production.