Table of Contents

Introduction

Oracle support for property graphs covers multiple solutions. In some cases, when a large dataset needs to be analyzed, it would be good to consider moving part of the workload outside of the database to an Oracle Graph Server instance. This article covers such three-layer architecture and describes Oracle Graph Server Java API. The code presented here also uses the new approach of working with Property Graphs in Oracle Database 23c, where property graph operations are implemented as a part of SQL language (SQL/PGQ), as defined in SQL:2023 standard. As this integration with SQL has been described multiple times, for example here I won’t explain it once again, instead of this I would like to present how we can utilize graphs using Java and Oracle Graph Server.

Prerequisities

  1. Any flavour of Oracle Database 23c. For example Oracle Database 23c Free Developer Edition, Oracle Database 23c Base Database Service, etc.
  2. A database account with all the privileges described in the following LiveLabs Workshop: Analyze, Query and Visualize Graphs in Oracle Database
  3. Database tables used in exactly the same workshop.
  4. Oracle Graph Server version 23.1 or newer as these versions support Oracle SQL Property Graphs, configured to work with the database mentioned in point (1)
  5. Oracle JDK v. 17 (at least) 

Creating and querying SQL Property Graphs

This section presents the new way of working with property graphs in Oracle Database 23c. Previous versions, including 19c,21c etc. did not support property graphs natively, which means, that it was not possible to simply execute a SQL statement against a property graph… It was needed to enable support for graphs in a SQLcl, for example, or – in case of Oracle JDBC driver – even using a specific JDBC driver (PgqlJdbcRdbmsDriver)… In opposition to this, the new approach introduced by Oracle Database 23c is significantly simpler. We can use, for example SQL*Plus to work on a property graph, do not need to install and/or configure any additional extensions etc… This is also true, of course, for Java applications. To start to work with property graphs it is enough to use traditional Oracle JDBC thin driver and connect to the database as usually:

import java.sql.*;
import oracle.jdbc.pool.OracleDataSource;

