Execute Create Table query in Synapse using Databricks

Question

Hi All,

So this is my first time using Azure Databricks. I was trying out to load the csv files from my blob to my synapse SQL DW. I am currently using this code:-

df.write \
        .format("com.databricks.spark.sqldw") \
        .option("url", dwUrl) \
        .option("forwardSparkAzureStorageCredentials", "true") \
        .option("dbTable", "dbo.zz_"+name+"_raw_delete") \
        .option("tempDir", tempDir) \
        .mode("overwrite") \
        .option("truncate","true") \
        .save()

In this if the table already exits the data is truncated else it creates a new table.

The issue lies when it by default creates a table with columns as nvarchar 256 which fails with error string or binary is truncated.

So what I tried to do was create my own dynamic create statement with nvarchar 4000 (same as copy activity in ADF). Here is the code:

create_statment = "IF OBJECT_ID (N'DBO.ZZ_"+name+"_RAW_DELETE',N'U') IS NULL BEGIN; SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON  CREATE TABLE DBO.ZZ_" +name +"_RAW_DELETE ( ["+df.columns[0] +"] [nvarchar](4000) NULL"

      for cols in df.columns[1:]:
        create_statment += ", ["+cols+"] [nvarchar](4000) null"

      create_statment += ") WITH (DISTRIBUTION = ROUND_ROBIN, CLUSTERED COLUMNSTORE INDEX) END; SELECT OBJECT_ID (N'DBO.ZZ_"+name+"_RAW_DELETE') AS X"
      print(create_statment)

Then i tried to execute this using the code:

df1 = spark.read \
          .format("com.databricks.spark.sqldw") \
          .option("url", dwUrl) \
          .option("tempDir", tempDir) \
          .option("forwardSparkAzureStorageCredentials", "true") \
          .option("query",create_statment) \
          .load()

But it give me error saying:

A processing error "Parse error at line: 1, column: 31: Incorrect syntax near 'IF'." occurred. [ErrorCode = 0] [SQLState = null]

But when I take this statement and execute it in synapse it works. I don't know what is going wrong.

Could anyone please help in executing this create_statement in Synapse from Data bricks?
I also see that when I use Select query it works and I get the output.

Answer 1

A Spark driver is not a general-purpose database library. You can't run DDL or execute stored procedures with it. But the Spark driver is built on top of the JDBC driver, which you can use directly in either Scala or Java. EG

%scala
import java.sql.DriverManager
import java.sql.Connection
import java.util.Properties

val jdbcHostname = "yourServerName.database.windows.net"
val jdbcPort = 1433
val jdbcDatabase = "yourDbName"
val jdbcUsername = dbutils.secrets.get(scope = "keyvault", key = "sqluser")
val jdbcPassword = dbutils.secrets.get(scope = "keyvault", key = "sqlpassword")

// Create the JDBC URL without passing in the user and password parameters.
val jdbcUrl = s"jdbc:sqlserver://${jdbcHostname}:${jdbcPort};database=${jdbcDatabase}"

// Create a Properties() object to hold the parameters.
val connectionProperties = new Properties()
connectionProperties.put("user", s"${jdbcUsername}")
connectionProperties.put("password", s"${jdbcPassword}")

val driverClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
connectionProperties.setProperty("Driver", driverClass)

val con = DriverManager.getConnection(jdbcUrl, connectionProperties)
val stmt = con.createStatement()

stmt.execute("create table whatever(....")