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
monojmckvie
Regular Visitor

Connect to lakehouse endpoint view from fabric notebook

Hi All,

 

I have created one view in fabric lakehouse SQL endpoint.

I want to query that view from fabric notebook.

Can you please suggest how to do that?

1 ACCEPTED SOLUTION

Hi  @monojmckvie 
Currently there is a bug regarding the connection with JDBC. But you can use Pyodbc connection as below:Here's a python sample

 

import pyodbc

# Get the secret value from Key Vault
clientId = "<clientId>" 
tenantId = "<tenantId>"
principalSecret = mssparkutils.credentials.getSecret("https://<keyValutName>.vault.azure.net/", "<secretName>")  

# Set up SQL Server connection details
server = "<SQL Endpoint FQDN>"
database = "<database>"
username = f"{principalId}@{tenantId}"

# Create the connection string with the access token
connection_string = f"Driver={{ODBC Driver 18 for SQL Server}};Server={server};Database={database};Authentication=ActiveDirectoryServicePrincipal;UID={username};PWD={principalSecret};ConnectRetryCount=4"

print(connection_string)
# Connect to the Azure SQL Database using pyodbc
conn = pyodbc.connect(connection_string)


# Execute a query
query = "SELECT @@version version"
cursor = conn.cursor()
cursor.execute(query)

# Fetch all rows from the result set
rows = cursor.fetchall()

# Print the rows
for row in rows:
    print(row)

# Close the connection when done
conn.close()


Hope this helps.

View solution in original post

9 REPLIES 9
monojmckvie
Regular Visitor

Hi @v-nikhilan-msft ,

 

Thanks for sharing the details. I see in below document that there is a way to connect using JDBC. Do you have any use case or sample code with the implementation?

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

Hi  @monojmckvie 
Currently there is a bug regarding the connection with JDBC. But you can use Pyodbc connection as below:Here's a python sample

 

import pyodbc

# Get the secret value from Key Vault
clientId = "<clientId>" 
tenantId = "<tenantId>"
principalSecret = mssparkutils.credentials.getSecret("https://<keyValutName>.vault.azure.net/", "<secretName>")  

# Set up SQL Server connection details
server = "<SQL Endpoint FQDN>"
database = "<database>"
username = f"{principalId}@{tenantId}"

# Create the connection string with the access token
connection_string = f"Driver={{ODBC Driver 18 for SQL Server}};Server={server};Database={database};Authentication=ActiveDirectoryServicePrincipal;UID={username};PWD={principalSecret};ConnectRetryCount=4"

print(connection_string)
# Connect to the Azure SQL Database using pyodbc
conn = pyodbc.connect(connection_string)


# Execute a query
query = "SELECT @@version version"
cursor = conn.cursor()
cursor.execute(query)

# Fetch all rows from the result set
rows = cursor.fetchall()

# Print the rows
for row in rows:
    print(row)

# Close the connection when done
conn.close()


Hope this helps.

Hi @monojmckvie 
We haven’t heard from you on the last response and was just checking back to see if your query got resolved. Otherwise, will respond back with the more details and we will try to help.
Thanks

Thanks @v-nikhilan-msft for sharing the code.

Yes, the ODBC connection works perfectly. Thanks again for this.

I will request to update this thread once the JDBC issue gets resolved(if possible 🙂 ).

 

 

 

Hi @monojmckvie 

Glad that I could help you in resolving the query. I will definitely keep you posted regarding the updates. Please continue using Fabric Community for any help regarding your queries.

monojmckvie
Regular Visitor

Thanks @v-nikhilan-msft for the reply.

What can be the alternate solution?

Can you please suggest because I really need to query those lakehouse SQL endpoint views from fabric notebook 

Hi @monojmckvie 
Currently this is not possible. But in a few months Warehouse connector to notebooks will be available in Fabric. It will offer Notebooks efficient access to SQL Endpoints (in a different workspace) or to warehouses, enforcing RLS/CLS in SQL and likely supporting views as well. There is no estimated timeline for this.

What's new and planned for Synapse Data Warehouse in Microsoft Fabric - Microsoft Fabric | Microsoft...

vnikhilanmsft_0-1712733130669.png

 

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


Hi @monojmckvie 
We haven’t heard from you on the last response and was just checking back to see if your query got resolved. Otherwise, will respond back with the more details and we will try to help.
Thanks

v-nikhilan-msft
Community Support
Community Support

Hi @monojmckvie 
Thanks for using Fabric Community.
You can only query tables present in the Lakehouse using Notebooks. We cannot query views. This is not supported in Fabric yet. But you could share your feedback on our feedback link 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.
Feedback Link : Home (microsoft.com)
Thank you.




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.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors