Forum Discussion

AyusmanBasu0604's avatar
AyusmanBasu0604
Icon for Helper II rankHelper II
1 year ago
Solved

How to create a Materialized Lake View out of a Data Frame?

I am trying to create a Materialized Lake View in Fabric based on a dataFrame. Since, it seem impossible directly (unless anobody can suggest)

I tried to create both a temporary view/ Global temporary view using: 

createOrReplaceTempView
 
But, everytime I try to reference this temp view inside the Materialized Lake View definition it is unable to reference the temp view from the session and always try to searach it within the current Fabric workspace/lakehouse context.
template:

df = spark.createDataFrame([(2, "Alice"), (5, "Bob")], schema=["age", "name"])
df.createOrReplaceTempView("people")

createView = f"""
        CREATE MATERIALIZED LAKE VIEW IF NOT EXISTS vw_tempTest AS
        SELECT * FROM people
        """
result = spark.sql(createView)
display(result)

Error: AnalysisException: [TABLE_OR_VIEW_NOT_FOUND] The table or view `Workspace_name`.`Lakehouse_name`.`dbo`.`people` cannot be found. Verify the spelling and correctness of the schema and catalog.
 
Any suggestions or workarounds please? 
lakehouse
  • BhaveshPatel's avatar
    BhaveshPatel
    1 year ago

    For that, you have to understand bronze, silver and gold tables. 

     

    So, this is how it works:

     

    Python --> Apache Spark ( spark ) --> delta lake ( parquet with _delta_log transaction log) ( bronze, silver and gold tables )  .

     

    In Delta Lake, we are implementing bronze tables. Once the initial table is created in bronze tables, then we will proceed to materialised lake view ( silver tables ), and finally we will implement the final gold tables.

     

    so here First, create a apache spark ( dataframe  ) using pyspark which is correct in your instance ( df ) . then convert it into bronze delta tables . For that, 

     
    df.write.mode("overwrite").option("overwriteSchema", "true").format("delta").saveAsTable("Tables")  ( # creating bronze delta tables ) 
     
    Now, once bronze delta tables are created, then you are going to implement silver tables, aka Materialised Silver Tables. 
    createView = f"""
            CREATE MATERIALIZED LAKE VIEW IF NOT EXISTS vw_Tables AS
            SELECT * FROM Tables
            """
    result = spark.sql(createView)
    display(result)
     
    Here you are going to implement ETL using Delta Lake silver tables and finally you are preparing Final Gold Tables
     

4 Replies

  • BhaveshPatel's avatar
    BhaveshPatel
    Icon for Super User rankSuper User
    1 year ago

    For that, you have to understand bronze, silver and gold tables. 

     

    So, this is how it works:

     

    Python --> Apache Spark ( spark ) --> delta lake ( parquet with _delta_log transaction log) ( bronze, silver and gold tables )  .

     

    In Delta Lake, we are implementing bronze tables. Once the initial table is created in bronze tables, then we will proceed to materialised lake view ( silver tables ), and finally we will implement the final gold tables.

     

    so here First, create a apache spark ( dataframe  ) using pyspark which is correct in your instance ( df ) . then convert it into bronze delta tables . For that, 

     
    df.write.mode("overwrite").option("overwriteSchema", "true").format("delta").saveAsTable("Tables")  ( # creating bronze delta tables ) 
     
    Now, once bronze delta tables are created, then you are going to implement silver tables, aka Materialised Silver Tables. 
    createView = f"""
            CREATE MATERIALIZED LAKE VIEW IF NOT EXISTS vw_Tables AS
            SELECT * FROM Tables
            """
    result = spark.sql(createView)
    display(result)
     
    Here you are going to implement ETL using Delta Lake silver tables and finally you are preparing Final Gold Tables
     
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi AyusmanBasu0604.,

    Thank you  for reaching out to the Microsoft fabric community forum.

    I would also take a moment to thank spaceman127, BhaveshPatel  for actively participating in the community forum and for the solutions you have been sharing in the community forum. Your contributions make a real difference.

    In Fabric, you can’t create a Materialized Lake View directly from a temporary Spark view. That’s why it’s showing the table not found error  the temp view exists only in your current Spark session, not in the Lakehouse.

    In addition to the suggested answers above, please review the documentation below as it may help you resolve your issue.
    https://learn.microsoft.com/en-us/fabric/data-engineering/materialized-lake-views/tutorial
    https://learn.microsoft.com/en-us/fabric/data-engineering/materialized-lake-views/overview-materialized-lake-view
    https://learn.microsoft.com/en-us/fabric/data-engineering/materialized-lake-views/get-started-with-materialized-lake-views

    Hope this helps if you have any queries we are  happy to assist you further.
    Regards,
    Harshitha.

  • spaceman127's avatar
    spaceman127
    Icon for Super User rankSuper User
    1 year ago

    Hello AyusmanBasu0604 ,

    You cannot create the view directly from a Dataframe.

    Instead, you must go though the created table and then create the view.

    This has BhaveshPatel  already been added as part of the solution in his answer.

     

    Best regards