topic Re: SQL Server To Databricks Table Migration in Data Engineering

SQL Server To Databricks Table Migration

YS1 — Thu, 11 Jul 2024 22:55:59 GMT

Hello,

Is there an equivalent SQL code for the following Pyspark code? I'm trying to copy a table from SQL Server to Databricks and save it as a managed delta table.

jdbcHostname = "your_sql_server_hostname" jdbcPort = 1433 jdbcDatabase = "your_database_name" jdbcUsername = "your_username" jdbcPassword = "your_password" # JDBC URL format for SQL Server jdbcUrl = f"jdbc:sqlserver://{jdbcHostname}:{jdbcPort};database={jdbcDatabase}" # Connection properties connectionProperties = { "user" : jdbcUsername, "password" : jdbcPassword, "driver" : "com.microsoft.sqlserver.jdbc.SQLServerDriver" } df = spark.read.jdbc(url=jdbcUrl, table=query, properties=connectionProperties) df.write.format("delta").mode("overwrite").saveAsTable("table_name")

Re: SQL Server To Databricks Table Migration

ranged_coop — Fri, 12 Jul 2024 07:04:03 GMT

I hope you are asking for a SQL version of the pyspark code ? Can you please explain the advantages of having it in SQL as compared to pyspark ? There are some options, best would be federated queries against sql server ? select as if it were a databricks table and write it to the target ? alternatively you could create a view against the sql server table and then use that as insert into the databricks table...but in my limited understanding (could be wrong) all of it would basically get optimized similarly in the background and serves no additional benefits to be rewritten in SQL...

Re: SQL Server To Databricks Table Migration

jacovangelder — Fri, 12 Jul 2024 07:08:47 GMT

The only option to have it in Databricks SQL is lakehouse federation with a SQL Server connection.

Re: SQL Server To Databricks Table Migration

YS1 — Mon, 15 Jul 2024 20:19:21 GMT

@ranged_coop Yes, I'm asking for a SQL version of my Pyspark code. The only reason is to give it to person who only code in SQL which would make it easier for them to understand. Thanks for the suggested solution!

Re: SQL Server To Databricks Table Migration

YS1 — Mon, 15 Jul 2024 20:19:51 GMT

Thank you @jacovangelder