Forum Discussion

AyusmanBasu0604's avatar
AyusmanBasu0604
Helper II
1 year ago
Solved

Unable to call a Temp View in Fabric Notebook using Spark SQL

I am trying to refer a temp view that I have created but unable to do so even using the catalog schema: global_temp statement: %%sql CREATE OR REPLACE TEMP VIEW temp_holidays AS SELECT * F...
lakehouse
  • v-echaithra's avatar
    v-echaithra
    1 year ago

    Hi @AyusmanBasu0604,

    Thank you for your response, you're encountering known limitations in Microsoft Fabric's Lakehouse when working with temporary views and Materialized Lake Views (MLVs) using Spark SQL.
    Reference: Lakehouse schemas (Preview) - Microsoft Fabric | Microsoft Learn

    Fabric Lakehouse does not support reading from temporary or global temporary views in certain operations especially when working with Materialized Lake Views, which have stricter limitations, referencing views created using Spark SQL in another SQL statement or notebook cell or trying to use TEMP VIEW or GLOBAL TEMP VIEW in MLV creation or inside CREATE OR REPLACE VIEW.

    Maintain consistent namespace conventions for all views and tables. This will ensure they are accessible within your SQL environment. If necessary, re-declare or remap contexts to resolve any discrepancies.Activate detailed logging of PySpark operations to identify any hidden discrepancies in SQL calls. This can help pinpoint the exact location where the table or view is not being found, allowing you to adjust your code accordingly. Ensure that the Materialized Lake View is created with the correct SQL schema definitions. Check for any syntax errors or schema mismatches that could cause issues.

    You can try this workaround, Use a Staging Delta Table, instead of using TEMP VIEW, create a real physical Delta table (possibly under a temporary/staging schema), then refer to it. Then, use it in your MLV. This works because you’re using a physical Delta table. Spark and Fabric support referencing it in MLVs.

    Hope this helps.
    Best Regards
    Chaithra E.

HarishKM's avatar
HarishKM
Super User
1 year ago

AyusmanBasu0604 Hey,
The error is related to limitations regarding Spark views in Lakehouse environments,

I will follow up below steps to verify info

1)  Ensure that the schema and table formats are compatible with Spark.

2) Sometimes, issues arise from unsupported data types or formats in the Lakehouse environment.


3) instead of creating a temporary view, you might consider using a global temporary view, which persists across all sessions until the Spark application terminates:
Try this code - 

from pyspark.sql

import SparkSession spark.sql("CREATE OR REPLACE GLOBAL TEMP VIEW temp_grades AS SELECT * FROM Bronze.tbl_grades") df = spark.sql("SELECT * FROM global_temp.temp_grades") df.show()

 

4) If you can't use views, consider caching the DataFrame. This approach won't directly replace a materialized view but can improve performance

df = spark.sql("SELECT * FROM Bronze.tbl_grades") df.cache() # Perform transformations df.show()

 

5) If the only reason to avoid DataFrames is due to materialized views, consider saving the transformed DataFrame to a temporary location and then recreating the view/table. While this approach uses more I/O, it might work:

 

df = spark.sql("SELECT * FROM Bronze.tbl_grades") # Perform transformations df.write.mode("overwrite").save("/path/to/temp/location") # Load back from temp location into a new table/view spark.sql("CREATE OR REPLACE VIEW new_table AS SELECT * FROM delta.`/path/to/temp/location`")

 

 

Thanks

Harish M