create_cluster_to_ETL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "# Creating Redshift Cluster using the AWS python SDK"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import boto3\n",
    "import json\n",
    "import configparser\n",
    "import psycopg2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "## 1. Creation of the user on AWS.\n",
    "\n",
    "<p>First we need create a new IAM user in our AWS account Give it AdministratorAccess from existing policies. Copy the access key and secret Add to the the dwh.cfg file, the following:</p>\n",
    "\n",
    "<p>[AWS]\n",
    "\n",
    "KEY= YOUR_AWS_KEY\n",
    "\n",
    "SECRET= YOUR_AWS_SECRET</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "## 2. Load info from a file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "editable": 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>Param</th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>KEY</td>\n",
       "      <td>AKIAREYZ6OX6YATSCDRJ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>DWH_CLUSTER_TYPE</td>\n",
       "      <td>multi-node</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>DWH_NUM_NODES</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DWH_NODE_TYPE</td>\n",
       "      <td>dc2.large</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DWH_CLUSTER_IDENTIFIER</td>\n",
       "      <td>dwhCluster</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>DWH_DB</td>\n",
       "      <td>dwh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>DWH_DB_USER</td>\n",
       "      <td>dwhuser</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>DWH_DB_PASSWORD</td>\n",
       "      <td>Passw0rd</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>DWH_PORT</td>\n",
       "      <td>5439</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</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                     KEY  AKIAREYZ6OX6YATSCDRJ\n",
       "1        DWH_CLUSTER_TYPE            multi-node\n",
       "2           DWH_NUM_NODES                     4\n",
       "3           DWH_NODE_TYPE             dc2.large\n",
       "4  DWH_CLUSTER_IDENTIFIER            dwhCluster\n",
       "5                  DWH_DB                   dwh\n",
       "6             DWH_DB_USER               dwhuser\n",
       "7         DWH_DB_PASSWORD              Passw0rd\n",
       "8                DWH_PORT                  5439\n",
       "9       DWH_IAM_ROLE_NAME               dwhRole"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "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",
    "                  [\"KEY\",\"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",
    "                  [KEY, 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": 3,
   "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',aws_access_key_id=KEY,\n",
    "                     aws_secret_access_key=SECRET,\n",
    "                     region_name='us-west-2'\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\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "s3.ObjectSummary(bucket_name='awssampledbuswest2', key='ssbgz/')\n",
      "s3.ObjectSummary(bucket_name='awssampledbuswest2', key='ssbgz/customer0002_part_00.gz')\n",
      "s3.ObjectSummary(bucket_name='awssampledbuswest2', key='ssbgz/dwdate.tbl.gz')\n",
      "s3.ObjectSummary(bucket_name='awssampledbuswest2', key='ssbgz/lineorder0000_part_00.gz')\n",
      "s3.ObjectSummary(bucket_name='awssampledbuswest2', key='ssbgz/lineorder0001_part_00.gz')\n",
      "s3.ObjectSummary(bucket_name='awssampledbuswest2', key='ssbgz/lineorder0002_part_00.gz')\n",
      "s3.ObjectSummary(bucket_name='awssampledbuswest2', key='ssbgz/lineorder0003_part_00.gz')\n",
      "s3.ObjectSummary(bucket_name='awssampledbuswest2', key='ssbgz/lineorder0004_part_00.gz')\n",
      "s3.ObjectSummary(bucket_name='awssampledbuswest2', key='ssbgz/lineorder0005_part_00.gz')\n",
      "s3.ObjectSummary(bucket_name='awssampledbuswest2', key='ssbgz/lineorder0006_part_00.gz')\n",
      "s3.ObjectSummary(bucket_name='awssampledbuswest2', key='ssbgz/lineorder0007_part_00.gz')\n",
      "s3.ObjectSummary(bucket_name='awssampledbuswest2', key='ssbgz/part0000_part_00.gz')\n",
      "s3.ObjectSummary(bucket_name='awssampledbuswest2', key='ssbgz/part0001_part_00.gz')\n",
      "s3.ObjectSummary(bucket_name='awssampledbuswest2', key='ssbgz/part0002_part_00.gz')\n",
      "s3.ObjectSummary(bucket_name='awssampledbuswest2', key='ssbgz/part0003_part_00.gz')\n",
      "s3.ObjectSummary(bucket_name='awssampledbuswest2', key='ssbgz/supplier.tbl_0000_part_00.gz')\n",
      "s3.ObjectSummary(bucket_name='awssampledbuswest2', key='ssbgz/supplier0001_part_00.gz')\n",
      "s3.ObjectSummary(bucket_name='awssampledbuswest2', key='ssbgz/supplier0002_part_00.gz')\n",
      "s3.ObjectSummary(bucket_name='awssampledbuswest2', key='ssbgz/supplier0003_part_00.gz')\n"
     ]
    }
   ],
   "source": [
    "sampleDbBucket =  s3.Bucket(\"awssampledbuswest2\")\n",
    "\n",
    "for obj in sampleDbBucket.objects.filter(Prefix=\"ssbgz\"):\n",
    "    print (obj)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "## IAM ROLE\n",
    "- Create an IAM Role that makes Redshift able to access S3 bucket (ReadOnly)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1.1 Creating a new IAM Role\n"
     ]
    }
   ],
   "source": [
    "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",
    "              \"Version\": \"2012-10-17\",\n",
    "              \"Statement\": [\n",
    "                {\n",
    "                  \"Effect\": \"Allow\",\n",
    "                  \"Principal\": {\n",
    "                    \"Service\": \"redshift.amazonaws.com\"\n",
    "                  },\n",
    "                  \"Action\": \"sts:AssumeRole\"\n",
    "                }\n",
    "              ]\n",
    "            }),\n",
    "    )\n",
    "    \n",
    "except Exception as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1.2 Attaching Policy\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "200"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 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": 7,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1.3 Get the IAM role ARN\n"
     ]
    }
   ],
   "source": [
    "# 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": [
    "## Redshift Cluster\n",
    "- Create a RedShift Cluster\n",
    "- For complete arguments to create_cluster, see docs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "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": {
    "editable": true
   },
   "source": [
    "## Describe the cluster to see its status\n",
    "- run this block several times until the cluster status becomes Available"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "editable": 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>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.cjmqwm1viwss.us-west-2.redshift.amazonaws.com', 'Port': 5439}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>VpcId</td>\n",
       "      <td>vpc-069fcb5dc5c389e0d</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.cjmqwm1viwss.us-west-2.redshift.amazonaws.com', 'Port': 5439}  \n",
       "6  vpc-069fcb5dc5c389e0d                                                                  \n",
       "7  4                                                                                      "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "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
   },
   "source": [
    "## Take note of the cluster endpoint and role ARN\n",
    "- DO NOT RUN THIS unless the cluster status becomes \"Available"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# end point needs chnaged to east!\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": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "## Open an incoming TCP port to access the cluster endpoint"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ec2.SecurityGroup(id='sg-0b3cbd33f0c6ef64b')\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": [
    "try:\n",
    "    vpc = ec2.Vpc(id=myClusterProps['VpcId'])\n",
    "    defaultSg = list(vpc.security_groups.all())[0]\n",
    "    print(defaultSg)\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": {
    "editable": true
   },
   "source": [
    "## Connect to the cluster"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "postgresql://dwhuser:Passw0rd@dwhcluster.cjmqwm1viwss.us-west-2.redshift.amazonaws.com:5439/dwh\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'Connected: dwhuser@dwh'"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "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": [
    "## Create the tables\n",
    "- Sequence matters here. Run the queries first then create the tables before the ETL."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "!python sql_queries.py"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dropping tables\n"
     ]
    }
   ],
   "source": [
    "!python create_tables.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "## Execute the ETL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "!python etl.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "## Test the tables with SQL Queries\n",
    "- Run a simple SQL Query on each table to validate it works as intended.\n",
    "- Join several tables together to make a query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@dwhcluster.cjmqwm1viwss.us-west-2.redshift.amazonaws.com:5439/dwh\n",
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>songplay_id</th>\n",
       "        <th>start_time</th>\n",
       "        <th>user_id</th>\n",
       "        <th>level</th>\n",
       "        <th>song_id</th>\n",
       "        <th>artist_id</th>\n",
       "        <th>session_id</th>\n",
       "        <th>location</th>\n",
       "        <th>user_agent</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>19</td>\n",
       "        <td>2018-11-29 08:23:09</td>\n",
       "        <td>98</td>\n",
       "        <td>free</td>\n",
       "        <td>SOYJPKO12A6D4FDCEA</td>\n",
       "        <td>ARC3KQN1187FB54018</td>\n",
       "        <td>865</td>\n",
       "        <td>Philadelphia-Camden-Wilmington, PA-NJ-DE-MD</td>\n",
       "        <td>&quot;Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.143 Safari/537.36&quot;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>56</td>\n",
       "        <td>2018-11-27 04:25:00</td>\n",
       "        <td>8</td>\n",
       "        <td>free</td>\n",
       "        <td>SOWEUOO12A6D4F6D0C</td>\n",
       "        <td>ARQUMH41187B9AF699</td>\n",
       "        <td>833</td>\n",
       "        <td>Phoenix-Mesa-Scottsdale, AZ</td>\n",
       "        <td>&quot;Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/35.0.1916.153 Safari/537.36&quot;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>83</td>\n",
       "        <td>2018-11-06 08:49:19</td>\n",
       "        <td>12</td>\n",
       "        <td>free</td>\n",
       "        <td>SOBJDDA12A6BD53159</td>\n",
       "        <td>ARCS4GZ1187FB469EB</td>\n",
       "        <td>300</td>\n",
       "        <td>New York-Newark-Jersey City, NY-NJ-PA</td>\n",
       "        <td>Mozilla/5.0 (Windows NT 6.1; rv:31.0) Gecko/20100101 Firefox/31.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(19, datetime.datetime(2018, 11, 29, 8, 23, 9), 98, 'free', 'SOYJPKO12A6D4FDCEA', 'ARC3KQN1187FB54018', 865, 'Philadelphia-Camden-Wilmington, PA-NJ-DE-MD', '\"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.143 Safari/537.36\"'),\n",
       " (56, datetime.datetime(2018, 11, 27, 4, 25), 8, 'free', 'SOWEUOO12A6D4F6D0C', 'ARQUMH41187B9AF699', 833, 'Phoenix-Mesa-Scottsdale, AZ', '\"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/35.0.1916.153 Safari/537.36\"'),\n",
       " (83, datetime.datetime(2018, 11, 6, 8, 49, 19), 12, 'free', 'SOBJDDA12A6BD53159', 'ARCS4GZ1187FB469EB', 300, 'New York-Newark-Jersey City, NY-NJ-PA', 'Mozilla/5.0 (Windows NT 6.1; rv:31.0) Gecko/20100101 Firefox/31.0')]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM songplays LIMIT 3;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@dwhcluster.cjmqwm1viwss.us-west-2.redshift.amazonaws.com:5439/dwh\n",
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>user_id</th>\n",
       "        <th>first_name</th>\n",
       "        <th>last_name</th>\n",
       "        <th>gender</th>\n",
       "        <th>level</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Jizelle</td>\n",
       "        <td>Benjamin</td>\n",
       "        <td>F</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Isaac</td>\n",
       "        <td>Valdez</td>\n",
       "        <td>M</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>Alivia</td>\n",
       "        <td>Terrell</td>\n",
       "        <td>F</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2, 'Jizelle', 'Benjamin', 'F', 'free'),\n",
       " (3, 'Isaac', 'Valdez', 'M', 'free'),\n",
       " (4, 'Alivia', 'Terrell', 'F', 'free')]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM users LIMIT 3;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@dwhcluster.cjmqwm1viwss.us-west-2.redshift.amazonaws.com:5439/dwh\n",
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>song_id</th>\n",
       "        <th>title</th>\n",
       "        <th>artist_id</th>\n",
       "        <th>year</th>\n",
       "        <th>duration</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SOAAGBO12AB017CEBB</td>\n",
       "        <td>Death Of Emmett Till</td>\n",
       "        <td>AR60ODO1187FB4D9AB</td>\n",
       "        <td>2002</td>\n",
       "        <td>233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SOAAKLA12A58A7A3CC</td>\n",
       "        <td>Snow Day (LP Version)</td>\n",
       "        <td>ARGWNT41187FB463F1</td>\n",
       "        <td>0</td>\n",
       "        <td>211</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SOABHSF12AB0182977</td>\n",
       "        <td>Perdicion</td>\n",
       "        <td>ARFD5AT1187FB40779</td>\n",
       "        <td>0</td>\n",
       "        <td>281</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('SOAAGBO12AB017CEBB', 'Death Of Emmett Till', 'AR60ODO1187FB4D9AB', 2002, Decimal('233')),\n",
       " ('SOAAKLA12A58A7A3CC', 'Snow Day (LP Version)', 'ARGWNT41187FB463F1', 0, Decimal('211')),\n",
       " ('SOABHSF12AB0182977', 'Perdicion', 'ARFD5AT1187FB40779', 0, Decimal('281'))]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM songs LIMIT 3;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@dwhcluster.cjmqwm1viwss.us-west-2.redshift.amazonaws.com:5439/dwh\n",
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>artist_id</th>\n",
       "        <th>name</th>\n",
       "        <th>location</th>\n",
       "        <th>latitude</th>\n",
       "        <th>longitude</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AR00B1I1187FB433EB</td>\n",
       "        <td>Eagle-Eye Cherry</td>\n",
       "        <td>Stockholm, Sweden</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AR00DG71187B9B7FCB</td>\n",
       "        <td>Basslovers United</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AR00FVC1187FB5BE3E</td>\n",
       "        <td>Panda</td>\n",
       "        <td>Monterrey, NL, México</td>\n",
       "        <td>26</td>\n",
       "        <td>-100</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('AR00B1I1187FB433EB', 'Eagle-Eye Cherry', 'Stockholm, Sweden', None, None),\n",
       " ('AR00DG71187B9B7FCB', 'Basslovers United', '', None, None),\n",
       " ('AR00FVC1187FB5BE3E', 'Panda', 'Monterrey, NL, México', Decimal('26'), Decimal('-100'))]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM artists LIMIT 3;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@dwhcluster.cjmqwm1viwss.us-west-2.redshift.amazonaws.com:5439/dwh\n",
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>start_time</th>\n",
       "        <th>hour</th>\n",
       "        <th>day</th>\n",
       "        <th>week</th>\n",
       "        <th>month</th>\n",
       "        <th>year</th>\n",
       "        <th>weekday</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2018-11-01 21:01:46</td>\n",
       "        <td>21</td>\n",
       "        <td>1</td>\n",
       "        <td>44</td>\n",
       "        <td>11</td>\n",
       "        <td>2018</td>\n",
       "        <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2018-11-01 21:05:52</td>\n",
       "        <td>21</td>\n",
       "        <td>1</td>\n",
       "        <td>44</td>\n",
       "        <td>11</td>\n",
       "        <td>2018</td>\n",
       "        <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2018-11-01 21:08:16</td>\n",
       "        <td>21</td>\n",
       "        <td>1</td>\n",
       "        <td>44</td>\n",
       "        <td>11</td>\n",
       "        <td>2018</td>\n",
       "        <td>44</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2018, 11, 1, 21, 1, 46), 21, 1, 44, 11, 2018, 44),\n",
       " (datetime.datetime(2018, 11, 1, 21, 5, 52), 21, 1, 44, 11, 2018, 44),\n",
       " (datetime.datetime(2018, 11, 1, 21, 8, 16), 21, 1, 44, 11, 2018, 44)]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM time LIMIT 3;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@dwhcluster.cjmqwm1viwss.us-west-2.redshift.amazonaws.com:5439/dwh\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>first_name</th>\n",
       "        <th>last_name</th>\n",
       "        <th>gender</th>\n",
       "        <th>level</th>\n",
       "        <th>artist_name</th>\n",
       "        <th>user_agent</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jayden</td>\n",
       "        <td>Graves</td>\n",
       "        <td>M</td>\n",
       "        <td>paid</td>\n",
       "        <td>12 Stones</td>\n",
       "        <td>&quot;Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.143 Safari/537.36&quot;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Kate</td>\n",
       "        <td>Harrell</td>\n",
       "        <td>F</td>\n",
       "        <td>paid</td>\n",
       "        <td>Orishas</td>\n",
       "        <td>&quot;Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/37.0.2062.94 Safari/537.36&quot;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jayden</td>\n",
       "        <td>Graves</td>\n",
       "        <td>M</td>\n",
       "        <td>paid</td>\n",
       "        <td>Starting Rock Feat. Diva Avari</td>\n",
       "        <td>&quot;Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.143 Safari/537.36&quot;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jacqueline</td>\n",
       "        <td>Lynch</td>\n",
       "        <td>F</td>\n",
       "        <td>paid</td>\n",
       "        <td>Binary Star</td>\n",
       "        <td>&quot;Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4) AppleWebKit/537.78.2 (KHTML, like Gecko) Version/7.0.6 Safari/537.78.2&quot;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Kate</td>\n",
       "        <td>Harrell</td>\n",
       "        <td>F</td>\n",
       "        <td>paid</td>\n",
       "        <td>Andres Calamaro</td>\n",
       "        <td>&quot;Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/37.0.2062.94 Safari/537.36&quot;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Kate</td>\n",
       "        <td>Harrell</td>\n",
       "        <td>F</td>\n",
       "        <td>paid</td>\n",
       "        <td>Ben Kweller</td>\n",
       "        <td>&quot;Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/37.0.2062.94 Safari/537.36&quot;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jacqueline</td>\n",
       "        <td>Lynch</td>\n",
       "        <td>F</td>\n",
       "        <td>paid</td>\n",
       "        <td>Fergie</td>\n",
       "        <td>&quot;Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4) AppleWebKit/537.78.2 (KHTML, like Gecko) Version/7.0.6 Safari/537.78.2&quot;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Kate</td>\n",
       "        <td>Harrell</td>\n",
       "        <td>F</td>\n",
       "        <td>paid</td>\n",
       "        <td>Jesper Kyd</td>\n",
       "        <td>&quot;Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/37.0.2062.94 Safari/537.36&quot;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jayden</td>\n",
       "        <td>Graves</td>\n",
       "        <td>M</td>\n",
       "        <td>paid</td>\n",
       "        <td>Dash Berlin</td>\n",
       "        <td>&quot;Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.143 Safari/537.36&quot;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Avery</td>\n",
       "        <td>Martinez</td>\n",
       "        <td>F</td>\n",
       "        <td>paid</td>\n",
       "        <td>Muse</td>\n",
       "        <td>&quot;Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/37.0.2062.94 Safari/537.36&quot;</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Jayden', 'Graves', 'M', 'paid', '12 Stones', '\"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.143 Safari/537.36\"'),\n",
       " ('Kate', 'Harrell', 'F', 'paid', 'Orishas', '\"Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/37.0.2062.94 Safari/537.36\"'),\n",
       " ('Jayden', 'Graves', 'M', 'paid', 'Starting Rock Feat. Diva Avari', '\"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.143 Safari/537.36\"'),\n",
       " ('Jacqueline', 'Lynch', 'F', 'paid', 'Binary Star', '\"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4) AppleWebKit/537.78.2 (KHTML, like Gecko) Version/7.0.6 Safari/537.78.2\"'),\n",
       " ('Kate', 'Harrell', 'F', 'paid', 'Andres Calamaro', '\"Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/37.0.2062.94 Safari/537.36\"'),\n",
       " ('Kate', 'Harrell', 'F', 'paid', 'Ben Kweller', '\"Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/37.0.2062.94 Safari/537.36\"'),\n",
       " ('Jacqueline', 'Lynch', 'F', 'paid', 'Fergie', '\"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4) AppleWebKit/537.78.2 (KHTML, like Gecko) Version/7.0.6 Safari/537.78.2\"'),\n",
       " ('Kate', 'Harrell', 'F', 'paid', 'Jesper Kyd', '\"Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/37.0.2062.94 Safari/537.36\"'),\n",
       " ('Jayden', 'Graves', 'M', 'paid', 'Dash Berlin', '\"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.143 Safari/537.36\"'),\n",
       " ('Avery', 'Martinez', 'F', 'paid', 'Muse', '\"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/37.0.2062.94 Safari/537.36\"')]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# More complex query with joins\n",
    "qry = \"\"\"\n",
    "        SELECT \n",
    "        u.first_name,\n",
    "        u.last_name,\n",
    "        u.gender,\n",
    "        f.level,\n",
    "        a.name as artist_name,\n",
    "        f.user_agent\n",
    "        FROM songplays AS f\n",
    "        JOIN users AS u ON f.user_id = u.user_id\n",
    "        JOIN artists AS a ON f.artist_id = a.artist_id\n",
    "        WHERE f.level != 'free'\n",
    "        LIMIT 10;\n",
    "    \"\"\"\n",
    "\n",
    "%sql $qry"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@dwhcluster.cjmqwm1viwss.us-west-2.redshift.amazonaws.com:5439/dwh\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>level</th>\n",
       "        <th>level_count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>free</td>\n",
       "        <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>paid</td>\n",
       "        <td>271</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('free', 62), ('paid', 271)]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# test queries\n",
    "query = \"\"\"\n",
    "        SELECT \n",
    "        level, \n",
    "        COUNT(*) level_count \n",
    "        FROM songplays  \n",
    "        GROUP BY level; \n",
    "        \"\"\"\n",
    "\n",
    "%sql $query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@dwhcluster.cjmqwm1viwss.us-west-2.redshift.amazonaws.com:5439/dwh\n",
      "57 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>user_id</th>\n",
       "        <th>activity_cnt</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>49</td>\n",
       "        <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>97</td>\n",
       "        <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>80</td>\n",
       "        <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>44</td>\n",
       "        <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>73</td>\n",
       "        <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>88</td>\n",
       "        <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>15</td>\n",
       "        <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>29</td>\n",
       "        <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>24</td>\n",
       "        <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>36</td>\n",
       "        <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>25</td>\n",
       "        <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>58</td>\n",
       "        <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>95</td>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>30</td>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>85</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>42</td>\n",
       "        <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16</td>\n",
       "        <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>50</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>82</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>72</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>52</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>101</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>100</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>67</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>83</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>66</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>78</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>86</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>70</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>33</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>92</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>53</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>69</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>62</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>35</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>32</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>94</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>28</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>81</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>98</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>23</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>90</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>75</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>26</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>55</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>54</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>91</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>57</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>61</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>40</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>63</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(49, 42),\n",
       " (97, 32),\n",
       " (80, 31),\n",
       " (44, 21),\n",
       " (73, 18),\n",
       " (88, 17),\n",
       " (15, 15),\n",
       " (29, 13),\n",
       " (24, 13),\n",
       " (36, 13),\n",
       " (25, 10),\n",
       " (58, 9),\n",
       " (95, 8),\n",
       " (30, 8),\n",
       " (85, 7),\n",
       " (42, 6),\n",
       " (16, 6),\n",
       " (50, 5),\n",
       " (82, 3),\n",
       " (8, 3),\n",
       " (72, 2),\n",
       " (12, 2),\n",
       " (52, 2),\n",
       " (2, 2),\n",
       " (101, 2),\n",
       " (100, 2),\n",
       " (67, 2),\n",
       " (83, 2),\n",
       " (66, 2),\n",
       " (78, 2),\n",
       " (86, 2),\n",
       " (70, 2),\n",
       " (33, 2),\n",
       " (92, 2),\n",
       " (53, 2),\n",
       " (69, 2),\n",
       " (62, 1),\n",
       " (71, 1),\n",
       " (35, 1),\n",
       " (32, 1),\n",
       " (94, 1),\n",
       " (28, 1),\n",
       " (10, 1),\n",
       " (81, 1),\n",
       " (98, 1),\n",
       " (23, 1),\n",
       " (90, 1),\n",
       " (75, 1),\n",
       " (26, 1),\n",
       " (55, 1),\n",
       " (54, 1),\n",
       " (91, 1),\n",
       " (57, 1),\n",
       " (61, 1),\n",
       " (40, 1),\n",
       " (6, 1),\n",
       " (63, 1)]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query2 = \"\"\"SELECT user_id, COUNT(*) activity_cnt\n",
    "            FROM songplays\n",
    "            WHERE user_id IS NOT NULL\n",
    "            GROUP BY user_id\n",
    "            ORDER BY 2 DESC;\"\"\"\n",
    "\n",
    "%sql $query2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "## STEP 7: Clean up your resources\n",
    "DO NOT RUN THIS UNLESS YOU ARE SURE\n",
    "We will be using these resources in the next exercises"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Cluster': {'ClusterIdentifier': 'dwhcluster',\n",
       "  'NodeType': 'dc2.large',\n",
       "  'ClusterStatus': 'deleting',\n",
       "  'MasterUsername': 'dwhuser',\n",
       "  'DBName': 'dwh',\n",
       "  'Endpoint': {'Address': 'dwhcluster.cjmqwm1viwss.us-west-2.redshift.amazonaws.com',\n",
       "   'Port': 5439},\n",
       "  'ClusterCreateTime': datetime.datetime(2022, 11, 15, 14, 43, 57, 378000, tzinfo=tzlocal()),\n",
       "  'AutomatedSnapshotRetentionPeriod': 1,\n",
       "  'ClusterSecurityGroups': [],\n",
       "  'VpcSecurityGroups': [{'VpcSecurityGroupId': 'sg-0517a4f3e784a0c4a',\n",
       "    'Status': 'active'}],\n",
       "  'ClusterParameterGroups': [{'ParameterGroupName': 'default.redshift-1.0',\n",
       "    'ParameterApplyStatus': 'in-sync'}],\n",
       "  'ClusterSubnetGroupName': 'default',\n",
       "  'VpcId': 'vpc-069fcb5dc5c389e0d',\n",
       "  'AvailabilityZone': 'us-west-2c',\n",
       "  'PreferredMaintenanceWindow': 'sun:13:30-sun:14: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::078974449149:role/dwhRole',\n",
       "    'ApplyStatus': 'in-sync'}],\n",
       "  'MaintenanceTrackName': 'current'},\n",
       " 'ResponseMetadata': {'RequestId': '5b42466a-5a0c-43be-b2cf-2cecfd7627d1',\n",
       "  'HTTPStatusCode': 200,\n",
       "  'HTTPHeaders': {'x-amzn-requestid': '5b42466a-5a0c-43be-b2cf-2cecfd7627d1',\n",
       "   'content-type': 'text/xml',\n",
       "   'content-length': '2631',\n",
       "   'date': 'Tue, 15 Nov 2022 14:44:38 GMT'},\n",
       "  'RetryAttempts': 0}}"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#### CAREFUL!!\n",
    "#-- Uncomment & run to delete the created resources\n",
    "redshift.delete_cluster( ClusterIdentifier=DWH_CLUSTER_IDENTIFIER,  SkipFinalClusterSnapshot=True)\n",
    "#### CAREFUL!!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "editable": 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>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.cjmqwm1viwss.us-west-2.redshift.amazonaws.com', 'Port': 5439}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>VpcId</td>\n",
       "      <td>vpc-069fcb5dc5c389e0d</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.cjmqwm1viwss.us-west-2.redshift.amazonaws.com', 'Port': 5439}  \n",
       "6  vpc-069fcb5dc5c389e0d                                                                  \n",
       "7  4                                                                                      "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "myClusterProps = redshift.describe_clusters(ClusterIdentifier=DWH_CLUSTER_IDENTIFIER)['Clusters'][0]\n",
    "prettyRedshiftProps(myClusterProps)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'ResponseMetadata': {'RequestId': '672c501e-d3ba-4628-998d-d6a8ddbe874b',\n",
       "  'HTTPStatusCode': 200,\n",
       "  'HTTPHeaders': {'x-amzn-requestid': '672c501e-d3ba-4628-998d-d6a8ddbe874b',\n",
       "   'content-type': 'text/xml',\n",
       "   'content-length': '200',\n",
       "   'date': 'Tue, 15 Nov 2022 14:44:45 GMT'},\n",
       "  'RetryAttempts': 0}}"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "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": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "# The End!"
   ]
  },
  {
   "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"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}