Apache Calcite : Adding custom types and functions

Introduction

In this blog we will introduce a custom function and type in our SQL . In the end,we want to parse,validate and convert to a relational node for a simple query like
“SELECT CAST(my_custom_function(name) as my_custom_type) FROM SAMPLE” .

Setting up the basics

A sample schema

First we need a simple table named Sample : Sample(ID int not null,NAME varchar not null)

def createSampleSchema(rootSchema: CalciteSchema): CalciteSchema = {
    rootSchema.add(
      "SAMPLE",
      new AbstractTable() {
        override def getRowType(
                                 typeFactory: RelDataTypeFactory
                               ): RelDataType = {
          val builder = typeFactory.builder
          val id = typeFactory.createTypeWithNullability(
            typeFactory.createSqlType(SqlTypeName.INTEGER),
            false
          )
          val name = typeFactory.createTypeWithNullability(
            typeFactory.createSqlType(SqlTypeName.VARCHAR),
            false
          )
          builder.add("ID", id)
          builder.add("NAME", name)
          builder.build
        }
      }
    )
    rootSchema
  }

FrameworkConfig

Next we need to configure the framework API

val config = Frameworks.newConfigBuilder()
  .defaultSchema(Schema.createSampleSchema(CalciteSchema.createRootSchema(false, false)).plus())
  .build()

Putting it all together

Lets run the program now

val planner = Frameworks.getPlanner(config)
val sqlNode = planner.parse("SELECT CAST(my_custom_function(name) as my_custom_type)")
val validatedNode = planner.validate(sqlNode)
val relNode = planner.rel(validatedNode)

println(RelOptUtil.toString(relNode.rel))

First Try

Exception in thread "main" org.apache.calcite.tools.ValidationException: org.apache.calcite.runtime.CalciteContextException: From line 1, column 13 to line 1, column 32: No match found for function signature MY_CUSTOM_FUNCTION()
	at org.apache.calcite.prepare.PlannerImpl.validate(PlannerImpl.java:228)
	at com.knolx.calcite.Main$.delayedEndpoint$com$knolx$calcite$Main$1(Main.scala:16)
	at com.knolx.calcite.Main$delayedInit$body.apply(Main.scala:7)
	at scala.Function0.apply$mcV$sp(Function0.scala:39)
	at scala.Function0.apply$mcV$sp$(Function0.scala:39)
	at scala.runtime.AbstractFunction0.apply$mcV$sp(AbstractFunction0.scala:17)
	at scala.App.$anonfun$main$1(App.scala:76)
	at scala.App.$anonfun$main$1$adapted(App.scala:76)
	at scala.collection.IterableOnceOps.foreach(IterableOnce.scala:563)
	at scala.collection.IterableOnceOps.foreach$(IterableOnce.scala:561)
	at scala.collection.AbstractIterable.foreach(Iterable.scala:919)
	at scala.App.main(App.scala:76)
	at scala.App.main$(App.scala:74)
	at com.knolx.calcite.Main$.main(Main.scala:7)
	at com.knolx.calcite.Main.main(Main.scala)

What went wrong?

As expected, the validator has no information about my_custom_function().
We want to add a custom definition for it.

Solution

The SQL Parser marks any unknown operator as a SqlUnresolvedFunction. In the next phase,the SQL validator needs to resolve it from an OperatorTable. Failing to do so will result in an error.

Similarly,the SQL Parser will mark all unknown types as SqlUserDefinedTypeNameSpec which the SQL validator has to map from a given schema definition.

Lets re-configure our planner to take both in consideration.

Custom Function Definition

We need a custom function definition to map it to SqlUnresolvedFunction. This functions accepts a String argument and returns an Integer

val my_custom_function: SqlOperator = new SqlFunction(
    "MY_CUSTOM_FUNCTION",
    SqlKind.OTHER_FUNCTION,
    ReturnTypes.INTEGER,
    null,
    OperandTypes.STRING,
    SqlFunctionCategory.USER_DEFINED_FUNCTION
  )

Custom Type Definition

Next,we register a custom type to our schema. For simplicity, we’ll shadow it with VARCHAR.

def registerType(rootSchema: CalciteSchema): CalciteSchema = {
    rootSchema.add("MY_CUSTOM_TYPE",
      (typeFactory: RelDataTypeFactory) => typeFactory.createSqlType(SqlTypeName.VARCHAR)
    )
    rootSchema
  }

Final New Config

We need to update our configuration.

val config = Frameworks.newConfigBuilder()
    .defaultSchema(
      Schema.createSampleSchema(
        registerType(CalciteSchema.createRootSchema(false, false))
      )
        .plus()
    )
    .operatorTable(SqlOperatorTables.chain(
      SqlStdOperatorTable.instance(),
      new ListSqlOperatorTable(List(my_custom_function).asJava)
    )
    .build()

Second Try

Let us run the updated program.

LogicalProject(EXPR$0=[CAST(MY_CUSTOM_FUNCTION($1)):VARCHAR NOT NULL])
  LogicalTableScan(table=[[SAMPLE]])


Process finished with exit code 0

With our custom definitions were successfully able to

1. Parse
2. Validate
3. Convert to relational node

Conclusion

It was very easy to add custom operators and types in SQL queries.As we demonstrated, the calcite’s default SQL Parser allowed us with placeholders to work with. We only needed to resolve those placeholders.

Sometimes,adding definitions in OperatorTable is not enough for more complex cases.In my next blog we will try to achieve the same thing by modifying the parser itself for more complex syntax.