How to access Fabric Warehouse from Fabric Notebooks?

[Madhusudan_P]

Hi,

I am trying to access Fabric Datawarehouse table from Fabric notebooks. I am copying ABFS path of Datawarehouse table from its properties as under the hood table in datawarehouse is a delta table.

And then passed below query - 

df = spark.read.format("delta").load("abfss://My workspace@onelake.dfs.fabric.microsoft.com/test_dw.Datawarehouse/Tables/dbo/Date")

It's getting failed with the following error - 
IllegalArgumentException: Illegal character in authority at index 8: abfss://My workspace@onelake.dfs.fabric.microsoft.com.

I tried passing below query but again same error.

df = spark.read.format("delta").load("abfss://My%20workspace@onelake.dfs.fabric.microsoft.com/test_dw.Datawarehouse/Tables/dbo/Date")

Just wondering is it possible to read and write to warehouse tables from notebooks in Fabric?
We have similar option in Synapse in which we can read/write data warehouse tables using .synapsesql option using Notebooks.

https://techcommunity.microsoft.com/t5/azure-synapse-analytics-blog/writing-data-using-azure-synapse...

[govindarajan_d]

Hi @Madhusudan_P,

You can add the Warehouse as a shortcut to Lakehouse and then read it just as a table. But even in this method, you will be able to only read data from Warehouse and not write.

The approach I would suggest you is add the Lakehouse back in Warehouse and you would be able to query the data from Lakehouse within Warehouse. Kind of like a full circle. 

[AndyDDC]

You won't be able to read or write to Warehouse tables as those requests need to go through the SQL endpoint of the warehouse, it won't work with Spark.

You can use something like pyodbc or jdbc to connect to the Warehouse from within a notebook:

https://learn.microsoft.com/en-us/fabric/data-warehouse/connectivity

[Madhusudan_P · Accepted solution]

Hi @AndyDDC ,

Thanks for your answer. However, I was curious to try the query said above without any space in the workspace name. For that, I had to create new Fabric account as I don't have new workspace creation request in existing account. It took me a while, but I am able to read DW table from Spark notebook.

df = spark.read.format("delta").load("abfss://workspace-without-space@onelake.dfs.fabric.microsoft.com/test_dw.Datawarehouse/Tables/dbo/Date")

df.write is getting failed as it says PUT request forbidden.

However, It seems like overwrite or create new table in Warehouse using Spark Notebook is not possible as of now. Pyodbc and jdbc might help.

[v-nikhilan-msft]

Hi @Madhusudan_P 
That's correct, you cannot write directly to the warehouse folder. All updates have to come through the warehouse engine. You can't write to DW tables from Spark or any other engine .

[Madhusudan_P]

Hi @v-nikhilan-msft 
Since we have the capacity in Synapse Dedicated SQL Pool to write from Synapse notebook, why this hasn't been extended to Fabric Warehouse and Notebooks. Is it expected in future?
Dedicated SQL Pool connector for Apache Spark

[v-nikhilan-msft]

Hi @Madhusudan_P 
Currently we do not have any information regarding the availability of this feature. 

Appreciate if you could share the feedback on our feedback channel. Which would be open for the user community to upvote & comment on. This allows our product teams to effectively prioritize your request against our existing feature backlog and gives insight into the potential impact of implementing the suggested feature.

Hope this helps. Please let me know if you have any further queries.