You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Mike Hinchey <hi...@gmail.com> on 2015/11/16 07:48:13 UTC

insert statement and TableModify

I'm trying to make my adapter respond to an insert statement.

I started by looking at the jdbc adapter code, but I don't think that fully
implements insert - I get the error below.  The rules (including
JdbcTableModificationRule) are not converting any of the rels - I think
because JdbcTable doesn't implement ModifiableTable, so the rules aren't
registered with the planner.

I can get farther in my own code by implementing ModifiableTable.  The
comment in ModifiableTable says the interface will change. One problem I'm
having with it now is I can't tell when the DML statement is complete, so
that I can execute my adapter DML.  That is, if I run "insert into t values
(x), (y)", I get the call to getModifiableCollection, and Collection.add is
called multiple times. I need to send those new rows to my backend
database, not one at a time.  I created a workaround by using
Handler.onStatementClose, but that's messy.

I'm also trying to create a rule (like JdbcTableModificationRule), so I can
implement differently from EnumerableTableModify.  I can get it to convert
MyTableModify (my toModificationRel does that), and MyValues (from a rule),
but the planner fails because something could not be implemented, but I
can't figure out what's missing.  Can you tell from the exception report
(copied below) what is missing?

Without my rules, the plan is:

EnumerableTableModify(table=[[HR, DEPARTMENT]], operation=[INSERT],
updateColumnList=[[]], flattened=[false])

  EnumerableValues(tuples=[[{ 1, 'Marketing' }, { 2, 'Sales' }]])

Thanks,

Mike


>From the JDBC adapter:

java.sql.SQLException: Error while executing SQL "INSERT INTO T2 VALUES (1,
1)": Node [rel#34132:Subset#1.ENUMERABLE.[]] could not be implemented;
planner state:

Root: rel#34132:Subset#1.ENUMERABLE.[]

Original rel:

Sets:

Set#0, type: RecordType(INTEGER ID, INTEGER VALS)

rel#34128:Subset#0.NONE.[], best=null, importance=0.81

rel#34126:LogicalValues.NONE.[[0, 1], [1]](type=RecordType(INTEGER ID,
INTEGER VALS),tuples=[{ 1, 1 }]), rowcount=1.0, cumulative cost={inf}

rel#34136:Subset#0.ENUMERABLE.[], best=rel#34135, importance=0.405

rel#34135:EnumerableValues.ENUMERABLE.[[0, 1], [1]](type=RecordType(INTEGER
ID, INTEGER VALS),tuples=[{ 1, 1 }]), rowcount=1.0, cumulative cost={1.0
rows, 1.0 cpu, 0.0 io}

Set#1, type: RecordType(BIGINT ROWCOUNT)

rel#34130:Subset#1.NONE.[], best=null, importance=0.9

rel#34129:LogicalTableModify.NONE.[](input=rel#34128:Subset#0.NONE.[],table=[BASEJDBC,
T2],operation=INSERT,updateColumnList=[],flattened=false), rowcount=1.0,
cumulative cost={inf}

rel#34133:AbstractConverter.NONE.[](input=rel#34132:Subset#1.ENUMERABLE.[],convention=NONE,sort=[]),
rowcount=1.0, cumulative cost={inf}

rel#34132:Subset#1.ENUMERABLE.[], best=null, importance=1.0

rel#34134:AbstractConverter.ENUMERABLE.[](input=rel#34130:Subset#1.NONE.[],convention=ENUMERABLE,sort=[]),
rowcount=1.0, cumulative cost={inf}


at
org.apache.calcite.plan.volcano.RelSubset$CheapestPlanReplacer.visit(RelSubset.java:452)



My adapter:

java.sql.SQLException: Error while executing SQL "explain plan for INSERT
INTO department (id, name) VALUES

( 1, 'Marketing' ),

( 2, 'Sales' )

": Node [rel#60:Subset#1.ENUMERABLE.[]] could not be implemented; planner
state:


