Hi,
I am just swinging back around to this. Thank you for that pointer. I
still can't get the pre-built materializations working though, even though
I was able to get rid of that error:
I think this is the proper model for it
{
version: '1.0',
defaultSchema: 'sf1_warp',
schemas: [
{
name: 'sf1_warp',
type: 'jdbc',
jdbcUrl: 'jdbc:mysql://localhost/sf1_warp',
jdbcUser: 'root',
jdbcPassword: ''
}, {
name: 'm_schema',
materializations: [
{
view: 'mv_date_summary',
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 \"sf1_warp\".\"lineorder\" join
\"sf1_warp\".\"dim_date\" on \"LO_OrderDateKey\" = \"D_DateKey\" group by
\"D_Year\", \"D_Month\", \"D_DayNumInMonth\", \"LO_OrderDateKey\"",
viewSchemaPath: [ 'sf1_warp.lineorder', 'sf1_warp.dim_date',
'sf1_warp.mv_date_summary' ]
}
]
}
]
}
The problem I am now having is that Calcite does not recognize the view as
PRE-BUILT. From all of the examples I can see in the test suite (unless I
missed something) calcite builds the view and uses it at runtime. Here is
the error I am getting
sqlline> !connect jdbc:calcite:model=new.json admin admin
Transaction isolation level TRANSACTION_REPEATABLE_READ is not supported.
Default (TRANSACTION_NONE) will be used instead.
*0: jdbc:calcite:model=new.json> select count(*) from
"sf1_warp"."lineorder";Error: Error while executing SQL "select count(*)
from "sf1_warp"."lineorder"": While populating materialization [m_schema,
mv_date_summary] (state=,code=0)*
Instead of using the view that I have pre-populated into *table* it is
trying to compute the view and I guess store it in the table? It doesn't
give me a good error message as you can see.
Any pointers?
On Mon, Oct 11, 2021 at 5:29 AM Stamatis Zampetakis <[email protected]>
wrote:
> 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.
> >
>
