Creating PGQL Property Graphs on Oracle Database Tables

9.1 Creating PGQL Property Graphs on Oracle Database Tables

The CREATE PROPERTY GRAPH statement in PGQL can be used to create a view-like object that contains metadata about the graph. This graph can be queried using PGQL.

PGQL property graphs are created directly over data that exists in the relational database tables. These graphs are stored in the database tables and therefore they have a schema.

One of the main benefits of PGQL property graphs is that all updates to the database tables are immediately reflected in the graph.

Metadata Tables for PGQL Property Graphs

Each time a CREATE PROPERTY GRAPH statement is executed, metadata tables are created in the user's own schema.

The following table describes the set of metadata tables that are created for each graph on executing CREATE PROPERTY GRAPH statement.

All columns shown underlined in the Table 9-1 are part of the primary key of the table. Also all columns have a NOT NULL constraint.

Table 9-1 Metadata Tables for PGQL Property Graphs

Table Name	Description
`graphName_ELEM_TABLE$`	Metadata for graph element (vertex/edge) tables (one row per element table): `ET_NAME`: the name of the element table (the "alias") `ET_TYPE`: either "VERTEX" or "EDGE" `SCHEMA_NAME`: the name of the schema of the underlying table `TABLE_NAME`: the name of underlying table
`graphName_LABEL`$	Metadata on labels of element tables (one row per label; one label per element table): `LABEL_NAME`: the name of the label `ET_NAME`: the name of the element table ( the "alias") `ET_TYPE`: either "VERTEX" or "EDGE"
`graphName_PROPERTY$`	Metadata describing the columns that are exposed through a label (one row per property) `PROPERTY_NAME`: the name of the property `ET_NAME`: the name of the element table (the "alias") `ET_TYPE`: either "VERTEX" or "EDGE" `LABEL_NAME`: the name of the label that this property belongs to `COLUMN_NAME`: the name of the column (initially, only the case where property names equal column names is allowed)
`graphName_KEY$`	Metadata describing a vertex/edge key (one row per column in the key) `COLUMN_NAME`: the name of the column in the key `COLUMN_NUMBER`: the number of the column in the key For example, in KEY ( a, b, c ), "a" has number 1, "b" has number 2 and "c" has number 3. `KEY_TYPE`: either "VERTEX" or "EDGE" `ET_NAME`: the name of the element table (the "alias")
`graphName_SRC_DST_KEY$`	Metadata describing the edge source/destination keys (one row per column of a key): `ET_NAME`: the name of the element table ( the "alias"), which is always an edge table `VT_NAME`: the name of the vertex table `KEY_TYPE`: either "EDGE_SOURCE" or "EDGE_DESTINATION" `ET_COLUMN_NAME`: the name of the key column `ET_COLUMN_NUMBER`: the number of the column in the key. For example, in KEY ( a, b, c ), "a" has number 1, "b" has number 2 and "c" has number 3. Note: Currently, support is only for `SOURCE KEY ( ... ) REFERENCES T1`. So only the edge source/destination key is stored.

Example 9-1 To create a PGQL property graph

Consider the following CREATE PROPERTY GRAPH statement:


CREATE PROPERTY GRAPH student_network
  VERTEX TABLES(
    person
      KEY ( id )
      LABEL student
      PROPERTIES( name ),
    university
      KEY ( id )
      PROPERTIES( name )
  )
  EDGE TABLES(
    knows
      key (person1, person2)
      SOURCE KEY ( person1 ) REFERENCES person (id)
      DESTINATION KEY ( person2 ) REFERENCES person (id)
      NO PROPERTIES,
    person AS studentOf
      key (id, university)
      SOURCE KEY ( id ) REFERENCES person (id)
      DESTINATION KEY ( university ) REFERENCES university (id)
      NO PROPERTIES
  )
  OPTIONS (PG_PGQL)

The OPTIONS clause allows the creation of a PGQL property graph. You must simply pass the CREATE PROPERTY GRAPH statement to the execute method:

Note:

You can create PGQL property graphs using the RDBMS Java API or through SQLcl.
You can query PGQL property graphs using the graph visualization tool or SQLcl.

stmt.execute("CREATE PROPERTY GRAPH student_network ...");

This results in the creation of the following metadata tables:


SQL> SELECT * FROM STUDENT_NETWORK_ELEM_TABLE$;
 
ET_NAME         ET_TYPE    SCHEMA_NAME     TABLE_NAME
--------------- ---------- --------------- ---------------
PERSON          VERTEX     SCOTT           PERSON
UNIVERSITY      VERTEX     SCOTT           UNIVERSITY
KNOWS           EDGE       SCOTT           KNOWS
STUDENTOF       EDGE       SCOTT           PERSON
 
