Calcite UDF 自定义函数

目标

自定义 SQL 函数，并执行 SQL 查询。

准备如上的表，自定义函数查询返回 age 字段的平方。

相关准备工作在上一篇：https://snailgary.org/9c2d6b5842fa90afb1960b2cf303d6fe

步骤

自定义函数类

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

连接配置

// Build our connection
        Connection connection = DriverManager.getConnection("jdbc:calcite:");

        // Unwrap our connection using the CalciteConnection
        CalciteConnection calciteConnection = connection.unwrap(CalciteConnection.class);

        calciteConnection.getProperties().setProperty(CalciteConnectionProperty.CASE_SENSITIVE.camelName(), "false");

        // Get a pointer to our root schema for our Calcite Connection
        SchemaPlus rootSchema = calciteConnection.getRootSchema();

        // Instantiate a data source, this can be autowired in using Spring as well
        DataSource mysqlDataSource = JdbcSchema.dataSource(
                "jdbc:mysql://localhost:3306/local_test",
                "com.mysql.cj.jdbc.Driver", // Change this if you want to use something like MySQL, Oracle, etc.
                "root", // username
                "123456"   // password
        );


        // Attach our Postgres Jdbc Datasource to our Root Schema
        //rootSchema.add(POSTGRESQL_SCHEMA, JdbcSchema.create(rootSchema, POSTGRESQL_SCHEMA, postgresDataSource, null, null));

        // Attach our MySQL Jdbc Datasource to our Root Schema
        rootSchema.add(MYSQL_SCHEMA, JdbcSchema.create(rootSchema, MYSQL_SCHEMA, mysqlDataSource, null, null));

注册函数类

SchemaPlus post = rootSchema.add("custom_function", new AbstractSchema());

post.add("my_function", ScalarFunctionImpl.create(MySquareFunction.class,"eval"));

将自定义函数注册到 custom_function 空间中，使用 custom_function.functionName 调用。

编写 SQL

String customSql = "select custom_function.my_function(\"age\") as age2 from mysql.\"human\" h";

执行 SQL

Statement statement1 = connection.createStatement();
        ResultSet resultSet1 = statement1.executeQuery(customSql);

        DBTablePrinter.printResultSet(resultSet1);

输出结果如下：

+------+
| AGE2 |
+------+
|  324 |
+------+
|  324 |
+------+
|  324 |
+------+
|  324 |
+------+

巧了，都是 18 岁！