外部存储函数

Java UDF

Java UDF 是指用 Java 语言实现的用户自定义函数（User-Defined Function，通过支持 Java UDF，您可以将大量 Java 生态产品融入 OceanBase 数据库，提升了 UDF 的开发效率。

构造 Java UDF 函数

您可以编译并打包下述代码为 my_add.jar 后，然后可以在 OBServer 中创建 Java UDF，示例如下：

package org.example;

public class MyAdd {
    public Integer evaluate(Integer a, Integer b) {
        if (a == null || b == null) {
            return null;
        }
        return a + b;
    }
}

创建 Java UDF

为了适应 Java UDF 使用场景的灵活性，我们提供了两种方式来创建 Java UDF，分别是通过 URL 和外部资源创建。

通过 URL 创建 Java UDF

执行如下 SQL 语句创建名为 my_add 的 Java UDF。

CREATE FUNCTION my_add(x int, y int)
RETURNS int
PROPERTIES (
  symbol = 'org.example.MyAdd', 
  type = 'odpsjar',
  file = '<URL to Jar>'
);

其中：

• symbol：表示指定的入口类名。
• type：表示外部 UDF 的类型，目前支持 odpsjar、UDAFJar、UDTFJar、Python。
• file：表示 Jar 包所在的 URL。

创建成功之后即可像使用 PL UDF 一样使用 Java UDF，使用示例如下：

obclient> CREATE FUNCTION my_add(x int, y int)
    -> RETURNS int
    -> PROPERTIES (
    ->   symbol = 'org.example.MyAdd',
    ->   type = 'odpsjar',
    ->   file = 'http://******/my_add.jar'
    -> );
Query OK, 0 rows affected (0.113 sec)

obclient> SELECT my_add(1, 2);
+--------------+
| my_add(1, 2) |
+--------------+
|            3 |
+--------------+
1 row in set (0.021 sec)

通过外部资源创建 Java UDF

OBServer 通过外部资源管理 Java UDF Jar 包等外部存储过程的依赖资源，需要将 Jar 包通过 DBMS_JAVA.LOADJAVA 系统包函数上传到 OBServer，并在创建的 Java UDF 中引用这个 Jar 包对应的外部资源。

外部资源的上传和 UDF 的创建没有严格的先后次序要求，只要保证 UDF 调用时，资源已经上传即可。并且，一个外部资源可以被多个 UDF 使用。

操作步骤如下：

1. 上传 Jar 包

   调用 DBMS_JAVA.LOADJAVA 将 Jar 包作为外部资源上传到 OBServer。

   String url = "<URL to Jar>";
   InputStream is = new URL(url).openStream();
   
   // conn is a connection to OceanBase
   PreparedStatement ps = conn.prepareStatement("call dbms_java.loadjava(? ,? ,? )");
   ps.setString(1, "my_add_jar");
   ps.setBlob(2, is);
   ps.setString(3, "my add UDF jar");
   
   ps.execute();
   

2. 创建 Java UDF

   执行如下 SQL 语句创建名为 my_add 的 Java UDF。

   CREATE FUNCTION my_add(x int, y int)
   RETURNS int
   PROPERTIES (
     symbol = 'org.example.MyAdd', 
     type = 'odpsjar',
     file = 'my_add_jar'
   );
   

   其中：

   • symbol：表示指定的入口类名。
   • type：表示外部 UDF 的类型，目前支持 odpsjar、UDAFJar、UDTFJar、Python。
   • file：表示为 Jar 包对应的外部资源名称。

创建成功之后即可像使用 PL UDF 一样使用 Java UDF，使用示例如下：

obclient> CREATE FUNCTION my_add(x int, y int)
    -> RETURNS int
    -> PROPERTIES (
    ->   symbol = 'org.example.MyAdd',
    ->   type = 'odpsjar',
    ->   file = 'my_add_jar'
    -> );
Query OK, 0 rows affected (0.118 sec)

obclient> SELECT my_add(1, 2);
+--------------+
| my_add(1, 2) |
+--------------+
|            3 |
+--------------+
1 row in set (0.005 sec)

Java UDTF

Java UDTF 是指用 Java 语言实现的用户自定义表函数（Java User-Defined Table Function），UDTF 与一般 UDF 最大的不同是，一般 UDF 返回一个标量值，即一行数据，而 UDTF 可以返回多行数据。

