Connect to lakehouse endpoint view from fabric notebook

[monojmckvie]

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?

[monojmckvie]

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

[v-nikhilan-msft · 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.

[v-nikhilan-msft]

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

[monojmckvie]

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 🙂 ).

[v-nikhilan-msft]

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]

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 

[v-nikhilan-msft]

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...

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

[v-nikhilan-msft]

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]

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.