1 Property Graph Support Overview
- About the Property Graph Feature of Oracle Database
The Property Graph feature delivers advanced graph query and analytics capabilities in Oracle Database. - Property Graph Prerequisites
The requirements for using the Property Graph feature of Oracle Database are the following. - Property Graph Features
Graphs manage networks of linked data as vertices, edges, and properties of the vertices and edges. - Security Best Practices with Graph Data
Several security-related best practices apply when working with graph data. - Interactive Graph Shell
Both the Oracle Graph server and client packages contain an interactive command-line application for interacting with all the Java APIs of the product, locally or on remote computers. - Storing Graphs in Oracle Database and Loading Graphs into Memory
You can work with graphs in two-tier mode (graph client connects directly to Oracle Database), or three-tier mode (graph client connects to the graph server (PGX) on the middle-tier, which then connects to Oracle Database). - Using Oracle Graph with the Autonomous Database
Oracle Graph Server and Client supports the family of Oracle Autonomous Database. - Migrating Property Graph Applications from Before Release 21c
If you are migrating from a previous version of Oracle Spatial and Graph to Release 21c, you may need to make some changes to existing property graph-related applications. - Quick Start: Interactively Analyze Graph Data
This tutorial shows how you can quickly get started using property graph data. - Managing Property Graphs With Python Client
This section describes how to install Python Client and how to use the Python Client to work with Property Graphs.
1.1 About the Property Graph Feature of Oracle Database
The Property Graph feature delivers advanced graph query and analytics capabilities in Oracle Database.
This feature supports graph operations, indexing, queries, search, and in-memory analytics.
Parent topic: Property Graph Support Overview
1.2 Property Graph Prerequisites
The requirements for using the Property Graph feature of Oracle Database are the following.
-
Oracle Graph Server and Client must be installed, as described later in this topic.
This is required to use the Property Graph feature of Oracle Database and is supported for Oracle Database 12.2 and higher.
-
AL16UTF16
(instead ofUTF8
) must be specified as the NLS_NCHAR_CHARACTERSET.AL32UTF8 (UTF8)
should be the default character set, butAL16UTF16
must be the NLS_NCHAR_CHARACTERSET.
Subtopics:
- Graph Server Installation
- Set up Transport Layer Security
- Graph Server Upgrade
- Graph Server Uninstallation
- Graph Client Installation
- Database Compatibility and Restrictions
Graph Server Installation
Starting with Release 20.1, Graph Server and Client will be available as a separate downloadable package. The libraries will not be available in $ORACLE_HOME.
For installing the Graph server, the prerequisites are:
- Oracle Linux 6 or 7 x64 or a similar Linux distribution such as RedHat
- Oracle JDK 8 or JDK 11
Graph server can run standalone, or in Oracle WebLogic Server, or Apache Tomcat. For instructions to deploy Graph server in Oracle WebLogic Server or Apache Tomcat, see:
- Download Oracle Graph Server and Client from Oracle Software Delivery Cloud.
- As root, install the RPM file using the rpm command line
utility:
rpm -i oracle-graph-<version>.rpm
Where
<version>
reflects the version that you downloaded. (For example:oracle-graph-20.4.0.0.0.x86_64.rpm
)The
.rpm
file is the graph server. - As root, add operating system users allowed to use the server
installation to the operating system group
oraclegraph
. For example:usermod -a -G oraclegraph <graphuser>
This adds the specified graph user to the group
oraclegraph
.Note that
<graphuser>
must log out and log in again for this to take effect. - As
<graphuser>
, configure the server by modifying the files under /etc/oracle/graph.Note:
For demonstration purposes only, you can edit
/etc/oracle/graph/server.conf
to changeenable_tls
tofalse
. This will allow you to start the server as described in the next step. This is not recommended for production. In a secure configuration,enable_tls
should be set totrue
. - Ensure that authentication is enabled for database users that will connect to the graph server, as explained in PGX User Authentication and Authorization.
- As
<graphuser>
, start the PGX server by executing/opt/oracle/graph/pgx/bin/start-server
.
The PGX server is now ready to accept requests. Log files can be found in /var/log/oracle/graph
.
Additional installation operations are required for specific use cases, such as:
- Interactively analyze property graphs locally using the Java Shell tool (JShell).
- Deploy the graph server as a web application with Oracle WebLogic Server (see Deploying to Oracle WebLogic Server).
- Deploy GraphViz in Oracle WebLogic Server (see Deploying Graph Visualization Application in Oracle WebLogic Server).
- Deploy the graph server as a web application with Apache Tomcat (see Deploying to Apache Tomcat).
Set up Transport Layer Security
The graph server, by default, allows only encrypted connections using Transport Layer Security (TLS). TLS requires the server to present a server certificate to the client and the client must be configured to trust the issuer of that certificate.
Configure the graph server
Note:
If you deploy the graph server into your web server using the web applications download package, then this section does not apply. Please refer to the manual of your web server for instructions on how to configure TLS.Edit the file at /etc/oracle/graph/server.conf
, and
specify the paths to the server certificate and the server's private key in PEM
format. For example:
{
"port": 7007,
"enable_tls": true,
"server_private_key": "/etc/oracle/graph/server_key.pem",
"server_cert": "/etc/oracle/graph/server_certificate.pem",
"enable_client_authentication": false,
"working_dir": "/opt/oracle/graph/pgx/tmp_data"
}
Restart the server.
Note:
- You should use a certificate issued by a certificate authority (CA) which is trusted by your organization. If you do not have a CA certificate, you can temporarily create a self-signed certificate and get started.
- Always use a valid certificate trusted by your organization. We do not recommend the usage of self-signed certificates for production environments.
Generate self-signed certificates
The following example shows how to generate a self-signed certificate:
cd /etc/oracle/graph
openssl req -new -newkey rsa:2048 -days 365 -nodes -x509 -subj "/C=US/ST=MyState/L=MyTown/O=MyOrganization/CN=ROOT" -keyout ca_key.pem -out ca_certificate.pem
openssl genrsa -out server_key_traditional.pem 2048
openssl pkcs8 -topk8 -in server_key_traditional.pem -inform pem -out server_key.pem -outform pem -nocrypt
openssl req -new -subj "/C=US/ST=MyState/L=MyTown/O=MyOrganization/CN=localhost" -key server_key.pem -out server.csr
chmod 600 server_key.pem
openssl x509 -req -CA ca_certificate.pem -CAkey ca_key.pem -in server.csr -out server_certificate.pem -days 365 -CAcreateserial
Note:
- The certificate mentioned in the above example will only work
for the host
localhost
. If you have a different domain, you must replacelocalhost
with your domain name. - The above self-signed certificate is valid only for 365 days.
Configuring the client to trust the self-signed certificate
To configure a Java client (including jshell) to trust the self-signed certificate, import the root certificate to your Java installation local trust store.
- Java 8 (make sure
JAVA_HOME
is set):keytool -import -trustcacerts -keystore $JAVA_HOME/jre/lib/security/cacerts -storepass changeit -alias pgx -import -file ca_certificate.pem -noprompt
- Java 11 (make sure
JAVA11_HOME
is set):keytool -import -trustcacerts -keystore $JAVA11_HOME/lib/security/cacerts -storepass changeit -alias pgx -import -file ca_certificate.pem -noprompt
To configure a Python client to trust the self-signed certificate, you
need to additionally set the SSL_CERT_FILE
environment variable
pointing to the root certificate, before you start the Python application.
Consider the following example:
export SSL_CERT_FILE=/etc/oracle/graph/ca_certificate.pem
When you configure a browser to trust the self-signed certificate, while using the Graph Visualization application, one of the following messages might appear:
Your connection is not private
Your connection is not secure
Click the Continue or Accept button to proceed.
Prepare the Graph Server for Database Authentication
Connections to the graph server are authenticated by authenticating them as database
users with the role GRAPH_DEVELOPER
.
$ cd /etc/oracle/graph
$ vi pgx.conf
jdbc_url
line in the realm options.
Replace the text with the JDBC URL
for your database. For
example:...
"pgx_realm": {
"implementation": "oracle.pg.identity.DatabaseRealm",
"options": {
"jdbc_url": "jdbc:oracle:thin:@myhost:1521/myservice",
"token_expiration_seconds": 14400,
...
...
"pgx_realm": {
"implementation": "oracle.pg.identity.DatabaseRealm",
"options": {
"jdbc_url": "jdbc:oracle:thin:@myhost:1521/myservice",
"token_expiration_seconds": 14400,
...
where
/etc/oracle/graph/wallet
is an example path to the unzipped
wallet file that you downloaded from your Autonomous Database service console, and
my_identifier_low
is one of the connect identifiers specified
in /etc/oracle/graph/wallet/tnsnames.ora
.
Add Permissions to Publish the Graph
PGX_SESSION_ADD_PUBLISHED_GRAPH
in the
/etc/oracle/graph/pgx.conf
file:
...
"authorization": [{
"pgx_role": "GRAPH_DEVELOPER",
"pgx_permissions": [{
"grant": "PGX_SESSION_ADD_PUBLISHED_GRAPH"
},
...
Graph Server Upgrade
To upgrade the graph server, make sure the graph server is shut down, then execute the following command with the newer RPM file as an argument. For example:
rpm -U oracle-graph-20.2.0.0.0.x86_64.rpm
Graph Server Uninstallation
To uninstall the graph server, make sure the graph server is shut down, then run the following command.
rpm -e oracle-graph
Graph Client Installation
For installing the Graph client, the prerequisites are:
- A Unix-based operation system (such as Linux) or macOS or Microsoft Windows
- Oracle JDK 11
- Download Oracle Graph Client 20.1 from Oracle Software Cloud.
- Unzip the file into a directory of your choice.
- Connect to a PGX server using Jshell. For example:
cd <client-install-dir>./bin/opg-jshell --base_url https://<host>:7007
Additional installation operations are required for specific use cases, such as:
- Install the client into Apache Zeppelin.
Installation of PL/SQL Packages in Oracle Database
Oracle Graph Server and Client will work with Oracle Database 12.2 onward. However, you must install the updated PL/SQL packages that are part of the Oracle Graph Server and Client download.
- Download Oracle Graph Client for PL/SQL from Oracle Software Delivery Cloud.
- Unzip the file into a directory of your choice.
- Install the PL/SQL packages:
- There are two directories, one for users with Oracle Database 18c or below, and one for users with Oracle Database 19c or above. As a database user with DBA privilges, follow the instructions in the README.md file in the appropriate directory (that matches your database version). This has to be done for every PDB you will use the graph feature in. For example,
-- Connect as SYSDBA SQL> alter session set container=<YOUR_PDB_NAME>; SQL> @opgremov.sql SQL> @catopg.sql
- There are two directories, one for users with Oracle Database 18c or below, and one for users with Oracle Database 19c or above. As a database user with DBA privilges, follow the instructions in the README.md file in the appropriate directory (that matches your database version). This has to be done for every PDB you will use the graph feature in. For example,
- Create a database user in the database for working with graphs:
- As a database user with DBA privileges, create a user
<graphuser>
, and grant the necessary privileges.- If you plan to use a three-tier architecture (graph queries and analytics executed in the in-memory graph server (PGX), then grant privileges as described in the following command:
SQL> GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO <graphuser>
- If you plan to use a two-tier architecture and run graph queries in the database, then grant privileges as described in Required Privileges for Database Users:
SQL> GRANT CREATE SESSION, ALTER SESSION, CREATE TABLE, CREATE PROCEDURE, CREATE TYPE, CREATE SEQUENCE, CREATE VIEW, CREATE TRIGGER TO
<graphuser>
- If you plan to use a three-tier architecture (graph queries and analytics executed in the in-memory graph server (PGX), then grant privileges as described in the following command:
- As a
<graphuser>
in the database, check that the PL/SQL update is successful:SQL> connect
<graphuser>
/<password>
SQL> select opg_apis.get_opg_version() from dual; -- Should return 20.4 if you are using -- Graph Server and Client 20.4
- As a database user with DBA privileges, create a user
- As a database user with DBA privileges create the
GRAPH_DEVELOPER
andGRAPH_ADMINISTRATOR
roles in the Database. Grant this role to the database user created in step 4.-
SQL> CREATE ROLE GRAPH_DEVELOPER SQL> CREATE ROLE GRAPH_ADMINISTRATOR
- Grant appropriate roles to the database user who will be
working with
graphs.
SQL> GRANT GRAPH_DEVELOPER to
<graphuser>
SQL> GRANT GRAPH_ADMINISTRATOR to<adminuser>
-
Database Compatibility and Restrictions
Oracle Graph Server and Client will work with Oracle Database 12.2 onward. This includes working with the family of Oracle Autonomous Database -- all versions of Oracle Autonomous Data Warehouse (shared), Oracle Autonomous Database (shared), and Oracle Autonomous Database (dedicated).
For details, including any limitations and actions you should take to address them, see "Database Compatibility Matrix for Oracle Graph Server and Client".
Parent topic: Property Graph Support Overview
1.3 Property Graph Features
Graphs manage networks of linked data as vertices, edges, and properties of the vertices and edges.
Graphs are commonly used to model, store, and analyze relationships found in social networks, cybersecurity, utilities and telecommunications, life sciences and clinical data, and knowledge networks.
Typical graph analyses encompass graph traversal, recommendations, finding communities and influencers, and pattern matching. Industries including telecommunications, life sciences and healthcare, security, media, and publishing can benefit from graphs.
The property graph features of Oracle Special and Graph support those use cases with the following capabilities:
-
A scalable graph database
-
Developer-based APIs based upon PGQL and Java graph APIs
-
Text search and query through integration with Oracle Text
-
A parallel, in-memory graph server (PGX) for running graph queries and graph analytics
-
A fast, scalable suite of social network analysis functions that include ranking, centrality, recommender, community detection, and path finding
-
Parallel bulk load and export of property graph data in Oracle-defined flat files format
-
A powerful Graph Visualization (GraphViz) application
-
Notebook support through integration with Apache Zeppelin
1.3.1 Property Graph Sizing Recommendations
The following are recommendations for property graph installation.
Table 1-1 Property Graph Sizing Recommendations
Graph Size | Recommended Physical Memory to be Dedicated | Recommended Number of CPU Processors |
---|---|---|
10 to 100M edges |
Up to 14 GB RAM |
2 to 4 processors, and up to 16 processors for more compute-intensive workloads |
100M to 1B edges |
14 GB to 100 GB RAM |
4 to 12 processors, and up to 16 to 32 processors for more compute-intensive workloads |
Over 1B edges |
Over 100 GB RAM |
12 to 32 processors, or more for especially compute-intensive workloads |
Parent topic: Property Graph Features
1.4 Security Best Practices with Graph Data
Several security-related best practices apply when working with graph data.
Sensitive Information
Graph data can contain sensitive information and should therefore be treated with the same care as any other type of data. Oracle recommends the following considerations when using a graph product:
- Avoid storing sensitive information in your graph if that information is not required for analysis. If you have existing data, only model the relevant subset you need for analysis as a graph, either by applying a preprocessing step or by using subgraph and filtering techniques that are part of graph product.
- Model your graph in a way that vertex and edge identifiers are not considered sensitive information.
- Do not deploy the product into untrusted environments or in a way that gives access to untrusted client connections.
- Make sure all communication channels are encrypted and that authentication is always enabled, even if running within a trusted network.
Least Privilege Accounts
The database user account that is being used by the in-memory analyst (PGX) to read data should be a low-privilege, read-only account. PGX is an in-memory accelerator that acts as a read-only cache on top of the database, and it does not write any data back to the database.
If your application requires writing graph data and later analyzing it using PGX, make sure you use two different database user accounts for each component.
Parent topic: Property Graph Support Overview
1.5 Interactive Graph Shell
Both the Oracle Graph server and client packages contain an interactive command-line application for interacting with all the Java APIs of the product, locally or on remote computers.
This interactive graph shell dynamically interprets command-line inputs from the user, executes them by invoking the underlying functionality, and can print results or process them further. The graph shell provides a lightweight and interactive way of exercising graph functionality without creating a Java application.
The graph shell is especially helpful if want to do any of the following:
- Quickly run a "one-off" graph analysis on a specific data set, rather than creating a large application
- Explore the data set, trying different graph analyses on the data set interactively
- Learn how to use the product and develop a sense of what the built-in algorithms are good for
- Develop and test custom graph analytics algorithms
The same graph shell executable can be used in both local or remote modes.
This graph shell is implemented on top of the Java Shell tool (JShell). As such, it inherits all features provided by JShell such as tab-completion, history, reverse search, semicolon inference, script files, and internal variables.
The graph shell automatically connects to a PGX instance (either remote or embedded depending on the --base_url
command-line option) and creates a PGX session. If --base_url
is not specified, a new local PGX instance and session are created.
Starting the Graph Shell
The Graph Shell uses JShell, which means the shell needs to run on Java 11 or later.
After installation, the shell executables can be found in /opt/oracle/graph/bin
after server installation or <INSTALL_DIR>/bin
after client installation. The shell may be launched by entering the following in your terminal:
./bin/opg-jshell
This starts the shell in embedded (local) mode, which means the graph functions are running in the client JVM.
When the shell has started, the following command line prompt appears:
opg-jshell-rdbms>
If you have multiple versions of Java installed, you can easily switch between installations by setting the JAVA_HOME variable before starting the shell. For example:
export JAVA_HOME=/usr/lib/jvm/java-11-oracle
Command-line Options
To view the list of available command-line options, add --help
to the opg-jshell
command:
./bin/opg-jshell --help
Embedded (Local) versus Remote Mode
The graph shell can be started in embedded (local) mode or remote mode. By default the shell starts in embedded mode, which means a local PGX instance is created running in the same JVM in which the shell is running.
Embedded mode is only supported in the Graph Server installation. Graph Client installations only support remote mode.
The local PGX instance will try to load a PGX configuration file from /etc/oracle/graph/pgx.conf
. You can change the location of the configuration file by passing the --pgx_conf
command-line option followed by the path to the configuration file:
# start local PGX instance with custom config
./bin/opg-jshell --pgx_conf path/to/my/pgx.conf
The graph shell can also be started in remote mode. In that case the shell connects to a PGX instance that runs in a separate JVM (possibly on a different machine). To launch the shell in remote mode, specify the base URL of the server with the --base_url
option. For example:
./bin/opg-jshell --base_url https://myserver.com:7007
Batch Execution of Scripts
The graph shell can execute a script by passing the path(s) to the script(s) to the opg-jshell
command. For example:
./bin/opg-jshell /path/to/script.jsh
Predefined Functions
The graph shell provides the following utility functions:
println(String)
: A shorthand for System.out.println(String).loglevel(String loggerName, String levelName)
: A convenient function to set the loglevel.
The loglevel
function allows you to set the log level for a logger. For example, loglevel("ROOT", "INFO")
sets the level of the root logger to INFO
. This causes all logs of INFO
and higher (WARN
, ERROR
, FATAL
) to be printed to the console.
Script Arguments
You can provide parameters to the script. For example:
./bin/opg-jshell /path/to/script.jsh script-arg-1 script-arg-2
In this example, the script /path/to/script.jsh
can access the arguments via the scriptArgs
system property. For example:
println(System.getProperty("scriptArgs"))// Prints: script-arg-1 script-arg-2
Staying in Interactive Mode
By default, the graph shell exits after it finishes execution. To stay in interactive mode after the script finishes successfully, pass the --keep_running
flag to the shell. For example:
./bin/opg-jshell -b https://myserver.com:7007/ /path/to/script.jsh --keep_running
Parent topic: Property Graph Support Overview
1.6 Storing Graphs in Oracle Database and Loading Graphs into Memory
You can work with graphs in two-tier mode (graph client connects directly to Oracle Database), or three-tier mode (graph client connects to the graph server (PGX) on the middle-tier, which then connects to Oracle Database).
Both modes for connecting to Oracle Database can be used regardless of whether the database is autonomous or not autonomous.
The database schema storing the graph must have the privileges listed in Required Privileges for Database Users.
If you are using the Oracle Autonomous Database, see also Using Oracle Graph with the Autonomous Database for information about two-tier and three-tier deployments.
- Two-Tier Mode
In two-tier mode, the client graph application connects directly to Oracle Database. - Three-Tier Mode
In three-tier mode, the client graph application connects to the graph server (PGX) in the middle tier, and the graph server connects to Oracle Database.
Parent topic: Property Graph Support Overview
1.6.1 Two-Tier Mode
In two-tier mode, the client graph application connects directly to Oracle Database.
The graph is stored in the property graph schema (see Property Graph Schema Objects for Oracle Database).
You can use the PGQL DDL statement CREATE PROPERTY GRAPH to create a graph from database tables and store it in the property graph schema. You can then run PGQL queries on this graph from JShell shell, Java application, or the graph visualization tool.
The graph can be loaded from the property graph schema into memory in the graph server for faster processing and for using the analytics API.
1.6.2 Three-Tier Mode
In three-tier mode, the client graph application connects to the graph server (PGX) in the middle tier, and the graph server connects to Oracle Database.
The graph can be loaded from the property graph schema into the graph server, or directly from database tables into the graph server.
-
Loading a Graph from Property Graph Schema:
Loading a graph from the property graph schema into memory in the graph server is the same as in the two-tier mode.
-
Loading a Graph Directly from Database Tables:
When you load the graph from database tables into memory in the graph server, you create the graph in memory by directly reading data from the database tables. You do not create a graph in the property graph schema.
For more information about loading a graph from database tables into memory, see Reading Data from Oracle Database into Memory.
After the graph is loaded into memory, you can run PGQL queries on this graph from JShell shell, Java application, or the graph visualization tool. You can run graph analytics API from JShell shell or Java application, and visualize the results in the graph visualization application (GraphViz).
1.7 Using Oracle Graph with the Autonomous Database
Oracle Graph Server and Client supports the family of Oracle Autonomous Database.
This includes all versions of Oracle Autonomous Data Warehouse (shared), Oracle Autonomous Database (shared), and Oracle Autonomous Database (dedicated).
You can connect in two-tier mode (connect directly to Autonomous Database) or three-tier mode (connect to PGX on the middle tier, which then connects to Autonomous Database). (For basic information about two-tier and three-tier connection modes, see Storing Graphs in Oracle Database and Loading Graphs into Memory.)
The database schema storing the graph must have the privileges listed in Required Privileges for Database Users.
- Two-Tier Deployments of Oracle Graph with Autonomous Database
In two-tier deployments, the client graph application connects directly to the Autonomous Database. - Three-Tier Deployments of Oracle Graph with Autonomous Database
In three-tier deployments, the client graph application connects to PGX in a middle tier, and PGX connects to the Autonomous Database.
Parent topic: Property Graph Support Overview
1.7.1 Two-Tier Deployments of Oracle Graph with Autonomous Database
In two-tier deployments, the client graph application connects directly to the Autonomous Database.
- Install Oracle Graph Server, as explained in Property Graph Prerequisites.
- Establish a JDBC connection, as described in the Oracle Autonomous Warehouse documentation.
Note that the Oracle Graph Server installation already contains all the necessary JDBC client libraries for connecting to Autonomous Databases. You do not have to install them yourself. You only have to download the wallet, unzip it to a secure location, and then reference it when establishing the connection.
For example:
opg-jshell-rdbms> var jdbcUrl = "jdbc:oracle:thin:@db201901151442_low?TNS_ADMIN=/etc/wallet" opg-jshell-rdbms> var user = "hr" opg-jshell-rdbms> var pass = "ChangeMe1234#_" opg-jshell-rdbms> var conn = DriverManager.getConnection(jdbcUrl, user, pass) conn ==> oracle.jdbc.driver.T4CConnection@57e6cb01
- Use the connection in your graph application.
Parent topic: Using Oracle Graph with the Autonomous Database
1.7.2 Three-Tier Deployments of Oracle Graph with Autonomous Database
In three-tier deployments, the client graph application connects to PGX in a middle tier, and PGX connects to the Autonomous Database.
The wallets downloaded from the Oracle Cloud Console are mainly routing wallets, meaning they are used to route the connection to the right database and to encrypt the connection. In most cases, they are not auto-login wallets, so they do not contain the password for the actual connection. The password usually needs to be provided separately to the wallet location.
The graph server does not support a wallet stored on the client file system or provided directly by remote users. The high level implications of this are:
- The server administrator provides the wallet and stores the wallet securely on the server's file system.
- Similar to Java EE connection pools, remote users will use that wallet when connecting. This means the server administrator trusts all remote users to use the wallet. As with any production deployments, the PGX server must be configured to enforce authentication and authorization to establish that trust.
- Remote users still need to provide a user name and password when sending a graph read request, just as with non-autonomous databases.
- You can only configure one wallet for each PGX server.
Having the same PGX server connecting to multiple Autonomous Databases is not supported. If you have that use case, start start one PGX server for each Autonomous Database.
Pre-loaded graphs
To read a graph from Autonomous Database into PGX at server startup, follow the steps described in Reading Data from Oracle Database into Memory to:
- Create a Java Keystore containing the database password
- Create a PGX graph configuration file describing the location and properties of the graph to be loaded
- Update the
/opt/oracle/graph/pgx.conf
file to reference the graph configuration file
Then, configure the server JVM to reference the unzipped Oracle Wallet location before starting up by setting the JAVA_OPTS environment. For example:
export JAVA_OPTS="-Doracle.net.tns_admin=/etc/wallet -Doracle.jdbc.fanEnabled=false"
cd /opt/oracle/graph
./pgx/bin/start-server --secret-store /etc/keystore.p12
If you start the PGX server using systemd
, edit the service file at /etc/systemd/system/pgx.service
and specify the environment variable the under the [Service] directive:
Environment="JAVA_OPTS=-Doracle.net.tns_admin=/etc/wallet"
Make sure that the directory (/etc/wallet
in the preceding example) is readable by the Oracle Graph user, which is the user that starts up the PGX server when using systemd
.
After the file is edited, reload the changes using:
systemctl daemon-reload
On-demand graph loading
To allow remote users of PGX to read from the Autonomous Database on demand, you can choose from two approaches:
-
Provide the path to the wallet at server startup time via the
oracle.net.tns_admin
system property. Remote users have to provide the TNS address name, username and keystore alias (password) in their graph configuration files. The wallet is stored securely on the graph server's file system, and the server administrator trusts all remote users to use the wallet to connect to an Autonomous Database.For example, the server administrator starts the PGX server as follows:
export JAVA_OPTS="-Doracle.net.tns_admin=/etc/wallet -Doracle.jdbc.fanEnabled=false" cd /opt/oracle/graph ./pgx/bin/start-server --secret-store /etc/keystore.p12
The
/etc/wallet/tnsnames.ora
file contains an address as follows:sombrero_medium = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.us-ashburn-1.oraclecloud.com))(connect_data=(service_name=l8lgholga0ujxsa_sombrero_medium.adwc.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adwc.uscom-east-1.oraclecloud.com,OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US")))
Now remote users can read data into the server by sending a graph configuration file with the following connection properties:
{ ... "jdbc_url": "jdbc:oracle:thin:@sombrero_medium", "username": "hr", "keystore_alias": "database1", ... }
Note that the keystore still lives on the client side and should contain the password for the
hr
user referenced in the config object, as explained in Reading Data from Oracle Database into Memory. A similar approach works for Tomcat or WebLogic Server deployments. -
Use Java EE connection pools in your web application server. Remote users only have to provide the name of the datasource in their graph configuration files. The wallet and the connection credentials are stored securely in the web application server's file system, and the server administrator trusts all remote users to use a connection from the pool to connect to an Autonomous Database.
You can find instructions how to set up such a data source at the following locations:If you gave the data source the name adb_ds, you can the reference them by sending a graph configuration file with the following connection properties:
{ ... "datasource_id": "adb_ds", ... }
Parent topic: Using Oracle Graph with the Autonomous Database
1.8 Migrating Property Graph Applications from Before Release 21c
If you are migrating from a previous version of Oracle Spatial and Graph to Release 21c, you may need to make some changes to existing property graph-related applications.
Also note that Oracle Graph Server and Client is required for property graph applications. This can be downloaded from Oracle Software Delivery Cloud or from Oracle Downloads page.
Security-Related Changes
The Property Graph feature contains a series of enhancements to further strengthen the security of the property graph component of product. The following enhancements may require manual changes to existing graph applications so that they continue to work properly.
- Graph configuration files now require sensitive information such as passwords to be stored in Java Keystore files
If you use graph configuration files you are required to use Java Keystore files to store sensitive information such as passwords. (See Reading Data from Oracle Database into Memory for how to create and reference such a keystore.)
All existing graph configuration files with secrets in them must be migrated to the keystore-based approach.
- In a three-tier deployment, access to the PGX server file system requires a whitelist
By default, the PGX server does not allow remote access to the local file system. This can be explicitly allowed, though, in
/etc/oracle/graph/pgx.conf
by settingallow_local_filesystem
totrue
. If you setallow_local_filesystem
totrue
, you must also specify a list of directories that are "whitelisted" to be accessed, by settingdatasource_dir_whitelist
. For example:"allow_local_filesystem": true, "datasource_dir_whitelist": ["/scratch/data1", "/scratch/data2"]
This will allow remote users to read and write data on the server's file-system from and into
/scratch/data1
and/scratch/data2
. - In a three-tier deployment, reading from remote locations into PGX is no longer allowed by default
Previously, PGX allowed graph data to be read from remote locations over FTP or HTTP. This is no longer allowed by default and requires explicit opt-in by the server administrator. To opt-in, specify the
allowed_remote_loading_locations
configuration option in/etc/oracle/graph/pgx.conf
. For example:allowed_remote_loading_locations: ["*"]
In addition:
- The ftp and http protocols are no longer supported for loading or storing data because they are unencrypted and thus insecure.
- Configuration files can no longer be loaded from remote locations, but must be loaded from the local file system.
- Removed shell command line options
The following command line options of the Groovy-based
opg
shell have been removed and will no longer work:--attach
- the shell no longer supports attaching to existing sessions via command line--password
- the shell will prompt now for the password
Also note that the Groovy-based shell has been deprecated, and you are encourage to use the new JShell-based shell instead (see Interactive Graph Shell).
- Changes to PGX APIs
The following APIs no longer return graph configuration information:
ServerInstance#getGraphInfo()
ServerInstance#getGraphInfos()
ServerInstance#getServerState()
The REST API now identifies collections, graphs, and properties by UUID instead of a name.
The namespaces for graphs and properties are session private by default now. This implies that some operations that would previously throw an exception due to a naming conflict could succeed now.
PgxGraph#publish()
throws an exception now if a graph with the given name has been published before.
Migrating Data to a New Database Version
Oracle Graph Server and Client works with older database versions. (See the "Database Compatibility and Restrictions" subtopic in Property Graph Prerequisites for information.) If as part of your upgrade you also upgraded your Oracle Database, you can migrate your existing graph data that was stored using the Oracle Property Graph format by invoking the following helper script in your database after the upgrade:
sqlplus> execute mdsys.opg.migrate_pg_to_current(graph_name=>'mygraph');
The preceding example migrates the property graph mygraph to the current database version.
Uninstalling Previous Versions of Property Graph Libraries
This is only necessary if you are using Oracle Database versions 12.2, 18c, or 19c.
Use of the Property Graph feature of Oracle Database now requires Oracle Graph Server and Client that is installed separately. After you have completed the Graph Server and Client installation, complete the preceding migration steps (if needed), and confirmed that everything is working well, it is recommended that you remove the binaries of older graph installations from your Oracle Database installation by performing the following un-install steps:
-
Make sure the Property Graph mid-tier components are not in use on the target database host. For example, ensure that there is no application running which uses any files under
$ORACLE_HOME/md/property_graph
. Examples of such an application are a running PGX server on the same host as the database or a client application that references the JAR files under$ORACLE_HOME/md/property_graph/lib
.It is not necessary to shut down the database to perform the uninstall. The Oracle database itself does not reference or use any files under
$ORACLE_HOME/md/property_graph
. - Remove the files under
$ORACLE_HOME/md/property_graph
on your database host. On Linux, you can copy the following helper script to your database host and run it with as the DBA operating system user:/opt/oracle/graph/scripts/patch-opg-oracle-home.sh
Parent topic: Property Graph Support Overview
1.9 Quick Start: Interactively Analyze Graph Data
This tutorial shows how you can quickly get started using property graph data.
You will learn how to:
- Convert existing relational data into a graph.
- Query that data using PGQL.
- Run graph algorithms on that data and display results.
The tutorials in this section are:
- 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). - Quick Start: Create and Query a Graph in the Database, Load into In-Memory Graph Server (PGX) for Analytics
InSection 1.9.2.1
, this tutorial shows how you can get started using property graph data when you create a graph and persist it in the database. The graph can be queried in the database. This tutorial uses the JShell client. - Quick Start: Executing PGQL Queries in SQLcl
Parent topic: Property Graph Support Overview
1.9.1 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
-
An installation of Oracle Graph server (this is PGX, the in-memory graph server).
See Property Graph Prerequisites for information to download Oracle Graph Server and Client.
-
An installation of 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.
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 in-memory graph server (PGX):
./bin/opg-jshell --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/opg-jshell --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 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 in-memory graph server
(PGX):opg-jshell> 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);
opg-jshell> PgxGraph g = session.getGraph("HR_SIMPLIFIED");
Run a few PGQL queries
opg-jshell> 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";
opg-jshell> PgqlResultSet resultSet = g.queryPgql(query);
opg-jshell> resultSet.print();
+-----------------+
| department_name |
+-----------------+
| Shipping |
+-----------------+
opg-jshell> String query =
"SELECT label(n), COUNT(*) "
+ "FROM MATCH (n) "
+ "GROUP BY label(n) "
+ "ORDER BY COUNT(*) DESC";
opg-jshell> PgqlResultSet resultSet = g.queryPgql(query);
opg-jshell> resultSet.print();
+-----------------------+
| label(n) | COUNT(*) |
+-----------------------+
| EMPLOYEE | 107 |
| DEPARTMENT | 27 |
+-----------------------+
To get an overview of the types of edges and their frequencies, execute:
opg-jshell> 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";
opg-jshell> PgqlResultSet resultSet = g.queryPgql(query);
opg-jshell> resultSet.print();
+---------------------------------------------+
| srcLbl | edgeLbl | dstLbl | COUNT(*) |
+---------------------------------------------+
| EMPLOYEE | WORKS_AT | DEPARTMENT | 106 |
+---------------------------------------------+
Execute algorithms and query the algorithm results
pagerank
:opg-jshell> analyst.pagerank(g)
$31==> VertexProperty[name=pagerank,type=double,graph=hr]
opg-jshell> 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.
opg-jshell> 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
publish()
API to make the graph visible to other sessions, such as the
graph visualization session. For
example:opg-jshell> 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 Add Permissions to Publish the Graph. We had given the GRAPH_DEVELOPER
role to the database user in Installation of 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.
opg-jshell> 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 Set up Transport Layer Security for more details.Parent topic: Quick Start: Interactively Analyze Graph Data
1.9.2 Quick Start: Create and Query a Graph in the Database, Load into In-Memory Graph Server (PGX) for Analytics
In Section 1.9.2.1
, this tutorial shows how you can get
started using property graph data when you create a graph and persist it in the database. The
graph can be queried in the database. This tutorial uses the JShell client.
- Convert existing relational data into a graph in the database.
- Query this graph using PGQL.
Section 1.9.2.2
, you will run graph algorithms after loading the graph
into the in-memory graph server (PGX).
- Load the graph into the in-memory graph server (PGX), run graph algorithms on this graph, and visualize results.
-
An installation of Oracle Graph server (this is PGX, the in-memory graph server).
See Property Graph Prerequisites for information to download Oracle Graph Server and Client.
- An installation of Oracle Graph client
- 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.
- Connection details your Oracle Database. See Database Compatibility and Restrictions to identify any limitations. The Property Graph feature is supported for Oracle Database versions 12.2 and later.
- Basic knowledge about how to run commands on Oracle Database (for example, using
SQL*Plus
orSQL Developer
).
Set up the example data
This example uses the HR (human resources) sample dataset.
- 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.
- 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. - Load the Graph into Memory and Run Graph Analytics
Parent topic: Quick Start: Interactively Analyze Graph Data
1.9.2.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/opg-jshell --noconnect
<graphuser>
is the database user you will use to for the
PGX server authentication. You will be prompted for the database password.
The --noconnect
option indicates that you are not connecting
to the in-memory 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 for details about the shell.
Open a JDBC database connection
opg-jshell> var jdbcUrl = "<jdbc-url>" // for example: jdbc:oracle:thin:@myhost:1521/myservice
opg-jshell> var user = "<db-user>" // for example: hr
opg-jshell> var pass = "<db-pass>"
opg-jshell> 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
opg-jshell> conn.setAutoCommit(false)
opg-jshell> 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
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:opg-jshell> 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
opg-jshell> 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
opg-jshell> query.accept("select count(v) from hr match (v)")
+----------+
| count(v) |
+----------+
| 215 |
+----------+
// print the number of edges in the graph
opg-jshell> query.accept("select count(e) from hr match ()-[e]->()")
+----------+
| count(e) |
+----------+
| 433 |
+----------+
// find the highest earning managers
opg-jshell> query.accept("select distinct m.FIRST_NAME, m.LAST_NAME, m.SALARY from hr match (v:EMPLOYEE)-[:WORKS_FOR]->(m:EMPLOYEE) 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
opg-jshell> query.accept("select avg(e.SALARY) from hr match (e:EMPLOYEE) -[h:WORKS_AT]-> (d:DEPARTMENT) -[:LOCATED_IN]-> (:LOCATION) -[:LOCATED_IN]-> (:COUNTRY) -[:LOCATED_IN]-> (r:REGION) where r.REGION_NAME = 'Americas' and d.DEPARTMENT_NAME = 'Accounting'")
+---------------+
| avg(e.SALARY) |
+---------------+
| 14500.0 |
+---------------+
1.9.2.2 Load the Graph into Memory and Run Graph Analytics
Load the graph from the property graph schema into memory
In this section of the quickstart, you will load the graph stored in the Property Graphs schema in the database into the in-memory graph server (PGX). This will enable you to run a variety of different built-in algorithms on the graph and will also improve query performance for larger graphs.
./bin/opg-jshell --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/opg-jshell --base_url http://<graph server host>:7007 --username <graphuser>
This starts the shell and makes a connection to the graph server.
Note:
Always use low-privilege read-only database user accounts for PGX, as explained in Security Best Practices with Graph Data.Next load the graph into memory in this server.
To load the graph into memory, create a PGX graph config object, using the PGX graph config builder API to do this directly in the shell.
Supplier<GraphConfig> pgxConfig = () -> { return GraphConfigBuilder.forPropertyGraphRdbms()
.setName("hr")
.addVertexProperty("COUNTRY_NAME", PropertyType.STRING)
.addVertexProperty("DEPARTMENT_NAME", PropertyType.STRING)
.addVertexProperty("FIRST_NAME", PropertyType.STRING)
.addVertexProperty("LAST_NAME", PropertyType.STRING)
.addVertexProperty("EMAIL", PropertyType.STRING)
.addVertexProperty("PHONE_NUMBER", PropertyType.STRING)
.addVertexProperty("SALARY", PropertyType.DOUBLE)
.addVertexProperty("MIN_SALARY", PropertyType.DOUBLE)
.addVertexProperty("MAX_SALARY", PropertyType.DOUBLE)
.addVertexProperty("STREET_ADDRESS", PropertyType.STRING)
.addVertexProperty("POSTAL_CODE", PropertyType.STRING)
.addVertexProperty("CITY", PropertyType.STRING)
.addVertexProperty("STATE_PROVINCE", PropertyType.STRING)
.addVertexProperty("REGION_NAME", PropertyType.STRING)
.setPartitionWhileLoading(PartitionWhileLoading.BY_LABEL)
.setLoadVertexLabels(true)
.setLoadEdgeLabel(true)
.build(); }
opg-jshell> var graph = session.readGraphWithProperties(pgxConfig.get())
graph ==> PgxGraph[name=hr,N=215,E=433,created=1586996113457]
The session object is created for you automatically.
Execute algorithms and query the algorithm results
pagerank
:opg-jshell> analyst.pagerank(graph)
$31==> VertexProperty[name=pagerank,type=double,graph=hr]
opg-jshell> session.queryPgql("select m.FIRST_NAME, m.LAST_NAME, m.pagerank from hr match (m:EMPLOYEE) order by m.pagerank desc limit 10").print().close()
+----------------------------------------------------+
| m.FIRST_NAME | m.LAST_NAME | m.pagerank |
+----------------------------------------------------+
| Adam | Fripp | 0.002959240305566317 |
| John | Russell | 0.0028810951120575284 |
| Michael | Hartstein | 0.002181365227465801 |
| Alexander | Hunold | 0.002082616009054747 |
| Den | Raphaely | 0.0020378615199327507 |
| Shelley | Higgins | 0.002028946863425767 |
| Nancy | Greenberg | 0.0017419394483596667 |
| Steven | King | 0.0016622985848193119 |
| Neena | Kochhar | 0.0015252785582170803 |
| Jennifer | Whalen | 0.0014263044976976823 |
+----------------------------------------------------+
Share the Graph with Other Sessions
publish()
API to make the graph visible to other sessions, such as the
graph visualization session. For
example:opg-jshell> 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
.
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.
opg-jshell> 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 Set up Transport Layer Security for more details.1.9.3 Quick Start: Executing PGQL Queries in SQLcl
You can execute PGQL queries in SQLcl with a plugin that is available with Oracle Graph Server and Client.
See Execute PGQL Queries in SQLcl for more details.
You can also refer to PGQL Plug-in for SQLclPGQL Plug-in for SQLcl section in the SQLcl documentation.
Parent topic: Quick Start: Interactively Analyze Graph Data
1.10 Managing Property Graphs With Python Client
This section describes how to install Python Client and how to use the Python Client to work with Property Graphs.
- Installing the Python Client
- Using the Python Client
You can use the Python client in an interactive mode (through Python shell) or as a module used by a Python program. - Troubleshooting the Python Client
This section helps you to troubleshoot issues on installing or using the Python client. - Uninstalling the Python Client
This section describes how to uninstall the Python client.
Parent topic: Property Graph Support Overview
1.10.1 Installing the Python Client
- Oracle JDK 8 or later
- Python 3.5 or later
Note:
Python 2.x is not supported.For more information on installing Python 3 on Oracle Linux, see Python for Oracle Linux.
To install the Python client:
Parent topic: Managing Property Graphs With Python Client
1.10.2 Using the Python Client
You can use the Python client in an interactive mode (through Python shell) or as a module used by a Python program.
In either of those two modes, you can connect to a graph server running on a different host (remote server) or in the client process (embedded server).
- Interactive Mode Using Python Shell
This section describes how you can use the Python client interactively using the Python shell. - Module Mode
This section describes how to use the Python client as a module in Python applications.
Parent topic: Managing Property Graphs With Python Client
1.10.2.1 Interactive Mode Using Python Shell
This section describes how you can use the Python client interactively using the Python shell.
Embedded Server
Note:
For this mode, the Python client and the Graph Server RPM package must be installed on the same machine.- Start the Python
shell.
cd /opt/oracle/graph/ ./bin/opgpy
- When the shell is running, you can see the following prompt on
your
screen
Oracle Graph Server Shell 20.4.0 >>>
Remote Server
You can use the Python client can be used to connect to a running graph server instance, by following these instructions:
- Unzip the client
package.
unzip oracle-graph-client-20.4.0.zip cd oracle-graph-client-20.4.0
- Start the shell by running one of the following commands:
- To connect to the PGX server instance located at
https://localhost:7007
using login credentials:./bin/opgpy --base_url https://localhost:7007 --username scott
You are prompted to enter your password.
- If you have an existing authentication token for
the graph server, do the following to connect the
graph
server:
./bin/opgpy --base_url https://localhost:7007
You will be prompted to enter your authentication token.
- To start the client shell, and to avoid
establishing a connection to any graph
server:
./bin/opgpy --no_connect
- To connect to the PGX server instance located at
- When the shell is running, you will see the following appear in
your
screen:
Oracle Graph Client Shell 20.4.0 >>>
Shell Example
After the shell starts up successfully, the variables session, instance, analyst are already pre-defined and ready to use as illustrated in the following example:
Oracle Graph Server Shell 20.4.0
>>> instance
ServerInstance(embedded: True, version: <oracle.pgx.common.VersionInfo at 0x7f7cfb5ee200 jclass=oracle/pgx/common/VersionInfo jself=<LocalRef obj=0x3692a48 at 0x7f7d04084570>>)
>>> graph = session.create_graph_builder().add_edge(1, 2).add_edge(2, 3).build("my_graph")
>>> analyst.pagerank(graph)
VertexProperty(name: pagerank, type: double, graph: my_graph)
>>> rs = session.query_pgql("select id(x), x.pagerank from match (x) on my_graph")
>>> rs.print()
+-----------------------------+
| id(x) | pagerank |
+-----------------------------+
| 1 | 0.05000000000000001 |
| 2 | 0.09250000000000003 |
| 3 | 0.12862500000000002 |
+-----------------------------+
Note:
To view the complete set of available Python APIs, see Pypgx API.Parent topic: Using the Python Client
1.10.2.2 Module Mode
This section describes how to use the Python client as a module in Python applications.
Embedded Server
Note:
For this mode, the Python client and the Graph Server RPM package must be installed on the same machine.import os
os.environ["PGX_CLASSPATH"] = "/opt/oracle/graph/lib/*"
import pypgx
session = pypgx.get_session()
graph = session.create_graph_builder().add_edge(1, 2).add_edge(2, 3).build("my_graph")
analyst = session.create_analyst()
analyst.pagerank(graph)
rs = session.query_pgql("select id(x), x.pagerank from match (x) on my_graph")
rs.print()
To execute, save the above program into a file named
program.py
and run the following
command.
python3 program.py
You will see the following output:
+-----------------------------+
| id(x) | pagerank |
+-----------------------------+
| 1 | 0.05000000000000001 |
| 2 | 0.09250000000000003 |
| 3 | 0.12862500000000002 |
+-----------------------------+
Note:
To view the complete set of available Python APIs, see Pypgx API.Remote Server
Note:
Replace thebase_url
,
username, and password with values to match your environment
details.
import json
import os
import platform
import sys
from urllib.request import Request, urlopen
from urllib.error import HTTPError
import pypgx as pgx
base_url = "https://localhost:7007"
username = "scott"
password = "tiger"
def generateToken():
body = json.dumps({ 'username': username, 'password': password }).encode('utf8')
headers = { 'content-type': 'application/json' }
request = Request(base_url + '/auth/token', data=body, headers=headers)
try:
response = urlopen(request).read().decode('utf-8')
return json.loads(response).get('access_token')
except HTTPError as err:
if err.code == 400:
print('Authentication failed no username/password given')
elif err.code == 401:
print('Authentication failed invalid username/password')
else:
print("Server returned HTTP response code: {} for URL: {}".format(err.code, err.url))
os._exit(1)
session = pgx.get_session(base_url=base_url, token=generateToken())
print(session)
To execute, save the above program into a file named
program.py
and run the following
command:
python3 program.py
After successful login, you'll see the following message indicating a PGX session was created:
PgxSession(id: 0bdd4828-c3cc-4cef-92c8-0fcd105416f0, name: python_pgx_client)
Note:
To view the complete set of available Python APIs, see Pypgx API.Parent topic: Using the Python Client
1.10.3 Troubleshooting the Python Client
This section helps you to troubleshoot issues on installing or using the Python client.
Verifying your Python client version
$> python3 --version
Python 3.6.1
Missing modules after installation
In some cases, the Python client installation might fail to install some required dependencies, and you might see the following error message when running the shell:
ImportError: No module named 'jnius_config'
To fix this, you can manually install the required dependencies by following these steps:
- Navigate to your Oracle Graph Client installation directory.
- Run the following commands in the same
order:
python3 -m pip install python/Cython-0.29.17.zip python3 -m pip install python/six-1.14.0.zip python3 -m pip install python/pyjnius-1.3.0.zip
Parent topic: Managing Property Graphs With Python Client
1.10.4 Uninstalling the Python Client
This section describes how to uninstall the Python client.
To uninstall the Python client, run the following command:
pip3 uninstall pypgx
Parent topic: Managing Property Graphs With Python Client