Forum Discussion

CoulterJames2's avatar
CoulterJames2
Icon for Advocate II rankAdvocate II
7 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 in lakehouse 1

4. Create a MLV in lakehouse 2 that is just a SELECT from the table in lakehouse 1

5. Rename the workspace from DEMO to DEMO2

6. Refresh the MLV in lakehouse 2

 

After a while the refresh operation fails with the message [MLV_SCHEMA_NOT_FOUND] Cannot perform operation on DEMO 2.lh_silver.dbo.mlv_demo because default database is not defined.

 

You can still SELECT from the view but the only option to refresh it is to rename the workspace back to its original name or to drop and recreate the lakehouse. Is this a known problem? I cannot find any mention of this on the Known Issues page.

data engineering
  • apturlov's avatar
    apturlov
    7 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.

     

4 Replies

  • apturlov's avatar
    apturlov
    Icon for Super User rankSuper User
    7 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
    7 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!

     

  • v-prasare's avatar
    v-prasare
    Icon for Community Support rankCommunity Support
    7 months ago

    Hi CoulterJames2 ,

    We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

     

    apturlov & 4iurchenko, thanks for your prompt response

     

     

     

    Thank you for your patience and look forward to hearing from you.
    Best Regards,
    Prashanth Are
    MS Fabric community support