Re: Query Compilation happening more often then expected

[Julian Hyde]

I found evidence that MSSQL[1] and Sybase ASE[2] do it.

I agree, it's not a free lunch. For instance, if a column has a
non-uniform distribution, some values might be much more selective
than others, and it would be much better to know which value you are
dealing with at planning time, rather than execution time.

Julian

[1] 
https://docs.microsoft.com/en-us/sql/relational-databases/performance/specify-query-parameterization-behavior-by-using-plan-guides?view=sql-server-2017

[2] 
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00743.1570/html/queryprocessing/BIIIBEJJ.htm

On Mon, Sep 16, 2019 at 3:36 PM Stamatis Zampetakis  wrote:
>
> Out of curiosity does anybody know if popular DBMS (Postgres, Oracle, SQL
> Server, etc.) support "hoisting"?
>
> Performing it all the time does not seem a very good idea (constant
> reduction, histograms, and other optimization techniques would be
> impossible)
> while leaving its configuration to the end-user may not be a
> straightforward decision.
>
> On Sat, Sep 14, 2019 at 4:29 PM Julian Hyde  wrote:
>
> > The idea of converting literals into bind variables is called “hoisting”.
> > We had the idea a while ago but have not implemented it.
> >
> > https://issues.apache.org/jira/browse/CALCITE-963
> >
> > Until that feature is implemented, you will need to create bind variables
> > explicitly, and bind them before executing the query.
> >
> > Julian
> >
> > > On Sep 13, 2019, at 4:39 PM, Scott Reynolds 
> > wrote:
> > >
> > > Hi,
> > >
> > > Spent a bunch of time researching and staring at code today to understand
> > > the code compilation path within Calcite. I started down this path
> > because
> > > we noticed whenever we changed the `startDate` or `endDate` for the query
> > > it went through compilation process again. We expected it to use the
> > > previous classes `bind` it with the new RexLiterals. I was *hoping*  the
> > > RexLiterals were passed into the `bind()` method but that does not appear
> > > to be the main goal of `DataContext` objects.
> > >
> > > We also found the trick Kylin did to improve their query compilation with
> > > prepared statements:
> > > https://issues.apache.org/jira/browse/KYLIN-3434 but PreparedStatement
> > is
> > > stateful and I don't believe a good way to solve this issue.
> > >
> > > I would like to propose a change to Calcite so that Filters are passed
> > into
> > > the `bind()` call alongside or within DataContext. This would allow the
> > > `EnumerableRel` implementations to reference the `Filters` as arguments.
> > > This -- I believe -- would cause any change to the filters to use
> > > the previously compiled class instead of generating a brand new one.
> > >
> > > I am emailing everyone on this list for two reasons:
> > > 1. Is this a bad idea ?
> > > 2. I don't have a design yet so would love any ideas. Should we stick
> > more
> > > stuff into `DataContext`? Should `EnumerableRel` have another method that
> > > is used to gather these RexLiterals?
> >

Re: auto-format or fix checkstyle by maven command

[Danny Chan]

If you use IntelliJ IDEA for developing, you can generate a check style conf 
xml file for Calcite, then use this conf file in your code style config for 
Java. You should first download a check style plugin.

Best,
Danny Chan
在 2019年9月17日 +0800 AM3:09，Rui Wang ，写道：
> Hi community,
>
> Is there a maven command or auto-formatter to fix checkstyle error when
> developing in Calcite repo? I am less familiar with maven and did some
> searches in both [1] and Google but found no luck.
>
>
> [1]: https://calcite.apache.org/develop/
>
> -Rui

Re: auto-format or fix checkstyle by maven command

[Rui Wang]

The minimum spotless maven can do is:

remove unused import
correct import order
check license header

To use it is also easy after enabling it in maven pom:
mvn spotless:check // check the style
mvn spotless:apply // apply spotless fix

The difference though is the default setting of code style of spotless
differs from maven checkstyle (for example: import ordering is difference).
Spotless's style can be also configurable by a file so seems it's feasible
to match maven checkstyle.


