Creating a SQL Property Graph

Using the CREATE PROPERTY GRAPH DDL statement, you can create a property graph object directly in an Oracle AI Database.

Example: Creating a SQL Property Graph Using the CREATE PROPERTY GRAPH DDL Statement

This example creates a SQL property graph, students_graph, using persons, university, friends, and student_of as the underlying database tables for the graph.

In order to run this example, ensure the following:

  1. Set up the sample tables in the database as explained in Setting Up Sample Data in the Database.

  2. See Granting System and Object Privileges for SQL Property Graphs to ensure you have the required privileges to create a SQL property graph.

The following diagram illustrates the students_graph:

Figure: STUDENTS_GRAPH

Description of image follows

Description of the illustration students_graph.png

The corresponding SQL property graph DDL statement is as shown:

CREATE PROPERTY GRAPH students_graph
  VERTEX TABLES (
    persons KEY (person_id)
      LABEL person
        PROPERTIES (person_id, name, birthdate AS dob)
      LABEL person_ht
        PROPERTIES (height),
    university KEY (id)
  )
  EDGE TABLES (
    friends
      KEY (friendship_id)
      SOURCE KEY (person_a) REFERENCES persons(person_id)
      DESTINATION KEY (person_b) REFERENCES persons(person_id)
      PROPERTIES (friendship_id, meeting_date),
    student_of
      KEY (s_id)
      SOURCE KEY (s_person_id) REFERENCES persons(person_id)
      DESTINATION KEY (s_univ_id) REFERENCES university(id)
      PROPERTIES (subject)
  );

On execution, the preceding example creates a SQL property graph object that uses the tables in your schema to define its graph element tables. Note that the creation of the new SQL property graph object, results only in the storage of the property graph metadata, and there is no copying of data from the underlying database objects into the graph element tables. This implies that when querying a SQL property graph, all the graph queries are performed on the current graph data in the database. You may also specify another schema to contain the SQL property graph provided that you have sufficient privileges.

The graph definition in the example creates a graph that comprises:

It is important to note that once a SQL property graph is created, you cannot alter the graph definition. However, you can redefine a SQL property graph using the OR REPLACE clause in the CREATE PROPERTY GRAPH DDL statement. You can use this clause to change the definition of an existing SQL property graph without dropping, re-creating, and regranting object privileges that were earlier granted on it.

See Also:

The following sections explain more on the concepts of the graph element tables, keys, labels and properties:

About Vertex and Edge Graph Element Tables

The vertices and edges of a SQL property graph defined from the underlying database objects are stored in the graph element tables.

A graph element table can either be a vertex table or an edge table.

Refer to the graph definition in Example: Creating a SQL Property Graph Using the CREATE PROPERTY GRAPH DDL Statement to easily understand the following sections:

Vertex graph element table

Edge graph element table

About Vertex and Edge Table Keys

Each vertex and edge table used in a SQL property graph definition must have a key in order to identify a unique vertex or an edge in a SQL property graph.

The key is defined from one or more columns of the underlying table. The key may be implicitly inferred based on an existing primary key or a unique constraint defined on the underlying table, or explicitly defined. The key should be unique.

However, note that the uniqueness constraint for the key column is required if you create the graph in ENFORCED MODE. Otherwise, you can create the graph in TRUSTED MODE using key columns that do not have a uniqueness constraint. See Using Graph Options to Create SQL Property Graphs for more information on the different modes that can be applied during graph creation.

Vertex or edge table keys can be defined for any of the following built-in data type columns:

Note that the TIMESTAMP WITH TIME ZONE data type is not supported.

Refer to the SQL property graph definition in Example: Creating a SQL Property Graph Using the CREATE PROPERTY GRAPH DDL Statement to easily understand the following sections:

Vertex Table Key

Edge Table Key

Note: All restrictions that apply for primary key constraints on a database object also apply on vertex and edge table keys.

About Labels and Properties

Labels can be associated to one or more graph element tables and they enrich the graph definition. A label can be defined with or without properties.

You can optionally define LABELS and PROPERTIES for the vertex and edge tables in your graph. When not specified, the graph element tables are automatically assigned a label with the name of the graph element table, and all visible columns are exposed as properties, using the column name as property name.

Refer to the SQL property graph definition in Example: Creating a SQL Property Graph Using the CREATE PROPERTY GRAPH DDL Statement to easily understand the following sections:

