1. Graph Developer's Guide for Property Graph
  2. Property Graph Views
  3. Quick Starts for Using Property Graph Views

9 Quick Starts for Using Property Graph Views

This chapter contains quick start tutorials and other resources to help you get started on working with property graph views.

Parent topic: Property Graph Views

9.1 Using Sample Data for Graph Analysis

The rpm installation of the graph server provides you with sample graph data in /opt/oracle/graph/data directory.

The bank_graph folder contains data that represent the vertices and edges of a graph in bank_nodes.csv and bank_edges_amt.csv files respectively. You can import the graph data from these .csv files into the database. You can then create a graph for querying and analyses.

Parent topic: Quick Starts for Using Property Graph Views

9.1.1 Importing Data from CSV Files

You can import data from CSV files into the database through Oracle SQL Developer or by using Oracle Database utilities (such as SQL*Loader or External Tables).

The following instructions enable you to load data into the database tables using Oracle SQL Loader.

As a prerequisite requirement, you must execute the following SQL statements to create the vertex (bank_accounts) and edge (bank_txns) tables in the database:
CREATE TABLE bank_accounts(id NUMBER, name VARCHAR2(10));
CREATE TABLE bank_txns(from_acct_id NUMBER, to_acct_id NUMBER, description VARCHAR2(10), amount NUMBER);

You can then perform the following steps to load the data:

  1. Create a SQL*Loader control file to load the vertices from bank_nodes.csv as shown:
    load data
infile '<path_to_bank_nodes.csv>'
into table bank_accounts
fields terminated by "," optionally enclosed by '"'
( id, name )
  2. Invoke SQL*Loader from the command line to load the vertices in bank_accounts table, using the preceding configuration file as shown:
    sqlldr <dbuser>/<password> CONTROL=<path_to_vertex_loader.ctl>

    The bank_accounts table gets successfully loaded with 1000 rows.

  3. Create a SQL*Loader control file to load the edge from bank_edges_amt.csv as shown:
    load data
infile '<path_to_bank_edges_amt.csv>'
into table bank_txns
fields terminated by "," optionally enclosed by '"'
(from_acct_id,to_acct_id,description,amount)
  4. Invoke SQL*Loader from the command line to load the edges in bank_txns table, using the preceding configuration file as shown:
    sqlldr <dbuser>/<password> CONTROL=<path_to_edge_loader.ctl>

    The bank_txns table gets successfully loaded with 4996 rows.

  5. Execute the following SQL statement to add the primary key constraint in the bank_accounts table:
    ALTER TABLE bank_accounts ADD PRIMARY KEY (id);
  6. Execute the following SQL statements to add a primary key column to the bank_txns table, populate it with ROWNUM values and then define the primary key constraint:
    ALTER TABLE bank_txns ADD txn_id NUMBER;
UPDATE bank_txns SET txn_id = ROWNUM;
COMMIT;
ALTER TABLE bank_txns ADD PRIMARY KEY (txn_id);
  7. Execute the following SQL statements to add the foreign key constraints to the bank_txns table:
    ALTER TABLE bank_txns MODIFY from_acct_id REFERENCES bank_accounts(id);
ALTER TABLE bank_txns MODIFY to_acct_id REFERENCES bank_accounts(id);
The sample bank graph data is now available in the database tables.

Parent topic: Using Sample Data for Graph Analysis

9.2 Quick Start: Working with Property Graph Views

This tutorial helps you get started on creating, querying and executing graph algorithms on property graph views.

The instructions assume that you have loaded the sample bank graph data provided with the graph server installation in the database tables. See Using Sample Data for Graph Analysis for more information.
The following instructions are supported with examples that can be executed either with the OPG4J Java shell or OPG4PY Python shell or through a Java program using the PGX API.
  1. Start the interactive graph shell CLI: 
    cd /opt/oracle/graph
./bin/opg4j --no_connect
Oracle Graph Server Shell 23.1.0
    cd /opt/oracle/graph
./bin/opg4py --no_connect
Oracle Graph Server Shell 23.1.0
  2. Obtain a JDBC database connection, if using OPG4J shell or a Java program. 
    opg4j> var jdbcUrl="jdbc:oracle:thin:@<host>:<port>/<sid>"
jdbcUrl ==> "jdbc:oracle:thin:@localhost:1521/orclpdb"
opg4j> var conn = DriverManager.getConnection(jdbcUrl,"<username>","<password>")
conn ==> oracle.jdbc.driver.T4CConnection@7d463c9f
opg4j> conn.setAutoCommit(false);
    import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlStatement;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.pgx.api.*;
