Error on the final step to ASDW

SQLDataSide commented 6 years ago

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 commented 6 years ago

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

SQLDataSide commented 6 years ago

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 commented 6 years ago

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

SQLDataSide commented 6 years ago

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 commented 6 years ago

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

Sincerely Nik

JasonWHowell commented 6 years ago

@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://container@myaccount.blob.core.windows.net/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 commented 6 years ago

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

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

Deepu14 commented 5 years ago

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

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.
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 commented 5 years ago

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 commented 5 years ago

@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 blobs

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 commented 5 years ago

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 commented 5 years ago

@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 commented 5 years ago

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 commented 5 years ago

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

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

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

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):

MicrosoftDocs / azure-docs

Error on the final step to ASDW #12263

Document Details