public class Main {
   ...
   public static void SQLPGDemo() {
   try {
        OracleDataSource ds = new OracleDataSource();
        ...
        Connection con = ds.getConnection();
        Statement stmt = con.createStatement();
        stmt.execute("CREATE OR REPLACE PROPERTY GRAPH "+pgName+" VERTEX TABLES (\n" +
                     " customer\n" +
                     " , account\n" +
                     " , merchant\n" +
                     " )\n" +
                     " EDGE TABLES (\n" +
                     " account as account_edge\n" +
                     " SOURCE KEY(id) REFERENCES account (id)\n" +
                     " DESTINATION KEY(customer_id) REFERENCES customer (id)\n" +
                     " LABEL owned_by PROPERTIES (id)\n" +
                     " , parent_of as parent_of_edge \n" +
                     " SOURCE KEY(customer_id_parent) REFERENCES customer (id)\n" +
                     " DESTINATION KEY(customer_id_child) REFERENCES customer (id)\n" +
                     " , purchased as puchased_edge \n" +
                     " SOURCE KEY(account_id) REFERENCES account (id)\n" +
                     " DESTINATION KEY(merchant_id) REFERENCES merchant (id)\n" +
                     " , transfer as transfer_edge \n" +
                     " SOURCE KEY(account_id_from) REFERENCES account (id)\n" +
                     " DESTINATION KEY(account_id_to) REFERENCES account (id)\n" +
        " ) ");
...

Above code uses a normal JDBC connection and executes a normal CREATE OR REPLACE statement to create a property graph. Also it is possible to query such graph using SQL/PGQ and normal JDBC Statement and ResultSet interfaces:

             ResultSet rset = stmt.executeQuery("SELECT account_no\n" +
                                                "FROM GRAPH_TABLE ( CUSTOMER_GRAPH MATCH (v1)-[transfer_edge]->{1,2}(v1)\n" +
                                                "columns (v1.account_no as account_no))");
             while (rset.next()) {
                  System.out.println(rset.getString(1));
             }

This approach offers, of course, excellent integration with other data models available in Oracle Database, significantly simplifying development of something, what we can call, say a “Property-Graph oriented application”

Using Graph Server API to work with SQL Property Graphs

We could not call SQL/PGQ a perfect solution if it is not complete. It means, that it should be supported not only by Oracle Database, but also by other, external tools/software, which are frequently used to work on property graphs, like Oracle Graph Server, for example. Indeed Oracle Graph Server supports SQL Property Graphs since version 23.1, so they can be accessed in Graph Visualization Application, opg4py and opg4j tools… They are also supported by Java API, which will be shortly presented in this section. First of all we need to add a repository and a dependency to our pom.xml file (we assume, that we are creating a Maven-based Java application):

...
<repository>
   <id>spoofax</id>
   <url>https://artifacts.metaborg.org/content/repositories/releases</url>
</repository>
...
<dependency>
   <groupId>com.oracle.database.graph</groupId>
   <artifactId>pgx-api</artifactId>
   <version>24.1.0</version>
</dependency>
...

Now we need to import required packages…

import oracle.pg.rdbms.GraphServer;
import oracle.pgx.api.*;

…

… and finally we are able to use Java API for Oracle Graph Server. In the first step we need to open a connection and create a session in an Oracle Graph Server instance

...
ServerInstance si = GraphServer.getInstance("https://"+pgxHost+":"+pgxPort,username,password.toCharArray());
PgxSession ses = si.createSession("my-session");
...

where 

  • pgxHost is the name or ip address of the host where our Graph Server instance has been installed
  • pgxPort is the port number used by the Graph Server instance (usually 7007)
  • username and password are database username and password, where the SQL Property Graph has been created

After the session is created successfully, before we can query the graph, we need to load it into Graph Server memory:

PgxGraph graph = ses.readGraphByName(username.toUpperCase(), pgName.toUpperCase(), GraphSource.PG_SQL);

// above instruction is equivalent of the following instruction in opg4py tool:

// graph = session.read_graph_by_name(“<graph_name>”,”pg_sql”)

where

  • username is the name of the schema owning the property graph; please, note, that we need to ensure, that it is provided in upper characters only
  • pgName is the name of the graph we want to use. It needs to be provided in capital characters as well.
  • The third parameter describes the type of graph we want to load. In our case we need to set it to GraphSource.PG_SQL as we want to load a SQL Property Graph. In case when a graph has been created using the older interface it would be needed to set it to GraphSource.PG_VIEW.

After the graph is loaded into our session we can query it:

PgqlResultSet rset = graph.queryPgql("SELECT a1.account_no AS a1_account\n" +
                                     " , t1.transfer_date AS t1_date\n" +
                                     " , t1.amount AS t1_amount\n" +
                                     " , a2.account_no AS a2_account\n" +
                                     " , t2.transfer_date AS t2_date\n" +
                                     " , t2.amount AS t2_amount\n" +
                                     "FROM MATCH (a1)-[t1:transfer_edge]->(a2)-[t2:transfer_edge]->(a1)\n" +
                                     "WHERE t1.transfer_date < t2.transfer_date");

while (rset.next()) {
   System.out.println(rset.getString(1));
}

Please, note, that we use in this case specific result set class (PgqlResultSet). Also syntax of the query is different, than the one defined in SQL:2023 standard. In fact, above example uses PGQL in contrast to SQL/PGQ, which can be used when connected to the database directly. 

Summary

This article presents how to use Property Graphs in two ways.

  • In some cases it would be good to keep the whole workload as near to the data as possible – in the database. In this way we can easily ensure the data consistency. Also SQL/PGQ simplifies the development of property-graph-oriented applications as it allows for using standard interfaces and tools, like JDBC, SQL*Plus etc…
  • In some other cases, especially when large data set needs to be analyzed, moving a part of the workload outside of the database – to Graph Server – may bring significant performance benefits. Also in case, when there is need to ensure high standard of security, separating property graphs from the rest of the data may be a good idea. In that case we are not limited to use set of tools installed with Oracle Graph Server. We can develop our own, customized applications using multiple APIs provided by this layer.

Additional resources

GraphQL, SQL/PGQ or SPARQL ? A blog post by Karin Patenge, senior principal graph and spatial product manager

Get started with property graphs in Oracle Database 23c Free – Developer Release: a blog post by Jayant Sharma, Sr. Director, Product Mgmt

Github repository containing complete, working example described in this article

Analyze, Query and Visualize Graphs in Oracle Database Oracle LiveLabs Workshop