import oracle.pg.rdbms.GraphServer;
import oracle.pg.rdbms.pgql.jdbc.PgqlJdbcRdbmsDriver;
…
…
// Get a jdbc connection
DriverManager.registerDriver(new PgqlJdbcRdbmsDriver());
String jdbcUrl="jdbc:oracle:thin:@"+<host>+":"+<port>+"/"+<sid>;
conn = DriverManager.getConnection(jdbcUrl, <username>, <password>);
conn.setAutoCommit(false);
  3. Create a PGQL connection. 
    opg4j> var pgqlConn = PgqlConnection.getConnection(conn)
pgqlConn ==> oracle.pg.rdbms.pgql.PgqlConnection@5c5c784c
    PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
    >>> pgql_conn = opg4py.pgql.get_connection("<username>","<password>", "jdbc:oracle:thin:@<host>:<port>/<sid>")
  4. Create a PGQL statement to execute PGQL queries. 
    opg4j> var pgqlStmt = pgqlConn.createStatement()
pgqlStmt ==> oracle.pg.rdbms.pgql.PgqlExecution@29e3c28
    PgqlStatement pgqlStmt = pgqlConn.createStatement();
    >>> pgql_statement = pgql_conn.create_statement()
  5. Create a property graph view using the CREATE PROPERTY GRAPH statement: 
    opg4j> String pgql =
...> "CREATE PROPERTY GRAPH bank_graph_view "
...> + "VERTEX TABLES ( BANK_ACCOUNTS AS ACCOUNTS "
...> + "KEY (ID) "
...> + "LABEL ACCOUNTS "
...> + "PROPERTIES (ID, NAME) "
...> + ") "
...> + "EDGE TABLES ( BANK_TXNS AS TRANSFERS "
...> + "KEY (FROM_ACCT_ID, TO_ACCT_ID, AMOUNT) "
...> + "SOURCE KEY (FROM_ACCT_ID) REFERENCES ACCOUNTS (ID) "
...> + "DESTINATION KEY (TO_ACCT_ID) REFERENCES ACCOUNTS (ID) "
...> + "LABEL TRANSFERS "
...> + "PROPERTIES (FROM_ACCT_ID, TO_ACCT_ID, AMOUNT, DESCRIPTION) "
...> + ") OPTIONS (PG_VIEW) "
opg4j> pgqlStmt.execute(pgql)
    String pgql = 
        "CREATE PROPERTY GRAPH " + graph + " " +
        "VERTEX TABLES ( BANK_ACCOUNTS AS ACCOUNTS " +
        "KEY (ID) " +
        "LABEL ACCOUNTS " +
        "PROPERTIES (ID, NAME)" +
        ") " +
        "EDGE TABLES ( BANK_TXNS AS TRANSFERS " +
        "KEY (FROM_ACCT_ID, TO_ACCT_ID, AMOUNT) " +
        "SOURCE KEY (FROM_ACCT_ID) REFERENCES ACCOUNTS (ID) " +
        "DESTINATION KEY (TO_ACCT_ID) REFERENCES ACCOUNTS (ID) " +
        "LABEL TRANSFERS " +
        "PROPERTIES (FROM_ACCT_ID, TO_ACCT_ID, AMOUNT, DESCRIPTION)" +
        ") OPTIONS(PG_VIEW)";

      pgqlStmt.execute(pgql);
    >>> pgql = """
...         CREATE PROPERTY GRAPH bank_graph_view
...         VERTEX TABLES (
...           BANK_ACCOUNTS
...           LABEL ACCOUNTS
...           PROPERTIES (ID, NAME)
...         )
...         EDGE TABLES (
...           BANK_TXNS
...             SOURCE KEY (FROM_ACCT_ID) REFERENCES BANK_ACCOUNTS (ID)
...             DESTINATION KEY (TO_ACCT_ID) REFERENCES BANK_ACCOUNTS (ID)
...             LABEL TRANSFERS
...             PROPERTIES (FROM_ACCT_ID, TO_ACCT_ID, AMOUNT, DESCRIPTION)
...         ) OPTIONS(PG_VIEW)
... """
>>> pgql_statement.execute(pgql)
False
    The property graph view bank_graph_view gets created successfully.
  6. Execute the following query to retrieve the first 10 elements of the graph as shown: 
    opg4j> String pgqlQuery =
