Hi Dan, Yes, I want to add some udf defined in Spark.
Initially, I had: SqlLibraryOperatorTableFactory.INSTANCE.getOperatorTable( SqlLibrary.STANDARD, SqlLibrary.SPARK ) but it looks like some functions are not available, in my case it was: * sha2 (not defined) * from_unixtime (not defined) * to_timestamp (available in POSTGRESQL, ORACLE) I will take some time later this month to find the missing functions in https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java and submit a PR. functions doc: https://spark.apache.org/docs/latest/api/sql/index.html 马赛卫 On Mon, Mar 20, 2023 at 10:48 PM Dan Zou <[email protected]> wrote: > Hi Guillaume, are you using your own udf and some functions in > SqlStdOperatorTable at the same time? If yes, SqlOperatorTables#chain is > the right way to organize multiple SqlOperatorTable. > > Best, > Dan Zou > > > 2023年3月21日 02:59，Guillaume Masse <[email protected]> 写道： > > > > I found the solution here: > > > https://stackoverflow.com/questions/58268828/apache-calcite-registering-udfs-for-use-in-relbuilder > > > > The idea is to use > > > > `SqlOperatorTables.of`` to register a list of SqlFunction > > > > then use `SqlOperatorTables.chain` to join it with > > `SqlStdOperatorTable.instance()` > > > > On Mon, Mar 20, 2023 at 1:39 PM Guillaume Masse < > > [email protected]> wrote: > > > >> ah this is Scala, it's a bit shorter to write > >> > >> On Mon, Mar 20, 2023 at 1:12 PM Julian Hyde <[email protected]> > >> wrote: > >> > >>> What language is this? > >>> > >>>> On Mar 20, 2023, at 9:01 AM, Guillaume Masse < > >>> [email protected]> wrote: > >>>> > >>>> Hi All, > >>>> > >>>> How can we register a custom udf? I found some solutions on > >>> Stackoverflow > >>>> but they are out of date ( > >>>> > >>> > https://stackoverflow.com/questions/44147819/adding-a-user-defined-function-to-calcite > >>>> ) > >>>> > >>>> So far what I have: > >>>> > >>>> // top level class > >>>> class CustomUdf { > >>>> def foo(in: Double): Double = in + 1 > >>>> } > >>>> > >>>> // somewhere else > >>>> val typeFactory = new JavaTypeFactoryImpl() > >>>> > >>>> val root = CalciteSchema.createRootSchema(false) > >>>> root.plus().add("foo", ScalarFunctionImpl.create(classOf[CustomUdf], > >>> "foo")) > >>>> > >>>> val catalogReader = new CalciteCatalogReader( > >>>> root, > >>>> Collections.emptyList(), > >>>> typeFactory, > >>>> CalciteConnectionConfig.DEFAULT > >>>> ) > >>>> > >>>> val operators = > >>>> SqlLibraryOperatorTableFactory.INSTANCE.getOperatorTable( > >>>> SqlLibrary.STANDARD, > >>>> SqlLibrary.SPARK > >>>> ) > >>>> > >>>> val sqlValidator = SqlValidatorUtil.newValidator( > >>>> operators, > >>>> catalogReader, > >>>> typeFactory, > >>>> SqlValidator.Config.DEFAULT > >>>> ) > >>>> > >>>> simple expression failed to > >>>> validate,org.apache.calcite.runtime.CalciteContextException: From line > >>> 1, > >>>> column 1 to line 1, column 22: No match found for function signature > >>>> toto(<NUMERIC>)) > >>>> > >>>> From what I saw on Stackoverflow, it looks like I need to also add the > >>>> function to the operator table. > >>>> > >>>> Thanks! > >>>> Guillaume Massé (马赛卫) > >>> > >> > >
