A.10.2 Quick Start: Create, Query, and Analyze a Graph in Graph Server (PGX)
This tutorial shows how you can quickly get started using property graph data when using the graph server (PGX).
This is for use cases where the graph is available as long as the graph server (PGX) session is active. The graph is not persisted in the database.
- Create a graph in the graph server (PGX), directly from existing relational data
- Query this graph using PGQL in the graph server (PGX)
- Run graph algorithms in the graph server (PGX) on this graph and display results
-
An installation of Oracle 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 From the Graph Server and Client Downloads for information to download Oracle Graph Client.
You will authenticate yourself as the database user to the graph server, and these database credentials are used to access the database tables and create a graph.
- Java 11
- The 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.
Set up the example data
- For instructions how to import that data into a user managed database, see: https://github.com/oracle/db-sample-schemas
- If you are using Autonomous Database, see: https://www.thatjeffsmith.com/archive/2019/07/creating-hr-in-oracle-autonomous-database-w-sql-developer-web/
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 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 setenable_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.
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.
Write and execute the graph creation statement
employee works at
department
”, by executing a CREATE PROPERTY GRAPH
statement. The
following statement creates a graph in the 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 (employee_id) "
+ " DESTINATION departments "
+ " PROPERTIES ( employee_id ) "
+ " )"
opg-jshell> session.executePgql(statement)
opg4j> var g = session.getGraph("HR_SIMPLIFIED")
Run a few PGQL queries
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> var resultSet = g.queryPgql(query)
opg4j> resultSet.print()
+-----------------+
| department_name |
+-----------------+
| Shipping |
+-----------------+
opg4j> String query =
"SELECT label(n), COUNT(*) "
+ "FROM MATCH (n) "
+ "GROUP BY label(n) "
+ "ORDER BY COUNT(*) DESC"
opg4j> var 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> var resultSet = g.queryPgql(query)
opg4j> resultSet.print()
+---------------------------------------------+
| srcLbl | edgeLbl | dstLbl | COUNT(*) |
+---------------------------------------------+
| EMPLOYEE | WORKS_AT | DEPARTMENT | 106 |
+---------------------------------------------+
Execute algorithms and query the algorithm results
pagerank
:opg4j> analyst.pagerank(g)
$31==> VertexProperty[name=pagerank,type=double,graph=hr]
opg4j> session.queryPgql("SELECT m.FIRST_NAME, m.LAST_NAME, m.pagerank FROM MATCH (m:EMPLOYEE) "
...> + "ON hr_simplified 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 MATCH (m:DEPARTMENT) ON hr_simplified ORDER BY m.pagerank").print().close()
+----------------------------------------------+
| DEPARTMENT_NAME | 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
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 graph server (PGX) from the Graph Visualization Application. See Setting Up Transport Layer Security for more details.