topic Re: Out of Memory/Connection Lost When Writing to External SQL Server from Databricks Using JDBC Connection in Data Engineering

Out of Memory/Connection Lost When Writing to External SQL Server from Databricks Using JDBC Connection

Megan05 — Wed, 22 Jun 2022 15:50:23 GMT

I am working on writing a large amount of data from Databricks to an external SQL server using a JDB connection. I keep getting timeout errors/connection lost but digging deeper it appears to be a memory problem. I am wondering what cluster configurations I may need/where would be best to cache my data. The input data is about about 60 gb of data that is reduced to 60 mil rows. The process works to write about 1 million rows to the external database then crashes.

I have tried different cluster configurations, memory optimized, compute optimized etc. I have also tried different garbage collection settings as the garbage collection metric is dark red during the process.

Re: Out of Memory/Connection Lost When Writing to External SQL Server from Databricks Using JDBC Connection

Hubert-Dudek — Wed, 22 Jun 2022 16:37:46 GMT

Please extend the number of dataframe partitions using

coalesce(<N>) or repartition(<N>). In most cases, it should save the issue automatically as it will write in chunks per partition.

In addition these jdbc connection properties can help (as on JDBC To Other Databases - Spark 3.3.0 Documentation (apache.org)😞

numPartitions

batchsize

isolationLevel

Re: Out of Memory/Connection Lost When Writing to External SQL Server from Databricks Using JDBC Connection

Megan05 — Wed, 22 Jun 2022 22:41:04 GMT

Thanks for your response, Hubert! That seemed to work to fix the timeout issue.

Re: Out of Memory/Connection Lost When Writing to External SQL Server from Databricks Using JDBC Connection

hotrabattecom — Thu, 23 Jun 2022 09:42:18 GMT

Thanks for the answer. I am also get in this problem.

Hotrabatt

Re: Out of Memory/Connection Lost When Writing to External SQL Server from Databricks Using JDBC Connection

Hubert-Dudek — Thu, 23 Jun 2022 18:46:09 GMT

Great to hear. If it is possible, please select my answer as the best one.

Re: Out of Memory/Connection Lost When Writing to External SQL Server from Databricks Using JDBC Con

PabloCSD — Wed, 14 Aug 2024 21:14:26 GMT

Excuse me Megan05, what parameters did you use?