Hi Justin, There are a few tests similar to your use case in the repository; check MaterializationTest#testViewSchemaPath [1] for instance. Maybe you can take inspiration from there.
Best, Stamatis [1] https://github.com/apache/calcite/blob/460de048042a4062bebbc08f27199a14ba7503d2/core/src/test/java/org/apache/calcite/test/MaterializationTest.java#L198 On Fri, Oct 8, 2021 at 11:20 AM Justin Swanhart <[email protected]> wrote: > I have several materialized views in a MySQL database that I would like to > use for materialized view rewrite. > > For example I have a "materialized view" named mv_date_summary which for > simplicity could be created with: > create table mv_date_summary as select LO_OrderDateKey, D_Year, D_Month, > D_DayNumInMonth, count(*) the_cnt, sum(LO_Quantity * LO_ExtendedPrice) > the_sum from lineorder join dim_date on LO_OrderDateKey = D_DateKey group > by D_Year, D_Month, D_DayNumInMonth, LO_OrderDateKey; > > I have created a model with the following JSON: > { > version: '1.0', > defaultSchema: 'ssb', > schemas: [ > { > name: 'ssb', > type: 'jdbc', > jdbcUrl: 'jdbc:mysql://localhost:3306/ssb', > jdbcUser: 'root', > jdbcPassword: '', > > materializations: [ > { table: "mv_date_summary", > sql: "select \"LO_OrderDateKey\", \"D_Year\", \"D_Month\", > \"D_DayNumInMonth\", count(*) \"the_cnt\", sum(\"LO_Quantity\" * > \"LO_ExtendedPrice\") \"the_sum\" from \"ssb\".\"lineorder\" join > \"ssb\".\"dim_date\" on \"LO_OrderDateKey\" = \"D_DateKey\" group by > \"D_Year\", \"D_Month\", \"D_DayNumInMonth\", \"LO_OrderDateKey\"" > } > ] > } > ] > } > > However, when I try to use the model in sqlline, I get an error: > Caused by: java.lang.RuntimeException: Cannot define materialization; > parent schema 'ssb' is not a SemiMutableSchema > at org.apache.calcite.model.ModelHandler.visit(ModelHandler.java:349) > > I tried with CustomModel with a JDBC type factory, but I got the same > error. >
