6.8 Loading SQL Property Graphs with Composite Keys

You can load a full SQL property graph or a sub-graph with composite keys into the graph server (PGX) memory using server-generated IDs.

Consider the following SQL property graph which is defined with composite vertex (FIRST_NAME, LAST_NAME) and edge (USER1_FIRST_NAME, USER1_LAST_NAME, USER2_FIRST_NAME, USER2_LAST_NAME) keys:
CREATE PROPERTY GRAPH SOCIAL_NETWORK
    VERTEX TABLES (
      USERS
         KEY (FIRST_NAME, LAST_NAME)
         PROPERTIES (FULL_NAME, USERNAME)
    ) 
    EDGE TABLES (
      FRIENDS_WITH 
        KEY (USER1_FIRST_NAME, USER1_LAST_NAME, USER2_FIRST_NAME, USER2_LAST_NAME) 
        SOURCE KEY (USER1_FIRST_NAME, USER1_LAST_NAME) REFERENCES USERS (FIRST_NAME, LAST_NAME)
        DESTINATION KEY (USER2_FIRST_NAME, USER2_LAST_NAME) REFERENCES USERS (FIRST_NAME, LAST_NAME)
        NO PROPERTIES
    ) OPTIONS (ENFORCED MODE);
  1. Load the complete graph into the graph server (PGX) memory using the readGraphByName API as shown:
    opg4j> var graph = session.readGraphByName("SOCIAL_NETWORK", GraphSource.PG_SQL)
graph ==> PgxGraph[name=SOCIAL_NETWORK,N=3,E=3,created=1742566083153]
    PgxGraph graph = session.readGraphByName("SOCIAL_NETWORK", GraphSource.PG_SQL);
    >>> graph = session.read_graph_by_name("SOCIAL_NETWORK", "pg_sql")
>>> graph
PgxGraph(name: SOCIAL_NETWORK, v: 3, e: 3, directed: True, memory(Mb): 0)

    Also, it is important to note the following when creating graphs using composite keys:

    • It is recommended that you enable ENFORCED MODE when creating the graph. This is to ensure the uniqueness for the specified composite keys, and avoid having the same vertex or edge being loaded multiple times with duplicate keys.
    • As these IDs are automatically generated, there is no key mapping between the graph entities and the database elements. Therefore, these graphs are not synchronizable.
    • The data dictionary views which store the metadata related to the key columns of the SQL property graph gets updated on the composite keys as shown:
      SQL> SELECT ELEMENT_NAME, COLUMN_NAME from ALL_PG_KEYS WHERE GRAPH_NAME = 'SOCIAL_NETWORK';

ELEMENT_NAME         COLUMN_NAME
-------------------- --------------------
USERS                FIRST_NAME
USERS                LAST_NAME
FRIENDS_WITH         USER1_FIRST_NAME
FRIENDS_WITH         USER1_LAST_NAME
FRIENDS_WITH         USER2_FIRST_NAME
FRIENDS_WITH         USER2_LAST_NAME

6 rows selected.
  2. Optionally, query the graph in the graph server (PGX) to view the auto-generated vertex and edge keys.
    opg4j> var pgql =
...>   "SELECT * "+
...>   "FROM GRAPH_TABLE ( social_network "+
...>   "MATCH (a IS users) -[e IS friends_with]-> (b IS users) "+
...>   "COLUMNS (vertex_id(a), a.full_name, edge_id(e), b.full_name AS friend) "+
...> ")"
pgql ==> "SELECT * FROM GRAPH_TABLE ( social_network MATCH (a IS users) -[e IS friends_with]-> (b IS users) COLUMNS (vertex_id(a), a.full_name, edge_id(e), b.full_name AS friend) )"
opg4j> g.queryPgql(pgql).print()
+------------------------------------------------------------+
| vertex_id(a) | full_name   | edge_id(e)      | friend      |
+------------------------------------------------------------+
| USERS(0)     | Steven King | FRIENDS_WITH(0) | Tom Rogers  |
| USERS(1)     | Tom Rogers  | FRIENDS_WITH(1) | Peter Hall  |
| USERS(2)     | Peter Hall  | FRIENDS_WITH(2) | Steven King |
+------------------------------------------------------------+
$27 ==> PgqlResultSetImpl[graph=SOCIAL_NETWORK,numResults=3]
    String pgql = "SELECT * "+
  "FROM GRAPH_TABLE ( social_network "+
  "MATCH (a IS users) -[e IS friends_with]-> (b IS users) "+
  "COLUMNS (vertex_id(a), a.full_name, edge_id(e), b.full_name) "+
