Oracle 12.2 Property Graph

Oracle+12.2+Property+Graph.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[`@rmoff`](https://twitter.com/rmoff/) / March 10, 2017"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "_Make sure you have install `pyopg` in your Python environment in which Jupyter is running._\n",
    "\n",
    "Launch Jupyter using the following statements which will set up the environment as required: \n",
    "\n",
    "    export OPG_HOME=/u01/app/oracle/product/12.2/db_1/md/property_graph\n",
    "    export OPG_JARS=\"$OPG_HOME/lib/*.jar\"\n",
    "    export OPG_CP=`ls $OPG_JARS | xargs | tr ' ' ':'`\n",
    "\n",
    "    cd $OPG_HOME/pyopg\n",
    "    jupyter-notebook --ip=192.168.56.101 --no-browser"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Class loading done\n"
     ]
    }
   ],
   "source": [
    "from pyopg.core import *"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Connect to the Property Graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "opg=connectRDBMS(jdbc_url=\"jdbc:oracle:thin:@127.0.0.1:1521/orcl\",\n",
    "                 username='scott',\n",
    "                 password='oracle',\n",
    "                 graph_name='panama'\n",
    "                )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Examine a Vertex"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Name\tPortcullis TrustNet Chambers P.O. Box 3444 Road Town- Tortola British Virgin Islands (w.e.f 9 December 2005)\n",
      "Country\tVirgin Islands- British\n",
      "Country Codes\tVGB\n",
      "Type\tAddress\n",
      "ID\t236724\n",
      "Source ID\tOffshore Leaks\n"
     ]
    }
   ],
   "source": [
    "v=getVertexDict(236724)\n",
    "for key in v:\n",
    "    print '%s\\t%s' % (key,v[key])    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Entity incorporation.date\t15-OCT-2003\n",
      "Name\tR & P Enterprises Limited\n",
      "Countries\tNot identified;Hong Kong\n",
      "Entity note\t\n",
      "Entity address\tCompany Kit Limited Unit A- 6/F Shun On Comm Bldg. 112-114 Des Voeux Road C.- Hong Kong  SAR RT BVI\n",
      "Entity status\tDead\n",
      "Entity struck.off.date\t\n",
      "ID\t230070\n",
      "Entity company.type\tStandard Company under IBC Act\n",
      "Entity jurisdiction\tXXX\n",
      "Source ID\tOffshore Leaks\n",
      "Entity dorm.date\t01-MAY-2005\n",
      "Type\tEntity\n",
      "Country Codes\tXXX;HKG\n",
      "Entity jurisdiction.description\tUndetermined\n"
     ]
    }
   ],
   "source": [
    "v=getVertexDict(230070)\n",
    "for key in v:\n",
    "    print '%s\\t%s' % (key,v[key])    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# This returns the actual Vertex object\n",
    "v=opg.getVertex(236724)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "u'{Country:str:Virgin Islands- British, Country Codes:str:VGB, ID:int:236724, Name:str:Portcullis TrustNet Chambers P.O. Box 3444 Road Town- Tortola British Virgin Islands (w.e.f 9 December 2005), Source ID:str:Offshore Leaks, Type:str:Address}'"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "v.attributes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Examine Edge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "intermediary_of\n",
      "Edge ID 572604 from Vertex ID 11000130 {} =[intermediary_of]=> Vertex ID 10100965 {} edgeKV[{}]\n"
     ]
    }
   ],
   "source": [
    "e=opg.getEdge(572604)\n",
    "print e.getLabel()\n",
    "print e"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Launching In-Memory Analyst"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{\"name\":\"panama\",\"format\":\"pg\",\"loading\":{},\"db_engine\":\"RDBMS\",\"password\":\"oracle\",\"vertex_props\":[],\"max_num_connections\":2,\"username\":\"scott\",\"error_handling\":{},\"edge_props\":[],\"jdbc_url\":\"jdbc:oracle:thin:@127.0.0.1:1521/orcl\"}\n"
     ]
    }
   ],
   "source": [
    "pgx_analyst=analyst()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Run PageRank algorithm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "pr = pgx_analyst.pagerank()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of vertices in PageRank result: 838295\n"
     ]
    }
   ],
   "source": [
    "print 'Number of vertices in PageRank result: %d' % pr.size()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Page Rank value 0.010559003397444675\t Vertex ID 236724\tPortcullis TrustNet Chambers P.O. Box 3444 Road Town- Tortola British Virgin Islands (w.e.f 9 December 2005)\n",
      "--\n",
      "Page Rank value 0.001078650933915916\t Vertex ID 288469\tUnitrust Corporate Services Ltd. John Humphries House- Room 304 4-10 Stockwell Street- Greenwich London SE10 9JN\n",
      "--\n",
      "Page Rank value 7.961668833565952E-4\t Vertex ID 264051\tCompany Kit Limited Unit A- 6/F Shun On Comm Bldg. 112-114 Des Voeux Road C.- Hong Kong  SAR\n",
      "--\n",
      "Page Rank value 6.710783250168256E-4\t Vertex ID 285729\tSealight Incorporations Limited Room 1201- Connaught Commercial Building 185 Wanchai Road Wanchai- Hong Kong\n",
      "--\n",
      "Page Rank value 5.961377743232271E-4\t Vertex ID 237076\tDavid Chong & Co. Office B1- 7/F. Loyong Court 212-220 Lockhart Road Wanchai Hong Kong CAYMAN ISLANDS\n",
      "--\n"
     ]
    }
   ],
   "source": [
    "for v in pr.getTopKValues(5):\n",
    "    print 'Page Rank value %s\\t Vertex ID %s\\t%s\\n--' %  (v.getValue(),\\\n",
    "                                                      v.getKey().getId(), getVertexDict(v.getKey().getId())['Name'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Show more information about the top-ranked vertex"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Top Page Rank vertex is 236724 / Address / Portcullis TrustNet Chambers P.O. Box 3444 Road Town- Tortola British Virgin Islands (w.e.f 9 December 2005)\n",
      "\tinEdges: 37338\n",
      "\tinNeighbors: 37338\n",
      "\toutEdges: 0\n",
      "\toutNeighbors: 0\n"
     ]
    }
   ],
   "source": [
    "for x in pr.getTopKValues(1): topVertex = x.getKey()\n",
    "\n",
    "print 'Top Page Rank vertex is %s / %s / %s' % (topVertex.getId(),\\\n",
    "                                                getVertexDict(topVertex.getId())['Type'],\\\n",
    "                                           getVertexDict(topVertex.getId())['Name'])\n",
    "print '\\tinEdges: %s' % len(topVertex.inEdges)\n",
    "print '\\tinNeighbors: %s' % len(topVertex.inNeighbors)\n",
    "print '\\toutEdges: %s' % len(topVertex.outEdges)\n",
    "print '\\toutNeighbors: %s' % len(topVertex.outNeighbors)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### First five inbound edges\n",
    "\n",
    "Any five, not in any order..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Source Hophil Nominess (BVI) ltd. (22464) \t -[ officer_of ]-> Portcullis TrustNet Chambers P.O. Box 3444 Road Town- Tortola British Virgin Islands (w.e.f 9 December 2005) (236724)\n",
      "Source HOPHIL SERVICES (BVI) LIMITED (22981) \t -[ officer_of ]-> Portcullis TrustNet Chambers P.O. Box 3444 Road Town- Tortola British Virgin Islands (w.e.f 9 December 2005) (236724)\n",
      "Source Wealthy Century Group Limited (23254) \t -[ officer_of ]-> Portcullis TrustNet Chambers P.O. Box 3444 Road Town- Tortola British Virgin Islands (w.e.f 9 December 2005) (236724)\n",
      "Source Hophil Nominees (BVI ) Limited (23393) \t -[ officer_of ]-> Portcullis TrustNet Chambers P.O. Box 3444 Road Town- Tortola British Virgin Islands (w.e.f 9 December 2005) (236724)\n",
      "Source Hophil Nominees (bvi)Limited (23398) \t -[ officer_of ]-> Portcullis TrustNet Chambers P.O. Box 3444 Road Town- Tortola British Virgin Islands (w.e.f 9 December 2005) (236724)\n"
     ]
    }
   ],
   "source": [
    "a=0\n",
    "for e in topVertex.getInEdges():\n",
    "    edge=opg.getEdge(e.getId()).label\n",
    "    source_name = getVertexDict(e.getSource().getId())['Name']\n",
    "    source_id = e.getSource().getId()\n",
    "    dest_name = getVertexDict(e.getDestination().getId())['Name']\n",
    "    dest_id = e.getDestination().getId()\n",
    "    print 'Source %s (%s) \\t -[ %s ]-> %s (%s)' % (source_name,source_id,edge,dest_name,dest_id)\n",
    "    a+=1\n",
    "    if a>=5: break"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Triangle Count algorithm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of triangles in the graph 237128\n"
     ]
    }
   ],
   "source": [
    "print \"Number of triangles in the graph: \", pgx_analyst.countTriangles()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Degree Centrality"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dc=pgx_analyst.inDegreeCentrality()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "In Degree Centrality value 37338\t Vertex ID 236724 / Address / Portcullis TrustNet Chambers P.O. Box 3444 Road Town- Tortola British Virgin Islands (w.e.f 9 December 2005)\n",
      "In Degree Centrality value 5699\t Vertex ID 288469 / Address / Unitrust Corporate Services Ltd. John Humphries House- Room 304 4-10 Stockwell Street- Greenwich London SE10 9JN\n",
      "In Degree Centrality value 3896\t Vertex ID 285729 / Address / Sealight Incorporations Limited Room 1201- Connaught Commercial Building 185 Wanchai Road Wanchai- Hong Kong\n",
      "In Degree Centrality value 2671\t Vertex ID 264051 / Address / Company Kit Limited Unit A- 6/F Shun On Comm Bldg. 112-114 Des Voeux Road C.- Hong Kong  SAR\n",
      "In Degree Centrality value 2194\t Vertex ID 279944 / Address / Panocean Secretarial Services Limited Room 1708 Kai Tak Commercial Building 317-321 Des Voeux Road C Hong Kong\n"
     ]
    }
   ],
   "source": [
    "for v in dc.getTopKValues(5):\n",
    "    print 'In Degree Centrality value %s\\t Vertex ID %s / %s / %s' %  (v.getValue(),\\\n",
    "                                                      v.getKey().getId(),\\\n",
    "                                                      getVertexDict(v.getKey().getId())['Type'],\\\n",
    "                                                      getVertexDict(v.getKey().getId())['Name'])"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}