Hi,
I think this might be caused by placing the materializations in another
schema, which is what the example does, it appears to place them in a
JavaReflectionSchema$Factory but I don't have another schema in which the
materialized views are placed, they are existing tables in the source
schema.
Like before, if I try to attach materializations directly to the JDBC
schema, I get the following error:
sqlline> !connect jdbc:calcite:model=new2.json admin admin
...
Caused by: java.lang.RuntimeException: Cannot define materialization;
parent schema 'sf1_warp' is not a SemiMutableSchema
at org.apache.calcite.model.ModelHandler.visit(ModelHandler.java:349)
... 21 more
sqlline>
Here is the model for that error just in case:
justin@ubuntu:~/calcite$ cat new2.json
{
version: '1.0',
defaultSchema: 'sf1_warp',
schemas: [
{
name: 'sf1_warp',
type: 'jdbc',
jdbcUrl: 'jdbc:mysql://localhost/sf1_warp',
jdbcUser: 'root',
jdbcPassword: '',
materializations: [
{ view: 'mv0' ,
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\""
}
]
}
]
}
I can't see how to use existing materializations with the JDBC adapter and
the test cases don't seem to help me. Any suggestions would be appreciated.
On Thu, Jan 6, 2022 at 3:11 PM Justin Swanhart <[email protected]> wrote:
> 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.
>> >
>>
>
