Untitled1.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": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>version</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>PostgreSQL 9.4.24 (Greenplum Database 6.12.0 build commit:4c176763c7619fb678ce38095e6b3e8fb9548186) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Oct 28 2020 19:42:15</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('PostgreSQL 9.4.24 (Greenplum Database 6.12.0 build commit:4c176763c7619fb678ce38095e6b3e8fb9548186) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Oct 28 2020 19:42:15',)]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql $CONNECTION_STRING\n",
    "%sql $DB_USER@$DB_NAME {\"SELECT version();\"}"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "%%sql\n",
    "SELECT * \n",
    "FROM information_schema.COLUMNS \n",
    "WHERE TABLE_NAME LIKE '%amzn_reviews_1_%'\n",
    "    AND TABLE_SCHEMA = 'demo'\n",
    "ORDER BY table_catalog, table_schema, table_name, ordinal_position;"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "%%sql\n",
    "SELECT \n",
    "    schemaname AS schema\n",
    "    , tablename AS table\n",
    "    , attname AS column\n",
    "    , aut.autreltuples AS row_count\n",
    "    , aut.autreltuples * null_frac AS null_row_count\n",
    "    , CASE WHEN n_distinct < 0 THEN ROUND(-1 * n_distinct * aut.autreltuples) ELSE n_distinct END AS distinct_row_count\n",
    "FROM \n",
    "    pg_catalog.pg_stats\n",
    "    , gp_toolkit.__gp_user_tables aut\n",
    "WHERE \n",
    "    pg_stats.schemaname = aut.autnspname\n",
    "    AND pg_stats.tablename = aut.autrelname\n",
    "    AND schemaname = 'demo'\n",
    "    AND tablename = 'amzn_reviews_1';"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "SELECT aut.autnspname AS namespace\n",
    "    , aut.autrelname AS tablename\n",
    "    , attrs.attname AS columnname\n",
    "    , aut.autreltuples AS row_count\n",
    "    , bar.stanullfrac AS rows_null\n",
    "    , bar.stadistinct AS rows_distinct\n",
    "FROM \n",
    "    gp_toolkit.__gp_user_tables aut\n",
    "    , pg_catalog.pg_attribute attrs\n",
    "    , pg_catalog.pg_statistic bar\n",
    "WHERE\n",
    "    aut.autoid = attrs.attrelid\n",
    "    AND aut.autoid = bar.starelid\n",
    "    AND autrelkind = 'r'\n",
    "    AND attstattarget != 0\n",
    "    AND autnspname = 'demo'\n",
    "    AND autrelname = 'amzn_reviews_1'"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "%sql ANALYZE demo.amzn_reviews_1\n",
    "#SELECT COUNT(*) FROM demo.amzn_reviews_1\n",
    "# SELECT COUNT(review_id) FROM (SELECT review_id, COUNT(*) FROM demo.amzn_reviews_1 GROUP BY review_id HAVING COUNT(*)=1) A;\n"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "%%sql\n",
    "SELECT COUNT(*) FROM demo.amzn_reviews_1;"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "demo.amzn_reviews_1   160789790\n",
    "demo.amzn_reviews_1_1 160789790\n"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "%%sql\n",
    "SELECT * FROM pg_attribute WHERE attname = 'review_id';"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "%%sql\n",
    "SELECT pgc.oid,\n",
    "    pg_catalog.pg_get_table_distributedby(pgc.OID),\n",
    "    pgn.nspname,\n",
    "    pgc.relname\n",
    "FROM pg_catalog.pg_namespace pgn\n",
    "    LEFT JOIN pg_catalog.pg_class pgc\n",
    "        ON pgc.relnamespace = pgn.oid\n",
    "WHERE pgc.relkind = 'r'\n",
    "--    AND c.relname OPERATOR(pg_catalog.~) '^(amzn_reviews_2_1)$'\n",
    "--    AND n.nspname OPERATOR(pg_catalog.~) '^(demo)$'\n",
    "    AND NULLIF(pg_catalog.pg_get_table_distributedby(pgc.OID), '') IS NOT NULL\n",
    "ORDER BY 2, 3;"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "%%sql\n",
    "SELECT\n",
    "    table_owner\n",
    "    , table_name\n",
    "--    , array_agg(COALESCE(distribution_keys,'DISTRIBUTED RANDOMLY')) distribution_keys\n",
    "    , array_agg(distribution_keys) distribution_keys\n",
    "FROM \n",
    "    (SELECT \n",
    "         pgn.nspname as table_owner\n",
    "         , pgc.relname as table_name\n",
    "         , pga.attname as distribution_keys\n",
    "     FROM \n",
    "         (\n",
    "            SELECT\n",
    "                gdp.localoid\n",
    "                , CASE WHEN ( ARRAY_UPPER(gdp.distkey, 1) > 0 ) THEN UNNEST(gdp.distkey) ELSE NULL END AS attnum\n",
    "             FROM\n",
    "                 gp_distribution_policy gdp\n",
    "         ) AS distrokey\n",
    "         INNER JOIN pg_class AS pgc\n",
    "             ON distrokey.localoid = pgc.oid\n",
    "         INNER JOIN pg_namespace pgn\n",
    "             ON pgc.relnamespace = pgn.oid\n",
    "         LEFT OUTER JOIN pg_attribute pga\n",
    "             ON distrokey.attnum = pga.attnum\n",
    "             AND distrokey.localoid = pga.attrelid\n",
    "ORDER BY pgn.nspname, pgc.relname) AS a\n",
    "GROUP BY table_owner, table_name"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get table distribution policy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "23 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>table_owner</th>\n",
       "        <th>table_name</th>\n",
       "        <th>distribution_keys</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>demo</td>\n",
       "        <td>amzn_reviews</td>\n",
       "        <td>DISTRIBUTED RANDOMLY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>demo</td>\n",
       "        <td>amzn_reviews_1</td>\n",
       "        <td>DISTRIBUTED RANDOMLY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>demo</td>\n",
       "        <td>amzn_reviews_1_1</td>\n",
       "        <td>DISTRIBUTED RANDOMLY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>demo</td>\n",
       "        <td>amzn_reviews_1_2</td>\n",
       "        <td>DISTRIBUTED RANDOMLY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>demo</td>\n",
       "        <td>amzn_reviews_2</td>\n",
       "        <td>DISTRIBUTED RANDOMLY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>demo</td>\n",
       "        <td>amzn_reviews_2_1</td>\n",
       "        <td>DISTRIBUTED RANDOMLY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>demo</td>\n",
       "        <td>amzn_reviews_3</td>\n",
       "        <td>DISTRIBUTED BY (customer_id,review_id)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>demo</td>\n",
       "        <td>amzn_reviews_3_1</td>\n",
       "        <td>DISTRIBUTED BY (customer_id,review_id)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>demo</td>\n",
       "        <td>amzn_reviews_summary</td>\n",
       "        <td>DISTRIBUTED RANDOMLY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>madlib</td>\n",
       "        <td>migrationhistory</td>\n",
       "        <td>DISTRIBUTED RANDOMLY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>public</td>\n",
       "        <td>test100</td>\n",
       "        <td>DISTRIBUTED RANDOMLY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>public</td>\n",
       "        <td>test1000</td>\n",
       "        <td>DISTRIBUTED RANDOMLY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>public</td>\n",
       "        <td>test1000_summary</td>\n",
       "        <td>DISTRIBUTED RANDOMLY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>public</td>\n",
       "        <td>test100_summary</td>\n",
       "        <td>DISTRIBUTED RANDOMLY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>public</td>\n",
       "        <td>test100k</td>\n",
       "        <td>DISTRIBUTED RANDOMLY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>public</td>\n",
       "        <td>test100k_summary</td>\n",
       "        <td>DISTRIBUTED RANDOMLY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>public</td>\n",
       "        <td>test100m</td>\n",
       "        <td>DISTRIBUTED RANDOMLY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>public</td>\n",
       "        <td>test10k</td>\n",
       "        <td>DISTRIBUTED RANDOMLY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>public</td>\n",
       "        <td>test10k_summary</td>\n",
       "        <td>DISTRIBUTED RANDOMLY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>public</td>\n",
       "        <td>test10m</td>\n",
       "        <td>DISTRIBUTED RANDOMLY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>public</td>\n",
       "        <td>test10m_summary</td>\n",
       "        <td>DISTRIBUTED RANDOMLY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>public</td>\n",
       "        <td>test1m</td>\n",
       "        <td>DISTRIBUTED RANDOMLY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>public</td>\n",
       "        <td>test1m_summary</td>\n",
       "        <td>DISTRIBUTED RANDOMLY</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('demo', 'amzn_reviews', 'DISTRIBUTED RANDOMLY'),\n",
       " ('demo', 'amzn_reviews_1', 'DISTRIBUTED RANDOMLY'),\n",
       " ('demo', 'amzn_reviews_1_1', 'DISTRIBUTED RANDOMLY'),\n",
       " ('demo', 'amzn_reviews_1_2', 'DISTRIBUTED RANDOMLY'),\n",
       " ('demo', 'amzn_reviews_2', 'DISTRIBUTED RANDOMLY'),\n",
       " ('demo', 'amzn_reviews_2_1', 'DISTRIBUTED RANDOMLY'),\n",
       " ('demo', 'amzn_reviews_3', 'DISTRIBUTED BY (customer_id,review_id)'),\n",
       " ('demo', 'amzn_reviews_3_1', 'DISTRIBUTED BY (customer_id,review_id)'),\n",
       " ('demo', 'amzn_reviews_summary', 'DISTRIBUTED RANDOMLY'),\n",
       " ('madlib', 'migrationhistory', 'DISTRIBUTED RANDOMLY'),\n",
       " ('public', 'test100', 'DISTRIBUTED RANDOMLY'),\n",
       " ('public', 'test1000', 'DISTRIBUTED RANDOMLY'),\n",
       " ('public', 'test1000_summary', 'DISTRIBUTED RANDOMLY'),\n",
       " ('public', 'test100_summary', 'DISTRIBUTED RANDOMLY'),\n",
       " ('public', 'test100k', 'DISTRIBUTED RANDOMLY'),\n",
       " ('public', 'test100k_summary', 'DISTRIBUTED RANDOMLY'),\n",
       " ('public', 'test100m', 'DISTRIBUTED RANDOMLY'),\n",
       " ('public', 'test10k', 'DISTRIBUTED RANDOMLY'),\n",
       " ('public', 'test10k_summary', 'DISTRIBUTED RANDOMLY'),\n",
       " ('public', 'test10m', 'DISTRIBUTED RANDOMLY'),\n",
       " ('public', 'test10m_summary', 'DISTRIBUTED RANDOMLY'),\n",
       " ('public', 'test1m', 'DISTRIBUTED RANDOMLY'),\n",
       " ('public', 'test1m_summary', 'DISTRIBUTED RANDOMLY')]"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT \n",
    "    pgn.nspname as table_owner\n",
    "    , pgc.relname as table_name\n",
    "    , CASE WHEN ARRAY_UPPER(ARRAY_AGG(pga.attname), 1) > 1\n",
    "        THEN 'DISTRIBUTED BY (' || ARRAY_TO_STRING(ARRAY_AGG(pga.attname), ',') || ')' \n",
    "        ELSE 'DISTRIBUTED RANDOMLY' END AS distribution_keys\n",
    "FROM\n",
    "    (\n",
    "        SELECT\n",
    "            gdp.localoid\n",
    "            , CASE WHEN ( ARRAY_UPPER(gdp.distkey, 1) > 0 ) THEN UNNEST(gdp.distkey) ELSE NULL END AS attnum\n",
    "        FROM \n",
    "            gp_distribution_policy gdp\n",
    "    ) AS distrokey\n",
    "    INNER JOIN pg_class AS pgc\n",
    "        ON distrokey.localoid = pgc.oid\n",
    "    INNER JOIN pg_namespace pgn\n",
    "        ON pgc.relnamespace = pgn.oid\n",
    "    LEFT OUTER JOIN pg_attribute pga\n",
    "        ON distrokey.attnum = pga.attnum\n",
    "        AND distrokey.localoid = pga.attrelid\n",
    "GROUP BY \n",
    "    pgn.nspname, pgc.relname;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get table partition levels"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "%%sql\n",
    "CREATE TABLE sales1 (id int, year int, qtr int, c_rank int, code char(1), region text)\n",
    "DISTRIBUTED BY (id)\n",
    "PARTITION BY LIST (code)\n",
    "( PARTITION sales VALUES ('S'),\n",
    "  PARTITION returns VALUES ('R')\n",
    ");\n",
    "\n",
    "CREATE TABLE sales2 (id int, year int, qtr int, c_rank int, code char(1), region text)\n",
    "DISTRIBUTED BY (id)\n",
    "PARTITION BY LIST (code)\n",
    "  SUBPARTITION BY RANGE (c_rank)\n",
    "    SUBPARTITION by LIST (region)\n",
    "\n",
    "( PARTITION sales VALUES ('S')\n",
    "   ( SUBPARTITION cr1 START (1) END (2)\n",
    "      ( SUBPARTITION ca VALUES ('CA') ), \n",
    "      SUBPARTITION cr2 START (3) END (4)\n",
    "        ( SUBPARTITION ca VALUES ('CA') ) ),\n",
    "\n",
    " PARTITION returns VALUES ('R')\n",
    "   ( SUBPARTITION cr1 START (1) END (2)\n",
    "      ( SUBPARTITION ca VALUES ('CA') ), \n",
    "     SUBPARTITION cr2 START (3) END (4)\n",
    "        ( SUBPARTITION ca VALUES ('CA') ) )\n",
    ");\n",
    "\n",
    "CREATE TABLE sales3 (id int, year int, qtr int, c_rank int, code char(1), region text)\n",
    "DISTRIBUTED BY (id)\n",
    "PARTITION BY LIST (code)\n",
    "\n",
    "   SUBPARTITION BY RANGE (c_rank)\n",
    "     SUBPARTITION TEMPLATE (\n",
    "     SUBPARTITION cr1 START (1) END (2),\n",
    "     SUBPARTITION cr2 START (3) END (4) )\n",
    "\n",
    "     SUBPARTITION BY LIST (region)\n",
    "       SUBPARTITION TEMPLATE (\n",
    "       SUBPARTITION ca VALUES ('CA') )\n",
    "\n",
    "( PARTITION sales VALUES ('S'),\n",
    "  PARTITION  returns VALUES ('R')\n",
    ") ;\n",
    "\n",
    "CREATE TABLE sales4 (id int, year int, qtr int, c_rank int, code char(1), region text)\n",
    "DISTRIBUTED BY (id)\n",
    "PARTITION BY RANGE (year)\n",
    "\n",
    "  SUBPARTITION BY RANGE (qtr)\n",
    "    SUBPARTITION TEMPLATE (\n",
    "    START (1) END (5) EVERY (1), \n",
    "    DEFAULT SUBPARTITION bad_qtr )\n",
    "\n",
    "    SUBPARTITION BY LIST (region)\n",
    "      SUBPARTITION TEMPLATE (\n",
    "      SUBPARTITION usa VALUES ('usa'),\n",
    "      SUBPARTITION europe VALUES ('europe'),\n",
    "      SUBPARTITION asia VALUES ('asia'),\n",
    "      DEFAULT SUBPARTITION other_regions)\n",
    "\n",
    "( START (2009) END (2011) EVERY (1),\n",
    "  DEFAULT PARTITION outlying_years);"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "%%sql\n",
    "SELECT tbl.*\n",
    "    , prt.*\n",
    "FROM \n",
    "    pg_stat_user_tables tbl\n",
    "    , pg_partitions prt\n",
    "WHERE \n",
    "    tbl.schemaname = prt.schemaname\n",
    "    AND tbl.relname = prt.tablename\n",
    "ORDER BY 1, 2, 3\n",
    "--LIMIT 25;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "25 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>tablename</th>\n",
       "        <th>partitionlevel</th>\n",
       "        <th>partitiontablename</th>\n",
       "        <th>partitionname</th>\n",
       "        <th>partitiontype</th>\n",
       "        <th>partitionboundary</th>\n",
       "        <th>partitionisdefault</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>demo.amzn_reviews_partitioned</td>\n",
       "        <td>1</td>\n",
       "        <td>demo.amzn_reviews_partitioned_1_prt_year1995</td>\n",
       "        <td>year1995</td>\n",
       "        <td>RANGE</td>\n",
       "        <td>PARTITION year1995 START (&#x27;1995-01-01&#x27;::date) END (&#x27;1996-01-01&#x27;::date) EVERY (&#x27;1 year&#x27;::interval)</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>demo.amzn_reviews_partitioned</td>\n",
       "        <td>1</td>\n",
       "        <td>demo.amzn_reviews_partitioned_1_prt_year1996</td>\n",
       "        <td>year1996</td>\n",
       "        <td>RANGE</td>\n",
       "        <td>PARTITION year1996 START (&#x27;1996-01-01&#x27;::date) END (&#x27;1997-01-01&#x27;::date) EVERY (&#x27;1 year&#x27;::interval)</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>demo.amzn_reviews_partitioned</td>\n",
       "        <td>1</td>\n",
       "        <td>demo.amzn_reviews_partitioned_1_prt_year1997</td>\n",
       "        <td>year1997</td>\n",
       "        <td>RANGE</td>\n",
       "        <td>PARTITION year1997 START (&#x27;1997-01-01&#x27;::date) END (&#x27;1998-01-01&#x27;::date) EVERY (&#x27;1 year&#x27;::interval)</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>demo.amzn_reviews_partitioned</td>\n",
       "        <td>1</td>\n",
       "        <td>demo.amzn_reviews_partitioned_1_prt_year1998</td>\n",
       "        <td>year1998</td>\n",
       "        <td>RANGE</td>\n",
       "        <td>PARTITION year1998 START (&#x27;1998-01-01&#x27;::date) END (&#x27;1999-01-01&#x27;::date) EVERY (&#x27;1 year&#x27;::interval)</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>demo.amzn_reviews_partitioned</td>\n",
       "        <td>1</td>\n",
       "        <td>demo.amzn_reviews_partitioned_1_prt_year1999</td>\n",
       "        <td>year1999</td>\n",
       "        <td>RANGE</td>\n",
       "        <td>PARTITION year1999 START (&#x27;1999-01-01&#x27;::date) END (&#x27;2000-01-01&#x27;::date) EVERY (&#x27;1 year&#x27;::interval)</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>demo.amzn_reviews_partitioned</td>\n",
       "        <td>1</td>\n",
       "        <td>demo.amzn_reviews_partitioned_1_prt_year2000</td>\n",
       "        <td>year2000</td>\n",
       "        <td>RANGE</td>\n",
       "        <td>PARTITION year2000 START (&#x27;2000-01-01&#x27;::date) END (&#x27;2001-01-01&#x27;::date) EVERY (&#x27;1 year&#x27;::interval)</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>demo.amzn_reviews_partitioned</td>\n",
       "        <td>1</td>\n",
       "        <td>demo.amzn_reviews_partitioned_1_prt_year2001</td>\n",
       "        <td>year2001</td>\n",
       "        <td>RANGE</td>\n",
       "        <td>PARTITION year2001 START (&#x27;2001-01-01&#x27;::date) END (&#x27;2002-01-01&#x27;::date) EVERY (&#x27;1 year&#x27;::interval)</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>demo.amzn_reviews_partitioned</td>\n",
       "        <td>1</td>\n",
       "        <td>demo.amzn_reviews_partitioned_1_prt_year2002</td>\n",
       "        <td>year2002</td>\n",
       "        <td>RANGE</td>\n",
       "        <td>PARTITION year2002 START (&#x27;2002-01-01&#x27;::date) END (&#x27;2003-01-01&#x27;::date) EVERY (&#x27;1 year&#x27;::interval)</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>demo.amzn_reviews_partitioned</td>\n",
       "        <td>1</td>\n",
       "        <td>demo.amzn_reviews_partitioned_1_prt_year2003</td>\n",
       "        <td>year2003</td>\n",
       "        <td>RANGE</td>\n",
       "        <td>PARTITION year2003 START (&#x27;2003-01-01&#x27;::date) END (&#x27;2004-01-01&#x27;::date) EVERY (&#x27;1 year&#x27;::interval)</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>demo.amzn_reviews_partitioned</td>\n",
       "        <td>1</td>\n",
       "        <td>demo.amzn_reviews_partitioned_1_prt_year2004</td>\n",
       "        <td>year2004</td>\n",
       "        <td>RANGE</td>\n",
       "        <td>PARTITION year2004 START (&#x27;2004-01-01&#x27;::date) END (&#x27;2005-01-01&#x27;::date) EVERY (&#x27;1 year&#x27;::interval)</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>demo.amzn_reviews_partitioned</td>\n",
       "        <td>1</td>\n",
       "        <td>demo.amzn_reviews_partitioned_1_prt_year2005</td>\n",
       "        <td>year2005</td>\n",
       "        <td>RANGE</td>\n",
       "        <td>PARTITION year2005 START (&#x27;2005-01-01&#x27;::date) END (&#x27;2006-01-01&#x27;::date) EVERY (&#x27;1 year&#x27;::interval)</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>demo.amzn_reviews_partitioned</td>\n",
       "        <td>1</td>\n",
       "        <td>demo.amzn_reviews_partitioned_1_prt_year2006</td>\n",
       "        <td>year2006</td>\n",
       "        <td>RANGE</td>\n",
       "        <td>PARTITION year2006 START (&#x27;2006-01-01&#x27;::date) END (&#x27;2007-01-01&#x27;::date) EVERY (&#x27;1 year&#x27;::interval)</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>demo.amzn_reviews_partitioned</td>\n",
       "        <td>1</td>\n",
       "        <td>demo.amzn_reviews_partitioned_1_prt_year2007</td>\n",
       "        <td>year2007</td>\n",
       "        <td>RANGE</td>\n",
       "        <td>PARTITION year2007 START (&#x27;2007-01-01&#x27;::date) END (&#x27;2008-01-01&#x27;::date) EVERY (&#x27;1 year&#x27;::interval)</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>demo.amzn_reviews_partitioned</td>\n",
       "        <td>1</td>\n",
       "        <td>demo.amzn_reviews_partitioned_1_prt_year2008</td>\n",
       "        <td>year2008</td>\n",
       "        <td>RANGE</td>\n",
       "        <td>PARTITION year2008 START (&#x27;2008-01-01&#x27;::date) END (&#x27;2009-01-01&#x27;::date) EVERY (&#x27;1 year&#x27;::interval)</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>demo.amzn_reviews_partitioned</td>\n",
       "        <td>1</td>\n",
       "        <td>demo.amzn_reviews_partitioned_1_prt_year2009</td>\n",
       "        <td>year2009</td>\n",
       "        <td>RANGE</td>\n",
       "        <td>PARTITION year2009 START (&#x27;2009-01-01&#x27;::date) END (&#x27;2010-01-01&#x27;::date) EVERY (&#x27;1 year&#x27;::interval)</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>demo.amzn_reviews_partitioned</td>\n",
       "        <td>1</td>\n",
       "        <td>demo.amzn_reviews_partitioned_1_prt_year2010</td>\n",
       "        <td>year2010</td>\n",
       "        <td>RANGE</td>\n",
       "        <td>PARTITION year2010 START (&#x27;2010-01-01&#x27;::date) END (&#x27;2011-01-01&#x27;::date) EVERY (&#x27;1 year&#x27;::interval)</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>demo.amzn_reviews_partitioned</td>\n",
       "        <td>1</td>\n",
       "        <td>demo.amzn_reviews_partitioned_1_prt_year2011</td>\n",
       "        <td>year2011</td>\n",
       "        <td>RANGE</td>\n",
       "        <td>PARTITION year2011 START (&#x27;2011-01-01&#x27;::date) END (&#x27;2012-01-01&#x27;::date) EVERY (&#x27;1 year&#x27;::interval)</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>demo.amzn_reviews_partitioned</td>\n",
       "        <td>1</td>\n",
       "        <td>demo.amzn_reviews_partitioned_1_prt_year2012</td>\n",
       "        <td>year2012</td>\n",
       "        <td>RANGE</td>\n",
       "        <td>PARTITION year2012 START (&#x27;2012-01-01&#x27;::date) END (&#x27;2013-01-01&#x27;::date) EVERY (&#x27;1 year&#x27;::interval)</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>demo.amzn_reviews_partitioned</td>\n",
       "        <td>1</td>\n",
       "        <td>demo.amzn_reviews_partitioned_1_prt_year2013</td>\n",
       "        <td>year2013</td>\n",
       "        <td>RANGE</td>\n",
       "        <td>PARTITION year2013 START (&#x27;2013-01-01&#x27;::date) END (&#x27;2014-01-01&#x27;::date) EVERY (&#x27;1 year&#x27;::interval)</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>demo.amzn_reviews_partitioned</td>\n",
       "        <td>1</td>\n",
       "        <td>demo.amzn_reviews_partitioned_1_prt_year2014</td>\n",
       "        <td>year2014</td>\n",
       "        <td>RANGE</td>\n",
       "        <td>PARTITION year2014 START (&#x27;2014-01-01&#x27;::date) END (&#x27;2015-01-01&#x27;::date) EVERY (&#x27;1 year&#x27;::interval)</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>demo.amzn_reviews_partitioned</td>\n",
       "        <td>1</td>\n",
       "        <td>demo.amzn_reviews_partitioned_1_prt_year2015</td>\n",
       "        <td>year2015</td>\n",
       "        <td>RANGE</td>\n",
       "        <td>PARTITION year2015 START (&#x27;2015-01-01&#x27;::date) END (&#x27;2015-12-31&#x27;::date) INCLUSIVE EVERY (&#x27;1 year&#x27;::interval)</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>public.sales1</td>\n",
       "        <td>1</td>\n",
       "        <td>public.sales1_1_prt_returns</td>\n",
       "        <td>returns</td>\n",
       "        <td>LIST</td>\n",
       "        <td>PARTITION returns VALUES(&#x27;R&#x27;)</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>public.sales1</td>\n",
       "        <td>1</td>\n",
       "        <td>public.sales1_1_prt_sales</td>\n",
       "        <td>sales</td>\n",
       "        <td>LIST</td>\n",
       "        <td>PARTITION sales VALUES(&#x27;S&#x27;)</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>public.sales2</td>\n",
       "        <td>1</td>\n",
       "        <td>public.sales2_1_prt_returns</td>\n",
       "        <td>returns</td>\n",
       "        <td>LIST</td>\n",
       "        <td>PARTITION returns VALUES(&#x27;R&#x27;)</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>public.sales2</td>\n",
       "        <td>1</td>\n",
       "        <td>public.sales2_1_prt_sales</td>\n",
       "        <td>sales</td>\n",
       "        <td>LIST</td>\n",
       "        <td>PARTITION sales VALUES(&#x27;S&#x27;)</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('demo.amzn_reviews_partitioned', 1, 'demo.amzn_reviews_partitioned_1_prt_year1995', 'year1995', 'RANGE', \"PARTITION year1995 START ('1995-01-01'::date) END ('1996-01-01'::date) EVERY ('1 year'::interval)\", False),\n",
       " ('demo.amzn_reviews_partitioned', 1, 'demo.amzn_reviews_partitioned_1_prt_year1996', 'year1996', 'RANGE', \"PARTITION year1996 START ('1996-01-01'::date) END ('1997-01-01'::date) EVERY ('1 year'::interval)\", False),\n",
       " ('demo.amzn_reviews_partitioned', 1, 'demo.amzn_reviews_partitioned_1_prt_year1997', 'year1997', 'RANGE', \"PARTITION year1997 START ('1997-01-01'::date) END ('1998-01-01'::date) EVERY ('1 year'::interval)\", False),\n",
       " ('demo.amzn_reviews_partitioned', 1, 'demo.amzn_reviews_partitioned_1_prt_year1998', 'year1998', 'RANGE', \"PARTITION year1998 START ('1998-01-01'::date) END ('1999-01-01'::date) EVERY ('1 year'::interval)\", False),\n",
       " ('demo.amzn_reviews_partitioned', 1, 'demo.amzn_reviews_partitioned_1_prt_year1999', 'year1999', 'RANGE', \"PARTITION year1999 START ('1999-01-01'::date) END ('2000-01-01'::date) EVERY ('1 year'::interval)\", False),\n",
       " ('demo.amzn_reviews_partitioned', 1, 'demo.amzn_reviews_partitioned_1_prt_year2000', 'year2000', 'RANGE', \"PARTITION year2000 START ('2000-01-01'::date) END ('2001-01-01'::date) EVERY ('1 year'::interval)\", False),\n",
       " ('demo.amzn_reviews_partitioned', 1, 'demo.amzn_reviews_partitioned_1_prt_year2001', 'year2001', 'RANGE', \"PARTITION year2001 START ('2001-01-01'::date) END ('2002-01-01'::date) EVERY ('1 year'::interval)\", False),\n",
       " ('demo.amzn_reviews_partitioned', 1, 'demo.amzn_reviews_partitioned_1_prt_year2002', 'year2002', 'RANGE', \"PARTITION year2002 START ('2002-01-01'::date) END ('2003-01-01'::date) EVERY ('1 year'::interval)\", False),\n",
       " ('demo.amzn_reviews_partitioned', 1, 'demo.amzn_reviews_partitioned_1_prt_year2003', 'year2003', 'RANGE', \"PARTITION year2003 START ('2003-01-01'::date) END ('2004-01-01'::date) EVERY ('1 year'::interval)\", False),\n",
       " ('demo.amzn_reviews_partitioned', 1, 'demo.amzn_reviews_partitioned_1_prt_year2004', 'year2004', 'RANGE', \"PARTITION year2004 START ('2004-01-01'::date) END ('2005-01-01'::date) EVERY ('1 year'::interval)\", False),\n",
       " ('demo.amzn_reviews_partitioned', 1, 'demo.amzn_reviews_partitioned_1_prt_year2005', 'year2005', 'RANGE', \"PARTITION year2005 START ('2005-01-01'::date) END ('2006-01-01'::date) EVERY ('1 year'::interval)\", False),\n",
       " ('demo.amzn_reviews_partitioned', 1, 'demo.amzn_reviews_partitioned_1_prt_year2006', 'year2006', 'RANGE', \"PARTITION year2006 START ('2006-01-01'::date) END ('2007-01-01'::date) EVERY ('1 year'::interval)\", False),\n",
       " ('demo.amzn_reviews_partitioned', 1, 'demo.amzn_reviews_partitioned_1_prt_year2007', 'year2007', 'RANGE', \"PARTITION year2007 START ('2007-01-01'::date) END ('2008-01-01'::date) EVERY ('1 year'::interval)\", False),\n",
       " ('demo.amzn_reviews_partitioned', 1, 'demo.amzn_reviews_partitioned_1_prt_year2008', 'year2008', 'RANGE', \"PARTITION year2008 START ('2008-01-01'::date) END ('2009-01-01'::date) EVERY ('1 year'::interval)\", False),\n",
       " ('demo.amzn_reviews_partitioned', 1, 'demo.amzn_reviews_partitioned_1_prt_year2009', 'year2009', 'RANGE', \"PARTITION year2009 START ('2009-01-01'::date) END ('2010-01-01'::date) EVERY ('1 year'::interval)\", False),\n",
       " ('demo.amzn_reviews_partitioned', 1, 'demo.amzn_reviews_partitioned_1_prt_year2010', 'year2010', 'RANGE', \"PARTITION year2010 START ('2010-01-01'::date) END ('2011-01-01'::date) EVERY ('1 year'::interval)\", False),\n",
       " ('demo.amzn_reviews_partitioned', 1, 'demo.amzn_reviews_partitioned_1_prt_year2011', 'year2011', 'RANGE', \"PARTITION year2011 START ('2011-01-01'::date) END ('2012-01-01'::date) EVERY ('1 year'::interval)\", False),\n",
       " ('demo.amzn_reviews_partitioned', 1, 'demo.amzn_reviews_partitioned_1_prt_year2012', 'year2012', 'RANGE', \"PARTITION year2012 START ('2012-01-01'::date) END ('2013-01-01'::date) EVERY ('1 year'::interval)\", False),\n",
       " ('demo.amzn_reviews_partitioned', 1, 'demo.amzn_reviews_partitioned_1_prt_year2013', 'year2013', 'RANGE', \"PARTITION year2013 START ('2013-01-01'::date) END ('2014-01-01'::date) EVERY ('1 year'::interval)\", False),\n",
       " ('demo.amzn_reviews_partitioned', 1, 'demo.amzn_reviews_partitioned_1_prt_year2014', 'year2014', 'RANGE', \"PARTITION year2014 START ('2014-01-01'::date) END ('2015-01-01'::date) EVERY ('1 year'::interval)\", False),\n",
       " ('demo.amzn_reviews_partitioned', 1, 'demo.amzn_reviews_partitioned_1_prt_year2015', 'year2015', 'RANGE', \"PARTITION year2015 START ('2015-01-01'::date) END ('2015-12-31'::date) INCLUSIVE EVERY ('1 year'::interval)\", False),\n",
       " ('public.sales1', 1, 'public.sales1_1_prt_returns', 'returns', 'LIST', \"PARTITION returns VALUES('R')\", False),\n",
       " ('public.sales1', 1, 'public.sales1_1_prt_sales', 'sales', 'LIST', \"PARTITION sales VALUES('S')\", False),\n",
       " ('public.sales2', 1, 'public.sales2_1_prt_returns', 'returns', 'LIST', \"PARTITION returns VALUES('R')\", False),\n",
       " ('public.sales2', 1, 'public.sales2_1_prt_sales', 'sales', 'LIST', \"PARTITION sales VALUES('S')\", False)]"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT\n",
    "    prt.schemaname || '.' || prt.tablename AS tablename\n",
    "    , prt.partitionlevel + 1 AS partitionlevel\n",
    "--    , COALESCE(prt.partitionrank, 1) AS partitionrank\n",
    "    , prt.partitionschemaname || '.' || prt.partitiontablename AS partitiontablename\n",
    "    , prt.partitionname\n",
    "    , UPPER(prt.partitiontype) AS partitiontype\n",
    "    , prt.partitionboundary\n",
    "    , prt.partitionisdefault\n",
    "FROM pg_stat_user_tables tbl, \n",
    "    pg_partitions prt\n",
    "WHERE \n",
    "    tbl.schemaname = prt.schemaname\n",
    "    AND tbl.relname = prt.tablename\n",
    "GROUP BY\n",
    "    prt.schemaname, prt.tablename, prt.partitionlevel, prt.partitionrank, prt.partitionschemaname, prt.partitiontablename\n",
    "    , prt.partitionname, prt.partitiontype, prt.partitionboundary, prt.partitionisdefault\n",
    "ORDER BY \n",
    "    prt.schemaname, prt.tablename, prt.partitionlevel, prt.partitionrank\n",
    "LIMIT 25;"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "%%sql\n",
    "TRUNCATE demo.amzn_reviews_by_marketplace;\n",
    "INSERT INTO demo.amzn_reviews_by_marketplace \n",
    "SELECT * FROM demo.amzn_reviews_1_1; "
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "%%sql\n",
    "TRUNCATE demo.amzn_reviews_partitioned;\n",
    "INSERT INTO demo.amzn_reviews_partitioned \n",
    "SELECT * FROM demo.amzn_reviews_1_1; "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "%%sql\n",
    "SELECT * FROM gp_distribution_policy;"
   ]
  }
 ],
 "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
}