setup_database.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Project 3: Data Warehouse in AWS using S3 and Redshift\n",
    "\n",
    "## Table of Contents\n",
    "- [Introduction](#intro)\n",
    "- [Load credentials](#load)\n",
    "- [Create clients for IAM, EC2, S3 and Redshift](#create_clients)\n",
    "- [Check out the sample data sources on S3](#check_s3)\n",
    "- [Create IAM role](#create_iam_role)\n",
    "- [Create Redshift cluster](#create_redshift)\n",
    "- [Open incoming TCP ports in the VPC to access the Redshift cluster](#modify_vpc)\n",
    "- [Create the Redshift cluster and insert the data](#create_cluster)\n",
    "- [Analysing data from the Redshift cluster](#analysis)\n",
    "- [Clean up the Redshift cluster and the IAM role](#clean)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "toc-hr-collapsed": true
   },
   "source": [
    "<a id='intro'></a>\n",
    "## Introduction\n",
    "\n",
    "First of all, we have to create a `IAM user` in the [AWS Management Console](console.aws.amazon.com).\n",
    "\n",
    "To that, you have to go to the service `IAM` and then click on `Users` and then the blue button `Add user`. First we have to assing the acces type as `Programmatic access` and then assign the policy `AdministratorAccess` in the `Attach existing policies directly` tab. Once we have the the user created, we save the credentials in the file `dwh.cfg`.\n",
    "\n",
    "```python\n",
    "[AWS]\n",
    "KEY=[USER_KEY]\n",
    "SECRET=[USER_SECRET]\n",
    "```\n",
    "\n",
    "The configuration of the IAM user in the AWS Management Console should look like the next image."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![IAM user](img/iam_user.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='load'></a>\n",
    "# Load credentials\n",
    "\n",
    "First I will load all the libraries that I will use in this report."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import boto3\n",
    "import json\n",
    "import configparser\n",
    "import psycopg2\n",
    "import seaborn as sns\n",
    "from botocore.exceptions import ClientError\n",
    "\n",
    "%matplotlib inline\n",
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once we have loaded the libraries and the credentials, we read the configuration file with the credentials to access to AWS and to create the Redshift cluster."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read the configuration file\n",
    "config = configparser.ConfigParser()\n",
    "config.read_file(open('dwh.cfg'))\n",
    "\n",
    "# AWS\n",
    "KEY                    = config.get('AWS','KEY')\n",
    "SECRET                 = config.get('AWS','SECRET')\n",
    "ZONE                   = config.get('AWS','ZONE')\n",
    "\n",
    "# DWH\n",
    "DWH_CLUSTER_TYPE       = config.get(\"DWH\",\"DWH_CLUSTER_TYPE\")\n",
    "DWH_NUM_NODES          = config.get(\"DWH\",\"DWH_NUM_NODES\")\n",
    "DWH_NODE_TYPE          = config.get(\"DWH\",\"DWH_NODE_TYPE\")\n",
    "DWH_CLUSTER_IDENTIFIER = config.get(\"DWH\",\"DWH_CLUSTER_IDENTIFIER\")\n",
    "DWH_DB                 = config.get(\"DWH\",\"DWH_DB\")\n",
    "DWH_DB_USER            = config.get(\"DWH\",\"DWH_DB_USER\")\n",
    "DWH_DB_PASSWORD        = config.get(\"DWH\",\"DWH_DB_PASSWORD\")\n",
    "DWH_PORT               = config.get(\"DWH\",\"DWH_PORT\")\n",
    "DWH_IAM_ROLE_NAME      = config.get(\"DWH\", \"DWH_IAM_ROLE_NAME\")\n",
    "\n",
    "# S3\n",
    "LOG_DATA               = config.get('S3', 'LOG_DATA')\n",
    "LOG_JSONPATH           = config.get('S3', 'LOG_JSONPATH')\n",
    "SONG_DATA              = config.get('S3', 'SONG_DATA')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, we check the credentials that we have loaded."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>Param</th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DWH_CLUSTER_TYPE</td>\n",
       "      <td>multi-node</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>DWH_NUM_NODES</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>DWH_NODE_TYPE</td>\n",
       "      <td>dc2.large</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DWH_CLUSTER_IDENTIFIER</td>\n",
       "      <td>dwhCluster</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DWH_DB</td>\n",
       "      <td>dwh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>DWH_DB_USER</td>\n",
       "      <td>dwhuser</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>DWH_DB_PASSWORD</td>\n",
       "      <td>Passw0rd</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>DWH_PORT</td>\n",
       "      <td>5439</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>DWH_IAM_ROLE_NAME</td>\n",
       "      <td>dwhRole1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    Param       Value\n",
       "0        DWH_CLUSTER_TYPE  multi-node\n",
       "1           DWH_NUM_NODES           2\n",
       "2           DWH_NODE_TYPE   dc2.large\n",
       "3  DWH_CLUSTER_IDENTIFIER  dwhCluster\n",
       "4                  DWH_DB         dwh\n",
       "5             DWH_DB_USER     dwhuser\n",
       "6         DWH_DB_PASSWORD    Passw0rd\n",
       "7                DWH_PORT        5439\n",
       "8       DWH_IAM_ROLE_NAME    dwhRole1"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check the credentials\n",
    "pd.DataFrame({\"Param\":\n",
    "                  [\"DWH_CLUSTER_TYPE\", \"DWH_NUM_NODES\", \"DWH_NODE_TYPE\", \"DWH_CLUSTER_IDENTIFIER\", \"DWH_DB\", \"DWH_DB_USER\", \"DWH_DB_PASSWORD\", \"DWH_PORT\", \"DWH_IAM_ROLE_NAME\"],\n",
    "              \"Value\":\n",
    "                  [DWH_CLUSTER_TYPE, DWH_NUM_NODES, DWH_NODE_TYPE, DWH_CLUSTER_IDENTIFIER, DWH_DB, DWH_DB_USER, DWH_DB_PASSWORD, DWH_PORT, DWH_IAM_ROLE_NAME]\n",
    "             })"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='create_clients'></a>\n",
    "## Create clients for IAM, EC2, S3 and Redshift\n",
    "\n",
    "With the IAM user that we have created before in the AWS Management Console we create the clients of the different services that we will use in this report using the AWS SDK for Python called `boto3`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Client for EC2\n",
    "ec2 = boto3.resource('ec2', region_name=ZONE, aws_access_key_id=KEY, aws_secret_access_key=SECRET)\n",
    "\n",
    "# Client for S3\n",
    "s3 = boto3.resource('s3', region_name=ZONE, aws_access_key_id=KEY, aws_secret_access_key=SECRET)\n",
    "\n",
    "# Client for IAM\n",
    "iam = boto3.client('iam', region_name=ZONE, aws_access_key_id=KEY, aws_secret_access_key=SECRET)\n",
    "\n",
    "# Client for Redshift\n",
    "redshift = boto3.client('redshift', region_name=ZONE, aws_access_key_id=KEY, aws_secret_access_key=SECRET)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='check_s3'></a>\n",
    "## Check out the sample data sources on S3\n",
    "\n",
    "Once we have the clients for each resource, I will check the number of documents that we will import into the Redshift cluster.\n",
    "\n",
    "This documents are in JSON format and are stored in public buckets offered by Udacity for this nanodegree."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total number of logs: 31\n"
     ]
    }
   ],
   "source": [
    "# Calculate the total number of JSON files in the folder 's3://udacity-dend/log_data'\n",
    "count_logs = sum(1 for _ in s3.Bucket('udacity-dend').objects.filter(Prefix='log_data'))\n",
    "\n",
    "print(\"Total number of logs: \" + str(count_logs))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total number of songs: 14897\n"
     ]
    }
   ],
   "source": [
    "# Calculate the total number of JSON files in the folder 's3://udacity-dend/song_data'\n",
    "count_songs = sum(1 for _ in s3.Bucket('udacity-dend').objects.filter(Prefix='song_data'))\n",
    "\n",
    "print(\"Total number of songs: \" + str(count_songs))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we can see, there are 31 logs and 14897 songs."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "toc-hr-collapsed": true
   },
   "source": [
    "<a id='create_iam_role'></a>\n",
    "## Create IAM role\n",
    "\n",
    "Now we create a new `IAM Role` that will allow the Redshift cluster to access S3 buckets (ReadOnly).\n",
    "\n",
    "First we create the `IAM Role` with a name that we have specified in the configuration file `dwh.cfg` in the parameter called `DWH_IAM_ROLE_NAME`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "IAM Role with name dwhRole1 has been created successfully!\n"
     ]
    }
   ],
   "source": [
    "# Create the IAM role\n",
    "try:\n",
    "    dwhRole = iam.create_role(\n",
    "        Path='/',\n",
    "        RoleName=DWH_IAM_ROLE_NAME,\n",
    "        Description = \"Allows Redshift clusters to call AWS services on your behalf.\",\n",
    "        AssumeRolePolicyDocument=json.dumps(\n",
    "            {'Statement': [{'Action': 'sts:AssumeRole',\n",
    "               'Effect': 'Allow',\n",
    "               'Principal': {'Service': 'redshift.amazonaws.com'}}],\n",
    "             'Version': '2012-10-17'})\n",
    "    )\n",
    "    print(\"IAM Role with name \" + str(DWH_IAM_ROLE_NAME) + \" has been created successfully!\") \n",
    "except Exception as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once we have the `IAM role` created, we attach the policy of `AmazonS3ReadOnlyAccess` to allow the role to access to S3 with only read access."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "200"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Attach the policy of `AmazonS3ReadOnlyAccess` to the role\n",
    "iam.attach_role_policy(RoleName=DWH_IAM_ROLE_NAME,\n",
    "                       PolicyArn=\"arn:aws:iam::aws:policy/AmazonS3ReadOnlyAccess\"\n",
    "                      )['ResponseMetadata']['HTTPStatusCode']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If the output is `200` it means that the policy has been attached successfully!\n",
    "\n",
    "Finally, we store the `ARN` value of this new `IAM role` that we have created. This `ARN` value we will use it later for the creation of the Redshift cluster."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get the ARN of the IAM role\n",
    "roleArn = iam.get_role(RoleName=DWH_IAM_ROLE_NAME)['Role']['Arn']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='create_redshift'></a>\n",
    "## Create Redshift cluster\n",
    "\n",
    "In this part we create the Redshift cluster using the client `redshift` and the IAM role that we have created before. The type of the cluster that we will create is configured in the document `dwh.cfg`.\n",
    "For complete arguments to `create_cluster`, see [docs](https://boto3.amazonaws.com/v1/documentation/api/latest/reference/services/redshift.html#Redshift.Client.create_cluster)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the Redshift cluster using the 'redshift' client\n",
    "try:\n",
    "    response = redshift.create_cluster(        \n",
    "        #HW\n",
    "        ClusterType=DWH_CLUSTER_TYPE,\n",
    "        NodeType=DWH_NODE_TYPE,\n",
    "        NumberOfNodes=int(DWH_NUM_NODES),\n",
    "\n",
    "        #Identifiers & Credentials\n",
    "        DBName=DWH_DB,\n",
    "        ClusterIdentifier=DWH_CLUSTER_IDENTIFIER,\n",
    "        MasterUsername=DWH_DB_USER,\n",
    "        MasterUserPassword=DWH_DB_PASSWORD,\n",
    "        \n",
    "        #Roles (for s3 access)\n",
    "        IamRoles=[roleArn]\n",
    "    )\n",
    "except Exception as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "toc-hr-collapsed": true
   },
   "source": [
    "Once we have created the cluster, we can see the properties of this one by running the next function.\n",
    "\n",
    "Be aware about the `ClusterStatus`, to setup the cluster can take some time (2-4 minuts) until the status is `available`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>Key</th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ClusterIdentifier</td>\n",
       "      <td>dwhcluster</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NodeType</td>\n",
       "      <td>dc2.large</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ClusterStatus</td>\n",
       "      <td>available</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MasterUsername</td>\n",
       "      <td>dwhuser</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DBName</td>\n",
       "      <td>dwh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Endpoint</td>\n",
       "      <td>{'Address': 'dwhcluster.cgtpb0o0xpfy.us-west-2.redshift.amazonaws.com', 'Port': 5439}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>VpcId</td>\n",
       "      <td>vpc-b4b115cc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>NumberOfNodes</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Key  \\\n",
       "0  ClusterIdentifier   \n",
       "1           NodeType   \n",
       "2      ClusterStatus   \n",
       "3     MasterUsername   \n",
       "4             DBName   \n",
       "5           Endpoint   \n",
       "6              VpcId   \n",
       "7      NumberOfNodes   \n",
       "\n",
       "                                                                                   Value  \n",
       "0                                                                             dwhcluster  \n",
       "1                                                                              dc2.large  \n",
       "2                                                                              available  \n",
       "3                                                                                dwhuser  \n",
       "4                                                                                    dwh  \n",
       "5  {'Address': 'dwhcluster.cgtpb0o0xpfy.us-west-2.redshift.amazonaws.com', 'Port': 5439}  \n",
       "6                                                                           vpc-b4b115cc  \n",
       "7                                                                                      2  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show the properties of the cluster\n",
    "def prettyRedshiftProps(props):\n",
    "    pd.set_option('display.max_colwidth', None)\n",
    "    keysToShow = [\"ClusterIdentifier\", \"NodeType\", \"ClusterStatus\", \"MasterUsername\", \"DBName\", \"Endpoint\", \"NumberOfNodes\", 'VpcId']\n",
    "    x = [(k, v) for k,v in props.items() if k in keysToShow]\n",
    "    return pd.DataFrame(data=x, columns=[\"Key\", \"Value\"])\n",
    "\n",
    "myClusterProps = redshift.describe_clusters(ClusterIdentifier=DWH_CLUSTER_IDENTIFIER)['Clusters'][0]\n",
    "prettyRedshiftProps(myClusterProps)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also check the status of the cluster by going to the [AWS Management Console](console.aws.amazon.com) and from there go to the service `Amazon Redshift` and then clic on `Clusters`. There you should be available to see the cluster like this:\n",
    "\n",
    "![Redshift summary](img/redshift_summary.png)\n",
    "\n",
    "If you clic on the name you will open a more detailed page of the properties and status of the cluster.\n",
    "\n",
    "![Redshift detailed](img/redshift_detail.png)\n",
    "\n",
    "Once the cluster it's online and with the status `available` we can run the next commands to get the URL of endpoint and the ARN role of the cluster."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Redshift enpoint: 'dwhcluster.cgtpb0o0xpfy.us-west-2.redshift.amazonaws.com'\n"
     ]
    }
   ],
   "source": [
    "# Get the Redshift endpoint and the ARN role\n",
    "DWH_ENDPOINT = myClusterProps['Endpoint']['Address']\n",
    "DWH_ROLE_ARN = myClusterProps['IamRoles'][0]['IamRoleArn']\n",
    "\n",
    "# Show the endpoint\n",
    "print(\"Redshift enpoint: '\" + DWH_ENDPOINT + \"'\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Is important to store this endpoint URL in the file `dwh.cfg` in the parameters `[DWH].DWH_ENDPOINT` and `[CLUSTER].HOST`.\n",
    "\n",
    "The value of `DWH_ROLE_ARN` it also needed to be saved in the file `dwh.cfg` in the parameter `[IAM_ROLE].ARN`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='modify_vpc'></a>\n",
    "## Open incoming TCP ports in the VPC to access the Redshift cluster\n",
    "\n",
    "The Redshift cluster that we have created before it is in a virtual private cloud (VPC) and this one by default it is NOT accessible by Internet. So, even that we have and endpoint to access to the cluster, this one will not work until we open the access from Internet.\n",
    "\n",
    "To do that, we need to modify the security groups of the VPC that has the Redshift cluster. So first, we need to know the VPC Id of our Redshift cluster."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'vpc-b4b115cc'"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Virtual private cloud ID (VPC ID)\n",
    "myClusterProps['VpcId']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'sg-7840e830'"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Security group ID\n",
    "myClusterProps['VpcSecurityGroups'][0]['VpcSecurityGroupId']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, we add the option that this cluster will be accessible from all Internet (0.0.0.0/0). This is just a demo, so this is just a quick and general solution for that. In cas of a production database, this is not recommended for security reasons, the best option in this case is to limit the range of allowed IPs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    defaultSg = ec2.SecurityGroup(id=myClusterProps['VpcSecurityGroups'][0]['VpcSecurityGroupId'])\n",
    "    defaultSg.authorize_ingress(\n",
    "        GroupName=defaultSg.group_name,\n",
    "        CidrIp='0.0.0.0/0',\n",
    "        IpProtocol='TCP',\n",
    "        FromPort=int(DWH_PORT),\n",
    "        ToPort=int(DWH_PORT)\n",
    "    )\n",
    "except Exception as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once we have modifies the VPC configuration we can see this in the [AWS Management Console](console.aws.amazon.com)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![VPC Configuration](img/vpc_conf.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='create_cluster'></a>\n",
    "## Create the Redshift cluster and insert the data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once we have created and configured the Redshift cluster, we need to first create the tables, and if it's needed drop the tables if they already exist.\n",
    "\n",
    "The next line of code will execute the code inside the script `create_tables.py`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[INFO] Connecting to the Redshift cluster...\n",
      "[INFO] [OK] Connected to the Redshift cluster!\n",
      "[INFO] Droping the tables if they exist in the Redshift cluster...\n",
      "[INFO] Creating the tables in the Redshift cluster...\n",
      "[INFO] End of the creation of the tables\n"
     ]
    }
   ],
   "source": [
    "# Create the tables in the database\n",
    "%run create_tables.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once we have the tables created, we will first insert the data in the 2 staging tables and then from there we will insert the data in the dimensional tables and the fact table using the code in the script `etl.py`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[INFO] Connecting to the Redshift cluster...\n",
      "[INFO] [OK] Connected to the Redshift cluster!\n",
      "[INFO] Loading staging tables to the Redshift cluster...\n",
      "[INFO] Inserting data from the staging tables to the dimensional tables...\n",
      "[INFO] End of the ETL\n"
     ]
    }
   ],
   "source": [
    "# Run the ETL script to upload data to the Redshift cluster\n",
    "%run etl.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='analysis'></a>\n",
    "## Analysing data from the Redshift cluster"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once we have the data correctly inserted in the dimensionals and fact table, we can test the connection to the database and check the data by executing some queries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Connect to the Redshift cluster\n",
    "conn = psycopg2.connect(\"host={} dbname={} user={} password={} port={}\".format(DWH_ENDPOINT,DWH_DB,DWH_DB_USER,DWH_DB_PASSWORD,DWH_PORT))\n",
    "cur = conn.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once we have the connection we will try some queries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "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>n</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>14896</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       n\n",
       "0  14896"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get number of row in the table songs\n",
    "pd.read_sql_query(\"\"\"SELECT COUNT(*) AS N FROM songs\"\"\", con = conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "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>n</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10025</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       n\n",
       "0  10025"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get number of row in the table artists\n",
    "pd.read_sql_query(\"\"\"SELECT COUNT(*) AS N FROM artists\"\"\", con = conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "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>n</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6813</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      n\n",
       "0  6813"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get number of row in the table time\n",
    "pd.read_sql_query(\"\"\"SELECT COUNT(*) AS N FROM time\"\"\", con = conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "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>n</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>96</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    n\n",
       "0  96"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get number of row in the table users\n",
    "pd.read_sql_query(\"\"\"SELECT COUNT(*) AS N FROM users\"\"\", con = conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "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>n</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6820</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      n\n",
       "0  6820"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get number of row in the table songplays\n",
    "pd.read_sql_query(\"\"\"SELECT COUNT(*) AS N FROM songplays\"\"\", con = conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYsAAAEGCAYAAACUzrmNAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4yLjEsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+j8jraAAAP+UlEQVR4nO3cf6zddX3H8edL6m+dgFwrtmVls5ninKjXgtEsKqwU5yyZSGA/6BxJY9apSzYdbokoSKZxkWk22DppLKgD1Bk65oYNYIxOpa04oCDhDmRth7baimNON9x7f9xP3bHcy6e4e85pe5+P5OZ8vu/v5/s975vcm9f5/jjfVBWSJD2Sx4y7AUnSwc+wkCR1GRaSpC7DQpLUZVhIkroWjLuBYTjmmGNq6dKl425Dkg4pW7du/VZVTcy07rAMi6VLl7Jly5ZxtyFJh5Qk9822ztNQkqQuw0KS1GVYSJK6DAtJUpdhIUnqMiwkSV2GhSSpy7CQJHUZFpKkrsPyG9xz4cVvvWLcLeggtPV95467BWksPLKQJHUZFpKkLsNCktRlWEiSugwLSVKXYSFJ6jIsJEldhoUkqcuwkCR1GRaSpC7DQpLUZVhIkroMC0lSl2EhSeoyLCRJXYaFJKnLsJAkdQ01LJJ8PcltSb6aZEurHZ1kU5K72+tRrZ4kH0wyleTWJC8a2M/qNv/uJKuH2bMk6eFGcWTxyqo6saom2/L5wA1VtQy4oS0DnA4saz9rgMtgOlyAC4CTgOXABfsCRpI0GuM4DbUK2NDGG4AzBupX1LQvAUcmORY4DdhUVXuqai+wCVg56qYlaT4bdlgU8JkkW5OsabWFVXV/G38DWNjGi4DtA9vuaLXZ6pKkEVkw5P2/vKp2JnkGsCnJ1wZXVlUlqbl4oxZGawCOO+64udilJKkZ6pFFVe1sr7uATzF9zeGb7fQS7XVXm74TWDKw+eJWm62+/3utq6rJqpqcmJiY619Fkua1oYVFkicneeq+MbACuB3YCOy7o2k1cG0bbwTObXdFnQw80E5XXQ+sSHJUu7C9otUkSSMyzNNQC4FPJdn3Ph+rqn9Mshm4Jsl5wH3AWW3+p4FXA1PA94A3AFTVniQXAZvbvAuras8Q+5Yk7WdoYVFV9wAvmKH+beCUGeoFrJ1lX+uB9XPdoyTpwPgNbklSl2EhSeoyLCRJXYaFJKnLsJAkdRkWkqQuw0KS1GVYSJK6DAtJUpdhIUnqMiwkSV2GhSSpy7CQJHUZFpKkLsNCktRlWEiSugwLSVKXYSFJ6jIsJEldhoUkqcuwkCR1GRaSpC7DQpLUZVhIkroMC0lSl2EhSeoyLCRJXYaFJKlr6GGR5IgktyS5ri0fn+TLSaaSXJ3kca3++LY81dYvHdjH21v9riSnDbtnSdKPG8WRxVuAOweW3wtcUlXPBvYC57X6ecDeVr+kzSPJCcDZwPOAlcClSY4YQd+SpGaoYZFkMfDLwIfacoBXAZ9oUzYAZ7TxqrZMW39Km78KuKqqflBV9wJTwPJh9i1J+nHDPrL4M+BtwP+05acD36mqh9ryDmBRGy8CtgO09Q+0+T+qz7CNJGkEhhYWSV4D7KqqrcN6j/3eb02SLUm27N69exRvKUnzxjCPLF4GvDbJ14GrmD799AHgyCQL2pzFwM423gksAWjrnwZ8e7A+wzY/UlXrqmqyqiYnJibm/reRpHlsaGFRVW+vqsVVtZTpC9Q3VtWvAzcBZ7Zpq4Fr23hjW6atv7GqqtXPbndLHQ8sA24eVt+SpIdb0J8y5/4QuCrJu4FbgMtb/XLgyiRTwB6mA4aq2pbkGuAO4CFgbVX9cPRtS9L8NZKwqKrPAp9t43uY4W6mqvo+8PpZtr8YuHh4HUqSHonf4JYkdRkWkqQuw0KS1GVYSJK6DAtJUpdhIUnqMiwkSV2GhSSpy7CQJHUZFpKkLsNCktRlWEiSugwLSVKXYSFJ6jIsJEldhoUkqcuwkCR1GRaSpC7DQpLUZVhIkroMC0lSl2EhSeoyLCRJXYaFJKnLsJAkdRkWkqQuw0KS1GVYSJK6DAtJUtfQwiLJE5LcnOSfk2xL8q5WPz7Jl5NMJbk6yeNa/fFteaqtXzqwr7e3+l1JThtWz5KkmQ3zyOIHwKuq6gXAicDKJCcD7wUuqapnA3uB89r884C9rX5Jm0eSE4CzgecBK4FLkxwxxL4lSfsZWljUtAfb4mPbTwGvAj7R6huAM9p4VVumrT8lSVr9qqr6QVXdC0wBy4fVtyTp4YZ6zSLJEUm+CuwCNgH/Anynqh5qU3YAi9p4EbAdoK1/AHj6YH2GbQbfa02SLUm27N69exi/jiTNWwsOZFKSxwOvA5YOblNVFz7SdlX1Q+DEJEcCnwKe8xN32lFV64B1AJOTkzWs95Gk+eiAwgK4lulP+luZvhbxqFTVd5LcBLwUODLJgnb0sBjY2abtBJYAO5IsAJ4GfHugvs/gNpKkETjQsFhcVSsfzY6TTAD/3YLiicAvMX3R+ibgTOAqYDXTQQSwsS1/sa2/saoqyUbgY0neDzwLWAbc/Gh6kST9/xxoWPxTkudX1W2PYt/HAhvanUuPAa6pquuS3AFcleTdwC3A5W3+5cCVSaaAPUzfAUVVbUtyDXAH8BCwtp3ekiSNyIGGxcuB30pyL9OnocL0DU+/MNsGVXUr8MIZ6vcww91MVfV94PWz7Oti4OID7FWSNMcONCxOH2oXkqSD2gGFRVXdN+xGJEkHL58NJUnqMiwkSV2GhSSpy7CQJHUZFpKkLsNCktRlWEiSugwLSVKXYSFJ6jIsJEldhoUkqcuwkCR1GRaSpC7DQpLUZVhIkroMC0lSl2EhSeoyLCRJXYaFJKnLsJAkdRkWkqQuw0KS1GVYSJK6DAtJUpdhIUnqMiwkSV1DC4skS5LclOSOJNuSvKXVj06yKcnd7fWoVk+SDyaZSnJrkhcN7Gt1m393ktXD6lmSNLNhHlk8BPx+VZ0AnAysTXICcD5wQ1UtA25oywCnA8vazxrgMpgOF+AC4CRgOXDBvoCRJI3G0MKiqu6vqq+08b8DdwKLgFXAhjZtA3BGG68CrqhpXwKOTHIscBqwqar2VNVeYBOwclh9S5IebiTXLJIsBV4IfBlYWFX3t1XfABa28SJg+8BmO1pttvr+77EmyZYkW3bv3j2n/UvSfDf0sEjyFOCTwO9V1XcH11VVATUX71NV66pqsqomJyYm5mKXkqRmqGGR5LFMB8VHq+pvW/mb7fQS7XVXq+8ElgxsvrjVZqtLkkZkmHdDBbgcuLOq3j+waiOw746m1cC1A/Vz211RJwMPtNNV1wMrkhzVLmyvaDVJ0ogsGOK+Xwb8JnBbkq+22h8B7wGuSXIecB9wVlv3aeDVwBTwPeANAFW1J8lFwOY278Kq2jPEviVJ+xlaWFTV54HMsvqUGeYXsHaWfa0H1s9dd5KkR8NvcEuSugwLSVKXYSFJ6jIsJEldhoUkqcuwkCR1GRaSpC7DQpLUZVhIkroMC0lSl2EhSeoyLCRJXYaFJKnLsJAkdRkWkqQuw0KS1GVYSJK6DAtJUpdhIUnqMiwkSV2GhSSpy7CQJHUtGHcDkh6df73w+eNuQQeh495x21D375GFJKnLsJAkdRkWkqQuw0KS1GVYSJK6hhYWSdYn2ZXk9oHa0Uk2Jbm7vR7V6knywSRTSW5N8qKBbVa3+XcnWT2sfiVJsxvmkcWHgZX71c4HbqiqZcANbRngdGBZ+1kDXAbT4QJcAJwELAcu2BcwkqTRGVpYVNXngD37lVcBG9p4A3DGQP2KmvYl4MgkxwKnAZuqak9V7QU28fAAkiQN2aivWSysqvvb+BvAwjZeBGwfmLej1WarP0ySNUm2JNmye/fuue1akua5sV3grqoCag73t66qJqtqcmJiYq52K0li9GHxzXZ6ifa6q9V3AksG5i1utdnqkqQRGnVYbAT23dG0Grh2oH5uuyvqZOCBdrrqemBFkqPahe0VrSZJGqGhPUgwyd8ArwCOSbKD6bua3gNck+Q84D7grDb908CrgSnge8AbAKpqT5KLgM1t3oVVtf9Fc0nSkA0tLKrqnFlWnTLD3ALWzrKf9cD6OWxNkvQo+Q1uSVKXYSFJ6jIsJEldhoUkqcuwkCR1GRaSpC7DQpLUZVhIkroMC0lSl2EhSeoyLCRJXYaFJKnLsJAkdRkWkqQuw0KS1GVYSJK6DAtJUpdhIUnqMiwkSV2GhSSpy7CQJHUZFpKkLsNCktRlWEiSugwLSVKXYSFJ6jIsJEldhoUkqeuQCYskK5PclWQqyfnj7keS5pNDIiySHAH8BXA6cAJwTpITxtuVJM0fh0RYAMuBqaq6p6r+C7gKWDXmniRp3lgw7gYO0CJg+8DyDuCkwQlJ1gBr2uKDSe4aUW/zwTHAt8bdxMEgf7p63C3ox/m3uc8FmYu9/PRsKw6VsOiqqnXAunH3cThKsqWqJsfdh7Q//zZH51A5DbUTWDKwvLjVJEkjcKiExWZgWZLjkzwOOBvYOOaeJGneOCROQ1XVQ0l+F7geOAJYX1XbxtzWfOLpPR2s/NsckVTVuHuQJB3kDpXTUJKkMTIsJEldhoUOWJILk5w6Q/0VSa4bR086vCV5c5I7k3x03L3Md4fEBW4dHKrqHePuQfPO7wCnVtWOfYUkC6rqoTH2NC95ZDGPJVma5GtJPto+vX0iyZOSvCPJ5iS3J1mXJG3+h5Oc2cYr27ZfAX51rL+IDktJ/hL4GeAfkjyQ5MokXwCuTDKR5JPt73Rzkpe1bZ6cZH2Sm5PcksTHAs0Rw0I/B1xaVc8Fvsv0J7k/r6qXVNXPA08EXjO4QZInAH8N/ArwYuCZo21Z80FVvRH4N+CVwCVMP0T01Ko6B/gAcElVvQR4HfChttkfAzdW1fK23fuSPHnkzR+GPA2l7VX1hTb+CPBm4N4kbwOeBBwNbAP+bmCb5wD3VtXdAEk+wv89l0salo1V9Z9tfCpwQjvoBfipJE8BVgCvTfIHrf4E4DjgzpF2ehgyLLT/F20KuBSYrKrtSd7J9D+cNG7/MTB+DHByVX1/cEI7Zfq6qvJBonPM01A6LslL2/jXgM+38bfaJ7UzZ9jma8DSJD/bls8Zco/S/j4DvGnfQpIT2/B64E0D19leOIbeDkuGhe4C1ia5EzgKuIzp6xG3M/2Pt3n/DdqnuTXA37cL3LtG164ETJ8unUxya5I7gDe2+kXAY4Fbk2xry5oDPu5jHkuyFLiuXciWpFl5ZCFJ6vLIQpLU5ZGFJKnLsJAkdRkWkqQuw0KaA0kePBT2Kf2kDAtJUpdhIc2xJG9tT0K9Ncm7Wu09SdYOzHnnvucXzTRfOtgYFtIcSrICWAYsB04EXpzkF4GrgbMGpp4FXP0I86WDig8SlObWivZzS1t+CrCsqi5P8owkzwImgL3tQY1vmWk+8LkR9y09IsNCmlsB/qSq/mqGdR9n+sGMz2T6SKM3XzpoeBpKmlvXA7/dnthLkkVJntHWXQ2czXRgfPwA5ksHDY8spDlUVZ9J8lzgi+0p2Q8CvwHsqqptSZ4K7Kyq+3vzx/ILSLPw2VCSpC5PQ0mSugwLSVKXYSFJ6jIsJEldhoUkqcuwkCR1GRaSpK7/BQa//jqoy6J0AAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Get number of different levels in the table songplays\n",
    "levels_types = pd.read_sql_query(\"\"\"\n",
    "SELECT level, COUNT(1) as n \n",
    "FROM songplays\n",
    "GROUP BY level\n",
    "\"\"\", con = conn)\n",
    "\n",
    "sns.barplot(data=levels_types, x = 'level', y='n');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we can see, most of the rows in the table `songplays` they have the **paid** level."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='clean'></a>\n",
    "## Clean up the Redshift cluster and the IAM role"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b><font color='red'>DO NOT RUN THIS UNLESS YOU ARE SURE <br/></span></b>\n",
    "\n",
    "Once we have executed all the analysis in our Redshift cluster and we don't need the data anymore we also can remove the Redshift cluster using code. In this case, first we will remove the Redshift cluster."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Delete the Redshift cluster\n",
    "redshift.delete_cluster(ClusterIdentifier=DWH_CLUSTER_IDENTIFIER,  SkipFinalClusterSnapshot=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check the status of the Redshift cluster\n",
    "myClusterProps = redshift.describe_clusters(ClusterIdentifier=DWH_CLUSTER_IDENTIFIER)['Clusters'][0]\n",
    "prettyRedshiftProps(myClusterProps)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once the Redshift cluster is deleted we delete the permission '0.0.0.0/0' in the security group of the Redshift cluster that we have created."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    # Get the security group\n",
    "    defaultSg = ec2.SecurityGroup(id=myClusterProps['VpcSecurityGroups'][0]['VpcSecurityGroupId'])\n",
    "    \n",
    "    # Revoke the permissions of '0.0.0.0/0' in the security group\n",
    "    defaultSg.revoke_ingress(IpPermissions=defaultSg.ip_permissions)\n",
    "except Exception as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, we detach and delete the IAM role that we created before for the Redshift cluster."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'ResponseMetadata': {'RequestId': '7e1eb85e-da3f-4af3-ae84-f7246b398b48',\n",
       "  'HTTPStatusCode': 200,\n",
       "  'HTTPHeaders': {'x-amzn-requestid': '7e1eb85e-da3f-4af3-ae84-f7246b398b48',\n",
       "   'content-type': 'text/xml',\n",
       "   'content-length': '200',\n",
       "   'date': 'Sun, 17 May 2020 17:07:57 GMT'},\n",
       "  'RetryAttempts': 0}}"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Delete the role policy attached to the IAM role that we have created\n",
    "iam.detach_role_policy(RoleName=DWH_IAM_ROLE_NAME, PolicyArn=\"arn:aws:iam::aws:policy/AmazonS3ReadOnlyAccess\")\n",
    "\n",
    "# Delete the IAM role\n",
    "iam.delete_role(RoleName=DWH_IAM_ROLE_NAME)"
   ]
  }
 ],
 "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.6.10"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  },
  "toc-autonumbering": false,
  "toc-showcode": false,
  "toc-showmarkdowntxt": false,
  "toc-showtags": false
 },
 "nbformat": 4,
 "nbformat_minor": 4
}