7.1 PGQLを使用したSQLプロパティ・グラフの作成

CREATE PROPERTY GRAPH PGQL DDL文を使用して、データベース表からSQLプロパティ・グラフを作成できます。

次の例では、CSVファイルからのデータのインポートで作成されたデータセット表を使用します。

• JShell
• Java
• Python

opg4j> var jdbcUrl="jdbc:oracle:thin:@<host_name>:<port>/<service>"
opg4j> var conn = DriverManager.getConnection(jdbcUrl,"<username>","<password>");
opg4j> var pgqlConn = PgqlConnection.getConnection(conn)
opg4j> var pgqlStmt = pgqlConn.createStatement()
opg4j> var pgql = 
...> "CREATE PROPERTY GRAPH bank_sql_pg "
...> + "VERTEX TABLES ( BANK_ACCOUNTS "
...> + "KEY (ID) "
...> + "LABEL Account "
...> + "PROPERTIES (ID, NAME) "
...> + ") "
...> + "EDGE TABLES ( BANK_TXNS "
...> + "KEY (TXN_ID) "
...> + "SOURCE KEY (FROM_ACCT_ID) REFERENCES BANK_ACCOUNTS (ID) "
...> + "DESTINATION KEY (TO_ACCT_ID) REFERENCES BANK_ACCOUNTS (ID) "
...> + "LABEL TRANSFER "
...> + "PROPERTIES (FROM_ACCT_ID, TO_ACCT_ID, AMOUNT, DESCRIPTION) "
...> + ") OPTIONS (PG_SQL) "
opg4j> pgqlStmt.execute(pgql)

import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlStatement;


/*
 * This example creates a SQL property graph.
 */
public class CreateSQLGraph
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String jdbcUrl            = args[idx++];
    String username           = args[idx++];
    String password           = args[idx++];
    String graph              = args[idx++];


    Connection conn = null;
    PgqlStatement pgqlStmt = null;

    try {
      //Get a jdbc connection
      conn = DriverManager.getConnection(jdbcUrl, username, password);
      conn.setAutoCommit(false);


      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);

      // Create a PGQL Statement
      pgqlStmt = pgqlConn.createStatement();
      // Execute PGQL Query
      String pgql =
        "CREATE PROPERTY GRAPH " + graph + " " +
        "VERTEX TABLES ( bank_accounts " +
        "KEY (id) " +
        "LABEL Account " +
        "PROPERTIES (id, name)" +
        ") " +
        "EDGE TABLES ( bank_txns " +
        "KEY (txn_id) " +
        "SOURCE KEY (from_acct_id) REFERENCES bank_accounts (id) " +
        "DESTINATION KEY (to_acct_id) REFERENCES bank_accounts (id) " +
        "LABEL Transfer " +
        "PROPERTIES (from_acct_id, to_acct_id, amount, description)" +
        ") OPTIONS (PG_SQL) ";

      // Print the results
      pgqlStmt.execute(pgql);
    }
    finally {
      // close the statement
      if (pgqlStmt != null) {
         pgqlStmt.close();
         }
      // close the connection
      if (conn != null) {
         conn.close();
         }
      }
  }
}

>>> pgql_conn = opg4py.pgql.get_connection("<username>","<password>", "jdbc:oracle:thin:@<host_name>:<port>/<service>")
>>> pgql_statement = pgql_conn.create_statement()
>>> pgql = """
... CREATE PROPERTY GRAPH bank_sql_pg
... VERTEX TABLES (
...   BANK_ACCOUNTS
...     KEY (ID)
...     LABEL Account
...     PROPERTIES (ID, NAME)
... )
... EDGE TABLES (
...   BANK_TXNS
...     KEY (TXN_ID)
...     SOURCE KEY (FROM_ACCT_ID) REFERENCES BANK_ACCOUNTS (ID)
...     DESTINATION KEY (TO_ACCT_ID) REFERENCES BANK_ACCOUNTS (ID)
...     LABEL TRANSFER
...     PROPERTIES (FROM_ACCT_ID, TO_ACCT_ID, AMOUNT, DESCRIPTION)
... ) OPTIONS (PG_SQL)
... """
>>> pgql_statement.execute(pgql)
False

PGQLの概念を理解するには、PGQLを使用したプロパティ・グラフの作成を参照してください。