L1_Exercise_2_Creating_a_Table_with_Apache_Cassandra.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Lesson 1 Exercise 2: Creating a Table with Apache Cassandra\n",
    "<img src=\"images/cassandralogo.png\" width=\"250\" height=\"250\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Walk through the basics of Apache Cassandra. Complete the following tasks:<li> Create a table in Apache Cassandra, <li> Insert rows of data,<li> Run a simple SQL query to validate the information. <br>\n",
    "`#####` denotes where the code needs to be completed."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Import Apache Cassandra python package"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import cassandra"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create a connection to the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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)\n",
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### TO-DO: Create a keyspace to do the work in "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "## TO-DO: Create the keyspace\n",
    "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": [
    "### TO-DO: Connect to the Keyspace"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "## To-Do: Add in the keyspace you created\n",
    "try:\n",
    "    session.set_keyspace('udacity')\n",
    "except Exception as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create a Song Library that contains a list of songs, including the song name, artist name, year, album it was from, and if it was a single. \n",
    "\n",
    "`song_title\n",
    "artist_name\n",
    "year\n",
    "album_name\n",
    "single`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### TO-DO: You need to create a table to be able to run the following query: \n",
    "`select * from songs WHERE year=1970 AND artist_name=\"The Beatles\"`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "## TO-DO: Complete the query below\n",
    "query = \"CREATE TABLE IF NOT EXISTS songs\"\n",
    "query = query + \"(year int, song_title varchar, artist_name varchar, album_name varchar, single boolean, PRIMARY KEY (year))\"\n",
    "try:\n",
    "    session.execute(query)\n",
    "except Exception as e:\n",
    "    print(e)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### TO-DO: Insert the following two rows in your table\n",
    "`First Row: \"1970\", \"Let It Be\", \"The Beatles\", \"Across The Universe\", \"False\", `\n",
    "\n",
    "`Second Row: \"1965\", \"Think For Yourself\", \"The Beatles\", \"Rubber Soul\", \"False\"`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Add in query and then run the insert statement\n",
    "query = \"INSERT INTO songs(year, song_title, artist_name, album_name, single)\" \n",
    "query = query + \" VALUES (%s, %s, %s, %s, %s)\"\n",
    "\n",
    "try:\n",
    "    session.execute(query, (1970, \"Let It Be\", \"The Beatles\", \"Across The Universe\", False))\n",
    "except Exception as e:\n",
    "    print(e)\n",
    "    \n",
    "try:\n",
    "    session.execute(query, (1965, \"Think For Yourself\", \"The Beatles\", \"Rubber Soul\", False))\n",
    "except Exception as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### TO-DO: Validate your data was inserted into the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1965 Rubber Soul The Beatles\n",
      "1970 Across The Universe The Beatles\n"
     ]
    }
   ],
   "source": [
    "## TO-DO: Complete and then run the select statement to validate the data was inserted into the table\n",
    "query = 'SELECT * FROM songs'\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.album_name, row.artist_name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### TO-DO: Validate the Data Model with the original query.\n",
    "\n",
    "`select * from songs WHERE YEAR=1970 AND artist_name=\"The Beatles\"`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1970 Across The Universe The Beatles\n"
     ]
    }
   ],
   "source": [
    "##TO-DO: Complete the select statement to run the query \n",
    "query = \"select * from songs WHERE YEAR=1970 AND artist_name='The Beatles' ALLOW FILTERING\"\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.album_name, row.artist_name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### And Finally close the session and cluster connection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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": 4
}