topic Re: databricks job taking longer time to load 2.3 gb data from bolb to ssms table in Data Engineering

databricks job taking longer time to load 2.3 gb data from bolb to ssms table

vijaypodili — Sat, 21 Dec 2024 06:35:17 GMT

df_CorpBond= spark.read.format("parquet").option("header", "true").load(f"/mnt/{container_name}/raw_data/dsl.corporate.parquet")
df_CorpBond.repartition(20).write\
    .format("jdbc")\
    .option("url", url_connector)\
    .option("dbtable", "MarkIt_CorpBonds")\
    .option("user", user)\
    .option("password", pwd)\
    .option("driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver")\
    .option("numPartitions", 100)\
    .option("batchsize", 100000)\
    .mode("overwrite")\
    .save()

this is my code to load 2.3 gb blob data into ssms table job will take more than 2 hours my cluster size is 94gb and have 1 driver  node and 2 worker node how we can optimize the code

Re: databricks job taking longer time to load 2.3 gb data from bolb to ssms table

Walter_C — Sat, 21 Dec 2024 14:16:41 GMT

Is this comparing it with another job runs or by comparing it with all purpose cluster?

Re: databricks job taking longer time to load 2.3 gb data from bolb to ssms table

vijaypodili — Sat, 21 Dec 2024 14:21:04 GMT

i'm trying to say my cluster have enough storage space 94gb so it can easy handle 2.3 gb data but my job taking longer time time

job 2 and 3 completed with in the 3 min

but job 4 taking longer to to complete its tasks

Re: databricks job taking longer time to load 2.3 gb data from bolb to ssms table

vijaypodili — Sat, 21 Dec 2024 14:41:12 GMT

i'm trying to say my cluster have enough storage space 94gb so it can easy handle 2.3 gb data but my job taking longer time time

job 2 and 3 completed with in the 3 min

but job 4 taking longer to to complete its tasks

Re: databricks job taking longer time to load 2.3 gb data from bolb to ssms table

RiyazAliM — Mon, 23 Dec 2024 06:03:44 GMT

Hi @vijaypodili, wondering why did you repartition the df to 20 and then set the num partitions to 100. Also I see that your cluster has 94 gigs but what is the number of cores your cluster has?

Re: databricks job taking longer time to load 2.3 gb data from bolb to ssms table

vijaypodili — Mon, 23 Dec 2024 14:04:32 GMT

Hi @RiyazAliM

i just want want to decrease the job time so that why i was using repartition,batch size,num partiton, but i did not work can you please suggest correct code this my worker node details

1-2 Workers32-64 GB Memory4-8 Cores

1 Driver32 GB Memory, 4 Cores

Runtime14.3.x-scala2.12

Re: databricks job taking longer time to load 2.3 gb data from bolb to ssms table

RiyazAliM — Tue, 24 Dec 2024 07:11:43 GMT

Hi @vijaypodili

ideally, given you have 8 cores (for 2 workers), repartition/numPartition should be a multiple of 8 (no. of cores).

Concern here is, I don't see any transformation in the code snippet shared that could potentially trigger a long running job. I strongly believe, writing it to the table in SSMS is taking longer in this case.

For the job that's taking time to execute, would you be able to share the DAG from the spark UI?

Also, check the below StackOverFlow, where folks suggesting to use various connectors to improve write performance.

Re: databricks job taking longer time to load 2.3 gb data from bolb to ssms table

vijaypodili — Tue, 24 Dec 2024 10:30:51 GMT

i removed the numpartiton,batch size and repartition as well job will take almost 3hrs to write data into ssms tables

Re: databricks job taking longer time to load 2.3 gb data from bolb to ssms table

RiyazAliM — Thu, 26 Dec 2024 09:22:19 GMT

Instead of removing, try to tweak the num partitions, repartition and shuffle partitions to see if it increases the write speed. The Spark UI & DAG flow will exactly tell us the execution plan and we can see what's taking time to load the table to SSMS.

Re: databricks job taking longer time to load 2.3 gb data from bolb to ssms table

vijaypodili — Mon, 30 Dec 2024 06:43:55 GMT

Hi @RiyazAliM
this is my dag digram

file size is 3.5 gb and in future we need to load 14gb as well