IaC-Redshift.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import boto3\n",
    "import configparser\n",
    "import json\n",
    "import pandas as pd\n",
    "import psycopg2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "config=configparser.ConfigParser()\n",
    "config.read_file(open('redshift-configuration.cfg'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "#IAM_USER\n",
    "KEY=config.get('IAM_USER', 'KEY')\n",
    "SECRET=config.get('IAM_USER','SECRET')\n",
    "#IAM_ROLE\n",
    "IAM_ROLE_NAME=config.get('IAM_ROLE', 'IAM_ROLE_NAME')\n",
    "#CLUSTER-CONFIGURATION\n",
    "CLUSTER_IDENTIFIER= config.get('CLUSTER_CONFIGURATION','CLUSTER_IDENTIFIER')\n",
    "CLUSTER_TYPE      = config.get('CLUSTER_CONFIGURATION','CLUSTER_TYPE')\n",
    "NODE_TYPE         = config.get('CLUSTER_CONFIGURATION','NODE_TYPE')\n",
    "NUM_NODES         = config.get('CLUSTER_CONFIGURATION','NUM_NODES')\n",
    "#DATABASE\n",
    "DB_NAME     = config.get('DATABASE_CONFIGURATION','DB_NAME')\n",
    "DB_USER     = config.get('DATABASE_CONFIGURATION','DB_USER')\n",
    "DB_PASSWORD = config.get('DATABASE_CONFIGURATION','DB_PASSWORD')\n",
    "DB_PORT     = config.get('DATABASE_CONFIGURATION','DB_PORT')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Client for IAM, EC2, S3, and Redshift"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "ec2 = boto3.resource('ec2', \n",
    "                    region_name=\"us-west-2\",\n",
    "                    aws_access_key_id     = KEY,\n",
    "                    aws_secret_access_key = SECRET\n",
    "                    )\n",
    "\n",
    "s3 = boto3.resource('s3',\n",
    "                   region_name='us-west-2',\n",
    "                   aws_access_key_id      = KEY,\n",
    "                   aws_secret_access_key  = SECRET\n",
    "                   )\n",
    "\n",
    "iam = boto3.client('iam', \n",
    "                region_name='us-west-2',\n",
    "                aws_access_key_id         = KEY,\n",
    "                aws_secret_access_key     = SECRET\n",
    "                )\n",
    "\n",
    "redshift = boto3.client('redshift',\n",
    "                      region_name='us-west-2',\n",
    "                      aws_access_key_id    = KEY,\n",
    "                      aws_secret_access_key= SECRET\n",
    "                      )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1.1 Creating a new IAM Role called 'IAM-FOR-data-modeling-on-Cloud'\n",
      "An error occurred (EntityAlreadyExists) when calling the CreateRole operation: Role with name iamRoleName-demo already exists.\n",
      "1.2 Attaching Policy\n",
      "1.3 Get the IAM role ARN\n",
      "arn:aws:iam::185187464837:role/iamRoleName-demo\n"
     ]
    }
   ],
   "source": [
    "from botocore.exceptions import ClientError\n",
    "\n",
    "#1.1 Create the role\n",
    "try:\n",
    "    print(\"1.1 Creating a new IAM Role called 'IAM-FOR-data-modeling-on-Cloud'\") \n",
    "    dwhRole = iam.create_role(\n",
    "        Path='/',\n",
    "        RoleName=IAM_ROLE_NAME,\n",
    "        Description = \"Allows Redshift clusters to call AWS services on your behalf.\",\n",
    "        AssumeRolePolicyDocument=json.dumps(\n",
    "            {'Statement': [{'Action': 'sts:AssumeRole','Effect': 'Allow','Principal': {'Service': 'redshift.amazonaws.com'}}],\n",
    "             'Version': '2012-10-17'})\n",
    "    )    \n",
    "except Exception as e:\n",
    "    print(e)\n",
    "    \n",
    "\n",
    "print(\"1.2 Attaching Policy\")\n",
    "\n",
    "iam.attach_role_policy(RoleName=IAM_ROLE_NAME,PolicyArn=\"arn:aws:iam::aws:policy/AmazonS3ReadOnlyAccess\")['ResponseMetadata']['HTTPStatusCode']\n",
    "\n",
    "print(\"1.3 Get the IAM role ARN\")\n",
    "roleArn = iam.get_role(RoleName=IAM_ROLE_NAME)['Role']['Arn']\n",
    "\n",
    "\n",
    "print(roleArn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## STEP2: Redshift Cluster"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create a Redshift Cluster"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    response=redshift.create_cluster(ClusterType       = CLUSTER_TYPE,\n",
    "                                     NodeType          = NODE_TYPE,\n",
    "                                     NumberOfNodes     = int(NUM_NODES),\n",
    "                                     DBName            = DB_NAME,\n",
    "                                     ClusterIdentifier = CLUSTER_IDENTIFIER,\n",
    "                                     MasterUsername    = DB_USER,\n",
    "                                     MasterUserPassword= DB_PASSWORD,\n",
    "                                     #Roles ( for s3 access)\n",
    "                                     IamRoles= [roleArn]\n",
    "                                        )\n",
    "except Exception as e : \n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "def description (props):\n",
    "    pd.set_option('display.max_colwidth',None)\n",
    "    keyToShow= [\"ClusterIdentifier\", \"NodeType\", \"ClusterStatus\", \"MasterUsername\", \"DBName\", \"Endpoint\", \"NumberOfNodes\", 'VpcId']\n",
    "    x = [(k,v) for k,v in props.items() if k in keyToShow]\n",
    "    return pd.DataFrame(data=x,columns=['Key','Value'])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "scrolled": true
   },
   "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>data-modeling-cloud</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>admin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DBName</td>\n",
       "      <td>database</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Endpoint</td>\n",
       "      <td>{'Address': 'data-modeling-cloud.cahjwy6ny05j.us-west-2.redshift.amazonaws.com', 'Port': 5439}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>VpcId</td>\n",
       "      <td>vpc-61eca419</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                                                                             data-modeling-cloud  \n",
       "1                                                                                       dc2.large  \n",
       "2                                                                                       available  \n",
       "3                                                                                           admin  \n",
       "4                                                                                        database  \n",
       "5  {'Address': 'data-modeling-cloud.cahjwy6ny05j.us-west-2.redshift.amazonaws.com', 'Port': 5439}  \n",
       "6                                                                                    vpc-61eca419  \n",
       "7                                                                                               4  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "myClusterProps = redshift.describe_clusters(ClusterIdentifier=CLUSTER_IDENTIFIER)['Clusters'][0]\n",
    "description( myClusterProps)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ENDPOINT = myClusterProps['Endpoint']['Address']\n",
    "ROLE_ARN = myClusterProps['IamRoles'][0]['IamRoleArn']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# write the endpoint to redshift-configuration.cfg\n",
    "with open('redshift-configuration.cfg', 'r') as file:\n",
    "    data = file.readlines()\n",
    "# 6&15 are the row numbers where to write the ROLE & ENDPOINT\n",
    "data[6]=\"ARN={}\\n\".format(ROLE_ARN) \n",
    "data[15]=\"HOST={}\\n\".format(ENDPOINT)\n",
    "\n",
    "with open('redshift-configuration.cfg', 'w') as f:\n",
    "    f.writelines( data )\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# this one is functional ( les parametre dans cfg sont dans l'order pour CLUSTER ) \n",
    "conn = psycopg2.connect(\"host={} dbname={} user={} password={} port={}\".format(*config['DATABASE-CONFIGURATION'].values()))\n",
    "cur=conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Clean up the AWS resources"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Cluster': {'ClusterIdentifier': 'data-modeling-cloud',\n",
       "  'NodeType': 'dc2.large',\n",
       "  'ClusterStatus': 'deleting',\n",
       "  'ClusterAvailabilityStatus': 'Modifying',\n",
       "  'MasterUsername': 'admin',\n",
       "  'DBName': 'database',\n",
       "  'Endpoint': {'Address': 'data-modeling-cloud.cahjwy6ny05j.us-west-2.redshift.amazonaws.com',\n",
       "   'Port': 5439},\n",
       "  'ClusterCreateTime': datetime.datetime(2021, 1, 25, 18, 58, 50, 531000, tzinfo=tzutc()),\n",
       "  'AutomatedSnapshotRetentionPeriod': 1,\n",
       "  'ManualSnapshotRetentionPeriod': -1,\n",
       "  'ClusterSecurityGroups': [],\n",
       "  'VpcSecurityGroups': [{'VpcSecurityGroupId': 'sg-1d670a33',\n",
       "    'Status': 'active'}],\n",
       "  'ClusterParameterGroups': [{'ParameterGroupName': 'default.redshift-1.0',\n",
       "    'ParameterApplyStatus': 'in-sync'}],\n",
       "  'ClusterSubnetGroupName': 'default',\n",
       "  'VpcId': 'vpc-61eca419',\n",
       "  'AvailabilityZone': 'us-west-2c',\n",
       "  'PreferredMaintenanceWindow': 'sun:06:00-sun:06: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::185187464837:role/iamRoleName-demo',\n",
       "    'ApplyStatus': 'in-sync'}],\n",
       "  'MaintenanceTrackName': 'current',\n",
       "  'DeferredMaintenanceWindows': [],\n",
       "  'NextMaintenanceWindowStartTime': datetime.datetime(2021, 1, 31, 6, 0, tzinfo=tzutc())},\n",
       " 'ResponseMetadata': {'RequestId': '7ec3f2ec-bcb6-4a55-a2a4-4675e37db302',\n",
       "  'HTTPStatusCode': 200,\n",
       "  'HTTPHeaders': {'x-amzn-requestid': '7ec3f2ec-bcb6-4a55-a2a4-4675e37db302',\n",
       "   'content-type': 'text/xml',\n",
       "   'content-length': '2571',\n",
       "   'vary': 'accept-encoding',\n",
       "   'date': 'Mon, 25 Jan 2021 19:12:52 GMT'},\n",
       "  'RetryAttempts': 0}}"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "redshift.delete_cluster(ClusterIdentifier= CLUSTER_IDENTIFIER, SkipFinalClusterSnapshot= True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>data-modeling-cloud</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>admin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DBName</td>\n",
       "      <td>database</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Endpoint</td>\n",
       "      <td>{'Address': 'data-modeling-cloud.cahjwy6ny05j.us-west-2.redshift.amazonaws.com', 'Port': 5439}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>VpcId</td>\n",
       "      <td>vpc-61eca419</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                                                                             data-modeling-cloud  \n",
       "1                                                                                       dc2.large  \n",
       "2                                                                                        deleting  \n",
       "3                                                                                           admin  \n",
       "4                                                                                        database  \n",
       "5  {'Address': 'data-modeling-cloud.cahjwy6ny05j.us-west-2.redshift.amazonaws.com', 'Port': 5439}  \n",
       "6                                                                                    vpc-61eca419  \n",
       "7                                                                                               4  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "myClusterProps=redshift.describe_clusters(ClusterIdentifier= CLUSTER_IDENTIFIER)['Clusters'][0]\n",
    "description( myClusterProps)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}