11 Property Graph Query Language (PGQL)

PGQL is a SQL-like query language for property graph data structures that consist of vertices that are connected to other vertices by edges, each of which can have key-value pairs (properties) associated with them.

The language is based on the concept of graph pattern matching, which allows you to specify patterns that are matched against vertices and edges in a data graph.

Note:

The graph server (PGX) 23.1.0 supports PGQL 1.5 and earlier versions.

The property graph support provides two ways to execute Property Graph Query Language (PGQL) queries through Java APIs:

• Use the oracle.pgx.api Java package to query an in-memory snapshot of a graph that has been loaded into the graph server (PGX), as described in Executing PGQL Queries Against the Graph Server (PGX).

• Use the oracle.pg.rdbms.pgql Java package to directly query graph data stored in Oracle Database. See Executing PGQL Queries Against Property Graph Views and Executing PGQL Queries Against SQL Property Graphs for more information.

For more information about PGQL, see the PGQL Specification.

• Creating a Property Graph Using PGQL
  
• Pattern Matching with PGQL
  
• Edge Patterns Have a Direction with PGQL
  
• Vertex and Edge Labels with PGQL
  
• Variable-Length Paths with PGQL
  
• Aggregation and Sorting with PGQL
  
• Executing PGQL Queries Against Property Graph Views
  This topic explains how you can execute PGQL queries directly against the property graph views on Oracle Database tables.

11.1 Creating a Property Graph Using PGQL

CREATE PROPERTY GRAPH is a PGQL DDL statement to create a property graph view (PG View) from the database tables.

The CREATE PROPERTY GRAPH statement starts with the name you give the graph, followed by a set of vertex tables and edge tables. The graph can have no vertex tables or edge tables (an empty graph), or vertex tables and no edge tables (a graph with only vertices and no edges), or both vertex tables and edge tables (a graph with vertices and edges). However, a graph cannot be specified with only edge tables and no vertex tables.

Consider the bank_accounts and bank_txns database tables created using the sample graph data in opt/oracle/graph/data directory. See Importing Data from CSV Files for more information.

• BANK_ACCOUNTS is a table with columns id, name. A row is added into this table for every new account.
• BANK_TXNS is a table with columns txn_id, from_acct_id, to_acct_id, description, and amount. A row is added into this table for every new transaction from from_acct_id to to_acct_id.

You can create a PG View using the database tables as shown:

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 (txn_id)
         SOURCE KEY (from_acct_id) REFERENCES accounts (id)
         DESTINATION KEY (to_acct_id) REFERENCES accounts (id)
         PROPERTIES (description, amount)
     ) OPTIONS (PG_VIEW)

The following graph concepts are explained by mapping the database tables to the graph and using the preceding PGQL DDL statement:

• Vertex tables: A table that contains data entities is a vertex table (for example, bank_accounts).
  • Each row in the vertex table is a vertex.
  • The columns in the vertex table are properties of the vertex.
  • The name of the vertex table is the default label for this set of vertices. Alternatively, you can specify a label name as part of the CREATE PROPERTY GRAPH statement.
• Edge tables: An edge table can be any table that links two vertex tables, or a table that has data that indicates an action from a source entity to a target entity. For example, transfer of money from FROM_ACCOUNT_ID to TO_ACCOUNT_ID is a natural edge.
  • Foreign key relationships can give guidance on what links are relevant in your data. CREATE PROPERTY GRAPH will default to using foreign key relationships to identify edges.
  • Some of the properties of an edge table can be the properties of the edge. For example, an edge from from_acct_id to to_acct_id can have properties description and amount.
  • The name of an edge table is the default label for the set of edges. Alternatively, you can specify a label name as part of the CREATE PROPERTY GRAPH statement.
• Keys:
  • Keys in a vertex table: The key of a vertex table identifies a unique vertex in the graph. The key can be specified in the CREATE PROPERTY GRAPH statement; otherwise, it defaults to the primary key of the table. If there are duplicate rows in the table, the CREATE PROPERTY GRAPH statement will return an error.
  • Key in an edge table: The key of an edge table uniquely identifies an edge in the graph. The KEY clause when specifying source and destination vertices uniquely identifies the source and destination vertex keys.
• Table aliases: Vertex and edge tables must have unique names. If you need to identify multiple vertex tables from the same relational table, or multiple edge tables from the same relational table, you must use aliases. For example, you can create two vertex tables bank_accounts and accounts from one table bank_accounts, as shown:

  CREATE PROPERTY GRAPH bank_transfers
       VERTEX TABLES (bank_accounts KEY(id)
                      bank_accounts AS accounts KEY(id))
  

  In case any of your vertex and edge table share the same name, then you must again use a table alias. In the following example, table alias is used for the edge table, DEPARTMENTS, as there is a vertex table referenced with the same name:

  CREATE PROPERTY GRAPH hr
  VERTEX TABLES (
    employees KEY(employee_id)
      PROPERTIES ARE ALL COLUMNS,
    departments KEY(department_id)
      PROPERTIES ARE ALL COLUMNS
   )
  EDGE TABLES (   
    departments AS managed_by
      SOURCE KEY ( department_id ) REFERENCES departments ( department_id )
      DESTINATION employees
      PROPERTIES ARE ALL COLUMNS
   )OPTIONS (PG_VIEW)

• Properties: The vertex and edge properties of a graph are derived from the columns of the vertex and edge tables respectively and by default have the same name as the underlying table columns. However, you can choose a different property name for each column. This helps to avoid conflicts when two tables have the same column name but with different data types.

  In the following example, the vertex properties id and name are renamed to acct_no and acct_name respectively:

  CREATE PROPERTY GRAPH bank_transfers
  VERTEX TABLES (
    bank_accounts AS accounts
      LABEL accounts  
      PROPERTIES (id AS acct_no, name AS acct_name)
  )

• REFERENCES clause: This connects the source and destination vertices of an edge to the corresponding vertex tables.

For more details on the CREATE PROPERTY GRAPH statement, see the PGQL Specification.

Refer to the following table for creating a property graph:

Table 11-1 CREATE PROPERTY GRAPH Statement Support

Method	More Information
Create a property graph in the graph server (PGX) using the oracle.pgx.api Java package	Java APIs for Executing CREATE PROPERTY GRAPH Statements
Create a property graph in the graph server (PGX) using the pypgx.api Python package	Python APIs for Executing CREATE PROPERTY GRAPH Statements
Create a property graph view on Oracle Database tables	Creating a Property Graph View

11.2 Pattern Matching with PGQL

Pattern matching is done by specifying one or more path patterns in the MATCH clause. A single path pattern matches a linear path of vertices and edges, while more complex patterns can be matched by combining multiple path patterns, separated by comma. Value expressions (similar to their SQL equivalents) are specified in the WHERE clause and let you filter out matches, typically by specifying constraints on the properties of the vertices and edges

For example, assume a graph of TCP/IP connections on a computer network, and you want to detect cases where someone logged into one machine, from there into another, and from there into yet another. You would query for that pattern like this:

SELECT id(host1) AS id1, id(host2) AS id2, id(host3) AS id3         /* choose what to return */
FROM MATCH
    (host1) -[connection1]-> (host2) -[connection2]-> (host3)       /* single linear path pattern to match */
WHERE
    connection1.toPort = 22 AND connection1.opened = true AND
    connection2.toPort = 22 AND connection2.opened = true AND
    connection1.bytes > 300 AND                                     /* meaningful amount of data was exchanged */
    connection2.bytes > 300 AND
    connection1.start < connection2.start AND                       /* second connection within time-frame of first */
    connection2.start + connection2.duration < connection1.start + connection1.duration
GROUP BY id1, id2, id3                                              /* aggregate multiple matching connections */ 

For more examples of pattern matching, see the Writing simple queries section in the PGQL specification.

11.3 Edge Patterns Have a Direction with PGQL

An edge pattern has a direction, as edges in graphs do. Thus, (a) <-[]- (b) specifies a case where b has an edge pointing at a, whereas (a) -[]-> (b) looks for an edge in the opposite direction.

The following example finds common friends of April and Chris who are older than both of them.

SELECT friend.name, friend.dob
FROM MATCH                  /* note the arrow directions below */
  (p1:person) -[:likes]-> (friend) <-[:likes]- (p2:person)
WHERE
  p1.name = 'April' AND p2.name ='Chris' AND
  friend.dob > p1.dob AND friend.dob > p2.dob
ORDER BY friend.dob DESC 

For more examples of edge patterns, see the Edge Patterns section in the PGQL specification.

11.4 Vertex and Edge Labels with PGQL

Labels are a way of attaching type information to edges and nodes in a graph, and can be used in constraints in graphs where not all nodes represent the same thing. For example:

SELECT p.name
FROM MATCH (p:person) -[e1:likes]-> (m1:movie),
     MATCH (p) -[e2:likes]-> (m2:movie)
WHERE m1.title = 'Star Wars'
  AND m2.title = 'Avatar'

The example queries a graph which contains a set of vertices with the label person, a set of vertices with the label movie, and a set of edges with the label likes. A label predicate can start with either a colon (:) or the keyword IS followed by one or more labels. If more than one label is used, then the labels are separated by a vertical bar (|).

The following query shows the preceding example query with the keyword IS for the label predicate:

SELECT p.name
FROM MATCH (p IS person) -[e1 IS likes]-> (m1 IS movie),
     MATCH (p IS person) -[e2 IS likes]-> (m2 IS movie)
WHERE m1.title = 'Star Wars'
  AND m2.title = 'Avatar'

See Also:

• Label Expressions section in the PGQL specification
• Label Predicates section in the PGQL specification

11.5 Variable-Length Paths with PGQL

Variable-length path patterns have a quantifier like * to match a variable number of vertices and edges. Using a PATH macro, you can specify a named path pattern at the start of a query that can be embedded into the MATCH clause any number of times, by referencing its name. The following example finds all of the common ancestors of Mario and Luigi.

PATH has_parent AS () -[:has_father|has_mother]-> ()
SELECT ancestor.name
FROM MATCH (p1:Person) -/:has_parent*/-> (ancestor:Person)
   , MATCH (p2:Person) -/:has_parent*/-> (ancestor)
WHERE
  p1.name = 'Mario' AND
  p2.name = 'Luigi'

The preceding path specification also shows the use of anonymous constraints, because there is no need to define names for intermediate edges or nodes that will not be used in additional constraints or query results. Anonymous elements can have constraints, such as [:has_father|has_mother] -- the edge does not get a variable name (because it will not be referenced elsewhere), but it is constrained.

For more examples of variable-length path pattern matching, see the Variable-Length Paths section in the PGQL specification.

11.6 Aggregation and Sorting with PGQL

Like SQL, PGQL has support for the following:

• GROUP BY to create groups of solutions

• MIN, MAX, SUM, and AVG aggregations

• ORDER BY to sort results

And for many other familiar SQL constructs.

See Also:

• See Grouping and Aggregation for more information on GROUP BY
• See Sorting and Row Limiting for more information on ORDER BY

11.7 Executing PGQL Queries Against Property Graph Views

This topic explains how you can execute PGQL queries directly against the property graph views on Oracle Database tables.

The PGQL query execution flow is shown in the following figure.

Figure 11-1 PGQL on Property Graph Views in Oracle Database

Description of "Figure 11-1 PGQL on Property Graph Views in Oracle Database"

The basic execution flow is:

1. The PGQL query is submitted to PGQL on RDBMS through a Java API.
2. The PGQL query is translated into SQL statements using the internal metadata tables for property graph views.
3. The translated SQL is submitted to Oracle Database by JDBC.
4. The SQL result set is wrapped as a PGQL result set and returned to the caller.

• Supported PGQL Features and Limitations for PG Views
  Learn about the supported PGQL features and limitations for property graph views (PG Views).
• Performance Considerations for PGQL Queries
  
• Using the Java and Python APIs to Run PGQL Queries
  

11.7.1 Supported PGQL Features and Limitations for PG Views

Learn about the supported PGQL features and limitations for property graph views (PG Views).

The following table describes the complete list of supported and unsupported PGQL features for PG Views:

Table 11-2 Supported PGQL Functionalities and Limitations for PG Views

Feature	PGQL on PG Views
CREATE PROPERTY GRAPH	Supported
DROP PROPERTY GRAPH	Supported
Fixed-length pattern matching	Supported
Variable-length pattern matching goals	Supported: Reachability ANY ANY SHORTEST TOP k SHORTEST ALL Limitations: ALL SHORTEST ANY CHEAPEST TOP k CHEAPEST
Variable-length pattern matching quantifiers	Supported: * + ? { n } { n, } { n, m } { , m }
Variable-length path unnesting	Not supported
GROUP BY	Supported
HAVING	Supported
Aggregations	Supported: COUNT MIN, MAX, AVG, SUM LISTAGG Limitations: ARRAY_AGG
DISTINCT SELECT DISTINCT Aggregation with DISTINCT (such as, COUNT(DISTINCT e.prop))	Supported
SELECT v.*	Supported
ORDER BY (+ASC/DESC), LIMIT, OFFSET	Supported
Data Types	All available Oracle RDBMS data types supported
JSON	Supported: JSON storage: JSON strings (VARCHAR2) JSON objects JSON functions: Any JSON function call that follows the syntax, json_function_name(arg1, arg2,…). For example: json_value(department_data, '$.department') Limitations: Simple Dot Notation Any optional clause in a JSON function call (such as RETURNING, ERROR, and so on) is not supported. For example: json_value(department_data, '$.employees[1].hireDate' RETURNING DATE)
Operators	Supported: Relational: +, -, *, /, %, - (unary minus) Arithmetic: =, <>, <, >, <=, >= Logical: AND, OR, NOT String: || (concat)
Functions and predicates	Supported are all available functions in the Oracle RDBMS that take the form function_name(arg1, arg2, ...) with optional schema and package qualifiers. Supported PGQL functions/predicates: IS NULL, IS NOT NULL JAVA_REGEXP_LIKE (based on CONTAINS) LOWER, UPPER SUBSTRING ABS, CEIL/CEILING, FLOOR, ROUND EXTRACT ID LABEL, HAS_LABEL ALL_DIFFERENT CAST CASE IN and NOT IN Limitations: LABELS IN_DEGREE, OUT_DEGREE
User-defined functions	Supported: PL/SQL functions Functions created via the Oracle Database Multilingual Engine (MLE)
Subqueries: Scalar subqueries EXISTS and NOT EXISTS subqueries	Supported
INSERT/UPDATE/DELETE	Supported
INTERVAL literals and operations	Not supported

