In the last post - How to setup and start to use Oracle Property Graph server, we talked about how to setup and start to use Oracle Graph Server and Client on a DBCS instance. But all the steps are completed manually, and the Graph Server and database instance reside in the same server (VM). From the best practice perspective, this is not a good approach. The property graph server and database instance should be deployed in separated servers to avoid interference to each other.

Furthermore, there should be an easier way to do the deployment. We are on the Cloud, don't we?

Prerequisite

The Graph Server will be deployed in front of a DBCS instance, so an existing DBCS instance is expected and we need to do some configuration in the database.

For details, please refer to the Configuration in Oracle Database section in last post.

Configuration in Oracle Database

Switch user to oracle and connect to Oracle Database as sys.

sudo su - oracle
sqlplus / as sysdba

[Figure: connect to DB]

PL/SQL Packages

Oracle Graph Server and Client will work with Oracle Database 12.2 onward. However, you must install the updated PL/SQL packages that are part of the Oracle Graph Server and Client download.

Download Oracle Graph Client for PL/SQL and unzip the file into a directory of your choice.
Login to the Oracle Database and execute following statements

-- Connect as SYSDBA
SQL> alter session set container=<YOUR_PDB_NAME>;
SQL> @opgremov.sql
SQL> @catopg.sql

Note: there are two directories in the unzipped directory, one for users with Oracle Database 18c or below, and one for users with Oracle Database 19c or above. As a database user with DBA privilges, follow the instructions in the README.md file in the appropriate directory (that matches your database version). This has to be done for every PDB you will use the graph feature in. The DBCS instance I created is 19c, so I should execute the scripts in 19c_and_above.

user & roles

Create database user demograph in PDB pdb1, grant role and tablespace accordingly.
All the tables will be created and loaded into this schema demograph.

CREATE USER demograph IDENTIFIED BY <PASSWORD>;
GRANT CONNECT, resource TO demograph;
GRANT ALTER SESSION,CREATE PROCEDURE,CREATE SESSION,CREATE TABLE, CREATE TYPE, CREATE VIEW to demograph;

CREATE ROLE graph_developer;
CREATE ROLE graph_administrator;

GRANT graph_developer TO demograph;
GRANT graph_administrator to demograph;

ALTER USER demograph QUOTA 10G ON USERS

GRANT UNLIMITED TABLESPACE TO demograph;

[Figure: prepare DB user demograph]

Create Online Retail tables

Please refer to the Create Online Retail tables section in the last post to create sample tables.

Deploy from Marketplace

Visit OCI Marketplace and input keyword graph, then we will see the tile of Oracle Property Graph Server & Client image.

[Figure: Marketplace]

[Figure: PGX in marketplace]

Please read the Overview and Usage Instructions before you click the Launch Stack button.

[Figure: Launch Stack]

Input a name of the stack. The compartment is selected same as the one when you launch the stack.

[Figrue: Stack info 1]

On the next page, we need to input more information.

In the Oracle Graph Server Compute Instance section,

RESOURCE NAME PREFIX will help us to identify the created resources.
ORACLE GRAPH SERVER COMPARTMENT allows us to choose which compartment to deploy the graph server.
ORACLE GRAPH SERVER SHAPE allows us to choose different shape of the server. That means different performance of the server. Check VM Shape page to get more information of the shapes.
SSH PUBLIC KEY is the key to connect to the compute instance in which the graph server is deployed.

[Figure: Stack info 2-1]

Scroll down the page, in the Instance Network section, we need to select compartment, VCN, subnet accordingly.

In the Graph Server Configuration section, JDBC URL FOR AUTHENTICATION is the JDBC connection string to the Oracle database we prepared in advance. Make sure the URL is accessible from the graph server. PGQL ENGINE FOR GRAPHVIZ we leave it as default.

[Figure: Stack info 2-2]

Click Next and have brief review of our settings, then just click Create to start the deployment.

[Figure: Stack review]

The deployment from marketplace image is performed by OCI Resource Manager. A job will be executed based on the information we configured just now.

[Figure: RM job]

Several minutes later, the resource manager job will be completed. We can visit the compute instance console to check the created graph server.

[Figure: Graph server compute instance]

Verify the deployment

PGX service

As we can see on the compute instance console, the graph server (PGX20201127) is running. Now, we can use our favorite terminal to connect to the server and check the PGX service status.

systemctl status pgx

[Figure: PGX service status]

Java Shell tool `opg-jshell`

Let's perform following command to connect to the server.

opg-jshell --base_url https://localhost:7007 --username demograph

[Figure: jshell connect]

Python client `opgpy`

Besides the Java Shell tool opgjshell, Oracle also provides a Python client called opgpy. If you prefer Python syntax, you can try this one.

opgpy --base_url https://localhost:7007 --user demograph

[Figure: Python client]

Graph in PGX

In this section, we will use the Python client opgpy to connect to the property graph server and interact with the database.

Create graph

stmt_create = """
CREATE PROPERTY GRAPH "or"
    VERTEX TABLES (
        CUSTOMERS KEY(CUSTOMER_ID) PROPERTIES ARE ALL COLUMNS,
        PRODUCTS  KEY(STOCK_CODE)  PROPERTIES ARE ALL COLUMNS,
        PURCHASES_DISTINCT KEY(PURCHASE_ID) PROPERTIES ALL COLUMNS
    )
    EDGE TABLES (
        PURCHASES_DISTINCT as has_purchased
            KEY (PURCHASE_ID)
            SOURCE KEY(CUSTOMER_ID) REFERENCES CUSTOMERS
            DESTINATION KEY(STOCK_CODE) REFERENCES PRODUCTS
            LABEL "has_purchased"
        , PURCHASES_DISTINCT as purchased_by
            KEY (PURCHASE_ID)
            SOURCE KEY(STOCK_CODE) REFERENCES PRODUCTS
            DESTINATION KEY(CUSTOMER_ID) REFERENCES CUSTOMERS
            LABEL "purchased_by"
    )
"""
session.prepare_pgql(stmt_create).execute()
graph_or = session.get_graph("or")
graph_or

[Figure: create graph or]

Query graph

graph_or.query_pgql("SELECT ID(c), ID(p), p.description FROM or MATCH (c)-[has_purchased]->(p) WHERE c.CUSTOMER_ID = 'cust_12353'").print();

[Figure: query graph or]

Destroy graph

graph_or.destroy()
session.get_graph("or")

[Figure: destroy graph]

Conclusion

As we can see, by using the marketplace image, we just need to input some basic information and click some buttons, then we will get a fully workable Property Graph Server. If we have prepared properly, it may only cost us less than 5 minutes to make it done. Super easy! Isn't it?