Forum Discussion

raiajithkumar's avatar
raiajithkumar
Regular Visitor
4 months ago
Solved

User data functions : How to access Lakehouse Fabric SQL Endpoint

Hi Team, I’m trying to access a Lakehouse SQL analytics endpoint from a Fabric User Data Function (UDF) using the documented approach. https://learn.microsoft.com/en-us/fabric/data-engineering/us...
workspaces
  • Aparnaa_MS's avatar
    Aparnaa_MS
    4 months ago

    Hi raiajithkumar 

     

    Accessing a Lakehouse SQL analytics endpoint from a Fabric User Data Function is supported in read-only mode, but the UDF execution context does not set the Lakehouse as the default database; as a result, it might fail.
     
    Why don't you try with a three-part names such as lakehousename.dbo.tablename 
     
    The following worked for me: 
     
    @udf.connection(argName="lakehouse", alias="directdacpacdep")
    @udf.function()
    def get_data(lakehouse: fn.FabricLakehouseClient) -> list:
        conn = lakehouse.connectToSql()
        cursor = conn.cursor()
        cursor.execute("""
            SELECT TOP 10 *
            FROM direct_dacpac_deploy.dbo.dimension_city
        """)
        return cursor.fetchall()
     
    Additionally to answer your questions 
     
    Qn1:  Yes, this scenario is supported in read‑only mode. You can connect by connectToSql() but they cannot perform inserts, updates, deletes, or execute Spark operations only select.
     

    Qn2:  Yes. In a UDF, the SQL endpoint connection does not set the Lakehouse as the default database, so 3 part naming system is required.

     

    Hope this helps!

     

    Thanks 

    Aparnaa

  • raiajithkumar's avatar
    raiajithkumar
    4 months ago

    Thank you everyone for your responses and guidance. I was able to get this working and wanted to share a complete working sample along with a few key observations that might help others.

     

     

    Sample Code (Working)

     

    import fabric.functions as fn

    udf = fn.UserDataFunctions()

    @udf.connection(argName="lakehouse", alias="LHNAME_Test")
    @udf.function()
    def get_lakehouse_data(lakehouse: fn.FabricLakehouseClient) -> list:

    conn = lakehouse.connectToSql()
    cursor = conn.cursor()

    # Note: Use schema.table (avoid database.schema.table)
    cursor.execute("SELECT TOP 10 * FROM LHNAME_Test.dbo.cdm_job")

    columns = [desc[0] for desc in cursor.description]
    rows = cursor.fetchall()

    data = [dict(zip(columns, row)) for row in rows]

    cursor.close()
    conn.close()

    return data

     

    Key Observations / Learnings

    1. Library Management Issues

    I encountered the following error:

    ModuleNotFoundError: No module named 'fabric'

ERROR: Cannot install fabric-user-data-functions ... 
because of conflicting dependencies (pyarrow etc.)

    👉 Root cause:

    • Multiple versions of fabric-user-data-functions were added
    • Conflict with pre-installed dependencies in Fabric runtime

    👉 Resolution:

    • Remove all duplicate versions from Library Management

    • Add only a single entry:

      fabric-user-data-functions
    • Do not manually add pyarrow

     

raiajithkumar's avatar
raiajithkumar
Regular Visitor
4 months ago

Thank you everyone for your responses and guidance. I was able to get this working and wanted to share a complete working sample along with a few key observations that might help others.

 

 

Sample Code (Working)

 

import fabric.functions as fn

udf = fn.UserDataFunctions()

@udf.connection(argName="lakehouse", alias="LHNAME_Test")
@udf.function()
def get_lakehouse_data(lakehouse: fn.FabricLakehouseClient) -> list:

conn = lakehouse.connectToSql()
cursor = conn.cursor()

# Note: Use schema.table (avoid database.schema.table)
cursor.execute("SELECT TOP 10 * FROM LHNAME_Test.dbo.cdm_job")

columns = [desc[0] for desc in cursor.description]
rows = cursor.fetchall()

data = [dict(zip(columns, row)) for row in rows]

cursor.close()
conn.close()

return data

 

Key Observations / Learnings

1. Library Management Issues

I encountered the following error:

ModuleNotFoundError: No module named 'fabric'

ERROR: Cannot install fabric-user-data-functions ... 
because of conflicting dependencies (pyarrow etc.)

👉 Root cause:

  • Multiple versions of fabric-user-data-functions were added
  • Conflict with pre-installed dependencies in Fabric runtime

👉 Resolution:

  • Remove all duplicate versions from Library Management

  • Add only a single entry:

    fabric-user-data-functions
  • Do not manually add pyarrow