1. Graph Developer's Guide for Property Graph
  2. Getting Started with Oracle Property Graphs
  3. Quick Starts for Using Oracle Property Graph
  4. Quick Start: Interactively Analyze Graph Data
  5. Quick Start: Create, Query, and Analyze a Graph in In-Memory Graph Server (PGX)

2.1.2 Quick Start: Create, Query, and Analyze a Graph in In-Memory Graph Server (PGX)

This tutorial shows how you can quickly get started using property graph data when using the in-memory graph server (PGX).

This is for use cases where the graph is available as long as the in-memory graph server (PGX) session is active. The graph is not persisted in the database.

  • Create a graph in the in-memory graph server (PGX), directly from existing relational data
  • Query this graph using PGQL in the in-memory graph server (PGX)
  • Run graph algorithms in the in-memory graph server (PGX) on this graph and display results
Prerequisites for the following quickstart are:

  • An installation of Oracle Graph server (this is PGX, the in-memory graph server).

    See Installing Oracle Graph Server for information to download Oracle Graph Server.

  • An installation of Oracle Graph client.

    See Installing the Java Client for information to download Oracle Graph Client.

    You will authenticate yourself as the database user to the in-memory graph server, and these database credentials are used to access the database tables and create a graph.

  • Java 11
    • The in-memory graph server can work with Java 8 or Java 11.
    • The JShell client used in this example requires Java 11.

    For Java downloads, see https://www.oracle.com/technetwork/java/javase/overview/index.html.

Major tasks for this tutorial:

Set up the example data

This example uses the HR (human resources) sample dataset.

Note that the database schema storing the graph must have the privileges listed in Required Privileges for Database Users.

Start the shell

On the system where Oracle Graph Client is installed, start the shell as follows. This is an example of starting a shell in remote mode and connecting to the in-memory graph server (PGX):

./bin/opg4j --base_url https://<graph server host>:7007 --username <graphuser>

<graphuser> is the database user you will use to for the PGX server authentication. You will be prompted for the database password.

Note:

For demo purposes only, if you have set enable_tls to false in the /etc/oracle/graph/server.conf file you can use an http instead of https connection. 
./bin/opg4j --base_url http://<graph server host>:7007 --username <graphuser>

This starts the shell and makes a connection to the graph server.

Note that, JAVA_HOME should be set to Java 11 before you start the shell. For example:
export JAVA_HOME=/usr/lib/jvm/java-11-oracle

See Interactive Graph Shell for details about the shell.

Write and execute the graph creation statement

Create a graph with employees, departments, and “employee works at department”, by executing a CREATE PROPERTY GRAPH statement. The following statement creates a graph in the in-memory graph server (PGX):
opg4j> String statement =
      "CREATE PROPERTY GRAPH hr_simplified "
    + "  VERTEX TABLES ( "
    + "    hr.employees LABEL employee "
    + "      PROPERTIES ARE ALL COLUMNS EXCEPT ( job_id, manager_id, department_id ), "
    + "    hr.departments LABEL department "
    + "      PROPERTIES ( department_id, department_name ) "
    + "  ) "
    + "  EDGE TABLES ( "
    + "    hr.employees AS works_at "
    + "      SOURCE KEY ( employee_id ) REFERENCES employees "
    + "      DESTINATION departments "
    + "      PROPERTIES ( employee_id ) "
    + "  )";
opg-jshell> session.executePgql(statement);
To get a handle to the graph, execute:
opg4j> PgxGraph g = session.getGraph("HR_SIMPLIFIED");

Run a few PGQL queries

You can use this handle to run PGQL queries on this graph. For example, to find the department that “Nandita Sarchand” works for, execute:
opg4j> String query =
    "SELECT dep.department_name "
  + "FROM MATCH (emp:Employee) -[:works_at]-> (dep:Department) "
  + "WHERE emp.first_name = 'Nandita' AND emp.last_name = 'Sarchand' "
  + "ORDER BY 1";
opg4j> PgqlResultSet resultSet = g.queryPgql(query);
opg4j> resultSet.print();
+-----------------+
| department_name |
+-----------------+
| Shipping        |
+-----------------+
To get an overview of the types of vertices and their frequencies, execute:
opg4j> String query =
      "SELECT label(n), COUNT(*) "
    + "FROM MATCH (n) "
    + "GROUP BY label(n) "
    + "ORDER BY COUNT(*) DESC";
opg4j> PgqlResultSet resultSet = g.queryPgql(query);
opg4j> resultSet.print();

+-----------------------+
| label(n)   | COUNT(*) |
+-----------------------+
| EMPLOYEE   | 107      |
| DEPARTMENT | 27       |
+-----------------------+

