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. 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 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):
|
graphName_LABEL $
|
Metadata on labels of element tables (one row per label; one label per element table):
|
graphName_PROPERTY$ |
Metadata describing the columns that are exposed through a label (one row per property)
|
graphName_KEY$ |
Metadata describing a vertex/edge key (one row per column in the key)
|
graphName_SRC_DST_KEY$ |
Metadata describing the edge source/destination keys (one row per column of a key):
Note: Currently, support is only forSOURCE 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_VIEW)
OPTIONS
clause allows the creation of a PGQL property graph 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 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-inPROPERTY_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