创建 Java UDTF

为了适应 Java UDTF 使用场景的灵活性，我们提供了两种方式来创建 Java UDTF，分别是通过 URL 和外部资源创建。

通过 URL 创建 Java UDTF

您可以编译并打包下述代码为 my_split.jar 后，然后可以在 OBServer 中创建 Java UDTF，示例如下：

package my.test;

public class MySplit {
    public String[] process(String str) {
        if (str == null) return null;
        return str.split(" ");
    }
}

创建 Java UDTF

CREATE FUNCTION my_split(x longtext)
RETURNS varchar(1024)
PROPERTIES (
  symbol = 'my.test.MySplit', 
  type = 'UDTFJar',
  file = 'http://******/my_split.jar'
);

创建外部函数时，您可以通过指定 type 为 UDTFJar 来创建 Java UDTF。之后可以在 SQL 语句中与table function 联合使用，可以作为单标查询，也可以与其它表做 join 操作。

调用 Java UDTF 函数

调用示例如下：

1. 创建表 t1 并插入几条数据。

   obclient> CREATE TABLE t1(a int, b decimal(10, 2), c1 varchar(32));
   

   obclient> INSERT INTO t1 VALUES
   (1, 2.1, 'hello oceanbase'),
   (2, 2.2, 'hello UDTF')
   ;
   

2. 执行如下命令调用 Java UDTF。

   obclient> SELECT t1.a,t1.b, COLUMN_VALUE FROM t1, table(my_split(t1.c1));
   +------+------+--------------+
   | a    | b    | COLUMN_VALUE |
   +------+------+--------------+
   |    1 | 2.10 | hello        |
   |    1 | 2.10 | oceanbase    |
   |    2 | 2.20 | hello        |
   |    2 | 2.20 | UDTF         |
   +------+------+--------------+ 
   

通过外部资源创建 Java UDTF

OBServer 通过外部资源管理 Java UDTF Jar 包等外部存储过程的依赖资源，需要将 Jar 包通过 DBMS_JAVA.LOADJAVA 系统包函数上传到 OBServer，并在创建的 Java UDTF 中引用这个 Jar 包对应的外部资源。

操作步骤如下：

1. 上传 Jar 包

   调用 DBMS_JAVA.LOADJAVA 将 Jar 包作为外部资源上传到 OBServer。

2. 创建 Java UDTF

   执行如下 SQL 语句创建名为 my_split 的 Java UDTF。

   CREATE FUNCTION my_split(x int, y int)
   RETURNS int
   PROPERTIES (
     symbol = 'my.test.MySplit', 
     type = 'UDTFJar',
     file = 'my_split_jar'
   );
   

   其中：

   • symbol：表示指定的入口类名。
   • type：表示外部 UDF 的类型，目前支持 odpsjar、UDAFJar、UDTFJar、Python。
   • file：表示为 Jar 包对应的外部资源名称。

创建成功之后即可像使用 PL UDF 一样使用 Java UDTF，使用示例如下：

obclient> SELECT t1.a,t1.b, COLUMN_VALUE FROM t1, table(my_split(t1.c1));
    +------+------+--------------+
    | a    | b    | COLUMN_VALUE |
    +------+------+--------------+
    |    1 | 2.10 | hello        |
    |    1 | 2.10 | oceanbase    |
    |    2 | 2.20 | hello        |
    |    2 | 2.20 | UDTF         |
    +------+------+--------------+ 

Java UDAF

Java UDAF 是指使用 Java 语言实现的用户自定义聚合函数（Java User-Defined Aggregate Function）， UDAF 可以将多行数据中的一列或多列通过用户自定义逻辑聚合成一个标量数据，通常与 GROUP BY 语句同时使用，对于每个 GROUP 各返回一个聚合后的结果。

创建Java UDAF

为了适应 Java UDAF 使用场景的灵活性，我们提供了两种方式来创建 Java UDAF，分别是通过 URL 和外部资源创建。

通过 URL 创建 Java UDAF

编译并打包下述 Java 代码为 my_avg.jar ，然后可以在 OBServer 中创建 Java UDAF，示例如下：

package my.test;

public class MyAvg {
    private double sum = 0;
    private double count = 0;

    public void iterate(Double x) {
        sum += x;
        count += 1;
    }

    public void merge(MyAvg other) {
        sum += other.sum;
        count += other.count;
    }

