cluster_initialization.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "# Exercise 2: Creating Redshift Cluster using the AWS python SDK \n",
    "## An example of Infrastructure-as-code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "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",
    "- Edit 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": 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>DWH_CLUSTER_TYPE</td>\n",
       "      <td>multi-node</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>DWH_NUM_NODES</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>DWH_NODE_TYPE</td>\n",
       "      <td>dc2.large</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DWH_CLUSTER_IDENTIFIER</td>\n",
       "      <td>dwhCluster</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DWH_DB</td>\n",
       "      <td>dwh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>DWH_DB_USER</td>\n",
       "      <td>dwhuser</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>DWH_DB_PASSWORD</td>\n",
       "      <td>Passw0rd</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>DWH_PORT</td>\n",
       "      <td>5439</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>DWH_IAM_ROLE_NAME</td>\n",
       "      <td>dwhRole</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    Param       Value\n",
       "0        DWH_CLUSTER_TYPE  multi-node\n",
       "1           DWH_NUM_NODES           4\n",
       "2           DWH_NODE_TYPE   dc2.large\n",
       "3  DWH_CLUSTER_IDENTIFIER  dwhCluster\n",
       "4                  DWH_DB         dwh\n",
       "5             DWH_DB_USER     dwhuser\n",
       "6         DWH_DB_PASSWORD    Passw0rd\n",
       "7                DWH_PORT        5439\n",
       "8       DWH_IAM_ROLE_NAME     dwhRole"
      ]
     },
     "execution_count": 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",
    "                  [\"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 IAM, EC2, S3 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"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "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",
    "for obj in sampleDbBucket.objects.filter(Prefix=\"ssbgz\"):\n",
    "    print(obj)\n",
    "# for obj in sampleDbBucket.objects.all():\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": 4,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1.1 Creating a new IAM Role\n",
      "An error occurred (EntityAlreadyExists) when calling the CreateRole operation: Role with name dwhRole already exists.\n",
      "1.2 Attaching Policy\n",
      "1.3 Get the IAM role ARN\n",
      "arn:aws:iam::983828553154:role/dwhRole\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\") \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': [{'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)\n",
    "    \n",
    "    \n",
    "print(\"1.2 Attaching Policy\")\n",
    "\n",
    "iam.attach_role_policy(RoleName=DWH_IAM_ROLE_NAME,\n",
    "                       PolicyArn=\"arn:aws:iam::aws:policy/AmazonS3ReadOnlyAccess\"\n",
    "                      )['ResponseMetadata']['HTTPStatusCode']\n",
    "\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": 5,
   "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,
    "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": 17,
   "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.cflhkfkd7jex.us-west-2.redshift.amazonaws.com', 'Port': 5439}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>VpcId</td>\n",
       "      <td>vpc-36c5b54e</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.cflhkfkd7jex.us-west-2.redshift.amazonaws.com', 'Port': 5439}  \n",
       "6  vpc-36c5b54e                                                                           \n",
       "7  4                                                                                      "
      ]
     },
     "execution_count": 17,
     "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,
    "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": 19,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DWH_ENDPOINT ::  dwhcluster.cflhkfkd7jex.us-west-2.redshift.amazonaws.com\n",
      "DWH_ROLE_ARN ::  arn:aws:iam::983828553154:role/dwhRole\n"
     ]
    }
   ],
   "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 ednpoint"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ec2.SecurityGroup(id='sg-b07c7fe2')\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": [
    "# STEP 4: Make sure you can connect to the cluster"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "postgresql://dwhuser:Passw0rd@dwhcluster.cflhkfkd7jex.us-west-2.redshift.amazonaws.com:5439/dwh\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'Connected: dwhuser@dwh'"
      ]
     },
     "execution_count": 22,
     "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": [
    "### Testing the loaded tables (Note: You can make use of Redshift Query editor to query the tables)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@dwhcluster.cflhkfkd7jex.us-west-2.redshift.amazonaws.com:5439/dwh\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>artist_name</th>\n",
       "        <th>auth</th>\n",
       "        <th>first_name</th>\n",
       "        <th>gender</th>\n",
       "        <th>iteminsession</th>\n",
       "        <th>last_name</th>\n",
       "        <th>length</th>\n",
       "        <th>level</th>\n",
       "        <th>location</th>\n",
       "        <th>method</th>\n",
       "        <th>page</th>\n",
       "        <th>registration</th>\n",
       "        <th>sessionid</th>\n",
       "        <th>song</th>\n",
       "        <th>status</th>\n",
       "        <th>ts</th>\n",
       "        <th>useragent</th>\n",
       "        <th>userid</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Skillet</td>\n",
       "        <td>Logged In</td>\n",
       "        <td>Kevin</td>\n",
       "        <td>M</td>\n",
       "        <td>0</td>\n",
       "        <td>Arellano</td>\n",
       "        <td>178.02404</td>\n",
       "        <td>free</td>\n",
       "        <td>Harrisburg-Carlisle, PA</td>\n",
       "        <td>PUT</td>\n",
       "        <td>NextSong</td>\n",
       "        <td>1540006905796.0</td>\n",
       "        <td>153</td>\n",
       "        <td>Monster (Album Version)</td>\n",
       "        <td>200</td>\n",
       "        <td>1541126568796.0</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.125 Safari/537.36&quot;</td>\n",
       "        <td>66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>Logged In</td>\n",
       "        <td>Kynnedi</td>\n",
       "        <td>F</td>\n",
       "        <td>0</td>\n",
       "        <td>Sanchez</td>\n",
       "        <td>None</td>\n",
       "        <td>free</td>\n",
       "        <td>Cedar Rapids, IA</td>\n",
       "        <td>GET</td>\n",
       "        <td>Home</td>\n",
       "        <td>1541079034796.0</td>\n",
       "        <td>88</td>\n",
       "        <td>None</td>\n",
       "        <td>200</td>\n",
       "        <td>1541149530796.0</td>\n",
       "        <td>&quot;Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.125 Safari/537.36&quot;</td>\n",
       "        <td>89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Kylie Auldist</td>\n",
       "        <td>Logged In</td>\n",
       "        <td>Lily</td>\n",
       "        <td>F</td>\n",
       "        <td>8</td>\n",
       "        <td>Koch</td>\n",
       "        <td>265.92608</td>\n",
       "        <td>paid</td>\n",
       "        <td>Chicago-Naperville-Elgin, IL-IN-WI</td>\n",
       "        <td>PUT</td>\n",
       "        <td>NextSong</td>\n",
       "        <td>1541048010796.0</td>\n",
       "        <td>172</td>\n",
       "        <td>Ship Inside A Bottle</td>\n",
       "        <td>200</td>\n",
       "        <td>1541151060796.0</td>\n",
       "        <td>&quot;Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Ubuntu Chromium/36.0.1985.125 Chrome/36.0.1985.125 Safari/537.36&quot;</td>\n",
       "        <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Commit Suicide</td>\n",
       "        <td>Logged In</td>\n",
       "        <td>Lily</td>\n",
       "        <td>F</td>\n",
       "        <td>16</td>\n",
       "        <td>Koch</td>\n",
       "        <td>105.482</td>\n",
       "        <td>paid</td>\n",
       "        <td>Chicago-Naperville-Elgin, IL-IN-WI</td>\n",
       "        <td>PUT</td>\n",
       "        <td>NextSong</td>\n",
       "        <td>1541048010796.0</td>\n",
       "        <td>172</td>\n",
       "        <td>In All This Revelation</td>\n",
       "        <td>200</td>\n",
       "        <td>1541152940796.0</td>\n",
       "        <td>&quot;Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Ubuntu Chromium/36.0.1985.125 Chrome/36.0.1985.125 Safari/537.36&quot;</td>\n",
       "        <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>The Black Keys</td>\n",
       "        <td>Logged In</td>\n",
       "        <td>Lily</td>\n",
       "        <td>F</td>\n",
       "        <td>24</td>\n",
       "        <td>Koch</td>\n",
       "        <td>294.76526</td>\n",
       "        <td>paid</td>\n",
       "        <td>Chicago-Naperville-Elgin, IL-IN-WI</td>\n",
       "        <td>PUT</td>\n",
       "        <td>NextSong</td>\n",
       "        <td>1541048010796.0</td>\n",
       "        <td>172</td>\n",
       "        <td>The Lengths</td>\n",
       "        <td>200</td>\n",
       "        <td>1541154252796.0</td>\n",
       "        <td>&quot;Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Ubuntu Chromium/36.0.1985.125 Chrome/36.0.1985.125 Safari/537.36&quot;</td>\n",
       "        <td>15</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Skillet', 'Logged In', 'Kevin', 'M', 0, 'Arellano', 178.02404, 'free', 'Harrisburg-Carlisle, PA', 'PUT', 'NextSong', 1540006905796.0, 153, 'Monster (Album Version)', 200, 1541126568796.0, '\"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.125 Safari/537.36\"', 66),\n",
       " (None, 'Logged In', 'Kynnedi', 'F', 0, 'Sanchez', None, 'free', 'Cedar Rapids, IA', 'GET', 'Home', 1541079034796.0, 88, None, 200, 1541149530796.0, '\"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.125 Safari/537.36\"', 89),\n",
       " ('Kylie Auldist', 'Logged In', 'Lily', 'F', 8, 'Koch', 265.92608, 'paid', 'Chicago-Naperville-Elgin, IL-IN-WI', 'PUT', 'NextSong', 1541048010796.0, 172, 'Ship Inside A Bottle', 200, 1541151060796.0, '\"Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Ubuntu Chromium/36.0.1985.125 Chrome/36.0.1985.125 Safari/537.36\"', 15),\n",
       " ('Commit Suicide', 'Logged In', 'Lily', 'F', 16, 'Koch', 105.482, 'paid', 'Chicago-Naperville-Elgin, IL-IN-WI', 'PUT', 'NextSong', 1541048010796.0, 172, 'In All This Revelation', 200, 1541152940796.0, '\"Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Ubuntu Chromium/36.0.1985.125 Chrome/36.0.1985.125 Safari/537.36\"', 15),\n",
       " ('The Black Keys', 'Logged In', 'Lily', 'F', 24, 'Koch', 294.76526, 'paid', 'Chicago-Naperville-Elgin, IL-IN-WI', 'PUT', 'NextSong', 1541048010796.0, 172, 'The Lengths', 200, 1541154252796.0, '\"Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Ubuntu Chromium/36.0.1985.125 Chrome/36.0.1985.125 Safari/537.36\"', 15)]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM staging_events LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@dwhcluster.cflhkfkd7jex.us-west-2.redshift.amazonaws.com:5439/dwh\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>song_id</th>\n",
       "        <th>num_songs</th>\n",
       "        <th>title</th>\n",
       "        <th>artist_name</th>\n",
       "        <th>artist_latitude</th>\n",
       "        <th>year</th>\n",
       "        <th>duration</th>\n",
       "        <th>artist_id</th>\n",
       "        <th>artist_longitude</th>\n",
       "        <th>artist_location</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SOKTJDS12AF72A25E5</td>\n",
       "        <td>1</td>\n",
       "        <td>Drown In My Own Tears (24-Bit Digitally Remastered 04)</td>\n",
       "        <td>The Smithereens</td>\n",
       "        <td>40.57885</td>\n",
       "        <td>0</td>\n",
       "        <td>192.522</td>\n",
       "        <td>ARA23XO1187B9AF18F</td>\n",
       "        <td>-74.21956</td>\n",
       "        <td>Carteret, New Jersey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SONQBUB12A6D4F8ED0</td>\n",
       "        <td>1</td>\n",
       "        <td>Angie (1993 Digital Remaster)</td>\n",
       "        <td>The Rolling Stones</td>\n",
       "        <td>51.50632</td>\n",
       "        <td>0</td>\n",
       "        <td>271.49016</td>\n",
       "        <td>ARFCUN31187B9AD578</td>\n",
       "        <td>-0.12714</td>\n",
       "        <td>London, England</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SOHOXLZ12A6D4F8FC0</td>\n",
       "        <td>1</td>\n",
       "        <td>Les Gens Raisonnables</td>\n",
       "        <td>Mickey 3D</td>\n",
       "        <td>None</td>\n",
       "        <td>2003</td>\n",
       "        <td>207.12444</td>\n",
       "        <td>ARY0RQP1187FB48B93</td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SOXQDSK12A6D4FA909</td>\n",
       "        <td>1</td>\n",
       "        <td>La Notte Dell&#x27;11 Ottobre</td>\n",
       "        <td>Massimo Volume</td>\n",
       "        <td>None</td>\n",
       "        <td>1995</td>\n",
       "        <td>270.78485</td>\n",
       "        <td>ARIVQ621187FB5A88D</td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SOGXFIF12A58A78CC4</td>\n",
       "        <td>1</td>\n",
       "        <td>Hanging On (Medium Key Performance Track Without Background Vocals)</td>\n",
       "        <td>Britt Nicole</td>\n",
       "        <td>35.66693</td>\n",
       "        <td>0</td>\n",
       "        <td>204.06812</td>\n",
       "        <td>AR5LZJD1187FB4C5E5</td>\n",
       "        <td>-80.46935</td>\n",
       "        <td> NC</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('SOKTJDS12AF72A25E5', 1, 'Drown In My Own Tears (24-Bit Digitally Remastered 04)', 'The Smithereens', 40.57885, 0, 192.522, 'ARA23XO1187B9AF18F', -74.21956, 'Carteret, New Jersey'),\n",
       " ('SONQBUB12A6D4F8ED0', 1, 'Angie (1993 Digital Remaster)', 'The Rolling Stones', 51.50632, 0, 271.49016, 'ARFCUN31187B9AD578', -0.12714, 'London, England'),\n",
       " ('SOHOXLZ12A6D4F8FC0', 1, 'Les Gens Raisonnables', 'Mickey 3D', None, 2003, 207.12444, 'ARY0RQP1187FB48B93', None, ''),\n",
       " ('SOXQDSK12A6D4FA909', 1, \"La Notte Dell'11 Ottobre\", 'Massimo Volume', None, 1995, 270.78485, 'ARIVQ621187FB5A88D', None, ''),\n",
       " ('SOGXFIF12A58A78CC4', 1, 'Hanging On (Medium Key Performance Track Without Background Vocals)', 'Britt Nicole', 35.66693, 0, 204.06812, 'AR5LZJD1187FB4C5E5', -80.46935, ' NC')]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM staging_songs LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@dwhcluster.cflhkfkd7jex.us-west-2.redshift.amazonaws.com:5439/dwh\n",
      "5 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>3071</td>\n",
       "        <td>2018-11-01 21:24:53.796000</td>\n",
       "        <td>8</td>\n",
       "        <td>free</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>139</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>3079</td>\n",
       "        <td>2018-11-01 21:28:54.796000</td>\n",
       "        <td>8</td>\n",
       "        <td>free</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>139</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>2922</td>\n",
       "        <td>2018-11-01 21:42:00.796000</td>\n",
       "        <td>10</td>\n",
       "        <td>free</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>9</td>\n",
       "        <td>Washington-Arlington-Alexandria, DC-VA-MD-WV</td>\n",
       "        <td>&quot;Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4) AppleWebKit/537.77.4 (KHTML, like Gecko) Version/7.0.5 Safari/537.77.4&quot;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2796</td>\n",
       "        <td>2018-11-01 21:52:05.796000</td>\n",
       "        <td>26</td>\n",
       "        <td>free</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>169</td>\n",
       "        <td>San Jose-Sunnyvale-Santa Clara, CA</td>\n",
       "        <td>&quot;Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Ubuntu Chromium/36.0.1985.125 Chrome/36.0.1985.125 Safari/537.36&quot;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3064</td>\n",
       "        <td>2018-11-01 22:23:14.796000</td>\n",
       "        <td>101</td>\n",
       "        <td>free</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>100</td>\n",
       "        <td>New Orleans-Metairie, LA</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",
       "</table>"
      ],
      "text/plain": [
       "[(3071, datetime.datetime(2018, 11, 1, 21, 24, 53, 796000), '8', 'free', None, None, '139', '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",
       " (3079, datetime.datetime(2018, 11, 1, 21, 28, 54, 796000), '8', 'free', None, None, '139', '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",
       " (2922, datetime.datetime(2018, 11, 1, 21, 42, 0, 796000), '10', 'free', None, None, '9', 'Washington-Arlington-Alexandria, DC-VA-MD-WV', '\"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4) AppleWebKit/537.77.4 (KHTML, like Gecko) Version/7.0.5 Safari/537.77.4\"'),\n",
       " (2796, datetime.datetime(2018, 11, 1, 21, 52, 5, 796000), '26', 'free', None, None, '169', 'San Jose-Sunnyvale-Santa Clara, CA', '\"Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Ubuntu Chromium/36.0.1985.125 Chrome/36.0.1985.125 Safari/537.36\"'),\n",
       " (3064, datetime.datetime(2018, 11, 1, 22, 23, 14, 796000), '101', 'free', None, None, '100', 'New Orleans-Metairie, LA', '\"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.143 Safari/537.36\"')]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM songplays LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@dwhcluster.cflhkfkd7jex.us-west-2.redshift.amazonaws.com:5439/dwh\n",
      "5 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>100</td>\n",
       "        <td>Adler</td>\n",
       "        <td>Barrera</td>\n",
       "        <td>M</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>101</td>\n",
       "        <td>Jayden</td>\n",
       "        <td>Fox</td>\n",
       "        <td>M</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16</td>\n",
       "        <td>Rylan</td>\n",
       "        <td>George</td>\n",
       "        <td>M</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16</td>\n",
       "        <td>Rylan</td>\n",
       "        <td>George</td>\n",
       "        <td>M</td>\n",
       "        <td>paid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>23</td>\n",
       "        <td>Morris</td>\n",
       "        <td>Gilmore</td>\n",
       "        <td>M</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('100', 'Adler', 'Barrera', 'M', 'free'),\n",
       " ('101', 'Jayden', 'Fox', 'M', 'free'),\n",
       " ('16', 'Rylan', 'George', 'M', 'free'),\n",
       " ('16', 'Rylan', 'George', 'M', 'paid'),\n",
       " ('23', 'Morris', 'Gilmore', 'M', 'free')]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM users LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@dwhcluster.cflhkfkd7jex.us-west-2.redshift.amazonaws.com:5439/dwh\n",
      "5 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>SOAACFC12A8C140567</td>\n",
       "        <td>Supernatural Pt. II</td>\n",
       "        <td>ARNHTE41187B99289A</td>\n",
       "        <td>0</td>\n",
       "        <td>343</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SOAAEHR12A6D4FB060</td>\n",
       "        <td>Slaves &amp; Bulldozers</td>\n",
       "        <td>AR5N8VN1187FB37A4E</td>\n",
       "        <td>1991</td>\n",
       "        <td>415</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SOAAGXT12A8C13A94D</td>\n",
       "        <td>Beautiful Zelda (2007 Digital Remaster)</td>\n",
       "        <td>ARTC1LV1187B9A4858</td>\n",
       "        <td>1968</td>\n",
       "        <td>145</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SOAAMWQ12A8C144DF1</td>\n",
       "        <td>Happy Nation</td>\n",
       "        <td>AR2IKF71187FB4D0C2</td>\n",
       "        <td>1992</td>\n",
       "        <td>255</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SOAANBW12A6D4F8DE9</td>\n",
       "        <td>A Change Is Gonna Come (2001 Digital Remaster)</td>\n",
       "        <td>ARGF9VF1187FB37DAE</td>\n",
       "        <td>1973</td>\n",
       "        <td>259</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('SOAACFC12A8C140567', 'Supernatural Pt. II', 'ARNHTE41187B99289A', 0, 343),\n",
       " ('SOAAEHR12A6D4FB060', 'Slaves & Bulldozers', 'AR5N8VN1187FB37A4E', 1991, 415),\n",
       " ('SOAAGXT12A8C13A94D', 'Beautiful Zelda (2007 Digital Remaster)', 'ARTC1LV1187B9A4858', 1968, 145),\n",
       " ('SOAAMWQ12A8C144DF1', 'Happy Nation', 'AR2IKF71187FB4D0C2', 1992, 255),\n",
       " ('SOAANBW12A6D4F8DE9', 'A Change Is Gonna Come (2001 Digital Remaster)', 'ARGF9VF1187FB37DAE', 1973, 259)]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM songs LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@dwhcluster.cflhkfkd7jex.us-west-2.redshift.amazonaws.com:5439/dwh\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>artist_id</th>\n",
       "        <th>name</th>\n",
       "        <th>location</th>\n",
       "        <th>lattitude</th>\n",
       "        <th>longitude</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AR00FVC1187FB5BE3E</td>\n",
       "        <td>Panda</td>\n",
       "        <td>Monterrey, NL, México</td>\n",
       "        <td>25.67084</td>\n",
       "        <td>-100.30953</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AR00JIO1187B9A5A15</td>\n",
       "        <td>Saigon</td>\n",
       "        <td>Brooklyn</td>\n",
       "        <td>40.65507</td>\n",
       "        <td>-73.94888</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AR00TGQ1187B994F29</td>\n",
       "        <td>Paula Toller</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AR00Y9I1187B999412</td>\n",
       "        <td>Akercocke</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AR026BB1187B994DC3</td>\n",
       "        <td>Ijahman Levi</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('AR00FVC1187FB5BE3E', 'Panda', 'Monterrey, NL, México', 25.67084, -100.30953),\n",
       " ('AR00JIO1187B9A5A15', 'Saigon', 'Brooklyn', 40.65507, -73.94888),\n",
       " ('AR00TGQ1187B994F29', 'Paula Toller', '', None, None),\n",
       " ('AR00Y9I1187B999412', 'Akercocke', '', None, None),\n",
       " ('AR026BB1187B994DC3', 'Ijahman Levi', '', None, None)]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM artists LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@dwhcluster.cflhkfkd7jex.us-west-2.redshift.amazonaws.com:5439/dwh\n",
      "5 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:08:16.796000</td>\n",
       "        <td>21</td>\n",
       "        <td>1</td>\n",
       "        <td>44</td>\n",
       "        <td>11</td>\n",
       "        <td>2018</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2018-11-01 21:28:54.796000</td>\n",
       "        <td>21</td>\n",
       "        <td>1</td>\n",
       "        <td>44</td>\n",
       "        <td>11</td>\n",
       "        <td>2018</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2018-11-02 05:15:41.796000</td>\n",
       "        <td>5</td>\n",
       "        <td>2</td>\n",
       "        <td>44</td>\n",
       "        <td>11</td>\n",
       "        <td>2018</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2018-11-02 09:19:15.796000</td>\n",
       "        <td>9</td>\n",
       "        <td>2</td>\n",
       "        <td>44</td>\n",
       "        <td>11</td>\n",
       "        <td>2018</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2018-11-02 10:02:20.796000</td>\n",
       "        <td>10</td>\n",
       "        <td>2</td>\n",
       "        <td>44</td>\n",
       "        <td>11</td>\n",
       "        <td>2018</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2018, 11, 1, 21, 8, 16, 796000), 21, 1, 44, 11, 2018, 4),\n",
       " (datetime.datetime(2018, 11, 1, 21, 28, 54, 796000), 21, 1, 44, 11, 2018, 4),\n",
       " (datetime.datetime(2018, 11, 2, 5, 15, 41, 796000), 5, 2, 44, 11, 2018, 5),\n",
       " (datetime.datetime(2018, 11, 2, 9, 19, 15, 796000), 9, 2, 44, 11, 2018, 5),\n",
       " (datetime.datetime(2018, 11, 2, 10, 2, 20, 796000), 10, 2, 44, 11, 2018, 5)]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM time LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "### Analytical Queries"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### Top 5 users ranked according to their app usage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@dwhcluster.cflhkfkd7jex.us-west-2.redshift.amazonaws.com:5439/dwh\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>first_name</th>\n",
       "        <th>last_name</th>\n",
       "        <th>usage_frequency</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Chloe</td>\n",
       "        <td>Cuevas</td>\n",
       "        <td>2361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Tegan</td>\n",
       "        <td>Levine</td>\n",
       "        <td>2333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Kate</td>\n",
       "        <td>Harrell</td>\n",
       "        <td>1817</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Lily</td>\n",
       "        <td>Koch</td>\n",
       "        <td>1511</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Aleena</td>\n",
       "        <td>Kirby</td>\n",
       "        <td>1340</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Chloe', 'Cuevas', 2361),\n",
       " ('Tegan', 'Levine', 2333),\n",
       " ('Kate', 'Harrell', 1817),\n",
       " ('Lily', 'Koch', 1511),\n",
       " ('Aleena', 'Kirby', 1340)]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT u.first_name, u.last_name, count(*) usage_frequency\n",
    "FROM songplays sp\n",
    "JOIN users u ON u.user_id = sp.user_id\n",
    "GROUP BY u.first_name, u.last_name\n",
    "ORDER BY usage_frequency DESC\n",
    "LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### Top Artist whose songs users listen more frequently"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@dwhcluster.cflhkfkd7jex.us-west-2.redshift.amazonaws.com:5439/dwh\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>artist_name</th>\n",
       "        <th>hits</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Dwight Yoakam</td>\n",
       "        <td>222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Kid Cudi / Kanye West / Common</td>\n",
       "        <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Kid Cudi</td>\n",
       "        <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ron Carter</td>\n",
       "        <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Lonnie Gordon</td>\n",
       "        <td>54</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Dwight Yoakam', 222),\n",
       " ('Kid Cudi / Kanye West / Common', 60),\n",
       " ('Kid Cudi', 60),\n",
       " ('Ron Carter', 54),\n",
       " ('Lonnie Gordon', 54)]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT a.name artist_name, count(*) hits\n",
    "FROM songplays sp\n",
    "JOIN artists a ON a.artist_id = sp.artist_id\n",
    "GROUP BY artist_name\n",
    "ORDER BY hits DESC\n",
    "LIMIT 5;"
   ]
  },
  {
   "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": 85,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Cluster': {'AllowVersionUpgrade': True,\n",
       "  'AutomatedSnapshotRetentionPeriod': 1,\n",
       "  'AvailabilityZone': 'us-west-2b',\n",
       "  'ClusterCreateTime': datetime.datetime(2019, 2, 16, 6, 21, 30, 630000, tzinfo=tzutc()),\n",
       "  'ClusterIdentifier': 'dwhcluster',\n",
       "  'ClusterParameterGroups': [{'ParameterApplyStatus': 'in-sync',\n",
       "    'ParameterGroupName': 'default.redshift-1.0'}],\n",
       "  'ClusterSecurityGroups': [],\n",
       "  'ClusterStatus': 'deleting',\n",
       "  'ClusterSubnetGroupName': 'default',\n",
       "  'ClusterVersion': '1.0',\n",
       "  'DBName': 'dwh',\n",
       "  'Encrypted': False,\n",
       "  'Endpoint': {'Address': 'dwhcluster.csmamz5zxmle.us-west-2.redshift.amazonaws.com',\n",
       "   'Port': 5439},\n",
       "  'EnhancedVpcRouting': False,\n",
       "  'IamRoles': [{'ApplyStatus': 'in-sync',\n",
       "    'IamRoleArn': 'arn:aws:iam::988332130976:role/dwhRole'}],\n",
       "  'MasterUsername': 'dwhuser',\n",
       "  'NodeType': 'dc2.large',\n",
       "  'NumberOfNodes': 4,\n",
       "  'PendingModifiedValues': {},\n",
       "  'PreferredMaintenanceWindow': 'fri:10:30-fri:11:00',\n",
       "  'PubliclyAccessible': True,\n",
       "  'Tags': [],\n",
       "  'VpcId': 'vpc-54d40a2c',\n",
       "  'VpcSecurityGroups': []},\n",
       " 'ResponseMetadata': {'HTTPHeaders': {'content-length': '2041',\n",
       "   'content-type': 'text/xml',\n",
       "   'date': 'Sat, 16 Feb 2019 07:13:32 GMT',\n",
       "   'x-amzn-requestid': '5e58b2d8-31ba-11e9-b19b-0945d449b0a9'},\n",
       "  'HTTPStatusCode': 200,\n",
       "  'RequestId': '5e58b2d8-31ba-11e9-b19b-0945d449b0a9',\n",
       "  'RetryAttempts': 0}}"
      ]
     },
     "execution_count": 85,
     "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": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "- run this block several times until the cluster really deleted"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'redshift' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-3-9b3202a2945e>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mmyClusterProps\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mredshift\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdescribe_clusters\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mClusterIdentifier\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mDWH_CLUSTER_IDENTIFIER\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'Clusters'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      2\u001b[0m \u001b[0mprettyRedshiftProps\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mmyClusterProps\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mNameError\u001b[0m: name 'redshift' is not defined"
     ]
    }
   ],
   "source": [
    "myClusterProps = redshift.describe_clusters(ClusterIdentifier=DWH_CLUSTER_IDENTIFIER)['Clusters'][0]\n",
    "prettyRedshiftProps(myClusterProps)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'iam' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-4-fd4a14e2cd8f>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0;31m#### CAREFUL!!\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      2\u001b[0m \u001b[0;31m#-- Uncomment & run to delete the created resources\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 3\u001b[0;31m \u001b[0miam\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdetach_role_policy\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mRoleName\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mDWH_IAM_ROLE_NAME\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mPolicyArn\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m\"arn:aws:iam::aws:policy/AmazonS3ReadOnlyAccess\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      4\u001b[0m \u001b[0miam\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdelete_role\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mRoleName\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mDWH_IAM_ROLE_NAME\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      5\u001b[0m \u001b[0;31m#### CAREFUL!!\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mNameError\u001b[0m: name 'iam' is not defined"
     ]
    }
   ],
   "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
}