...> "SELECT e.from_acct_id, e.to_acct_id, e.amount FROM "
...> + "MATCH (n:ACCOUNTS) -[e:TRANSFERS]-> (m:ACCOUNTS) ON BANK_GRAPH_VIEW "
...> + "LIMIT 10"
opg4j> var rs = pgqlStmt.executeQuery(pgqlQuery)
rs ==> oracle.pg.rdbms.pgql.pgview.PgViewResultSet@1e368085
opg4j> rs.print()
+------------------------------------+
| FROM_ACCT_ID | TO_ACCT_ID | AMOUNT |
+------------------------------------+
| 121          | 94         | 1000   |
| 121          | 255        | 1000   |
| 121          | 221        | 1000   |
| 122          | 27         | 1000   |
| 122          | 606        | 1000   |
| 122          | 495        | 1000   |
| 122          | 640        | 1000   |
| 122          | 140        | 1000   |
| 123          | 95         | 1000   |
| 123          | 130        | 1000   |
+------------------------------------+
$16 ==> oracle.pg.rdbms.pgql.pgview.PgViewResultSet@1e368085
    String pgqlQuery = 
        "SELECT e.from_acct_id, e.to_acct_id, e.amount FROM " +
        "MATCH (n:ACCOUNTS) -[e:TRANSFERS]-> (m:ACCOUNTS) ON BANK_GRAPH_VIEW " +
        "LIMIT 10";
PgqlResultSet rs = pgqlStmt.executeQuery(pgqlQuery);
rs.print();
    >>> pgql = """
... SELECT e.from_acct_id, e.to_acct_id, e.amount FROM
... MATCH (n:ACCOUNTS) -[e:TRANSFERS]-> (m:ACCOUNTS) on BANK_GRAPH_VIEW
... limit 10
... """
>>> pgql_statement.execute_query(pgql).print()
+------------------------------------+
| FROM_ACCT_ID | TO_ACCT_ID | AMOUNT |
+------------------------------------+
| 121          | 94         | 1000   |
| 121          | 255        | 1000   |
| 121          | 221        | 1000   |
| 122          | 27         | 1000   |
| 122          | 606        | 1000   |
| 122          | 495        | 1000   |
| 122          | 640        | 1000   |
| 122          | 140        | 1000   |
| 123          | 95         | 1000   |
| 123          | 130        | 1000   |
+------------------------------------+
  7. Load the graph into the 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. 
    opg4j> var instance = GraphServer.getInstance("https://localhost:7007", "<username>", "<password>".toCharArray())
