Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
DataEnjoyer
New Member

Unable to read SQL Endpoint view into a Notebook dataframe

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! 

1 ACCEPTED SOLUTION
AndyDDC
Solution Sage
Solution Sage

Hi @DataEnjoyer 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 

View solution in original post

4 REPLIES 4
AndyDDC
Solution Sage
Solution Sage

Hi @DataEnjoyer 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 

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 🙂

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Expanding the Synapse Forums

New forum boards available in Synapse

Ask questions in Data Engineering, Data Science, Data Warehouse and General Discussion.

MayFabricCarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

Top Solution Authors