• Additional Information on Supported PGQL Features with Examples
  

11.7.1.1 Additional Information on Supported PGQL Features with Examples

The following PGQL features are supported in property graph views (PG Views):

• Recursive queries are supported for the following variable-length path finding goals:
  • Reachability
  • ANY
  • ANY SHORTEST
  • TOP k SHORTEST
• Recursive queries are supported for the following horizontal aggregations:
  • LISTAGG

    SELECT LISTAGG(src.first_name || ' ' || src.last_name, ',')
    FROM MATCH TOP 2 SHORTEST ( (n:Person) ((src)-[e:knows]->)* (m:Person) )
    WHERE n.id = 1234

  • SUM

    SELECT SUM(e.weight + 3)
    FROM MATCH TOP 2 SHORTEST ( (n:Person) -[e:knows]->* (m:Person) )
    WHERE n.id = 1234

  • COUNT

    SELECT COUNT(e)
    FROM MATCH TOP 2 SHORTEST ( (n:Person) -[e:knows]->* (m:Person) )
    WHERE n.id = 1234

  • AVG

    SELECT AVG(dst.age)
    FROM MATCH TOP 2 SHORTEST ( (n:Person) (-[e:knows]->(dst))* (m:Person) )
    WHERE n.id = 1234

  • MIN (Only for property value or CAST expressions)

    SELECT MIN(CAST(dst.age + 5 AS INTEGER))
    FROM MATCH TOP 2 SHORTEST ( (n:Person) (-[e:knows]->(dst))* (m:Person) )
    WHERE n.id = 1234

  • MAX (Only for property value or CAST expressions)

    SELECT MAX(dst.birthday)
    FROM MATCH TOP 2 SHORTEST ( (n:Person) (-[e:knows]->(dst))* (m:Person) )
    WHERE n.id = 1234

• The following quantifiers are supported in recursive queries:

  Table 11-3 Supported Quantifiers in PGQL SELECT Queries

  Syntax	Description
  *	zero or more
  +	one or more
  ?	zero or one
  {n}	exactly n
  {n,}	n or more
  {n,m}	between n and m (inclusive)
  {,m}	between zero and m (inclusive)

• Data type casting with precision and scale is supported:

  SELECT CAST(v.id AS VARCHAR2(10)) || '→' || CAST(w.id AS VARCHAR2(10)) AS friendOf
  FROM MATCH (v) -[:friendOf]->(w)

  SELECT CAST(e.mval AS NUMBER(5,2)) AS mval
  FROM MATCH () -[e:knows]->()
  WHERE e.mval = '342.5'

• Both built-in Oracle Database functions and user defined functions (UDFs) are supported.

  For example:

  • Assuming a table has a JSON column with values such as, {"name":"John", "age": 43}:

    SELECT JSON_VALUE(p.attributes, '$.name') AS name
    FROM MATCH (p:Person)
    WHERE JSON_VALUE(p.attributes, '$.age') > 35

  • Assuming an Oracle Text index exists on a text column in a table:

    SELECT n.text
    FROM MATCH (n)
    WHERE CONTAINS(n.text, 'cat', 1) > 0

  • Assuming a UDF updated_id is registered with the graph server (PGX):

    SELECT my.updated_id(n.ID) FROM MATCH(n) LIMIT 10

• Selecting all properties of vertices or edges is supported through SELECT v.* clause, where v is the variable whose properties are selected. The following example retrieves all the edge properties of a graph:

  SELECT label(e), e.* FROM MATCH (n)-[e]->(m) ON bank_graph_view LIMIT 3

  On execution, the preceding query retrieves all the properties that are bound to the variable e as shown:

  +--------------------------------------------------------------+
  | label(e)  | AMOUNT | DESCRIPTION | FROM_ACCT_ID | TO_ACCT_ID |
  +--------------------------------------------------------------+
  | TRANSFERS | 1000   | transfer    | 178          | 921        |
  | TRANSFERS | 1000   | transfer    | 178          | 462        |
  | TRANSFERS | 1000   | transfer    | 179          | 688        |
  +--------------------------------------------------------------+
  

  A PREFIX can be specified to avoid duplicate column names in cases where you select all properties using multiple variables. For example:

  SELECT n.* PREFIX 'n_', e.* PREFIX 'e_', m.* PREFIX 'm_' 
  FROM MATCH (n:Accounts) -[e:transfers]-> (m:Accounts) 
  ON bank_graph_view LIMIT 3

  The query output is as follows:

  +--------------------------------------------------------------------------------------------+
  | n_ID | n_NAME  | e_AMOUNT | e_DESCRIPTION | e_FROM_ACCT_ID | e_TO_ACCT_ID | m_ID | m_NAME  |
  +--------------------------------------------------------------------------------------------+
  | 178  | Account | 1000     | transfer      | 178            | 921          | 921  | Account |
  | 178  | Account | 1000     | transfer      | 178            | 462          | 462  | Account |
  | 179  | Account | 1000     | transfer      | 179            | 688          | 688  | Account |
  +--------------------------------------------------------------------------------------------+
  

  Label expressions can be used such that only properties that belong to the specified vertex or edge labels are selected:

  SELECT LABEL(n), n.* FROM MATCH (n:Accounts) ON bank_graph_view LIMIT 3

  The preceding query output is as shown:

  +-----------------------+
  | LABEL(n) | ID | NAME  |
  +-----------------------+
  | ACCOUNTS | 1  | User1 |
  | ACCOUNTS | 2  | User2 |
  | ACCOUNTS | 3  | User3 |
  +-----------------------+
  

• Support for ALL path finding goal to return all the paths between a pair of vertices. However, to avoid endless cycling, only the following quantifiers are supported:
  • ?
  • {n}
  • {n.m}
  • {,m}

  For example, the following PGQL query finds all the transaction paths from account 284 to account 616 :

  SELECT LISTAGG(e.amount, ' + ') || ' = ', SUM(e.amount) AS total_amount
  FROM MATCH ALL (a:Accounts) -[e:Transfers]->{1,4}(b:Accounts)
  WHERE a.id = 284 AND b.id = 616
  ORDER BY total_amount

  On execution, the query produces the following result:

  +--------------------------------------------------+
  | LISTAGG(e.amount, ' + ') || ' = ' | TOTAL_AMOUNT |
  +--------------------------------------------------+
  | 1000 + 1000 + 1000 =              | 3000         |
  | 1000 + 1500 + 1000 =              | 3500         |
  | 1000 + 1000 + 1000 + 1000 =       | 4000         |
  +--------------------------------------------------+
  $16 ==> oracle.pg.rdbms.pgql.pgview.PgViewResultSet@4f38acf
  

