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
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