loading-to-athena.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Loading CSVs to AWS Athena\n",
    "\n",
    "Our sensor data is a collection of CSV of over 30 million records.  For us to analyze this large dataset, we will load them to AWS Athena.  [AWS Athena](https://aws.amazon.com/athena/) is Amazon's interactive query service to analyze data in Amazon S3 using standard SQL.  I chose Athena because:\n",
    "* **our data is already hosted in S3** - with minimal data setup, we can use it directly for query and analysis.  I also provide this data as a csv download for anyone, so the same s3 bucket is accessible for sharing and distribution.\n",
    "* **its serverless** - we don't need to deploy an SQL server. In additon, you only get billed whenever the data is queried. This makes it attractive since the cost is cheaper than traditional databases.\n",
    "\n",
    "This notebook walks-through the basic setup of Athena to access our data csv. I found a great introductory video about the basics of Athena from [Academind](https://www.youtube.com/playlist?list=PL55RiY5tL51rZooHydslYclCYio7eoC66)\n",
    "\n",
    "*Note: We will use terminal CLI and Amazon's Athena Query UI, most of these commands can be executed with AWS [Command Line Interface](https://aws.amazon.com/cli/?sc_channel=PS&sc_campaign=acquisition_IN&sc_publisher=google&sc_medium=command_line_b&sc_content=aws_cli_e&sc_detail=aws%20cli&sc_category=command_line&sc_segment=159807028048&sc_matchtype=e&sc_country=IN&s_kwcid=AL!4422!3!159807028048!e!!g!!aws%20cli&ef_id=VgvD3AAAAAAp9jGB:20180729163045:s), but for simplicity, we will use the UI from AWS console.*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Preparing the data\n",
    "\n",
    "* Inspect the data from terminal.\n",
    "\n",
    "```sh\n",
    " head arg.csv\n",
    "id,name,time,value,lon,lat,the_geom\n",
    "32156535,apayaoflora_r2,2015-08-29 14:15:00,0,121.471109,18.01623,0101000020E61000007BF65CA6265E5E40CE3637A627043240\n",
    "32156538,isabelacvaardcomplexisuechague_r1,2015-08-29 14:15:00,0,121.683333,16.716667,0101000020E6100000C4D155BABB6B5E40581F0F7D77B73040\n",
    "32156539,agusandelsurmagsaysayprosperidad_r2,2015-08-29 14:30:00,0,126.00708,8.63208,0101000020E61000002A1DACFF73805F404FE960FD9F432140\n",
    "...\n",
    "```\n",
    "\n",
    "Our raw CSV contains several columns, for example, in the automated rainfall gauge (arg), we have:\n",
    "\n",
    "* id - unique for each record\n",
    "* name - name of station\n",
    "* time - time of record\n",
    "* rainfall - rainfall in mm\n",
    "* lon - longitude of station\n",
    "* lat - latitude of station\n",
    "* the_geom - WKB of the station location\n",
    "\n",
    "* We remove the `the_geom` column since we don't need it for Athena, the `lat` and `lon` values is enough if we need basic spatial query later.\n",
    "\n",
    "```sh\n",
    "cut -d',' -f 1-8 arg.csv > arg-clean.csv\n",
    "```\n",
    "\n",
    "Then we compress the cleaned csv.\n",
    "\n",
    "```sh\n",
    "gzip arg-clean.csv\n",
    "```\n",
    "\n",
    "Repeat for all the other 3 files.\n",
    "\n",
    "```sh\n",
    "ls -alh *.csv *.gz\n",
    "-rw-r--r--  1 maning  staff   243M Jul 28 15:13 arg-clean.csv.gz\n",
    "-rw-r--r--  1 maning  staff   1.6G Jan 21  2017 arg.csv\n",
    "-rw-r--r--  1 maning  staff   127M Jul 28 15:15 asg-clean.csv.gz\n",
    "-rw-r--r--  1 maning  staff   936M Jan 21  2017 asg.csv\n",
    "-rw-r--r--  1 maning  staff    40M Jul 28 15:17 aws-clean.csv.gz\n",
    "-rw-r--r--  1 maning  staff   337M Jan 21  2017 aws.csv\n",
    "-rw-r--r--  1 maning  staff   255M Jul 28 15:20 td-clean.csv.gz\n",
    "-rw-r--r--  1 maning  staff   1.9G Jan 21  2017 td.csv\n",
    "```\n",
    "\n",
    "Compression is important to reduce the cost.  Amazon charges \"based on the amount of data scanned by each query\".  Compression reduces the file size and thus reducing the cost. For example, by cleaning unnecessary columns and file compression, the total **4.7 GB of the combined 4 files reduced to 665 MB, about 7x smaller**.\n",
    "\n",
    "There are other data formats (i.e. [ORC, parquet](https://docs.aws.amazon.com/athena/latest/ug/convert-to-columnar.html)) that siginificantly reduce size and cost, for simplicity, we will use the compressed CSVs."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Upload to S3 \n",
    "\n",
    "For Athena to access the compressed CSVs, we need to upload the data to an s3 bucket.\n",
    "\n",
    "* Go to S3 from the [AWS Console](https://s3.console.aws.amazon.com/s3/home?region=us-east-1#)\n",
    "* Create a new bucket (for example, `s3://dostsensor-db/`)\n",
    "* Create sub directories for each compressed csv.  Each of the csv has a different data structure. A subdirectory in the main bucket will be a separate table in Athena. Use the following structure for your bucket:\n",
    "\n",
    "```sh\n",
    "tree\n",
    "├──dostsensor-db\n",
    " ├── arg\n",
    " │   ├── arg-clean.csv.gz\n",
    " └── asg\n",
    " │   ├── arg-clean.csv.gz\n",
    " └── aws\n",
    " │   ├── aws-clean.csv.gz\n",
    " └── td\n",
    " │   ├── td-clean.csv.gz\n",
    "    \n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The AWS Athena UI\n",
    "\n",
    "* To access Athena, go [Athena via AWS console](https://console.aws.amazon.com/athena/home?region=us-east-1).\n",
    "* The Athena UI has the following parts:\n",
    "  1. Query editor - view/write SQL code.\n",
    "  2. Catalog - list of available database and tables.\n",
    "  3. Results - display latest query results.\n",
    "  4. Tabs to other features of the UI.\n",
    "\n",
    "![](img/athena-ui.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Setup your database and tables\n",
    "\n",
    "* From the Athena UI query window, create a new database.\n",
    "\n",
    "```\n",
    "CREATE DATABASE dostsensor;\n",
    "```\n",
    "\n",
    "Once created, the database will appear in the dropbox of the Database list.\n",
    "\n",
    "\n",
    "* Defining the table's Data Definition Language (DDL).  Each table should have a DDL, this is the schema \n",
    "that Athena will use to read the CSV.  More info about DDLs [here](https://docs.aws.amazon.com/athena/latest/ug/ddl-sql-reference.html).\n",
    "* For the asg.csv, I used the ddl below:\n",
    "\n",
    "```sql\n",
    "CREATE EXTERNAL TABLE IF NOT EXISTS dostsensor.arg (\n",
    "  `id` int, \n",
    "  `name` string, \n",
    "  `time` string, \n",
    "  `value` decimal(10,6), \n",
    "  `lon` decimal(10,6), \n",
    "  `lat` decimal(10,6)\n",
    "  )\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://dostsensor-db/asg'\n",
    "TBLPROPERTIES (\n",
    "  'has_encrypted_data'='false', \n",
    "  'skip.header.line.count'='1')\n",
    "```\n",
    "\n",
    "* *[todo] - explain the parts of the DDL.*\n",
    "* Once the table is created, it will appear in the list of tables under the `dostsensor` database.\n",
    "* Do the same for all your CSVs.  Use the pre-defined ddl in the [ddl](ddl/) directory."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Test your database\n",
    "\n",
    "Once your tables are ready, test if the data is queryable.\n",
    "* In the Query editor:\n",
    "\n",
    "```\n",
    "SELECT * FROM \"dostsensor\".\"arg\" limit 10;\n",
    "```\n",
    "\n",
    "* The result should display in the Result window.\n",
    "\n",
    "```\n",
    "|   | id       | name                                | time                | value    | lon        | lat       |\n",
    "| - | -------- | ----------------------------------- | ------------------- | -------- | ---------- | --------- |\n",
    "| 1 | 32156535 | apayaoflora_r2                      | 2015-08-29 14:15:00 | 0.000000 | 121.471109 | 18.016230 |\n",
    "| 2 | 32156538 | isabelacvaardcomplexisuechague_r1   | 2015-08-29 14:15:00 | 0.000000 | 121.683333 | 16.716667 |\n",
    "| 3 | 32156539 | agusandelsurmagsaysayprosperidad_r2 | 2015-08-29 14:30:00 | 0.000000 | 126.007080 | 8.632080  |\n",
    "| 4 | 32156540 | aklanguadalupebridgemadalag_rwl     | 2015-08-29 14:30:00 | 0.000000 | 122.309917 | 11.529853 |\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now all your CSV are available for query and analysis through Athena.  In the next section, we will discuss how to connect the [Athena to a Jupyter notebook](athena-to-pandas.ipynb) and do exploratory data analysis of the results."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## See also\n",
    "* [AWS Athena Guide](https://docs.aws.amazon.com/athena/latest/ug/what-is.html)\n",
    "* [Athena videos by Academind](https://www.youtube.com/playlist?list=PL55RiY5tL51rZooHydslYclCYio7eoC66)"
   ]
  }
 ],
 "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}