2020.final.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a href=\"https://colab.research.google.com/github/rzl-ds/gu511_exam/blob/master/2020.final.ipynb\" target=\"_parent\">\n",
    "    <img src=\"https://colab.research.google.com/assets/colab-badge.svg\"/>\n",
    "</a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# GU511 FINAL EXAM (2020)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## goal"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "in this midterm exam we will cover all the material covered in lectures so far in 2020, including:\n",
    "\n",
    "+ new material\n",
    "    + web scraping and `REST` `api`s\n",
    "    + `aws` `iam`\n",
    "    + databases\n",
    "    + `aws` `rds`\n",
    "    + `aws` `redshift`\n",
    "    + `gpu` analytics\n",
    "    + deep learning including `tensorflow` and `keras`\n",
    "    + `hadoop` and `spark`\n",
    "+ first-half material\n",
    "    + `aws` `ec2`\n",
    "    + `ssh`\n",
    "    + `linux`\n",
    "    + `git`\n",
    "    + `python`\n",
    "    + `conda`\n",
    "    + `docker`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## a note on \"intermediate products\"\n",
    "\n",
    "the questions and answers in this exam build on eachother:\n",
    "\n",
    "+ exercise 7 asks you to write `sql` that will upload a dataset to a redshift cluster, and exercise 8 asks you write queries on that uploaded data\n",
    "+ exercise 2 asks you to write `python` code that will generate a list of downloadable files, and exercise 5 asks you to do something with that list of files\n",
    "\n",
    "I don't want the inability to do one exercise to block you from doing the next, so in every instance where that is possible I have created a publically available intermediate product that you can use instead, and provided you with the necessary code to do it. **you should not be blocked on any one exercise due to inability to complete another**. if you feel that is not so, reach out to me asap!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## a note on the s3 submission bucket\n",
    "\n",
    "in exercise 1 you will access a sepcial `s3` bucket we will call your \"final submission bucket\". every file submitted for this final **MUST** be submitted into **THAT** bucket -- do not use any of the `s3` buckets we have created previously in this class.\n",
    "\n",
    "in exercise1 we will specifically talk about a flag `--bucket-owner-full-control` that you must provide every time you upload your files. please remember to use that flag for every file you upload to your submission bucket; we cannot access the files otherwise."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## method of delivery"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "you will submit most of the results of your exam via `s3`. we have created a bucket for you (see exercise 1 below) and you will be pushing files there. **do not** push files to your homework submission buckets we've used in previous weeks!\n",
    "\n",
    "summary:\n",
    "\n",
    "| exercise | deliverable | method of delivery | points |\n",
    "|----------|-------------|--------------------|--------|\n",
    "| 1 | a file `test.txt` | uploaded to your `s3` **final** (not hw) submission bucket | 5 |\n",
    "| 2 | a file `urls.py` | uploaded to your `s3` final submission bucket | 15 |\n",
    "| 3 | a file `exercise1_ec2_details.png` | uploaded to your `s3` final submission bucket | 5 |\n",
    "| 4 | a file `exercise2_ec2_w_s3_role.png` | uploaded to your `s3` final submission bucket | 5 |\n",
    "| 5 | a file `usaspending2s3.py` | uploaded to your `s3` final submission bucket | 15 |\n",
    "| 6 | a file `copy_usaspending_to_postgres.psql` | uploaded to your `s3` final submission bucket | 10 |\n",
    "| 7 | a file `s3toredshift.sql` | uploaded to your `s3` final submission bucket | 15 |\n",
    "| 8 | a file `usaspending_queries.sql` | uploaded to your `s3` final submission bucket | 15 |\n",
    "| 9 | a file `mapreduce_termination.sh` | uploaded to your `s3` final submission bucket | 10 |\n",
    "| 10 | a `github` `pull request` from a `fork`ed `repo` | will be observe on `github` | 15 |\n",
    "| -1 | cleanup | nothing to submit | 5 |\n",
    "\n",
    "total points: 115"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div style=\"border: 1px solid lightgrey;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# exercise 0: honor code\n",
    "\n",
    "while you are welcome to reference course notes, stack overflow, online documentation, and all the other resources of the world, there is one resource which you *CANNOT* use: your fellow classmates.\n",
    "\n",
    "you are expected to complete this exam **completely independently**. as this exam will be unsupervised, you are (as always) expected to abide by the guidelines laid out by [the honor council](https://honorcouncil.georgetown.edu/), and summarized in the student pledge:\n",
    "\n",
    "> In pursuit of the high ideals and rigorous standards of academic life I commit myself to respect and to uphold the Georgetown University honor system:\n",
    "> \n",
    "> To be honest in every academic endeavor, and\n",
    "> \n",
    "> To conduct myself honorably, as a responsible member of the Georgetown community as we live and work together."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div style=\"border: 1px solid lightgrey;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# exercise 1: access your `s3` final submission bucket\n",
    "\n",
    "I have created an `s3` bucket for each of you to submit your answers to the final. it is named\n",
    "\n",
    "`2020-fall-gu511-[YOUR_GU_ID]`\n",
    "\n",
    "\n",
    "## 1.1: verify you can read\n",
    "\n",
    "verify that you can list the contents of that directory with\n",
    "\n",
    "```sh\n",
    "# this must be run with your aws account credentials!\n",
    "aws s3 ls 2020-fall-gu511-[YOUR_GU_ID]\n",
    "```\n",
    "\n",
    "you should see one file named `dbcredentials.txt`:\n",
    "\n",
    "```\n",
    "2020-12-07 12:07:48         12 dbcredentials.txt\n",
    "```\n",
    "\n",
    "verify that you can download and read that file with\n",
    "\n",
    "```sh\n",
    "aws s3 cp s3://2020-fall-gu511-[YOUR_GU_ID]/dbcredentials.txt .\n",
    "cat dbcredentials.txt && echo # print a new line so it's not \"hidden\"\n",
    "```\n",
    "\n",
    "\n",
    "## 1.2: verify you can write\n",
    "\n",
    "next we will test that you can upload files to that bucket. note: we have to do something special here: we have to set an `acl` (permissions, not ligaments) value. an `a`ccess `c`ontrol `l`ist is something we've set silently on everything we've created so far. in order to make sure that *I* (the bucket owner) can read what *you* (the `s3` file object owner) have created, you have to explicitly grant me that permission. do so with the extra flag `--acl bucket-owner-full-control`:\n",
    "\n",
    "```sh\n",
    "echo \"hello world\" > test.txt\n",
    "aws s3 cp test.txt s3://2020-fall-gu511-[YOUR_GU_ID]/ --acl bucket-owner-full-control\n",
    "```\n",
    "\n",
    "ultimately, you can verify that this worked by going to `https://s3.console.aws.amazon.com/s3/object/2020-fall-gu511-[YOUR_GU_ID]?region=us-east-1&prefix=test.txt` (note: you must fill in `[YOUR_GU_ID]` with, you know...). you should see something like:\n",
    "\n",
    "<br><div align=\"center\"><img src=\"http://drive.google.com/uc?export=view&id=1cgjB7MIOC-XYhPlMdGmC3SC3ZM8EdzK9\"></div>\n",
    "\n",
    "##### we will verify that a `test.txt` file was written to your `s3` final submission bucket"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div style=\"border: 1px solid lightgrey;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# exercise 2: access a list of downloadable `zip` files\n",
    "\n",
    "head over to [the `usaspending.gov` award data archive page](https://www.usaspending.gov/download_center/award_data_archive) we visited in a previous homework assignment.\n",
    "\n",
    "the page contains a form which allows us to specify a federal agency (default is \"All\"), an award type (default is \"Contracts\") and a year (default is 2020). the basic layout is like this (obviously years and dates will be more recent):\n",
    "\n",
    "<br><div align=\"center\"><img src=\"http://drive.google.com/uc?export=view&id=1YrjWErf_J-bE5Fz_25mdxG0cvnHCjuI2\"></div>\n",
    "\n",
    "previously we downloaded one of those files -- `FY2020_All_Contracts_Full_20201108.zip`. today our goal is to download every `FYYYYY_All_Contracts_Full_YYYYMMDD.zip` file. to do that, we *could* click through the form for every year and download every `zip` `url` by hand... but we have better tools!\n",
    "\n",
    "\n",
    "## 2.1: figuring out the `api` endpoint we want to use\n",
    "\n",
    "it would be great if we could find a programatic way to list the monthly files we want to download. if we could programmatically access the `url`s of those `zip` archives, we could do everything much faster...\n",
    "\n",
    "you'll be shocked to find out that *we can*!\n",
    "\n",
    "first, collect some info from the webpage itself. once you've loaded that award data archive page, open your browser's developer tools and navigate to the tab which shows all `request`s and `response`s made by your browser. if your browser is\n",
    "\n",
    "+ google chrome, go to the \"Network\" tab and click the \"preserve logs\" checkbox\n",
    "+ firefox, go to the \"Network\" tab and click the \"persist logs\" checkbox\n",
    "+ IE, go to the \"Network\" tab and toggle the \"Clear entries on navigate\" icon (details [here](https://docs.microsoft.com/en-us/previous-versions/windows/internet-explorer/ie-developer/samples/dn255004%28v=vs.85%29))\n",
    "\n",
    "click on the \"Fiscal Year\" drop-down and change it to 2020, then click the \"Apply\" button and see what happens in the Network tab of your browser's developer tools window.\n",
    "\n",
    "+ what `url`s are `request`ed?\n",
    "+ what type of `request` (`GET`, `POST`, etc) are those?\n",
    "+ what is the `response`?\n",
    "+ do any of the `response`s have the information we are looking for?\n",
    "\n",
    "one of the `request`s we made lines up with one of the endpoints of [the `usaspending` `api`](https://api.usaspending.gov/docs/endpoints) -- specifically, one of the [download endpoints](https://github.com/fedspendingtransparency/usaspending-api/tree/master/usaspending_api/api_contracts/contracts/v2/bulk_download). if called correctly, it will respond with a `json` blob that contains all of the download information we need.\n",
    "\n",
    "<br><div align=\"center\"><img src=\"http://drive.google.com/uc?export=view&id=1NsCNY3lU1FQMy5GTMxU5rmNrA-WkiImW\"></div>\n",
    "\n",
    "\n",
    "## 2.2: a function to get the `json` data we need\n",
    "\n",
    "using the `python` `requests` library and the information you can glean from the developer tools and the `api` documentation, fill in the following function which takes a year and returns a `url` for downloading a `FYYYYY_All_Contracts_Full_YYYYMMDD.zip` archive of award data. save this to a file named `urls.py`.\n",
    "\n",
    "```python\n",
    "import requests\n",
    "\n",
    "def get_zip_url(year):\n",
    "    \"\"\"given the year of interest, find the current url of the zip file\n",
    "    which contains contract data for *all* agencies for that year\n",
    "    \n",
    "    args:\n",
    "        year (int): the year of contract information we want\n",
    "        \n",
    "    returns:\n",
    "        str: the url of the zip archive for that year's contract data\n",
    "        \n",
    "    \"\"\"\n",
    "    resp = #-----------------#\n",
    "           # FILL THIS IN!!! #\n",
    "           #-----------------#\n",
    "            \n",
    "    j = resp.json()\n",
    "    \n",
    "    # somewhere within that response's json payload is the url -- get it!\n",
    "    # make sure you get the *FULL* file and not the *DIFF* file\n",
    "    url = #-----------------#\n",
    "          # FILL THIS IN!!! #\n",
    "          #-----------------#\n",
    "    \n",
    "    return url\n",
    "\n",
    "\n",
    "def zip_url_range(year_0, year_1):\n",
    "    \"\"\"a simple helper function for iterating over a range of years.\n",
    "    this will be inclusive of both endpoints.\n",
    "    \n",
    "    args:\n",
    "        year_0 (int): the first year to get\n",
    "        year_1 (int): the last year to get\n",
    "    \n",
    "    \"\"\"\n",
    "    return [get_zip_url(year) for year in range(year_0, year_1 + 1)]\n",
    "```\n",
    "\n",
    "if all goes according to plan, you should be able to run\n",
    "\n",
    "```python\n",
    "get_zip_url(2021)\n",
    "```\n",
    "\n",
    "and the `url` it spits out is the one given by the first link in the table on [the award archive page](https://www.usaspending.gov/#/download_center/award_data_archive). at the time of writing, that is `https://files.usaspending.gov/award_data_archive/20201202.zip`\n",
    "\n",
    "similarly, you should be able to run\n",
    "\n",
    "```python\n",
    "zip_url_range(2019, 2021)\n",
    "```\n",
    "\n",
    "and get a list containing three urls\n",
    "\n",
    "\n",
    "##### upload your updated `urls.py` file to your final `s3` submission bucket. don't forget the `--acl bucket-owner-full-control`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div style=\"border: 1px solid lightgrey;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# exercise 3: create an `ec2` instance\n",
    "\n",
    "\n",
    "## 3.1: image details\n",
    "\n",
    "we are going to use the list of urls we just generated in exercise 2 to do some bulk download from usaspending, unzipping, and uploading to `s3` (exercise 5). before we do that, though, let's create a new `ec2` instance to do everything on\n",
    "\n",
    "you have a choice to make for the instance type:\n",
    "\n",
    "+ you can use the **free** instance type `t2.micro`, but it will be **slow** (there is a task that will take about 2 hours and 45 minutes to run in the background (you don't have to wait on it to go forward))\n",
    "+ you can use the **for charge** (0.384 USD per hour) instance type `m5.2xlarge`, but it will be **much faster** (that same task will take about 30 minutes)\n",
    "\n",
    "it should have the following properties:\n",
    "\n",
    "+ `ami`: \"Amazon Linux 2 AMI (HVM), SSD Volume Type\" and 64 bit x86\n",
    "+ type: `t2.micro` (free and slow) or `m5.2xlarge` (for charge and fast)\n",
    "+ hard drive disk size: 20GiB (the default of 8 won't cut it)\n",
    "\n",
    "\n",
    "## 3.2: setting a `python` environment\n",
    "\n",
    "after the launch has been completed, `ssh` in to your machine and create a `miniconda` environment with the following:\n",
    "\n",
    "```sh\n",
    "wget https://repo.anaconda.com/miniconda/Miniconda3-latest-Linux-x86_64.sh\n",
    "sh Miniconda3-latest-Linux-x86_64.sh\n",
    "# answer the questions!\n",
    "rm Miniconda3-latest-Linux-x86_64.sh\n",
    "exit\n",
    "```\n",
    "\n",
    "now, re-connect to your instance and verify that your `python` version is correct:\n",
    "\n",
    "```sh\n",
    "python --version\n",
    "```\n",
    "\n",
    "you should have version 3.8.5 (or higher)\n",
    "\n",
    "now, also install `boto3` with\n",
    "\n",
    "```sh\n",
    "conda install -y boto3\n",
    "```\n",
    "\n",
    "\n",
    "## 3.3: pics or it didn't happen\n",
    "\n",
    "from the web console, take a screenshot of your new `ec2` instance's details menu. for example, mine looks like\n",
    "\n",
    "<br><div align=\"center\"><img src=\"http://drive.google.com/uc?export=view&id=1hZtYhBRWskKLAMiIBTAWcX5Iq2-3RKxB\"></div>\n",
    "\n",
    "be sure your screenshot includes at least the items highlighted in the red box\n",
    "\n",
    "save that snapshot as `exercise1_ec2_details.png` (you can use a different image format (e.g. `gif`, `jpeg`) if that is easier; just make sure the base name is the same).\n",
    "\n",
    "##### upload that `exercise1_ec2_details.png` to your final `s3` submission bucket. don't forget the `--acl bucket-owner-full-control`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div style=\"border: 1px solid lightgrey;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# exercise 4: create an `ec2`-service `iam` `role`\n",
    "\n",
    "using the `aws` web console, create a new `role` with the following properties\n",
    "\n",
    "1. it should be an `aws` service role which will be used by the `ec2` service\n",
    "1. it will have the `AmazonS3FullAccess` permission policy attached\n",
    "1. it will be named `gu511-final-ec2-s3-role`\n",
    "\n",
    "back in the `ec2` service, find your final `ec2` server you created in exercise 2 and attach this role to it (Actions > Security > Modify IAM Role)\n",
    "\n",
    "switch from the Details tab to the Security tab and take *another* screenshot of this window, making sure to include the `IAM role` field\n",
    "\n",
    "<br><div align=\"center\"><img src=\"http://drive.google.com/uc?export=view&id=1O3kW_5_aAt59LNJm_H1OV8I8xkWorpus\"></div>\n",
    "\n",
    "this time save that picture with the name `exercise2_ec2_w_s3_role.png`\n",
    "\n",
    "##### upload that `exercise2_ec2_w_s3_role.png` to your final `s3` submission bucket. don't forget the `--acl bucket-owner-full-control`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div style=\"border: 1px solid lightgrey;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# exercise 5: download, unzip, and upload files to `s3`\n",
    "\n",
    "let's create a single `python` function which can download the `zip` files for `usaspending`, unzip them, and upload them to `s3`. the downloading and unzipping code will be provided for you (but explained, in 5.2 and 5.3 below). you will be responsible for filling in the upload of the created files using the `boto3` library (5.4).\n",
    "\n",
    "\n",
    "## 5.1: getting a list of `zip` files to download\n",
    "\n",
    "the code we defined in the previous exercise will be used to identify the list of `zip` files we should download. if it is working as expected, we should be able to run (in the directory that contains `urls.py`)\n",
    "\n",
    "```python\n",
    "import urls\n",
    "\n",
    "zip_urls = urls.zip_url_range(2001, 2021)\n",
    "```\n",
    "\n",
    "if you didn't complete exercise 4 yet, you can still get a file `urls.py` which will work for now/ please do the following: in a `bash` shell, run\n",
    "\n",
    "```sh\n",
    "wget https://s3.amazonaws.com/shared.rzl.gu511.com/urls.py\n",
    "```\n",
    "\n",
    "and verify it works by opening a `python` session and running\n",
    "\n",
    "```python\n",
    "import urls\n",
    "\n",
    "zip_urls = urls.zip_url_range(2001, 2021)\n",
    "\n",
    "assert len(zip_urls) == 21\n",
    "```\n",
    "\n",
    "\n",
    "## 5.2: downloading a `zip` file\n",
    "\n",
    "downloading files is possible with the `requests` library but a little messy and frankly slower than I'm willing to live with for big files -- why not use a faster `bash` option like `wget`? we will use the `python` `subprocess` module to call a `bash` command from *within* our `python` code.\n",
    "\n",
    "for example, take the last `zip_url`\n",
    "\n",
    "```python\n",
    "zip_urls[-1]\n",
    "```\n",
    "\n",
    "at the time of writing, this was https://files.usaspending.gov/award_data_archive/FY2020_All_Contracts_Full_20201108.zip\n",
    "\n",
    "we can easily download that from a `bash` command prompt on our `aws` `linux` `ec2` instances with\n",
    "\n",
    "```sh\n",
    "wget --quiet https://files.usaspending.gov/award_data_archive/FY2020_All_Contracts_Full_20201108.zip\n",
    "```\n",
    "\n",
    "to use the `subprocess` module, we simply need to build our command strings up as if we were calling them from the `bash` prompt and then split them up based on the spaces within them (done for us in \"the right way\" by the `shlex` module). the above `bash` command could be done from within `python` via\n",
    "\n",
    "```python\n",
    "import shlex\n",
    "import subprocess\n",
    "\n",
    "cmd = 'wget --quiet https://files.usaspending.gov/award_data_archive/FY2020_All_Contracts_Full_20201108.zip'\n",
    "cmd = shlex.split(cmd)\n",
    "subprocess.run(cmd)\n",
    "```\n",
    "\n",
    "\n",
    "## 5.3: unzipping a downloaded `zip` file\n",
    "\n",
    "each `zip` archive we have downloaded is (internally) a collection of 1M-record-`csv`s representing the contract actions for the downloaded year. the `zipfile` library in `python` is sufficient for extracting these `zip` files into the component `csv` files.\n",
    "\n",
    "if we had downloaded the `FY2020_All_Contracts_Full_20201108.zip` archive file as in 5.2, we could now run\n",
    "\n",
    "```python\n",
    "import zipfile\n",
    "\n",
    "with zipfile.ZipFile('FY2020_All_Contracts_Full_20201108.zip') as zfp:\n",
    "    zfp.extractall()\n",
    "```\n",
    "\n",
    "this would create files in the same directory as `FY2020_All_Contracts_Full_20201108.zip` that are the contents of that `zip` file.\n",
    "\n",
    "we can find the names of those `csv` files we have unzipped with the `glob` library:\n",
    "\n",
    "```python\n",
    "import glob\n",
    "\n",
    "unzipped_csvs = glob.glob('./*All_Contracts_Full*.csv')\n",
    "```\n",
    "\n",
    "\n",
    "## 5.4: uploading unzipped `csv` files to `s3`\n",
    "\n",
    "finally, something for you to do!\n",
    "\n",
    "\n",
    "### 5.4.1: creating an `s3` bucket for uploading these files\n",
    "\n",
    "in the `aws` web console, go to the `s3` service page and create a new bucket named `[YOUR GU ID]-gu511-final-usaspending`. leave all defaults\n",
    "\n",
    "\n",
    "### 5.4.2: filling in `boto3` code\n",
    "\n",
    "given the name of one of the unzipped `csv` files obtained via 5.2 and 5.3, fill in the following `python` code to upload that one file to your `s3` bucket you just created\n",
    "\n",
    "```python\n",
    "import boto3\n",
    "\n",
    "BUCKET_NAME = '[YOUR GU ID]-gu511-final-usaspending'\n",
    "\n",
    "def csv_to_s3(fcsv, region_name='us-east-1', bucket_name=BUCKET_NAME):\n",
    "    \"\"\"given the file name of a csv file, upload it to my s3 bucket.\n",
    "    we will put each file in a sub-directory of our bucket named\n",
    "    \"raw\", so that the full Key value will be\n",
    "    \n",
    "        Key = f'raw/{fcsv}'\n",
    "    \n",
    "    note: we don't need to deal with permissions or group names here\n",
    "    because we are using an iam role on our `ec2` server which has\n",
    "    full s3 permissions. we *do* still have to set the region, though!\n",
    "    stick with the default (us-east-1)\n",
    "    \n",
    "    args:\n",
    "        fcsv (str): path to the csv file we want to upload\n",
    "        region_name (str): the aws region for the boto3 session we are \n",
    "            about to create\n",
    "        bucket_name (str): the name of the s3 bucket into which we are\n",
    "            loading our csvs\n",
    "    \n",
    "    returns:\n",
    "        none\n",
    "    \n",
    "    \"\"\"\n",
    "    Key = f'raw/{fcsv}'\n",
    "    #-----------------#\n",
    "    # FILL THIS IN!!! #\n",
    "    #-----------------#\n",
    "```\n",
    "\n",
    "\n",
    "## 5.5: bringing it all together\n",
    "\n",
    "update the `csv_to_s3` function in the code below and save the results as `usaspending2s3.py`\n",
    "\n",
    "```python\n",
    "import datetime\n",
    "import glob\n",
    "import os\n",
    "import shlex\n",
    "import subprocess\n",
    "import zipfile\n",
    "\n",
    "import boto3\n",
    "import requests\n",
    "\n",
    "\n",
    "BUCKET_NAME = '[YOUR GU ID]-gu511-final-usaspending'\n",
    "\n",
    "\n",
    "def csv_to_s3(fcsv, region_name='us-east-1', bucket_name=BUCKET_NAME):\n",
    "    \"\"\"given the file name of a csv file, upload it to my s3 bucket.\n",
    "    we will put each file in a sub-directory of our bucket named\n",
    "    \"raw\", so that the full Key value will be\n",
    "    \n",
    "        Key = f'raw/{fcsv}'\n",
    "    \n",
    "    note: we don't need to deal with permissions or group names here\n",
    "    because we are using an iam role on our `ec2` server which has\n",
    "    full s3 permissions. we *do* still have to set the region, though!\n",
    "    stick with the default (us-east-1)\n",
    "    \n",
    "    args:\n",
    "        fcsv (str): path to the csv file we want to upload\n",
    "        region_name (str): the aws region for the boto3 session we are \n",
    "            about to create\n",
    "        bucket_name (str): the name of the s3 bucket into which we are\n",
    "            loading our csvs\n",
    "    \n",
    "    returns:\n",
    "        none\n",
    "    \n",
    "    \"\"\"\n",
    "    Key = f'raw/{fcsv}'\n",
    "    #-----------------#\n",
    "    # FILL THIS IN!!! #\n",
    "    #-----------------#\n",
    "\n",
    "\n",
    "def main():\n",
    "    t0 = datetime.datetime.now()\n",
    "    \n",
    "    # get the list of urls to download\n",
    "    zip_urls = requests.get(\n",
    "        'https://s3.amazonaws.com/shared.rzl.gu511.com/zip_list_key.json'\n",
    "    ).json()\n",
    "\n",
    "    for (i, zip_url) in enumerate(zip_urls):\n",
    "        # downloading...\n",
    "        cmd = f'wget --quiet {zip_url}'\n",
    "        cmd = shlex.split(cmd)\n",
    "        print(f'downloading {zip_url}...')\n",
    "        subprocess.run(cmd)\n",
    "        print('download complete')\n",
    "\n",
    "        # unzipping...\n",
    "        zipfile_name = os.path.basename(zip_url)\n",
    "        print(f'unzipping {zipfile_name}...')\n",
    "        with zipfile.ZipFile(zipfile_name) as zfp:\n",
    "            zfp.extractall()\n",
    "        print('unzipped')\n",
    "        \n",
    "        # removing the zip file\n",
    "        os.remove(zipfile_name)\n",
    "        \n",
    "        # new csv files\n",
    "        unzipped_csvs = glob.glob('*All_Contracts_Full*.csv')\n",
    "        print(f'{len(unzipped_csvs)} new csv files')\n",
    "        for fcsv in unzipped_csvs:\n",
    "            print(f'uploading file {fcsv} to s3...')\n",
    "            csv_to_s3(fcsv)\n",
    "            print('uploaded')\n",
    "        \n",
    "            # removing the csv file\n",
    "            os.remove(fcsv)\n",
    "        \n",
    "        t1 = datetime.datetime.now()\n",
    "        print(f'we have completed {i + 1} out of {len(zip_urls)} years')\n",
    "        print(f'in {t1 - t0}')\n",
    "    \n",
    "    t1 = datetime.datetime.now()\n",
    "    print(f'task complete in {t1 - t0}')\n",
    "\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    main()\n",
    "```\n",
    "\n",
    "if everything works as planned, you should be able to push all the `usaspending` data to your `s3` bucket with this script. \n",
    "\n",
    "this entire process takes about **2 hours and 45 minutes** on a `t2.micro` and about **30 minutes** on an `m4.2xlarge`, so you may not want to stay and watch it run. you need to \"background\" the command with `nohup`:\n",
    "\n",
    "```sh\n",
    "nohup python -u usaspending2s3.py &\n",
    "```\n",
    "\n",
    "when you execute the code that way, you can close your terminal and come back without killing the running process. you can check in on the state of that process (now running in the background) with\n",
    "\n",
    "```sh\n",
    "tail -f nohup.out\n",
    "```\n",
    "\n",
    "at any time.\n",
    "\n",
    "***NOTE: if you created the more expensive `ec2` instance to do this assignment, please shut it off!!!***\n",
    "\n",
    "\n",
    "##### upload your file `usaspending2s3.py` to your final `s3` submission bucket. don't forget the `--acl bucket-owner-full-control`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div style=\"border: 1px solid lightgrey;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# exercise 6: `\\copy`ing those `csv`s to `rds` \n",
    "\n",
    "we just uploaded almost 100GiB of files to `s3`, and it would be nice to put them some place. one options is in a traditional relational database like `postgres`.\n",
    "\n",
    "\n",
    "## 6.1: logging in\n",
    "\n",
    "I have created a user name and table for each of you on my personal `postgres` database. you can connect to it with the `psql` command. first and foremost, on your `ec2` instance you created in exercise 2, install the `psql` command\n",
    "\n",
    "```sh\n",
    "sudo yum install -y postgresql\n",
    "```\n",
    "\n",
    "after installation is complete, use the `psql` command to connect to my `postgres` `rds` with the command\n",
    "\n",
    "```sh\n",
    "GUID=[FILL THIS IN!!]\n",
    "psql -h gu511-fall-2020-final-psql.cdmknaubrmaw.us-east-1.rds.amazonaws.com -U $GUID --dbname postgres\n",
    "```\n",
    "\n",
    "you will be prompted for your password -- that is in the file `dbcredentials.txt` which I uploaded to your `s3` finals bucket. you can view it with the series of commands\n",
    "\n",
    "```sh\n",
    "aws s3 cp s3://2020-fall-gu511-$GUID/dbcredentials.txt .\n",
    "cat dbcredentials.txt && echo\n",
    "# optionally, remove it\n",
    "rm dbcredentials.txt\n",
    "```\n",
    "\n",
    "you can verify that you've logged in and that the table exists by executing\n",
    "\n",
    "```sql\n",
    "select count(*) from [YOUR GU ID]_usaspending;\n",
    "```\n",
    "\n",
    "you should see 0 rows\n",
    "\n",
    "\n",
    "## 6.2: getting data to insert\n",
    "\n",
    "rather than insert any one (let alone all!) of those large files, let's just get one much smaller file and insert that as a test of our `\\copy` `psql` skills. download the insertable records `csv` file on your `ec2` instance with\n",
    "\n",
    "```sh\n",
    "wget https://s3.amazonaws.com/shared.rzl.gu511.com/usaspending_dev.csv -O /tmp/usaspending_dev.csv\n",
    "```\n",
    "\n",
    "\n",
    "## 6.3: inserting data\n",
    "\n",
    "use the `\\copy` command to copy the local (to your finals `ec2` instance) `/tmp/usaspending_dev.csv` file into the table named `[YOUR GU ID]_usaspending`. write the command you use to do this to a file `copy_usaspending_to_postgres.psql`\n",
    "\n",
    "a successful command will yield 100 records in the `[YOUR GU ID]_usaspending` table:\n",
    "\n",
    "```\n",
    "gu511db=> select count(*) from [YOUR GU ID]_usaspending ;\n",
    " count \n",
    "-------\n",
    "   100\n",
    "(1 row)\n",
    "```\n",
    "\n",
    "\n",
    "***note: you are done with your finals `ec2` instance now, so feel free to terminate it if you have completed the assignments up to this one***\n",
    "\n",
    "##### upload `copy_usaspending_to_postgres.psql` to your final `s3` submission bucket. don't forget the `--acl bucket-owner-full-control`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div style=\"border: 1px solid lightgrey;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# exercise 7: `copy`ing those `csv`s to `redshift`\n",
    "\n",
    "our entire collection of `csv`s saved out on `s3` is now about 81.2 GiB -- well within the reach of a typical `rdbms` like `postgres` or `redshift`. because our data is in `s3` and we can copy directly from `s3` to `redshift` (and we cannot say the same for `postgres`) let's go with `redshift` for our main data warehouse for this `usaspending` data.\n",
    "\n",
    "we would like to `copy` the files in our `s3` bucket (named `[YOUR GU ID]-gu511-final-usaspending`) to `redshift`.\n",
    "\n",
    "\n",
    "## 7.1: re-connect to or re-build your `redshift` cluster from a previous homework\n",
    "\n",
    "in the `012_dbs_5_redshift` lecture, we walked through the creation of a `redshift` cluster and installation of `datagrip`. additionally, on a previous homework you were asked to execute a few queries in that `redshift` cluster using `datagrip`.\n",
    "\n",
    "if that cluster (the one you created) is still up and running, use it again for this excercise. if not, please create a new `redshift` cluster following the steps laid out in that `012_dbs_5_redshift.ipynb` lecture, and connect to it using `datagrip`.\n",
    "\n",
    "**note**: please re-use the `allow_redshift_s3_read_role` `iam` role for this cluster (created in lecture [here](https://colab.research.google.com/github/rzl-ds/gu511/blob/master/012_dbs_5_redshift.ipynb#scrollTo=YokUQWSImXOv)). we will again be copying files from `s3` so this will be essential\n",
    "\n",
    "\n",
    "## 7.2: alternatives to your `s3` bucket\n",
    "\n",
    "we want to push about 115 GiB of `csv` files from `s3` to `redshift`. if you were unable to complete exercise 4 and have not populated a bucket named `[YOUR GU ID]-gu511-final-usaspending` with the `usaspending` data, you may instead use my publicly available bucket `rzl5-gu511-final-usaspending` which has files located in `s3://rzl5-gu511-final-usaspending/raw`.\n",
    "\n",
    "\n",
    "## 7.3: the `create table` command\n",
    "\n",
    "I initially considered asking you to determine the schema for this 277-column table, but the fact of the matter is that it's a long, stupid exercise and would be mostly a waste of your time. so I have saved the `create table` command for you as a file at https://s3.amazonaws.com/shared.rzl.gu511.com/redshift_create_usaspending.sql. download that file and execute it while connected to your `redshift` cluster.\n",
    "\n",
    "\n",
    "## 7.4: copy command\n",
    "\n",
    "let's load `csv` information from `s3` into a `redshift` table. this is very similar to how we created the `trainpositions` table in a previous homework, with one twist: we are loading all of the items in an `s3` bucket instead of just one file. you may need to [refer to the `redshift` documentation](https://docs.aws.amazon.com/redshift/latest/dg/copy-parameters-data-source-s3.html)\n",
    "\n",
    "a properly running command can take **1 hour and 45 minutes** to run. if you haven't seen an error through a minute, it's running. go do anything else.\n",
    "\n",
    "when you have a `copy` query expression that succesfully copies records from `s3` to `redshift`, save that expression to a file `s3toredshift.sql`\n",
    "\n",
    "\n",
    "##### upload `s3toredshift.sql` to your final `s3` submission bucket. don't forget the `--acl bucket-owner-full-control`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div style=\"border: 1px solid lightgrey;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# exercise 8: some simple `sql` queries\n",
    "\n",
    "\n",
    "## 8.1: a `redshift` for all of us\n",
    "\n",
    "hopefully you were able to complete exercise 7 and have access to a `redshift` cluster with a `usaspending` table in it. if not, though, you can connect to my `redshift` cluster.\n",
    "\n",
    "+ jdbc: `jdbc:redshift://rzl5-gu511-final-redshift.csneoatl4kff.us-east-1.redshift.amazonaws.com:5439/dev`\n",
    "+ the `username` and `password` were included in the announcement on canvas\n",
    "\n",
    "if you run into any issues connecting, please reach out.\n",
    "\n",
    "\n",
    "## 8.2: querying the `usaspending` data\n",
    "\n",
    "let's do just a few simple things to make sure our `redshift` cluster is functional. fill in the `sql` queries describe below and save the resulting file as `usaspending_queries.sql`.\n",
    "\n",
    "for reference, I have included the results I received for the `csv`s which had an \"as of\" date `2020-11-08` (this could change during the run of the exam, and I will try to update the text below to include those values as well)\n",
    "\n",
    "```sql\n",
    "/*  understaning awards ---------------------------------------------------- */\n",
    "\n",
    "/*  a single award (aka contract) is defined by a pair of values:\n",
    "\n",
    "        award_id_piid, parent_award_id_piid\n",
    "\n",
    "    every award has an award_id_piid, but there are a handful of records that\n",
    "    don't have parent_award_id_piid values (they are the highest-level contracts\n",
    "    and other contracts roll up under them)\n",
    "\n",
    "    the query below will show a handful of these pairs of identifying id values\n",
    "    among our many records. note that many have parent_award_id_piid values that\n",
    "    are null!\n",
    "*/\n",
    "SELECT DISTINCT award_id_piid,\n",
    "                parent_award_id_piid\n",
    "FROM usaspending\n",
    "LIMIT 100\n",
    ";\n",
    "\n",
    "/*  we can calculate whether or not a contract has a parent by checking the\n",
    "    value of parent_award_id_piid\n",
    "\n",
    "        parent_award_id_piid = ''\n",
    "\n",
    "    indicates there is no parent, and\n",
    "\n",
    "        parent_award_id_piid != ''\n",
    "\n",
    "    indicates there is a parent\n",
    "\n",
    "    we can easily determine how many conracts have parents\n",
    "*/\n",
    "SELECT has_parent,\n",
    "       COUNT(*) AS ct\n",
    "FROM (SELECT DISTINCT award_id_piid,\n",
    "                      parent_award_id_piid,\n",
    "                      CASE\n",
    "                          WHEN parent_award_id_piid = '' THEN 0\n",
    "                          ELSE 1\n",
    "                      END AS has_parent\n",
    "      FROM usaspending)\n",
    "GROUP BY has_parent\n",
    ";\n",
    "/*  for \"as of\" 2020-11-08, this is\n",
    "\n",
    "1 38202037\n",
    "0 14282946\n",
    "\n",
    "    for \"as of\" 2020-12-08, this is\n",
    "\n",
    "1 38617440\n",
    "0 14337985\n",
    "*/\n",
    "\n",
    "\n",
    "/*  modifications of awards ------------------------------------------------- */\n",
    "\n",
    "/*  for any single award (aka contract) defined by\n",
    "\n",
    "        award_id_piid, parent_award_id_piid\n",
    "\n",
    "    a modification is any distinct value of the field\n",
    "\n",
    "        modification_number\n",
    "\n",
    "    find the 5 awards which had the highest number of modifications\n",
    "*/\n",
    "SELECT\n",
    "    -- FILL THIS IN!!!\n",
    "FROM\n",
    "    -- FILL THIS IN!!!\n",
    "GROUP BY\n",
    "    -- FILL THIS IN!!!\n",
    "ORDER BY\n",
    "    -- FILL THIS IN!!!\n",
    "LIMIT 5;\n",
    "/*  for \"as of\" 2020-11-08, this is\n",
    "\n",
    "N0002409C2107  \"\"  3992\n",
    "GS35F0511T     \"\"  3898\n",
    "USZA2203C0056  \"\"  3507\n",
    "F4261098C0001  \"\"  3178\n",
    "N0002403C2101  \"\"  2672\n",
    "\n",
    "    for \"as of\" 2020-12-08, this is\n",
    "\n",
    "N0002409C2107  \"\"  3992\n",
    "GS35F0511T     \"\"  3962\n",
    "USZA2203C0056  \"\"  3507\n",
    "F4261098C0001  \"\"  3179\n",
    "N0002403C2101  \"\"  2673\n",
    "\n",
    "*/\n",
    "\n",
    "\n",
    "/*  find the 5 awards *which had parents* which had the highest number of\n",
    "    modifications\n",
    "*/\n",
    "SELECT\n",
    "    -- FILL THIS IN!!!\n",
    "FROM\n",
    "    -- FILL THIS IN!!!\n",
    "WHERE\n",
    "    -- FILL THIS IN!!!\n",
    "GROUP BY\n",
    "    -- FILL THIS IN!!!\n",
    "ORDER BY\n",
    "    -- FILL THIS IN!!!\n",
    "LIMIT 5;\n",
    "/*  for both \"as of\" 2020-11-08 and \"as of\" 2020-12-08, this is\n",
    "\n",
    "0004        N0002404D4409  679\n",
    "SD21        F3365701D2050  531\n",
    "0017        W912ER04D0004  488\n",
    "FB00        N6927200D3170  431\n",
    "NNJ07JF12D  NAS598144      383\n",
    "\n",
    "*/\n",
    "\n",
    "/*  award monetary values --------------------------------------------------- */\n",
    "\n",
    "/*  the monetary value of a contract is given by the field\n",
    "\n",
    "        current_total_value_of_award\n",
    "\n",
    "    find the highest monetary value of any single award\n",
    "*/\n",
    "SELECT\n",
    "    -- FILL THIS IN!!!\n",
    "FROM\n",
    "-- FILL THIS IN!!!\n",
    ";\n",
    "/*  for both \"as of\" 2020-11-08 and \"as of\" 2020-12-08, this is\n",
    "\n",
    "    1000202900000\n",
    "\n",
    "    (that's 1 trillion dollars, 1,000,202,900,000)\n",
    "*/\n",
    "\n",
    "/*  find the lowest *non-zero* monetary value of any single award  */\n",
    "SELECT\n",
    "    -- FILL THIS IN!!!\n",
    "FROM\n",
    "    -- FILL THIS IN!!!\n",
    "WHERE\n",
    "    -- FILL THIS IN!!!\n",
    ";\n",
    "/*  for both \"as of\" 2020-11-08 and \"as of\" 2020-12-08, this is\n",
    "\n",
    "    0.01\n",
    "*/\n",
    "\n",
    "/*  contract termination ---------------------------------------------------- */\n",
    "\n",
    "/*  when a modification is made to a contract the modifying agent is required to\n",
    "    provide a code describing why that action was taken. that code is saved as\n",
    "    field\n",
    "\n",
    "        action_type\n",
    "\n",
    "    there ar a handful of action types, but among them are a few \"bad\"\n",
    "    action_type values:\n",
    "\n",
    "        TERMINATE FOR DEFAULT (COMPLETE OR PARTIAL)\n",
    "        TERMINATE FOR CAUSE\n",
    "\n",
    "    for every award, determine whether or not that award ever included either or\n",
    "    the above `action_type` values. do this by using two sql functions:\n",
    "\n",
    "        + use a `case` statement to create a binary variable for each record\n",
    "          with a value of 1 if the `action_type` is one of the two above and 0\n",
    "          otherwise\n",
    "        + use the `max` function to determine whether or not there were any 1\n",
    "          indicators for a given award\n",
    "\n",
    "    the result of this query will be a list of awards (award_id_piid,\n",
    "    parent_award_id_piid pairs) and a flag indicating whether there was or was\n",
    "    not a termination on that award. we will save the result of that query as a\n",
    "    new table\n",
    "\n",
    "    NOTE: if you are running on the shared cluster, you probably can't create\n",
    "    this table because I already did. create your own with a name of\n",
    "    `[YOUR_GU_ID]_award_terminated`. once you are finished, please also remove\n",
    "    this table (`DROP TABLE [YOUR_GU_ID]_award_terminated`)\n",
    "*/\n",
    "CREATE TABLE award_terminated AS (\n",
    "    SELECT award_id_piid,\n",
    "           parent_award_id_piid,\n",
    "           (\n",
    "               -- FILL THIS IN!!!\n",
    "               ) AS had_termination_action_type\n",
    "    FROM usaspending\n",
    "    GROUP BY award_id_piid,\n",
    "             parent_award_id_piid\n",
    ");\n",
    "\n",
    "/*  if this worked, you can run the following  */\n",
    "SELECT had_termination_action_type,\n",
    "       COUNT(*)\n",
    "FROM award_terminated\n",
    "GROUP BY had_termination_action_type\n",
    ";\n",
    "/*  for \"as of\" 2020-11-08, this is\n",
    "\n",
    "0  52470836\n",
    "1  14147\n",
    "\n",
    "    for \"as of\" 2020-12-08, this is\n",
    "\n",
    "0  52941224\n",
    "1  14201\n",
    "\n",
    "*/\n",
    "```\n",
    "\n",
    "##### upload `usaspending_queries.sql` to your final `s3` submission bucket. don't forget the `--acl bucket-owner-full-control`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div style=\"border: 1px solid lightgrey;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# exercise 9: using `emr` to process `s3` spending data\n",
    "\n",
    "at the end of the previous exercise we developed a `sql` query to determine whether or not any one of the contract actions defined for a single contract (uniquely identified by a pair of fields, `award_id_piid` and `parent_award_id_piid`) were of a \"bad\" type. let's replicate that using `python` scripts and `hadoop` streaming in an `emr` cluster!\n",
    "\n",
    "\n",
    "## 9.1: starting a cluster\n",
    "\n",
    "create a new `emr` cluster using the \"quick options\" menu. you can clone the one we made in class, or you can create a new one -- if you go for a new cluster, the only things to change from the default configurations are:\n",
    "\n",
    "+ pick a descriptive name\n",
    "+ under the \"Security and access\" section, pick an \"EC2 key pair\" that you have access to.\n",
    "+ make sure that the security group for the master node has a rule allowing you to make `ssh` connections from your personal IP address.\n",
    "\n",
    "\n",
    "## 9.2: getting the code\n",
    "\n",
    "log in to the `emr` master node and download the `usaspending_mapreduce` code. make sure you're getting the `branch` named **`final`**, not the `master` branch, with the following code:\n",
    "\n",
    "```sh\n",
    "sudo yum install -y git\n",
    "git clone -b final https://github.com/rzl-ds/usaspending_mapreduce.git\n",
    "cd usaspending_mapreduce\n",
    "```\n",
    "\n",
    "and then put the `mapper.py` and `reducer.py` files into `hdfs` with\n",
    "\n",
    "```sh\n",
    "hadoop fs -put mapper.py\n",
    "hadoop fs -put reducer.py\n",
    "```\n",
    "\n",
    "\n",
    "## 9.3: executing a map reduce job\n",
    "\n",
    "following the example in the lecture and the last homework, execute a `hadoop-streaming` job using the provided `mapper.py` and `reducer.py` files.\n",
    "\n",
    "\n",
    "### 9.3.1: development\n",
    "\n",
    "for a development stage, use a small input\n",
    "\n",
    "+ `input`: your smallest `csv` in `s3`\n",
    "    + as of writing, this would be `s3://[YOUR GU ID]-gu511-final-usaspending/raw/FY2004_All_Contracts_Full_20201108_3.csv`\n",
    "+ `output`: `s3://rzl5-gu511-final-usaspending/terminations-test/`\n",
    "    + this is the directory your output will be written to\n",
    "+ you will also need to specify the `files`, `mapper`, and `reducer` arguments to `hadoop jar`\n",
    "\n",
    "if this works as expected, you will see the standard `map` `reduce` log messages in your terminal, and in the end you will have a directory `s3://rzl5-gu511-final-usaspending/terminations-test/` that contains `part-****` files with the output of the map-reduce job.\n",
    "\n",
    "\n",
    "### 9.3.2: doing the full job\n",
    "\n",
    "the ultimate goal is to run this process for your *entire* `s3` bucket `s3://[YOUR GU ID]-gu511-final-usaspending/raw`, and to write the result to a directory `s3://[YOUR GU ID]-gu511-final-usaspending/terminations`. \n",
    "\n",
    "this whole process will take about ***1 hour and 45 minutes***\n",
    "\n",
    "please write the `hadoop jar` command you use to run a `hadoop-streaming` job with `mapper.py` and `reducer.py` and save it as `mapreduce_termination.sh`\n",
    "\n",
    "\n",
    "##### upload `mapreduce_termination.sh` to your final `s3` submission bucket. don't forget the `--acl bucket-owner-full-control`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div style=\"border: 1px solid lightgrey;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# exercise 10: `fork`ing and `pull request`ing\n",
    "\n",
    "one final `github` exercise until freedom!\n",
    "\n",
    "in two of the last homework exercises we covered special `github`-specific features for collaborating on a `repository` via special `github` features -- either by direct commits to the `master` `branch` referencing issues (automatic closure of issues) or by creating a new `branch` in a repository and then initiating a `pull request` and a `github`-based merge commit.\n",
    "\n",
    "there is yet another way of contributing to repositories and collaborating on `github`, and that is `pull request`s from `fork`ed repositories.\n",
    "\n",
    "\n",
    "## 10.1: `fork`ing\n",
    "\n",
    "a `fork` or a repository is a special `github` concept. basically, you are a creating your own personal copy of someone else's `github` repository in a way that `github` knows how to track (it \"remembers\" the original repository you copied).\n",
    "\n",
    "I have created a repository https://github.com/rzl-ds/gu511_students where there is one file for every one of your `github` account names. the content of that file right now is simply\n",
    "\n",
    "```txt\n",
    "I'm almost done!\n",
    "```\n",
    "\n",
    "in the top right corner of the `github` page is the `fork` button\n",
    "\n",
    "<br><div align=\"center\"><img src=\"http://drive.google.com/uc?export=view&id=1NChn8mklEgdeJWvV3slBl8w-Yb9Wb2Me\"></div>\n",
    "\n",
    "when you click on that button you will create your own copy of this repository under *your* `github` account.\n",
    "\n",
    "`fork` it!\n",
    "\n",
    "\n",
    "## 10.2: making a change in your `fork`ed `repo`\n",
    "\n",
    "you're no longer \"almost\" done, you *are* done. let's update that file to reflect that! please edit the file associated with your `github` account name (and **only** that file!). furthermore, let's do it *entirely* on `github` (no `cli` at all).\n",
    "\n",
    "in your `fork`ed `repo`, click on the link for your `.txt` file. at the top of the window showing the file's contents is a pencil icon which will allow you to edit directly on `github`. click it!\n",
    "\n",
    "<br><div align=\"center\"><img src=\"http://drive.google.com/uc?export=view&id=1tosAKKNttLjT1mofVoka7AOnTg1ciTQ3\"></div>\n",
    "\n",
    "in that editor, update the message to say anything you'd like (as long as it's different).\n",
    "\n",
    "at the bottom, you are given a form to fill out that mirrors the elements of the command line `commit` we would make.\n",
    "\n",
    "1. add whatever `commit` message you'd like\n",
    "1. **click the \"Create a new branch\"** radio button\n",
    "1. click \"propose file change\"\n",
    "1. **DO NOT** create a pull request from this page\n",
    "    + confusingly, `github` here is suggesting a `pull request` from your `fork`ed `branch` to your `fork`ed `master` -- that's *not* what we want to do\n",
    "1. head back to your repository's top level web page\n",
    "\n",
    "at this point, if everything went according to plan, you'll see something like\n",
    "\n",
    "<br><div align=\"center\"><img src=\"http://drive.google.com/uc?export=view&id=1XJr2msCzooJa9iRU49zHqqkRYvGNCPf1\"></div>\n",
    "\n",
    "1. click on \"compare & pull request\"\n",
    "1. from the left dropdown boxes, select `rzl-ds/gu511_students` and `master` branch\n",
    "1. from the right dropdown boxes, select your repo and your recently created `patch` branch\n",
    "1. click \"create pull request\"\n",
    "\n",
    "at this point I will receive a notification that you have asked me (owner of `rzl-ds/gu511_students`) if I will accept your `pull request`. I will be able to reivew it and possibly `merge` it into my original `repo` all from within the comfort of my `chrome` window\n",
    "\n",
    "##### there is nothing to submit; work will be verified on `github`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div style=\"border: 1px solid lightgrey;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# exercise -1: cleaning up `aws` services \n",
    "\n",
    "we've reached the end! now's a good time to go through the following checklist of services we have used in this course and see if you have anything running there you don't want to keep -- they won't be free forever!\n",
    "\n",
    "+ `ec2`: this includes instances and also elastic ip addresses (you will start paying for them unless you release them)\n",
    "+ `s3`: please keep your homework and final submission buckets as is for the time being. beyond that, feel free to delete anything you no longer need\n",
    "+ `rds`: you can kill any database instances you created for this class\n",
    "    + check for backups / snapshots, too -- delete them if they exist\n",
    "+ `redshift`: if you have finished the exam, you can shut down any `redshift` clusters you have created\n",
    "    + check for backups / snapshots, too -- delete them if they exist\n",
    "+ `emr`: you should definitely shut down any `emr` clusters you have running"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div style=\"border: 1px solid lightgrey;\">"
   ]
  }
 ],
 "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.7.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": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "414px"
   },
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}