• Scalar subqueries which return exactly one column and one row is supported.

  For example:

  SELECT p.name AS name , (
    SELECT SUM(t.amount)      
    FROM MATCH (a) <-[t:transaction]- (:Account)  
  ) AS sum_incoming , (
    SELECT SUM(t.amount)      
    FROM MATCH (a) -[t:transaction]-> (:Account)  
  ) AS sum_outgoing , (
    SELECT COUNT(DISTINCT p2)      
    FROM MATCH (a) -[t:transaction]- (:Account) -[:owner]-> (p2:Person)    
    WHERE p2 <> p  
  ) AS num_persons_transacted_with , (
    SELECT COUNT(DISTINCT c)      
    FROM MATCH (a) -[t:transaction]- (:Account) -[:owner]-> (c:Company)  
  ) AS num_companies_transacted_with   
  FROM MATCH (p:Person) <-[:owner]- (a:Account)
  ORDER BY sum_outgoing + sum_incoming DESC

• EXISTS and NOT EXISTS subqueries are supported. Such queries yield TRUE or FALSE depending on whether the query produces at least one results given the bindings of the outer query.

  For example:

  SELECT fof.name, COUNT(friend) AS num_common_friends 
  FROM MATCH (p:Person) -[:knows]-> (friend:Person) -[:knows]-> (fof:Person)
  WHERE NOT EXISTS (   
    SELECT * FROM MATCH (p) -[:knows]-> (fof) 
  )

The following are a few PGQL features which are not supported:

• The following PGQL SELECT features are not supported:
  • Use of bind variables in path expressions.

    If you attempt to use a bind variable, it will result in an error as shown:

    opg4j> String s = "SELECT id(a) FROM MATCH ANY SHORTEST (a) -[e]->* (b) WHERE id(a) = ?";
    s ==> "SELECT id(a) FROM MATCH ANY SHORTEST (a) -[e]->* (b) WHERE id(a) = ?"
     
    opg4j> PgqlPreparedStatement ps = pgqlConn.prepareStatement(s);
    ps ==> oracle.pg.rdbms.pgql.PgqlExecution@7806db3f
     
    opg4j> ps.setString(1, "PERSON(3)");
     
    opg4j> ps.executeQuery();
    |  Exception java.lang.UnsupportedOperationException: Use of bind variables for path queries is not supported

  • in_degree and out_degree functions.

Note:

• See Supported PGQL Features and Limitations for PG Views for a complete list of supported and unsupported PGQL features for PG Views.
• See Performance Considerations for PGQL Queries for details on recommended practices to enhance query performance for recursive queries.

11.7.2 Performance Considerations for PGQL Queries

The following are some recommended practices for query performance.

• Recursive Queries
• Using Query Optimizer Hints
• Speed Up Query Translation Using Graph Metadata Cache and Translation Cache

Recursive Queries

The following indexes are recommended in order to speed up execution of recursive queries:

• For underlying VERTEX tables of the recursive pattern, an index on the key column
• For underlying EDGE tables of the recursive pattern, an index on the source key column

  Note:

  You can also create index on (source key, destination key).

For example, consider the following CREATE PROPERTY GRAPH statement:

CREATE PROPERTY GRAPH people
  VERTEX TABLES(
    person
      KEY ( id )
      LABEL person
      PROPERTIES( name, age )
  )
  EDGE TABLES(
    knows
      key (person1, person2)
      SOURCE KEY ( person1 ) REFERENCES person (id)
      DESTINATION KEY ( person2 ) REFERENCES person (id)
      NO PROPERTIES
  )
  OPTIONS ( PG_VIEW )

And also consider the following query:

SELECT COUNT(*)
FROM MATCH ANY SHORTEST ( (n:Person) -[e:knows]->* (m:Person) )
WHERE n.id = 1234

In order to improve performance of the recursive part of the preceding query, the following indexes must exist:

• CREATE INDEX <INDEX_NAME> ON PERSON(ID)
• CREATE INDEX <INDEX_NAME> ON KNOWS(PERSON1) or

  CREATE INDEX <INDEX_NAME> ON KNOWS(PERSON1, PERSON2)

Composite Vertex Keys

For composite vertex keys, query execution can be optimized with the creation of function-base indexes on the key columns:

• For underlying VERTEX tables of the recursive pattern, a function-based index on the comma-separated concatenation of key columns
• For underlying EDGE tables of the recursive pattern, a function-based index on the comma-separated concatenation of source key columns

  Note:

  You can also create index on (source key columns, destination key columns).

For example, consider the following CREATE PROPERTY GRAPH statement:

CREATE PROPERTY GRAPH people
  VERTEX TABLES(
    person
      KEY ( id1, id2 )
      LABEL person
      PROPERTIES( name, age )
  )
  EDGE TABLES(
    knows
      key (id)
      SOURCE KEY ( id1person1, id2person1 ) REFERENCES person (id1,id2)
      DESTINATION KEY ( id1person2, id2person2 ) REFERENCES person (id1,id2)
      NO PROPERTIES
  )
  OPTIONS ( PG_VIEW )

And also consider the following query:

SELECT COUNT(*)
FROM MATCH ANY SHORTEST ( (n:Person) -[e:knows]->* (m:Person) )
WHERE n.id = 1234

In order to improve performance of the recursive part of the preceding query, the following indexes must exist:

• CREATE INDEX <INDEX_NAME> ON PERSON (ID1 || ',' || ID2)
• CREATE INDEX <INDEX_NAME> ON KNOWS (ID1PERSON1 || ',' || ID2PERSON1) or

  CREATE INDEX <INDEX_NAME> ON KNOWS (ID1PERSON1 || ',' || ID2PERSON1, ID1PERSON2 || ',' || ID2PERSON2)

If some of the columns in a composite vertex key is a string column, the column needs to be comma-escaped in the function-base index creation.

For example, if column ID1 in table PERSON of the preceding example is of type VARCHAR2(10), you need to escape the comma for the column as follows:

replace(ID1, ',', '\,')

So, the indexes to improve performance will result as shown:

• CREATE INDEX <INDEX_NAME> ON PERSON (replace(ID1, ',', '\,') || ',' || ID2)
• CREATE INDEX <INDEX_NAME> ON KNOWS (replace(ID1PERSON1, ',', '\,') || ',' || ID2PERSON1)

Using Query Optimizer Hints

The following hints can be used to influence translation of PGQL variable-length path patterns to SQL:

• REVERSE_PATH: Switches on or off the reverse path optimization (ON by default). If ON, it automatically determines if the pattern can best be evaluated from source to destination or from destination to source, based on specified filter predicates.
• PUSH_SRC_HOPS: Switches on or off pushing source filter optimization (ON by default). If ON, then filter predicates are used to limit the number of source vertices (or destination vertices if path evaluation is reversed) and thereby the search space of variable-length path pattern evaluations.
• PUSH_DST_HOPS: Switches on or off pushing destination filter optimization (OFF by default). If ON, then filter predicates are used to limit the number of destination vertices (or source vertices if path evaluation is reversed) and thereby the search space of variable-length path pattern evaluations.

The preceding hints can be configured as options parameter in the following Java API methods:

• executeQuery(String pgql, String options)
• translateQuery(String pgql, String options)
• execute(String pgql, String matchOptions, String options)

For example, consider the following PGQL query:

SELECT v1.name AS v1, v2.name AS v2, v3.name As v3 
FROM MATCH (v1:Person)-[e1:friendOf]->(v2:Person), 
MATCH ANY (v2:Person)-[e2:friendOf]->*(v3:Person) 
WHERE v1.name= 'Bob'

