{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Edgar Holdings \n",
    "\n",
    "The examples in this notebook demonstrate using the GremlinPython library to connect to and work with a Neptune instance. Using a Jupyter notebook in this way provides a nice way to interact with your Neptune graph database in a familiar and instantly productive environment."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Connect to the Neptune Database which has the load Edgar Data \n",
    "\n",
    "When the SageMaker notebook instance was created the appropriate Python libraries for working with a Tinkerpop enabled graph were installed. We now need to `import` some classes from those libraries before connecting to our Neptune instance, loading some sample data, and running queries. \n",
    "\n",
    "Below are the packages that need to be installed. This should be executed once to configure the environment. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install --upgrade pip\n",
    "!pip install futures  \n",
    "!pip install gremlinpython\n",
    "!pip install SPARQLWrapper\n",
    "!pip install matplotlib\n",
    "!pip install numpy \n",
    "!pip install pandas \n",
    "!pip install networkx "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%run '../util/neptune.py'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Establish access to our Neptune instance\n",
    "\n",
    "Before we can work with our graph we need to establish a connection to it. This is done using the `DriverRemoteConnection` capability as defined by Apache TinkerPop and supported by GremlinPython. The `neptune.py` helper module facilitates creating this connection.\n",
    "\n",
    "Once this cell has been run we will be able to use the variable `g` to refer to our graph in Gremlin queries in subsequent cells. By default Neptune uses port 8182 and that is what we connect to below. When you configure your own Neptune instance you can you choose a different endpoint and port number by specifiying the `neptune_endpoint` and `neptune_port` parameters to the `graphTraversal()` method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "endpoint=\"neptuneuser.cluster-carpeooi4ov5.us-east-1.neptune.amazonaws.com\"\n",
    "port=8182\n",
    "my_region='us-east-1'\n",
    "g = neptune.graphTraversal(neptune_endpoint=endpoint,neptune_port=port)\n",
    "print(\"g = {0} \".format(g))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data for Analysis \n",
    "IF you are using a neptune graph that was loaded by another process then do not uncomment these line. \n",
    "IF the graph is empty you can load data from a single quater. The following code will \n",
    " - clear the grpah of any data \n",
    " - copy the sample data to s3 \n",
    " - bulk load the data \n",
    "For this to work you will need to have created an S3 bucket and update the variable #s3bucket to its name . You should define a \n",
    "#key to be associated with the entries. You also need to define an IAM role #NEPTUNE_LOAD_FROM_S3_ROLE_ARN as explained in the neptune documentation. https://docs.aws.amazon.com/neptune/latest/userguide/bulk-load-tutorial-IAM.html\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import boto3\n",
    "s3 = boto3.resource('s3')\n",
    "\n",
    "s3bucket=\"s3stockmktdata\"\n",
    "key=\"neptune\"\n",
    "s3role=\"arn:aws:iam::983739021977:role/NeptuneLoadFromS3\"\n",
    "\n",
    "fileslist= [ \"security.csv\" ,\"securityholdr.csv\",\"srelations.csv\" ]\n",
    "localhome=\"/home/ec2-user/SageMaker/amazon-neptune-samples/neptune-sagemaker/notebooks/edgar\"\n",
    "bulkloaddir=\"s3://{0}/{1}/\".format(s3bucket,key)\n",
    "\n",
    "neptune.clear(neptune_endpoint=endpoint,neptune_port=8182)\n",
    "\n",
    "for filename in fileslist :  \n",
    "    afile=\"{0}/{1}\".format(localhome,filename)\n",
    "    rmtfilename=\"{0}/{1}\".format(key,filename)\n",
    "    bulkloaddir=\"s3://{0}/{1}/{2}\".format(s3bucket,key,filename)\n",
    "    print(\"Copy local file {0} \\n to s3 filename={1}\".format(localfile,rmtfilename))\n",
    "    print(\"Neptune Load of {0} \".format(bulkloaddir))\n",
    "    s3.Bucket(s3bucket).upload_file(localfile,rmtfilename)\n",
    "    neptune.bulkLoad(bulkloaddir,format='csv', interval=5,role=s3role,region=my_region,neptune_endpoint=endpoint,neptune_port=8182)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Let's find out a bit about the graph\n",
    "\n",
    "Let's start off with a simple query just to make sure our connection to Neptune is working. The queries below look at all of the vertices and edges in the graph and create two maps that show the demographic of the graph. As we are using the air routes data set, not surprisingly, the values returned are related to airports and routes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "vertices = g.V().groupCount().by(T.label).toList()\n",
    "edges  = g.E().groupCount().by(T.label).toList()\n",
    "print(\"Vertices ={0}\".format(vertices))\n",
    "print(\"Edges = {0} \".format(edges))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let take a look at some of the relationships. Holder are the Vertexs that are in possesion of the security .\n",
    "The edges are the details from the edgar docments that link holders to securities. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "securitypath =  g.V().toList()\n",
    "\n",
    "print(securitypath)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "countedgae = g.V().hasLabel('13F-HR').by(outE().count()).by(out().groupCount().by(label))\n",
    "print(countedgae)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "conda_python3",
   "language": "python",
   "name": "conda_python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}