de-partition-training.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "9129cdcb",
   "metadata": {},
   "outputs": [],
   "source": [
    "import configparser\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "10b493f3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['clustertab.config']"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "config = configparser.ConfigParser()\n",
    "config.read('clustertab.config')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "a471cc22",
   "metadata": {},
   "outputs": [],
   "source": [
    "db = 'partition_training'\n",
    "user = config['POSTGRES']['PG_UNAME']\n",
    "passwd = config['POSTGRES']['PG_PASS']\n",
    "port = config['POSTGRES']['PG_PORT']\n",
    "host = config['POSTGRES']['PG_HOST']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "4216af6b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'partition_training'"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "623308f3",
   "metadata": {},
   "outputs": [],
   "source": [
    "credentials = \"postgresql://{}:{}@{}:{}/{}\".format(user,passwd,host,port,db)\n",
    "\n",
    "#using psycopg2 to test connection since there are no tables\n",
    "import psycopg2\n",
    "try:\n",
    "    conn = psycopg2.connect(host=host,dbname=db,user=user,password=passwd,port=port)\n",
    "except Exception as e:\n",
    "    print(e)\n",
    "    \n",
    "conn.set_session(autocommit=True)\n",
    "\n",
    "try:\n",
    "    cur = conn.cursor()\n",
    "    \n",
    "except:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "6846fc00",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'postgresql://postgres:1234@172.17.0.2:5432/partition_training'"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "credentials"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "77f47076",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Helper functions to work with the database\n",
    "def schemaGen(dataframe, schemaName):\n",
    "    localSchema = pd.io.sql.get_schema(dataframe,schemaName)\n",
    "    localSchema = localSchema.replace('TEXT','VARCHAR(255)').replace('INTEGER','NUMERIC').replace('\\n','').replace('\"',\"\")\n",
    "    return \"\".join(localSchema)\n",
    "\n",
    "#Using pandas read_sql for getting schema\n",
    "def getSchema(tableName, credentials):\n",
    "    schema = pd.read_sql(\"\"\"SELECT * FROM information_schema.columns where table_name='{}'\"\"\".format(tableName),con=credentials)\n",
    "    return schema\n",
    "\n",
    "#Issue is in using pd.read_sql to write data to the database. so using psycopg2\n",
    "def queryTable(query):\n",
    "    try:\n",
    "        schema = cur.execute(query)\n",
    "        return \n",
    "    except Exception as e:\n",
    "        print(e)\n",
    "        \n",
    "#This doesn't return anything\n",
    "\n",
    "#Using the pd.read_sql for getting data from db\n",
    "def queryBase(query):\n",
    "    requiredTable = pd.read_sql(query,con=credentials)\n",
    "    return requiredTable\n",
    "\n",
    "#This returns the dataframe"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dcdb2e31",
   "metadata": {},
   "source": [
    "### Purpose of Partitioning\n",
    "\n",
    "- Main purpose is to get performance benefits by segregating the data into partitions\n",
    "\n",
    "- Managing the partitions individually is much easier when deleting or moving the partitions\n",
    "\n",
    "- When reporting are sending the data for analysis down the pipe-line, it is simpler to refer to a particular partition. (In the exercises there is a scenario to find the order details in Jan month. In such scenarios, the partitioning by Range will help) \n",
    "\n",
    "These partitions are created during the Table creation itsels. So this is a Data Definition Task.\n",
    "\n",
    "Before the exercise is started, a seperate Database is created. Then all the activities are done in that database to avoid confusion\n",
    "\n",
    "We will be working on all three types of List, Range and Hash Partitions here. The flow is always the creation, management and manipulating data for each of the partitions. \n",
    "\n",
    "This activity provides the insight into how glue and later parquet files work. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "7ebc0db8",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"CREATE TABLE users_part (\n",
    "    user_id SERIAL,\n",
    "    user_first_name VARCHAR(30) NOT NULL,\n",
    "    user_last_name VARCHAR(30) NOT NULL,\n",
    "    user_email_id VARCHAR(50) NOT NULL,\n",
    "    user_email_validated BOOLEAN DEFAULT FALSE,\n",
    "    user_password VARCHAR(200),\n",
    "    user_role VARCHAR(1) NOT NULL DEFAULT 'U', --U and A\n",
    "    is_active BOOLEAN DEFAULT FALSE,\n",
    "    created_dt DATE DEFAULT CURRENT_DATE,\n",
    "    last_updated_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n",
    "    PRIMARY KEY (user_role, user_id)\n",
    ") PARTITION BY LIST(user_role)\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "e86ed2d4",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Why create this?\n",
    "queryTable(\"\"\"CREATE INDEX users_part_email_id_idx \n",
    "    ON users_part(user_email_id)\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "d0626f9e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "no partition of relation \"users_part\" found for row\n",
      "DETAIL:  Partition key of the failing row contains (user_role) = (U).\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Fail as the data contains No partition information\n",
    "queryTable(\"\"\"INSERT INTO users_part (user_first_name, user_last_name, user_email_id)\n",
    "VALUES \n",
    "    ('Scott', 'Tiger', 'scott@tiger.com'),\n",
    "    ('Donald', 'Duck', 'donald@duck.com'),\n",
    "    ('Mickey', 'Mouse', 'mickey@mouse.com')\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "26b176d5",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"CREATE TABLE users_part_default \n",
    "                PARTITION OF users_part DEFAULT\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7b638d9d",
   "metadata": {},
   "source": [
    "Once the partition is initiated, the same can be listed using \\d+ in the server prompt.\n",
    "\n",
    "Also the number of partitions start increasing under the table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "3df531d6",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"INSERT INTO users_part (user_first_name, user_last_name, user_email_id, user_role)\n",
    "VALUES \n",
    "    ('Scott', 'Tiger', 'scott@tiger.com', 'U'),\n",
    "    ('Donald', 'Duck', 'donald@duck.com', 'U'),\n",
    "    ('Mickey', 'Mouse', 'mickey@mouse.com', 'U')\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "5f1c4385",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>user_first_name</th>\n",
       "      <th>user_last_name</th>\n",
       "      <th>user_email_id</th>\n",
       "      <th>user_email_validated</th>\n",
       "      <th>user_password</th>\n",
       "      <th>user_role</th>\n",
       "      <th>is_active</th>\n",
       "      <th>created_dt</th>\n",
       "      <th>last_updated_ts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>Scott</td>\n",
       "      <td>Tiger</td>\n",
       "      <td>scott@tiger.com</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>U</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-11-13</td>\n",
       "      <td>2022-11-13 02:05:47.377391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>Donald</td>\n",
       "      <td>Duck</td>\n",
       "      <td>donald@duck.com</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>U</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-11-13</td>\n",
       "      <td>2022-11-13 02:05:47.377391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>Mickey</td>\n",
       "      <td>Mouse</td>\n",
       "      <td>mickey@mouse.com</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>U</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-11-13</td>\n",
       "      <td>2022-11-13 02:05:47.377391</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id user_first_name user_last_name     user_email_id  \\\n",
       "0        2           Scott          Tiger   scott@tiger.com   \n",
       "1        3          Donald           Duck   donald@duck.com   \n",
       "2        4          Mickey          Mouse  mickey@mouse.com   \n",
       "\n",
       "   user_email_validated user_password user_role  is_active  created_dt  \\\n",
       "0                 False          None         U      False  2022-11-13   \n",
       "1                 False          None         U      False  2022-11-13   \n",
       "2                 False          None         U      False  2022-11-13   \n",
       "\n",
       "             last_updated_ts  \n",
       "0 2022-11-13 02:05:47.377391  \n",
       "1 2022-11-13 02:05:47.377391  \n",
       "2 2022-11-13 02:05:47.377391  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT * FROM users_part\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "d7de7672",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>user_first_name</th>\n",
       "      <th>user_last_name</th>\n",
       "      <th>user_email_id</th>\n",
       "      <th>user_email_validated</th>\n",
       "      <th>user_password</th>\n",
       "      <th>user_role</th>\n",
       "      <th>is_active</th>\n",
       "      <th>created_dt</th>\n",
       "      <th>last_updated_ts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>Scott</td>\n",
       "      <td>Tiger</td>\n",
       "      <td>scott@tiger.com</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>U</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-11-13</td>\n",
       "      <td>2022-11-13 02:05:47.377391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>Donald</td>\n",
       "      <td>Duck</td>\n",
       "      <td>donald@duck.com</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>U</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-11-13</td>\n",
       "      <td>2022-11-13 02:05:47.377391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>Mickey</td>\n",
       "      <td>Mouse</td>\n",
       "      <td>mickey@mouse.com</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>U</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-11-13</td>\n",
       "      <td>2022-11-13 02:05:47.377391</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id user_first_name user_last_name     user_email_id  \\\n",
       "0        2           Scott          Tiger   scott@tiger.com   \n",
       "1        3          Donald           Duck   donald@duck.com   \n",
       "2        4          Mickey          Mouse  mickey@mouse.com   \n",
       "\n",
       "   user_email_validated user_password user_role  is_active  created_dt  \\\n",
       "0                 False          None         U      False  2022-11-13   \n",
       "1                 False          None         U      False  2022-11-13   \n",
       "2                 False          None         U      False  2022-11-13   \n",
       "\n",
       "             last_updated_ts  \n",
       "0 2022-11-13 02:05:47.377391  \n",
       "1 2022-11-13 02:05:47.377391  \n",
       "2 2022-11-13 02:05:47.377391  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#At this moment there seems to be no difference between the tables. It will soon change\n",
    "queryBase(\"\"\"SELECT * FROM users_part_default\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "5a33a235",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"CREATE TABLE users_part_a \n",
    "                PARTITION OF users_part  \n",
    "                    FOR VALUES IN ('A')\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "f77376f6",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"UPDATE users_part\n",
    "                SET user_role = 'A'\n",
    "                    WHERE user_email_id = 'scott@tiger.com'\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "febec44b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>user_first_name</th>\n",
       "      <th>user_last_name</th>\n",
       "      <th>user_email_id</th>\n",
       "      <th>user_email_validated</th>\n",
       "      <th>user_password</th>\n",
       "      <th>user_role</th>\n",
       "      <th>is_active</th>\n",
       "      <th>created_dt</th>\n",
       "      <th>last_updated_ts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>Scott</td>\n",
       "      <td>Tiger</td>\n",
       "      <td>scott@tiger.com</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-11-13</td>\n",
       "      <td>2022-11-13 02:05:47.377391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>Donald</td>\n",
       "      <td>Duck</td>\n",
       "      <td>donald@duck.com</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>U</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-11-13</td>\n",
       "      <td>2022-11-13 02:05:47.377391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>Mickey</td>\n",
       "      <td>Mouse</td>\n",
       "      <td>mickey@mouse.com</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>U</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-11-13</td>\n",
       "      <td>2022-11-13 02:05:47.377391</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id user_first_name user_last_name     user_email_id  \\\n",
       "0        2           Scott          Tiger   scott@tiger.com   \n",
       "1        3          Donald           Duck   donald@duck.com   \n",
       "2        4          Mickey          Mouse  mickey@mouse.com   \n",
       "\n",
       "   user_email_validated user_password user_role  is_active  created_dt  \\\n",
       "0                 False          None         A      False  2022-11-13   \n",
       "1                 False          None         U      False  2022-11-13   \n",
       "2                 False          None         U      False  2022-11-13   \n",
       "\n",
       "             last_updated_ts  \n",
       "0 2022-11-13 02:05:47.377391  \n",
       "1 2022-11-13 02:05:47.377391  \n",
       "2 2022-11-13 02:05:47.377391  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT * FROM users_part\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "c3caddec",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>user_first_name</th>\n",
       "      <th>user_last_name</th>\n",
       "      <th>user_email_id</th>\n",
       "      <th>user_email_validated</th>\n",
       "      <th>user_password</th>\n",
       "      <th>user_role</th>\n",
       "      <th>is_active</th>\n",
       "      <th>created_dt</th>\n",
       "      <th>last_updated_ts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>Scott</td>\n",
       "      <td>Tiger</td>\n",
       "      <td>scott@tiger.com</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-11-13</td>\n",
       "      <td>2022-11-13 02:05:47.377391</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id user_first_name user_last_name    user_email_id  \\\n",
       "0        2           Scott          Tiger  scott@tiger.com   \n",
       "\n",
       "   user_email_validated user_password user_role  is_active  created_dt  \\\n",
       "0                 False          None         A      False  2022-11-13   \n",
       "\n",
       "             last_updated_ts  \n",
       "0 2022-11-13 02:05:47.377391  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#The data point update has created the below data insertion into users_part_a\n",
    "queryBase(\"\"\"SELECT * FROM users_part_a\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "af958a31",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>user_first_name</th>\n",
       "      <th>user_last_name</th>\n",
       "      <th>user_email_id</th>\n",
       "      <th>user_email_validated</th>\n",
       "      <th>user_password</th>\n",
       "      <th>user_role</th>\n",
       "      <th>is_active</th>\n",
       "      <th>created_dt</th>\n",
       "      <th>last_updated_ts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>Donald</td>\n",
       "      <td>Duck</td>\n",
       "      <td>donald@duck.com</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>U</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-11-13</td>\n",
       "      <td>2022-11-13 02:05:47.377391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>Mickey</td>\n",
       "      <td>Mouse</td>\n",
       "      <td>mickey@mouse.com</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>U</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-11-13</td>\n",
       "      <td>2022-11-13 02:05:47.377391</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id user_first_name user_last_name     user_email_id  \\\n",
       "0        3          Donald           Duck   donald@duck.com   \n",
       "1        4          Mickey          Mouse  mickey@mouse.com   \n",
       "\n",
       "   user_email_validated user_password user_role  is_active  created_dt  \\\n",
       "0                 False          None         U      False  2022-11-13   \n",
       "1                 False          None         U      False  2022-11-13   \n",
       "\n",
       "             last_updated_ts  \n",
       "0 2022-11-13 02:05:47.377391  \n",
       "1 2022-11-13 02:05:47.377391  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#The data from users_part_default has moved directly to users_part_a. Only the data point was updated\n",
    "queryBase(\"\"\"SELECT * FROM users_part_default\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "fd61fd80",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "updated partition constraint for default partition \"users_part_default\" would be violated by some row\n",
      "\n"
     ]
    }
   ],
   "source": [
    "#The \"U\" values are currently considered default\n",
    "queryTable(\"\"\"CREATE TABLE users_part_u \n",
    "                    PARTITION OF users_part  \n",
    "                        FOR VALUES IN ('U')\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "53fe5a57",
   "metadata": {},
   "outputs": [],
   "source": [
    "#detaching partitions\n",
    "queryTable(\"\"\"ALTER TABLE users_part\n",
    "                    DETACH PARTITION users_part_default\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "ab20d7f8",
   "metadata": {},
   "outputs": [],
   "source": [
    "#The \"U\" values can now be moved into new partition\n",
    "queryTable(\"\"\"CREATE TABLE users_part_u \n",
    "                    PARTITION OF users_part  \n",
    "                        FOR VALUES IN ('U')\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "00bd56e1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>user_first_name</th>\n",
       "      <th>user_last_name</th>\n",
       "      <th>user_email_id</th>\n",
       "      <th>user_email_validated</th>\n",
       "      <th>user_password</th>\n",
       "      <th>user_role</th>\n",
       "      <th>is_active</th>\n",
       "      <th>created_dt</th>\n",
       "      <th>last_updated_ts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [user_id, user_first_name, user_last_name, user_email_id, user_email_validated, user_password, user_role, is_active, created_dt, last_updated_ts]\n",
       "Index: []"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#The data from users_part_default is not moved into users_part_u\n",
    "queryBase(\"\"\"SELECT * FROM users_part_u\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "be075ce1",
   "metadata": {},
   "outputs": [],
   "source": [
    "#The data from users_part_default is manually inserted into \n",
    "queryTable(\"\"\"INSERT INTO users_part_u\n",
    "                SELECT * FROM users_part_default\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "710d4cd6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>user_first_name</th>\n",
       "      <th>user_last_name</th>\n",
       "      <th>user_email_id</th>\n",
       "      <th>user_email_validated</th>\n",
       "      <th>user_password</th>\n",
       "      <th>user_role</th>\n",
       "      <th>is_active</th>\n",
       "      <th>created_dt</th>\n",
       "      <th>last_updated_ts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>Donald</td>\n",
       "      <td>Duck</td>\n",
       "      <td>donald@duck.com</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>U</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-11-13</td>\n",
       "      <td>2022-11-13 02:05:47.377391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>Mickey</td>\n",
       "      <td>Mouse</td>\n",
       "      <td>mickey@mouse.com</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>U</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-11-13</td>\n",
       "      <td>2022-11-13 02:05:47.377391</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id user_first_name user_last_name     user_email_id  \\\n",
       "0        3          Donald           Duck   donald@duck.com   \n",
       "1        4          Mickey          Mouse  mickey@mouse.com   \n",
       "\n",
       "   user_email_validated user_password user_role  is_active  created_dt  \\\n",
       "0                 False          None         U      False  2022-11-13   \n",
       "1                 False          None         U      False  2022-11-13   \n",
       "\n",
       "             last_updated_ts  \n",
       "0 2022-11-13 02:05:47.377391  \n",
       "1 2022-11-13 02:05:47.377391  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT * FROM users_part_u\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "150e7828",
   "metadata": {},
   "outputs": [],
   "source": [
    "#The data from users_part_default is manually inserted into \n",
    "queryTable(\"\"\"DROP TABLE users_part_default\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "f7ba753c",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"CREATE TABLE users_part_default\n",
    "                    PARTITION OF users_part DEFAULT\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "18861282",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"INSERT INTO users_part (user_first_name, user_last_name, user_email_id)\n",
    "VALUES \n",
    "    ('tosca', 'Major', 'tosca@gmail.com'),\n",
    "    ('Aki', 'Kimbo', 'kimbo@acimbo.com')\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "07887bcd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>user_first_name</th>\n",
       "      <th>user_last_name</th>\n",
       "      <th>user_email_id</th>\n",
       "      <th>user_email_validated</th>\n",
       "      <th>user_password</th>\n",
       "      <th>user_role</th>\n",
       "      <th>is_active</th>\n",
       "      <th>created_dt</th>\n",
       "      <th>last_updated_ts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>Scott</td>\n",
       "      <td>Tiger</td>\n",
       "      <td>scott@tiger.com</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-11-13</td>\n",
       "      <td>2022-11-13 02:05:47.377391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>Donald</td>\n",
       "      <td>Duck</td>\n",
       "      <td>donald@duck.com</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>U</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-11-13</td>\n",
       "      <td>2022-11-13 02:05:47.377391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>Mickey</td>\n",
       "      <td>Mouse</td>\n",
       "      <td>mickey@mouse.com</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>U</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-11-13</td>\n",
       "      <td>2022-11-13 02:05:47.377391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>tosca</td>\n",
       "      <td>Major</td>\n",
       "      <td>tosca@gmail.com</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>U</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-11-13</td>\n",
       "      <td>2022-11-13 02:24:55.698886</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6</td>\n",
       "      <td>Aki</td>\n",
       "      <td>Kimbo</td>\n",
       "      <td>kimbo@acimbo.com</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>U</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-11-13</td>\n",
       "      <td>2022-11-13 02:24:55.698886</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id user_first_name user_last_name     user_email_id  \\\n",
       "0        2           Scott          Tiger   scott@tiger.com   \n",
       "1        3          Donald           Duck   donald@duck.com   \n",
       "2        4          Mickey          Mouse  mickey@mouse.com   \n",
       "3        5           tosca          Major   tosca@gmail.com   \n",
       "4        6             Aki          Kimbo  kimbo@acimbo.com   \n",
       "\n",
       "   user_email_validated user_password user_role  is_active  created_dt  \\\n",
       "0                 False          None         A      False  2022-11-13   \n",
       "1                 False          None         U      False  2022-11-13   \n",
       "2                 False          None         U      False  2022-11-13   \n",
       "3                 False          None         U      False  2022-11-13   \n",
       "4                 False          None         U      False  2022-11-13   \n",
       "\n",
       "             last_updated_ts  \n",
       "0 2022-11-13 02:05:47.377391  \n",
       "1 2022-11-13 02:05:47.377391  \n",
       "2 2022-11-13 02:05:47.377391  \n",
       "3 2022-11-13 02:24:55.698886  \n",
       "4 2022-11-13 02:24:55.698886  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#There is default setting in DDL of the users_part, so the above data points have 'U'\n",
    "queryBase(\"\"\"SELECT * FROM users_part\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "6578055e",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"INSERT INTO users_part (user_first_name, user_last_name, user_email_id,user_role)\n",
    "VALUES \n",
    "    ('kili', 'manjaror', 'kimi@kiliman.com','N')\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "bf303399",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>user_first_name</th>\n",
       "      <th>user_last_name</th>\n",
       "      <th>user_email_id</th>\n",
       "      <th>user_email_validated</th>\n",
       "      <th>user_password</th>\n",
       "      <th>user_role</th>\n",
       "      <th>is_active</th>\n",
       "      <th>created_dt</th>\n",
       "      <th>last_updated_ts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7</td>\n",
       "      <td>kili</td>\n",
       "      <td>manjaror</td>\n",
       "      <td>kimi@kiliman.com</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>N</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-11-13</td>\n",
       "      <td>2022-11-13 02:30:09.045313</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id user_first_name user_last_name     user_email_id  \\\n",
       "0        7            kili       manjaror  kimi@kiliman.com   \n",
       "\n",
       "   user_email_validated user_password user_role  is_active  created_dt  \\\n",
       "0                 False          None         N      False  2022-11-13   \n",
       "\n",
       "             last_updated_ts  \n",
       "0 2022-11-13 02:30:09.045313  "
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT * FROM users_part_default\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "510a16c5",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"DELETE FROM users_part WHERE user_email_id = 'donald@duck.com'\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "cb08a240",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>user_first_name</th>\n",
       "      <th>user_last_name</th>\n",
       "      <th>user_email_id</th>\n",
       "      <th>user_email_validated</th>\n",
       "      <th>user_password</th>\n",
       "      <th>user_role</th>\n",
       "      <th>is_active</th>\n",
       "      <th>created_dt</th>\n",
       "      <th>last_updated_ts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>Scott</td>\n",
       "      <td>Tiger</td>\n",
       "      <td>scott@tiger.com</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-11-13</td>\n",
       "      <td>2022-11-13 02:05:47.377391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>Mickey</td>\n",
       "      <td>Mouse</td>\n",
       "      <td>mickey@mouse.com</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>U</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-11-13</td>\n",
       "      <td>2022-11-13 02:05:47.377391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5</td>\n",
       "      <td>tosca</td>\n",
       "      <td>Major</td>\n",
       "      <td>tosca@gmail.com</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>U</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-11-13</td>\n",
       "      <td>2022-11-13 02:24:55.698886</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6</td>\n",
       "      <td>Aki</td>\n",
       "      <td>Kimbo</td>\n",
       "      <td>kimbo@acimbo.com</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>U</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-11-13</td>\n",
       "      <td>2022-11-13 02:24:55.698886</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7</td>\n",
       "      <td>kili</td>\n",
       "      <td>manjaror</td>\n",
       "      <td>kimi@kiliman.com</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>N</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-11-13</td>\n",
       "      <td>2022-11-13 02:30:09.045313</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id user_first_name user_last_name     user_email_id  \\\n",
       "0        2           Scott          Tiger   scott@tiger.com   \n",
       "1        4          Mickey          Mouse  mickey@mouse.com   \n",
       "2        5           tosca          Major   tosca@gmail.com   \n",
       "3        6             Aki          Kimbo  kimbo@acimbo.com   \n",
       "4        7            kili       manjaror  kimi@kiliman.com   \n",
       "\n",
       "   user_email_validated user_password user_role  is_active  created_dt  \\\n",
       "0                 False          None         A      False  2022-11-13   \n",
       "1                 False          None         U      False  2022-11-13   \n",
       "2                 False          None         U      False  2022-11-13   \n",
       "3                 False          None         U      False  2022-11-13   \n",
       "4                 False          None         N      False  2022-11-13   \n",
       "\n",
       "             last_updated_ts  \n",
       "0 2022-11-13 02:05:47.377391  \n",
       "1 2022-11-13 02:05:47.377391  \n",
       "2 2022-11-13 02:24:55.698886  \n",
       "3 2022-11-13 02:24:55.698886  \n",
       "4 2022-11-13 02:30:09.045313  "
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT * FROM users_part\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "2c8d0c71",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"DELETE FROM users_part WHERE user_email_id ~ 'tosca'\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "b5937bf2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>user_first_name</th>\n",
       "      <th>user_last_name</th>\n",
       "      <th>user_email_id</th>\n",
       "      <th>user_email_validated</th>\n",
       "      <th>user_password</th>\n",
       "      <th>user_role</th>\n",
       "      <th>is_active</th>\n",
       "      <th>created_dt</th>\n",
       "      <th>last_updated_ts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "      <td>Mickey</td>\n",
       "      <td>Mouse</td>\n",
       "      <td>mickey@mouse.com</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>U</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-11-13</td>\n",
       "      <td>2022-11-13 02:05:47.377391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6</td>\n",
       "      <td>Aki</td>\n",
       "      <td>Kimbo</td>\n",
       "      <td>kimbo@acimbo.com</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>U</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-11-13</td>\n",
       "      <td>2022-11-13 02:24:55.698886</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id user_first_name user_last_name     user_email_id  \\\n",
       "0        4          Mickey          Mouse  mickey@mouse.com   \n",
       "1        6             Aki          Kimbo  kimbo@acimbo.com   \n",
       "\n",
       "   user_email_validated user_password user_role  is_active  created_dt  \\\n",
       "0                 False          None         U      False  2022-11-13   \n",
       "1                 False          None         U      False  2022-11-13   \n",
       "\n",
       "             last_updated_ts  \n",
       "0 2022-11-13 02:05:47.377391  \n",
       "1 2022-11-13 02:24:55.698886  "
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT * FROM users_part_u\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "93fdd299",
   "metadata": {},
   "source": [
    "### Starting the range partition exercise\n",
    "\n",
    "  * Create table using `PARTITION BY RANGE`\n",
    "  * Add default and range specific partitions\n",
    "  * Validate by inserting data into the table\n",
    "\n",
    "How the data will be provided in reality. As some CSV or User input. When it is provided as CSV file, the files are split based on partitions into individual files or even into \n",
    "seperate folders. \n",
    "\n",
    "If I need to compare the effort of Glue with manual data engineering, the process of moving the data from multiple CSV files from different folders has to be understood"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "c35457f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"DROP TABLE IF EXISTS users_part\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "d9150b9b",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"CREATE TABLE users_range_part (\n",
    "    user_id SERIAL,\n",
    "    user_first_name VARCHAR(30) NOT NULL,\n",
    "    user_last_name VARCHAR(30) NOT NULL,\n",
    "    user_email_id VARCHAR(50) NOT NULL,\n",
    "    user_email_validated BOOLEAN DEFAULT FALSE,\n",
    "    user_password VARCHAR(200),\n",
    "    user_role VARCHAR(1) NOT NULL DEFAULT 'U', --U and A\n",
    "    is_active BOOLEAN DEFAULT FALSE,\n",
    "    created_dt DATE DEFAULT CURRENT_DATE,\n",
    "    last_updated_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n",
    "    PRIMARY KEY (created_dt, user_id)\n",
    ") PARTITION BY RANGE(created_dt)\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a0092845",
   "metadata": {},
   "source": [
    "* We can add partition to existing partitioned table using `CREATE TABLE partition_name PARTITION OF table_name`.\n",
    "* We can have a partition for default values so that all the data that does not satisfy the partition condition can be added to it.\n",
    "* We can have a partition for specific range of values using `FOR VALUES FROM (from_value) TO (to_value)` as part of `CREATE TABLE partition_name PARTITION OF table_name`.\n",
    "* Once partitions are added, we can insert data into the partitioned table.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "5fd747a2",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"CREATE TABLE users_range_part_flt\n",
    "                PARTITION OF users_range_part DEFAULT\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "9bcdc545",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"CREATE TABLE users_range_part_2016\n",
    "                    PARTITION OF users_range_part\n",
    "                        FOR VALUES FROM ('2016-01-01') TO ('2016-12-31');\n",
    "            CREATE TABLE users_range_part_2017\n",
    "                PARTITION OF users_range_part\n",
    "                    FOR VALUES FROM ('2017-01-01') TO ('2017-12-31');\n",
    "            CREATE TABLE users_range_part_2018\n",
    "                PARTITION OF users_range_part\n",
    "                    FOR VALUES FROM ('2018-01-01') TO ('2018-12-31');\n",
    "            CREATE TABLE users_range_part_2019\n",
    "                PARTITION OF users_range_part\n",
    "                    FOR VALUES FROM ('2019-01-01') TO ('2019-12-31');\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "7f683c19",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"INSERT INTO users_range_part \n",
    "    (user_first_name, user_last_name, user_email_id, created_dt)\n",
    "VALUES \n",
    "    ('Scott', 'Tiger', 'scott@tiger.com', '2018-10-01'),\n",
    "    ('Donald', 'Duck', 'donald@duck.com', '2019-02-10'),\n",
    "    ('Mickey', 'Mouse', 'mickey@mouse.com', '2017-06-22')\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "ab7243da",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>user_first_name</th>\n",
       "      <th>user_last_name</th>\n",
       "      <th>user_email_id</th>\n",
       "      <th>user_email_validated</th>\n",
       "      <th>user_password</th>\n",
       "      <th>user_role</th>\n",
       "      <th>is_active</th>\n",
       "      <th>created_dt</th>\n",
       "      <th>last_updated_ts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [user_id, user_first_name, user_last_name, user_email_id, user_email_validated, user_password, user_role, is_active, created_dt, last_updated_ts]\n",
       "Index: []"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT * FROM users_range_part_flt\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "5ea73101",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>user_first_name</th>\n",
       "      <th>user_last_name</th>\n",
       "      <th>user_email_id</th>\n",
       "      <th>user_email_validated</th>\n",
       "      <th>user_password</th>\n",
       "      <th>user_role</th>\n",
       "      <th>is_active</th>\n",
       "      <th>created_dt</th>\n",
       "      <th>last_updated_ts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>Mickey</td>\n",
       "      <td>Mouse</td>\n",
       "      <td>mickey@mouse.com</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>U</td>\n",
       "      <td>False</td>\n",
       "      <td>2017-06-22</td>\n",
       "      <td>2022-11-13 03:16:58.713794</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id user_first_name user_last_name     user_email_id  \\\n",
       "0        3          Mickey          Mouse  mickey@mouse.com   \n",
       "\n",
       "   user_email_validated user_password user_role  is_active  created_dt  \\\n",
       "0                 False          None         U      False  2017-06-22   \n",
       "\n",
       "             last_updated_ts  \n",
       "0 2022-11-13 03:16:58.713794  "
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT * FROM users_range_part_2017\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "36beb444",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>user_first_name</th>\n",
       "      <th>user_last_name</th>\n",
       "      <th>user_email_id</th>\n",
       "      <th>user_email_validated</th>\n",
       "      <th>user_password</th>\n",
       "      <th>user_role</th>\n",
       "      <th>is_active</th>\n",
       "      <th>created_dt</th>\n",
       "      <th>last_updated_ts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [user_id, user_first_name, user_last_name, user_email_id, user_email_validated, user_password, user_role, is_active, created_dt, last_updated_ts]\n",
       "Index: []"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT * FROM users_range_part_2016\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "1dc1b5e6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>user_first_name</th>\n",
       "      <th>user_last_name</th>\n",
       "      <th>user_email_id</th>\n",
       "      <th>user_email_validated</th>\n",
       "      <th>user_password</th>\n",
       "      <th>user_role</th>\n",
       "      <th>is_active</th>\n",
       "      <th>created_dt</th>\n",
       "      <th>last_updated_ts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>Mickey</td>\n",
       "      <td>Mouse</td>\n",
       "      <td>mickey@mouse.com</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>U</td>\n",
       "      <td>False</td>\n",
       "      <td>2017-06-22</td>\n",
       "      <td>2022-11-13 03:16:58.713794</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Scott</td>\n",
       "      <td>Tiger</td>\n",
       "      <td>scott@tiger.com</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>U</td>\n",
       "      <td>False</td>\n",
       "      <td>2018-10-01</td>\n",
       "      <td>2022-11-13 03:16:58.713794</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Donald</td>\n",
       "      <td>Duck</td>\n",
       "      <td>donald@duck.com</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>U</td>\n",
       "      <td>False</td>\n",
       "      <td>2019-02-10</td>\n",
       "      <td>2022-11-13 03:16:58.713794</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id user_first_name user_last_name     user_email_id  \\\n",
       "0        3          Mickey          Mouse  mickey@mouse.com   \n",
       "1        1           Scott          Tiger   scott@tiger.com   \n",
       "2        2          Donald           Duck   donald@duck.com   \n",
       "\n",
       "   user_email_validated user_password user_role  is_active  created_dt  \\\n",
       "0                 False          None         U      False  2017-06-22   \n",
       "1                 False          None         U      False  2018-10-01   \n",
       "2                 False          None         U      False  2019-02-10   \n",
       "\n",
       "             last_updated_ts  \n",
       "0 2022-11-13 03:16:58.713794  \n",
       "1 2022-11-13 03:16:58.713794  \n",
       "2 2022-11-13 03:16:58.713794  "
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT * FROM users_range_part\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "ac1a0803",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"ALTER TABLE users_range_part\n",
    "    DETACH PARTITION users_range_part_2016;\n",
    "    ALTER TABLE users_range_part\n",
    "    DETACH PARTITION users_range_part_2017;\n",
    "    ALTER TABLE users_range_part\n",
    "    DETACH PARTITION users_range_part_2018;\n",
    "    ALTER TABLE users_range_part\n",
    "    DETACH PARTITION users_range_part_2019\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "46a9f27c",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "201601:2016-01-01:2016-01-31\n",
      "201602:2016-02-01:2016-02-29\n",
      "201603:2016-03-01:2016-03-31\n",
      "201604:2016-04-01:2016-04-30\n",
      "201605:2016-05-01:2016-05-31\n",
      "201606:2016-06-01:2016-06-30\n",
      "201607:2016-07-01:2016-07-31\n",
      "201608:2016-08-01:2016-08-31\n",
      "201609:2016-09-01:2016-09-30\n",
      "201610:2016-10-01:2016-10-31\n",
      "201611:2016-11-01:2016-11-30\n",
      "201612:2016-12-01:2016-12-31\n",
      "201701:2017-01-01:2017-01-31\n",
      "201702:2017-02-01:2017-02-28\n",
      "201703:2017-03-01:2017-03-31\n",
      "201704:2017-04-01:2017-04-30\n",
      "201705:2017-05-01:2017-05-31\n",
      "201706:2017-06-01:2017-06-30\n",
      "201707:2017-07-01:2017-07-31\n",
      "201708:2017-08-01:2017-08-31\n",
      "201709:2017-09-01:2017-09-30\n",
      "201710:2017-10-01:2017-10-31\n",
      "201711:2017-11-01:2017-11-30\n",
      "201712:2017-12-01:2017-12-31\n",
      "201801:2018-01-01:2018-01-31\n",
      "201802:2018-02-01:2018-02-28\n",
      "201803:2018-03-01:2018-03-31\n",
      "201804:2018-04-01:2018-04-30\n",
      "201805:2018-05-01:2018-05-31\n",
      "201806:2018-06-01:2018-06-30\n",
      "201807:2018-07-01:2018-07-31\n",
      "201808:2018-08-01:2018-08-31\n",
      "201809:2018-09-01:2018-09-30\n",
      "201810:2018-10-01:2018-10-31\n",
      "201811:2018-11-01:2018-11-30\n",
      "201812:2018-12-01:2018-12-31\n",
      "201901:2019-01-01:2019-01-31\n",
      "201902:2019-02-01:2019-02-28\n",
      "201903:2019-03-01:2019-03-31\n",
      "201904:2019-04-01:2019-04-30\n",
      "201905:2019-05-01:2019-05-31\n",
      "201906:2019-06-01:2019-06-30\n",
      "201907:2019-07-01:2019-07-31\n",
      "201908:2019-08-01:2019-08-31\n",
      "201909:2019-09-01:2019-09-30\n",
      "201910:2019-10-01:2019-10-31\n",
      "201911:2019-11-01:2019-11-30\n",
      "201912:2019-12-01:2019-12-31\n",
      "202001:2020-01-01:2020-01-31\n",
      "202002:2020-02-01:2020-02-29\n",
      "202003:2020-03-01:2020-03-31\n",
      "202004:2020-04-01:2020-04-30\n",
      "202005:2020-05-01:2020-05-31\n",
      "202006:2020-06-01:2020-06-30\n",
      "202007:2020-07-01:2020-07-31\n",
      "202008:2020-08-01:2020-08-31\n",
      "202009:2020-09-01:2020-09-30\n",
      "202010:2020-10-01:2020-10-31\n",
      "202011:2020-11-01:2020-11-30\n",
      "202012:2020-12-01:2020-12-31\n",
      "202101:2021-01-01:2021-01-31\n",
      "202102:2021-02-01:2021-02-28\n",
      "202103:2021-03-01:2021-03-31\n",
      "202104:2021-04-01:2021-04-30\n",
      "202105:2021-05-01:2021-05-31\n",
      "202106:2021-06-01:2021-06-30\n",
      "202107:2021-07-01:2021-07-31\n",
      "202108:2021-08-01:2021-08-31\n",
      "202109:2021-09-01:2021-09-30\n",
      "202110:2021-10-01:2021-10-31\n",
      "202111:2021-11-01:2021-11-30\n",
      "202112:2021-12-01:2021-12-31\n",
      "202201:2022-01-01:2022-01-31\n",
      "202202:2022-02-01:2022-02-28\n",
      "202203:2022-03-01:2022-03-31\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "from pandas.tseries.offsets import MonthBegin, MonthEnd\n",
    "\n",
    "months = pd.date_range(start='1/1/2016', end='3/31/2022', freq='1M')\n",
    "\n",
    "for month in months:\n",
    "    begin_date = month - MonthBegin(1)\n",
    "    end_date = month + MonthEnd(0)\n",
    "    print(str(month)[:7].replace('-', ''), end=':')\n",
    "    print(str(begin_date).split(' ')[0], end=':')\n",
    "    print(str(end_date).split(' ')[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "4d5b809b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<MonthBegin>"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "MonthBegin(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "322cbd84",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<10 * MonthBegins>"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "MonthBegin(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "74e271d1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Timestamp('2016-11-30 00:00:00', freq='M')"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "MonthEnd(10) + months[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "e8412e8d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DatetimeIndex(['2016-01-31', '2016-02-29', '2016-03-31'], dtype='datetime64[ns]', freq='M')"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "months"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "d43e6d6d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Adding partition for 2016-01-01 00:00:00 and 2016-01-31 00:00:00\n",
      "\n",
      "            CREATE TABLE users_range_part_201601\n",
      "            PARTITION OF users_range_part\n",
      "            FOR VALUES FROM ('2016-01-01') TO ('2016-01-31')\n",
      "        \n",
      "Adding partition for 2016-02-01 00:00:00 and 2016-02-29 00:00:00\n",
      "\n",
      "            CREATE TABLE users_range_part_201602\n",
      "            PARTITION OF users_range_part\n",
      "            FOR VALUES FROM ('2016-02-01') TO ('2016-02-29')\n",
      "        \n",
      "Adding partition for 2016-03-01 00:00:00 and 2016-03-31 00:00:00\n",
      "\n",
      "            CREATE TABLE users_range_part_201603\n",
      "            PARTITION OF users_range_part\n",
      "            FOR VALUES FROM ('2016-03-01') TO ('2016-03-31')\n",
      "        \n"
     ]
    }
   ],
   "source": [
    "for month in months:\n",
    "    begin_date = month - MonthBegin(1)\n",
    "    end_date = month + MonthEnd(0)\n",
    "    print(f'Adding partition for {begin_date} and {end_date}')\n",
    "    yyyymm=str(month)[:7].replace('-', '')\n",
    "    begin_date=str(begin_date).split(' ')[0]\n",
    "    end_date=str(end_date).split(' ')[0]\n",
    "    query = f'''\n",
    "            CREATE TABLE users_range_part_{yyyymm}\n",
    "            PARTITION OF users_range_part\n",
    "            FOR VALUES FROM ('{begin_date}') TO ('{end_date}')\n",
    "        ''' \n",
    "\n",
    "    print(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "c4ee1e88",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"ALTER TABLE users_range_part\n",
    "                DETACH PARTITION users_range_part_flt\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "id": "dba5f685",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Adding partition for 2016-01-01 00:00:00 and 2016-01-31 00:00:00\n",
      "Adding partition for 2016-02-01 00:00:00 and 2016-02-29 00:00:00\n",
      "Adding partition for 2016-03-01 00:00:00 and 2016-03-31 00:00:00\n",
      "Adding partition for 2016-04-01 00:00:00 and 2016-04-30 00:00:00\n",
      "Adding partition for 2016-05-01 00:00:00 and 2016-05-31 00:00:00\n",
      "Adding partition for 2016-06-01 00:00:00 and 2016-06-30 00:00:00\n",
      "Adding partition for 2016-07-01 00:00:00 and 2016-07-31 00:00:00\n",
      "Adding partition for 2016-08-01 00:00:00 and 2016-08-31 00:00:00\n",
      "Adding partition for 2016-09-01 00:00:00 and 2016-09-30 00:00:00\n",
      "Adding partition for 2016-10-01 00:00:00 and 2016-10-31 00:00:00\n",
      "Adding partition for 2016-11-01 00:00:00 and 2016-11-30 00:00:00\n",
      "Adding partition for 2016-12-01 00:00:00 and 2016-12-31 00:00:00\n",
      "Adding partition for 2017-01-01 00:00:00 and 2017-01-31 00:00:00\n",
      "Adding partition for 2017-02-01 00:00:00 and 2017-02-28 00:00:00\n",
      "Adding partition for 2017-03-01 00:00:00 and 2017-03-31 00:00:00\n",
      "Adding partition for 2017-04-01 00:00:00 and 2017-04-30 00:00:00\n",
      "Adding partition for 2017-05-01 00:00:00 and 2017-05-31 00:00:00\n",
      "Adding partition for 2017-06-01 00:00:00 and 2017-06-30 00:00:00\n",
      "Adding partition for 2017-07-01 00:00:00 and 2017-07-31 00:00:00\n",
      "Adding partition for 2017-08-01 00:00:00 and 2017-08-31 00:00:00\n",
      "Adding partition for 2017-09-01 00:00:00 and 2017-09-30 00:00:00\n",
      "Adding partition for 2017-10-01 00:00:00 and 2017-10-31 00:00:00\n",
      "Adding partition for 2017-11-01 00:00:00 and 2017-11-30 00:00:00\n",
      "Adding partition for 2017-12-01 00:00:00 and 2017-12-31 00:00:00\n",
      "Adding partition for 2018-01-01 00:00:00 and 2018-01-31 00:00:00\n",
      "Adding partition for 2018-02-01 00:00:00 and 2018-02-28 00:00:00\n",
      "Adding partition for 2018-03-01 00:00:00 and 2018-03-31 00:00:00\n",
      "Adding partition for 2018-04-01 00:00:00 and 2018-04-30 00:00:00\n",
      "Adding partition for 2018-05-01 00:00:00 and 2018-05-31 00:00:00\n",
      "Adding partition for 2018-06-01 00:00:00 and 2018-06-30 00:00:00\n",
      "Adding partition for 2018-07-01 00:00:00 and 2018-07-31 00:00:00\n",
      "Adding partition for 2018-08-01 00:00:00 and 2018-08-31 00:00:00\n",
      "Adding partition for 2018-09-01 00:00:00 and 2018-09-30 00:00:00\n",
      "Adding partition for 2018-10-01 00:00:00 and 2018-10-31 00:00:00\n",
      "Adding partition for 2018-11-01 00:00:00 and 2018-11-30 00:00:00\n",
      "Adding partition for 2018-12-01 00:00:00 and 2018-12-31 00:00:00\n",
      "Adding partition for 2019-01-01 00:00:00 and 2019-01-31 00:00:00\n",
      "Adding partition for 2019-02-01 00:00:00 and 2019-02-28 00:00:00\n",
      "Adding partition for 2019-03-01 00:00:00 and 2019-03-31 00:00:00\n",
      "Adding partition for 2019-04-01 00:00:00 and 2019-04-30 00:00:00\n",
      "Adding partition for 2019-05-01 00:00:00 and 2019-05-31 00:00:00\n",
      "Adding partition for 2019-06-01 00:00:00 and 2019-06-30 00:00:00\n",
      "Adding partition for 2019-07-01 00:00:00 and 2019-07-31 00:00:00\n",
      "Adding partition for 2019-08-01 00:00:00 and 2019-08-31 00:00:00\n",
      "Adding partition for 2019-09-01 00:00:00 and 2019-09-30 00:00:00\n",
      "Adding partition for 2019-10-01 00:00:00 and 2019-10-31 00:00:00\n",
      "Adding partition for 2019-11-01 00:00:00 and 2019-11-30 00:00:00\n",
      "Adding partition for 2019-12-01 00:00:00 and 2019-12-31 00:00:00\n",
      "Adding partition for 2020-01-01 00:00:00 and 2020-01-31 00:00:00\n",
      "Adding partition for 2020-02-01 00:00:00 and 2020-02-29 00:00:00\n",
      "Adding partition for 2020-03-01 00:00:00 and 2020-03-31 00:00:00\n",
      "Adding partition for 2020-04-01 00:00:00 and 2020-04-30 00:00:00\n",
      "Adding partition for 2020-05-01 00:00:00 and 2020-05-31 00:00:00\n",
      "Adding partition for 2020-06-01 00:00:00 and 2020-06-30 00:00:00\n",
      "Adding partition for 2020-07-01 00:00:00 and 2020-07-31 00:00:00\n",
      "Adding partition for 2020-08-01 00:00:00 and 2020-08-31 00:00:00\n",
      "Adding partition for 2020-09-01 00:00:00 and 2020-09-30 00:00:00\n",
      "Adding partition for 2020-10-01 00:00:00 and 2020-10-31 00:00:00\n",
      "Adding partition for 2020-11-01 00:00:00 and 2020-11-30 00:00:00\n",
      "Adding partition for 2020-12-01 00:00:00 and 2020-12-31 00:00:00\n",
      "Adding partition for 2021-01-01 00:00:00 and 2021-01-31 00:00:00\n",
      "Adding partition for 2021-02-01 00:00:00 and 2021-02-28 00:00:00\n",
      "Adding partition for 2021-03-01 00:00:00 and 2021-03-31 00:00:00\n",
      "Adding partition for 2021-04-01 00:00:00 and 2021-04-30 00:00:00\n",
      "Adding partition for 2021-05-01 00:00:00 and 2021-05-31 00:00:00\n",
      "Adding partition for 2021-06-01 00:00:00 and 2021-06-30 00:00:00\n",
      "Adding partition for 2021-07-01 00:00:00 and 2021-07-31 00:00:00\n",
      "Adding partition for 2021-08-01 00:00:00 and 2021-08-31 00:00:00\n",
      "Adding partition for 2021-09-01 00:00:00 and 2021-09-30 00:00:00\n",
      "Adding partition for 2021-10-01 00:00:00 and 2021-10-31 00:00:00\n",
      "Adding partition for 2021-11-01 00:00:00 and 2021-11-30 00:00:00\n",
      "Adding partition for 2021-12-01 00:00:00 and 2021-12-31 00:00:00\n",
      "Adding partition for 2022-01-01 00:00:00 and 2022-01-31 00:00:00\n",
      "Adding partition for 2022-02-01 00:00:00 and 2022-02-28 00:00:00\n",
      "Adding partition for 2022-03-01 00:00:00 and 2022-03-31 00:00:00\n"
     ]
    }
   ],
   "source": [
    "for month in months:\n",
    "    begin_date = month - MonthBegin(1)\n",
    "    end_date = month + MonthEnd(0)\n",
    "    print(f'Adding partition for {begin_date} and {end_date}')\n",
    "    yyyymm=str(month)[:7].replace('-', '')\n",
    "    begin_date=str(begin_date).split(' ')[0]\n",
    "    end_date=str(end_date).split(' ')[0]\n",
    "    query = f'''\n",
    "            CREATE TABLE users_range_part_{yyyymm}\n",
    "            PARTITION OF users_range_part\n",
    "            FOR VALUES FROM ('{begin_date}') TO ('{end_date}')\n",
    "        ''' \n",
    "    queryTable(f\"\"\"DROP TABLE IF EXISTS users_range_part_{yyyymm}\"\"\")\n",
    "    queryTable(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "id": "31970a2b",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"INSERT INTO users_range_part\n",
    "SELECT * FROM users_range_part_2016;\n",
    "INSERT INTO users_range_part\n",
    "SELECT * FROM users_range_part_2017;\n",
    "INSERT INTO users_range_part\n",
    "SELECT * FROM users_range_part_2018;\n",
    "INSERT INTO users_range_part\n",
    "SELECT * FROM users_range_part_2019\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "id": "c584c810",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>user_first_name</th>\n",
       "      <th>user_last_name</th>\n",
       "      <th>user_email_id</th>\n",
       "      <th>user_email_validated</th>\n",
       "      <th>user_password</th>\n",
       "      <th>user_role</th>\n",
       "      <th>is_active</th>\n",
       "      <th>created_dt</th>\n",
       "      <th>last_updated_ts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>Mickey</td>\n",
       "      <td>Mouse</td>\n",
       "      <td>mickey@mouse.com</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>U</td>\n",
       "      <td>False</td>\n",
       "      <td>2017-06-22</td>\n",
       "      <td>2022-11-13 03:16:58.713794</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id user_first_name user_last_name     user_email_id  \\\n",
       "0        3          Mickey          Mouse  mickey@mouse.com   \n",
       "\n",
       "   user_email_validated user_password user_role  is_active  created_dt  \\\n",
       "0                 False          None         U      False  2017-06-22   \n",
       "\n",
       "             last_updated_ts  \n",
       "0 2022-11-13 03:16:58.713794  "
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT * FROM users_range_part_201706\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "id": "0a501355",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>table_catalog</th>\n",
       "      <th>table_schema</th>\n",
       "      <th>table_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>partition_training</td>\n",
       "      <td>public</td>\n",
       "      <td>users_range_part_2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>partition_training</td>\n",
       "      <td>public</td>\n",
       "      <td>users_range_part_201601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>partition_training</td>\n",
       "      <td>public</td>\n",
       "      <td>users_range_part_201602</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>partition_training</td>\n",
       "      <td>public</td>\n",
       "      <td>users_range_part_201603</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>partition_training</td>\n",
       "      <td>public</td>\n",
       "      <td>users_range_part_201604</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>partition_training</td>\n",
       "      <td>public</td>\n",
       "      <td>users_range_part_202112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>partition_training</td>\n",
       "      <td>public</td>\n",
       "      <td>users_range_part_202201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>partition_training</td>\n",
       "      <td>public</td>\n",
       "      <td>users_range_part_202202</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>partition_training</td>\n",
       "      <td>public</td>\n",
       "      <td>users_range_part_202203</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>partition_training</td>\n",
       "      <td>public</td>\n",
       "      <td>users_range_part_flt</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>80 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         table_catalog table_schema               table_name\n",
       "0   partition_training       public    users_range_part_2016\n",
       "1   partition_training       public  users_range_part_201601\n",
       "2   partition_training       public  users_range_part_201602\n",
       "3   partition_training       public  users_range_part_201603\n",
       "4   partition_training       public  users_range_part_201604\n",
       "..                 ...          ...                      ...\n",
       "75  partition_training       public  users_range_part_202112\n",
       "76  partition_training       public  users_range_part_202201\n",
       "77  partition_training       public  users_range_part_202202\n",
       "78  partition_training       public  users_range_part_202203\n",
       "79  partition_training       public     users_range_part_flt\n",
       "\n",
       "[80 rows x 3 columns]"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT table_catalog, \n",
    "    table_schema, \n",
    "    table_name FROM information_schema.tables\n",
    "WHERE table_name ~ 'users_range_part_'\n",
    "ORDER BY table_name\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2493815a",
   "metadata": {},
   "source": [
    "Here are the steps involved in creating table using hash partitioning strategy.\n",
    "\n",
    "* Create table using PARTITION BY HASH\n",
    "* Add default and remainder specific partitions based up on modulus.\n",
    "* Validate by inserting data into the table\n",
    "\n",
    "We can detach as well as drop the partitions from the table.\n",
    "\n",
    "Hash partitioning is typically done on sparse columns such as user_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "id": "ef065139",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"CREATE TABLE users_hash_part (\n",
    "    user_id SERIAL,\n",
    "    user_first_name VARCHAR(30) NOT NULL,\n",
    "    user_last_name VARCHAR(30) NOT NULL,\n",
    "    user_email_id VARCHAR(50) NOT NULL,\n",
    "    user_email_validated BOOLEAN DEFAULT FALSE,\n",
    "    user_password VARCHAR(200),\n",
    "    user_role VARCHAR(1) NOT NULL DEFAULT 'U', --U and A\n",
    "    is_active BOOLEAN DEFAULT FALSE,\n",
    "    created_dt DATE DEFAULT CURRENT_DATE,\n",
    "    last_updated_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n",
    "    PRIMARY KEY (user_id)\n",
    ") PARTITION BY HASH(user_id)\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "id": "eeefc41f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "a hash-partitioned table may not have a default partition\n",
      "\n"
     ]
    }
   ],
   "source": [
    "queryTable(\"\"\"CREATE TABLE users_hash_part_default\n",
    "PARTITION OF users_hash_part DEFAULT\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "id": "f6dcd91f",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"CREATE TABLE users_hash_part_0_of_8\n",
    "                    PARTITION OF users_hash_part\n",
    "                        FOR VALUES WITH (modulus 8, remainder 0);\n",
    "            CREATE TABLE users_hash_part_1_of_8\n",
    "                    PARTITION OF users_hash_part\n",
    "                        FOR VALUES WITH (modulus 8, remainder 1);\n",
    "            CREATE TABLE users_hash_part_2_of_8\n",
    "                    PARTITION OF users_hash_part\n",
    "                        FOR VALUES WITH (modulus 8, remainder 2);\n",
    "            CREATE TABLE users_hash_part_3_of_8\n",
    "                    PARTITION OF users_hash_part\n",
    "                        FOR VALUES WITH (modulus 8, remainder 3);\n",
    "            CREATE TABLE users_hash_part_4_of_8\n",
    "                    PARTITION OF users_hash_part\n",
    "                        FOR VALUES WITH (modulus 8, remainder 4);\n",
    "            CREATE TABLE users_hash_part_5_of_8\n",
    "                    PARTITION OF users_hash_part\n",
    "                        FOR VALUES WITH (modulus 8, remainder 5);\n",
    "            CREATE TABLE users_hash_part_6_of_8\n",
    "                    PARTITION OF users_hash_part\n",
    "                        FOR VALUES WITH (modulus 8, remainder 6);\n",
    "            CREATE TABLE users_hash_part_7_of_8\n",
    "                    PARTITION OF users_hash_part\n",
    "                        FOR VALUES WITH (modulus 8, remainder 7);\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "id": "38110d52",
   "metadata": {},
   "outputs": [],
   "source": [
    "#When you insert there is no need to care about the partitions, the table takes care\n",
    "queryTable(\"\"\"INSERT INTO users_hash_part\n",
    "    (user_first_name, user_last_name, user_email_id, created_dt)\n",
    "VALUES \n",
    "    ('Scott', 'Tiger', 'scott@tiger.com', '2018-10-01'),\n",
    "    ('Donald', 'Duck', 'donald@duck.com', '2019-02-10'),\n",
    "    ('Mickey', 'Mouse', 'mickey@mouse.com', '2017-06-22')\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "id": "168f8767",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>user_first_name</th>\n",
       "      <th>user_last_name</th>\n",
       "      <th>user_email_id</th>\n",
       "      <th>user_email_validated</th>\n",
       "      <th>user_password</th>\n",
       "      <th>user_role</th>\n",
       "      <th>is_active</th>\n",
       "      <th>created_dt</th>\n",
       "      <th>last_updated_ts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Scott</td>\n",
       "      <td>Tiger</td>\n",
       "      <td>scott@tiger.com</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>U</td>\n",
       "      <td>False</td>\n",
       "      <td>2018-10-01</td>\n",
       "      <td>2022-11-13 03:59:10.361660</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>Mickey</td>\n",
       "      <td>Mouse</td>\n",
       "      <td>mickey@mouse.com</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>U</td>\n",
       "      <td>False</td>\n",
       "      <td>2017-06-22</td>\n",
       "      <td>2022-11-13 03:59:10.361660</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Donald</td>\n",
       "      <td>Duck</td>\n",
       "      <td>donald@duck.com</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>U</td>\n",
       "      <td>False</td>\n",
       "      <td>2019-02-10</td>\n",
       "      <td>2022-11-13 03:59:10.361660</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id user_first_name user_last_name     user_email_id  \\\n",
       "0        1           Scott          Tiger   scott@tiger.com   \n",
       "1        3          Mickey          Mouse  mickey@mouse.com   \n",
       "2        2          Donald           Duck   donald@duck.com   \n",
       "\n",
       "   user_email_validated user_password user_role  is_active  created_dt  \\\n",
       "0                 False          None         U      False  2018-10-01   \n",
       "1                 False          None         U      False  2017-06-22   \n",
       "2                 False          None         U      False  2019-02-10   \n",
       "\n",
       "             last_updated_ts  \n",
       "0 2022-11-13 03:59:10.361660  \n",
       "1 2022-11-13 03:59:10.361660  \n",
       "2 2022-11-13 03:59:10.361660  "
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT * FROM users_hash_part\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "id": "33ef1ec5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>user_first_name</th>\n",
       "      <th>user_last_name</th>\n",
       "      <th>user_email_id</th>\n",
       "      <th>user_email_validated</th>\n",
       "      <th>user_password</th>\n",
       "      <th>user_role</th>\n",
       "      <th>is_active</th>\n",
       "      <th>created_dt</th>\n",
       "      <th>last_updated_ts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Scott</td>\n",
       "      <td>Tiger</td>\n",
       "      <td>scott@tiger.com</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>U</td>\n",
       "      <td>False</td>\n",
       "      <td>2018-10-01</td>\n",
       "      <td>2022-11-13 03:59:10.361660</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id user_first_name user_last_name    user_email_id  \\\n",
       "0        1           Scott          Tiger  scott@tiger.com   \n",
       "\n",
       "   user_email_validated user_password user_role  is_active  created_dt  \\\n",
       "0                 False          None         U      False  2018-10-01   \n",
       "\n",
       "             last_updated_ts  \n",
       "0 2022-11-13 03:59:10.361660  "
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT * FROM users_hash_part_0_of_8\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "id": "9dedd0bf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>user_first_name</th>\n",
       "      <th>user_last_name</th>\n",
       "      <th>user_email_id</th>\n",
       "      <th>user_email_validated</th>\n",
       "      <th>user_password</th>\n",
       "      <th>user_role</th>\n",
       "      <th>is_active</th>\n",
       "      <th>created_dt</th>\n",
       "      <th>last_updated_ts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>Mickey</td>\n",
       "      <td>Mouse</td>\n",
       "      <td>mickey@mouse.com</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>U</td>\n",
       "      <td>False</td>\n",
       "      <td>2017-06-22</td>\n",
       "      <td>2022-11-13 03:59:10.361660</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id user_first_name user_last_name     user_email_id  \\\n",
       "0        3          Mickey          Mouse  mickey@mouse.com   \n",
       "\n",
       "   user_email_validated user_password user_role  is_active  created_dt  \\\n",
       "0                 False          None         U      False  2017-06-22   \n",
       "\n",
       "             last_updated_ts  \n",
       "0 2022-11-13 03:59:10.361660  "
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT * FROM users_hash_part_1_of_8\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "id": "2d002262",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>user_first_name</th>\n",
       "      <th>user_last_name</th>\n",
       "      <th>user_email_id</th>\n",
       "      <th>user_email_validated</th>\n",
       "      <th>user_password</th>\n",
       "      <th>user_role</th>\n",
       "      <th>is_active</th>\n",
       "      <th>created_dt</th>\n",
       "      <th>last_updated_ts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>Donald</td>\n",
       "      <td>Duck</td>\n",
       "      <td>donald@duck.com</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>U</td>\n",
       "      <td>False</td>\n",
       "      <td>2019-02-10</td>\n",
       "      <td>2022-11-13 03:59:10.361660</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id user_first_name user_last_name    user_email_id  \\\n",
       "0        2          Donald           Duck  donald@duck.com   \n",
       "\n",
       "   user_email_validated user_password user_role  is_active  created_dt  \\\n",
       "0                 False          None         U      False  2019-02-10   \n",
       "\n",
       "             last_updated_ts  \n",
       "0 2022-11-13 03:59:10.361660  "
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT * FROM users_hash_part_2_of_8\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "id": "30266985",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>user_first_name</th>\n",
       "      <th>user_last_name</th>\n",
       "      <th>user_email_id</th>\n",
       "      <th>user_email_validated</th>\n",
       "      <th>user_password</th>\n",
       "      <th>user_role</th>\n",
       "      <th>is_active</th>\n",
       "      <th>created_dt</th>\n",
       "      <th>last_updated_ts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [user_id, user_first_name, user_last_name, user_email_id, user_email_validated, user_password, user_role, is_active, created_dt, last_updated_ts]\n",
       "Index: []"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT * FROM users_hash_part_3_of_8\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "84857ce9",
   "metadata": {},
   "source": [
    "## Sub Partitioning\n",
    "\n",
    "We can have sub partitions created with different permutations and combinations. Sub Partitioning is also known as nested partitioning.\n",
    "* List - List\n",
    "* List - Range\n",
    "and others."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "id": "b740192a",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"CREATE TABLE users_qtly (\n",
    "    user_id SERIAL,\n",
    "    user_first_name VARCHAR(30) NOT NULL,\n",
    "    user_last_name VARCHAR(30) NOT NULL,\n",
    "    user_email_id VARCHAR(50) NOT NULL,\n",
    "    user_email_validated BOOLEAN DEFAULT FALSE,\n",
    "    user_password VARCHAR(200),\n",
    "    user_role VARCHAR(1) NOT NULL DEFAULT 'U', --U and A\n",
    "    is_active BOOLEAN DEFAULT FALSE,\n",
    "    created_dt DATE DEFAULT CURRENT_DATE,\n",
    "    created_year INT,\n",
    "    created_mnth INT,\n",
    "    last_updated_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n",
    "    PRIMARY KEY (created_year, created_mnth, user_id)\n",
    ") PARTITION BY LIST(created_year)\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "id": "42d9bd20",
   "metadata": {},
   "outputs": [],
   "source": [
    "#The first partition will be partitioned again\n",
    "queryTable(\"\"\"CREATE TABLE users_qtly_2016\n",
    "                PARTITION OF users_qtly\n",
    "                    FOR VALUES IN (2016)\n",
    "                        PARTITION BY LIST (created_mnth)\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "id": "86f8f7e9",
   "metadata": {},
   "outputs": [],
   "source": [
    "#The first partition will be partitioned again\n",
    "queryTable(\"\"\"CREATE TABLE users_qtly_2016\n",
    "                PARTITION OF users_qtly\n",
    "                    FOR VALUES IN (2016)\n",
    "                        PARTITION BY LIST (created_mnth)\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "id": "dc7aa75a",
   "metadata": {},
   "outputs": [],
   "source": [
    "#The first partition will be partitioned again, based on the month number. This will be used \n",
    "#in the exercise\n",
    "queryTable(\"\"\"CREATE TABLE users_qtly_2016q1\n",
    "                    PARTITION OF users_qtly_2016\n",
    "                        FOR VALUES IN (1, 2, 3)\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f940f82e",
   "metadata": {},
   "source": [
    "### List - Range Partitioning\n",
    "\n",
    "Let us understand how we can create table using list - Range sub partitioning using same example as before (partitioning by year and then by quarter).\n",
    "* Create table with `PARTITION BY LIST` with `created_year`.\n",
    "* Create tables for yearly partitions with `PARTITION BY RANGE` with `created_month`.\n",
    "* Create tables for quarterly partitions with the range of values using `FOR VALUES FROM (lower_bound) TO (upper_bound)`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "id": "93a43a6f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "relation \"users_qtly_2016\" already exists\n",
      "\n"
     ]
    }
   ],
   "source": [
    "#The first partition will be partitioned again, based on the month number. This will be used \n",
    "#in the exercise\n",
    "queryTable(\"\"\"CREATE TABLE users_qtly_2016\n",
    "                    PARTITION OF users_qtly\n",
    "                        FOR VALUES IN (2016)\n",
    "                            PARTITION BY RANGE (created_mnth)\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "id": "28066604",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "relation \"users_qtly_2016q1\" already exists\n",
      "\n"
     ]
    }
   ],
   "source": [
    "#The first partition will be partitioned again, based on the month number. This will be used \n",
    "#in the exercise\n",
    "queryTable(\"\"\"CREATE TABLE users_qtly_2016q1\n",
    "                    PARTITION OF users_qtly_2016\n",
    "                            FOR VALUES FROM (1) TO (3)\"\"\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}