External storage functions

Java UDF

A Java UDF is a user-defined function implemented in the Java language. By supporting Java UDFs, you can integrate a wide range of Java ecosystem products into OceanBase Database, thereby improving UDF development efficiency.

Construct a Java UDF function

You can compile and package the following code into my_add.jar, then create a Java UDF in OBServer. Example:

package org.example;

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

Create a Java UDF

To accommodate the flexibility of Java UDF usage scenarios, we provide two methods for creating Java UDFs: via URL and via external resources.

Create a Java UDF from a URL

Execute the following SQL statement to create a Java UDF named my_add.

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

Where:

• symbol: the name of the specified entry class.
• type: the type of the external UDF. Valid values: odpsjar, UDAFJar, UDTFJar, and Python.
• file: the URL where the JAR package is located.

After successful creation, you can use the Java UDF as you would a PL/UGF. Here is an example:

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)

Create a Java UDF using external resources

OBServer manages dependency resources for external stored procedures, such as Java UDF JAR packages, through external resources. You must use the DBMS_JAVA.LOADJAVA system package function to upload the JAR package to the OBServer, and reference the external resources corresponding to this JAR package in the created Java UDF.

There is no strict order for uploading external resources and creating UDFs. As long as the resources are uploaded before the UDF is called, it will work. Moreover, one external resource can be used by multiple UDFs.

The procedure is as follows:

1. Upload the JAR package.

   Call DBMS_JAVA.LOADJAVA to upload the JAR package as an external resource to the 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. Create a Java UDF

   Execute the following SQL statement to create a Java UDF named my_add.

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

   Where:

   • symbol: the name of the specified entry class.
   • type: The type of the external UDF. Valid values: odpsjar, UDAFJar, UDTFJar, and Python.
   • file: the name of the external resource corresponding to the JAR package.

After successful creation, you can use the Java UDF as you would a PL/UGF. Example usage:

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

A Java UDTF is a user-defined table function implemented in the Java language. The main difference between a UDTF and a regular UDF is that a regular UDF returns a single scalar value, which corresponds to a single row of data, whereas a UDTF can return multiple rows of data.

Create a Java UDTF

To accommodate the flexibility of Java UDTF usage scenarios, we provide two methods for creating Java UDTFs: via URL and using external resources.

Create Java UDTF from URL

You can compile and package the following code into my_split.jar, then create a Java UDTF in OBServer. Example:

package my.test;

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

Create a Java UDTF

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

When creating an external function, you can specify type as UDTFJar to create a Java UDTF. It can then be used with table function in SQL statements, either as a single-row query or for join operations with other tables.

Call Java UDTF functions

A sample call is as follows:

1. Create table t1 and insert several rows of data.

   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. Run the following command to call the 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         |
   +------+------+--------------+ 
   

Create a Java UDTF from an external resource

OBServer manages dependency resources for external stored procedures, such as Java UDTF JAR packages, through external resources. You must upload the JAR package to OBServer using the DBMS_JAVA.LOADJAVA system package function, and reference the external resources corresponding to this JAR package in the created Java UDTF.

The procedure is as follows:

1. Upload the JAR package

   Call DBMS_JAVA.LOADJAVA to upload the JAR package as an external resource to the OBServer.

2. Create a Java UDTF

   Execute the following SQL statement to create a Java UDTF named my_split.

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

   Where:

   • symbol: the name of the specified entry class.
   • type: The type of the external UDF. Valid values: odpsjar, UDAFJar, UDTFJar, and Python.
   • file: the name of the external resource corresponding to the JAR package.

After successful creation, you can use the Java UDTF as you would a PL/UDF. The usage example is as follows:

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

A Java UDAF is a user-defined aggregate function implemented in the Java language. A UDAF can aggregate one or more columns across multiple rows into a single scalar value using user-defined logic. It is typically used together with the GROUP BY statement, returning an aggregated result for each group.

Create a Java UDAF

To accommodate the flexibility of Java UDAF usage scenarios, we provide two methods for creating Java UDAFs: via URL and through an external resource.

Create a Java UDAF from a URL

Compile and package the following Java code into my_avg.jar. You can then create a Java UDAF on the OBServer, as shown in the following example:

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;
    }
}

