Getting Started with Oracle Graph Server and Client

In this article, we will explore a simple use case to get started with Oracle Graph Server and Client.

Prerequisites

This article assumes that you already have an Oracle Database and have Graph Server configured for that database. If you have not done so, you can follow these steps to deploy the Graph Server/Client Marketplace image with the Autonomous Database on Oracle Cloud, or follow our official documentation to deploy Graph Server/Client.

We also assume that you have created a database user with the necessary permissions for graph. If you do not, you can follow these steps if you are using an Autonomous Database, or follow these steps if you are using a self-managed Oracle database.

You have SQLcl and necessary packages installed on your Graph Server VM. The Oracle Marketplace image has this installed by default. If you have a self-managed database, you can install this PL/SQL package to your database; go here to download SQLcl, and here to download the PGQL plugin for SQLcl.

Create Database Tables

1. Connect to your Oracle Database with your Graph-enabled user. For instructions on connecting to an Oracle Database, see this document. To connect to an Autonomous Database, see this document.
2. Create the following tables:

create table users_table(
   user_id         NUMBER(4),
   first_name      VARCHAR2(50),
   last_name       VARCHAR2(50),
   gender          VARCHAR2(50),
   date_of_birth   TIMESTAMP(3),
   phone_number    VARCHAR2(50),
   infected        VARCHAR2(50),
   date_diagnosis  TIMESTAMP(3),
   constraint pk_uid primary key (user_id)
);
create table contacts(
   contact_id      NUMBER(4),
   reporting_user  NUMBER(4),
   contact_user    NUMBER(4),
   contact_start   TIMESTAMP(3),
   contact_end     TIMESTAMP(3),
   infectious_start TIMESTAMP(3),
   infectious_end  TIMESTAMP(3),
   constraint pk_cid primary key (contact_id),
   constraint FK_rep_user
      FOREIGN key (reporting_user) references users_table(user_id),
   constraint FK_con_user
      FOREIGN key (contact_user) references users_table(user_id)
);

The users_table will represent all vertices in this graph. The contacts table will represent all edges in this graph.

Load Data

1. Download this zip file, which contains csv files for each table.
2. Load users.csv into users_table and contacts.csv into contacts. This can be done through SQL Developer, with an example shown here.

Create Graph

1. SSH to your graph server instance.
2. Log in to SQLcl using your graph user. If you are using the marketplace image with Autonomous, that will look like the following:

$ sql -oci
$ Username? (''?) graph_user@<ADB_connection_string>
$ Password? <graph_user_password>

3. Enable PGQL mode.

pgql auto on;

4. Create Property Graph.

PGQL> CREATE PROPERTY GRAPH Contact_Graph
   VERTEX TABLES ( 
      graph_user.users_table KEY ( user_id ) 
      PROPERTIES ( 
         date_diagnosis, 
         date_of_birth, 
         first_name,
         gender,
         infected,
         last_name,
         phone_number,
         user_id 
      )) 
   EDGE TABLES ( 
      graph_user.contacts SOURCE KEY ( reporting_user ) 
      REFERENCES users_table DESTINATION KEY ( contact_user ) 
      REFERENCES users_table PROPERTIES ( 
         contact_end,
         contact_id,
         contact_start,
         contact_user,
         infectious_end,
         infectious_start,
         reporting_user
 ));

4. Disconnect from SQLcl.

Log in Using the Graph Visualization Tool

At this point, we should be able to see that the graph has been created in the database. We will be able to run PGQL queries using SQLcl or the Graph visualization tool. Note that when we load the graph into memory we can additionally run graph analytics algorithms, and gain even more insight into our data.

1. In a browser, navigate to your Oracle Graph visualization tool. This should look like:

https://<IP of graph viz>:7007/ui/

2. Select the Advanced Options drop-down, and select the Database tab.

3. In the Database URL box, enter your database JDBC url.

4. Click Submit.

Query your Graph

1. In Graph Visualization, select the drop-down box on the bottom left and select the graph that we just made.

2. Query and visualize 100 elements (nodes and edges) of CONTACT_GRAPH. This query shows the result of 100 elements where a source vertex has a transaction to a destination vertex. In other words, this shows 100 times people came into contact with one another.

select * 
from match (s)-[t]->(d) on contact_graph 
limit 100

3. Query and visualize based on if the contacter was infected.

select * from
match (s) - [t] -> (d) on contact_graph 
where s.infected='yes'

4. Query and visualize assuming a person could be infectious two weeks before or after diagnosis.

select * from 
match (s) - [t] -> (d) on contact_graph 
where s.infected = 'yes' and (s.date_diagnosis >=  t.infectious_start and s.date_diagnosis <=  t.infectious_end)

5. We can drill even deeper to see if there has been anyone infected in a group, who then exposed more people.

select * from
match (s) - [t] -> (d) - [f] -> (v) on contact_graph 
where s.infected = 'yes' and (s.date_diagnosis >=  t.infectious_start and s.date_diagnosis <=  t.infectious_end)

6. But, we also want to see if the people contacted were exposed within the infectious range.

select * from
match (s) - [t] -> (d) - [f] -> (v) on contact_graph
where s.infected = 'yes' and (s.date_diagnosis >=  t.infectious_start and s.date_diagnosis <=  t.infectious_end)
and d.infected = 'yes' and (d.date_diagnosis >=  f.infectious_start and d.date_diagnosis <=  f.infectious_end)

Example Use Case

Let’s say that Judy (user ID 1) hears that Janet (user ID 73) was infected and spread the virus to several others. This person is concerned for their health, and asks “Was I two or less contact points away from Janet at any point?” We can help her by running a Match All query with Janet’s and Judy’s user ID.

1. Run the following query. Here we are querying for all paths, with two or less edges, between Judy and Janet.

SELECT p1.user_id AS start, p2.user_id as end_
   FROM MATCH All (p1) -[e]->{,2} (p2)
   WHERE p1.user_id =1 AND p2.user_id =73

2. Click on the table mode option in the Graph visualization tool to see that there are thirty two paths, with two or less edges, between Judy and Janet.

3. We can confirm that Judy and Janet were never directly in contact with the following query, which should return an empty result.

SELECT *
   FROM MATCH (p1) -[e]-> (p2)
   WHERE p1.user_id =73 AND p2.user_id =1

4. We can then query to see who Janet came in contact with, while she was infected, and if those people were infected and contagious while in contact with Judy.

SELECT *
   FROM MATCH (p1) -[e]-> (i) -[f]-> (p2)
   WHERE p1.user_id =73 AND p2.user_id =1
and
   i.infected='yes'
and
   ((e.infectious_start <= e.contact_start and e.contact_start <= e.infectious_end)
   or (e.contact_start <= e.infectious_start and e.contact_end <= e.infectious_end)
   or (e.contact_start <= e.infectious_start and e.infectious_end <= e.contact_end))
and
   ((f.infectious_start <= f.contact_start and f.contact_start <= f.infectious_end)
   or (f.contact_start <= f.infectious_start and f.contact_end <= f.infectious_end)
   or (f.contact_start <= f.infectious_start and f.infectious_end <= f.contact_end))

With this information, we can answer Judy’s question by telling her she was two contact points away from Janet thirty two times. However, the two of them were never in direct contact, and only four of those contact points were during the infectious range for Janet and the middle point of contact.

You can then continue to query with a similar syntax, and explore the graph! You can find the official documentation for PGQL here.

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)