When the preceding query is executed using the default option for PUSH_SRC_HOPS, the output for start_nodes_translation displays the filter expression as shown:

System.out.println(pgqlStatement.translateQuery(pgql).getSqlTranslation())
...
...
start_nodes_translation => (to_clob('SELECT ''PERSONS'' AS "src_table", e1.person_b AS "src_key"
FROM "GRAPHUSER"."PERSONS" "V1", "GRAPHUSER"."FRIENDSHIPS" "E1"
WHERE (((e1.person_a = v1.person_id) AND NOT(e1.person_b IS NULL)) AND (v1.name = ''Bob''))')),
     end_nodes_translation => (to_clob('SELECT ''PERSONS'' AS "dst_table", v3.person_id AS "dst_key"
FROM "GRAPHUSER"."PERSONS" "V3"')),
...
...

If the preceding query is executed with the hint PUSH_SRC_HOPS=F, then the query is translated into SQL as shown:

System.out.println(pgqlStatement.translateQuery(pgql,"PUSH_SRC_HOPS=F").getSqlTranslation())

...
...start_nodes_translation => (to_clob('SELECT ''PERSONS'' AS "src_table", v2.person_id AS "src_key"
FROM "GRAPHUSER"."PERSONS" "V2"')),
     end_nodes_translation => (to_clob('SELECT ''PERSONS'' AS "dst_table", v3.person_id AS "dst_key"
FROM "GRAPHUSER"."PERSONS" "V3"')),
...
...

Speed Up Query Translation Using Graph Metadata Cache and Translation Cache

The following global caches help to speed up PGQL query translation:

• Graph Metadata Cache: Stores graph metadata such as tables, labels, properties, and so on.
• Translation Cache: Stores PGQL to SQL translation.

You can configure the caches using the following Java APIs:

• clearTranslationCache()
• disableTranslationCache()
• enableTranslationCache()
• setTranslationCacheMaxCapacity(int maxCapacity)
• clearGraphMetadataCache()
• disableGraphMetadataCache()
• enableGraphMetadataCache()
• setGraphMetadataCacheMaxCapacity(int maxCapacity)

These preceding methods are part of the PgqlConnection class. Separate caches are maintained for each database user such that cached objects are shared between different PgqlConnection objects if they have the same connection URL and user underneath.

11.7.3 Using the Java and Python APIs to Run PGQL Queries

You can run PGQL queries using the Java API in the oracle.pg.rdbms.pgql package. Also, you can use the Python OPG4Py package for executing PGQL queries against the graph data in the Oracle Database. This package contains a sub-package Pgql with one or more modules that wraps around the Java API in the oracle.pg.rdbms.pgql package.

• Creating a Property Graph View
  
• Executing PGQL SELECT Queries
  
• Executing PGQL Queries Using JDBC Driver
  
• Executing PGQL Queries to Modify Property Graph Views
  
• Dropping A Property Graph View
  

11.7.3.1 Creating a Property Graph View

You can create a property graph view (PG View) using the CREATE PROPERTY GRAPH statement.

Example 11-1 Creating a Property Graph View

The following example describes the creation of a PG View.

• JShell
• Java
• Python

opg4j> var jdbcUrl="jdbc:oracle:thin:@<host_name>:<port>/<db_service>"
opg4j> var conn = DriverManager.getConnection(jdbcUrl,"<username>","<password>");
opg4j> var pgqlConn = PgqlConnection.getConnection(conn)
opg4j> var pgqlStmt = pgqlConn.createStatement() //create a PGQL Statement
opg4j> conn.setAutoCommit(false)
opg4j> var 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 (txn_id) "
...> + "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)

import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import oracle.pg.rdbms.pgql.jdbc.PgqlJdbcRdbmsDriver;
import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlStatement;


/*
 * This example shows how to create a property graph view.
 */
public class PgqlCreate
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String jdbcUrl            = args[idx++];
    String username           = args[idx++];
    String password           = args[idx++];
    String graph              = args[idx++];


    Connection conn = null;
    PgqlStatement pgqlStmt = null;

    try {
      //Get a jdbc connection
      DriverManager.registerDriver(new PgqlJdbcRdbmsDriver());
      conn = DriverManager.getConnection(jdbcUrl, username, password);
      conn.setAutoCommit(false);


      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);

      // Create a PGQL Statement
      pgqlStmt = pgqlConn.createStatement();
      // Execute PGQL Query
      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 (txn_id) " +
        "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) ";

      // Print the results
      pgqlStmt.execute(pgql);
    }
    finally {
      // close the statement
      if (pgqlStmt != null) {
         pgqlStmt.close();
         }
      // close the connection
      if (conn != null) {
         conn.close();
         }
      }
  }
}

>>> pgql_conn = opg4py.pgql.get_connection("<username>","<password>", "jdbc:oracle:thin:@localhost:1521/orclpdb")
>>> 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
...             KEY (txn_id)
...             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)
False

You can verify the property graph view creation by checking the metadata tables that get created in the database.

11.7.3.2 Executing PGQL SELECT Queries

You can run PGQL SELECT queries as described in the following examples.

Example 11-2 Running a Simple SELECT Query Using PgqlStatement and PgqlResultSet

In the following example, PgqlConnection is used to obtain a PgqlStatement. Then, it calls the executeQuery method of PgqlStatement, which returns a PgqlResultSet object. PgqlResultSet provides a print() method, which displays results in a tabular mode.

• JShell
• Java
• Python

opg4j> var jdbcUrl="jdbc:oracle:thin:@<host_name>:<port>/<db_service>"
opg4j> var conn = DriverManager.getConnection(jdbcUrl,"<username>","<password>");
opg4j> var pgqlConn = PgqlConnection.getConnection(conn)
opg4j> pgqlConn.setGraph("BANK_GRAPH")        
opg4j> var pgqlStmt = pgqlConn.createStatement() //create a PGQL Statement
opg4j> String s = "SELECT n.* FROM MATCH (n:Accounts) LIMIT 3"
opg4j> var resultSet = pgqlStmt.executeQuery(s)
opg4j> resultSet.print() //Prints the query result set
+---------------+
| ID | NAME     |
+---------------+
| 1  | Account1 |
| 2  | Account2 |
| 3  | Account3 |
+---------------+

import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import oracle.pg.rdbms.pgql.jdbc.PgqlJdbcRdbmsDriver;
import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.pg.rdbms.pgql.PgqlStatement;

/*
 * This example shows how to execute a SELECT query on a property graph view.
 */
public class PgqlExample1
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String jdbcUrl            = args[idx++];
    String username           = args[idx++];
    String password           = args[idx++];
    String graph              = args[idx++];


    Connection conn = null;
    PgqlStatement pgqlStmt = null;
    PgqlResultSet rs = null;

    try {
      //Get a jdbc connection
      DriverManager.registerDriver(new PgqlJdbcRdbmsDriver());
      conn = DriverManager.getConnection(jdbcUrl, username, password);
      conn.setAutoCommit(false);


      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PGQL Statement
      pgqlStmt = pgqlConn.createStatement();

      // Execute PGQL Query
      String query = "SELECT n.* FROM MATCH (n:Accounts) LIMIT 5";
      rs = pgqlStmt.executeQuery(query);

      // Print the results
      rs.print();
    }
    finally {
      // close the result set
      if (rs != null) {
         rs.close();
         }
      // close the statement
      if (pgqlStmt != null) {
         pgqlStmt.close();
         }
      // close the connection
      if (conn != null) {
         conn.close();
         }
      }
  }
}

>>> pgql_conn = opg4py.pgql.get_connection("<username>","<password>", "<jdbcUrl>")
>>> pgql_statement = pgql_conn.create_statement()
>>> pgql_conn.set_graph("BANK_GRAPH")
>>> s = "SELECT n.* FROM MATCH (n:Accounts) LIMIT 3"
>>> pgql_statement.execute_query(s)
>>> pgql_result_set = pgql_statement.execute_query(s)
>>> pgql_result_set.print()
+---------------+
| ID | NAME     |
+---------------+
| 1  | Account1 |
| 2  | Account2 |
| 3  | Account3 |
+---------------+
>>> pgql_result_set
PgqlResultSet(java_pgql_result_set: oracle.pg.rdbms.pgql.PgqlResultSet, # of results: 3)

Also, you can convert the PGQL result set obtained in the preceding code to a Pandas dataframe using the to_pandas() method.

Note:

The pandas package must be installed in your system to successfully execute the call to to_pandas(). This package is automatically installed at the time of the Python client installation for versions Python 3.8 and Python 3.9. However, if your call to to_pandas() fails, verify if the pandas module is installed in your system. In case the module is found missing or your Python version differs from the earlier mentioned versions, then install the pandas package manually.

Example 11-3 Running a SELECT Query Using PgqlPreparedStatement

• JShell
• Java
• Python

opg4j> var jdbcUrl="jdbc:oracle:thin:@<host_name>:<port>/<db_service>"
opg4j> var conn = DriverManager.getConnection(jdbcUrl,"<username>","<password>");
opg4j> var pgqlConn = PgqlConnection.getConnection(conn)
opg4j> pgqlConn.setGraph("BANK_GRAPH");         
opg4j> String s = "SELECT n.* FROM MATCH (n:Accounts) LIMIT ?"
opg4j> var ps = pgqlConn.prepareStatement(s, 0 /* timeout */, 4 /* parallel */, 2 /* dynamic sampling */, -1 /* max results */, null /* match options */, null /* options */)
opg4j> ps.setInt(1, 3)
opg4j> var rs = ps.executeQuery()
opg4j> rs.print() //Prints the query result set
+---------------+
| ID | NAME     |
+---------------+
| 1  | Account1 |
| 2  | Account2 |
| 3  | Account3 |
+---------------+

import java.sql.Statement;
import java.sql.DriverManager;
import oracle.pg.rdbms.pgql.jdbc.PgqlJdbcRdbmsDriver;
import oracle.pg.rdbms.pgql.*; 

public class PgqlExample2
{
  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String jdbcUrl            = args[idx++];
    String username           = args[idx++];
    String password           = args[idx++];
    String graph              = args[idx++];


    Connection conn = null;
    PgqlStatement pgqlStmt = null;
    PgqlResultSet rs = null;

    try {
      //Get a jdbc connection
      DriverManager.registerDriver(new PgqlJdbcRdbmsDriver());
      conn = DriverManager.getConnection(jdbcUrl, username, password);
      conn.setAutoCommit(false);


      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);


      // Execute PGQL Query
      String s = "SELECT n.* FROM MATCH (n:Accounts) LIMIT ?";
      PgqlPreparedStatement pStmt = pgqlConn.prepareStatement(s, 0, 4 , 2 , -1 , null , null);
      pStmt.setInt(1,3);
      rs = pStmt.executeQuery();

      // Print the results
      rs.print();
    }
    finally {
      // close the result set
      if (rs != null) {
         rs.close();
         }
      // close the statement
      if (pgqlStmt != null) {
         pgqlStmt.close();
         }
      // close the connection
      if (conn != null) {
         conn.close();
         }
      }
  }
}

>>> pgql_conn = opg4py.pgql.get_connection("<username>","<password>", "<jdbcUrl>")
>>> pgql_statement = pgql_conn.create_statement()
>>> pgql_conn.set_graph("BANK_GRAPH")
>>> s = "SELECT n.* FROM MATCH (n:Accounts) LIMIT ?"
>>> ps = pgql_conn.prepare_statement(s, timeout=0, parallel=4, dynamicSampling=2, maxResults=-1, matchOptions=None, options=None)
>>> ps.set_int(1,3)
>>> ps.execute_query().print()
+---------------+
| ID | NAME     |
+---------------+
| 1  | Account1 |
| 2  | Account2 |
| 3  | Account3 |
+---------------+

Example 11-4 Running a SELECT Query with Grouping and Aggregation

• JShell
• Java
• Python

opg4j> var jdbcUrl="jdbc:oracle:thin:@<host_name>:<port>/<db_service>"
opg4j> var conn = DriverManager.getConnection(jdbcUrl,"<username>","<password>");
opg4j> var pgqlConn = PgqlConnection.getConnection(conn)
opg4j> pgqlConn.setGraph("BANK_GRAPH")        
opg4j> var pgqlStmt = pgqlConn.createStatement() //create a PGQL Statement
opg4j> String query = "SELECT v1.id, COUNT(v2) AS numTxns "+
...>         "FROM MATCH (v1)-[e IS Transfers]->(v2) "+
...>         "GROUP BY v1 "+
...>         "ORDER BY numTxns DESC "+
...>         "LIMIT 3"
opg4j> var resultSet = pgqlStmt.executeQuery(query)
opg4j> resultSet.print() //Prints the query result set
+---------------+
| ID  | NUMTXNS |
+---------------+
| 687 | 6       |
| 195 | 5       |
| 192 | 5       |
+---------------+

import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import oracle.pg.rdbms.pgql.jdbc.PgqlJdbcRdbmsDriver;
import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.pg.rdbms.pgql.PgqlStatement;


/*
 * This example shows how to execute a SELECT query with aggregation .*/
public class PgqlExample3
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String jdbcUrl            = args[idx++];
    String username           = args[idx++];
    String password           = args[idx++];
    String graph              = args[idx++];


    Connection conn = null;
    PgqlStatement pgqlStmt = null;
    PgqlResultSet rs = null;

    try {
      //Get a jdbc connection
      DriverManager.registerDriver(new PgqlJdbcRdbmsDriver());
      conn = DriverManager.getConnection(jdbcUrl, username, password);
      conn.setAutoCommit(false);


      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PGQL Statement
      pgqlStmt = pgqlConn.createStatement();

      // Execute PGQL Query
      String query =
        "SELECT v1.id, COUNT(v2) AS numTxns "+
        "FROM MATCH (v1)-[e IS Transfers]->(v2) "+
        "GROUP BY v1 "+
        "ORDER BY numTxns DESC";

      rs = pgqlStmt.executeQuery(query);
      // Print the results
      rs.print();
    }
    finally {
      // close the result set
      if (rs != null) {
         rs.close();
         }
      // close the statement
      if (pgqlStmt != null) {
         pgqlStmt.close();
         }
      // close the connection
      if (conn != null) {
         conn.close();
         }
      }
  }
}

