3 Property Graph Views on Oracle Database Tables

You can create property graph views over data stored in Oracle Database. You can perform various graph analytics operations using PGQL on these views.

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.

The property graph views are created directly over data that exists in the relational database tables. Since the graph is stored in the database tables it has a schema. This is unlike the graphs created with a flexible schema, where the data is copied from the source tables to property graph schema tables as described in Property Graph Schema Objects for Oracle Database.

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

Metadata Tables for PG Views

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 3-1 are part of the primary key of the table. Also all columns have a NOT NULL constraint.

Table 3-1 Metadata Tables for PG Views

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 3-1 To create a Property Graph View

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
      DESTINATION KEY ( person2 ) REFERENCES person
      NO PROPERTIES,
    person AS studentOf
      key (id, university)
      SOURCE KEY ( id ) REFERENCES person
      DESTINATION KEY ( university ) REFERENCES university
      NO PROPERTIES
  )
  OPTIONS (PG_VIEW)

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

Note:

• You can create property graph views using the RDBMS Java API or through SQLcl.
• You can query property graph views using the graph visualization tool or SQLcl.
• Both creation and querying of property graph views are not supported when using Python API.

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 property graph view student_network.