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