>>> pgql_conn = opg4py.pgql.get_connection("<username>","<password>", "<jdbcUrl>")
>>> pgql_statement = pgql_conn.create_statement()
>>> pgql_conn.set_graph("BANK_GRAPH")
>>> query = """
...          SELECT v1.id, COUNT(v2) AS numtxns
...          FROM MATCH (v1)-[e IS Transfers]->(v2)
...          GROUP BY v1
...          ORDER BY numtxns DESC
...          LIMIT 3
...          """
>>> pgql_statement.execute_query(query).print()
+---------------+
| ID  | NUMTXNS |
+---------------+
| 687 | 6       |
| 195 | 5       |
| 192 | 5       |
+---------------+

Example 11-5 Showing a PGQL Path Query

• JShell
• Java
• Python

opg4j> var jdbcUrl="jdbc:oracle:thin:@<host_name>:<port>/<db_service>"
opg4j> var conn = DriverManager.getConnection(jdbcUrl,"<username>","<password>");
opg4j> var pgqlConn = PgqlConnection.getConnection(conn)
opg4j> pgqlConn.setGraph("BANK_GRAPH")        
opg4j> var pgqlStmt = pgqlConn.createStatement() //create a PGQL Statement
opg4j> String query = "PATH onehop AS ()-[IS transfers]->() "+
...>         "SELECT v1.id FROM MATCH (v1)-/:onehop/->(v2) "+
...>         "WHERE v2.id = 365"
opg4j> var resultSet = pgqlStmt.executeQuery(query)
opg4j> resultSet.print() //Prints the query result set
+-----+
| ID  |
+-----+
| 132 |
| 435 |
| 296 |
| 327 |
| 328 |
| 399 |
| 684 |
| 919 |
| 923 |
| 771 |
+-----+

