2.2.1.1 Create and Query a Graph in the Database

In this section, you will use the Oracle Graph client to create a graph from relational tables and store it in the property graph schema in the database.

Major tasks for this tutorial:

• Start the shell
• Open a JDBC database connection
• Create a PGQL connection
• Write and execute the graph creation statement
• Run a few PGQL queries

Start the shell

On the system where Oracle Graph client is installed, start the shell by as follows:

cd <client-install-dir>
./bin/opg4j --noconnect 

The --noconnect option indicates that you are not connecting to the graph server (PGX). You will only be connecting to the database in this example.

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 CLIs for details about the shell.

Open a JDBC database connection

Inside the shell prompt, use the standard JDBC Java API to obtain a database connection object. For example:

opg4j> var jdbcUrl = "<jdbc-url>" // for example: jdbc:oracle:thin:@myhost:1521/myservice
opg4j> var user = "<db-user>" // for example: hr
opg4j> var pass = "<db-pass>"
opg4j> var conn = DriverManager.getConnection(jdbcUrl, user, pass)
conn ==> oracle.jdbc.driver.T4CConnection@57e6cb01

Connecting to an Autonomous Database works the same way: provide a JDBC URL that points to the local wallet. See Using Oracle Graph with the Autonomous Database for an example.

Create a PGQL connection

Convert the JDBC connection into a PGQL connection object. For example:

opg4j> conn.setAutoCommit(false)
opg4j> var pgql = PgqlConnection.getConnection(conn)
pgql ==> oracle.pg.rdbms.pgql.PgqlConnection@6fb3d3bb

Write and execute the graph creation statement

Using a text editor, write a CREATE PROPERTY GRAPH statement that describes how the HR sample data should be converted into a graph. Save this file as create.pgql at a location of your choice. For example:

CREATE PROPERTY GRAPH hr
  VERTEX TABLES (
    employees LABEL employee
      PROPERTIES ARE ALL COLUMNS EXCEPT ( job_id, manager_id, department_id ),
    departments LABEL department
      PROPERTIES ( department_id, department_name ),
    jobs LABEL job
      PROPERTIES ARE ALL COLUMNS,
    job_history
      PROPERTIES ( start_date, end_date ),
    locations LABEL location
      PROPERTIES ARE ALL COLUMNS EXCEPT ( country_id ),
    countries LABEL country
      PROPERTIES ARE ALL COLUMNS EXCEPT ( region_id ),
    regions LABEL region
  )
  EDGE TABLES (
    employees AS works_for
      SOURCE employees
      DESTINATION KEY ( manager_id ) REFERENCES employees
      NO PROPERTIES,
    employees AS works_at
      SOURCE employees
      DESTINATION departments
      NO PROPERTIES,
    employees AS works_as
      SOURCE employees
      DESTINATION jobs
      NO PROPERTIES,
    departments AS managed_by
      SOURCE departments
      DESTINATION employees
      NO PROPERTIES,
    job_history AS for_employee
      SOURCE job_history
      DESTINATION employees
      LABEL for
      NO PROPERTIES,
    job_history AS for_department
      SOURCE job_history
      DESTINATION departments
      LABEL for
      NO PROPERTIES,
    job_history AS for_job
      SOURCE job_history
      DESTINATION jobs
      LABEL for
      NO PROPERTIES,
    departments AS department_located_in
      SOURCE departments
      DESTINATION locations
      LABEL located_in
      NO PROPERTIES,
    locations AS location_located_in
      SOURCE locations
      DESTINATION countries
      LABEL located_in
      NO PROPERTIES,
    countries AS country_located_in
      SOURCE countries
      DESTINATION regions
      LABEL located_in
      NO PROPERTIES
  )

Then, back in your graph shell, execute the CREATE PROPERTY GRAPH statement by sending it to your PGQL connection. Replace <path> with the path to the directory containing the create.pgql file:

opg4j> pgql.prepareStatement(Files.readString(Paths.get("<path>/create.pgql"))).execute()
$16 ==> false

Run a few PGQL queries

Now that you have a graph named hr, you can use PGQL to run a few queries against it directly on the database. For example:

// define a little helper function that executes the query, prints the results and properly closes the statement
opg4j> Consumer<String> query = q -> { try(var s = pgql.prepareStatement(q)) { s.execute(); s.getResultSet().print(); } catch(Exception e) { throw new RuntimeException(e); } }
query ==> $Lambda$605/0x0000000100ae6440@6c9e7af2
 
// print the number of vertices in the graph
opg4j> query.accept("SELECT COUNT(v) FROM MATCH (v) ON hr")
+----------+
| COUNT(v) |
+----------+
| 215      |
+----------+
 
// print the number of edges in the graph
opg4j> query.accept("SELECT COUNT(e) FROM MATCH ()-[e]->() ON hr")
+----------+
| count(e) |
+----------+
| 433      |
+----------+
 
// find the highest earning managers
opg4j> query.accept("SELECT DISTINCT m.FIRST_NAME, m.LAST_NAME, m.SALARY FROM MATCH (v:EMPLOYEE)-[:WORKS_FOR]->(m:EMPLOYEE) ON hr ORDER BY m.SALARY DESC")
+---------------------------------------+
| m.FIRST_NAME | m.LAST_NAME | m.SALARY |
+---------------------------------------+
| Steven       | King        | 24000.0  |
| Lex          | De Haan     | 17000.0  |
| Neena        | Kochhar     | 17000.0  |
| John         | Russell     | 14000.0  |
| Karen        | Partners    | 13500.0  |
| Michael      | Hartstein   | 13000.0  |
| Alberto      | Errazuriz   | 12000.0  |
| Shelley      | Higgins     | 12000.0  |
| Nancy        | Greenberg   | 12000.0  |
| Den          | Raphaely    | 11000.0  |
| Gerald       | Cambrault   | 11000.0  |
| Eleni        | Zlotkey     | 10500.0  |
| Alexander    | Hunold      | 9000.0   |
| Adam         | Fripp       | 8200.0   |
| Matthew      | Weiss       | 8000.0   |
| Payam        | Kaufling    | 7900.0   |
| Shanta       | Vollman     | 6500.0   |
| Kevin        | Mourgos     | 5800.0   |
+---------------------------------------+
 
// find the average salary of accountants in the Americas
opg4j> query.accept("SELECT AVG(e.SALARY) FROM MATCH (e:EMPLOYEE) -[h:WORKS_AT]-> (d:DEPARTMENT) -[:LOCATED_IN]-> (:LOCATION) -[:LOCATED_IN]-> (:COUNTRY) -[:LOCATED_IN]-> (r:REGION) ON hr WHERE r.REGION_NAME = 'Americas' AND d.DEPARTMENT_NAME = 'Accounting'")
+---------------+
| avg(e.SALARY) |
+---------------+
| 14500.0       |
+---------------+