012_dbs_3_dynamodb.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "<a href=\"https://colab.research.google.com/github/rzl-ds/gu511/blob/master/012_dbs_3_dynamodb.ipynb\" target=\"_parent\">\n",
    "    <img src=\"https://colab.research.google.com/assets/colab-badge.svg\"/>\n",
    "</a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# `aws dynamodb`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "the previous two lecture topics were about the difference between relational databases and `nosql` databases, and an overview of one particular relational database (`postgres`) which can be implemented using the `aws rds` service.\n",
    "\n",
    "now, let's turn our attention to `nosql` options"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "`aws`'s most popular managed `nosql` service -- and the largest of the cloud database services overall in terms of adoption -- is  `dynamodb`.\n",
    "\n",
    "(with a name like `dynamo`, it must be *VERY* cool)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "`dynamodb` is both a key-value and a document store. it is primarily designed for very low latency applications (large-scale web projects, phone apps, *etc.*).\n",
    "\n",
    "like other `nosql` databases, it doesn't propose or mandate any schema, which is kind of the point: any file can come in at any time with arbitrary contents, and we don't want it to break our super l33t mobile game."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "in all honesty, while you may come across these databases in the data science wilderness, it is (in my experience, at least) *much* less common than relational databases.\n",
    "\n",
    "data scientists are usually working with structured data, so imposing a structure (i.e. a relation) makes sense. the use case for document stores generally has much more to do with software and data engineering than analysis."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "all that being said, it is still good to know about them -- you don't always get to determine what structure your data comes in, and if you're provided data in a `nosql` database such as this one (or, more commonly, `mongodb`), it'd be pretty great if *you* knew how to handle it.\n",
    "\n",
    "so let's figure that out!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## how is data arranged in `dynamodb`?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "hat tip to [the docs](http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Introduction.html) which we are following in our intro discussion"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### tables, items, and attributes\n",
    "\n",
    "the nomenclature and conceptual arrangement of items in `dynamodb` is not very different from that in a regular ol' relational database (even though implementation is very different). it has:\n",
    "\n",
    "+ **tables**: a high-level conceptual distinction (e.g. `Person` and `Company`)\n",
    "+ **items**: similar to records, these are individual instances of the concept described in the **table** (e.g. a \"Zach Lamberty\" item)\n",
    "+ **attributes**: similar to a single field in a record, these are units of information (under *no* schema) about one of the above **items** (e.g. `age`, `address`). these can be hierarchical"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "there are relationships between those three levels:\n",
    "\n",
    "+ **tables** are collections of **items**\n",
    "+ **items** are collections of **attributes**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "+ **primary keys**: every **item** must be uniquely identified by one or two of its attributes\n",
    "    + looking up a record by its **primary key** is *instantaneous* in `dynaomdb`. this is equivalent to a `python` dictionary lookup (the **primary key** is converted into a hash key)\n",
    "    + a single element **primary key** determines where a record is stored\n",
    "    + a second element in a **primary key** determines the order of records in that storage space"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### an example\n",
    "\n",
    "take the following records as a single `Person` table:\n",
    "\n",
    "```json\n",
    "[\n",
    "    {\n",
    "        \"PersonID\": \"chb49\",\n",
    "        \"LastName\": \"Blancarte\",\n",
    "        \"FirstName\": \"Carlos\"\n",
    "    },\n",
    "    {\n",
    "        \"PersonID\": \"rzl5\",\n",
    "        \"LastName\": \"Lamberty\",\n",
    "        \"FirstName\": \"Ron\",\n",
    "        \"MiddleName\": \"Zach\",\n",
    "        \"Title\": \"Adjunct\"\n",
    "    }\n",
    "    {\n",
    "        \"PersonID\": \"jks49\",\n",
    "        \"LastName\": \"Shaw\",\n",
    "        \"FirstName\": \"J\",\n",
    "        \"MiddleName\": \"Ken\",\n",
    "        \"Title\": \"Director of Graduate Studies\"\n",
    "    }\n",
    "]\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "in the above, the `PersonID` serves as a unique identifier **attribute** of each **item**. also, note that the schema is not consistent -- it doesn't have to be."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### indexes\n",
    "\n",
    "in analogy with relational databases, `dynamodb` also supports *indexes* on this data. you can think of indexes as effectively pre-caching the query results based on certain fields' values."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "in the above `Person` example, querying for records by the primary key (`PersonID`) is instantaneous. we can also create an *index* on `LastName` to make lookup based on last name much faster.\n",
    "\n",
    "faster is inherently better and no matter what we should always care that things are fast"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "just kidding.\n",
    "\n",
    "why does this actually matter? often times it won't. but you should know that when interacting with a `nosql` database (or a relational database, for that matter):\n",
    "\n",
    "*when doing queries, search on primary keys or indices will be fast(er), and search on other fields or columns will be slow(er). if query speed is a bottleneck in some process you are working on, consider discussing the possibility of creating an index with your dba*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### streams\n",
    "\n",
    "a particularly useful feature of `dynamodb` (compared to other `nosql` databases) is **streams**, which are effectively `aws event` notifications (such as those we used to trigger `lambda` functions, hint hint hint) about changes to the database."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "\"changes\" in this context refers to changes to **items** in **tables**:\n",
    "\n",
    "+ a new item is inserted\n",
    "+ an existing item is updated\n",
    "+ an existing item is removed"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "**<div align=\"center\">PAUSE FOR ZOOM BREAK</div>**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## how is data added to `dynamodb`?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "for most relational databases (and `postgres` in particular) we make a connection to the database with some program (e.g. `psql`) and execute `sql` statements within that session. \n",
    "\n",
    "under the hood, the relational database developers have created their own low-level protocol for allowing those connections and submitting those statements, and our `psql` program is really just sending messages in that protocol over the wire to the database."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "`dynamodb`, by comparison, is an inherently web-centric database, which means that *all* of the requests that are sent to and from the database are exclusively `http` messages -- *including* things like table creation, item insert or update, and item deletion."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "for a general service, `aws` will expose a `REST api`, will implement this in `boto3`, and will expose the most common actions via the `aws cli`.\n",
    "\n",
    "for `rds`, this meant we could create database *instances*, or inquire about their state (up and running, backing up, etc), but if we wanted to perform any database options we had to use the database tools.\n",
    "\n",
    "for `dynamodb`, there is no such separation between admin tools and database tools. the \"thing we do to ask about our database status\" (tm) is the same as the \"thing we do to insert a record\" (tm squared)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "this is all to say: records are added via `http` messages, related `boto3` statements, or `aws cli` commands"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "sidenote: [here is a really great presentation](https://wulczer.org/postgres-on-the-wire.pdf) about the `postgres` protocol. messages are transferred from client to server using TCP (just like `http` messages), but the internal structure is *very* different"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### an example: creating a table and inserting a record"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "the creation of a `dynamodb` is done via the [`CreateTable`](https://docs.aws.amazon.com/amazondynamodb/latest/APIReference/API_CreateTable.html) endpoint, or [the `boto3 dynamodb resource create_table` function](http://boto3.readthedocs.io/en/latest/reference/services/dynamodb.html#DynamoDB.ServiceResource.create_table).\n",
    "\n",
    "as with other `aws` services, all of this can be done via [the `dynamodb` web console](https://console.aws.amazon.com/dynamodb/home)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "the table creation command is *asynchronous*, which means that we will receive an *immediate* response to our request to create a table even though that request won't be *completed* by that point\n",
    "\n",
    "in order to add records to that we will need to poll the state of the database (with the `describe_table` function) before attempting to insert a new item. fortunately, `boto3` has taken care of that for us"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "**<div align=\"center\">mini exercise: create a simple `dynamodb` table and poll for creation</div>**\n",
    "\n",
    "1. think about how your `ec2` is authenticating (`gu511` profile? `iam` `ec2` server role?). make sure that method has the `AmazonDynamoDBFullAccess` policy in `iam`\n",
    "1. on that `ec2` instance, run the following `python` code to create a table `Person`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "import boto3, time\n",
    "\n",
    "# is this your session name??\n",
    "session = boto3.session.Session(profile_name='gu511')\n",
    "dynamodb = session.resource('dynamodb')\n",
    "\n",
    "# create the table\n",
    "person = dynamodb.create_table(\n",
    "    TableName=\"Person\",\n",
    "    AttributeDefinitions=[{'AttributeName': 'guid', \n",
    "                           'AttributeType': 'S'}],\n",
    "    KeySchema=[{'AttributeName': 'guid', \n",
    "                'KeyType': 'HASH'}],\n",
    "    ProvisionedThroughput={'ReadCapacityUnits': 1, 'WriteCapacityUnits': 1},\n",
    ")\n",
    "print('immediate status: {}'.format(person.table_status))\n",
    "\n",
    "# wait until it's up\n",
    "person.wait_until_exists()\n",
    "print('table exists!')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "we have successfully create a table using the `boto3` library! let's add some records to it.\n",
    "\n",
    "we could add any 1 record with the `person.put_item` method, but we could also create a batch writer (using a context manager) to take many items and write them in larger groups (\"batches\") of 25 or fewer records."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# it's up! insert some record\n",
    "items = [{\"guid\": \"chb49\",\n",
    "          \"LastName\": \"Blancarte\",\n",
    "          \"FirstName\": \"Carloa\",\n",
    "          \"Title\": \"TA\"},\n",
    "         {\"guid\": \"rzl5\",\n",
    "          \"LastName\": \"Lamberty\",\n",
    "          \"FirstName\": \"Ron\",\n",
    "          \"MiddleName\": \"Zach\",\n",
    "          \"Title\": \"Adjunct\"},\n",
    "         {\"guid\": \"jks49\",\n",
    "          \"LastName\": \"Shaw\",\n",
    "          \"FirstName\": \"J\",\n",
    "          \"MiddleName\": \"Ken\",\n",
    "          \"Title\": \"Director of Graduate Studies\",\n",
    "          \"Office\": \"327\"}]\n",
    "\n",
    "with person.batch_writer() as batch:\n",
    "    for item in items:\n",
    "        batch.put_item(Item=item)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "go check out those records in [the `dynamodb` web console](https://console.aws.amazon.com/dynamodb/home?region=us-east-1#tables:selected=Person)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### updating records\n",
    "\n",
    "once inserted, items are updated using the `UpdateItem` endpoint, which is availabe in `boto3` as a `DynamoDB.Table.update_item` method. we can use that to fix the annoying typo in Carlos' first name:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "person.update_item(\n",
    "    Key={'guid': 'chb49'},\n",
    "    UpdateExpression=\"SET FirstName = :fn\",\n",
    "    ExpressionAttributeValues={':fn': 'Carlos'}\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "of course, you can also manually edit any record via [the web console](https://console.aws.amazon.com/dynamodb/home?region=us-east-1#tables:selected=Person)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "**<div align=\"center\">PAUSE FOR ZOOM BREAK</div>**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## how do I get data out of `dynamodb`?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "as I said previously, for most traditional data science applications where *you* control data acquisition and persistence, it will generally not make sense to put data into a key-value or document store like `dynamodb`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "that being said, most of the data in the world wasn't persisted to a database *by* and *for* you, so other people made other decisions with other things in mind. maybe that awesome clickstream data you want to model just so happened to fit the use case of `dynamodb`, and the web developers went with what made sense."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "in other words: it's nice to know how to write and administer `dynamodb`, but it's better to know how to get data from it."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "there are three basic ways to get data (listed here by the `REST api` endpoint names):\n",
    "\n",
    "1. `GetItem`: pull a single item by its primary key\n",
    "2. `Query`: pull the set of items which all share a primary key, possibly with a condition on the sort key\n",
    "3. `Scan`: pull *all* items"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "the first two (`GetItem` and `Query`) are for fast access (i.e. the query knows the physical location of the record \"in the cloud\"), and they involve the primary key. `GetItem` uses an exact primary key, and `Query` requires a composite primary key (two values, not just one).\n",
    "\n",
    "the last (`Scan`) is a slow, full-table query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### `GetItem`\n",
    "\n",
    "the primary key (either a single partition key or a partition key with a sort key) is a unique identifier of an item in `dynamodb`. we can, of course, pull up that record by that key:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "person = dynamodb.Table('Person')\n",
    "response = person.get_item(Key={'guid': 'rzl5'})\n",
    "response['Item']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "this method is equivalent to the `sql` statement (which is nonsense, since this is `nosql`, right?!?)\n",
    "\n",
    "```sql\n",
    "select * from Person where guid = \"rzl5\"\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "often in `sql` statements such as the above we don't want to return every value -- we only want the first and last name, say.\n",
    "\n",
    "```sql\n",
    "select FirstName, LastName from Person where guid = \"rzl5\"\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "we can also limit the fields returned by this request with the `ProjectionExpression` parameter (a list of the fields we want returned)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "response = person.get_item(Key={'guid': 'rzl5'}, ProjectionExpression='FirstName,LastName')\n",
    "response['Item']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### `Query`\n",
    "\n",
    "for a table with only one element in its primary key (such as our `Person` example), the only meaningful way to access records with that key is via the `GetItem` endpoint.\n",
    "\n",
    "for tables with compound primary keys (a hash key and a sort key), there is a possible generalization called a `Query`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "suppose we didn't have a `guid` as a primary key in `Person`, but instead we had a primary key that was `(LastName, FirstName)` (and let's ignore the complication that this is definitely not a good idea since first and last name pairs are far from unique)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "our records would then be\n",
    "\n",
    "```json\n",
    "[\n",
    "    {\n",
    "        \"LastName\": \"Blancarte\",\n",
    "        \"FirstName\": \"Carlos\"\n",
    "    },\n",
    "    {\n",
    "        \"LastName\": \"Lamberty\",\n",
    "        \"FirstName\": \"Ron\",\n",
    "        \"MiddleName\": \"Zach\",\n",
    "        \"Title\": \"Adjunct\"\n",
    "    },\n",
    "    {\n",
    "        \"LastName\": \"Shaw\",\n",
    "        \"FirstName\": \"J\",\n",
    "        \"MiddleName\": \"Ken\",\n",
    "        \"Title\": \"Director of Graduate Studies\"\n",
    "    }\n",
    "]\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "also, now, suppose I had been entered incorrectly at first as just \"Zach Lamberty\". there might then be two records for me:\n",
    "\n",
    "```json\n",
    "[\n",
    "    {\n",
    "        \"LastName\": \"Lamberty\",\n",
    "        \"FirstName\": \"Ron\",\n",
    "        \"MiddleName\": \"Zach\",\n",
    "        \"Title\": \"Adjunct\"\n",
    "    },\n",
    "    {\n",
    "        \"LastName\": \"Lamberty\",\n",
    "        \"FirstName\": \"Zach\",\n",
    "        \"Title\": \"None\"\n",
    "    }\n",
    "]\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "we could use the previous `GetItem` record to find either *one* of these records, but we could use `Query` to find *both* of them. this requires what `aws` calls a `KeyConditionExpression`, a boolean query string *a la* the `WHERE` clauses in standard `sql`:\n",
    "\n",
    "```python\n",
    "response = person.query(\n",
    "    KeyConditionExpression='LastName = :ln',\n",
    "    ExpressionAttributeValues={':ln': 'Lamberty'}\n",
    ")\n",
    "response['Item']\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "the `KeyConditionExpression` abstraction allows for arbitrarily complex expressions to be described in the same way, but also assists (on the `dynamodb` side, not the developer side) in query validation."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "finally, it is possible to perform more advanced logic on other attributes using a `FilterExpression`. for example, finding all `Lamberty` records where the Title is `Adjunct`:\n",
    "\n",
    "```python\n",
    "response = person.query(\n",
    "    KeyConditionExpression='LastName = :ln',\n",
    "    FilterExpression='Title = :pos'\n",
    "    ExpressionAttributeValues={\n",
    "        ':ln': 'Lamberty',\n",
    "        ':pos': 'Adjunct'\n",
    "    }\n",
    ")\n",
    "response['Item']\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "one important note: this is all ***still*** isolated to items that have a shared primary key value. In the `Person` example, both `GetItem` and `Query` will be isolated to within one `guid`. in the modified `Person` example, they will both necessary be isolated to within one `Lastname`.\n",
    "\n",
    "to pull records that have *different* hash key values, you have to use the slower `Scan` endpoint"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### `Scan`\n",
    "\n",
    "sometimes your interest isn't limited to the specific fields in the primary key. for example, suppose I was interested in all of the items in `People` with a `Title` of `TA`. this specifically cuts across primary keys, so `GetItem` and `Query` are pointless to me."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "the `Scan` endpoint reads every record in the database and returns them (possibly filtered down based on a filter expression). this is analogous to the `sql` statement\n",
    "\n",
    "```sql\n",
    "select * from People\n",
    "/* optionally\n",
    "where some_condition = True\n",
    "*/\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "the `Scan` statement is striaghtforward"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "response = person.scan()\n",
    "response['Items']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "`Scan` operations are much more expensive (both in terms of resources and dollars) than `GetItem` or `Query` expressions. `aws` strongly recommends you avoid them, but it is sometimes unavoidable -- especially when the database was created for some *other* purpose (e.g. easy website traffic monitoring or construction keyed on specific users) and is being analyzed now to look at a property along a different dimension (e.g. traffic spikes as a function of time)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "let's scan a particularly special table (one I had up and running for a few days)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "airportweather = dynamodb.Table('AirportWeather')\n",
    "reports = airportweather.scan()\n",
    "print(reports['Count'])\n",
    "reports['Items'][0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "and look what `pandas` can do"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from pandas.io.json import json_normalize\n",
    "\n",
    "df = json_normalize(reports['Items'])\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### indexes\n",
    "\n",
    "at this point you would be right to be thinking these options are pretty limited. basically, you can *quickly* pull up a record or several records which all have one shared value for *an important* key, or you can slowly pull up all records which have a shared value or are in a preferred range for *the other* keys."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "**indexes** are a way of elevating \"other\" keys to \"important\" keys and open up faster query options without changing the fundamental structure (the primary key constraint) of the database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "under the hood, indexes are basically just lookup tables. for each record in an existing table (where the primary key is the important thing), we add a record to the index table where the *index keys* are the new primary keys and the attributes are that base table record's primary key."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### creating an index\n",
    "\n",
    "creating an index on `Title` through [the web interface](https://console.aws.amazon.com/dynamodb/home?region=us-east-1#tables:selected=Person) is pretty straight-forward (select table > index tab > create index > field is `Title`), and takes a minute or two.\n",
    "\n",
    "after we've done that let's also create one using `boto3`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "person.update(\n",
    "    AttributeDefinitions=[\n",
    "        {'AttributeName': 'LastName', 'AttributeType': 'S'},\n",
    "        {'AttributeName': 'FirstName', 'AttributeType': 'S'}\n",
    "    ],\n",
    "    GlobalSecondaryIndexUpdates=[\n",
    "        {\n",
    "            'Create': {\n",
    "                'IndexName': 'LastName-FirstName-index',\n",
    "                'KeySchema': [\n",
    "                    {'AttributeName': 'LastName', 'KeyType': 'HASH'},\n",
    "                    {'AttributeName': 'FirstName', 'KeyType': 'RANGE'}\n",
    "                ],\n",
    "                'Projection': {'ProjectionType': 'ALL'},\n",
    "                'ProvisionedThroughput': {\n",
    "                    'ReadCapacityUnits': 1,\n",
    "                    'WriteCapacityUnits': 1\n",
    "                }\n",
    "            }\n",
    "        }\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "note: `boto3` doesn't provide a nifty polling function (like `wait_until_exists`), so we have to implement our own. We can do this as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "outputs": [],
   "source": [
    "import time\n",
    "\n",
    "person.reload()\n",
    "waitseconds = 5\n",
    "while person.global_secondary_indexes[-1]['IndexStatus'] != 'ACTIVE':\n",
    "    time.sleep(waitseconds)\n",
    "    print('.', end='')\n",
    "    person.reload()\n",
    "print('done!')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### using an index\n",
    "\n",
    "the point of creating an index is to allow for fast searches (*i.e.* `Query` -- `GetItem` only applies to table primary keys) on non-primary-key attributes. Let's test that out by selecting all records with a `Title = \"TA\"`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "person.query(\n",
    "    IndexName='Title-index',\n",
    "    # we can include index elements now:\n",
    "    KeyConditionExpression='Title = :t',\n",
    "    ExpressionAttributeValues={':t': 'TA'}\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "*note*: indexes sound awesome and they can be pretty helpful, so you might ask: why not just create an index for every column? the answer is that they are expensive, both in terms of resources and dollars, but also in terms of performance (every new record you write to a table updates every index on that table as well)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### indexes and dbas in the real world \n",
    "\n",
    "there is a very particular set of circumstances that would make this index discussion relevant to you and a project on which you might work:\n",
    "\n",
    "+ your source data is `dynamodb`\n",
    "    + generally, though, the following also applies to the analogous concepts in `rdbm`s\n",
    "+ the existing primary keys and indices do not cover the most important query keys of your modelling approach\n",
    "    + *e.g.* data is keyed on user and sorted on time and you are interested in `ip` address (no index)\n",
    "+ your queries and ingestion process is unacceptably slow\n",
    "    + this is often overlooked: just because your process is slower than it could be, that doesn't mean it's too slow!\n",
    "    + it has to be unacceptable to your client / project, not just to you ;)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "if your scenario matches these criteria, it's time to work with the `dba` to add an index. depending on the size of the data, this could be a pretty sizeable ask, so be prepared for some pushback"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## authentication"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "finally, `dynamodb` has a slightly different authentication method than `rds`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "`rds`, if you recall, was effectively just `rdbm`s installed on `aws`-managed `ec2` instances. authentication and access to those databases was managed in two ways\n",
    "\n",
    "1. network level: controlling connections to that `ec2` instance\n",
    "2. database level: creating users on the database and granting them permissions\n",
    "\n",
    "they recently added a bit of a hack, where you can *use* `iam` profiles to *create dynamic* database-level permissions. you still authenticate with a `postgres` server somewhere, however"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "`dynamodb`, being a fully integrated service, controls permissions in the same way as other `aws` services: via `iam`. there are a few basic ways to give a user or program access to your `dynamodb` tables:\n",
    "\n",
    "+ `iam` users\n",
    "    + create an `iam` user and assign that user specific permissions, then use access keys for programmatic access\n",
    "    + this is what I did for you all to access my [`AirportWeather` table](https://console.aws.amazon.com/dynamodb/home?region=us-east-1#tables:selected=AirportWeather)\n",
    "+ `iam` roles\n",
    "    + federated user access\n",
    "    + `aws` service access\n",
    "    + `ec2` application access"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "note: to clean up after yourself here, simply run:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "outputs": [],
   "source": [
    "person.delete()\n",
    "person.wait_until_not_exists()\n",
    "print('\"Person\" table has been deleted')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "note: if you are looking for further information, you should check out the [`dynamodb` and `python` \"getting started\" documentation](http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/GettingStarted.Python.01.html)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "***<div align=\"center\">this joke is not an endorsement of resume fraud</div>***\n",
    "<div align=\"center\"><img src=\"http://cerinman.github.io/images/nosqlfunny.png\" width=\"1500px\"></div>\n",
    "\n",
    "# END OF LECTURE\n",
    "\n",
    "next lecture: [`neo4j`](012_dbs_4_neo4j.ipynb)"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "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
}