How to create a Materialized View in Lakehouse? not in KQL Database

[Anonymous]

I have below code and want to create a Materialized View for it:

spark.sql("SELECT * FROM ParquetRainfallTable WHERE month == 'Apr' AND  \

          location = 'Clementi Road' ORDER BY rainfall desc").show(5)

## below is for Materialized View, but failed

# Create the Materialized View for Rainfall data

query = """

CREATE MATERIALIZED VIEW IF NOT EXISTS AprilRainfall_mv

AS

SELECT * FROM ParquetRainfallTable WHERE month == 'Apr' AND  

         location = 'Clementi Road' ORDER BY rainfall desc

"""

spark.sql(query)

ParseException: [PARSE_SYNTAX_ERROR] Syntax error at or near 'MATERIALIZED'.(line 2, pos 7) == SQL == CREATE MATERIALIZED VIEW IF NOT EXISTS AprilRainfall_mv -------^^^ AS SELECT * FROM ParquetRainfallTable WHERE month == 'Apr' AND location = 'Clementi Road' ORDER BY rainfall desc

So, does Fabric Lakehouse support CREATE MATERIALIZED VIEW in Spark SQL??

[AyusmanBasu0604]

Based on what I have explored and figured out, MLVs can only be created within lakehouses that have Lakehouse schema enabled. if your Lakehouse doesn't have schema enabled you need to create a new one as there is no option to enable Lakehouse Schemas for an already existing Lakehouse, not yet at least for now. Please refer the response in my post here: https://community.fabric.microsoft.com/t5/Fabric-platform/Can-we-enable-Lakehouse-Schemas-for-an-alr... 

[FilipAi]

First of all I would remove one of the equal signs in the WHERE clause, to make it read "month = 'Apr'". Does the error still persist after that?

[nilendraFabric · Accepted solution]

Hello @Anonymous 

the Spark SQL engine in Fabric Lakehouse does not recognize the materialized view syntax. In Microsoft Fabric, while materialized views exist, they are not created using the standard Spark SQL statement. Instead, materialized views in Fabric are designed to be created through a KQL-based interface

in kusto query: 

.create materialized-view [ifnotexists] <ViewName> on table <SourceTable>
{
// Your summarize-based aggregation query here
}

use %kusto magic command 

if this is helpful please accept the solution