topic Re: Select data from Lakehouses via Fabric API in Data Engineering

Select data from Lakehouses via Fabric API

Nicolas_DHONT — Wed, 14 May 2025 10:31:48 GMT

Hello,

I am wondering how I can do a SELECT on a Lakehouse from an external "home-made" tool. Actulally, I would like to send my SQL statement via Fabric API, but, in the folowing documentatio, I am not able to find the appropriate call: https://learn.microsoft.com/en-us/fabric/data-engineering/lakehouse-api

Thank you by advance for your help,

Best regards,

Re: Select data from Lakehouses via Fabric API

yvaubourg — Wed, 14 May 2025 21:36:07 GMT

Hi,

Unfortunatly, the Fabric API does not allow to get your Data. It's here to manage Fabric artifacts.

You have to use the SQL Endpoint of your lakehouse or to out in place a GraphQL API to get your Data outside MS Fabric.

Re: Select data from Lakehouses via Fabric API

Srisakthi — Thu, 15 May 2025 04:08:37 GMT

Hi @Nicolas_DHONT ,

You can only do a select on Lakehouse using SQLEndpoint by utilising ODBC driver from local.

Fetch sql endpoint of your lakehouse, authentication using your identity

Example:

server_name ='jkshdjhsdf.datawarehouse.pbidedicated.windows.net'

db_name = 'You lakehouse name'

user_name ='abc@xyz.com'

password = 'xxxxxx'

mydb = pyodbc.connect('DRIVER=' + driver + ';SERVER=' + server_name + ';PORT=1433;DATABASE=' + db_name + ';UID=' + user_name + ';PWD=' + password + ';Authentication=ActiveDirectoryPassword;')

cursor = mydb.cursor()

# Sample select query

cursor.execute("SELECT TOP(10) * FROM INFORMATION_SCHEMA.TABLES")

row = cursor.fetchone()

while row:

print(row)

row = cursor.fetchone()

Regards,

Srisakthi

If this answer helps please mark "Accept as Solution"

Re: Select data from Lakehouses via Fabric API

Nicolas_DHONT — Thu, 15 May 2025 09:00:07 GMT

Hello,

Thank you @yvaubourg and @Srisakthi for your replies!

I tried the Python sript you provided @Srisakthi but this is not working due to MFA:

Is there another way to authenticate (maybe with "Application registration" from Azure Portal) to allow a script to automatically connect (disabling MFA on a user is not a best practice) ?

Thank you !

Re: Select data from Lakehouses via Fabric API

Srisakthi — Thu, 15 May 2025 09:17:31 GMT

Hi @Nicolas_DHONT ,

Yeah you can try with Service Principal, but few permissions needs to be enabled to use it.

Regards,

Srisakthi

Re: Select data from Lakehouses via Fabric API

jennratten — Fri, 16 May 2025 10:23:28 GMT

Hello @Nicolas_DHONT - you can query a lakehouse via an API call by creating a user data function. Below is a script for a UDF that I created which queries a lakehouse and returns a list of tables and their respective schemas.

You can customize this by editing the alias and SELECT statement sections which I've highlighted in the snip below.

Test the function by clicking the play icon from the function name in the function explorer. Then the Run pane will open on the right and you can click the Run button.

Once you are satisfied with the test result, click Generate invokation code and choose between invoking via Python or a Client and copy the respective script.

This will give you the code to copy so you can invoke the UDF externally.

UDF Script

import fabric.functions as fn udf = fn.UserDataFunctions() # Go to Manage Connections and add a connection to your lakehouse, note the alias # Replace "Community" with your actual Lakehouse connection alias @udf.connection(argName="lakehouse", alias="Community") @udf.function() def list_all_tables(lakehouse: fn.FabricLakehouseClient) -> list: connection = lakehouse.connectToSql() cursor = connection.cursor() # Query to list all tables in the Lakehouse cursor.execute("SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'") rows = [x for x in cursor] columnNames = [x[0] for x in cursor.description] tables = [] for row in rows: table_info = {} for col, val in zip(columnNames, row): table_info[col] = val tables.append(table_info) cursor.close() connection.close() return tables

Re: Select data from Lakehouses via Fabric API

v-priyankata — Mon, 19 May 2025 10:31:57 GMT

Hi @Nicolas_DHONT

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

Re: Select data from Lakehouses via Fabric API

Nicolas_DHONT — Mon, 19 May 2025 12:00:46 GMT

Thank you @Srisakthi !

I need to check how don the call with a Service Principal (an the app registration) but it looks the good option 😉

Thanks and best regards,
Nicolas

Re: Select data from Lakehouses via Fabric API

Nicolas_DHONT — Mon, 19 May 2025 13:19:33 GMT

Thank you very much @jennratten I will try this way next time !

Re: Select data from Lakehouses via Fabric API

Nicolas_DHONT — Mon, 19 May 2025 13:22:38 GMT

Hello,
Yes, I validated 2 different solutions
Thank you!