Nashpush

clickhouse-materialized-structure

2 min read

All tables

The following query retrieves all materialized views in the ClickHouse database along with their target and source tables:

SELECT
    mv.database AS view_database,
    mv.name AS view_name,
    extract(mv.create_table_query, 'FROM\\s+([a-zA-Z0-9_\\.]+)') AS source_table_name,
    st.engine AS source_table_engine,
    extract(mv.create_table_query, 'TO\\s+([a-zA-Z0-9_\\.]+)') AS target_table
FROM
    system.tables AS mv
LEFT JOIN
    system.tables AS st
ON
    extract(mv.create_table_query, 'FROM\\s+([a-zA-Z0-9_\\.]+)') = concat(st.database, '.', st.name)
WHERE
    mv.engine = 'MaterializedView'
    AND mv.create_table_query LIKE '% TO %'
    AND mv.create_table_query LIKE '% FROM %'
ORDER BY
    view_database,
    view_name;

Exclude Kafka source tables

To exclude materialized views that populate data from Kafka streams, you can modify the query as follows:

SELECT
    mv.database AS view_database,
    mv.name AS view_name,
    extract(mv.create_table_query, 'FROM\\s+([a-zA-Z0-9_\\.]+)') AS source_table_name,
    st.engine AS source_table_engine,
    extract(mv.create_table_query, 'TO\\s+([a-zA-Z0-9_\\.]+)') AS target_table
FROM
    system.tables AS mv
LEFT JOIN
    system.tables AS st
ON
    extract(mv.create_table_query, 'FROM\\s+([a-zA-Z0-9_\\.]+)') = concat(st.database, '.', st.name)
WHERE
    mv.engine = 'MaterializedView'
    AND mv.create_table_query LIKE '% TO %'
    AND mv.create_table_query LIKE '% FROM %'
    AND st.engine != 'Kafka'
ORDER BY
    view_database,
    view_name;

Kafka source tables only

SELECT
    mv.database AS view_database,
    mv.name AS view_name,
    extract(mv.create_table_query, 'FROM\\s+([a-zA-Z0-9_\\.]+)') AS source_table_name,
    st.engine AS source_table_engine,
    extract(mv.create_table_query, 'TO\\s+([a-zA-Z0-9_\\.]+)') AS target_table
FROM
    system.tables AS mv
LEFT JOIN
    system.tables AS st
ON
    extract(mv.create_table_query, 'FROM\\s+([a-zA-Z0-9_\\.]+)') = concat(st.database, '.', st.name)
WHERE
    mv.engine = 'MaterializedView'
    AND mv.create_table_query LIKE '% TO %'
    AND mv.create_table_query LIKE '% FROM %'
    AND st.engine = 'Kafka'
ORDER BY
    view_database,
    view_name;

Graph View

Backlinks