project3.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. Download Shopify dataset and create table script:\n",
    "\n",
    "#### Open terminal and run:\n",
    "`gsutil cp gs://cs327e-open-access/spanner_shopify.zip .`\n",
    "\n",
    "`unzip spanner_shopify.zip`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Configure Spanner CLI and create Shopify Database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "env: GOPATH=/home/jupyter/go\n",
      "env: PATH=$PATH:/usr/local/go/bin:$GOPATH/bin\n",
      "env: SPANNER_EMULATOR_HOST=localhost:9010\n",
      "env: CLI=/home/jupyter/go/bin/spanner-cli\n",
      "env: SPAN_PROJECT=lunar-analyzer-302702\n",
      "env: SPAN_INSTANCE=span-instance\n",
      "env: SPAN_DATABASE=test-database\n"
     ]
    }
   ],
   "source": [
    "%env GOPATH=/home/jupyter/go\n",
    "%env PATH=$PATH:/usr/local/go/bin:$GOPATH/bin\n",
    "%env SPANNER_EMULATOR_HOST=localhost:9010\n",
    "%env CLI=/home/jupyter/go/bin/spanner-cli\n",
    "%env SPAN_PROJECT=lunar-analyzer-302702\n",
    "%env SPAN_INSTANCE=span-instance\n",
    "%env SPAN_DATABASE=test-database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Tables_in_test-database\n",
      "T1\n"
     ]
    }
   ],
   "source": [
    "!$CLI -p $SPAN_PROJECT -i $SPAN_INSTANCE -d $SPAN_DATABASE -e \"show tables\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "CLI_CONNECT=\"$CLI -p $SPAN_PROJECT -i $SPAN_INSTANCE -d $SPAN_DATABASE\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "!{CLI_CONNECT} -e \"create database shopify\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "env: SPAN_DATABASE=shopify\n"
     ]
    }
   ],
   "source": [
    "%env SPAN_DATABASE=shopify"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "!$CLI -p $SPAN_PROJECT -i $SPAN_INSTANCE -d $SPAN_DATABASE -e \"show tables\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "!{CLI_CONNECT} -f /home/jupyter/Amaryllis/spanner_shopify/create_tables.sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Tables_in_shopify\n",
      "key_benefits\n",
      "apps\n",
      "pricing_plans\n",
      "apps_categories\n",
      "pricing_plan_features\n",
      "categories\n",
      "reviews\n"
     ]
    }
   ],
   "source": [
    "!{CLI_CONNECT} -e \"show tables\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table\tCreate Table\n",
      "key_benefits\tCREATE TABLE key_benefits (\n",
      "  app_id STRING(50),\n",
      "  title STRING(2000),\n",
      "  description STRING(MAX),\n",
      ") PRIMARY KEY(app_id, title)\n"
     ]
    }
   ],
   "source": [
    "!{CLI_CONNECT} -e \"show create table key_benefits\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table\tCreate Table\n",
      "apps\tCREATE TABLE apps (\n",
      "  id STRING(50),\n",
      "  url STRING(1000),\n",
      "  title STRING(2000),\n",
      "  developer STRING(100),\n",
      "  developer_link STRING(1000),\n",
      "  icon STRING(1000),\n",
      "  rating FLOAT64,\n",
      "  reviews_count INT64,\n",
      ") PRIMARY KEY(id)\n"
     ]
    }
   ],
   "source": [
    "!{CLI_CONNECT} -e \"show create table apps\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table\tCreate Table\n",
      "pricing_plans\tCREATE TABLE pricing_plans (\n",
      "  id STRING(50),\n",
      "  app_id STRING(50),\n",
      "  title STRING(200),\n",
      "  price FLOAT64,\n",
      ") PRIMARY KEY(id)\n"
     ]
    }
   ],
   "source": [
    "!{CLI_CONNECT} -e \"show create table pricing_plans\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table\tCreate Table\n",
      "apps_categories\tCREATE TABLE apps_categories (\n",
      "  app_id STRING(50),\n",
      "  category_id STRING(50),\n",
      ") PRIMARY KEY(app_id, category_id)\n"
     ]
    }
   ],
   "source": [
    "!{CLI_CONNECT} -e \"show create table apps_categories\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table\tCreate Table\n",
      "pricing_plan_features\tCREATE TABLE pricing_plan_features (\n",
      "  pricing_plan_id STRING(50),\n",
      "  app_id STRING(50),\n",
      "  feature STRING(MAX),\n",
      ") PRIMARY KEY(pricing_plan_id, app_id, feature)\n"
     ]
    }
   ],
   "source": [
    "!{CLI_CONNECT} -e \"show create table pricing_plan_features\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table\tCreate Table\n",
      "categories\tCREATE TABLE categories (\n",
      "  id STRING(50),\n",
      "  title STRING(2000),\n",
      ") PRIMARY KEY(id)\n"
     ]
    }
   ],
   "source": [
    "!{CLI_CONNECT} -e \"show create table categories\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table\tCreate Table\n",
      "reviews\tCREATE TABLE reviews (\n",
      "  app_id STRING(50),\n",
      "  author STRING(250),\n",
      "  rating INT64,\n",
      "  posted_at DATE,\n",
      ") PRIMARY KEY(app_id, author)\n"
     ]
    }
   ],
   "source": [
    "!{CLI_CONNECT} -e \"show create table reviews\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. Populate tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "from google.cloud import spanner\n",
    "from google.cloud.spanner_v1 import param_types\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "span_instance=\"span-instance\"\n",
    "span_database=\"shopify\"\n",
    "spanner_client = spanner.Client()\n",
    "instance = spanner_client.instance(span_instance)\n",
    "database = instance.database(span_database)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('/home/jupyter/Amaryllis/spanner_shopify/apps.csv', sep=',', header=0, lineterminator='\\n')\n",
    "values = df.values.tolist()\n",
    "\n",
    "with database.batch() as batch:\n",
    "    batch.insert(\n",
    "        table=\"apps\",\n",
    "        columns=(\"id\", \"url\", \"title\", \"developer\", \"developer_link\", \"icon\", \"rating\", \"reviews_count\"),\n",
    "        values=values\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('/home/jupyter/Amaryllis/spanner_shopify/apps_categories.csv', sep=',', header=0, lineterminator='\\n')\n",
    "values = df.values.tolist()\n",
    "\n",
    "with database.batch() as batch:\n",
    "    batch.insert(\n",
    "        table=\"apps_categories\",\n",
    "        columns=(\"app_id\", \"category_id\"),\n",
    "        values=values\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('/home/jupyter/Amaryllis/spanner_shopify/categories.csv', sep=',', header=0, lineterminator='\\n')\n",
    "values = df.values.tolist()\n",
    "\n",
    "with database.batch() as batch:\n",
    "    batch.insert(\n",
    "        table=\"categories\",\n",
    "        columns=(\"id\", \"title\"),\n",
    "        values=values\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('/home/jupyter/Amaryllis/spanner_shopify/key_benefits.csv', sep=',', header=0, lineterminator='\\n')\n",
    "values = df.values.tolist()\n",
    "\n",
    "with database.batch() as batch:\n",
    "    batch.insert(\n",
    "        table=\"key_benefits\",\n",
    "        columns=(\"app_id\", \"title\", \"description\"),\n",
    "        values=values\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('/home/jupyter/Amaryllis/spanner_shopify/pricing_plans.csv', sep=',', header=0, lineterminator='\\n')\n",
    "values = df.values.tolist()\n",
    "\n",
    "with database.batch() as batch:\n",
    "    batch.insert(\n",
    "        table=\"pricing_plans\",\n",
    "        columns=(\"id\", \"app_id\", \"title\", \"price\"),\n",
    "        values=values\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('/home/jupyter/Amaryllis/spanner_shopify/pricing_plan_features.csv', sep=',', header=0, lineterminator='\\n')\n",
    "values = df.values.tolist()\n",
    "\n",
    "with database.batch() as batch:\n",
    "    batch.insert(\n",
    "        table=\"pricing_plan_features\",\n",
    "        columns=(\"pricing_plan_id\", \"app_id\", \"feature\"),\n",
    "        values=values\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "3547\n"
     ]
    }
   ],
   "source": [
    "!{CLI_CONNECT} -e \"select count(*) from apps\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "5383\n"
     ]
    }
   ],
   "source": [
    "!{CLI_CONNECT} -e \"select count(*) from apps_categories\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "12\n"
     ]
    }
   ],
   "source": [
    "!{CLI_CONNECT} -e \"select count(*) from categories\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "9541\n"
     ]
    }
   ],
   "source": [
    "!{CLI_CONNECT} -e \"select count(*) from key_benefits\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "6275\n"
     ]
    }
   ],
   "source": [
    "!{CLI_CONNECT} -e \"select count(*) from pricing_plans\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "16270\n"
     ]
    }
   ],
   "source": [
    "!{CLI_CONNECT} -e \"select count(*) from pricing_plan_features\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 record(s) inserted.\n",
      "1 record(s) inserted.\n",
      "1 record(s) inserted.\n",
      "1 record(s) inserted.\n",
      "1 record(s) inserted.\n"
     ]
    }
   ],
   "source": [
    "def insert_app(transaction):\n",
    "\n",
    "    row_ct = transaction.execute_update(\n",
    "        \"INSERT INTO apps (id, url, title, developer, developer_link, icon, reviews_count) \"\n",
    "        \"VALUES ('683d06af-14c7-4733-9bde-ec5b699af996', \"\n",
    "          \"'https://apps.shopify.com/watchlist?surface_detail=inventory-management&surface_inter_position=1&surface_intra_position=9&surface_type=category',\" \n",
    "          \"'Back in Stock Product Alerts', \"\n",
    "          \"'Swym Corporation', \"\n",
    "          \"'https://apps.shopify.com/partners/developer-ca6a967f09890f68',\"\n",
    "          \"'https://apps.shopifycdn.com/listing_images/9905a4c8f22cb4a3b0c32af55a58ec21/icon/e6d46a7e5e1df375d542d033aae80459.png?height=72&width=72',\"\n",
    "           \"0)\"\n",
    "    )\n",
    "    \n",
    "    print(\"{} record(s) inserted.\".format(row_ct))\n",
    "    \n",
    "    row_ct = transaction.execute_update(\n",
    "        \"INSERT INTO apps_categories (app_id, category_id) \"\n",
    "        \"VALUES ('683d06af-14c7-4733-9bde-ec5b699af996', '737ad50051083aa051d127a53b3ac0da')\"\n",
    "    )\n",
    "    \n",
    "    print(\"{} record(s) inserted.\".format(row_ct))\n",
    "    \n",
    "    \n",
    "    row_ct = transaction.execute_update(\n",
    "        \"INSERT INTO key_benefits (app_id, title, description) \"\n",
    "        \"VALUES ('683d06af-14c7-4733-9bde-ec5b699af996', \"\n",
    "          \"'Drive Back In Stock Sales', 'Back in stock, out of stock, pre order & restock alerts')\"\n",
    "    )\n",
    "    \n",
    "    print(\"{} record(s) inserted.\".format(row_ct))\n",
    "    \n",
    "    row_ct = transaction.execute_update(\n",
    "        \"INSERT INTO pricing_plans (id, app_id, title, price) \"\n",
    "        \"VALUES ('961a2751-1dbd-4f0f-8248-9fe6a6efd4ab', '683d06af-14c7-4733-9bde-ec5b699af996', \"\n",
    "                \"'Free Trial', 14.99)\"\n",
    "    )\n",
    "    \n",
    "    print(\"{} record(s) inserted.\".format(row_ct))\n",
    "    \n",
    "\n",
    "    row_ct = transaction.execute_update(\n",
    "        \"INSERT INTO pricing_plan_features (app_id, pricing_plan_id, feature) \"\n",
    "        \" VALUES ('683d06af-14c7-4733-9bde-ec5b699af996', '961a2751-1dbd-4f0f-8248-9fe6a6efd4ab', \"\n",
    "                 \"'Up to 250 alert requests/mo')\"\n",
    "    )\n",
    "    \n",
    "    print(\"{} record(s) inserted.\".format(row_ct))\n",
    "      \n",
    "database.run_in_transaction(insert_app)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "4780 record(s) updated.\n"
     ]
    }
   ],
   "source": [
    "def update_price(transaction):\n",
    "    row_ct = transaction.execute_update(\n",
    "            \"UPDATE pricing_plans \"\n",
    "            \"SET price = price / 2 \"\n",
    "            \"WHERE price > 0\"\n",
    "        )\n",
    "\n",
    "    print(\"{} record(s) updated.\".format(row_ct))\n",
    "\n",
    "database.run_in_transaction(update_price)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 record(s) deleted.\n",
      "1 record(s) deleted.\n",
      "1 record(s) deleted.\n",
      "1 record(s) deleted.\n",
      "1 record(s) deleted.\n"
     ]
    }
   ],
   "source": [
    "def delete_app(transaction):\n",
    "        row_ct = transaction.execute_update(\n",
    "            \"DELETE FROM apps WHERE id = '683d06af-14c7-4733-9bde-ec5b699af996'\"\n",
    "        )\n",
    "\n",
    "        print(\"{} record(s) deleted.\".format(row_ct))\n",
    "        \n",
    "        row_ct = transaction.execute_update(\n",
    "            \"DELETE FROM apps_categories WHERE app_id = '683d06af-14c7-4733-9bde-ec5b699af996'\"\n",
    "        )\n",
    "        \n",
    "        print(\"{} record(s) deleted.\".format(row_ct))\n",
    "        \n",
    "        row_ct = transaction.execute_update(\n",
    "            \"DELETE FROM key_benefits WHERE app_id = '683d06af-14c7-4733-9bde-ec5b699af996'\"\n",
    "        )\n",
    "        \n",
    "        print(\"{} record(s) deleted.\".format(row_ct)) \n",
    "        \n",
    "        \n",
    "        row_ct = transaction.execute_update(\n",
    "            \"DELETE FROM pricing_plans WHERE app_id = '683d06af-14c7-4733-9bde-ec5b699af996'\"\n",
    "        )\n",
    "        \n",
    "        print(\"{} record(s) deleted.\".format(row_ct)) \n",
    "        \n",
    "        \n",
    "        row_ct = transaction.execute_update(\n",
    "            \"DELETE FROM pricing_plan_features WHERE app_id = '683d06af-14c7-4733-9bde-ec5b699af996'\"\n",
    "        )\n",
    "        \n",
    "        print(\"{} record(s) deleted.\".format(row_ct))\n",
    "\n",
    "database.run_in_transaction(delete_app)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "!{CLI_CONNECT} -e \"ALTER TABLE apps_categories ADD CONSTRAINT apps_cat_app_id_fk FOREIGN KEY (app_id) REFERENCES apps (id)\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table\tCreate Table\n",
      "apps_categories\tCREATE TABLE apps_categories (\n",
      "  app_id STRING(50),\n",
      "  category_id STRING(50),\n",
      "  CONSTRAINT apps_cat_app_id_fk FOREIGN KEY(app_id) REFERENCES apps(id),\n",
      ") PRIMARY KEY(app_id, category_id)\n"
     ]
    }
   ],
   "source": [
    "!{CLI_CONNECT} -e \"show create table apps_categories\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "!{CLI_CONNECT} -e \"CREATE INDEX app_rating_idx ON apps(rating)\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Begin Project 3 Work"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Populate the reviews table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('/home/jupyter/Amaryllis/spanner_shopify/reviews.csv', sep=',', header=0, lineterminator='\\n')\n",
    "values = df.values.tolist() \n",
    "\n",
    "with database.batch() as batch:\n",
    "    batch.insert(\n",
    "        table=\"reviews\",\n",
    "        columns=(\"app_id\", \"author\", \"rating\", \"posted_at\"),\n",
    "        values=values\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Run a count on the reviews table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "124601\n"
     ]
    }
   ],
   "source": [
    "!{CLI_CONNECT} -e \"SELECT count(*) from reviews\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Describe the reviews table via the show create table command"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table\tCreate Table\n",
      "reviews\tCREATE TABLE reviews (\n",
      "  app_id STRING(50),\n",
      "  author STRING(250),\n",
      "  rating INT64,\n",
      "  posted_at DATE,\n",
      ") PRIMARY KEY(app_id, author)\n"
     ]
    }
   ],
   "source": [
    "!{CLI_CONNECT} -e \"SHOW CREATE TABLE reviews\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add 2 fake reviews for reviews table and update the apps table in ONE transaction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 records(s) inserted.\n",
      "1 records(s) inserted.\n",
      "1 records(s) updated.\n"
     ]
    }
   ],
   "source": [
    "def add_update_reviews(transaction):\n",
    "    row_ct = transaction.execute_update(\n",
    "        \"INSERT INTO reviews (app_id, author, rating, posted_at) \"\n",
    "        \"VALUES ('6286d4cd-3fcb-4ee3-bb3c-de051c28b83c', 'Kimmi Sin', 5, '2021-03-02') \"\n",
    "    )\n",
    "    print(\"{} records(s) inserted.\".format(row_ct))\n",
    "    \n",
    "    row_ct = transaction.execute_update(\n",
    "        \"INSERT INTO reviews (app_id, author, rating, posted_at) \"\n",
    "        \"VALUES ('6286d4cd-3fcb-4ee3-bb3c-de051c28b83c', 'Brinnah Welmaker', 4, '2021-03-03') \"\n",
    "    )\n",
    "    print(\"{} records(s) inserted.\".format(row_ct))\n",
    "    \n",
    "    row_ct = transaction.execute_update(\n",
    "        \"UPDATE apps \"\n",
    "        \"SET rating = ((rating*reviews_count) + 9)/(reviews_count + 2), reviews_count = reviews_count + 2 \"\n",
    "        \"WHERE id = '6286d4cd-3fcb-4ee3-bb3c-de051c28b83c' \"\n",
    "    )\n",
    "    print(\"{} records(s) updated.\".format(row_ct))\n",
    "\n",
    "database.run_in_transaction(add_update_reviews)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add missing foreign keys"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Adds pricing_plan_features.app_id FK\n",
    "!{CLI_CONNECT} -e \"ALTER TABLE pricing_plan_features ADD CONSTRAINT pricing_plan_feat_app_id_fk FOREIGN KEY (app_id) REFERENCES apps (id)\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Adds pricing_plan_features.pricing_plan_id FK\n",
    "!{CLI_CONNECT} -e \"ALTER TABLE pricing_plan_features ADD CONSTRAINT pricing_plan_feat_plan_id_fk FOREIGN KEY (pricing_plan_id) REFERENCES pricing_plans (id)\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Adds apps_categories.category_id FK\n",
    "!{CLI_CONNECT} -e \"ALTER TABLE apps_categories ADD CONSTRAINT apps_cat_cat_id_fk FOREIGN KEY (category_id) REFERENCES categories (id)\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Adds key_benefits.app_id FK\n",
    "!{CLI_CONNECT} -e \"ALTER TABLE key_benefits ADD CONSTRAINT key_ben_app_id_fk FOREIGN KEY (app_id) REFERENCES apps (id)\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Adds reviews.app_id FK\n",
    "!{CLI_CONNECT} -e \"ALTER TABLE reviews ADD CONSTRAINT reviews_app_id_fk FOREIGN KEY (app_id) REFERENCES apps (id)\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Describe tables that had foreign key constraints added"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table\tCreate Table\n",
      "apps_categories\tCREATE TABLE apps_categories (\n",
      "  app_id STRING(50),\n",
      "  category_id STRING(50),\n",
      "  CONSTRAINT apps_cat_app_id_fk FOREIGN KEY(app_id) REFERENCES apps(id),\n",
      "  CONSTRAINT apps_cat_cat_id_fk FOREIGN KEY(category_id) REFERENCES categories(id),\n",
      ") PRIMARY KEY(app_id, category_id)\n"
     ]
    }
   ],
   "source": [
    "!{CLI_CONNECT} -e \"show create table apps_categories\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table\tCreate Table\n",
      "key_benefits\tCREATE TABLE key_benefits (\n",
      "  app_id STRING(50),\n",
      "  title STRING(2000),\n",
      "  description STRING(MAX),\n",
      "  CONSTRAINT key_ben_app_id_fk FOREIGN KEY(app_id) REFERENCES apps(id),\n",
      ") PRIMARY KEY(app_id, title)\n"
     ]
    }
   ],
   "source": [
    "!{CLI_CONNECT} -e \"show create table key_benefits\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table\tCreate Table\n",
      "pricing_plan_features\tCREATE TABLE pricing_plan_features (\n",
      "  pricing_plan_id STRING(50),\n",
      "  app_id STRING(50),\n",
      "  feature STRING(MAX),\n",
      "  CONSTRAINT pricing_plan_feat_app_id_fk FOREIGN KEY(app_id) REFERENCES apps(id),\n",
      "  CONSTRAINT pricing_plan_feat_plan_id_fk FOREIGN KEY(pricing_plan_id) REFERENCES pricing_plans(id),\n",
      ") PRIMARY KEY(pricing_plan_id, app_id, feature)\n"
     ]
    }
   ],
   "source": [
    "!{CLI_CONNECT} -e \"show create table pricing_plan_features\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table\tCreate Table\n",
      "reviews\tCREATE TABLE reviews (\n",
      "  app_id STRING(50),\n",
      "  author STRING(250),\n",
      "  rating INT64,\n",
      "  posted_at DATE,\n",
      "  CONSTRAINT reviews_app_id_fk FOREIGN KEY(app_id) REFERENCES apps(id),\n",
      ") PRIMARY KEY(app_id, author)\n"
     ]
    }
   ],
   "source": [
    "!{CLI_CONNECT} -e \"show create table reviews\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Optimize query by creating an index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "# An index for the apps table's rating column has been done by Professor Cohen above, leaving the reviews table's author column.\n",
    "!{CLI_CONNECT} -e \"CREATE INDEX reviews_author_idx ON reviews(author)\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "id\turl\ttitle\tdeveloper\tdeveloper_link\ticon\trating\treviews_count\tapp_id\tauthor\trating\tposted_at\n",
      "5373693a-c29e-49c5-9b59-bea48c88ed7e\thttps://apps.shopify.com/reconvert\tReConvert Post Purchase Upsell\tStilyoApps\thttps://apps.shopify.com/partners/stilyo\thttps://apps.shopifycdn.com/listing_images/66d9956edf807b697e7f539e5e9bbaf3/icon/9cc30225cab5cf16e0082596d0c17711.png?height=84&width=84\t5.000000\t603\t5373693a-c29e-49c5-9b59-bea48c88ed7e\tFunky Moose Records\t5\t2019-03-21\n",
      "ec42fb4d-8b33-4652-9f65-d8860c49ed16\thttps://apps.shopify.com/shippable\tShippable Back‑Order Manager\tJSJ.Expert\thttps://apps.shopify.com/partners/jsj-expert\thttps://apps.shopifycdn.com/listing_images/9c286192f0d1f0b6641e3098bc7701d2/icon/db16f90af3a20a11261d66bab28554e7.png?height=84&width=84\t5.000000\t2\tec42fb4d-8b33-4652-9f65-d8860c49ed16\tFunky Moose Records\t5\t2018-10-16\n",
      "3531b725-f0d8-4661-bd51-ca863d17b015\thttps://apps.shopify.com/quick-pick\tQuick Pick Pick Lists\tJSJ.Expert\thttps://apps.shopify.com/partners/jsj-expert\thttps://apps.shopifycdn.com/listing_images/b0f803948bb23b85ea90f0b4ef564b62/icon/ecc03cf47ab3cebb6ee636d5cd84af8f.png?height=84&width=84\t5.000000\t10\t3531b725-f0d8-4661-bd51-ca863d17b015\tFunky Moose Records\t5\t2018-09-12\n",
      "89f0221f-9faa-4d76-b587-13411cb909fb\thttps://apps.shopify.com/incentivise\tIncentivise Loyalty & Rewards\tJSJ.Expert\thttps://apps.shopify.com/partners/jsj-expert\thttps://apps.shopifycdn.com/listing_images/f0025941b65ac163b6a43364217d3fbb/icon/ad7c8d68abbbaccd7280e5fb7d771876.png?height=84&width=84\t5.000000\t1\t89f0221f-9faa-4d76-b587-13411cb909fb\tFunky Moose Records\t5\t2019-07-09\n"
     ]
    }
   ],
   "source": [
    "!{CLI_CONNECT} -e \"SELECT * FROM apps a JOIN reviews r ON a.id = r.app_id WHERE a.rating >= 5.0 and r.author = 'Funky Moose Records'\""
   ]
  }
 ],
 "metadata": {
  "environment": {
   "name": "common-cpu.mnightly-2021-01-20-debian-10-test",
   "type": "gcloud",
   "uri": "gcr.io/deeplearning-platform-release/base-cpu:mnightly-2021-01-20-debian-10-test"
  },
  "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.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}