Forum Discussion
Unable to call a Temp View in Fabric Notebook using Spark SQL
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 LearnFabric 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.
First of all thanks for your detailed suggestions HarishKM
point# 3 - With Global temp views also the issue is same as I had tired this multiple times earlier.
point# 5 - I tried saving the dataframe into a temp location, while that worked but while calling, I got the error: Py4JJavaError: An error occurred while calling o348.sql.
: java.lang.AssertionError: assertion failed: Only the following table types are supported: MANAGED, MATERIALIZED_LAKE_VIEW
I tried using a Materialized lake view and it comes back to the original error which I was getting while trying to reference a global/temp view directly into a Materialized Lake View: [TABLE_OR_VIEW_NOT_FOUND]
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.