-Rui

On Mon, Sep 16, 2019 at 3:10 PM Rui Wang  wrote:

> Thanks. I will try to setup IDE then.
>
> I don't have a clear idea how to use spotless for maven. I could spend
> some time to explore it and if it's easy to setup, I will report it back to
> you.
>
>
> -Rui
>
> On Mon, Sep 16, 2019 at 12:51 PM Vladimir Sitnikov <
> sitnikov.vladi...@gmail.com> wrote:
>
>> Rui>Import order and length of a single line of code is too long were
>> what I
>> faced.
>>
>> I guess both of them could be configured in IDE.
>> Even though it might look boring/complicated, configuring import order in
>> IDE pays off quickly.
>>
>> Rui>In Apache Beam, we are using gradle and have adopted diffplug/spotless
>>
>> It looks like there's spotless-maven-plugin, so we might move certain
>> checkstyle rules to spotless even before we migrate to Gradle.
>> Do you have cycles to implement relevant Spotless configuration?
>>
>> Vladimir
>>
>

Re: Query Compilation happening more often then expected

[Stamatis Zampetakis]

Out of curiosity does anybody know if popular DBMS (Postgres, Oracle, SQL
Server, etc.) support "hoisting"?

Performing it all the time does not seem a very good idea (constant
reduction, histograms, and other optimization techniques would be
impossible)
while leaving its configuration to the end-user may not be a
straightforward decision.

On Sat, Sep 14, 2019 at 4:29 PM Julian Hyde  wrote:

> The idea of converting literals into bind variables is called “hoisting”.
> We had the idea a while ago but have not implemented it.
>
> https://issues.apache.org/jira/browse/CALCITE-963
>
> Until that feature is implemented, you will need to create bind variables
> explicitly, and bind them before executing the query.
>
> Julian
>
> > On Sep 13, 2019, at 4:39 PM, Scott Reynolds 
> wrote:
> >
> > Hi,
> >
> > Spent a bunch of time researching and staring at code today to understand
> > the code compilation path within Calcite. I started down this path
> because
> > we noticed whenever we changed the `startDate` or `endDate` for the query
> > it went through compilation process again. We expected it to use the
> > previous classes `bind` it with the new RexLiterals. I was *hoping*  the
> > RexLiterals were passed into the `bind()` method but that does not appear
> > to be the main goal of `DataContext` objects.
> >
> > We also found the trick Kylin did to improve their query compilation with
> > prepared statements:
> > https://issues.apache.org/jira/browse/KYLIN-3434 but PreparedStatement
> is
> > stateful and I don't believe a good way to solve this issue.
> >
> > I would like to propose a change to Calcite so that Filters are passed
> into
> > the `bind()` call alongside or within DataContext. This would allow the
> > `EnumerableRel` implementations to reference the `Filters` as arguments.
> > This -- I believe -- would cause any change to the filters to use
> > the previously compiled class instead of generating a brand new one.
> >
> > I am emailing everyone on this list for two reasons:
> > 1. Is this a bad idea ?
> > 2. I don't have a design yet so would love any ideas. Should we stick
> more
> > stuff into `DataContext`? Should `EnumerableRel` have another method that
> > is used to gather these RexLiterals?
>

Re: auto-format or fix checkstyle by maven command

[Rui Wang]

Thanks. I will try to setup IDE then.

I don't have a clear idea how to use spotless for maven. I could spend some
time to explore it and if it's easy to setup, I will report it back to you.


-Rui

On Mon, Sep 16, 2019 at 12:51 PM Vladimir Sitnikov <
sitnikov.vladi...@gmail.com> wrote:

> Rui>Import order and length of a single line of code is too long were what
> I
> faced.
>
> I guess both of them could be configured in IDE.
> Even though it might look boring/complicated, configuring import order in
> IDE pays off quickly.
>
> Rui>In Apache Beam, we are using gradle and have adopted diffplug/spotless
>
> It looks like there's spotless-maven-plugin, so we might move certain
> checkstyle rules to spotless even before we migrate to Gradle.
> Do you have cycles to implement relevant Spotless configuration?
>
> Vladimir
>

