A.10.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.
Start the shell
cd <client-install-dir>
./bin/opg4j --no_connect
The --no_connect
option indicates that you are not connecting
to the graph server (PGX). You will only be connecting to the database in this example.
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
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
opg4j> conn.setAutoCommit(false)
opg4j> var pgql = PgqlConnection.getConnection(conn)
pgql ==> oracle.pg.rdbms.pgql.PgqlConnection@6fb3d3bb
Write and execute the graph creation statement
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 (employee_id)
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
)
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
// 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 |
+---------------+