2017_11_20.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exercises due by EOD 2017.11.20 (*a Monday*)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## goal"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "in this homework assignment we will focus on interacting with relational databases we construct using `aws` `rds`, and `nosql` databases we create using `aws` `dynamodb`."
   ]
  },
  {
   "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 via upload to two your own person `s3` homework submission bucket (forget the one we created last week, go back to uploading files to your own bucket).to repeat: use the same homework bucket you had been using for several weeks.\n",
    "\n",
    "summary:\n",
    "\n",
    "| exercise | deliverable                                         | method of delivery                 |\n",
    "|----------|-----------------------------------------------------|------------------------------------|\n",
    "| 1        | `python` file `dbconnections.py`                    | upload to you `s3` homework bucket |\n",
    "| 4        | a csv of timing information `bulk_insert_times.csv` | upload to you `s3` homework bucket |\n",
    "| 6        | `python` file `csvtodynamodb.py`                    | upload to you `s3` homework bucket |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# exercise 1: `boto3` and the `rds` service\n",
    "\n",
    "remember: the `rds` service is a totally separate concept from the database that is implemented with `rds`. think of it as the chef: it collects the ingredients (one of which is `postgres`) and bakes you a `postgres` cake. we're going to talk to the chef right now instead of the cake.\n",
    "\n",
    "download the skeleton `python` script here: https://s3.amazonaws.com/shared.rzl.gu511.com/dbconnections.py\n",
    "\n",
    "fill in the missing pieces to construct a function which can take an `rds` database id (e.g. `rzl-gu511-shared`) and create a `psycopg2` or `sqlalchemy` connection object from just that information.\n",
    "\n",
    "you can test this file by running the following (the part in brackets is optional) in an environment with `psycopg2` and `sqlalchemy`installed.\n",
    "\n",
    "```bash\n",
    "python dbconnections.py --dbid YOUR_RDS_DB_ID [--profile_name YOU_AWS_CONFIGURE_PROFILE]\n",
    "```\n",
    "\n",
    "we will test your result by running your file with our `dbid` and `profile name`\n",
    "\n",
    "\n",
    "##### upload your modified `dbconnections.py` file to the `s3` homework bucket *you* own (from several previous homework assignments)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# exercise 2: creating an `rds` postgres instance\n",
    "\n",
    "create your own `postgres` `rds` instance using the `aws` `rds` service with the following properties (note: on the \"Select Engine\" screen, if you click the \"Only enable options elegible for RDS free usage tier\", most of these options will be filled in for you):\n",
    "\n",
    "1. engine: `postgresql`\n",
    "2. engine version: `PostgreSQL 9.6.3-R1`\n",
    "3. instance class: `db.t2.micro`\n",
    "4. storage type: SSD\n",
    "5. storage: 20 GB\n",
    "\n",
    "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": [
    "# exercise 3: installing `postgres` once for yourself\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.\n",
    "\n",
    "\n",
    "## 3.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 ami 64 bit (free tier)\n",
    "+ instance type: `t2.micro`\n",
    "+ storage: set the type to SSD and the size of the storage to 20 GB\n",
    "\n",
    "with that done, let's go about installing and configuring `postgres`. \n",
    "\n",
    "## 3.2: installing `postgres`\n",
    "\n",
    "once that server starts up, `ssh` into it. let's start by installing postgres with the following:\n",
    "\n",
    "```bash\n",
    "sudo yum install postgresql postgresql-server postgresql-devel postgresql-contrib postgresql-docs\n",
    "sudo service postgresql initdb\n",
    "sudo vim /var/lib/pgsql9/data/pg_hba.conf\n",
    "```\n",
    "\n",
    "## 3.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 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",
    "these are saying that for people logged on to your machine (`local`), for all databases (the first `all`) and for all database usernames (the second `all`), use `peer` authentication (that is, get the user's name according to the operating system (so for us, `ec2-user`) and attempt to log them in with that name).\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",
    "## 3.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 start\n",
    "\n",
    "# only one user exists right now: postgres\n",
    "psql -U postgres\n",
    "```\n",
    "\n",
    "you're good! feel free to exit that `psql` shell with `\\q`\n",
    "\n",
    "## 3.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",
    "bash Miniconda3-latest-Linux-x86_64.sh\n",
    "\n",
    "cd\n",
    "source ~/.bashrc\n",
    "conda install psycopg2\n",
    "```\n",
    "\n",
    "##### there is nothing to submit for this assignment"
   ]
  },
  {
   "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",
    "let's try out a few different options to see how considerable the speed differences are. first, though, we need to do some setup.\n",
    "\n",
    "\n",
    "## 4.1: prepping\n",
    "\n",
    "we will be performing this exercise from the `ec2` server we created above (and on which we installed `postgres`). `ssh` into that `ec2` server and take care of a few setup options.\n",
    "\n",
    "\n",
    "### 4.1.1: getting data\n",
    "\n",
    "download the bulk `csv` 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",
    "```\n",
    "\n",
    "### 4.1.2: getting the `python` code\n",
    "\n",
    "I've written a short snippet of `python` code to\n",
    "\n",
    "0. mark the starting time\n",
    "1. create a connection to your `rds` database\n",
    "2. create a csv dictreader to read lines from `train_positions.csv`\n",
    "3. iterate through those csv lines and insert each of them one at a time\n",
    "    1. using a parameterized sql insert statement\n",
    "4. mark the ending time\n",
    "5. 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",
    "```\n",
    "\n",
    "\n",
    "### 4.1.3: creating an empty table\n",
    "\n",
    "you can log into your local `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!\n",
    "\n",
    "\n",
    "\n",
    "## 4.2: inserting in batches via `python`\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",
    "note how long this process took.\n",
    "\n",
    "\n",
    "## 4.3: `psql` `\\copy` command\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",
    "```\n",
    "\n",
    "\n",
    "## 4.4: `sql` `COPY` command\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",
    "```\n",
    "\n",
    "\n",
    "## 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 several previous homework assignments)"
   ]
  },
  {
   "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": [
    "# exercise 6: using `boto3` to add documents to `dynamodb`\n",
    "\n",
    "I've been downloading `wmata` metro train position information every 10 seconds since late 2016. at this point, I have about 210,000,000 individual records of train locations. I have collected 5,000,000 of those records and we're going to load them into a `dynamodb` instance and do some quick querying!\n",
    "\n",
    "\n",
    "## 6.1: getting data\n",
    "\n",
    "download the bulk `csv` of `wmata` train position data which we intend to upload to our `dynamodb` instance:\n",
    "\n",
    "```bash\n",
    "cd /tmp\n",
    "wget https://s3.amazonaws.com/shared.rzl.gu511.com/train_positions/train_positions.csv\n",
    "```\n",
    "\n",
    "## 6.2: converting `csv` records into `json` documents\n",
    "\n",
    "a single row in a `csv` can easily be converted into a `json` document: simply treat each record as a single `json` objects with keys being column headers and values being the associated record value. for example, a `csv` like\n",
    "\n",
    "| a | b |\n",
    "|---|---|\n",
    "| 0 | 1 |\n",
    "| 1 | 2 |\n",
    "| 2 | 4 |\n",
    "| 3 | 8 |\n",
    "\n",
    "can be converted into a list of `json` objects\n",
    "\n",
    "```json\n",
    "[\n",
    "    {'a': 0, 'b': 1},\n",
    "    {'a': 1, 'b': 2},\n",
    "    {'a': 2, 'b': 4},\n",
    "    {'a': 3, 'b': 8},\n",
    "]\n",
    "```\n",
    "\n",
    "the `python` `csv.DictReader` class is actually perfrect for this -- that's exactly how it reads `csv` files.\n",
    "\n",
    "we can create a generator from any `csv` file using the following 5 lines of code:\n",
    "\n",
    "```python\n",
    "import csv\n",
    "\n",
    "def csv_to_json(fcsv):\n",
    "    with open('fcsv', 'r') as f:\n",
    "        for record in csv.DictReader(f):\n",
    "            yield record\n",
    "```\n",
    "\n",
    "however, we have to do one more thing: `dynamodb` does not accept null values in `items`, so we should not include the keys in `record` whose values are null:\n",
    "\n",
    "```python\n",
    "def csv_to_json(fcsv):\n",
    "    with open('fcsv', 'r') as f:\n",
    "        for record in csv.DictReader(f):\n",
    "            yield {k: v for (k, v) in record.items() if v}\n",
    "\n",
    "```\n",
    "\n",
    "this is all overkill -- anywhere we could use this we could also just use `csv.DictReader` in-line.\n",
    "\n",
    "\n",
    "## 6.3: creating a `dynamodb` table\n",
    "\n",
    "for the purposes of this exercise, let's just assume that the best choice of hash key is `trainid` (it's not a bad choice: there are many values and it's well distributed), and the best choice of sort key is `timestamp`. for now, leave them both as strings (again, just to keep it simple -- best practice would be to convert them). \n",
    "\n",
    "the result would be individual trains ordered by snapshots in time. fun fact -- those trainids are not consistent from one run to the next, so this doesn't actually *mean* anything, it's just for fun.\n",
    "\n",
    "create a new `dynamodb` table called `TrainPositions` with hash and sort key as described above.\n",
    "\n",
    "\n",
    "## 6.4: filling in the details\n",
    "\n",
    "download the `python` skeleton here: https://s3.amazonaws.com/shared.rzl.gu511.com/csvtodynamodb.py\n",
    "\n",
    "you know the drill -- fill in the `FILL ME IN` sections. the end result should be a function `upload` which can push a `csv` into any `dynamodb` table (provided that the primary key columns for the `dynamodb` table are headers in the `csv` file).\n",
    "\n",
    "\n",
    "## 6.5: upload the data\n",
    "\n",
    "you can test your file by running the `upload` function, or invoking the entire `python` script from a `bash` command line:\n",
    "\n",
    "```bash\n",
    "python csvtodynamodb.py --fcsv CSV_FILE_PATH --tablename YOUR_DYNAMODB_TABLE_NAME [--profile YOUR_AWS_CONFIG_PROFILE]\n",
    "```\n",
    "\n",
    "prove your script works by uploading some records from `train_positions.csv` into `dynamodb`. verify through the web console that records are being added.\n",
    "\n",
    "*note*: the default provisioned write capacity of 5 units will allow you to write 5 KB per second. our file is 1 GB (1,000,000 KB) so a rough calculation of how long this would take is 200,000 seconds, or 2.3 days. I don't think we're going to write everything that way. feel free to write some number of records and then simply kill the `python` session that is running it `ctrl + c`.\n",
    "\n",
    "\n",
    "## 6.6: clean up\n",
    "\n",
    "you can keep the records in that `dynamodb` table if you want, or you can delete the `TrainPositions` table -- it's completely up to you\n",
    "\n",
    "\n",
    "##### upload your modified version of `csvtodynamodb.py` to your `s3` homework submission bucket"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}