SELECT from VIEW to CREATE a table or view

[TamD]

Hi; I'm new to Databricks, so apologies if this is a dumb question.

I have a notebook with SQL cells that are selecting data from various Delta tables into temporary views.  Then I have a query that joins up the data from these temporary views.

I'd like to use that query to populate a materialized view, however, the CREATE statement throws an error complaining that the temp views cannot be found.  This is course rubbish, because it finds the views just fine when it's just a SELECT.  The error only happens when the CREATE AS is appended to the front.

So,

"SELECT foo, bar FROM viewa" --  works fine. 

"CREATE OR REPLACE MATERIALIZED VIEW matview AS SELECT foo, bar FROM viewa" -- says viewa cannot be found.

I just ran this with CREATE OR REPLACE TABLE instead, and that worked.  So I guess this is a limitation of materialized views?  If so,  1) can this limitation be noted in the mat view documentation, and b) can the error message be corrected to explain that?

[Akshay_Petkar]

Hi @TamD 

You cannot create a materialized view on a temporary view because temporary views are session-based. Once the session expires, the temporary view will be dropped. To resolve this, you need to create either a regular view or a table, and then you can successfully create a materialized view.
The best practice is to create a materialized view directly on a table.

[TamD]

Hi Akshay

Yes, the temporary view exists for the duration of the session.  The table in my example above is loaded in the same session as the temp views and that works fine.  The materialized view is also loaded in the same session as the temp views, only the mat view creation "cannot find" the temp views.  

It makes sense that a VIEW would not work created against temporary views, however the data in a materialized view is persisted and does not change until the notebook is run again to refresh the data:  once the data is loaded, the materialized view has no dependency on the temp views used to load it until the notebook is run again (and the temp views are recreated).  Therefore, I don't see a reason why a table load would work and a mat view load would not.

[Akshay_Petkar]

Hi @TamD 

A materialized view looks for a permanent table. In your case, since the temporary view used to load the data is deleted after the session expires, the materialized view cannot find it when the session ends. For example, if you create a materialized view based on a temporary view and then refresh it daily, the refresh will fail if the temporary view is no longer available. This is why a materialized view requires a permanent table or view.