aws_sdk_create_redshift_cluster.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "# Creating Redshift Cluster using the AWS python SDK \n",
    "## An example of Infrastructure-as-code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import boto3\n",
    "import json"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "toc-hr-collapsed": true
   },
   "source": [
    "# STEP 0: Make sure you have an AWS secret and access key\n",
    "\n",
    "- Create a new IAM user in your AWS account\n",
    "- Give it `AdministratorAccess`, From `Attach existing policies directly` Tab\n",
    "- Take note of the access key and secret \n",
    "- create the file `dwh.cfg` in the same folder as this notebook and fill\n",
    "<font color='red'>\n",
    "<BR>\n",
    "[AWS]<BR>\n",
    "KEY= YOUR_AWS_KEY<BR>\n",
    "SECRET= YOUR_AWS_SECRET<BR>\n",
    "<font/>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "# Load DWH Params from a file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "import configparser\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",
    "\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",
    "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",
    "DWH_IAM_ROLE_NAME      = config.get(\"DWH\", \"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": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "## Create clients for EC2, S3, IAM, and Redshift"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "import boto3\n",
    "\n",
    "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": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "## Check out the sample data sources on S3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "sampleDbBucket =  s3.Bucket(\"awssampledbuswest2\")\n",
    "# TODO: Iterate over bucket objects starting with \"ssbgz\" and print\n",
    "for obj in sampleDbBucket.objects.filter(Prefix=\"ssbgz\"):\n",
    "    print(obj)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "toc-hr-collapsed": true
   },
   "source": [
    "## STEP 1: IAM ROLE\n",
    "- Create an IAM Role that makes Redshift able to access S3 bucket (ReadOnly)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# TODO: Create the IAM role\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 = \"Allows Redshift clusters to call AWS services on your behalf.\",\n",
    "        AssumeRolePolicyDocument=json.dumps(\n",
    "            {'Statement': [\n",
    "                {'Action': 'sts:AssumeRole',\n",
    "                 'Effect': 'Allow',\n",
    "                 'Principal': {'Service': 'redshift.amazonaws.com'}}],\n",
    "             'Version': '2012-10-17'})\n",
    "        )  \n",
    "except Exception as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# TODO: Attach Policy\n",
    "print('1.2 Attaching Policy')\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": null,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# TODO: Get and print the IAM role ARN\n",
    "print('1.3 Get the IAM role ARN')\n",
    "roleArn = iam.get_role(RoleName=DWH_IAM_ROLE_NAME)['Role']['Arn']\n",
    "\n",
    "print(roleArn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "## STEP 2:  Redshift Cluster\n",
    "\n",
    "- Create a RedShift Cluster\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": null,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "try:\n",
    "    response = redshift.create_cluster(        \n",
    "        # TODO: add parameters for hardware\n",
    "        ClusterType=DWH_CLUSTER_TYPE,\n",
    "        NodeType=DWH_NODE_TYPE,\n",
    "        NumberOfNodes=int(DWH_NUM_NODES),\n",
    "        \n",
    "        # TODO: add parameters for identifiers & credentials\n",
    "        DBName=DWH_DB,\n",
    "        ClusterIdentifier=DWH_CLUSTER_IDENTIFIER,\n",
    "        MasterUsername=DWH_DB_USER,\n",
    "        MasterUserPassword=DWH_DB_PASSWORD,        \n",
    "        \n",
    "        # TODO: add parameter for role (to allow s3 access)\n",
    "        IamRoles=[roleArn]\n",
    "    )\n",
    "except Exception as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "toc-hr-collapsed": true
   },
   "source": [
    "## 2.1 *Describe* the cluster to see its status\n",
    "- run this block several times until the cluster status becomes `Available`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "def prettyRedshiftProps(props):\n",
    "    pd.set_option('display.max_colwidth', -1)\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": {
    "editable": true,
    "toc-hr-collapsed": true
   },
   "source": [
    "<h2> 2.2 Take note of the cluster <font color='red'> endpoint and role ARN </font> </h2>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "<font color='red'>DO NOT RUN THIS unless the cluster status becomes \"Available\" </font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "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": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "## STEP 3: Open an incoming  TCP port to access the cluster endpoint"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "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": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "## STEP 4: Make sure you can connect to the clusterConnect to the cluster"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "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": {
    "editable": true
   },
   "source": [
    "## STEP 5: Clean up your resources"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "<b><font color='red'>DO NOT RUN THIS UNLESS YOU ARE SURE <br/> \n",
    "    We will be using these resources in the next exercises</span></b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "#### CAREFUL!!\n",
    "#-- Uncomment & run to delete the created resources\n",
    "redshift.delete_cluster( ClusterIdentifier=DWH_CLUSTER_IDENTIFIER,  SkipFinalClusterSnapshot=True)\n",
    "#### CAREFUL!!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "- run this block several times until the cluster really deleted"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "myClusterProps = redshift.describe_clusters(ClusterIdentifier=DWH_CLUSTER_IDENTIFIER)['Clusters'][0]\n",
    "prettyRedshiftProps(myClusterProps)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "#### CAREFUL!!\n",
    "#-- Uncomment & run to delete the created resources\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)\n",
    "#### CAREFUL!!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true
   },
   "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.6.3"
  },
  "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
}