Generate logical plan in Calcite

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

  • 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
Screenshot-from-2021-02-24-12-26-19
  • 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

Knoldus-blog-footer-image

Written by 

Shivraj Singh is a software consultant having experience of more than 2+ years. On the personal hand, he likes eating food and playing video games like NFS. He already ready to learn new technology and explore new trends in the IT world. Shivraj is familiar with programming languages such as Java, Scala, C, C++ and he is currently working on reactive technologies like Scala, Akka, and Kafka.

1 thought on “Generate logical plan in Calcite2 min read

Comments are closed.