Second Quarterly Update on Oracle Graph (2023)

The graph features of Oracle Database enables developers to store and navigate relationships between entities. Oracle provides support for both property and RDF knowledge graphs and simplifies the process of modeling relational data as graph structures. Interactive graph queries can run directly on graph data in the database or in a high-performance in-memory graph server. Oracle Graph Server and Client enables developers, analysts, and data scientists to use graphs within Oracle Database, while Graph Studio in Oracle Autonomous Database removes barriers to entry by automating complicated setup and management, making data integration seamless, and by providing step-by-step examples for getting started.

The last quarterly update on Oracle Graph, announced the availability of Oracle Graph Server and Client 23.1. That release included the graph visualization JavaScript library for Property Graphs, which allows developers to leverage many of the benefits of graph visualization available in Graph Studio and the graph visualization tool, but in their own applications. Additionally, it announced GraphML support for a Supervised Edge Wise model, and a new API for Python and Java, that can be used to import local GraphSON v3.0 files into Oracle Database, and create a graph from it.

Oracle Graph Server and Client 23.2 is now available for download for use with databases in the Cloud (OCI Marketplace image is available) and for databases on-premises. This release includes a number of changes, including changes to the graph visualization app, updates to PGQL, and integrations of Oracle Graph with other services. The graph visualization app has been changed to more closely align with the SQL standard and support SQL property graphs, which are available through Oracle Database 23c Free – Developer Release. It also includes additional functionality through PGQL, and updates that closely align PGQL with the SQL standard. Lastly, Oracle Graph is now available through PyPi, and has enhanced integrations with SQL Developer and OCI Data Science. Learn more about these exciting changes below.

Graph Visualization App

This release includes some changes to the Graph Visualization application. First, the drop-down to select a graph has been removed. Instead, the graph name needs to be given in the ON clause, in an effort to align with the SQL Standard. To see the list of available graphs, the drop-down has been replaced by a popover with a list of available graphs. 

Oracle Database 23c Free – Developer Release, announced on April 3rd, includes support for SQL Property Graphs. The visualization application will detect if there are SQL Property Graphs available, and if there are, it will create a tab for SQL/PGQ queries. The application will also remember your preferred tab, for greater ease of use.

PGQL Updates

Path modes WALK, TRAIL, SIMPLE, ACYCLIC

PGQL updates include support for querying along paths to allow for cycle avoidance. Each path mode (WALK, TRAIL, ACYCLIC, and SIMPLE) are different path modes and can be summarized as follows:

• WALK: the default path mode, where no filtering of paths happens.
• TRAIL: where path bindings with repeated edges are not returned.
• ACYCLIC: where path bindings with repeated vertices are not returned.
• SIMPLE: where path bindings with repeated vertices are not returned unless the repeated vertex is the first and the last in the path.

Path modes are syntactically placed after ALL, ANY, ANY SHORTEST, SHORTEST k, ALL SHORTEST and are optionally followed by a PATH or PATHS keyword. See the following example, which traverses the graph for paths from account 10039 back to itself without repeating vertices, except for the vertex for account 10039:

SELECT LISTAGG(e.amount, ', ') AS amounts FROM MATCH ALL SIMPLE PATHS (a:account) -[:transaction]->+ (a) WHERE a.number = 10039

 

+--------------------------------+

| amounts                        |

+--------------------------------+

| 1000.0, 1500.3, 9999.5, 9900.0 |

| 1000.0, 3000.7, 9999.5, 9900.0 |

+--------------------------------+

LATERAL subquery

In this release, we added support for LATERAL subqueries to allow for passing the output rows of one query into another. For example, you can use ORDER BY / GROUP BY on top of another ORDER BY / GROUP BY.

/* Find the top-5 largest transactions and return the account number that received the highest number of such large transactions */

SELECT recipient, COUNT(*) AS num_large_transactions

FROM LATERAL( SELECT m.number AS recipient

FROM MATCH (n:account) -[e:transaction]-> (m:account)

ORDER BY e.amount DESC

LIMIT 5 )

GROUP BY recipient

ORDER BY num_large_transactions DESC

LIMIT 1

