[SPARK-32063] Spark native temporary table

• Type: New Feature
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 3.1.0
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Lantao Jin
• Created: 23/Jun/20 02:28
• Updated: 16/Nov/20 06:39

Description

Many databases and data warehouse SQL engines support temporary tables. A temporary table, as its named implied, is a short-lived table that its life will be only for current session.

In Spark, there is no temporary table. the DDL “CREATE TEMPORARY TABLE AS SELECT” will create a temporary view. A temporary view is totally different with a temporary table. 

A temporary view is just a VIEW. It doesn’t materialize data in storage. So it has below shortage:

1. View will not give improved performance. Materialize intermediate data in temporary tables for a complex query will accurate queries, especially in an ETL pipeline.
2. View which calls other views can cause severe performance issues. Even, executing a very complex view may fail in Spark. 
3. Temporary view has no database namespace. In some complex ETL pipelines or data warehouse applications, without database prefix is not convenient. It needs some tables which only used in current session.

 

More details are described in Design Docs

Issue Links

• relates to: SPARK-33459 Commonly used Teradata extension syntax

Sub-Tasks

• Supporting create temporary table: In Progress
• Supporting analyze temporary table: Open
• Supporting create temporary table LIKE: Open