Labels

Properties

Using Graph Options to Create SQL Property Graphs

You can use graph options to control the behavior of a SQL property graph at the time of its creation.

Graph options can be specified at the end of the CREATE PROPERTY GRAPH DDL statement using the OPTIONS clause. You can use either the MODE or MIXED PROPERTY TYPES option, or both as required.

Using an Option to Specify the Mode of the Graph

You can specify the MODE of the graph by using one of the following option values at the time of creating the SQL property graph:

Using an Option to Allow or Disallow Different Property Types for Shared Property Names

You can specify the MIXED PROPERTY TYPES options using one of the following values:

CREATE PROPERTY GRAPH g3
   VERTEX TABLES (
      persons
        LABEL t PROPERTIES (height),
      t3
        LABEL t PROPERTIES (height)
   )OPTIONS(ALLOW MIXED PROPERTY TYPES);

See Type Compatibility Rules for Determining Property Types for more information.

The following table summarizes compatibility rules with respect to the MIXED PROPERTY TYPES options

Description ALLOW DISALLOW
Properties with the same name exposed by shared labels Union-compatible Types must match
Shared properties Any Types must match

In the preceding table:

Granting System and Object Privileges for SQL Property Graphs

Learn about the new system and object privileges for performing operations on SQL property graphs.

Table: System Privileges for SQL Property Graph Objects

System Privileges Description
CREATE PROPERTY GRAPH To create a SQL property graph in the grantee’s schema.
CREATE ANY PROPERTY GRAPH To create a SQL property graph in any schema except SYS and AUDSYS.
ALTER PROPERTY GRAPH To alter a SQL property graph in the grantee’s schema.
ALTER ANY PROPERTY GRAPH To alter a SQL property graph in any schema except SYS and AUDSYS.
READ PROPERTY GRAPH To query a SQL property graph in the grantee’s schema.
READ ANY PROPERTY GRAPH To query a SQL property graph in any schema except SYS and AUDSY.
SELECT PROPERTY GRAPH To query a SQL property graph in the grantee’s schema.
SELECT ANY PROPERTY GRAPH To query a SQL property graph in any schema except SYS and AUDSY.
DROP PROPERTY GRAPH To drop a SQL property graph in the grantee’s schema.
DROP ANY PROPERTY GRAPH To drop a SQL property graph in any schema except SYS and AUDSYS.
COMMENT PROPERTY GRAPH To add a comment to a SQL property graph in the grantee’s schema.
COMMENT ANY PROPERTY GRAPH To add a comment to a SQL property graph in any schema except SYS and AUDSYS.

Table: Object Privileges for SQL Property Graphs

Object Privileges Description
ALTER To alter a SQL property graph.
READ To query a SQL property graph with a SQL graph query.
SELECT To query a SQL property graph with a SQL graph query.

Note that the SELECT privilege behaves exactly as the READ privilege for the SQL property graph object. It is mainly present for compatibility with the SQL standards for a property graph object.

The following shows the examples for granting and revoking the SQL property graph related privileges. Ensure you have SYSDBA access to grant and revoke these privileges:

GRANT CREATE PROPERTY GRAPH, CREATE ANY PROPERTY GRAPH,
      ALTER ANY PROPERTY GRAPH, DROP ANY PROPERTY GRAPH,
      READ ANY PROPERTY GRAPH TO <graphuser>;
REVOKE CREATE PROPERTY GRAPH, CREATE ANY PROPERTY GRAPH,
      ALTER ANY PROPERTY GRAPH, DROP ANY PROPERTY GRAPH,
      READ ANY PROPERTY GRAPH FROM <graphuser>;

You can share your SQL property graph in the database with another user as shown.

GRANT SELECT ON PROPERTY GRAPH <graph_name> TO <schema_user>;

Retrieving Metadata for SQL Property Graphs

The metadata of SQL property graphs can be accessed through a series of data dictionary views.

The following table provides a complete list of the data dictionary views that you can access to retrieve the metadata for SQL property graphs. Note that the metadata for each category in the table exists across ALL_ , USER_, and DBA_ view set. Depending upon your level of privilege, you can access the corresponding view.

Table: List of Data Dictionary Views to Retrieve Metadata for SQL Property Graphs

