A.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}]Parent topic: Handling Property Graphs Using a Two-Tables Schema