    public Double terminate() {
        return sum / count;
    }
}

创建 Java UDAF

CREATE FUNCTION my_avg(x double)
RETURNS double
PROPERTIES (
  symbol = 'my.test.MyAvg', 
  type = 'UDAFJar',
  file = 'http://******/my_avg.jar'
);

调用 Java UDAF 函数

调用示例如下：

1. 创建表 t 并插入几条数据。

   obclient> CREATE TABLE t(a int, b int);
   

   obclient> INSERT INTO t VALUES(1, 10),(2, 100),(1, 20),(2, 200),(3, 0);
   

2. 执行如下命令调用 Java UDAF。

   obclient> SELECT my_avg(b) FROM t GROUP BY a;
   +--------------+
   |my_avg(b)     |
   +--------------+
   |15.0          |
   +--------------+
   |150.0         |
   +--------------+
   |0.0           |
   +--------------+
   

通过外部资源创建 Java UDAF

OBServer 通过外部资源管理 Java UDAF Jar 包等外部存储过程的依赖资源，需要将 Jar 包通过 DBMS_JAVA.LOADJAVA 系统包函数上传到 OBServer，并在创建的 Java UDAF 中引用这个 Jar 包对应的外部资源。

操作步骤如下：

1. 上传 Jar 包

   调用 DBMS_JAVA.LOADJAVA 将 Jar 包作为外部资源上传到 OBServer。

2. 创建 Java UDAF

   执行如下 SQL 语句创建名为 my_avg 的 Java UDAF。

   CREATE FUNCTION my_avg(x int, y int)
   RETURNS int
   PROPERTIES (
     symbol = 'my.test.MyAvg', 
     type = 'UDAFJar',
     file = 'my_avg_jar'
   );
   

   其中：

   • symbol：表示指定的入口类名。
   • type：表示外部 UDF 的类型，目前支持 odpsjar、UDAFJar、UDTFJar、Python。
   • file：表示为 Jar 包对应的外部资源名称。

创建成功之后即可像使用 PL UDF 一样使用 Java UDAF，使用示例如下：

obclient> SELECT my_avg(b) FROM t GROUP BY a;
+--------------+
|my_avg(b)     |
+--------------+
|15.0          |
+--------------+
|150.0         |
+--------------+
|0.0           |
+--------------+

Python UDF

Python UDF 是指使用 Python 语言实现的用户自定义函数。

OceanBase 数据库在创建 Python UDF 时只需指定 Python 脚本和入口类，执行期 OBServer 会实例化入口类并调用其 evaluate 方法。

以如下所示的 varcharUrl.py 为例创建一个 Python UDF:e

#!/usr/bin/env python3
# -*- coding:utf-8 -*-
"""
# @File     : varcharUrl.py
"""

class varcharUrl(object):
    def evaluate(self, c1, c2):
      if c1 == None:
        return c2
      if c2 == None:
        return c1
      return c1 + c2

上述 varcharUrl.py 脚本是标准的 OceanBase 风格，同时也支持了 ODPS 风格，可以将 ODPS UDF Python 脚本直接迁移到 OceanBase 中，脚本示例如下：

#!/usr/bin/env python3e
# -*- coding:utf-8 -*-
"""
# @File     : varcharUrl_ODPS.py
"""

from odps.udf import annotate

@annotate("string, string -> string")
class varcharUrl(object):
    def evaluate(self, c1, c2):
      if c1 == None:
        return c2
      if c2 == None:
        return c1
      return c1 + c2

创建 Python UDF

为了适应 Python UDF 使用场景的灵活性，OceanBase 提供了两种方式来创建 Python UDF，分别是通过 URL 和外部资源创建。通过 URL 创建的 Python UDF 不存在 Python 脚本和数据库的耦合，在包含 Python UDF 的 SQL 每次执行前都会从 URL 拉取最新版本的 Python 脚本，适合调试等频繁更新 Python 脚本的场景，但存在额外的性能开销。通过外部资源创建的 Python UDF，将 Python 脚本作为外部资源保存在数据库内部，利用 OceanBase 的分布式能力将 Python 脚本分发到各个节点，适合稳定和对性能有一定要求的场景。

通过 URL 创建 Python UDF

执行如下 SQL 语句创建名为 varchar_test 的 Python UDF。