View Name Description
To describe all the property graphs in the database.
To describe all the graph element tables of the property graphs in the database.
To describe all the columns used to define the edge relationships.
To describe all the columns used as the key for the graph element tables.
To describe labels of property graphs in the database.
To describe the properties of all the labels of the property graphs in the database.
To describe all the column expressions used to define the properties of labels.
To describe all the labels of all the graph element tables of the property graphs in the database.
To display comments on the property graphs that are accessible to the current user.

The following example retrieves the graph element tables that were defined for the SQL property graph (students_graph) created in Creating a SQL Property Graph.

SQL> SELECT * FROM USER_PG_ELEMENTS;

GRAPH_NAME           ELEMENT_NAME    ELEMENT_KIND    OBJECT_OWNER    OBJECT_NAME
-------------------- --------------- --------------- --------------- ---------------
STUDENTS_GRAPH       PERSONS         VERTEX          GRAPHUSER       PERSONS
STUDENTS_GRAPH       UNIVERSITY      VERTEX          GRAPHUSER       UNIVERSITY
STUDENTS_GRAPH       FRIENDS         EDGE            GRAPHUSER       FRIENDS
STUDENTS_GRAPH       STUDENT_OF      EDGE            GRAPHUSER       STUDENT_OF

Retrieving SQL Creation DDL Using the DBMS_METADATA Package

You can retrieve the creation DDL for a SQL property graph using the DBMS_METADATA package.

The following example displays the DDL for the graph created in Creating a SQL Property Graph using the DBMS_METADATA package.

SQL> SELECT DBMS_METADATA.GET_DDL('PROPERTY_GRAPH', 'STUDENTS_GRAPH') FROM DUAL;

  CREATE PROPERTY GRAPH "GRAPHUSER"."STUDENTS_GRAPH"
  VERTEX TABLES (
   "GRAPHUSER"."PERSONS" AS "PERSONS" KEY ("PERSON_ID")
      LABEL PERSON PROPERTIES ("PERSON_ID", "NAME", "BIRTHDATE" AS "DOB")
      LABEL PERSON_HT PROPERTIES ("HEIGHT"),
   "GRAPHUSER"."UNIVERSITY" AS "UNIVERSITY" KEY ("ID")
      PROPERTIES ("ID", "NAME") )
  EDGE TABLES (
   "GRAPHUSER"."FRIENDS" AS "FRIENDS" KEY ("FRIENDSHIP_ID")
      SOURCE KEY("PERSON_A") REFERENCES PERSONS ("PERSON_ID")
      DESTINATION KEY("PERSON_B") REFERENCES PERSONS ("PERSON_ID")
     PROPERTIES ("FRIENDSHIP_ID", "MEETING_DATE"),
   "GRAPHUSER"."STUDENT_OF" AS "STUDENT_OF" KEY ("S_ID")
      SOURCE KEY("S_PERSON_ID") REFERENCES PERSONS ("PERSON_ID")
      DESTINATION KEY("S_UNIV_ID") REFERENCES PERSONS ("ID")
     PROPERTIES ("SUBJECT") )
  OPTIONS (TRUSTED MODE, DISALLOW MIXED PROPERTY TYPES)

Creating a SQL Property Graph Using Database Views

You can create SQL property graphs using database views or synonyms that reference views.

Views defined on database tables (single or multiple tables), hybrid partitioned tables, database links, and object views can be used for creating SQL property graphs. Also, the following views are supported for creating SQL property graphs:

When using views to define vertex or edge graph element tables in the CREATE PROPERTY GRAPH DDL statement, note the following:

The following examples describe a few scenarios on creating SQL property graphs using views. Ensure you have the required privileges to create a SQL property graph as described in Granting System and Object Privileges for SQL Property Graphs.

Example: Creating a SQL Property Graph Using Views on Database Tables

This example creates tech_students and student_profiles views from student_of and persons database tables respectively. See Setting Up Sample Data in the Database for the initial set up of these database tables.

CREATE or REPLACE VIEW tech_students AS
  SELECT * FROM student_of a  WHERE a.subject = 'Math' or a.SUBJECT= 'Science';

CREATE or REPLACE VIEW student_profiles AS
  SELECT PERSON_ID, NAME, BIRTHDATE FROM PERSONS;

Using the preceding views, the example creates a SQL property graph using the following CREATE PROPERTY GRAPH DDL statement.

