1. Property Graph Developer’s Guide
  2. Supplementary Information for Property Graph Support
  3. Handling Property Graphs Using a Two-Tables Schema
  4. Storing Data in a Property Graph Using a Two-Tables Schema

A.2 Storing Data in a Property Graph Using a Two-Tables Schema

To load a set of vertices into a vertex table using a two-tables schema, you can use the API OraclePropertyGraphUtils.writeTwoTablesGraphVertexAndProperties. This operation takes an array of Iterable (or Iterator) of TinkerPop Blueprints Vertex objects, and reads out the ID and the values for the properties defined in the vertex table schema. Based on this information, the vertex is later inserted as a new row in the vertex table. Note that if a vertex does not include a property defined in the schema, the value for that associated column is set to NULL.

The following code snippet creates a property graph employeesGraphDAL using the OraclePropertyGraph API, and loads two vertices and an edge. Then, it creates a vertex table employeesNodes using a two-tables schema and populates it with the data from the vertices in employeesGraphDAL. Note that the property email in the vertex v1 is not loaded into the employeesNode table because it is not defined in the schema. Also, the property SSN for vertex v2 is set NULL because it is not defined in the vertex. 

// Create employeesGraphDAL
import oracle.pg.rdbms.*;
Oracle oracle = new Oracle(jdbcURL, username, password);
OraclePropertyGraph opgEmployees
                  = OraclePropertyGraph.getInstance(oracle, "employeesGraphDAL");

// Create vertex v1 and assign it properties as key-value pairs
Vertex v1 = opgEmployees.addVertex(1l);
v1.setProperty("age",  Integer.valueOf(31));
v1.setProperty("name", "Alice");
v1.setProperty("address", "Main Street 12");
v1.setProperty("email", "alice@mymail.com");
v1.setProperty("SSN", "123456789");

Vertex v2 = opgEmployees.addVertex(2l);
v2.setProperty("age",  Integer.valueOf(27));
v2.setProperty("name", "Bob");
v2.setProperty("adress", "Sesame Street 334"); 
       
// Add edge e1
Edge e1 = opgEmployees.addEdge(1l, v1, v2, "managerOf");
e1.setProperty("weight", 0.5d);

opgEmployees.commit();

// Prepare the vertex table using a Two Tables schema
import oracle.pgx.common.types.PropertyType;
List<String> propertyNames = new ArrayList<String>();
propertyNames.addAll(new String[4]{ "name", "age", "address", "SSN" });

List<PropertyType> = new ArrayList<PropertyType>();
propertyType.add(PropertyType.STRING);
propertyType.add(PropertyType.INTEGER);
propertyType.add(PropertyType.STRING);
propertyType.add(PropertyType.STRING);

Connection conn 
     = opgEmployees.getOracle().clone().getConnection(); /* Clone the connection
                                                            from the property graph 
                                                            instance */    
OraclePropertyGraphUtils.prepareTwoTablesGraphVertexTab(conn /* Connection object */,
                                            pg /* table owner */, 
                                            "employeesNodes" /* vertex table name */, 
                                            propertyNames /* property names */, 
                                            propertyTypes /* property data types */,
                                            "pgts" /* table space */, 
                                            null /* storage options */, 
                                            true /* no logging */);

// Get the vertices from the employeesDAL graph
Iterable<Vertex> vertices = opgEmployees.getVertices();

// Load the vertices into the vertex table using a Two-Tables schema
Connection[] conns = new Connection[1]; /* the connection array size defines the
                                           Degree of parallelism (multithreading)
                                        */
conns[1] = conn;
OraclePropertyGraphUtils.writeTwoTablesGraphVertexAndProperties(
                                           conn /* Connectionobject */,
                                            pg /* table owner */, 
                                            "employeesNodes" /* vertex table name */,  
                                            1000 /* batch size*/, 
                                            new Iterable[] {vertices} /* array of 
                                                                vertex iterables */);

To load a set of edges into an edge table using a two-tables schema, you can use the API OraclePropertyGraphUtils.writeTwoTablesGraphEdgesAndProperties. This operation takes an array of Iterable (or Iterator) of Blueprints Edge objects, and reads out the ID, EL, SVID, DVID, and the values for the properties defined in the edge table schema. Based on this information, the edge is later inserted as a new row in the edge table. Note that if an edge does not include a property defined in the schema, the value for that given column is set to NULL.

The following code snippet creates a property graph employeesGraphDAL using the OraclePropertyGraph API, and loads two vertices and an edge. Then, it creates a vertex table organizationEdges using a two-tables schema, and populates it with the data from the edges in employeesGraphDAL. 

// Create employeesGraphDAL
import oracle.pg.rdbms.*;
Oracle oracle = new Oracle(jdbcURL, username, password);
OraclePropertyGraph opgEmployees
                  = OraclePropertyGraph.getInstance(oracle, "employeesGraphDAL");

// Create vertex v1 and assign it properties as key-value pairs
Vertex v1 = opgEmployees.addVertex(1l);
v1.setProperty("age",  Integer.valueOf(31));
v1.setProperty("name", "Alice");
v1.setProperty("address", "Main Street 12");
v1.setProperty("email", "alice@mymail.com");
v1.setProperty("SSN", "123456789");

Vertex v2 = opgEmployees.addVertex(2l);
v2.setProperty("age",  Integer.valueOf(27));
v2.setProperty("name", "Bob");
v2.setProperty("adress", "Sesame Street 334"); 
       
// Add edge e1
Edge e1 = opgEmployees.addEdge(1l, v1, v2, "managerOf");
e1.setProperty("weight", 0.5d);

opgEmployees.commit();

// Prepare the edge table using a Two Tables schema
import oracle.pgx.common.types.PropertyType;
       Connection conn 
            = opgEmployees.getOracle().clone().getConnection(); /* Clone the connection
                                                                   from the property graph 
                                                                   instance */    
List<String> propertyNames = new ArrayList<String>();
propertyNames.addAll(new String[1]{ "weight" });

List<PropertyType> = new ArrayList<PropertyType>();
propertyType.add(PropertyType.DOUBLE);
OraclePropertyGraphUtils.prepareTwoTablesGraphEdgeTab(conn /* Connection object */,
                                            pg /* table owner */, 
                                            organizationEdges" /* edge table name */, 
                                            propertyNames /* property names */, 
                                            propertyTypes /* property data types */,
                                            "pgts" /* table space */, 
                                            null /* storage options */, 
                                            true /* no logging */);

// Get the edges from the employeesDAL graph
Iterator<Edge> edges = opgEmployees.getEdges().iterator();

// Load the edges into the edges table using a Two-Tables schema
Connection[] conns = new Connection[1]; /* the connection array size defines the
                                           Degree of parallelism (multithreading)
                                        */
conns[1] = conn;
OraclePropertyGraphUtils.writeTwoTablesGraphVertexAndProperties(conn /* Connection 
                                                                        object */,
                                            pg /* table owner */, 
                                            "organizationEdges" /* edge table 
                                                                   name */,  
                                            1000 /* batch size*/, 
                                            new Iterator[] {edges} /* array of 
                                                           iterator of edges */);

To optimize the performance of the storing operations, you can specify a set of flags and hints when calling the writeTwoTablesGraph APIs. These hints include:

  • DOP: Degree of parallelism. The size of the connection array defines the degree of parallelism to use when loading the data. This determines the number of chunks to generate when reading the Iterables as well as the number of loader threads to use when loading the data into the table.

  • Batch Size: An integer specifying the batch size to use for Oracle update statements in batching mode. A recommended batch size is 1000.

Parent topic: Handling Property Graphs Using a Two-Tables Schema