AWS-GP-demo-1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Greenplum Database  Concepts Explained - Part 1\n",
    "## 1. System Setup\n",
    "### 1.1 Initialize database connection and setup global variable values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os, re\n",
    "from IPython.display import display_html\n",
    "\n",
    "import pygments.lexers\n",
    "from pygments import highlight\n",
    "from pygments.formatters import HtmlFormatter\n",
    "\n",
    "CONNECTION_STRING = os.getenv('AWSGPDBCONN')\n",
    "\n",
    "cs = re.match('^postgresql:\\/\\/(\\S+):(\\S+)@(\\S+):(\\S+)\\/(\\S+)$', CONNECTION_STRING)\n",
    "\n",
    "DB_USER   = cs.group(1)\n",
    "DB_PWD    = cs.group(2)\n",
    "DB_SERVER = cs.group(3)\n",
    "DB_PORT   = cs.group(4)\n",
    "DB_NAME   = cs.group(5)\n",
    "\n",
    "%reload_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>version</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>PostgreSQL 9.4.24 (Greenplum Database 6.12.0 build commit:4c176763c7619fb678ce38095e6b3e8fb9548186) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Oct 28 2020 19:42:15</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('PostgreSQL 9.4.24 (Greenplum Database 6.12.0 build commit:4c176763c7619fb678ce38095e6b3e8fb9548186) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Oct 28 2020 19:42:15',)]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql $CONNECTION_STRING\n",
    "%sql $DB_USER@$DB_NAME {\"SELECT version();\"}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 rows affected.\n",
      "Done.\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>gp_autostats_mode</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>on_no_stats</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('on_no_stats',)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"SHOW gp_autostats_mode; \\\n",
    "ALTER DATABASE {} SET gp_autostats_mode TO 'NONE'; \\\n",
    "SHOW gp_autostats_mode;\".format(DB_NAME)\n",
    "\n",
    "%sql $DB_USER@$DB_NAME {''.join(query)}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>version</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>PostgreSQL 9.4.24 (Greenplum Database 6.12.0 build commit:4c176763c7619fb678ce38095e6b3e8fb9548186) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Oct 28 2020 19:42:15</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('PostgreSQL 9.4.24 (Greenplum Database 6.12.0 build commit:4c176763c7619fb678ce38095e6b3e8fb9548186) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Oct 28 2020 19:42:15',)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql $DB_USER@$DB_NAME\n",
    "SELECT version();"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. The Amazon Customer Reviews Dataset\n",
    "\n",
    "Over 130+ million customer reviews are available to researchers as part of this release. The data is available in TSV files in the `amazon-reviews-pds` S3 bucket in AWS US East Region. Each line in the data files corresponds to an individual review (tab delimited, with no quote and escape characters). Samples of the data are available in English and French; more details on the information in each column can be found [here](https://s3.amazonaws.com/amazon-reviews-pds/tsv/index.txt).\n",
    "\n",
    "If you use the AWS Command Line Interface, you can list data in the bucket with the `aws s3 ls` command:\n",
    "\n",
    "`aws s3 ls s3://amazon-reviews-pds/tsv/`\n",
    "\n",
    "To download data using the AWS Command Line Interface, you can use the `aws s3 cp` command. For instance, the following command will copy the file named `amazon_reviews_us_Camera_v1_00.tsv.gz` to your local directory:\n",
    "\n",
    "`aws s3 cp s3://amazon-reviews-pds/tsv/<S3 File> <Local File>`\n",
    "\n",
    "### 2.1 Prepare AWS System and setup awscli library via pip"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<!DOCTYPE html PUBLIC \"-//W3C//DTD HTML 4.01//EN\"\n",
       "   \"http://www.w3.org/TR/html4/strict.dtd\">\n",
       "<!--\n",
       "generated by Pygments <http://pygments.org>\n",
       "Copyright 2006-2019 by the Pygments team.\n",
       "Licensed under the BSD license, see LICENSE for details.\n",
       "-->\n",
       "<html>\n",
       "<head>\n",
       "  <title></title>\n",
       "  <meta http-equiv=\"content-type\" content=\"text/html; charset=UTF-8\">\n",
       "  <style type=\"text/css\">\n",
       "/*\n",
       "generated by Pygments <http://pygments.org>\n",
       "Copyright 2006-2019 by the Pygments team.\n",
       "Licensed under the BSD license, see LICENSE for details.\n",
       "*/\n",
       "td.linenos { background-color: #f0f0f0; padding-right: 10px; }\n",
       "span.lineno { background-color: #f0f0f0; padding: 0 5px 0 5px; }\n",
       "pre { line-height: 125%; }\n",
       "body .hll { background-color: #ffffcc }\n",
       "body  { background: #f0f0f0; }\n",
       "body .c { color: #60a0b0; font-style: italic } /* Comment */\n",
       "body .err { border: 1px solid #FF0000 } /* Error */\n",
       "body .k { color: #007020; font-weight: bold } /* Keyword */\n",
       "body .o { color: #666666 } /* Operator */\n",
       "body .ch { color: #60a0b0; font-style: italic } /* Comment.Hashbang */\n",
       "body .cm { color: #60a0b0; font-style: italic } /* Comment.Multiline */\n",
       "body .cp { color: #007020 } /* Comment.Preproc */\n",
       "body .cpf { color: #60a0b0; font-style: italic } /* Comment.PreprocFile */\n",
       "body .c1 { color: #60a0b0; font-style: italic } /* Comment.Single */\n",
       "body .cs { color: #60a0b0; background-color: #fff0f0 } /* Comment.Special */\n",
       "body .gd { color: #A00000 } /* Generic.Deleted */\n",
       "body .ge { font-style: italic } /* Generic.Emph */\n",
       "body .gr { color: #FF0000 } /* Generic.Error */\n",
       "body .gh { color: #000080; font-weight: bold } /* Generic.Heading */\n",
       "body .gi { color: #00A000 } /* Generic.Inserted */\n",
       "body .go { color: #888888 } /* Generic.Output */\n",
       "body .gp { color: #c65d09; font-weight: bold } /* Generic.Prompt */\n",
       "body .gs { font-weight: bold } /* Generic.Strong */\n",
       "body .gu { color: #800080; font-weight: bold } /* Generic.Subheading */\n",
       "body .gt { color: #0044DD } /* Generic.Traceback */\n",
       "body .kc { color: #007020; font-weight: bold } /* Keyword.Constant */\n",
       "body .kd { color: #007020; font-weight: bold } /* Keyword.Declaration */\n",
       "body .kn { color: #007020; font-weight: bold } /* Keyword.Namespace */\n",
       "body .kp { color: #007020 } /* Keyword.Pseudo */\n",
       "body .kr { color: #007020; font-weight: bold } /* Keyword.Reserved */\n",
       "body .kt { color: #902000 } /* Keyword.Type */\n",
       "body .m { color: #40a070 } /* Literal.Number */\n",
       "body .s { color: #4070a0 } /* Literal.String */\n",
       "body .na { color: #4070a0 } /* Name.Attribute */\n",
       "body .nb { color: #007020 } /* Name.Builtin */\n",
       "body .nc { color: #0e84b5; font-weight: bold } /* Name.Class */\n",
       "body .no { color: #60add5 } /* Name.Constant */\n",
       "body .nd { color: #555555; font-weight: bold } /* Name.Decorator */\n",
       "body .ni { color: #d55537; font-weight: bold } /* Name.Entity */\n",
       "body .ne { color: #007020 } /* Name.Exception */\n",
       "body .nf { color: #06287e } /* Name.Function */\n",
       "body .nl { color: #002070; font-weight: bold } /* Name.Label */\n",
       "body .nn { color: #0e84b5; font-weight: bold } /* Name.Namespace */\n",
       "body .nt { color: #062873; font-weight: bold } /* Name.Tag */\n",
       "body .nv { color: #bb60d5 } /* Name.Variable */\n",
       "body .ow { color: #007020; font-weight: bold } /* Operator.Word */\n",
       "body .w { color: #bbbbbb } /* Text.Whitespace */\n",
       "body .mb { color: #40a070 } /* Literal.Number.Bin */\n",
       "body .mf { color: #40a070 } /* Literal.Number.Float */\n",
       "body .mh { color: #40a070 } /* Literal.Number.Hex */\n",
       "body .mi { color: #40a070 } /* Literal.Number.Integer */\n",
       "body .mo { color: #40a070 } /* Literal.Number.Oct */\n",
       "body .sa { color: #4070a0 } /* Literal.String.Affix */\n",
       "body .sb { color: #4070a0 } /* Literal.String.Backtick */\n",
       "body .sc { color: #4070a0 } /* Literal.String.Char */\n",
       "body .dl { color: #4070a0 } /* Literal.String.Delimiter */\n",
       "body .sd { color: #4070a0; font-style: italic } /* Literal.String.Doc */\n",
       "body .s2 { color: #4070a0 } /* Literal.String.Double */\n",
       "body .se { color: #4070a0; font-weight: bold } /* Literal.String.Escape */\n",
       "body .sh { color: #4070a0 } /* Literal.String.Heredoc */\n",
       "body .si { color: #70a0d0; font-style: italic } /* Literal.String.Interpol */\n",
       "body .sx { color: #c65d09 } /* Literal.String.Other */\n",
       "body .sr { color: #235388 } /* Literal.String.Regex */\n",
       "body .s1 { color: #4070a0 } /* Literal.String.Single */\n",
       "body .ss { color: #517918 } /* Literal.String.Symbol */\n",
       "body .bp { color: #007020 } /* Name.Builtin.Pseudo */\n",
       "body .fm { color: #06287e } /* Name.Function.Magic */\n",
       "body .vc { color: #bb60d5 } /* Name.Variable.Class */\n",
       "body .vg { color: #bb60d5 } /* Name.Variable.Global */\n",
       "body .vi { color: #bb60d5 } /* Name.Variable.Instance */\n",
       "body .vm { color: #bb60d5 } /* Name.Variable.Magic */\n",
       "body .il { color: #40a070 } /* Literal.Number.Integer.Long */\n",
       "\n",
       "  </style>\n",
       "</head>\n",
       "<body>\n",
       "<h2></h2>\n",
       "\n",
       "<div class=\"highlight\"><pre><span></span><span class=\"ch\">#!/bin/bash</span>\n",
       "\n",
       "<span class=\"nb\">cd</span> <span class=\"nv\">$HOME</span>\n",
       "\n",
       "curl -O https://bootstrap.pypa.io/get-pip.py\n",
       "python <span class=\"nv\">$HOME</span>/get-pip.py --force-reinstall\n",
       "rm <span class=\"nv\">$HOME</span>/get-pip.py\n",
       "\n",
       "mv <span class=\"nv\">$HOME</span>/.bash_profile <span class=\"nv\">$HOME</span>/.bash_profile.old\n",
       "\n",
       "cat &gt;&gt; <span class=\"nv\">$HOME</span>/.bash_profile <span class=\"s\">&lt;&lt; &#39;EOF&#39;</span>\n",
       "<span class=\"s\"># .bash_profile</span>\n",
       "\n",
       "<span class=\"s\"># Get the aliases and functions</span>\n",
       "<span class=\"s\">if [ -f ~/.bashrc ]; then</span>\n",
       "<span class=\"s\">\t. ~/.bashrc</span>\n",
       "<span class=\"s\">fi</span>\n",
       "\n",
       "<span class=\"s\"># User specific environment and startup programs</span>\n",
       "\n",
       "<span class=\"s\">export PATH=$HOME/.local/bin:$HOME/bin:$PATH</span>\n",
       "\n",
       "<span class=\"s\">EOF</span>\n",
       "\n",
       "chmod <span class=\"m\">644</span> <span class=\"nv\">$HOME</span>/.bash_profile\n",
       "<span class=\"nb\">source</span> <span class=\"nv\">$HOME</span>/.bash_profile\n",
       "\n",
       "pip install --upgrade --force-reinstall pip\n",
       "pip install --upgrade --force-reinstall boto3\n",
       "pip install --upgrade --force-reinstall awscli\n",
       "pip install --upgrade --force-reinstall matplotlib\n",
       "\n",
       "<span class=\"nb\">echo</span> <span class=\"s2\">&quot;***********************&quot;</span>\n",
       "<span class=\"nb\">echo</span> <span class=\"s2\">&quot;* Get AWS CLI version *&quot;</span>\n",
       "<span class=\"nb\">echo</span> <span class=\"s2\">&quot;***********************&quot;</span>\n",
       "aws --version\n",
       "</pre></div>\n",
       "</body>\n",
       "</html>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "shfilecode = !pygmentize -f html -O full,style=friendly -l shell script/1-1-system-prepare.sh\n",
    "display_html('\\n'.join(shfilecode), raw=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Host ec2-18-134-242-91.eu-west-2.compute.amazonaws.com not found in /root/.ssh/known_hosts\n",
      "# ec2-18-134-242-91.eu-west-2.compute.amazonaws.com:22 SSH-2.0-OpenSSH_7.4\n",
      "# ec2-18-134-242-91.eu-west-2.compute.amazonaws.com:22 SSH-2.0-OpenSSH_7.4\n",
      "# ec2-18-134-242-91.eu-west-2.compute.amazonaws.com:22 SSH-2.0-OpenSSH_7.4\n",
      "# ec2-18-134-242-91.eu-west-2.compute.amazonaws.com:22 SSH-2.0-OpenSSH_7.4\n",
      "# ec2-18-134-242-91.eu-west-2.compute.amazonaws.com:22 SSH-2.0-OpenSSH_7.4\n",
      "Warning: Permanently added the ECDSA host key for IP address '18.134.242.91' to the list of known hosts.\n",
      "1-1-system-prepare.sh                         100%  763    82.1KB/s   00:00    \n",
      "  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current\n",
      "                                 Dload  Upload   Total   Spent    Left  Speed\n",
      "100 1842k  100 1842k    0     0  10.4M      0 --:--:-- --:--:-- --:--:-- 10.4M\n",
      "DEPRECATION: Python 2.7 reached the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 is no longer maintained. pip 21.0 will drop support for Python 2.7 in January 2021. More details about Python 2 support in pip can be found at https://pip.pypa.io/en/latest/development/release-process/#python-2-support pip 21.0 will remove support for this functionality.\n",
      "Collecting pip\n",
      "  Downloading pip-20.3.3-py2.py3-none-any.whl (1.5 MB)\n",
      "Installing collected packages: pip\n",
      "  Attempting uninstall: pip\n",
      "    Found existing installation: pip 10.0.1\n",
      "    Uninstalling pip-10.0.1:\n",
      "      Successfully uninstalled pip-10.0.1\n",
      "Successfully installed pip-20.3.3\n",
      "DEPRECATION: Python 2.7 reached the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 is no longer maintained. pip 21.0 will drop support for Python 2.7 in January 2021. More details about Python 2 support in pip can be found at https://pip.pypa.io/en/latest/development/release-process/#python-2-support pip 21.0 will remove support for this functionality.\n",
      "Collecting pip\n",
      "  Using cached pip-20.3.3-py2.py3-none-any.whl (1.5 MB)\n",
      "Installing collected packages: pip\n",
      "  Attempting uninstall: pip\n",
      "    Found existing installation: pip 20.3.3\n",
      "    Uninstalling pip-20.3.3:\n",
      "      Successfully uninstalled pip-20.3.3\n",
      "Successfully installed pip-20.3.3\n",
      "DEPRECATION: Python 2.7 reached the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 is no longer maintained. pip 21.0 will drop support for Python 2.7 in January 2021. More details about Python 2 support in pip can be found at https://pip.pypa.io/en/latest/development/release-process/#python-2-support pip 21.0 will remove support for this functionality.\n",
      "Collecting boto3\n",
      "  Downloading boto3-1.16.41-py2.py3-none-any.whl (130 kB)\n",
      "Collecting botocore<1.20.0,>=1.19.41\n",
      "  Downloading botocore-1.19.41-py2.py3-none-any.whl (7.1 MB)\n",
      "Collecting jmespath<1.0.0,>=0.7.1\n",
      "  Downloading jmespath-0.10.0-py2.py3-none-any.whl (24 kB)\n",
      "Collecting s3transfer<0.4.0,>=0.3.0\n",
      "  Downloading s3transfer-0.3.3-py2.py3-none-any.whl (69 kB)\n",
      "Collecting urllib3<1.27,>=1.25.4; python_version != \"3.4\"\n",
      "  Downloading urllib3-1.26.2-py2.py3-none-any.whl (136 kB)\n",
      "Collecting python-dateutil<3.0.0,>=2.1\n",
      "  Downloading python_dateutil-2.8.1-py2.py3-none-any.whl (227 kB)\n",
      "Collecting futures<4.0.0,>=2.2.0; python_version == \"2.7\"\n",
      "  Downloading futures-3.3.0-py2-none-any.whl (16 kB)\n",
      "Collecting six>=1.5\n",
      "  Downloading six-1.15.0-py2.py3-none-any.whl (10 kB)\n",
      "Installing collected packages: jmespath, urllib3, six, python-dateutil, botocore, futures, s3transfer, boto3\n",
      "  Attempting uninstall: urllib3\n",
      "    Found existing installation: urllib3 1.10.2\n",
      "    Uninstalling urllib3-1.10.2:\n",
      "      Successfully uninstalled urllib3-1.10.2\n",
      "  Attempting uninstall: six\n",
      "    Found existing installation: six 1.9.0\n",
      "    Uninstalling six-1.9.0:\n",
      "      Successfully uninstalled six-1.9.0\n",
      "Successfully installed boto3-1.16.41 botocore-1.19.41 futures-3.3.0 jmespath-0.10.0 python-dateutil-2.8.1 s3transfer-0.3.3 six-1.15.0 urllib3-1.26.2\n",
      "DEPRECATION: Python 2.7 reached the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 is no longer maintained. pip 21.0 will drop support for Python 2.7 in January 2021. More details about Python 2 support in pip can be found at https://pip.pypa.io/en/latest/development/release-process/#python-2-support pip 21.0 will remove support for this functionality.\n",
      "Collecting awscli\n",
      "  Downloading awscli-1.18.201-py2.py3-none-any.whl (3.5 MB)\n",
      "Collecting rsa<=4.5.0,>=3.1.2; python_version != \"3.4\"\n",
      "  Downloading rsa-4.5-py2.py3-none-any.whl (36 kB)\n",
      "Collecting colorama<0.4.4,>=0.2.5; python_version != \"3.4\"\n",
      "  Downloading colorama-0.4.3-py2.py3-none-any.whl (15 kB)\n",
      "Collecting botocore==1.19.41\n",
      "  Using cached botocore-1.19.41-py2.py3-none-any.whl (7.1 MB)\n",
      "Collecting s3transfer<0.4.0,>=0.3.0\n",
      "  Using cached s3transfer-0.3.3-py2.py3-none-any.whl (69 kB)\n",
      "Collecting PyYAML<5.4,>=3.10; python_version != \"3.4\"\n",
      "  Downloading PyYAML-5.3.1.tar.gz (269 kB)\n",
      "Collecting docutils<0.16,>=0.10\n",
      "  Downloading docutils-0.15.2-py2-none-any.whl (548 kB)\n",
      "Collecting pyasn1>=0.1.3\n",
      "  Downloading pyasn1-0.4.8-py2.py3-none-any.whl (77 kB)\n",
      "Collecting jmespath<1.0.0,>=0.7.1\n",
      "  Using cached jmespath-0.10.0-py2.py3-none-any.whl (24 kB)\n",
      "Collecting urllib3<1.27,>=1.25.4; python_version != \"3.4\"\n",
      "  Using cached urllib3-1.26.2-py2.py3-none-any.whl (136 kB)\n",
      "Collecting python-dateutil<3.0.0,>=2.1\n",
      "  Using cached python_dateutil-2.8.1-py2.py3-none-any.whl (227 kB)\n",
      "Collecting futures<4.0.0,>=2.2.0; python_version == \"2.7\"\n",
      "  Using cached futures-3.3.0-py2-none-any.whl (16 kB)\n",
      "Collecting six>=1.5\n",
      "  Using cached six-1.15.0-py2.py3-none-any.whl (10 kB)\n",
      "Building wheels for collected packages: PyYAML\n",
      "  Building wheel for PyYAML (setup.py): started\n",
      "  Building wheel for PyYAML (setup.py): finished with status 'done'\n",
      "  Created wheel for PyYAML: filename=PyYAML-5.3.1-cp27-cp27mu-linux_x86_64.whl size=45620 sha256=f711e07f21ca6d35b59a834e02b36b903c5dd331eb009c12d4d965f830d153bc\n",
      "  Stored in directory: /root/.cache/pip/wheels/d1/d5/a0/3c27cdc8b0209c5fc1385afeee936cf8a71e13d885388b4be2\n",
      "Successfully built PyYAML\n",
      "Installing collected packages: pyasn1, rsa, colorama, jmespath, urllib3, six, python-dateutil, botocore, futures, s3transfer, PyYAML, docutils, awscli\n",
      "  Attempting uninstall: jmespath\n",
      "    Found existing installation: jmespath 0.10.0\n",
      "    Uninstalling jmespath-0.10.0:\n",
      "      Successfully uninstalled jmespath-0.10.0\n",
      "  Attempting uninstall: urllib3\n",
      "    Found existing installation: urllib3 1.26.2\n",
      "    Uninstalling urllib3-1.26.2:\n",
      "      Successfully uninstalled urllib3-1.26.2\n",
      "  Attempting uninstall: six\n",
      "    Found existing installation: six 1.15.0\n",
      "    Uninstalling six-1.15.0:\n",
      "      Successfully uninstalled six-1.15.0\n",
      "  Attempting uninstall: python-dateutil\n",
      "    Found existing installation: python-dateutil 2.8.1\n",
      "    Uninstalling python-dateutil-2.8.1:\n",
      "      Successfully uninstalled python-dateutil-2.8.1\n",
      "  Attempting uninstall: botocore\n",
      "    Found existing installation: botocore 1.19.41\n",
      "    Uninstalling botocore-1.19.41:\n",
      "      Successfully uninstalled botocore-1.19.41\n",
      "  Attempting uninstall: futures\n",
      "    Found existing installation: futures 3.3.0\n",
      "    Uninstalling futures-3.3.0:\n",
      "      Successfully uninstalled futures-3.3.0\n",
      "  Attempting uninstall: s3transfer\n",
      "    Found existing installation: s3transfer 0.3.3\n",
      "    Uninstalling s3transfer-0.3.3:\n",
      "      Successfully uninstalled s3transfer-0.3.3\n",
      "  Attempting uninstall: PyYAML\n",
      "    Found existing installation: PyYAML 3.10\n",
      "ERROR: Cannot uninstall 'PyYAML'. It is a distutils installed project and thus we cannot accurately determine which files belong to it which would lead to only a partial uninstall.\n",
      "DEPRECATION: Python 2.7 reached the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 is no longer maintained. pip 21.0 will drop support for Python 2.7 in January 2021. More details about Python 2 support in pip can be found at https://pip.pypa.io/en/latest/development/release-process/#python-2-support pip 21.0 will remove support for this functionality.\n",
      "Collecting matplotlib\n",
      "  Downloading matplotlib-2.2.5-cp27-cp27mu-manylinux1_x86_64.whl (12.8 MB)\n",
      "Collecting python-dateutil>=2.1\n",
      "  Using cached python_dateutil-2.8.1-py2.py3-none-any.whl (227 kB)\n",
      "Collecting backports.functools-lru-cache\n",
      "  Downloading backports.functools_lru_cache-1.6.1-py2.py3-none-any.whl (5.7 kB)\n",
      "Collecting numpy>=1.7.1\n",
      "  Downloading numpy-1.16.6-cp27-cp27mu-manylinux1_x86_64.whl (17.0 MB)\n",
      "Collecting six>=1.10\n",
      "  Using cached six-1.15.0-py2.py3-none-any.whl (10 kB)\n",
      "Collecting pyparsing!=2.0.4,!=2.1.2,!=2.1.6,>=2.0.1\n",
      "  Downloading pyparsing-2.4.7-py2.py3-none-any.whl (67 kB)\n",
      "Collecting cycler>=0.10\n",
      "  Downloading cycler-0.10.0-py2.py3-none-any.whl (6.5 kB)\n",
      "Collecting kiwisolver>=1.0.1\n",
      "  Downloading kiwisolver-1.1.0-cp27-cp27mu-manylinux1_x86_64.whl (93 kB)\n",
      "Collecting pytz\n",
      "  Downloading pytz-2020.4-py2.py3-none-any.whl (509 kB)\n",
      "Collecting subprocess32\n",
      "  Downloading subprocess32-3.5.4-cp27-cp27mu-manylinux2014_x86_64.whl (69 kB)\n",
      "Collecting setuptools\n",
      "  Downloading setuptools-44.1.1-py2.py3-none-any.whl (583 kB)\n",
      "Installing collected packages: six, python-dateutil, backports.functools-lru-cache, numpy, pyparsing, cycler, setuptools, kiwisolver, pytz, subprocess32, matplotlib\n",
      "  Attempting uninstall: six\n",
      "    Found existing installation: six 1.15.0\n",
      "    Uninstalling six-1.15.0:\n",
      "      Successfully uninstalled six-1.15.0\n",
      "  Attempting uninstall: python-dateutil\n",
      "    Found existing installation: python-dateutil 2.8.1\n",
      "    Uninstalling python-dateutil-2.8.1:\n",
      "      Successfully uninstalled python-dateutil-2.8.1\n",
      "  Attempting uninstall: setuptools\n",
      "    Found existing installation: setuptools 0.9.8\n",
      "    Uninstalling setuptools-0.9.8:\n",
      "      Successfully uninstalled setuptools-0.9.8\n",
      "Successfully installed backports.functools-lru-cache-1.6.1 cycler-0.10.0 kiwisolver-1.1.0 matplotlib-2.2.5 numpy-1.16.6 pyparsing-2.4.7 python-dateutil-2.8.1 pytz-2020.4 setuptools-44.1.1 six-1.15.0 subprocess32-3.5.4\n",
      "***********************\n",
      "* Get AWS CLI version *\n",
      "***********************\n",
      "aws-cli/1.15.30 Python/2.7.5 Linux/3.10.0-1127.19.1.el7.x86_64 botocore/1.10.30\n"
     ]
    }
   ],
   "source": [
    "!ssh-keygen -R $DB_SERVER\n",
    "!ssh-keyscan $DB_SERVER >> ~/.ssh/known_hosts\n",
    "!scp -i ~/.ssh/aws-gp.pem script/1-1-system-prepare.sh $DB_USER@$DB_SERVER:system-prepare.sh\n",
    "!ssh -i ~/.ssh/aws-gp.pem $DB_USER@$DB_SERVER 'chmod +x ./system-prepare.sh'\n",
    "!ssh -i ~/.ssh/aws-gp.pem $DB_USER@$DB_SERVER 'sudo ./system-prepare.sh'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.2 Provide AWS Access Key ID & Secret Access Key"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<!DOCTYPE html PUBLIC \"-//W3C//DTD HTML 4.01//EN\"\n",
       "   \"http://www.w3.org/TR/html4/strict.dtd\">\n",
       "<!--\n",
       "generated by Pygments <http://pygments.org>\n",
       "Copyright 2006-2019 by the Pygments team.\n",
       "Licensed under the BSD license, see LICENSE for details.\n",
       "-->\n",
       "<html>\n",
       "<head>\n",
       "  <title></title>\n",
       "  <meta http-equiv=\"content-type\" content=\"text/html; charset=UTF-8\">\n",
       "  <style type=\"text/css\">\n",
       "/*\n",
       "generated by Pygments <http://pygments.org>\n",
       "Copyright 2006-2019 by the Pygments team.\n",
       "Licensed under the BSD license, see LICENSE for details.\n",
       "*/\n",
       "td.linenos { background-color: #f0f0f0; padding-right: 10px; }\n",
       "span.lineno { background-color: #f0f0f0; padding: 0 5px 0 5px; }\n",
       "pre { line-height: 125%; }\n",
       "body .hll { background-color: #ffffcc }\n",
       "body  { background: #f0f0f0; }\n",
       "body .c { color: #60a0b0; font-style: italic } /* Comment */\n",
       "body .err { border: 1px solid #FF0000 } /* Error */\n",
       "body .k { color: #007020; font-weight: bold } /* Keyword */\n",
       "body .o { color: #666666 } /* Operator */\n",
       "body .ch { color: #60a0b0; font-style: italic } /* Comment.Hashbang */\n",
       "body .cm { color: #60a0b0; font-style: italic } /* Comment.Multiline */\n",
       "body .cp { color: #007020 } /* Comment.Preproc */\n",
       "body .cpf { color: #60a0b0; font-style: italic } /* Comment.PreprocFile */\n",
       "body .c1 { color: #60a0b0; font-style: italic } /* Comment.Single */\n",
       "body .cs { color: #60a0b0; background-color: #fff0f0 } /* Comment.Special */\n",
       "body .gd { color: #A00000 } /* Generic.Deleted */\n",
       "body .ge { font-style: italic } /* Generic.Emph */\n",
       "body .gr { color: #FF0000 } /* Generic.Error */\n",
       "body .gh { color: #000080; font-weight: bold } /* Generic.Heading */\n",
       "body .gi { color: #00A000 } /* Generic.Inserted */\n",
       "body .go { color: #888888 } /* Generic.Output */\n",
       "body .gp { color: #c65d09; font-weight: bold } /* Generic.Prompt */\n",
       "body .gs { font-weight: bold } /* Generic.Strong */\n",
       "body .gu { color: #800080; font-weight: bold } /* Generic.Subheading */\n",
       "body .gt { color: #0044DD } /* Generic.Traceback */\n",
       "body .kc { color: #007020; font-weight: bold } /* Keyword.Constant */\n",
       "body .kd { color: #007020; font-weight: bold } /* Keyword.Declaration */\n",
       "body .kn { color: #007020; font-weight: bold } /* Keyword.Namespace */\n",
       "body .kp { color: #007020 } /* Keyword.Pseudo */\n",
       "body .kr { color: #007020; font-weight: bold } /* Keyword.Reserved */\n",
       "body .kt { color: #902000 } /* Keyword.Type */\n",
       "body .m { color: #40a070 } /* Literal.Number */\n",
       "body .s { color: #4070a0 } /* Literal.String */\n",
       "body .na { color: #4070a0 } /* Name.Attribute */\n",
       "body .nb { color: #007020 } /* Name.Builtin */\n",
       "body .nc { color: #0e84b5; font-weight: bold } /* Name.Class */\n",
       "body .no { color: #60add5 } /* Name.Constant */\n",
       "body .nd { color: #555555; font-weight: bold } /* Name.Decorator */\n",
       "body .ni { color: #d55537; font-weight: bold } /* Name.Entity */\n",
       "body .ne { color: #007020 } /* Name.Exception */\n",
       "body .nf { color: #06287e } /* Name.Function */\n",
       "body .nl { color: #002070; font-weight: bold } /* Name.Label */\n",
       "body .nn { color: #0e84b5; font-weight: bold } /* Name.Namespace */\n",
       "body .nt { color: #062873; font-weight: bold } /* Name.Tag */\n",
       "body .nv { color: #bb60d5 } /* Name.Variable */\n",
       "body .ow { color: #007020; font-weight: bold } /* Operator.Word */\n",
       "body .w { color: #bbbbbb } /* Text.Whitespace */\n",
       "body .mb { color: #40a070 } /* Literal.Number.Bin */\n",
       "body .mf { color: #40a070 } /* Literal.Number.Float */\n",
       "body .mh { color: #40a070 } /* Literal.Number.Hex */\n",
       "body .mi { color: #40a070 } /* Literal.Number.Integer */\n",
       "body .mo { color: #40a070 } /* Literal.Number.Oct */\n",
       "body .sa { color: #4070a0 } /* Literal.String.Affix */\n",
       "body .sb { color: #4070a0 } /* Literal.String.Backtick */\n",
       "body .sc { color: #4070a0 } /* Literal.String.Char */\n",
       "body .dl { color: #4070a0 } /* Literal.String.Delimiter */\n",
       "body .sd { color: #4070a0; font-style: italic } /* Literal.String.Doc */\n",
       "body .s2 { color: #4070a0 } /* Literal.String.Double */\n",
       "body .se { color: #4070a0; font-weight: bold } /* Literal.String.Escape */\n",
       "body .sh { color: #4070a0 } /* Literal.String.Heredoc */\n",
       "body .si { color: #70a0d0; font-style: italic } /* Literal.String.Interpol */\n",
       "body .sx { color: #c65d09 } /* Literal.String.Other */\n",
       "body .sr { color: #235388 } /* Literal.String.Regex */\n",
       "body .s1 { color: #4070a0 } /* Literal.String.Single */\n",
       "body .ss { color: #517918 } /* Literal.String.Symbol */\n",
       "body .bp { color: #007020 } /* Name.Builtin.Pseudo */\n",
       "body .fm { color: #06287e } /* Name.Function.Magic */\n",
       "body .vc { color: #bb60d5 } /* Name.Variable.Class */\n",
       "body .vg { color: #bb60d5 } /* Name.Variable.Global */\n",
       "body .vi { color: #bb60d5 } /* Name.Variable.Instance */\n",
       "body .vm { color: #bb60d5 } /* Name.Variable.Magic */\n",
       "body .il { color: #40a070 } /* Literal.Number.Integer.Long */\n",
       "\n",
       "  </style>\n",
       "</head>\n",
       "<body>\n",
       "<h2></h2>\n",
       "\n",
       "<div class=\"highlight\"><pre><span></span><span class=\"ch\">#!/bin/bash</span>\n",
       "\n",
       "<span class=\"k\">if</span> <span class=\"o\">[</span> -d <span class=\"nv\">$HOME</span>/.aws <span class=\"o\">]</span><span class=\"p\">;</span> <span class=\"k\">then</span> rm -rf <span class=\"nv\">$HOME</span>/.aws<span class=\"p\">;</span> <span class=\"k\">fi</span>\n",
       "\n",
       "mkdir <span class=\"nv\">$HOME</span>/.aws\n",
       "\n",
       "<span class=\"nb\">echo</span> <span class=\"s2\">&quot;[default]</span>\n",
       "<span class=\"s2\">aws_access_key_id=&quot;</span><span class=\"nv\">$1</span><span class=\"s2\">&quot;</span>\n",
       "<span class=\"s2\">aws_secret_access_key=&quot;</span><span class=\"nv\">$2</span><span class=\"s2\">&quot;&quot;</span> &gt; <span class=\"nv\">$HOME</span>/.aws/credentials\n",
       "\n",
       "<span class=\"nb\">echo</span> <span class=\"s2\">&quot;[default]</span>\n",
       "<span class=\"s2\">region=eu-west-2</span>\n",
       "<span class=\"s2\">output=json&quot;</span> &gt; <span class=\"nv\">$HOME</span>/.aws/config\n",
       "\n",
       "<span class=\"c1\">#test aws config</span>\n",
       "<span class=\"k\">if</span> <span class=\"o\">[</span> <span class=\"s2\">&quot;</span><span class=\"k\">$(</span>/usr/local/bin/aws s3 ls s3://amazon-reviews-pds/tsv/ <span class=\"p\">|</span> awk <span class=\"s1\">&#39;{print $4}&#39;</span> <span class=\"p\">|</span> awk <span class=\"s1\">&#39;END {print NR}&#39;</span><span class=\"k\">)</span><span class=\"s2\">&quot;</span> -eq <span class=\"s2\">&quot;0&quot;</span> <span class=\"o\">]</span><span class=\"p\">;</span> \n",
       "  <span class=\"k\">then</span> <span class=\"nb\">echo</span> <span class=\"s2\">&quot;Error on AWS S3 Configuration setup&quot;</span><span class=\"p\">;</span> \n",
       "  <span class=\"k\">else</span> <span class=\"nb\">echo</span> <span class=\"s2\">&quot;AWS S3 Configuration setup correctly&quot;</span><span class=\"p\">;</span> \n",
       "<span class=\"k\">fi</span>\n",
       "</pre></div>\n",
       "</body>\n",
       "</html>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "shfilecode = !pygmentize -f html -O full,style=friendly -l bash script/1-2-aws-configure.sh\n",
    "display_html('\\n'.join(shfilecode), raw=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1-2-aws-configure.sh                          100%  484    55.0KB/s   00:00    \n"
     ]
    },
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      "AWS Access Key ID [None]: ····················\n",
      "AWS Secret Access Key [None]: ········································\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "AWS S3 Configuration setup correctly\n"
     ]
    }
   ],
   "source": [
    "import getpass\n",
    "\n",
    "!scp -i ~/.ssh/aws-gp.pem script/1-2-aws-configure.sh $DB_USER@$DB_SERVER:aws-configure.sh\n",
    "!ssh -i ~/.ssh/aws-gp.pem $DB_USER@$DB_SERVER 'chmod +x ./aws-configure.sh'\n",
    "\n",
    "cmd = 'sudo ./aws-configure.sh ' \n",
    "cmd = cmd + getpass.getpass(\"AWS Access Key ID [None]:\") \n",
    "cmd = cmd + ' ' + getpass.getpass(\"AWS Secret Access Key [None]:\")\n",
    "\n",
    "!ssh -i ~/.ssh/aws-gp.pem $DB_USER@$DB_SERVER $cmd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.3 Copy source files from AWS S3\n",
    "For our demo, we choose to download the available files into the `/home/gpadmin/data/` folder, using the `aws s3 cp <S3 File> <Local File>` command described before, as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<!DOCTYPE html PUBLIC \"-//W3C//DTD HTML 4.01//EN\"\n",
       "   \"http://www.w3.org/TR/html4/strict.dtd\">\n",
       "<!--\n",
       "generated by Pygments <http://pygments.org>\n",
       "Copyright 2006-2019 by the Pygments team.\n",
       "Licensed under the BSD license, see LICENSE for details.\n",
       "-->\n",
       "<html>\n",
       "<head>\n",
       "  <title></title>\n",
       "  <meta http-equiv=\"content-type\" content=\"text/html; charset=UTF-8\">\n",
       "  <style type=\"text/css\">\n",
       "/*\n",
       "generated by Pygments <http://pygments.org>\n",
       "Copyright 2006-2019 by the Pygments team.\n",
       "Licensed under the BSD license, see LICENSE for details.\n",
       "*/\n",
       "td.linenos { background-color: #f0f0f0; padding-right: 10px; }\n",
       "span.lineno { background-color: #f0f0f0; padding: 0 5px 0 5px; }\n",
       "pre { line-height: 125%; }\n",
       "body .hll { background-color: #ffffcc }\n",
       "body  { background: #f0f0f0; }\n",
       "body .c { color: #60a0b0; font-style: italic } /* Comment */\n",
       "body .err { border: 1px solid #FF0000 } /* Error */\n",
       "body .k { color: #007020; font-weight: bold } /* Keyword */\n",
       "body .o { color: #666666 } /* Operator */\n",
       "body .ch { color: #60a0b0; font-style: italic } /* Comment.Hashbang */\n",
       "body .cm { color: #60a0b0; font-style: italic } /* Comment.Multiline */\n",
       "body .cp { color: #007020 } /* Comment.Preproc */\n",
       "body .cpf { color: #60a0b0; font-style: italic } /* Comment.PreprocFile */\n",
       "body .c1 { color: #60a0b0; font-style: italic } /* Comment.Single */\n",
       "body .cs { color: #60a0b0; background-color: #fff0f0 } /* Comment.Special */\n",
       "body .gd { color: #A00000 } /* Generic.Deleted */\n",
       "body .ge { font-style: italic } /* Generic.Emph */\n",
       "body .gr { color: #FF0000 } /* Generic.Error */\n",
       "body .gh { color: #000080; font-weight: bold } /* Generic.Heading */\n",
       "body .gi { color: #00A000 } /* Generic.Inserted */\n",
       "body .go { color: #888888 } /* Generic.Output */\n",
       "body .gp { color: #c65d09; font-weight: bold } /* Generic.Prompt */\n",
       "body .gs { font-weight: bold } /* Generic.Strong */\n",
       "body .gu { color: #800080; font-weight: bold } /* Generic.Subheading */\n",
       "body .gt { color: #0044DD } /* Generic.Traceback */\n",
       "body .kc { color: #007020; font-weight: bold } /* Keyword.Constant */\n",
       "body .kd { color: #007020; font-weight: bold } /* Keyword.Declaration */\n",
       "body .kn { color: #007020; font-weight: bold } /* Keyword.Namespace */\n",
       "body .kp { color: #007020 } /* Keyword.Pseudo */\n",
       "body .kr { color: #007020; font-weight: bold } /* Keyword.Reserved */\n",
       "body .kt { color: #902000 } /* Keyword.Type */\n",
       "body .m { color: #40a070 } /* Literal.Number */\n",
       "body .s { color: #4070a0 } /* Literal.String */\n",
       "body .na { color: #4070a0 } /* Name.Attribute */\n",
       "body .nb { color: #007020 } /* Name.Builtin */\n",
       "body .nc { color: #0e84b5; font-weight: bold } /* Name.Class */\n",
       "body .no { color: #60add5 } /* Name.Constant */\n",
       "body .nd { color: #555555; font-weight: bold } /* Name.Decorator */\n",
       "body .ni { color: #d55537; font-weight: bold } /* Name.Entity */\n",
       "body .ne { color: #007020 } /* Name.Exception */\n",
       "body .nf { color: #06287e } /* Name.Function */\n",
       "body .nl { color: #002070; font-weight: bold } /* Name.Label */\n",
       "body .nn { color: #0e84b5; font-weight: bold } /* Name.Namespace */\n",
       "body .nt { color: #062873; font-weight: bold } /* Name.Tag */\n",
       "body .nv { color: #bb60d5 } /* Name.Variable */\n",
       "body .ow { color: #007020; font-weight: bold } /* Operator.Word */\n",
       "body .w { color: #bbbbbb } /* Text.Whitespace */\n",
       "body .mb { color: #40a070 } /* Literal.Number.Bin */\n",
       "body .mf { color: #40a070 } /* Literal.Number.Float */\n",
       "body .mh { color: #40a070 } /* Literal.Number.Hex */\n",
       "body .mi { color: #40a070 } /* Literal.Number.Integer */\n",
       "body .mo { color: #40a070 } /* Literal.Number.Oct */\n",
       "body .sa { color: #4070a0 } /* Literal.String.Affix */\n",
       "body .sb { color: #4070a0 } /* Literal.String.Backtick */\n",
       "body .sc { color: #4070a0 } /* Literal.String.Char */\n",
       "body .dl { color: #4070a0 } /* Literal.String.Delimiter */\n",
       "body .sd { color: #4070a0; font-style: italic } /* Literal.String.Doc */\n",
       "body .s2 { color: #4070a0 } /* Literal.String.Double */\n",
       "body .se { color: #4070a0; font-weight: bold } /* Literal.String.Escape */\n",
       "body .sh { color: #4070a0 } /* Literal.String.Heredoc */\n",
       "body .si { color: #70a0d0; font-style: italic } /* Literal.String.Interpol */\n",
       "body .sx { color: #c65d09 } /* Literal.String.Other */\n",
       "body .sr { color: #235388 } /* Literal.String.Regex */\n",
       "body .s1 { color: #4070a0 } /* Literal.String.Single */\n",
       "body .ss { color: #517918 } /* Literal.String.Symbol */\n",
       "body .bp { color: #007020 } /* Name.Builtin.Pseudo */\n",
       "body .fm { color: #06287e } /* Name.Function.Magic */\n",
       "body .vc { color: #bb60d5 } /* Name.Variable.Class */\n",
       "body .vg { color: #bb60d5 } /* Name.Variable.Global */\n",
       "body .vi { color: #bb60d5 } /* Name.Variable.Instance */\n",
       "body .vm { color: #bb60d5 } /* Name.Variable.Magic */\n",
       "body .il { color: #40a070 } /* Literal.Number.Integer.Long */\n",
       "\n",
       "  </style>\n",
       "</head>\n",
       "<body>\n",
       "<h2></h2>\n",
       "\n",
       "<div class=\"highlight\"><pre><span></span><span class=\"ch\">#!/bin/bash</span>\n",
       "\n",
       "<span class=\"nb\">export</span> <span class=\"nv\">S3_DATA_DIR</span><span class=\"o\">=</span>/data1/tmp_s3_data\n",
       "\n",
       "<span class=\"k\">if</span> <span class=\"o\">[</span> -d <span class=\"nv\">$S3_DATA_DIR</span> <span class=\"o\">]</span><span class=\"p\">;</span> <span class=\"k\">then</span> rm -rf <span class=\"nv\">$S3_DATA_DIR</span><span class=\"p\">;</span> <span class=\"k\">fi</span>\n",
       "\n",
       "mkdir <span class=\"nv\">$S3_DATA_DIR</span>\n",
       "<span class=\"nb\">cd</span> <span class=\"nv\">$S3_DATA_DIR</span>\n",
       "\n",
       "ls -laF <span class=\"nv\">$S3_DATA_DIR</span>\n",
       "\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Wireless_v1_00.tsv.gz amazon_reviews_us_Wireless_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Watches_v1_00.tsv.gz amazon_reviews_us_Watches_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Video_Games_v1_00.tsv.gz amazon_reviews_us_Video_Games_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Video_DVD_v1_00.tsv.gz amazon_reviews_us_Video_DVD_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Video_v1_00.tsv.gz amazon_reviews_us_Video_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Toys_v1_00.tsv.gz amazon_reviews_us_Toys_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Tools_v1_00.tsv.gz amazon_reviews_us_Tools_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Sports_v1_00.tsv.gz amazon_reviews_us_Sports_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Software_v1_00.tsv.gz amazon_reviews_us_Software_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Shoes_v1_00.tsv.gz amazon_reviews_us_Shoes_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Pet_Products_v1_00.tsv.gz amazon_reviews_us_Pet_Products_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Personal_Care_Appliances_v1_00.tsv.gz amazon_reviews_us_Personal_Care_Appliances_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_PC_v1_00.tsv.gz amazon_reviews_us_PC_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Outdoors_v1_00.tsv.gz amazon_reviews_us_Outdoors_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Office_Products_v1_00.tsv.gz amazon_reviews_us_Office_Products_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Musical_Instruments_v1_00.tsv.gz amazon_reviews_us_Musical_Instruments_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Music_v1_00.tsv.gz amazon_reviews_us_Music_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Mobile_Electronics_v1_00.tsv.gz amazon_reviews_us_Mobile_Electronics_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Mobile_Apps_v1_00.tsv.gz amazon_reviews_us_Mobile_Apps_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Major_Appliances_v1_00.tsv.gz amazon_reviews_us_Major_Appliances_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Luggage_v1_00.tsv.gz amazon_reviews_us_Luggage_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Lawn_and_Garden_v1_00.tsv.gz amazon_reviews_us_Lawn_and_Garden_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Kitchen_v1_00.tsv.gz amazon_reviews_us_Kitchen_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Jewelry_v1_00.tsv.gz amazon_reviews_us_Jewelry_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Home_Improvement_v1_00.tsv.gz amazon_reviews_us_Home_Improvement_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Home_Entertainment_v1_00.tsv.gz amazon_reviews_us_Home_Entertainment_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Home_v1_00.tsv.gz amazon_reviews_us_Home_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Health_Personal_Care_v1_00.tsv.gz amazon_reviews_us_Health_Personal_Care_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Grocery_v1_00.tsv.gz amazon_reviews_us_Grocery_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Gift_Card_v1_00.tsv.gz amazon_reviews_us_Gift_Card_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Furniture_v1_00.tsv.gz amazon_reviews_us_Furniture_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Electronics_v1_00.tsv.gz amazon_reviews_us_Electronics_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Digital_Video_Games_v1_00.tsv.gz amazon_reviews_us_Digital_Video_Games_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Digital_Video_Download_v1_00.tsv.gz amazon_reviews_us_Digital_Video_Download_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Digital_Software_v1_00.tsv.gz amazon_reviews_us_Digital_Software_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Digital_Music_Purchase_v1_00.tsv.gz amazon_reviews_us_Digital_Music_Purchase_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Digital_Ebook_Purchase_v1_01.tsv.gz amazon_reviews_us_Digital_Ebook_Purchase_v1_01.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Digital_Ebook_Purchase_v1_00.tsv.gz amazon_reviews_us_Digital_Ebook_Purchase_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Camera_v1_00.tsv.gz amazon_reviews_us_Camera_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Books_v1_02.tsv.gz amazon_reviews_us_Books_v1_02.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Books_v1_01.tsv.gz amazon_reviews_us_Books_v1_01.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Books_v1_00.tsv.gz amazon_reviews_us_Books_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Beauty_v1_00.tsv.gz amazon_reviews_us_Beauty_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Baby_v1_00.tsv.gz amazon_reviews_us_Baby_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Automotive_v1_00.tsv.gz amazon_reviews_us_Automotive_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_us_Apparel_v1_00.tsv.gz amazon_reviews_us_Apparel_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_multilingual_US_v1_00.tsv.gz amazon_reviews_multilingual_US_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_multilingual_UK_v1_00.tsv.gz amazon_reviews_multilingual_UK_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_multilingual_JP_v1_00.tsv.gz amazon_reviews_multilingual_JP_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_multilingual_FR_v1_00.tsv.gz amazon_reviews_multilingual_FR_v1_00.tsv.gz\n",
       "/usr/local/bin/aws s3 cp s3://amazon-reviews-pds/tsv/amazon_reviews_multilingual_DE_v1_00.tsv.gz amazon_reviews_multilingual_DE_v1_00.tsv.gz\n",
       "</pre></div>\n",
       "</body>\n",
       "</html>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "shfilecode = !pygmentize -f html -O full,style=friendly -l bash script/1-3-aws-s3-copy.sh\n",
    "display_html('\\n'.join(shfilecode), raw=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1-3-aws-s3-copy.sh                            100% 7251   397.3KB/s   00:00    \n",
      "total 0\n",
      "drwxr-xr-x 2 root    root     6 Dec 22 16:11 ./\n",
      "drwxr-xr-x 4 gpadmin gpadmin 39 Dec 22 16:11 ../\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Wireless_v1_00.tsv.gz to ./amazon_reviews_us_Wireless_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Watches_v1_00.tsv.gz to ./amazon_reviews_us_Watches_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Video_Games_v1_00.tsv.gz to ./amazon_reviews_us_Video_Games_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Video_DVD_v1_00.tsv.gz to ./amazon_reviews_us_Video_DVD_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Video_v1_00.tsv.gz to ./amazon_reviews_us_Video_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Toys_v1_00.tsv.gz to ./amazon_reviews_us_Toys_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Tools_v1_00.tsv.gz to ./amazon_reviews_us_Tools_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Sports_v1_00.tsv.gz to ./amazon_reviews_us_Sports_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Software_v1_00.tsv.gz to ./amazon_reviews_us_Software_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Shoes_v1_00.tsv.gz to ./amazon_reviews_us_Shoes_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Pet_Products_v1_00.tsv.gz to ./amazon_reviews_us_Pet_Products_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Personal_Care_Appliances_v1_00.tsv.gz to ./amazon_reviews_us_Personal_Care_Appliances_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_PC_v1_00.tsv.gz to ./amazon_reviews_us_PC_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Outdoors_v1_00.tsv.gz to ./amazon_reviews_us_Outdoors_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Office_Products_v1_00.tsv.gz to ./amazon_reviews_us_Office_Products_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Musical_Instruments_v1_00.tsv.gz to ./amazon_reviews_us_Musical_Instruments_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Music_v1_00.tsv.gz to ./amazon_reviews_us_Music_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Mobile_Electronics_v1_00.tsv.gz to ./amazon_reviews_us_Mobile_Electronics_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Mobile_Apps_v1_00.tsv.gz to ./amazon_reviews_us_Mobile_Apps_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Major_Appliances_v1_00.tsv.gz to ./amazon_reviews_us_Major_Appliances_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Luggage_v1_00.tsv.gz to ./amazon_reviews_us_Luggage_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Lawn_and_Garden_v1_00.tsv.gz to ./amazon_reviews_us_Lawn_and_Garden_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Kitchen_v1_00.tsv.gz to ./amazon_reviews_us_Kitchen_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Jewelry_v1_00.tsv.gz to ./amazon_reviews_us_Jewelry_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Home_Improvement_v1_00.tsv.gz to ./amazon_reviews_us_Home_Improvement_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Home_Entertainment_v1_00.tsv.gz to ./amazon_reviews_us_Home_Entertainment_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Home_v1_00.tsv.gz to ./amazon_reviews_us_Home_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Health_Personal_Care_v1_00.tsv.gz to ./amazon_reviews_us_Health_Personal_Care_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Grocery_v1_00.tsv.gz to ./amazon_reviews_us_Grocery_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Gift_Card_v1_00.tsv.gz to ./amazon_reviews_us_Gift_Card_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Furniture_v1_00.tsv.gz to ./amazon_reviews_us_Furniture_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Electronics_v1_00.tsv.gz to ./amazon_reviews_us_Electronics_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Digital_Video_Games_v1_00.tsv.gz to ./amazon_reviews_us_Digital_Video_Games_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Digital_Video_Download_v1_00.tsv.gz to ./amazon_reviews_us_Digital_Video_Download_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Digital_Software_v1_00.tsv.gz to ./amazon_reviews_us_Digital_Software_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Digital_Music_Purchase_v1_00.tsv.gz to ./amazon_reviews_us_Digital_Music_Purchase_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Digital_Ebook_Purchase_v1_01.tsv.gz to ./amazon_reviews_us_Digital_Ebook_Purchase_v1_01.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Digital_Ebook_Purchase_v1_00.tsv.gz to ./amazon_reviews_us_Digital_Ebook_Purchase_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Camera_v1_00.tsv.gz to ./amazon_reviews_us_Camera_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Books_v1_02.tsv.gz to ./amazon_reviews_us_Books_v1_02.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Books_v1_01.tsv.gz to ./amazon_reviews_us_Books_v1_01.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Books_v1_00.tsv.gz to ./amazon_reviews_us_Books_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Beauty_v1_00.tsv.gz to ./amazon_reviews_us_Beauty_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Baby_v1_00.tsv.gz to ./amazon_reviews_us_Baby_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Automotive_v1_00.tsv.gz to ./amazon_reviews_us_Automotive_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_us_Apparel_v1_00.tsv.gz to ./amazon_reviews_us_Apparel_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_multilingual_US_v1_00.tsv.gz to ./amazon_reviews_multilingual_US_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_multilingual_UK_v1_00.tsv.gz to ./amazon_reviews_multilingual_UK_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_multilingual_JP_v1_00.tsv.gz to ./amazon_reviews_multilingual_JP_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_multilingual_FR_v1_00.tsv.gz to ./amazon_reviews_multilingual_FR_v1_00.tsv.gz\n",
      "download: s3://amazon-reviews-pds/tsv/amazon_reviews_multilingual_DE_v1_00.tsv.gz to ./amazon_reviews_multilingual_DE_v1_00.tsv.gz\n"
     ]
    }
   ],
   "source": [
    "!scp -i ~/.ssh/aws-gp.pem script/1-3-aws-s3-copy.sh $DB_USER@$DB_SERVER:aws-s3-copy.sh\n",
    "!ssh -i ~/.ssh/aws-gp.pem $DB_USER@$DB_SERVER 'chmod +x ./aws-s3-copy.sh'\n",
    "!ssh -i ~/.ssh/aws-gp.pem $DB_USER@$DB_SERVER 'sudo ./aws-s3-copy.sh'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Data Loading\n",
    "### 3.1. Create the Schema (optional) and the Database Table to hold the dataset, as shown below:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<!DOCTYPE html PUBLIC \"-//W3C//DTD HTML 4.01//EN\"\n",
       "   \"http://www.w3.org/TR/html4/strict.dtd\">\n",
       "<!--\n",
       "generated by Pygments <http://pygments.org>\n",
       "Copyright 2006-2019 by the Pygments team.\n",
       "Licensed under the BSD license, see LICENSE for details.\n",
       "-->\n",
       "<html>\n",
       "<head>\n",
       "  <title></title>\n",
       "  <meta http-equiv=\"content-type\" content=\"text/html; charset=UTF-8\">\n",
       "  <style type=\"text/css\">\n",
       "/*\n",
       "generated by Pygments <http://pygments.org>\n",
       "Copyright 2006-2019 by the Pygments team.\n",
       "Licensed under the BSD license, see LICENSE for details.\n",
       "*/\n",
       "td.linenos { background-color: #f0f0f0; padding-right: 10px; }\n",
       "span.lineno { background-color: #f0f0f0; padding: 0 5px 0 5px; }\n",
       "pre { line-height: 125%; }\n",
       "body .hll { background-color: #ffffcc }\n",
       "body  { background: #f0f0f0; }\n",
       "body .c { color: #60a0b0; font-style: italic } /* Comment */\n",
       "body .err { border: 1px solid #FF0000 } /* Error */\n",
       "body .k { color: #007020; font-weight: bold } /* Keyword */\n",
       "body .o { color: #666666 } /* Operator */\n",
       "body .ch { color: #60a0b0; font-style: italic } /* Comment.Hashbang */\n",
       "body .cm { color: #60a0b0; font-style: italic } /* Comment.Multiline */\n",
       "body .cp { color: #007020 } /* Comment.Preproc */\n",
       "body .cpf { color: #60a0b0; font-style: italic } /* Comment.PreprocFile */\n",
       "body .c1 { color: #60a0b0; font-style: italic } /* Comment.Single */\n",
       "body .cs { color: #60a0b0; background-color: #fff0f0 } /* Comment.Special */\n",
       "body .gd { color: #A00000 } /* Generic.Deleted */\n",
       "body .ge { font-style: italic } /* Generic.Emph */\n",
       "body .gr { color: #FF0000 } /* Generic.Error */\n",
       "body .gh { color: #000080; font-weight: bold } /* Generic.Heading */\n",
       "body .gi { color: #00A000 } /* Generic.Inserted */\n",
       "body .go { color: #888888 } /* Generic.Output */\n",
       "body .gp { color: #c65d09; font-weight: bold } /* Generic.Prompt */\n",
       "body .gs { font-weight: bold } /* Generic.Strong */\n",
       "body .gu { color: #800080; font-weight: bold } /* Generic.Subheading */\n",
       "body .gt { color: #0044DD } /* Generic.Traceback */\n",
       "body .kc { color: #007020; font-weight: bold } /* Keyword.Constant */\n",
       "body .kd { color: #007020; font-weight: bold } /* Keyword.Declaration */\n",
       "body .kn { color: #007020; font-weight: bold } /* Keyword.Namespace */\n",
       "body .kp { color: #007020 } /* Keyword.Pseudo */\n",
       "body .kr { color: #007020; font-weight: bold } /* Keyword.Reserved */\n",
       "body .kt { color: #902000 } /* Keyword.Type */\n",
       "body .m { color: #40a070 } /* Literal.Number */\n",
       "body .s { color: #4070a0 } /* Literal.String */\n",
       "body .na { color: #4070a0 } /* Name.Attribute */\n",
       "body .nb { color: #007020 } /* Name.Builtin */\n",
       "body .nc { color: #0e84b5; font-weight: bold } /* Name.Class */\n",
       "body .no { color: #60add5 } /* Name.Constant */\n",
       "body .nd { color: #555555; font-weight: bold } /* Name.Decorator */\n",
       "body .ni { color: #d55537; font-weight: bold } /* Name.Entity */\n",
       "body .ne { color: #007020 } /* Name.Exception */\n",
       "body .nf { color: #06287e } /* Name.Function */\n",
       "body .nl { color: #002070; font-weight: bold } /* Name.Label */\n",
       "body .nn { color: #0e84b5; font-weight: bold } /* Name.Namespace */\n",
       "body .nt { color: #062873; font-weight: bold } /* Name.Tag */\n",
       "body .nv { color: #bb60d5 } /* Name.Variable */\n",
       "body .ow { color: #007020; font-weight: bold } /* Operator.Word */\n",
       "body .w { color: #bbbbbb } /* Text.Whitespace */\n",
       "body .mb { color: #40a070 } /* Literal.Number.Bin */\n",
       "body .mf { color: #40a070 } /* Literal.Number.Float */\n",
       "body .mh { color: #40a070 } /* Literal.Number.Hex */\n",
       "body .mi { color: #40a070 } /* Literal.Number.Integer */\n",
       "body .mo { color: #40a070 } /* Literal.Number.Oct */\n",
       "body .sa { color: #4070a0 } /* Literal.String.Affix */\n",
       "body .sb { color: #4070a0 } /* Literal.String.Backtick */\n",
       "body .sc { color: #4070a0 } /* Literal.String.Char */\n",
       "body .dl { color: #4070a0 } /* Literal.String.Delimiter */\n",
       "body .sd { color: #4070a0; font-style: italic } /* Literal.String.Doc */\n",
       "body .s2 { color: #4070a0 } /* Literal.String.Double */\n",
       "body .se { color: #4070a0; font-weight: bold } /* Literal.String.Escape */\n",
       "body .sh { color: #4070a0 } /* Literal.String.Heredoc */\n",
       "body .si { color: #70a0d0; font-style: italic } /* Literal.String.Interpol */\n",
       "body .sx { color: #c65d09 } /* Literal.String.Other */\n",
       "body .sr { color: #235388 } /* Literal.String.Regex */\n",
       "body .s1 { color: #4070a0 } /* Literal.String.Single */\n",
       "body .ss { color: #517918 } /* Literal.String.Symbol */\n",
       "body .bp { color: #007020 } /* Name.Builtin.Pseudo */\n",
       "body .fm { color: #06287e } /* Name.Function.Magic */\n",
       "body .vc { color: #bb60d5 } /* Name.Variable.Class */\n",
       "body .vg { color: #bb60d5 } /* Name.Variable.Global */\n",
       "body .vi { color: #bb60d5 } /* Name.Variable.Instance */\n",
       "body .vm { color: #bb60d5 } /* Name.Variable.Magic */\n",
       "body .il { color: #40a070 } /* Literal.Number.Integer.Long */\n",
       "\n",
       "  </style>\n",
       "</head>\n",
       "<body>\n",
       "<h2></h2>\n",
       "\n",
       "<div class=\"highlight\"><pre><span></span><span class=\"k\">DROP</span> <span class=\"k\">SCHEMA</span> <span class=\"k\">IF</span> <span class=\"k\">EXISTS</span> <span class=\"n\">demo</span> <span class=\"k\">CASCADE</span><span class=\"p\">;</span>\n",
       "\n",
       "<span class=\"k\">CREATE</span> <span class=\"k\">SCHEMA</span> <span class=\"n\">demo</span><span class=\"p\">;</span>\n",
       "\n",
       "<span class=\"k\">DROP</span> <span class=\"k\">TABLE</span> <span class=\"k\">IF</span> <span class=\"k\">EXISTS</span> <span class=\"n\">demo</span><span class=\"mf\">.</span><span class=\"n\">amzn_reviews</span><span class=\"p\">;</span>\n",
       "\n",
       "<span class=\"k\">CREATE</span> <span class=\"k\">TABLE</span> <span class=\"n\">demo</span><span class=\"mf\">.</span><span class=\"n\">amzn_reviews</span><span class=\"p\">(</span>\n",
       "  <span class=\"n\">marketplace</span> <span class=\"nb\">TEXT</span><span class=\"p\">,</span> \n",
       "  <span class=\"n\">customer_id</span> <span class=\"nb\">BIGINT</span><span class=\"p\">,</span> \n",
       "  <span class=\"n\">review_id</span> <span class=\"nb\">TEXT</span><span class=\"p\">,</span>\n",
       "  <span class=\"n\">product_id</span> <span class=\"nb\">TEXT</span><span class=\"p\">,</span> \n",
       "  <span class=\"n\">product_parent</span> <span class=\"nb\">BIGINT</span><span class=\"p\">,</span> \n",
       "  <span class=\"n\">product_title</span> <span class=\"nb\">TEXT</span><span class=\"p\">,</span> \n",
       "  <span class=\"n\">product_category</span> <span class=\"nb\">TEXT</span><span class=\"p\">,</span> \n",
       "  <span class=\"n\">star_rating</span> <span class=\"nb\">INTEGER</span><span class=\"p\">,</span> \n",
       "  <span class=\"n\">helpful_votes</span> <span class=\"nb\">INTEGER</span><span class=\"p\">,</span> \n",
       "  <span class=\"n\">total_votes</span> <span class=\"nb\">INTEGER</span><span class=\"p\">,</span> \n",
       "  <span class=\"n\">vine</span> <span class=\"nb\">TEXT</span><span class=\"p\">,</span> \n",
       "  <span class=\"n\">verified_purchase</span> <span class=\"nb\">TEXT</span><span class=\"p\">,</span> \n",
       "  <span class=\"n\">review_headline</span> <span class=\"nb\">TEXT</span><span class=\"p\">,</span> \n",
       "  <span class=\"n\">review_body</span> <span class=\"nb\">TEXT</span><span class=\"p\">,</span> \n",
       "  <span class=\"n\">review_date</span> <span class=\"nb\">DATE</span><span class=\"p\">)</span> \n",
       "<span class=\"n\">DISTRIBUTED</span> <span class=\"k\">BY</span> <span class=\"p\">(</span><span class=\"n\">review_id</span><span class=\"p\">);</span>\n",
       "</pre></div>\n",
       "</body>\n",
       "</html>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sqlfilecode = !pygmentize -f html -O full,style=friendly -l postgres script/2-1-create-db-schema-table.sql\n",
    "display_html('\\n'.join(sqlfilecode), raw=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = !cat script/2-1-create-db-schema-table.sql\n",
    "%sql $DB_USER@$DB_NAME {''.join(query)}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<!DOCTYPE html PUBLIC \"-//W3C//DTD HTML 4.01//EN\"\n",
       "   \"http://www.w3.org/TR/html4/strict.dtd\">\n",
       "<!--\n",
       "generated by Pygments <http://pygments.org>\n",
       "Copyright 2006-2019 by the Pygments team.\n",
       "Licensed under the BSD license, see LICENSE for details.\n",
       "-->\n",
       "<html>\n",
       "<head>\n",
       "  <title></title>\n",
       "  <meta http-equiv=\"content-type\" content=\"text/html; charset=UTF-8\">\n",
       "  <style type=\"text/css\">\n",
       "/*\n",
       "generated by Pygments <http://pygments.org>\n",
       "Copyright 2006-2019 by the Pygments team.\n",
       "Licensed under the BSD license, see LICENSE for details.\n",
       "*/\n",
       "td.linenos { background-color: #f0f0f0; padding-right: 10px; }\n",
       "span.lineno { background-color: #f0f0f0; padding: 0 5px 0 5px; }\n",
       "pre { line-height: 125%; }\n",
       "body .hll { background-color: #ffffcc }\n",
       "body  { background: #f0f0f0; }\n",
       "body .c { color: #60a0b0; font-style: italic } /* Comment */\n",
       "body .err { border: 1px solid #FF0000 } /* Error */\n",
       "body .k { color: #007020; font-weight: bold } /* Keyword */\n",
       "body .o { color: #666666 } /* Operator */\n",
       "body .ch { color: #60a0b0; font-style: italic } /* Comment.Hashbang */\n",
       "body .cm { color: #60a0b0; font-style: italic } /* Comment.Multiline */\n",
       "body .cp { color: #007020 } /* Comment.Preproc */\n",
       "body .cpf { color: #60a0b0; font-style: italic } /* Comment.PreprocFile */\n",
       "body .c1 { color: #60a0b0; font-style: italic } /* Comment.Single */\n",
       "body .cs { color: #60a0b0; background-color: #fff0f0 } /* Comment.Special */\n",
       "body .gd { color: #A00000 } /* Generic.Deleted */\n",
       "body .ge { font-style: italic } /* Generic.Emph */\n",
       "body .gr { color: #FF0000 } /* Generic.Error */\n",
       "body .gh { color: #000080; font-weight: bold } /* Generic.Heading */\n",
       "body .gi { color: #00A000 } /* Generic.Inserted */\n",
       "body .go { color: #888888 } /* Generic.Output */\n",
       "body .gp { color: #c65d09; font-weight: bold } /* Generic.Prompt */\n",
       "body .gs { font-weight: bold } /* Generic.Strong */\n",
       "body .gu { color: #800080; font-weight: bold } /* Generic.Subheading */\n",
       "body .gt { color: #0044DD } /* Generic.Traceback */\n",
       "body .kc { color: #007020; font-weight: bold } /* Keyword.Constant */\n",
       "body .kd { color: #007020; font-weight: bold } /* Keyword.Declaration */\n",
       "body .kn { color: #007020; font-weight: bold } /* Keyword.Namespace */\n",
       "body .kp { color: #007020 } /* Keyword.Pseudo */\n",
       "body .kr { color: #007020; font-weight: bold } /* Keyword.Reserved */\n",
       "body .kt { color: #902000 } /* Keyword.Type */\n",
       "body .m { color: #40a070 } /* Literal.Number */\n",
       "body .s { color: #4070a0 } /* Literal.String */\n",
       "body .na { color: #4070a0 } /* Name.Attribute */\n",
       "body .nb { color: #007020 } /* Name.Builtin */\n",
       "body .nc { color: #0e84b5; font-weight: bold } /* Name.Class */\n",
       "body .no { color: #60add5 } /* Name.Constant */\n",
       "body .nd { color: #555555; font-weight: bold } /* Name.Decorator */\n",
       "body .ni { color: #d55537; font-weight: bold } /* Name.Entity */\n",
       "body .ne { color: #007020 } /* Name.Exception */\n",
       "body .nf { color: #06287e } /* Name.Function */\n",
       "body .nl { color: #002070; font-weight: bold } /* Name.Label */\n",
       "body .nn { color: #0e84b5; font-weight: bold } /* Name.Namespace */\n",
       "body .nt { color: #062873; font-weight: bold } /* Name.Tag */\n",
       "body .nv { color: #bb60d5 } /* Name.Variable */\n",
       "body .ow { color: #007020; font-weight: bold } /* Operator.Word */\n",
       "body .w { color: #bbbbbb } /* Text.Whitespace */\n",
       "body .mb { color: #40a070 } /* Literal.Number.Bin */\n",
       "body .mf { color: #40a070 } /* Literal.Number.Float */\n",
       "body .mh { color: #40a070 } /* Literal.Number.Hex */\n",
       "body .mi { color: #40a070 } /* Literal.Number.Integer */\n",
       "body .mo { color: #40a070 } /* Literal.Number.Oct */\n",
       "body .sa { color: #4070a0 } /* Literal.String.Affix */\n",
       "body .sb { color: #4070a0 } /* Literal.String.Backtick */\n",
       "body .sc { color: #4070a0 } /* Literal.String.Char */\n",
       "body .dl { color: #4070a0 } /* Literal.String.Delimiter */\n",
       "body .sd { color: #4070a0; font-style: italic } /* Literal.String.Doc */\n",
       "body .s2 { color: #4070a0 } /* Literal.String.Double */\n",
       "body .se { color: #4070a0; font-weight: bold } /* Literal.String.Escape */\n",
       "body .sh { color: #4070a0 } /* Literal.String.Heredoc */\n",
       "body .si { color: #70a0d0; font-style: italic } /* Literal.String.Interpol */\n",
       "body .sx { color: #c65d09 } /* Literal.String.Other */\n",
       "body .sr { color: #235388 } /* Literal.String.Regex */\n",
       "body .s1 { color: #4070a0 } /* Literal.String.Single */\n",
       "body .ss { color: #517918 } /* Literal.String.Symbol */\n",
       "body .bp { color: #007020 } /* Name.Builtin.Pseudo */\n",
       "body .fm { color: #06287e } /* Name.Function.Magic */\n",
       "body .vc { color: #bb60d5 } /* Name.Variable.Class */\n",
       "body .vg { color: #bb60d5 } /* Name.Variable.Global */\n",
       "body .vi { color: #bb60d5 } /* Name.Variable.Instance */\n",
       "body .vm { color: #bb60d5 } /* Name.Variable.Magic */\n",
       "body .il { color: #40a070 } /* Literal.Number.Integer.Long */\n",
       "\n",
       "  </style>\n",
       "</head>\n",
       "<body>\n",
       "<h2></h2>\n",
       "\n",
       "<div class=\"highlight\"><pre><span></span><span class=\"k\">SELECT</span> <span class=\"n\">COUNT</span><span class=\"p\">(</span><span class=\"o\">*</span><span class=\"p\">)</span> <span class=\"k\">FROM</span> <span class=\"n\">demo</span><span class=\"mf\">.</span><span class=\"n\">amzn_reviews</span><span class=\"p\">;</span>\n",
       "</pre></div>\n",
       "</body>\n",
       "</html>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sqlfilecode = !pygmentize -f html -O full,style=friendly -l postgres script/2-2-count-table.sql\n",
    "display_html('\\n'.join(sqlfilecode), raw=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(0,)]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = !cat script/2-2-count-table.sql\n",
    "%sql $DB_USER@$DB_NAME {''.join(query)}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.2. Load the Input Dataset using the gpload Utility\n",
    "**gpload** is a data loading utility that acts as an interface to the Greenplum Database external table parallel loading feature. Using a load specification defined in a YAML formatted control file, gpload executes a load by invoking the Greenplum Database parallel file server (**gpfdist**), creating an external table definition based on the source data defined, and executing an *INSERT*, *UPDATE* or *MERGE* operation to load the source data into the target table in the database.\n",
    "\n",
    "You can declare more than one file as input/source as long as the data is of the same format in all files specified. Additionally, if the files are compressed using **gzip** or **bzip2** (have a .gz or .bz2 file extension), the files will be uncompressed automatically (provided that gunzip or bunzip2 is in your path). You can also declare options such as the schema of the source data files, perform basic transformations, define custom delimiter and/or escape character(s), and many more. For the full list of available options, check the GPLoad Utility Reference available on [Pivotal Greenplum Database Documentation](https://gpdb.docs.pivotal.io/latest) (*Pivotal Greenplum Documentation > Utility Guide > Management Utility Reference > gpload*).\n",
    "\n",
    "The operation, including any SQL commands specified in the SQL collection of the YAML control file, are performed as a single transaction to prevent inconsistent data when performing multiple, simultaneous load operations on a target table.\n",
    "\n",
    "For our demo, we have prepared the *gpload_amzn_reviews.yaml* YAML control file, as shown here:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<!DOCTYPE html PUBLIC \"-//W3C//DTD HTML 4.01//EN\"\n",
       "   \"http://www.w3.org/TR/html4/strict.dtd\">\n",
       "<!--\n",
       "generated by Pygments <http://pygments.org>\n",
       "Copyright 2006-2019 by the Pygments team.\n",
       "Licensed under the BSD license, see LICENSE for details.\n",
       "-->\n",
       "<html>\n",
       "<head>\n",
       "  <title></title>\n",
       "  <meta http-equiv=\"content-type\" content=\"text/html; charset=UTF-8\">\n",
       "  <style type=\"text/css\">\n",
       "/*\n",
       "generated by Pygments <http://pygments.org>\n",
       "Copyright 2006-2019 by the Pygments team.\n",
       "Licensed under the BSD license, see LICENSE for details.\n",
       "*/\n",
       "td.linenos { background-color: #f0f0f0; padding-right: 10px; }\n",
       "span.lineno { background-color: #f0f0f0; padding: 0 5px 0 5px; }\n",
       "pre { line-height: 125%; }\n",
       "body .hll { background-color: #ffffcc }\n",
       "body  { background: #f0f0f0; }\n",
       "body .c { color: #60a0b0; font-style: italic } /* Comment */\n",
       "body .err { border: 1px solid #FF0000 } /* Error */\n",
       "body .k { color: #007020; font-weight: bold } /* Keyword */\n",
       "body .o { color: #666666 } /* Operator */\n",
       "body .ch { color: #60a0b0; font-style: italic } /* Comment.Hashbang */\n",
       "body .cm { color: #60a0b0; font-style: italic } /* Comment.Multiline */\n",
       "body .cp { color: #007020 } /* Comment.Preproc */\n",
       "body .cpf { color: #60a0b0; font-style: italic } /* Comment.PreprocFile */\n",
       "body .c1 { color: #60a0b0; font-style: italic } /* Comment.Single */\n",
       "body .cs { color: #60a0b0; background-color: #fff0f0 } /* Comment.Special */\n",
       "body .gd { color: #A00000 } /* Generic.Deleted */\n",
       "body .ge { font-style: italic } /* Generic.Emph */\n",
       "body .gr { color: #FF0000 } /* Generic.Error */\n",
       "body .gh { color: #000080; font-weight: bold } /* Generic.Heading */\n",
       "body .gi { color: #00A000 } /* Generic.Inserted */\n",
       "body .go { color: #888888 } /* Generic.Output */\n",
       "body .gp { color: #c65d09; font-weight: bold } /* Generic.Prompt */\n",
       "body .gs { font-weight: bold } /* Generic.Strong */\n",
       "body .gu { color: #800080; font-weight: bold } /* Generic.Subheading */\n",
       "body .gt { color: #0044DD } /* Generic.Traceback */\n",
       "body .kc { color: #007020; font-weight: bold } /* Keyword.Constant */\n",
       "body .kd { color: #007020; font-weight: bold } /* Keyword.Declaration */\n",
       "body .kn { color: #007020; font-weight: bold } /* Keyword.Namespace */\n",
       "body .kp { color: #007020 } /* Keyword.Pseudo */\n",
       "body .kr { color: #007020; font-weight: bold } /* Keyword.Reserved */\n",
       "body .kt { color: #902000 } /* Keyword.Type */\n",
       "body .m { color: #40a070 } /* Literal.Number */\n",
       "body .s { color: #4070a0 } /* Literal.String */\n",
       "body .na { color: #4070a0 } /* Name.Attribute */\n",
       "body .nb { color: #007020 } /* Name.Builtin */\n",
       "body .nc { color: #0e84b5; font-weight: bold } /* Name.Class */\n",
       "body .no { color: #60add5 } /* Name.Constant */\n",
       "body .nd { color: #555555; font-weight: bold } /* Name.Decorator */\n",
       "body .ni { color: #d55537; font-weight: bold } /* Name.Entity */\n",
       "body .ne { color: #007020 } /* Name.Exception */\n",
       "body .nf { color: #06287e } /* Name.Function */\n",
       "body .nl { color: #002070; font-weight: bold } /* Name.Label */\n",
       "body .nn { color: #0e84b5; font-weight: bold } /* Name.Namespace */\n",
       "body .nt { color: #062873; font-weight: bold } /* Name.Tag */\n",
       "body .nv { color: #bb60d5 } /* Name.Variable */\n",
       "body .ow { color: #007020; font-weight: bold } /* Operator.Word */\n",
       "body .w { color: #bbbbbb } /* Text.Whitespace */\n",
       "body .mb { color: #40a070 } /* Literal.Number.Bin */\n",
       "body .mf { color: #40a070 } /* Literal.Number.Float */\n",
       "body .mh { color: #40a070 } /* Literal.Number.Hex */\n",
       "body .mi { color: #40a070 } /* Literal.Number.Integer */\n",
       "body .mo { color: #40a070 } /* Literal.Number.Oct */\n",
       "body .sa { color: #4070a0 } /* Literal.String.Affix */\n",
       "body .sb { color: #4070a0 } /* Literal.String.Backtick */\n",
       "body .sc { color: #4070a0 } /* Literal.String.Char */\n",
       "body .dl { color: #4070a0 } /* Literal.String.Delimiter */\n",
       "body .sd { color: #4070a0; font-style: italic } /* Literal.String.Doc */\n",
       "body .s2 { color: #4070a0 } /* Literal.String.Double */\n",
       "body .se { color: #4070a0; font-weight: bold } /* Literal.String.Escape */\n",
       "body .sh { color: #4070a0 } /* Literal.String.Heredoc */\n",
       "body .si { color: #70a0d0; font-style: italic } /* Literal.String.Interpol */\n",
       "body .sx { color: #c65d09 } /* Literal.String.Other */\n",
       "body .sr { color: #235388 } /* Literal.String.Regex */\n",
       "body .s1 { color: #4070a0 } /* Literal.String.Single */\n",
       "body .ss { color: #517918 } /* Literal.String.Symbol */\n",
       "body .bp { color: #007020 } /* Name.Builtin.Pseudo */\n",
       "body .fm { color: #06287e } /* Name.Function.Magic */\n",
       "body .vc { color: #bb60d5 } /* Name.Variable.Class */\n",
       "body .vg { color: #bb60d5 } /* Name.Variable.Global */\n",
       "body .vi { color: #bb60d5 } /* Name.Variable.Instance */\n",
       "body .vm { color: #bb60d5 } /* Name.Variable.Magic */\n",
       "body .il { color: #40a070 } /* Literal.Number.Integer.Long */\n",
       "\n",
       "  </style>\n",
       "</head>\n",
       "<body>\n",
       "<h2></h2>\n",
       "\n",
       "<div class=\"highlight\"><pre><span></span><span class=\"nt\">VERSION</span><span class=\"p\">:</span> <span class=\"l l-Scalar l-Scalar-Plain\">1.0.0.1</span>\n",
       "<span class=\"nt\">GPLOAD</span><span class=\"p\">:</span>\n",
       "   <span class=\"nt\">INPUT</span><span class=\"p\">:</span>\n",
       "    <span class=\"p p-Indicator\">-</span> <span class=\"nt\">SOURCE</span><span class=\"p\">:</span>\n",
       "         <span class=\"nt\">FILE</span><span class=\"p\">:</span>\n",
       "           <span class=\"p p-Indicator\">-</span> <span class=\"l l-Scalar l-Scalar-Plain\">/data1/tmp_s3_data/amazon_reviews_us_*.tsv.gz</span>\n",
       "    <span class=\"p p-Indicator\">-</span> <span class=\"nt\">FORMAT</span><span class=\"p\">:</span> <span class=\"l l-Scalar l-Scalar-Plain\">text</span>\n",
       "    <span class=\"p p-Indicator\">-</span> <span class=\"nt\">HEADER</span><span class=\"p\">:</span> <span class=\"l l-Scalar l-Scalar-Plain\">true</span>\n",
       "    <span class=\"p p-Indicator\">-</span> <span class=\"nt\">LOG_ERRORS</span><span class=\"p\">:</span> <span class=\"l l-Scalar l-Scalar-Plain\">true</span>\n",
       "    <span class=\"p p-Indicator\">-</span> <span class=\"nt\">MAX_LINE_LENGTH</span><span class=\"p\">:</span> <span class=\"l l-Scalar l-Scalar-Plain\">100000</span>\n",
       "    <span class=\"p p-Indicator\">-</span> <span class=\"nt\">ERROR_LIMIT</span><span class=\"p\">:</span> <span class=\"l l-Scalar l-Scalar-Plain\">50000</span>\n",
       "   <span class=\"nt\">OUTPUT</span><span class=\"p\">:</span>\n",
       "    <span class=\"p p-Indicator\">-</span> <span class=\"nt\">TABLE</span><span class=\"p\">:</span> <span class=\"l l-Scalar l-Scalar-Plain\">demo.amzn_reviews</span>\n",
       "    <span class=\"p p-Indicator\">-</span> <span class=\"nt\">MODE</span><span class=\"p\">:</span> <span class=\"l l-Scalar l-Scalar-Plain\">insert</span>\n",
       "   <span class=\"nt\">PRELOAD</span><span class=\"p\">:</span>\n",
       "    <span class=\"p p-Indicator\">-</span> <span class=\"nt\">TRUNCATE</span><span class=\"p\">:</span> <span class=\"l l-Scalar l-Scalar-Plain\">true</span>\n",
       "    <span class=\"p p-Indicator\">-</span> <span class=\"nt\">REUSE_TABLES</span><span class=\"p\">:</span> <span class=\"l l-Scalar l-Scalar-Plain\">true</span>\n",
       "</pre></div>\n",
       "</body>\n",
       "</html>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sqlfilecode = !pygmentize -f html -O full,style=friendly -l yaml script/3-2-gpload-amzn-reviews.yaml\n",
    "display_html('\\n'.join(sqlfilecode), raw=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 3.2.1. Delete error log information for existing tables in the current database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<!DOCTYPE html PUBLIC \"-//W3C//DTD HTML 4.01//EN\"\n",
       "   \"http://www.w3.org/TR/html4/strict.dtd\">\n",
       "<!--\n",
       "generated by Pygments <http://pygments.org>\n",
       "Copyright 2006-2019 by the Pygments team.\n",
       "Licensed under the BSD license, see LICENSE for details.\n",
       "-->\n",
       "<html>\n",
       "<head>\n",
       "  <title></title>\n",
       "  <meta http-equiv=\"content-type\" content=\"text/html; charset=UTF-8\">\n",
       "  <style type=\"text/css\">\n",
       "/*\n",
       "generated by Pygments <http://pygments.org>\n",
       "Copyright 2006-2019 by the Pygments team.\n",
       "Licensed under the BSD license, see LICENSE for details.\n",
       "*/\n",
       "td.linenos { background-color: #f0f0f0; padding-right: 10px; }\n",
       "span.lineno { background-color: #f0f0f0; padding: 0 5px 0 5px; }\n",
       "pre { line-height: 125%; }\n",
       "body .hll { background-color: #ffffcc }\n",
       "body  { background: #f0f0f0; }\n",
       "body .c { color: #60a0b0; font-style: italic } /* Comment */\n",
       "body .err { border: 1px solid #FF0000 } /* Error */\n",
       "body .k { color: #007020; font-weight: bold } /* Keyword */\n",
       "body .o { color: #666666 } /* Operator */\n",
       "body .ch { color: #60a0b0; font-style: italic } /* Comment.Hashbang */\n",
       "body .cm { color: #60a0b0; font-style: italic } /* Comment.Multiline */\n",
       "body .cp { color: #007020 } /* Comment.Preproc */\n",
       "body .cpf { color: #60a0b0; font-style: italic } /* Comment.PreprocFile */\n",
       "body .c1 { color: #60a0b0; font-style: italic } /* Comment.Single */\n",
       "body .cs { color: #60a0b0; background-color: #fff0f0 } /* Comment.Special */\n",
       "body .gd { color: #A00000 } /* Generic.Deleted */\n",
       "body .ge { font-style: italic } /* Generic.Emph */\n",
       "body .gr { color: #FF0000 } /* Generic.Error */\n",
       "body .gh { color: #000080; font-weight: bold } /* Generic.Heading */\n",
       "body .gi { color: #00A000 } /* Generic.Inserted */\n",
       "body .go { color: #888888 } /* Generic.Output */\n",
       "body .gp { color: #c65d09; font-weight: bold } /* Generic.Prompt */\n",
       "body .gs { font-weight: bold } /* Generic.Strong */\n",
       "body .gu { color: #800080; font-weight: bold } /* Generic.Subheading */\n",
       "body .gt { color: #0044DD } /* Generic.Traceback */\n",
       "body .kc { color: #007020; font-weight: bold } /* Keyword.Constant */\n",
       "body .kd { color: #007020; font-weight: bold } /* Keyword.Declaration */\n",
       "body .kn { color: #007020; font-weight: bold } /* Keyword.Namespace */\n",
       "body .kp { color: #007020 } /* Keyword.Pseudo */\n",
       "body .kr { color: #007020; font-weight: bold } /* Keyword.Reserved */\n",
       "body .kt { color: #902000 } /* Keyword.Type */\n",
       "body .m { color: #40a070 } /* Literal.Number */\n",
       "body .s { color: #4070a0 } /* Literal.String */\n",
       "body .na { color: #4070a0 } /* Name.Attribute */\n",
       "body .nb { color: #007020 } /* Name.Builtin */\n",
       "body .nc { color: #0e84b5; font-weight: bold } /* Name.Class */\n",
       "body .no { color: #60add5 } /* Name.Constant */\n",
       "body .nd { color: #555555; font-weight: bold } /* Name.Decorator */\n",
       "body .ni { color: #d55537; font-weight: bold } /* Name.Entity */\n",
       "body .ne { color: #007020 } /* Name.Exception */\n",
       "body .nf { color: #06287e } /* Name.Function */\n",
       "body .nl { color: #002070; font-weight: bold } /* Name.Label */\n",
       "body .nn { color: #0e84b5; font-weight: bold } /* Name.Namespace */\n",
       "body .nt { color: #062873; font-weight: bold } /* Name.Tag */\n",
       "body .nv { color: #bb60d5 } /* Name.Variable */\n",
       "body .ow { color: #007020; font-weight: bold } /* Operator.Word */\n",
       "body .w { color: #bbbbbb } /* Text.Whitespace */\n",
       "body .mb { color: #40a070 } /* Literal.Number.Bin */\n",
       "body .mf { color: #40a070 } /* Literal.Number.Float */\n",
       "body .mh { color: #40a070 } /* Literal.Number.Hex */\n",
       "body .mi { color: #40a070 } /* Literal.Number.Integer */\n",
       "body .mo { color: #40a070 } /* Literal.Number.Oct */\n",
       "body .sa { color: #4070a0 } /* Literal.String.Affix */\n",
       "body .sb { color: #4070a0 } /* Literal.String.Backtick */\n",
       "body .sc { color: #4070a0 } /* Literal.String.Char */\n",
       "body .dl { color: #4070a0 } /* Literal.String.Delimiter */\n",
       "body .sd { color: #4070a0; font-style: italic } /* Literal.String.Doc */\n",
       "body .s2 { color: #4070a0 } /* Literal.String.Double */\n",
       "body .se { color: #4070a0; font-weight: bold } /* Literal.String.Escape */\n",
       "body .sh { color: #4070a0 } /* Literal.String.Heredoc */\n",
       "body .si { color: #70a0d0; font-style: italic } /* Literal.String.Interpol */\n",
       "body .sx { color: #c65d09 } /* Literal.String.Other */\n",
       "body .sr { color: #235388 } /* Literal.String.Regex */\n",
       "body .s1 { color: #4070a0 } /* Literal.String.Single */\n",
       "body .ss { color: #517918 } /* Literal.String.Symbol */\n",
       "body .bp { color: #007020 } /* Name.Builtin.Pseudo */\n",
       "body .fm { color: #06287e } /* Name.Function.Magic */\n",
       "body .vc { color: #bb60d5 } /* Name.Variable.Class */\n",
       "body .vg { color: #bb60d5 } /* Name.Variable.Global */\n",
       "body .vi { color: #bb60d5 } /* Name.Variable.Instance */\n",
       "body .vm { color: #bb60d5 } /* Name.Variable.Magic */\n",
       "body .il { color: #40a070 } /* Literal.Number.Integer.Long */\n",
       "\n",
       "  </style>\n",
       "</head>\n",
       "<body>\n",
       "<h2></h2>\n",
       "\n",
       "<div class=\"highlight\"><pre><span></span><span class=\"k\">SELECT</span> <span class=\"n\">gp_truncate_error_log</span><span class=\"p\">(</span><span class=\"s1\">&#39;demo.amzn_reviews&#39;</span><span class=\"p\">);</span>\n",
       "</pre></div>\n",
       "</body>\n",
       "</html>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sqlfilecode = !pygmentize -f html -O full,style=friendly -l postgres script/3-1-delete-error-log-info.sql\n",
    "display_html('\\n'.join(sqlfilecode), raw=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>gp_truncate_error_log</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>True</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(True,)]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "!ssh -i ~/.ssh/aws-gp.pem $DB_USER@$DB_SERVER 'if [ -f ./gpload_amzn_reviews.log ]; then rm ./gpload_amzn_reviews.log; fi'\n",
    "\n",
    "query = !cat script/3-1-delete-error-log-info.sql\n",
    "%sql $DB_USER@$DB_NAME {''.join(query)}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 3.2.2. Copy GPLoad YAML file across to the Database Server and execute"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3-2-gpload-amzn-reviews.yaml                  100%  358    41.1KB/s   00:00    \n",
      "2020-12-22 16:25:49|INFO|gpload session started 2020-12-22 16:25:49\n",
      "2020-12-22 16:25:49|INFO|no host supplied, defaulting to localhost\n",
      "2020-12-22 16:25:49|INFO|started gpfdist -p 8000 -P 9000 -f \"/data1/tmp_s3_data/amazon_reviews_us_*.tsv.gz\" -t 30 -m 100000\n",
      "2020-12-22 16:25:51|INFO|did not find an external table to reuse. creating ext_gpload_reusable_5a278b68_4472_11eb_9df3_064760077968\n",
      "2020-12-22 16:35:47|WARN|7622 bad rows\n",
      "2020-12-22 16:35:47|WARN|Please use following query to access the detailed error\n",
      "2020-12-22 16:35:47|WARN|select * from gp_read_error_log('ext_gpload_reusable_5a278b68_4472_11eb_9df3_064760077968') where cmdtime > to_timestamp('1608654349.44')\n",
      "2020-12-22 16:35:47|INFO|running time: 598.30 seconds\n",
      "2020-12-22 16:35:47|INFO|rows Inserted          = 150955707\n",
      "2020-12-22 16:35:47|INFO|rows Updated           = 0\n",
      "2020-12-22 16:35:47|INFO|data formatting errors = 7622\n",
      "2020-12-22 16:35:47|INFO|gpload succeeded with warnings\n"
     ]
    }
   ],
   "source": [
    "!scp -i ~/.ssh/aws-gp.pem script/3-2-gpload-amzn-reviews.yaml $DB_USER@$DB_SERVER:gpload_amzn_reviews.yaml\n",
    "\n",
    "cmd = \"gpload -d {0} -f ./gpload_amzn_reviews.yaml -l ./gpload_amzn_reviews.log 2>&1\".format(DB_NAME) \n",
    "!ssh -i ~/.ssh/aws-gp.pem $DB_USER@$DB_SERVER $cmd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.3. Check gpload execution\n",
    "\n",
    "Check **gpload** execution output (shown above and also available on *./gpload_amzn_reviews.log*), confirm successful loading of the data and/or identify any message which require ones attention and/or actions:\n",
    "\n",
    "#### 3.3.1. Check the data has been properly loaded, by confirming row count shown above:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<!DOCTYPE html PUBLIC \"-//W3C//DTD HTML 4.01//EN\"\n",
       "   \"http://www.w3.org/TR/html4/strict.dtd\">\n",
       "<!--\n",
       "generated by Pygments <http://pygments.org>\n",
       "Copyright 2006-2019 by the Pygments team.\n",
       "Licensed under the BSD license, see LICENSE for details.\n",
       "-->\n",
       "<html>\n",
       "<head>\n",
       "  <title></title>\n",
       "  <meta http-equiv=\"content-type\" content=\"text/html; charset=UTF-8\">\n",
       "  <style type=\"text/css\">\n",
       "/*\n",
       "generated by Pygments <http://pygments.org>\n",
       "Copyright 2006-2019 by the Pygments team.\n",
       "Licensed under the BSD license, see LICENSE for details.\n",
       "*/\n",
       "td.linenos { background-color: #f0f0f0; padding-right: 10px; }\n",
       "span.lineno { background-color: #f0f0f0; padding: 0 5px 0 5px; }\n",
       "pre { line-height: 125%; }\n",
       "body .hll { background-color: #ffffcc }\n",
       "body  { background: #f0f0f0; }\n",
       "body .c { color: #60a0b0; font-style: italic } /* Comment */\n",
       "body .err { border: 1px solid #FF0000 } /* Error */\n",
       "body .k { color: #007020; font-weight: bold } /* Keyword */\n",
       "body .o { color: #666666 } /* Operator */\n",
       "body .ch { color: #60a0b0; font-style: italic } /* Comment.Hashbang */\n",
       "body .cm { color: #60a0b0; font-style: italic } /* Comment.Multiline */\n",
       "body .cp { color: #007020 } /* Comment.Preproc */\n",
       "body .cpf { color: #60a0b0; font-style: italic } /* Comment.PreprocFile */\n",
       "body .c1 { color: #60a0b0; font-style: italic } /* Comment.Single */\n",
       "body .cs { color: #60a0b0; background-color: #fff0f0 } /* Comment.Special */\n",
       "body .gd { color: #A00000 } /* Generic.Deleted */\n",
       "body .ge { font-style: italic } /* Generic.Emph */\n",
       "body .gr { color: #FF0000 } /* Generic.Error */\n",
       "body .gh { color: #000080; font-weight: bold } /* Generic.Heading */\n",
       "body .gi { color: #00A000 } /* Generic.Inserted */\n",
       "body .go { color: #888888 } /* Generic.Output */\n",
       "body .gp { color: #c65d09; font-weight: bold } /* Generic.Prompt */\n",
       "body .gs { font-weight: bold } /* Generic.Strong */\n",
       "body .gu { color: #800080; font-weight: bold } /* Generic.Subheading */\n",
       "body .gt { color: #0044DD } /* Generic.Traceback */\n",
       "body .kc { color: #007020; font-weight: bold } /* Keyword.Constant */\n",
       "body .kd { color: #007020; font-weight: bold } /* Keyword.Declaration */\n",
       "body .kn { color: #007020; font-weight: bold } /* Keyword.Namespace */\n",
       "body .kp { color: #007020 } /* Keyword.Pseudo */\n",
       "body .kr { color: #007020; font-weight: bold } /* Keyword.Reserved */\n",
       "body .kt { color: #902000 } /* Keyword.Type */\n",
       "body .m { color: #40a070 } /* Literal.Number */\n",
       "body .s { color: #4070a0 } /* Literal.String */\n",
       "body .na { color: #4070a0 } /* Name.Attribute */\n",
       "body .nb { color: #007020 } /* Name.Builtin */\n",
       "body .nc { color: #0e84b5; font-weight: bold } /* Name.Class */\n",
       "body .no { color: #60add5 } /* Name.Constant */\n",
       "body .nd { color: #555555; font-weight: bold } /* Name.Decorator */\n",
       "body .ni { color: #d55537; font-weight: bold } /* Name.Entity */\n",
       "body .ne { color: #007020 } /* Name.Exception */\n",
       "body .nf { color: #06287e } /* Name.Function */\n",
       "body .nl { color: #002070; font-weight: bold } /* Name.Label */\n",
       "body .nn { color: #0e84b5; font-weight: bold } /* Name.Namespace */\n",
       "body .nt { color: #062873; font-weight: bold } /* Name.Tag */\n",
       "body .nv { color: #bb60d5 } /* Name.Variable */\n",
       "body .ow { color: #007020; font-weight: bold } /* Operator.Word */\n",
       "body .w { color: #bbbbbb } /* Text.Whitespace */\n",
       "body .mb { color: #40a070 } /* Literal.Number.Bin */\n",
       "body .mf { color: #40a070 } /* Literal.Number.Float */\n",
       "body .mh { color: #40a070 } /* Literal.Number.Hex */\n",
       "body .mi { color: #40a070 } /* Literal.Number.Integer */\n",
       "body .mo { color: #40a070 } /* Literal.Number.Oct */\n",
       "body .sa { color: #4070a0 } /* Literal.String.Affix */\n",
       "body .sb { color: #4070a0 } /* Literal.String.Backtick */\n",
       "body .sc { color: #4070a0 } /* Literal.String.Char */\n",
       "body .dl { color: #4070a0 } /* Literal.String.Delimiter */\n",
       "body .sd { color: #4070a0; font-style: italic } /* Literal.String.Doc */\n",
       "body .s2 { color: #4070a0 } /* Literal.String.Double */\n",
       "body .se { color: #4070a0; font-weight: bold } /* Literal.String.Escape */\n",
       "body .sh { color: #4070a0 } /* Literal.String.Heredoc */\n",
       "body .si { color: #70a0d0; font-style: italic } /* Literal.String.Interpol */\n",
       "body .sx { color: #c65d09 } /* Literal.String.Other */\n",
       "body .sr { color: #235388 } /* Literal.String.Regex */\n",
       "body .s1 { color: #4070a0 } /* Literal.String.Single */\n",
       "body .ss { color: #517918 } /* Literal.String.Symbol */\n",
       "body .bp { color: #007020 } /* Name.Builtin.Pseudo */\n",
       "body .fm { color: #06287e } /* Name.Function.Magic */\n",
       "body .vc { color: #bb60d5 } /* Name.Variable.Class */\n",
       "body .vg { color: #bb60d5 } /* Name.Variable.Global */\n",
       "body .vi { color: #bb60d5 } /* Name.Variable.Instance */\n",
       "body .vm { color: #bb60d5 } /* Name.Variable.Magic */\n",
       "body .il { color: #40a070 } /* Literal.Number.Integer.Long */\n",
       "\n",
       "  </style>\n",
       "</head>\n",
       "<body>\n",
       "<h2></h2>\n",
       "\n",
       "<div class=\"highlight\"><pre><span></span><span class=\"k\">SELECT</span> <span class=\"n\">COUNT</span><span class=\"p\">(</span><span class=\"o\">*</span><span class=\"p\">)</span> <span class=\"k\">FROM</span> <span class=\"n\">demo</span><span class=\"mf\">.</span><span class=\"n\">amzn_reviews</span><span class=\"p\">;</span>\n",
       "</pre></div>\n",
       "</body>\n",
       "</html>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sqlfilecode = !pygmentize -f html -O full,style=friendly -l postgres script/3-3-count-amzn-reviews.sql\n",
    "display_html('\\n'.join(sqlfilecode), raw=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>150955707</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(150955707,)]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = !cat script/3-3-count-amzn-reviews.sql\n",
    "%sql $DB_USER@$DB_SERVER {''.join(query)}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7622</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(7622,)]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cmd = 'cat /home/gpadmin/gpload_amzn_reviews.log\\\n",
    "    | grep -e '\"'\"'WARN|select'\"'\"'\\\n",
    "    | awk '\"'\"'BEGIN{FS=\"|\";OFS=\" \"} {print $3}'\"'\"'\\\n",
    "    | awk '\"'\"'{print $1, \"COUNT(*)\", $3, $4, $5, $6, $7, $8}'\"'\"''\n",
    "query = !ssh -i ~/.ssh/aws-gp.pem $DB_USER@$DB_SERVER $cmd\n",
    "%sql $DB_USER@$DB_NAME {''.join(query)}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Continue to Part 2 of Greenplum Database Concepts Explained; [Basic Table Functions](AWS-GP-demo-2.ipynb)."
   ]
  }
 ],
 "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}