instance ==> ServerInstance[embedded=false,baseUrl=https://localhost:7007]
opg4j> var session = instance.createSession("mySession")
session ==> PgxSession[ID=43653128-59cd-4e69-992c-1a2beac05857,source=mySession]
opg4j> var graph = session.readGraphByName("BANK_GRAPH_VIEW",GraphSource.PG_VIEW)
graph ==> PgxGraph[name=BANK_GRAPH_VIEW,N=1000,E=4996,created=1643308582055]
    ServerInstance instance = GraphServer.getInstance("https://localhost:7007", "<username>", "<password>".toCharArray());
PgxSession session = instance.createSession("my-session");
PgxGraph graph = session.readGraphByName("BANK_GRAPH_VIEW",GraphSource.PG_VIEW);
    >>> instance = graph_server.get_instance("https://localhost:7007","<username>","<password>")
>>> session = instance.create_session("my_session")
>>> graph = session.read_graph_by_name('BANK_GRAPH_VIEW', 'pg_view')
>>> graph
PgxGraph(name: BG_PY_VIEW, v: 1000, e: 4996, directed: True, memory(Mb): 0)
  8. Execute the PageRank algorithm as shown: 
    opg4j> var analyst = session.createAnalyst()
analyst ==> NamedArgumentAnalyst[session=3f0a9a71-f349-4aac-b75f-a7c4ae50851b]
opg4j> analyst.pagerank(graph)
$10 ==> VertexProperty[name=pagerank,type=double,graph=BANK_GRAPH_VIEW]
    Analyst analyst = session.createAnalyst();
analyst.pagerank(graphView);
    >>> analyst = session.create_analyst()
>>> analyst.pagerank(graph)
VertexProperty(name: pagerank, type: double, graph: BANK_GRAPH_VIEW)
  9. Query the graph to list the top 10 accounts by pagerank: 
    opg4j> String pgql ==> "SELECT a.id, a.pagerank FROM MATCH (a) ON BANK_GRAPH_VIEW ORDER BY a.pagerank DESC LIMIT 10"
opg4j> session.queryPgql(pgql).print()
+-----------------------------+
| id  | pagerank              |
+-----------------------------+
| 387 | 0.007292323575404966  |
| 406 | 0.0067300944623203615 |
| 135 | 0.0067205459831892545 |
| 934 | 0.00663484385036358   |
| 397 | 0.005693569761570973  |
| 559 | 0.0052584383114609844 |
| 352 | 0.005216329599236731  |
| 330 | 0.005093350408942336  |
| 222 | 0.004682551613749817  |
| 4   | 0.004569682370461633  |
+-----------------------------+
$18 ==> PgqlResultSetImpl[graph=BANK_GRAPH_VIEW,numResults=10]

    String pgQuery = "SELECT a.id, a.pagerank FROM MATCH (a) ON BANK_GRAPH_VIEW ORDER BY a.pagerank DESC LIMIT 10";
session.queryPgql(pgQuery).print();
    >>> pgql = "SELECT a.id, a.pagerank FROM MATCH (a) ON BANK_GRAPH_VIEW ORDER BY a.pagerank DESC LIMIT 10"
>>> session.query_pgql(pgql).print()
+-----------------------------+
| id  | pagerank              |
+-----------------------------+
| 387 | 0.007292323575404966  |
| 406 | 0.0067300944623203615 |
| 135 | 0.0067205459831892545 |
| 934 | 0.00663484385036358   |
| 397 | 0.005693569761570973  |
| 559 | 0.0052584383114609844 |
| 352 | 0.005216329599236731  |
| 330 | 0.005093350408942336  |
| 222 | 0.004682551613749817  |
| 4   | 0.004569682370461633  |
+-----------------------------+

Parent topic: Quick Starts for Using Property Graph Views

9.3 Quick Start: Using the Python Client as a Module

This section describes how to use the Python client as a module in Python applications.

Remote Server

For this mode, all you need is the Python client to be installed. In your Python program, you must authenticate with the remote server before you can create a session as illustrated in the following example. Note that you must replace the values for base_url, jdbc_url, username, and password with values to match your environment details. 

import pypgx
import opg4py
import opg4py.graph_server as graph_server
pgql_conn = opg4py.pgql.get_connection("<username>","<password>", "<jdbc_url>")
pgql_statement = pgql_conn.create_statement()
pgql = """
        CREATE PROPERTY GRAPH bank_graph
        VERTEX TABLES (
          bank_accounts
            LABEL ACCOUNTS
            PROPERTIES (ID, NAME)
        )
        EDGE TABLES (
          bank_txns
            SOURCE KEY (from_acct_id) REFERENCES bank_accounts
            DESTINATION KEY (to_acct_id) REFERENCES bank_accounts
            LABEL TRANSFERS
            PROPERTIES (FROM_ACCT_ID, TO_ACCT_ID, AMOUNT, DESCRIPTION)
        ) OPTIONS(PG_VIEW)
"""
pgql_statement.execute(pgql)
instance = graph_server.get_instance("<base_url>", "<username>", "<password>")
session = instance.create_session("my_session")
graph = session.read_graph_by_name('BANK_GRAPH', 'pg_view')
analyst = session.create_analyst()
analyst.pagerank(graph)
rs = graph.query_pgql("SELECT id(x), x.pagerank FROM MATCH (x) LIMIT 5")
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              |
+-------------------------------------------+
| BANK_ACCOUNTS(2)  | 9.749447313256548E-4  |
| BANK_ACCOUNTS(4)  | 0.004584001759076056  |
| BANK_ACCOUNTS(6)  | 5.358461393401424E-4  |
| BANK_ACCOUNTS(8)  | 0.0013051552434930175 |
| BANK_ACCOUNTS(10) | 0.0015040122009364232 |
+-------------------------------------------+

Converting PGQL result set into pandas dataframe

Additionally, you can also convert the PGQL result set to a pandas.DataFrame object using the to_pandas() method. This makes it easier to perform various data filtering operations on the result set and it can also be used in Lambda functions. For example,
example_query = (
    "SELECT n.name AS name, n.age AS age "
    "WHERE (n)"
)
result_set = sample_graph.query_pgql(example_query)
result_df = result_set.to_pandas()

result_df['age_bin'] = result_df['age'].apply(lambda x: int(x)/20) # create age bins based on age ranges

Note:

To view the complete set of available Python APIs, see OPG4PY Python API Reference.

Embedded Server

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/*"
instance = graph_server.get_embedded_instance()
session = instance.create_session("python_pgx_client")
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 must see a similar message indicating a PGX session was created:

PgxSession(id: 32fc7037-18f1-4381-ba94-107e5f63aec2, name: python_pgx_client)

Note:

To view the complete set of available Python APIs, see OPG4PY Python API Reference.

Parent topic: Quick Starts for Using Property Graph Views

9.4 Oracle LiveLabs Workshops for Graphs

You can also explore Oracle Property Graph features using the graph workshops in Oracle LiveLabs.

See the Oracle LiveLabs Workshop for a complete example on querying, analyzing and visualizing graphs using data stored in a free tier Autonomous Database instance. You will provision a new free tier Autonomous Database instance, load data into it, create a graph, and then query, analyze and visualize the graph.

Parent topic: Quick Starts for Using Property Graph Views