PXF & MADlib summary.ipynb

{
 "cells": [
  {
   "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": 3,
   "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": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql $CONNECTION_STRING\n",
    "%sql $DB_USER@$DB_NAME {\"SELECT version();\"}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "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": 80,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 80,
     "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": 119,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "Done.\n",
      "Done.\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>marketplace</th>\n",
       "        <th>customer_id</th>\n",
       "        <th>review_id</th>\n",
       "        <th>product_id</th>\n",
       "        <th>product_parent</th>\n",
       "        <th>product_title</th>\n",
       "        <th>product_category</th>\n",
       "        <th>star_rating</th>\n",
       "        <th>helpful_votes</th>\n",
       "        <th>total_votes</th>\n",
       "        <th>vine</th>\n",
       "        <th>verified_purchase</th>\n",
       "        <th>review_headline</th>\n",
       "        <th>review_body</th>\n",
       "        <th>review_date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>US</td>\n",
       "        <td>12039526</td>\n",
       "        <td>RTIS3L2M1F5SM</td>\n",
       "        <td>B001CXYMFS</td>\n",
       "        <td>737716809</td>\n",
       "        <td>Thrustmaster T-Flight Hotas X Flight Stick</td>\n",
       "        <td>Video Games</td>\n",
       "        <td>5</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>N</td>\n",
       "        <td>Y</td>\n",
       "        <td>an amazing joystick. I especially love that you can twist ...</td>\n",
       "        <td>Used this for Elite Dangerous on my mac, an amazing joystick. I especially love that you can twist the stick for different movement bindings as well as move it in the normal way.</td>\n",
       "        <td>2015-08-31</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('US', 12039526, 'RTIS3L2M1F5SM', 'B001CXYMFS', 737716809, 'Thrustmaster T-Flight Hotas X Flight Stick', 'Video Games', 5, 0, 0, 'N', 'Y', 'an amazing joystick. I especially love that you can twist ...', 'Used this for Elite Dangerous on my mac, an amazing joystick. I especially love that you can twist the stick for different movement bindings as well as move it in the normal way.', datetime.date(2015, 8, 31))]"
      ]
     },
     "execution_count": 119,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "DROP EXTERNAL TABLE IF EXISTS demo.amzn_reviews_ext;\n",
    "CREATE EXTERNAL TABLE demo.amzn_reviews_ext(\n",
    "    marketplace TEXT, \n",
    "    customer_id BIGINT, \n",
    "    review_id TEXT, \n",
    "    product_id TEXT, \n",
    "    product_parent BIGINT, \n",
    "    product_title TEXT, \n",
    "    product_category TEXT, \n",
    "    star_rating INTEGER, \n",
    "    helpful_votes INTEGER, \n",
    "    total_votes INTEGER, \n",
    "    vine TEXT, \n",
    "    verified_purchase TEXT, \n",
    "    review_headline TEXT, \n",
    "    review_body TEXT, \n",
    "    review_date DATE) \n",
    "LOCATION ('pxf://amazon-reviews-pds/tsv/?PROFILE=s3:text&SERVER=s3srvcfg')\n",
    "FORMAT 'TEXT' (HEADER ) SEGMENT REJECT LIMIT 1000 ROWS;\n",
    "SELECT * FROM demo.amzn_reviews_ext LIMIT 1;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "DROP EXTERNAL TABLE IF EXISTS demo.amzn_reviews_ext_parquet;\n",
    "CREATE EXTERNAL TABLE demo.amzn_reviews_ext_parquet (\n",
    "    marketplace TEXT, \n",
    "    customer_id BIGINT, \n",
    "    review_id TEXT, \n",
    "    product_id TEXT, \n",
    "    product_parent BIGINT, \n",
    "    product_title TEXT, \n",
    "    star_rating INTEGER, \n",
    "    helpful_votes INTEGER, \n",
    "    total_votes INTEGER, \n",
    "    vine TEXT, \n",
    "    verified_purchase TEXT, \n",
    "    review_headline TEXT, \n",
    "    review_body TEXT, \n",
    "    review_date DATE) \n",
    "LOCATION ('pxf://amazon-reviews-pds/parquet/*?PROFILE=s3:parquet&SERVER=s3srvcfg')\n",
    "FORMAT 'TEXT' ( HEADER )\n",
    "LOG ERRORS SEGMENT REJECT LIMIT 1000;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>160789831</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(160789831,)]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT COUNT(*) FROM demo.amzn_reviews_ext_parquet;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>marketplace</th>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>US</td>\n",
       "        <td>157886668</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>JP</td>\n",
       "        <td>262429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>UK</td>\n",
       "        <td>1707471</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>FR</td>\n",
       "        <td>254116</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>DE</td>\n",
       "        <td>679106</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('US', 157886668),\n",
       " ('JP', 262429),\n",
       " ('UK', 1707471),\n",
       " ('FR', 254116),\n",
       " ('DE', 679106)]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT marketplace, COUNT(*) FROM demo.amzn_reviews_ext GROUP BY marketplace;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "15 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>target_column</th>\n",
       "        <th>data_type</th>\n",
       "        <th>row_count</th>\n",
       "        <th>distinct_values</th>\n",
       "        <th>missing_values</th>\n",
       "        <th>blank_values</th>\n",
       "        <th>positive_values</th>\n",
       "        <th>negative_values</th>\n",
       "        <th>zero_values</th>\n",
       "        <th>variance</th>\n",
       "        <th>?column?</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>marketplace</td>\n",
       "        <td>text</td>\n",
       "        <td>3105464</td>\n",
       "        <td>1</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>3105464.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>customer_id</td>\n",
       "        <td>int8</td>\n",
       "        <td>3105464</td>\n",
       "        <td>1594733</td>\n",
       "        <td>0</td>\n",
       "        <td>None</td>\n",
       "        <td>3105464</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>104367252300574.0</td>\n",
       "        <td>1.94732535164194</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>review_id</td>\n",
       "        <td>text</td>\n",
       "        <td>3105464</td>\n",
       "        <td>3105464</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>product_id</td>\n",
       "        <td>text</td>\n",
       "        <td>3105464</td>\n",
       "        <td>832669</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>3.72952998130109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>product_parent</td>\n",
       "        <td>int8</td>\n",
       "        <td>3105464</td>\n",
       "        <td>685185</td>\n",
       "        <td>0</td>\n",
       "        <td>None</td>\n",
       "        <td>3105464</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>8.28531638988266e+16</td>\n",
       "        <td>4.53230003575677</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>product_title</td>\n",
       "        <td>text</td>\n",
       "        <td>3105464</td>\n",
       "        <td>702087</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>4.4231897186531</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>product_category</td>\n",
       "        <td>text</td>\n",
       "        <td>3105464</td>\n",
       "        <td>1</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>3105464.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>star_rating</td>\n",
       "        <td>int4</td>\n",
       "        <td>3105464</td>\n",
       "        <td>5</td>\n",
       "        <td>0</td>\n",
       "        <td>None</td>\n",
       "        <td>3105464</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>1.55223801357914</td>\n",
       "        <td>621092.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>helpful_votes</td>\n",
       "        <td>int4</td>\n",
       "        <td>3105464</td>\n",
       "        <td>942</td>\n",
       "        <td>0</td>\n",
       "        <td>None</td>\n",
       "        <td>2645552</td>\n",
       "        <td>0</td>\n",
       "        <td>459912</td>\n",
       "        <td>687.696303106136</td>\n",
       "        <td>3296.67091295117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>total_votes</td>\n",
       "        <td>int4</td>\n",
       "        <td>3105464</td>\n",
       "        <td>1024</td>\n",
       "        <td>0</td>\n",
       "        <td>None</td>\n",
       "        <td>2789289</td>\n",
       "        <td>0</td>\n",
       "        <td>316175</td>\n",
       "        <td>914.722979030818</td>\n",
       "        <td>3032.6796875</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>vine</td>\n",
       "        <td>text</td>\n",
       "        <td>3105464</td>\n",
       "        <td>2</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>1552732.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>verified_purchase</td>\n",
       "        <td>text</td>\n",
       "        <td>3105464</td>\n",
       "        <td>2</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>1552732.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>review_headline</td>\n",
       "        <td>text</td>\n",
       "        <td>3105464</td>\n",
       "        <td>2439518</td>\n",
       "        <td>0</td>\n",
       "        <td>1764</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>1.27298261377862</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>review_body</td>\n",
       "        <td>text</td>\n",
       "        <td>3105464</td>\n",
       "        <td>3021343</td>\n",
       "        <td>0</td>\n",
       "        <td>17</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>1.02784225425581</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>review_date</td>\n",
       "        <td>date</td>\n",
       "        <td>3105464</td>\n",
       "        <td>3575</td>\n",
       "        <td>0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>868.661258741259</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('marketplace', 'text', 3105464, 1, 0, 0, None, None, None, None, 3105464.0),\n",
       " ('customer_id', 'int8', 3105464, 1594733, 0, None, 3105464, 0, 0, 104367252300574.0, 1.94732535164194),\n",
       " ('review_id', 'text', 3105464, 3105464, 0, 0, None, None, None, None, 1.0),\n",
       " ('product_id', 'text', 3105464, 832669, 0, 0, None, None, None, None, 3.72952998130109),\n",
       " ('product_parent', 'int8', 3105464, 685185, 0, None, 3105464, 0, 0, 8.28531638988266e+16, 4.53230003575677),\n",
       " ('product_title', 'text', 3105464, 702087, 0, 0, None, None, None, None, 4.4231897186531),\n",
       " ('product_category', 'text', 3105464, 1, 0, 0, None, None, None, None, 3105464.0),\n",
       " ('star_rating', 'int4', 3105464, 5, 0, None, 3105464, 0, 0, 1.55223801357914, 621092.8),\n",
       " ('helpful_votes', 'int4', 3105464, 942, 0, None, 2645552, 0, 459912, 687.696303106136, 3296.67091295117),\n",
       " ('total_votes', 'int4', 3105464, 1024, 0, None, 2789289, 0, 316175, 914.722979030818, 3032.6796875),\n",
       " ('vine', 'text', 3105464, 2, 0, 0, None, None, None, None, 1552732.0),\n",
       " ('verified_purchase', 'text', 3105464, 2, 0, 0, None, None, None, None, 1552732.0),\n",
       " ('review_headline', 'text', 3105464, 2439518, 0, 1764, None, None, None, None, 1.27298261377862),\n",
       " ('review_body', 'text', 3105464, 3021343, 0, 17, None, None, None, None, 1.02784225425581),\n",
       " ('review_date', 'date', 3105464, 3575, 0, None, None, None, None, None, 868.661258741259)]"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "--DROP TABLE IF EXISTS demo.amzn_reviews_summary; \n",
    "--SELECT * FROM madlib.summary('demo.amzn_reviews_ext', 'demo.amzn_reviews_summary', \n",
    "--                            NULL, NULL, \n",
    "--                            TRUE, FALSE);\n",
    "\n",
    "SELECT \n",
    "    target_column\n",
    "    , data_type\n",
    "    , row_count\n",
    "    , distinct_values\n",
    "    , missing_values\n",
    "    , blank_values\n",
    "    , positive_values\n",
    "    , negative_values\n",
    "    , zero_values\n",
    "    , (row_count / distinct_values::float8)\n",
    "    , (distinct_values::float8 / row_count)\n",
    "FROM demo.amzn_reviews_summary;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "Done.\n",
      "160789790 rows affected.\n",
      "Done.\n",
      "160789790 rows affected.\n",
      "Done.\n",
      "160789790 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 147,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "/*DROP TABLE IF EXISTS demo.amzn_reviews_1_1;\n",
    "CREATE TABLE demo.amzn_reviews_1_1 AS\n",
    "SELECT marketplace\n",
    "    , customer_id\n",
    "    , review_id\n",
    "    , product_id\n",
    "    , product_parent\n",
    "    , product_title\n",
    "    , product_category\n",
    "    , star_rating\n",
    "    , helpful_votes\n",
    "    , total_votes\n",
    "    , vine\n",
    "    , verified_purchase\n",
    "    , review_headline\n",
    "    , review_body\n",
    "    , review_date\n",
    "FROM demo.amzn_reviews_ext\n",
    "DISTRIBUTED RANDOMLY;\n",
    "*/\n",
    "DROP TABLE IF EXISTS demo.amzn_reviews_1_2;\n",
    "CREATE TABLE demo.amzn_reviews_1_2 \n",
    "WITH (appendoptimized=true) AS\n",
    "SELECT marketplace\n",
    "    , customer_id\n",
    "    , review_id\n",
    "    , product_id\n",
    "    , product_parent\n",
    "    , product_title\n",
    "    , product_category\n",
    "    , star_rating\n",
    "    , helpful_votes\n",
    "    , total_votes\n",
    "    , vine\n",
    "    , verified_purchase\n",
    "    , review_headline\n",
    "    , review_body\n",
    "    , review_date\n",
    "FROM demo.amzn_reviews_1_1\n",
    "DISTRIBUTED RANDOMLY;\n",
    "\n",
    "DROP TABLE IF EXISTS demo.amzn_reviews_2_1;\n",
    "CREATE TABLE demo.amzn_reviews_2_1 AS\n",
    "SELECT marketplace\n",
    "    , customer_id\n",
    "    , review_id\n",
    "    , product_id\n",
    "    , product_parent\n",
    "    , product_title\n",
    "    , product_category\n",
    "    , star_rating\n",
    "    , helpful_votes\n",
    "    , total_votes\n",
    "    , vine\n",
    "    , verified_purchase\n",
    "    , review_headline\n",
    "    , review_body\n",
    "    , review_date\n",
    "FROM demo.amzn_reviews_1_1\n",
    "DISTRIBUTED BY (review_id);\n",
    "\n",
    "\n",
    "DROP TABLE IF EXISTS demo.amzn_reviews_3_1;\n",
    "CREATE TABLE demo.amzn_reviews_3_1 AS\n",
    "SELECT marketplace\n",
    "    , customer_id\n",
    "    , review_id\n",
    "    , product_id\n",
    "    , product_parent\n",
    "    , product_title\n",
    "    , product_category\n",
    "    , star_rating\n",
    "    , helpful_votes\n",
    "    , total_votes\n",
    "    , vine\n",
    "    , verified_purchase\n",
    "    , review_headline\n",
    "    , review_body\n",
    "    , review_date\n",
    "FROM demo.amzn_reviews_1_1\n",
    "DISTRIBUTED BY (review_id, customer_id);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 148,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "ANALYSE demo.amzn_reviews_1_1;\n",
    "ANALYSE demo.amzn_reviews_1_2;\n",
    "ANALYSE demo.amzn_reviews_2_1;\n",
    "ANALYSE demo.amzn_reviews_3_1;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "20 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>QUERY PLAN</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Gather Motion 72:1  (slice2; segments: 72)  (cost=0.00..1878.79 rows=6620966 width=20) (actual time=6421.115..14805.374 rows=55833629 loops=1)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;-&gt;  HashAggregate  (cost=0.00..1579.04 rows=91958 width=20) (actual time=6437.166..6598.272 rows=777429 loops=1)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Group Key: customer_id, star_rating</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Extra Text: (seg42)  Hash chain length 3.1 avg, 14 max, using 248682 of 262144 buckets; total 13 expansions.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-&gt;  Redistribute Motion 72:72  (slice1; segments: 72)  (cost=0.00..1555.53 rows=91958 width=20) (actual time=1840.205..4200.650 rows=2014893 loops=1)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Hash Key: customer_id, star_rating</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-&gt;  Result  (cost=0.00..1549.77 rows=91958 width=20) (actual time=2068.010..3541.064 rows=2008238 loops=1)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-&gt;  HashAggregate  (cost=0.00..1549.77 rows=91958 width=20) (actual time=2068.007..3085.564 rows=2008238 loops=1)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Group Key: customer_id, star_rating</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Extra Text: (seg35)  Hash chain length 3.9 avg, 16 max, using 513006 of 524288 buckets; total 14 expansions.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-&gt;  Seq Scan on amzn_reviews_1  (cost=0.00..940.14 rows=2236023 width=12) (actual time=0.060..720.216 rows=2237678 loops=1)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Planning time: 3.708 ms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;(slice0)    Executor memory: 151K bytes.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;(slice1)    Executor memory: 133756K bytes avg x 72 workers, 134000K bytes max (seg35).</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;(slice2)    Executor memory: 52789K bytes avg x 72 workers, 52931K bytes max (seg42).</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Memory used:  1888256kB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Optimizer: Pivotal Optimizer (GPORCA)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Execution time: 17832.155 ms</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Gather Motion 72:1  (slice2; segments: 72)  (cost=0.00..1878.79 rows=6620966 width=20) (actual time=6421.115..14805.374 rows=55833629 loops=1)',),\n",
       " ('  ->  HashAggregate  (cost=0.00..1579.04 rows=91958 width=20) (actual time=6437.166..6598.272 rows=777429 loops=1)',),\n",
       " ('        Group Key: customer_id, star_rating',),\n",
       " ('        Extra Text: (seg42)  Hash chain length 3.1 avg, 14 max, using 248682 of 262144 buckets; total 13 expansions.',),\n",
       " ('',),\n",
       " ('        ->  Redistribute Motion 72:72  (slice1; segments: 72)  (cost=0.00..1555.53 rows=91958 width=20) (actual time=1840.205..4200.650 rows=2014893 loops=1)',),\n",
       " ('              Hash Key: customer_id, star_rating',),\n",
       " ('              ->  Result  (cost=0.00..1549.77 rows=91958 width=20) (actual time=2068.010..3541.064 rows=2008238 loops=1)',),\n",
       " ('                    ->  HashAggregate  (cost=0.00..1549.77 rows=91958 width=20) (actual time=2068.007..3085.564 rows=2008238 loops=1)',),\n",
       " ('                          Group Key: customer_id, star_rating',),\n",
       " ('                          Extra Text: (seg35)  Hash chain length 3.9 avg, 16 max, using 513006 of 524288 buckets; total 14 expansions.',),\n",
       " ('',),\n",
       " ('                          ->  Seq Scan on amzn_reviews_1  (cost=0.00..940.14 rows=2236023 width=12) (actual time=0.060..720.216 rows=2237678 loops=1)',),\n",
       " ('Planning time: 3.708 ms',),\n",
       " ('  (slice0)    Executor memory: 151K bytes.',),\n",
       " ('  (slice1)    Executor memory: 133756K bytes avg x 72 workers, 134000K bytes max (seg35).',),\n",
       " ('  (slice2)    Executor memory: 52789K bytes avg x 72 workers, 52931K bytes max (seg42).',),\n",
       " ('Memory used:  1888256kB',),\n",
       " ('Optimizer: Pivotal Optimizer (GPORCA)',),\n",
       " ('Execution time: 17832.155 ms',)]"
      ]
     },
     "execution_count": 140,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "EXPLAIN ANALYZE SELECT COUNT(*), customer_id, star_rating FROM demo.amzn_reviews_1 GROUP BY customer_id, star_rating;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "20 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>QUERY PLAN</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Gather Motion 72:1  (slice2; segments: 72)  (cost=0.00..1931.92 rows=7682077 width=20) (actual time=6313.370..14761.991 rows=55833629 loops=1)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;-&gt;  HashAggregate  (cost=0.00..1584.14 rows=106696 width=20) (actual time=6328.560..6487.077 rows=777429 loops=1)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Group Key: customer_id, star_rating</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Extra Text: (seg42)  Hash chain length 3.1 avg, 14 max, using 248682 of 262144 buckets; total 13 expansions.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-&gt;  Redistribute Motion 72:72  (slice1; segments: 72)  (cost=0.00..1556.86 rows=106696 width=20) (actual time=1803.491..4085.755 rows=1934811 loops=1)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Hash Key: customer_id, star_rating</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-&gt;  Result  (cost=0.00..1550.18 rows=106696 width=20) (actual time=2696.306..4022.566 rows=1928417 loops=1)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-&gt;  HashAggregate  (cost=0.00..1550.18 rows=106696 width=20) (actual time=2696.305..3613.222 rows=1928417 loops=1)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Group Key: customer_id, star_rating</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Extra Text: (seg1)   Hash chain length 3.8 avg, 15 max, using 511010 of 524288 buckets; total 14 expansions.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-&gt;  Seq Scan on amzn_reviews_2  (cost=0.00..940.96 rows=2234206 width=12) (actual time=0.043..1033.252 rows=2238167 loops=1)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Planning time: 4.152 ms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;(slice0)    Executor memory: 151K bytes.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;(slice1)    Executor memory: 128780K bytes avg x 72 workers, 129004K bytes max (seg1).</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;(slice2)    Executor memory: 52789K bytes avg x 72 workers, 52931K bytes max (seg42).</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Memory used:  1888256kB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Optimizer: Pivotal Optimizer (GPORCA)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Execution time: 17794.773 ms</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Gather Motion 72:1  (slice2; segments: 72)  (cost=0.00..1931.92 rows=7682077 width=20) (actual time=6313.370..14761.991 rows=55833629 loops=1)',),\n",
       " ('  ->  HashAggregate  (cost=0.00..1584.14 rows=106696 width=20) (actual time=6328.560..6487.077 rows=777429 loops=1)',),\n",
       " ('        Group Key: customer_id, star_rating',),\n",
       " ('        Extra Text: (seg42)  Hash chain length 3.1 avg, 14 max, using 248682 of 262144 buckets; total 13 expansions.',),\n",
       " ('',),\n",
       " ('        ->  Redistribute Motion 72:72  (slice1; segments: 72)  (cost=0.00..1556.86 rows=106696 width=20) (actual time=1803.491..4085.755 rows=1934811 loops=1)',),\n",
       " ('              Hash Key: customer_id, star_rating',),\n",
       " ('              ->  Result  (cost=0.00..1550.18 rows=106696 width=20) (actual time=2696.306..4022.566 rows=1928417 loops=1)',),\n",
       " ('                    ->  HashAggregate  (cost=0.00..1550.18 rows=106696 width=20) (actual time=2696.305..3613.222 rows=1928417 loops=1)',),\n",
       " ('                          Group Key: customer_id, star_rating',),\n",
       " ('                          Extra Text: (seg1)   Hash chain length 3.8 avg, 15 max, using 511010 of 524288 buckets; total 14 expansions.',),\n",
       " ('',),\n",
       " ('                          ->  Seq Scan on amzn_reviews_2  (cost=0.00..940.96 rows=2234206 width=12) (actual time=0.043..1033.252 rows=2238167 loops=1)',),\n",
       " ('Planning time: 4.152 ms',),\n",
       " ('  (slice0)    Executor memory: 151K bytes.',),\n",
       " ('  (slice1)    Executor memory: 128780K bytes avg x 72 workers, 129004K bytes max (seg1).',),\n",
       " ('  (slice2)    Executor memory: 52789K bytes avg x 72 workers, 52931K bytes max (seg42).',),\n",
       " ('Memory used:  1888256kB',),\n",
       " ('Optimizer: Pivotal Optimizer (GPORCA)',),\n",
       " ('Execution time: 17794.773 ms',)]"
      ]
     },
     "execution_count": 141,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "EXPLAIN ANALYZE SELECT COUNT(*), customer_id, star_rating FROM demo.amzn_reviews_2 GROUP BY customer_id, star_rating;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "20 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>QUERY PLAN</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Gather Motion 72:1  (slice2; segments: 72)  (cost=0.00..1888.84 rows=6821064 width=20) (actual time=6098.043..14528.420 rows=55833629 loops=1)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;-&gt;  HashAggregate  (cost=0.00..1580.04 rows=94737 width=20) (actual time=6114.202..6285.618 rows=777429 loops=1)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Group Key: customer_id, star_rating</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Extra Text: (seg42)  Hash chain length 3.1 avg, 14 max, using 248682 of 262144 buckets; total 13 expansions.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-&gt;  Redistribute Motion 72:72  (slice1; segments: 72)  (cost=0.00..1555.81 rows=94737 width=20) (actual time=1781.057..3823.407 rows=1934708 loops=1)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Hash Key: customer_id, star_rating</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-&gt;  Result  (cost=0.00..1549.88 rows=94737 width=20) (actual time=1973.009..3626.598 rows=1928756 loops=1)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-&gt;  HashAggregate  (cost=0.00..1549.88 rows=94737 width=20) (actual time=1973.007..3119.252 rows=1928756 loops=1)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Group Key: customer_id, star_rating</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Extra Text: (seg55)  Hash chain length 3.8 avg, 16 max, using 511281 of 524288 buckets; total 14 expansions.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-&gt;  Seq Scan on amzn_reviews_3  (cost=0.00..940.85 rows=2233756 width=12) (actual time=0.045..641.034 rows=2236931 loops=1)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Planning time: 4.104 ms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;(slice0)    Executor memory: 151K bytes.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;(slice1)    Executor memory: 128778K bytes avg x 72 workers, 129004K bytes max (seg55).</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;(slice2)    Executor memory: 52789K bytes avg x 72 workers, 52931K bytes max (seg42).</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Memory used:  1888256kB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Optimizer: Pivotal Optimizer (GPORCA)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Execution time: 17507.684 ms</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Gather Motion 72:1  (slice2; segments: 72)  (cost=0.00..1888.84 rows=6821064 width=20) (actual time=6098.043..14528.420 rows=55833629 loops=1)',),\n",
       " ('  ->  HashAggregate  (cost=0.00..1580.04 rows=94737 width=20) (actual time=6114.202..6285.618 rows=777429 loops=1)',),\n",
       " ('        Group Key: customer_id, star_rating',),\n",
       " ('        Extra Text: (seg42)  Hash chain length 3.1 avg, 14 max, using 248682 of 262144 buckets; total 13 expansions.',),\n",
       " ('',),\n",
       " ('        ->  Redistribute Motion 72:72  (slice1; segments: 72)  (cost=0.00..1555.81 rows=94737 width=20) (actual time=1781.057..3823.407 rows=1934708 loops=1)',),\n",
       " ('              Hash Key: customer_id, star_rating',),\n",
       " ('              ->  Result  (cost=0.00..1549.88 rows=94737 width=20) (actual time=1973.009..3626.598 rows=1928756 loops=1)',),\n",
       " ('                    ->  HashAggregate  (cost=0.00..1549.88 rows=94737 width=20) (actual time=1973.007..3119.252 rows=1928756 loops=1)',),\n",
       " ('                          Group Key: customer_id, star_rating',),\n",
       " ('                          Extra Text: (seg55)  Hash chain length 3.8 avg, 16 max, using 511281 of 524288 buckets; total 14 expansions.',),\n",
       " ('',),\n",
       " ('                          ->  Seq Scan on amzn_reviews_3  (cost=0.00..940.85 rows=2233756 width=12) (actual time=0.045..641.034 rows=2236931 loops=1)',),\n",
       " ('Planning time: 4.104 ms',),\n",
       " ('  (slice0)    Executor memory: 151K bytes.',),\n",
       " ('  (slice1)    Executor memory: 128778K bytes avg x 72 workers, 129004K bytes max (seg55).',),\n",
       " ('  (slice2)    Executor memory: 52789K bytes avg x 72 workers, 52931K bytes max (seg42).',),\n",
       " ('Memory used:  1888256kB',),\n",
       " ('Optimizer: Pivotal Optimizer (GPORCA)',),\n",
       " ('Execution time: 17507.684 ms',)]"
      ]
     },
     "execution_count": 142,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "EXPLAIN ANALYZE SELECT COUNT(*), customer_id, star_rating FROM demo.amzn_reviews_3 GROUP BY customer_id, star_rating;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "Done.\n",
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "100 rows affected.\n",
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "Done.\n",
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>output_table</th>\n",
       "        <th>num_col_summarized</th>\n",
       "        <th>duration</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>test100_summary</td>\n",
       "        <td>2</td>\n",
       "        <td>0.147065877914429</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('test100_summary', 2, 0.147065877914429)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql DROP TABLE IF EXISTS test100; \n",
    "%sql CREATE TABLE test100 (a, b) AS SELECT generate_series(1, 100) * random() * 10 AS a, generate_series(1, 100) * random() * 10 AS b DISTRIBUTED BY (a);\n",
    "#%sql SELECT * FROM test100;\n",
    "%sql DROP TABLE IF EXISTS test100_summary; \n",
    "%sql SELECT * FROM madlib.summary('test100', 'test100_summary');\n",
    "#%sql SELECT * FROM test100_summary;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "Done.\n",
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "1000 rows affected.\n",
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "1000 rows affected.\n",
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "Done.\n",
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>output_table</th>\n",
       "        <th>num_col_summarized</th>\n",
       "        <th>duration</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>test1000_summary</td>\n",
       "        <td>2</td>\n",
       "        <td>0.110813140869141</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('test1000_summary', 2, 0.110813140869141)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql DROP TABLE IF EXISTS test1000; \n",
    "%sql CREATE TABLE test1000 (a, b) AS SELECT generate_series(1, 1000) * random() * 10 AS a, generate_series(1, 1000) * random() * 10 AS b DISTRIBUTED BY (a);\n",
    "%sql SELECT * FROM test1000;\n",
    "%sql DROP TABLE IF EXISTS test1000_summary; \n",
    "%sql SELECT * FROM madlib.summary('test1000', 'test1000_summary');\n",
    "#%sql SELECT * FROM test100_summary;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "Done.\n",
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "10000 rows affected.\n",
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "Done.\n",
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "Done.\n",
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>output_table</th>\n",
       "        <th>num_col_summarized</th>\n",
       "        <th>duration</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>test10K_summary</td>\n",
       "        <td>2</td>\n",
       "        <td>0.593071937561035</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('test10K_summary', 2, 0.593071937561035)]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql DROP TABLE IF EXISTS test10K; \n",
    "%sql CREATE TABLE test10K (a, b) AS SELECT generate_series(1, 10000) * random() * 10 AS a, generate_series(1, 10000) * random() * 10 AS b DISTRIBUTED BY (a);\n",
    "#%sql SELECT * FROM test10K;\n",
    "%sql ANALYZE test10K;\n",
    "%sql DROP TABLE IF EXISTS test10K_summary; \n",
    "%sql SELECT * FROM madlib.summary('test10K', 'test10K_summary');\n",
    "#%sql SELECT * FROM test10K_summary;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "Done.\n",
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "100000 rows affected.\n",
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "Done.\n",
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "Done.\n",
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "1 rows affected.\n",
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>group_by</th>\n",
       "        <th>group_by_value</th>\n",
       "        <th>target_column</th>\n",
       "        <th>column_number</th>\n",
       "        <th>data_type</th>\n",
       "        <th>row_count</th>\n",
       "        <th>distinct_values</th>\n",
       "        <th>missing_values</th>\n",
       "        <th>blank_values</th>\n",
       "        <th>fraction_missing</th>\n",
       "        <th>fraction_blank</th>\n",
       "        <th>positive_values</th>\n",
       "        <th>negative_values</th>\n",
       "        <th>zero_values</th>\n",
       "        <th>mean</th>\n",
       "        <th>variance</th>\n",
       "        <th>confidence_interval</th>\n",
       "        <th>min</th>\n",
       "        <th>max</th>\n",
       "        <th>first_quartile</th>\n",
       "        <th>median</th>\n",
       "        <th>third_quartile</th>\n",
       "        <th>most_frequent_values</th>\n",
       "        <th>mfv_frequencies</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>a</td>\n",
       "        <td>1</td>\n",
       "        <td>float8</td>\n",
       "        <td>100000</td>\n",
       "        <td>99671</td>\n",
       "        <td>0</td>\n",
       "        <td>None</td>\n",
       "        <td>0.0</td>\n",
       "        <td>None</td>\n",
       "        <td>100000</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>20467.9307253425</td>\n",
       "        <td>139643999.619154</td>\n",
       "        <td>[20394.6875421145, 20541.1739085705]</td>\n",
       "        <td>0.409354520961642</td>\n",
       "        <td>40935.4520961642</td>\n",
       "        <td>10234.1700399318</td>\n",
       "        <td>20467.9307253426</td>\n",
       "        <td>30701.6914107534</td>\n",
       "        <td>[&#x27;39463.0038842652&#x27;, &#x27;37961.0821468569&#x27;, &#x27;37588.5695327818&#x27;, &#x27;36711.732148882&#x27;, &#x27;38043.7717600912&#x27;, &#x27;40920.3059788886&#x27;, &#x27;40186.3333228044&#x27;, &#x27;38939.8488064762&#x27;, &#x27;33394.7324655298&#x27;, &#x27;37574.2421245482&#x27;]</td>\n",
       "        <td>[100, 100, 99, 99, 99, 99, 98, 96, 96, 96]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>b</td>\n",
       "        <td>2</td>\n",
       "        <td>float8</td>\n",
       "        <td>100000</td>\n",
       "        <td>93400</td>\n",
       "        <td>0</td>\n",
       "        <td>None</td>\n",
       "        <td>0.0</td>\n",
       "        <td>None</td>\n",
       "        <td>100000</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>390285.850149114</td>\n",
       "        <td>50773840537.1345</td>\n",
       "        <td>[388889.237154017, 391682.463144211]</td>\n",
       "        <td>7.80563894659281</td>\n",
       "        <td>780563.894659281</td>\n",
       "        <td>195146.82789403</td>\n",
       "        <td>390285.850149114</td>\n",
       "        <td>585424.872404197</td>\n",
       "        <td>[&#x27;693835.440323688&#x27;, &#x27;703654.934118502&#x27;, &#x27;776778.159770183&#x27;, &#x27;670957.112571225&#x27;, &#x27;646611.324696802&#x27;, &#x27;772305.528653786&#x27;, &#x27;730381.441871636&#x27;, &#x27;740497.54994642&#x27;, &#x27;631109.325748868&#x27;, &#x27;775841.483096592&#x27;]</td>\n",
       "        <td>[100, 100, 99, 99, 98, 98, 97, 97, 97, 97]</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(None, None, 'a', 1, 'float8', 100000, 99671, 0, None, 0.0, None, 100000, 0, 0, 20467.9307253425, 139643999.619154, [20394.6875421145, 20541.1739085705], 0.409354520961642, 40935.4520961642, 10234.1700399318, 20467.9307253426, 30701.6914107534, ['39463.0038842652', '37961.0821468569', '37588.5695327818', '36711.732148882', '38043.7717600912', '40920.3059788886', '40186.3333228044', '38939.8488064762', '33394.7324655298', '37574.2421245482'], [100, 100, 99, 99, 99, 99, 98, 96, 96, 96]),\n",
       " (None, None, 'b', 2, 'float8', 100000, 93400, 0, None, 0.0, None, 100000, 0, 0, 390285.850149114, 50773840537.1345, [388889.237154017, 391682.463144211], 7.80563894659281, 780563.894659281, 195146.82789403, 390285.850149114, 585424.872404197, ['693835.440323688', '703654.934118502', '776778.159770183', '670957.112571225', '646611.324696802', '772305.528653786', '730381.441871636', '740497.54994642', '631109.325748868', '775841.483096592'], [100, 100, 99, 99, 98, 98, 97, 97, 97, 97])]"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql DROP TABLE IF EXISTS test100K; \n",
    "%sql CREATE TABLE test100K (a, b) AS SELECT generate_series(1, 100000) * random() * 10 AS a, generate_series(1, 100000) * random() * 10 AS b DISTRIBUTED BY (a);\n",
    "#%sql SELECT * FROM test100K;\n",
    "%sql ANALYZE test100K;\n",
    "%sql DROP TABLE IF EXISTS test100K_summary; \n",
    "%sql SELECT * FROM madlib.summary('test100K', 'test100K_summary');\n",
    "%sql SELECT * FROM test100K_summary;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>QUERY PLAN</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Result  (cost=0.00..431.00 rows=1 width=136)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;-&gt;  Aggregate  (cost=0.00..431.00 rows=1 width=184)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-&gt;  Gather Motion 72:1  (slice1; segments: 72)  (cost=0.00..431.00 rows=1 width=16)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-&gt;  Seq Scan on test100m  (cost=0.00..431.00 rows=1 width=16)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Optimizer: Pivotal Optimizer (GPORCA)</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Result  (cost=0.00..431.00 rows=1 width=136)',),\n",
       " ('  ->  Aggregate  (cost=0.00..431.00 rows=1 width=184)',),\n",
       " ('        ->  Gather Motion 72:1  (slice1; segments: 72)  (cost=0.00..431.00 rows=1 width=16)',),\n",
       " ('              ->  Seq Scan on test100m  (cost=0.00..431.00 rows=1 width=16)',),\n",
       " ('Optimizer: Pivotal Optimizer (GPORCA)',)]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "EXPLAIN SELECT \n",
    "\tarray['a','b']::text[] as target_column, \n",
    "\tarray['float8','float8']::text[] as datatype, \n",
    "\tarray[1,2]::integer[] as colnum, \n",
    "\tcount(*)::bigint as rowcount, \n",
    "\tarray[sum(case when a > 0 then 1 else 0 end),sum(case when b > 0 then 1 else 0 end)]::bigint[] as positive_values, \n",
    "\tarray[sum(case when a < 0 then 1 else 0 end),sum(case when b < 0 then 1 else 0 end)]::bigint[] as negative_values, \n",
    "\tarray[sum(case when a = 0 then 1 else 0 end),sum(case when b = 0 then 1 else 0 end)]::bigint[] as zero_values, \n",
    "\tarray[avg(a),avg(b)]::float8[] as mean, \n",
    "\tarray[variance(a),variance(b)]::float8[] as variance, \n",
    "\tarray[madlib.fmsketch_dcount(a),madlib.fmsketch_dcount(b)]::bigint[] as distinct_values, \n",
    "\tarray[sum(case when a is null then 1 else 0 end),sum(case when b is null then 1 else 0 end)]::bigint[] as missing_values, \n",
    "\tarray[NULL,NULL]::bigint[] as blank_values, \n",
    "\tarray[min(a),min(b)]::float8[] as min, \n",
    "\tarray[max(a),max(b)]::float8[] as max, \n",
    "--\tarray[percentile_cont(0.25) WITHIN GROUP (ORDER BY a),percentile_cont(0.25) WITHIN GROUP (ORDER BY b)]::float8[] as first_quartile, \n",
    "--\tarray[percentile_cont(0.5) WITHIN GROUP (ORDER BY a),percentile_cont(0.5) WITHIN GROUP (ORDER BY b)]::float8[] as median, \n",
    "--\tarray[percentile_cont(0.75) WITHIN GROUP (ORDER BY a),percentile_cont(0.75) WITHIN GROUP (ORDER BY b)]::float8[] as third_quartile, \n",
    "\tarray[array_to_string(array[NULL], ',')]::text[] as ntiles, \n",
    "\tarray[ \n",
    "\tarray_to_string((madlib.mfvsketch_quick_histogram( \n",
    "\t\ta,10))[0:10-1][0:0], \n",
    "\t'_.*.&.!.!.&.*_'), \n",
    "\tarray_to_string((madlib.mfvsketch_quick_histogram( \n",
    "\t\tb,10))[0:10-1][0:0], \n",
    "\t'_.*.&.!.!.&.*_')]::text[] as mfv_value, \n",
    "\tarray[ \n",
    "\tarray_to_string((madlib.mfvsketch_quick_histogram( \n",
    "\t\ta,10))[0:10-1][1:1], \n",
    "\t'_.*.&.!.!.&.*_'), \n",
    "\tarray_to_string((madlib.mfvsketch_quick_histogram( \n",
    "\t\tb,10))[0:10-1][1:1], \n",
    "\t'_.*.&.!.!.&.*_')]::text[] as mfv_count \n",
    "FROM test100M;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "Done.\n",
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "1000000 rows affected.\n",
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "Done.\n",
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "Done.\n",
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>output_table</th>\n",
       "        <th>num_col_summarized</th>\n",
       "        <th>duration</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>test1M_summary</td>\n",
       "        <td>2</td>\n",
       "        <td>7.44582605361938</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('test1M_summary', 2, 7.44582605361938)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql DROP TABLE IF EXISTS test1M; \n",
    "%sql CREATE TABLE test1M (a, b) AS SELECT generate_series(1, 1000000) * random() * 10 AS a, generate_series(1, 1000000) * random() * 10 AS b DISTRIBUTED BY (a);\n",
    "#%sql SELECT * FROM test1M;\n",
    "%sql ANALYZE test1M;\n",
    "%sql DROP TABLE IF EXISTS test1M_summary; \n",
    "%sql SELECT * FROM madlib.summary('test1M', 'test1M_summary');\n",
    "#%sql SELECT * FROM test1M_summary;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "Done.\n",
      "1 rows affected.\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>group_by</th>\n",
       "        <th>group_by_value</th>\n",
       "        <th>target_column</th>\n",
       "        <th>column_number</th>\n",
       "        <th>data_type</th>\n",
       "        <th>row_count</th>\n",
       "        <th>distinct_values</th>\n",
       "        <th>missing_values</th>\n",
       "        <th>blank_values</th>\n",
       "        <th>fraction_missing</th>\n",
       "        <th>fraction_blank</th>\n",
       "        <th>positive_values</th>\n",
       "        <th>negative_values</th>\n",
       "        <th>zero_values</th>\n",
       "        <th>mean</th>\n",
       "        <th>variance</th>\n",
       "        <th>confidence_interval</th>\n",
       "        <th>min</th>\n",
       "        <th>max</th>\n",
       "        <th>most_frequent_values</th>\n",
       "        <th>mfv_frequencies</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>a</td>\n",
       "        <td>1</td>\n",
       "        <td>float8</td>\n",
       "        <td>10000000</td>\n",
       "        <td>10000000</td>\n",
       "        <td>0</td>\n",
       "        <td>None</td>\n",
       "        <td>0.0</td>\n",
       "        <td>None</td>\n",
       "        <td>10000000</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>43607135.123834</td>\n",
       "        <td>633860681183370.0</td>\n",
       "        <td>[43591530.5117182, 43622739.7359498]</td>\n",
       "        <td>8.72142615262419</td>\n",
       "        <td>87214261.5262419</td>\n",
       "        <td>[&#x27;85784750.0084176&#x27;, &#x27;86756831.4459629&#x27;, &#x27;86820349.5926325&#x27;, &#x27;86536798.5855584&#x27;, &#x27;85095617.7995418&#x27;, &#x27;87135350.0624129&#x27;, &#x27;86361968.8769028&#x27;, &#x27;86996897.42224&#x27;, &#x27;87164043.5544551&#x27;, &#x27;87195937.8098952&#x27;]</td>\n",
       "        <td>[9796, 9792, 9785, 9767, 9764, 9764, 9758, 9752, 9711, 9701]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>b</td>\n",
       "        <td>2</td>\n",
       "        <td>float8</td>\n",
       "        <td>10000000</td>\n",
       "        <td>9998781</td>\n",
       "        <td>0</td>\n",
       "        <td>None</td>\n",
       "        <td>0.0</td>\n",
       "        <td>None</td>\n",
       "        <td>10000000</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>46367740.3085844</td>\n",
       "        <td>716655708775869.0</td>\n",
       "        <td>[46351147.8266397, 46384332.790529]</td>\n",
       "        <td>9.27354713436216</td>\n",
       "        <td>92735471.3436216</td>\n",
       "        <td>[&#x27;92012190.3084242&#x27;, &#x27;91945235.2981141&#x27;, &#x27;92075194.787655&#x27;, &#x27;92693054.1390291&#x27;, &#x27;91656948.5383482&#x27;, &#x27;91845340.6483827&#x27;, &#x27;91774221.8154093&#x27;, &#x27;92008824.0108144&#x27;, &#x27;92260304.062004&#x27;, &#x27;92701808.3675239&#x27;]</td>\n",
       "        <td>[9803, 9760, 9756, 9743, 9737, 9729, 9728, 9723, 9715, 9708]</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(None, None, 'a', 1, 'float8', 10000000, 10000000, 0, None, 0.0, None, 10000000, 0, 0, 43607135.123834, 633860681183370.0, [43591530.5117182, 43622739.7359498], 8.72142615262419, 87214261.5262419, ['85784750.0084176', '86756831.4459629', '86820349.5926325', '86536798.5855584', '85095617.7995418', '87135350.0624129', '86361968.8769028', '86996897.42224', '87164043.5544551', '87195937.8098952'], [9796, 9792, 9785, 9767, 9764, 9764, 9758, 9752, 9711, 9701]),\n",
       " (None, None, 'b', 2, 'float8', 10000000, 9998781, 0, None, 0.0, None, 10000000, 0, 0, 46367740.3085844, 716655708775869.0, [46351147.8266397, 46384332.790529], 9.27354713436216, 92735471.3436216, ['92012190.3084242', '91945235.2981141', '92075194.787655', '92693054.1390291', '91656948.5383482', '91845340.6483827', '91774221.8154093', '92008824.0108144', '92260304.062004', '92701808.3675239'], [9803, 9760, 9756, 9743, 9737, 9729, 9728, 9723, 9715, 9708])]"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "DROP TABLE IF EXISTS test10M_summary; \n",
    "SELECT * FROM madlib.summary('test10M', 'test10M_summary', \n",
    "                            NULL, NULL, \n",
    "                            TRUE, FALSE);\n",
    "SELECT * FROM test10M_summary;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>group_by</th>\n",
       "        <th>group_by_value</th>\n",
       "        <th>target_column</th>\n",
       "        <th>column_number</th>\n",
       "        <th>data_type</th>\n",
       "        <th>row_count</th>\n",
       "        <th>distinct_values</th>\n",
       "        <th>missing_values</th>\n",
       "        <th>blank_values</th>\n",
       "        <th>fraction_missing</th>\n",
       "        <th>fraction_blank</th>\n",
       "        <th>positive_values</th>\n",
       "        <th>negative_values</th>\n",
       "        <th>zero_values</th>\n",
       "        <th>mean</th>\n",
       "        <th>variance</th>\n",
       "        <th>confidence_interval</th>\n",
       "        <th>min</th>\n",
       "        <th>max</th>\n",
       "        <th>most_frequent_values</th>\n",
       "        <th>mfv_frequencies</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>a</td>\n",
       "        <td>1</td>\n",
       "        <td>float8</td>\n",
       "        <td>10000000</td>\n",
       "        <td>10000000</td>\n",
       "        <td>0</td>\n",
       "        <td>None</td>\n",
       "        <td>0.0</td>\n",
       "        <td>None</td>\n",
       "        <td>10000000</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>43607135.123834</td>\n",
       "        <td>633860681183368.0</td>\n",
       "        <td>[43591530.5117183, 43622739.7359498]</td>\n",
       "        <td>8.72142615262419</td>\n",
       "        <td>87214261.5262419</td>\n",
       "        <td>[&#x27;86756831.4459629&#x27;, &#x27;86699863.090334&#x27;, &#x27;86936091.6391039&#x27;, &#x27;87035079.8259362&#x27;, &#x27;86931652.4331923&#x27;, &#x27;86909770.3749753&#x27;, &#x27;86793914.9499639&#x27;, &#x27;85934540.5025889&#x27;, &#x27;86143976.830218&#x27;, &#x27;86293976.638617&#x27;]</td>\n",
       "        <td>[9792, 9771, 9759, 9756, 9749, 9744, 9743, 9740, 9723, 9718]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>b</td>\n",
       "        <td>2</td>\n",
       "        <td>float8</td>\n",
       "        <td>10000000</td>\n",
       "        <td>9998781</td>\n",
       "        <td>0</td>\n",
       "        <td>None</td>\n",
       "        <td>0.0</td>\n",
       "        <td>None</td>\n",
       "        <td>10000000</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>46367740.3085844</td>\n",
       "        <td>716655708775871.0</td>\n",
       "        <td>[46351147.8266397, 46384332.790529]</td>\n",
       "        <td>9.27354713436216</td>\n",
       "        <td>92735471.3436216</td>\n",
       "        <td>[&#x27;92012190.3084242&#x27;, &#x27;92174792.6838781&#x27;, &#x27;91837569.4158841&#x27;, &#x27;91947702.0616518&#x27;, &#x27;91855318.9850993&#x27;, &#x27;91673742.9322085&#x27;, &#x27;92377419.6887639&#x27;, &#x27;90072256.9833869&#x27;, &#x27;92213964.1469736&#x27;, &#x27;91776224.9015903&#x27;]</td>\n",
       "        <td>[9803, 9789, 9779, 9772, 9762, 9760, 9749, 9737, 9733, 9733]</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(None, None, 'a', 1, 'float8', 10000000, 10000000, 0, None, 0.0, None, 10000000, 0, 0, 43607135.123834, 633860681183368.0, [43591530.5117183, 43622739.7359498], 8.72142615262419, 87214261.5262419, ['86756831.4459629', '86699863.090334', '86936091.6391039', '87035079.8259362', '86931652.4331923', '86909770.3749753', '86793914.9499639', '85934540.5025889', '86143976.830218', '86293976.638617'], [9792, 9771, 9759, 9756, 9749, 9744, 9743, 9740, 9723, 9718]),\n",
       " (None, None, 'b', 2, 'float8', 10000000, 9998781, 0, None, 0.0, None, 10000000, 0, 0, 46367740.3085844, 716655708775871.0, [46351147.8266397, 46384332.790529], 9.27354713436216, 92735471.3436216, ['92012190.3084242', '92174792.6838781', '91837569.4158841', '91947702.0616518', '91855318.9850993', '91673742.9322085', '92377419.6887639', '90072256.9833869', '92213964.1469736', '91776224.9015903'], [9803, 9789, 9779, 9772, 9762, 9760, 9749, 9737, 9733, 9733])]"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT\n",
    "    group_by as group_by,\n",
    "    group_by_value as group_by_value,\n",
    "    target_column as target_column,\n",
    "    colnum as column_number,\n",
    "    datatype as data_type,\n",
    "    rowcount as row_count,\n",
    "    \n",
    "    (CASE WHEN rowcount < distinct_values THEN rowcount\n",
    "    ELSE distinct_values\n",
    "    END) AS distinct_values,\n",
    "    missing_values as missing_values,\n",
    "    blank_values as blank_values,\n",
    "    (missing_values::float8 / rowcount) as fraction_missing,\n",
    "    (blank_values::float8 / rowcount) as fraction_blank,\n",
    "    positive_values,\n",
    "    negative_values,\n",
    "    zero_values,\n",
    "    mean,\n",
    "    variance,\n",
    "    CASE WHEN ci_lower_bound is NULL OR ci_upper_bound is NULL\n",
    "         THEN null\n",
    "         ELSE ARRAY[ci_lower_bound, ci_upper_bound]\n",
    "    END as confidence_interval,\n",
    "    min,\n",
    "    max,\n",
    "--    first_quartile,\n",
    "--    median,\n",
    "--    third_quartile,\n",
    "\n",
    "    mfv_value as most_frequent_values,\n",
    "    mfv_count::bigint[] as mfv_frequencies\n",
    "FROM\n",
    "(\n",
    "    SELECT\n",
    "        group_by,\n",
    "        group_by_value,\n",
    "        target_column,\n",
    "        datatype,\n",
    "        colnum,\n",
    "        rowcount,\n",
    "        distinct_values,\n",
    "        missing_values,\n",
    "        blank_values,\n",
    "        distinct_values::float8 / rowcount as fraction_distinct_values,\n",
    "        missing_values::float8 / rowcount as fraction_missing_values,\n",
    "        blank_values::float8 / rowcount as fraction_blank_values,\n",
    "        positive_values,\n",
    "        negative_values,\n",
    "        zero_values,\n",
    "        mean,\n",
    "        variance,\n",
    "        (mean - 1.96 * sqrt(variance / rowcount))::float8 as ci_lower_bound,\n",
    "        (mean + 1.96 * sqrt(variance / rowcount))::float8 as ci_upper_bound,\n",
    "        min,\n",
    "        max,\n",
    "--        first_quartile,\n",
    "--        median,\n",
    "--        third_quartile,\n",
    "        ntiles,\n",
    "        string_to_array(mfv_value, '_.*.&.!.!.&.*_') as mfv_value,\n",
    "        string_to_array(mfv_count, '_.*.&.!.!.&.*_') as mfv_count\n",
    "    FROM\n",
    "    (\n",
    "        SELECT\n",
    "            group_by,\n",
    "            group_by_value,\n",
    "            unnest(target_column) AS target_column,\n",
    "            unnest(datatype) AS datatype,\n",
    "            unnest(colnum) AS colnum,\n",
    "            rowcount,\n",
    "            unnest(distinct_values) as distinct_values,\n",
    "            unnest(missing_values) as missing_values,\n",
    "            unnest(blank_values) as blank_values,\n",
    "            unnest(positive_values) as positive_values,\n",
    "            unnest(negative_values) as negative_values,\n",
    "            unnest(zero_values) as zero_values,\n",
    "            unnest(mean) as mean,\n",
    "            unnest(variance) as variance,\n",
    "            unnest(min) as min,\n",
    "            unnest(max) as max,\n",
    "--            unnest(first_quartile) as first_quartile,\n",
    "--            unnest(median) as median,\n",
    "--            unnest(third_quartile) as third_quartile,\n",
    "            string_to_array(unnest(ntiles), ',') as ntiles,\n",
    "            unnest(mfv_value) as mfv_value,\n",
    "            unnest(mfv_count) as mfv_count\n",
    "        FROM (\n",
    "\t\t\tSELECT\n",
    "\t\t\t\tNULL::text as group_by,\n",
    "\t\t\t\tNULL::text as group_by_value,\n",
    "\t\t\t\tarray['a','b']::text[] as target_column,\n",
    "\t\t\t\tarray['float8','float8']::text[] as datatype,\n",
    "\t\t\t\tarray[1,2]::integer[] as colnum,\n",
    "\t\t\t\tcount(*)::bigint as rowcount,\n",
    "\t\t\t\tarray[sum(case when a > 0 then 1 else 0 end),sum(case when b > 0 then 1 else 0 end)]::bigint[] as positive_values,\n",
    "\t\t\t\tarray[sum(case when a < 0 then 1 else 0 end),sum(case when b < 0 then 1 else 0 end)]::bigint[] as negative_values,\n",
    "\t\t\t\tarray[sum(case when a = 0 then 1 else 0 end),sum(case when b = 0 then 1 else 0 end)]::bigint[] as zero_values,\n",
    "\t\t\t\tarray[avg(a),avg(b)]::float8[] as mean,\n",
    "\t\t\t\tarray[variance(a),variance(b)]::float8[] as variance,\n",
    "\t\t\t\tarray[madlib.fmsketch_dcount(a),madlib.fmsketch_dcount(b)]::bigint[] as distinct_values,\n",
    "\t\t\t\tarray[sum(case when a is null then 1 else 0 end),sum(case when b is null then 1 else 0 end)]::bigint[] as missing_values,\n",
    "\t\t\t\tarray[NULL,NULL]::bigint[] as blank_values,\n",
    "\t\t\t\tarray[min(a),min(b)]::float8[] as min,\n",
    "\t\t\t\tarray[max(a),max(b)]::float8[] as max,\n",
    "--\t\t\t\tarray[percentile_cont(0.25) WITHIN GROUP (ORDER BY a),percentile_cont(0.25) WITHIN GROUP (ORDER BY b)]::float8[] as first_quartile,\n",
    "--\t\t\t\tarray[percentile_cont(0.5) WITHIN GROUP (ORDER BY a),percentile_cont(0.5) WITHIN GROUP (ORDER BY b)]::float8[] as median,\n",
    "--\t\t\t\tarray[percentile_cont(0.75) WITHIN GROUP (ORDER BY a),percentile_cont(0.75) WITHIN GROUP (ORDER BY b)]::float8[] as third_quartile,\n",
    "\t\t\t\tarray[array_to_string(array[NULL], ',')]::text[] as ntiles,\n",
    "\t\t\t\tarray[\n",
    "\t\t\t\t\tarray_to_string((madlib.mfvsketch_quick_histogram(\n",
    "\t\t\t\t\t\ta,10))[0:10-1][0:0],\n",
    "\t\t\t\t\t\t'_.*.&.!.!.&.*_'),\n",
    "\t\t\t\t\tarray_to_string((madlib.mfvsketch_quick_histogram(\n",
    "\t\t\t\t\t\tb,10))[0:10-1][0:0],\n",
    "\t\t\t\t\t\t'_.*.&.!.!.&.*_')]::text[] as mfv_value,\n",
    "\t\t\t\tarray[\n",
    "\t\t\t\t\tarray_to_string((madlib.mfvsketch_quick_histogram(\n",
    "\t\t\t\t\t\ta,10))[0:10-1][1:1],\n",
    "\t\t\t\t\t\t'_.*.&.!.!.&.*_'),\n",
    "\t\t\t\t\tarray_to_string((madlib.mfvsketch_quick_histogram(\n",
    "\t\t\t\t\t\tb,10))[0:10-1][1:1],\n",
    "\t\t\t\t\t\t'_.*.&.!.!.&.*_')]::text[] as mfv_count\n",
    "\t\t\tFROM test10M\n",
    "\t\t) q1\n",
    "    ) q2\n",
    ") q3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "Done.\n",
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "10000000 rows affected.\n",
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "Done.\n",
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "Done.\n",
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "(psycopg2.errors.AdminShutdown) terminating connection due to administrator command\n",
      "CONTEXT:  SQL statement \"CREATE TABLE test10M_summary AS \n",
      "            SELECT\n",
      "                group_by as group_by,\n",
      "                group_by_value as group_by_value,\n",
      "                target_column as target_column,\n",
      "                colnum as column_number,\n",
      "                datatype as data_type,\n",
      "                rowcount as row_count,\n",
      "                \n",
      "                (CASE WHEN rowcount < distinct_values THEN rowcount\n",
      "                ELSE distinct_values\n",
      "                END) AS distinct_values,\n",
      "                missing_values as missing_values,\n",
      "                blank_values as blank_values,\n",
      "                (missing_values::float8 / rowcount) as fraction_missing,\n",
      "                (blank_values::float8 / rowcount) as fraction_blank,\n",
      "                positive_values,\n",
      "                negative_values,\n",
      "                zero_values,\n",
      "                mean,\n",
      "                variance,\n",
      "                CASE WHEN ci_lower_bound is NULL OR ci_upper_bound is NULL\n",
      "                     THEN null\n",
      "                     ELSE ARRAY[ci_lower_bound, ci_upper_bound]\n",
      "                END as confidence_interval,\n",
      "                min,\n",
      "                max,\n",
      "                first_quartile,\n",
      "                median,\n",
      "                third_quartile,\n",
      "                \n",
      "                mfv_value as most_frequent_values,\n",
      "                mfv_count::bigint[] as mfv_frequencies\n",
      "            FROM\n",
      "            (\n",
      "                    \n",
      "                SELECT\n",
      "                    group_by,\n",
      "                    group_by_value,\n",
      "                    target_column,\n",
      "                    datatype,\n",
      "                    colnum,\n",
      "                    rowcount,\n",
      "                    distinct_values,\n",
      "                    missing_values,\n",
      "                    blank_values,\n",
      "                    distinct_values::float8 / rowcount as fraction_distinct_values,\n",
      "                    missing_values::float8 / rowcount as fraction_missing_values,\n",
      "                    blank_values::float8 / rowcount as fraction_blank_values,\n",
      "                    positive_values,\n",
      "                    negative_values,\n",
      "                    zero_values,\n",
      "                    mean,\n",
      "                    variance,\n",
      "                    (mean - 1.96 * sqrt(variance / rowcount))::float8 as ci_lower_bound,\n",
      "                    (mean + 1.96 * sqrt(variance / rowcount))::float8 as ci_upper_bound,\n",
      "                    min,\n",
      "                    max,\n",
      "                    first_quartile,\n",
      "                    median,\n",
      "                    third_quartile,\n",
      "                    ntiles,\n",
      "                    string_to_array(mfv_value, '_.*.&.!.!.&.*_') as mfv_value,\n",
      "                    string_to_array(mfv_count, '_.*.&.!.!.&.*_') as mfv_count\n",
      "                FROM\n",
      "                (\n",
      "                    SELECT\n",
      "                        group_by,\n",
      "                        group_by_value,\n",
      "                        unnest(target_column) AS target_column,\n",
      "                        unnest(datatype) AS datatype,\n",
      "                        unnest(colnum) AS colnum,\n",
      "                        rowcount,\n",
      "                        unnest(distinct_values) as distinct_values,\n",
      "                        unnest(missing_values) as missing_values,\n",
      "                        unnest(blank_values) as blank_values,\n",
      "                        unnest(positive_values) as positive_values,\n",
      "                        unnest(negative_values) as negative_values,\n",
      "                        unnest(zero_values) as zero_values,\n",
      "                        unnest(mean) as mean,\n",
      "                        unnest(variance) as variance,\n",
      "                        unnest(min) as min,\n",
      "                        unnest(max) as max,\n",
      "                        unnest(first_quartile) as first_quartile,\n",
      "                        unnest(median) as median,\n",
      "                        unnest(third_quartile) as third_quartile,\n",
      "                        string_to_array(unnest(ntiles), ',') as ntiles,\n",
      "                        unnest(mfv_value) as mfv_value,\n",
      "                        unnest(mfv_count) as mfv_count\n",
      "                    FROM (\n",
      "                SELECT\n",
      "                    NULL::text as group_by,\n",
      "                    NULL::text as group_by_value,\n",
      "                    array['a','b']::text[] as target_column,\n",
      "                    array['float8','float8']::text[] as datatype,\n",
      "                    array[1,2]::integer[] as colnum,\n",
      "                    count(*)::bigint as rowcount,\n",
      "                    array[sum(case when a > 0 then 1 else 0 end),sum(case when b > 0 then 1 else 0 end)]::bigint[] as positive_values,\n",
      "                    array[sum(case when a < 0 then 1 else 0 end),sum(case when b < 0 then 1 else 0 end)]::bigint[] as negative_values,\n",
      "                    array[sum(case when a = 0 then 1 else 0 end),sum(case when b = 0 then 1 else 0 end)]::bigint[] as zero_values,\n",
      "                    array[avg(a),avg(b)]::float8[] as mean,\n",
      "                    array[variance(a),variance(b)]::float8[] as variance,\n",
      "                    array[madlib.fmsketch_dcount(a),madlib.fmsketch_dcount(b)]::bigint[] as distinct_values,\n",
      "                    array[sum(case when a is null then 1 else 0 end),sum(case when b is null then 1 else 0 end)]::bigint[] as missing_values,\n",
      "                    array[NULL,NULL]::bigint[] as blank_values,\n",
      "                    array[min(a),min(b)]::float8[] as min,\n",
      "                    array[max(a),max(b)]::float8[] as max,\n",
      "                    array[percentile_cont(0.25) WITHIN GROUP (ORDER BY a),percentile_cont(0.25) WITHIN GROUP (ORDER BY b)]::float8[] as first_quartile,\n",
      "                    array[percentile_cont(0.5) WITHIN GROUP (ORDER BY a),percentile_cont(0.5) WITHIN GROUP (ORDER BY b)]::float8[] as median,\n",
      "                    array[percentile_cont(0.75) WITHIN GROUP (ORDER BY a),percentile_cont(0.75) WITHIN GROUP (ORDER BY b)]::float8[] as third_quartile,\n",
      "                    array[array_to_string(array[NULL], ',')]::text[] as ntiles,\n",
      "                    array[\n",
      "                    array_to_string((madlib.mfvsketch_quick_histogram(\n",
      "                                a,10))[0:10-1][0:0],\n",
      "                                '_.*.&.!.!.&.*_'),\n",
      "                    array_to_string((madlib.mfvsketch_quick_histogram(\n",
      "                                b,10))[0:10-1][0:0],\n",
      "                                '_.*.&.!.!.&.*_')]::text[] as mfv_value,\n",
      "                    array[\n",
      "                    array_to_string((madlib.mfvsketch_quick_histogram(\n",
      "                                a,10))[0:10-1][1:1],\n",
      "                                '_.*.&.!.!.&.*_'),\n",
      "                    array_to_string((madlib.mfvsketch_quick_histogram(\n",
      "                                b,10))[0:10-1][1:1],\n",
      "                                '_.*.&.!.!.&.*_')]::text[] as mfv_count\n",
      "                FROM test10M\n",
      "         ) q1\n",
      "                ) q2\n",
      "        \n",
      "\n",
      "            ) q3\n",
      "        \n",
      "                DISTRIBUTED BY (group_by)\"\n",
      "PL/Python function \"summary\"\n",
      "ERROR:  server conn crashed?\n",
      "SSL connection has been closed unexpectedly\n",
      "\n",
      "[SQL: SELECT * FROM madlib.summary('test10M', 'test10M_summary' );]\n",
      "(Background on this error at: http://sqlalche.me/e/e3q8)\n",
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n"
     ]
    },
    {
     "ename": "DatabaseError",
     "evalue": "(psycopg2.errors.ProtocolViolation) query_wait_timeout\nSSL connection has been closed unexpectedly\n\n[SQL: SELECT * FROM test10M_summary;]\n(Background on this error at: http://sqlalche.me/e/4xp6)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mProtocolViolation\u001b[0m                         Traceback (most recent call last)",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, *args)\u001b[0m\n\u001b[1;32m   1245\u001b[0m                     self.dialect.do_execute(\n\u001b[0;32m-> 1246\u001b[0;31m                         \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1247\u001b[0m                     )\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/default.py\u001b[0m in \u001b[0;36mdo_execute\u001b[0;34m(self, cursor, statement, parameters, context)\u001b[0m\n\u001b[1;32m    587\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdo_execute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 588\u001b[0;31m         \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    589\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mProtocolViolation\u001b[0m: query_wait_timeout\nSSL connection has been closed unexpectedly\n",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[0;31mDatabaseError\u001b[0m                             Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-60-56b5980e3395>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      5\u001b[0m \u001b[0mget_ipython\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrun_line_magic\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'sql'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'DROP TABLE IF EXISTS test10M_summary; '\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      6\u001b[0m \u001b[0mget_ipython\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrun_line_magic\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'sql'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m\"SELECT * FROM madlib.summary('test10M', 'test10M_summary');\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 7\u001b[0;31m \u001b[0mget_ipython\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrun_line_magic\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'sql'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'SELECT * FROM test10M_summary;'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/IPython/core/interactiveshell.py\u001b[0m in \u001b[0;36mrun_line_magic\u001b[0;34m(self, magic_name, line, _stack_depth)\u001b[0m\n\u001b[1;32m   2315\u001b[0m                 \u001b[0mkwargs\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'local_ns'\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0msys\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_getframe\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstack_depth\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mf_locals\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2316\u001b[0m             \u001b[0;32mwith\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mbuiltin_trap\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2317\u001b[0;31m                 \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mfn\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2318\u001b[0m             \u001b[0;32mreturn\u001b[0m \u001b[0mresult\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2319\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m</root/anaconda3/lib/python3.7/site-packages/decorator.py:decorator-gen-127>\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, line, cell, local_ns)\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/IPython/core/magic.py\u001b[0m in \u001b[0;36m<lambda>\u001b[0;34m(f, *a, **k)\u001b[0m\n\u001b[1;32m    185\u001b[0m     \u001b[0;31m# but it's overkill for just that one bit of state.\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    186\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mmagic_deco\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0marg\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 187\u001b[0;31m         \u001b[0mcall\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mlambda\u001b[0m \u001b[0mf\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0ma\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mk\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0mf\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0ma\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mk\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    188\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    189\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mcallable\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0marg\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m</root/anaconda3/lib/python3.7/site-packages/decorator.py:decorator-gen-126>\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, line, cell, local_ns)\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/IPython/core/magic.py\u001b[0m in \u001b[0;36m<lambda>\u001b[0;34m(f, *a, **k)\u001b[0m\n\u001b[1;32m    185\u001b[0m     \u001b[0;31m# but it's overkill for just that one bit of state.\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    186\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mmagic_deco\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0marg\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 187\u001b[0;31m         \u001b[0mcall\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mlambda\u001b[0m \u001b[0mf\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0ma\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mk\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0mf\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0ma\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mk\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    188\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    189\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mcallable\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0marg\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/sql/magic.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, line, cell, local_ns)\u001b[0m\n\u001b[1;32m    215\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    216\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 217\u001b[0;31m             \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0msql\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrun\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrun\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mconn\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparsed\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m\"sql\"\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0muser_ns\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    218\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    219\u001b[0m             if (\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/sql/run.py\u001b[0m in \u001b[0;36mrun\u001b[0;34m(conn, sql, config, user_namespace)\u001b[0m\n\u001b[1;32m    365\u001b[0m             \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    366\u001b[0m                 \u001b[0mtxt\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0msqlalchemy\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msql\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mtext\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 367\u001b[0;31m                 \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mconn\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msession\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtxt\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0muser_namespace\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    368\u001b[0m             \u001b[0m_commit\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mconn\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mconn\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mconfig\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mconfig\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    369\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mresult\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0mconfig\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mfeedback\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, object_, *multiparams, **params)\u001b[0m\n\u001b[1;32m    980\u001b[0m             \u001b[0;32mraise\u001b[0m \u001b[0mexc\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mObjectNotExecutableError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mobject_\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    981\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 982\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mmeth\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    983\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    984\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_execute_function\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfunc\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/sqlalchemy/sql/elements.py\u001b[0m in \u001b[0;36m_execute_on_connection\u001b[0;34m(self, connection, multiparams, params)\u001b[0m\n\u001b[1;32m    291\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_execute_on_connection\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mconnection\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    292\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msupports_execution\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 293\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mconnection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_execute_clauseelement\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    294\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    295\u001b[0m             \u001b[0;32mraise\u001b[0m \u001b[0mexc\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mObjectNotExecutableError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_clauseelement\u001b[0;34m(self, elem, multiparams, params)\u001b[0m\n\u001b[1;32m   1099\u001b[0m             \u001b[0mdistilled_params\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1100\u001b[0m             \u001b[0mcompiled_sql\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1101\u001b[0;31m             \u001b[0mdistilled_params\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1102\u001b[0m         )\n\u001b[1;32m   1103\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_has_events\u001b[0m \u001b[0;32mor\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mengine\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_has_events\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, *args)\u001b[0m\n\u001b[1;32m   1248\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mBaseException\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1249\u001b[0m             self._handle_dbapi_exception(\n\u001b[0;32m-> 1250\u001b[0;31m                 \u001b[0me\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1251\u001b[0m             )\n\u001b[1;32m   1252\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_handle_dbapi_exception\u001b[0;34m(self, e, statement, parameters, cursor, context)\u001b[0m\n\u001b[1;32m   1474\u001b[0m                 \u001b[0mutil\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mraise_from_cause\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mnewraise\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_info\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1475\u001b[0m             \u001b[0;32melif\u001b[0m \u001b[0mshould_wrap\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1476\u001b[0;31m                 \u001b[0mutil\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mraise_from_cause\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msqlalchemy_exception\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_info\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1477\u001b[0m             \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1478\u001b[0m                 \u001b[0mutil\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mreraise\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0mexc_info\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/sqlalchemy/util/compat.py\u001b[0m in \u001b[0;36mraise_from_cause\u001b[0;34m(exception, exc_info)\u001b[0m\n\u001b[1;32m    396\u001b[0m     \u001b[0mexc_type\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_value\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_tb\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mexc_info\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    397\u001b[0m     \u001b[0mcause\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mexc_value\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0mexc_value\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mexception\u001b[0m \u001b[0;32melse\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 398\u001b[0;31m     \u001b[0mreraise\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtype\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mexception\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexception\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtb\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mexc_tb\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcause\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mcause\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    399\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    400\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/sqlalchemy/util/compat.py\u001b[0m in \u001b[0;36mreraise\u001b[0;34m(tp, value, tb, cause)\u001b[0m\n\u001b[1;32m    150\u001b[0m             \u001b[0mvalue\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__cause__\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mcause\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    151\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__traceback__\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mtb\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 152\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mwith_traceback\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtb\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    153\u001b[0m         \u001b[0;32mraise\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    154\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, *args)\u001b[0m\n\u001b[1;32m   1244\u001b[0m                 \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mevt_handled\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1245\u001b[0m                     self.dialect.do_execute(\n\u001b[0;32m-> 1246\u001b[0;31m                         \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1247\u001b[0m                     )\n\u001b[1;32m   1248\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mBaseException\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/default.py\u001b[0m in \u001b[0;36mdo_execute\u001b[0;34m(self, cursor, statement, parameters, context)\u001b[0m\n\u001b[1;32m    586\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    587\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdo_execute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 588\u001b[0;31m         \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    589\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    590\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdo_execute_no_params\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mDatabaseError\u001b[0m: (psycopg2.errors.ProtocolViolation) query_wait_timeout\nSSL connection has been closed unexpectedly\n\n[SQL: SELECT * FROM test10M_summary;]\n(Background on this error at: http://sqlalche.me/e/4xp6)"
     ]
    }
   ],
   "source": [
    "%sql DROP TABLE IF EXISTS test10M; \n",
    "%sql CREATE TABLE test10M (a, b) AS SELECT generate_series(1, 10000000) * random() * 10 AS a, generate_series(1, 10000000) * random() * 10 AS b DISTRIBUTED BY (a);\n",
    "#%sql SELECT * FROM test10M;\n",
    "%sql ANALYZE test10M;\n",
    "%sql DROP TABLE IF EXISTS test10M_summary; \n",
    "%sql SELECT * FROM madlib.summary('test10M', 'test10M_summary');\n",
    "%sql SELECT * FROM test10M_summary;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "Done.\n",
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "100000000 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql DROP TABLE IF EXISTS test100M; \n",
    "%sql CREATE TABLE test100M (a, b) AS SELECT generate_series(1, 100000000) * random() * 10 AS a, generate_series(1, 100000000) * random() * 10 AS b DISTRIBUTED BY (a);\n",
    "#%sql SELECT * FROM test100M;\n",
    "#%sql ANALYZE test100M;\n",
    "#%sql DROP TABLE IF EXISTS test100M_summary; \n",
    "#%sql SELECT * FROM madlib.summary('test100M', 'test100M_summary');\n",
    "#%sql SELECT * FROM test100M_summary;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "Done.\n",
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "(psycopg2.errors.QueryCanceled) canceling statement due to user request: \"gpmon canceled this query from GPCC UI.\"\n",
      "\n",
      "[SQL: CREATE TABLE test1B (a, b) AS SELECT generate_series(1, 1000000000) * random() * 10 AS a, generate_series(1, 1000000000) * random() * 10 AS b DISTRIBUTED BY (a);]\n",
      "(Background on this error at: http://sqlalche.me/e/e3q8)\n"
     ]
    }
   ],
   "source": [
    "%sql DROP TABLE IF EXISTS test1B; \n",
    "%sql CREATE TABLE test1B (a, b) AS SELECT generate_series(1, 1000000000) * random() * 10 AS a, generate_series(1, 1000000000) * random() * 10 AS b DISTRIBUTED BY (a);\n",
    "#%sql SELECT * FROM test1B;\n",
    "#%sql ANALYZE test1B;\n",
    "#%sql DROP TABLE IF EXISTS test1B_summary; \n",
    "#%sql SELECT * FROM madlib.summary('test1B', 'test1B_summary');\n",
    "#%sql SELECT * FROM test1B_summary;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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
}