Create a Java UDAF

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

Call Java UDAF functions

A sample call is as follows:

1. Create table t and insert a few rows of data.

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

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

2. Run the following command to call the Java UDAF.

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

Create a Java UDAF from an external resource

OBServer manages dependency resources for external stored procedures, such as Java UDAF JAR packages, through external resources. You must use the DBMS_JAVA.LOADJAVA system package function to upload the JAR package to the OBServer, and reference the external resources corresponding to this JAR package in the created Java UDAF.

The procedure is as follows:

1. Upload the JAR package

   Call DBMS_JAVA.LOADJAVA to upload the JAR package as an external resource to the OBServer.

2. Create a Java UDAF

   Execute the following SQL statement to create a Java UDAF named my_avg.

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

   Where:

   • symbol: the name of the specified entry class.
   • type: The type of the external UDF. Valid values: odpsjar, UDAFJar, UDTFJar, and Python.
   • file: the name of the external resource corresponding to the JAR package.

After successful creation, you can use the Java UDAF just like a PL UDF. Here is an example:

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

Python UDF

A Python UDF is a user-defined function implemented in the Python language.

When creating a Python UDF in OceanBase Database, you only need to specify the Python script and the entry class. During execution, the OBServer instance will instantiate the entry class and call its evaluate method.

Take the varcharUrl.py example shown below to create a 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

The preceding varcharUrl.py script follows the standard OceanBase style and also supports the ODPS style. This allows you to directly migrate ODPS UDF Python scripts to OceanBase. The following is an example:

#!/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

Create a Python UDF

To accommodate the flexible usage scenarios of Python UDFs, OceanBase provides two methods for creating them: via a URL or an external resource. A Python UDF created via a URL decouples the Python script from the database. Each time an SQL statement containing this UDF is executed, the latest version of the Python script is fetched from the URL. This approach is suitable for scenarios involving frequent script updates, such as debugging, but introduces additional performance overhead. A Python UDF created from an external resource stores the Python script as an external resource within the database. OceanBase's distributed capabilities are leveraged to distribute the script to all nodes, making it ideal for stable applications with specific performance requirements.

Install Python

The following table describes the version, architecture requirements, and download addresses of Python dependency packages for OceanBase Database:

|Platform|Architecture|Download address| |--|--|--| |EL7| x86_64| https://mirrors.aliyun.com/oceanbase/development-kit/el/7/x86_64/devdeps-python3-3.13.3-152026052814.el7.x86_64.rpm| |EL7| aarch64| https://mirrors.aliyun.com/oceanbase/development-kit/el/7/aarch64/devdeps-python3-3.13.3-152026052814.el7.aarch64.rpm| |AL8| x86_64| https://mirrors.aliyun.com/oceanbase/development-kit/al/8/x86_64/devdeps-python3-3.13.3-152026052814.al8.x86_64.rpm| |AL8| aarch64| https://mirrors.aliyun.com/oceanbase/development-kit/al/8/aarch64/devdeps-python3-3.13.3-152026052814.al8.aarch64.rpm|

The following table describes the version, architecture requirements, and download address of the pyarrow dependency package for OceanBase Database:

PyArrow does not distinguish between EL7 and AL8; it only differentiates between architectures (x86_64 / aarch64) and glibc versions.

The following steps describe how to install OceanBase Database in an AL8 environment.

1. Run the following command to download the Python dependency package:

   wget https://mirrors.aliyun.com/oceanbase/development-kit/al/8/x86_64/devdeps-python3-3.13.3-152026052814.al8.x86_64.rpm
   

2. Run the following command to extract all files in the package to the current directory:

   rpm2cpio devdeps-python3-3.13.3-132025070111.al8.x86_64.rpm | cpio -div
   

3. Navigate to the installation directory of the Python-related library files

   cd usr/local/oceanbase/deps/devel/python3
   

