Select data from Lakehouses via Fabric API

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:

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