6.9.1.2 Creating Property Graphs through CREATE PROPERTY GRAPH Statements
You can use PGQL to create property graphs from relational database tables. A CREATE PROPERTY GRAPH statement defines a set of vertex tables that are transformed into vertices and a set of edge tables that are transformed into edges. For each table a key, a label and a set of column properties can be specified. The column types CHAR, NCHAR, VARCHAR, VARCHAR2, NVARCHAR2 , NUMBER, LONG, FLOAT, DATE, TIMESTAMP and TIMESTAMP WITH TIMEZONE are supported for CREATE PROPERTY GRAPH column properties.
When a CREATE PROPERTY GRAPH statement is called, a property graph schema for the graph is created, and the data is copied from the source tables into the property graph schema tables. The graph is created as a one-time copy and is not automatically kept in sync with the source data.
Example 6-3 PgqlCreateExample1.java
This example shows how to create a property graph from a set of relational tables. Notice that the example creates tables Person, Hobby, and Hobbies, so they should not exist before running the example. The example also shows how to execute a query against a property graph.
import java.sql.Connection;
import java.sql.Statement;
import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.pg.rdbms.pgql.PgqlStatement;
import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;
/**
* This example shows how to create a Property Graph from relational
* data stored in Oracle Database executing a PGQL create statement.
*/
public class PgqlCreateExample1
{
public static void main(String[] args) throws Exception
{
int idx=0;
String host = args[idx++];
String port = args[idx++];
String sid = args[idx++];
String user = args[idx++];
String password = args[idx++];
String graph = args[idx++];
Connection conn = null;
Statement stmt = null;
PgqlStatement pgqlStmt = null;
PgqlResultSet rs = null;
try {
//Get a jdbc connection
PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
pds.setUser(user);
pds.setPassword(password);
conn = pds.getConnection();
conn.setAutoCommit(false);
// Create relational data
stmt = conn.createStatement();
//Table Person
stmt.executeUpdate(
"create table Person( " +
" id NUMBER, " +
" name VARCHAR2(20), " +
" dob TIMESTAMP " +
")");
// Insert some data
stmt.executeUpdate("insert into Person values(1,'Alan', DATE '1995-05-26')");
stmt.executeUpdate("insert into Person values(2,'Ben', DATE '2007-02-15')");
stmt.executeUpdate("insert into Person values(3,'Claire', DATE '1967-11-30')");
// Table Hobby
stmt.executeUpdate(
"create table Hobby( " +
" id NUMBER, " +
" name VARCHAR2(20) " +
")");
// Insert some data
stmt.executeUpdate("insert into Hobby values(1, 'Sports')");
stmt.executeUpdate("insert into Hobby values(2, 'Music')");
// Table Hobbies
stmt.executeUpdate(
"create table Hobbies( "+
" person NUMBER, "+
" hobby NUMBER, "+
" strength NUMBER "+
")");
// Insert some data
stmt.executeUpdate("insert into Hobbies values(1, 1, 20)");
stmt.executeUpdate("insert into Hobbies values(1, 2, 30)");
stmt.executeUpdate("insert into Hobbies values(2, 1, 10)");
stmt.executeUpdate("insert into Hobbies values(3, 2, 20)");
//Commit changes
conn.commit();
// Get a PGQL connection
PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
// Create a PgqlStatement
pgqlStmt = pgqlConn.createStatement();
// Execute PGQL to create property graph
String pgql =
"Create Property Graph " + graph + " " +
"VERTEX TABLES ( " +
" Person " +
" Key(id) " +
" Label \"people\"" +
" PROPERTIES(name AS \"first_name\", dob AS \"birthday\")," +
" Hobby " +
" Key(id) Label \"hobby\" PROPERTIES(name AS \"name\")" +
")" +
"EDGE TABLES (" +
" Hobbies" +
" SOURCE KEY(person) REFERENCES Person " +
" DESTINATION KEY(hobby) REFERENCES Hobby " +
" LABEL \"likes\" PROPERTIES (strength AS \"score\")" +
")";
pgqlStmt.execute(pgql);
// Execute a PGQL query to verify Graph creation
pgql =
"SELECT p.\"first_name\", p.\"birthday\", h.\"name\", e.\"score\" " +
"FROM MATCH (p:\"people\")-[e:\"likes\"]->(h:\"hobby\") ON " + graph;
rs = pgqlStmt.executeQuery(pgql, "");
// Print the results
rs.print();
}
finally {
// close the sql statment
if (stmt != null) {
stmt.close();
}
// close the result set
if (rs != null) {
rs.close();
}
// close the statement
if (pgqlStmt != null) {
pgqlStmt.close();
}
// close the connection
if (conn != null) {
conn.close();
}
}
}
}
The output for PgqlCreateExample1.java is:
+---------------------------------------------------------+ | first_name | birthday | name | score | +---------------------------------------------------------+ | Alan | 1995-05-25 17:00:00.0 | Music | 30.0 | | Claire | 1967-11-29 16:00:00.0 | Music | 20.0 | | Ben | 2007-02-14 16:00:00.0 | Sports | 10.0 | | Alan | 1995-05-25 17:00:00.0 | Sports | 20.0 | +---------------------------------------------------------+
Example 6-4 PgqlCreateExample2.java
This example shows how a create property graph statement without specifying any keys. Notice that the example creates tables Person, Hobby, and Hobbies, so they should not exist before running the example.
import java.sql.Connection;
import java.sql.Statement;
import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.pg.rdbms.pgql.PgqlStatement;
import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;
/**
* This example shows how to create a Property Graph from relational
* data stored in Oracle Database executing a PGQL create statement.
*/
public class PgqlCreateExample2
{
public static void main(String[] args) throws Exception
{
int idx=0;
String host = args[idx++];
String port = args[idx++];
String sid = args[idx++];
String user = args[idx++];
String password = args[idx++];
String graph = args[idx++];
Connection conn = null;
Statement stmt = null;
PgqlStatement pgqlStmt = null;
PgqlResultSet rs = null;
try {
//Get a jdbc connection
PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
pds.setURL("jdbc:oracle:thin:@"+host+":"+port +":"+sid);
pds.setUser(user);
pds.setPassword(password);
conn = pds.getConnection();
conn.setAutoCommit(false);
// Create relational data
stmt = conn.createStatement();
//Table Person
stmt.executeUpdate(
"create table Person( " +
" id NUMBER, " +
" name VARCHAR2(20), " +
" dob TIMESTAMP, " +
" CONSTRAINT pk_person PRIMARY KEY(id)" +
")");
// Insert some data
stmt.executeUpdate("insert into Person values(1,'Alan', DATE '1995-05-26')");
stmt.executeUpdate("insert into Person values(2,'Ben', DATE '2007-02-15')");
stmt.executeUpdate("insert into Person values(3,'Claire', DATE '1967-11-30')");
// Table Hobby
stmt.executeUpdate(
"create table Hobby( " +
" id NUMBER, " +
" name VARCHAR2(20), " +
" CONSTRAINT pk_hobby PRIMARY KEY(id)" +
")");
// Insert some data
stmt.executeUpdate("insert into Hobby values(1, 'Sports')");
stmt.executeUpdate("insert into Hobby values(2, 'Music')");
// Table Hobbies
stmt.executeUpdate(
"create table Hobbies( "+
" person NUMBER, "+
" hobby NUMBER, "+
" strength NUMBER, "+
" CONSTRAINT fk_hobbies1 FOREIGN KEY (person) REFERENCES Person(id), "+
" CONSTRAINT fk_hobbies2 FOREIGN KEY (hobby) REFERENCES Hobby(id)"+
")");
// Insert some data
stmt.executeUpdate("insert into Hobbies values(1, 1, 20)");
stmt.executeUpdate("insert into Hobbies values(1, 2, 30)");
stmt.executeUpdate("insert into Hobbies values(2, 1, 10)");
stmt.executeUpdate("insert into Hobbies values(3, 2, 20)");
//Commit changes
conn.commit();
// Get a PGQL connection
PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
// Create a PgqlStatement
pgqlStmt = pgqlConn.createStatement();
// Execute PGQL to create property graph
String pgql =
"Create Property Graph " + graph + " " +
"VERTEX TABLES ( " +
" Person " +
" Label people +
" PROPERTIES ALL COLUMNS," +
" Hobby " +
" Label hobby PROPERTIES ALL COLUMNS EXCEPT(id)" +
")" +
"EDGE TABLES (" +
" Hobbies" +
" SOURCE Person DESTINATION Hobby " +
" LABEL likes NO PROPERTIES" +
")";
pgqlStmt.execute(pgql);
// Execute a PGQL query to verify Graph creation
pgql =
"SELECT p.NAME AS person, p.DOB, h.NAME AS hobby " +
"FROM MATCH (p:people)-[e:likes]->(h:hobby) ON " + graph;
rs = pgqlStmt.executeQuery(pgql, "");
// Print the results
rs.print();
}
finally {
// close the sql statment
if (stmt != null) {
stmt.close();
}
// close the result set
if (rs != null) {
rs.close();
}
// close the statement
if (pgqlStmt != null) {
pgqlStmt.close();
}
// close the connection
if (conn != null) {
conn.close();
}
}
}
}
The output for PgqlCreateExample2.java is:
+-----------------------------------------+ | PERSON | DOB | HOBBY | +-----------------------------------------+ | Alan | 1995-05-25 17:00:00.0 | Music | | Claire | 1967-11-29 16:00:00.0 | Music | | Ben | 2007-02-14 16:00:00.0 | Sports | | Alan | 1995-05-25 17:00:00.0 | Sports | +-----------------------------------------+