topic Re: Unable to read SQL Endpoint view into a Notebook dataframe in Data Engineering

Unable to read SQL Endpoint view into a Notebook dataframe

Anonymous — Mon, 08 Jan 2024 01:44:49 GMT

Hi All,

We are seeking to use a view within a SQL analytics Endpoint as a source of data for a dataframe in a notebook.

When we seek to query it, we see an error like [TABLE_OR_VIEW_NOT_FOUND]

code looks like this. The view is under the DBO schema.

df = spark.sql("SELECT * FROM lakehousename.viewname LIMIT 1000") display(df)

Is this possible? I feel like it wont be :*( but staying positive.

Thanks!

Re: Unable to read SQL Endpoint view into a Notebook dataframe

AndyDDC — Mon, 08 Jan 2024 22:35:32 GMT

Hi @Anonymous no I don't believe this will work as the View has been created using the SQL Analytics endpoint, there is no metadata sync from the SQL Endpoint to the Lakehouse (it's the other way round).

You could use JDBC connector in the Notebook to query the View in the SQL Endpoint, or create the View in the Lakehouse using Spark.

edit: I believe pyodbc would work here

Re: Unable to read SQL Endpoint view into a Notebook dataframe

SorenSparso — Mon, 08 Jan 2024 12:20:37 GMT

Hi,

Do you know how to get the authentication to work in the SQL endpoint? I have tried different combinations of below statement, but it is not working:

df = spark.read\

.format("jdbc") \

.option("url", f"jdbc:sqlserver://***.datawarehouse.pbidedicated.windows.net:1433;database=***") \

.option("dbtable", "someTable") \

.option("authentication", "ActiveDirectoryIntegrated") \

.option("encrypt", "true") \

.option("clientid", "***") \

.option("hostNameInCertificate", "*.pbidedicated.windows.net") \

.load()

display(df)

Thanks 🙂

Re: Unable to read SQL Endpoint view into a Notebook dataframe

AndyDDC — Mon, 08 Jan 2024 22:35:46 GMT

Does this help at all? https://community.fabric.microsoft.com/t5/General-Discussion/authenticate-to-Fabric-Data-Warehouse-with-Service-Principal/td-p/3467512

Re: Unable to read SQL Endpoint view into a Notebook dataframe

SorenSparso — Tue, 09 Jan 2024 12:45:12 GMT

Hi,

Yes, that helped, thanks 🙂

I had to add the serviceprincipal to the workspace (CREATE USER is not a supported statement type.) but after that, I can now read views in a Lakehouse from a Notebook.

Unable to read SQL Endpoint view into a Notebook dataframe

PunChili — Tue, 25 Jun 2024 10:50:56 GMT

Hi,

I can read data by using pyodbc package from endpoint. Now I would like to store these results in a delta table in lakehouse... is this possible?

Thank you 😄

Re: Unable to read SQL Endpoint view into a Notebook dataframe

daanblinde — Thu, 24 Oct 2024 09:11:29 GMT

having the same issue here. a lot of people out there use sql statements stored in views to create there ETL logic. would be very nice if microsoft is going to support the possibility to read from views in a notebook. I came across the following example using the spark connector which works for me perfect as a workaround. https://fabric.guru/querying-sql-endpoint-of-fabric-lakehousewarehouse-in-a-notebook-with-t-sql