java - 向 Calcite 添加用户定义的函数

Question

我需要向 Calcite 添加一个用户定义的函数，它将一个整数作为参数并返回一个整数。

    public class SquareFunction  {
        public int eval(int a) {
            return a*a;
        }
    }

创建模式和添加功能的相关代码是

     SchemaPlus rootSchema = Frameworks.createRootSchema(false);
     rootSchema.add("SQUARE_FUNC", 
                    ScalarFunctionImpl.create(SquareFunction.class,"eval"); 

但是像一个简单的SQL

select SQUARE_FUNC(1) from test;

在验证期间失败并显示以下消息:

No match found for function signature SQUARE_FUNC(<NUMERIC>)

堆栈跟踪是:

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
    at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463)
    at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:804)
    at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:789)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:4386)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.handleUnresolvedFunction(SqlValidatorImpl.java:1670)
    at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:278)
    at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:223)
    at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:4965)
    at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:1)
    at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:137)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1586)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1571)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.expandSelectItem(SqlValidatorImpl.java:453)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList(SqlValidatorImpl.java:3668)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3186)
    at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60)
    at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:937)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:918)
    at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:220)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:893)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:603)
    at org.apache.calcite.prepare.PlannerImpl.validate(PlannerImpl.java:188) ... 26 more

我遵循了 Calcite 的 UdfTest.testUserDefinedFunctionInView 实现，但仍然无法使其工作。我做错了什么？

Answer 1

Calcite 如何验证函数是否存在？

据我所知，SqlOperatorTable 定义了一个用于枚举和查找 SQL 运算符和函数的目录接口(interface)，而 lookupOperatorOverloads 检索具有给定名称和语法的运算符列表。

对于Frameworks，FrameworkConfig 的默认SqlOperatorTable 是SqlStdOperatorTable.instance()。但是您添加的函数仅在 CalciteCatalogReader 处进行搜索。所以下面的代码可以工作:

   public class UdfTest {
    private static final String SQL      = "select SQUARE_FUNC(1)";
    private static final String FUN_NAME = "SQUARE_FUNC";

    public static void main(String[] args) throws Exception {
        useFramworksExec();
    }

    private static void useFramworksExec() throws Exception {
        CalciteSchema rootSchema = CalciteSchema.createRootSchema(false, false);
        SchemaPlus schema = rootSchema.plus();
        schema.add(FUN_NAME, ScalarFunctionImpl.create(SquareFunction.class, "eval"));
        CalciteCatalogReader reader = new CalciteCatalogReader(rootSchema,
            SqlParser.Config.DEFAULT.caseSensitive(),
            rootSchema.path(null),
            new JavaTypeFactoryImpl());
        FrameworkConfig config = Frameworks.newConfigBuilder().operatorTable(reader).defaultSchema(schema).build();
        Planner planner = Frameworks.getPlanner(config);
        SqlNode ast = planner.parse(SQL);
        SqlNode validatedAst = planner.validate(ast);
        System.out.println(validatedAst.toString());
    }
}