You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Junwei Li <ju...@gmail.com> on 2017/11/14 08:33:27 UTC

How to use a customized sql2relconverter in the planner?

I am trying to write my own adapter for some data source. In which,

Statement statement = conn.createStatement();
ResultSet rs = statement.executeQuery("select hostname, ts from
source.table where ts > 1510033561000");

I noticed that the default SqltoRelConverter will try to trim unused
fields, such as:

14:16:52.500 [main] DEBUG org.apache.calcite.sql2rel - Plan after
converting SqlNode to RelNode
LogicalProject(hostname=[$2], ts=[$3])
  LogicalFilter(condition=[>($3, 1510033561000)])
    BPTableScan(table=[[source, table]])

14:16:52.517 [main] DEBUG org.apache.calcite.sql2rel - Plan after trimming
unused fields
LogicalFilter(condition=[>($1, 1510033561000)])
  LogicalProject(hostname=[$2], ts=[$3])
    BPTableScan(table=[[source, table]])


But what I really want is to keep LogicalProject always on top of
LogicalFilter. I don't want unused fields get trimmed automatically. I
noticed that in Prepare.java,
final SqlToRelConverter.ConfigBuilder builder =
        SqlToRelConverter.configBuilder()
            .withTrimUnusedFields(true)
            .withExpand(THREAD_EXPAND.get())
            .withExplain(sqlQuery.getKind() == SqlKind.EXPLAIN);


So trimming unused fields is a default behavior. Is there a way to use a
customized sql2rel converter when I run statement.executeQuery?


If the answer to the question above is no, the follow-up question is can we
apply some rule to transpose logicalfilter and logicalproject? I did find a
built-in rule, named FilterProjectTransposeRule, which got applied in
the RelOptPlanner. But the final cheapest plan doesn't pick it. What I
understand is that the calculation of cost is simply a sum of all nodes'
cost. So there is no way to transpose filter and project by only tweaking
the cost of filter and project, since the cost doesn't get lesser. Is my
understanding correct? Thank you in advance!

Sincerely,
Junwei Li

Re: How to use a customized sql2relconverter in the planner?

Posted by Julian Hyde <jh...@apache.org>.
To prevent field trimming you will want to create a SqlToRelConverter passing a SqlToRelConverter.Config parameter whose isTrimUnusedFields() method returns false.

To do that, you probably want to override the Prepare.getSqlToRelConverter method. Put a break point in CalcitePrepareImpl.convert_, which is probably where that method gets called, and you should see what’s going on.

Julian

> On Nov 14, 2017, at 7:52 AM, Michael Mior <mm...@uwaterloo.ca> wrote:
> 
> I'm not sure about the appropriate way to configure SqlToRelConverter for
> the planner. However, if you always want a rule to be applied, than you
> should construct a HepPlanner and provide it with the appropriate rules.
> 
> Also, it might be helpful if you could explain why you need the plan
> constructed in that particular way as there may be an alternative solution
> to your problem.
> 
> --
> Michael Mior
> mmior@apache.org
> 
> 2017-11-14 3:33 GMT-05:00 Junwei Li <ju...@gmail.com>:
> 
>> I am trying to write my own adapter for some data source. In which,
>> 
>> Statement statement = conn.createStatement();
>> ResultSet rs = statement.executeQuery("select hostname, ts from
>> source.table where ts > 1510033561000");
>> 
>> I noticed that the default SqltoRelConverter will try to trim unused
>> fields, such as:
>> 
>> 14:16:52.500 [main] DEBUG org.apache.calcite.sql2rel - Plan after
>> converting SqlNode to RelNode
>> LogicalProject(hostname=[$2], ts=[$3])
>>  LogicalFilter(condition=[>($3, 1510033561000)])
>>    BPTableScan(table=[[source, table]])
>> 
>> 14:16:52.517 [main] DEBUG org.apache.calcite.sql2rel - Plan after trimming
>> unused fields
>> LogicalFilter(condition=[>($1, 1510033561000)])
>>  LogicalProject(hostname=[$2], ts=[$3])
>>    BPTableScan(table=[[source, table]])
>> 
>> 
>> But what I really want is to keep LogicalProject always on top of
>> LogicalFilter. I don't want unused fields get trimmed automatically. I
>> noticed that in Prepare.java,
>> final SqlToRelConverter.ConfigBuilder builder =
>>        SqlToRelConverter.configBuilder()
>>            .withTrimUnusedFields(true)
>>            .withExpand(THREAD_EXPAND.get())
>>            .withExplain(sqlQuery.getKind() == SqlKind.EXPLAIN);
>> 
>> 
>> So trimming unused fields is a default behavior. Is there a way to use a
>> customized sql2rel converter when I run statement.executeQuery?
>> 
>> 
>> If the answer to the question above is no, the follow-up question is can we
>> apply some rule to transpose logicalfilter and logicalproject? I did find a
>> built-in rule, named FilterProjectTransposeRule, which got applied in
>> the RelOptPlanner. But the final cheapest plan doesn't pick it. What I
>> understand is that the calculation of cost is simply a sum of all nodes'
>> cost. So there is no way to transpose filter and project by only tweaking
>> the cost of filter and project, since the cost doesn't get lesser. Is my
>> understanding correct? Thank you in advance!
>> 
>> Sincerely,
>> Junwei Li
>>

Re: How to use a customized sql2relconverter in the planner?

Posted by Michael Mior <mm...@uwaterloo.ca>.
I'm not sure about the appropriate way to configure SqlToRelConverter for
the planner. However, if you always want a rule to be applied, than you
should construct a HepPlanner and provide it with the appropriate rules.

Also, it might be helpful if you could explain why you need the plan
constructed in that particular way as there may be an alternative solution
to your problem.

--
Michael Mior
mmior@apache.org

2017-11-14 3:33 GMT-05:00 Junwei Li <ju...@gmail.com>:

> I am trying to write my own adapter for some data source. In which,
>
> Statement statement = conn.createStatement();
> ResultSet rs = statement.executeQuery("select hostname, ts from
> source.table where ts > 1510033561000");
>
> I noticed that the default SqltoRelConverter will try to trim unused
> fields, such as:
>
> 14:16:52.500 [main] DEBUG org.apache.calcite.sql2rel - Plan after
> converting SqlNode to RelNode
> LogicalProject(hostname=[$2], ts=[$3])
>   LogicalFilter(condition=[>($3, 1510033561000)])
>     BPTableScan(table=[[source, table]])
>
> 14:16:52.517 [main] DEBUG org.apache.calcite.sql2rel - Plan after trimming
> unused fields
> LogicalFilter(condition=[>($1, 1510033561000)])
>   LogicalProject(hostname=[$2], ts=[$3])
>     BPTableScan(table=[[source, table]])
>
>
> But what I really want is to keep LogicalProject always on top of
> LogicalFilter. I don't want unused fields get trimmed automatically. I
> noticed that in Prepare.java,
> final SqlToRelConverter.ConfigBuilder builder =
>         SqlToRelConverter.configBuilder()
>             .withTrimUnusedFields(true)
>             .withExpand(THREAD_EXPAND.get())
>             .withExplain(sqlQuery.getKind() == SqlKind.EXPLAIN);
>
>
> So trimming unused fields is a default behavior. Is there a way to use a
> customized sql2rel converter when I run statement.executeQuery?
>
>
> If the answer to the question above is no, the follow-up question is can we
> apply some rule to transpose logicalfilter and logicalproject? I did find a
> built-in rule, named FilterProjectTransposeRule, which got applied in
> the RelOptPlanner. But the final cheapest plan doesn't pick it. What I
> understand is that the calculation of cost is simply a sum of all nodes'
> cost. So there is no way to transpose filter and project by only tweaking
> the cost of filter and project, since the cost doesn't get lesser. Is my
> understanding correct? Thank you in advance!
>
> Sincerely,
> Junwei Li
>