topic Re: dataframe takes unusually long time to save as a delta table using sql for a very small dataset in Data Engineering

dataframe takes unusually long time to save as a delta table using sql for a very small dataset with 30k rows. It takes around 2hrs. Is there a solution for this problem?

suresh1122 — Tue, 13 Dec 2022 06:14:09 GMT

I am trying to save a dataframe after a series of data manipulations using Udf functions to a delta table. I tried using this code

(

.write

.format('delta')

.mode('overwrite')

.option('overwriteSchema', 'true')

.saveAsTable('output_table')

)

but this is taking more than 2 hours. So I converted the dataframe into a sql local temp view and tried saving the df as a delta table from that temp view, this worked for one of the notebooks(14 minutes) but for other notebooks this is also taking around 2 hours to write to the delta table. Not sure why this is happening for a very small dataset. Any solution is appreciated.

code:

df.createOrReplaceTempView("sql_temp_view")

%sql

DROP TABLE IF EXISTS default.output_version_2;

create table default.output_version_2

select * from sql_temp_view

Re: dataframe takes unusually long time to save as a delta table using sql for a very small dataset with 30k rows. It takes around 2hrs. Is there a solution for this problem?

UmaMahesh1 — Tue, 13 Dec 2022 06:43:42 GMT

What is the cluster config you are using ? Also what sort of transformations are being done before your final dataframe is getting created ?

Re: dataframe takes unusually long time to save as a delta table using sql for a very small dataset with 30k rows. It takes around 2hrs. Is there a solution for this problem?

suresh1122 — Tue, 13 Dec 2022 06:59:30 GMT

This is the cluster config & transformations like data cleanup using filters and search operations using dictionaries

Re: dataframe takes unusually long time to save as a delta table using sql for a very small dataset with 30k rows. It takes around 2hrs. Is there a solution for this problem?

UmaMahesh1 — Tue, 13 Dec 2022 07:23:44 GMT

Can you also give the number of partitions the df has ?

you can use df.rdd.getNumPartitions()

Re: dataframe takes unusually long time to save as a delta table using sql for a very small dataset with 30k rows. It takes around 2hrs. Is there a solution for this problem?

Ajay-Pandey — Tue, 13 Dec 2022 07:31:25 GMT

Hi @Suresh Kakarlapudi what is your file size??

Re: dataframe takes unusually long time to save as a delta table using sql for a very small dataset with 30k rows. It takes around 2hrs. Is there a solution for this problem?

suresh1122 — Tue, 13 Dec 2022 07:45:40 GMT

96 partitions

Re: dataframe takes unusually long time to save as a delta table using sql for a very small dataset with 30k rows. It takes around 2hrs. Is there a solution for this problem?

suresh1122 — Tue, 13 Dec 2022 09:00:47 GMT

35 MB

Re: dataframe takes unusually long time to save as a delta table using sql for a very small dataset with 30k rows. It takes around 2hrs. Is there a solution for this problem?

UmaMahesh1 — Tue, 13 Dec 2022 10:23:26 GMT

Since data is too low, try repartitioning that data before you write using repartition or coalesce.

Re: dataframe takes unusually long time to save as a delta table using sql for a very small dataset with 30k rows. It takes around 2hrs. Is there a solution for this problem?

Sreekanth_N — Wed, 04 Jan 2023 09:40:16 GMT

I too have similar issue, the no.of partition is 1 at table level and transformation only appyling like date, decimal(20, 2)..etc using withColumn. 5 worker nodes.

1,80,890 records taking 10min time. - how to improve the performance and what are the possible ways to find where it is taking time ?

Re: dataframe takes unusually long time to save as a delta table using sql for a very small dataset with 30k rows. It takes around 2hrs. Is there a solution for this problem?

Jfoxyyc — Thu, 05 Jan 2023 05:15:30 GMT

Is your databricks workspace set up as vnet injection by any chance?

Re: dataframe takes unusually long time to save as a delta table using sql for a very small dataset

Fadhi — Wed, 23 Aug 2023 20:35:34 GMT

@Jfoxyyc i am having similar problem and cam across the post. Do vnet injection cause this as my workspace is set up like that

Re: dataframe takes unusually long time to save as a delta table using sql for a very small dataset

Lakshay — Thu, 24 Aug 2023 14:57:45 GMT

You should also look into the sql plan if the writing phase is indeed the part that is taking time. Since spark works on lazy evaluation, there might be some other phase that might be taking time

Re: dataframe takes unusually long time to save as a delta table using sql for a very small dataset

jaga2 — Tue, 10 Sep 2024 14:46:47 GMT

Same issue I am having read/write takes long time around 10hrs, data size was 21gb