Forum Discussion

msprog's avatar
msprog
Icon for Advocate III rankAdvocate III
1 year ago
Solved

Pyspark notebook : Lakehouse Sql end point

Can a pyspark notebook in Fabric connecting to  the Lakehouse Sql endpoint? Please let me know   thanks  
notebook
  • tayloramy's avatar
    tayloramy
    1 year ago

    Hi msprog, 

     

    A Fabric PySpark notebook can’t “see” T-SQL views that live in a Lakehouse’s SQL analytics endpoint via the Spark catalog. Those views are objects of the SQL endpoint (TDS/T-SQL world), not Spark. But you can query them from a notebook by connecting to the SQL endpoint (via JDBC/TDS or the built-in Fabric Spark TDS reader). Alternatively, re-create the logic as a Spark view/table if you want native Spark access. 

     

     

    Query the view from a notebook

    1. Get your Workspace ID and the SQL endpoint name (Lakehouse’s SQL endpoint).

    2. In the notebook, use the Fabric Spark TDS reader (Scala cell) to run a T-SQL query and bring the result back as a Spark DataFrame.

     

    // Scala cell
import com.microsoft.spark.fabric.tds.implicits.read.FabricSparkTDSImplicits._
import com.microsoft.spark.fabric.Constants

val wsId = "<your-workspace-guid>"
val lakehouseSqlEndpointName = "<your-lakehouse-sql-endpoint-name>"

// Query the view
val df = spark.read
  .option(Constants.WorkspaceId, wsId)
  .option(Constants.DatabaseName, lakehouseSqlEndpointName)
  .synapsesql("select * from dbo.YourViewName");

display(df)

     

    Notes:

    If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution

     

AntoineW's avatar
AntoineW
Icon for Super User rankSuper User
1 year ago

Hello msprog

 

Yes—use a Fabric notebook attached to the Lakehouse. You don’t need (and can’t directly “bind”) the Lakehouse SQL endpoint from PySpark; instead you query the same Delta tables via Spark.

Two simple ways:

  1. Spark tables (recommended)

  • In the notebook, attach the Lakehouse (left pane → “Add lakehouse”).

  • Then query its tables:

 
# read a table registered in the Lakehouse df = spark.read.table("lakehouse.default.MyTable") # or "lakehouse.<schema>.MyTable" df.display()
 
# Spark SQL result = spark.sql("SELECT col1, col2 FROM lakehouse.default.MyTable WHERE col3 > 0") result.display()

  1. Delta path (Files/Delta)

 
# direct Delta path under /Tables df = spark.read.format("delta").load("Tables/MyTable") df.display()

Notes

  • %%sql in Fabric notebooks runs Spark SQL, not T-SQL. The Lakehouse SQL analytics endpoint is for T-SQL tools (SQL editor, SSMS, Fabric items using T-SQL).

  • For programmatic T-SQL against a Warehouse/SQL endpoint you’d use JDBC/ODBC from outside; inside Fabric notebooks, stick to Spark/Spark SQL for Lakehouse data.

 
 
Hope it can help you ! 
best regards,
Antoine
  • msprog's avatar
    msprog
    Icon for Advocate III rankAdvocate III
    1 year ago

    thanks for this. but i want to invoke a view that is defined - i can see the view when i am on the sql endpoint. Hence i was hoping if the notebook can see the endpoint, i would be able to fire a query using the view. 

    • tayloramy's avatar
      tayloramy
      Icon for Super User rankSuper User
      1 year ago

      Hi msprog, 

       

      A Fabric PySpark notebook can’t “see” T-SQL views that live in a Lakehouse’s SQL analytics endpoint via the Spark catalog. Those views are objects of the SQL endpoint (TDS/T-SQL world), not Spark. But you can query them from a notebook by connecting to the SQL endpoint (via JDBC/TDS or the built-in Fabric Spark TDS reader). Alternatively, re-create the logic as a Spark view/table if you want native Spark access. 

       

       

      Query the view from a notebook

      1. Get your Workspace ID and the SQL endpoint name (Lakehouse’s SQL endpoint).

      2. In the notebook, use the Fabric Spark TDS reader (Scala cell) to run a T-SQL query and bring the result back as a Spark DataFrame.

       

      // Scala cell
import com.microsoft.spark.fabric.tds.implicits.read.FabricSparkTDSImplicits._
import com.microsoft.spark.fabric.Constants

val wsId = "<your-workspace-guid>"
val lakehouseSqlEndpointName = "<your-lakehouse-sql-endpoint-name>"

// Query the view
val df = spark.read
  .option(Constants.WorkspaceId, wsId)
  .option(Constants.DatabaseName, lakehouseSqlEndpointName)
  .synapsesql("select * from dbo.YourViewName");

display(df)

       

      Notes:

      If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution

       

      • v-menakakota's avatar
        v-menakakota
        Icon for Community Support rankCommunity Support
        1 year ago

        Hi msprog   ,
        Thanks for reaching out to the Microsoft fabric community forum. 

         

        I would also take a moment to thank tayloramy , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference. 

        I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

        Best Regards, 
        Community Support Team.