SQL> SELECT * FROM STUDENT_NETWORK_LABEL$;
 
LABEL_NAME      ET_NAME         ET_TYPE
--------------- --------------- ----------
STUDENT         PERSON          VERTEX
UNIVERSITY      UNIVERSITY      VERTEX
KNOWS           KNOWS           EDGE
STUDENTOF       STUDENTOF       EDGE
 
SQL> SELECT * FROM STUDENT_NETWORK_PROPERTY$;
 
PROPERTY_NAME   ET_NAME         ET_TYPE    LABEL_NAME      COLUMN_NAME
--------------- --------------- ---------- --------------- ---------------
NAME            PERSON          VERTEX     STUDENT         NAME
NAME            UNIVERSITY      VERTEX     UNIVERSITY      NAME
 
SQL> SELECT * FROM STUDENT_NETWORK_KEY$;
 
COLUMN_NAME     COLUMN_NUMBER KEY_TY ET_NAME
--------------- ------------- ------ ---------------
ID                          1 VERTEX PERSON
ID                          1 VERTEX UNIVERSITY
PERSON1                     1 EDGE   KNOWS
PERSON2                     2 EDGE   KNOWS
ID                          1 EDGE   STUDENTOF
UNIVERSITY                  2 EDGE   STUDENTOF
 
SQL> SELECT * FROM STUDENT_NETWORK_SRC_DST_KEY$;
 
ET_NAME     VT_NAME        KEY_TYPE         ET_COLUMN_NAME  ET_COLUMN_NUMBER
--------------- ---------- ---------------- --------------- ----------------
KNOWS       PERSON         EDGE_SOURCE      PERSON1                        1
KNOWS       PERSON         EDGE_DESTINATION PERSON2                        1
STUDENTOF   PERSON         EDGE_SOURCE      ID                             1
STUDENTOF   UNIVERSITY     EDGE_DESTINATION UNIVERSITY                     1

You can now run PGQL queries on the student_network PGQL property graph.

See Executing PGQL Queries Against PGQL Property Graphs for more details to create, query and drop PGQL property graphs.

Retrieving Metadata for PGQL Property Graphs
You can retrieve the metadata of PGQL property graphs created in the database using the built-in PROPERTY_GRAPH_METADATA graph in your PGQL queries.
Privileges for Working with PGQL Property Graphs
Learn about the privileges that are required for working with PGQL property graphs.

Parent topic: About PGQL Property Graphs

9.1.1 Retrieving Metadata for PGQL Property Graphs

You can retrieve the metadata of PGQL property graphs created in the database using the built-in PROPERTY_GRAPH_METADATA graph in your PGQL queries.

The PROPERTY_GRAPH_METADATA graph structure including properties is as shown:

Figure 9-1 PROPERTY_GRAPH_METADATA Graph Design

Description of "Figure 9-1 PROPERTY_GRAPH_METADATA Graph Design"

The following describes the preceding design of the metadata graph:

PROPERTY_GRAPH -[:HAS_VERTEX_TABLE]-> VERTEX_TABLE
               -[:HAS_EDGE_TABLE]-> EDGE_TABLE
 
VERTEX_TABLE -[:HAS_KEY_COLUMN]-> KEY_COLUMN
             -[:HAS_LABEL]-> LABEL
 
EDGE_TABLE -[:HAS_KEY_COLUMN]-> KEY_COLUMN
           -[:HAS_LABEL]-> LABEL
           -[:HAS_SOURCE_TABLE]-> VERTEX_TABLE
           -[:HAS_DESTINATION_TABLE]-> VERTEX_TABLE
 
LABEL -[:HAS_PROPERTY]-> PROPERTY

It is important to note the following when using PROPERTY_GRAPH_METADATA in PGQL queries:

The PROPERTY_GRAPH_METADATA graph is automatically created and updated the first time you attempt to access it in a PGQL query.

The PROPERTY_GRAPH_METADATA graph is similar to a PGQL property graph and has its own set of metadata tables that describe its structure. In addition to the metadata tables for PGQL property graphs that are described in Table 9-1, the graph data for PROPERTY_GRAPH_METADATA is also stored in database objects that are listed in the following table:

Table 9-2 Additional Metadata Tables

