Open in app

Sign in

Write

Sign in

Oracle Graph Quick Start (On-prem)

Ryota Yamanaka
Oracle Developers
Published in
7 min readApr 10, 2023

--

This is a brief guide for the following users:

  • You are using a new or existing Oracle Database (12.2 or higher. Note: the latest developer release Database 23c Free is also available),
  • You are in an on-premises environment (including VMs on the cloud), and
  • You want to try Oracle Graph right now!

I’ll show how to quickly get started setting up and querying a graph database. This article covers 3 sections:

  • Use Oracle Database and a SQL client
  • Add Graph Server
  • Use Python Client

(Graph Server and Client 23.1 were tested for this article.)

Use Oracle Database and client

First, let’s set up our graph using only Oracle Database with a SQL client. SQL Developer, a free SQL client, including a graph client library, so you can start using the graph feature, without any additional configuration.

Here is the procedure:

- Create a database user
- SQL Developer
  - Create a sample dataset
  - Create a graph
  - Run a PGQL query

Create a database user

Connect as a database user with DBA privilege and create a new user. You can also use existing database users.

CREATE USER graphuser
IDENTIFIED BY ********
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;

Grant necessary privileges.

GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO graphuser

SQL Developer

Download SQL Developer and create a connection for GRAPHUSER.

Version 21.2.1.204.1703 is used in this article.

Create a sample dataset

Once you have connected to the database from SQL Developer as the user above, create the sample table data.

CREATE TABLE bank_account (
  acc_id NUMBER
);

CREATE TABLE bank_transaction (
  acc_id_src NUMBER, 
  acc_id_dst NUMBER, 
  txn_id NUMBER, 
  amount NUMBER
);

INSERT INTO bank_account VALUES (1);
INSERT INTO bank_account VALUES (2);
INSERT INTO bank_account VALUES (3);
INSERT INTO bank_account VALUES (4);
INSERT INTO bank_account VALUES (5);

INSERT INTO bank_transaction VALUES (1, 2, 101, 800);
INSERT INTO bank_transaction VALUES (2, 3, 102, 700);
INSERT INTO bank_transaction VALUES (3, 4, 103, 600);
INSERT INTO bank_transaction VALUES (4, 5, 104, 500);
INSERT INTO bank_transaction VALUES (5, 1, 105, 400);

Create a graph

To create a graph from the data in the database tables, you need to run a PGQL statement (Note: starting with Database 23c, you will be able to do this directly from SQL). Right-click on the Property Graph icon to open a PGQL Worksheet.

Run a CREATE PROPERTY GRAPH statement.

CREATE PROPERTY GRAPH bank_graph
  VERTEX TABLES (
    bank_account
      KEY (acc_id)
      LABEL account
  )
  EDGE TABLES (
    bank_transaction
      KEY (txn_id)
      SOURCE KEY(acc_id_src) REFERENCES bank_account
      DESTINATION KEY(acc_id_dst) REFERENCES bank_account
      LABEL transferred_to
  )
  OPTIONS (PG_VIEW)
;

Run a PGQL query

Run a simple PGQL SELECT query and confirm that the result is returned.

SELECT a2.acc_id, t.amount
FROM MATCH (a1) -[t:transferred_to]-> (a2) ON bank_graph
WHERE a1.acc_id = 1

Add Graph Server

Next, let’s add a Graph Server. As we have seen above, Oracle Database can run PGQL queries by itself (more precisely, SQL is converted to PGQL by the Graph Client library installed on SQL clients). Graph Server can speed up graph traversal queries, run various graph algorithms, and even apply graph neural networks (GNNs).

Here is the procedure:

- Install Graph Server
  - Create database roles
  - Install RPM packages
  - Disable TLS
- Load and publish the graph
- Use the Graph Visualization App

Install Graph Server

You need a separate server instance. Oracle Linux 7/8 and RedHat Linux 7/8 are supported.

