6.9.1.2 CREATE PROPERTY GRAPH文によるプロパティ・グラフの作成

PGQLを使用して、リレーショナル・データベース表からプロパティ・グラフを作成できます。CREATE PROPERTY GRAPH文では、頂点に変換される一連の頂点の表と、エッジに変換される一連のエッジの表を定義します。各表にはキー、ラベルおよび一連の列プロパティを指定できます。列タイプCHAR、NCHAR、VARCHAR、VARCHAR2、NVARCHAR2、NUMBER、LONG、FLOAT、DATE、TIMESTAMPおよびTIMESTAMP WITH TIMEZONEは、CREATE PROPERTY GRAPH列プロパティでサポートされています。

CREATE PROPERTY GRAPH文がコールされると、グラフのプロパティ・グラフ・スキーマが作成され、データがソース表からプロパティ・グラフ・スキーマ表にコピーされます。グラフは1回かぎりのコピーとして作成され、ソース・データとの同期は自動的には保持されません。

例6-3 PgqlCreateExample1.java

この例は、一連のリレーショナル表からプロパティ・グラフを作成する方法を示しています。この例では表Person、HobbyおよびHobbiesが作成されるため、この例を実行する前にはこれらの表が存在しないことに注意してください。この例は、プロパティ・グラフに対して問合せを実行する方法も示しています。

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();
      }
    }
  }
}

PgqlCreateExample1.javaの出力は次のとおりです。

+---------------------------------------------------------+
|   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    |
+---------------------------------------------------------+

例6-4 PgqlCreateExample2.java

この例は、キーを指定せずにプロパティ・グラフ文を作成する方法を示しています。この例では表Person、HobbyおよびHobbiesが作成されるため、この例を実行する前にはこれらの表が存在しないことに注意してください。

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();
      }
    }
  }
}

PgqlCreateExample2.javaの出力は次のとおりです。

+-----------------------------------------+
| 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 |
+-----------------------------------------+