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
which returns the create query that could be used to create the view:
SHOW CREATE VIEW <view_name>;
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.