Reading Time: 2 minutes
Hello everyone! in the previous blog of Apache Calcite we discussed how Apache Calcite helps you to parse the database query and some basics. In this blog, we will discuss how to generate the logical plan of the database query you have written.
What is logical plan
- A logical plan is a relational expression with only a logical operator.
- Logical algebra has no implementation of the relational operator and therefore can’t run.
- The logical plan is the first plan created when transforming a SQL tree into relational algebra.
- All logical operator starts with the prefix Logical
How to generate Logical plan
- You need to define your schema first like below
CalciteSchema.createRootSchema(false, false);
- After that in schema, we need to define the table name and columns like below, you have to add according to your tables
def getSchema(rootSchema: CalciteSchema): CalciteSchema = { rootSchema.add( "USERS", new AbstractTable() { override def getRowType( typeFactory: RelDataTypeFactory ): RelDataType = { val builder = typeFactory.builder val t1 = typeFactory.createTypeWithNullability( typeFactory.createSqlType(SqlTypeName.INTEGER), true ) val t2 = typeFactory.createTypeWithNullability( typeFactory.createSqlType(SqlTypeName.CHAR), true ) val t3 = typeFactory.createTypeWithNullability( typeFactory.createSqlType(SqlTypeName.CHAR), true ) builder.add("ID", t1) builder.add("NAME", t2) builder.add("OWNERID", t3) builder.build } } ) rootSchema }
- Initially you need to define the SqlParserCongiguration like below
- Now we have to create the RelNode converter that will convert into the logical tree it takes three parameter
private val groupByQuery: String = "SELECT ID FROM USERS GROUP BY ID" private var relNode: RelNode = sqlToRelNode(testDefaultSchema, config, groupByQuery)
- print the logical tree
println("Logical plan for the query=\n " + RelOptUtil.toString(relNode))
Conclusion
Now you are able to generate the logical tree of the given SQL query. For full example please go to the link given in the references
Reference
https://calcite.apache.org/
https://github.com/knoldus/calcite-logical-tree
1 thought on “Generate logical plan in Calcite2 min read”
Comments are closed.