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 <jinxing.co...@gmail.com> 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<RelNode>) 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 <yuzhao....@gmail.com> 于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
