Insert,update ,delete,drop sample code by using relation algebra

Question

I am new Apache Calcite and am able to fetch the data from DB by using relational algebra ,but not able to do insert,update, delete,drop operation. If can share sample code will be more helpful.

Answer 1

As far as I am aware, RelBuilder can not build a RelNode for INSERT, UPDATE, DELETE, DROP operations.

For DML (INSERT, UPDATE, DELETE, MERGE), the equivalent relational algebra uses TableModify, so you can call LogicalTableModify.create to build one TableModify node and use RelBuilder to build a RelNode`` as its input as follows:

RelNode node = builder.scan("envliven").project("Name");
TableModify modifyNode = LogicalTableModify.create((table,
  schema, node,
  UPDATE, updateColumnList,
  sourceExpressionList, flattened);

For DDL (DROP, CREATE, ALTER), there is no corresponding relational algebra，but you can use the SqlNode to execute directly like CalcitePrepareImpl.executeDdl.

For example:

update nation set n_nationkey = 1 where n_nationkey = 2;

RelNode as follows:

LogicalTableModify(table=[[test, nation]], operation=[UPDATE], updateColumnList=[[n_nationkey]], sourceExpressionList=[[1]], flattened=[false]) 
  LogicalProject(n_nationkey=[$0], n_name=[$1], n_regionkey=[$2], n_comment=[$3], EXPR$0=[1]) 
   LogicalFilter(condition=[=($0,2)])
     LogicalTableScan(table=[[test, nation]])

For UPDATE, the updateColumnList has the column you updated, and the sourceExpressionList has the new values.

For INSERT:

insert into nation(n_nationkey, n_name) values(1, 'test');

RelNode as follows:

LogicalTableModify(table=[[test, nation]], operation=[INSERT], flattened=[false])
  LogicalProject(n_nationkey=[$0], n_name=[$1], n_regionkey=[null], n_comment=[null])
    LogicalValues(tuples=[[{ 1, _UTF-16'test                     ' }]])