topic Updating tables from SQL Server to Databricks in Data Engineering

Updating tables from SQL Server to Databricks

YS1 — Thu, 20 Jul 2023 22:24:22 GMT

Hi,

I have SQL Server tables which are the primary location for all live transactions happen and currently I read them through pyspark as dataframes and overwrite them everyday to have the latest copy of them in Databricks. The problem is it takes long time to copy all tables from SQL Server to Databricks and the tables don't have a primary key like an id, some tables have timestamps. The question is how can I read only the changes and write them to my Databricks tables in an efficient way without the need to rewrite the whole table again and again?

Re: Updating tables from SQL Server to Databricks

Stephen678 — Sat, 22 Jul 2023 23:46:02 GMT

Do you have any audit column in the sql server table which can be used for incremental loading?

Is Sql server table full load each time?

Re: Updating tables from SQL Server to Databricks

YS1 — Sun, 23 Jul 2023 04:10:49 GMT

For the first question, some tables have pkid or timestamp columns, but some don't.

For the second question, some tables gets only appended to while others are truncated and created again (on SQL Server) but on Databricks currently we overwrite them everyday.

Re: Updating tables from SQL Server to Databricks

Anonymous — Sun, 23 Jul 2023 04:37:42 GMT

Hi @YS1

Hope you are well. Just wanted to see if you were able to find an answer to your question and would you like to mark an answer as best? It would be really helpful for the other members too.

Cheers!