6.9.2.3 Creating a Property Graph View
You can create a property graph view using the CREATE PROPERTY GRAPH
statement.
Creating a Property Graph View Using JShell
To create a property graph view using JShell, execute the following steps as shown:
- Launch the JShell to work with the database as
shown:
./bin/opg4j --no_connect
- Connect to the Oracle Database as
shown:
opg4j> var jdbcUrl="jdbc:oracle:thin:@<host_name>:<port>/<service>" jdbcUrl ==> "jdbc:oracle:thin:@<host_name>:<port>/<db_service>" opg4j> var conn = DriverManager.getConnection(jdbcUrl,"<username>","<password>"); conn ==> oracle.jdbc.driver.T4CConnection@3e9a20e
In the preceding code:<host_name>
: database host<port>
: database port<service>
: database SID<username>
: user name<password>
: database password
- Create a PGQL Connection as
shown:
opg4j> var pgqlConn = PgqlConnection.getConnection(conn) pgqlConn ==> oracle.pg.rdbms.pgql.PgqlConnection@4301fa39
- Execute the following comands as shown to create a property graph
view:
opg4j> var pgqlStmt = pgqlConn.createStatement(); //create a PGQL Statement pgqlStmt ==> oracle.jdbc.driver.OracleStatementWrapper@6f976c opg4j> String pgql = ...> "CREATE PROPERTY GRAPH <pgview> " ...> + "VERTEX TABLES ( bank_nodes AS Accounts " ...> + "KEY (id) " ...> + "LABEL Accounts " ...> + "PROPERTIES (id, label) " ...> + ") " ...> + "EDGE TABLES ( bank_edges_amt AS Transfers " ...> + "KEY (src_id, dest_id, amount) " ...> + "SOURCE KEY (src_id) REFERENCES Accounts (id) " ...> + "DESTINATION KEY (dest_id) REFERENCES Accounts (id) " ...> + "LABEL Transfers " ...> + "PROPERTIES (src_id, dest_id, amount, label) " ...> + ") OPTIONS (PG_VIEW) "; opg4j> pgqlStmt.execute(pgql); $8 ==> false
The property graph view is created.
Creating a Property Graph View Using Java
The following example shows how to create a property graph view from the
relational database tables using Java. The examples assumes the tables
bank_nodes
and bank_edges_amt
already exist in the
database.
import java.sql.Connection;
import java.sql.Statement;
import oracle.pg.rdbms.pgql.PgqlConnection;
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 View from relational
* data stored in Oracle Database by executing a PGQL create statement.
*/
public class CreatePgView
{
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 pgview = args[idx++];
Connection conn = null;
PgqlStatement pgqlStmt = 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);
// Get a PGQL connection
PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
// Create a PgqlStatement
pgqlStmt = pgqlConn.createStatement();
// Execute PGQL to create property graph view
String pgql =
"CREATE PROPERTY GRAPH " + pgview + " " +
"VERTEX TABLES ( bank_nodes as Accounts " +
"KEY (id) " +
"LABEL \"Accounts\"" +
"PROPERTIES (id, label)" +
") " +
"EDGE TABLES ( bank_edges_amt as Transfers " +
"KEY (src_id, dest_id, amount) " +
"SOURCE KEY (src_id) REFERENCES Accounts (id) " +
"DESTINATION KEY (dest_id) REFERENCES Accounts (id) " +
"LABEL \"Transfers\"" +
"PROPERTIES (src_id, dest_id, amount, label)" +
") OPTIONS (PG_VIEW) ";
pgqlStmt.execute(pgql);
}
finally {
// close the statement
if (pgqlStmt != null) {
pgqlStmt.close();
}
// close the connection
if (conn != null) {
conn.close();
}
}
}
}
You can verify the property graph view creation by checking the metadata tables that get created in the Oracle Database.
Parent topic: Executing PGQL Queries Against Property Graph Views