Week12/notebook/intro2db.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<DIV ALIGN=CENTER>\n",
    "\n",
    "# Introduction to Data Persistence\n",
    "## Professor Robert J. Brunner\n",
    "  \n",
    "</DIV>  \n",
    "-----\n",
    "-----"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Introduction\n",
    "\n",
    "Persisting data is an important task, and not just for data science\n",
    "applications. Programs may need to persist data to ensure state, to\n",
    "share information, and to improve performance. As a result, many\n",
    "different approaches exist for saving data, spanning everything from\n",
    "basic file input/output techniques to enterprise level database\n",
    "management software. In this lesson, we explore some of these different\n",
    "techniques with the goal of leveraging them to facilitate data science\n",
    "investigations.\n",
    "\n",
    "-----"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data Persistence Techniques\n",
    "\n",
    "We have already discussed the simplest persistence technique, basic file\n",
    "input/output in previous lessons. By using the Python programming\n",
    "language, you can open a file for reading and writing and even use\n",
    "binary mode to save storage space (or even directly use a compression\n",
    "technique by using the appropriate Python library like bzip2). To\n",
    "recall, the following code segment demonstrates how to write data to a\n",
    "file called _test.dat_.\n",
    "\n",
    "-----"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "data = \"\"\"Data to write to the file, which can easily include any Python datatype \n",
    "by using string formatting techniques.\"\"\"\n",
    "    \n",
    "with open('test.dat', 'w') as fout:\n",
    "    fout.write(data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-----\n",
    "\n",
    "and we can easily read data back into a Python program (and in this\n",
    "example, simply echo the text to _STDOUT_) in a similar manner:\n",
    "\n",
    "-----\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data to write to the file, which can easily include any Python datatype \n",
      "\n",
      "by using string formatting techniques.\n"
     ]
    }
   ],
   "source": [
    "with open('test.dat', 'r') as fin:\n",
    "    for line in fin:\n",
    "        print(line)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-----\n",
    "\n",
    "While this works, it is not optimal for several reasons:\n",
    "\n",
    "1. All data is written and read as Python strings. Complex arrangements\n",
    "of heterogenous data thus require potentially complex (and costly in\n",
    "execution time) transformations.\n",
    "\n",
    "2. All _concurrency_ is provides by the file system, thus we are not\n",
    "guaranteed consistent results if multiple writers work at the same time.\n",
    "\n",
    "3. Without extra effort, for example, to write to a binary file or to\n",
    "employ compression, this approach is costly in terms of storage space.\n",
    "\n",
    "4. We rely completely on the underlying file system for _consistency_ and\n",
    "_durability_. Thus, persisted application state may have unintentional\n",
    "dependencies on the underlying file system.\n",
    "\n",
    "An alternative approach is available for more advanced data structures,\n",
    "like the NumPy array. \n",
    "\n",
    "```python\n",
    "data = np.genfromtxt('rppdm/data/airports.csv', names=True, \n",
    "    delimiter=',', dtype=None, invalid_raise=False)\n",
    "```\n",
    "\n",
    "And if we develop our own data types, we can create our own custom\n",
    "input/output routines to read/write any new objects we have created. But\n",
    "this can be a lot of extra work, especially to verify the routines work\n",
    "accurately as a program is continually developed or maintained.\n",
    "Furthermore, this doesn't solve all of the problems as we still rely\n",
    "entirely on the file system to maintain concurrency, consistency, and\n",
    "durability. \n",
    "\n",
    "-----"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Pickling\n",
    "\n",
    "Fortunately, Python provides a simple technique, called _pickling_, that\n",
    "we can use to easily save data to a file and to later reconstitute the\n",
    "data into a Python program. Pickling writes the _class_ information for\n",
    "any data being written to the file along with the data. When you\n",
    "_unpickle_ data, this class information is used to properly reconstitute\n",
    "the data in the pickled file. Pickling is easy to use and can often\n",
    "suffice for simple data persistence tasks. To pickle data to a file, you\n",
    " must import the pickle module and open a file in binary writing mode.\n",
    "After this, simply call the `pickle.dump()` method with the data to\n",
    "write and the file stream.\n",
    "\n",
    "-----"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pickle\n",
    "\n",
    "data = np.random.rand(100)\n",
    "\n",
    "with open('test.p', 'wb') as fout:\n",
    "    pickle.dump(data, fout)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-----\n",
    "\n",
    "Unpickling data is also easy, simply open the appropriate file in binary\n",
    "read mode and call the `pickle.load()` method to retrieve the data from\n",
    "the file and assign to a variable.\n",
    "\n",
    "-----"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[ 0.93906     0.67149937  0.90741164  0.07251423  0.69469563]\n"
     ]
    }
   ],
   "source": [
    "with open('test.p', 'rb') as fin:\n",
    "    newData = pickle.load(fin)\n",
    "\n",
    "print(newData[0:20:4])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-rw-r--r-- 1 data_scientist staff 111 Nov  6 23:09 test.dat\r\n",
      "-rw-r--r-- 1 data_scientist staff 958 Nov  6 23:09 test.p\r\n"
     ]
    }
   ],
   "source": [
    "!ls -l test*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-----\n",
    "\n",
    "While easier than custom read/write routines, pickling still requires\n",
    "the file system to provide support for concurrency, consistency, and\n",
    "durability. To go any further with data persistence, we need to move\n",
    "beyond Python language constructs and employ additional software tools. \n",
    "\n",
    "-----"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Database Systems"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Whether you realize it or not, as you surf the Internet you're\n",
    "interacting with a variety of database-backed Web applications. This\n",
    "nomenclature may be unfamiliar, but it simply means that a website you\n",
    "visit is dynamically created using data saved in a database. To\n",
    "demonstrate, consider the following types of Web sites that you may\n",
    "visit:\n",
    "\n",
    "- An information portal, like the [Yahoo][1]\n",
    "\n",
    "- A newspaper Web site to catch up on the local news or sports\n",
    "\n",
    "- A financial Web site, like that of a bank or investment institution,\n",
    "to monitor your financial portfolio\n",
    "\n",
    "- A map website to find driving directions\n",
    "\n",
    "- A search engine where you can identify interesting Web sites for more\n",
    "detailed information on a subject\n",
    "\n",
    "Each of these examples use databases to store, locate, and retrieve\n",
    "information dynamically. In each of these applications, the website\n",
    "collects necessary information from the user (such as a street address),\n",
    "queries the application database, and collects the data that has been\n",
    "requested into a suitable visual result.\n",
    "\n",
    "Many of these database systems are large and complex-imagine holding all\n",
    "the map information needed to provide accurate driving directions with\n",
    "pictures! Clearly, storing data and making it available to applications\n",
    "is a big task, one that has been addressed by a number of commercial\n",
    "vendors, that provide different solutions that are optimized for\n",
    "different tasks. Many of these open-source or commercial database\n",
    "systems provide full, enterprise-class capabilities. As a result, they\n",
    "can hold enormous quantities of data, concurrently interact with a large\n",
    "number of users, and scale across large computational systems.\n",
    "\n",
    "We can broadly classify these systems into two categories:\n",
    "\n",
    "1. Relational Database Management Systems like the open-source [MySQL][2] and [PostgreSQL][3], and commercial systems like [IBM DB2][4], [Microsoft SQL Server][5], or [Oracle Database][6] that rely on a tabular data model.\n",
    "\n",
    "2. NoSQL (or _Not only SQL_) systems that abondon the tabular data model to achieve a simpler design, better scaling or higher availability than is traditionally possible with relational databases. NoSQL databases can be classifid based on their data model, and include key-store databases like Amazon's [Dynamo][7], Object Databases like [ZopeDB][8], Document Store databases like [MongoDB][9], and Column Databases like  [Cassandra][10] or [HBase][11], which is an open source implementation of Google's [BigTable][bt] model.\n",
    "\n",
    "While the NoSQL databases are extremely interesting, many of them have\n",
    "been developed to meet the **big data** challenges faced by companies\n",
    "like Google, Facebook, or Amazon, for the rest of this week's lessons,\n",
    "we will focus on relational database systems.\n",
    "\n",
    "\n",
    "-----\n",
    "[rdb]: https://en.wikipedia.org/wiki/Relational_database\n",
    "[nosql]: https://en.wikipedia.org/wiki/NoSQL\n",
    "[1]: http://yahoo.com\n",
    "[2]: https://www.mysql.com\n",
    "[3]: http://www.postgresql.org\n",
    "[4]: http://www-01.ibm.com/software/data/db2/\n",
    "[5]: http://www.microsoft.com/en-us/server-cloud/products/sql-server/\n",
    "[6]: https://www.oracle.com/database/index.html\n",
    "[7]: https://aws.amazon.com/dynamodb/\n",
    "[8]: http://www.zodb.org/en/latest/\n",
    "[9]: https://www.mongodb.org\n",
    "[bt]: https://en.wikipedia.org/wiki/BigTable\n",
    "[10]: https://cassandra.apache.org\n",
    "[11]: https://hbase.apache.org"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Database Roles\n",
    "\n",
    "As you might expect, working with these systems isn't trivial, and they\n",
    "can be expensive to operate. Historically, the tasks involved in working\n",
    "with these databases have been divided into three categories. Although\n",
    "the roles sometimes overlap, their individual responsibilities are easy\n",
    "to comprehend:\n",
    "\n",
    "**Database administrator (DBA)**: Responsible for the overall operation\n",
    "of the database system, which includes the selection and layout of the\n",
    "underlying hardware, the installation and optimization of the database\n",
    "server (especially given the hardware being used), and the day-to-day\n",
    "operations of the database server, such as data backup and recovery.\n",
    "\n",
    "**Database developer**: Responsible for the actual databases in\n",
    "operation, including designing databases, schemas, tables, table\n",
    "relationships, and indexes as well as optimizing queries.\n",
    "\n",
    "**Database application developer**: Responsible for integrating\n",
    "application code with the underlying database by using database\n",
    "application programming interfaces (APIs) to store and retrieve data as\n",
    "necessary.\n",
    "\n",
    "If the previous discussion leaves you feeling intimidated, that's OK,\n",
    "working with databases has historically been difficult. To understand\n",
    "why, let's examine a specific example in more detail: online banking.\n",
    "When you connect to your bank's Web site, you provide your credentials\n",
    "(most likely a username and password) and thereby gain access to your\n",
    "financial accounts. You can view your data, pay bills, and transfer\n",
    "funds. The database your bank uses must quickly locate the relevant\n",
    "information, safely manage the transactions, securely interact with\n",
    "users, and *most important* not lose any data! And the bank must do\n",
    "this for a large number of users concurrently. To ensure these tasks are\n",
    "performed correctly, relational database systems are given a special\n",
    "test, known as the **ACID Test**.\n",
    "\n",
    "-----"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The ACID Test\n",
    "\n",
    "Diamonds are obviously a valuable commodity,so valuable that\n",
    "counterfeits are a serious concern. One simple and (at least, in the\n",
    "movies) popular test to determine whether a diamond is real is to run it\n",
    "across a piece of glass. Because diamonds are one of the hardest\n",
    "materials known, a real diamond easily cuts the glass surface; a fake,\n",
    "especially if it's made of glass itself, won't.\n",
    "\n",
    "To a software developer, databases are equally valuable. If you use a\n",
    "database, you want to be sure it will safely store your data and let you\n",
    "easily retrieve the data later. You also want your database to allow\n",
    "multiple programs (or people) to work with the database without\n",
    "interfering with each other. To demonstrate, imagine you own a bank. The\n",
    "database for your bank must do the following, among other things:\n",
    "\n",
    "- Safely store the appropriate data\n",
    "- Quickly retrieve the appropriate data\n",
    "- Support multiple, concurrent user sessions\n",
    "\n",
    "These tasks can be collectively referred to as the ACID test; ACID is an\n",
    "acronym for Atomicity, Consistency, Isolation, and Durability.\n",
    "\n",
    "**Atomicity** means that operations with the database can be grouped\n",
    "together and treated as a single unit.\n",
    "\n",
    "**Consistency** guarantees that either all the operations in this single\n",
    "unit (or transaction) are performed successfully, or none of them is\n",
    "performed. In other words, a database can't be in an unfinished state.\n",
    "To understand why these characteristics are important, think about a\n",
    "bank transaction during which money is transferred from a savings\n",
    "account into a checking account. If the transfer process fails after\n",
    "subtracting the money from your savings account and before it was added\n",
    "to your checking account, you would become poorer, and the bank would\n",
    "have an angry (ex)customer! Atomicity enables the two operations -- the\n",
    "subtraction from the savings account and the addition to the checking\n",
    "account -- to be treated as a single transaction. Consistency guarantees\n",
    "that both operations in the transaction either succeed or fail. That\n",
    "way, your money isn't lost.\n",
    "\n",
    "**Isolation** means that independent sets of database transactions are\n",
    "performed in such a way that they don't conflict with each other.\n",
    "Continuing the bank analogy, consider two customers who transfer funds\n",
    "between accounts at the same time. The database must track both\n",
    "transfers separately; otherwise, the funds could go into the wrong\n",
    "accounts, and the bank might be left with two angry (ex)customers.\n",
    "\n",
    "**Durability** guarantees that the database is safe against unexpected\n",
    "terminations. It may be a minor inconvenience if your television or\n",
    "computer won't work when the power goes out, but the same can't be said\n",
    "for a database. If the bank's computers lose power when transferring\n",
    "your funds, you won't be a happy customer if the transaction is lost.\n",
    "Durability guarantees that if the database terminates abnormally during\n",
    "a funds transfer, then when the database is brought back up, it will be\n",
    "able to recover the transaction and continue with normal operations.\n",
    "\n",
    "Passing the ACID test is nontrivial, and many simple databases fall\n",
    "short. For critical e-business or Web-based applications, passing the\n",
    "ACID test is a must. This is one of the reasons so many companies and\n",
    "individuals utilize enterprise-level database systems, such as IBM DB2,\n",
    "Oracle Database, or Microsoft SQL Server. These databases are fully\n",
    "compliant with the ACID test, and can meet many of the data persistence\n",
    "needs of large corporations or organizations. To do so, however, often\n",
    "requires a large team that includes database administrators, database\n",
    "developers, and database application developers to ensure that data is\n",
    "effectively persisted and available as necessary for business\n",
    "applications.\n",
    "\n",
    "-----"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### SQLite\n",
    "\n",
    "But not all applications are this demanding, especially when you're\n",
    "starting out and trying to learn the basic relational database concepts.\n",
    "If you're just learning to work with databases, or if you want to\n",
    "quickly prototype a database application, most commercial database\n",
    "systems can be cumbersome. Fortunately, open-source, ACID compliant\n",
    "database systems exist, including the zero-configuration, serverless\n",
    "relational database system known as [SQLite][1]. By using SQLite, you\n",
    "can learn to work with a relational database by using SQL as well as the\n",
    "Python programming language. If you later find your application needs\n",
    "require a more powerful database system, you can always migrate your\n",
    "efforts to a more powerful database system; however, many systems\n",
    "continue to embed SQLite within their own applications, as demonstrated\n",
    "in the following webpage.\n",
    "\n",
    "-----\n",
    "\n",
    "[1]: https://www.sqlite.org"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe src=https://www.sqlite.org/famous.html width=750 height=300></iframe>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from IPython.display import HTML\n",
    "HTML('<iframe src=https://www.sqlite.org/famous.html width=750 height=300></iframe>')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### What is SQLite?\n",
    "\n",
    "SQLite is quite different than traditional relational database\n",
    "systems. SQLite does not have a separate server process, instead SQLite\n",
    "is a software library that, as the website states:\n",
    "\n",
    "> implements a self-contained, serverless, zero-configuration,\n",
    "> transactional SQL database engine.\n",
    "\n",
    "Before progressing, lets examine each of these concepts in turn:\n",
    "\n",
    "- *self-contained*: Nothing else is needed to use SQLite but the\n",
    "software library. Since, by default,  this comes with Python, we can use\n",
    "SQLite without any additional software downloads or installs. in\n",
    "addition, if you want to embed SQLite in your own application, you can\n",
    "obtain a single ANSI-C file that contains the entire SQLite library.\n",
    "\n",
    "- *serverless*: We interact with the SQLite database by using the\n",
    "SQLite library. The database is stored in a single file that is platform\n",
    "independent (so you can simply copy it over to a new machine with no\n",
    "further effort).\n",
    "\n",
    "- *zero-configuration*: SQLite does not use a server process, so there\n",
    "is no configuration required. While you can customize sqlite to change\n",
    "[default limits][1], for most applications this is unnecessary. You can\n",
    "also pre-specify certain options for the `sqlite3` command line client\n",
    "in a separate configuration file (e.g., `.sqliterc`, which is located in\n",
    "the current user's home directory).\n",
    "\n",
    "- *transactional*: A transaction is a logical set of operations. SQLite\n",
    "is ACID-complaint by implementing [atomic commits][2], which means that\n",
    "either every operation within the transaction completes successfully or\n",
    "none of them do. No partial writes are persisted, so that the database\n",
    "is always in a consistent state.\n",
    "\n",
    "With this power, it is even more surprising that the SQLite library is\n",
    "quite small, and can be compacted to as small as **300 kb** if required.\n",
    "\n",
    "SQLite by default will store data in a single database file; however, it\n",
    "can also be used as an _in memory_ database. SQLite has been distributed\n",
    "as a component within the Python language for many years, but also has a\n",
    "stand-alone command line interface client, called `sqlite3` that we will\n",
    "use in this lesson use to create a database, create schema within that\n",
    "database, and to import data.\n",
    "\n",
    "-----\n",
    "[1]: https://www.sqlite.org/limits.html\n",
    "[2]: https://www.sqlite.org/atomiccommit.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Additional References\n",
    "\n",
    "1. [SQLite Documentation][1]\n",
    "2. [Free SQLite][2] Tutorial\n",
    "-----\n",
    "\n",
    "[1]: http://www.sqlite.org/docs.html\n",
    "[2]: http://zetcode.com/db/sqlite/\n",
    "\n"
   ]
  }
 ],
 "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.4.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}