012_dbs_1.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_1.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": [
    "# databases and `aws`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "what if you want to do something with data beyond just dumping it into an `s3` bucket?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "`aws` has [several database options](https://aws.amazon.com/free/?all-free-tier.sort-by=item.additionalFields.SortRank&all-free-tier.sort-order=asc&awsf.Free%20Tier%20Categories=categories%23databases), but we're going to talk* about a few in particular:\n",
    "\n",
    "1. `rds` (Relational Database Service): several common `sql rdbms` running on managed `ec2` servers\n",
    "1. `dynamodb`: an `aws`-specific `nosql` database service\n",
    "1. `redshift`: a distributed columnar database\n",
    "\n",
    "\\* this year, only `rds` and `redshift` will be covered in lectures. lecture materials contain one chapter on `dynamodb`, but due to time constraints and feedback from student interest surveys, we will not be including it in this year's materials"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "there are also several database options that `aws` doesn't support natively, but which you can quite easily build and use without them rolling it for you. again, due to time and interest, we won't be officially covering one of them (graph database `neo4j`), but you are welcome to look into the lecture chapter on that topic if you are so interested."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "lastly, there are *so many types and implementations of databases*. **so many!**. check out [this site](https://db-engines.com/en/ranking) for a somewhat scientific ranking of the popularity of various database approaches in the world today"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## understanding `sql`, `rdbms`, and `nosql`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "from about 2010 to 2016 or so every blogger on the internet wrote an article explaining the difference between `nosql` and `sql` (or specifically: `rdbms`). I won't even pretend to be able to do a better job than them. Here are a few worth reading:\n",
    "\n",
    "1. [the wikipedia `nosql` entry](https://en.wikipedia.org/wiki/NoSQL)\n",
    "2. [a good blog post motivating why one might choose `nosql`](https://www.upwork.com/hiring/data/sql-vs-nosql-databases-whats-the-difference/)\n",
    "3. [a random stack exchange link that 61 people on the internet like](https://dba.stackexchange.com/questions/5/what-are-the-differences-between-nosql-and-a-traditional-rdbms)\n",
    "4. [another good blog post focusing on the differences between the two](http://www.thegeekstuff.com/2014/01/sql-vs-nosql-db/?utm_source=tuicool)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "what follows is about as high-level an introduction to these topics as I can give. If you'd like to know more, you have a lifetime to \\~\\*\\~LeArN\\~\\*\\~!!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### the basics: `sql`\n",
    "\n",
    "`sql` (Structured Query Language) has existed since the 1970s, been an ANSI ([American National Standards Institute](https://en.wikipedia.org/wiki/American_National_Standards_Institute)) standard since 1986, and an ISO ([International Organization for Standardization](https://en.wikipedia.org/wiki/International_Organization_for_Standardization)) standard since 1987.\n",
    "\n",
    "show of hands: who here is older than ANSI-standard `sql`?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "as the name implies, `sql` is a generally-agreed-upon `l`anguage for making `s`tructured `q`ueries of databases. **if** you have stored your data in a particular way (discussed below), **then** it is *theoretically* possible to use this language to query that dataset.\n",
    "\n",
    "(theory becomes practice when someone writes code to interpret `sql` and actually implement those query actions, but we can take that for granted, generally)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "you've learned `sql` in 510 and almost certainly used it in data applications. it's everywhere.\n",
    "\n",
    "people create perverse monstrosities within real programming language to allow people to write queries in `sql` instead (I'm looking at you, [sqldf](https://cran.r-project.org/web/packages/sqldf/index.html) and [pandassql](https://github.com/yhat/pandasql). you should be ashamed of yourselves)\n",
    "\n",
    "you cannot escape it."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "which is a good thing, I suppose -- having one universally accepted standard for how you interact with large, normalized, relational data means that you only need to learn it once, and large user communities mean that the standards will be both rigorous and evolving."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### the basics: `rdbms`\n",
    "\n",
    "it is somewhat common for people to *say* `sql` but actually *talk* about `rdbm`s ([Relational DataBase Management Systems](https://en.wikipedia.org/wiki/Relational_database_management_system)). as the `l` in `sql` implies, `sql` is a *language*. it would exist if no database had ever been built."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "but databases have been built, and most of them have been built under the same paradigm:\n",
    "\n",
    "1. tables\n",
    "2. records\n",
    "3. links between records (relationships, *not* relations)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "*tables* encapsulate single objects or concepts.\n",
    "\n",
    "for example, a `Person` table\n",
    "\n",
    "| person_id | person_name |\n",
    "|-----------|-------------|\n",
    "| 1         | zach        |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "an `Employer` table\n",
    "\n",
    "| empl_id | empl_name   |\n",
    "|---------|-------------|\n",
    "| 1       | storyblocks |\n",
    "| 2       | georgetown  |\n",
    "| 3       | eri         |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "and an `EmploymentHistory` table\n",
    "\n",
    "| empl_hist_id | person_id | empl_id | start_date | end_date   |\n",
    "|--------------|-----------|---------|------------|------------|\n",
    "| 1            | 1         | 1       | 2019-02-25 | `null`     |\n",
    "| 2            | 1         | 2       | 2017-08-01 | `null`     |\n",
    "| 3            | 1         | 3       | 2016-01-10 | 2019-02-17 |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "*records* (rows in those tables) are single instances of that table object\n",
    "\n",
    "my personal information might exist as a row in the `Person` table, the properties of Storyblocks might exist in the `Employer` table, and my start and (`null`) end date might exist in the `EmploymentHistory` table\n",
    "\n",
    "| empl_id | empl_name   |\n",
    "|---------|-------------|\n",
    "| 1       | storyblocks |\n",
    "| <div style=\"color:red\">2</div>| <div style=\"color:red\">georgetown</div>  |\n",
    "| 3       | eri         |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "*relationships* exist to tie records in one table to records in another\n",
    "\n",
    "for example, I may have *foreign key* in the `EmploymentHistory` table pointing to records in the `Person` and `Employer` tables\n",
    "\n",
    "| <div style=\"color:red\">empl_id</div> | <div style=\"color:red\">person_id</div> | empl_hist_id | start_date | end_date   |\n",
    "|--------------|-----------|---------|------------|------------|\n",
    "| <div style=\"color:red\">1</div> | <div style=\"color:red\">1</div> | 1 | 2019-02-25 | `null`     |\n",
    "| <div style=\"color:red\">2</div> | <div style=\"color:red\">1</div> | 2 | 2017-08-01 | `null`     |\n",
    "| <div style=\"color:red\">3</div> | <div style=\"color:red\">1</div> | 3 | 2016-01-10 | 2019-02-17 |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "technically speaking, a ***relation*** is the table schema with all the records together (so, a table).\n",
    "\n",
    "***note: a `relation` is not a `relationship`!!***"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "the `sql` query *language* is used to manipulate or query those tables, but the infrastructure which maintains those tables, records, and relationships (*i.e.* manages the relations in that database) is an `rdbms`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "[here is a ranking of popularity of various `rdbms` systems](https://db-engines.com/en/ranking/relational+dbms). some of the most important to know are:\n",
    "\n",
    "+ `oracle` (1, 1)\n",
    "+ `mysql` (2, 2)\n",
    "+ `mssql` (3, 3)\n",
    "+ `psql` (4, 4)\n",
    "+ `db2` (5, 6)\n",
    "+ `mariadb` (8, 12)\n",
    "+ `hive` (10, 15)\n",
    "+ google's `bigquery` (15, 24)\n",
    "+ `aws`'s `redshift` (16, 29)\n",
    "\n",
    "\\* here the first number is the rank within `rdbms`, the second is overall among all database engines"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "**<div align=\"center\">PAUSE FOR ZOOM BREAK</div>**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### a wrinkle: relational `columnar` databases\n",
    "\n",
    "when we talked about **relations** above, we described them as basically being tables (schema-defined records of information in tabular format). stealing an example from [the column-oriented `dbms` wikipedia page](https://en.wikipedia.org/wiki/Column-oriented_DBMS), assume we have a table `employees`:\n",
    "\n",
    "| EmpId | Lastname | Firstname | Salary |\n",
    "|-|-|-|-|\n",
    "| 10 | Smith | Joe | 40000 |\n",
    "| 12 | Jones | Mary | 50000 |\n",
    "| 11 | Johnson | Cathy | 44000 |\n",
    "| 22 | Jones | Bob | 55000 |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "if you imagine what a table looks like in plain text, you're probably thinking of something that looks like what I just showed you -- kind of like a `csv` file.\n",
    "\n",
    "under the hood, an `rdbms` will create new rows in your tables by writing them to some file, more or less as a single line that contains one record and all the values for each field in the table for that record:\n",
    "\n",
    "```\n",
    "10,Smith,Joe,40000\n",
    "12,Jones,Mary,50000\n",
    "11,Johnson,Cathy,44000\n",
    "22,Jones,Bob,55000\n",
    "```\n",
    "\n",
    "that is, you are probably imagining / assuming that the \"thing\" written in each line of that file is a **row** of data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "in this structure a *column* is one component of those records, and a *row* is a single record\n",
    "\n",
    "```\n",
    "10,Smith,Joe,40000\n",
    "12,Jones,Mary,50000\n",
    "11,Johnson,Cathy,44000\n",
    "22,Jones,Bob,55000\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "that `csv`-esque view of our data could also have been transposed and it would still contain the same information:\n",
    "\n",
    "```\n",
    "10,12,11,22\n",
    "Smith,Jones,Johnson,Jones\n",
    "Joe,Mary,Cathy,Bob\n",
    "40000,50000,44000,55000\n",
    "```\n",
    "\n",
    "in this formulation, a *column* is a single record, and a *row* is one component"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "for similar numbers of columns and rows this might not make much of a difference, but it makes a huge difference when you have many, many rows. remember that the above look, to a computer, like one long string:\n",
    "\n",
    "```\n",
    "A,B,C,D|A,B,C,D|A,B,C,D|A,B,C,D|A,B,C,D|A,B,C,D|A,B,C,D|A,B,C,D|A,B,C,D|A,B,C,D|...\n",
    "```\n",
    "\n",
    "and the other\n",
    "\n",
    "```\n",
    "A,A,A,A,...|B,B,B,B,...|C,C,C,C,...|D,D,D,D,...|\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "so, who cares?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "well, the way that the database chooses to save that information on the disk is actually super important for performance and storage size reasons."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "##### you be the `dbms`: example query 1\n",
    "\n",
    "to understand the performance implications, let's pretend we are a `dbms` executing queries.\n",
    "\n",
    "imagine I asked you to give me all the fields for a person with `empid = 11`. how would you write that query in `sql`?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "```sql\n",
    "select * from employees where empid = 11\n",
    "```\n",
    "\n",
    "which of the two file formats would you rather use to do that? remember: you're a computer, you read this as one long line of text\n",
    "\n",
    "```\n",
    "10,Smith,Joe,40000|12,Jones,Mary,50000|11,Johnson,Cathy,44000|22,Jones,Bob,55000|\n",
    "```\n",
    "\n",
    "or\n",
    "\n",
    "```\n",
    "10,12,11,22|Smith,Jones,Johnson,Jones|Joe,Mary,Cathy,Bob|40000,50000,44000,55000|\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "let me \"think computer\" out loud here for a moment. I'm asked\n",
    "\n",
    "```sql\n",
    "select * from employees where empid = 11\n",
    "```\n",
    "\n",
    "if I see\n",
    "\n",
    "```\n",
    "10,Smith,Joe,40000|12,Jones,Mary,50000|11,Johnson,Cathy,44000|22,Jones,Bob,55000|\n",
    "```\n",
    "\n",
    "I would: scan through the records until I find one that starts with `11,`, then return that entire record. **easy**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "if instead I saw\n",
    "\n",
    "```\n",
    "10,12,11,22|Smith,Jones,Johnson,Jones|Joe,Mary,Cathy,Bob|40000,50000,44000,55000|\n",
    "```\n",
    "\n",
    "I would: scan the employee id piece to find which index corresponds to 11:\n",
    "\n",
    "```\n",
    "index:  1  2  3  4\n",
    "empid: 10 12 11 12\n",
    "```\n",
    "\n",
    "and then get the 3rd element of each other chunk of records\n",
    "\n",
    "```\n",
    "    index: 1     2     3       4\n",
    " lastname: Smith Jones Johnson Jones\n",
    "firstname: Joe   Mary  Cathy   Bob\n",
    "   salary: 40000 50000 44000   55000\n",
    "```\n",
    "\n",
    "that... didn't like that."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "winner: **row-based storage**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "##### you be the `dbms`: example query 2\n",
    "\n",
    "alternatively, suppose I asked you to calculate the total value of the salaries for all of my employees, or to give me *just* the last names. what `sql` code would you write? and which storage method would make that easier?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "```sql\n",
    "select sum(Salary) as total_salary from employees\n",
    "select Lastname from employees\n",
    "```\n",
    "\n",
    "which of the two file formats would you rather use?\n",
    "\n",
    "```\n",
    "10,Smith,Joe,40000|12,Jones,Mary,50000|11,Johnson,Cathy,44000|22,Jones,Bob,55000|\n",
    "```\n",
    "\n",
    "or\n",
    "\n",
    "```\n",
    "10,12,11,22|Smith,Jones,Johnson,Jones|Joe,Mary,Cathy,Bob|40000,50000,44000,55000|\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "winner: **column-based storage**\n",
    "\n",
    "hopefully this pair of examples has helped develop a bit of an intuition for the performance implications of different types of querying use cases under row-based or column-based storage."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "##### you be the `dbms`: adding records\n",
    "\n",
    "the above focused on reading (retrieval), but the storage-based implications are similar. again imagine you are the database. imagine I ask you to do the following:\n",
    "\n",
    "1. update Mary Jones' salary\n",
    "1. insert a new employee with `EmpId == 002` and shift all the other employee ids forward\n",
    "1. insert a new employee at the end of the `RowID` queue (an \"append\")\n",
    "1. insert a new column of \"middle name\" for all records\n",
    "\n",
    "for each of the above, which file-based arrangement make it easier to write those changes?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "one last note: columnar databases allow for special compression options (because each element in the row is of the same data type). this can lead to significant improvements in storage footprint and speed on top of the ones that come naturally from data being laid out in the way our queries prefer"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "in the end:\n",
    "\n",
    "+ use **row**-based storage when you are doing **transactional** things: inserting, writing, and updating. having the ability to quickly access all fields of a single record is important\n",
    "+ use **column**-based storage when you are doing **analytical** things: aggregating all the values in a handful of columns, or only looking at a small subset of columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "some popular **relational columnar** databases (ranks among *all* database options, not just `rdbms`):\n",
    "\n",
    "+ `sap hana` (19)\n",
    "+ `google` `bigquery` (24)\n",
    "+ `aws` `redshift` (29)\n",
    "+ `vertica` (31)\n",
    "+ `greenplum` (39)\n",
    "+ `monetdb` (126)\n",
    "+ `extremedb` (224)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### `acid`ity\n",
    "\n",
    "one last note on database management and `rdbms`: you will occasionally hear reference to \"ACID\" or \"ACID\"ity. this is an acronym which describes the best-practice principles of database *transactions* which allow multiple different users to share a single representation of data and not break everything.\n",
    "\n",
    "think of these as the rules that any `rdbms` must follow in order to make sure that `sql` works for the data that `rdbms` is managing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "those properties are:\n",
    "\n",
    "1. atomicity: every transaction is atomic, i.e. \"all or nothing\". no partial commits are allowed. if one part fails, it *all* fails (it is \"rolled back\")\n",
    "2. consistency: every transaction must take the database from one valid state to another valid state\n",
    "3. isolation: every transaction should be \"isolated\" from others until it is committed (information from uncommitted transactions \"doesn't exist\" outside of that transaction)\n",
    "    + another way of putting it: the end result of a set of transactions being executed *in parallel* **must** have the same result as if they had been executed *sequentially*\n",
    "    + another way of putting it: nothing you have done but haven't committed should change what I am doing\n",
    "4. durability: once you've committed a transaction it *stays* committed, and can be recovered if needed."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "I only bring up `acid` because the above constraints happen to get harder to manage if you want to scale your database to many different servers or parallel processes, or if you want things to be *BLAZING FASTTTTTTTTTT*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "**<div align=\"center\">PAUSE FOR ZOOM BREAK</div>**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### the basics: `nosql`\n",
    "\n",
    "one problem with `rdbms` is that the tables you create must be defined ahead of time -- the object model of the data you are working with is assumed, and if that changes the only way to incorporate that is to update your schema\n",
    "\n",
    "a second issue is that `acid`ity relies heavily on the existence of one central process (the database server) and one \"golden copy\" of the underlying data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "if you want to scale out the number of connected clients or the volume of the data itself, you could keep making your central process bigger and faster (\"scale vertically\").\n",
    "\n",
    "at some point, though, you'd like to distribute the workload -- have several servers and access whichever one is free, and create new servers to meet demand"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "as it turns out, there are several reasons why traditional databases are hard to distribute across multiple tables (*e.g.* joining tables is harder, hard to guarantee consistency or shared state, partitioned or segmented data is a major complication)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "> `rdbms` are nice and all, but maybe they aren't worth the hassle. what if we could do something that solved *these* problems (easier to scale, flexible data model) by giving up some of the nice parts (`acid`ity, relationships, or even `sql`)..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "and `nosql` is born. `nosql` is a catch-all term for any database system which abandons the **relations** of `rdbms` in favor of some other data model which more directly addresses the above issue. `DROP tables`.\n",
    "\n",
    "by losing `tables`, we've also necessarily lost `sql`.\n",
    "\n",
    "really, it might be a little more to-the-point to call it \"norel\" or \"non-relational\" but that doesn't sound as `l33t` so I guess I get it."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "so we decide to lose relations and `sql`, but we gain a few things.\n",
    "\n",
    "first, we can tune our performance to be *much, much* faster for some operations (e.g. read, write, and single record lookup) by sacrificing others (aggregation or joins). we can start handling amazon- or google-level data flows.\n",
    "\n",
    "second -- and this is a big deal -- we can start having a **schemaless** model for our data. we no longer need to be locked in to the fields that we thought we'd have in our model at the get-go. if we suddenly have a new column, we add it! if we don't get a value, we don't store anything (not even a `NULL`)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### extremely hand-wavy run-down of types of `nosql` databases\n",
    "\n",
    "suppose you've decided that you need to scale your database horizontally and you're willing to accept the cost of breaking the traditional relational data model for database storage and try one of the `nosql` options. which do you choose?\n",
    "\n",
    "here's a lightning-round, mostly hand-waving description of the structure, motivation, and common implementations for the hottest, l33test `nosql` options out there"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "##### key-value\n",
    "\n",
    "you've probably heard references to \"key-value pairs\" many times before, perhaps in the context of `python` dictionaries. the reason we use dictionaries in `python` (or maps in other languages) is that they are extremely fast for *record insertion* and *lookup*\n",
    "\n",
    "key-value `nosql` databases are basically just giant distributed dictionaries / hash tables. a *unique* key is associated with a physical place in memory or on disk (via hashing), and the associated value is saved there. the values themselves can be anything: short strings, blobs of text, binary file contents, etc."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "key-value stores are **extremely** fast for read, write, update, and delete. at the same time, they are almost completely structureless, so there is effectively no querying possible. you use these when you want to be able to handle extremely large and extremely fast-moving IO reliably and at scale, and not for much else."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "the [most popular implementations](https://db-engines.com/en/ranking/key-value+store) are\n",
    "\n",
    "+ `redis` (8)\n",
    "+ amazon's `dynamodb` (multi-model) (16)\n",
    "+ `azure` `cosmosdb` (multi-model) (25)\n",
    "+ `memcached` (27)\n",
    "+ `hazelcast` (42)\n",
    "\n",
    "(numbers above are from all-types ranking, they are presented here in order of popularity among only key-value stores)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "##### document\n",
    "\n",
    "document stores are basically key-value stores that have some rules. they still generally associate unique keys to values in the same way, but they make a stipulation that the *values* be \"documents\" -- text with a format that is generally understood by that database. the commonly accepted structures are things like `json` and `bson` (`b`inary `json`), `xml`, or `yaml`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "by giving up just a bit on the flexibility of key-value stored, document stores are able to allow querying on the database in languages that are generically applicable for the document formats. this is a meaningful improvement if you still have extremely high requirements for throughput but would like to be able to do more than simple lookup queries (e.g. find all `user` documents which have a `location = Washington DC`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "the [most popular implementations](https://db-engines.com/en/ranking/document+store) are\n",
    "\n",
    "+ `mongodb` (5)\n",
    "+ amazon's `dynamodb` (multimodel) (16)\n",
    "+ `azure` `cosmosdb` (multi-model) (25)\n",
    "+ `couchbase` (26)\n",
    "+ `couchdb` (35)\n",
    "+ `firebase` (36)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "##### \"columnar\" (AKA wide column store AKA column family databases)\n",
    "\n",
    "this *awesomely* named family of `nosql` databases is actually **extremely different** from *relational* columnar databases, which leads to basically infinite confusion.\n",
    "\n",
    "it's so cool! I love it!\n",
    "\n",
    "the most important thing to take away from this brief rundown is: there are two types of databases people call \"columnar\" and they are *not the same thing*! this is important because the reasons I would want to use **relational** columnar databases and **`nosql`** columnar databases are generally exact opposites."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "that being said: `nosql` columnar databases are *basically* two-dimensional key-value store databases, where the two dimensions of the key are `(row_name, column_name`). sometimes a `timestamp` is thrown in to get to that precious 3D\n",
    "\n",
    "the \"wide\" column store or \"column family database\" names come about because oftentimes in implementation the columns of the \"table\" are divvied up into \"column families\", and different column families are sent to different servers to distribute and scale."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "the divvying up of columns is worth some elaboration just to understand the data model. start by think of this as taking a normal table and splitting it into `N` thin and very tall chunks, and distribution those.\n",
    "\n",
    "for example, assume I have 1M records `row_000000` through `row_999999` and 100 columns `col_00` through `col_99`. I create column families (`col_00 - col_09`, `col_10 - col_19`, etc). I distribute those column families across multiple servers. the first server has records:\n",
    "\n",
    "```json\n",
    "{row_000000: {col_00: val_00, ..., col_09: val_09},\n",
    " row_000001: {col_00: val_00, ..., col_09: val_09},\n",
    " ... ,\n",
    " row_999999: {col_00: val_00, ..., col_09: val_09}}\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "when we are asked for a specific `row_name, column_name` pair we can very quickly find to the server that has the column family chunk with `column_name` in it, then do a fast lookup of `row_name` there. when we get `row_name` we may have to look up `column_name` again to get our value.\n",
    "\n",
    "like all other key-value stores, these are specifically tuned for *lookup and insertion* -- the **exact opposite** of relational columnar stores!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "also, the example above was *dense*: it had a value for ever row and column pair. in practice, columnar `nosql` tables are *sparse*: none of those rows or columns are required, and only real, known values would be written. the example could have read:\n",
    "\n",
    "```json\n",
    "{row_000000: {col_07: val_07},\n",
    " // no row_000001 record at all! no values for these columns\n",
    " ... ,\n",
    " row_999999: {col_00: val_00, col_09: val_09}}\n",
    "```\n",
    "\n",
    "this is an example of a `nosql` databases being *schemaless*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "the [most popular implementations](https://db-engines.com/en/ranking/wide+column+store) are:\n",
    "\n",
    "+ `cassandra` (10)\n",
    "+ `hbase` (22)\n",
    "+ `azure` `cosmosdb` (multi-model) (25)\n",
    "+ `datastax` (49)\n",
    "+ `azure` `table storage` (66)\n",
    "+ `accumulo` (83)\n",
    "+ `bigtable` (84)\n",
    "+ `scylladb` (106)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "[this](http://dbmsmusings.blogspot.com/2010/03/distinguishing-two-major-types-of_29.html) is a bit outdated but is one of the better articles I found on the topic, and I borrow a fair amount of the above from the descriptions there"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "##### graph\n",
    "\n",
    "much real-world data is not only about entities (records), but *also* about relationships between those entities.\n",
    "\n",
    "in traditional `rdbms` settings, these are often encoded as \"foreign key\" constraints. going back to our ugly `EmploymentHistory` table from before:\n",
    "\n",
    "| empl_hist_id | person_id | empl_id |\n",
    "|--------------|-----------|---------|\n",
    "| 1            | 1         | 1       |\n",
    "| 2            | 1         | 2       |\n",
    "\n",
    "a record in table `EmploymentHistory` has foreign keys pointing to the `Person` and `Employer` tables indicating there is a relationship between them."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "this way of storing relationships is fine for one-hop querying, but is particularly bad for multi-hop querying or relationship pattern matching (see the `neo4j` lecture for more info)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "graph databases elevate **relationships** between objects to be of primary importance at the expense of relations (tabular layouts).\n",
    "\n",
    "most graph databases have two concepts:\n",
    "\n",
    "+ nodes: generic \"things\" that have properties. these are often thought of as being nouns in your data's language. properties are arbitrary (it's *schemaless*)\n",
    "+ edges: relationships between one \"thing\" and another \"thing. these can be directed edges, and they too can have properties. there can be many edges between two nodes."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "the same employer example above might be represented internal in a graph database as:\n",
    "\n",
    "```json\n",
    "{0: {'edges': {1: ['EMPLOYED_BY'], 2: ['EMPLOYED_BY']},\n",
    "     'name': 'Zach', 'type': 'Person',\n",
    "     'properties': {...}},\n",
    " 1: {'edges': {0: ['EMPLOYS'], 3: [...], ...},\n",
    "     'name': 'Storyblocks', 'type': 'Employer',\n",
    "     'properties': {...}},\n",
    " 2: {'edges': {0: ['EMPLOYS'], 100: [...], ...},\n",
    "     'name': 'Georgetown', 'type': 'Employer',\n",
    "     'properties': {...}}}\n",
    "```\n",
    "\n",
    "note that you can go from any node (top level key) directly to all it's edges via the `edges` property -- that's the point"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "the [most popular implementations](https://db-engines.com/en/ranking/graph+dbms) are:\n",
    "\n",
    "+ `neo4j` (21)\n",
    "+ `azure` `cosmosdb` (multi-model) (25)\n",
    "+ `arangodb` (69)\n",
    "+ `orientdb` (70)\n",
    "+ `virtuoso` (110)\n",
    "+ amazon `neptune` (113)\n",
    "+ `janusgraph` (116)\n",
    "+ `graphdb` (125)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## summary / why we talked about this\n",
    "\n",
    "| database | type | purpose |\n",
    "|-|-|-|\n",
    "| `rdbms` (e.g. `postgres`) | relational | structured datastore, fast transactions, no distribution |\n",
    "| columnar | relational | fast analytical query, warehousing, limited transaction and distribution |\n",
    "| key-value store | `nosql` | extremely fast throughput, distributed, schemaless, no query |\n",
    "| document store | `nosql` | extremely fast throughput, distributed, schemaless, limited query |\n",
    "| wide column store | `nosql` | fast throughput, distributed, schemaless, limited query |\n",
    "| graph | `nosql` | relationship data, relationship-based querying (not `sql`) |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "given your data (size and structure) and goal, one of the above is probably genuinely a best fit for you. the problem is you usually don't get to choose -- that choice was made by engineers well above you in the data food chain.\n",
    "\n",
    "given that, it's good to know what the fundamental pieces were in that decision, and what the limitations are of the chosen database."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "for ETL there are pros and cons of each type. you are probably more familiar with `sql` as an extraction and computation language, so `nosql` is a hurdle.\n",
    "\n",
    "for modelling, most of our algorithmic approaches assume tabular data, and relational databases shine. columnar databases fit our purposes well if we are doing aggregation or statistical calculations on subsets of data. when we get down to analytical base tables and proper modelling, we typically anticipate we will be using *all* the columns in that table, and we will be training, validating, and predicting on single records of all features one at a time, and nothing beats a traditional `rdbms` for this."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "the next few lectures look at `aws` implementations of several of the above types of databases and datastores. we're going to cover the `aws` built-in services:\n",
    "\n",
    "1. `rds`, the `rdbms` option\n",
    "    + supports several flavors including `mysql`, `postgrest`, `mssql`, `oracle`, and `mariadb`\n",
    "1. `dynamodb`, the `nosql` key-value / document store\n",
    "1. `redshift`, the relational columnar database\n",
    "\n",
    "in addition we will cover how to use `neo4j` with `ec2` instances"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "<strong><em><div align=\"center\"><code>DROP joke WHERE is_bad</code></div></em></strong>\n",
    "<div align=\"center\"><img src=\"https://i.redd.it/u0doli6bk2c01.jpg\" width=\"500px\"></div>\n",
    "\n",
    "# END OF LECTURE\n",
    "\n",
    "next lecture: [`aws rds`](012_dbs_2_rds.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": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "288px"
   },
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}