B.3 Reading Data from a Property Graph Using a Two-Tables Schema

To read a subset of vertices from a vertex table using a two-tables schema, you can use the API OraclePropertyGraphUtils.readTwoTablesGraphVertexAndProperties. This operation returns an array of ResultSet objects with all the rows found in the corresponding splits of the vertex table. Each ResultSet object in the array uses one of the connections provided to fetch the vertex rows from the corresponding split. The splits are determined by the specified number of total splits.

An integer ID (in the range of [0, N - 1]) is assigned to the splits in the vertex table with N splits. This way, the subset of splits queried will consist of those splits with ID value in the range between the start split ID and the start split ID plus the size of the connection array. If the sum is greater than the total number of splits, then the subset of splits queried will consist of those splits with ID in the range of [start split ID, N - 1].

The following code reads all vertices from a vertex table using a two-tables schema using a total of 1 split. Note that you can easily create an array of Blueprints Vertex Iterables by executing the API on OraclePropertyGraph. The vertices retrieved will include all the properties defined in the vertex table schema.

ResultSet[] rsAr = readTwoTablesGraphVertexAndProperties(conns, 
                                                 "pg" /* table owner */,
                                                 "employeeNodes /* vertex table 
                                                                   name */,                                                                                              
                                                 1 /* Total Splits*/, 
                                                 0 /* Start Split);

Iterable<Vertex>[] vertices = getVerticesPartitioned(rsAr /* ResultSet array */,
                                                     true /* skip store to cache */,
                                                     null /* vertex filter 
                                                              callback */,
                                                     null /* optimization flag */);

To optimize reading performance, you can specify the list of property names to retrieve for each vertex read from the table.

The following code creates a property graph employeesGraphDAL using the OraclePropertyGraph API, and loads two vertices and an edge. Then, it creates a vertex table employeNodes using a two-tables schema, and populates it with the data from the vertices in employeesGraphDAL. Finally, it reads the vertices out of the vertex table using only the name property.

// 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 /* Connection 
                                                                        object */,
                                            pg /* table owner */, 
                                            "employeesNodes" /* vertex table name */,  
                                            1000 /* batch size*/, 
                                            new Iterable[] {vertices} /* array of 
                                                                vertex iterables */); 

// Read the vertices (using only name property)
List<String> vPropertyNames = new ArrayList<String>();
vPropertyNames.add("name");
ResultSet[] rsAr = readTwoTablesGraphVertexAndProperties(conns, 
                                                 "pg" /* table owner */,
                                                 "employeeNodes /* vertex table 
                                                                   name */,  
                                                 vPropertyNames /* list of property 
                                                                   names */,
                                                 1 /* Total Splits*/, 
                                                 0 /* Start Split);

Iterable<Vertex>[] vertices = getVerticesPartitioned(rsAr /* ResultSet array */,
                                                     true /* skip store to cache */,
                                                     null /* vertex filter 
                                                              callback */,
                                                     null /* optimization flag */);

for (int idx = 0; vertices.length; idx++) {
  Iterator<Vertex> it = vertices[idx].iterator();
  while (it.hasNext()) {
    System.out.println(it.next());
  }
}

The preceding code produces output similar to the following:

Vertex ID 1 {name:str:Alice}
Vertex ID 2 {name:str:Bob}

To read a subset of edges from an edge table using a two-tables schema, you can use the API OraclePropertyGraphUtils.readTwoTablesGraphEdgeAndProperties. This operation returns an array of ResultSet objects with all the rows found in the corresponding splits of the vertex table. Each ResultSet object in the array uses one of the connections provided to fetch the vertex rows from the corresponding split. The splits are determined by the specified number of total splits.

Similar to what is done for reading vertices, an integer ID (in the range of [0, N - 1]) is assigned to the splits in the vertex table with N splits. The subset of splits queried will consist of those splits with ID value in the range between the start split ID and the start split ID plus the size of the connection array.

The following code creates a property graph employeesGraphDAL using the OraclePropertyGraph API, and loads two vertices and an edge. Then, it creates an edge table organizationEdges using a two-tables schema, and populates it with the data from the edges in employeesGraphDAL. Finally, it reads the edges out of table using only the name weight.

       // 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;
List<String> propertyNames = new ArrayList<String>();
propertyNames.addAll(new String[4]{ "weight" });

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

       Connection conn 
            = opgEmployees.getOracle().clone().getConnection(); /* Clone the connection
                                                                   from the property graph 
                                                                   instance */    
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
Iterable<Edge> edges = 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.writeTwoTablesGraphEdgeAndProperties(conn /* Connection 
                                                                        object */,
                                            pg /* table owner */, 
                                            organizationEdges" /* edge table name */,  
                                            1000 /* batch size*/, 
                                            new Iterable[] {edges} /* array of 
                                                                edge iterables */); 

// Read the edges (using only weight property)
List<String> ePropertyNames = new ArrayList<String>();
ePropertyNames.add("weight");
ResultSet[] rsAr = readTwoTablesGraphVertexAndProperties(conns, 
                                                 "pg" /* table owner */,
                                                 "organizationEdges /* edge table 
                                                                   name */,  
                                                 ePropertyNames /* list of property 
                                                                   names */,                                                                                            
                                                 1 /* Total Splits*/, 
                                                 0 /* Start Split);

Iterable<Edge>[] edges = getEdgesPartitioned(rsAr /* ResultSet array */,
                                                     true /* skip store to cache */,
                                                     null /* edge filter 
                                                              callback */,
                                                     null /* optimization flag */);

for (int idx = 0; edges.length; idx++) {
  Iterator<Edge> it = edges[idx].iterator();
  while (it.hasNext()) {
    System.out.println(it.next());
  }
}

The preceding code produces output similar to the following:

Edge ID 1 from Vertex ID 1 {} =[references]=> Vertex ID 2 {} edgeKV[{weight:dbl:0.5}]