Note: In release 23.2, FROM clauses may only contain a single LATERAL subquery or one or more MATCH clauses.

GRAPH_TABLE subquery

To align PGQL with the SQL:2023 standard, we added GRAPH_TABLE syntax for PGQL, which can be used even when querying graphs in Graph Server (PGX). For example, if we have a graph named “financial_transactions”, we can write a query as follows:

SELECT *

FROM GRAPH_TABLE ( financial_transactions

     MATCH ALL TRAIL (a IS account) -[e IS transaction]->* (b IS account) 

     /* optional ONE ROW PER VERTEX/STEP clause here */

     WHERE a.number= 8021 AND b.number= 1001

     COLUMNS ( LISTAGG(e.amount, ', ') AS amounts )

)

ORDER BY amounts

 

+----------------------------------------+

| amounts                                |

+----------------------------------------+

| 1500.3                                 |

| 1500.3, 9999.5, 9900.0, 1000.0, 3000.7 |

| 3000.7                                 |

| 3000.7, 9999.5, 9900.0, 1000.0, 1500.3 |

+----------------------------------------+

When PGQL queries contain one or more GRAPH_TABLE subqueries, you must use only associated syntax that is in the SQL standard. For example, MATCH (a:account) must be replaced with MATCH (a IS account) when using GRAPH_TABLE subqueries.

Additional SQL standard alignments

This release includes syntax variations that align with the SQL standard, including the following:

• FETCH [FIRST/NEXT] 10 [ROW/ROWS] ONLY as standard form of LIMIT 10
• v [IS] SOURCE/DESTINATION [OF] e as standard form of is_source_of(e, v) / is_destination_of(e, v)
• e IS LABELED transaction as standard form of has_label(e, 'TRANSACTION')
• MATCH SHORTEST 10 (n) –[e]->* (m) as standard form of MATCH TOP 10 SHORTEST (n) –[e]->* (m)
• MATCH (n) –[e]->{1,4} (m) as alternative for MATCH ALL (n) –[e]->{1,4} (m)
• The ALL keyword became optional)
• VERTEX_ID(v) / EDGE_ID(e) as alternative for ID(v) / ID(e)
• VERTEX_EQUAL(v1, v2) / EDGE_EQUAL(e1, e2) as alternative for v1 = v2 / e1 = e2 

PG Schema Deprecation

The PG objects (also known as PG schema) data format has been deprecated, and replaced with PG View and SQL Property Graph, starting in 23c. This deprecation includes any APIs to create, query, update, remove or interact in any other way with graphs stored in VT$ and EG$ tables. Additionally, OPTIONS (PG_SCHEMA) is no longer the default in PGQL CREATE PROPERTY GRAPH statements. The OPTIONS clause now needs to be explicitly specified every time a property graph is created using PGQL. For example:

CREATE PROPERTY GRAPH BANK_GRAPH_PGQL

    VERTEX TABLES (

        BANK_ACCOUNTS

            KEY ( ID )

            LABEL accounts PROPERTIES ( ID, name )

    )

    EDGE TABLES (

       BANK_TRANSFERS

           SOURCE KEY ( src_acct_id ) REFERENCES BANK_ACCOUNTS

           DESTINATION KEY ( dst_acct_id ) REFERENCES BANK_ACCOUNTS|

           LABEL transfers PROPERTIES ( amount, description, src_acct_id, dst_acct_id, txn_id )

) OPTIONS (PG_VIEW);

Integrations

Along with this release, we are excited to announce integrations with a few other services.

The Python Client is now available on PyPI.org. The Python Package Index (PyPI) is the official software repository for the Python community. This integration simplifies the installation for python users by making the client installation as simple as:

pip install oracle-graph-client

There have been improvements to the SQL Developer integration in SQL Developer 23.1. The graph integration with SQL Developer now has added support for multiple statements in a single PGQL worksheet, and a single statement can be highlighted to be executed. 

The full Oracle Graph Python client has been added to OCI Data Science. While PyPGX has been available on OCI Data Science since version 21.4, this release adds the missing client components, including, the Graph Server client, the Oracle Graph client for Graph Studio in Autonomous Database, and PGQL on RDBMS library.

Source: oracle.com