Lesson 3 Exercise 2 Primary Key.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Lesson 3 Exercise 2: Focus on Primary Key\n",
    "<img src=\"images/cassandralogo.png\" width=\"250\" height=\"250\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Walk through the basics of creating a table with a good Primary Key in Apache Cassandra, inserting rows of data, and doing a simple CQL query to validate the information. \n",
    "\n",
    "### Replace ##### with your own answers. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### We will use a python wrapper/ python driver called cassandra to run the Apache Cassandra queries. This library should be preinstalled but in the future to install this library you can run this command in a notebook to install locally: \n",
    "! pip install cassandra-driver\n",
    "#### More documentation can be found here:  https://datastax.github.io/python-driver/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Import Apache Cassandra python package"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import cassandra"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create a connection to the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "from cassandra.cluster import Cluster\n",
    "try: \n",
    "    cluster = Cluster(['127.0.0.1']) #If you have a locally installed Apache Cassandra instance\n",
    "    session = cluster.connect()\n",
    "except Exception as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create a keyspace to work in "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    session.execute(\"\"\"\n",
    "    CREATE KEYSPACE IF NOT EXISTS udacity \n",
    "    WITH REPLICATION = \n",
    "    { 'class' : 'SimpleStrategy', 'replication_factor' : 1 }\"\"\"\n",
    ")\n",
    "\n",
    "except Exception as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Connect to the Keyspace. Compare this to how we had to create a new session in PostgreSQL.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    session.set_keyspace('udacity')\n",
    "except Exception as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Imagine you need to create a new Music Library of albums \n",
    "\n",
    "### Here is the information asked of the data:\n",
    "#### 1. Give every album in the music library that was created by a given artist\n",
    "```sql\n",
    "SELECT * FROM music_library WHERE artist_name=”The Beatles”\n",
    "```\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Here is the collection of data\n",
    "<img src=\"images/table3.png\" width=\"650\" height=\"350\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Practice by making the PRIMARY KEY only 1 Column (not 2 or more)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<cassandra.cluster.ResultSet at 0x7a8138cce390>"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.execute(\"DROP TABLE music_library1\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"CREATE TABLE IF NOT EXISTS music_library \"\n",
    "query = query + \"(year int, artist_name text, album_name text, city text, PRIMARY KEY (artist_name))\"\n",
    "try:\n",
    "    session.execute(query)\n",
    "except Exception as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Let's insert the data into the table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"INSERT INTO music_library (year, artist_name, album_name, city)\"\n",
    "query = query + \" VALUES (%s, %s, %s, %s)\"\n",
    "\n",
    "try:\n",
    "    session.execute(query, (1970, \"The Beatles\", \"Let It Be\", \"Liverpool\"))\n",
    "except Exception as e:\n",
    "    print(e)\n",
    "    \n",
    "try:\n",
    "    session.execute(query, (1965, \"The Beatles\", \"Rubber Soul\", \"Oxford\"))\n",
    "except Exception as e:\n",
    "    print(e)\n",
    "    \n",
    "try:\n",
    "    session.execute(query, (1965, \"The Who\", \"My Generation\", \"London\"))\n",
    "except Exception as e:\n",
    "    print(e)\n",
    "\n",
    "try:\n",
    "    session.execute(query, (1966, \"The Monkees\", \"The Monkees\", \"Los Angeles\"))\n",
    "except Exception as e:\n",
    "    print(e)\n",
    "\n",
    "try:\n",
    "    session.execute(query, (1970, \"The Carpenters\", \"Close To You\", \"San Diego\"))\n",
    "except Exception as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Validate the Data Model -- Does it give you two rows?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1965 The Beatles Rubber Soul Oxford\n"
     ]
    }
   ],
   "source": [
    "query = \"SELECT * FROM music_library WHERE artist_name = 'The Beatles'\"\n",
    "try:\n",
    "    rows = session.execute(query)\n",
    "except Exception as e:\n",
    "    print(e)\n",
    "    \n",
    "for row in rows:\n",
    "    print (row.year, row.artist_name, row.album_name, row.city)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### If you used just one column as your PRIMARY KEY, your output should be:\n",
    "1965 The Beatles Rubber Soul Oxford\n",
    "\n",
    "\n",
    "### That didn't work out as planned! Why is that?  Did you create a unique primary key?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Try again - Create a new table with a composite key this time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"CREATE TABLE IF NOT EXISTS music_library1\"\n",
    "query = query + \"(year int, artist_name text, album_name text, city text, PRIMARY KEY(artist_name, year))\"\n",
    "try:\n",
    "    session.execute(query)\n",
    "except Exception as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "## You can opt to change the sequence of columns to match your composite key. \\ \n",
    "## Make sure to match the values in the INSERT statement\n",
    "\n",
    "query = \"INSERT INTO music_library1 (year, artist_name, album_name, city)\"\n",
    "query = query + \" VALUES (%s, %s, %s, %s)\"\n",
    "\n",
    "try:\n",
    "    session.execute(query, (1970, \"The Beatles\", \"Let It Be\", \"Liverpool\"))\n",
    "except Exception as e:\n",
    "    print(e)\n",
    "    \n",
    "try:\n",
    "    session.execute(query, (1965, \"The Beatles\", \"Rubber Soul\", \"Oxford\"))\n",
    "except Exception as e:\n",
    "    print(e)\n",
    "    \n",
    "try:\n",
    "    session.execute(query, (1965, \"The Who\", \"My Generation\", \"London\"))\n",
    "except Exception as e:\n",
    "    print(e)\n",
    "\n",
    "try:\n",
    "    session.execute(query, (1966, \"The Monkees\", \"The Monkees\", \"Los Angeles\"))\n",
    "except Exception as e:\n",
    "    print(e)\n",
    "\n",
    "try:\n",
    "    session.execute(query, (1970, \"The Carpenters\", \"Close To You\", \"San Diego\"))\n",
    "except Exception as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Validate the Data Model -- Did it work?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1965 The Beatles Rubber Soul Oxford\n",
      "1970 The Beatles Let It Be Liverpool\n"
     ]
    }
   ],
   "source": [
    "query = \"SELECT * FROM music_library1 WHERE artist_name = 'The Beatles'\"\n",
    "try:\n",
    "    rows = session.execute(query)\n",
    "except Exception as e:\n",
    "    print(e)\n",
    "    \n",
    "for row in rows:\n",
    "    print (row.year, row.artist_name, row.album_name, row.city)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Your output should be:\n",
    "1970 The Beatles Let it Be Liverpool<br>\n",
    "1965 The Beatles Rubber Soul Oxford"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Drop the tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Error from server: code=2200 [Invalid query] message=\"unconfigured table music_library\"\n",
      "Error from server: code=2200 [Invalid query] message=\"unconfigured table music_library1\"\n"
     ]
    }
   ],
   "source": [
    "query = \"DROP TABLE music_library\"\n",
    "try:\n",
    "    rows = session.execute(query)\n",
    "except Exception as e:\n",
    "    print(e)\n",
    "\n",
    "query = \"DROP TABLE music_library1\"\n",
    "try:\n",
    "    rows = session.execute(query)\n",
    "except Exception as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Close the session and cluster connection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "session.shutdown()\n",
    "cluster.shutdown()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}