topic Re: SQL Connection string shared accross all Onelakes in Data Warehouse

SQL Connection string shared accross all Onelakes

jbshml — Tue, 10 Dec 2024 16:05:55 GMT

Hello,

I'm currently facing an issue while trying to query my SQL analytics endpoints from my local machine using Python script.
In my selected workspace, I have 3 onelakes (named Bronze, Silver and Gold: created historically in this given order)

However, all of them share the same SQL Connection string provided, making it impossible to reach all of them, and they all seems to be related to the Bronze's one. I even tried to remove the database inside the connection_string below, but I still can't get my Silver and Gold list of tables or whatever - Is it an expected behavior or something that I'm missing ?

Below the python code

import pyodbc from azure.identity import InteractiveBrowserCredential, AzureCliCredential import struct from itertools import chain, repeat import pandas as pd sql_endpoint = "thecommonendpoint.datawarehouse.fabric.microsoft.com" credential = AzureCliCredential() # replace by InteractiveBrowserCredential() if needed token = credential.get_token("https://database.windows.net/.default") token_as_bytes = bytes(token.token, "UTF-8") encoded_bytes = bytes(chain.from_iterable(zip(token_as_bytes, repeat(0)))) token_bytes = struct.pack("<i", len(encoded_bytes)) + encoded_bytes connection_string = ( f"Driver={{ODBC Driver 18 for SQL Server}};Server={sql_endpoint},1433;Encrypt=Yes;TrustServerCertificate=No" ) conn = pyodbc.connect(connection_string, attrs_before={1256: token_bytes}) query = "SELECT * FROM sys.tables;" #List tables df = pd.read_sql(query, conn)

Thanks in advance for your enlightment

Re: SQL Connection string shared accross all Onelakes

Anonymous — Wed, 11 Dec 2024 06:23:51 GMT

Hi jbshml

You can think of SQL Connection string as a server name, and each workspace is equivalent to a server. Therefore, all SQL analytics endpoints, data warehouses, SQL databases and mirrored databases in the same workspace share the same SQL Connection string.

I connect to a SQL Connection string in SSMS, and it shows all of above Fabric items as databases on the server. Just like below.

UPDATE:

Based on your python code, I used the following code, which was able to read data from the database specified in the connection string. I don't use Pandas here.

import pyodbc from azure.identity import InteractiveBrowserCredential, AzureCliCredential import struct from itertools import chain, repeat import pandas as pd sql_endpoint = "xxxxxxxxxxxxx.datawarehouse.fabric.microsoft.com" database_name = "your_lakehouse_name" # credential = AzureCliCredential() # replace by InteractiveBrowserCredential() if needed credential = InteractiveBrowserCredential() token = credential.get_token("https://database.windows.net/.default") token_as_bytes = bytes(token.token, "UTF-8") encoded_bytes = bytes(chain.from_iterable(zip(token_as_bytes, repeat(0)))) token_bytes = struct.pack("<i", len(encoded_bytes)) + encoded_bytes connection_string = ( f"Driver={{ODBC Driver 18 for SQL Server}};Server={sql_endpoint},1433;Database={database_name};Encrypt=Yes;TrustServerCertificate=No" ) conn = pyodbc.connect(connection_string, attrs_before={1256: token_bytes}) cursor = conn.cursor() cursor.execute("select * from sys.tables") for row in cursor: print(row) conn.close()

Using Pandas version:

connection_string = ( f"Driver={{ODBC Driver 18 for SQL Server}};Server={sql_endpoint},1433;Database={database_name};Encrypt=Yes;TrustServerCertificate=No" ) conn = pyodbc.connect(connection_string, attrs_before={1256: token_bytes}) query = "SELECT * FROM sys.tables" df = pd.read_sql(query, conn) conn.close() print(df.head())

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

Re: SQL Connection string shared accross all Onelakes

jbshml — Wed, 11 Dec 2024 08:12:04 GMT

Thanks for your answer and the explanation about SQL analytics endpoints !

I tried requesting my selected lakehouse within the query to make it more flexible like this:

cursor.execute("SELECT * FROM Lakehouse_name.dbo.my_table")

Works like a charm,

Thanks again !