Root: rel#60:Subset#1.ENUMERABLE.[]

Original rel:


Sets:

Set#0, type: RecordType(INTEGER ID, VARCHAR(1) NAME)

rel#56:Subset#0.NONE.[], best=null, importance=0.81

rel#54:LogicalValues.NONE.[[0, 1], [1]](type=RecordType(INTEGER ID,
VARCHAR(1) NAME),tuples=[{ 1, 'Marketing' }, { 2, 'Sales' }]),
rowcount=2.0, cumulative cost={inf}

rel#64:Subset#0.ENUMERABLE.[], best=rel#63, importance=0.405

rel#63:EnumerableValues.ENUMERABLE.[[0, 1], [1]](type=RecordType(INTEGER
ID, VARCHAR(1) NAME),tuples=[{ 1, 'Marketing' }, { 2, 'Sales' }]),
rowcount=2.0, cumulative cost={2.0 rows, 1.0 cpu, 0.0 io}

rel#66:Subset#0.MY.HR.[], best=rel#65, importance=0.405

rel#65:MyValues.MY.HR.[[0, 1], [1]](type=RecordType(INTEGER ID, VARCHAR(1)
NAME),tuples=[{ 1, 'Marketing' }, { 2, 'Sales' }]), rowcount=2.0,
cumulative cost={2.0 rows, 1.0 cpu, 0.0 io}

Set#1, type: RecordType(BIGINT ROWCOUNT)

rel#58:Subset#1.MY.HR.[], best=null, importance=0.9

rel#57:MyTableModify.MY.HR.[](input=rel#56:Subset#0.NONE.[],table=[HR,
DEPARTMENT],operation=INSERT,updateColumnList=[],flattened=false),
rowcount=2.0, cumulative cost={inf}

rel#61:AbstractConverter.MY.HR.[](input=rel#60:Subset#1.ENUMERABLE.[],convention=
MY.HR,sort=[]), rowcount=2.0, cumulative cost={inf}

rel#60:Subset#1.ENUMERABLE.[], best=null, importance=1.0

rel#62:AbstractConverter.ENUMERABLE.[](input=rel#58:Subset#1.MY.HR.[],convention=ENUMERABLE,sort=[]),
rowcount=2.0, cumulative cost={inf}

Re: insert statement and TableModify

Posted by Mike Hinchey <hi...@gmail.com>.
That makes sense.  I would prefer to use TableModify rather than
ModifiableTable.  Actually, I'll work on getting JdbcTableModify to work -
that will make it easier to ask questions.

How does JdbcTableModificationRule (and others) get added to the planner?
The JdbcTable adds rules through the Convention when toRel is called, and
also toModificationRel if ModifiableTable is used.  Without
ModificationTable, are you thinking of another way?  In my project, I
extend the calcite Driver, so I can maybe add rules by overriding
createPrepareFactory/createPlanner like the MockDdlDriver - is that a good
idea?  But the JDBC adapter doesn't have it's own driver, so I don't see
another hook.

Also, with the JDBC adapter, I have the same problem I reported with the
planner Rel conversions.  I don't know how to interpret what's missing that
leaves the AbstractConverter.

Thanks,
Mike



On Mon, Nov 16, 2015 at 5:52 PM Julian Hyde <jh...@apache.org> wrote:

> Since you want to modify data in an external system, ModifiableTable is
> not suitable. An approach similar to JdbcTableModify is called for.
>
> But if I recall correctly, JdbcTableModify (and in fact the whole of
> JdbcRules) was copied quickly and without much thought from
> EnumerableTableModify. So the “assert table.unwrap(ModifiableTable.class)
> != null” logic is bogus. Treat the whole thing as a red herring.
>
> If we implemented JdbcTableModify, we would simply generate “INSERT INTO
> schema.table <<query>>” where <<query>> is obtained by converting the input
> RelNode into SQL. I have logged
> https://issues.apache.org/jira/browse/CALCITE-973 <
> https://issues.apache.org/jira/browse/CALCITE-973> to track this.
>
> The one virtue of taking the ModifiableTable route would be if you could
> write into a message queue (e.g. Kafka) and have that write into your
> table. It would be cheating somewhat — your table would be backed by a
> queue rather than a collection, and values would disappear from the queue
> as soon as the consumer took them — but it might achieve the desired
> result. Your call; I don’t know the architecture of the system you are
> building.
>
> Julian
>
>
>
> > On Nov 15, 2015, at 10:48 PM, Mike Hinchey <hi...@gmail.com> wrote:
> >
> > I'm trying to make my adapter respond to an insert statement.
> >
> > I started by looking at the jdbc adapter code, but I don't think that
> fully
> > implements insert - I get the error below.  The rules (including
> > JdbcTableModificationRule) are not converting any of the rels - I think
> > because JdbcTable doesn't implement ModifiableTable, so the rules aren't
> > registered with the planner.
> >
> > I can get farther in my own code by implementing ModifiableTable.  The
> > comment in ModifiableTable says the interface will change. One problem
> I'm
> > having with it now is I can't tell when the DML statement is complete, so
> > that I can execute my adapter DML.  That is, if I run "insert into t
> values
> > (x), (y)", I get the call to getModifiableCollection, and Collection.add
> is
> > called multiple times. I need to send those new rows to my backend
> > database, not one at a time.  I created a workaround by using
> > Handler.onStatementClose, but that's messy.
> >
> > I'm also trying to create a rule (like JdbcTableModificationRule), so I
> can
> > implement differently from EnumerableTableModify.  I can get it to
> convert
> > MyTableModify (my toModificationRel does that), and MyValues (from a
> rule),
> > but the planner fails because something could not be implemented, but I
> > can't figure out what's missing.  Can you tell from the exception report
> > (copied below) what is missing?
> >
> > Without my rules, the plan is:
> >
> > EnumerableTableModify(table=[[HR, DEPARTMENT]], operation=[INSERT],
> > updateColumnList=[[]], flattened=[false])
> >
> >  EnumerableValues(tuples=[[{ 1, 'Marketing' }, { 2, 'Sales' }]])
> >
> > Thanks,
> >
> > Mike
> >
> >
> > From the JDBC adapter:
> >
> > java.sql.SQLException: Error while executing SQL "INSERT INTO T2 VALUES
> (1,
> > 1)": Node [rel#34132:Subset#1.ENUMERABLE.[]] could not be implemented;
> > planner state:
> >
> > Root: rel#34132:Subset#1.ENUMERABLE.[]
> >
> > Original rel:
> >
> > Sets:
> >
> > Set#0, type: RecordType(INTEGER ID, INTEGER VALS)
> >
> > rel#34128:Subset#0.NONE.[], best=null, importance=0.81
> >
> > rel#34126:LogicalValues.NONE.[[0, 1], [1]](type=RecordType(INTEGER ID,
> > INTEGER VALS),tuples=[{ 1, 1 }]), rowcount=1.0, cumulative cost={inf}
> >
> > rel#34136:Subset#0.ENUMERABLE.[], best=rel#34135, importance=0.405
> >
> > rel#34135:EnumerableValues.ENUMERABLE.[[0, 1],
> [1]](type=RecordType(INTEGER
> > ID, INTEGER VALS),tuples=[{ 1, 1 }]), rowcount=1.0, cumulative cost={1.0
> > rows, 1.0 cpu, 0.0 io}
> >
> > Set#1, type: RecordType(BIGINT ROWCOUNT)
> >
> > rel#34130:Subset#1.NONE.[], best=null, importance=0.9
> >
> >
> rel#34129:LogicalTableModify.NONE.[](input=rel#34128:Subset#0.NONE.[],table=[BASEJDBC,
> > T2],operation=INSERT,updateColumnList=[],flattened=false), rowcount=1.0,
> > cumulative cost={inf}
> >
> >
> rel#34133:AbstractConverter.NONE.[](input=rel#34132:Subset#1.ENUMERABLE.[],convention=NONE,sort=[]),
> > rowcount=1.0, cumulative cost={inf}
> >
> > rel#34132:Subset#1.ENUMERABLE.[], best=null, importance=1.0
> >
> >
> rel#34134:AbstractConverter.ENUMERABLE.[](input=rel#34130:Subset#1.NONE.[],convention=ENUMERABLE,sort=[]),
> > rowcount=1.0, cumulative cost={inf}
> >
> >
> > at
> >
> org.apache.calcite.plan.volcano.RelSubset$CheapestPlanReplacer.visit(RelSubset.java:452)
> >
> >
> >
> > My adapter:
> >
> > java.sql.SQLException: Error while executing SQL "explain plan for INSERT
> > INTO department (id, name) VALUES
> >
> > ( 1, 'Marketing' ),
> >
> > ( 2, 'Sales' )
> >
> > ": Node [rel#60:Subset#1.ENUMERABLE.[]] could not be implemented; planner
> > state:
> >
> >
> > Root: rel#60:Subset#1.ENUMERABLE.[]
> >
> > Original rel:
> >
> >
> > Sets:
> >
> > Set#0, type: RecordType(INTEGER ID, VARCHAR(1) NAME)
> >
> > rel#56:Subset#0.NONE.[], best=null, importance=0.81
> >
> > rel#54:LogicalValues.NONE.[[0, 1], [1]](type=RecordType(INTEGER ID,
> > VARCHAR(1) NAME),tuples=[{ 1, 'Marketing' }, { 2, 'Sales' }]),
> > rowcount=2.0, cumulative cost={inf}
> >
> > rel#64:Subset#0.ENUMERABLE.[], best=rel#63, importance=0.405
> >
> > rel#63:EnumerableValues.ENUMERABLE.[[0, 1], [1]](type=RecordType(INTEGER
> > ID, VARCHAR(1) NAME),tuples=[{ 1, 'Marketing' }, { 2, 'Sales' }]),
> > rowcount=2.0, cumulative cost={2.0 rows, 1.0 cpu, 0.0 io}
> >
> > rel#66:Subset#0.MY.HR.[], best=rel#65, importance=0.405
> >
> > rel#65:MyValues.MY.HR.[[0, 1], [1]](type=RecordType(INTEGER ID,
> VARCHAR(1)
> > NAME),tuples=[{ 1, 'Marketing' }, { 2, 'Sales' }]), rowcount=2.0,
> > cumulative cost={2.0 rows, 1.0 cpu, 0.0 io}
> >
> > Set#1, type: RecordType(BIGINT ROWCOUNT)
> >
> > rel#58:Subset#1.MY.HR.[], best=null, importance=0.9
> >
> > rel#57:MyTableModify.MY.HR.[](input=rel#56:Subset#0.NONE.[],table=[HR,
> > DEPARTMENT],operation=INSERT,updateColumnList=[],flattened=false),
> > rowcount=2.0, cumulative cost={inf}
> >
> >
> rel#61:AbstractConverter.MY.HR.[](input=rel#60:Subset#1.ENUMERABLE.[],convention=
> > MY.HR,sort=[]), rowcount=2.0, cumulative cost={inf}
> >
> > rel#60:Subset#1.ENUMERABLE.[], best=null, importance=1.0
> >
> >
> rel#62:AbstractConverter.ENUMERABLE.[](input=rel#58:Subset#1.MY.HR.[],convention=ENUMERABLE,sort=[]),
> > rowcount=2.0, cumulative cost={inf}
>
>