Re: auto-format or fix checkstyle by maven command

[Vladimir Sitnikov]

Rui>Import order and length of a single line of code is too long were what I
faced.

I guess both of them could be configured in IDE.
Even though it might look boring/complicated, configuring import order in
IDE pays off quickly.

Rui>In Apache Beam, we are using gradle and have adopted diffplug/spotless

It looks like there's spotless-maven-plugin, so we might move certain
checkstyle rules to spotless even before we migrate to Gradle.
Do you have cycles to implement relevant Spotless configuration?

Vladimir

Re: auto-format or fix checkstyle by maven command

[Julian Hyde]

I’m sorry that we do not have the technology to auto-correct. People also 
complain that there are coding standards that are not captured in check style 
rules, so we are caught between a rock and a hard place.

I agree, something like https://github.com/editorconfig/editorconfig/issues/231 
, so that the editor 
is configured based on the project definition (pom.xml) would be nice.

Never underestimate the power of “git diff”. Check to see whether your editor 
has made changes that are not helpful, and back them out before submitting the 
PR. Then change your editor’s config so that next time it either the right 
thing or does nothing.

Julian



> On Sep 16, 2019, at 12:26 PM, Vladimir Sitnikov  
> wrote:
> 
> Rui>when developing in Calcite repo?
> 
> Hi, could you please clarify which issues do you run into the most?
> 
> Rui>auto-formatter to fix checkstyle error
> 
> That would really be awesome.
> Unfortunately, my experience with Checkstyle project 5 years ago was that
> core developers did not value "automatic fix of the issues".
> 
> See example: https://github.com/checkstyle/checkstyle/pull/164
> I used to run into "import order is wrong" a lot, and Checkstyle is not
> very helpful with explaining on what should be done.
> 
> However, then I configured import order, and now it generates the proper
> import order by default.
> It would be great if editorconfig implements
> https://github.com/editorconfig/editorconfig/issues/231 so IDEs could take
> configuration from there.
> 
> 
> Recently I've discovered https://github.com/diffplug/spotless , and I like
> how it prints sensible messages, and it allows to apply lots of fixes
> automatically.
> See example:
> https://travis-ci.org/vlsi/vlsi-release-plugins/jobs/584025923#L410
> We might want to migrate certain checks to spotless so the violations
> become fixable automatically.
> 
> Vladimir

Re: auto-format or fix checkstyle by maven command

[Rui Wang]

Import order and length of a single line of code is too long were what I
faced.

In Apache Beam, we are using gradle and have adopted
https://github.com/diffplug/spotless. So there is a command to fix unused
import, import ordering, line length, etc there (gradle spotlessApply).


-Rui




On Mon, Sep 16, 2019 at 12:27 PM Vladimir Sitnikov <
sitnikov.vladi...@gmail.com> wrote:

> Rui>when developing in Calcite repo?
>
> Hi, could you please clarify which issues do you run into the most?
>
> Rui>auto-formatter to fix checkstyle error
>
> That would really be awesome.
> Unfortunately, my experience with Checkstyle project 5 years ago was that
> core developers did not value "automatic fix of the issues".
>
> See example: https://github.com/checkstyle/checkstyle/pull/164
> I used to run into "import order is wrong" a lot, and Checkstyle is not
> very helpful with explaining on what should be done.
>
> However, then I configured import order, and now it generates the proper
> import order by default.
> It would be great if editorconfig implements
> https://github.com/editorconfig/editorconfig/issues/231 so IDEs could take
> configuration from there.
>
>
> Recently I've discovered https://github.com/diffplug/spotless , and I like
> how it prints sensible messages, and it allows to apply lots of fixes
> automatically.
> See example:
> https://travis-ci.org/vlsi/vlsi-release-plugins/jobs/584025923#L410
> We might want to migrate certain checks to spotless so the violations
> become fixable automatically.
>
> Vladimir
>

