Forum Discussion

UdaySutar's avatar
UdaySutar
Frequent Visitor
1 year ago
Solved

Read view data using notebook

We are seeking to use a view within a SQL analytics Endpoint as a source of data for a dataframe in a notebook. When we seek to query it, we see an error like  [TABLE_OR_VIEW_NOT_FOUND] code lo...
data engineering
  • nilendraFabric's avatar
    nilendraFabric
    1 year ago

    Try this

     

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

    t_sql_query = """
    SELECT * FROM your_view_name
    """

    wsid = spark.conf.get("ws_id")
    lh_name = spark.conf.get("lh_name")

    df = spark.read.option(Constants.WorkspaceId, wsid).option(Constants.DatabaseName, lh_name).synapsesql(t_sql_query)

    df.createOrReplaceTempView("view_data")

  • V-yubandi-msft's avatar
    V-yubandi-msft
    1 year ago

    Hi UdaySutar ,

    It appears that this approach might not work as expected because views created in a SQL Analytics Endpoint aren’t visible on the Lakehouse side. The metadata sync only happens from the Lakehouse to the SQL Endpoint, not the other way around.

    As an alternative, 

    1. Use a JDBC connector in your notebook to directly query the view from the SQL Endpoint, or

    2. Recreate the view within the Lakehouse using Spark so that it becomes accessible in your notebook.

     

     If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

     

Vinodh247's avatar
Vinodh247
Super User
1 year ago

What do you get when you try this?

spark.sql("SHOW TABLES IN sales").show()

 

Things to check:

 

Case Sensitivity or Incorrect Naming:

  • Spark is case-sensitive by default. You must use exact case when referencing schema and view names.

  • For example: if the view is Sales.DailySummary, you must use that exact casing.

Default Database Context Missing:

  • If you are not specifying the full-qualified name (schema.viewname or database.schema.viewname), Spark may not know which catalog or schema to search.

View is Not Accessible from Spark:

  • The view must be created or published in the SQL analytics endpoint tied to the lakehouse or warehouse.

  • Make sure the view is materialized or created in the same SQL analytics eendpoint that your notebook is connected to.

View is a Virtual/External View Not Backed by Delta Lake or Tables:

  • Only views over actual physical tables (Delta format or supported filebacked) are accessible from Spark.

  • Avoid views built purely on unsupported or dynamic external sources.