import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import oracle.pg.rdbms.pgql.jdbc.PgqlJdbcRdbmsDriver;
import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.pg.rdbms.pgql.PgqlStatement;


/*
 * This example shows how to execute a PGQL PATH query.*/
public class PgqlExample4
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String jdbcUrl            = args[idx++];
    String username           = args[idx++];
    String password           = args[idx++];
    String graph              = args[idx++];


    Connection conn = null;
    PgqlStatement pgqlStmt = null;
    PgqlResultSet rs = null;

    try {
      //Get a jdbc connection
      DriverManager.registerDriver(new PgqlJdbcRdbmsDriver());
      conn = DriverManager.getConnection(jdbcUrl, username, password);
      conn.setAutoCommit(false);


      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PGQL Statement
      pgqlStmt = pgqlConn.createStatement();

     // Execute PGQL Query
      String query =
                 "PATH onehop AS ()-[IS transfers]->() "+
                 "SELECT v1.id FROM MATCH (v1)-/:onehop/->(v2) "+
                 "WHERE v2.id = 365";
      rs = pgqlStmt.executeQuery(query);

      // Print the results
      rs.print();
    }
    finally {
      // close the result set
      if (rs != null) {
         rs.close();
         }
      // close the statement
      if (pgqlStmt != null) {
         pgqlStmt.close();
         }
      // close the connection
      if (conn != null) {
         conn.close();
         }
      }
  }
}

>>> pgql_conn = opg4py.pgql.get_connection("<username>","<password>", "<jdbcUrl>")
>>> pgql_statement = pgql_conn.create_statement()
>>> pgql_conn.set_graph("BANK_GRAPH")
>>> query = """
...                  PATH onehop AS ()-[IS transfers]->()
...                  SELECT v1.id FROM MATCH (v1)-/:onehop/->(v2)
...                  WHERE v2.id = 365
...         """
>>> pgql_statement.execute_query(query).print()
+-----+
| ID  |
+-----+
| 132 |
| 435 |
| 296 |
| 327 |
| 328 |
| 399 |
| 684 |
| 919 |
| 923 |
| 771 |
+-----+

11.7.3.3 Executing PGQL Queries Using JDBC Driver

The Oracle Graph Server and Client Release 21.2.0 includes a JDBC driver which allows you to run PGQL queries directly against the Oracle Database. To use the driver, register the following class at the JDBC driver manager:

import java.sql.DriverManager;
import oracle.pg.rdbms.pgql.jdbc.PgqlJdbcRdbmsDriver;
...
DriverManager.registerDriver(new PgqlJdbcRdbmsDriver());

To make JDBC use the driver, you need to prefix the JDBC URLs with jdbc:oracle:pgql as shown in this example:

import java.sql.Connection;
import java.sql.DriverManager;
 
Connection conn = DriverManager.getConnection("jdbc:oracle:pgql:@<DB Host>:<DB Port>/<DB SID>", "<DB Username>", "<DB Password>");

The part after jdbc:oracle:pgql follows the same syntax as the regular Oracle JDBC thin driver. In other words, you can convert any valid Oracle JDBC thin driver URL into a PGQL driver URL by replacing jdbc:oracle:thin with jdbc:oracle:pgql. Once you obtained a connection object, you can use it to query property graphs using PGQL syntax. For example:

Example 11-6 Executing a PGQL Query using the PGQL JDBC driver

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import oracle.pg.rdbms.pgql.jdbc.PgqlJdbcRdbmsDriver;
  
public class PgqlJdbcTest {
  
  public static void main(String[] args) throws Exception {
  
    DriverManager.registerDriver(new PgqlJdbcRdbmsDriver());
    String jdbcUrl = "jdbc:oracle:pgql:@<DB Host>:<DB Port>/<DB SID>";
    String username = "<DB Username>";
    String password = "<DB Password>";
  
    try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password)) {
      String query = "SELECT n.name FROM MATCH(n) ON test_graph WHERE id(n) = ?";
      PreparedStatement pstmt = conn.prepareStatement(query);
      pstmt.setLong(1, 10L);
      pstmt.execute();
      ResultSet rs = pstmt.getResultSet();
      while(rs.next()){
        System.out.println("NAME = " + rs.getString("name"));
      }
    }
  }
}

Save the preceding code in a file PgqlJdbcTest.java and compile using:

javac -cp "<graph-client>/lib/*" PgqlJdbcTest.java

The driver is also included in a regular graph server (RPM) install. For example:

javac -cp "/opt/oracle/graph/lib/*" PgqlJdbcTest.java

11.7.3.4 Executing PGQL Queries to Modify Property Graph Views

You can execute PGQL INSERT, UPDATE and DELETE queries against property graph views using the OPG4J Java shell, OPG4Py Python shell or through a Java or Python application.

It is important to note that unique IDs are not auto generated when inserting vertices or edges in a graph. Therefore, you must ensure that the key column values are either present in the graph properties or they are auto generated by the database (through SEQUENCE and TRIGGERS or implemented with auto increment functionality using IDENTITY column).

The following example inserts two new vertices and also adds an edge relationship between the two vertices.

• JShell
• Java
• Python

