CODEWITHSUNDEEP
azureapache-sparkapache-spark-sqlazure-databricksazure-synapse
adnane
adnane

Reputation: 109

I can't create-load data from spark dataframe in databricks to Azure Synapse (DWH)

I am new to this field and I am currently playing with Azure & Databricks. I ingested a Json file from blob to databricks, did some transformations and wish now to load it to a virgin DWH (Synapse) in Azure.

The code execution shows an error at : .mode("overwrite") and gives the following error: com.databricks.spark.sqldw.SqlDWSideException: Azure Synapse Analytics failed to execute the JDBC query produced by the connector.

The cell contains the following code :

dwTable= "mytable001"
dwDatabase = "*****"
dwServer = "****.database.windows.net" 
dwUser = "****"
dwPass = "****"
dwJdbcPort =  "1433"
dwJdbcExtraOptions = "encrypt=true;trustServerCertificate=true;hostNameInCertificate=*.database.windows.net;loginTimeout=30;"
sqlDwUrl = "jdbc:sqlserver://" + dwServer + ".database.windows.net:" + dwJdbcPort + ";database=" + dwDatabase + ";user=" + dwUser+";password=" + dwPass + ";$dwJdbcExtraOptions"
sqlDwUrlSmall = "jdbc:sqlserver://" + dwServer + ".database.windows.net:" + dwJdbcPort + ";database=" + dwDatabase + ";user=" + dwUser+";password=" + dwPass


tempDir = "wasbs://****@*****.blob.core.windows.net/tempDirs"

acntInfo = "fs.azure.account.key.databrickstrainingst.blob.core.windows.net"
sc._jsc.hadoopConfiguration().set(
  acntInfo, 
  "key****")

spark.conf.set("spark.sql.parquet.writeLegacyFormat","true")

renamedColumnsDF.write \
  .format("com.databricks.spark.sqldw") \
  .option("url", sqlDwUrlSmall) \
  .option("dbtable", dwTable) \
  .option( "forward_spark_azure_storage_credentials","true") \
  .option("tempdir", tempDir) \
  .mode("overwrite") \
  .save()

Upvotes: 0

Views: 1666

Answers (1)

CHEEKATLAPRADEEP
CHEEKATLAPRADEEP

Reputation: 12788

If the encrypt property is set to true and the trustServerCertificate property is set to false and if the server name in the connection string doesn't match the server name in the TLS certificate, the following error will occur. As of version 7.2, the driver supports wildcard pattern matching in the left-most label of the server name in the TLS certificate.

("url", "jdbc:sqlserver://cheprasynapse.sql.azuresynapse.net:1433;database=chepra;user={Your_username};password={Your_Password};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.sql.azuresynapse.net;loginTimeout=30;")

enter image description here

When the encrypt property is set to true and the trustServerCertificate property is set to true, the Microsoft JDBC Driver for SQL Server won't validate the SQL Server TLS certificate. This is usually required for allowing connections in test environments, such as where the SQL Server instance has only a self signed certificate.

("url", "jdbc:sqlserver://cheprasynapse.sql.azuresynapse.net:1433;database=chepra;user={Your_username};password={Your_Password};encrypt=true;trustServerCertificate=true;hostNameInCertificate=*.sql.azuresynapse.net;loginTimeout=30;")

enter image description here

For more details, refer Connecting with encryption.

Upvotes: 1

Related Questions