Oracle Spatial and Graph – technical tips, best practices, and news from the product team

A Trick to Build Efficiently a Config for a Property Graph with Many Properties

Alan Wu

If you have used Oracle's property graph database, then you are very likely familiar with "graph config" concept. Basically, it contains all the information required to read a graph from a backend database and construct an in-memory snapshot of the graph. Specifically, the config includes the graph name, the underlying graph database information, and the list of vertex/edge properties that need to be included in the in-memory snapshot of the graph.

An example is as follows.

cfg = GraphConfigBuilder.forPropertyGraphRdbms().setJdbcUrl("jdbc:oracle:thin:@") 

      .setUsername("pg").setPassword("pg") .setName("lj1") .setMaxNumConnections(16)

      .setLoadEdgeLabel(false).addEdgeProperty("weight", PropertyType.DOUBLE, "1000000")  .build();

Now, it is important that one lists out every single property to be included. If a property P is not declared in the config, then you cannot use P in a follow-up PGQL query. Declaring all properties required can be a tedious task when a graph happens to have many properties. 

Fortunately, with a bit of SQL trick, this can be simplified. Say, we have a graph named myGraph stored in an Oracle Database (either on-prem or in Oracle Database Cloud Service). The following SQL lists out all the property keys and the data type of the property values.

-- "1" denotes an integer property data type. 

SQL>  select distinct k, t from myGraphVT$;





To generate the necessary config setting, I use a SQL statement as follows and it would produce a list of addVertexProperty commands in one shot.

SQL> set heading off;
SQL> set pagesize 9999

SQL> select distinct '.addVertexProperty("' || k || '", PropertyType.STRING, "null")' from myGraphVT$;

.addVertexProperty("LOB", PropertyType.STRING, "null")
.addVertexProperty("Label", PropertyType.STRING, "null")
.addVertexProperty("Type", PropertyType.STRING, "null")
.addVertexProperty("Address", PropertyType.STRING, "null")
.addVertexProperty("Name", PropertyType.STRING, "null")
.addVertexProperty("Web", PropertyType.STRING, "null")

The rest of the work is a simple copy & paste. 



Join the discussion

Comments ( 1 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.