Re: auto-format or fix checkstyle by maven command

[Vladimir Sitnikov]

Rui>when developing in Calcite repo?

Hi, could you please clarify which issues do you run into the most?

Rui>auto-formatter to fix checkstyle error

That would really be awesome.
Unfortunately, my experience with Checkstyle project 5 years ago was that
core developers did not value "automatic fix of the issues".

See example: https://github.com/checkstyle/checkstyle/pull/164
I used to run into "import order is wrong" a lot, and Checkstyle is not
very helpful with explaining on what should be done.

However, then I configured import order, and now it generates the proper
import order by default.
It would be great if editorconfig implements
https://github.com/editorconfig/editorconfig/issues/231 so IDEs could take
configuration from there.


Recently I've discovered https://github.com/diffplug/spotless , and I like
how it prints sensible messages, and it allows to apply lots of fixes
automatically.
See example:
https://travis-ci.org/vlsi/vlsi-release-plugins/jobs/584025923#L410
We might want to migrate certain checks to spotless so the violations
become fixable automatically.

Vladimir

auto-format or fix checkstyle by maven command

[Rui Wang]

Hi community,

Is there a maven command or auto-formatter to fix checkstyle error when
developing in Calcite repo? I am less familiar with maven and did some
searches in both [1] and Google but found no luck.


[1]: https://calcite.apache.org/develop/

-Rui

[jira] [Created] (CALCITE-3351) calcite mysql utf8中文查询报错

[cui (Jira)]

cui created CALCITE-3351:


 Summary: calcite mysql utf8中文查询报错
 Key: CALCITE-3351
 URL: https://issues.apache.org/jira/browse/CALCITE-3351
 Project: Calcite
  Issue Type: Bug
  Components: core
Affects Versions: 1.19.0
 Environment: 无论是windows还是mac，都会出同样的错误
Reporter: cui


发现连接mysql进行中文查询时候，执行这条sql：

select * from \"test\".\"score_new\" where \"name\"= \'催\' limit 1报错

，后来修改了RelDataTypeFactoryImpl的

getDefaultCharset方法为

return Charset.forName("UTF8");

还是报错，报错如下：

java.sql.SQLException: Error while executing SQL "select * from 
"test"."score_new" where "name"= '催' limit 1": While executing SQL [SELECT 
*java.sql.SQLException: Error while executing SQL "select * from 
"test"."score_new" where "name"= '催' limit 1": While executing SQL [SELECT 
*FROM `score_new`WHERE `name` = u&'\50ac'LIMIT 1] on JDBC sub-schema at 
org.apache.calcite.avatica.Helper.createException(Helper.java:56) at 
org.apache.calcite.avatica.Helper.createException(Helper.java:41) at 
org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:163)
 at 
org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:227)
 at QueryMysql.main(QueryMysql.java:42)Caused by: java.lang.RuntimeException: 