obclient> CREATE FUNCTION varchar_test(c1 varchar(1000), c2 varchar(1000))
RETURNS varchar(1000)
PROPERTIES (
  symbol = 'varcharUrl', 
  type = 'Python',
  file = '<URL to Python>'
);

其中：

• symbol：表示指定的入口类名。

  说明

  symbol 大小写敏感， 支持两个格式，class_name和 module_name.class_name。如果定义class_name，OceanBase 内部会生成一个唯一的 module_name保证每个 Python 类的唯一性。如果是 module_name.class_name, 需要用户保证其唯一性，否则在同一条语句中调用两个不同 UDF，如果两个 UDF 定义中的 module_name.class_name相同，会出现覆盖情况，即只有一个 Python 类会起作用。

• type：表示外部 UDF 的类型，目前支持 odpsjar、UDAFJar、UDTFJar、Python。

• file：表示 Python 脚本所在的 URL。

创建成功之后即可像使用 PL UDF 一样使用 Python UDF，使用示例如下：

obclient> CREATE FUNCTION varchar_test(c1 varchar(1000), c2 varchar(1000))
    -> RETURNS varchar(1000)
    -> PROPERTIES (
    ->   symbol = 'varcharUrl', 
    ->   type = 'Python',
    ->   file = 'http://*****/varcharUrl.py'
    -> );
Query OK, 0 rows affected (0.289 sec)
obclient> SELECT varchar_test('OceanBase', '海扬数据库');
+----------------------------------------------+
| varchar_test('OceanBase', '海扬数据库')      |
+----------------------------------------------+
| OceanBase海扬数据库                          |
+----------------------------------------------+

通过外部资源创建 Python UDF

OBServer 通过外部资源管理 Python 脚本等外部存储过程的依赖资源，需要将 Python 脚本通过 DBMS_PYTHON.LOADPYHON 系统包函数上传到 OBServer，并在创建的 Python UDF 中引用这个 Python 脚本对应的外部资源。

外部资源的上传和 UDF 的创建没有严格的先后次序要求，只要保证 UDF 调用时，资源已经上传即可。并且，一个外部资源可以被多个 UDF 使用。

操作步骤如下：

1. 上传 Python 脚本

   可以调用 DBMS_PYTHON.LOADPYHON 将 Python 脚本作为外部资源上传到 OBServer。脚本文件示例如下：

   String url = "<URL to Python>";
   InputStream is = new URL(url).openStream();
   
   // conn is a connection to OceanBase
   PreparedStatement ps = conn.prepareStatement("call DBMS_PYTHON.LOADPYHON(? ,? ,? )");
   ps.setString(1, "varchar_test_script");
   ps.setBlob(2, is);
   ps.setString(3, "varcharUrl Python script");
   
   ps.execute();
   

   创建成功后，DBA 用户可以在当前租户下查询 DBA_OB_EXTERNAL_RESOURCES 视图获取当前租户所有的外部资源，或登录 SYS 租户查询 CDB_OB_EXTERNAL_RESOURCES 获取所有租户的外部资源。

2. 创建 Python UDF

   执行如下 SQL 语句创建名为 varchar_test 的 Python UDF：

   obclient> CREATE FUNCTION varchar_test(c1 varchar(1000), c2 varchar(1000))
    RETURNS varchar(1000)                                                      
    PROPERTIES (
    symbol = 'varcharUrl', 
    type = 'Python',
    file = 'varchar_test_script'
    );
   

   其中：

   • symbol：表示指定的入口类名。
   • type：表示 Python 的类型，目前仅支持 Python 类型。
   • file：为 Python 脚本对应的外部资源名称。

创建成功之后即可像使用 PL UDF 一样使用 Python UDF，使用示例如下：

obclient> CREATE FUNCTION varchar_test(c1 varchar(1000), c2 varchar(1000))
    -> RETURNS varchar(1000)
    -> PROPERTIES (
    ->   symbol = 'varcharUrl', 
    ->   type = 'Python',
    ->   file = 'varchar_test_script'
    -> );
Query OK, 0 rows affected (0.221 sec)

obclient> SELECT varchar_test('OceanBase', '海扬数据库');
+----------------------------------------------+
| varchar_test('OceanBase', '海扬数据库')      |
+----------------------------------------------+
| OceanBase海扬数据库                          |
+----------------------------------------------+
1 row in set (0.060 sec)