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

[AyusmanBasu0604]

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? 

[AyusmanBasu0604]

Thanks to each one of you for your suggestions and responses. @BhaveshPatel @spaceman127 @v-hjannapu 

[v-hjannapu]

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-materiali...
https://learn.microsoft.com/en-us/fabric/data-engineering/materialized-lake-views/get-started-with-m...

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

[spaceman127]

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

[BhaveshPatel · Accepted solution]

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