Graph Server uses the authentication for the database, so database users can access data on Graph Server. We need to create new roles in the database and set the JDBC URL for the Graph Server to connect the database.

Create database roles

Connect to the database as a user with DBA privileges and run the following script to create new roles. This step is not necessary for 23c.

-- This procedure creates a list of roles needed for the Graph Server.
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
  role_exists EXCEPTION;
  PRAGMA EXCEPTION_INIT(role_exists, -01921);
  TYPE graph_roles_table IS TABLE OF VARCHAR2(50);
  graph_roles graph_roles_table;
BEGIN
  graph_roles := graph_roles_table(
    'GRAPH_DEVELOPER',
    'GRAPH_ADMINISTRATOR',
    'GRAPH_USER',
    'PGX_SESSION_CREATE',
    'PGX_SERVER_GET_INFO',
    'PGX_SERVER_MANAGE',
    'PGX_SESSION_READ_MODEL',
    'PGX_SESSION_MODIFY_MODEL',
    'PGX_SESSION_NEW_GRAPH',
    'PGX_SESSION_GET_PUBLISHED_GRAPH',
    'PGX_SESSION_COMPILE_ALGORITHM',
    'PGX_SESSION_ADD_PUBLISHED_GRAPH');
  FOR elem IN 1 .. graph_roles.count LOOP
    BEGIN
      dbms_output.put_line('create_graph_roles: ' || elem || ': CREATE ROLE ' || graph_roles(elem));
      EXECUTE IMMEDIATE 'CREATE ROLE ' || graph_roles(elem);
    EXCEPTION
      WHEN role_exists THEN
        dbms_output.put_line('create_graph_roles: role already exists. continue');
      WHEN OTHERS THEN
        RAISE;
    END;
  END LOOP;
EXCEPTION
  when others then
    dbms_output.put_line('create_graph_roles: hit error ');
    raise;
END;
/
-- This procedure adds some grants to the graph roles.
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  EXECUTE IMMEDIATE 'GRANT PGX_SESSION_CREATE TO GRAPH_ADMINISTRATOR';
  EXECUTE IMMEDIATE 'GRANT PGX_SERVER_GET_INFO TO GRAPH_ADMINISTRATOR';
  EXECUTE IMMEDIATE 'GRANT PGX_SERVER_MANAGE TO GRAPH_ADMINISTRATOR';
  EXECUTE IMMEDIATE 'GRANT PGX_SESSION_CREATE TO GRAPH_DEVELOPER';
  EXECUTE IMMEDIATE 'GRANT PGX_SESSION_NEW_GRAPH TO GRAPH_DEVELOPER';
  EXECUTE IMMEDIATE 'GRANT PGX_SESSION_GET_PUBLISHED_GRAPH TO GRAPH_DEVELOPER';
  EXECUTE IMMEDIATE 'GRANT PGX_SESSION_MODIFY_MODEL TO GRAPH_DEVELOPER';
  EXECUTE IMMEDIATE 'GRANT PGX_SESSION_READ_MODEL TO GRAPH_DEVELOPER';
  EXECUTE IMMEDIATE 'GRANT PGX_SESSION_CREATE TO GRAPH_USER';
  EXECUTE IMMEDIATE 'GRANT PGX_SESSION_GET_PUBLISHED_GRAPH TO GRAPH_USER';
EXCEPTION
  when others then
    dbms_output.put_line('add_graph_roles_grants: hit error ');
    raise;
END;
/

Grant necessary roles to GRAPHUSER.

GRANT GRAPH_DEVELOPER, PGX_SESSION_ADD_PUBLISHED_GRAPH TO graphuser;

Install RPM packages

Download and install JDK 11 and Graph Server.

sudo yum install -y libgfortran
sudo rpm -i jdk-11.0.17_linux-x64_bin.rpm
sudo rpm -i oracle-graph-23.1.0.x86_64.rpm

Set the JDBC URL to connect the database.

