redshift_create.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Creating a Redshift Cluster\n",
    "### This Notebook has to be ran before any other scripts in order to create the Redshift cluster needed for the rest of the project"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Imports\n",
    "\n",
    "import pandas as pd\n",
    "import boto3\n",
    "import json\n",
    "import configparser"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>4</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>dwhRole</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           4\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     dwhRole"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Retrieve Parameters from the config file (dwh.cfg)\n",
    "\n",
    "config = configparser.ConfigParser()\n",
    "config.read_file(open('dwh.cfg'))\n",
    "\n",
    "KEY                    = config.get('AWS','KEY')\n",
    "SECRET                 = config.get('AWS','SECRET')\n",
    "SESSION_TOKEN          = config.get('AWS','SESSION_TOKEN')                \n",
    "\n",
    "DWH_CLUSTER_TYPE       = config.get(\"CLUSTER\",\"DWH_CLUSTER_TYPE\")\n",
    "DWH_NUM_NODES          = config.get(\"CLUSTER\",\"DWH_NUM_NODES\")\n",
    "DWH_NODE_TYPE          = config.get(\"CLUSTER\",\"DWH_NODE_TYPE\")\n",
    "\n",
    "DWH_CLUSTER_IDENTIFIER = config.get(\"CLUSTER\",\"DWH_CLUSTER_IDENTIFIER\")\n",
    "DWH_DB                 = config.get(\"CLUSTER\",\"DB_NAME\")\n",
    "DWH_DB_USER            = config.get(\"CLUSTER\",\"DB_USER\")\n",
    "DWH_DB_PASSWORD        = config.get(\"CLUSTER\",\"DB_PASSWORD\")\n",
    "DWH_PORT               = config.get(\"CLUSTER\",\"DB_PORT\")\n",
    "\n",
    "DWH_IAM_ROLE_NAME      = config.get(\"IAM_ROLE\", \"DWH_IAM_ROLE_NAME\")\n",
    "\n",
    "(DWH_DB_USER, DWH_DB_PASSWORD, DWH_DB)\n",
    "\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": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creating Clients for Redshift,  IAM , EC2 and S3\n",
    "\n",
    "redshift = boto3.client('redshift',\n",
    "                        aws_access_key_id=KEY,\n",
    "                        aws_secret_access_key=SECRET,\n",
    "                        aws_session_token=SESSION_TOKEN,\n",
    "                        region_name=\"us-west-2\")\n",
    "\n",
    "\n",
    "iam = boto3.client('iam',\n",
    "                   aws_access_key_id=KEY,\n",
    "                   aws_secret_access_key=SECRET,\n",
    "                   aws_session_token=SESSION_TOKEN,\n",
    "                   region_name=\"us-west-2\")\n",
    "\n",
    "ec2 = boto3.resource('ec2',\n",
    "                   aws_access_key_id=KEY,\n",
    "                   aws_secret_access_key=SECRET,\n",
    "                   aws_session_token=SESSION_TOKEN,\n",
    "                   region_name=\"us-west-2\")\n",
    "\n",
    "s3 = boto3.resource('s3',\n",
    "                  aws_access_key_id=KEY,\n",
    "                  aws_secret_access_key=SECRET,\n",
    "                  aws_session_token=SESSION_TOKEN,\n",
    "                  region_name=\"us-west-2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1.1 Creating a new IAM Role\n"
     ]
    }
   ],
   "source": [
    "# Create IAM role to access on ReadOnly S3 Buckets\n",
    "\n",
    "try:\n",
    "    print('1.1 Creating a new IAM Role')\n",
    "    dwhRole = iam.create_role(\n",
    "        Path='/',\n",
    "        RoleName=DWH_IAM_ROLE_NAME,\n",
    "        Description='Redshift able to access S3 bucket (ReadOnly)',\n",
    "        AssumeRolePolicyDocument=json.dumps(\n",
    "            {\n",
    "    \"Version\": \"2012-10-17\",\n",
    "    \"Statement\": [\n",
    "        {\n",
    "            \"Action\": 'sts:AssumeRole',\n",
    "            'Effect': 'Allow',\n",
    "            'Principal': {'Service':'redshift.amazonaws.com'}\n",
    "        }]\n",
    "            \n",
    "        }\n",
    "        )\n",
    "    )\n",
    "    \n",
    "\n",
    "except Exception as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "200"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Attaching Policy to DataWarehouse role\n",
    "\n",
    "iam.attach_role_policy(RoleName=DWH_IAM_ROLE_NAME,\n",
    "                      PolicyArn=\"arn:aws:iam::aws:policy/AmazonS3ReadOnlyAccess\"\n",
    "                      )['ResponseMetadata']['HTTPStatusCode']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get the Datawarehouse role\n",
    "\n",
    "roleArn = iam.get_role(RoleName=DWH_IAM_ROLE_NAME)['Role']['Arn']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creating the Redshift Cluster\n",
    "\n",
    "try:\n",
    "    response = redshift.create_cluster(        \n",
    "        ClusterType=DWH_CLUSTER_TYPE,\n",
    "        NodeType=DWH_NODE_TYPE,\n",
    "        NumberOfNodes=int(DWH_NUM_NODES),\n",
    "\n",
    "        DBName=DWH_DB,\n",
    "        ClusterIdentifier=DWH_CLUSTER_IDENTIFIER,\n",
    "        MasterUsername=DWH_DB_USER,\n",
    "        MasterUserPassword=DWH_DB_PASSWORD,\n",
    "        \n",
    "        IamRoles=[roleArn]\n",
    "    )\n",
    "except Exception as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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.cvmlouqtoltn.us-west-2.redshift.amazonaws.com', 'Port': 5439}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>VpcId</td>\n",
       "      <td>vpc-04cb4ca72b3f12ed7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>NumberOfNodes</td>\n",
       "      <td>4</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.cvmlouqtoltn.us-west-2.redshift.amazonaws.com', 'Port': 5439}  \n",
       "6                                                                  vpc-04cb4ca72b3f12ed7  \n",
       "7                                                                                      4  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Display parameters of Cluster for check\n",
    "\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": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DWH_ENDPOINT ::  dwhcluster.cvmlouqtoltn.us-west-2.redshift.amazonaws.com\n",
      "DWH_ROLE_ARN ::  arn:aws:iam::639508895380:role/dwhRole\n"
     ]
    }
   ],
   "source": [
    "# Print Endpoint & role\n",
    "\n",
    "DWH_ENDPOINT = myClusterProps['Endpoint']['Address']\n",
    "DWH_ROLE_ARN = myClusterProps['IamRoles'][0]['IamRoleArn']\n",
    "print(\"DWH_ENDPOINT :: \", DWH_ENDPOINT)\n",
    "print(\"DWH_ROLE_ARN :: \", DWH_ROLE_ARN)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ec2.SecurityGroup(id='sg-00688d3858ccb02e5')\n",
      "An error occurred (InvalidPermission.Duplicate) when calling the AuthorizeSecurityGroupIngress operation: the specified rule \"peer: 0.0.0.0/0, TCP, from port: 5439, to port: 5439, ALLOW\" already exists\n"
     ]
    }
   ],
   "source": [
    "# Open incoming traffic on TCP port\n",
    "\n",
    "try:\n",
    "    vpc = ec2.Vpc(id=myClusterProps['VpcId'])\n",
    "    defaultSg = list(vpc.security_groups.all())[0]\n",
    "    print(defaultSg)\n",
    "    \n",
    "    defaultSg.authorize_ingress(\n",
    "        GroupName=defaultSg.group_name,  # TODO: fill out\n",
    "        CidrIp='0.0.0.0/0',  # TODO: fill out\n",
    "        IpProtocol='TCP',  # TODO: fill out\n",
    "        FromPort=int(DWH_PORT),\n",
    "        ToPort=int(DWH_PORT)\n",
    "    )\n",
    "except Exception as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The sql extension is already loaded. To reload it, use:\n",
      "  %reload_ext sql\n"
     ]
    }
   ],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "postgresql://dwhuser:Passw0rd@dwhcluster.cvmlouqtoltn.us-west-2.redshift.amazonaws.com:5439/dwh\n"
     ]
    }
   ],
   "source": [
    "\n",
    "conn_string=\"postgresql://{}:{}@{}:{}/{}\".format(DWH_DB_USER, DWH_DB_PASSWORD, DWH_ENDPOINT, DWH_PORT,DWH_DB)\n",
    "print(conn_string)\n",
    "%sql $conn_string"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# In this section we decommission all components created\n",
    "\n",
    "### Make sure you follow this step once finished with the project to avoid incurring costs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Cluster': {'ClusterIdentifier': 'dwhcluster',\n",
       "  'NodeType': 'dc2.large',\n",
       "  'ClusterStatus': 'deleting',\n",
       "  'ClusterAvailabilityStatus': 'Modifying',\n",
       "  'MasterUsername': 'dwhuser',\n",
       "  'DBName': 'dwh',\n",
       "  'Endpoint': {'Address': 'dwhcluster.cvmlouqtoltn.us-west-2.redshift.amazonaws.com',\n",
       "   'Port': 5439},\n",
       "  'ClusterCreateTime': datetime.datetime(2021, 11, 29, 19, 16, 1, 796000, tzinfo=tzutc()),\n",
       "  'AutomatedSnapshotRetentionPeriod': 1,\n",
       "  'ManualSnapshotRetentionPeriod': -1,\n",
       "  'ClusterSecurityGroups': [],\n",
       "  'VpcSecurityGroups': [{'VpcSecurityGroupId': 'sg-00688d3858ccb02e5',\n",
       "    'Status': 'active'}],\n",
       "  'ClusterParameterGroups': [{'ParameterGroupName': 'default.redshift-1.0',\n",
       "    'ParameterApplyStatus': 'in-sync'}],\n",
       "  'ClusterSubnetGroupName': 'default',\n",
       "  'VpcId': 'vpc-04cb4ca72b3f12ed7',\n",
       "  'AvailabilityZone': 'us-west-2c',\n",
       "  'PreferredMaintenanceWindow': 'sun:13:00-sun:13:30',\n",
       "  'PendingModifiedValues': {},\n",
       "  'ClusterVersion': '1.0',\n",
       "  'AllowVersionUpgrade': True,\n",
       "  'NumberOfNodes': 4,\n",
       "  'PubliclyAccessible': True,\n",
       "  'Encrypted': False,\n",
       "  'Tags': [],\n",
       "  'EnhancedVpcRouting': False,\n",
       "  'IamRoles': [{'IamRoleArn': 'arn:aws:iam::639508895380:role/dwhRole',\n",
       "    'ApplyStatus': 'in-sync'}],\n",
       "  'MaintenanceTrackName': 'current',\n",
       "  'DeferredMaintenanceWindows': [],\n",
       "  'NextMaintenanceWindowStartTime': datetime.datetime(2021, 12, 5, 13, 0, tzinfo=tzutc()),\n",
       "  'TotalStorageCapacityInMegaBytes': 1600000,\n",
       "  'AquaConfiguration': {'AquaStatus': 'disabled',\n",
       "   'AquaConfigurationStatus': 'auto'}},\n",
       " 'ResponseMetadata': {'RequestId': '2740261c-08a0-4528-b350-71c534fea5ad',\n",
       "  'HTTPStatusCode': 200,\n",
       "  'HTTPHeaders': {'x-amzn-requestid': '2740261c-08a0-4528-b350-71c534fea5ad',\n",
       "   'content-type': 'text/xml',\n",
       "   'content-length': '2703',\n",
       "   'vary': 'accept-encoding',\n",
       "   'date': 'Mon, 29 Nov 2021 20:36:41 GMT'},\n",
       "  'RetryAttempts': 0}}"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Redshift Delete\n",
    "\n",
    "redshift.delete_cluster( ClusterIdentifier=DWH_CLUSTER_IDENTIFIER,  SkipFinalClusterSnapshot=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "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>deleting</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.cvmlouqtoltn.us-west-2.redshift.amazonaws.com', 'Port': 5439}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>VpcId</td>\n",
       "      <td>vpc-04cb4ca72b3f12ed7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>NumberOfNodes</td>\n",
       "      <td>4</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                                                                               deleting  \n",
       "3                                                                                dwhuser  \n",
       "4                                                                                    dwh  \n",
       "5  {'Address': 'dwhcluster.cvmlouqtoltn.us-west-2.redshift.amazonaws.com', 'Port': 5439}  \n",
       "6                                                                  vpc-04cb4ca72b3f12ed7  \n",
       "7                                                                                      4  "
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check that ClusterStatus is \"deleted\"\n",
    "\n",
    "# Can take a few minutes\n",
    "\n",
    "myClusterProps = redshift.describe_clusters(ClusterIdentifier=DWH_CLUSTER_IDENTIFIER)['Clusters'][0]\n",
    "prettyRedshiftProps(myClusterProps)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'ResponseMetadata': {'RequestId': '2205c6e4-f057-4762-acfe-0f2e6bee67fd',\n",
       "  'HTTPStatusCode': 200,\n",
       "  'HTTPHeaders': {'x-amzn-requestid': '2205c6e4-f057-4762-acfe-0f2e6bee67fd',\n",
       "   'content-type': 'text/xml',\n",
       "   'content-length': '200',\n",
       "   'date': 'Mon, 29 Nov 2021 20:36:49 GMT'},\n",
       "  'RetryAttempts': 0}}"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Detach policy and Delete role\n",
    "\n",
    "iam.detach_role_policy(RoleName=DWH_IAM_ROLE_NAME, PolicyArn=\"arn:aws:iam::aws:policy/AmazonS3ReadOnlyAccess\")\n",
    "iam.delete_role(RoleName=DWH_IAM_ROLE_NAME)"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "5cf85096884a479684da6e0d44e8d70b1b8794ba9d2ff59be26fc72e8ec63517"
  },
  "kernelspec": {
   "display_name": "Python 3.8.5 64-bit ('base': conda)",
   "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.5"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}