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 ?

[Kaniz_Fatma]

Hi @mano7438, In Databricks, you can create a temporary table using the CREATE TEMPORARY TABLE statement. However, this functionality is only available in Delta Live Tables.

Sources:
- [Docs: publish](https://docs.databricks.com/delta-live-tables/publish.html)
- [Docs: sql-ref](https://docs.databricks.com/delta-live-tables/sql-ref.html)

[StephanieRivera]

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?