6.1 Creating a Property Graph Using PGQL
CREATE PROPERTY GRAPH is a PGQL DDL statement to create a graph from database tables.
The CREATE PROPERTY GRAPH statement starts with the name you give the graph, followed by a set of vertex tables and edge tables. The graph can have no vertex tables or edge tables (an empty graph), or vertex tables and no edge tables (a graph with only vertices and no edges), or both vertex tables and edge tables (a graph with vertices and edges). However, a graph cannot be specified with only edge tables and no vertex tables.
Consider the following example:
- PERSONS is a table with columns ID, NAME, and ACCOUNT_NUMBER. A row is added to this table for every person who has an account.
- TRANSACTIONS is a table with columns FROM_ACCOUNT, TO_ACCOUNT, DATE, and AMOUNT. A row is added into this table in the database every time money is transferred from a FROM_ACCOUNT to a TO_ACCOUNT.
A straightforward mapping of tables to graphs is as follows. The graph concepts mapped are: vertices, edges, labels, properties.
- Vertex tables: A table that contains data entities is a vertex
table.
- Each row in the vertex table is a vertex.
- The columns in the vertex table are properties of the vertex.
- The name of the vertex table is the default label for this set of vertices. Alternatively, you can specify a label name as part of the CREATE PROPERTY GRAPH statement.
- Edge tables: An edge table can be any table that links two
vertex tables, or a table that has data that indicates an action from a source
entity to a target entity. For example, a transfer of money from FROM_ACCOUNT to
TO_ACCOUNT is a natural edge.
- Foreign key relationships can give guidance on what links are relevant in your data. CREATE PROPERTY GRAPH will default to using foreign key relationships to identify edges.
- Some of the properties of an edge table can be the properties of the edge. For example, an edge from FROM_ACCOUNT to TO_ACCOUNT can have properties DATE and AMOUNT.
- The name of an edge table is the default label for this set of edges. Alternatively, you can specify a label name as part of the CREATE PROPERTY GRAPH statement.
- Keys:
- Keys in a vertex table: The key of a vertex table identifies a unique vertex in the graph. The key can be specified in the CREATE PROPERTY GRAPH statement; otherwise, it defaults to the primary key of the table. If there are duplicate rows in the table, the CREATE PROPERTY GRAPH statement will return an error.
- Key in an edge table: The key of an edge table uniquely identifies an edge in the graph. The KEY clause when specifying source and destination vertices uniquely identifies the source and destination vertex keys.
The following is an example CREATE PROPERTY GRAPH statement for the tables PERSONS and TRANSACTIONS.
CREATE PROPERTY GRAPH bank_transfers VERTEX TABLES (persons KEY(account_number)) EDGE TABLES( transactions KEY (from_acct, to_acct, date, amount) SOURCE KEY (from_account) REFERENCES persons (account_number) DESTINATION KEY (to_account) REFERENCES persons (account_number) PROPERTIES (date, amount) )
- Table aliases: Vertex and edge tables must have unique names.
If you need to identify multiple vertex tables from the same relational table,
or multiple edge tables from the same relational table, you must use aliases.
For example, you can create two vertex tables PERSONS and PERSONS_ID from one
table PERSONS, as in the following
example.
CREATE PROPERTY GRAPH bank_transfers VERTEX TABLES (persons KEY(account_number) persons_id AS persons KEY(id))
In case any of your vertex and edge table share the same name, then you must again use a table alias. In the following example, table alias is used for the edge table, DEPARTMENTS, as there is a vertex table referenced with the same name:
CREATE PROPERTY GRAPH hr VERTEX TABLES ( employees KEY(employee_id) PROPERTIES ARE ALL COLUMNS, departments KEY(department_id) PROPERTIES ARE ALL COLUMNS ) EDGE TABLES ( departments AS managed_by SOURCE KEY ( department_id ) REFERENCES departments ( department_id ) DESTINATION employees PROPERTIES ARE ALL COLUMNS )
- Properties: The vertex and edge
properties of a graph are derived from the columns of the vertex and edge tables
respectively and by default have the same name as the underlying table columns.
However, you can choose a different property name for each column. This helps to
avoid conflicts when two tables have the same column name but with different
data types.
In the following example, the vertex properties
ID
,NAME
andACCOUNT_NUMBER
are renamed toPERSON_ID
,PERSON_NAME
andAC_NO
respectively:CREATE PROPERTY GRAPH bank_transfers VERTEX TABLES ( PERSONS LABEL Person PROPERTIES (ID AS PERSON_ID, NAME AS PERSON_NAME, ACCOUNT_NUMBER AS AC_NO) )
- REFERENCES clause: This connects the source and destination vertices of an edge to the corresponding vertex tables.
For more details on the CREATE PROPERTY GRAPH
statement, see the
PGQL Specification.
Refer to the following table for creating a property graph:
Table 6-1 CREATE PROPERTY
GRAPH
Statement Support
Method | More Information |
---|---|
Create a property graph in the graph server (PGX)
using the oracle.pgx.api Java package
|
Java APIs for Executing CREATE PROPERTY GRAPH Statements |
Create a property graph in the graph server (PGX)
using the pypgx.api Python package
|
Python APIs for Executing CREATE PROPERTY GRAPH Statements |
Create a property graph in Oracle Database
(Property Graph Schema) using the
oracle.pg.rdbms.pgql Java package
|
Creating Property Graphs through CREATE PROPERTY GRAPH Statements |
Create a property graph in Oracle Database
(Property Graph Schema) using the opgpy.pgql
Python package
|
Creating a Property Graph Using the Python Client |
Create a property graph view on Oracle Database tables | Creating a Property Graph View |
Parent topic: Property Graph Query Language (PGQL)