X

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

  • November 1, 2017

How to enable Oracle Database Cloud Service with Property Graph Capabilities

Charles Wang
Senior Development Manager

With Oracle Database release 12.2, Oracle property graph capability is available from Oracle Database side as well. Oracle property graph feature is available with the Oracle Spatial and Graph product.  This feature provides graph data management and analysis. The property graph feature is also a feature of Oracle Big Data Spatial and Graph.  You can refer to Oracle Big Data Spatial and Graph for more details on this product.

This blog tells you how to enable property graph capabilities with an existing Oracle Database Cloud service. For tutorial on how to create an Oracle Database Cloud service, please visit the link, or you can download the HOW-TO document at the end of this blog.

Enable 32K Varchar2, which is required by property graph

A detailed description of the following steps can be found in:

https://docs.oracle.com/database/121/REFRN/GUID-D424D23B-0933-425F-BC69-9C0E6724693C.htm#REFRN10321

Before we do that, we may check the default MAX_STRING value

SQL> show parameters max_string;

SQL> ALTER SESSION SET CONTAINER=CDB$ROOT;

Session altered.

SQL> ALTER SYSTEM SET max_string_size=extended SCOPE=SPFILE;

System altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup upgrade;

ORACLE instance started.

Database opened.

SQL> ALTER PLUGGABLE DATABASE ALL OPEN UPGRADE;

Pluggable database altered.

SQL> quit

Disconnected from Oracle Database 12c EE High Perf Release 12.2.0.1…

[oracle@GDBCS1 dbhome_1]$ cd $ORACLE_HOME/rdbms/admin

[oracle@GDBCS1 admin]$ mkdir /u01/utl32k_cdb_pdbs_output

[oracle@GDBCS1 admin]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS -d $ORACLE_HOME/rdbms/admin -l '/u01/utl32k_cdb_pdbs_output' -b utl32k_cdb_pdbs_output utl32k.sql

Enter Password:

catcon.pl: completed successfully

[oracle@GDBCS1 admin]$ sqlplus / as sysdba

SQL> shutdown immediate;

SQL> startup

SQL> ALTER PLUGGABLE DATABASE ALL OPEN READ WRITE;

Pluggable database altered.

SQL> quit

Disconnected from Oracle Database 12c EE High Perf Release 12.2.0.1…

[oracle@GDBCS1 admin]$ mkdir /u01/utlrp_cdb_pdbs_output

[oracle@GDBCS1admin]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS -d $ORACLE_HOME/rdbms/admin -l '/u01/utlrp_cdb_pdbs_output' -b utlrp_cdb_pdbs_output utlrp.sql

Enter Password:

catcon.pl: completed successfully

[oracle@GDBCS1 admin]$

Validate the change made to MAX_STRING_SIZE. To verify, run the following commands and you should see the value of max_string_size changed to "EXTENDED"

[oracle@GDBCS1 admin]$ sqlplus / as sysdba

SQL> alter session set container=PDB1;

Session altered.

SQL> show parameters max_string;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

max_string_size string EXTENDED

Create a schema for later use by enabling user ‘scott’

SQL> conn / as sysdba

Connected.

SQL> alter session set container=PDB1;

Session altered.

SQL> alter user scott identified by tiger789#_O0 account unlock;

User altered.

Apply a patch to enable database support for PGQL

Download this patch: https://support.oracle.com/epmos/faces/PatchDetail?patchId=25640325

Notes on transferring files to/from DBCS are here https://docs.oracle.com/en/cloud/paas/database-dbaas-cloud/csdbi/copy-files-node.html

In this exercise, we will use WinSCP to upload the patch file into the DBCS with the private key.

Follow the readme and apply the patch as described below.

[oracle@GDBCS1 gpatch]$ cd 25640325/

[oracle@GDBCS1 25640325]$ ls

p25640325_osg_pg_update.zip  README.txt

