X

Oracle Big Data Spatial and Graph - technical tips, best practices, and news from the product team

A Robust and Easy Way to Create Flat Files (.opv/.ope) on BDSG

Alan Wu
Architect

Today I got a question on a data loading problem that happened during a parallel load of a PG flat file (.opv/.ope). This made me realize it is indeed tricky for a user to manually create those records in the flat file format. Some of you may even question who on earth designed such a weird looking format with all those commas. Well, that would be me. Believe it or not, there is a reason for such a format :)  For users who are familiar with Oracle Database external tables, you may see that it is trivial to define an external table on top of .opv/.ope and ingest the graph data into Oracle Database, really efficiently.

Now, back to the original problem. Fortunately, the product also offers two utility APIs to help a user to manually and incrementally create .opv/.ope. Note that, if you have a CSV or a relational data source, then there are utility APIs to convert the whole CSV or tables (views) into flat files in one shot. There is no need to do the graph generation incrementally.  As usual, I am using Java code snippets in Groovy.

cd /opt/oracle/oracle-spatial-graph/property_graph/dal/groovy/

osV = new FileOutputStream("/tmp/g.opv")
osE = new FileOutputStream("/tmp/g.ope")

// Say there is a person Jon with some attributes (properties).

// The following static API outputVertexRecord is able to handle escaping/comma placement, for you.

// Note that different data types are used here for illustration purpose.

OraclePropertyGraphUtilsBase.outputVertexRecord(osV, 1l, "name", "Jon");
OraclePropertyGraphUtilsBase.outputVertexRecord(osV, 1l, "salary", 18000.5d);
OraclePropertyGraphUtilsBase.outputVertexRecord(osV, 1l, "age", 18);

osV.flush();

Now, if we take a look at the generated .opv file, you will see

[oracle@bigdatalite ~]$ cat /tmp/g.opv
1,name,1,Jon,,
1,salary,4,,18000.5,
1,age,2,,18,
 

So far so good, let's introduce a second vertex Mary who is married.

OraclePropertyGraphUtilsBase.outputVertexRecord(osV, 2l, "name", "Mary");
OraclePropertyGraphUtilsBase.outputVertexRecord(osV, 2l, "married", Boolean.TRUE);

osV.flush();

==>
[oracle@bigdatalite ~]$ cat /tmp/g.opv
1,name,1,Jon,,
1,salary,4,,18000.5,
1,age,2,,18,
2,name,1,Mary,,
2,married,6,Y,,


Finally, let's create an edge (denoting friend relationship) that links these two vertices together. Assume this friendship started on a fine day in 2004,

sdf=new java.text.SimpleDateFormat("mm/dd/yyyy")
OraclePropertyGraphUtilsBase.outputEdgeRecord(osE, 100l, 1l, 2l, "friend_of", "started_on", sdf.parse("01/02/2004"));

osE.flush()
 

And let's take a look at the generated .ope file.

[oracle@bigdatalite ~]$ cat /tmp/g.ope
100,1,2,friend_of,started_on,5,,,2004-01-02T00:01:00.000-05:00
[oracle@bigdatalite ~]$
 

Cheers,

Zhe Wu

Be the first to comment

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