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 <[email protected]> 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}
