> You say you are ‘using JdbcMeta’ but I don’t really understand what that > means. Can you expand on your use case/environment?
Our users interact with our system via an Avatica HttpServer, whose handler is an AvaticaHandler (for example, AvaticaJsonHander), whose service is a LocalService, whose Avatica “Meta” is an Avatica JdbcMeta. But I think that’s all just irrelevant details. The short of it is: when the user invokes `Connection.prepare`, that eventually gets all the way to `CalciteConnection.prepare` (or specifically in our case `CalciteConnectionImpl.prepare`) The problem is that we don’t see any way to provide any SqlOperatorTable or SqlOperatorTable factory of some kind what so ever to that connection, or its CalciteConnectionConfig which is ultimately responsible for producing the SqlOperatorTable which is used in the CalciteCatalogReader which is used in the validation process. As far as I can tell, the *only* way to do something even remotely similar is to set CalciteConnectionProperties.FUN in the configuration. But since we are defining a *new* library of operators, that requires setting up our own SqlLibrary, which means adding a new SqlLibrary enum, which means *forking* the Calcite library which is a non-starter for us. Hence, this rigamarole with providing them though the Schema, which doesn’t provide the same functionality as SqlOperators, which is a problem for us. Is my understanding of how the CalciteConnection.prepare pathway sources the SqlOperator table(s) correct? Is there a way of providing an additional set of SqlOperator table(s)? Is there a way to define an SqlLibrary and the operators for that library, *without* having to fork the Calcite library? And if not, could such functionality be added to Calcite? > When you say ‘our approach’ and ‘our users’, I guess you are talking about > your organization as opposed to the Calcite community in general Correct. I guess I should clarify that our UDFs are really our organizations special purpose SQL functions that we define and implement on our users behalf. Our users themselves do not define functions to the SQL front-end; they asks us to do so and we provide it for them. Probably the easiest example is a pair of functions which convert between organization’s “unique ids” in the human readable notation we use (in the form of a string) and the internal representation (64-bit ints) that that the system uses and stores. Thanks -Ian J. Bertolacci On 2022/08/23 20:22:57 Julian Hyde wrote: > 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><https://issues.apache.org/jira/browse/CALCITE-2772%3e> > > > > > > On Aug 23, 2022, at 11:56 AM, Ian Bertolacci > > <[email protected]<mailto:[email protected]>LID> 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 > > > >
