16.2 Quick Start: Working with PGQL Property Graphs

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

The instructions assume that you have loaded the sample bank graph data provided with the graph server installation in the database tables (BANK_ACCOUNTS and BANK_TRANSFERS). See Using Sample Graph Data 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 26.3.0
    cd /opt/oracle/graph
./bin/opg4py --no_connect
Oracle Graph Server Shell 26.3.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;

// Get a jdbc connection
String jdbcUrl="jdbc:oracle:thin:@"+<host>+":"+<port>+"/"+<service>;
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 PGQL property graph using the CREATE PROPERTY GRAPH statement:
    opg4j> String pgql =
...> "CREATE PROPERTY GRAPH bank_graph "
...> + "VERTEX TABLES ( BANK_ACCOUNTS AS ACCOUNTS "
...> + "KEY (ID) "
...> + "LABEL ACCOUNTS "
...> + "PROPERTIES (ID, NAME) "
...> + ") "
...> + "EDGE TABLES ( BANK_TXNS AS TRANSFERS "
...> + "KEY (SRC_ACCT_ID, DST_ACCT_ID, AMOUNT) "
...> + "SOURCE KEY (SRC_ACCT_ID) REFERENCES ACCOUNTS (ID) "
...> + "DESTINATION KEY (DST_ACCT_ID) REFERENCES ACCOUNTS (ID) "
...> + "LABEL TRANSFERS "
...> + "PROPERTIES (SRC_ACCT_ID, DST_ACCT_ID, AMOUNT, DESCRIPTION) "
...> + ") OPTIONS (PG_PGQL) "
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 (SRC_ACCT_ID, DST_ACCT_ID, AMOUNT) " +
        "SOURCE KEY (SRC_ACCT_ID) REFERENCES ACCOUNTS (ID) " +
        "DESTINATION KEY (DST_ACCT_ID) REFERENCES ACCOUNTS (ID) " +
        "LABEL TRANSFERS " +
        "PROPERTIES (SRC_ACCT_ID, DST_ACCT_ID, AMOUNT, DESCRIPTION)" +
        ") OPTIONS(PG_PGQL)";

      pgqlStmt.execute(pgql);
    >>> pgql = """
...         CREATE PROPERTY GRAPH bank_graph
...         VERTEX TABLES (
...           BANK_ACCOUNTS
...           LABEL ACCOUNTS
...           PROPERTIES (ID, NAME)
...         )
...         EDGE TABLES (
...           BANK_TXNS
...             SOURCE KEY (SRC_ACCT_ID) REFERENCES BANK_ACCOUNTS (ID)
...             DESTINATION KEY (DST_ACCT_ID) REFERENCES BANK_ACCOUNTS (ID)
...             LABEL TRANSFERS
...             PROPERTIES (SRC_ACCT_ID, DST_ACCT_ID, AMOUNT, DESCRIPTION)
...         ) OPTIONS(PG_PGQL)
... """
>>> pgql_statement.execute(pgql)
False
    The graph gets created successfully.
  6. Execute the following query to retrieve the first 10 elements of the graph as shown:
    opg4j> String pgqlQuery =
...> "SELECT e.SRC_ACCT_ID, e.DST_ACCT_ID, e.amount FROM "
...> + "MATCH (n:ACCOUNTS) -[e:TRANSFERS]-> (m:ACCOUNTS) ON BANK_GRAPH "
...> + "LIMIT 10"
opg4j> var rs = pgqlStmt.executeQuery(pgqlQuery)
rs ==> oracle.pg.rdbms.pgql.pgview.PgViewResultSet@1e368085
opg4j> rs.print()
+------------------------------------+
| SRC_ACCT_ID | DST_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.src_acct_id, e.dst_acct_id, e.amount FROM " +
        "MATCH (n:ACCOUNTS) -[e:TRANSFERS]-> (m:ACCOUNTS) ON BANK_GRAPH " +
        "LIMIT 10";
PgqlResultSet rs = pgqlStmt.executeQuery(pgqlQuery);
rs.print();
    >>> pgql = """
... SELECT e.src_acct_id, e.dst_acct_id, e.amount FROM
... MATCH (n:ACCOUNTS) -[e:TRANSFERS]-> (m:ACCOUNTS) on BANK_GRAPH
... limit 10
... """
>>> pgql_statement.execute_query(pgql).print()
+------------------------------------+
| SRC_ACCT_ID | DST_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",GraphSource.PG_PGQL)
graph ==> PgxGraph[name=BANK_GRAPH,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",GraphSource.PG_PGQL);
    >>> instance = graph_server.get_instance("https://localhost:7007","<username>","<password>")
>>> session = instance.create_session("my_session")
>>> graph = session.read_graph_by_name('BANK_GRAPH', 'pg_pgql')
>>> graph
PgxGraph(name: BANK_GRAPH, 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]
    Analyst analyst = session.createAnalyst();
analyst.pagerank(graph);
    >>> analyst = session.create_analyst()
>>> analyst.pagerank(graph)
VertexProperty(name: pagerank, type: double, graph: BANK_GRAPH)
  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 ORDER BY a.pagerank DESC LIMIT 10"
opg4j> var result = session.queryPgql(pgql)
opg4j> result.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,numResults=10]

    String pgql = "SELECT a.id, a.pagerank FROM MATCH (a) ON BANK_GRAPH ORDER BY a.pagerank DESC LIMIT 10";
PgqlResultSet result = session.queryPgql(pgql);
result.print();
    >>> pgql = "SELECT a.id, a.pagerank FROM MATCH (a) ON BANK_GRAPH ORDER BY a.pagerank DESC LIMIT 10"
>>> result = session.query_pgql(pgql)
>>> result.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  |
+-----------------------------+
  10. Optionally, to persist the results of your analysis, you can convert the PgqlResultSet to a PgxFrame and store it in a database table as shown:
    opg4j> var rsFrame = result.toFrame()
opg4j> rsFrame.write().
...>     db().                         // select the format as relational database
...>     name("DSFrame").             // name of the frame
...>     tablename("data_frame_tbl").       // name of the table in which the data is stored
...>     overwrite(true).              // overwrite the table if it already exists
...>     connections(16).              // use 16 connections to store in parallel
...>     store()
    PgxFrame rsFrame = result.toFrame();
rsFrame.write()
    .db()                    // select the format as relational database
    .name("NewFrame")        // name of the frame
    .tablename("frame_tbl")  // name of the table in which the data is stored
    .overwrite(true)         // overwrite the table if it already exists
    .connections(16)         // use 16 connections to store in parallel
    .store();
    >>> rsFrame.to_frame()
>>> (rsFrame.write().
...     name("NewFrame").        # name of the frame
...     db().                    # select the format as relational database
...     table_name("frame_tbl"). # name of the table in which the data is stored
...     overwrite(True).         # overwrite the table if it already exists
...     connections(16).         # use 16 connections to store in parallel
...     store())

Parent topic: Quick Starts for Using PGQL Property Graphs