[oracle@GDBCS1 25640325]$ rm -rf $ORACLE_HOME/md/property_graph/*

[oracle@GDBCS1 25640325]$ rm -f $ORACLE_HOME/md/admin/*opg*

 [oracle@GDBCS1 25640325]$ cd $ORACLE_HOME

[oracle@GDBCS1 dbhome_1]$ unzip /home/oracle/gpatch/25640325/p25640325_osg_pg_update.zip

Archive:  /home/oracle/gpatch/25640325/p25640325_osg_pg_update.zip

   creating: md/property_graph/examples/

   creating: md/property_graph/pyopg/

… …

Re-install Oracle Spatial and Graph Property Graph schema PL/SQL packages

cd $ORACLE_HOME/md/admin/

sqlplus /  as sysdba       

alter session set container=pdb1;

@catopg.sql

 

Loading a Property Graph in Oracle Database using Groovy

Before you can start building a text index or running graph analytics, you will first need to get the graph data loaded into the database. The following shows an example flow of using the parallel property graph data loader to ingest a property graph that represents a small social network into an Oracle Database (12c Release 2). This property graph data is encoded in flat file format (.opv/.ope).

DBCS includes a built-in Groovy shell (based on the Gremlin Groovy shell script). With this command line shell interface, you can perform graph operations using Java APIs.

cd $ORACLE_HOME/md/property_graph/dal/groovy
$ sh ./gremlin-opg-rdbms.sh 
--------------------------------
opg-oracledb> // you need to customize JdbcURL, Username, and Password in the following graph config setting.

opg-oracledb> cfg =GraphConfigBuilder.forPropertyGraphRdbms().setJdbcUrl("jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(HOST= 129.150.84.48)(PORT=1521)(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=PDB1.uspm020.oraclecloud.internal)))").setUsername("scott").setPassword("tiger789#_O0").setName("connections")

.setMaxNumConnections(8).setLoadEdgeLabel(false).addVertexProperty("name",PropertyType.STRING, "default_name").addEdgeProperty("weight",PropertyType.DOUBLE, "1000000").build(); 
opg-oracledb> opg = OraclePropertyGraph.getInstance(cfg);

opg-oracledb> opg.clearRepository();   // start from scratch
opg-oracledb> opgdl=OraclePropertyGraphDataLoader.getInstance();
opg-oracledb> vfile="../../data/connections.opv"  //vertex flat file
opg-oracledb> efile="../../data/connections.ope"  //edge flat file
opg-oracledb> opgdl.loadData(opg, vfile, efile, 2,10000, true, null);
opg-oracledb> opg.countVertices()
==>78
opg-oracledb> opg.countEdges()
==>164

Run Graph Analysis and PGQL with an embedded PGX mode

Find these people who has direct link between.

// Create an in memory analytics session and analyst
opg-oracledb> session=Pgx.createSession("session_ID_1");
opg-oracledb> analyst=session.createAnalyst();

// Read graph data from database into memory
opg-oracledb> pgxGraph =session.readGraphWithProperties(opg.getConfig(),true);

//count triangles

analyst.countTriangles(pgxGraph, true);

==>22

opg-oracledb> pgxResultSet = pgxGraph.queryPgql("SELECT n,m WHERE (n) -> (m)")

==>PgqlResultSetImpl[graph=connections,numResults=164]

opg-oracledb> pgxResultSet.print(10);

+------------------------------------+

| n                | m               |

+------------------------------------+

| PgxVertex[ID=2]  | PgxVertex[ID=1] |

| PgxVertex[ID=3]  | PgxVertex[ID=1] |

| PgxVertex[ID=6]  | PgxVertex[ID=1] |

| PgxVertex[ID=7]  | PgxVertex[ID=1] |

| PgxVertex[ID=8]  | PgxVertex[ID=1] |

| PgxVertex[ID=9]  | PgxVertex[ID=1] |

| PgxVertex[ID=10] | PgxVertex[ID=1] |

| PgxVertex[ID=11] | PgxVertex[ID=1] |

| PgxVertex[ID=12] | PgxVertex[ID=1] |

| PgxVertex[ID=19] | PgxVertex[ID=1] |

+------------------------------------+

Congratulations! you have now set up your Oracle Database Cloud Service instance with property graph capabilities, loaded a sample property graph data, and started performing property graph analysis with an embedded pgx, next you may follow another HOW-TO tutorial to deploy your own pgx service to a WebLogic server and access it remotely.

HOW-TO document is available HERE

 

 

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.