opg4j> String pgql =
...>     "INSERT VERTEX v1 LABELS (Person) PROPERTIES (v1.name= 'ABC', v1.height=1.6, v1.birthdate = to_date('13/06/1963', 'DD/MM/YYYY')) "+
...>     "     , VERTEX v2 LABELS (Person) PROPERTIES (v2.name= 'XYZ', v2.height=1.75, v2.birthdate = to_date('19/06/1963', 'DD/MM/YYYY')) "+
...>     "     , EDGE e BETWEEN v1 AND v2 LABELS (friendof) PROPERTIES ( e.meeting_date = to_date('19/06/2021', 'DD/MM/YYYY')) "
pgql ==> "INSERT VERTEX v1 LABELS (Person) PROPERTIES (v1.name= 'ABC', v1.height=1.6, v1.birthdate = to_date('13/06/1963', 'DD/MM/YYYY'))      , VERTEX v2 LABELS (Person) PROPERTIES (v2.name= 'XYZ', v2.height=1.75, v2.birthdate = to_date('19/06/1963', 'DD/MM/YYYY'))      , EDGE e BETWEEN v1 AND v2 LABELS (friendof) PROPERTIES ( e.meeting_date = to_date('19/06/2021', 'DD/MM/YYYY')) "
opg4j> pgqlStmt.execute(pgql)
$14 ==> false

String pgql =
...>     "INSERT VERTEX v1 LABELS (Person) PROPERTIES (v1.name= 'ABC', v1.height=1.6, v1.birthdate = to_date('13/06/1963', 'DD/MM/YYYY')) "+
...>     "     , VERTEX v2 LABELS (Person) PROPERTIES (v2.name= 'XYZ', v2.height=1.75, v2.birthdate = to_date('19/06/1963', 'DD/MM/YYYY')) "+
...>     "     , EDGE e BETWEEN v1 AND v2 LABELS (friendof) PROPERTIES ( e.meeting_date = to_date('19/06/2021', 'DD/MM/YYYY')) ";
pgqlStmt.execute(pgql);

>>> pgql = """
...     INSERT VERTEX v1 LABELS (Person) PROPERTIES (v1.name= 'ABC', v1.height=1.6, v1.birthdate = to_date('13/06/1963', 'DD/MM/YYYY'))
...     , VERTEX v2 LABELS (Person) PROPERTIES (v2.name= 'XYZ', v2.height=1.75, v2.birthdate = to_date('19/06/1963', 'DD/MM/YYYY'))
...     , EDGE e BETWEEN v1 AND v2 LABELS (friendof) PROPERTIES ( e.meeting_date = to_date('19/06/2021', 'DD/MM/YYYY'))
... """
>>> pgql_statement.execute(pgql)
False

The following example executes an UPDATE query to modify the edge property that was inserted in the preceding example and subsequently verifies the update operation through a SELECT query.

• JShell
• Java
• Python

opg4j> String pgql = "UPDATE e SET (e.meeting_date = to_date('12/02/2022', 'DD/MM/YYYY')) "+
...>     "FROM MATCH (v1:Person)-[e:friendof]->(v2:Person) "+
...>     "WHERE v1.person_id = 27 AND v2.person_id = 28"
pgql ==> "UPDATE e SET (e.meeting_date = to_date('12/02/2022', 'DD/MM/YYYY')) FROM MATCH (v1:Person)-[e:friendof]->(v2:Person) WHERE v1.person_id = 27 AND v2.person_id = 28"
opg4j> pgqlStmt.execute(pgql)
$40 ==> false
opg4j>pgqlStmt.executeQuery("SELECT e.meeting_date FROM MATCH (v1:Person)-[e:friendof]->(v2:Person) WHERE v1.person_id = 27").print()
+-----------------------+
| MEETING_DATE          |
+-----------------------+
| 2022-02-12 00:00:00.0 |
+-----------------------+

String pgql ="UPDATE e SET (e.meeting_date = to_date('12/02/2022', 'DD/MM/YYYY')) "+
"FROM MATCH (v1:Person)-[e:friendof]->(v2:Person) "+
"WHERE v1.person_id = 27 AND v2.person_id = 28";
pgqlStmt.execute(pgql);

>>> pgql = """
...     UPDATE e SET (e.meeting_date = to_date('12/02/2022', 'DD/MM/YYYY'))
...     FROM MATCH (v1:Person)-[e:friendof]->(v2:Person)
...     WHERE v1.person_id = 27 AND v2.person_id = 28
... """
>>> pgql_statement.execute(pgql)
False
>>> pgql_statement.execute_query("SELECT e.meeting_date FROM MATCH(v1:Person)-[e:friendof]->(v2:Person) WHERE v1.person_id = 27").print()
+-----------------------+
| MEETING_DATE          |
+-----------------------+
| 2022-02-12 00:00:00.0 |
+-----------------------+

A DELETE query allows deleting of vertices and edges in a graph. The following example executes a DELETE query to delete an edge in the graph.

• JShell
• Java
• Python

opg4j> pgqlStmt.execute("DELETE e FROM MATCH (v1:Person)-[e:friendof]->(v2:Person) WHERE v.person_id=27")
$14 ==> false

pgqlStmt.execute("DELETE e FROM MATCH (v1:Person)-[e:friendof]->(v2:Person) WHERE v.person_id=27");

>>> pgql_statement.execute("DELETE e FROM MATCH (v1:Person)-[e:friendof]->(v2:Person) WHERE v1.person_id=27")
False

11.7.3.5 Dropping A Property Graph View

You can use the PGQL DROP PROPERTY GRAPH statement to drop a property graph view (PG View). Note that all the metadata tables for the PG View are dropped.

Example 11-7 Creating a Property Graph View

The following example describes the creation of a PG View.

• JShell
• Java
• Python

opg4j> var jdbcUrl="jdbc:oracle:thin:@<host_name>:<port>/<db_service>"
opg4j> var conn = DriverManager.getConnection(jdbcUrl,"<username>","<password>")
opg4j> var pgqlConn = PgqlConnection.getConnection(conn)
opg4j> var pgqlStmt = pgqlConn.createStatement() //create a PGQL Statement
opg4j> pgqlStmt.execute("DROP PROPERTY GRAPH <pgview>")
$9 ==> false

import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import oracle.pg.rdbms.pgql.jdbc.PgqlJdbcRdbmsDriver;
import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlStatement;
/**
 * This example shows how to drop a property graph view.
 */
public class DropPgView
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String jdbcUrl            = args[idx++];
    String username           = args[idx++];
    String password           = args[idx++];
    String graph              = args[idx++];  
    
    Connection conn = null;
    PgqlStatement pgqlStmt = null;
    
    try {
      //Get a jdbc connection
      DriverManager.registerDriver(new PgqlJdbcRdbmsDriver());
      conn = DriverManager.getConnection(jdbcUrl, username, password);
      conn.setAutoCommit(false);
                
      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
	  
	  // Create PGQL Statement
      pgqlStmt = pgqlConn.createStatement();

      String query = "DROP PROPERTY GRAPH " +pgview;
      pgqlStmt.execute(query);
      
    }
    finally {
      // close the statement
      if (pgqlStmt != null) {
         pgqlStmt.close();
         }
      // close the connection
      if (conn != null) {
         conn.close();
         }
      }
  }
}

>>> pgql_conn = opg4py.pgql.get_connection("<username>","<password>", "jdbc:oracle:thin:@localhost:1521/orclpdb")
>>> pgql_statement = pgql_conn.create_statement()
>>> pgql = "DROP PROPERTY GRAPH <pgview>"
>>> pgql_statement.execute(pgql)
False