4. Automatically install PyArrow using pip

   PyArrow is the official implementation of the Apache Arrow project in Python. It is a high-performance, cross-language tool for in-memory data processing and exchange. Its core value lies in providing a standardized and efficient underlying format support for big data analytics, machine learning, and cross-system data exchange.

   bin/python3.13 -m ensurepip
   bin/python3.13 -m pip install pyarrow
   

   As with the previous command, if the default source does not contain the wheel package, it will automatically download the source code package, such as pyarrow-24.0.0.tar.gz. In this case, you can specify a mirror source for installation using the following command:

   bin/python3.13 -m pip install pyarrow --only-binary=pyarrow -i https://mirrors.aliyun.com/pypi/simple/
   

If you cannot access the public network, you can also manually download and install PyArrow by following these steps:

1. Download the pyarrow wheel package in an environment with public network access.

   wget https://mirrors.aliyun.com/pypi/packages/b3/93/10a48b5e238de6d562a411af6467e71e7aedbc9b87f8d3a35f1560ae30fb/pyarrow-23.0.1-cp313-cp313-manylinux_2_28_x86_64.whl
   

2. Extract the PyArrow Python wheel package and install it in the Python library directory of your OceanBase development environment.

   unzip pyarrow-23.0.1-cp313-cp313-manylinux_2_28_x86_64.whl -d usr/local/oceanbase/deps/devel/python3/lib/python3.13/site-packages/
   

In the sys tenant, you need to set the ob_python_home parameter to the Python installation path using the following command:

obclient> ALTER SYSTEM STE ob_python_home ="devdeps_python_path/usr/local/oceanbase/deps/devel/python3"; 

Here, devdeps_python_path is the directory where the devdeps-python3 dependency package is extracted.

After installing the Python environment, you can use the following command to enable the Python UDF feature for a tenant:

obclient> ALTER SYSTEM STE ob_enable_python_udf = TRUE tenant tenant_name;

Create a Python UDF from a URL

Execute the following SQL statement to create a Python UDF named varchar_test.

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

Where:

• symbol: the name of the specified entry class.

  Note

  symbol is case-sensitive and supports two formats: class_name and module_name.class_name. If you define class_name, OceanBase internally generates a unique module_name to ensure the uniqueness of each Python class. For module_name.class_name, you must ensure its uniqueness. Otherwise, if you call two different UDFs in the same statement and their module_name.class_name definitions are the same, an overriding situation will occur, meaning only one Python class will take effect.

• type: The type of the external UDF. Valid values: odpsjar, UDAFJar, UDTFJar, and Python.

• file: the URL where the Python script is located.

After successful creation, you can use the Python UDF just like a PL/UDF. The usage example is as follows:

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', 'Haiyang Database');
+----------------------------------------------+
| varchar_test('OceanBase', 'Haiyang Database')      |
+----------------------------------------------+
| OceanBase Database                          |
+----------------------------------------------+

Create a Python UDF using external resources

OBServer manages the dependent resources of external stored procedures, such as Python scripts, through external resources. You must upload the Python script to OBServer using the DBMS_PYTHON.LOADPYHON system package function, and reference the external resources corresponding to this Python script in the created Python UDF.

There is no strict order for uploading external resources and creating UDFs. As long as the resources are uploaded before the UDF is called, it will work. Moreover, one external resource can be used by multiple UDFs.

The procedure is as follows:

1. Upload the Python script

   You can call DBMS_PYTHON.LOADPYHON to upload a Python script as an external resource to the OBServer. The following example shows a script file:

   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();
   

   After the external resources are successfully created, you can query the DBA_OB_EXTERNAL_resources view in the current tenant to obtain all external resources of the current tenant, or log in to the SYS tenant and query the CDB_OB_EXTERNAL_RESOURCES view to obtain the external resources of all tenants.

2. Create a Python UDF

   Execute the following SQL statement to create a Python UDF named varchar_test:

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

   Where:

   • symbol: the name of the specified entry class.
   • type: the Python type. Currently, only Python types are supported.
   • file: The name of the external resource corresponding to the Python script.

After successful creation, you can use the Python UDF just like a PL/UDF. The usage example is as follows:

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', 'Haiyang Database');
+----------------------------------------------+
| varchar_test('OceanBase', 'Haiyang Database')      |
+----------------------------------------------+
| OceanBase Database                          |
+----------------------------------------------+
1 row in set (0.060 sec)