You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Justin Swanhart <gr...@gmail.com> on 2021/10/08 09:20:24 UTC
Error using pre-built materializations with JDBC schema sources
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.
Re: Error using pre-built materializations with JDBC schema sources
Posted by Justin Swanhart <gr...@gmail.com>.
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 <gr...@gmail.com> 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 <za...@gmail.com>
> 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 <gr...@gmail.com>
>> 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.
>> >
>>
>
Re: Error using pre-built materializations with JDBC schema sources
Posted by Justin Swanhart <gr...@gmail.com>.
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 <za...@gmail.com>
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 <gr...@gmail.com>
> 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.
> >
>
Re: Error using pre-built materializations with JDBC schema sources
Posted by Stamatis Zampetakis <za...@gmail.com>.
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 <gr...@gmail.com> 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.
>