Getting Query Backing a View in ClickHouse

Recently I inherited a ClickHouse cluster and needed to look into the query backing a materialized view. Since the view wasn't defined in the code I had to obtain it from ClickHouse itself. However I wasn't able to find in the ClickHouse cloud UI and a quick google search wasn't helpful either.

Then I remembered that relational databases hold that information inside the information schema tables, so I checked to see if something like that is available in ClickHouse. Indeed it is, and here's the query to get the query behind a view:

  table_schema, table_name, view_definition

UPDATE: There is an even better way to do what we want by using SHOW CREATE, which returns the create query that could be used to create the view:

SHOW CREATE VIEW <view_name>;

If you've come this far with the article you may want to know a thing or two about me if you don't already. You can also read other blog posts or about stuff I've learned recently.

This website is open source. If you've come across a mistake please let me know there. For other types of feedback you can reach out to me through email or social-media.