Skip to main content

How to Identify Queries Using Materialized Views in ClickHouse

Learn how to query ClickHouse logs to identify all queries involving Materialized Views within a specified time range.

Question​

How do I show all queries involving materialized views in the last 60m?

Answer​

This query will display all queries directed towards Materialized Views considering that:

  • we can leverage the create_table_query field in system.tables table to identify what tables are explicit (TO) recipient of MVs;
  • we can track back (using uuid and the name convention .inner_id.<uuid>) what tables are implicit recipient of MVs;

We can also configure how long back in time we want to look, by changing the value (60 m by default) in the initial query CTE

expected output:

In this example results above default.big_changes_mv and default.sum_of_volumes_mv are both materialized views.

· 2 min read