{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Step 2 – Alternative\n",
    "\n",
    "In this notebook we look at an alternative method for exporting data from MySQL to Neptune. Instead of exporting to CSV files and using the Neptune bulk loader to load the data into Neptune, we use an AWS Glue job that extracts data from MySQL and inserts it directly into Neptune.\n",
    "\n",
    "For details on converting from a relational data model to a graph data model, see [Converting a Relational Data Model to a Graph Model](https://github.com/aws-samples/aws-dbs-refarch-graph/tree/master/src/converting-to-graph#converting-a-relational-data-model-to-a-graph-model)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"https://s3.amazonaws.com/aws-neptune-customer-samples/neptune-sagemaker/images/mysql-2-neptune-02.png\"/>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Import from MySQL to Neptune\n",
    "\n",
    "Instead of importing by way of S3 and the Neptune bulk loader, we use an AWS Glue job, `export_from_mysql_to_neptune`, to extract data from MySQL and write it directly to Neptune. \n",
    "\n",
    "The script for the job can be found [here](https://github.com/aws-samples/amazon-neptune-samples/tree/master/gremlin/glue-neptune/glue-jobs/mysql-neptune/export-from-mysql-to-neptune.py). The script uses the [neptune-python-utils](https://github.com/awslabs/amazon-neptune-tools/tree/master/neptune-python-utils) Python library."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%run './glue_utils.py'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import nest_asyncio\n",
    "nest_asyncio.apply()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We'll clear the database before running the import to simulate this comprising a first step in a migration to a fresh Neptune database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%db_reset -y"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we'll run the [export job](https://github.com/aws-samples/amazon-neptune-samples/tree/master/gremlin/glue-neptune/glue-jobs/mysql-neptune/export-from-mysql-to-neptune.py)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "job_name = glue_resource('export_from_mysql_to_neptune')\n",
    "\n",
    "run_job(job_name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Query the graph\n",
    "\n",
    "Once again we can query the graph to review the results of the import."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from neptune_python_utils.gremlin_utils import GremlinUtils\n",
    "\n",
    "GremlinUtils.init_statics(globals())\n",
    "\n",
    "gremlin_utils = GremlinUtils()\n",
    "\n",
    "conn = gremlin_utils.remote_connection()\n",
    "g = gremlin_utils.traversal_source(connection=conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Count the number of Product and Supplier vertices in the graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print('# Products : {}'.format(g.V().hasLabel('Product').count().next()))\n",
    "print('# Suppliers: {}'.format(g.V().hasLabel('Supplier').count().next()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Show the products supplied by a supplier\n",
    "\n",
    "Product vertices are connected to Supplier vertices using an outgoing SUPPLIER edge."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "results = (g.V().hasLabel('Supplier').limit(1).\n",
    "      project('supplier', 'products').\n",
    "      by('name').\n",
    "      by(in_('SUPPLIER').values('name').fold()).next())\n",
    "\n",
    "print('Supplier: {}\\nProducts: {}'.format(results['supplier'], results['products']))\n",
    "\n",
    "conn.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "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.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}