0_launch_Redshift_cluster.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <center>Creating Redshift Cluster using the AWS Boto3 SDK</center>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import boto3\n",
    "import json\n",
    "import configparser"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### STEP 0: (Prerequisite) Create an IAM User with `AdministratorAccess` and Save AWS Secret and Access Key"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The following two steps were completed as prerequisites before running the code to launch the Redshift cluster:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. A **new IAM user** was created on **AWS Management Console** with an **`AdministratorAccess`** policy attached.\n",
    "\n",
    "2. An **Access key** and **Secret access key** were created in the **AWS Management Console**, then copied and stored in the `dwh.cfg` file located in the same folder as this notebook.\n",
    "```bash\n",
    "KEY= <MY_AWS_KEY>\n",
    "SECRET= <MY_AWS_SECRET>\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Load DWH Params from a Configuration File"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import the configparser module to read configuration files\n",
    "config = configparser.ConfigParser()\n",
    "# Read the configuration file (dwh.cfg) containing AWS and Redshift settings\n",
    "config.read_file(open('dwh.cfg'))\n",
    "\n",
    "# Retrieve AWS Access Key and Secret Access Key from the configuration file\n",
    "KEY                    = config.get('AWS', 'KEY')\n",
    "SECRET                 = config.get('AWS', 'SECRET')\n",
    "\n",
    "# Retrieve Redshift cluster properties from the configuration file\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",
    "\n",
    "# Retrieve Redshift database and cluster identification details\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",
    "\n",
    "# Retrieve the IAM role name for Redshift to access other AWS services (e.g., S3)\n",
    "DWH_IAM_ROLE_NAME      = config.get(\"DWH\", \"DWH_IAM_ROLE_NAME\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>Params</th>\n",
       "      <th>Values</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": [
       "                   Params      Values\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": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check the retrieved key DWH parameter values\n",
    "pd.DataFrame(\n",
    "    [\n",
    "        ['DWH_CLUSTER_TYPE', DWH_CLUSTER_TYPE],\n",
    "        ['DWH_NUM_NODES', DWH_NUM_NODES],\n",
    "        ['DWH_NODE_TYPE', DWH_NODE_TYPE],\n",
    "        ['DWH_CLUSTER_IDENTIFIER', DWH_CLUSTER_IDENTIFIER],\n",
    "        ['DWH_DB', DWH_DB],\n",
    "        ['DWH_DB_USER', DWH_DB_USER],\n",
    "        ['DWH_DB_PASSWORD', DWH_DB_PASSWORD],\n",
    "        ['DWH_PORT', DWH_PORT],\n",
    "        ['DWH_IAM_ROLE_NAME', DWH_IAM_ROLE_NAME],\n",
    "    ],\n",
    "    columns=['Params', 'Values']\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Create resources and clients for EC2, S3, IAM and Redshift\n",
    "\n",
    "To interact with **EC2** and **S3**, utilize `boto3.resource`; for **IAM** and **Redshift**, use `boto3.client`. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create an EC2 resource to interact with AWS EC2 service\n",
    "ec2 = boto3.resource(\n",
    "    'ec2',\n",
    "    region_name='us-west-2',\n",
    "    aws_access_key_id=KEY,\n",
    "    aws_secret_access_key=SECRET,\n",
    ")\n",
    "\n",
    "# Create an S3 resource to interact with AWS S3 service\n",
    "s3 = boto3.resource(\n",
    "    's3',\n",
    "    region_name='us-west-2',\n",
    "    aws_access_key_id=KEY,\n",
    "    aws_secret_access_key=SECRET,\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create an IAM client to interact with AWS IAM service\n",
    "iam = boto3.client(\n",
    "    'iam',\n",
    "    region_name='us-west-2',\n",
    "    aws_access_key_id=KEY,\n",
    "    aws_secret_access_key=SECRET,\n",
    ")\n",
    "\n",
    "# Create a Redshift client to interact with AWS Redshift service\n",
    "redshift = boto3.client(\n",
    "    'redshift',\n",
    "    region_name='us-west-2',\n",
    "    aws_access_key_id=KEY,\n",
    "    aws_secret_access_key=SECRET,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "toc-hr-collapsed": true
   },
   "source": [
    "### STEP 1: Create IAM Role\n",
    "- Create an IAM Role that enables Redshift to access S3 bucket (ReadOnly)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1.1 Creating a new role\n",
      "1.2 Attaching Policy\n",
      "1.3 Get the Amazon Resource Name (ARN) for the IAM role\n",
      "arn:aws:iam::980658774738:role/dwhRole\n"
     ]
    }
   ],
   "source": [
    "# Create a new IAM role that allows Redshift to interact with other AWS services\n",
    "try:\n",
    "    print('1.1 Creating a new role')\n",
    "    iam.create_role(\n",
    "        Path='/',\n",
    "        RoleName=DWH_IAM_ROLE_NAME,\n",
    "        Description='Allows Redshift cluster to call AWS services on my behalf.',\n",
    "        AssumeRolePolicyDocument=json.dumps(\n",
    "            {\n",
    "                'Statement': [\n",
    "                    {\n",
    "                        'Action': 'sts:AssumeRole',\n",
    "                        'Effect': 'Allow',\n",
    "                        'Principal': {\n",
    "                            'Service': 'redshift.amazonaws.com'\n",
    "                        }\n",
    "                    }\n",
    "                ],\n",
    "                'Version': '2012-10-17',\n",
    "            }\n",
    "        )\n",
    "    )\n",
    "except Exception as e:\n",
    "    print(e)\n",
    "\n",
    "# Attach a policy to the IAM role to allow Redshift to have \n",
    "# read-only access to S3\n",
    "print(\"1.2 Attaching Policy\")\n",
    "iam.attach_role_policy(\n",
    "    RoleName=DWH_IAM_ROLE_NAME,\n",
    "    PolicyArn='arn:aws:iam::aws:policy/AmazonS3ReadOnlyAccess',  # Attach AmazonS3ReadOnlyAccess policy\n",
    ")['ResponseMetadata']['HTTPStatusCode']\n",
    "\n",
    "# Retrieve and print the ARN of the newly created IAM role\n",
    "print(\"1.3 Get the Amazon Resource Name (ARN) for the IAM role\")\n",
    "roleArn = iam.get_role(\n",
    "    RoleName=DWH_IAM_ROLE_NAME\n",
    ")['Role']['Arn']\n",
    "print(roleArn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### STEP 2:  Launch Redshift Cluster"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a Redshift cluster with the specified configuration\n",
    "try:\n",
    "    response = redshift.create_cluster(\n",
    "        # Haredware configuration\n",
    "        ClusterType = DWH_CLUSTER_TYPE,\n",
    "        NodeType = DWH_NODE_TYPE,\n",
    "        NumberOfNodes = int(DWH_NUM_NODES),\n",
    "\n",
    "        # Identifiers and database credentials\n",
    "        DBName = DWH_DB,\n",
    "        ClusterIdentifier = DWH_CLUSTER_IDENTIFIER,\n",
    "        MasterUsername = DWH_DB_USER,\n",
    "        MasterUserPassword = DWH_DB_PASSWORD,\n",
    "\n",
    "        # IAM role for accessing S3\n",
    "        IamRoles = [roleArn],\n",
    "    )\n",
    "except Exception as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "toc-hr-collapsed": true
   },
   "source": [
    "#### 2.1 *Describe* the cluster to see its status"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "def prettyRedshiftProps(props):\n",
    "    \"\"\"Formats and displays specific Redshift cluster \n",
    "    properties in a pandas DataFrame for easier readability.\n",
    "    \n",
    "    Args:\n",
    "    - props: dict\n",
    "        A dictionary containing the Redshift cluster properties, \n",
    "        typically returned from AWS SDK (boto3). This dictionary \n",
    "        includes various details about the Redshift cluster, \n",
    "        such as cluster identifier, node type, status, username,\n",
    "        and more.\n",
    "        \n",
    "    Returns:\n",
    "    - A pandas Dataframe with the relevant Redshift cluster \n",
    "      properties, where the keys are listed in one column and\n",
    "      their corresponding values in another.\n",
    "    \"\"\"\n",
    "    # Avoid truncating long text in DataFrame output\n",
    "    pd.set_option('display.max_colwidth', -1)\n",
    "    \n",
    "    # Define the keys to be displayed\n",
    "    keysToShow = [\"ClusterIdentifier\", \"NodeType\", \n",
    "                  \"ClusterStatus\", \"MasterUsername\", \n",
    "                  \"DBName\", \"Endpoint\", \"NumberOfNodes\", \n",
    "                  'VpcId']\n",
    "    \n",
    "    # Filter the key-value pairs based on keysToShow\n",
    "    x = [\n",
    "        (k, v) for k, v in props.items() if k in keysToShow\n",
    "    ]\n",
    "    \n",
    "    # Return the filtered properties as a DataFrame\n",
    "    return pd.DataFrame(x, columns=[\"Key\", \"Value\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Run this block below several times until the cluster status becomes **Available**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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.cejcerbeak3k.us-west-2.redshift.amazonaws.com', 'Port': 5439}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>VpcId</td>\n",
       "      <td>vpc-08ae7bb554e3c899d</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.cejcerbeak3k.us-west-2.redshift.amazonaws.com', 'Port': 5439}  \n",
       "6  vpc-08ae7bb554e3c899d                                                                  \n",
       "7  4                                                                                      "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Retrieve detailed information about the Redshift cluster\n",
    "myClusterProps = redshift.describe_clusters(\n",
    "    ClusterIdentifier=DWH_CLUSTER_IDENTIFIER\n",
    ")['Clusters'][0]\n",
    "\n",
    "# Display the selected Redshift cluster properties in pandas DataFrame\n",
    "prettyRedshiftProps(myClusterProps)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "toc-hr-collapsed": true
   },
   "source": [
    "#### 2.2 Take note of the cluster <font color='red'> Endpoint and Role ARN </font>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<font color='red'>DO NOT RUN THIS until the cluster status becomes **Available**.</font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DWH_ENDPOINT ::  dwhcluster.cejcerbeak3k.us-west-2.redshift.amazonaws.com\n",
      "DWH_ROLE_ARN ::  arn:aws:iam::980658774738:role/dwhRole\n"
     ]
    }
   ],
   "source": [
    "# Extract the endpoint address of the Redshift cluster \n",
    "DWH_ENDPOINT = myClusterProps['Endpoint']['Address']\n",
    "\n",
    "# Extract IAM role ARN associated with the Redshift cluster\n",
    "DWH_ROLE_ARN = myClusterProps['IamRoles'][0]['IamRoleArn']\n",
    "\n",
    "# Print the Redshift cluster endpoint address\n",
    "print(\"DWH_ENDPOINT :: \", DWH_ENDPOINT)\n",
    "\n",
    "# Print IAM role ARN associated with the Redshift cluster\n",
    "print(\"DWH_ROLE_ARN :: \", DWH_ROLE_ARN)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `DWH_ENDPOINT` and `DWH_ROLE_ARN` were copied and stored in the `dwh.cfg` file located in the same folder as this notebook.\n",
    "```bash\n",
    "[CLUSTER]\n",
    "HOST=<DWH_ENDPOINT>\n",
    "\n",
    "[IAM_ROLE]\n",
    "ARN=<DWH_ROLE_ARN>\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### STEP 3: Open an incoming TCP port to access the cluster endpoint"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ec2.SecurityGroup(id='sg-087225a4ebbe1e00e')\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": [
    "# Authorize inbound traffic to the Redshift cluster's security group\n",
    "try:\n",
    "    # Retrieve the VPC associated with the Redshift cluster\n",
    "    vpc = ec2.Vpc(id=myClusterProps['VpcId'])\n",
    "    \n",
    "    # Get the default security group associated with the VPC\n",
    "    defaultSg = list(vpc.security_groups.all())[0]\n",
    "    # Print the details of the default security group for verification\n",
    "    print(defaultSg)\n",
    "    \n",
    "    # Authorize inbound traffic on the specified port for the security group\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": [
    "### STEP 4: Verify the connection to the Redshift cluster"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "postgresql://dwhuser:Passw0rd@dwhcluster.cejcerbeak3k.us-west-2.redshift.amazonaws.com:5439/dwh\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'Connected: dwhuser@dwh'"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Construct the connection string for PostgreSQL to \n",
    "# connect to the Redshift cluster\n",
    "conn_string = 'postgresql://{}:{}@{}:{}/{}'.format(\n",
    "    DWH_DB_USER, DWH_DB_PASSWORD, DWH_ENDPOINT, DWH_PORT, DWH_DB\n",
    ")\n",
    "# Print the connection string\n",
    "print(conn_string)\n",
    "\n",
    "# Use the SQL magic command to connect to the Redshift cluster\n",
    "%sql $conn_string"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### STEP 5: Clean up all AWS resources\n",
    "* The code below was executed at the end of the project to avoid unexpected AWS usage costs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Cluster': {'ClusterIdentifier': 'dwhcluster',\n",
       "  'NodeType': 'dc2.large',\n",
       "  'ClusterStatus': 'deleting',\n",
       "  'MasterUsername': 'dwhuser',\n",
       "  'DBName': 'dwh',\n",
       "  'Endpoint': {'Address': 'dwhcluster.cejcerbeak3k.us-west-2.redshift.amazonaws.com',\n",
       "   'Port': 5439},\n",
       "  'ClusterCreateTime': datetime.datetime(2024, 9, 9, 2, 44, 31, 934000, tzinfo=tzlocal()),\n",
       "  'AutomatedSnapshotRetentionPeriod': 1,\n",
       "  'ClusterSecurityGroups': [],\n",
       "  'VpcSecurityGroups': [{'VpcSecurityGroupId': 'sg-087225a4ebbe1e00e',\n",
       "    'Status': 'active'}],\n",
       "  'ClusterParameterGroups': [{'ParameterGroupName': 'default.redshift-1.0',\n",
       "    'ParameterApplyStatus': 'in-sync'}],\n",
       "  'ClusterSubnetGroupName': 'default',\n",
       "  'VpcId': 'vpc-08ae7bb554e3c899d',\n",
       "  'AvailabilityZone': 'us-west-2a',\n",
       "  'PreferredMaintenanceWindow': 'sat:10:30-sat:11:00',\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::980658774738:role/dwhRole',\n",
       "    'ApplyStatus': 'in-sync'}],\n",
       "  'MaintenanceTrackName': 'current'},\n",
       " 'ResponseMetadata': {'RequestId': '11fd8d73-3a3e-4434-8b09-138570d60432',\n",
       "  'HTTPStatusCode': 200,\n",
       "  'HTTPHeaders': {'x-amzn-requestid': '11fd8d73-3a3e-4434-8b09-138570d60432',\n",
       "   'content-type': 'text/xml',\n",
       "   'content-length': '2801',\n",
       "   'date': 'Mon, 09 Sep 2024 02:45:02 GMT'},\n",
       "  'RetryAttempts': 0}}"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Delete the Redshift cluster with the specified identifier\n",
    "redshift.delete_cluster(\n",
    "    ClusterIdentifier=DWH_CLUSTER_IDENTIFIER,\n",
    "    SkipFinalClusterSnapshot=True,\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>{'Port': 5439}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>VpcId</td>\n",
       "      <td>vpc-08ae7bb554e3c899d</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                  Value\n",
       "0  ClusterIdentifier  dwhcluster           \n",
       "1  NodeType           dc2.large            \n",
       "2  ClusterStatus      deleting             \n",
       "3  MasterUsername     dwhuser              \n",
       "4  DBName             dwh                  \n",
       "5  Endpoint           {'Port': 5439}       \n",
       "6  VpcId              vpc-08ae7bb554e3c899d\n",
       "7  NumberOfNodes      4                    "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Retrieve and display the properties of the deleting Redshift cluster\n",
    "try:\n",
    "    # Describe the properties of the Redshift cluster\n",
    "    myClusterProps = redshift.describe_clusters(\n",
    "        ClusterIdentifier=DWH_CLUSTER_IDENTIFIER\n",
    "    )['Clusters'][0]\n",
    "    \n",
    "    # Display the Redshift cluster properties in a pandas DataFrame\n",
    "    prettyRedshiftProps(myClusterProps)\n",
    "except Exception as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'ResponseMetadata': {'RequestId': 'f9052e9f-f43d-46a1-9189-b1f6cb2927c9',\n",
       "  'HTTPStatusCode': 200,\n",
       "  'HTTPHeaders': {'date': 'Mon, 09 Sep 2024 02:46:30 GMT',\n",
       "   'x-amzn-requestid': 'f9052e9f-f43d-46a1-9189-b1f6cb2927c9',\n",
       "   'content-type': 'text/xml',\n",
       "   'content-length': '200'},\n",
       "  'RetryAttempts': 0}}"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Detach the AmazonS3ReadOnlyAccess policy from the specified IAM role\n",
    "iam.detach_role_policy(\n",
    "    RoleName=DWH_IAM_ROLE_NAME,\n",
    "    PolicyArn=\"arn:aws:iam::aws:policy/AmazonS3ReadOnlyAccess\",\n",
    ")\n",
    "# Delete the IAM role after detaching the policy\n",
    "iam.delete_role(RoleName=DWH_IAM_ROLE_NAME)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.3"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": false,
   "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": 2
}