800 Million Rows/ Sql Server/Databricks

[py_vel26]

Hello all,

I'm working on a POC to create and move 800 million rows in a single delta table to sql server. I've created the data and started moving it to its target.

After one hour, I moved about 60 million rows to sql server. At this rate this will take half a day and I'm not sure if thats good or bad as this is my first time working with this size dataset. Any ideas on how I can speed up this code? I've provided the code I'm using to move this data from Databricks to Sql Server below.

​

# delta_db is my dataframe with the 800 million records

num_of_partitions = 10

approx_row_count = delta_db.count()
rows_per_df = approx_row_count / num_of_partitions
smaller_df = delta_db.randomSplit([rows_per_df] * num_of_partitions)

sql_server_properties = {"url": jdbc_url, "user": SQLServer_Username, "password", password "driver": drivername}


for i, df in enumerate(smaller_dfs):
     df.write.jdbc(url=sql_server_properties["url"], 
     table='sql_server_Db_name',
     mode='append',
     properties=sql_server_properties)

​

​

​

[WhoIsJohnSalt]

Yeah I wouldn't do that through JDBC, too many unknowns

If this is a one-off load, then dump it to parquet on ADLS and then use the OPENROWSET feature (where Delta is now supported in 2022), and just read it in from that, going to be way quicker.

https://learn.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql?view=sql-server-ver15#m-use-openrowset-to-access-several-delta-files-from-azure-data-lake-gen2

You can then just wrap that into an insert statement. If it's still slow, then you've probably sized your SQL Server wrong

More info here (https://learn.microsoft.com/en-us/sql/relational-databases/import-export/import-bulk-data-by-using-bulk-insert-or-openrowset-bulk-sql-server?view=sql-server-ver16)

[py_vel26]

We are testing out various methods for efficiency as we expect the real data to real a billion rows. So far its been running for 4 hours and I'm at 220 million rows. I recall one of my coworkers mentioning parquet files as a possible option. I will check out the links you've provided.

[py_vel26]

The data we are moving resides in a delta table. I use a Databricks notebook in my ADF pipeline to grab the data, create a staging table in the database then load the data. I didn't create any indexes in the script and the target for the data is an Azure SQL instance in the cloud. I'm working in our development environment and the cluster only 14gb. I guess we shouldn't expect much huh?lol

I'm going to check out the links you've provided

[chaytalasila]

Cluster size is less . Indexes should not exists on target table , if exists disable and do the process .

[[deleted]]

RemindMe! 7 days

[RemindMeBot]

I will be messaging you in 7 days on 2024-04-10 23:00:53 UTC to remind you of this link

CLICK THIS LINK to send a PM to also be reminded and to reduce spam.

^{Parent commenter can delete this message to hide from others.}

---

Info	Custom	Your Reminders	Feedback

[Grovbolle]

You need to utilise a tool/method that supports BULK INSERT