Skip to main content
    • Knowledge Base/
    How To Display Queries Using MV

    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