Creating user-defined functions dynamically

[mb...@gmail.com]

Hello all,

 

I have a question about user-defined functions. I see that one can extend
Calcite by adding new functions to various libraries.

My question is whether there exists a mechanism to add such functions
*dynamically*, without changing the Calcite code.

This would allow users to write UDFs in other languages, as long as their
SQL backends support them. What some PRs call UDFs are not really
user-defined, they are dialect-defined. 

 

In principle, Calcite only needs to know the function type signatures to
pass them through all the compilation stages, so a grammar extension for
declaring function "prototypes" could be enough to make this work (at least
for functions which are not polymorphic and thus require no type inference).
There is a complication with constant evaluation: I think that some Calcite
optimization stages try to evaluate functions with compile-time known
arguments at compilation time. One workaround would be to declare such
functions as "nondeterminstic", but perhaps there is a more principled way.

 

Thank you,

Mihai

Re: Creating user-defined functions dynamically

[LakeShen <sh...@gmail.com>]

In our practice,the SchemaPlus has the add(String name, Function function)
method,which could add the function to the schema. You could write a scalar
function method or a aggregate function class,and use
ScalarFunctionImpl or AggregateFunctionImpl
to create a Function,then add it to the schema. The advantage of this is
that you only need to write the method details and Calcite will register
the function for you, but be aware that some functions may be keywords in
parser.jj, which you can make it be nonReservedKeywords. The code you could
see: 1.
https://github.com/apache/calcite/blob/5b66bd3cfaf69cf21b2912f721e4a0b65920cd09/core/src/main/java/org/apache/calcite/schema/SchemaPlus.java#L79
2.
https://github.com/apache/calcite/blob/5b66bd3cfaf69cf21b2912f721e4a0b65920cd09/core/src/main/java/org/apache/calcite/schema/impl/ScalarFunctionImpl.java#L131
3.
https://github.com/apache/calcite/blob/5b66bd3cfaf69cf21b2912f721e4a0b65920cd09/core/src/main/java/org/apache/calcite/schema/impl/AggregateFunctionImpl.java#L85

Best,
LakeShen

Julian Hyde <jh...@gmail.com> 于2023年8月2日周三 06:27写道：

> When you prepare a statement you can provide an instance of
> SqlOperatorTable that contains any functions you desire. (The ‘fun’ JDBC
> connect string parameter is just one means to construct such a table. It
> happens to be particularly convenient for people who wish to create a
> connection by writing a URI and not by writing code.)
>
> You can also specify your own UDFs in a model file. See the ‘functions’
> attribute of ‘Map Schema’ in [1]. These UDFs are not global, but live in a
> particular schema and are visible only if the user has that schema on their
> path.
>
> Julian
>
> [1] https://calcite.apache.org/docs/model.html#map-schema
>
> > On Aug 1, 2023, at 1:40 PM, mbudiu@gmail.com wrote:
> >
> > Hello all,
> >
> >
> >
> > I have a question about user-defined functions. I see that one can extend
> > Calcite by adding new functions to various libraries.
> >
> > My question is whether there exists a mechanism to add such functions
> > *dynamically*, without changing the Calcite code.
> >
> > This would allow users to write UDFs in other languages, as long as their
> > SQL backends support them. What some PRs call UDFs are not really
> > user-defined, they are dialect-defined.
> >
> >
> >
> > In principle, Calcite only needs to know the function type signatures to
> > pass them through all the compilation stages, so a grammar extension for
> > declaring function "prototypes" could be enough to make this work (at
> least
> > for functions which are not polymorphic and thus require no type
> inference).
> > There is a complication with constant evaluation: I think that some
> Calcite
> > optimization stages try to evaluate functions with compile-time known
> > arguments at compilation time. One workaround would be to declare such
> > functions as "nondeterminstic", but perhaps there is a more principled
> way.
> >
> >
> >
> > Thank you,
> >
> > Mihai
> >
>
>

Re: Creating user-defined functions dynamically

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

When you prepare a statement you can provide an instance of SqlOperatorTable that contains any functions you desire. (The ‘fun’ JDBC connect string parameter is just one means to construct such a table. It happens to be particularly convenient for people who wish to create a connection by writing a URI and not by writing code.)

You can also specify your own UDFs in a model file. See the ‘functions’ attribute of ‘Map Schema’ in [1]. These UDFs are not global, but live in a particular schema and are visible only if the user has that schema on their path.

Julian

[1] https://calcite.apache.org/docs/model.html#map-schema

> On Aug 1, 2023, at 1:40 PM, mbudiu@gmail.com wrote:
> 
> Hello all,
> 
> 
> 
> I have a question about user-defined functions. I see that one can extend
> Calcite by adding new functions to various libraries.
> 
> My question is whether there exists a mechanism to add such functions
> *dynamically*, without changing the Calcite code.
> 
> This would allow users to write UDFs in other languages, as long as their
> SQL backends support them. What some PRs call UDFs are not really
> user-defined, they are dialect-defined. 
> 
> 
> 
> In principle, Calcite only needs to know the function type signatures to
> pass them through all the compilation stages, so a grammar extension for
> declaring function "prototypes" could be enough to make this work (at least
> for functions which are not polymorphic and thus require no type inference).
> There is a complication with constant evaluation: I think that some Calcite
> optimization stages try to evaluate functions with compile-time known
> arguments at compilation time. One workaround would be to declare such
> functions as "nondeterminstic", but perhaps there is a more principled way.
> 
> 
> 
> Thank you,
> 
> Mihai
>