topic Re: How to write/update in Data warehouse table using Notebook in Data Engineering

How to write/update in Data warehouse table using Notebook

digitalbrain — Sun, 15 Sep 2024 02:04:33 GMT

Hi there, I've a secanrio where I need to read a value from lakehouse and update that value in data warehouse using Notebook. Is there a way to accompolish it?

Re: How to write/update in Data warehouse table using Notebook

frithjof_v — Sun, 15 Sep 2024 18:18:47 GMT

Usually when this question comes up, people mention pyodbc or jdbc as ways to write to Warehouse from Notebook.

I haven't tried either of them myself, but I have read about others who have used these methods.

However my impression is that this is a "workaround". The Notebook is primarily meant to be used for the Lakehouse, and not really for the Warehouse. However Pyodbc or Jdbc may work and enable you to write to the Warehouse from a Notebook, but it has limited performance, is my impression based on what I've read other places. https://www.reddit.com/r/MicrosoftFabric/s/Z8LPqsbxeU

You can also vote for this Idea:

Write Data into Data Warehouse from Fabric Notebook

https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=ac5bdbd2-5afe-ee11-a73c-000d3a7c9aa4

Re: How to write/update in Data warehouse table using Notebook

FabianSchut — Sun, 15 Sep 2024 20:14:06 GMT

Hi @digitalbrain,

As @frithjof_v mentioned, there is no direct way to connect your Warehouse to a notebook. Is it possilble that you create the table you need in the Warehouse in a Lakehouse first and then create a shortcut in your Warehouse to your Lakehouse? Then you have full control with a notebook over that table and still be able to access the data from the Warehouse.

Re: How to write/update in Data warehouse table using Notebook

frithjof_v — Sun, 15 Sep 2024 21:15:54 GMT

It's not possible to create a shortcut in a Warehouse

There is an Idea for that:

Lakehouse shortcuts available in Warehouse also

https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=e5e4905c-7c7a-ee11-a81c-000d3adc66b1

You can use cross-database queries, though.

However it's not exactly the same (you can't use it in a Direct Lake semantic model, I think. Admittedly, I haven't tried.).

And it's exposed to the potential for SQL Analytics Endpoint sync delay.

Re: How to write/update in Data warehouse table using Notebook

digitalbrain — Sun, 15 Sep 2024 21:56:28 GMT

Hi @FabianSchut, I think @frithjof_v is correct here. I just tried to create a shortcut in data warehouse and there was no option to create it. Then I created a table in data warehouse and also created its shortcut in the lakehouse. I can read the data, but when I tried to overwrite the tabe via notebook, it threw an error. It confirms I can't write into the datawarehouse using notebook (same way as lakehouse table).

"Operation failed: "Forbidden", 403, PUT"

Re: How to write/update in Data warehouse table using Notebook

FabianSchut — Mon, 16 Sep 2024 05:53:56 GMT

My apologies, you are both correct @frithjof_v @digitalbrain. There are no shortcuts in Warehouse (yet).