While executing SQL [SELECT *FROM `score_new`WHERE `name` = u&'\50ac'



--
This message was sent by Atlassian Jira
(v8.3.2#803003)

Re: Is it possible to retrieve materialized view query rewrite from Calcite Plan

[XING JIN]

I think it will work. I once did similar things and was playing around with
VolcanoPlanner and RelOptMaterializations.



Shubham Kumar  于2019年9月16日周一 下午8:32写道：

> Hey everyone,
>
> Thank you for the help.
>
> @Stamatis, @Danny : Thanks for pointing out that  the reltosql api should
> be used after volcano planner execution which considers materializations
> and lattices, I understand it now.
>
> @Xing: Yeah, it works perfectly fine, I am able to see simple substitutions
> in action done by Calcite Planner!
>
> Although I guess "*org.apache.calcite.tes*t" API can't be accessed in some
> other project. To extract out the materialized query,  I was playing around
> with Prepare and optimize of Calcite Prepare class[1]. Let me know if I am
> moving in the correct direction.
>
> Since, now I am able to get the rewrite query, I wanted to share the exact
> use case which I am trying to implement and get your insights:
>
> I have explicitly defined materialized views which are stored in data
> source and defining it in *root.schema.materializations *of model file
> (keeping the "*view*" field empty as its already exists). Now, I will be
> completely responsible for maintaining this materialized view table in my
> datasource as various datasources don't have built in Materialized View
> like Mysql etc. Finally, I aim the rewrite query that I retrieve from
> Calcite to execute it using Spark SQL (data of tables will be stored in
> HDFS). So basically query rewrite of Calcite will be a layer before Spark
> SQL and it uses the optimized query using materialized views for making the
> actual Spark plan and executing it in a distributed manner.
>
> I would love to have your insights on this on whether this is will be
> feasible and if it would improve query performance.
>
> Problems that I am facing currently is that while using "
> *org.apache.calcite.adapter.jdbc.JdbcSchema$Factory*" as the factory method
> I get the following exception:
>
>
> *Cannot define materialization; parent schema 'test_calcite' is not a
> SemiMutableSchema*
>
> Currently I am using CloneSchema$Factory for testing things out but I guess
> it maintains in memory tables which eventually I won't be able to use in
> case of large tables. I was wondering if I would have to write my own
> Factory classes and implementations for my use case or is there something
> already present.
>
> [1]
>
>
> https://github.com/apache/calcite/blob/73023148e7f37d494f6caf92b01b090f6dde13cd/core/src/main/java/org/apache/calcite/prepare/Prepare.java#L320
>
> Thanks
> Shubham
>
>
> On Mon, Sep 16, 2019 at 11:32 AM XING JIN  wrote:
>
> > Hi,  Shubham Kumar
> > If I understand correctly, you want get the optimized(by materialized
> view)
> > SQL String. I wrote a simple test as below, please check if it's helpful
> > for you.
> >
> > @Test public void testDEV() {
> >   final String m = "select \"deptno\", \"empid\", \"name\""
> >   + "from \"emps\" where \"deptno\" = 10";
> >   final String q = "select \"empid\" + 1 as x, \"name\""
> >   + "from \"emps\" where \"deptno\" = 10";
> >
> >   CalciteAssert.that()
> >   .withMaterializations(HR_FKUK_MODEL, "m0", m)
> >   .query(q)
> >   .withHook(Hook.SUB, (Consumer) r ->
> >   {
> > RelToSqlConverter converter =
> > new RelToSqlConverter(CalciteSqlDialect.DEFAULT);
> > final SqlNode sqlNode = converter.visitChild(0, r).asStatement();
> >
> >
> System.out.println(sqlNode.toSqlString(CalciteSqlDialect.DEFAULT).getSql());
> >   })
> >   .enableMaterializations(true)
> >   .explainContains("hr, m0");
> > }
> >
> > The output is as below:
> >
> > SELECT "empid" + 1 AS "X", "name"
> > FROM "hr"."m0"
> >
> >
> > Danny Chan  于2019年9月16日周一 下午1:44写道：
> >
> > > Hi, Shubham Kumar ~
> > >
> > > > However, I wanted the optimized rewrite SQL query if possible, not
> just
> > > the
> > > > plan. So far, I tried to use the rel2sql api but when I print
> > > > RelNode.toString(), it gives me a plan which involves scanning the
> raw
> > > > tables and hence the SQL generated by rel2sql is not the one which
> > > utilized
> > > > the materialized view. Any pointers to get the rewrite query.
> > >
> > > Did you try the RelNode.toString() after the volcano planner promotion,
> > it
> > > is not expected to involve scanning the raw tables.
> > >
> > > Best,
> > > Danny Chan
> > > 在 2019年9月11日 +0800 PM9:42，dev@calcite.apache.org，写道：
> > > >
> > > > However, I wanted the optimized rewrite SQL query if possible, not
> just
> > > the
> > > > plan. So far, I tried to use the rel2sql api but when I print
> > > > RelNode.toString(), it gives me a plan which involves scanning the
> raw
> > > > tables and hence the SQL generated by rel2sql is not the one which
> > > utilized
> > > > the materialized view. Any pointers to get the rewrite query.
> > >
> >
>
>
> --
> Thanks & Regards
>
> Shubham Kumar
>

Re: [DISCUSS] Refinement for Substitution-Based MV Matching

[XING JIN]

Thanks a lot Danny and Haisheng for comments on the doc ~
And more comments are welcome ~

Best,
Jin

XING JIN  于2019年9月16日周一 下午9:17写道：

> Hi Contributors,
>
> I'm writing this email and hope to start a discussion about
> https://issues.apache.org/jira/browse/CALCITE-3334 .
>
> The approach of substitution-based materialized view matching is effective
> for its simplicity and extensibility. But now and then we confront
> materialization matching failures especially for SQLs with multiple nested
> levels. We keep thinking that how many more rules still need to be added
> and can we enumerate all the common matching patterns.
>
> The existing rules in SubstitutionVisitor &
> MaterializedViewSubstitutionVisitor are created by heuristic and
> experience. They are quite straightforward. But from my understanding the
> design of rule set is not systematic enough. Especially compensating
> Project or Filter are not given enough good care.
>
> I raised a doc --
> https://docs.google.com/document/d/1JpwGNFE3hw3yXb7W3-95-jXKClZC5UFPKbuhgYDuEu4
>  to
> propose refinement for substitution-based materialized view matching from
> two aspects:
> 1. Canonicalize before materialization matching;
> 2. Separate matching rules into two categories and enumerate matching
> patterns which need to be covered by rules.
>
> It's great if you can share some comments on this.
>
> Best,
> Jin
>
>

[DISCUSS] Refinement for Substitution-Based MV Matching

[XING JIN]

Hi Contributors,

I'm writing this email and hope to start a discussion about
https://issues.apache.org/jira/browse/CALCITE-3334 .

The approach of substitution-based materialized view matching is effective
for its simplicity and extensibility. But now and then we confront
materialization matching failures especially for SQLs with multiple nested
levels. We keep thinking that how many more rules still need to be added
and can we enumerate all the common matching patterns.

The existing rules in SubstitutionVisitor &
MaterializedViewSubstitutionVisitor are created by heuristic and
experience. They are quite straightforward. But from my understanding the
design of rule set is not systematic enough. Especially compensating
Project or Filter are not given enough good care.

I raised a doc --
https://docs.google.com/document/d/1JpwGNFE3hw3yXb7W3-95-jXKClZC5UFPKbuhgYDuEu4
to
propose refinement for substitution-based materialized view matching from
two aspects:
1. Canonicalize before materialization matching;
2. Separate matching rules into two categories and enumerate matching
patterns which need to be covered by rules.

It's great if you can share some comments on this.

Best,
Jin

[jira] [Created] (CALCITE-3350) Keep origin type for RexLiteral when deserialized from json string

[Wang Yanlin (Jira)]

Wang Yanlin created CALCITE-3350:


 Summary: Keep origin type for RexLiteral when deserialized from 
json string
 Key: CALCITE-3350
 URL: https://issues.apache.org/jira/browse/CALCITE-3350
 Project: Calcite
  Issue Type: Improvement
Reporter: Wang Yanlin


The json string of sql

{noformat}
select ename from emp where job = "abd"
{noformat}

is

{noformat}
{
  "rels": [
{
  "id": "0",
  "relOp": "LogicalTableScan",
  "table": [
"scott",
"EMP"
  ],
  "inputs": []
},
{
  "id": "1",
  "relOp": "LogicalFilter",
  "condition": {
"op": {
  "name": "=",
  "kind": "EQUALS",
  "syntax": "BINARY"
},
"operands": [
  {
"input": 2,
"name": "$2"
  },
  {
"literal": "abd",
"type": {
  "type": "VARCHAR",
  "nullable": false,
  "precision": 10
}
  }
]
  }
},
{
  "id": "2",
  "relOp": "LogicalProject",
  "fields": [
"ENAME"
  ],
  "exprs": [
{
  "input": 1,
  "name": "$1"
}
  ]
}
  ]
}
{noformat}
The original type of literal "abd" is
{noformat}
VARCHAR(10) NOT NULL
{noformat}
When deserialized relnode from this json string, the type of literal "abd" is 
changed to

{noformat}
CHAR(3) NOT NULL
{noformat}

Better to keep the same with original type when deserialized from string.



--
This message was sent by Atlassian Jira
(v8.3.2#803003)

Re: Is it possible to retrieve materialized view query rewrite from Calcite Plan

[Shubham Kumar]

Hey everyone,

Thank you for the help.

@Stamatis, @Danny : Thanks for pointing out that  the reltosql api should
be used after volcano planner execution which considers materializations
and lattices, I understand it now.

@Xing: Yeah, it works perfectly fine, I am able to see simple substitutions
in action done by Calcite Planner!

Although I guess "*org.apache.calcite.tes*t" API can't be accessed in some
other project. To extract out the materialized query,  I was playing around
with Prepare and optimize of Calcite Prepare class[1]. Let me know if I am
moving in the correct direction.

Since, now I am able to get the rewrite query, I wanted to share the exact
use case which I am trying to implement and get your insights:

I have explicitly defined materialized views which are stored in data
source and defining it in *root.schema.materializations *of model file
(keeping the "*view*" field empty as its already exists). Now, I will be
completely responsible for maintaining this materialized view table in my
datasource as various datasources don't have built in Materialized View
like Mysql etc. Finally, I aim the rewrite query that I retrieve from
Calcite to execute it using Spark SQL (data of tables will be stored in
HDFS). So basically query rewrite of Calcite will be a layer before Spark
SQL and it uses the optimized query using materialized views for making the
actual Spark plan and executing it in a distributed manner.

I would love to have your insights on this on whether this is will be
feasible and if it would improve query performance.

Problems that I am facing currently is that while using "
*org.apache.calcite.adapter.jdbc.JdbcSchema$Factory*" as the factory method
I get the following exception:


*Cannot define materialization; parent schema 'test_calcite' is not a
SemiMutableSchema*

Currently I am using CloneSchema$Factory for testing things out but I guess
it maintains in memory tables which eventually I won't be able to use in
case of large tables. I was wondering if I would have to write my own
Factory classes and implementations for my use case or is there something
already present.

[1]

https://github.com/apache/calcite/blob/73023148e7f37d494f6caf92b01b090f6dde13cd/core/src/main/java/org/apache/calcite/prepare/Prepare.java#L320

Thanks
Shubham


On Mon, Sep 16, 2019 at 11:32 AM XING JIN  wrote:

> Hi,  Shubham Kumar
> If I understand correctly, you want get the optimized(by materialized view)
> SQL String. I wrote a simple test as below, please check if it's helpful
> for you.
>
> @Test public void testDEV() {
>   final String m = "select \"deptno\", \"empid\", \"name\""
>   + "from \"emps\" where \"deptno\" = 10";
>   final String q = "select \"empid\" + 1 as x, \"name\""
>   + "from \"emps\" where \"deptno\" = 10";
>
>   CalciteAssert.that()
>   .withMaterializations(HR_FKUK_MODEL, "m0", m)
>   .query(q)
>   .withHook(Hook.SUB, (Consumer) r ->
>   {
> RelToSqlConverter converter =
> new RelToSqlConverter(CalciteSqlDialect.DEFAULT);
> final SqlNode sqlNode = converter.visitChild(0, r).asStatement();
>
> System.out.println(sqlNode.toSqlString(CalciteSqlDialect.DEFAULT).getSql());
>   })
>   .enableMaterializations(true)
>   .explainContains("hr, m0");
> }
>
> The output is as below:
>
> SELECT "empid" + 1 AS "X", "name"
> FROM "hr"."m0"
>
>
> Danny Chan  于2019年9月16日周一 下午1:44写道：
>
> > Hi, Shubham Kumar ~
> >
> > > However, I wanted the optimized rewrite SQL query if possible, not just
> > the
> > > plan. So far, I tried to use the rel2sql api but when I print
> > > RelNode.toString(), it gives me a plan which involves scanning the raw
> > > tables and hence the SQL generated by rel2sql is not the one which
> > utilized
> > > the materialized view. Any pointers to get the rewrite query.
> >
> > Did you try the RelNode.toString() after the volcano planner promotion,
> it
> > is not expected to involve scanning the raw tables.
> >
> > Best,
> > Danny Chan
> > 在 2019年9月11日 +0800 PM9:42，dev@calcite.apache.org，写道：
> > >
> > > However, I wanted the optimized rewrite SQL query if possible, not just
> > the
> > > plan. So far, I tried to use the rel2sql api but when I print
> > > RelNode.toString(), it gives me a plan which involves scanning the raw
> > > tables and hence the SQL generated by rel2sql is not the one which
> > utilized
> > > the materialized view. Any pointers to get the rewrite query.
> >
>


-- 
Thanks & Regards

Shubham Kumar

Re: Is it possible to retrieve materialized view query rewrite from Calcite Plan

[XING JIN]

Hi,  Shubham Kumar
If I understand correctly, you want get the optimized(by materialized view)
SQL String. I wrote a simple test as below, please check if it's helpful
for you.

@Test public void testDEV() {
  final String m = "select \"deptno\", \"empid\", \"name\""
  + "from \"emps\" where \"deptno\" = 10";
  final String q = "select \"empid\" + 1 as x, \"name\""
  + "from \"emps\" where \"deptno\" = 10";

  CalciteAssert.that()
  .withMaterializations(HR_FKUK_MODEL, "m0", m)
  .query(q)
  .withHook(Hook.SUB, (Consumer) r ->
  {
RelToSqlConverter converter =
new RelToSqlConverter(CalciteSqlDialect.DEFAULT);
final SqlNode sqlNode = converter.visitChild(0, r).asStatement();

System.out.println(sqlNode.toSqlString(CalciteSqlDialect.DEFAULT).getSql());
  })
  .enableMaterializations(true)
  .explainContains("hr, m0");
}

The output is as below:

SELECT "empid" + 1 AS "X", "name"
FROM "hr"."m0"


Danny Chan  于2019年9月16日周一 下午1:44写道：

> Hi, Shubham Kumar ~
>
> > However, I wanted the optimized rewrite SQL query if possible, not just
> the
> > plan. So far, I tried to use the rel2sql api but when I print
> > RelNode.toString(), it gives me a plan which involves scanning the raw
> > tables and hence the SQL generated by rel2sql is not the one which
> utilized
> > the materialized view. Any pointers to get the rewrite query.
>
> Did you try the RelNode.toString() after the volcano planner promotion, it
> is not expected to involve scanning the raw tables.
>
> Best,
> Danny Chan
> 在 2019年9月11日 +0800 PM9:42，dev@calcite.apache.org，写道：
> >
> > However, I wanted the optimized rewrite SQL query if possible, not just
> the
> > plan. So far, I tried to use the rel2sql api but when I print
> > RelNode.toString(), it gives me a plan which involves scanning the raw
> > tables and hence the SQL generated by rel2sql is not the one which
> utilized
> > the materialized view. Any pointers to get the rewrite query.
>

[jira] [Created] (CALCITE-3349) Add Function DDL into SqlKind DDL enum

[Zhenqiu Huang (Jira)]

Zhenqiu Huang created CALCITE-3349:
--

 Summary: Add Function DDL into SqlKind DDL enum
 Key: CALCITE-3349
 URL: https://issues.apache.org/jira/browse/CALCITE-3349
 Project: Calcite
  Issue Type: Improvement
  Components: core
Reporter: Zhenqiu Huang


Currently, Create Function, Drop Function are not added into SqlKind DDL enum. 



--
This message was sent by Atlassian Jira
(v8.3.2#803003)