Lesson5_AWS_Big_Data_Analysis.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Lesson5-AWS-Big-Data-Analysis.ipynb",
      "version": "0.3.2",
      "provenance": [],
      "collapsed_sections": [
        "c_Id55m6Jsbu",
        "IdrxEbJvoqQN",
        "k4V3F0bhowQ_",
        "zty7ydffo0j6",
        "-7R3yc49o0qB"
      ],
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/paiml/awsbigdata/blob/master/Lesson5_AWS_Big_Data_Analysis.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "metadata": {
        "id": "Wy6nhdcaVsCH",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "# Lesson 5 Analysis"
      ]
    },
    {
      "metadata": {
        "id": "c_Id55m6Jsbu",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## Pragmatic AI Labs\n",
        "\n"
      ]
    },
    {
      "metadata": {
        "id": "e5p96AqpSDZa",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "![alt text](https://paiml.com/images/logo_with_slogan_white_background.png)\n",
        "\n",
        "This notebook was produced by [Pragmatic AI Labs](https://paiml.com/).  You can continue learning about these topics by:\n",
        "\n",
        "*   Buying a copy of [Pragmatic AI: An Introduction to Cloud-Based Machine Learning](http://www.informit.com/store/pragmatic-ai-an-introduction-to-cloud-based-machine-9780134863917)\n",
        "*   Reading an online copy of [Pragmatic AI:Pragmatic AI: An Introduction to Cloud-Based Machine Learning](https://www.safaribooksonline.com/library/view/pragmatic-ai-an/9780134863924/)\n",
        "*  Watching video [Essential Machine Learning and AI with Python and Jupyter Notebook-Video-SafariOnline](https://www.safaribooksonline.com/videos/essential-machine-learning/9780135261118) on Safari Books Online.\n",
        "* Watching video [AWS Certified Machine Learning-Speciality](https://learning.oreilly.com/videos/aws-certified-machine/9780135556597)\n",
        "* Purchasing video [Essential Machine Learning and AI with Python and Jupyter Notebook- Purchase Video](http://www.informit.com/store/essential-machine-learning-and-ai-with-python-and-jupyter-9780135261095)\n",
        "*   Viewing more content at [noahgift.com](https://noahgift.com/)\n"
      ]
    },
    {
      "metadata": {
        "id": "IdrxEbJvoqQN",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## 5.1 Determine how to design and architect the analytical solution\n"
      ]
    },
    {
      "metadata": {
        "id": "O-WWOXgTAcz_",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        ""
      ]
    },
    {
      "metadata": {
        "id": "k4V3F0bhowQ_",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## 5.2 Understand Redshift overview"
      ]
    },
    {
      "metadata": {
        "id": "IzprtbLaAnMu",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "### Link to AWS Console for Redshift cluster creation exercise:\n",
        "\n",
        "https://console.aws.amazon.com/console\n",
        "\n"
      ]
    },
    {
      "metadata": {
        "id": "zty7ydffo0j6",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## 5.3  Learn Redshift design"
      ]
    },
    {
      "metadata": {
        "id": "RujUaBmBo0m-",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "### AWS Whitepaper on sizing Redshift clusters:  \n",
        "\n",
        "https://d1.awsstatic.com/whitepapers/Size-Cloud-Data-Warehouse-on-AWS.pdf\n"
      ]
    },
    {
      "metadata": {
        "id": "-7R3yc49o0qB",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## 5.4 Use Redshift Data Ingestion"
      ]
    },
    {
      "metadata": {
        "id": "mljYsdnHAsgE",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "### Link to AWS Console for the Redshift data ingestion exercise:\n",
        "\n",
        "https://console.aws.amazon.com/console\n"
      ]
    },
    {
      "metadata": {
        "id": "usf_N4j9UCDJ",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# code for:  `redshift-00-sales-create-tables.sql`\n",
        "# should be run from your SQL IDE - not this colab notebook\n",
        "\n",
        "-- create tables IF they do not already exist in the schema\n",
        "-- helpfully, AWS provides IF NOT EXISTS which is useful in many queries\n",
        "create table IF NOT EXISTS users(\n",
        "\tuserid integer not null distkey sortkey,\n",
        "\tusername char(8),\n",
        "\tfirstname varchar(30),\n",
        "\tlastname varchar(30),\n",
        "\tcity varchar(30),\n",
        "\tstate char(2),\n",
        "\temail varchar(100),\n",
        "\tphone char(14),\n",
        "\tlikesports boolean,\n",
        "\tliketheatre boolean,\n",
        "\tlikeconcerts boolean,\n",
        "\tlikejazz boolean,\n",
        "\tlikeclassical boolean,\n",
        "\tlikeopera boolean,\n",
        "\tlikerock boolean,\n",
        "\tlikevegas boolean,\n",
        "\tlikebroadway boolean,\n",
        "\tlikemusicals boolean);\n",
        "\n",
        "create table IF NOT EXISTS venue(\n",
        "\tvenueid smallint not null distkey sortkey,\n",
        "\tvenuename varchar(100),\n",
        "\tvenuecity varchar(30),\n",
        "\tvenuestate char(2),\n",
        "\tvenueseats integer);\n",
        "\n",
        "create table IF NOT EXISTS category(\n",
        "\tcatid smallint not null distkey sortkey,\n",
        "\tcatgroup varchar(10),\n",
        "\tcatname varchar(10),\n",
        "\tcatdesc varchar(50));\n",
        "\n",
        "create table IF NOT EXISTS date(\n",
        "\tdateid smallint not null distkey sortkey,\n",
        "\tcaldate date not null,\n",
        "\tday character(3) not null,\n",
        "\tweek smallint not null,\n",
        "\tmonth character(5) not null,\n",
        "\tqtr character(5) not null,\n",
        "\tyear smallint not null,\n",
        "\tholiday boolean default('N'));\n",
        "\n",
        "create table IF NOT EXISTS event(\n",
        "\teventid integer not null distkey,\n",
        "\tvenueid smallint not null,\n",
        "\tcatid smallint not null,\n",
        "\tdateid smallint not null sortkey,\n",
        "\teventname varchar(200),\n",
        "\tstarttime timestamp);\n",
        "\n",
        "create table IF NOT EXISTS listing(\n",
        "\tlistid integer not null distkey,\n",
        "\tsellerid integer not null,\n",
        "\teventid integer not null,\n",
        "\tdateid smallint not null  sortkey,\n",
        "\tnumtickets smallint not null,\n",
        "\tpriceperticket decimal(8,2),\n",
        "\ttotalprice decimal(8,2),\n",
        "\tlisttime timestamp);\n",
        "\n",
        "create table IF NOT EXISTS sales(\n",
        "\tsalesid integer not null,\n",
        "\tlistid integer not null distkey,\n",
        "\tsellerid integer not null,\n",
        "\tbuyerid integer not null,\n",
        "\teventid integer not null,\n",
        "\tdateid smallint not null sortkey,\n",
        "\tqtysold smallint not null,\n",
        "\tpricepaid decimal(8,2),\n",
        "\tcommission decimal(8,2),\n",
        "\tsaletime timestamp);\n"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "kzcHZMM5UV3B",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# code for:  `redshift-01-sales-load-s3data.sql`\n",
        "# should be run from your SQL IDE - not this colab notebook\n",
        "\n",
        "copy users from 's3://awssampledbuswest2/tickit/allusers_pipe.txt'\n",
        "credentials 'aws_iam_role=arn:aws:iam::<< insert your IAM ROLE ARN HERE'\n",
        "delimiter '|' region 'us-west-2';\n",
        "\n",
        "copy venue from 's3://awssampledbuswest2/tickit/venue_pipe.txt'\n",
        "credentials 'aws_iam_role=arn:aws:iam::<< insert your IAM ROLE ARN HERE'\n",
        "delimiter '|' region 'us-west-2';\n",
        "\n",
        "copy category from 's3://awssampledbuswest2/tickit/category_pipe.txt'\n",
        "credentials 'aws_iam_role=arn:aws:iam::<< insert your IAM ROLE ARN HERE'\n",
        "delimiter '|' region 'us-west-2';\n",
        "\n",
        "copy date from 's3://awssampledbuswest2/tickit/date2008_pipe.txt'\n",
        "credentials 'aws_iam_role=arn:aws:iam::<< insert your IAM ROLE ARN HERE'\n",
        "delimiter '|' region 'us-west-2';\n",
        "\n",
        "copy event from 's3://awssampledbuswest2/tickit/allevents_pipe.txt'\n",
        "credentials 'aws_iam_role=arn:aws:iam::<< insert your IAM ROLE ARN HERE'\n",
        "delimiter '|' timeformat 'YYYY-MM-DD HH:MI:SS' region 'us-west-2';\n",
        "\n",
        "copy listing from 's3://awssampledbuswest2/tickit/listings_pipe.txt'\n",
        "credentials 'aws_iam_role=arn:aws:iam::<< insert your IAM ROLE ARN HERE'\n",
        "delimiter '|' region 'us-west-2';\n",
        "\n",
        "copy sales from 's3://awssampledbuswest2/tickit/sales_tab.txt'\n",
        "credentials 'aws_iam_role=arn:aws:iam::<< insert your IAM ROLE ARN HERE'\n",
        "delimiter '\\t' timeformat 'MM/DD/YYYY HH:MI:SS' region 'us-west-2';"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "ELIuBus_UzGb",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# code for:  `redshift-02-sales-check-tables-after-data-load.sql`\n",
        "# should be run from your SQL IDE - not this colab notebook\n",
        "\n",
        "select count(*) from users;\n",
        "\n",
        "select count(*) from venue;\n",
        "\n",
        "select count(*) from category;\n",
        "\n",
        "select count(*) from date;\n",
        "\n",
        "select count(*) from event;\n",
        "\n",
        "select count(*) from listing;\n",
        "\n",
        "select count(*) from sales;\n"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "-BN1ivZ9U9A8",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# code for:  `redshift-03-sales-sample-queries.sql`\n",
        "# should be run from your SQL IDE - not this colab notebook\n",
        "\n",
        "-- list all public tables, with columns and metadata (which we created) in Redshift\n",
        "SELECT * FROM PG_TABLE_DEF\n",
        "WHERE schemaname = 'public';\n",
        "\n",
        "-- list all tables and columns where datatype of column is in a list we define\n",
        "SELECT * FROM PG_TABLE_DEF\n",
        "WHERE schemaname = 'public'\n",
        "AND type IN ('smallint', 'date');\n",
        "\n",
        "-- Get definition for the sales table.\n",
        "SELECT *\n",
        "FROM pg_table_def\n",
        "WHERE tablename = 'sales';\n",
        "\n",
        "-- Find total sales on a given calendar date.\n",
        "SELECT sum(qtysold)\n",
        "FROM   sales, date\n",
        "WHERE  sales.dateid = date.dateid\n",
        "AND    caldate = '2008-02-05';\n",
        "\n",
        "-- Find top 10 buyers by quantity.\n",
        "SELECT firstname, lastname, total_quantity\n",
        "FROM   (SELECT buyerid, sum(qtysold) total_quantity\n",
        "        FROM  sales\n",
        "        GROUP BY buyerid\n",
        "        ORDER BY total_quantity desc limit 20) Q, users\n",
        "WHERE Q.buyerid = userid\n",
        "ORDER BY Q.total_quantity desc;\n",
        "\n",
        "-- Find events in the 99th percentile in terms of all time gross sales.\n",
        "SELECT eventname, total_price\n",
        "FROM  (SELECT eventid, total_price, ntile(1000) over(order by total_price desc) as percentile\n",
        "       FROM (SELECT eventid, sum(pricepaid) total_price\n",
        "             FROM   sales\n",
        "             GROUP BY eventid)) Q, event E\n",
        "       WHERE Q.eventid = E.eventid\n",
        "       AND percentile = 1\n",
        "ORDER BY total_price desc;\n"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "gw9CcMA2VGIu",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# code for:  `redshift-drop_tables-sales-example.sql`\n",
        "# should be run from your SQL IDE - not this colab notebook\n",
        "\n",
        "-- used to reset database; so you can repeat the tutorial on the same cluster\n",
        "-- this will drop everything that user `awsuser` owns - change username to match your config\n",
        "drop table category, date, event, listing, sales, users, venue;\n"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "CcRBRmgWVU6e",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# code for:  `redshift-vacuum_analyze.sql`\n",
        "# should be run from your SQL IDE - not this colab notebook\n",
        "\n",
        "-- VACUUM and ANALYZE are important to do after add/delete/modify a significant number of rows\n",
        "-- VACUUM recovers space from deleted rows and restores the sort order\n",
        "-- ANALYZE updates the statistics metadata.  This helps the query optimizer to generate more accurate query plans\n",
        "vacuum;\n",
        "\n",
        "-- this threshold can be changes; 0 means all rows in all tables are analyzed\n",
        "set analyze_threshold_percent to 0;\n",
        "analyze;\n"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "vDPvXKnZo0wu",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## 5.5 Apply Redshift operations"
      ]
    },
    {
      "metadata": {
        "id": "mWLdPK77o0zw",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "### AWS provided Redshift utilities - available on GitHub:\n",
        "\n",
        "https://github.com/awslabs/amazon-redshift-utils\n",
        "\n"
      ]
    },
    {
      "metadata": {
        "id": "GbNGsd8ro02q",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## 5.6 Use AWS Elasticsearch: operational analytics"
      ]
    },
    {
      "metadata": {
        "id": "XRlsLGLJo05e",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "### Link to AWS Console for Elasticsearch exercise:\n",
        "\n",
        "https://console.aws.amazon.com/console\n"
      ]
    },
    {
      "metadata": {
        "id": "VlH1kM3NVqbU",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# elasticsearch - curl put single record\n",
        "\n",
        "curl -XPUT <<INPUT YOUR elasticsearch_domain_endpoint>>/movies/_doc/1 -d '{\"director\": \"Burton, Tim\", \"genre\": [\"Comedy\",\"Sci-Fi\"], \"year\": 1996, \"actor\": [\"Jack Nicholson\",\"Pierce Brosnan\",\"Sarah Jessica Parker\"], \"title\": \"Mars Attacks!\"}' -H 'Content-Type: application/json'"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "yKP47QmAWbPY",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# elasticsearch - curl search single record\n",
        "\n",
        "curl -XGET 'INPUT YOUR elasticsearch_domain_endpoint/movies/_search?q=mars'\n",
        "\n"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "RnV_aoluo-Eu",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## 5.7 Understand Machine Learning: clustering & regression"
      ]
    },
    {
      "metadata": {
        "id": "lrCT2tkIo-H3",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        ""
      ]
    },
    {
      "metadata": {
        "id": "XkORPI0eo-Ng",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## 5.8 Use AWS Athena: interactive analytics"
      ]
    },
    {
      "metadata": {
        "id": "ggca3XEOXnzR",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "### S3 bucket path for NOAA data:   `s3://noaa-ghcn-pds/csv.gz/`"
      ]
    },
    {
      "metadata": {
        "id": "6ZjG7WhHWww8",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# code for:  `noaa.create_database.ddl`\n",
        "# should be run from your Athena console in a browser - not this colab notebook\n",
        "\n",
        "-- ddl query to create a separate database for NOAA climate data IF one does not exist\n",
        "CREATE DATABASE IF NOT EXISTS noaa_climate;\n"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "M4TPxhsgW6Io",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# code for:  `noaa.create_table_from_gzip.ddl`\n",
        "# should be run from your Athena console in a browser - not this colab notebook\n",
        "\n",
        "-- first, manually create a climate database using the Athena console\n",
        "-- Then use this DDL as a new query from Athena console to create historic_climate_gz table\n",
        "\n",
        "CREATE EXTERNAL TABLE `historic_climate_gz`(\n",
        "  `id` string,\n",
        "  `yearmonthday` int,\n",
        "  `element` string,\n",
        "  `temperature` int,\n",
        "  `m-flag` string,\n",
        "  `q-flag` string,\n",
        "  `s-flag` string,\n",
        "  `obs-time` int)\n",
        "ROW FORMAT DELIMITED\n",
        "  FIELDS TERMINATED BY ','\n",
        "STORED AS INPUTFORMAT\n",
        "  'org.apache.hadoop.mapred.TextInputFormat'\n",
        "OUTPUTFORMAT\n",
        "  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'\n",
        "LOCATION\n",
        "  's3://noaa-ghcn-pds/csv.gz/'\n",
        "TBLPROPERTIES (\n",
        "  'has_encrypted_data'='false',\n",
        "  'transient_lastDdlTime'='1553207260')\n"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "lpqv71pDXFgA",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# code for:  `noaa.select_hot_days.sql`\n",
        "# should be run from your Athena console in a browser - not this colab notebook\n",
        "\n",
        "-- query to scan 231+ years of climate data to report on hot days\n",
        "--  data temperature is an integer multiple of 1/10 degree celcius, so we divide by 10 to get actual temp\n",
        "-- filter on temps >40 deg celsius which is about 105 deg fahrenheit\n",
        "SELECT yearmonthday, round(temperature/10) from historic_climate_gz\n",
        "WHERE element = 'TMAX' AND (temperature/10) > 40\n",
        "LIMIT 10000;\n",
        "\n",
        "\n"
      ],
      "execution_count": 0,
      "outputs": []
    }
  ]
}