Azure-docs: Error on the final step to ASDW

[SQLDataSide]

Hi
in the final step when loading into ASDW I get the error

om.databricks.spark.sqldw.SqlDWConnectorException: Exception encountered in SQL DW connector code.

very strange, i tried very thing, only the last step fails, please help

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: d863ab39-0b6a-eb81-9dde-5eb25b46cc33
• Version Independent ID: 655abadf-5f9e-a65b-0109-c5166f956c4d
• Content: Tutorial: Perform ETL operations using Azure Databricks
• Content Source: articles/azure-databricks/databricks-extract-load-sql-data-warehouse.md
• Service: azure-databricks
• GitHub Login: @nitinme
• Microsoft Alias: nitinme

[vasivara-MSFT]

@SQLDataSide Thanks for the feedback. We are actively investigating and will get back to you soon.

[SQLDataSide]

I even tried it on ASDW Gen2 and it did not work
i feel that its the mentioned section that can not write

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

[SQLDataSide]

I am still getting the same error, can someone help please
Sincerely
Nik

[SQLDataSide]

I found the issue and another possible issue that MS need to test

• Issue 1 :
  the code are correct except the part
  val tempDir = "wasbs://" + blobContainer + "@" + blobStorage +"/tempDirs"

In the above documentation it says "/@" it must be "@" only

• Possible issue: i did try it on ASDW Gen2 (14.69 CAD/hour) and it was not working maybe I was wrong and was using "/@" , anyway please someone at MS should test this with GEN2 of ASDW
  Sincerely
  Nik

[SQLDataSide]

FYI the error message was miss leading maybe that can be improved, Thank you

Sincerely
Nik

[JasonWHowell]

@SQLDataSide Sorry for the trouble you've been though.

It makes sense that the WASB path should be in the format you noted without the slash or backslash at the @ sign. Maybe a publishing artifact or escape character that didn't render correctly.
wasbs://[email protected]/tempDirs

The SQLDW adapter takes that storage path in the tempdir option. If an invalid temp path is given, maybe the obscure error occurs without any detail on the cause.

I'll update the docs for now with the right syntax to unblock any others facing the issue.

I didn't have a chance to test on the Gen 2 platform yet, but can consider it later on.

Thanks, Jason

[JasonWHowell]

Regarding the vague SQL error, one way to see the full error text is to look at the driver logs.

1. In the DataBricks notebook, select Clusters icon on the left, and select your cluster name.

2. Select Driver Logs on the tabs at the top of the page.

3. Scroll through the Standard error section to review the recent errors.

[Deepu14]

I faced similar error and my issue was resolved using the following steps.

1. Made changes to firewall settings (on Azure portal) on server level allowing connections from a range of IP Addresses.
   Even after making the above change, I got similar error.
2. Modified the server name from xxxxx.database.windows.net to xxxxx

That's it. I was able to move the table to Dataware house on Azure.

[scubajon]

I am having a similar issue; however, I am using "@" in
val tempDir = "wasbs://" + blobContainer + "@" + blobStorage +"/tempDirs"

and I am using mynewserver-20190119 for the server name, so that the variable sql
sqlDwUrl contains "mynewserver-20190119.database.windows.net" versus "mynewserver-20190119.database.windows.net.database.windows.net".

In the last step, I tried experimenting using
.format("jdbc") with no luck.
I also replaced sqlDwUrlSmall with sqlDwUrl in the option for "url".

renamedColumnsDf.write
.format("com.databricks.spark.sqldw")
/* .option("url", sqlDwUrlSmall) /
/ .format("jdbc") /
.option("url", sqlDwUrl)
.option("dbtable", "SampleTable")
/ .option("dbtable", "schema.tablename")
.option("user", "username")
.option("password", "password") */
.option("forward_spark_azure_storage_credentials","True")
.option("tempdir", tempDir)
.mode("overwrite")
.save()

When I substituted sqlDwUrlSmall with sqlDwUrl for the url option, and I get a different message.

com.databricks.spark.sqldw.SqlDWSideException: SQL DW failed to execute the JDBC query produced by the connector.

I reviewed the driver log (see attached), but I am not sure what I am looking for here.

I do have one question about the tempDir in the Storage Account Blob. The folder does not exist. Is it dynamically created; if not, do I need to create this folder and if so, how do I create folder in the container for the Blob?

Suggestions?

[Deepu14]

@scubajon Looks like you haven't created blob storage and blobContainer.
You have to create a blob storage account and a container.

Then you have to use the following code.
val blobStorage = ".blob.core.windows.net"
val blobContainer = ""
val blobAccessKey = ""

val tempDir = "wasbs://" + blobContainer + "@" + blobStorage +"/tempDirs"
val acntInfo = "fs.azure.account.key."+ blobStorage
sc.hadoopConfiguration.set(acntInfo, blobAccessKey)

followed by your code.

To create a blob storage go to All Services < storage accounts < Click Add on the left hand top.
When the storage account is created go to overview of that storage and go Blobs tab under services

Then click on a new container to create a new blob container. You will also find the blobAccessKey in Access Keys under settings on the same page.

If this doesn't work, make changes to firewall settings (on Azure portal) on server level allowing connections from your IP Address.

If it still doesn't work, Click on the + button beside Exception : SQL DW failed to execute the JDBC query produced by the connector, check for the exact error message and paste it here so that we will get to know what the issue is about.

[scubajon]

I did create a Blob container but it is not in the same resource group as Data Bricks. The Tutorial does mention that the same resource group for Data Bricks is to be used for the Storage Blob Container? Does this mean that the same resource group is to be used for the Data Lake Storage Gen 1 as well?

[Deepu14]

@scubajon . I am not sure. I just wanted to try that option regarding resource group. Because that was one of the issues I came across while using Azure Key vault. So you are saying that you have included the details of Blob containers in the core right?. Moreover, if you can check for the exact error message and paste it here, I might help you in figuring out what the issue is about.

[scubajon]

The message was pretty vague. It just notes that there was an exception with no details. I also went to the driver logs but could not gain any insight from them. Please see:
https://github.com/MicrosoftDocs/azure-docs/issues/22947

[agnaldocarmo]

Regarding the vague SQL error, one way to see the full error text is to look at the driver logs.

1. In the DataBricks notebook, select Clusters icon on the left, and select your cluster name.
2. Select Driver Logs on the tabs at the top of the page.
3. Scroll through the Standard error section to review the recent errors.

I've followed this steps and investigating resulted as bellow, I discovery that Sql DW in this method does not accept special character(s):