Forum Discussion

CoulterJames2's avatar
CoulterJames2
Icon for Advocate II rankAdvocate II
9 months ago
Solved

Materialized Lake View Refresh fails if Workspace is renamed

Hi, I have come across an issue with MLV in Fabric.   1. Create a workspace called DEMO 2. Create two lakehouses, one representing bronze layer and one representing silver layer 3. Create a table...
data engineering
  • apturlov's avatar
    apturlov
    9 months ago

    Hi CoulterJames2. Could you please provide details about how you created and refresh your MLV (Materialized Lake View)?

     

    The likely reason why you are seeing this error is because when you create your MLV using Spark SQL in a Notebook you use a three-part name for the source table(s) [workspace.lakehouse.schema].MLV_NAME. Because MLV is essentially a delta table on OneLake, its data is stored in the OneLake file system and its metadata is stored in the OneLake catalog. When you renamed the workspace the file path to the MLV data changed but the metadata was not updated. You need to update your MLV metadata by updating your Spark SQL script with the correct fully qualified name for the origin table. You need to use Spark SQL script for that:

    CREATE [OR REPLACE] MATERIALIZED LAKE VIEW [IF NOT EXISTS][workspace.lakehouse.schema].MLV_Identifier 
[( 
    CONSTRAINT constraint_name1 CHECK (condition expression1)[ON MISMATCH DROP | FAIL],  
    CONSTRAINT constraint_name2 CHECK (condition expression2)[ON MISMATCH DROP | FAIL] 
)] 
[PARTITIONED BY (col1, col2, ... )] 
[COMMENT "description or comment"] 
[TBLPROPERTIES ("key1"="val1", "key2"="val2", ... )] 
AS select_statement

    You can read more here Spark SQL Reference for Materialized Lake Views - Microsoft Fabric | Microsoft Learn

     

    If you find this answer useful or solving your problem please consider giving kudos and/or marking as a solution.

     

4iurchenko's avatar
4iurchenko
Icon for Advocate III rankAdvocate III
9 months ago

Hi,

 

I was able to reproduce the steps. I was also able to observe a very similar issue.

I believe it is a current limitation. But I can't guarantee 100%. This feature is now in preview.

 

So, I did exactly what you did. But, instead of waiting, I ran that in the Notebook:

%%sql
REFRESH MATERIALIZED LAKE VIEW CoreGold.gold.fact_claims FULL

It gave me that:

[MLV_SCHEMA_NOT_FOUND] Cannot perform operation on CoreData2.CoreGold.gold.fact_claims because default database is not defined failureType: UserError errorDetails: SqlAnalysisException: [MLV_SCHEMA_NOT_FOUND] Cannot perform operation on CoreData2.CoreGold.gold.fact_claims because default database is not defined failureType: UserError errorDetails: No additional trace available [ErrorClass: MLV_SCHEMA_NOT_FOUND]
org.apache.spark.sql.fabric.materialized.lake.views.analyzer.v2.MaterializedLakeViewAnalyzerBaseV2.validateDefaultDatabase(MaterializedLakeViewAnalyzerBaseV2.scala:643)

 

After, I tried to rename the view. Successfully. But the data wasn't refreshed. So, it doesn't havy any effect.

Last step was to recreate the view:

%%sql
DROP MATERIALIZED LAKE VIEW IF EXISTS CoreGold.gold.fact_claims_v2;

CREATE MATERIALIZED LAKE VIEW IF NOT EXISTS gold.fact_claims 
AS
SELECT 
    patient_id,
    encounter_id,
    event_ts,
    age
FROM stresstest_hc_claims_delta_silver;

It actually helped to resolve the problem.

 

Conclusion:

1. Option One: waiting on the new feature which solves that

2. Option Two: recreating the view(s) when you change the name of the workspace. 

 

If you use it for the Demos, it may create some inconvenience, but if you have a one notebook, it may be easier than a disaster.

 

That's it from me. Thanks for the good question. If this info helped you out, a thumbs-up would be awesome! Marking that answer as a solution is highly appreciated. Best of luck, and happy fabric-ing and engineering!