To get an overview of the types of edges and their frequencies, execute:

opg4j> String query =
    "SELECT label(n) AS srcLbl, label(e) AS edgeLbl, label(m) AS dstLbl, COUNT(*) "
  + "FROM MATCH (n) -[e]-> (m) "
  + "GROUP BY srcLbl, edgeLbl, dstLbl "
  + "ORDER BY COUNT(*) DESC";
opg4j> PgqlResultSet resultSet = g.queryPgql(query);
opg4j> resultSet.print();

+---------------------------------------------+
| srcLbl   | edgeLbl  | dstLbl     | COUNT(*) |
+---------------------------------------------+
| EMPLOYEE | WORKS_AT | DEPARTMENT | 106      |
+---------------------------------------------+

Execute algorithms and query the algorithm results

Now that you have the graph in memory, you can run each built-in algorithms using a single API invocation. For example, for pagerank:
opg4j> analyst.pagerank(g)
$31==> VertexProperty[name=pagerank,type=double,graph=hr]
As you can see from the preceding outputs, each algorithm created a new vertex property on the graph holding the output of the algorithm. To print the most important people in the graph (according to pagerank), you can run the following query:
opg4j> session.queryPgql("select m.FIRST_NAME, m.LAST_NAME, m.pagerank from HR_SIMPLIFIED match (m:EMPLOYEE) where m.FIRST_NAME = ‘Nandita’ “).print().close()
+---------------------------------------------------+
| m.FIRST_NAME | m.LAST_NAME | m.pagerank           |
+---------------------------------------------------+
| Nandita      | Sarchand    | 0.001119402985074627 |
+---------------------------------------------------+

In the following example, we order departments by their pagerank value. Departments with higher pagerank values have more employees. 

opg4j> session.queryPgql("select m.DEPARTMENT_NAME, m.pagerank from HR_SIMPLIFIED match (m:DEPARTMENT) order by m.pagerank ").print().close();


+----------------------------------------------+
| m.DEPARTMENT_NAME | m.pagerank |
+----------------------------------------------+
| Manufacturing | 0.001119402985074627 |
| Construction | 0.001119402985074627 |
| Contracting | 0.001119402985074627 |
| Operations | 0.001119402985074627 |
| IT Support | 0.001119402985074627 |
| NOC | 0.001119402985074627 |
| IT Helpdesk | 0.001119402985074627 |
| Government Sales | 0.001119402985074627 |
| Retail Sales | 0.001119402985074627 |
| Recruiting | 0.001119402985074627 |
| Payroll | 0.001119402985074627 |
| Treasury | 0.001119402985074627 |
| Corporate Tax | 0.001119402985074627 |
| Control And Credit | 0.001119402985074627 |
| Shareholder Services | 0.001119402985074627 |
| Benefits | 0.001119402985074627 |
| Human Resources | 0.0020708955223880596 |
| Administration | 0.0020708955223880596 |
| Public Relations | 0.0020708955223880596 |
| Marketing | 0.003022388059701493 |
| Accounting | 0.003022388059701493 |
| Executive | 0.003973880597014925 |
| IT | 0.005876865671641792 |
| Purchasing | 0.006828358208955224 |
| Finance | 0.006828358208955224 |
| Sales | 0.03347014925373134 |
| Shipping | 0.043936567164179076 |
+----------------------------------------------+

Share the Graph with Other Sessions

After you load the graph into the server, you can use the publish() API to make the graph visible to other sessions, such as the graph visualization session. For example:
opg4j> graph.publish(VertexProperty.ALL, EdgeProperty.ALL)

The published graph will include any new properties you add to the graph by calling functions, such as pagerank.

Ensure that the logged-in user has the privilege to publish graphs. You can do this by adding the privilege PGX_SESSION_ADD_PUBLISHED_GRAPH to the GRAPH_DEVELOPER role as explained in Adding Permissions to Publish the Graph. We had given the GRAPH_DEVELOPER role to the database user in Installing PL/SQL Packages in Oracle Database.

You can use the Graph Visualization Application by navigating to <my-server-name>:7007/ui/ in your browser.

You can connect to a particular client session by providing the session ID when you log into the Graph Visualization Application. You will then be able to visualize all graphs in the session, even if they have not been published. 

opg4j> session
session ==> PgxSession[ID=5adf83ab-31b1-4a0e-8c08-d6a95ba63ee0,source=pgxShell]

The session id is 5adf83ab-31b1-4a0e-8c08-d6a95ba63ee0.

Note:

You must create a server certificate to connect to the in-memory graph server (PGX) from the Graph Visualization Application. See Setting Up Transport Layer Security for more details.

Parent topic: Quick Start: Interactively Analyze Graph Data