Java UDF
Java UDF refers to a user-defined function implemented in the Java language. By supporting Java UDF, you can integrate a wide range of Java ecosystem products into OceanBase AI 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 through 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. Currently, the following types are supported:odpsjar,UDAFJar,UDTFJar, andPython.file: the URL where the JAR package is located.
After successful creation, you can use the Java UDF as you would a PL/UDF. The usage example is as follows:
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 upload the JAR package to the OBServer using the DBMS_JAVA.LOADJAVA system package function, 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:
Upload the JAR package
Call
DBMS_JAVA.LOADJAVAto 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();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. Currently, the following types are supported:odpsjar,UDAFJar,UDTFJar, andPython.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 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 via external resources.
Create a Java UDTF from a URL
You can compile and package the following code into my_split.jar, then create a Java UDTF on the OBServer, as shown below:
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:
Create table
t1and insert some data into it.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') ;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 the dependency resources of external stored procedures, such as Java UDTF JAR packages, through external resources. You must upload the JAR package to the 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:
Upload the JAR package.
Call
DBMS_JAVA.LOADJAVAto upload the JAR package as an external resource to the OBServer.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. Currently, the following types are supported:odpsjar,UDAFJar,UDTFJar, andPython.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. Here is an example:
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 using external resources.
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 in OBServer, as shown below:
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:
Create table
tand 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);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 upload the JAR package to the OBServer using the DBMS_JAVA.LOADJAVA system package function, and reference the external resources corresponding to this JAR package in the created Java UDAF.
The procedure is as follows:
Upload the JAR package.
Call
DBMS_JAVA.LOADJAVAto upload the JAR package as an external resource to the OBServer.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. Currently, the following types are supported:odpsjar,UDAFJar,UDTFJar, andPython.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 |
+--------------+
Java UDF sandbox mode
Starting from V4.6.3, Java UDFs can run in independent sandboxed processes. When sandbox mode is enabled, the JVM runs in isolation from the observer main process, preventing JVM exceptions from affecting the database main process. It is recommended to enable this for public cloud deployment scenarios.
To enable it: After enabling the ob_enable_java_udf and ob_enable_java_env parameters, set the ob_enable_java_udf_sandbox parameter to TRUE.
ALTER SYSTEM SET ob_enable_java_udf_sandbox = TRUE;
For more information, see ob_enable_java_udf_sandbox.
Python UDF
A Python UDF is a user-defined function implemented in the Python language.
To create a Python UDF in OceanBase AI 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 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 is in the standard OceanBase style and also supports the ODPS style. This allows you to directly migrate ODPS UDF Python scripts to OceanBase. An example script is as follows:
#!/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 flexibility of Python UDF usage scenarios, OceanBase provides two methods for creating Python UDFs: via URL and using external resources. A Python UDF created via a URL decouples the Python script from the database. Each time an SQL statement containing the Python UDF is executed, the latest version of the Python script is fetched from the URL. This method is suitable for scenarios where the Python script is frequently updated, such as debugging, but introduces additional performance overhead. A Python UDF created using external resources saves the Python script as an external resource within the database. OceanBase's distributed capabilities are leveraged to distribute the Python script to all nodes, making it suitable for stable scenarios with specific performance requirements.
Note
Starting from OceanBase AI Database V4.6.3, Python UDFs are executed in independent child processes without affecting the main observer process. The child processes run in a restricted security environment, with high-risk system calls disabled, no network access permissions, and no ability to access file systems outside the sandbox environment.
Install Python
The following table describes the version, architecture requirements, and download addresses of Python dependency packages for OceanBase AI 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 AI Database:
PyArrow does not differentiate between EL7 and AL8; it only distinguishes between architectures (x86_64 / aarch64) and glibc versions.
The following steps use the AL8 environment as an example. The complete installation procedure is as follows:
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.rpmRun 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 -divNavigate to the installation directory of the Python-related library files.
cd usr/local/oceanbase/deps/devel/python3Automatically install PyArrow using pip PyArrow is the official Python implementation of the Apache Arrow project. 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 pyarrowAs with the previous command, if the default source does not contain the
wheelpackage, it will automatically download the source code package, such aspyarrow-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.
Download the pyarrow wheel package to 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.whlExtract 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
symbolis case-sensitive and supports two formats:class_nameandmodule_name.class_name. If you defineclass_name, OceanBase internally generates a uniquemodule_nameto ensure the uniqueness of each Python class. Formodule_name.class_name, you must ensure its uniqueness. Otherwise, if you call two different UDFs in the same statement and theirmodule_name.class_namedefinitions are the same, an overriding situation will occur, meaning only one Python class will take effect.type: the type of the external UDF. Currently, the following types are supported:odpsjar,UDAFJar,UDTFJar, andPython.file: the URL where the Python script is located.
After successful creation, you can use the Python UDF just like a PL/UDF. Here is an example:
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. Moreover, one external resource can be used by multiple UDFs.
The procedure is as follows:
Upload the Python script
You can call
DBMS_PYTHON.LOADPYHONto 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 successful creation, the DBA user can query the
DBA_OB_EXTERNAL_RESOURCESview in the current tenant to obtain all external resources of the current tenant, or log in to the SYS tenant to queryCDB_OB_EXTERNAL_RESOURCESfor external resources of all tenants.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. Here is an example:
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)