How to create temporary table in databricks

[mano7438]

Hi Team,

I have a requirement where I need to create temporary table not temporary view.

Can you tell me how to create temporary table in data bricks ?

[StephanieAlba]

Can you share why you want a table instead of a view? I use createOrReplaceGlobalTempView if I need my temp view in other notebooks.

https://spark.apache.org/docs/3.1.1/api/python/reference/api/pyspark.sql.DataFrame.createGlobalTempV...

[Raja_DBA]

When there is a complex logic which takes long time to process using TEMP views for further processing will take processing time of the calling script + the time of the TEMP view which is not optimal in DBMS. SAme effect or slower than writing the temp view code as a sub query....

If it is a TEMP TABLE then the data can be reused as much as required and will be auto discarded at end of session.

[NandiniN]

Hi mano7438,

CREATE TEMPORARY TABLE my_table
USING <CSV>
OPTIONS (
  path 'dbfs:/<path-to-your-file>/file.tsv',
  delimiter '\t',
  header 'true'
)

or 

CREATE TEMPORARY TABLE temp_table (id int) using parquet;

Note: CREATE TEMPORARY TABLE without a provider is not allowed.

Thanks!

[Raja_DBA]

Thank you for the syntax.
How do I insert data into this created temp table. When I use SQL syntax it gives me an error message..

AnalysisException: Can only write data to relations with a single path.

[NandiniN]

I just learnt, the above is a LEGACY support and hence must not be used. This isn't supported syntax, so there would be a lot of restrictions on the usage of this.

Internally it is just a view and hence we should go for create temp view instead. 

I know your question was sometime back(so for newer visits to this query), I would like to understand, are you wanting to migrate and it blocks you somehow?

[abueno · Accepted solution]

This worked for me.  using "TABLE" was restricted for me.

CREATE or replace TEMPORARY VIEW details AS ...

[NandiniN]

I see, thanks for sharing, can you mark the solution which worked for you @abueno as Accepted.