Materialized view invalidated and cannot remember original SQL

Question

I have a materialized view named "hourly_metrics" on my table. It is a straightforward view that stores max, min, avg, stdedev and so on at hourly intervals. The thing here is that I also had some CASE statements to pivot some data and I didn't store the SQL for my view.

It was all working perfectly, but I truncated some data from my base table and now the materialized view is not refreshing anymore. The web console shows the view is invalidated due to table truncate.

I was thinking I could just drop the materialize view and recreate it, but since I don't have the original SQL, I would like to know if there is some way to retrieve it, as I want to make sure I have all the CASE conditions right.

Answer 1

The good news is that you probably don't need to drop and re-create the view. It is very likely you can just do

REFRESH MATERIALIZE VIEW hourly_metrics FULL 

This will cause the view to reload all the data and should make it valid again.

In some cases, like for example when the structure of the base table changes, you might have to re-create the view. You can get the original SQL with

materialized_views(); 

Or

SHOW CREATE MATERIALIZE VIEW hourly_metrics; 

Or just by right clicking on the name of the view on the web console and selecting copy schema.

You would need to re-write whichever part of the query is not compatible with the new structure, and just drop and create the view again.