You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Christian Tzolov <ct...@pivotal.io> on 2016/11/01 19:14:30 UTC
Modifying the AST (SqlNode) on the fly
Hi guys,
I am looking for a solution/approach that would allow me intercept and JDBC
call, replace an UPDATE statement by related INSERT and run the new SQL on
the backend database (using JDBC). Similarly on SELECT i would like to add
a filter to the existing statement.
My big-data DB doesn't support UPDATES so i am looking for a way to emulate
UPDATEs by converting them into INSERTs, append records with additional
'timestamp' column. On read the SELECT would be extended with additional
Filter to retrieve the records with most recent 'timestamp'.
Is Calcite the right tool to tackle such problem? Or are there any better
alternatives?
If Calcite is the way to go, it seems that the right place to perform such
query alteration is at SqlNode AST before the RelNode?
For the existing JDBC Adapter is there a way to pass the whole SQL query to
the backend database? There query will always be based on tables from a
single JDBC database.
Thanks,
Christian
--
Christian Tzolov <http://www.linkedin.com/in/tzolov> | Solution Architect,
EMEA Practice Team | Pivotal <http://pivotal.io/>
ctzolov@pivotal.io|+31610285517
Re: Modifying the AST (SqlNode) on the fly
Posted by Julian Hyde <jh...@apache.org>.
PS Plus, the main thing you are doing is transforming the algebra, i.e. writing a tricky planner rule. An adapter is mainly about plumbing — getting metadata from the other system, executing queries against the other system — and packaging for convenience. So, you should slot an additional rule (or two) into the existing adapter.
> On Nov 1, 2016, at 3:58 PM, Julian Hyde <jh...@apache.org> wrote:
>
> Definitely re-use the existing JDBC adapter. Within the adapter are ways to tweak (a) the dialect of the SQL you generate, (b) the capabilities of the DB (e.g. whether it supports OFFSET). Tweaking those knobs is a lot easier than building a new adapter.
>
> Julian
>
>
>> On Nov 1, 2016, at 3:55 PM, Christian Tzolov <ctzolov@pivotal.io <ma...@pivotal.io>> wrote:
>>
>> Thanks again!
>>
>> Would it make sense to reuse/extend the existing jdbc adapter or i better
>> start from scratch?
>> Since my backend DB uses postgres dialect i wonder what is the easiest way
>> to modify the relations and pass the whole query to the target DB.
>>
>> On 1 November 2016 at 22:42, Julian Hyde <jhyde@apache.org <ma...@apache.org>> wrote:
>>
>>> You might find that you only need to change the root node (TableModify)
>>> from UPDATE to INSERT, plus maybe a Project immediately underneath it. You
>>> can re-use the parts of the tree that you don’t change. This is typical of
>>> how planner rules work.
>>>
>>>> On Nov 1, 2016, at 2:38 PM, Christian Tzolov <ctzolov@pivotal.io <ma...@pivotal.io>> wrote:
>>>>
>>>> Thanks Julian!
>>>>
>>>> So i can override the whole RelNode tree from UPDATE to INSERT for
>>> example?
>>>> Was not sure if this is allowed in the RelNode phase.
>>>>
>>>> I guess as a start i need to implement my own TableModify relation and
>>>> related rule to substitute the LogicalTableModify and alter the
>>>> underlying operation.
>>>>
>>>> Is the there some sample code doing similar relation tree overriding?
>>>>
>>>> Thanks,
>>>> Christian
>>>>
>>>>
>>>>
>>>> On 1 November 2016 at 20:21, Julian Hyde <jhyde@apache.org <ma...@apache.org> <mailto:
>>> jhyde@apache.org <ma...@apache.org>>> wrote:
>>>>
>>>>> Calcite is the right tool for the job, but our experience is that
>>> hacking
>>>>> the AST is not the way to do it. You can do simple transformations on
>>> the
>>>>> AST, but SQL has complex semantics (e.g. consider the rules required to
>>>>> look up an unqualified column name in a sub-query), so complex
>>>>> transformations tend to break.
>>>>>
>>>>> Calcite is a query transformation engine, and the best place to do
>>>>> transformations is on the relational algebra (RelNode) rather than AST
>>>>> (SqlNode).
>>>>>
>>>>> Of course, you’re welcome to use just Calcite’s SQL parser, work on the
>>>>> AST, and then convert the AST back to SQL. It may be sufficient for your
>>>>> task - it’s your call.
>>>>>
>>>>> Julian
>>>>>
>>>>>
>>>>>> On Nov 1, 2016, at 12:14 PM, Christian Tzolov <ctzolov@pivotal.io <ma...@pivotal.io>>
>>>>> wrote:
>>>>>>
>>>>>> Hi guys,
>>>>>>
>>>>>> I am looking for a solution/approach that would allow me intercept and
>>>>> JDBC
>>>>>> call, replace an UPDATE statement by related INSERT and run the new SQL
>>>>> on
>>>>>> the backend database (using JDBC). Similarly on SELECT i would like to
>>>>> add
>>>>>> a filter to the existing statement.
>>>>>>
>>>>>> My big-data DB doesn't support UPDATES so i am looking for a way to
>>>>> emulate
>>>>>> UPDATEs by converting them into INSERTs, append records with additional
>>>>>> 'timestamp' column. On read the SELECT would be extended with
>>> additional
>>>>>> Filter to retrieve the records with most recent 'timestamp'.
>>>>>>
>>>>>> Is Calcite the right tool to tackle such problem? Or are there any
>>> better
>>>>>> alternatives?
>>>>>>
>>>>>> If Calcite is the way to go, it seems that the right place to perform
>>>>> such
>>>>>> query alteration is at SqlNode AST before the RelNode?
>>>>>>
>>>>>> For the existing JDBC Adapter is there a way to pass the whole SQL
>>> query
>>>>> to
>>>>>> the backend database? There query will always be based on tables from a
>>>>>> single JDBC database.
>>>>>>
>>>>>> Thanks,
>>>>>> Christian
>>>>>>
>>>>>> --
>>>>>>
>>>>>
>>>>>
>>>>>
>>>> --
>>>
>>
>>
>>
>> --
>> Christian Tzolov <http://www.linkedin.com/in/tzolov <http://www.linkedin.com/in/tzolov>> | Solution Architect,
>> EMEA Practice Team | Pivotal <http://pivotal.io/ <http://pivotal.io/>>
>> ctzolov@pivotal.io <ma...@pivotal.io>|+31610285517
Re: Modifying the AST (SqlNode) on the fly
Posted by Julian Hyde <jh...@apache.org>.
That’s the problem with documentation. It goes out of date. If you want the truth, see JdbcAdapterTest.
> On Nov 1, 2016, at 4:20 PM, Christian Tzolov <ct...@pivotal.io> wrote:
>
> Sounds great!
>
> I am concerned by this remark in the documentation: "The JDBC adapter
> currently only pushes down table scan operations".
> Is there an easy way to pass the the whole (new) query to the backend
> database? The query would always concern tables from a single database
> (e.g. no multiple jdbc sources).
>
> On 1 November 2016 at 23:58, Julian Hyde <jhyde@apache.org <ma...@apache.org>> wrote:
>
>> Definitely re-use the existing JDBC adapter. Within the adapter are ways
>> to tweak (a) the dialect of the SQL you generate, (b) the capabilities of
>> the DB (e.g. whether it supports OFFSET). Tweaking those knobs is a lot
>> easier than building a new adapter.
>>
>> Julian
>>
>>
>>> On Nov 1, 2016, at 3:55 PM, Christian Tzolov <ct...@pivotal.io> wrote:
>>>
>>> Thanks again!
>>>
>>> Would it make sense to reuse/extend the existing jdbc adapter or i better
>>> start from scratch?
>>> Since my backend DB uses postgres dialect i wonder what is the easiest
>> way
>>> to modify the relations and pass the whole query to the target DB.
>>>
>>> On 1 November 2016 at 22:42, Julian Hyde <jhyde@apache.org <ma...@apache.org> <mailto:
>> jhyde@apache.org <ma...@apache.org>>> wrote:
>>>
>>>> You might find that you only need to change the root node (TableModify)
>>>> from UPDATE to INSERT, plus maybe a Project immediately underneath it.
>> You
>>>> can re-use the parts of the tree that you don’t change. This is typical
>> of
>>>> how planner rules work.
>>>>
>>>>> On Nov 1, 2016, at 2:38 PM, Christian Tzolov <ctzolov@pivotal.io <ma...@pivotal.io>>
>> wrote:
>>>>>
>>>>> Thanks Julian!
>>>>>
>>>>> So i can override the whole RelNode tree from UPDATE to INSERT for
>>>> example?
>>>>> Was not sure if this is allowed in the RelNode phase.
>>>>>
>>>>> I guess as a start i need to implement my own TableModify relation and
>>>>> related rule to substitute the LogicalTableModify and alter the
>>>>> underlying operation.
>>>>>
>>>>> Is the there some sample code doing similar relation tree overriding?
>>>>>
>>>>> Thanks,
>>>>> Christian
>>>>>
>>>>>
>>>>>
>>>>> On 1 November 2016 at 20:21, Julian Hyde <jhyde@apache.org <ma...@apache.org> <mailto:
>> jhyde@apache.org <ma...@apache.org>> <mailto:
>>>> jhyde@apache.org <ma...@apache.org> <mailto:jhyde@apache.org <ma...@apache.org>>>> wrote:
>>>>>
>>>>>> Calcite is the right tool for the job, but our experience is that
>>>> hacking
>>>>>> the AST is not the way to do it. You can do simple transformations on
>>>> the
>>>>>> AST, but SQL has complex semantics (e.g. consider the rules required
>> to
>>>>>> look up an unqualified column name in a sub-query), so complex
>>>>>> transformations tend to break.
>>>>>>
>>>>>> Calcite is a query transformation engine, and the best place to do
>>>>>> transformations is on the relational algebra (RelNode) rather than AST
>>>>>> (SqlNode).
>>>>>>
>>>>>> Of course, you’re welcome to use just Calcite’s SQL parser, work on
>> the
>>>>>> AST, and then convert the AST back to SQL. It may be sufficient for
>> your
>>>>>> task - it’s your call.
>>>>>>
>>>>>> Julian
>>>>>>
>>>>>>
>>>>>>> On Nov 1, 2016, at 12:14 PM, Christian Tzolov <ctzolov@pivotal.io <ma...@pivotal.io>
>> <mailto:ctzolov@pivotal.io <ma...@pivotal.io>>>
>>>>>> wrote:
>>>>>>>
>>>>>>> Hi guys,
>>>>>>>
>>>>>>> I am looking for a solution/approach that would allow me intercept
>> and
>>>>>> JDBC
>>>>>>> call, replace an UPDATE statement by related INSERT and run the new
>> SQL
>>>>>> on
>>>>>>> the backend database (using JDBC). Similarly on SELECT i would like
>> to
>>>>>> add
>>>>>>> a filter to the existing statement.
>>>>>>>
>>>>>>> My big-data DB doesn't support UPDATES so i am looking for a way to
>>>>>> emulate
>>>>>>> UPDATEs by converting them into INSERTs, append records with
>> additional
>>>>>>> 'timestamp' column. On read the SELECT would be extended with
>>>> additional
>>>>>>> Filter to retrieve the records with most recent 'timestamp'.
>>>>>>>
>>>>>>> Is Calcite the right tool to tackle such problem? Or are there any
>>>> better
>>>>>>> alternatives?
>>>>>>>
>>>>>>> If Calcite is the way to go, it seems that the right place to perform
>>>>>> such
>>>>>>> query alteration is at SqlNode AST before the RelNode?
>>>>>>>
>>>>>>> For the existing JDBC Adapter is there a way to pass the whole SQL
>>>> query
>>>>>> to
>>>>>>> the backend database? There query will always be based on tables
>> from a
>>>>>>> single JDBC database.
>>>>>>>
>>>>>>> Thanks,
>>>>>>> Christian
>>>>>>>
>>>>>>> --
>>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>> --
>>>>
>>>
>>>
>>>
>>> --
>>> Christian Tzolov <http://www.linkedin.com/in/tzolov <http://www.linkedin.com/in/tzolov> <
>> http://www.linkedin.com/in/tzolov <http://www.linkedin.com/in/tzolov>>> | Solution Architect,
>>> EMEA Practice Team | Pivotal <http://pivotal.io/ <http://pivotal.io/> <http://pivotal.io/ <http://pivotal.io/>>>
>>> ctzolov@pivotal.io <ma...@pivotal.io> <mailto:ctzolov@pivotal.io <ma...@pivotal.io>>|+31610285517
>>
>
>
>
> --
> Christian Tzolov <http://www.linkedin.com/in/tzolov <http://www.linkedin.com/in/tzolov>> | Solution Architect,
> EMEA Practice Team | Pivotal <http://pivotal.io/ <http://pivotal.io/>>
> ctzolov@pivotal.io <ma...@pivotal.io>|+31610285517
Re: Modifying the AST (SqlNode) on the fly
Posted by Christian Tzolov <ct...@pivotal.io>.
Sounds great!
I am concerned by this remark in the documentation: "The JDBC adapter
currently only pushes down table scan operations".
Is there an easy way to pass the the whole (new) query to the backend
database? The query would always concern tables from a single database
(e.g. no multiple jdbc sources).
On 1 November 2016 at 23:58, Julian Hyde <jh...@apache.org> wrote:
> Definitely re-use the existing JDBC adapter. Within the adapter are ways
> to tweak (a) the dialect of the SQL you generate, (b) the capabilities of
> the DB (e.g. whether it supports OFFSET). Tweaking those knobs is a lot
> easier than building a new adapter.
>
> Julian
>
>
> > On Nov 1, 2016, at 3:55 PM, Christian Tzolov <ct...@pivotal.io> wrote:
> >
> > Thanks again!
> >
> > Would it make sense to reuse/extend the existing jdbc adapter or i better
> > start from scratch?
> > Since my backend DB uses postgres dialect i wonder what is the easiest
> way
> > to modify the relations and pass the whole query to the target DB.
> >
> > On 1 November 2016 at 22:42, Julian Hyde <jhyde@apache.org <mailto:
> jhyde@apache.org>> wrote:
> >
> >> You might find that you only need to change the root node (TableModify)
> >> from UPDATE to INSERT, plus maybe a Project immediately underneath it.
> You
> >> can re-use the parts of the tree that you don’t change. This is typical
> of
> >> how planner rules work.
> >>
> >>> On Nov 1, 2016, at 2:38 PM, Christian Tzolov <ct...@pivotal.io>
> wrote:
> >>>
> >>> Thanks Julian!
> >>>
> >>> So i can override the whole RelNode tree from UPDATE to INSERT for
> >> example?
> >>> Was not sure if this is allowed in the RelNode phase.
> >>>
> >>> I guess as a start i need to implement my own TableModify relation and
> >>> related rule to substitute the LogicalTableModify and alter the
> >>> underlying operation.
> >>>
> >>> Is the there some sample code doing similar relation tree overriding?
> >>>
> >>> Thanks,
> >>> Christian
> >>>
> >>>
> >>>
> >>> On 1 November 2016 at 20:21, Julian Hyde <jhyde@apache.org <mailto:
> jhyde@apache.org> <mailto:
> >> jhyde@apache.org <ma...@apache.org>>> wrote:
> >>>
> >>>> Calcite is the right tool for the job, but our experience is that
> >> hacking
> >>>> the AST is not the way to do it. You can do simple transformations on
> >> the
> >>>> AST, but SQL has complex semantics (e.g. consider the rules required
> to
> >>>> look up an unqualified column name in a sub-query), so complex
> >>>> transformations tend to break.
> >>>>
> >>>> Calcite is a query transformation engine, and the best place to do
> >>>> transformations is on the relational algebra (RelNode) rather than AST
> >>>> (SqlNode).
> >>>>
> >>>> Of course, you’re welcome to use just Calcite’s SQL parser, work on
> the
> >>>> AST, and then convert the AST back to SQL. It may be sufficient for
> your
> >>>> task - it’s your call.
> >>>>
> >>>> Julian
> >>>>
> >>>>
> >>>>> On Nov 1, 2016, at 12:14 PM, Christian Tzolov <ctzolov@pivotal.io
> <ma...@pivotal.io>>
> >>>> wrote:
> >>>>>
> >>>>> Hi guys,
> >>>>>
> >>>>> I am looking for a solution/approach that would allow me intercept
> and
> >>>> JDBC
> >>>>> call, replace an UPDATE statement by related INSERT and run the new
> SQL
> >>>> on
> >>>>> the backend database (using JDBC). Similarly on SELECT i would like
> to
> >>>> add
> >>>>> a filter to the existing statement.
> >>>>>
> >>>>> My big-data DB doesn't support UPDATES so i am looking for a way to
> >>>> emulate
> >>>>> UPDATEs by converting them into INSERTs, append records with
> additional
> >>>>> 'timestamp' column. On read the SELECT would be extended with
> >> additional
> >>>>> Filter to retrieve the records with most recent 'timestamp'.
> >>>>>
> >>>>> Is Calcite the right tool to tackle such problem? Or are there any
> >> better
> >>>>> alternatives?
> >>>>>
> >>>>> If Calcite is the way to go, it seems that the right place to perform
> >>>> such
> >>>>> query alteration is at SqlNode AST before the RelNode?
> >>>>>
> >>>>> For the existing JDBC Adapter is there a way to pass the whole SQL
> >> query
> >>>> to
> >>>>> the backend database? There query will always be based on tables
> from a
> >>>>> single JDBC database.
> >>>>>
> >>>>> Thanks,
> >>>>> Christian
> >>>>>
> >>>>> --
> >>>>>
> >>>>
> >>>>
> >>>>
> >>> --
> >>
> >
> >
> >
> > --
> > Christian Tzolov <http://www.linkedin.com/in/tzolov <
> http://www.linkedin.com/in/tzolov>> | Solution Architect,
> > EMEA Practice Team | Pivotal <http://pivotal.io/ <http://pivotal.io/>>
> > ctzolov@pivotal.io <ma...@pivotal.io>|+31610285517
>
--
Christian Tzolov <http://www.linkedin.com/in/tzolov> | Solution Architect,
EMEA Practice Team | Pivotal <http://pivotal.io/>
ctzolov@pivotal.io|+31610285517
Re: Modifying the AST (SqlNode) on the fly
Posted by Julian Hyde <jh...@apache.org>.
Definitely re-use the existing JDBC adapter. Within the adapter are ways to tweak (a) the dialect of the SQL you generate, (b) the capabilities of the DB (e.g. whether it supports OFFSET). Tweaking those knobs is a lot easier than building a new adapter.
Julian
> On Nov 1, 2016, at 3:55 PM, Christian Tzolov <ct...@pivotal.io> wrote:
>
> Thanks again!
>
> Would it make sense to reuse/extend the existing jdbc adapter or i better
> start from scratch?
> Since my backend DB uses postgres dialect i wonder what is the easiest way
> to modify the relations and pass the whole query to the target DB.
>
> On 1 November 2016 at 22:42, Julian Hyde <jhyde@apache.org <ma...@apache.org>> wrote:
>
>> You might find that you only need to change the root node (TableModify)
>> from UPDATE to INSERT, plus maybe a Project immediately underneath it. You
>> can re-use the parts of the tree that you don’t change. This is typical of
>> how planner rules work.
>>
>>> On Nov 1, 2016, at 2:38 PM, Christian Tzolov <ct...@pivotal.io> wrote:
>>>
>>> Thanks Julian!
>>>
>>> So i can override the whole RelNode tree from UPDATE to INSERT for
>> example?
>>> Was not sure if this is allowed in the RelNode phase.
>>>
>>> I guess as a start i need to implement my own TableModify relation and
>>> related rule to substitute the LogicalTableModify and alter the
>>> underlying operation.
>>>
>>> Is the there some sample code doing similar relation tree overriding?
>>>
>>> Thanks,
>>> Christian
>>>
>>>
>>>
>>> On 1 November 2016 at 20:21, Julian Hyde <jhyde@apache.org <ma...@apache.org> <mailto:
>> jhyde@apache.org <ma...@apache.org>>> wrote:
>>>
>>>> Calcite is the right tool for the job, but our experience is that
>> hacking
>>>> the AST is not the way to do it. You can do simple transformations on
>> the
>>>> AST, but SQL has complex semantics (e.g. consider the rules required to
>>>> look up an unqualified column name in a sub-query), so complex
>>>> transformations tend to break.
>>>>
>>>> Calcite is a query transformation engine, and the best place to do
>>>> transformations is on the relational algebra (RelNode) rather than AST
>>>> (SqlNode).
>>>>
>>>> Of course, you’re welcome to use just Calcite’s SQL parser, work on the
>>>> AST, and then convert the AST back to SQL. It may be sufficient for your
>>>> task - it’s your call.
>>>>
>>>> Julian
>>>>
>>>>
>>>>> On Nov 1, 2016, at 12:14 PM, Christian Tzolov <ctzolov@pivotal.io <ma...@pivotal.io>>
>>>> wrote:
>>>>>
>>>>> Hi guys,
>>>>>
>>>>> I am looking for a solution/approach that would allow me intercept and
>>>> JDBC
>>>>> call, replace an UPDATE statement by related INSERT and run the new SQL
>>>> on
>>>>> the backend database (using JDBC). Similarly on SELECT i would like to
>>>> add
>>>>> a filter to the existing statement.
>>>>>
>>>>> My big-data DB doesn't support UPDATES so i am looking for a way to
>>>> emulate
>>>>> UPDATEs by converting them into INSERTs, append records with additional
>>>>> 'timestamp' column. On read the SELECT would be extended with
>> additional
>>>>> Filter to retrieve the records with most recent 'timestamp'.
>>>>>
>>>>> Is Calcite the right tool to tackle such problem? Or are there any
>> better
>>>>> alternatives?
>>>>>
>>>>> If Calcite is the way to go, it seems that the right place to perform
>>>> such
>>>>> query alteration is at SqlNode AST before the RelNode?
>>>>>
>>>>> For the existing JDBC Adapter is there a way to pass the whole SQL
>> query
>>>> to
>>>>> the backend database? There query will always be based on tables from a
>>>>> single JDBC database.
>>>>>
>>>>> Thanks,
>>>>> Christian
>>>>>
>>>>> --
>>>>>
>>>>
>>>>
>>>>
>>> --
>>
>
>
>
> --
> Christian Tzolov <http://www.linkedin.com/in/tzolov <http://www.linkedin.com/in/tzolov>> | Solution Architect,
> EMEA Practice Team | Pivotal <http://pivotal.io/ <http://pivotal.io/>>
> ctzolov@pivotal.io <ma...@pivotal.io>|+31610285517
Re: Modifying the AST (SqlNode) on the fly
Posted by Christian Tzolov <ct...@pivotal.io>.
Thanks again!
Would it make sense to reuse/extend the existing jdbc adapter or i better
start from scratch?
Since my backend DB uses postgres dialect i wonder what is the easiest way
to modify the relations and pass the whole query to the target DB.
On 1 November 2016 at 22:42, Julian Hyde <jh...@apache.org> wrote:
> You might find that you only need to change the root node (TableModify)
> from UPDATE to INSERT, plus maybe a Project immediately underneath it. You
> can re-use the parts of the tree that you don’t change. This is typical of
> how planner rules work.
>
> > On Nov 1, 2016, at 2:38 PM, Christian Tzolov <ct...@pivotal.io> wrote:
> >
> > Thanks Julian!
> >
> > So i can override the whole RelNode tree from UPDATE to INSERT for
> example?
> > Was not sure if this is allowed in the RelNode phase.
> >
> > I guess as a start i need to implement my own TableModify relation and
> > related rule to substitute the LogicalTableModify and alter the
> > underlying operation.
> >
> > Is the there some sample code doing similar relation tree overriding?
> >
> > Thanks,
> > Christian
> >
> >
> >
> > On 1 November 2016 at 20:21, Julian Hyde <jhyde@apache.org <mailto:
> jhyde@apache.org>> wrote:
> >
> >> Calcite is the right tool for the job, but our experience is that
> hacking
> >> the AST is not the way to do it. You can do simple transformations on
> the
> >> AST, but SQL has complex semantics (e.g. consider the rules required to
> >> look up an unqualified column name in a sub-query), so complex
> >> transformations tend to break.
> >>
> >> Calcite is a query transformation engine, and the best place to do
> >> transformations is on the relational algebra (RelNode) rather than AST
> >> (SqlNode).
> >>
> >> Of course, you’re welcome to use just Calcite’s SQL parser, work on the
> >> AST, and then convert the AST back to SQL. It may be sufficient for your
> >> task - it’s your call.
> >>
> >> Julian
> >>
> >>
> >>> On Nov 1, 2016, at 12:14 PM, Christian Tzolov <ct...@pivotal.io>
> >> wrote:
> >>>
> >>> Hi guys,
> >>>
> >>> I am looking for a solution/approach that would allow me intercept and
> >> JDBC
> >>> call, replace an UPDATE statement by related INSERT and run the new SQL
> >> on
> >>> the backend database (using JDBC). Similarly on SELECT i would like to
> >> add
> >>> a filter to the existing statement.
> >>>
> >>> My big-data DB doesn't support UPDATES so i am looking for a way to
> >> emulate
> >>> UPDATEs by converting them into INSERTs, append records with additional
> >>> 'timestamp' column. On read the SELECT would be extended with
> additional
> >>> Filter to retrieve the records with most recent 'timestamp'.
> >>>
> >>> Is Calcite the right tool to tackle such problem? Or are there any
> better
> >>> alternatives?
> >>>
> >>> If Calcite is the way to go, it seems that the right place to perform
> >> such
> >>> query alteration is at SqlNode AST before the RelNode?
> >>>
> >>> For the existing JDBC Adapter is there a way to pass the whole SQL
> query
> >> to
> >>> the backend database? There query will always be based on tables from a
> >>> single JDBC database.
> >>>
> >>> Thanks,
> >>> Christian
> >>>
> >>> --
> >>>
> >>
> >>
> >>
> > --
>
--
Christian Tzolov <http://www.linkedin.com/in/tzolov> | Solution Architect,
EMEA Practice Team | Pivotal <http://pivotal.io/>
ctzolov@pivotal.io|+31610285517
Re: Modifying the AST (SqlNode) on the fly
Posted by Julian Hyde <jh...@apache.org>.
You might find that you only need to change the root node (TableModify) from UPDATE to INSERT, plus maybe a Project immediately underneath it. You can re-use the parts of the tree that you don’t change. This is typical of how planner rules work.
> On Nov 1, 2016, at 2:38 PM, Christian Tzolov <ct...@pivotal.io> wrote:
>
> Thanks Julian!
>
> So i can override the whole RelNode tree from UPDATE to INSERT for example?
> Was not sure if this is allowed in the RelNode phase.
>
> I guess as a start i need to implement my own TableModify relation and
> related rule to substitute the LogicalTableModify and alter the
> underlying operation.
>
> Is the there some sample code doing similar relation tree overriding?
>
> Thanks,
> Christian
>
>
>
> On 1 November 2016 at 20:21, Julian Hyde <jhyde@apache.org <ma...@apache.org>> wrote:
>
>> Calcite is the right tool for the job, but our experience is that hacking
>> the AST is not the way to do it. You can do simple transformations on the
>> AST, but SQL has complex semantics (e.g. consider the rules required to
>> look up an unqualified column name in a sub-query), so complex
>> transformations tend to break.
>>
>> Calcite is a query transformation engine, and the best place to do
>> transformations is on the relational algebra (RelNode) rather than AST
>> (SqlNode).
>>
>> Of course, you’re welcome to use just Calcite’s SQL parser, work on the
>> AST, and then convert the AST back to SQL. It may be sufficient for your
>> task - it’s your call.
>>
>> Julian
>>
>>
>>> On Nov 1, 2016, at 12:14 PM, Christian Tzolov <ct...@pivotal.io>
>> wrote:
>>>
>>> Hi guys,
>>>
>>> I am looking for a solution/approach that would allow me intercept and
>> JDBC
>>> call, replace an UPDATE statement by related INSERT and run the new SQL
>> on
>>> the backend database (using JDBC). Similarly on SELECT i would like to
>> add
>>> a filter to the existing statement.
>>>
>>> My big-data DB doesn't support UPDATES so i am looking for a way to
>> emulate
>>> UPDATEs by converting them into INSERTs, append records with additional
>>> 'timestamp' column. On read the SELECT would be extended with additional
>>> Filter to retrieve the records with most recent 'timestamp'.
>>>
>>> Is Calcite the right tool to tackle such problem? Or are there any better
>>> alternatives?
>>>
>>> If Calcite is the way to go, it seems that the right place to perform
>> such
>>> query alteration is at SqlNode AST before the RelNode?
>>>
>>> For the existing JDBC Adapter is there a way to pass the whole SQL query
>> to
>>> the backend database? There query will always be based on tables from a
>>> single JDBC database.
>>>
>>> Thanks,
>>> Christian
>>>
>>> --
>>>
>>
>>
>>
> --
Re: Modifying the AST (SqlNode) on the fly
Posted by Christian Tzolov <ct...@pivotal.io>.
Thanks Julian!
So i can override the whole RelNode tree from UPDATE to INSERT for example?
Was not sure if this is allowed in the RelNode phase.
I guess as a start i need to implement my own TableModify relation and
related rule to substitute the LogicalTableModify and alter the
underlying operation.
Is the there some sample code doing similar relation tree overriding?
Thanks,
Christian
On 1 November 2016 at 20:21, Julian Hyde <jh...@apache.org> wrote:
> Calcite is the right tool for the job, but our experience is that hacking
> the AST is not the way to do it. You can do simple transformations on the
> AST, but SQL has complex semantics (e.g. consider the rules required to
> look up an unqualified column name in a sub-query), so complex
> transformations tend to break.
>
> Calcite is a query transformation engine, and the best place to do
> transformations is on the relational algebra (RelNode) rather than AST
> (SqlNode).
>
> Of course, you’re welcome to use just Calcite’s SQL parser, work on the
> AST, and then convert the AST back to SQL. It may be sufficient for your
> task - it’s your call.
>
> Julian
>
>
> > On Nov 1, 2016, at 12:14 PM, Christian Tzolov <ct...@pivotal.io>
> wrote:
> >
> > Hi guys,
> >
> > I am looking for a solution/approach that would allow me intercept and
> JDBC
> > call, replace an UPDATE statement by related INSERT and run the new SQL
> on
> > the backend database (using JDBC). Similarly on SELECT i would like to
> add
> > a filter to the existing statement.
> >
> > My big-data DB doesn't support UPDATES so i am looking for a way to
> emulate
> > UPDATEs by converting them into INSERTs, append records with additional
> > 'timestamp' column. On read the SELECT would be extended with additional
> > Filter to retrieve the records with most recent 'timestamp'.
> >
> > Is Calcite the right tool to tackle such problem? Or are there any better
> > alternatives?
> >
> > If Calcite is the way to go, it seems that the right place to perform
> such
> > query alteration is at SqlNode AST before the RelNode?
> >
> > For the existing JDBC Adapter is there a way to pass the whole SQL query
> to
> > the backend database? There query will always be based on tables from a
> > single JDBC database.
> >
> > Thanks,
> > Christian
> >
> > --
> >
>
>
>
--
Re: Modifying the AST (SqlNode) on the fly
Posted by Julian Hyde <jh...@apache.org>.
Calcite is the right tool for the job, but our experience is that hacking the AST is not the way to do it. You can do simple transformations on the AST, but SQL has complex semantics (e.g. consider the rules required to look up an unqualified column name in a sub-query), so complex transformations tend to break.
Calcite is a query transformation engine, and the best place to do transformations is on the relational algebra (RelNode) rather than AST (SqlNode).
Of course, you’re welcome to use just Calcite’s SQL parser, work on the AST, and then convert the AST back to SQL. It may be sufficient for your task - it’s your call.
Julian
> On Nov 1, 2016, at 12:14 PM, Christian Tzolov <ct...@pivotal.io> wrote:
>
> Hi guys,
>
> I am looking for a solution/approach that would allow me intercept and JDBC
> call, replace an UPDATE statement by related INSERT and run the new SQL on
> the backend database (using JDBC). Similarly on SELECT i would like to add
> a filter to the existing statement.
>
> My big-data DB doesn't support UPDATES so i am looking for a way to emulate
> UPDATEs by converting them into INSERTs, append records with additional
> 'timestamp' column. On read the SELECT would be extended with additional
> Filter to retrieve the records with most recent 'timestamp'.
>
> Is Calcite the right tool to tackle such problem? Or are there any better
> alternatives?
>
> If Calcite is the way to go, it seems that the right place to perform such
> query alteration is at SqlNode AST before the RelNode?
>
> For the existing JDBC Adapter is there a way to pass the whole SQL query to
> the backend database? There query will always be based on tables from a
> single JDBC database.
>
> Thanks,
> Christian
>
> --
>
> Christian Tzolov <http://www.linkedin.com/in/tzolov> | Solution Architect,
> EMEA Practice Team | Pivotal <http://pivotal.io/>
> ctzolov@pivotal.io|+31610285517