vi /etc/oracle/graph/pgx.conf

...
"pgx_realm": {
  "implementation": "oracle.pg.identity.DatabaseRealm",
  "options": {
    "jdbc_url": "jdbc:oracle:thin:@myhost:1521/myservice",    <-- Here
    "token_expiration_seconds": 14400,
...

Disable TLS

The Graph Server, by default, allows only encrypted connections using Transport Layer Security (TLS). Turning off TLS is not recommended for production environments as it reduces the security of your connection. However, we will disable TLS for convenience, since this is a test environment.

Disable TLS in the Graph Server.

vi /etc/oracle/graph/server.conf

  "enable_tls": false,

Modify the WAR file for the graph visualization app. This step is not necessary in 23.3 and later.

yum install -y zip unzip

# GraphViz
WAR=$(find /opt/oracle/graph/graphviz -name '*.war')
TMP=$(mktemp -d)
cd $TMP
unzip $WAR WEB-INF/web.xml
sed -i 's|<secure>true</secure>|<secure>false</secure>|' WEB-INF/web.xml
sed -i 's|https://|http://|' WEB-INF/web.xml
sudo zip $WAR WEB-INF/web.xml
rm -r $TMP

# restart
sudo systemctl restart pgx

Load and publish the graph

Connect Graph Server using the Java Client.

$ opg4j -b http://localhost:7007 --user graphuser
password: 
For an introduction type: /help intro
Oracle Graph Server Shell 23.1.0
Variables instance, session, and analyst ready to use.
opg4j>

Load the graph from the database to Graph Server.

opg4j> var graph = session.readGraphByName("BANK_GRAPH", GraphSource.PG_VIEW)

Publish the graph.

opg4j> graph.publish()

Use the Graph Visualization App

Access http://<ip_address>:7007/ui/ from your browser.

Run a PGQL query.

Use a Python Client

Install Graph Client on a client machine where you can run Python.

$ pip install oracle-graph-client==23.1.0

Run the following script to connect to Graph Server. In the screenshot below, test.ipynb (Jupyter Notebook file) is created in Visual Studio Code, and the script is executed.

from opg4py import graph_server
from pypgx import setloglevel
from getpass import getpass

setloglevel("ROOT", "WARN")

base_url = "http://localhost:7008"
username = "graphuser"
password = getpass("Password: ")

instance = graph_server.get_instance(base_url, username, password)
session = instance.create_session("jupyter")
analyst = session.create_analyst()

print(session)

Access the graph BANK_GRAPH published in memory.

graph = session.get_graph("BANK_GRAPH")
graph

Get all edges and their source vertex and destination vertex using PGQL.

graph.query_pgql("""
SELECT a1.acc_id AS a1_acc_id, a2.acc_id AS a2_acc_id, t.amount
FROM MATCH (a1)-[t]->(a2)
ORDER BY a1_acc_id
""").print()

Try a pathfinding query.

graph.query_pgql("""
SELECT
  ELEMENT_NUMBER(e) AS order
, v1.acc_id AS a1_acc_id
, e.amount
, v2.acc_id AS a2_acc_id
FROM MATCH SHORTEST
  ((a1) (-[t:transferred_to]->(a) WHERE t.amount > 300)+ (a2))
  ONE ROW PER STEP (v1, e, v2)
WHERE a1.acc_id = 2 AND a2.acc_id = 5
  AND COUNT(a) = COUNT(DISTINCT a)
ORDER BY order ASC
""").print()

That’s all for now. You can also check out many other articles about Oracle Graph. Please try those tutorials using this environment!

Please learn more about Oracle Graph from:

Oracle Graph
Graph Database
Oracle Database
Oracle Cloud

--

--

Ryota Yamanaka
Oracle Developers

Written by Ryota Yamanaka

117 Followers

Ex-Oracle Database Consultant. tw @oraryotas

Help

Status

About

Careers

Press

Blog

Privacy

Terms

Text to speech

Teams