0

I was working on connecting dedicated sql pool(formerly sql DWH) to synapse spark notebooks. I was using spark.read.synapsesql(). I'm able to write data as table but not able to read data from the table.

val df:DataFrame = spark.read.option(Constants.SERVER, "XXXXX.database.windows.net")
           .option(Constants.USER, "XXXXX")
           .option(Constants.PASSWORD, "XXXXX")         
           .option(Constants.TEMP_FOLDER,"abfss://xxxxx@xxxx.dfs.core.windows.net/Tempfolder/")
           .synapsesql("dedicated-poc.dbo.customer"

com.microsoft.spark.sqlanalytics.SQLAnalyticsConnectorException: com.microsoft.sqlserver.jdbc.SQLServerException: External file access failed due to internal error: 'Error occurred while accessing HDFS: Java exception raised on call to HdfsBridge_Connect.

Java exception message: Configuration property XXXXXXXX.dfs.core.windows.net not found.' at com.microsoft.spark.sqlanalytics.ItemsScanBuilder$PlanInputPartitionsUtilities$.extractDataAndGetLocation(ItemsScanBuilder.scala:183)

Permission: we have owner, storage data blob contributor access for synapse and specific user

CHEEKATLAPRADEEP
  • 12,191
  • 1
  • 19
  • 42
Coder
  • 1
  • A dedicated SQL pool connection string would look more like `someName.sql.azuresynapse.net` I think? Maybe just comment out the temp folder line and see what happens. – wBob May 09 '22 at 09:29

1 Answers1

0

To resolve the above exception, please try the below:

  • Try updating the code by adding below:
spark._jsc.hadoopConfiguration().set("fs.azure.account.key.xxxxx.dfs.core.windows.net", "xxxx==")
  • To read data from table, try including date data type in SQL Pool and then read.

Note:

Synapse RBAC roles do not grant permissions to create or manage SQL pools, Apache Spark pools, and Integration runtimes in Azure Synapse workspaces. Azure Owner or Azure Contributor roles on the resource group are required for these actions.

  • Give Azure owner role to resource group instead of synapse and specific user.
  • Check if there is any firewall rule that is blocking the connectivity and disable it.

If still the issue persists, raise a Azure support request

For more in detail, please refer below links:

Azure Synapse RBAC roles - Azure Synapse Analytics | Microsoft Docs

azure databricks - File read from ADLS Gen2 Error - Configuration property xxx.dfs.core.windows.net not found - Stack Overflow

Imran
  • 3,875
  • 2
  • 3
  • 12