creating_and_maintaining_database_tables_in_athena.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "native-house",
   "metadata": {},
   "source": [
    "# Creating and Maintaining Database Tables in Athena\n",
    "\n",
    "In this tutorial we are going to use Athena SQL queries (via pydbtools) to create a new database from and existing databases in Athena.\n",
    "\n",
    "First we need to create a database of tables to act as our existing database. But then we will create a new database that holds tables that are derived from the original.\n",
    "\n",
    "Our source database will have the test data `employees.csv`, `sales.csv` and `department.csv` (all in the `data/` folder)\n",
    "\n",
    "Useful links:\n",
    "- https://docs.aws.amazon.com/athena/latest/ug/ctas-examples.html\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "essential-secret",
   "metadata": {},
   "source": [
    "## Setup\n",
    "\n",
    "Just run this script to create the source database so we can use it for our example."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "french-percentage",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import awswrangler as wr\n",
    "import pydbtools as pydb\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "driven-meaning",
   "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": 18,
   "id": "retired-scene",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "deleting objs\n",
      "source_db_isichei found deleting\n"
     ]
    }
   ],
   "source": [
    "bucketname = \"alpha-everyone\"\n",
    "s3_base_path = f\"s3://{bucketname}/{foldername}/\"\n",
    "\n",
    "source_db_name = f\"source_db_{foldername}\"\n",
    "source_db_base_path = f\"s3://{bucketname}/{foldername}/source_db/\"\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 source_db_name in df_dbs[\"Database\"].to_list():\n",
    "    print(f\"{source_db_name} found deleting\")\n",
    "    wr.catalog.delete_database(\n",
    "        name=source_db_name\n",
    "    )\n",
    "\n",
    "# Setup source database\n",
    "# Create the database\n",
    "wr.catalog.create_database(source_db_name)\n",
    "\n",
    "# Iterate through the tables in data/ and write them to our db using awswrangler\n",
    "for table_name in [\"department\", \"employees\", \"sales\"]:\n",
    "    \n",
    "    df = pd.read_csv(f\"data/{table_name}.csv\")\n",
    "    table_path = os.path.join(source_db_base_path, f\"{table_name}/\")\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=source_db_name,\n",
    "        table=table_name,\n",
    "        mode=\"overwrite\",\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "preliminary-henry",
   "metadata": {},
   "source": [
    "## Now for the actual tutorial\n",
    "\n",
    "We are going to run all of our queries using SQL. You may have seen that I've used awswrangler to create the database above (which is fine to do). However this part of the tutorial will all be in SQL so you can run this from anything (i.e. R, Athena workbench, anything that can send queries to Athena).\n",
    "\n",
    "### Step 1. create the new database\n",
    "\n",
    "We are going to create a new database which will generate derived tables from our source database. For ease we are going to create the database location in the same parent folder as our source database. However, in reality you probably want to create your own bucket for the database and tables to sit in so that you can control who has access to your database.\n",
    "\n",
    "> **Note:** We use a lot of f-strings here to parameterise our SQL queries so for ease and understanding we are going to print out each SQL query each time just so you can see what you are actually running on athena. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "selected-fisher",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "CREATE DATABASE IF NOT EXISTS new_db_isichei\n",
      "COMMENT 'example or running queries and insert to'\n",
      "LOCATION 's3://alpha-everyone/isichei/new_db/'\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "{'QueryExecutionId': '796c2a74-6558-4972-a539-ca1e42329d59',\n",
       " 'Query': \"CREATE DATABASE IF NOT EXISTS new_db_isichei\\nCOMMENT 'example or running queries and insert to'\\nLOCATION 's3://alpha-everyone/isichei/new_db/'\",\n",
       " 'StatementType': 'DDL',\n",
       " 'ResultConfiguration': {'OutputLocation': 's3://aws-athena-query-results-593291632749-eu-west-1/796c2a74-6558-4972-a539-ca1e42329d59.txt'},\n",
       " 'QueryExecutionContext': {},\n",
       " 'Status': {'State': 'SUCCEEDED',\n",
       "  'SubmissionDateTime': datetime.datetime(2021, 1, 20, 9, 15, 45, 566000, tzinfo=tzlocal()),\n",
       "  'CompletionDateTime': datetime.datetime(2021, 1, 20, 9, 15, 45, 937000, tzinfo=tzlocal())},\n",
       " 'Statistics': {'EngineExecutionTimeInMillis': 280,\n",
       "  'DataScannedInBytes': 0,\n",
       "  'TotalExecutionTimeInMillis': 371,\n",
       "  'QueryQueueTimeInMillis': 61,\n",
       "  'ServiceProcessingTimeInMillis': 30},\n",
       " 'WorkGroup': 'primary'}"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_db_name = f\"new_db_{foldername}\"\n",
    "new_db_base_path = f\"s3://{bucketname}/{foldername}/new_db/\"\n",
    "\n",
    "sql = f\"\"\"\n",
    "CREATE DATABASE IF NOT EXISTS {new_db_name}\n",
    "COMMENT 'example or running queries and insert to'\n",
    "LOCATION '{new_db_base_path}'\n",
    "\"\"\"\n",
    "print(sql)\n",
    "_ = pydb.start_query_execution_and_wait(sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "apart-devil",
   "metadata": {},
   "source": [
    "### Step 2. Run a CTAS query to create your new derived table in your new database\n",
    "\n",
    "We use a CTAS query as it both generates the output data into S3 but also creates the schema of the new table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "curious-consumer",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "CREATE TABLE new_db_isichei.sales_report WITH\n",
      "(\n",
      "    external_location='s3://alpha-everyone/isichei/new_db/sales_report/'\n",
      ") AS\n",
      "SELECT qtr as sales_quarter, sum(sales) AS total_sales\n",
      "FROM source_db_isichei.sales\n",
      "WHERE qtr IN (1,2)\n",
      "GROUP BY qtr\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "{'QueryExecutionId': '42acdbd4-425c-4d0b-9170-5ca5ed6ad35f',\n",
       " 'Query': \"CREATE TABLE new_db_isichei.sales_report WITH\\n(\\n    external_location='s3://alpha-everyone/isichei/new_db/sales_report/'\\n) AS\\nSELECT qtr as sales_quarter, sum(sales) AS total_sales\\nFROM source_db_isichei.sales\\nWHERE qtr IN (1,2)\\nGROUP BY qtr\",\n",
       " 'StatementType': 'DDL',\n",
       " 'ResultConfiguration': {'OutputLocation': 's3://aws-athena-query-results-593291632749-eu-west-1/tables/42acdbd4-425c-4d0b-9170-5ca5ed6ad35f'},\n",
       " 'QueryExecutionContext': {},\n",
       " 'Status': {'State': 'SUCCEEDED',\n",
       "  'SubmissionDateTime': datetime.datetime(2021, 1, 20, 9, 16, 6, 453000, tzinfo=tzlocal()),\n",
       "  'CompletionDateTime': datetime.datetime(2021, 1, 20, 9, 16, 9, 609000, tzinfo=tzlocal())},\n",
       " 'Statistics': {'EngineExecutionTimeInMillis': 2962,\n",
       "  'DataScannedInBytes': 1341,\n",
       "  'DataManifestLocation': 's3://aws-athena-query-results-593291632749-eu-west-1/tables/42acdbd4-425c-4d0b-9170-5ca5ed6ad35f-manifest.csv',\n",
       "  'TotalExecutionTimeInMillis': 3156,\n",
       "  'QueryQueueTimeInMillis': 154,\n",
       "  'QueryPlanningTimeInMillis': 357,\n",
       "  'ServiceProcessingTimeInMillis': 40},\n",
       " 'WorkGroup': 'primary'}"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Note our table s3 path is saved in the following format:\n",
    "# s3://<bucket>/<path to database folder>/<table_name>/\n",
    "# You don't have to do this but it is strongly recommended to make it easier\n",
    "# to map your schemas to your data.\n",
    "\n",
    "sales_report_s3_path = os.path.join(new_db_base_path, \"sales_report/\")\n",
    "\n",
    "sql = f\"\"\"\n",
    "CREATE TABLE {new_db_name}.sales_report WITH\n",
    "(\n",
    "    external_location='{sales_report_s3_path}'\n",
    ") AS\n",
    "SELECT qtr as sales_quarter, sum(sales) AS total_sales\n",
    "FROM {source_db_name}.sales\n",
    "WHERE qtr IN (1,2)\n",
    "GROUP BY qtr\n",
    "\"\"\"\n",
    "print(sql)\n",
    "_ = pydb.start_query_execution_and_wait(sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "frozen-vector",
   "metadata": {},
   "source": [
    "### Step 3. Use An insert into query to add new data into that table\n",
    "\n",
    "We use an Insert INTO query here as we already created the schema in the previous CTAS query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "absent-locking",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "INSERT INTO new_db_isichei.sales_report\n",
      "SELECT qtr as sales_quarter, sum(sales) AS total_sales\n",
      "FROM source_db_isichei.sales\n",
      "WHERE qtr IN (3,4)\n",
      "GROUP BY qtr\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "{'QueryExecutionId': '1ea2597c-e49e-4ebc-af4d-491904bcb69f',\n",
       " 'Query': 'INSERT INTO new_db_isichei.sales_report\\nSELECT qtr as sales_quarter, sum(sales) AS total_sales\\nFROM source_db_isichei.sales\\nWHERE qtr IN (3,4)\\nGROUP BY qtr',\n",
       " 'StatementType': 'DML',\n",
       " 'ResultConfiguration': {'OutputLocation': 's3://aws-athena-query-results-593291632749-eu-west-1/1ea2597c-e49e-4ebc-af4d-491904bcb69f'},\n",
       " 'QueryExecutionContext': {},\n",
       " 'Status': {'State': 'SUCCEEDED',\n",
       "  'SubmissionDateTime': datetime.datetime(2021, 1, 20, 9, 16, 20, 102000, tzinfo=tzlocal()),\n",
       "  'CompletionDateTime': datetime.datetime(2021, 1, 20, 9, 16, 23, 710000, tzinfo=tzlocal())},\n",
       " 'Statistics': {'EngineExecutionTimeInMillis': 3318,\n",
       "  'DataScannedInBytes': 1341,\n",
       "  'DataManifestLocation': 's3://aws-athena-query-results-593291632749-eu-west-1/1ea2597c-e49e-4ebc-af4d-491904bcb69f-manifest.csv',\n",
       "  'TotalExecutionTimeInMillis': 3608,\n",
       "  'QueryQueueTimeInMillis': 248,\n",
       "  'QueryPlanningTimeInMillis': 453,\n",
       "  'ServiceProcessingTimeInMillis': 42},\n",
       " 'WorkGroup': 'primary'}"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Let's say we want to add more data into our table\n",
    "\n",
    "sql = f\"\"\"\n",
    "INSERT INTO {new_db_name}.sales_report\n",
    "SELECT qtr as sales_quarter, sum(sales) AS total_sales\n",
    "FROM {source_db_name}.sales\n",
    "WHERE qtr IN (3,4)\n",
    "GROUP BY qtr\n",
    "\"\"\"\n",
    "print(sql)\n",
    "_ = pydb.start_query_execution_and_wait(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "dated-zambia",
   "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>sales_quarter</th>\n",
       "      <th>total_sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>26419.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>27558.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>30696.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>28167.78</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sales_quarter  total_sales\n",
       "0              3     26419.31\n",
       "1              4     27558.68\n",
       "2              2     30696.60\n",
       "3              1     28167.78"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Now lets see what our sales_report looks like\n",
    "pydb.read_sql_query(f\"SELECT * FROM {new_db_name}.sales_report\", database=None, ctas_approach=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cellular-france",
   "metadata": {},
   "source": [
    "### Step 4. Create another new table and insert new data into it this time using partitions\n",
    "\n",
    "We are going to do the same but this time partition the data and write new data into a new partition. Let's parition the data based on something like when the report was ran.\n",
    "\n",
    "> **Note:** that columns that are partitions should always be the last columns in your table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "charming-camping",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "CREATE TABLE new_db_isichei.daily_sales_report WITH\n",
      "(\n",
      "    external_location='s3://alpha-everyone/isichei/new_db/daily_sales_report/',\n",
      "    partitioned_by = ARRAY['report_date']\n",
      ") AS\n",
      "SELECT qtr as sales_quarter, sum(sales) AS total_sales,\n",
      "date '2021-01-01' AS report_date\n",
      "FROM source_db_isichei.sales\n",
      "GROUP BY qtr, date '2021-01-01'\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "{'QueryExecutionId': 'f48934aa-0139-4176-8410-3bed8a94da1f',\n",
       " 'Query': \"CREATE TABLE new_db_isichei.daily_sales_report WITH\\n(\\n    external_location='s3://alpha-everyone/isichei/new_db/daily_sales_report/',\\n    partitioned_by = ARRAY['report_date']\\n) AS\\nSELECT qtr as sales_quarter, sum(sales) AS total_sales,\\ndate '2021-01-01' AS report_date\\nFROM source_db_isichei.sales\\nGROUP BY qtr, date '2021-01-01'\",\n",
       " 'StatementType': 'DDL',\n",
       " 'ResultConfiguration': {'OutputLocation': 's3://aws-athena-query-results-593291632749-eu-west-1/tables/f48934aa-0139-4176-8410-3bed8a94da1f'},\n",
       " 'QueryExecutionContext': {},\n",
       " 'Status': {'State': 'SUCCEEDED',\n",
       "  'SubmissionDateTime': datetime.datetime(2021, 1, 20, 9, 17, 0, 764000, tzinfo=tzlocal()),\n",
       "  'CompletionDateTime': datetime.datetime(2021, 1, 20, 9, 17, 4, 567000, tzinfo=tzlocal())},\n",
       " 'Statistics': {'EngineExecutionTimeInMillis': 3623,\n",
       "  'DataScannedInBytes': 1341,\n",
       "  'DataManifestLocation': 's3://aws-athena-query-results-593291632749-eu-west-1/tables/f48934aa-0139-4176-8410-3bed8a94da1f-manifest.csv',\n",
       "  'TotalExecutionTimeInMillis': 3803,\n",
       "  'QueryQueueTimeInMillis': 133,\n",
       "  'QueryPlanningTimeInMillis': 598,\n",
       "  'ServiceProcessingTimeInMillis': 47},\n",
       " 'WorkGroup': 'primary'}"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales_report_s3_path = os.path.join(new_db_base_path, \"daily_sales_report/\")\n",
    "\n",
    "sql = f\"\"\"\n",
    "CREATE TABLE {new_db_name}.daily_sales_report WITH\n",
    "(\n",
    "    external_location='{sales_report_s3_path}',\n",
    "    partitioned_by = ARRAY['report_date']\n",
    ") AS\n",
    "SELECT qtr as sales_quarter, sum(sales) AS total_sales,\n",
    "date '2021-01-01' AS report_date\n",
    "FROM {source_db_name}.sales\n",
    "GROUP BY qtr, date '2021-01-01'\n",
    "\"\"\"\n",
    "print(sql)\n",
    "_ = pydb.start_query_execution_and_wait(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "ecological-occurrence",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "INSERT INTO new_db_isichei.daily_sales_report\n",
      "SELECT qtr as sales_quarter, sum(sales) AS total_sales,\n",
      "date '2021-01-02' AS report_date\n",
      "FROM source_db_isichei.sales\n",
      "GROUP BY qtr, date '2021-01-02'\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "{'QueryExecutionId': 'adf578c0-1e85-4320-9f3b-d970956484cc',\n",
       " 'Query': \"INSERT INTO new_db_isichei.daily_sales_report\\nSELECT qtr as sales_quarter, sum(sales) AS total_sales,\\ndate '2021-01-02' AS report_date\\nFROM source_db_isichei.sales\\nGROUP BY qtr, date '2021-01-02'\",\n",
       " 'StatementType': 'DML',\n",
       " 'ResultConfiguration': {'OutputLocation': 's3://aws-athena-query-results-593291632749-eu-west-1/adf578c0-1e85-4320-9f3b-d970956484cc'},\n",
       " 'QueryExecutionContext': {},\n",
       " 'Status': {'State': 'SUCCEEDED',\n",
       "  'SubmissionDateTime': datetime.datetime(2021, 1, 20, 9, 18, 27, 593000, tzinfo=tzlocal()),\n",
       "  'CompletionDateTime': datetime.datetime(2021, 1, 20, 9, 18, 31, 163000, tzinfo=tzlocal())},\n",
       " 'Statistics': {'EngineExecutionTimeInMillis': 3288,\n",
       "  'DataScannedInBytes': 1341,\n",
       "  'DataManifestLocation': 's3://aws-athena-query-results-593291632749-eu-west-1/adf578c0-1e85-4320-9f3b-d970956484cc-manifest.csv',\n",
       "  'TotalExecutionTimeInMillis': 3570,\n",
       "  'QueryQueueTimeInMillis': 242,\n",
       "  'QueryPlanningTimeInMillis': 281,\n",
       "  'ServiceProcessingTimeInMillis': 40},\n",
       " 'WorkGroup': 'primary'}"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Then assume we run the report the next day pretending our source database is updated every day\n",
    "sales_report_s3_path = os.path.join(new_db_base_path, \"daily_sales_report/\")\n",
    "\n",
    "sql = f\"\"\"\n",
    "INSERT INTO {new_db_name}.daily_sales_report\n",
    "SELECT qtr as sales_quarter, sum(sales) AS total_sales,\n",
    "date '2021-01-02' AS report_date\n",
    "FROM {source_db_name}.sales\n",
    "GROUP BY qtr, date '2021-01-02'\n",
    "\"\"\"\n",
    "print(sql)\n",
    "_ = pydb.start_query_execution_and_wait(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "earlier-strength",
   "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>sales_quarter</th>\n",
       "      <th>total_sales</th>\n",
       "      <th>report_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>26419.31</td>\n",
       "      <td>2021-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>27558.68</td>\n",
       "      <td>2021-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>28167.78</td>\n",
       "      <td>2021-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>30696.60</td>\n",
       "      <td>2021-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>30696.60</td>\n",
       "      <td>2021-01-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>3</td>\n",
       "      <td>26419.31</td>\n",
       "      <td>2021-01-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1</td>\n",
       "      <td>28167.78</td>\n",
       "      <td>2021-01-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>4</td>\n",
       "      <td>27558.68</td>\n",
       "      <td>2021-01-02</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sales_quarter  total_sales report_date\n",
       "0              3     26419.31  2021-01-01\n",
       "1              4     27558.68  2021-01-01\n",
       "2              1     28167.78  2021-01-01\n",
       "3              2     30696.60  2021-01-01\n",
       "4              2     30696.60  2021-01-02\n",
       "5              3     26419.31  2021-01-02\n",
       "6              1     28167.78  2021-01-02\n",
       "7              4     27558.68  2021-01-02"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pydb.read_sql_query(f\"SELECT * FROM {new_db_name}.daily_sales_report\", database=None, ctas_approach=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "presidential-stream",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Clean up\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",
    "for db_name in [source_db_name, new_db_name]:\n",
    "    if db_name in df_dbs[\"Database\"].to_list():\n",
    "        print(f\"{db_name} found deleting\")\n",
    "        wr.catalog.delete_database(\n",
    "            name=db_name\n",
    "        )"
   ]
  }
 ],
 "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": 5
}