Re: insert statement and TableModify

Posted by Julian Hyde <jh...@apache.org>.
Since you want to modify data in an external system, ModifiableTable is not suitable. An approach similar to JdbcTableModify is called for.

But if I recall correctly, JdbcTableModify (and in fact the whole of JdbcRules) was copied quickly and without much thought from EnumerableTableModify. So the “assert table.unwrap(ModifiableTable.class) != null” logic is bogus. Treat the whole thing as a red herring.

If we implemented JdbcTableModify, we would simply generate “INSERT INTO schema.table <<query>>” where <<query>> is obtained by converting the input RelNode into SQL. I have logged https://issues.apache.org/jira/browse/CALCITE-973 <https://issues.apache.org/jira/browse/CALCITE-973> to track this.

The one virtue of taking the ModifiableTable route would be if you could write into a message queue (e.g. Kafka) and have that write into your table. It would be cheating somewhat — your table would be backed by a queue rather than a collection, and values would disappear from the queue as soon as the consumer took them — but it might achieve the desired result. Your call; I don’t know the architecture of the system you are building.

Julian



> On Nov 15, 2015, at 10:48 PM, Mike Hinchey <hi...@gmail.com> wrote:
> 
> I'm trying to make my adapter respond to an insert statement.
> 
> I started by looking at the jdbc adapter code, but I don't think that fully
> implements insert - I get the error below.  The rules (including
> JdbcTableModificationRule) are not converting any of the rels - I think
> because JdbcTable doesn't implement ModifiableTable, so the rules aren't
> registered with the planner.
> 
> I can get farther in my own code by implementing ModifiableTable.  The
> comment in ModifiableTable says the interface will change. One problem I'm
> having with it now is I can't tell when the DML statement is complete, so
> that I can execute my adapter DML.  That is, if I run "insert into t values
> (x), (y)", I get the call to getModifiableCollection, and Collection.add is
> called multiple times. I need to send those new rows to my backend
> database, not one at a time.  I created a workaround by using
> Handler.onStatementClose, but that's messy.
> 
> I'm also trying to create a rule (like JdbcTableModificationRule), so I can
> implement differently from EnumerableTableModify.  I can get it to convert
> MyTableModify (my toModificationRel does that), and MyValues (from a rule),
> but the planner fails because something could not be implemented, but I
> can't figure out what's missing.  Can you tell from the exception report
> (copied below) what is missing?
> 
> Without my rules, the plan is:
> 
> EnumerableTableModify(table=[[HR, DEPARTMENT]], operation=[INSERT],
> updateColumnList=[[]], flattened=[false])
> 
>  EnumerableValues(tuples=[[{ 1, 'Marketing' }, { 2, 'Sales' }]])
> 
> Thanks,
> 
> Mike
> 
> 
> From the JDBC adapter:
> 
> java.sql.SQLException: Error while executing SQL "INSERT INTO T2 VALUES (1,
> 1)": Node [rel#34132:Subset#1.ENUMERABLE.[]] could not be implemented;
> planner state:
> 
> Root: rel#34132:Subset#1.ENUMERABLE.[]
> 
> Original rel:
> 
> Sets:
> 
> Set#0, type: RecordType(INTEGER ID, INTEGER VALS)
> 
> rel#34128:Subset#0.NONE.[], best=null, importance=0.81
> 
> rel#34126:LogicalValues.NONE.[[0, 1], [1]](type=RecordType(INTEGER ID,
> INTEGER VALS),tuples=[{ 1, 1 }]), rowcount=1.0, cumulative cost={inf}
> 
> rel#34136:Subset#0.ENUMERABLE.[], best=rel#34135, importance=0.405
> 
> rel#34135:EnumerableValues.ENUMERABLE.[[0, 1], [1]](type=RecordType(INTEGER
> ID, INTEGER VALS),tuples=[{ 1, 1 }]), rowcount=1.0, cumulative cost={1.0
> rows, 1.0 cpu, 0.0 io}
> 
> Set#1, type: RecordType(BIGINT ROWCOUNT)
> 
> rel#34130:Subset#1.NONE.[], best=null, importance=0.9
> 
> rel#34129:LogicalTableModify.NONE.[](input=rel#34128:Subset#0.NONE.[],table=[BASEJDBC,
> T2],operation=INSERT,updateColumnList=[],flattened=false), rowcount=1.0,
> cumulative cost={inf}
> 
> rel#34133:AbstractConverter.NONE.[](input=rel#34132:Subset#1.ENUMERABLE.[],convention=NONE,sort=[]),
> rowcount=1.0, cumulative cost={inf}
> 
> rel#34132:Subset#1.ENUMERABLE.[], best=null, importance=1.0
> 
> rel#34134:AbstractConverter.ENUMERABLE.[](input=rel#34130:Subset#1.NONE.[],convention=ENUMERABLE,sort=[]),
> rowcount=1.0, cumulative cost={inf}
> 
> 
> at
> org.apache.calcite.plan.volcano.RelSubset$CheapestPlanReplacer.visit(RelSubset.java:452)
> 
> 
> 
> My adapter:
> 
> java.sql.SQLException: Error while executing SQL "explain plan for INSERT
> INTO department (id, name) VALUES
> 
> ( 1, 'Marketing' ),
> 
> ( 2, 'Sales' )
> 
> ": Node [rel#60:Subset#1.ENUMERABLE.[]] could not be implemented; planner
> state:
> 
> 
> Root: rel#60:Subset#1.ENUMERABLE.[]
> 
> Original rel:
> 
> 
> Sets:
> 
> Set#0, type: RecordType(INTEGER ID, VARCHAR(1) NAME)
> 
> rel#56:Subset#0.NONE.[], best=null, importance=0.81
> 
> rel#54:LogicalValues.NONE.[[0, 1], [1]](type=RecordType(INTEGER ID,
> VARCHAR(1) NAME),tuples=[{ 1, 'Marketing' }, { 2, 'Sales' }]),
> rowcount=2.0, cumulative cost={inf}
> 
> rel#64:Subset#0.ENUMERABLE.[], best=rel#63, importance=0.405
> 
> rel#63:EnumerableValues.ENUMERABLE.[[0, 1], [1]](type=RecordType(INTEGER
> ID, VARCHAR(1) NAME),tuples=[{ 1, 'Marketing' }, { 2, 'Sales' }]),
> rowcount=2.0, cumulative cost={2.0 rows, 1.0 cpu, 0.0 io}
> 
> rel#66:Subset#0.MY.HR.[], best=rel#65, importance=0.405
> 
> rel#65:MyValues.MY.HR.[[0, 1], [1]](type=RecordType(INTEGER ID, VARCHAR(1)
> NAME),tuples=[{ 1, 'Marketing' }, { 2, 'Sales' }]), rowcount=2.0,
> cumulative cost={2.0 rows, 1.0 cpu, 0.0 io}
> 
> Set#1, type: RecordType(BIGINT ROWCOUNT)
> 
> rel#58:Subset#1.MY.HR.[], best=null, importance=0.9
> 
> rel#57:MyTableModify.MY.HR.[](input=rel#56:Subset#0.NONE.[],table=[HR,
> DEPARTMENT],operation=INSERT,updateColumnList=[],flattened=false),
> rowcount=2.0, cumulative cost={inf}
> 
> rel#61:AbstractConverter.MY.HR.[](input=rel#60:Subset#1.ENUMERABLE.[],convention=
> MY.HR,sort=[]), rowcount=2.0, cumulative cost={inf}
> 
> rel#60:Subset#1.ENUMERABLE.[], best=null, importance=1.0
> 
> rel#62:AbstractConverter.ENUMERABLE.[](input=rel#58:Subset#1.MY.HR.[],convention=ENUMERABLE,sort=[]),
> rowcount=2.0, cumulative cost={inf}