HomeTechnologyGenerate logical plan in Calcite

Generate logical plan in Calcite

Reading Time: 2 minutes

Hello everybody! in the previous blog of Apache Calcite we mentioned how Apache Calcite helps you to parse the database query and some fundamentals. 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 begins with the prefix Logical

How to generate Logical plan

  • You need to define your schema first like below
CalciteSchema.createRootSchema(untrue, untrue);
  • After that in schema, we need to define the desk 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.construct
      }
    }
  )
  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 3 parameter
private val groupByQuery: String = "SELECT ID FROM USERS GROUP BY ID"
private var relNode: RelNode = sqlToRelNode(testDefaultSchema, config, groupByQuery)
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



.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular