topic Re: Temp Table Vs Temp View Vs temp table function- which one is better for large Databrick data pro in Data Engineering

Temp Table Vs Temp View Vs temp table function- which one is better for large Databrick data processing

Abhot — Mon, 22 May 2023 20:23:43 GMT

Hello ,

1 ) Which one is better during large data processing - Temp table vs Temporary view vs temp Table function .

2) How lazy evaluation better for processing ? and which one of the above helps in lazy evaluation

Re: Temp Table Vs Temp View Vs temp table function- which one is better for large Databrick data pro

Abhot — Mon, 29 Jul 2024 13:47:11 GMT

Does anyone have any suggestions regarding the question above?

Re: Temp Table Vs Temp View Vs temp table function- which one is better for large Databrick data pro

szymon_dybczak — Mon, 29 Jul 2024 14:09:09 GMT

Hi @Abhot ,

It depends, if you're going to use it multiple times in your processing then creating temp view will be slower due to lazy evaluation (unless you persist that view (dataframe cache)).

Every time your temp view is called, it will execute the view definition/code. This also depends upon your cluster capacity, how complex the view definition is, how much data the temp view has.

So every time you use view name it will be recomputed. In such scenarios it's better to use temporary table, especially if transformantion are complex.

Re: Temp Table Vs Temp View Vs temp table function- which one is better for large Databrick data pro

Abhot — Mon, 29 Jul 2024 18:31:10 GMT

What about Temporary view vs Temporary function as both are lazy evaluations. Any preference of when to use view vs function. Usually UDFs are not recommended in where clause. Does this still holds true in Databricks ?

Re: Temp Table Vs Temp View Vs temp table function- which one is better for large Databrick data pro

szymon_dybczak — Mon, 29 Jul 2024 18:35:49 GMT

Most often it's better to use temp view over UDFS. UDFs might introduce significant processing bottlenecks into code execution. Databricks uses a number of different optimizers automatically for code written with included Apache Spark, SQL, and Delta Lake syntax. When custom logic is introduced by UDFs, these optimizers do not have the ability to efficiently plan tasks around this custom logic. In addition, logic that executes outside the JVM has additional costs around data serialization.