CREATE OR REPLACE PROPERTY GRAPH students
  VERTEX TABLES (
    student_profiles KEY (person_id)
      PROPERTIES ARE ALL COLUMNS,
    university KEY (id)
      PROPERTIES ARE ALL COLUMNS
  )
  EDGE TABLES (
    tech_students
      KEY (s_id)
      SOURCE KEY (s_person_id) REFERENCES student_profiles(person_id)
      DESTINATION KEY (s_univ_id) REFERENCES university(id)
      PROPERTIES (subject)
  );

Optionally, you can query the data dictionary views to verify the graph creation using views and table objects as shown:

SELECT e.element_name, e.object_owner, e.object_name, o.object_type
FROM USER_PG_ELEMENTS e, ALL_OBJECTS o
WHERE graph_name='STUDENTS'
  AND e.object_name = o.object_name
  AND e.object_owner = o.owner;

The query shows the following output:

ELEMENT_NAME         OBJECT_OWNER    OBJECT_NAME          OBJECT_TYPE
-------------------- --------------- -------------------- ---------------
STUDENT_PROFILES     GRAPHUSER       STUDENT_PROFILES     VIEW
UNIVERSITY           GRAPHUSER       UNIVERSITY           TABLE
TECH_STUDENTS        GRAPHUSER       TECH_STUDENTS        VIEW

You can then query the newly created SQL property graph. For example:

SELECT * FROM GRAPH_TABLE (students
  MATCH
  (a IS student_profiles) -[e IS tech_students]-> (b IS university)
    COLUMNS (a.name AS student, e.subject as subject, b.name AS university)
);

The preceding query retrieves the student names, subject, and the university to which they belong as shown:

STUDENT   SUBJECT    UNIVERSITY
--------  -------    ----------
Mary	  Math       XYZ
Alice	  Science    XYZ

If you are not the graph creator, then see Privileges to Query a SQL Property Graph to learn about the privileges to query a SQL property graph.

Example: Creating a SQL Property Graph With Views Using JSON_TABLE function

This example creates hr_data_view on persons database table which is set up as described in Setting Up Sample Data in the Database. The view is created using JSON_TABLE function on hr_data column.

CREATE OR REPLACE VIEW hr_data_view AS
  SELECT person_id, name, v.department, v.role
    FROM persons, JSON_TABLE ( hr_data, '$' COLUMNS (department VARCHAR2(20) PATH '$.department',  role VARCHAR2 (20) PATH '$.role')) v;

Using the preceding view, the example creates the emp SQL property graph as shown

CREATE OR REPLACE PROPERTY GRAPH emp
  VERTEX TABLES(
    hr_data_view KEY(person_id)
      LABEL person
        PROPERTIES(name, department, role)
);

Optionally, you can then query the SQL property graph. For example:

SELECT * FROM GRAPH_TABLE (emp
  MATCH
  (a IS person)
    COLUMNS (a.name AS empName, a.department as worksIn, a.role AS role)
);

The preceding query produces the following output:

EMPNAME    WORKSIN              ROLE
---------- -------------------- --------------------
John       IT                   Software Developer
Mary       HR                   HR Manager
Bob        IT                   Technical Consultant
Alice      HR                   HR Assistant

Special Instructions for Creating SQL Property Graphs with Advanced Table Features

Review the special instructions when using certain table types and features to create a SQL property graph.

Hybrid Partitioned Tables & Database Links:

Hybrid partitioned tables and database links cannot be used directly as graph element tables.

However, you can define views on these tables and use these views to create the graph.

Pseudocolumns:

Pseudocolumns cannot be exposed as properties or as keys for graph element tables.

However, you can define a view that projects the pseudocolumns, and then expose the resulting view columns as properties or keys.

Column Expressions with PL/SQL Functions:

Column expressions that comprise invocations to PL/SQL functions cannot be exposed as properties.

However, you can define a view that projects these column expressions, and then expose these view columns as properties.

Virtual Columns Based on PL/SQL Functions:

Virtual columns defined over column expressions that comprise invocations to PL/SQL functions cannot be exposed as properties.

However, you can define a view that projects these virtual columns, and then expose these view columns as properties.

XMLType Columns:

XMLType columns cannot be directly exposed as properties.

However, note the following:

Limitations of Creating a SQL Property Graph

This section lists a few restrictions that apply when creating a SQL property graph.