topic Re: How to create temporary table in databricks in Get Started Discussions

How to create temporary table in databricks

mano7438 — Mon, 28 Aug 2023 07:17:49 GMT

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 ?

Re: How to create temporary table in databricks

StephanieAlba — Mon, 05 Feb 2024 19:13:37 GMT

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

Re: How to create temporary table in databricks

NandiniN — Wed, 01 May 2024 04:14:20 GMT

Hi mano7438,

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

CREATE TEMPORARY TABLE temp_table (id int) using parquet;

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

Thanks!

Re: How to create temporary table in databricks

NandiniN — Wed, 01 May 2024 05:12:43 GMT

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?

Re: How to create temporary table in databricks

abueno — Fri, 02 Aug 2024 19:22:02 GMT

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

CREATE or replace TEMPORARY VIEW details AS ...

Re: How to create temporary table in databricks

NandiniN — Sat, 03 Aug 2024 00:03:35 GMT

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

Re: How to create temporary table in databricks

Raja_DBA — Fri, 01 Aug 2025 16:53:30 GMT

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.

Re: How to create temporary table in databricks

Raja_DBA — Fri, 01 Aug 2025 17:04:24 GMT

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.