")";
PgqlResultSet rs = g.queryPgql(pgql);
rs.print();
    >>> pgql = """
... SELECT *
... FROM GRAPH_TABLE ( social_network
... MATCH (a IS users) -[e IS friends_with]-> (b IS users)
... COLUMNS (vertex_id(a), a.full_name, edge_id(e), b.full_name as name_of_friend)
... )
... """
>>> graph.query_pgql(pgql).print()
+---------------------------------------------------------------+
| vertex_id(a) | full_name   | edge_id(e)      | name_of_friend |
+---------------------------------------------------------------+
| USERS(0)     | Steven King | FRIENDS_WITH(0) | Tom Rogers     |
| USERS(1)     | Tom Rogers  | FRIENDS_WITH(1) | Peter Hall     |
| USERS(2)     | Peter Hall  | FRIENDS_WITH(2) | Steven King    |
+---------------------------------------------------------------+
  3. Optionally, load and query only a subgraph of the SOCIAL_NETWORK graph which uses composite keys.
    opg4j> var subGraph = session.readSubgraph().
...>     fromPgSql("SOCIAL_NETWORK").
...>     queryPgql("MATCH (v1 IS users)-[e IS friends_with]->(v2 IS users) WHERE v1.full_name = 'Peter Hall'").
...>     load()
graph ==> PgxGraph[name=SOCIAL_NETWORK_2,N=2,E=1,created=1743066245310]

opg4j> subGraph.queryPgql("SELECT v1.full_name as name, v2.full_name as name_of_friend from MATCH (v1 IS users)-[e IS friends_with]->(v2 IS users)").print()
+-----------------------------+
| name       | name_of_friend |
+-----------------------------+
| Peter Hall | Steven King    |
+-----------------------------+
$11 ==> PgqlResultSetImpl[graph=SOCIAL_NETWORK_3,numResults=1]
    PgxGraph subGraph = session.readSubgraph()
  .fromPgSql("SOCIAL_NETWORK")
  .queryPgql("MATCH (v1 IS users)-[e IS friends_with]->(v2 IS users) WHERE v1.full_name = 'Peter Hall'")
  .load();

PgqlResultSet rs = subGraph.queryPgql("SELECT v1.full_name as name, v2.full_name as name_of_friend from MATCH (v1 IS users)-[e IS friends_with]->(v2 IS users)");
rs.print();
    >>> sub_graph = session.read_subgraph_from_pg_sql("SOCIAL_NETWORK",
...     "MATCH (v1 IS users)-[e IS friends_with]->(v2 IS users) WHERE v1.full_name = 'Peter Hall'",
...     graph_name = "nw_sub_graph")

>>> sub_graph.query_pgql("SELECT v1.full_name as name, v2.full_name as name_of_friend from MATCH (v1 IS users)-[e IS friends_with]->(v2 IS users)").print()
+-----------------------------+
| name       | name_of_friend |
+-----------------------------+
| Peter Hall | Steven King    |
+-----------------------------+
    Also, note the following limitations when loading a subgraph from a graph that uses composite keys:
    • Loading a subgraph based on a condition that depends on the auto-generated key IDs is not supported. For instance: MATCH (V) WHERE ID(V) = 'Person(0)'
    • Subgraphs with auto-generated IDs cannot be expanded.

Parent topic: Loading a SQL Property Graph into the Graph Server (PGX)