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