Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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

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??

 

data engineering
lakehouse
  • nilendraFabric's avatar
    nilendraFabric
    1 year ago

    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 

3 Replies

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

    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 

  • FilipAi's avatar
    FilipAi
    Icon for Helper I rankHelper I
    1 year ago

    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?

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

    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-already-existing/m-p/4788037#M19363