Creating a view table with intermediate view tables I want to drop

user_19668618 0
Viewed 57

In a SQL notebook, I have created a bunch of view tables which involves merging/joining to ultimately create one combined/merged table. I don't want to keep all of these intermediate tables in the database, is there a way to assign a variable to these intermediate view tables (table 1,table2, etc.) I created? Given example with "Combined_table" as the final output:

CREATE or REPLACE VIEW database.table1 as select ... from...left join on...;

CREATE or REPLACE VIEW database.table2 as select ...from database.table1...left join on...;

CREATE or REPLACE VIEW database.table3 as select ...from database.table1...left join on...;

CREATE or REPLACE VIEW database.Combined_table as select table2.field1 table2.field2 table3.field1 from database.table4 left join table2 on... left join table3 on...

Hopefully you get the idea. Is there a more efficient way to do this/pass a variable to the intermediate tables?

databricks-sql azure-databricks databricks
1 Answers

You can use temporary views.

CREATE TEMPORARY view_name AS query

TEMPORARY views are session-scoped and is dropped when session ends because it skips persisting the definition in the underlying metastore, if any.

Like a normal view it is only a metadata object, ie. does not materialize data. So depending on the complexity of the queries it might not be the best solution. However in a lot of cases - like yours when every such interim view is only used once - it's enough and works just fine. Try it out to see.

user_28900930
Related