Table Name	Description
`PROPERTY_GRAPH_METADATA_GRAPH_LIST$`	Metadata table describing the list of PGQL property graphs to which the current user has access
`PROPERTY_GRAPH_METADATA_EDGE_KEY_COLUMNS$`	Metadata table describing the edge key columns
`PROPERTY_GRAPH_METADATA_EDGE_LABELS$`	Metadata table describing the edge labels
`PROPERTY_GRAPH_METADATA_EDGE_TABLES$`	Metadata table describing the edge tables
`PROPERTY_GRAPH_METADATA_LABEL_PROPERTIES$`	Metadata table describing the vertex and edge label properties
`PROPERTY_GRAPH_METADATA_LABELS$`	Metadata table describing the vertex and edge labels
`PROPERTY_GRAPH_METADATA_VERTEX_KEY_COLUMNS$`	Metadata table describing the vertex key columns
`PROPERTY_GRAPH_METADATA_VERTEX_LABELS$`	Metadata table describing the vertex labels
`PROPERTY_GRAPH_METADATA_VERTEX_TABLES$`	Metadata table describing the vertex tables

Note:

It is important that you do not alter or remove any of the metadata tables for the PROPERTY_GRAPH_METADATA graph.

When running PGQL queries using the Java API, you must disable autocommit on the JDBC connection (conn.setAutoCommit(false)). This ensures that PROPERTY_GRAPH_METADATA graph gets created automatically.

The following examples show using PROPERTY_GRAPH_METADATA in PGQL queries to retrieve the required metadata.

You can retrieve the list of graphs to which you have access as shown:

opg4j> String pgql =
...> "SELECT g.graph_name "
...> +"FROM MATCH (g:property_graph) ON property_graph_metadata "
...> +"ORDER BY g.graph_name"
pgql ==> "SELECT g.graph_name FROM MATCH (g:property_graph) ON property_graph_metadata ORDER BY g.graph_name"
opg4j> pgqlStmt.executeQuery(pgql).print()

String pgql = "SELECT g.graph_name "+
"FROM MATCH (g:property_graph) ON property_graph_metadata "+
"ORDER BY g.graph_name";
PgqlResultSet rs = pgqlStmt.executeQuery(pgql);
rs.print();

>>> pgql = '''
... SELECT g.graph_name
... FROM MATCH (g:property_graph) ON property_graph_metadata
... ORDER BY g.graph_name
... '''
>>> pgql_statement.execute_query(pgql).print()

On execution, the preceding query produces the following result:

+------------------------+
| GRAPH_NAME             |
+------------------------+
| BANK_GRAPH_VIEW        |
| FINANCIAL_TRANSACTIONS |
| FRIENDS                |
+------------------------+

You can retrieve the vertex properties of a graph as shown:

opg4j> String pgql =
...> "SELECT p.property_name "
...> +"FROM MATCH(g:property_graph)-[:has_vertex_table]->(v)-[:has_label]->(l:label)-[:has_property]->(p:property) "
...> +"ON property_graph_metadata "
...> +"WHERE g.graph_name = 'FRIENDS' "
pgql ==> "SELECT p.property_name FROM MATCH(g:property_graph)-[:has_vertex_table]->(v)-[:has_label]->(l:label)-[:has_property]->(p:property) ON property_graph_metadata WHERE g.graph_name = 'FRIENDS' "
opg4j> pgqlStmt.executeQuery(pgql).print()

String pgql = "SELECT p.property_name "+
"FROM MATCH(g:property_graph)-[:has_vertex_table]->(v)-[:has_label]->(l:label)-[:has_property]->(p:property) "+
"ON property_graph_metadata "+
"WHERE g.graph_name = 'FRIENDS' ";
PgqlResultSet rs = pgqlStmt.executeQuery(pgql);
rs.print();

>>> pgql = '''
... SELECT p.property_name
... FROM MATCH(g:property_graph)-[:has_vertex_table]->(v)-[:has_label]->(l:label)-[:has_property]->(p:property)
... ON property_graph_metadata
... WHERE g.graph_name = 'FRIENDS'
... '''
>>> pgql_statement.execute_query(pgql).print()

On execution, the preceding query produces the following result:

+---------------+
| PROPERTY_NAME |
+---------------+
| BIRTHDATE     |
| HEIGHT        |
| NAME          |
+---------------+

Parent topic: Creating PGQL Property Graphs on Oracle Database Tables

9.1.2 Privileges for Working with PGQL Property Graphs

Learn about the privileges that are required for working with PGQL property graphs.

In order to create PGQL property graphs, ensure that you have the following privileges:

CREATE SESSION
CREATE TABLE

Note that these privileges can be granted directly to the user:

GRANT CREATE SESSION, CREATE TABLE TO <graphuser>

Or they can be granted indirectly through an appropriate role:

GRANT CREATE SESSION, CREATE TABLE TO GRAPH_DEVELOPER

For loading a PGQL property graph created by another user into the graph server (PGX), you must have:

SELECT permission on the underlying source database tables or views.
SELECT permission on the metadata tables used by the PGQL property graph.
See Table 9-1 and Table 9-2 for more details on the metadata tables.

Parent topic: Creating PGQL Property Graphs on Oracle Database Tables