How are custom UDFs, operators and their implementations provided to Calcite when using JdbcMeta?

[Ian Bertolacci <ia...@workday.com.INVALID>]

Hello,
Our current approach to defining custom UDFs and their implementations works by…

  1.  Defining the udf and its implementation in a class member function
  2.  Creating a Calcite definitions in the form of `schema.Function`s via  `ScalarFunctionImpl.create` or `AggregateFunctionImpl.create` with that class and member function
  3.  Returning a Symbol -> Function map from our Schema’s implementation of `AbstractSchema.getFunctionMultimap`
That map is then consumed by the CatalogReader used for validation.

As far as we can tell, this is the only way to provide UDFs to Calcite when using JdbcMeta as Meta for the Avatica Service and handler.

This method of defining UDFs and their implementations severely limits the kinds of functions we are allowed to define, which would otherwise be allowed by defining them via SqlOperators and an SqlOperatorTable.
For example, it does not allow the definition of variadic functions, which is a particularly sore spot for our users.
The flip side of using SqlOperators is that it is unclear how to then bind implementations to those operators for Calcite’s execution.

I’ve previously been pointed to this example [1] but this is only applicable when building up the entire pipeline, which is a non-starter for us.

Any help is greatly appreciated.


[1] https://github.com/zabetak/calcite-tutorial/blob/31cce59c747e0b763a934109db6a6e7055f175ae/solution/src/main/java/com/github/zabetak/calcite/tutorial/LuceneQueryProcessor.java#L166

Re: How are custom UDFs, operators and their implementations provided to Calcite when using JdbcMeta?

[Julian Hyde <jh...@gmail.com>]

Ian,

When you say ‘our approach’ and ‘our users’, I guess you are talking about your organization as opposed to the Calcite community in general. You say you are ‘using JdbcMeta’ but I don’t really understand what that means. Can you expand on your use case/environment?

I think of Java UDFs as just one means for a user (for a very loose definition of ‘user’) to define their own operator. As you say, extending the SqlOperator class and adding the operator object to a SqlOperatorTable is another way. Which mechanism is appropriate depends very much on the user and the environment, which is why I asked for more explanation above.

There is an open jira case for varargs in UDFs [1]. I could never get excited about it because it didn’t explain how things would look to the SQL user.

Julian

[1] https://issues.apache.org/jira/browse/CALCITE-2772 <https://issues.apache.org/jira/browse/CALCITE-2772> 




> On Aug 23, 2022, at 11:56 AM, Ian Bertolacci <ia...@workday.com.INVALID> wrote:
> 
> Hello,
> Our current approach to defining custom UDFs and their implementations works by…
> 
>  1.  Defining the udf and its implementation in a class member function
>  2.  Creating a Calcite definitions in the form of `schema.Function`s via  `ScalarFunctionImpl.create` or `AggregateFunctionImpl.create` with that class and member function
>  3.  Returning a Symbol -> Function map from our Schema’s implementation of `AbstractSchema.getFunctionMultimap`
> That map is then consumed by the CatalogReader used for validation.
> 
> As far as we can tell, this is the only way to provide UDFs to Calcite when using JdbcMeta as Meta for the Avatica Service and handler.
> 
> This method of defining UDFs and their implementations severely limits the kinds of functions we are allowed to define, which would otherwise be allowed by defining them via SqlOperators and an SqlOperatorTable.
> For example, it does not allow the definition of variadic functions, which is a particularly sore spot for our users.
> The flip side of using SqlOperators is that it is unclear how to then bind implementations to those operators for Calcite’s execution.
> 
> I’ve previously been pointed to this example [1] but this is only applicable when building up the entire pipeline, which is a non-starter for us.
> 
> Any help is greatly appreciated.
> 
> 
> [1] https://github.com/zabetak/calcite-tutorial/blob/31cce59c747e0b763a934109db6a6e7055f175ae/solution/src/main/java/com/github/zabetak/calcite/tutorial/LuceneQueryProcessor.java#L166
>