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...

[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!

[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]

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.