hw09.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a href=\"https://colab.research.google.com/github/rzl-ds/gu511_hw/blob/master/hw09.ipynb\" target=\"_parent\">\n",
    "    <img src=\"https://colab.research.google.com/assets/colab-badge.svg\"/>\n",
    "</a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exercises due by EOD 2020.11.13"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## goal"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "in this homework assignment we will learn how to work with the `rds` database service and `redshift`, as well as handle our first(ish) `git` CONFLICT"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## method of delivery"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*as mentioned in our first lecture, the method of delivery may change from assignment to assignment. we will include this section in every assignment to provide an overview of how we expect homework results to be submitted, and to provide background notes or explanations for \"new\" delivery concepts or methods.*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "this week you will be submitting the results of your homework in a few ays: via your `github` repo, via upload to a shared `s3` homework bucket, and via record insertion into shared databases.\n",
    "\n",
    "NO submission email\n",
    "\n",
    "summary:\n",
    "\n",
    "| exercise | deliverable | method of delivery | points |\n",
    "|----------|-------------|--------------------|--------|\n",
    "| 1 | a record in a shared `postgres` database | via an `INSERT` statement | 7 |\n",
    "| 2 | none | none | 4 |\n",
    "| 3 | none | none | 4 |\n",
    "| 4 | a file `bulk_insert_times.csv` | uploaded to your `s3` homework bucket | 10 |\n",
    "| 5 | none | none | 2 |\n",
    "| 6 | none | none | 2 |\n",
    "| 7 | none | none | 5 |\n",
    "| 8 | a file named `create_trainpositions.sql` | uploaded to your `s3` homework submission bucket | 5 |\n",
    "| 9 | a file named `copy_trainpositions.sql` | uploaded to your `s3` homework submission bucket | 5 |\n",
    "| 10 | a conflict-resolving `merge` commit | pushed to your shared `github` repo | 6 |\n",
    "\n",
    "total points: 50"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div style=\"border: 1px solid lightgrey;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# exercise 1: connect and write to a `postgres` database\n",
    "\n",
    "we have set up a shared `postgres` database with the following connection details:\n",
    "\n",
    "| parameter  | value |\n",
    "|------------|-------------------------------------------------------------|\n",
    "| `host`     | `gu511-shared-hw-postgres-2020.cdmknaubrmaw.us-east-1.rds.amazonaws.com` |\n",
    "| `port`     | `5432` |\n",
    "| `username` | `gu511` |\n",
    "| `db`       | `postgres` |\n",
    "| `password` | emailed to you |\n",
    "\n",
    "we would like for you to install the `psql` client (not the `postgres` database) on your `ec2 ubuntu` instances, use it to connect to this shared database, and `insert` a value into a shared table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.1: install `psql`\n",
    "\n",
    "on your `ec2 ubuntu` instances, install `psql` using the command\n",
    "\n",
    "```bash\n",
    "sudo apt install postgresql-client\n",
    "```\n",
    "\n",
    "you can verify that your installation worked be checking\n",
    "\n",
    "```bash\n",
    "which psql\n",
    "```\n",
    "\n",
    "and receiving an output that is the file path to the `psql` executible"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.2: connect to the database\n",
    "\n",
    "using the values we provided in the table above, the course notes, and `man psql`, figure out how to connect to our shared database with the `psql` command"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.3: `insert` a new record\n",
    "\n",
    "the following command will insert a record into a shared database we've created called `gu511_shared_table`. the table has two columns:\n",
    "\n",
    "1. `guid`: your georgetown user id\n",
    "2. `message`: a test message to verify you were able to successfully insert your record\n",
    "\n",
    "once you've successfully connect to the `postgres` database, insert a record with the following query (replacing `MY_GUID` and `MY_MESSAGE` with real values, of course):\n",
    "\n",
    "```sql\n",
    "INSERT INTO gu511_shared_table (guid, message) VALUES ('MY_GUID', 'MY_MESSAGE');\n",
    "```\n",
    "\n",
    "*note*: the single quotes in `'MY_GUID', 'MY_MESSAGE'` are required. they indicate that the values being passed in are strings, which is the datatype of those two coumns in our table\n",
    "\n",
    "\n",
    "##### the submission is the record in the shared database -- we will check results by running `SELECT * FROM gu511_shared_table;;`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div style=\"border: 1px solid lightgrey;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# exercise 2: creating an `rds` postgres instance\n",
    "\n",
    "**note: this is not significantly different from what we did in class; if you already did that you do not need to make a new one for this purpose**\n",
    "\n",
    "create your own `postgres` `rds` instance using the `aws` `rds` service. select the \"Easy Create\" method and update\n",
    "\n",
    "1. engine type: `postgresql`\n",
    "1. db instance size: \"Free tier\"\n",
    "1. db instance identifier: whatever you want\n",
    "1. master password\n",
    "\n",
    "after it starts up,\n",
    "\n",
    "1. edit the security group to allow `postgres` traffic (TCP on port 5432) from your `ec2` instance's **private** ip address.\n",
    "\n",
    "after it successfully starts up, be sure to capture the hostname, port, database name, master user name, and master user password.\n",
    "\n",
    "##### there is nothing to submit for this assignment"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div style=\"border: 1px solid lightgrey;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# exercise 3: creating an `ec2` server with `postgres` on it\n",
    "\n",
    "choose one of the following two options (regular (3.1) vs. advanced (3.2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.1: use an `ami`\n",
    "\n",
    "I have already done all the steps in the second \"from scratch\" option below and saved them as an `ami` that I have shared with you.\n",
    "\n",
    "create a new `ec2` instance with `postgres` pre-installed and configured by\n",
    "\n",
    "1. going to the `ec2` service\n",
    "1. clicking on `ami` in the left menu\n",
    "1. change \"Owned by me\" to \"Private Images\" in the dropdown next to the search bar\n",
    "1. you should see an `ami` with `ami-id = ami-0922deee48c98a016` and name `gu511-dinky-postgres`\n",
    "\n",
    "select that `ami` and click \"Launch\". the only thing you should have to configure is the `ssh` key.\n",
    "\n",
    "test that this all worked by\n",
    "\n",
    "1. log in via `ssh`\n",
    "    1. note: user name on this image is `ec2-user`\n",
    "    1. `ssh -i [path to your pem key here] ec2-user@[your ec2 public dns here]`\n",
    "1. start `postgres` service with `sudo service postgresql start`\n",
    "1. connect to that `postgres` service with `psql --user postgres`\n",
    "    1. quit by entering `\\q`\n",
    "    \n",
    "##### there is nothing to submit for this assignment"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.2: from scratch [ADVANCED]\n",
    "\n",
    "\n",
    "the goal of this exercise is to replicate what `rds` does for us: create an `ec2` instance and install the `postgres` service. let's start by creating that server."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.2.1: create the `ec2` server\n",
    "\n",
    "first, using the `aws` `ec2` service, create a new `ec2` instance that is as close as possible to the underlying `ec2` instance of the `postgres` database instance we created in the previous problem  (that is, it should have the following properties):\n",
    "    \n",
    "+ `ami`: Amazon Linux 2 AMI (HVM), SSD Volume Type 64 bit (free tier)\n",
    "+ instance type: `t2.micro`\n",
    "+ storage: set the type to SSD (`gp2`) and the size of the storage to 20 GB\n",
    "\n",
    "with that done, let's go about installing and configuring `postgres`. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.2.2: installing `postgres`\n",
    "\n",
    "once that server starts up, `ssh` into it (note: for the `amazon linux ami` the username is `ec2-user`). let's start by installing `postgres` with the following commands:\n",
    "\n",
    "```bash\n",
    "sudo yum install postgresql postgresql-server postgresql-devel postgresql-contrib postgresql-docs\n",
    "sudo postgresql-setup initdb\n",
    "sudo service postgresql start\n",
    "sudo vim /var/lib/pgsql/data/pg_hba.conf\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.2.3: configuring `postgres`\n",
    "\n",
    "oh fun -- remember `vim`?!\n",
    "\n",
    "we need to tell the `postgres` service to allow you to log in as the `postgres` user (which is technically considered bad practice, but let's do it for fun anyway). toward the bottom of the file you just opened in `vim` there is a pair of lines that look like the following:\n",
    "\n",
    "```conf\n",
    "# \"local\" is for Unix domain socket connections only\n",
    "local   all             all                                     peer\n",
    "```\n",
    "\n",
    "this config line is saying that\n",
    "\n",
    "+ for people logged on to your machine (`local`)\n",
    "+ for all databases (the first `all`)\n",
    "+ and for all postgres database service users (the second `all`)\n",
    "+ use `peer` authentication\n",
    "    + in `peer` authentication, the `postgres` service assumes that the local `linux` username and `postgres` user name are meant to be the same -- e.g. it would assume you want to log in as database user `ec2-user`\n",
    "\n",
    "we will loosen up those restrictions by changing the `peer` authentication method to `trust`, which allows anyone who has made it onto our `ec2` instance (`local`) to view any database as any user (`all` and `all`).\n",
    "\n",
    "```conf\n",
    "# \"local\" is for Unix domain socket connections only\n",
    "local   all             all                                     trust\n",
    "```\n",
    "\n",
    "edit this file to change `peer` to `trust`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.2.4: verifying our install\n",
    "\n",
    "once this edit has been made, you can finally start the server and log in to your own `postgres` database server:\n",
    "\n",
    "```bash\n",
    "sudo service postgresql restart\n",
    "\n",
    "# only one user exists right now: postgres\n",
    "psql -U postgres\n",
    "```\n",
    "\n",
    "the output of that should just be a `psql` prompt:\n",
    "\n",
    "```\n",
    "psql (9.2.24)\n",
    "Type \"help\" for help.\n",
    "\n",
    "postgres=# \n",
    "```\n",
    "\n",
    "you're good! feel free to exit that `psql` shell with `\\q`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.2.5: installing `psycopg2`\n",
    "\n",
    "one last thing: let's install `psycopg2`. execute the following:\n",
    "\n",
    "```bash\n",
    "cd /tmp\n",
    "wget https://repo.continuum.io/miniconda/Miniconda3-latest-Linux-x86_64.sh\n",
    "# answer \"yes\" to all questions\n",
    "bash Miniconda3-latest-Linux-x86_64.sh\n",
    "\n",
    "cd\n",
    "conda install psycopg2\n",
    "```\n",
    "\n",
    "##### there is nothing to submit for this assignment"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div style=\"border: 1px solid lightgrey;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# exercise 4: comparing bulk `csv` insert methods\n",
    "\n",
    "there are a few different ways to perform inserts of many records, and they have pretty drastically different performance. the fastest way to insert large quantities of data into a database is *usually* by utilizing a proprietary bulk insert command. for example, in\n",
    "\n",
    "+ `ms sql`: [a `BULK INSERT` command](https://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql)\n",
    "+ `mysql`: [the `LOAD DATA INFILE` command](https://dev.mysql.com/doc/refman/5.6/en/load-data.html)\n",
    "+ `oracle`: using a special oracle tool [`sql*loader`](https://docs.oracle.com/cd/B19306_01/server.102/b14215/ldr_concepts.htm) to write a \"control\" file (to outline the import steps) and import the datafile (this is what you pay hundreds of thousands of dollars for)\n",
    "\n",
    "`postgres` has implemented both a standard [`sql COPY` command](https://www.postgresql.org/docs/current/static/sql-copy.html) and a `psql` [`\\copy` meta command](https://www.postgresql.org/docs/9.2/static/app-psql.html#APP-PSQL-META-COMMANDS-COPY) for this task.\n",
    "\n",
    "the former (`sql COPY` command) can only be executed by users with appropriate permissions, and *must* be done with a local file. since we are using an unaccessbile `ec2` instance, we are out of luck on that count.\n",
    "\n",
    "the latter (`\\copy psql` command) can be executed by a non-admin user, and can be run remotely. the drawback: it is executed on the client side, so we have to send the entire inserted file over the wire.\n",
    "\n",
    "you now have two `postgres` databases at your disposal:\n",
    "\n",
    "1. your `rds` instance\n",
    "1. an `ec2` instance with `postgres` manually installed on it\n",
    "\n",
    "let's try out three different copy options to see how considerable the speed differences are. first, though, we need to do some setup."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.1: prepping\n",
    "\n",
    "you will be performing this exercise from the `ec2` server from the previous exercise (the one you created which has `postgres` running on it). `ssh` into that `ec2` server and take care of a few setup options."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.1.1: getting data\n",
    "\n",
    "download the bulk `csv` of `wmata` train position data which we intend to upload to our various `postgres` servers:\n",
    "\n",
    "```bash\n",
    "cd /tmp\n",
    "wget https://s3.amazonaws.com/shared.rzl.gu511.com/train_positions/train_positions.csv\n",
    "chmod a+r train_positions.csv\n",
    "cd ~\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.1.2: getting the `python` code\n",
    "\n",
    "I've written a short snippet of `python` code to\n",
    "\n",
    "1. mark the starting time\n",
    "1. create a connection to your `rds` database\n",
    "1. create a csv dictreader to read lines from `train_positions.csv`\n",
    "1. iterate through those csv lines and insert them in batches\n",
    "1. mark the ending time\n",
    "1. print the total time it took to insert the records to the screen\n",
    "\n",
    "download it to your `ec2` instance and review it:\n",
    "\n",
    "```bash\n",
    "cd ~\n",
    "wget https://s3.amazonaws.com/shared.rzl.gu511.com/train_positions/bulkinsert.py\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.1.3: creating an empty table on both dbs\n",
    "\n",
    "you can log into your `ec2` `postgres` server with\n",
    "\n",
    "```bash\n",
    "psql -U postgres\n",
    "```\n",
    "\n",
    "and you can log into your `rds` server with\n",
    "\n",
    "```bash\n",
    "psql --host YOUR_RDS_ENDPOINT --port YOUR_RDS_PORT --dbname YOUR_DB_NAME --user YOUR_MASTER_USER_NAME\n",
    "```\n",
    "\n",
    "log into each and execute the following `sql` code:\n",
    "\n",
    "```sql\n",
    "CREATE TABLE train_positions (   \n",
    "    carcount real                \n",
    "    , circuitid real             \n",
    "    , destinationstationcode text\n",
    "    , directionnum real          \n",
    "    , linecode text              \n",
    "    , secondsatlocation real     \n",
    "    , servicetype text           \n",
    "    , trainid text               \n",
    "    , timestamp timestamp        \n",
    ");                               \n",
    "```\n",
    "\n",
    "you should get\n",
    "\n",
    "```bash\n",
    "CREATE TABLE\n",
    "```\n",
    "\n",
    "as a result of that command on either server. anything else may be an error.\n",
    "\n",
    "create this empty table on both servers!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.2: inserting in batches via `python` (remote)\n",
    "\n",
    "the first way we will attempt to insert these records is to use the `psycopg2` library to `INSERT` records in batches of 100 at a time. \n",
    "\n",
    "assuming your working directory is the one in which you downloaded the file `bulkinsert.py` above, run the following from your `ec2` server's terminal:\n",
    "\n",
    "```bash\n",
    "python bulkinsert.py --host YOUR_RDS_ENDPOINT --port YOUR_RDS_PORT --dbname YOUR_DB_NAME --user YOUR_MASTER_USER_NAME --fcsv /tmp/train_positions.csv\n",
    "```\n",
    "\n",
    "for example, I successfully ran\n",
    "\n",
    "```bash\n",
    "python bulkinsert.py --host trainposdb.cdmknaubrmaw.us-east-1.rds.amazonaws.com --port 5432 --dbname postgres --user postgres --fcsv /tmp/train_positions.csv\n",
    "```\n",
    "\n",
    "note how long this process took (it will take minutes, not seconds or hours)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.3: `psql` `\\copy` command (remote)\n",
    "\n",
    "what the previous command did was something pretty common: given a `csv` of records that matches the schema of the table we created, load all of the records. as it happens, this is so common that most relational databases have implemented shortcuts for doing exactly that.\n",
    "\n",
    "one such shortcut is the `psql` command `\\copy`. let's use the `\\copy` command to copy this *local* `csv` file to our *remote* `rds` database.\n",
    "\n",
    "on your `ec2` server, open a `psql` shell connection to your `rds` instance:\n",
    "\n",
    "```bash\n",
    "psql --host YOUR_RDS_ENDPOINT --port YOUR_RDS_PORT --dbname YOUR_DB_NAME --user YOUR_MASTER_USER_NAME\n",
    "```\n",
    "\n",
    "once in, execute the following `psql` command to turn on query timing (this will measure how long a query takes for us):\n",
    "\n",
    "```sql\n",
    "\\timing\n",
    "```\n",
    "\n",
    "now, execute the `\\copy` command:\n",
    "\n",
    "```sql\n",
    "\\copy train_positions from /tmp/train_positions.csv with delimiter as ',' null as '' csv header;\n",
    "```\n",
    "\n",
    "note how long this process took.\n",
    "\n",
    "after this has been run, clean up after yourself again:\n",
    "\n",
    "```sql\n",
    "DELETE FROM train_positions;\n",
    "```\n",
    "\n",
    "and then close the `psql` shell session:\n",
    "\n",
    "```sql\n",
    "\\q\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.4: `sql` `COPY` command (local)\n",
    "\n",
    "finally, let's mimic the process of doing a bulk load from a *local* file to a *local* `postgres` server. as I wrote above, this requires a superuser (e.g. user `postgres`) and that the file is on the same computer as the database server. this is exactly the scenario we set up on our `ec2` server.\n",
    "\n",
    "open a `psql` shell connected to your *local* (to `ec2`) `postgres` server:\n",
    "\n",
    "```bash\n",
    "psql -U postgres\n",
    "```\n",
    "\n",
    "again, turn on timing:\n",
    "\n",
    "```sql\n",
    "\\timing\n",
    "```\n",
    "\n",
    "and finally, copy the file into the table `train_positions`\n",
    "\n",
    "```sql\n",
    "COPY train_positions\n",
    "FROM '/tmp/train_positions.csv'\n",
    "WITH (\n",
    "    FORMAT csv\n",
    "    , DELIMITER ','\n",
    "    , NULL ''\n",
    "    , HEADER TRUE\n",
    ");\n",
    "```\n",
    "\n",
    "note how long this process took.\n",
    "\n",
    "after this has been run, clean up after yourself again:\n",
    "\n",
    "```sql\n",
    "DELETE FROM train_positions;\n",
    "```\n",
    "\n",
    "and then close the `psql` shell session:\n",
    "\n",
    "```sql\n",
    "\\q\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.5: delivering you results\n",
    "\n",
    "put the three different processing times you found on this exercise into a `csv` called `bulk_insert_times.csv` which has the following format:\n",
    "\n",
    "| method                    | time_in_ms |\n",
    "|---------------------------|------------|\n",
    "| `psycopg2` batch `INSERT` |            | \n",
    "| `\\copy`                   |            |\n",
    "| `COPY`                    |            |\n",
    "\n",
    "please report all the times you received in `ms` (this is the unit given for `\\timing`, as well as the `python` script I wrote).\n",
    "\n",
    "\n",
    "##### upload `bulk_insert_times.csv` to the `s3` homework bucket *you* own (from a previous homework assignments)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div style=\"border: 1px solid lightgrey;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# exercise 5: terminate your `postgres` `ec2` instance\n",
    "\n",
    "via the `aws` `ec2` web console, terminate the `ec2` instance we created above for your `postgres` database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div style=\"border: 1px solid lightgrey;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# exercise 6: terminate your `rds` `postgres` instance\n",
    "\n",
    "via the `aws` `rds` web console, terminate the `rds` instance we created above for your `postgres` database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div style=\"border: 1px solid lightgrey;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# exercise 7: create a `redshift` cluster\n",
    "\n",
    "if you didn't do it as part of the `redshift` lecture, go back to the lecture and create your own `redshift` cluster\n",
    "\n",
    "***note***: if you are not free-tier eligible at this time, please reach out to me to talk about it"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div style=\"border: 1px solid lightgrey;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# exercise 8: wmata data: `create table` in `redshift`\n",
    "\n",
    "I've been downloading `wmata` metro train position information every 10 seconds since late 2016. at this point, I have... a *lot* of individual records of train locations. I have put 20,000,000 of those records into a `csv` and shared it via `s3`, and we're going to load them into a `redshift` instance and do some quick querying.\n",
    "\n",
    "in order to load this `csv` into `redshift` there must be a destination table to load it into. our first step must be to create that table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8.1: understanding the data\n",
    "\n",
    "the first ten records of this `csv` are:\n",
    "\n",
    "```csv\n",
    "carcount,circuitid,destinationstationcode,directionnum,linecode,secondsatlocation,servicetype,trainid,timestamp\n",
    "8,2009,F11,2,GR,42,Normal,067,2017-02-20 01:16:02.557021\n",
    "6,1912,F11,2,GR,0,Normal,175,2017-02-20 01:16:02.557021\n",
    "6,1480,D13,1,OR,0,Normal,182,2017-02-20 01:16:02.557021\n",
    "6,2786,D13,1,OR,0,Normal,234,2017-02-20 01:16:02.557021\n",
    "6,1522,D13,1,OR,48,Normal,272,2017-02-20 01:16:02.557021\n",
    "6,1384,D13,1,OR,5,Normal,342,2017-02-20 01:16:02.557021\n",
    "8,2890,D13,1,OR,0,Normal,390,2017-02-20 01:16:02.557021\n",
    "6,1710,K08,2,OR,615,Normal,061,2017-02-20 01:16:02.557021\n",
    "6,1288,K08,2,OR,5,Normal,160,2017-02-20 01:16:02.557021\n",
    "```\n",
    "\n",
    "you are welcome to download this information to investigate, but it is not necessary for this assignment. you can download it via\n",
    "\n",
    "```sh\n",
    "wget https://s3.amazonaws.com/shared.rzl.gu511.com/train_positions/train_positions.csv\n",
    "```\n",
    "\n",
    "typically our first questions when creating a new table would be\n",
    "\n",
    "1. what are the names of our columns?\n",
    "1. what are the data types of our columns?\n",
    "1. are any of them appropriate `distkey` or `sortkey` values? what is our distribution strategy?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 8.1.1: column names\n",
    "\n",
    "this one is simple: the last column name -- `timestamp` -- is a reserved keyword in `redshift` / `sql` (it is one of the data types). we could keep it as is, but then [we would need to escape it](https://docs.aws.amazon.com/redshift/latest/dg/r_pg_keywords.html) in queries (`select \"timestamp\", ...`). I'm lazy and don't want to do that.\n",
    "\n",
    "so when we create that column, let's rename it to `asoftime`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 8.1.2: data types\n",
    "\n",
    "the data types break down roughly as:\n",
    "\n",
    "+ `integer`: `carcount`, `circuitid`, `directionnum`, `secondsatlocation`, `trainid`\n",
    "+ `varchar(4)`: `desintationstationcode`, `linecode`\n",
    "+ `varchar(100)`: `servicetype`\n",
    "+ `asoftime`: `timestamp`\n",
    "\n",
    "*note: the numeric lengths of the `varchar` selections above are not particularly empirical; other values may have been more appropriate.*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 8.1.3: key choice and distribution strategy\n",
    "\n",
    "I'll cut to the chase: this batch of records doesn't have a good `distkey` candidate. \n",
    "\n",
    "a good `distkey` candidate would have *high cardinality* (many values) and relatively even distribution of the number of records per `distkey` value. having an imballance -- e.g. 75% of records having a value of 0 or `NULL` -- is called **skew** and will lead to certain slices of data and certain workers being much more taxed than others (worse than even distribution!).\n",
    "\n",
    "for `sortkey`, however, we do have a natural sort value -- our `asoftime` column.\n",
    "\n",
    "when you `create` this table, do not set a `distkey` but do set `asoftime` as the `sortkey`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8.2: the `create table` statement\n",
    "\n",
    "write a [`create table`](https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html) statement that will create a new table that\n",
    "\n",
    "1. is named `trainpositions`\n",
    "1. keeps the same column names as the `csv` except for `timestamp` which is renamed `asoftime`\n",
    "1. uses the provided data types\n",
    "1. has no `distkey` and has `asoftime` as a `sortkey`\n",
    "\n",
    "again, the column names and types should be:\n",
    "\n",
    "| name | type |\n",
    "|-|-|\n",
    "| `carcount` | `INTEGER` |\n",
    "| `circuitid` | `INTEGER` |\n",
    "| `destinationstationcode` | `VARCHAR(4)` |\n",
    "| `directionnum` | `INTEGER` |\n",
    "| `linecode` | `VARCHAR(4)` |\n",
    "| `secondsatlocation` | `INTEGER` |\n",
    "| `servicetype` | `VARCHAR(100)` |\n",
    "| `trainid` | `INTEGER` |\n",
    "| `asoftime` | `TIMESTAMP` |\n",
    "\n",
    "save that statement in a file called `create_trainpositions.sql`\n",
    "\n",
    "##### upload `create_trainpositions.sql` to your s3 homework submission bucket"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div style=\"border: 1px solid lightgrey;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# exercise 9: wmata data: `copy`\n",
    "\n",
    "now that we have created our `trainpositions` table, we must copy our `csv` data into it. at this stage, the questions shift over to the details about the format of the `csv` we wish to import and the permissions we will need to import it, and subsequently how we can configure those values or behaviors in our `copy` command (this is no different than reading a `csv` into `pandas` or an `R` dataframe).\n",
    "\n",
    "for now, there are a few things we must tackle:\n",
    "\n",
    "1. our `csv` file has a comma as a delimiter\n",
    "1. our `csv` file has a header row that you should ignore\n",
    "1. our `csv` is publically available on `s3` at `s3://shared.rzl.gu511.com/train_positions/train_positions.csv`\n",
    "    1. this means that in order to execute our `copy` command, `redshift` will need to know about credentials allowing it to access `s3`\n",
    "1. our `csv` is in the `us-east-1` region\n",
    "\n",
    "use [the `redshift` `copy` command documentation](https://docs.aws.amazon.com/redshift/latest/dg/r_COPY.html) to figure out how to specify the above configuration options.\n",
    "\n",
    "create a `copy` statement that will copy the `csv` into `trainpositions` and save it to a file named `copy_trainpositions.sql`\n",
    "\n",
    "##### upload `copy_trainpositions.sql` to your `s3` homework submission bucket"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div style=\"border: 1px solid lightgrey;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# exercise 10: `merge` two `branch`es with non-overlapping edits to the same file\n",
    "\n",
    "## 10.1: make a local update to `dspipeline.py`\n",
    "\n",
    "you, being astute and dilligent, notice that the `dspipeline.py` file we committed last week [has no `usage` instructions](https://gist.github.com/RZachLamberty/32f6d0ec0a69e951693eae50e686c2e3#file-dspipeline-py-L13-L15)\n",
    "\n",
    "```py\n",
    "Usage:\n",
    "    <usage>\n",
    "\n",
    "\"\"\"\n",
    "```\n",
    "\n",
    "and you decide to fill that in.\n",
    "\n",
    "update `dspipeline.py` so that it reads\n",
    "\n",
    "```py\n",
    "Usage:\n",
    "    import dspipeline\n",
    "    dspipeline.adult_data_demo()\n",
    "\n",
    "\"\"\"\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10.2: update `master`\n",
    "\n",
    "`add` this change, `commit` it with a message `dspipeline: adding usage instructions`, and `push` to `github`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10.3: fetch my new `branch`\n",
    "\n",
    "after pushing to `master` and checking on `github`, you notice that I have sneakily added my *own* updates to `dspipeline.py` as a new `branch` called `minortweak`.\n",
    "\n",
    "use `git fetch --all` to create a mirror repository of that `branch`.\n",
    "\n",
    "*note: this branch will be pushed on Saturday afternoon to make sure all users have had time to update their `github` repos from the previous assignment*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10.4: `merge` my changes in with yours\n",
    "\n",
    "use [`git merge`](https://git-scm.com/docs/git-merge) to `merge` the change that I made on the `minortweak` branch into `master`. if given the opportunity to edit a `commit` `message` go with the provided default value, and then `push` the updated `master` `branch` to `github`\n",
    "\n",
    "*hint: if you're not sure, read the docs above to figure out which branch you should have checked out and which branch name you should include in your `git merge` call*\n",
    "\n",
    "\n",
    "##### nothing to submit, we will see your commit on `github`"
   ]
  },
  {
   "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": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}