simple_database_creation_manipulation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Simple Database Creation and Manipulation\n",
    "\n",
    "In this tutorial we are going to use aws-wrangler to create a database of different tables.\n",
    "\n",
    "Let's create a database out of the test data `employees.csv`, `sales.csv` and `department.csv` (all in the `data/` folder)\n",
    "\n",
    "Note this is basically taken from: https://github.com/awslabs/aws-data-wrangler/blob/master/tutorials/014%20-%20Schema%20Evolution.ipynb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import awswrangler as wr\n",
    "import datetime\n",
    "import pydbtools as pydb"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Setup first"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# setup your own testing area (set foldername = GH username)\n",
    "foldername = \"isichei\" # GH username\n",
    "foldername = foldername.lower().replace(\"-\",\"_\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "deleting objs\n"
     ]
    }
   ],
   "source": [
    "bucketname = \"alpha-everyone\"\n",
    "db_name = f\"aws_example_{foldername}\"\n",
    "db_base_path = f\"s3://{bucketname}/{foldername}/database\"\n",
    "s3_base_path = f\"s3://{bucketname}/{foldername}/\"\n",
    "\n",
    "# Delete all the s3 files in a given path\n",
    "if wr.s3.list_objects(s3_base_path):\n",
    "    print(\"deleting objs\")\n",
    "    wr.s3.delete_objects(s3_base_path)\n",
    "\n",
    "# Delete the database if it exists\n",
    "df_dbs = wr.catalog.databases(None)\n",
    "if db_name in df_dbs[\"Database\"].to_list():\n",
    "    wr.catalog.delete_database(\n",
    "        name=db_name\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Lets get the data in pandas first"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>employee_id</th>\n",
       "      <th>sex</th>\n",
       "      <th>forename</th>\n",
       "      <th>surname</th>\n",
       "      <th>department_id</th>\n",
       "      <th>manager_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>M</td>\n",
       "      <td>Dexter</td>\n",
       "      <td>Mitchell</td>\n",
       "      <td>1.0</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>F</td>\n",
       "      <td>Summer</td>\n",
       "      <td>Bennett</td>\n",
       "      <td>1.0</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>M</td>\n",
       "      <td>Pip</td>\n",
       "      <td>Carter</td>\n",
       "      <td>1.0</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>F</td>\n",
       "      <td>Bella</td>\n",
       "      <td>Long</td>\n",
       "      <td>1.0</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>F</td>\n",
       "      <td>Lexie</td>\n",
       "      <td>Perry</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   employee_id sex forename   surname  department_id  manager_id\n",
       "0            1   M   Dexter  Mitchell            1.0        17.0\n",
       "1            2   F   Summer   Bennett            1.0        17.0\n",
       "2            3   M      Pip    Carter            1.0        17.0\n",
       "3            4   F    Bella      Long            1.0        17.0\n",
       "4            5   F    Lexie     Perry            NaN        17.0"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\"data/employees.csv\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Lets do some transforms on it"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>employee_id</th>\n",
       "      <th>sex</th>\n",
       "      <th>forename</th>\n",
       "      <th>surname</th>\n",
       "      <th>department_id</th>\n",
       "      <th>manager_id</th>\n",
       "      <th>creation_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>M</td>\n",
       "      <td>Dexter</td>\n",
       "      <td>Mitchell</td>\n",
       "      <td>1.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>2021-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>F</td>\n",
       "      <td>Summer</td>\n",
       "      <td>Bennett</td>\n",
       "      <td>1.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>2021-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>M</td>\n",
       "      <td>Pip</td>\n",
       "      <td>Carter</td>\n",
       "      <td>1.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>2021-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>F</td>\n",
       "      <td>Bella</td>\n",
       "      <td>Long</td>\n",
       "      <td>1.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>2021-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>F</td>\n",
       "      <td>Lexie</td>\n",
       "      <td>Perry</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17.0</td>\n",
       "      <td>2021-01-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   employee_id sex forename   surname  department_id  manager_id creation_date\n",
       "0            1   M   Dexter  Mitchell            1.0        17.0    2021-01-01\n",
       "1            2   F   Summer   Bennett            1.0        17.0    2021-01-01\n",
       "2            3   M      Pip    Carter            1.0        17.0    2021-01-01\n",
       "3            4   F    Bella      Long            1.0        17.0    2021-01-01\n",
       "4            5   F    Lexie     Perry            NaN        17.0    2021-01-01"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"creation_date\"] = datetime.date(2021, 1, 1)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### write the table to a database\n",
    "\n",
    "parquet is always your best bet for writing data to a Glue Database especially if you only want to retrieve that data via Athena SQL queries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'paths': ['s3://alpha-everyone/isichei/database/employees/9c9e0cec839e4218b46be8ac337b361e.snappy.parquet'],\n",
       " 'partitions_values': {}}"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create the database\n",
    "wr.catalog.create_database(db_name)\n",
    "\n",
    "# note table_path is a folder as glue treats all the\n",
    "# data in a folder as contents of a single table\n",
    "table_path = f\"{db_base_path}/employees/\"\n",
    "\n",
    "# Write your pandas dataframe to S3 and add it as a table in your database\n",
    "wr.s3.to_parquet(\n",
    "    df=df,\n",
    "    path=table_path,\n",
    "    index=False,\n",
    "    dataset=True, # True allows the other params below i.e. overwriting to db.table\n",
    "    database=db_name,\n",
    "    table='employees',\n",
    "    mode=\"overwrite\",\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Append new data to the table\n",
    "\n",
    "Let's for fun also add new cols as well"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>employee_id</th>\n",
       "      <th>sex</th>\n",
       "      <th>forename</th>\n",
       "      <th>surname</th>\n",
       "      <th>department_id</th>\n",
       "      <th>manager_id</th>\n",
       "      <th>creation_date</th>\n",
       "      <th>new_col1</th>\n",
       "      <th>new_col2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>M</td>\n",
       "      <td>Dexter</td>\n",
       "      <td>Mitchell</td>\n",
       "      <td>1.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>101</td>\n",
       "      <td>some text</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>F</td>\n",
       "      <td>Summer</td>\n",
       "      <td>Bennett</td>\n",
       "      <td>1.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>102</td>\n",
       "      <td>some text</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>M</td>\n",
       "      <td>Pip</td>\n",
       "      <td>Carter</td>\n",
       "      <td>1.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>103</td>\n",
       "      <td>some text</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>F</td>\n",
       "      <td>Bella</td>\n",
       "      <td>Long</td>\n",
       "      <td>1.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>104</td>\n",
       "      <td>some text</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>F</td>\n",
       "      <td>Lexie</td>\n",
       "      <td>Perry</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17.0</td>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>105</td>\n",
       "      <td>some text</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   employee_id sex forename   surname  department_id  manager_id  \\\n",
       "0            1   M   Dexter  Mitchell            1.0        17.0   \n",
       "1            2   F   Summer   Bennett            1.0        17.0   \n",
       "2            3   M      Pip    Carter            1.0        17.0   \n",
       "3            4   F    Bella      Long            1.0        17.0   \n",
       "4            5   F    Lexie     Perry            NaN        17.0   \n",
       "\n",
       "  creation_date  new_col1   new_col2  \n",
       "0    2021-01-01       101  some text  \n",
       "1    2021-01-01       102  some text  \n",
       "2    2021-01-01       103  some text  \n",
       "3    2021-01-01       104  some text  \n",
       "4    2021-01-01       105  some text  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"creation_date\"] = datetime.date(2021, 1, 1)\n",
    "\n",
    "df[\"new_col1\"] = df[\"employee_id\"] + 100\n",
    "df[\"new_col2\"] = \"some text\"\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'paths': ['s3://alpha-everyone/isichei/database/employees/d8e5651d797f4ea9a46f8cbf394dab4c.snappy.parquet'],\n",
       " 'partitions_values': {}}"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Write the new data to S3.\n",
    "# Note the only thing has changed is mode=\"append\" whereas previously it was mode=\"overwrite\"\n",
    "wr.s3.to_parquet(\n",
    "    df=df,\n",
    "    path=table_path,\n",
    "    index=False,\n",
    "    dataset=True,\n",
    "    database=db_name,\n",
    "    table='employees',\n",
    "    mode=\"append\",\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Now query the data with Athena to look at it\n",
    "\n",
    "This should use pydbtools rather than aws_wrangler (if you are a AP user)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Each uploaded dataset had one employee with an employee_id == 1\n",
    "# So lets pull that down to demonstrate both tables were added to the data\n",
    "sql = f\"SELECT * from {db_name}.employees where employee_id = 1\"\n",
    "db_table = pydb.read_sql_query(\n",
    "    sql,\n",
    "    ctas_approach=False\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT * from aws_example_isichei.employees where employee_id = 1\n"
     ]
    }
   ],
   "source": [
    "print(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>employee_id</th>\n",
       "      <th>sex</th>\n",
       "      <th>forename</th>\n",
       "      <th>surname</th>\n",
       "      <th>department_id</th>\n",
       "      <th>manager_id</th>\n",
       "      <th>creation_date</th>\n",
       "      <th>new_col1</th>\n",
       "      <th>new_col2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>M</td>\n",
       "      <td>Dexter</td>\n",
       "      <td>Mitchell</td>\n",
       "      <td>1.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>M</td>\n",
       "      <td>Dexter</td>\n",
       "      <td>Mitchell</td>\n",
       "      <td>1.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>101</td>\n",
       "      <td>some text</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   employee_id sex forename   surname  department_id  manager_id  \\\n",
       "0            1   M   Dexter  Mitchell            1.0        17.0   \n",
       "1            1   M   Dexter  Mitchell            1.0        17.0   \n",
       "\n",
       "  creation_date  new_col1   new_col2  \n",
       "0    2021-01-01      <NA>       <NA>  \n",
       "1    2021-01-01       101  some text  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db_table.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Clean up\n",
    "\n",
    "# Delete all the s3 files in a given path\n",
    "if wr.s3.list_objects(s3_base_path):\n",
    "    print(\"deleting objs\")\n",
    "    wr.s3.delete_objects(s3_base_path)\n",
    "\n",
    "# Delete the database if it exists\n",
    "df_dbs = wr.catalog.databases(None)\n",
    "if db_name in df_dbs[\"Database\"].to_list():\n",
    "    print(f\"deleting {db_name}\")\n",
    "    wr.catalog.delete_database(\n",
    "        name=db_name\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Demonstrate db no longer exists\n",
    "db_name in wr.catalog.databases()[\"Database\"].to_list()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}