17_databases.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ETL\n",
    "* How do we get data into a database?\n",
    "* There are several different patterns, and at a high level you will hear this refered to as an ETL process: extract, transform, load\n",
    "* This is often used in data warehousing specifically, and is usually done as a batch process"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ETL Steps\n",
    "* The first step is extraction, where we pull data out of various databases. This could be csv, json files, or other SQL databases\n",
    "* The second step is to transform it. Sometimes this is aligning data structures, mapping columns, or reducing the data.\n",
    "* The third part is to load it, to push it into a new data warehouse (or database) as a solution\n",
    "* Overall we call the ETL process a \"pipeline\" (or pipelines)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"https://webassets.mongodb.com/_com_assets/cms/ETL_Visual-sa656kl6df.png\" width=60%/>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ETL Thoughts\n",
    "* ETL is useful in reducing dependancies in the data created for warehouses and data marts\n",
    "* ETL process can help with permissions issues and heterogeoneity of data sources, especially in an increasingly json world\n",
    "* ETL tools are robust and processes are well known\n",
    "* At the same time, the ETL process overall can be fragile depending upon the developer building it (all it takes is one failed statement to stop a pipeline)\n",
    "* ETL is slow, and the batch-nature means that the data you are looking at is often not live"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Let's do some toy ETL in python\n",
    "# first, let's load our datasets\n",
    "import pandas as pd\n",
    "df_person=pd.read_csv(\"person.csv\")\n",
    "df_sales=pd.read_csv(\"sales.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>street_address</th>\n",
       "      <th>city</th>\n",
       "      <th>postcode</th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Amanda</td>\n",
       "      <td>Miller</td>\n",
       "      <td>4302 John Skyway Apt. 650</td>\n",
       "      <td>West Victor</td>\n",
       "      <td>55311</td>\n",
       "      <td>4910</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Monica</td>\n",
       "      <td>Jenkins</td>\n",
       "      <td>2794 Danielle Skyway Suite 323</td>\n",
       "      <td>West Jenniferport</td>\n",
       "      <td>91875</td>\n",
       "      <td>910</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Keith</td>\n",
       "      <td>Richards</td>\n",
       "      <td>395 Lloyd Route Apt. 439</td>\n",
       "      <td>Port Ashleyville</td>\n",
       "      <td>1667</td>\n",
       "      <td>9970</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Mark</td>\n",
       "      <td>Cowan</td>\n",
       "      <td>4026 Timothy Court</td>\n",
       "      <td>New Sarah</td>\n",
       "      <td>40411</td>\n",
       "      <td>8764</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Amber</td>\n",
       "      <td>King</td>\n",
       "      <td>74417 Warren Ways Apt. 647</td>\n",
       "      <td>South Kelly</td>\n",
       "      <td>40440</td>\n",
       "      <td>6046</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  first_name last_name                  street_address               city  \\\n",
       "0     Amanda    Miller       4302 John Skyway Apt. 650        West Victor   \n",
       "1     Monica   Jenkins  2794 Danielle Skyway Suite 323  West Jenniferport   \n",
       "2      Keith  Richards        395 Lloyd Route Apt. 439   Port Ashleyville   \n",
       "3       Mark     Cowan              4026 Timothy Court          New Sarah   \n",
       "4      Amber      King      74417 Warren Ways Apt. 647        South Kelly   \n",
       "\n",
       "   postcode    id  \n",
       "0     55311  4910  \n",
       "1     91875   910  \n",
       "2      1667  9970  \n",
       "3     40411  8764  \n",
       "4     40440  6046  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Let's assume we are building a datamart for the sales team, and we want to hide PII\n",
    "# information to protect our customers in case of a data breach\n",
    "df_person.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>street_address</th>\n",
       "      <th>city</th>\n",
       "      <th>postcode</th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4302 John Skyway Apt. 650</td>\n",
       "      <td>West Victor</td>\n",
       "      <td>55311</td>\n",
       "      <td>4910</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2794 Danielle Skyway Suite 323</td>\n",
       "      <td>West Jenniferport</td>\n",
       "      <td>91875</td>\n",
       "      <td>910</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>395 Lloyd Route Apt. 439</td>\n",
       "      <td>Port Ashleyville</td>\n",
       "      <td>1667</td>\n",
       "      <td>9970</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4026 Timothy Court</td>\n",
       "      <td>New Sarah</td>\n",
       "      <td>40411</td>\n",
       "      <td>8764</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>74417 Warren Ways Apt. 647</td>\n",
       "      <td>South Kelly</td>\n",
       "      <td>40440</td>\n",
       "      <td>6046</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   street_address               city  postcode    id\n",
       "0       4302 John Skyway Apt. 650        West Victor     55311  4910\n",
       "1  2794 Danielle Skyway Suite 323  West Jenniferport     91875   910\n",
       "2        395 Lloyd Route Apt. 439   Port Ashleyville      1667  9970\n",
       "3              4026 Timothy Court          New Sarah     40411  8764\n",
       "4      74417 Warren Ways Apt. 647        South Kelly     40440  6046"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_person=df_person[[\"street_address\",\"city\",\"postcode\",\"id\"]]\n",
    "df_person.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>credit_card_number</th>\n",
       "      <th>credit_card_provider</th>\n",
       "      <th>id</th>\n",
       "      <th>price</th>\n",
       "      <th>purchase_date</th>\n",
       "      <th>isbn10</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2286937402408045</td>\n",
       "      <td>Discover</td>\n",
       "      <td>8360</td>\n",
       "      <td>7681</td>\n",
       "      <td>2020-03-02</td>\n",
       "      <td>0-7276-4998-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>30009424238819</td>\n",
       "      <td>JCB 15 digit</td>\n",
       "      <td>2416</td>\n",
       "      <td>1820</td>\n",
       "      <td>2020-04-13</td>\n",
       "      <td>0-617-66614-8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>346863220162402</td>\n",
       "      <td>Diners Club / Carte Blanche</td>\n",
       "      <td>1206</td>\n",
       "      <td>2782</td>\n",
       "      <td>2020-09-02</td>\n",
       "      <td>1-4775-5373-8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4591096487982840</td>\n",
       "      <td>Mastercard</td>\n",
       "      <td>7494</td>\n",
       "      <td>844</td>\n",
       "      <td>2020-09-23</td>\n",
       "      <td>0-630-45908-8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4373723705109567</td>\n",
       "      <td>Maestro</td>\n",
       "      <td>8450</td>\n",
       "      <td>4740</td>\n",
       "      <td>2020-06-06</td>\n",
       "      <td>1-80546-961-4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   credit_card_number         credit_card_provider    id  price purchase_date  \\\n",
       "0    2286937402408045                     Discover  8360   7681    2020-03-02   \n",
       "1      30009424238819                 JCB 15 digit  2416   1820    2020-04-13   \n",
       "2     346863220162402  Diners Club / Carte Blanche  1206   2782    2020-09-02   \n",
       "3    4591096487982840                   Mastercard  7494    844    2020-09-23   \n",
       "4    4373723705109567                      Maestro  8450   4740    2020-06-06   \n",
       "\n",
       "          isbn10  \n",
       "0  0-7276-4998-1  \n",
       "1  0-617-66614-8  \n",
       "2  1-4775-5373-8  \n",
       "3  0-630-45908-8  \n",
       "4  1-80546-961-4  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Now let's check out sales\n",
    "df_sales.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "credit_card_number       int64\n",
       "credit_card_provider    object\n",
       "id                       int64\n",
       "price                    int64\n",
       "purchase_date           object\n",
       "isbn10                  object\n",
       "dtype: object"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# I wonder what that date is\n",
    "df_sales.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>credit_card_number</th>\n",
       "      <th>credit_card_provider</th>\n",
       "      <th>id</th>\n",
       "      <th>price</th>\n",
       "      <th>purchase_date</th>\n",
       "      <th>isbn10</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2286937402408045</td>\n",
       "      <td>Discover</td>\n",
       "      <td>8360</td>\n",
       "      <td>7681</td>\n",
       "      <td>2020-03-02</td>\n",
       "      <td>0-7276-4998-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>30009424238819</td>\n",
       "      <td>JCB 15 digit</td>\n",
       "      <td>2416</td>\n",
       "      <td>1820</td>\n",
       "      <td>2020-04-13</td>\n",
       "      <td>0-617-66614-8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>346863220162402</td>\n",
       "      <td>Diners Club / Carte Blanche</td>\n",
       "      <td>1206</td>\n",
       "      <td>2782</td>\n",
       "      <td>2020-09-02</td>\n",
       "      <td>1-4775-5373-8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4591096487982840</td>\n",
       "      <td>Mastercard</td>\n",
       "      <td>7494</td>\n",
       "      <td>844</td>\n",
       "      <td>2020-09-23</td>\n",
       "      <td>0-630-45908-8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4373723705109567</td>\n",
       "      <td>Maestro</td>\n",
       "      <td>8450</td>\n",
       "      <td>4740</td>\n",
       "      <td>2020-06-06</td>\n",
       "      <td>1-80546-961-4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   credit_card_number         credit_card_provider    id  price purchase_date  \\\n",
       "0    2286937402408045                     Discover  8360   7681    2020-03-02   \n",
       "1      30009424238819                 JCB 15 digit  2416   1820    2020-04-13   \n",
       "2     346863220162402  Diners Club / Carte Blanche  1206   2782    2020-09-02   \n",
       "3    4591096487982840                   Mastercard  7494    844    2020-09-23   \n",
       "4    4373723705109567                      Maestro  8450   4740    2020-06-06   \n",
       "\n",
       "          isbn10  \n",
       "0  0-7276-4998-1  \n",
       "1  0-617-66614-8  \n",
       "2  1-4775-5373-8  \n",
       "3  0-630-45908-8  \n",
       "4  1-80546-961-4  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Let's fix that date column\n",
    "df_sales[\"purchase_date\"]=pd.to_datetime(df_sales[\"purchase_date\"])\n",
    "df_sales.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "credit_card_number               int64\n",
       "credit_card_provider            object\n",
       "id                               int64\n",
       "price                            int64\n",
       "purchase_date           datetime64[ns]\n",
       "isbn10                          object\n",
       "dtype: object"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sales.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Originally there were 1000 people\n",
      "In modified set there are 800 people\n"
     ]
    }
   ],
   "source": [
    "# Now, let's drop all of the people who aren't in the sales. The sales team\n",
    "# doesn't need to know about them\n",
    "print(f\"Originally there were {len(df_person)} people\")\n",
    "df_person=df_person[df_person[\"id\"].isin(df_sales[\"id\"])]\n",
    "print(f\"In modified set there are {len(df_person)} people\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We can keep modifying the data as needed depending upon the business rules.\n",
    "# For instance, let's say that credit card number is a sensitive but important\n",
    "# piece of data. That is, we need to know whether the same credit card has been\n",
    "# used, but we don't want to put the number in the database. How might we solve this?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>credit_card_number</th>\n",
       "      <th>credit_card_provider</th>\n",
       "      <th>id</th>\n",
       "      <th>price</th>\n",
       "      <th>purchase_date</th>\n",
       "      <th>isbn10</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>60cb3ca758cacb123eff0dd516d4a6fa</td>\n",
       "      <td>Discover</td>\n",
       "      <td>8360</td>\n",
       "      <td>7681</td>\n",
       "      <td>2020-03-02</td>\n",
       "      <td>0-7276-4998-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>d5488a3f524f2e2de94d46c3f2a142c6</td>\n",
       "      <td>JCB 15 digit</td>\n",
       "      <td>2416</td>\n",
       "      <td>1820</td>\n",
       "      <td>2020-04-13</td>\n",
       "      <td>0-617-66614-8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6a3b447c02cef78f0dd73f181c09c521</td>\n",
       "      <td>Diners Club / Carte Blanche</td>\n",
       "      <td>1206</td>\n",
       "      <td>2782</td>\n",
       "      <td>2020-09-02</td>\n",
       "      <td>1-4775-5373-8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1f23209dda829a5c06d389473c619f5c</td>\n",
       "      <td>Mastercard</td>\n",
       "      <td>7494</td>\n",
       "      <td>844</td>\n",
       "      <td>2020-09-23</td>\n",
       "      <td>0-630-45908-8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>44d7d4e82594312fac29a4cdf9bc5593</td>\n",
       "      <td>Maestro</td>\n",
       "      <td>8450</td>\n",
       "      <td>4740</td>\n",
       "      <td>2020-06-06</td>\n",
       "      <td>1-80546-961-4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 credit_card_number         credit_card_provider    id  price  \\\n",
       "0  60cb3ca758cacb123eff0dd516d4a6fa                     Discover  8360   7681   \n",
       "1  d5488a3f524f2e2de94d46c3f2a142c6                 JCB 15 digit  2416   1820   \n",
       "2  6a3b447c02cef78f0dd73f181c09c521  Diners Club / Carte Blanche  1206   2782   \n",
       "3  1f23209dda829a5c06d389473c619f5c                   Mastercard  7494    844   \n",
       "4  44d7d4e82594312fac29a4cdf9bc5593                      Maestro  8450   4740   \n",
       "\n",
       "  purchase_date         isbn10  \n",
       "0    2020-03-02  0-7276-4998-1  \n",
       "1    2020-04-13  0-617-66614-8  \n",
       "2    2020-09-02  1-4775-5373-8  \n",
       "3    2020-09-23  0-630-45908-8  \n",
       "4    2020-06-06  1-80546-961-4  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def h(x):\n",
    "    import hashlib\n",
    "    return hashlib.md5(str(x).encode()).hexdigest()\n",
    "\n",
    "df_sales[\"credit_card_number\"]=df_sales[\"credit_card_number\"].apply(h)\n",
    "df_sales.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Let's assume that was all the transformation we wanted to do. Now we can push this data into the datamart.\n",
    "* Most ETL libraries will automatically build the schema for you. This is cool!\n",
    "* This is often done through object relational mapping (ORM) libraries\n",
    "* In the python ecosystem this is handled usually by SQLAlchemy\n",
    "* https://www.sqlalchemy.org/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlalchemy\n",
    "engine = sqlalchemy.create_engine('postgres://jovyan:si330studentuser@localhost:5432/si330')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# once we have the engine created we can actually leverage pandas to push the dataframes directly\n",
    "# into tables using the to_sql() functions\n",
    "df_person.to_sql(\"person\",engine,if_exists='replace')\n",
    "df_sales.to_sql(\"sales\",engine,if_exists='replace')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# what went wrong?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# now we can just check out result. For this in an interactive model I like to use the \n",
    "#%sql magics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: jovyan@si330'"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%load_ext sql\n",
    "%sql postgres://jovyan:si330studentuser@localhost:5432/si330"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgres://jovyan:***@localhost:5432/si330\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>index</th>\n",
       "        <th>street_address</th>\n",
       "        <th>city</th>\n",
       "        <th>postcode</th>\n",
       "        <th>id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>0</td>\n",
       "        <td>4302 John Skyway Apt. 650</td>\n",
       "        <td>West Victor</td>\n",
       "        <td>55311</td>\n",
       "        <td>4910</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>2794 Danielle Skyway Suite 323</td>\n",
       "        <td>West Jenniferport</td>\n",
       "        <td>91875</td>\n",
       "        <td>910</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>395 Lloyd Route Apt. 439</td>\n",
       "        <td>Port Ashleyville</td>\n",
       "        <td>1667</td>\n",
       "        <td>9970</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>4026 Timothy Court</td>\n",
       "        <td>New Sarah</td>\n",
       "        <td>40411</td>\n",
       "        <td>8764</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>74417 Warren Ways Apt. 647</td>\n",
       "        <td>South Kelly</td>\n",
       "        <td>40440</td>\n",
       "        <td>6046</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(0, '4302 John Skyway Apt. 650', 'West Victor', 55311, 4910),\n",
       " (1, '2794 Danielle Skyway Suite 323', 'West Jenniferport', 91875, 910),\n",
       " (2, '395 Lloyd Route Apt. 439', 'Port Ashleyville', 1667, 9970),\n",
       " (3, '4026 Timothy Court', 'New Sarah', 40411, 8764),\n",
       " (4, '74417 Warren Ways Apt. 647', 'South Kelly', 40440, 6046)]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select * from person limit 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgres://jovyan:***@localhost:5432/si330\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>index</th>\n",
       "        <th>credit_card_number</th>\n",
       "        <th>credit_card_provider</th>\n",
       "        <th>id</th>\n",
       "        <th>price</th>\n",
       "        <th>purchase_date</th>\n",
       "        <th>isbn10</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>0</td>\n",
       "        <td>60cb3ca758cacb123eff0dd516d4a6fa</td>\n",
       "        <td>Discover</td>\n",
       "        <td>8360</td>\n",
       "        <td>7681</td>\n",
       "        <td>2020-03-02 00:00:00</td>\n",
       "        <td>0-7276-4998-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>d5488a3f524f2e2de94d46c3f2a142c6</td>\n",
       "        <td>JCB 15 digit</td>\n",
       "        <td>2416</td>\n",
       "        <td>1820</td>\n",
       "        <td>2020-04-13 00:00:00</td>\n",
       "        <td>0-617-66614-8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>6a3b447c02cef78f0dd73f181c09c521</td>\n",
       "        <td>Diners Club / Carte Blanche</td>\n",
       "        <td>1206</td>\n",
       "        <td>2782</td>\n",
       "        <td>2020-09-02 00:00:00</td>\n",
       "        <td>1-4775-5373-8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>1f23209dda829a5c06d389473c619f5c</td>\n",
       "        <td>Mastercard</td>\n",
       "        <td>7494</td>\n",
       "        <td>844</td>\n",
       "        <td>2020-09-23 00:00:00</td>\n",
       "        <td>0-630-45908-8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>44d7d4e82594312fac29a4cdf9bc5593</td>\n",
       "        <td>Maestro</td>\n",
       "        <td>8450</td>\n",
       "        <td>4740</td>\n",
       "        <td>2020-06-06 00:00:00</td>\n",
       "        <td>1-80546-961-4</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(0, '60cb3ca758cacb123eff0dd516d4a6fa', 'Discover', 8360, 7681, datetime.datetime(2020, 3, 2, 0, 0), '0-7276-4998-1'),\n",
       " (1, 'd5488a3f524f2e2de94d46c3f2a142c6', 'JCB 15 digit', 2416, 1820, datetime.datetime(2020, 4, 13, 0, 0), '0-617-66614-8'),\n",
       " (2, '6a3b447c02cef78f0dd73f181c09c521', 'Diners Club / Carte Blanche', 1206, 2782, datetime.datetime(2020, 9, 2, 0, 0), '1-4775-5373-8'),\n",
       " (3, '1f23209dda829a5c06d389473c619f5c', 'Mastercard', 7494, 844, datetime.datetime(2020, 9, 23, 0, 0), '0-630-45908-8'),\n",
       " (4, '44d7d4e82594312fac29a4cdf9bc5593', 'Maestro', 8450, 4740, datetime.datetime(2020, 6, 6, 0, 0), '1-80546-961-4')]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select * from sales limit 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# wait, is this what we want? what's wrong?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql select * from sales limit 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* ok, that's the end of the ETL process. In our case it was pretty simple. In this week's assignment you have a bigger job to do\n",
    "* a large scale business will have hundreds of these kinds of processes fire up nightly (or hourly), there's a lot that can go wrong\n",
    "* an emerging technology to deal with these issues is called __data virtualization__ \n",
    "* I think data virtualization is frankly amazing and a game changer. In the data virtualization approach the data is left at it's source and the transformations are are \"soft rules\" which are done at run time\n",
    "* Here is the sort of ETLhell that corporations can be locked into"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"https://community.denodo.com/tutorials/img/basics/zero_complete.png\" />"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* it can be a complete mess! The promise with data virtualization is that instead of these systems all speaking across one another with error prone ETL, you build a logial data layer which hides the implementation details\n",
    "* everything in that logical layer looks like a new database, regardless of whether it's a csv, google sheet, api, json datastore, on prem or in the cloud\n",
    "<img src=\"https://community.denodo.com/tutorials/img/basics/zero_denodo.png\" />"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* I'm going to try and arrange for a demonstration of a specific tool, Denodo, which we have here on campus.\n",
    "* It's really really expensive, and reminds me of the \"early days\" of RDBMS systems with 6 digit price tags\n",
    "* But it works really well, in my opinion, for some of my workloads\n",
    "* (images ripped from https://community.denodo.com/tutorials/browse/basics/index)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Views\n",
    "* So we've ETLed the data up into our RDBMS for the sales team, but now we need to make views for them to make the data more coherent\n",
    "* views allow us to create \"virtual tables\", where the underlying view the result of some query\n",
    "* this allows to do further transformation and restriction of data if we want to\n",
    "* it also allows us to bring the data together into something more managable for the person querying it"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgres://jovyan:***@localhost:5432/si330\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>index</th>\n",
       "        <th>street_address</th>\n",
       "        <th>city</th>\n",
       "        <th>postcode</th>\n",
       "        <th>id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>0</td>\n",
       "        <td>4302 John Skyway Apt. 650</td>\n",
       "        <td>West Victor</td>\n",
       "        <td>55311</td>\n",
       "        <td>4910</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>2794 Danielle Skyway Suite 323</td>\n",
       "        <td>West Jenniferport</td>\n",
       "        <td>91875</td>\n",
       "        <td>910</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>395 Lloyd Route Apt. 439</td>\n",
       "        <td>Port Ashleyville</td>\n",
       "        <td>1667</td>\n",
       "        <td>9970</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>4026 Timothy Court</td>\n",
       "        <td>New Sarah</td>\n",
       "        <td>40411</td>\n",
       "        <td>8764</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>74417 Warren Ways Apt. 647</td>\n",
       "        <td>South Kelly</td>\n",
       "        <td>40440</td>\n",
       "        <td>6046</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(0, '4302 John Skyway Apt. 650', 'West Victor', 55311, 4910),\n",
       " (1, '2794 Danielle Skyway Suite 323', 'West Jenniferport', 91875, 910),\n",
       " (2, '395 Lloyd Route Apt. 439', 'Port Ashleyville', 1667, 9970),\n",
       " (3, '4026 Timothy Court', 'New Sarah', 40411, 8764),\n",
       " (4, '74417 Warren Ways Apt. 647', 'South Kelly', 40440, 6046)]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select * from person limit 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgres://jovyan:***@localhost:5432/si330\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# the simple view syntax is CREATE VIEW name AS select_statement\n",
    "%sql create view only_ports as select * from person where city like '%port%';"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgres://jovyan:***@localhost:5432/si330\n",
      "43 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>index</th>\n",
       "        <th>street_address</th>\n",
       "        <th>city</th>\n",
       "        <th>postcode</th>\n",
       "        <th>id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>2794 Danielle Skyway Suite 323</td>\n",
       "        <td>West Jenniferport</td>\n",
       "        <td>91875</td>\n",
       "        <td>910</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "        <td>7553 Watkins Curve Apt. 427</td>\n",
       "        <td>Brendaport</td>\n",
       "        <td>16142</td>\n",
       "        <td>7494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>013 Tim Track Suite 187</td>\n",
       "        <td>North Patriciaport</td>\n",
       "        <td>65340</td>\n",
       "        <td>5842</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14</td>\n",
       "        <td>864 Tamara Burgs Suite 603</td>\n",
       "        <td>Bishopport</td>\n",
       "        <td>48723</td>\n",
       "        <td>2180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>59</td>\n",
       "        <td>98655 Jordan Prairie Suite 506</td>\n",
       "        <td>Sandraport</td>\n",
       "        <td>89089</td>\n",
       "        <td>9048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>68</td>\n",
       "        <td>0142 Walter Loop Suite 655</td>\n",
       "        <td>Perezport</td>\n",
       "        <td>98311</td>\n",
       "        <td>412</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>76</td>\n",
       "        <td>9458 Ball Drives</td>\n",
       "        <td>Finleyport</td>\n",
       "        <td>94498</td>\n",
       "        <td>4594</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>99</td>\n",
       "        <td>1835 Cynthia Via Suite 392</td>\n",
       "        <td>Lake Timothyport</td>\n",
       "        <td>70578</td>\n",
       "        <td>3384</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>103</td>\n",
       "        <td>14080 Angela Islands</td>\n",
       "        <td>Garyport</td>\n",
       "        <td>12927</td>\n",
       "        <td>6583</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>108</td>\n",
       "        <td>809 Paul Overpass</td>\n",
       "        <td>Johnport</td>\n",
       "        <td>2646</td>\n",
       "        <td>5170</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>129</td>\n",
       "        <td>6283 Kristy Highway Suite 956</td>\n",
       "        <td>Cruzport</td>\n",
       "        <td>12874</td>\n",
       "        <td>6133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>147</td>\n",
       "        <td>090 Nichols Valley Suite 019</td>\n",
       "        <td>Rayport</td>\n",
       "        <td>50329</td>\n",
       "        <td>5369</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>204</td>\n",
       "        <td>757 Anthony Track Apt. 384</td>\n",
       "        <td>Averyport</td>\n",
       "        <td>33949</td>\n",
       "        <td>3032</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>219</td>\n",
       "        <td>7734 Torres Mission</td>\n",
       "        <td>Zimmermanport</td>\n",
       "        <td>72771</td>\n",
       "        <td>2160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>257</td>\n",
       "        <td>710 Justin Forest</td>\n",
       "        <td>North Coltonport</td>\n",
       "        <td>23138</td>\n",
       "        <td>6511</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>300</td>\n",
       "        <td>035 Baker Trail Apt. 602</td>\n",
       "        <td>North Troyport</td>\n",
       "        <td>65490</td>\n",
       "        <td>2726</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>329</td>\n",
       "        <td>469 Thompson Brook Suite 624</td>\n",
       "        <td>Daviesport</td>\n",
       "        <td>98043</td>\n",
       "        <td>8981</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>374</td>\n",
       "        <td>228 Myers Mountains Suite 721</td>\n",
       "        <td>Burnsport</td>\n",
       "        <td>19840</td>\n",
       "        <td>8260</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>408</td>\n",
       "        <td>9186 Aaron Cliff Apt. 948</td>\n",
       "        <td>North Jeffreyport</td>\n",
       "        <td>87445</td>\n",
       "        <td>121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>451</td>\n",
       "        <td>44546 Christopher Corner</td>\n",
       "        <td>Brandonport</td>\n",
       "        <td>7492</td>\n",
       "        <td>2242</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>478</td>\n",
       "        <td>8489 Quinn Stream</td>\n",
       "        <td>South Davidport</td>\n",
       "        <td>11453</td>\n",
       "        <td>1094</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>523</td>\n",
       "        <td>50642 Fowler Crossing</td>\n",
       "        <td>Jasonport</td>\n",
       "        <td>85762</td>\n",
       "        <td>2761</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>548</td>\n",
       "        <td>95840 Lydia Run Apt. 272</td>\n",
       "        <td>New Toddport</td>\n",
       "        <td>33298</td>\n",
       "        <td>6114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>570</td>\n",
       "        <td>1098 Daniel Plain</td>\n",
       "        <td>Jacksonport</td>\n",
       "        <td>27748</td>\n",
       "        <td>5107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>580</td>\n",
       "        <td>9366 Huerta Walks</td>\n",
       "        <td>Larsenport</td>\n",
       "        <td>8045</td>\n",
       "        <td>9302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>598</td>\n",
       "        <td>935 Williams River</td>\n",
       "        <td>Nicholasport</td>\n",
       "        <td>31365</td>\n",
       "        <td>5082</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>599</td>\n",
       "        <td>0939 Tanner Passage Apt. 256</td>\n",
       "        <td>Ericaport</td>\n",
       "        <td>61080</td>\n",
       "        <td>7440</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>612</td>\n",
       "        <td>417 Wanda Hollow</td>\n",
       "        <td>Debraport</td>\n",
       "        <td>64295</td>\n",
       "        <td>1269</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>640</td>\n",
       "        <td>082 Boyer Roads</td>\n",
       "        <td>Trevorport</td>\n",
       "        <td>74889</td>\n",
       "        <td>9292</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>700</td>\n",
       "        <td>2987 Sandra Plains Suite 764</td>\n",
       "        <td>Bradyport</td>\n",
       "        <td>29808</td>\n",
       "        <td>5707</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>743</td>\n",
       "        <td>465 Murphy Village Apt. 717</td>\n",
       "        <td>Andreaport</td>\n",
       "        <td>1366</td>\n",
       "        <td>3912</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>766</td>\n",
       "        <td>160 Coleman Fields Suite 444</td>\n",
       "        <td>North Brandiport</td>\n",
       "        <td>16683</td>\n",
       "        <td>2931</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>778</td>\n",
       "        <td>02996 James Neck Apt. 144</td>\n",
       "        <td>South Jessicaport</td>\n",
       "        <td>47203</td>\n",
       "        <td>8149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>781</td>\n",
       "        <td>670 Gonzalez Coves</td>\n",
       "        <td>Gutierrezport</td>\n",
       "        <td>68619</td>\n",
       "        <td>1225</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>823</td>\n",
       "        <td>1996 Robin Fort Suite 689</td>\n",
       "        <td>Garyport</td>\n",
       "        <td>18092</td>\n",
       "        <td>5157</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>837</td>\n",
       "        <td>8359 Snow View Suite 885</td>\n",
       "        <td>New Joshuaport</td>\n",
       "        <td>74922</td>\n",
       "        <td>7019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>865</td>\n",
       "        <td>3066 Lucas Green</td>\n",
       "        <td>Russellport</td>\n",
       "        <td>4840</td>\n",
       "        <td>2565</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>869</td>\n",
       "        <td>19519 Rachel Locks</td>\n",
       "        <td>Justinport</td>\n",
       "        <td>4312</td>\n",
       "        <td>8533</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>887</td>\n",
       "        <td>093 Jeffrey Turnpike</td>\n",
       "        <td>Moonport</td>\n",
       "        <td>35048</td>\n",
       "        <td>6845</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>894</td>\n",
       "        <td>58657 Sanders Light</td>\n",
       "        <td>Port Sharonport</td>\n",
       "        <td>82690</td>\n",
       "        <td>131</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>942</td>\n",
       "        <td>573 Thomas Dale Suite 285</td>\n",
       "        <td>Sabrinaport</td>\n",
       "        <td>2425</td>\n",
       "        <td>7903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>943</td>\n",
       "        <td>3282 Michelle Roads</td>\n",
       "        <td>Brittanyport</td>\n",
       "        <td>14815</td>\n",
       "        <td>1311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>986</td>\n",
       "        <td>2281 Lee Road</td>\n",
       "        <td>Ramosport</td>\n",
       "        <td>78132</td>\n",
       "        <td>4038</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, '2794 Danielle Skyway Suite 323', 'West Jenniferport', 91875, 910),\n",
       " (6, '7553 Watkins Curve Apt. 427', 'Brendaport', 16142, 7494),\n",
       " (8, '013 Tim Track Suite 187', 'North Patriciaport', 65340, 5842),\n",
       " (14, '864 Tamara Burgs Suite 603', 'Bishopport', 48723, 2180),\n",
       " (59, '98655 Jordan Prairie Suite 506', 'Sandraport', 89089, 9048),\n",
       " (68, '0142 Walter Loop Suite 655', 'Perezport', 98311, 412),\n",
       " (76, '9458 Ball Drives', 'Finleyport', 94498, 4594),\n",
       " (99, '1835 Cynthia Via Suite 392', 'Lake Timothyport', 70578, 3384),\n",
       " (103, '14080 Angela Islands', 'Garyport', 12927, 6583),\n",
       " (108, '809 Paul Overpass', 'Johnport', 2646, 5170),\n",
       " (129, '6283 Kristy Highway Suite 956', 'Cruzport', 12874, 6133),\n",
       " (147, '090 Nichols Valley Suite 019', 'Rayport', 50329, 5369),\n",
       " (204, '757 Anthony Track Apt. 384', 'Averyport', 33949, 3032),\n",
       " (219, '7734 Torres Mission', 'Zimmermanport', 72771, 2160),\n",
       " (257, '710 Justin Forest', 'North Coltonport', 23138, 6511),\n",
       " (300, '035 Baker Trail Apt. 602', 'North Troyport', 65490, 2726),\n",
       " (329, '469 Thompson Brook Suite 624', 'Daviesport', 98043, 8981),\n",
       " (374, '228 Myers Mountains Suite 721', 'Burnsport', 19840, 8260),\n",
       " (408, '9186 Aaron Cliff Apt. 948', 'North Jeffreyport', 87445, 121),\n",
       " (451, '44546 Christopher Corner', 'Brandonport', 7492, 2242),\n",
       " (478, '8489 Quinn Stream', 'South Davidport', 11453, 1094),\n",
       " (523, '50642 Fowler Crossing', 'Jasonport', 85762, 2761),\n",
       " (548, '95840 Lydia Run Apt. 272', 'New Toddport', 33298, 6114),\n",
       " (570, '1098 Daniel Plain', 'Jacksonport', 27748, 5107),\n",
       " (580, '9366 Huerta Walks', 'Larsenport', 8045, 9302),\n",
       " (598, '935 Williams River', 'Nicholasport', 31365, 5082),\n",
       " (599, '0939 Tanner Passage Apt. 256', 'Ericaport', 61080, 7440),\n",
       " (612, '417 Wanda Hollow', 'Debraport', 64295, 1269),\n",
       " (640, '082 Boyer Roads', 'Trevorport', 74889, 9292),\n",
       " (700, '2987 Sandra Plains Suite 764', 'Bradyport', 29808, 5707),\n",
       " (743, '465 Murphy Village Apt. 717', 'Andreaport', 1366, 3912),\n",
       " (766, '160 Coleman Fields Suite 444', 'North Brandiport', 16683, 2931),\n",
       " (778, '02996 James Neck Apt. 144', 'South Jessicaport', 47203, 8149),\n",
       " (781, '670 Gonzalez Coves', 'Gutierrezport', 68619, 1225),\n",
       " (823, '1996 Robin Fort Suite 689', 'Garyport', 18092, 5157),\n",
       " (837, '8359 Snow View Suite 885', 'New Joshuaport', 74922, 7019),\n",
       " (865, '3066 Lucas Green', 'Russellport', 4840, 2565),\n",
       " (869, '19519 Rachel Locks', 'Justinport', 4312, 8533),\n",
       " (887, '093 Jeffrey Turnpike', 'Moonport', 35048, 6845),\n",
       " (894, '58657 Sanders Light', 'Port Sharonport', 82690, 131),\n",
       " (942, '573 Thomas Dale Suite 285', 'Sabrinaport', 2425, 7903),\n",
       " (943, '3282 Michelle Roads', 'Brittanyport', 14815, 1311),\n",
       " (986, '2281 Lee Road', 'Ramosport', 78132, 4038)]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# this has allowed us to restrict which ROWS a user sees based on a given condition\n",
    "%sql select * from only_ports; -- limit 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgres://jovyan:***@localhost:5432/si330\n",
      "Done.\n",
      " * postgres://jovyan:***@localhost:5432/si330\n",
      "Done.\n",
      " * postgres://jovyan:***@localhost:5432/si330\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>city</th>\n",
       "        <th>postcode</th>\n",
       "        <th>id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>West Jenniferport</td>\n",
       "        <td>91875</td>\n",
       "        <td>910</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brendaport</td>\n",
       "        <td>16142</td>\n",
       "        <td>7494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>North Patriciaport</td>\n",
       "        <td>65340</td>\n",
       "        <td>5842</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bishopport</td>\n",
       "        <td>48723</td>\n",
       "        <td>2180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sandraport</td>\n",
       "        <td>89089</td>\n",
       "        <td>9048</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('West Jenniferport', 91875, 910),\n",
       " ('Brendaport', 16142, 7494),\n",
       " ('North Patriciaport', 65340, 5842),\n",
       " ('Bishopport', 48723, 2180),\n",
       " ('Sandraport', 89089, 9048)]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# we can restrict columns as well, maybe these users aren't allowed to see street_addresses\n",
    "%sql drop view only_ports;\n",
    "%sql create view only_ports as select city, postcode, id from person where city like '%port%';\n",
    "%sql select * from only_ports limit 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgres://jovyan:***@localhost:5432/si330\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>num</th>\n",
       "        <th>avg_price</th>\n",
       "        <th>min_date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1000</td>\n",
       "        <td>5168.8820000000000000</td>\n",
       "        <td>2020-01-01 00:00:00</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1000, Decimal('5168.8820000000000000'), datetime.datetime(2020, 1, 1, 0, 0))]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select count(*) as num, avg(price) as avg_price, min(purchase_date) as min_date from sales"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgres://jovyan:***@localhost:5432/si330\n",
      "Done.\n",
      " * postgres://jovyan:***@localhost:5432/si330\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>num</th>\n",
       "        <th>avg_price</th>\n",
       "        <th>min_date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1000</td>\n",
       "        <td>5168.8820000000000000</td>\n",
       "        <td>2020-01-01 00:00:00</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1000, Decimal('5168.8820000000000000'), datetime.datetime(2020, 1, 1, 0, 0))]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# of course, we can set views to be the results of pretty much any SQL query, so they could be summary stats to\n",
    "%sql create view summary_sales as select count(*) as num, avg(price) as avg_price, min(purchase_date) as min_date from sales\n",
    "%sql select * from summary_sales"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgres://jovyan:***@localhost:5432/si330\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>index</th>\n",
       "        <th>credit_card_number</th>\n",
       "        <th>credit_card_provider</th>\n",
       "        <th>id</th>\n",
       "        <th>price</th>\n",
       "        <th>purchase_date</th>\n",
       "        <th>isbn10</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>0</td>\n",
       "        <td>60cb3ca758cacb123eff0dd516d4a6fa</td>\n",
       "        <td>Discover</td>\n",
       "        <td>8360</td>\n",
       "        <td>7681</td>\n",
       "        <td>2020-03-02 00:00:00</td>\n",
       "        <td>0-7276-4998-1</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(0, '60cb3ca758cacb123eff0dd516d4a6fa', 'Discover', 8360, 7681, datetime.datetime(2020, 3, 2, 0, 0), '0-7276-4998-1')]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select * from sales limit 1;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgres://jovyan:***@localhost:5432/si330\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# what's really cool about views is that they are live views into the underlying tables.\n",
    "# change a table and the view changes.\n",
    "%sql insert into sales (price) values (9999);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgres://jovyan:***@localhost:5432/si330\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>num</th>\n",
       "        <th>avg_price</th>\n",
       "        <th>min_date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1000</td>\n",
       "        <td>5168.8820000000000000</td>\n",
       "        <td>2020-01-01 00:00:00</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1000, Decimal('5168.8820000000000000'), datetime.datetime(2020, 1, 1, 0, 0))]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select * from summary_sales"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgres://jovyan:***@localhost:5432/si330\n",
      "11 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>credit_card_provider</th>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>VISA 16 digit</td>\n",
       "        <td>175</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>VISA 13 digit</td>\n",
       "        <td>65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>JCB 15 digit</td>\n",
       "        <td>66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Discover</td>\n",
       "        <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mastercard</td>\n",
       "        <td>83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Maestro</td>\n",
       "        <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>VISA 19 digit</td>\n",
       "        <td>91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>JCB 16 digit</td>\n",
       "        <td>155</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>American Express</td>\n",
       "        <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Diners Club / Carte Blanche</td>\n",
       "        <td>81</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('VISA 16 digit', 175),\n",
       " (None, 1),\n",
       " ('VISA 13 digit', 65),\n",
       " ('JCB 15 digit', 66),\n",
       " ('Discover', 100),\n",
       " ('Mastercard', 83),\n",
       " ('Maestro', 99),\n",
       " ('VISA 19 digit', 91),\n",
       " ('JCB 16 digit', 155),\n",
       " ('American Express', 85),\n",
       " ('Diners Club / Carte Blanche', 81)]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select credit_card_provider, count(*) from sales group by credit_card_provider;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgres://jovyan:***@localhost:5432/si330\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>index</th>\n",
       "        <th>credit_card_number</th>\n",
       "        <th>credit_card_provider</th>\n",
       "        <th>id</th>\n",
       "        <th>price</th>\n",
       "        <th>purchase_date</th>\n",
       "        <th>isbn10</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>9999</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(None, None, None, None, 9999, None, None)]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# My person particularities won't let me keep bad data in this database\n",
    "%sql select * from sales where credit_card_number is null"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgres://jovyan:***@localhost:5432/si330\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql delete from sales where credit_card_number is null"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgres://jovyan:***@localhost:5432/si330\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>city</th>\n",
       "        <th>postcode</th>\n",
       "        <th>id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>West Jenniferport</td>\n",
       "        <td>91875</td>\n",
       "        <td>910</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brendaport</td>\n",
       "        <td>16142</td>\n",
       "        <td>7494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>North Patriciaport</td>\n",
       "        <td>65340</td>\n",
       "        <td>5842</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bishopport</td>\n",
       "        <td>48723</td>\n",
       "        <td>2180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sandraport</td>\n",
       "        <td>89089</td>\n",
       "        <td>9048</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('West Jenniferport', 91875, 910),\n",
       " ('Brendaport', 16142, 7494),\n",
       " ('North Patriciaport', 65340, 5842),\n",
       " ('Bishopport', 48723, 2180),\n",
       " ('Sandraport', 89089, 9048)]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# views are also updatable, sometimes. This is interesting, and sometimes infuriating\n",
    "%sql select * from only_ports limit 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgres://jovyan:***@localhost:5432/si330\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>43</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(43,)]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select count(*) from only_ports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgres://jovyan:***@localhost:5432/si330\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql insert into only_ports (city, postcode, id) values ('Chrisport','90210',22);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgres://jovyan:***@localhost:5432/si330\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>44</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(44,)]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select count(*) from only_ports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgres://jovyan:***@localhost:5432/si330\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql insert into only_ports (city, postcode, id) values ('Beverly Hills','90210',22);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgres://jovyan:***@localhost:5432/si330\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>44</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(44,)]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# did it put it in our view?\n",
    "%sql select count(*) from only_ports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgres://jovyan:***@localhost:5432/si330\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>index</th>\n",
       "        <th>street_address</th>\n",
       "        <th>city</th>\n",
       "        <th>postcode</th>\n",
       "        <th>id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>Beverly Hills</td>\n",
       "        <td>90210</td>\n",
       "        <td>22</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(None, None, 'Beverly Hills', 90210, 22)]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# whew, that's good, it's not a port\n",
    "# but wait, what about our underlying table?\n",
    "%sql select * from person where city ='Beverly Hills'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgres://jovyan:***@localhost:5432/si330\n",
      "Done.\n",
      " * postgres://jovyan:***@localhost:5432/si330\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# we can dabble in some advanced parameters after checking the docs to control this behavior\n",
    "%sql drop view if exists only_ports\n",
    "%sql create view only_ports as select * from person where city like '%port%' with check option;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgres://jovyan:***@localhost:5432/si330\n",
      "(psycopg2.errors.WithCheckOptionViolation) new row violates check option for view \"only_ports\"\n",
      "DETAIL:  Failing row contains (null, null, Beverly Hills, 90210, 22).\n",
      "\n",
      "[SQL: insert into only_ports (city, postcode, id) values ('Beverly Hills','90210',22);]\n",
      "(Background on this error at: http://sqlalche.me/e/13/f405)\n"
     ]
    }
   ],
   "source": [
    "%sql insert into only_ports (city, postcode, id) values ('Beverly Hills','90210',22);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgres://jovyan:***@localhost:5432/si330\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql insert into only_ports (city, postcode, id) values ('Beverly Hillsport','90210',22);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ok, whew, that was a lot, let's get rid of our tables now and clean up\n",
    "%sql drop table person;\n",
    "%sql drop tables sales;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "select *\n",
    "from (select *\n",
    "      from coursera.assessment_actions\n",
    "      where assessment_id in (select assessment_id\n",
    "                              from coursera.course_branch_item_assessments\n",
    "                              where course_item_id = 'AhLUP')) as actions\n",
    "         left join (\n",
    "    select *\n",
    "    from (select assessment_response_score,\n",
    "                 assessment_id,\n",
    "                 assessment_question_id,\n",
    "                 assessment_response_id,\n",
    "                 assessment_action_id\n",
    "          from coursera.assessment_responses\n",
    "          where assessment_id in (select assessment_id\n",
    "                                  from coursera.course_branch_item_assessments\n",
    "                                  where course_item_id = 'AhLUP')) as grades\n",
    "             left join coursera.assessment_response_options as details\n",
    "                       on grades.assessment_response_id = details.assessment_response_id\n",
    ") as subsmissions on actions.assessment_action_id = subsmissions.assessment_action_id;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgres://jovyan:***@localhost:5432/si330\n",
      "Done.\n"
     ]
    }
   ],
   "source": [
    "with open(\"untitled.txt\",\"r\") as file:\n",
    "    content=file.read()\n",
    "    %sql $content\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgres://jovyan:***@localhost:5432/si330\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select * from dog"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/bin/sh: 1: pq: not found\n"
     ]
    }
   ],
   "source": [
    "!pq"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}