User-Defined Functions (UDFs) in Oracle Graph

Oracle Graph provides an in-memory graph analysis engine called PGX, in addition to the ability to work with graphs on Oracle Database (RDBMS). PGX can further accelerate graph traversal operations by holding graph structure in an efficient compressed format in memory.

However, the query language “PGQL” supported by PGX has fewer functions compared to SQL, a general-purpose query language with a long history. Therefore, in this article, we will introduce a method to supplement necessary functions by using user-defined functions. For its actual use case scenario, please refer to the separate article “Count the Number of Parts using Graphs”.

This article uses Graph Server and Client version 23.2.

Create a user-defined function

First, log in to the instance where a PGX is deployed, create a directory, and store a Java program that defines functions.

mkdir -p ~/udfs/java
cd ~/udfs/java
vi Product.java

Product.java:

package my.udfs;

public class Product {
    public static int product(String str) {
        String[] nums = str.split(",");
        int product = 1;
        for (int i = 0; i < nums.length; i++) {
                product = product * Integer.parseInt(nums[i]);
        }
        return product;
    }
}

Compile and create a JAR file.

javac -d ./ Product.java
jar cvf udfs-product.jar my

Copy the JAR file to the directory where the PGX libraries are stored, and change its owner permissions appropriately.

sudo cp udfs-product.jar /opt/oracle/graph/pgx/server/lib/
sudo chown root:oraclegraph /opt/oracle/graph/pgx/server/lib/udfs-product.jar

Remove any unnecessary files.

rm -r my/

Enable user-defined functions

Create a new directory and a configuration file (udfs.json) for the metadata of user-defined functions.

sudo mkdir /opt/oracle/graph/pgx/udfs
sudo vi /opt/oracle/graph/pgx/udfs/udfs.json

udfs.json:

{
  "user_defined_functions": [
    {
      "namespace": "my",
      "language": "java",
      "implementation_reference": "my.udfs.Product",
      "function_name": "product",
      "return_type": "int",
      "arguments": [
         {
           "name": "str",
           "type": "string"
         }
       ]
    }
  ]
}

Enable user-defined functions by setting the path of the directory in the PGX configuration file (pgx.conf).

vi /etc/oracle/graph/pgx.conf

Please add the following line.

pgx.conf:

...

  "udf_config_directory": "/opt/oracle/graph/pgx/udfs/"
}

Restart the PGX.

sudo systemctl restart pgx
tail -f /var/log/oracle/graph/pgx-server.log

Test the user-defined function

Log in to the PGX with a Python client.

python

from opg4py import graph_server
from pypgx import setloglevel
setloglevel("ROOT", "WARN")

instance = graph_server.get_instance(
  "http://localhost:7007", "graphuser", "****")
session = instance.create_session("test")

Alternatively, you can also use the opg4py command to log in to the PGX.

opg4py -b http://localhost:7007 -u graphuser

Check the behavior of the user-defined function my.product() using the “hr” graph included in the setup.

graph = session.get_graph("hr")

graph.query_pgql("""
  SELECT my.product('1,2,3') FROM MATCH (v) LIMIT 1
""").print()

+---------------------+
| my.product('1,2,3') |
+---------------------+
| 6                   |
+---------------------+

User-defined functions can be written in JavaScript as well as Java. For more information, please refer to “14.9 User-Defined Functions (UDFs) in PGX” in the documentation.

Please also check the full list of Oracle Graph articles!

Please learn more about Oracle Graph from:

• Medium (= all articles with Oracle Graph tag)
• Stack Overflow (= questions with oracle-graph tag)
• Slack AnDOUC (= invitation link, please visit #graph)
• Slack OracleDevRel (= invitation link, please visit #oracle-db-graph)