Hi, one last question regarding udf.
on SchemaPlus it's also possible to register udf as ScalarFunction with a function body. How does it relate to SqlOperators? Do I need to define both? Thanks On Tue, Mar 21, 2023 at 12:21 AM Guillaume Masse < [email protected]> wrote: > 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é (马赛卫) >> >>> >> >> >> >>
