Skip to content

Instantly share code, notes, and snippets.

@codeaperature

codeaperature/DataFramesSparkJDBCPostgreSQL.ipynb

Last active February 11, 2019 19:15
Show Gist options
  • Save codeaperature/b3b007da4c5817ec9fa803b973ef1c25 to your computer and use it in GitHub Desktop.
Save codeaperature/b3b007da4c5817ec9fa803b973ef1c25 to your computer and use it in GitHub Desktop.
Download ZIP
Spark Scala DataFrames Example with JSON, PostgreSQL & Rest
Display the source blob
Display the rendered blob
Raw
DataFramesSparkJDBCPostgreSQL.ipynb
{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Spark Scala DataFrames with JSON, PostgreSQL & Rest\n",
"\n",
"### Stephan Warren - stephanwarren at ymail\n",
"\n",
"Here are some examples of how to use Spark to pull JSON data from a website parse some data and save that data to a PostgreSQL database over JDBC. The goal is to get a general idea of how to do these operations. \n",
"\n",
"Pre-requisite: Prior knowledge of DataFrames some Spark and some Scala are helpful. \n",
"\n",
"This example builds on another example from https://github.com/codeaperature/LoadFredObservations where another Scala, JDBC and JSON approach was taken taken to read data line-by-line in Scala without the use of Spark or DataFrames.\n",
"\n",
"\n",
"\n",
"## The Data Set\n",
"\n",
"Start by pulling in data from a JSON source. The means acquiring the data from a URL (Rest API). A blog by Alvin Alexander at: \n",
"\n",
"http://alvinalexander.com/scala/how-to-write-scala-http-get-request-client-source-fromurl\n",
"\n",
"contains the following code (and a simpler method without using a timeout):"
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"\n",
"def get(url: String,\n",
" connectTimeout:Int =5000,\n",
" readTimeout:Int =5000,\n",
" requestMethod: String = \"GET\") = {\n",
" import java.net.{HttpURLConnection, URL}\n",
" val connection = (new URL(url)).openConnection.asInstanceOf[HttpURLConnection]\n",
" connection.setConnectTimeout(connectTimeout)\n",
" connection.setReadTimeout(readTimeout)\n",
" connection.setRequestMethod(requestMethod)\n",
" val inputStream = connection.getInputStream\n",
" val content = scala.io.Source.fromInputStream(inputStream).mkString\n",
" if (inputStream != null) inputStream.close\n",
" content\n",
"}\n",
" "
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Pull the Data\n",
"\n",
"Time to pull the data. The following is a light read about the data how the data is acquired. It's safe to skim this section as it's intended more as a reference rather than a train of require information for understanding the basic process. \n",
"\n",
"In this example, JSON data from the Federal Reserve Bank of St. Louis is used. For this example, if it's interesting to read API, this can be found at:\n",
"\n",
"https://research.stlouisfed.org/docs/api/fred/\n",
"\n",
"In this case, the data is coming from the Fed's UNRATE table (US Civilian Unemployment Rate). Here is more on that API:\n",
"\n",
"https://research.stlouisfed.org/docs/api/fred/series_observations.html\n",
"\n",
"and an example of what the JSON request returns:\n",
"\n",
"https://research.stlouisfed.org/docs/api/fred/series_observations.html#example_json\n",
"\n",
"If you want to get your own API key, please go to:\n",
"\n",
"https://research.stlouisfed.org/useraccount/register/step1\n",
"\n",
"\n",
"As mentioned above, it's safe skip to this point.\n",
"\n",
"### What does this data look like? \n",
"\n",
"A pretty-printed partial version of the data acquired within a browser looks like this:\n",
"\n",
"```\n",
"{\"realtime_start\":\"2016-04-03\",\n",
"\"realtime_end\":\"2016-04-03\",\n",
"\"observation_start\":\"1776-07-04\",\n",
"\"observation_end\":\"9999-12-31\",\n",
"\"units\":\"lin\",\n",
"\"output_type\":1,\n",
"\"file_type\":\"json\",\n",
"\"order_by\":\"observation_date\",\n",
"\"sort_order\":\"asc\",\n",
"\"count\":819,\"offset\":0,\n",
"\"limit\":100000,\n",
"\"observations\":[\n",
" {\"realtime_start\":\"2016-04-03\",\"realtime_end\":\"2016-04-03\",\n",
" \"date\":\"1948-01-01\",\"value\":\"3.4\"},\n",
" {\"realtime_start\":\"2016-04-03\",\"realtime_end\":\"2016-04-03\",\n",
" \"date\":\"1948-02-01\",\"value\":\"3.8\"},\n",
" {\"realtime_start\":\"2016-04-03\",\"realtime_end\":\"2016-04-03\",\n",
" \"date\":\"1948-03-01\",\"value\":\"4.0\"},\n",
" {\"realtime_start\":\"2016-04-03\",\"realtime_end\":\"2016-04-03\",\n",
" \"date\":\"1948-04-01\",\"value\":\"3.9\"},\n",
" {\"realtime_start\":\"2016-04-03\",\"realtime_end\":\"2016-04-03\",\n",
" \"date\":\"1948-05-01\",\"value\":\"3.5\"},\n",
" ...\n",
" ...\n",
" ...\n",
" {\"realtime_start\":\"2016-04-03\",\"realtime_end\":\"2016-04-03\",\n",
" \"date\":\"2016-02-01\",\"value\":\"4.9\"},\n",
" {\"realtime_start\":\"2016-04-03\",\"realtime_end\":\"2016-04-03\",\n",
" \"date\":\"2016-03-01\",\"value\":\"5.0\"}\n",
" ]\n",
"}\n",
"```\n",
"\n",
"OK ... Geting the data can be done as follows. It can be noted that sys.env is an environment variable that holds the API key. (In .bash_profile, ```EXPORT FRED_API_KEY=_KEY_STRING_VALUE_```)"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
" val content = get(\"https://api.stlouisfed.org/fred/series/observations?series_id=%s&api_key=%s&file_type=json\".\n",
" format(\"UNRATE\", sys.env(\"FRED_API_KEY\"))\n",
" )"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Is the content what was expected? "
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/plain": [
"{\"realtime_start\":\"2016-04-03\",\"realtime_end\":\"2016-04-03\",\"observation_start\":\"1776-07-04\",\"observation_end\":\"9999-12-31\",\"units\":\"lin\",\"output_type\":1,\"file_type\":\"json\",\"order_by\":\"observation_date\",\"sort_order\":\"asc\",\"count\":819,\"offset\":0,\"limit\":100000,\"observations\":[{\"realtime_start\":\"2016-04-03\",\"realtime_end\":\"2016-04-03\",\"date\":\"1948-01-01\",\"value\":\"3.4\"},{\"realtime_start\":\"2016-04-03\",\"realtime_end\":\"2016-04-03\",\"date\":\"1948-02-01\",\"value\":\"3.8\"},{\"realtime_start\":\"2016-04-03\",\"realtime_end\":\"2016-04-03\",\"date\":\"1948-03-01\",\"value\":\"4.0\"},{\"realtime_start\":\"2016-04-03\",\"realtime_end\":\"2016-04-03\",\"date\":\"1948-04-01\",\"value\":\"3.9\"},{\"realtime_start\":\"2016-04-03\",\"realtime_end\":\"2016-04-03\",\"date\":\"1948-05-01\",\"value\":\"3.5\"},{\"realtime_start\":\"2016-04-03\",\"realt..."
]
},
"execution_count": 3,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"content.toString"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Converting a JSON string to DataFrame \n",
"First, the string will be made into an RDD with parallelize, when the RDD is created, the RDD is converted into a DataFrame."
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"val ev = sc.parallelize(content.toString::Nil)\n",
"val df = sqlContext.read.json(ev)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Again ... Is the content what was expected? \n",
"\n",
"The DataFrame show method provides a sample of this data, but with such wide columns, it's hard to see what you really have:"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"+-----+---------+------+---------------+-----------------+--------------------+------+----------------+-----------+------------+--------------+----------+-----+\n",
"|count|file_type| limit|observation_end|observation_start| observations|offset| order_by|output_type|realtime_end|realtime_start|sort_order|units|\n",
"+-----+---------+------+---------------+-----------------+--------------------+------+----------------+-----------+------------+--------------+----------+-----+\n",
"| 819| json|100000| 9999-12-31| 1776-07-04|[[1948-01-01,2016...| 0|observation_date| 1| 2016-04-03| 2016-04-03| asc| lin|\n",
"+-----+---------+------+---------------+-----------------+--------------------+------+----------------+-----------+------------+--------------+----------+-----+\n",
"\n"
]
}
],
"source": [
"df.show"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The DataFrame collect, iterate & print methods below provides another view of this data. It's easier to see the acutal data. (Note that the column headers disappeared.)"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"[819,json,100000,9999-12-31,1776-07-04,WrappedArray([1948-01-01,2016-04-03,2016-04-03,3.4], [1948-02-01,2016-04-03,2016-04-03,3.8], [1948-03-01,2016-04-03,2016-04-03,4.0], [1948-04-01,2016-04-03,2016-04-03,3.9], [1948-05-01,2016-04-03,2016-04-03,3.5], [1948-06-01,2016-04-03,2016-04-03,3.6], [1948-07-01,2016-04-03,2016-04-03,3.6], [1948-08-01,2016-04-03,2016-04-03,3.9], [1948-09-01,2016-04-03,2016-04-03,3.8], [1948-10-01,2016-04-03,2016-04-03,3.7], [1948-11-01,2016-04-03,2016-04-03,3.8], [1948-12-01,2016-04-03,2016-04-03,4.0], [1949-01-01,2016-04-03,2016-04-03,4.3], [1949-02-01,2016-04-03,2016-04-03,4.7], [1949-03-01,2016-04-03,2016-04-03,5.0], [1949-04-01,2016-04-03,2016-04-03,5.3], [1949-05-01,2016-04-03,2016-04-03,6.1], [1949-06-01,2016-04-03,2016-04-03,6.2], [1949-07-01,2016-04-03,2016-04-03,6.7], [1949-08-01,2016-04-03,2016-04-03,6.8], [1949-09-01,2016-04-03,2016-04-03,6.6], [1949-10-01,2016-04-03,2016-04-03,7.9], [1949-11-01,2016-04-03,2016-04-03,6.4], [1949-12-01,2016-04-03,2016-04-03,6.6], [1950-01-01,2016-04-03,2016-04-03,6.5], [1950-02-01,2016-04-03,2016-04-03,6.4], [1950-03-01,2016-04-03,2016-04-03,6.3], [1950-04-01,2016-04-03,2016-04-03,5.8], [1950-05-01,2016-04-03,2016-04-03,5.5], [1950-06-01,2016-04-03,2016-04-03,5.4], [1950-07-01,2016-04-03,2016-04-03,5.0], [1950-08-01,2016-04-03,2016-04-03,4.5], [1950-09-01,2016-04-03,2016-04-03,4.4], [1950-10-01,2016-04-03,2016-04-03,4.2], [1950-11-01,2016-04-03,2016-04-03,4.2], [1950-12-01,2016-04-03,2016-04-03,4.3], [1951-01-01,2016-04-03,2016-04-03,3.7], [1951-02-01,2016-04-03,2016-04-03,3.4], [1951-03-01,2016-04-03,2016-04-03,3.4], [1951-04-01,2016-04-03,2016-04-03,3.1], [1951-05-01,2016-04-03,2016-04-03,3.0], [1951-06-01,2016-04-03,2016-04-03,3.2], [1951-07-01,2016-04-03,2016-04-03,3.1], [1951-08-01,2016-04-03,2016-04-03,3.1], [1951-09-01,2016-04-03,2016-04-03,3.3], [1951-10-01,2016-04-03,2016-04-03,3.5], [1951-11-01,2016-04-03,2016-04-03,3.5], [1951-12-01,2016-04-03,2016-04-03,3.1], [1952-01-01,2016-04-03,2016-04-03,3.2], [1952-02-01,2016-04-03,2016-04-03,3.1], [1952-03-01,2016-04-03,2016-04-03,2.9], [1952-04-01,2016-04-03,2016-04-03,2.9], [1952-05-01,2016-04-03,2016-04-03,3.0], [1952-06-01,2016-04-03,2016-04-03,3.0], [1952-07-01,2016-04-03,2016-04-03,3.2], [1952-08-01,2016-04-03,2016-04-03,3.4], [1952-09-01,2016-04-03,2016-04-03,3.1], [1952-10-01,2016-04-03,2016-04-03,3.0], [1952-11-01,2016-04-03,2016-04-03,2.8], [1952-12-01,2016-04-03,2016-04-03,2.7], [1953-01-01,2016-04-03,2016-04-03,2.9], [1953-02-01,2016-04-03,2016-04-03,2.6], [1953-03-01,2016-04-03,2016-04-03,2.6], [1953-04-01,2016-04-03,2016-04-03,2.7], [1953-05-01,2016-04-03,2016-04-03,2.5], [1953-06-01,2016-04-03,2016-04-03,2.5], [1953-07-01,2016-04-03,2016-04-03,2.6], [1953-08-01,2016-04-03,2016-04-03,2.7], [1953-09-01,2016-04-03,2016-04-03,2.9], [1953-10-01,2016-04-03,2016-04-03,3.1], [1953-11-01,2016-04-03,2016-04-03,3.5], [1953-12-01,2016-04-03,2016-04-03,4.5], [1954-01-01,2016-04-03,2016-04-03,4.9], [1954-02-01,2016-04-03,2016-04-03,5.2], [1954-03-01,2016-04-03,2016-04-03,5.7], [1954-04-01,2016-04-03,2016-04-03,5.9], [1954-05-01,2016-04-03,2016-04-03,5.9], [1954-06-01,2016-04-03,2016-04-03,5.6], [1954-07-01,2016-04-03,2016-04-03,5.8], [1954-08-01,2016-04-03,2016-04-03,6.0], [1954-09-01,2016-04-03,2016-04-03,6.1], [1954-10-01,2016-04-03,2016-04-03,5.7], [1954-11-01,2016-04-03,2016-04-03,5.3], [1954-12-01,2016-04-03,2016-04-03,5.0], [1955-01-01,2016-04-03,2016-04-03,4.9], [1955-02-01,2016-04-03,2016-04-03,4.7], [1955-03-01,2016-04-03,2016-04-03,4.6], [1955-04-01,2016-04-03,2016-04-03,4.7], [1955-05-01,2016-04-03,2016-04-03,4.3], [1955-06-01,2016-04-03,2016-04-03,4.2], [1955-07-01,2016-04-03,2016-04-03,4.0], [1955-08-01,2016-04-03,2016-04-03,4.2], [1955-09-01,2016-04-03,2016-04-03,4.1], [1955-10-01,2016-04-03,2016-04-03,4.3], [1955-11-01,2016-04-03,2016-04-03,4.2], [1955-12-01,2016-04-03,2016-04-03,4.2], [1956-01-01,2016-04-03,2016-04-03,4.0], [1956-02-01,2016-04-03,2016-04-03,3.9], [1956-03-01,2016-04-03,2016-04-03,4.2], [1956-04-01,2016-04-03,2016-04-03,4.0], [1956-05-01,2016-04-03,2016-04-03,4.3], [1956-06-01,2016-04-03,2016-04-03,4.3], [1956-07-01,2016-04-03,2016-04-03,4.4], [1956-08-01,2016-04-03,2016-04-03,4.1], [1956-09-01,2016-04-03,2016-04-03,3.9], [1956-10-01,2016-04-03,2016-04-03,3.9], [1956-11-01,2016-04-03,2016-04-03,4.3], [1956-12-01,2016-04-03,2016-04-03,4.2], [1957-01-01,2016-04-03,2016-04-03,4.2], [1957-02-01,2016-04-03,2016-04-03,3.9], [1957-03-01,2016-04-03,2016-04-03,3.7], [1957-04-01,2016-04-03,2016-04-03,3.9], [1957-05-01,2016-04-03,2016-04-03,4.1], [1957-06-01,2016-04-03,2016-04-03,4.3], [1957-07-01,2016-04-03,2016-04-03,4.2], [1957-08-01,2016-04-03,2016-04-03,4.1], [1957-09-01,2016-04-03,2016-04-03,4.4], [1957-10-01,2016-04-03,2016-04-03,4.5], [1957-11-01,2016-04-03,2016-04-03,5.1], [1957-12-01,2016-04-03,2016-04-03,5.2], [1958-01-01,2016-04-03,2016-04-03,5.8], [1958-02-01,2016-04-03,2016-04-03,6.4], [1958-03-01,2016-04-03,2016-04-03,6.7], [1958-04-01,2016-04-03,2016-04-03,7.4], [1958-05-01,2016-04-03,2016-04-03,7.4], [1958-06-01,2016-04-03,2016-04-03,7.3], [1958-07-01,2016-04-03,2016-04-03,7.5], [1958-08-01,2016-04-03,2016-04-03,7.4], [1958-09-01,2016-04-03,2016-04-03,7.1], [1958-10-01,2016-04-03,2016-04-03,6.7], [1958-11-01,2016-04-03,2016-04-03,6.2], [1958-12-01,2016-04-03,2016-04-03,6.2], [1959-01-01,2016-04-03,2016-04-03,6.0], [1959-02-01,2016-04-03,2016-04-03,5.9], [1959-03-01,2016-04-03,2016-04-03,5.6], [1959-04-01,2016-04-03,2016-04-03,5.2], [1959-05-01,2016-04-03,2016-04-03,5.1], [1959-06-01,2016-04-03,2016-04-03,5.0], [1959-07-01,2016-04-03,2016-04-03,5.1], [1959-08-01,2016-04-03,2016-04-03,5.2], [1959-09-01,2016-04-03,2016-04-03,5.5], [1959-10-01,2016-04-03,2016-04-03,5.7], [1959-11-01,2016-04-03,2016-04-03,5.8], [1959-12-01,2016-04-03,2016-04-03,5.3], [1960-01-01,2016-04-03,2016-04-03,5.2], [1960-02-01,2016-04-03,2016-04-03,4.8], [1960-03-01,2016-04-03,2016-04-03,5.4], [1960-04-01,2016-04-03,2016-04-03,5.2], [1960-05-01,2016-04-03,2016-04-03,5.1], [1960-06-01,2016-04-03,2016-04-03,5.4], [1960-07-01,2016-04-03,2016-04-03,5.5], [1960-08-01,2016-04-03,2016-04-03,5.6], [1960-09-01,2016-04-03,2016-04-03,5.5], [1960-10-01,2016-04-03,2016-04-03,6.1], [1960-11-01,2016-04-03,2016-04-03,6.1], [1960-12-01,2016-04-03,2016-04-03,6.6], [1961-01-01,2016-04-03,2016-04-03,6.6], [1961-02-01,2016-04-03,2016-04-03,6.9], [1961-03-01,2016-04-03,2016-04-03,6.9], [1961-04-01,2016-04-03,2016-04-03,7.0], [1961-05-01,2016-04-03,2016-04-03,7.1], [1961-06-01,2016-04-03,2016-04-03,6.9], [1961-07-01,2016-04-03,2016-04-03,7.0], [1961-08-01,2016-04-03,2016-04-03,6.6], [1961-09-01,2016-04-03,2016-04-03,6.7], [1961-10-01,2016-04-03,2016-04-03,6.5], [1961-11-01,2016-04-03,2016-04-03,6.1], [1961-12-01,2016-04-03,2016-04-03,6.0], [1962-01-01,2016-04-03,2016-04-03,5.8], [1962-02-01,2016-04-03,2016-04-03,5.5], [1962-03-01,2016-04-03,2016-04-03,5.6], [1962-04-01,2016-04-03,2016-04-03,5.6], [1962-05-01,2016-04-03,2016-04-03,5.5], [1962-06-01,2016-04-03,2016-04-03,5.5], [1962-07-01,2016-04-03,2016-04-03,5.4], [1962-08-01,2016-04-03,2016-04-03,5.7], [1962-09-01,2016-04-03,2016-04-03,5.6], [1962-10-01,2016-04-03,2016-04-03,5.4], [1962-11-01,2016-04-03,2016-04-03,5.7], [1962-12-01,2016-04-03,2016-04-03,5.5], [1963-01-01,2016-04-03,2016-04-03,5.7], [1963-02-01,2016-04-03,2016-04-03,5.9], [1963-03-01,2016-04-03,2016-04-03,5.7], [1963-04-01,2016-04-03,2016-04-03,5.7], [1963-05-01,2016-04-03,2016-04-03,5.9], [1963-06-01,2016-04-03,2016-04-03,5.6], [1963-07-01,2016-04-03,2016-04-03,5.6], [1963-08-01,2016-04-03,2016-04-03,5.4], [1963-09-01,2016-04-03,2016-04-03,5.5], [1963-10-01,2016-04-03,2016-04-03,5.5], [1963-11-01,2016-04-03,2016-04-03,5.7], [1963-12-01,2016-04-03,2016-04-03,5.5], [1964-01-01,2016-04-03,2016-04-03,5.6], [1964-02-01,2016-04-03,2016-04-03,5.4], [1964-03-01,2016-04-03,2016-04-03,5.4], [1964-04-01,2016-04-03,2016-04-03,5.3], [1964-05-01,2016-04-03,2016-04-03,5.1], [1964-06-01,2016-04-03,2016-04-03,5.2], [1964-07-01,2016-04-03,2016-04-03,4.9], [1964-08-01,2016-04-03,2016-04-03,5.0], [1964-09-01,2016-04-03,2016-04-03,5.1], [1964-10-01,2016-04-03,2016-04-03,5.1], [1964-11-01,2016-04-03,2016-04-03,4.8], [1964-12-01,2016-04-03,2016-04-03,5.0], [1965-01-01,2016-04-03,2016-04-03,4.9], [1965-02-01,2016-04-03,2016-04-03,5.1], [1965-03-01,2016-04-03,2016-04-03,4.7], [1965-04-01,2016-04-03,2016-04-03,4.8], [1965-05-01,2016-04-03,2016-04-03,4.6], [1965-06-01,2016-04-03,2016-04-03,4.6], [1965-07-01,2016-04-03,2016-04-03,4.4], [1965-08-01,2016-04-03,2016-04-03,4.4], [1965-09-01,2016-04-03,2016-04-03,4.3], [1965-10-01,2016-04-03,2016-04-03,4.2], [1965-11-01,2016-04-03,2016-04-03,4.1], [1965-12-01,2016-04-03,2016-04-03,4.0], [1966-01-01,2016-04-03,2016-04-03,4.0], [1966-02-01,2016-04-03,2016-04-03,3.8], [1966-03-01,2016-04-03,2016-04-03,3.8], [1966-04-01,2016-04-03,2016-04-03,3.8], [1966-05-01,2016-04-03,2016-04-03,3.9], [1966-06-01,2016-04-03,2016-04-03,3.8], [1966-07-01,2016-04-03,2016-04-03,3.8], [1966-08-01,2016-04-03,2016-04-03,3.8], [1966-09-01,2016-04-03,2016-04-03,3.7], [1966-10-01,2016-04-03,2016-04-03,3.7], [1966-11-01,2016-04-03,2016-04-03,3.6], [1966-12-01,2016-04-03,2016-04-03,3.8], [1967-01-01,2016-04-03,2016-04-03,3.9], [1967-02-01,2016-04-03,2016-04-03,3.8], [1967-03-01,2016-04-03,2016-04-03,3.8], [1967-04-01,2016-04-03,2016-04-03,3.8], [1967-05-01,2016-04-03,2016-04-03,3.8], [1967-06-01,2016-04-03,2016-04-03,3.9], [1967-07-01,2016-04-03,2016-04-03,3.8], [1967-08-01,2016-04-03,2016-04-03,3.8], [1967-09-01,2016-04-03,2016-04-03,3.8], [1967-10-01,2016-04-03,2016-04-03,4.0], [1967-11-01,2016-04-03,2016-04-03,3.9], [1967-12-01,2016-04-03,2016-04-03,3.8], [1968-01-01,2016-04-03,2016-04-03,3.7], [1968-02-01,2016-04-03,2016-04-03,3.8], [1968-03-01,2016-04-03,2016-04-03,3.7], [1968-04-01,2016-04-03,2016-04-03,3.5], [1968-05-01,2016-04-03,2016-04-03,3.5], [1968-06-01,2016-04-03,2016-04-03,3.7], [1968-07-01,2016-04-03,2016-04-03,3.7], [1968-08-01,2016-04-03,2016-04-03,3.5], [1968-09-01,2016-04-03,2016-04-03,3.4], [1968-10-01,2016-04-03,2016-04-03,3.4], [1968-11-01,2016-04-03,2016-04-03,3.4], [1968-12-01,2016-04-03,2016-04-03,3.4], [1969-01-01,2016-04-03,2016-04-03,3.4], [1969-02-01,2016-04-03,2016-04-03,3.4], [1969-03-01,2016-04-03,2016-04-03,3.4], [1969-04-01,2016-04-03,2016-04-03,3.4], [1969-05-01,2016-04-03,2016-04-03,3.4], [1969-06-01,2016-04-03,2016-04-03,3.5], [1969-07-01,2016-04-03,2016-04-03,3.5], [1969-08-01,2016-04-03,2016-04-03,3.5], [1969-09-01,2016-04-03,2016-04-03,3.7], [1969-10-01,2016-04-03,2016-04-03,3.7], [1969-11-01,2016-04-03,2016-04-03,3.5], [1969-12-01,2016-04-03,2016-04-03,3.5], [1970-01-01,2016-04-03,2016-04-03,3.9], [1970-02-01,2016-04-03,2016-04-03,4.2], [1970-03-01,2016-04-03,2016-04-03,4.4], [1970-04-01,2016-04-03,2016-04-03,4.6], [1970-05-01,2016-04-03,2016-04-03,4.8], [1970-06-01,2016-04-03,2016-04-03,4.9], [1970-07-01,2016-04-03,2016-04-03,5.0], [1970-08-01,2016-04-03,2016-04-03,5.1], [1970-09-01,2016-04-03,2016-04-03,5.4], [1970-10-01,2016-04-03,2016-04-03,5.5], [1970-11-01,2016-04-03,2016-04-03,5.9], [1970-12-01,2016-04-03,2016-04-03,6.1], [1971-01-01,2016-04-03,2016-04-03,5.9], [1971-02-01,2016-04-03,2016-04-03,5.9], [1971-03-01,2016-04-03,2016-04-03,6.0], [1971-04-01,2016-04-03,2016-04-03,5.9], [1971-05-01,2016-04-03,2016-04-03,5.9], [1971-06-01,2016-04-03,2016-04-03,5.9], [1971-07-01,2016-04-03,2016-04-03,6.0], [1971-08-01,2016-04-03,2016-04-03,6.1], [1971-09-01,2016-04-03,2016-04-03,6.0], [1971-10-01,2016-04-03,2016-04-03,5.8], [1971-11-01,2016-04-03,2016-04-03,6.0], [1971-12-01,2016-04-03,2016-04-03,6.0], [1972-01-01,2016-04-03,2016-04-03,5.8], [1972-02-01,2016-04-03,2016-04-03,5.7], [1972-03-01,2016-04-03,2016-04-03,5.8], [1972-04-01,2016-04-03,2016-04-03,5.7], [1972-05-01,2016-04-03,2016-04-03,5.7], [1972-06-01,2016-04-03,2016-04-03,5.7], [1972-07-01,2016-04-03,2016-04-03,5.6], [1972-08-01,2016-04-03,2016-04-03,5.6], [1972-09-01,2016-04-03,2016-04-03,5.5], [1972-10-01,2016-04-03,2016-04-03,5.6], [1972-11-01,2016-04-03,2016-04-03,5.3], [1972-12-01,2016-04-03,2016-04-03,5.2], [1973-01-01,2016-04-03,2016-04-03,4.9], [1973-02-01,2016-04-03,2016-04-03,5.0], [1973-03-01,2016-04-03,2016-04-03,4.9], [1973-04-01,2016-04-03,2016-04-03,5.0], [1973-05-01,2016-04-03,2016-04-03,4.9], [1973-06-01,2016-04-03,2016-04-03,4.9], [1973-07-01,2016-04-03,2016-04-03,4.8], [1973-08-01,2016-04-03,2016-04-03,4.8], [1973-09-01,2016-04-03,2016-04-03,4.8], [1973-10-01,2016-04-03,2016-04-03,4.6], [1973-11-01,2016-04-03,2016-04-03,4.8], [1973-12-01,2016-04-03,2016-04-03,4.9], [1974-01-01,2016-04-03,2016-04-03,5.1], [1974-02-01,2016-04-03,2016-04-03,5.2], [1974-03-01,2016-04-03,2016-04-03,5.1], [1974-04-01,2016-04-03,2016-04-03,5.1], [1974-05-01,2016-04-03,2016-04-03,5.1], [1974-06-01,2016-04-03,2016-04-03,5.4], [1974-07-01,2016-04-03,2016-04-03,5.5], [1974-08-01,2016-04-03,2016-04-03,5.5], [1974-09-01,2016-04-03,2016-04-03,5.9], [1974-10-01,2016-04-03,2016-04-03,6.0], [1974-11-01,2016-04-03,2016-04-03,6.6], [1974-12-01,2016-04-03,2016-04-03,7.2], [1975-01-01,2016-04-03,2016-04-03,8.1], [1975-02-01,2016-04-03,2016-04-03,8.1], [1975-03-01,2016-04-03,2016-04-03,8.6], [1975-04-01,2016-04-03,2016-04-03,8.8], [1975-05-01,2016-04-03,2016-04-03,9.0], [1975-06-01,2016-04-03,2016-04-03,8.8], [1975-07-01,2016-04-03,2016-04-03,8.6], [1975-08-01,2016-04-03,2016-04-03,8.4], [1975-09-01,2016-04-03,2016-04-03,8.4], [1975-10-01,2016-04-03,2016-04-03,8.4], [1975-11-01,2016-04-03,2016-04-03,8.3], [1975-12-01,2016-04-03,2016-04-03,8.2], [1976-01-01,2016-04-03,2016-04-03,7.9], [1976-02-01,2016-04-03,2016-04-03,7.7], [1976-03-01,2016-04-03,2016-04-03,7.6], [1976-04-01,2016-04-03,2016-04-03,7.7], [1976-05-01,2016-04-03,2016-04-03,7.4], [1976-06-01,2016-04-03,2016-04-03,7.6], [1976-07-01,2016-04-03,2016-04-03,7.8], [1976-08-01,2016-04-03,2016-04-03,7.8], [1976-09-01,2016-04-03,2016-04-03,7.6], [1976-10-01,2016-04-03,2016-04-03,7.7], [1976-11-01,2016-04-03,2016-04-03,7.8], [1976-12-01,2016-04-03,2016-04-03,7.8], [1977-01-01,2016-04-03,2016-04-03,7.5], [1977-02-01,2016-04-03,2016-04-03,7.6], [1977-03-01,2016-04-03,2016-04-03,7.4], [1977-04-01,2016-04-03,2016-04-03,7.2], [1977-05-01,2016-04-03,2016-04-03,7.0], [1977-06-01,2016-04-03,2016-04-03,7.2], [1977-07-01,2016-04-03,2016-04-03,6.9], [1977-08-01,2016-04-03,2016-04-03,7.0], [1977-09-01,2016-04-03,2016-04-03,6.8], [1977-10-01,2016-04-03,2016-04-03,6.8], [1977-11-01,2016-04-03,2016-04-03,6.8], [1977-12-01,2016-04-03,2016-04-03,6.4], [1978-01-01,2016-04-03,2016-04-03,6.4], [1978-02-01,2016-04-03,2016-04-03,6.3], [1978-03-01,2016-04-03,2016-04-03,6.3], [1978-04-01,2016-04-03,2016-04-03,6.1], [1978-05-01,2016-04-03,2016-04-03,6.0], [1978-06-01,2016-04-03,2016-04-03,5.9], [1978-07-01,2016-04-03,2016-04-03,6.2], [1978-08-01,2016-04-03,2016-04-03,5.9], [1978-09-01,2016-04-03,2016-04-03,6.0], [1978-10-01,2016-04-03,2016-04-03,5.8], [1978-11-01,2016-04-03,2016-04-03,5.9], [1978-12-01,2016-04-03,2016-04-03,6.0], [1979-01-01,2016-04-03,2016-04-03,5.9], [1979-02-01,2016-04-03,2016-04-03,5.9], [1979-03-01,2016-04-03,2016-04-03,5.8], [1979-04-01,2016-04-03,2016-04-03,5.8], [1979-05-01,2016-04-03,2016-04-03,5.6], [1979-06-01,2016-04-03,2016-04-03,5.7], [1979-07-01,2016-04-03,2016-04-03,5.7], [1979-08-01,2016-04-03,2016-04-03,6.0], [1979-09-01,2016-04-03,2016-04-03,5.9], [1979-10-01,2016-04-03,2016-04-03,6.0], [1979-11-01,2016-04-03,2016-04-03,5.9], [1979-12-01,2016-04-03,2016-04-03,6.0], [1980-01-01,2016-04-03,2016-04-03,6.3], [1980-02-01,2016-04-03,2016-04-03,6.3], [1980-03-01,2016-04-03,2016-04-03,6.3], [1980-04-01,2016-04-03,2016-04-03,6.9], [1980-05-01,2016-04-03,2016-04-03,7.5], [1980-06-01,2016-04-03,2016-04-03,7.6], [1980-07-01,2016-04-03,2016-04-03,7.8], [1980-08-01,2016-04-03,2016-04-03,7.7], [1980-09-01,2016-04-03,2016-04-03,7.5], [1980-10-01,2016-04-03,2016-04-03,7.5], [1980-11-01,2016-04-03,2016-04-03,7.5], [1980-12-01,2016-04-03,2016-04-03,7.2], [1981-01-01,2016-04-03,2016-04-03,7.5], [1981-02-01,2016-04-03,2016-04-03,7.4], [1981-03-01,2016-04-03,2016-04-03,7.4], [1981-04-01,2016-04-03,2016-04-03,7.2], [1981-05-01,2016-04-03,2016-04-03,7.5], [1981-06-01,2016-04-03,2016-04-03,7.5], [1981-07-01,2016-04-03,2016-04-03,7.2], [1981-08-01,2016-04-03,2016-04-03,7.4], [1981-09-01,2016-04-03,2016-04-03,7.6], [1981-10-01,2016-04-03,2016-04-03,7.9], [1981-11-01,2016-04-03,2016-04-03,8.3], [1981-12-01,2016-04-03,2016-04-03,8.5], [1982-01-01,2016-04-03,2016-04-03,8.6], [1982-02-01,2016-04-03,2016-04-03,8.9], [1982-03-01,2016-04-03,2016-04-03,9.0], [1982-04-01,2016-04-03,2016-04-03,9.3], [1982-05-01,2016-04-03,2016-04-03,9.4], [1982-06-01,2016-04-03,2016-04-03,9.6], [1982-07-01,2016-04-03,2016-04-03,9.8], [1982-08-01,2016-04-03,2016-04-03,9.8], [1982-09-01,2016-04-03,2016-04-03,10.1], [1982-10-01,2016-04-03,2016-04-03,10.4], [1982-11-01,2016-04-03,2016-04-03,10.8], [1982-12-01,2016-04-03,2016-04-03,10.8], [1983-01-01,2016-04-03,2016-04-03,10.4], [1983-02-01,2016-04-03,2016-04-03,10.4], [1983-03-01,2016-04-03,2016-04-03,10.3], [1983-04-01,2016-04-03,2016-04-03,10.2], [1983-05-01,2016-04-03,2016-04-03,10.1], [1983-06-01,2016-04-03,2016-04-03,10.1], [1983-07-01,2016-04-03,2016-04-03,9.4], [1983-08-01,2016-04-03,2016-04-03,9.5], [1983-09-01,2016-04-03,2016-04-03,9.2], [1983-10-01,2016-04-03,2016-04-03,8.8], [1983-11-01,2016-04-03,2016-04-03,8.5], [1983-12-01,2016-04-03,2016-04-03,8.3], [1984-01-01,2016-04-03,2016-04-03,8.0], [1984-02-01,2016-04-03,2016-04-03,7.8], [1984-03-01,2016-04-03,2016-04-03,7.8], [1984-04-01,2016-04-03,2016-04-03,7.7], [1984-05-01,2016-04-03,2016-04-03,7.4], [1984-06-01,2016-04-03,2016-04-03,7.2], [1984-07-01,2016-04-03,2016-04-03,7.5], [1984-08-01,2016-04-03,2016-04-03,7.5], [1984-09-01,2016-04-03,2016-04-03,7.3], [1984-10-01,2016-04-03,2016-04-03,7.4], [1984-11-01,2016-04-03,2016-04-03,7.2], [1984-12-01,2016-04-03,2016-04-03,7.3], [1985-01-01,2016-04-03,2016-04-03,7.3], [1985-02-01,2016-04-03,2016-04-03,7.2], [1985-03-01,2016-04-03,2016-04-03,7.2], [1985-04-01,2016-04-03,2016-04-03,7.3], [1985-05-01,2016-04-03,2016-04-03,7.2], [1985-06-01,2016-04-03,2016-04-03,7.4], [1985-07-01,2016-04-03,2016-04-03,7.4], [1985-08-01,2016-04-03,2016-04-03,7.1], [1985-09-01,2016-04-03,2016-04-03,7.1], [1985-10-01,2016-04-03,2016-04-03,7.1], [1985-11-01,2016-04-03,2016-04-03,7.0], [1985-12-01,2016-04-03,2016-04-03,7.0], [1986-01-01,2016-04-03,2016-04-03,6.7], [1986-02-01,2016-04-03,2016-04-03,7.2], [1986-03-01,2016-04-03,2016-04-03,7.2], [1986-04-01,2016-04-03,2016-04-03,7.1], [1986-05-01,2016-04-03,2016-04-03,7.2], [1986-06-01,2016-04-03,2016-04-03,7.2], [1986-07-01,2016-04-03,2016-04-03,7.0], [1986-08-01,2016-04-03,2016-04-03,6.9], [1986-09-01,2016-04-03,2016-04-03,7.0], [1986-10-01,2016-04-03,2016-04-03,7.0], [1986-11-01,2016-04-03,2016-04-03,6.9], [1986-12-01,2016-04-03,2016-04-03,6.6], [1987-01-01,2016-04-03,2016-04-03,6.6], [1987-02-01,2016-04-03,2016-04-03,6.6], [1987-03-01,2016-04-03,2016-04-03,6.6], [1987-04-01,2016-04-03,2016-04-03,6.3], [1987-05-01,2016-04-03,2016-04-03,6.3], [1987-06-01,2016-04-03,2016-04-03,6.2], [1987-07-01,2016-04-03,2016-04-03,6.1], [1987-08-01,2016-04-03,2016-04-03,6.0], [1987-09-01,2016-04-03,2016-04-03,5.9], [1987-10-01,2016-04-03,2016-04-03,6.0], [1987-11-01,2016-04-03,2016-04-03,5.8], [1987-12-01,2016-04-03,2016-04-03,5.7], [1988-01-01,2016-04-03,2016-04-03,5.7], [1988-02-01,2016-04-03,2016-04-03,5.7], [1988-03-01,2016-04-03,2016-04-03,5.7], [1988-04-01,2016-04-03,2016-04-03,5.4], [1988-05-01,2016-04-03,2016-04-03,5.6], [1988-06-01,2016-04-03,2016-04-03,5.4], [1988-07-01,2016-04-03,2016-04-03,5.4], [1988-08-01,2016-04-03,2016-04-03,5.6], [1988-09-01,2016-04-03,2016-04-03,5.4], [1988-10-01,2016-04-03,2016-04-03,5.4], [1988-11-01,2016-04-03,2016-04-03,5.3], [1988-12-01,2016-04-03,2016-04-03,5.3], [1989-01-01,2016-04-03,2016-04-03,5.4], [1989-02-01,2016-04-03,2016-04-03,5.2], [1989-03-01,2016-04-03,2016-04-03,5.0], [1989-04-01,2016-04-03,2016-04-03,5.2], [1989-05-01,2016-04-03,2016-04-03,5.2], [1989-06-01,2016-04-03,2016-04-03,5.3], [1989-07-01,2016-04-03,2016-04-03,5.2], [1989-08-01,2016-04-03,2016-04-03,5.2], [1989-09-01,2016-04-03,2016-04-03,5.3], [1989-10-01,2016-04-03,2016-04-03,5.3], [1989-11-01,2016-04-03,2016-04-03,5.4], [1989-12-01,2016-04-03,2016-04-03,5.4], [1990-01-01,2016-04-03,2016-04-03,5.4], [1990-02-01,2016-04-03,2016-04-03,5.3], [1990-03-01,2016-04-03,2016-04-03,5.2], [1990-04-01,2016-04-03,2016-04-03,5.4], [1990-05-01,2016-04-03,2016-04-03,5.4], [1990-06-01,2016-04-03,2016-04-03,5.2], [1990-07-01,2016-04-03,2016-04-03,5.5], [1990-08-01,2016-04-03,2016-04-03,5.7], [1990-09-01,2016-04-03,2016-04-03,5.9], [1990-10-01,2016-04-03,2016-04-03,5.9], [1990-11-01,2016-04-03,2016-04-03,6.2], [1990-12-01,2016-04-03,2016-04-03,6.3], [1991-01-01,2016-04-03,2016-04-03,6.4], [1991-02-01,2016-04-03,2016-04-03,6.6], [1991-03-01,2016-04-03,2016-04-03,6.8], [1991-04-01,2016-04-03,2016-04-03,6.7], [1991-05-01,2016-04-03,2016-04-03,6.9], [1991-06-01,2016-04-03,2016-04-03,6.9], [1991-07-01,2016-04-03,2016-04-03,6.8], [1991-08-01,2016-04-03,2016-04-03,6.9], [1991-09-01,2016-04-03,2016-04-03,6.9], [1991-10-01,2016-04-03,2016-04-03,7.0], [1991-11-01,2016-04-03,2016-04-03,7.0], [1991-12-01,2016-04-03,2016-04-03,7.3], [1992-01-01,2016-04-03,2016-04-03,7.3], [1992-02-01,2016-04-03,2016-04-03,7.4], [1992-03-01,2016-04-03,2016-04-03,7.4], [1992-04-01,2016-04-03,2016-04-03,7.4], [1992-05-01,2016-04-03,2016-04-03,7.6], [1992-06-01,2016-04-03,2016-04-03,7.8], [1992-07-01,2016-04-03,2016-04-03,7.7], [1992-08-01,2016-04-03,2016-04-03,7.6], [1992-09-01,2016-04-03,2016-04-03,7.6], [1992-10-01,2016-04-03,2016-04-03,7.3], [1992-11-01,2016-04-03,2016-04-03,7.4], [1992-12-01,2016-04-03,2016-04-03,7.4], [1993-01-01,2016-04-03,2016-04-03,7.3], [1993-02-01,2016-04-03,2016-04-03,7.1], [1993-03-01,2016-04-03,2016-04-03,7.0], [1993-04-01,2016-04-03,2016-04-03,7.1], [1993-05-01,2016-04-03,2016-04-03,7.1], [1993-06-01,2016-04-03,2016-04-03,7.0], [1993-07-01,2016-04-03,2016-04-03,6.9], [1993-08-01,2016-04-03,2016-04-03,6.8], [1993-09-01,2016-04-03,2016-04-03,6.7], [1993-10-01,2016-04-03,2016-04-03,6.8], [1993-11-01,2016-04-03,2016-04-03,6.6], [1993-12-01,2016-04-03,2016-04-03,6.5], [1994-01-01,2016-04-03,2016-04-03,6.6], [1994-02-01,2016-04-03,2016-04-03,6.6], [1994-03-01,2016-04-03,2016-04-03,6.5], [1994-04-01,2016-04-03,2016-04-03,6.4], [1994-05-01,2016-04-03,2016-04-03,6.1], [1994-06-01,2016-04-03,2016-04-03,6.1], [1994-07-01,2016-04-03,2016-04-03,6.1], [1994-08-01,2016-04-03,2016-04-03,6.0], [1994-09-01,2016-04-03,2016-04-03,5.9], [1994-10-01,2016-04-03,2016-04-03,5.8], [1994-11-01,2016-04-03,2016-04-03,5.6], [1994-12-01,2016-04-03,2016-04-03,5.5], [1995-01-01,2016-04-03,2016-04-03,5.6], [1995-02-01,2016-04-03,2016-04-03,5.4], [1995-03-01,2016-04-03,2016-04-03,5.4], [1995-04-01,2016-04-03,2016-04-03,5.8], [1995-05-01,2016-04-03,2016-04-03,5.6], [1995-06-01,2016-04-03,2016-04-03,5.6], [1995-07-01,2016-04-03,2016-04-03,5.7], [1995-08-01,2016-04-03,2016-04-03,5.7], [1995-09-01,2016-04-03,2016-04-03,5.6], [1995-10-01,2016-04-03,2016-04-03,5.5], [1995-11-01,2016-04-03,2016-04-03,5.6], [1995-12-01,2016-04-03,2016-04-03,5.6], [1996-01-01,2016-04-03,2016-04-03,5.6], [1996-02-01,2016-04-03,2016-04-03,5.5], [1996-03-01,2016-04-03,2016-04-03,5.5], [1996-04-01,2016-04-03,2016-04-03,5.6], [1996-05-01,2016-04-03,2016-04-03,5.6], [1996-06-01,2016-04-03,2016-04-03,5.3], [1996-07-01,2016-04-03,2016-04-03,5.5], [1996-08-01,2016-04-03,2016-04-03,5.1], [1996-09-01,2016-04-03,2016-04-03,5.2], [1996-10-01,2016-04-03,2016-04-03,5.2], [1996-11-01,2016-04-03,2016-04-03,5.4], [1996-12-01,2016-04-03,2016-04-03,5.4], [1997-01-01,2016-04-03,2016-04-03,5.3], [1997-02-01,2016-04-03,2016-04-03,5.2], [1997-03-01,2016-04-03,2016-04-03,5.2], [1997-04-01,2016-04-03,2016-04-03,5.1], [1997-05-01,2016-04-03,2016-04-03,4.9], [1997-06-01,2016-04-03,2016-04-03,5.0], [1997-07-01,2016-04-03,2016-04-03,4.9], [1997-08-01,2016-04-03,2016-04-03,4.8], [1997-09-01,2016-04-03,2016-04-03,4.9], [1997-10-01,2016-04-03,2016-04-03,4.7], [1997-11-01,2016-04-03,2016-04-03,4.6], [1997-12-01,2016-04-03,2016-04-03,4.7], [1998-01-01,2016-04-03,2016-04-03,4.6], [1998-02-01,2016-04-03,2016-04-03,4.6], [1998-03-01,2016-04-03,2016-04-03,4.7], [1998-04-01,2016-04-03,2016-04-03,4.3], [1998-05-01,2016-04-03,2016-04-03,4.4], [1998-06-01,2016-04-03,2016-04-03,4.5], [1998-07-01,2016-04-03,2016-04-03,4.5], [1998-08-01,2016-04-03,2016-04-03,4.5], [1998-09-01,2016-04-03,2016-04-03,4.6], [1998-10-01,2016-04-03,2016-04-03,4.5], [1998-11-01,2016-04-03,2016-04-03,4.4], [1998-12-01,2016-04-03,2016-04-03,4.4], [1999-01-01,2016-04-03,2016-04-03,4.3], [1999-02-01,2016-04-03,2016-04-03,4.4], [1999-03-01,2016-04-03,2016-04-03,4.2], [1999-04-01,2016-04-03,2016-04-03,4.3], [1999-05-01,2016-04-03,2016-04-03,4.2], [1999-06-01,2016-04-03,2016-04-03,4.3], [1999-07-01,2016-04-03,2016-04-03,4.3], [1999-08-01,2016-04-03,2016-04-03,4.2], [1999-09-01,2016-04-03,2016-04-03,4.2], [1999-10-01,2016-04-03,2016-04-03,4.1], [1999-11-01,2016-04-03,2016-04-03,4.1], [1999-12-01,2016-04-03,2016-04-03,4.0], [2000-01-01,2016-04-03,2016-04-03,4.0], [2000-02-01,2016-04-03,2016-04-03,4.1], [2000-03-01,2016-04-03,2016-04-03,4.0], [2000-04-01,2016-04-03,2016-04-03,3.8], [2000-05-01,2016-04-03,2016-04-03,4.0], [2000-06-01,2016-04-03,2016-04-03,4.0], [2000-07-01,2016-04-03,2016-04-03,4.0], [2000-08-01,2016-04-03,2016-04-03,4.1], [2000-09-01,2016-04-03,2016-04-03,3.9], [2000-10-01,2016-04-03,2016-04-03,3.9], [2000-11-01,2016-04-03,2016-04-03,3.9], [2000-12-01,2016-04-03,2016-04-03,3.9], [2001-01-01,2016-04-03,2016-04-03,4.2], [2001-02-01,2016-04-03,2016-04-03,4.2], [2001-03-01,2016-04-03,2016-04-03,4.3], [2001-04-01,2016-04-03,2016-04-03,4.4], [2001-05-01,2016-04-03,2016-04-03,4.3], [2001-06-01,2016-04-03,2016-04-03,4.5], [2001-07-01,2016-04-03,2016-04-03,4.6], [2001-08-01,2016-04-03,2016-04-03,4.9], [2001-09-01,2016-04-03,2016-04-03,5.0], [2001-10-01,2016-04-03,2016-04-03,5.3], [2001-11-01,2016-04-03,2016-04-03,5.5], [2001-12-01,2016-04-03,2016-04-03,5.7], [2002-01-01,2016-04-03,2016-04-03,5.7], [2002-02-01,2016-04-03,2016-04-03,5.7], [2002-03-01,2016-04-03,2016-04-03,5.7], [2002-04-01,2016-04-03,2016-04-03,5.9], [2002-05-01,2016-04-03,2016-04-03,5.8], [2002-06-01,2016-04-03,2016-04-03,5.8], [2002-07-01,2016-04-03,2016-04-03,5.8], [2002-08-01,2016-04-03,2016-04-03,5.7], [2002-09-01,2016-04-03,2016-04-03,5.7], [2002-10-01,2016-04-03,2016-04-03,5.7], [2002-11-01,2016-04-03,2016-04-03,5.9], [2002-12-01,2016-04-03,2016-04-03,6.0], [2003-01-01,2016-04-03,2016-04-03,5.8], [2003-02-01,2016-04-03,2016-04-03,5.9], [2003-03-01,2016-04-03,2016-04-03,5.9], [2003-04-01,2016-04-03,2016-04-03,6.0], [2003-05-01,2016-04-03,2016-04-03,6.1], [2003-06-01,2016-04-03,2016-04-03,6.3], [2003-07-01,2016-04-03,2016-04-03,6.2], [2003-08-01,2016-04-03,2016-04-03,6.1], [2003-09-01,2016-04-03,2016-04-03,6.1], [2003-10-01,2016-04-03,2016-04-03,6.0], [2003-11-01,2016-04-03,2016-04-03,5.8], [2003-12-01,2016-04-03,2016-04-03,5.7], [2004-01-01,2016-04-03,2016-04-03,5.7], [2004-02-01,2016-04-03,2016-04-03,5.6], [2004-03-01,2016-04-03,2016-04-03,5.8], [2004-04-01,2016-04-03,2016-04-03,5.6], [2004-05-01,2016-04-03,2016-04-03,5.6], [2004-06-01,2016-04-03,2016-04-03,5.6], [2004-07-01,2016-04-03,2016-04-03,5.5], [2004-08-01,2016-04-03,2016-04-03,5.4], [2004-09-01,2016-04-03,2016-04-03,5.4], [2004-10-01,2016-04-03,2016-04-03,5.5], [2004-11-01,2016-04-03,2016-04-03,5.4], [2004-12-01,2016-04-03,2016-04-03,5.4], [2005-01-01,2016-04-03,2016-04-03,5.3], [2005-02-01,2016-04-03,2016-04-03,5.4], [2005-03-01,2016-04-03,2016-04-03,5.2], [2005-04-01,2016-04-03,2016-04-03,5.2], [2005-05-01,2016-04-03,2016-04-03,5.1], [2005-06-01,2016-04-03,2016-04-03,5.0], [2005-07-01,2016-04-03,2016-04-03,5.0], [2005-08-01,2016-04-03,2016-04-03,4.9], [2005-09-01,2016-04-03,2016-04-03,5.0], [2005-10-01,2016-04-03,2016-04-03,5.0], [2005-11-01,2016-04-03,2016-04-03,5.0], [2005-12-01,2016-04-03,2016-04-03,4.9], [2006-01-01,2016-04-03,2016-04-03,4.7], [2006-02-01,2016-04-03,2016-04-03,4.8], [2006-03-01,2016-04-03,2016-04-03,4.7], [2006-04-01,2016-04-03,2016-04-03,4.7], [2006-05-01,2016-04-03,2016-04-03,4.6], [2006-06-01,2016-04-03,2016-04-03,4.6], [2006-07-01,2016-04-03,2016-04-03,4.7], [2006-08-01,2016-04-03,2016-04-03,4.7], [2006-09-01,2016-04-03,2016-04-03,4.5], [2006-10-01,2016-04-03,2016-04-03,4.4], [2006-11-01,2016-04-03,2016-04-03,4.5], [2006-12-01,2016-04-03,2016-04-03,4.4], [2007-01-01,2016-04-03,2016-04-03,4.6], [2007-02-01,2016-04-03,2016-04-03,4.5], [2007-03-01,2016-04-03,2016-04-03,4.4], [2007-04-01,2016-04-03,2016-04-03,4.5], [2007-05-01,2016-04-03,2016-04-03,4.4], [2007-06-01,2016-04-03,2016-04-03,4.6], [2007-07-01,2016-04-03,2016-04-03,4.7], [2007-08-01,2016-04-03,2016-04-03,4.6], [2007-09-01,2016-04-03,2016-04-03,4.7], [2007-10-01,2016-04-03,2016-04-03,4.7], [2007-11-01,2016-04-03,2016-04-03,4.7], [2007-12-01,2016-04-03,2016-04-03,5.0], [2008-01-01,2016-04-03,2016-04-03,5.0], [2008-02-01,2016-04-03,2016-04-03,4.9], [2008-03-01,2016-04-03,2016-04-03,5.1], [2008-04-01,2016-04-03,2016-04-03,5.0], [2008-05-01,2016-04-03,2016-04-03,5.4], [2008-06-01,2016-04-03,2016-04-03,5.6], [2008-07-01,2016-04-03,2016-04-03,5.8], [2008-08-01,2016-04-03,2016-04-03,6.1], [2008-09-01,2016-04-03,2016-04-03,6.1], [2008-10-01,2016-04-03,2016-04-03,6.5], [2008-11-01,2016-04-03,2016-04-03,6.8], [2008-12-01,2016-04-03,2016-04-03,7.3], [2009-01-01,2016-04-03,2016-04-03,7.8], [2009-02-01,2016-04-03,2016-04-03,8.3], [2009-03-01,2016-04-03,2016-04-03,8.7], [2009-04-01,2016-04-03,2016-04-03,9.0], [2009-05-01,2016-04-03,2016-04-03,9.4], [2009-06-01,2016-04-03,2016-04-03,9.5], [2009-07-01,2016-04-03,2016-04-03,9.5], [2009-08-01,2016-04-03,2016-04-03,9.6], [2009-09-01,2016-04-03,2016-04-03,9.8], [2009-10-01,2016-04-03,2016-04-03,10.0], [2009-11-01,2016-04-03,2016-04-03,9.9], [2009-12-01,2016-04-03,2016-04-03,9.9], [2010-01-01,2016-04-03,2016-04-03,9.8], [2010-02-01,2016-04-03,2016-04-03,9.8], [2010-03-01,2016-04-03,2016-04-03,9.9], [2010-04-01,2016-04-03,2016-04-03,9.9], [2010-05-01,2016-04-03,2016-04-03,9.6], [2010-06-01,2016-04-03,2016-04-03,9.4], [2010-07-01,2016-04-03,2016-04-03,9.4], [2010-08-01,2016-04-03,2016-04-03,9.5], [2010-09-01,2016-04-03,2016-04-03,9.5], [2010-10-01,2016-04-03,2016-04-03,9.4], [2010-11-01,2016-04-03,2016-04-03,9.8], [2010-12-01,2016-04-03,2016-04-03,9.3], [2011-01-01,2016-04-03,2016-04-03,9.1], [2011-02-01,2016-04-03,2016-04-03,9.0], [2011-03-01,2016-04-03,2016-04-03,9.0], [2011-04-01,2016-04-03,2016-04-03,9.1], [2011-05-01,2016-04-03,2016-04-03,9.0], [2011-06-01,2016-04-03,2016-04-03,9.1], [2011-07-01,2016-04-03,2016-04-03,9.0], [2011-08-01,2016-04-03,2016-04-03,9.0], [2011-09-01,2016-04-03,2016-04-03,9.0], [2011-10-01,2016-04-03,2016-04-03,8.8], [2011-11-01,2016-04-03,2016-04-03,8.6], [2011-12-01,2016-04-03,2016-04-03,8.5], [2012-01-01,2016-04-03,2016-04-03,8.3], [2012-02-01,2016-04-03,2016-04-03,8.3], [2012-03-01,2016-04-03,2016-04-03,8.2], [2012-04-01,2016-04-03,2016-04-03,8.2], [2012-05-01,2016-04-03,2016-04-03,8.2], [2012-06-01,2016-04-03,2016-04-03,8.2], [2012-07-01,2016-04-03,2016-04-03,8.2], [2012-08-01,2016-04-03,2016-04-03,8.1], [2012-09-01,2016-04-03,2016-04-03,7.8], [2012-10-01,2016-04-03,2016-04-03,7.8], [2012-11-01,2016-04-03,2016-04-03,7.7], [2012-12-01,2016-04-03,2016-04-03,7.9], [2013-01-01,2016-04-03,2016-04-03,8.0], [2013-02-01,2016-04-03,2016-04-03,7.7], [2013-03-01,2016-04-03,2016-04-03,7.5], [2013-04-01,2016-04-03,2016-04-03,7.6], [2013-05-01,2016-04-03,2016-04-03,7.5], [2013-06-01,2016-04-03,2016-04-03,7.5], [2013-07-01,2016-04-03,2016-04-03,7.3], [2013-08-01,2016-04-03,2016-04-03,7.3], [2013-09-01,2016-04-03,2016-04-03,7.3], [2013-10-01,2016-04-03,2016-04-03,7.2], [2013-11-01,2016-04-03,2016-04-03,6.9], [2013-12-01,2016-04-03,2016-04-03,6.7], [2014-01-01,2016-04-03,2016-04-03,6.6], [2014-02-01,2016-04-03,2016-04-03,6.7], [2014-03-01,2016-04-03,2016-04-03,6.7], [2014-04-01,2016-04-03,2016-04-03,6.2], [2014-05-01,2016-04-03,2016-04-03,6.2], [2014-06-01,2016-04-03,2016-04-03,6.1], [2014-07-01,2016-04-03,2016-04-03,6.2], [2014-08-01,2016-04-03,2016-04-03,6.2], [2014-09-01,2016-04-03,2016-04-03,6.0], [2014-10-01,2016-04-03,2016-04-03,5.7], [2014-11-01,2016-04-03,2016-04-03,5.8], [2014-12-01,2016-04-03,2016-04-03,5.6], [2015-01-01,2016-04-03,2016-04-03,5.7], [2015-02-01,2016-04-03,2016-04-03,5.5], [2015-03-01,2016-04-03,2016-04-03,5.5], [2015-04-01,2016-04-03,2016-04-03,5.4], [2015-05-01,2016-04-03,2016-04-03,5.5], [2015-06-01,2016-04-03,2016-04-03,5.3], [2015-07-01,2016-04-03,2016-04-03,5.3], [2015-08-01,2016-04-03,2016-04-03,5.1], [2015-09-01,2016-04-03,2016-04-03,5.1], [2015-10-01,2016-04-03,2016-04-03,5.0], [2015-11-01,2016-04-03,2016-04-03,5.0], [2015-12-01,2016-04-03,2016-04-03,5.0], [2016-01-01,2016-04-03,2016-04-03,4.9], [2016-02-01,2016-04-03,2016-04-03,4.9], [2016-03-01,2016-04-03,2016-04-03,5.0]),0,observation_date,1,2016-04-03,2016-04-03,asc,lin]\n"
]
}
],
"source": [
"df.collect.foreach(println)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The DataFrame printSchema method provides a better means for understanding the structure of this data."
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"root\n",
" |-- count: long (nullable = true)\n",
" |-- file_type: string (nullable = true)\n",
" |-- limit: long (nullable = true)\n",
" |-- observation_end: string (nullable = true)\n",
" |-- observation_start: string (nullable = true)\n",
" |-- observations: array (nullable = true)\n",
" | |-- element: struct (containsNull = true)\n",
" | | |-- date: string (nullable = true)\n",
" | | |-- realtime_end: string (nullable = true)\n",
" | | |-- realtime_start: string (nullable = true)\n",
" | | |-- value: string (nullable = true)\n",
" |-- offset: long (nullable = true)\n",
" |-- order_by: string (nullable = true)\n",
" |-- output_type: long (nullable = true)\n",
" |-- realtime_end: string (nullable = true)\n",
" |-- realtime_start: string (nullable = true)\n",
" |-- sort_order: string (nullable = true)\n",
" |-- units: string (nullable = true)\n",
"\n"
]
}
],
"source": [
"df.printSchema"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The DataFrame explain method gives more information about the acquired data. "
]
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"== Physical Plan ==\n",
"Scan JSONRelation[][count#0L,file_type#1,limit#2L,observation_end#3,observation_start#4,observations#5,offset#6L,order_by#7,output_type#8L,realtime_end#9,realtime_start#10,sort_order#11,units#12]\n"
]
}
],
"source": [
"df.explain"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### A Short Detour for Maintenance - Cache and Persist\n",
"\n",
"Some maintenence utilites are worthy to keep in mind. \n",
"The DataFrame cache method stores the DataFrame in memory in compressed columnar form. "
]
},
{
"cell_type": "code",
"execution_count": 9,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/plain": [
"[count: bigint, file_type: string, limit: bigint, observation_end: string, observation_start: string, observations: array<struct<date:string,realtime_end:string,realtime_start:string,value:string>>, offset: bigint, order_by: string, output_type: bigint, realtime_end: string, realtime_start: string, sort_order: string, units: string]"
]
},
"execution_count": 9,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df.cache"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The DataFrame persist method allows the memory storage level to be set. (Same as RDDs.) "
]
},
{
"cell_type": "code",
"execution_count": 10,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/plain": [
"[count: bigint, file_type: string, limit: bigint, observation_end: string, observation_start: string, observations: array<struct<date:string,realtime_end:string,realtime_start:string,value:string>>, offset: bigint, order_by: string, output_type: bigint, realtime_end: string, realtime_start: string, sort_order: string, units: string]"
]
},
"execution_count": 10,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df.persist"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### How can SQL be performed on the DataFrame?\n",
"\n",
"Registering a table will allow SQL to be perfomed on the DataFrame. The DataFrame registerTempTable method will allow this access to occur."
]
},
{
"cell_type": "code",
"execution_count": 11,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"df.registerTempTable(\"fedTable\")"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"#### How many rows are there?"
]
},
{
"cell_type": "code",
"execution_count": 12,
"metadata": {
"collapsed": false,
"scrolled": true
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"+-----+\n",
"|count|\n",
"+-----+\n",
"| 819|\n",
"+-----+\n",
"\n"
]
}
],
"source": [
"sqlContext.sql(\"select count from fedTable\").show"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"#### No really -- How many rows are there?\n",
"Hmmm - Why do the observation show as only 1 item? It's because our original count was an item in the JSON payoad."
]
},
{
"cell_type": "code",
"execution_count": 13,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"+--------------------+\n",
"| observations|\n",
"+--------------------+\n",
"|[[1948-01-01,2016...|\n",
"+--------------------+\n",
"\n"
]
}
],
"source": [
"val obs = sqlContext.sql(\"select observations from fedTable\")\n",
"obs.show"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"How many rows of observations are there? Here is the first try:"
]
},
{
"cell_type": "code",
"execution_count": 14,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/plain": [
"1"
]
},
"execution_count": 14,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"obs.count"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"And .. the second try:"
]
},
{
"cell_type": "code",
"execution_count": 15,
"metadata": {
"collapsed": false,
"scrolled": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"+-----------+\n",
"|CountedRows|\n",
"+-----------+\n",
"| 1|\n",
"+-----------+\n",
"\n"
]
}
],
"source": [
"sqlContext.sql(\"select count(observations) as CountedRows from fedTable\").show\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Why does this occur? Look back to when the SQL show method was executed, the returned result started actually started with ```[[1948-01-01,2016...```. Taking a closer look in a different way, the collect method shows that the observations are really an Array of WrappedArray. "
]
},
{
"cell_type": "code",
"execution_count": 16,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/plain": [
"Array([WrappedArray([1948-01-01,2016-04-03,2016-04-03,3.4], [1948-02-01,2016-04-03,2016-04-03,3.8], [1948-03-01,2016-04-03,2016-04-03,4.0], [1948-04-01,2016-04-03,2016-04-03,3.9], [1948-05-01,2016-04-03,2016-04-03,3.5], [1948-06-01,2016-04-03,2016-04-03,3.6], [1948-07-01,2016-04-03,2016-04-03,3.6], [1948-08-01,2016-04-03,2016-04-03,3.9], [1948-09-01,2016-04-03,2016-04-03,3.8], [1948-10-01,2016-04-03,2016-04-03,3.7], [1948-11-01,2016-04-03,2016-04-03,3.8], [1948-12-01,2016-04-03,2016-04-03,4.0], [1949-01-01,2016-04-03,2016-04-03,4.3], [1949-02-01,2016-04-03,2016-04-03,4.7], [1949-03-01,2016-04-03,2016-04-03,5.0], [1949-04-01,2016-04-03,2016-04-03,5.3], [1949-05-01,2016-04-03,2016-04-03,6.1], [1949-06-01,2016-04-03,2016-04-03,6.2], [1949-07-01,2016..."
]
},
"execution_count": 16,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"obs.collect"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The following shows how each item."
]
},
{
"cell_type": "code",
"execution_count": 17,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"[WrappedArray([1948-01-01,2016-04-03,2016-04-03,3.4], [1948-02-01,2016-04-03,2016-04-03,3.8], [1948-03-01,2016-04-03,2016-04-03,4.0], [1948-04-01,2016-04-03,2016-04-03,3.9], [1948-05-01,2016-04-03,2016-04-03,3.5], [1948-06-01,2016-04-03,2016-04-03,3.6], [1948-07-01,2016-04-03,2016-04-03,3.6], [1948-08-01,2016-04-03,2016-04-03,3.9], [1948-09-01,2016-04-03,2016-04-03,3.8], [1948-10-01,2016-04-03,2016-04-03,3.7], [1948-11-01,2016-04-03,2016-04-03,3.8], [1948-12-01,2016-04-03,2016-04-03,4.0], [1949-01-01,2016-04-03,2016-04-03,4.3], [1949-02-01,2016-04-03,2016-04-03,4.7], [1949-03-01,2016-04-03,2016-04-03,5.0], [1949-04-01,2016-04-03,2016-04-03,5.3], [1949-05-01,2016-04-03,2016-04-03,6.1], [1949-06-01,2016-04-03,2016-04-03,6.2], [1949-07-01,2016-04-03,2016-04-03,6.7], [1949-08-01,2016-04-03,2016-04-03,6.8], [1949-09-01,2016-04-03,2016-04-03,6.6], [1949-10-01,2016-04-03,2016-04-03,7.9], [1949-11-01,2016-04-03,2016-04-03,6.4], [1949-12-01,2016-04-03,2016-04-03,6.6], [1950-01-01,2016-04-03,2016-04-03,6.5], [1950-02-01,2016-04-03,2016-04-03,6.4], [1950-03-01,2016-04-03,2016-04-03,6.3], [1950-04-01,2016-04-03,2016-04-03,5.8], [1950-05-01,2016-04-03,2016-04-03,5.5], [1950-06-01,2016-04-03,2016-04-03,5.4], [1950-07-01,2016-04-03,2016-04-03,5.0], [1950-08-01,2016-04-03,2016-04-03,4.5], [1950-09-01,2016-04-03,2016-04-03,4.4], [1950-10-01,2016-04-03,2016-04-03,4.2], [1950-11-01,2016-04-03,2016-04-03,4.2], [1950-12-01,2016-04-03,2016-04-03,4.3], [1951-01-01,2016-04-03,2016-04-03,3.7], [1951-02-01,2016-04-03,2016-04-03,3.4], [1951-03-01,2016-04-03,2016-04-03,3.4], [1951-04-01,2016-04-03,2016-04-03,3.1], [1951-05-01,2016-04-03,2016-04-03,3.0], [1951-06-01,2016-04-03,2016-04-03,3.2], [1951-07-01,2016-04-03,2016-04-03,3.1], [1951-08-01,2016-04-03,2016-04-03,3.1], [1951-09-01,2016-04-03,2016-04-03,3.3], [1951-10-01,2016-04-03,2016-04-03,3.5], [1951-11-01,2016-04-03,2016-04-03,3.5], [1951-12-01,2016-04-03,2016-04-03,3.1], [1952-01-01,2016-04-03,2016-04-03,3.2], [1952-02-01,2016-04-03,2016-04-03,3.1], [1952-03-01,2016-04-03,2016-04-03,2.9], [1952-04-01,2016-04-03,2016-04-03,2.9], [1952-05-01,2016-04-03,2016-04-03,3.0], [1952-06-01,2016-04-03,2016-04-03,3.0], [1952-07-01,2016-04-03,2016-04-03,3.2], [1952-08-01,2016-04-03,2016-04-03,3.4], [1952-09-01,2016-04-03,2016-04-03,3.1], [1952-10-01,2016-04-03,2016-04-03,3.0], [1952-11-01,2016-04-03,2016-04-03,2.8], [1952-12-01,2016-04-03,2016-04-03,2.7], [1953-01-01,2016-04-03,2016-04-03,2.9], [1953-02-01,2016-04-03,2016-04-03,2.6], [1953-03-01,2016-04-03,2016-04-03,2.6], [1953-04-01,2016-04-03,2016-04-03,2.7], [1953-05-01,2016-04-03,2016-04-03,2.5], [1953-06-01,2016-04-03,2016-04-03,2.5], [1953-07-01,2016-04-03,2016-04-03,2.6], [1953-08-01,2016-04-03,2016-04-03,2.7], [1953-09-01,2016-04-03,2016-04-03,2.9], [1953-10-01,2016-04-03,2016-04-03,3.1], [1953-11-01,2016-04-03,2016-04-03,3.5], [1953-12-01,2016-04-03,2016-04-03,4.5], [1954-01-01,2016-04-03,2016-04-03,4.9], [1954-02-01,2016-04-03,2016-04-03,5.2], [1954-03-01,2016-04-03,2016-04-03,5.7], [1954-04-01,2016-04-03,2016-04-03,5.9], [1954-05-01,2016-04-03,2016-04-03,5.9], [1954-06-01,2016-04-03,2016-04-03,5.6], [1954-07-01,2016-04-03,2016-04-03,5.8], [1954-08-01,2016-04-03,2016-04-03,6.0], [1954-09-01,2016-04-03,2016-04-03,6.1], [1954-10-01,2016-04-03,2016-04-03,5.7], [1954-11-01,2016-04-03,2016-04-03,5.3], [1954-12-01,2016-04-03,2016-04-03,5.0], [1955-01-01,2016-04-03,2016-04-03,4.9], [1955-02-01,2016-04-03,2016-04-03,4.7], [1955-03-01,2016-04-03,2016-04-03,4.6], [1955-04-01,2016-04-03,2016-04-03,4.7], [1955-05-01,2016-04-03,2016-04-03,4.3], [1955-06-01,2016-04-03,2016-04-03,4.2], [1955-07-01,2016-04-03,2016-04-03,4.0], [1955-08-01,2016-04-03,2016-04-03,4.2], [1955-09-01,2016-04-03,2016-04-03,4.1], [1955-10-01,2016-04-03,2016-04-03,4.3], [1955-11-01,2016-04-03,2016-04-03,4.2], [1955-12-01,2016-04-03,2016-04-03,4.2], [1956-01-01,2016-04-03,2016-04-03,4.0], [1956-02-01,2016-04-03,2016-04-03,3.9], [1956-03-01,2016-04-03,2016-04-03,4.2], [1956-04-01,2016-04-03,2016-04-03,4.0], [1956-05-01,2016-04-03,2016-04-03,4.3], [1956-06-01,2016-04-03,2016-04-03,4.3], [1956-07-01,2016-04-03,2016-04-03,4.4], [1956-08-01,2016-04-03,2016-04-03,4.1], [1956-09-01,2016-04-03,2016-04-03,3.9], [1956-10-01,2016-04-03,2016-04-03,3.9], [1956-11-01,2016-04-03,2016-04-03,4.3], [1956-12-01,2016-04-03,2016-04-03,4.2], [1957-01-01,2016-04-03,2016-04-03,4.2], [1957-02-01,2016-04-03,2016-04-03,3.9], [1957-03-01,2016-04-03,2016-04-03,3.7], [1957-04-01,2016-04-03,2016-04-03,3.9], [1957-05-01,2016-04-03,2016-04-03,4.1], [1957-06-01,2016-04-03,2016-04-03,4.3], [1957-07-01,2016-04-03,2016-04-03,4.2], [1957-08-01,2016-04-03,2016-04-03,4.1], [1957-09-01,2016-04-03,2016-04-03,4.4], [1957-10-01,2016-04-03,2016-04-03,4.5], [1957-11-01,2016-04-03,2016-04-03,5.1], [1957-12-01,2016-04-03,2016-04-03,5.2], [1958-01-01,2016-04-03,2016-04-03,5.8], [1958-02-01,2016-04-03,2016-04-03,6.4], [1958-03-01,2016-04-03,2016-04-03,6.7], [1958-04-01,2016-04-03,2016-04-03,7.4], [1958-05-01,2016-04-03,2016-04-03,7.4], [1958-06-01,2016-04-03,2016-04-03,7.3], [1958-07-01,2016-04-03,2016-04-03,7.5], [1958-08-01,2016-04-03,2016-04-03,7.4], [1958-09-01,2016-04-03,2016-04-03,7.1], [1958-10-01,2016-04-03,2016-04-03,6.7], [1958-11-01,2016-04-03,2016-04-03,6.2], [1958-12-01,2016-04-03,2016-04-03,6.2], [1959-01-01,2016-04-03,2016-04-03,6.0], [1959-02-01,2016-04-03,2016-04-03,5.9], [1959-03-01,2016-04-03,2016-04-03,5.6], [1959-04-01,2016-04-03,2016-04-03,5.2], [1959-05-01,2016-04-03,2016-04-03,5.1], [1959-06-01,2016-04-03,2016-04-03,5.0], [1959-07-01,2016-04-03,2016-04-03,5.1], [1959-08-01,2016-04-03,2016-04-03,5.2], [1959-09-01,2016-04-03,2016-04-03,5.5], [1959-10-01,2016-04-03,2016-04-03,5.7], [1959-11-01,2016-04-03,2016-04-03,5.8], [1959-12-01,2016-04-03,2016-04-03,5.3], [1960-01-01,2016-04-03,2016-04-03,5.2], [1960-02-01,2016-04-03,2016-04-03,4.8], [1960-03-01,2016-04-03,2016-04-03,5.4], [1960-04-01,2016-04-03,2016-04-03,5.2], [1960-05-01,2016-04-03,2016-04-03,5.1], [1960-06-01,2016-04-03,2016-04-03,5.4], [1960-07-01,2016-04-03,2016-04-03,5.5], [1960-08-01,2016-04-03,2016-04-03,5.6], [1960-09-01,2016-04-03,2016-04-03,5.5], [1960-10-01,2016-04-03,2016-04-03,6.1], [1960-11-01,2016-04-03,2016-04-03,6.1], [1960-12-01,2016-04-03,2016-04-03,6.6], [1961-01-01,2016-04-03,2016-04-03,6.6], [1961-02-01,2016-04-03,2016-04-03,6.9], [1961-03-01,2016-04-03,2016-04-03,6.9], [1961-04-01,2016-04-03,2016-04-03,7.0], [1961-05-01,2016-04-03,2016-04-03,7.1], [1961-06-01,2016-04-03,2016-04-03,6.9], [1961-07-01,2016-04-03,2016-04-03,7.0], [1961-08-01,2016-04-03,2016-04-03,6.6], [1961-09-01,2016-04-03,2016-04-03,6.7], [1961-10-01,2016-04-03,2016-04-03,6.5], [1961-11-01,2016-04-03,2016-04-03,6.1], [1961-12-01,2016-04-03,2016-04-03,6.0], [1962-01-01,2016-04-03,2016-04-03,5.8], [1962-02-01,2016-04-03,2016-04-03,5.5], [1962-03-01,2016-04-03,2016-04-03,5.6], [1962-04-01,2016-04-03,2016-04-03,5.6], [1962-05-01,2016-04-03,2016-04-03,5.5], [1962-06-01,2016-04-03,2016-04-03,5.5], [1962-07-01,2016-04-03,2016-04-03,5.4], [1962-08-01,2016-04-03,2016-04-03,5.7], [1962-09-01,2016-04-03,2016-04-03,5.6], [1962-10-01,2016-04-03,2016-04-03,5.4], [1962-11-01,2016-04-03,2016-04-03,5.7], [1962-12-01,2016-04-03,2016-04-03,5.5], [1963-01-01,2016-04-03,2016-04-03,5.7], [1963-02-01,2016-04-03,2016-04-03,5.9], [1963-03-01,2016-04-03,2016-04-03,5.7], [1963-04-01,2016-04-03,2016-04-03,5.7], [1963-05-01,2016-04-03,2016-04-03,5.9], [1963-06-01,2016-04-03,2016-04-03,5.6], [1963-07-01,2016-04-03,2016-04-03,5.6], [1963-08-01,2016-04-03,2016-04-03,5.4], [1963-09-01,2016-04-03,2016-04-03,5.5], [1963-10-01,2016-04-03,2016-04-03,5.5], [1963-11-01,2016-04-03,2016-04-03,5.7], [1963-12-01,2016-04-03,2016-04-03,5.5], [1964-01-01,2016-04-03,2016-04-03,5.6], [1964-02-01,2016-04-03,2016-04-03,5.4], [1964-03-01,2016-04-03,2016-04-03,5.4], [1964-04-01,2016-04-03,2016-04-03,5.3], [1964-05-01,2016-04-03,2016-04-03,5.1], [1964-06-01,2016-04-03,2016-04-03,5.2], [1964-07-01,2016-04-03,2016-04-03,4.9], [1964-08-01,2016-04-03,2016-04-03,5.0], [1964-09-01,2016-04-03,2016-04-03,5.1], [1964-10-01,2016-04-03,2016-04-03,5.1], [1964-11-01,2016-04-03,2016-04-03,4.8], [1964-12-01,2016-04-03,2016-04-03,5.0], [1965-01-01,2016-04-03,2016-04-03,4.9], [1965-02-01,2016-04-03,2016-04-03,5.1], [1965-03-01,2016-04-03,2016-04-03,4.7], [1965-04-01,2016-04-03,2016-04-03,4.8], [1965-05-01,2016-04-03,2016-04-03,4.6], [1965-06-01,2016-04-03,2016-04-03,4.6], [1965-07-01,2016-04-03,2016-04-03,4.4], [1965-08-01,2016-04-03,2016-04-03,4.4], [1965-09-01,2016-04-03,2016-04-03,4.3], [1965-10-01,2016-04-03,2016-04-03,4.2], [1965-11-01,2016-04-03,2016-04-03,4.1], [1965-12-01,2016-04-03,2016-04-03,4.0], [1966-01-01,2016-04-03,2016-04-03,4.0], [1966-02-01,2016-04-03,2016-04-03,3.8], [1966-03-01,2016-04-03,2016-04-03,3.8], [1966-04-01,2016-04-03,2016-04-03,3.8], [1966-05-01,2016-04-03,2016-04-03,3.9], [1966-06-01,2016-04-03,2016-04-03,3.8], [1966-07-01,2016-04-03,2016-04-03,3.8], [1966-08-01,2016-04-03,2016-04-03,3.8], [1966-09-01,2016-04-03,2016-04-03,3.7], [1966-10-01,2016-04-03,2016-04-03,3.7], [1966-11-01,2016-04-03,2016-04-03,3.6], [1966-12-01,2016-04-03,2016-04-03,3.8], [1967-01-01,2016-04-03,2016-04-03,3.9], [1967-02-01,2016-04-03,2016-04-03,3.8], [1967-03-01,2016-04-03,2016-04-03,3.8], [1967-04-01,2016-04-03,2016-04-03,3.8], [1967-05-01,2016-04-03,2016-04-03,3.8], [1967-06-01,2016-04-03,2016-04-03,3.9], [1967-07-01,2016-04-03,2016-04-03,3.8], [1967-08-01,2016-04-03,2016-04-03,3.8], [1967-09-01,2016-04-03,2016-04-03,3.8], [1967-10-01,2016-04-03,2016-04-03,4.0], [1967-11-01,2016-04-03,2016-04-03,3.9], [1967-12-01,2016-04-03,2016-04-03,3.8], [1968-01-01,2016-04-03,2016-04-03,3.7], [1968-02-01,2016-04-03,2016-04-03,3.8], [1968-03-01,2016-04-03,2016-04-03,3.7], [1968-04-01,2016-04-03,2016-04-03,3.5], [1968-05-01,2016-04-03,2016-04-03,3.5], [1968-06-01,2016-04-03,2016-04-03,3.7], [1968-07-01,2016-04-03,2016-04-03,3.7], [1968-08-01,2016-04-03,2016-04-03,3.5], [1968-09-01,2016-04-03,2016-04-03,3.4], [1968-10-01,2016-04-03,2016-04-03,3.4], [1968-11-01,2016-04-03,2016-04-03,3.4], [1968-12-01,2016-04-03,2016-04-03,3.4], [1969-01-01,2016-04-03,2016-04-03,3.4], [1969-02-01,2016-04-03,2016-04-03,3.4], [1969-03-01,2016-04-03,2016-04-03,3.4], [1969-04-01,2016-04-03,2016-04-03,3.4], [1969-05-01,2016-04-03,2016-04-03,3.4], [1969-06-01,2016-04-03,2016-04-03,3.5], [1969-07-01,2016-04-03,2016-04-03,3.5], [1969-08-01,2016-04-03,2016-04-03,3.5], [1969-09-01,2016-04-03,2016-04-03,3.7], [1969-10-01,2016-04-03,2016-04-03,3.7], [1969-11-01,2016-04-03,2016-04-03,3.5], [1969-12-01,2016-04-03,2016-04-03,3.5], [1970-01-01,2016-04-03,2016-04-03,3.9], [1970-02-01,2016-04-03,2016-04-03,4.2], [1970-03-01,2016-04-03,2016-04-03,4.4], [1970-04-01,2016-04-03,2016-04-03,4.6], [1970-05-01,2016-04-03,2016-04-03,4.8], [1970-06-01,2016-04-03,2016-04-03,4.9], [1970-07-01,2016-04-03,2016-04-03,5.0], [1970-08-01,2016-04-03,2016-04-03,5.1], [1970-09-01,2016-04-03,2016-04-03,5.4], [1970-10-01,2016-04-03,2016-04-03,5.5], [1970-11-01,2016-04-03,2016-04-03,5.9], [1970-12-01,2016-04-03,2016-04-03,6.1], [1971-01-01,2016-04-03,2016-04-03,5.9], [1971-02-01,2016-04-03,2016-04-03,5.9], [1971-03-01,2016-04-03,2016-04-03,6.0], [1971-04-01,2016-04-03,2016-04-03,5.9], [1971-05-01,2016-04-03,2016-04-03,5.9], [1971-06-01,2016-04-03,2016-04-03,5.9], [1971-07-01,2016-04-03,2016-04-03,6.0], [1971-08-01,2016-04-03,2016-04-03,6.1], [1971-09-01,2016-04-03,2016-04-03,6.0], [1971-10-01,2016-04-03,2016-04-03,5.8], [1971-11-01,2016-04-03,2016-04-03,6.0], [1971-12-01,2016-04-03,2016-04-03,6.0], [1972-01-01,2016-04-03,2016-04-03,5.8], [1972-02-01,2016-04-03,2016-04-03,5.7], [1972-03-01,2016-04-03,2016-04-03,5.8], [1972-04-01,2016-04-03,2016-04-03,5.7], [1972-05-01,2016-04-03,2016-04-03,5.7], [1972-06-01,2016-04-03,2016-04-03,5.7], [1972-07-01,2016-04-03,2016-04-03,5.6], [1972-08-01,2016-04-03,2016-04-03,5.6], [1972-09-01,2016-04-03,2016-04-03,5.5], [1972-10-01,2016-04-03,2016-04-03,5.6], [1972-11-01,2016-04-03,2016-04-03,5.3], [1972-12-01,2016-04-03,2016-04-03,5.2], [1973-01-01,2016-04-03,2016-04-03,4.9], [1973-02-01,2016-04-03,2016-04-03,5.0], [1973-03-01,2016-04-03,2016-04-03,4.9], [1973-04-01,2016-04-03,2016-04-03,5.0], [1973-05-01,2016-04-03,2016-04-03,4.9], [1973-06-01,2016-04-03,2016-04-03,4.9], [1973-07-01,2016-04-03,2016-04-03,4.8], [1973-08-01,2016-04-03,2016-04-03,4.8], [1973-09-01,2016-04-03,2016-04-03,4.8], [1973-10-01,2016-04-03,2016-04-03,4.6], [1973-11-01,2016-04-03,2016-04-03,4.8], [1973-12-01,2016-04-03,2016-04-03,4.9], [1974-01-01,2016-04-03,2016-04-03,5.1], [1974-02-01,2016-04-03,2016-04-03,5.2], [1974-03-01,2016-04-03,2016-04-03,5.1], [1974-04-01,2016-04-03,2016-04-03,5.1], [1974-05-01,2016-04-03,2016-04-03,5.1], [1974-06-01,2016-04-03,2016-04-03,5.4], [1974-07-01,2016-04-03,2016-04-03,5.5], [1974-08-01,2016-04-03,2016-04-03,5.5], [1974-09-01,2016-04-03,2016-04-03,5.9], [1974-10-01,2016-04-03,2016-04-03,6.0], [1974-11-01,2016-04-03,2016-04-03,6.6], [1974-12-01,2016-04-03,2016-04-03,7.2], [1975-01-01,2016-04-03,2016-04-03,8.1], [1975-02-01,2016-04-03,2016-04-03,8.1], [1975-03-01,2016-04-03,2016-04-03,8.6], [1975-04-01,2016-04-03,2016-04-03,8.8], [1975-05-01,2016-04-03,2016-04-03,9.0], [1975-06-01,2016-04-03,2016-04-03,8.8], [1975-07-01,2016-04-03,2016-04-03,8.6], [1975-08-01,2016-04-03,2016-04-03,8.4], [1975-09-01,2016-04-03,2016-04-03,8.4], [1975-10-01,2016-04-03,2016-04-03,8.4], [1975-11-01,2016-04-03,2016-04-03,8.3], [1975-12-01,2016-04-03,2016-04-03,8.2], [1976-01-01,2016-04-03,2016-04-03,7.9], [1976-02-01,2016-04-03,2016-04-03,7.7], [1976-03-01,2016-04-03,2016-04-03,7.6], [1976-04-01,2016-04-03,2016-04-03,7.7], [1976-05-01,2016-04-03,2016-04-03,7.4], [1976-06-01,2016-04-03,2016-04-03,7.6], [1976-07-01,2016-04-03,2016-04-03,7.8], [1976-08-01,2016-04-03,2016-04-03,7.8], [1976-09-01,2016-04-03,2016-04-03,7.6], [1976-10-01,2016-04-03,2016-04-03,7.7], [1976-11-01,2016-04-03,2016-04-03,7.8], [1976-12-01,2016-04-03,2016-04-03,7.8], [1977-01-01,2016-04-03,2016-04-03,7.5], [1977-02-01,2016-04-03,2016-04-03,7.6], [1977-03-01,2016-04-03,2016-04-03,7.4], [1977-04-01,2016-04-03,2016-04-03,7.2], [1977-05-01,2016-04-03,2016-04-03,7.0], [1977-06-01,2016-04-03,2016-04-03,7.2], [1977-07-01,2016-04-03,2016-04-03,6.9], [1977-08-01,2016-04-03,2016-04-03,7.0], [1977-09-01,2016-04-03,2016-04-03,6.8], [1977-10-01,2016-04-03,2016-04-03,6.8], [1977-11-01,2016-04-03,2016-04-03,6.8], [1977-12-01,2016-04-03,2016-04-03,6.4], [1978-01-01,2016-04-03,2016-04-03,6.4], [1978-02-01,2016-04-03,2016-04-03,6.3], [1978-03-01,2016-04-03,2016-04-03,6.3], [1978-04-01,2016-04-03,2016-04-03,6.1], [1978-05-01,2016-04-03,2016-04-03,6.0], [1978-06-01,2016-04-03,2016-04-03,5.9], [1978-07-01,2016-04-03,2016-04-03,6.2], [1978-08-01,2016-04-03,2016-04-03,5.9], [1978-09-01,2016-04-03,2016-04-03,6.0], [1978-10-01,2016-04-03,2016-04-03,5.8], [1978-11-01,2016-04-03,2016-04-03,5.9], [1978-12-01,2016-04-03,2016-04-03,6.0], [1979-01-01,2016-04-03,2016-04-03,5.9], [1979-02-01,2016-04-03,2016-04-03,5.9], [1979-03-01,2016-04-03,2016-04-03,5.8], [1979-04-01,2016-04-03,2016-04-03,5.8], [1979-05-01,2016-04-03,2016-04-03,5.6], [1979-06-01,2016-04-03,2016-04-03,5.7], [1979-07-01,2016-04-03,2016-04-03,5.7], [1979-08-01,2016-04-03,2016-04-03,6.0], [1979-09-01,2016-04-03,2016-04-03,5.9], [1979-10-01,2016-04-03,2016-04-03,6.0], [1979-11-01,2016-04-03,2016-04-03,5.9], [1979-12-01,2016-04-03,2016-04-03,6.0], [1980-01-01,2016-04-03,2016-04-03,6.3], [1980-02-01,2016-04-03,2016-04-03,6.3], [1980-03-01,2016-04-03,2016-04-03,6.3], [1980-04-01,2016-04-03,2016-04-03,6.9], [1980-05-01,2016-04-03,2016-04-03,7.5], [1980-06-01,2016-04-03,2016-04-03,7.6], [1980-07-01,2016-04-03,2016-04-03,7.8], [1980-08-01,2016-04-03,2016-04-03,7.7], [1980-09-01,2016-04-03,2016-04-03,7.5], [1980-10-01,2016-04-03,2016-04-03,7.5], [1980-11-01,2016-04-03,2016-04-03,7.5], [1980-12-01,2016-04-03,2016-04-03,7.2], [1981-01-01,2016-04-03,2016-04-03,7.5], [1981-02-01,2016-04-03,2016-04-03,7.4], [1981-03-01,2016-04-03,2016-04-03,7.4], [1981-04-01,2016-04-03,2016-04-03,7.2], [1981-05-01,2016-04-03,2016-04-03,7.5], [1981-06-01,2016-04-03,2016-04-03,7.5], [1981-07-01,2016-04-03,2016-04-03,7.2], [1981-08-01,2016-04-03,2016-04-03,7.4], [1981-09-01,2016-04-03,2016-04-03,7.6], [1981-10-01,2016-04-03,2016-04-03,7.9], [1981-11-01,2016-04-03,2016-04-03,8.3], [1981-12-01,2016-04-03,2016-04-03,8.5], [1982-01-01,2016-04-03,2016-04-03,8.6], [1982-02-01,2016-04-03,2016-04-03,8.9], [1982-03-01,2016-04-03,2016-04-03,9.0], [1982-04-01,2016-04-03,2016-04-03,9.3], [1982-05-01,2016-04-03,2016-04-03,9.4], [1982-06-01,2016-04-03,2016-04-03,9.6], [1982-07-01,2016-04-03,2016-04-03,9.8], [1982-08-01,2016-04-03,2016-04-03,9.8], [1982-09-01,2016-04-03,2016-04-03,10.1], [1982-10-01,2016-04-03,2016-04-03,10.4], [1982-11-01,2016-04-03,2016-04-03,10.8], [1982-12-01,2016-04-03,2016-04-03,10.8], [1983-01-01,2016-04-03,2016-04-03,10.4], [1983-02-01,2016-04-03,2016-04-03,10.4], [1983-03-01,2016-04-03,2016-04-03,10.3], [1983-04-01,2016-04-03,2016-04-03,10.2], [1983-05-01,2016-04-03,2016-04-03,10.1], [1983-06-01,2016-04-03,2016-04-03,10.1], [1983-07-01,2016-04-03,2016-04-03,9.4], [1983-08-01,2016-04-03,2016-04-03,9.5], [1983-09-01,2016-04-03,2016-04-03,9.2], [1983-10-01,2016-04-03,2016-04-03,8.8], [1983-11-01,2016-04-03,2016-04-03,8.5], [1983-12-01,2016-04-03,2016-04-03,8.3], [1984-01-01,2016-04-03,2016-04-03,8.0], [1984-02-01,2016-04-03,2016-04-03,7.8], [1984-03-01,2016-04-03,2016-04-03,7.8], [1984-04-01,2016-04-03,2016-04-03,7.7], [1984-05-01,2016-04-03,2016-04-03,7.4], [1984-06-01,2016-04-03,2016-04-03,7.2], [1984-07-01,2016-04-03,2016-04-03,7.5], [1984-08-01,2016-04-03,2016-04-03,7.5], [1984-09-01,2016-04-03,2016-04-03,7.3], [1984-10-01,2016-04-03,2016-04-03,7.4], [1984-11-01,2016-04-03,2016-04-03,7.2], [1984-12-01,2016-04-03,2016-04-03,7.3], [1985-01-01,2016-04-03,2016-04-03,7.3], [1985-02-01,2016-04-03,2016-04-03,7.2], [1985-03-01,2016-04-03,2016-04-03,7.2], [1985-04-01,2016-04-03,2016-04-03,7.3], [1985-05-01,2016-04-03,2016-04-03,7.2], [1985-06-01,2016-04-03,2016-04-03,7.4], [1985-07-01,2016-04-03,2016-04-03,7.4], [1985-08-01,2016-04-03,2016-04-03,7.1], [1985-09-01,2016-04-03,2016-04-03,7.1], [1985-10-01,2016-04-03,2016-04-03,7.1], [1985-11-01,2016-04-03,2016-04-03,7.0], [1985-12-01,2016-04-03,2016-04-03,7.0], [1986-01-01,2016-04-03,2016-04-03,6.7], [1986-02-01,2016-04-03,2016-04-03,7.2], [1986-03-01,2016-04-03,2016-04-03,7.2], [1986-04-01,2016-04-03,2016-04-03,7.1], [1986-05-01,2016-04-03,2016-04-03,7.2], [1986-06-01,2016-04-03,2016-04-03,7.2], [1986-07-01,2016-04-03,2016-04-03,7.0], [1986-08-01,2016-04-03,2016-04-03,6.9], [1986-09-01,2016-04-03,2016-04-03,7.0], [1986-10-01,2016-04-03,2016-04-03,7.0], [1986-11-01,2016-04-03,2016-04-03,6.9], [1986-12-01,2016-04-03,2016-04-03,6.6], [1987-01-01,2016-04-03,2016-04-03,6.6], [1987-02-01,2016-04-03,2016-04-03,6.6], [1987-03-01,2016-04-03,2016-04-03,6.6], [1987-04-01,2016-04-03,2016-04-03,6.3], [1987-05-01,2016-04-03,2016-04-03,6.3], [1987-06-01,2016-04-03,2016-04-03,6.2], [1987-07-01,2016-04-03,2016-04-03,6.1], [1987-08-01,2016-04-03,2016-04-03,6.0], [1987-09-01,2016-04-03,2016-04-03,5.9], [1987-10-01,2016-04-03,2016-04-03,6.0], [1987-11-01,2016-04-03,2016-04-03,5.8], [1987-12-01,2016-04-03,2016-04-03,5.7], [1988-01-01,2016-04-03,2016-04-03,5.7], [1988-02-01,2016-04-03,2016-04-03,5.7], [1988-03-01,2016-04-03,2016-04-03,5.7], [1988-04-01,2016-04-03,2016-04-03,5.4], [1988-05-01,2016-04-03,2016-04-03,5.6], [1988-06-01,2016-04-03,2016-04-03,5.4], [1988-07-01,2016-04-03,2016-04-03,5.4], [1988-08-01,2016-04-03,2016-04-03,5.6], [1988-09-01,2016-04-03,2016-04-03,5.4], [1988-10-01,2016-04-03,2016-04-03,5.4], [1988-11-01,2016-04-03,2016-04-03,5.3], [1988-12-01,2016-04-03,2016-04-03,5.3], [1989-01-01,2016-04-03,2016-04-03,5.4], [1989-02-01,2016-04-03,2016-04-03,5.2], [1989-03-01,2016-04-03,2016-04-03,5.0], [1989-04-01,2016-04-03,2016-04-03,5.2], [1989-05-01,2016-04-03,2016-04-03,5.2], [1989-06-01,2016-04-03,2016-04-03,5.3], [1989-07-01,2016-04-03,2016-04-03,5.2], [1989-08-01,2016-04-03,2016-04-03,5.2], [1989-09-01,2016-04-03,2016-04-03,5.3], [1989-10-01,2016-04-03,2016-04-03,5.3], [1989-11-01,2016-04-03,2016-04-03,5.4], [1989-12-01,2016-04-03,2016-04-03,5.4], [1990-01-01,2016-04-03,2016-04-03,5.4], [1990-02-01,2016-04-03,2016-04-03,5.3], [1990-03-01,2016-04-03,2016-04-03,5.2], [1990-04-01,2016-04-03,2016-04-03,5.4], [1990-05-01,2016-04-03,2016-04-03,5.4], [1990-06-01,2016-04-03,2016-04-03,5.2], [1990-07-01,2016-04-03,2016-04-03,5.5], [1990-08-01,2016-04-03,2016-04-03,5.7], [1990-09-01,2016-04-03,2016-04-03,5.9], [1990-10-01,2016-04-03,2016-04-03,5.9], [1990-11-01,2016-04-03,2016-04-03,6.2], [1990-12-01,2016-04-03,2016-04-03,6.3], [1991-01-01,2016-04-03,2016-04-03,6.4], [1991-02-01,2016-04-03,2016-04-03,6.6], [1991-03-01,2016-04-03,2016-04-03,6.8], [1991-04-01,2016-04-03,2016-04-03,6.7], [1991-05-01,2016-04-03,2016-04-03,6.9], [1991-06-01,2016-04-03,2016-04-03,6.9], [1991-07-01,2016-04-03,2016-04-03,6.8], [1991-08-01,2016-04-03,2016-04-03,6.9], [1991-09-01,2016-04-03,2016-04-03,6.9], [1991-10-01,2016-04-03,2016-04-03,7.0], [1991-11-01,2016-04-03,2016-04-03,7.0], [1991-12-01,2016-04-03,2016-04-03,7.3], [1992-01-01,2016-04-03,2016-04-03,7.3], [1992-02-01,2016-04-03,2016-04-03,7.4], [1992-03-01,2016-04-03,2016-04-03,7.4], [1992-04-01,2016-04-03,2016-04-03,7.4], [1992-05-01,2016-04-03,2016-04-03,7.6], [1992-06-01,2016-04-03,2016-04-03,7.8], [1992-07-01,2016-04-03,2016-04-03,7.7], [1992-08-01,2016-04-03,2016-04-03,7.6], [1992-09-01,2016-04-03,2016-04-03,7.6], [1992-10-01,2016-04-03,2016-04-03,7.3], [1992-11-01,2016-04-03,2016-04-03,7.4], [1992-12-01,2016-04-03,2016-04-03,7.4], [1993-01-01,2016-04-03,2016-04-03,7.3], [1993-02-01,2016-04-03,2016-04-03,7.1], [1993-03-01,2016-04-03,2016-04-03,7.0], [1993-04-01,2016-04-03,2016-04-03,7.1], [1993-05-01,2016-04-03,2016-04-03,7.1], [1993-06-01,2016-04-03,2016-04-03,7.0], [1993-07-01,2016-04-03,2016-04-03,6.9], [1993-08-01,2016-04-03,2016-04-03,6.8], [1993-09-01,2016-04-03,2016-04-03,6.7], [1993-10-01,2016-04-03,2016-04-03,6.8], [1993-11-01,2016-04-03,2016-04-03,6.6], [1993-12-01,2016-04-03,2016-04-03,6.5], [1994-01-01,2016-04-03,2016-04-03,6.6], [1994-02-01,2016-04-03,2016-04-03,6.6], [1994-03-01,2016-04-03,2016-04-03,6.5], [1994-04-01,2016-04-03,2016-04-03,6.4], [1994-05-01,2016-04-03,2016-04-03,6.1], [1994-06-01,2016-04-03,2016-04-03,6.1], [1994-07-01,2016-04-03,2016-04-03,6.1], [1994-08-01,2016-04-03,2016-04-03,6.0], [1994-09-01,2016-04-03,2016-04-03,5.9], [1994-10-01,2016-04-03,2016-04-03,5.8], [1994-11-01,2016-04-03,2016-04-03,5.6], [1994-12-01,2016-04-03,2016-04-03,5.5], [1995-01-01,2016-04-03,2016-04-03,5.6], [1995-02-01,2016-04-03,2016-04-03,5.4], [1995-03-01,2016-04-03,2016-04-03,5.4], [1995-04-01,2016-04-03,2016-04-03,5.8], [1995-05-01,2016-04-03,2016-04-03,5.6], [1995-06-01,2016-04-03,2016-04-03,5.6], [1995-07-01,2016-04-03,2016-04-03,5.7], [1995-08-01,2016-04-03,2016-04-03,5.7], [1995-09-01,2016-04-03,2016-04-03,5.6], [1995-10-01,2016-04-03,2016-04-03,5.5], [1995-11-01,2016-04-03,2016-04-03,5.6], [1995-12-01,2016-04-03,2016-04-03,5.6], [1996-01-01,2016-04-03,2016-04-03,5.6], [1996-02-01,2016-04-03,2016-04-03,5.5], [1996-03-01,2016-04-03,2016-04-03,5.5], [1996-04-01,2016-04-03,2016-04-03,5.6], [1996-05-01,2016-04-03,2016-04-03,5.6], [1996-06-01,2016-04-03,2016-04-03,5.3], [1996-07-01,2016-04-03,2016-04-03,5.5], [1996-08-01,2016-04-03,2016-04-03,5.1], [1996-09-01,2016-04-03,2016-04-03,5.2], [1996-10-01,2016-04-03,2016-04-03,5.2], [1996-11-01,2016-04-03,2016-04-03,5.4], [1996-12-01,2016-04-03,2016-04-03,5.4], [1997-01-01,2016-04-03,2016-04-03,5.3], [1997-02-01,2016-04-03,2016-04-03,5.2], [1997-03-01,2016-04-03,2016-04-03,5.2], [1997-04-01,2016-04-03,2016-04-03,5.1], [1997-05-01,2016-04-03,2016-04-03,4.9], [1997-06-01,2016-04-03,2016-04-03,5.0], [1997-07-01,2016-04-03,2016-04-03,4.9], [1997-08-01,2016-04-03,2016-04-03,4.8], [1997-09-01,2016-04-03,2016-04-03,4.9], [1997-10-01,2016-04-03,2016-04-03,4.7], [1997-11-01,2016-04-03,2016-04-03,4.6], [1997-12-01,2016-04-03,2016-04-03,4.7], [1998-01-01,2016-04-03,2016-04-03,4.6], [1998-02-01,2016-04-03,2016-04-03,4.6], [1998-03-01,2016-04-03,2016-04-03,4.7], [1998-04-01,2016-04-03,2016-04-03,4.3], [1998-05-01,2016-04-03,2016-04-03,4.4], [1998-06-01,2016-04-03,2016-04-03,4.5], [1998-07-01,2016-04-03,2016-04-03,4.5], [1998-08-01,2016-04-03,2016-04-03,4.5], [1998-09-01,2016-04-03,2016-04-03,4.6], [1998-10-01,2016-04-03,2016-04-03,4.5], [1998-11-01,2016-04-03,2016-04-03,4.4], [1998-12-01,2016-04-03,2016-04-03,4.4], [1999-01-01,2016-04-03,2016-04-03,4.3], [1999-02-01,2016-04-03,2016-04-03,4.4], [1999-03-01,2016-04-03,2016-04-03,4.2], [1999-04-01,2016-04-03,2016-04-03,4.3], [1999-05-01,2016-04-03,2016-04-03,4.2], [1999-06-01,2016-04-03,2016-04-03,4.3], [1999-07-01,2016-04-03,2016-04-03,4.3], [1999-08-01,2016-04-03,2016-04-03,4.2], [1999-09-01,2016-04-03,2016-04-03,4.2], [1999-10-01,2016-04-03,2016-04-03,4.1], [1999-11-01,2016-04-03,2016-04-03,4.1], [1999-12-01,2016-04-03,2016-04-03,4.0], [2000-01-01,2016-04-03,2016-04-03,4.0], [2000-02-01,2016-04-03,2016-04-03,4.1], [2000-03-01,2016-04-03,2016-04-03,4.0], [2000-04-01,2016-04-03,2016-04-03,3.8], [2000-05-01,2016-04-03,2016-04-03,4.0], [2000-06-01,2016-04-03,2016-04-03,4.0], [2000-07-01,2016-04-03,2016-04-03,4.0], [2000-08-01,2016-04-03,2016-04-03,4.1], [2000-09-01,2016-04-03,2016-04-03,3.9], [2000-10-01,2016-04-03,2016-04-03,3.9], [2000-11-01,2016-04-03,2016-04-03,3.9], [2000-12-01,2016-04-03,2016-04-03,3.9], [2001-01-01,2016-04-03,2016-04-03,4.2], [2001-02-01,2016-04-03,2016-04-03,4.2], [2001-03-01,2016-04-03,2016-04-03,4.3], [2001-04-01,2016-04-03,2016-04-03,4.4], [2001-05-01,2016-04-03,2016-04-03,4.3], [2001-06-01,2016-04-03,2016-04-03,4.5], [2001-07-01,2016-04-03,2016-04-03,4.6], [2001-08-01,2016-04-03,2016-04-03,4.9], [2001-09-01,2016-04-03,2016-04-03,5.0], [2001-10-01,2016-04-03,2016-04-03,5.3], [2001-11-01,2016-04-03,2016-04-03,5.5], [2001-12-01,2016-04-03,2016-04-03,5.7], [2002-01-01,2016-04-03,2016-04-03,5.7], [2002-02-01,2016-04-03,2016-04-03,5.7], [2002-03-01,2016-04-03,2016-04-03,5.7], [2002-04-01,2016-04-03,2016-04-03,5.9], [2002-05-01,2016-04-03,2016-04-03,5.8], [2002-06-01,2016-04-03,2016-04-03,5.8], [2002-07-01,2016-04-03,2016-04-03,5.8], [2002-08-01,2016-04-03,2016-04-03,5.7], [2002-09-01,2016-04-03,2016-04-03,5.7], [2002-10-01,2016-04-03,2016-04-03,5.7], [2002-11-01,2016-04-03,2016-04-03,5.9], [2002-12-01,2016-04-03,2016-04-03,6.0], [2003-01-01,2016-04-03,2016-04-03,5.8], [2003-02-01,2016-04-03,2016-04-03,5.9], [2003-03-01,2016-04-03,2016-04-03,5.9], [2003-04-01,2016-04-03,2016-04-03,6.0], [2003-05-01,2016-04-03,2016-04-03,6.1], [2003-06-01,2016-04-03,2016-04-03,6.3], [2003-07-01,2016-04-03,2016-04-03,6.2], [2003-08-01,2016-04-03,2016-04-03,6.1], [2003-09-01,2016-04-03,2016-04-03,6.1], [2003-10-01,2016-04-03,2016-04-03,6.0], [2003-11-01,2016-04-03,2016-04-03,5.8], [2003-12-01,2016-04-03,2016-04-03,5.7], [2004-01-01,2016-04-03,2016-04-03,5.7], [2004-02-01,2016-04-03,2016-04-03,5.6], [2004-03-01,2016-04-03,2016-04-03,5.8], [2004-04-01,2016-04-03,2016-04-03,5.6], [2004-05-01,2016-04-03,2016-04-03,5.6], [2004-06-01,2016-04-03,2016-04-03,5.6], [2004-07-01,2016-04-03,2016-04-03,5.5], [2004-08-01,2016-04-03,2016-04-03,5.4], [2004-09-01,2016-04-03,2016-04-03,5.4], [2004-10-01,2016-04-03,2016-04-03,5.5], [2004-11-01,2016-04-03,2016-04-03,5.4], [2004-12-01,2016-04-03,2016-04-03,5.4], [2005-01-01,2016-04-03,2016-04-03,5.3], [2005-02-01,2016-04-03,2016-04-03,5.4], [2005-03-01,2016-04-03,2016-04-03,5.2], [2005-04-01,2016-04-03,2016-04-03,5.2], [2005-05-01,2016-04-03,2016-04-03,5.1], [2005-06-01,2016-04-03,2016-04-03,5.0], [2005-07-01,2016-04-03,2016-04-03,5.0], [2005-08-01,2016-04-03,2016-04-03,4.9], [2005-09-01,2016-04-03,2016-04-03,5.0], [2005-10-01,2016-04-03,2016-04-03,5.0], [2005-11-01,2016-04-03,2016-04-03,5.0], [2005-12-01,2016-04-03,2016-04-03,4.9], [2006-01-01,2016-04-03,2016-04-03,4.7], [2006-02-01,2016-04-03,2016-04-03,4.8], [2006-03-01,2016-04-03,2016-04-03,4.7], [2006-04-01,2016-04-03,2016-04-03,4.7], [2006-05-01,2016-04-03,2016-04-03,4.6], [2006-06-01,2016-04-03,2016-04-03,4.6], [2006-07-01,2016-04-03,2016-04-03,4.7], [2006-08-01,2016-04-03,2016-04-03,4.7], [2006-09-01,2016-04-03,2016-04-03,4.5], [2006-10-01,2016-04-03,2016-04-03,4.4], [2006-11-01,2016-04-03,2016-04-03,4.5], [2006-12-01,2016-04-03,2016-04-03,4.4], [2007-01-01,2016-04-03,2016-04-03,4.6], [2007-02-01,2016-04-03,2016-04-03,4.5], [2007-03-01,2016-04-03,2016-04-03,4.4], [2007-04-01,2016-04-03,2016-04-03,4.5], [2007-05-01,2016-04-03,2016-04-03,4.4], [2007-06-01,2016-04-03,2016-04-03,4.6], [2007-07-01,2016-04-03,2016-04-03,4.7], [2007-08-01,2016-04-03,2016-04-03,4.6], [2007-09-01,2016-04-03,2016-04-03,4.7], [2007-10-01,2016-04-03,2016-04-03,4.7], [2007-11-01,2016-04-03,2016-04-03,4.7], [2007-12-01,2016-04-03,2016-04-03,5.0], [2008-01-01,2016-04-03,2016-04-03,5.0], [2008-02-01,2016-04-03,2016-04-03,4.9], [2008-03-01,2016-04-03,2016-04-03,5.1], [2008-04-01,2016-04-03,2016-04-03,5.0], [2008-05-01,2016-04-03,2016-04-03,5.4], [2008-06-01,2016-04-03,2016-04-03,5.6], [2008-07-01,2016-04-03,2016-04-03,5.8], [2008-08-01,2016-04-03,2016-04-03,6.1], [2008-09-01,2016-04-03,2016-04-03,6.1], [2008-10-01,2016-04-03,2016-04-03,6.5], [2008-11-01,2016-04-03,2016-04-03,6.8], [2008-12-01,2016-04-03,2016-04-03,7.3], [2009-01-01,2016-04-03,2016-04-03,7.8], [2009-02-01,2016-04-03,2016-04-03,8.3], [2009-03-01,2016-04-03,2016-04-03,8.7], [2009-04-01,2016-04-03,2016-04-03,9.0], [2009-05-01,2016-04-03,2016-04-03,9.4], [2009-06-01,2016-04-03,2016-04-03,9.5], [2009-07-01,2016-04-03,2016-04-03,9.5], [2009-08-01,2016-04-03,2016-04-03,9.6], [2009-09-01,2016-04-03,2016-04-03,9.8], [2009-10-01,2016-04-03,2016-04-03,10.0], [2009-11-01,2016-04-03,2016-04-03,9.9], [2009-12-01,2016-04-03,2016-04-03,9.9], [2010-01-01,2016-04-03,2016-04-03,9.8], [2010-02-01,2016-04-03,2016-04-03,9.8], [2010-03-01,2016-04-03,2016-04-03,9.9], [2010-04-01,2016-04-03,2016-04-03,9.9], [2010-05-01,2016-04-03,2016-04-03,9.6], [2010-06-01,2016-04-03,2016-04-03,9.4], [2010-07-01,2016-04-03,2016-04-03,9.4], [2010-08-01,2016-04-03,2016-04-03,9.5], [2010-09-01,2016-04-03,2016-04-03,9.5], [2010-10-01,2016-04-03,2016-04-03,9.4], [2010-11-01,2016-04-03,2016-04-03,9.8], [2010-12-01,2016-04-03,2016-04-03,9.3], [2011-01-01,2016-04-03,2016-04-03,9.1], [2011-02-01,2016-04-03,2016-04-03,9.0], [2011-03-01,2016-04-03,2016-04-03,9.0], [2011-04-01,2016-04-03,2016-04-03,9.1], [2011-05-01,2016-04-03,2016-04-03,9.0], [2011-06-01,2016-04-03,2016-04-03,9.1], [2011-07-01,2016-04-03,2016-04-03,9.0], [2011-08-01,2016-04-03,2016-04-03,9.0], [2011-09-01,2016-04-03,2016-04-03,9.0], [2011-10-01,2016-04-03,2016-04-03,8.8], [2011-11-01,2016-04-03,2016-04-03,8.6], [2011-12-01,2016-04-03,2016-04-03,8.5], [2012-01-01,2016-04-03,2016-04-03,8.3], [2012-02-01,2016-04-03,2016-04-03,8.3], [2012-03-01,2016-04-03,2016-04-03,8.2], [2012-04-01,2016-04-03,2016-04-03,8.2], [2012-05-01,2016-04-03,2016-04-03,8.2], [2012-06-01,2016-04-03,2016-04-03,8.2], [2012-07-01,2016-04-03,2016-04-03,8.2], [2012-08-01,2016-04-03,2016-04-03,8.1], [2012-09-01,2016-04-03,2016-04-03,7.8], [2012-10-01,2016-04-03,2016-04-03,7.8], [2012-11-01,2016-04-03,2016-04-03,7.7], [2012-12-01,2016-04-03,2016-04-03,7.9], [2013-01-01,2016-04-03,2016-04-03,8.0], [2013-02-01,2016-04-03,2016-04-03,7.7], [2013-03-01,2016-04-03,2016-04-03,7.5], [2013-04-01,2016-04-03,2016-04-03,7.6], [2013-05-01,2016-04-03,2016-04-03,7.5], [2013-06-01,2016-04-03,2016-04-03,7.5], [2013-07-01,2016-04-03,2016-04-03,7.3], [2013-08-01,2016-04-03,2016-04-03,7.3], [2013-09-01,2016-04-03,2016-04-03,7.3], [2013-10-01,2016-04-03,2016-04-03,7.2], [2013-11-01,2016-04-03,2016-04-03,6.9], [2013-12-01,2016-04-03,2016-04-03,6.7], [2014-01-01,2016-04-03,2016-04-03,6.6], [2014-02-01,2016-04-03,2016-04-03,6.7], [2014-03-01,2016-04-03,2016-04-03,6.7], [2014-04-01,2016-04-03,2016-04-03,6.2], [2014-05-01,2016-04-03,2016-04-03,6.2], [2014-06-01,2016-04-03,2016-04-03,6.1], [2014-07-01,2016-04-03,2016-04-03,6.2], [2014-08-01,2016-04-03,2016-04-03,6.2], [2014-09-01,2016-04-03,2016-04-03,6.0], [2014-10-01,2016-04-03,2016-04-03,5.7], [2014-11-01,2016-04-03,2016-04-03,5.8], [2014-12-01,2016-04-03,2016-04-03,5.6], [2015-01-01,2016-04-03,2016-04-03,5.7], [2015-02-01,2016-04-03,2016-04-03,5.5], [2015-03-01,2016-04-03,2016-04-03,5.5], [2015-04-01,2016-04-03,2016-04-03,5.4], [2015-05-01,2016-04-03,2016-04-03,5.5], [2015-06-01,2016-04-03,2016-04-03,5.3], [2015-07-01,2016-04-03,2016-04-03,5.3], [2015-08-01,2016-04-03,2016-04-03,5.1], [2015-09-01,2016-04-03,2016-04-03,5.1], [2015-10-01,2016-04-03,2016-04-03,5.0], [2015-11-01,2016-04-03,2016-04-03,5.0], [2015-12-01,2016-04-03,2016-04-03,5.0], [2016-01-01,2016-04-03,2016-04-03,4.9], [2016-02-01,2016-04-03,2016-04-03,4.9], [2016-03-01,2016-04-03,2016-04-03,5.0])]\n"
]
}
],
"source": [
"obs.collect.foreach(println)"
]
},
{
"cell_type": "markdown",
"metadata": {
"collapsed": false
},
"source": [
"#### How does this match the schema for the SQL select of observations collected earlier? \n",
"Executing the printSchema method provides some information."
]
},
{
"cell_type": "code",
"execution_count": 33,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"root\n",
" |-- observations: array (nullable = true)\n",
" | |-- element: struct (containsNull = true)\n",
" | | |-- date: string (nullable = true)\n",
" | | |-- realtime_end: string (nullable = true)\n",
" | | |-- realtime_start: string (nullable = true)\n",
" | | |-- value: string (nullable = true)\n",
"\n"
]
}
],
"source": [
"obs.printSchema"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### How can an embedded array be flattened?\n",
"The array embedded in the data needs to be flattened, or flatmapped, or in this case, _explode_ nes to be applied."
]
},
{
"cell_type": "code",
"execution_count": 34,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"val elements = sqlContext.sql(\"select explode(observations) as rowData from fedTable\")"
]
},
{
"cell_type": "code",
"execution_count": 35,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/plain": [
"819"
]
},
"execution_count": 35,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"elements.count"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Now, there are 819 elements."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"It's possible to see these elements. But wait ... these are in wrapped arrays as see below."
]
},
{
"cell_type": "code",
"execution_count": 36,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"+--------------------+\n",
"| rowData|\n",
"+--------------------+\n",
"|[1948-01-01,2016-...|\n",
"|[1948-02-01,2016-...|\n",
"|[1948-03-01,2016-...|\n",
"|[1948-04-01,2016-...|\n",
"|[1948-05-01,2016-...|\n",
"|[1948-06-01,2016-...|\n",
"|[1948-07-01,2016-...|\n",
"|[1948-08-01,2016-...|\n",
"|[1948-09-01,2016-...|\n",
"|[1948-10-01,2016-...|\n",
"|[1948-11-01,2016-...|\n",
"|[1948-12-01,2016-...|\n",
"|[1949-01-01,2016-...|\n",
"|[1949-02-01,2016-...|\n",
"|[1949-03-01,2016-...|\n",
"|[1949-04-01,2016-...|\n",
"|[1949-05-01,2016-...|\n",
"|[1949-06-01,2016-...|\n",
"|[1949-07-01,2016-...|\n",
"|[1949-08-01,2016-...|\n",
"+--------------------+\n",
"only showing top 20 rows\n",
"\n"
]
}
],
"source": [
"elements.show"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"What's the schema of this row data (rowData)?"
]
},
{
"cell_type": "code",
"execution_count": 37,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"root\n",
" |-- rowData: struct (nullable = true)\n",
" | |-- date: string (nullable = true)\n",
" | |-- realtime_end: string (nullable = true)\n",
" | |-- realtime_start: string (nullable = true)\n",
" | |-- value: string (nullable = true)\n",
"\n"
]
}
],
"source": [
"elements.printSchema"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### How can this structured row data be examined?\n",
"\n",
"Time to register another table. Since this data is now a structure (wrapped array), meaning the rowData is no longer an array, this elements DataFrame can be reistered as a table to make data extraction / analysis a bit simpler. (Note: A more complex query could likely be applied with subqueries, but the goal is to be simple.)"
]
},
{
"cell_type": "code",
"execution_count": 38,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"elements.registerTempTable(\"elements\")"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Now ... just execute an sql query."
]
},
{
"cell_type": "code",
"execution_count": 39,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"val finalProduct = \n",
" sqlContext.sql(\"select rowData.realtime_start, rowData.realtime_end, rowData.date, rowData.value from elements\")"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Visualizing the table in the first way, the following is seen. "
]
},
{
"cell_type": "code",
"execution_count": 40,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"+--------------+------------+----------+-----+\n",
"|realtime_start|realtime_end| date|value|\n",
"+--------------+------------+----------+-----+\n",
"| 2016-04-03| 2016-04-03|1948-01-01| 3.4|\n",
"| 2016-04-03| 2016-04-03|1948-02-01| 3.8|\n",
"| 2016-04-03| 2016-04-03|1948-03-01| 4.0|\n",
"| 2016-04-03| 2016-04-03|1948-04-01| 3.9|\n",
"| 2016-04-03| 2016-04-03|1948-05-01| 3.5|\n",
"| 2016-04-03| 2016-04-03|1948-06-01| 3.6|\n",
"| 2016-04-03| 2016-04-03|1948-07-01| 3.6|\n",
"| 2016-04-03| 2016-04-03|1948-08-01| 3.9|\n",
"| 2016-04-03| 2016-04-03|1948-09-01| 3.8|\n",
"| 2016-04-03| 2016-04-03|1948-10-01| 3.7|\n",
"| 2016-04-03| 2016-04-03|1948-11-01| 3.8|\n",
"| 2016-04-03| 2016-04-03|1948-12-01| 4.0|\n",
"| 2016-04-03| 2016-04-03|1949-01-01| 4.3|\n",
"| 2016-04-03| 2016-04-03|1949-02-01| 4.7|\n",
"| 2016-04-03| 2016-04-03|1949-03-01| 5.0|\n",
"| 2016-04-03| 2016-04-03|1949-04-01| 5.3|\n",
"| 2016-04-03| 2016-04-03|1949-05-01| 6.1|\n",
"| 2016-04-03| 2016-04-03|1949-06-01| 6.2|\n",
"| 2016-04-03| 2016-04-03|1949-07-01| 6.7|\n",
"| 2016-04-03| 2016-04-03|1949-08-01| 6.8|\n",
"+--------------+------------+----------+-----+\n",
"only showing top 20 rows\n",
"\n"
]
}
],
"source": [
"finalProduct.show"
]
},
{
"cell_type": "code",
"execution_count": 41,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"[2016-04-03,2016-04-03,1948-01-01,3.4]\n",
"[2016-04-03,2016-04-03,1948-02-01,3.8]\n",
"[2016-04-03,2016-04-03,1948-03-01,4.0]\n",
"[2016-04-03,2016-04-03,1948-04-01,3.9]\n",
"[2016-04-03,2016-04-03,1948-05-01,3.5]\n",
"[2016-04-03,2016-04-03,1948-06-01,3.6]\n",
"[2016-04-03,2016-04-03,1948-07-01,3.6]\n",
"[2016-04-03,2016-04-03,1948-08-01,3.9]\n",
"[2016-04-03,2016-04-03,1948-09-01,3.8]\n",
"[2016-04-03,2016-04-03,1948-10-01,3.7]\n",
"[2016-04-03,2016-04-03,1948-11-01,3.8]\n",
"[2016-04-03,2016-04-03,1948-12-01,4.0]\n",
"[2016-04-03,2016-04-03,1949-01-01,4.3]\n",
"[2016-04-03,2016-04-03,1949-02-01,4.7]\n",
"[2016-04-03,2016-04-03,1949-03-01,5.0]\n",
"[2016-04-03,2016-04-03,1949-04-01,5.3]\n",
"[2016-04-03,2016-04-03,1949-05-01,6.1]\n",
"[2016-04-03,2016-04-03,1949-06-01,6.2]\n",
"[2016-04-03,2016-04-03,1949-07-01,6.7]\n",
"[2016-04-03,2016-04-03,1949-08-01,6.8]\n",
"[2016-04-03,2016-04-03,1949-09-01,6.6]\n",
"[2016-04-03,2016-04-03,1949-10-01,7.9]\n",
"[2016-04-03,2016-04-03,1949-11-01,6.4]\n",
"[2016-04-03,2016-04-03,1949-12-01,6.6]\n",
"[2016-04-03,2016-04-03,1950-01-01,6.5]\n",
"[2016-04-03,2016-04-03,1950-02-01,6.4]\n",
"[2016-04-03,2016-04-03,1950-03-01,6.3]\n",
"[2016-04-03,2016-04-03,1950-04-01,5.8]\n",
"[2016-04-03,2016-04-03,1950-05-01,5.5]\n",
"[2016-04-03,2016-04-03,1950-06-01,5.4]\n",
"[2016-04-03,2016-04-03,1950-07-01,5.0]\n",
"[2016-04-03,2016-04-03,1950-08-01,4.5]\n",
"[2016-04-03,2016-04-03,1950-09-01,4.4]\n",
"[2016-04-03,2016-04-03,1950-10-01,4.2]\n",
"[2016-04-03,2016-04-03,1950-11-01,4.2]\n",
"[2016-04-03,2016-04-03,1950-12-01,4.3]\n",
"[2016-04-03,2016-04-03,1951-01-01,3.7]\n",
"[2016-04-03,2016-04-03,1951-02-01,3.4]\n",
"[2016-04-03,2016-04-03,1951-03-01,3.4]\n",
"[2016-04-03,2016-04-03,1951-04-01,3.1]\n",
"[2016-04-03,2016-04-03,1951-05-01,3.0]\n",
"[2016-04-03,2016-04-03,1951-06-01,3.2]\n",
"[2016-04-03,2016-04-03,1951-07-01,3.1]\n",
"[2016-04-03,2016-04-03,1951-08-01,3.1]\n",
"[2016-04-03,2016-04-03,1951-09-01,3.3]\n",
"[2016-04-03,2016-04-03,1951-10-01,3.5]\n",
"[2016-04-03,2016-04-03,1951-11-01,3.5]\n",
"[2016-04-03,2016-04-03,1951-12-01,3.1]\n",
"[2016-04-03,2016-04-03,1952-01-01,3.2]\n",
"[2016-04-03,2016-04-03,1952-02-01,3.1]\n",
"[2016-04-03,2016-04-03,1952-03-01,2.9]\n",
"[2016-04-03,2016-04-03,1952-04-01,2.9]\n",
"[2016-04-03,2016-04-03,1952-05-01,3.0]\n",
"[2016-04-03,2016-04-03,1952-06-01,3.0]\n",
"[2016-04-03,2016-04-03,1952-07-01,3.2]\n",
"[2016-04-03,2016-04-03,1952-08-01,3.4]\n",
"[2016-04-03,2016-04-03,1952-09-01,3.1]\n",
"[2016-04-03,2016-04-03,1952-10-01,3.0]\n",
"[2016-04-03,2016-04-03,1952-11-01,2.8]\n",
"[2016-04-03,2016-04-03,1952-12-01,2.7]\n",
"[2016-04-03,2016-04-03,1953-01-01,2.9]\n",
"[2016-04-03,2016-04-03,1953-02-01,2.6]\n",
"[2016-04-03,2016-04-03,1953-03-01,2.6]\n",
"[2016-04-03,2016-04-03,1953-04-01,2.7]\n",
"[2016-04-03,2016-04-03,1953-05-01,2.5]\n",
"[2016-04-03,2016-04-03,1953-06-01,2.5]\n",
"[2016-04-03,2016-04-03,1953-07-01,2.6]\n",
"[2016-04-03,2016-04-03,1953-08-01,2.7]\n",
"[2016-04-03,2016-04-03,1953-09-01,2.9]\n",
"[2016-04-03,2016-04-03,1953-10-01,3.1]\n",
"[2016-04-03,2016-04-03,1953-11-01,3.5]\n",
"[2016-04-03,2016-04-03,1953-12-01,4.5]\n",
"[2016-04-03,2016-04-03,1954-01-01,4.9]\n",
"[2016-04-03,2016-04-03,1954-02-01,5.2]\n",
"[2016-04-03,2016-04-03,1954-03-01,5.7]\n",
"[2016-04-03,2016-04-03,1954-04-01,5.9]\n",
"[2016-04-03,2016-04-03,1954-05-01,5.9]\n",
"[2016-04-03,2016-04-03,1954-06-01,5.6]\n",
"[2016-04-03,2016-04-03,1954-07-01,5.8]\n",
"[2016-04-03,2016-04-03,1954-08-01,6.0]\n",
"[2016-04-03,2016-04-03,1954-09-01,6.1]\n",
"[2016-04-03,2016-04-03,1954-10-01,5.7]\n",
"[2016-04-03,2016-04-03,1954-11-01,5.3]\n",
"[2016-04-03,2016-04-03,1954-12-01,5.0]\n",
"[2016-04-03,2016-04-03,1955-01-01,4.9]\n",
"[2016-04-03,2016-04-03,1955-02-01,4.7]\n",
"[2016-04-03,2016-04-03,1955-03-01,4.6]\n",
"[2016-04-03,2016-04-03,1955-04-01,4.7]\n",
"[2016-04-03,2016-04-03,1955-05-01,4.3]\n",
"[2016-04-03,2016-04-03,1955-06-01,4.2]\n",
"[2016-04-03,2016-04-03,1955-07-01,4.0]\n",
"[2016-04-03,2016-04-03,1955-08-01,4.2]\n",
"[2016-04-03,2016-04-03,1955-09-01,4.1]\n",
"[2016-04-03,2016-04-03,1955-10-01,4.3]\n",
"[2016-04-03,2016-04-03,1955-11-01,4.2]\n",
"[2016-04-03,2016-04-03,1955-12-01,4.2]\n",
"[2016-04-03,2016-04-03,1956-01-01,4.0]\n",
"[2016-04-03,2016-04-03,1956-02-01,3.9]\n",
"[2016-04-03,2016-04-03,1956-03-01,4.2]\n",
"[2016-04-03,2016-04-03,1956-04-01,4.0]\n",
"[2016-04-03,2016-04-03,1956-05-01,4.3]\n",
"[2016-04-03,2016-04-03,1956-06-01,4.3]\n",
"[2016-04-03,2016-04-03,1956-07-01,4.4]\n",
"[2016-04-03,2016-04-03,1956-08-01,4.1]\n",
"[2016-04-03,2016-04-03,1956-09-01,3.9]\n",
"[2016-04-03,2016-04-03,1956-10-01,3.9]\n",
"[2016-04-03,2016-04-03,1956-11-01,4.3]\n",
"[2016-04-03,2016-04-03,1956-12-01,4.2]\n",
"[2016-04-03,2016-04-03,1957-01-01,4.2]\n",
"[2016-04-03,2016-04-03,1957-02-01,3.9]\n",
"[2016-04-03,2016-04-03,1957-03-01,3.7]\n",
"[2016-04-03,2016-04-03,1957-04-01,3.9]\n",
"[2016-04-03,2016-04-03,1957-05-01,4.1]\n",
"[2016-04-03,2016-04-03,1957-06-01,4.3]\n",
"[2016-04-03,2016-04-03,1957-07-01,4.2]\n",
"[2016-04-03,2016-04-03,1957-08-01,4.1]\n",
"[2016-04-03,2016-04-03,1957-09-01,4.4]\n",
"[2016-04-03,2016-04-03,1957-10-01,4.5]\n",
"[2016-04-03,2016-04-03,1957-11-01,5.1]\n",
"[2016-04-03,2016-04-03,1957-12-01,5.2]\n",
"[2016-04-03,2016-04-03,1958-01-01,5.8]\n",
"[2016-04-03,2016-04-03,1958-02-01,6.4]\n",
"[2016-04-03,2016-04-03,1958-03-01,6.7]\n",
"[2016-04-03,2016-04-03,1958-04-01,7.4]\n",
"[2016-04-03,2016-04-03,1958-05-01,7.4]\n",
"[2016-04-03,2016-04-03,1958-06-01,7.3]\n",
"[2016-04-03,2016-04-03,1958-07-01,7.5]\n",
"[2016-04-03,2016-04-03,1958-08-01,7.4]\n",
"[2016-04-03,2016-04-03,1958-09-01,7.1]\n",
"[2016-04-03,2016-04-03,1958-10-01,6.7]\n",
"[2016-04-03,2016-04-03,1958-11-01,6.2]\n",
"[2016-04-03,2016-04-03,1958-12-01,6.2]\n",
"[2016-04-03,2016-04-03,1959-01-01,6.0]\n",
"[2016-04-03,2016-04-03,1959-02-01,5.9]\n",
"[2016-04-03,2016-04-03,1959-03-01,5.6]\n",
"[2016-04-03,2016-04-03,1959-04-01,5.2]\n",
"[2016-04-03,2016-04-03,1959-05-01,5.1]\n",
"[2016-04-03,2016-04-03,1959-06-01,5.0]\n",
"[2016-04-03,2016-04-03,1959-07-01,5.1]\n",
"[2016-04-03,2016-04-03,1959-08-01,5.2]\n",
"[2016-04-03,2016-04-03,1959-09-01,5.5]\n",
"[2016-04-03,2016-04-03,1959-10-01,5.7]\n",
"[2016-04-03,2016-04-03,1959-11-01,5.8]\n",
"[2016-04-03,2016-04-03,1959-12-01,5.3]\n",
"[2016-04-03,2016-04-03,1960-01-01,5.2]\n",
"[2016-04-03,2016-04-03,1960-02-01,4.8]\n",
"[2016-04-03,2016-04-03,1960-03-01,5.4]\n",
"[2016-04-03,2016-04-03,1960-04-01,5.2]\n",
"[2016-04-03,2016-04-03,1960-05-01,5.1]\n",
"[2016-04-03,2016-04-03,1960-06-01,5.4]\n",
"[2016-04-03,2016-04-03,1960-07-01,5.5]\n",
"[2016-04-03,2016-04-03,1960-08-01,5.6]\n",
"[2016-04-03,2016-04-03,1960-09-01,5.5]\n",
"[2016-04-03,2016-04-03,1960-10-01,6.1]\n",
"[2016-04-03,2016-04-03,1960-11-01,6.1]\n",
"[2016-04-03,2016-04-03,1960-12-01,6.6]\n",
"[2016-04-03,2016-04-03,1961-01-01,6.6]\n",
"[2016-04-03,2016-04-03,1961-02-01,6.9]\n",
"[2016-04-03,2016-04-03,1961-03-01,6.9]\n",
"[2016-04-03,2016-04-03,1961-04-01,7.0]\n",
"[2016-04-03,2016-04-03,1961-05-01,7.1]\n",
"[2016-04-03,2016-04-03,1961-06-01,6.9]\n",
"[2016-04-03,2016-04-03,1961-07-01,7.0]\n",
"[2016-04-03,2016-04-03,1961-08-01,6.6]\n",
"[2016-04-03,2016-04-03,1961-09-01,6.7]\n",
"[2016-04-03,2016-04-03,1961-10-01,6.5]\n",
"[2016-04-03,2016-04-03,1961-11-01,6.1]\n",
"[2016-04-03,2016-04-03,1961-12-01,6.0]\n",
"[2016-04-03,2016-04-03,1962-01-01,5.8]\n",
"[2016-04-03,2016-04-03,1962-02-01,5.5]\n",
"[2016-04-03,2016-04-03,1962-03-01,5.6]\n",
"[2016-04-03,2016-04-03,1962-04-01,5.6]\n",
"[2016-04-03,2016-04-03,1962-05-01,5.5]\n",
"[2016-04-03,2016-04-03,1962-06-01,5.5]\n",
"[2016-04-03,2016-04-03,1962-07-01,5.4]\n",
"[2016-04-03,2016-04-03,1962-08-01,5.7]\n",
"[2016-04-03,2016-04-03,1962-09-01,5.6]\n",
"[2016-04-03,2016-04-03,1962-10-01,5.4]\n",
"[2016-04-03,2016-04-03,1962-11-01,5.7]\n",
"[2016-04-03,2016-04-03,1962-12-01,5.5]\n",
"[2016-04-03,2016-04-03,1963-01-01,5.7]\n",
"[2016-04-03,2016-04-03,1963-02-01,5.9]\n",
"[2016-04-03,2016-04-03,1963-03-01,5.7]\n",
"[2016-04-03,2016-04-03,1963-04-01,5.7]\n",
"[2016-04-03,2016-04-03,1963-05-01,5.9]\n",
"[2016-04-03,2016-04-03,1963-06-01,5.6]\n",
"[2016-04-03,2016-04-03,1963-07-01,5.6]\n",
"[2016-04-03,2016-04-03,1963-08-01,5.4]\n",
"[2016-04-03,2016-04-03,1963-09-01,5.5]\n",
"[2016-04-03,2016-04-03,1963-10-01,5.5]\n",
"[2016-04-03,2016-04-03,1963-11-01,5.7]\n",
"[2016-04-03,2016-04-03,1963-12-01,5.5]\n",
"[2016-04-03,2016-04-03,1964-01-01,5.6]\n",
"[2016-04-03,2016-04-03,1964-02-01,5.4]\n",
"[2016-04-03,2016-04-03,1964-03-01,5.4]\n",
"[2016-04-03,2016-04-03,1964-04-01,5.3]\n",
"[2016-04-03,2016-04-03,1964-05-01,5.1]\n",
"[2016-04-03,2016-04-03,1964-06-01,5.2]\n",
"[2016-04-03,2016-04-03,1964-07-01,4.9]\n",
"[2016-04-03,2016-04-03,1964-08-01,5.0]\n",
"[2016-04-03,2016-04-03,1964-09-01,5.1]\n",
"[2016-04-03,2016-04-03,1964-10-01,5.1]\n",
"[2016-04-03,2016-04-03,1964-11-01,4.8]\n",
"[2016-04-03,2016-04-03,1964-12-01,5.0]\n",
"[2016-04-03,2016-04-03,1965-01-01,4.9]\n",
"[2016-04-03,2016-04-03,1965-02-01,5.1]\n",
"[2016-04-03,2016-04-03,1965-03-01,4.7]\n",
"[2016-04-03,2016-04-03,1965-04-01,4.8]\n",
"[2016-04-03,2016-04-03,1965-05-01,4.6]\n",
"[2016-04-03,2016-04-03,1965-06-01,4.6]\n",
"[2016-04-03,2016-04-03,1965-07-01,4.4]\n",
"[2016-04-03,2016-04-03,1965-08-01,4.4]\n",
"[2016-04-03,2016-04-03,1965-09-01,4.3]\n",
"[2016-04-03,2016-04-03,1965-10-01,4.2]\n",
"[2016-04-03,2016-04-03,1965-11-01,4.1]\n",
"[2016-04-03,2016-04-03,1965-12-01,4.0]\n",
"[2016-04-03,2016-04-03,1966-01-01,4.0]\n",
"[2016-04-03,2016-04-03,1966-02-01,3.8]\n",
"[2016-04-03,2016-04-03,1966-03-01,3.8]\n",
"[2016-04-03,2016-04-03,1966-04-01,3.8]\n",
"[2016-04-03,2016-04-03,1966-05-01,3.9]\n",
"[2016-04-03,2016-04-03,1966-06-01,3.8]\n",
"[2016-04-03,2016-04-03,1966-07-01,3.8]\n",
"[2016-04-03,2016-04-03,1966-08-01,3.8]\n",
"[2016-04-03,2016-04-03,1966-09-01,3.7]\n",
"[2016-04-03,2016-04-03,1966-10-01,3.7]\n",
"[2016-04-03,2016-04-03,1966-11-01,3.6]\n",
"[2016-04-03,2016-04-03,1966-12-01,3.8]\n",
"[2016-04-03,2016-04-03,1967-01-01,3.9]\n",
"[2016-04-03,2016-04-03,1967-02-01,3.8]\n",
"[2016-04-03,2016-04-03,1967-03-01,3.8]\n",
"[2016-04-03,2016-04-03,1967-04-01,3.8]\n",
"[2016-04-03,2016-04-03,1967-05-01,3.8]\n",
"[2016-04-03,2016-04-03,1967-06-01,3.9]\n",
"[2016-04-03,2016-04-03,1967-07-01,3.8]\n",
"[2016-04-03,2016-04-03,1967-08-01,3.8]\n",
"[2016-04-03,2016-04-03,1967-09-01,3.8]\n",
"[2016-04-03,2016-04-03,1967-10-01,4.0]\n",
"[2016-04-03,2016-04-03,1967-11-01,3.9]\n",
"[2016-04-03,2016-04-03,1967-12-01,3.8]\n",
"[2016-04-03,2016-04-03,1968-01-01,3.7]\n",
"[2016-04-03,2016-04-03,1968-02-01,3.8]\n",
"[2016-04-03,2016-04-03,1968-03-01,3.7]\n",
"[2016-04-03,2016-04-03,1968-04-01,3.5]\n",
"[2016-04-03,2016-04-03,1968-05-01,3.5]\n",
"[2016-04-03,2016-04-03,1968-06-01,3.7]\n",
"[2016-04-03,2016-04-03,1968-07-01,3.7]\n",
"[2016-04-03,2016-04-03,1968-08-01,3.5]\n",
"[2016-04-03,2016-04-03,1968-09-01,3.4]\n",
"[2016-04-03,2016-04-03,1968-10-01,3.4]\n",
"[2016-04-03,2016-04-03,1968-11-01,3.4]\n",
"[2016-04-03,2016-04-03,1968-12-01,3.4]\n",
"[2016-04-03,2016-04-03,1969-01-01,3.4]\n",
"[2016-04-03,2016-04-03,1969-02-01,3.4]\n",
"[2016-04-03,2016-04-03,1969-03-01,3.4]\n",
"[2016-04-03,2016-04-03,1969-04-01,3.4]\n",
"[2016-04-03,2016-04-03,1969-05-01,3.4]\n",
"[2016-04-03,2016-04-03,1969-06-01,3.5]\n",
"[2016-04-03,2016-04-03,1969-07-01,3.5]\n",
"[2016-04-03,2016-04-03,1969-08-01,3.5]\n",
"[2016-04-03,2016-04-03,1969-09-01,3.7]\n",
"[2016-04-03,2016-04-03,1969-10-01,3.7]\n",
"[2016-04-03,2016-04-03,1969-11-01,3.5]\n",
"[2016-04-03,2016-04-03,1969-12-01,3.5]\n",
"[2016-04-03,2016-04-03,1970-01-01,3.9]\n",
"[2016-04-03,2016-04-03,1970-02-01,4.2]\n",
"[2016-04-03,2016-04-03,1970-03-01,4.4]\n",
"[2016-04-03,2016-04-03,1970-04-01,4.6]\n",
"[2016-04-03,2016-04-03,1970-05-01,4.8]\n",
"[2016-04-03,2016-04-03,1970-06-01,4.9]\n",
"[2016-04-03,2016-04-03,1970-07-01,5.0]\n",
"[2016-04-03,2016-04-03,1970-08-01,5.1]\n",
"[2016-04-03,2016-04-03,1970-09-01,5.4]\n",
"[2016-04-03,2016-04-03,1970-10-01,5.5]\n",
"[2016-04-03,2016-04-03,1970-11-01,5.9]\n",
"[2016-04-03,2016-04-03,1970-12-01,6.1]\n",
"[2016-04-03,2016-04-03,1971-01-01,5.9]\n",
"[2016-04-03,2016-04-03,1971-02-01,5.9]\n",
"[2016-04-03,2016-04-03,1971-03-01,6.0]\n",
"[2016-04-03,2016-04-03,1971-04-01,5.9]\n",
"[2016-04-03,2016-04-03,1971-05-01,5.9]\n",
"[2016-04-03,2016-04-03,1971-06-01,5.9]\n",
"[2016-04-03,2016-04-03,1971-07-01,6.0]\n",
"[2016-04-03,2016-04-03,1971-08-01,6.1]\n",
"[2016-04-03,2016-04-03,1971-09-01,6.0]\n",
"[2016-04-03,2016-04-03,1971-10-01,5.8]\n",
"[2016-04-03,2016-04-03,1971-11-01,6.0]\n",
"[2016-04-03,2016-04-03,1971-12-01,6.0]\n",
"[2016-04-03,2016-04-03,1972-01-01,5.8]\n",
"[2016-04-03,2016-04-03,1972-02-01,5.7]\n",
"[2016-04-03,2016-04-03,1972-03-01,5.8]\n",
"[2016-04-03,2016-04-03,1972-04-01,5.7]\n",
"[2016-04-03,2016-04-03,1972-05-01,5.7]\n",
"[2016-04-03,2016-04-03,1972-06-01,5.7]\n",
"[2016-04-03,2016-04-03,1972-07-01,5.6]\n",
"[2016-04-03,2016-04-03,1972-08-01,5.6]\n",
"[2016-04-03,2016-04-03,1972-09-01,5.5]\n",
"[2016-04-03,2016-04-03,1972-10-01,5.6]\n",
"[2016-04-03,2016-04-03,1972-11-01,5.3]\n",
"[2016-04-03,2016-04-03,1972-12-01,5.2]\n",
"[2016-04-03,2016-04-03,1973-01-01,4.9]\n",
"[2016-04-03,2016-04-03,1973-02-01,5.0]\n",
"[2016-04-03,2016-04-03,1973-03-01,4.9]\n",
"[2016-04-03,2016-04-03,1973-04-01,5.0]\n",
"[2016-04-03,2016-04-03,1973-05-01,4.9]\n",
"[2016-04-03,2016-04-03,1973-06-01,4.9]\n",
"[2016-04-03,2016-04-03,1973-07-01,4.8]\n",
"[2016-04-03,2016-04-03,1973-08-01,4.8]\n",
"[2016-04-03,2016-04-03,1973-09-01,4.8]\n",
"[2016-04-03,2016-04-03,1973-10-01,4.6]\n",
"[2016-04-03,2016-04-03,1973-11-01,4.8]\n",
"[2016-04-03,2016-04-03,1973-12-01,4.9]\n",
"[2016-04-03,2016-04-03,1974-01-01,5.1]\n",
"[2016-04-03,2016-04-03,1974-02-01,5.2]\n",
"[2016-04-03,2016-04-03,1974-03-01,5.1]\n",
"[2016-04-03,2016-04-03,1974-04-01,5.1]\n",
"[2016-04-03,2016-04-03,1974-05-01,5.1]\n",
"[2016-04-03,2016-04-03,1974-06-01,5.4]\n",
"[2016-04-03,2016-04-03,1974-07-01,5.5]\n",
"[2016-04-03,2016-04-03,1974-08-01,5.5]\n",
"[2016-04-03,2016-04-03,1974-09-01,5.9]\n",
"[2016-04-03,2016-04-03,1974-10-01,6.0]\n",
"[2016-04-03,2016-04-03,1974-11-01,6.6]\n",
"[2016-04-03,2016-04-03,1974-12-01,7.2]\n",
"[2016-04-03,2016-04-03,1975-01-01,8.1]\n",
"[2016-04-03,2016-04-03,1975-02-01,8.1]\n",
"[2016-04-03,2016-04-03,1975-03-01,8.6]\n",
"[2016-04-03,2016-04-03,1975-04-01,8.8]\n",
"[2016-04-03,2016-04-03,1975-05-01,9.0]\n",
"[2016-04-03,2016-04-03,1975-06-01,8.8]\n",
"[2016-04-03,2016-04-03,1975-07-01,8.6]\n",
"[2016-04-03,2016-04-03,1975-08-01,8.4]\n",
"[2016-04-03,2016-04-03,1975-09-01,8.4]\n",
"[2016-04-03,2016-04-03,1975-10-01,8.4]\n",
"[2016-04-03,2016-04-03,1975-11-01,8.3]\n",
"[2016-04-03,2016-04-03,1975-12-01,8.2]\n",
"[2016-04-03,2016-04-03,1976-01-01,7.9]\n",
"[2016-04-03,2016-04-03,1976-02-01,7.7]\n",
"[2016-04-03,2016-04-03,1976-03-01,7.6]\n",
"[2016-04-03,2016-04-03,1976-04-01,7.7]\n",
"[2016-04-03,2016-04-03,1976-05-01,7.4]\n",
"[2016-04-03,2016-04-03,1976-06-01,7.6]\n",
"[2016-04-03,2016-04-03,1976-07-01,7.8]\n",
"[2016-04-03,2016-04-03,1976-08-01,7.8]\n",
"[2016-04-03,2016-04-03,1976-09-01,7.6]\n",
"[2016-04-03,2016-04-03,1976-10-01,7.7]\n",
"[2016-04-03,2016-04-03,1976-11-01,7.8]\n",
"[2016-04-03,2016-04-03,1976-12-01,7.8]\n",
"[2016-04-03,2016-04-03,1977-01-01,7.5]\n",
"[2016-04-03,2016-04-03,1977-02-01,7.6]\n",
"[2016-04-03,2016-04-03,1977-03-01,7.4]\n",
"[2016-04-03,2016-04-03,1977-04-01,7.2]\n",
"[2016-04-03,2016-04-03,1977-05-01,7.0]\n",
"[2016-04-03,2016-04-03,1977-06-01,7.2]\n",
"[2016-04-03,2016-04-03,1977-07-01,6.9]\n",
"[2016-04-03,2016-04-03,1977-08-01,7.0]\n",
"[2016-04-03,2016-04-03,1977-09-01,6.8]\n",
"[2016-04-03,2016-04-03,1977-10-01,6.8]\n",
"[2016-04-03,2016-04-03,1977-11-01,6.8]\n",
"[2016-04-03,2016-04-03,1977-12-01,6.4]\n",
"[2016-04-03,2016-04-03,1978-01-01,6.4]\n",
"[2016-04-03,2016-04-03,1978-02-01,6.3]\n",
"[2016-04-03,2016-04-03,1978-03-01,6.3]\n",
"[2016-04-03,2016-04-03,1978-04-01,6.1]\n",
"[2016-04-03,2016-04-03,1978-05-01,6.0]\n",
"[2016-04-03,2016-04-03,1978-06-01,5.9]\n",
"[2016-04-03,2016-04-03,1978-07-01,6.2]\n",
"[2016-04-03,2016-04-03,1978-08-01,5.9]\n",
"[2016-04-03,2016-04-03,1978-09-01,6.0]\n",
"[2016-04-03,2016-04-03,1978-10-01,5.8]\n",
"[2016-04-03,2016-04-03,1978-11-01,5.9]\n",
"[2016-04-03,2016-04-03,1978-12-01,6.0]\n",
"[2016-04-03,2016-04-03,1979-01-01,5.9]\n",
"[2016-04-03,2016-04-03,1979-02-01,5.9]\n",
"[2016-04-03,2016-04-03,1979-03-01,5.8]\n",
"[2016-04-03,2016-04-03,1979-04-01,5.8]\n",
"[2016-04-03,2016-04-03,1979-05-01,5.6]\n",
"[2016-04-03,2016-04-03,1979-06-01,5.7]\n",
"[2016-04-03,2016-04-03,1979-07-01,5.7]\n",
"[2016-04-03,2016-04-03,1979-08-01,6.0]\n",
"[2016-04-03,2016-04-03,1979-09-01,5.9]\n",
"[2016-04-03,2016-04-03,1979-10-01,6.0]\n",
"[2016-04-03,2016-04-03,1979-11-01,5.9]\n",
"[2016-04-03,2016-04-03,1979-12-01,6.0]\n",
"[2016-04-03,2016-04-03,1980-01-01,6.3]\n",
"[2016-04-03,2016-04-03,1980-02-01,6.3]\n",
"[2016-04-03,2016-04-03,1980-03-01,6.3]\n",
"[2016-04-03,2016-04-03,1980-04-01,6.9]\n",
"[2016-04-03,2016-04-03,1980-05-01,7.5]\n",
"[2016-04-03,2016-04-03,1980-06-01,7.6]\n",
"[2016-04-03,2016-04-03,1980-07-01,7.8]\n",
"[2016-04-03,2016-04-03,1980-08-01,7.7]\n",
"[2016-04-03,2016-04-03,1980-09-01,7.5]\n",
"[2016-04-03,2016-04-03,1980-10-01,7.5]\n",
"[2016-04-03,2016-04-03,1980-11-01,7.5]\n",
"[2016-04-03,2016-04-03,1980-12-01,7.2]\n",
"[2016-04-03,2016-04-03,1981-01-01,7.5]\n",
"[2016-04-03,2016-04-03,1981-02-01,7.4]\n",
"[2016-04-03,2016-04-03,1981-03-01,7.4]\n",
"[2016-04-03,2016-04-03,1981-04-01,7.2]\n",
"[2016-04-03,2016-04-03,1981-05-01,7.5]\n",
"[2016-04-03,2016-04-03,1981-06-01,7.5]\n",
"[2016-04-03,2016-04-03,1981-07-01,7.2]\n",
"[2016-04-03,2016-04-03,1981-08-01,7.4]\n",
"[2016-04-03,2016-04-03,1981-09-01,7.6]\n",
"[2016-04-03,2016-04-03,1981-10-01,7.9]\n",
"[2016-04-03,2016-04-03,1981-11-01,8.3]\n",
"[2016-04-03,2016-04-03,1981-12-01,8.5]\n",
"[2016-04-03,2016-04-03,1982-01-01,8.6]\n",
"[2016-04-03,2016-04-03,1982-02-01,8.9]\n",
"[2016-04-03,2016-04-03,1982-03-01,9.0]\n",
"[2016-04-03,2016-04-03,1982-04-01,9.3]\n",
"[2016-04-03,2016-04-03,1982-05-01,9.4]\n",
"[2016-04-03,2016-04-03,1982-06-01,9.6]\n",
"[2016-04-03,2016-04-03,1982-07-01,9.8]\n",
"[2016-04-03,2016-04-03,1982-08-01,9.8]\n",
"[2016-04-03,2016-04-03,1982-09-01,10.1]\n",
"[2016-04-03,2016-04-03,1982-10-01,10.4]\n",
"[2016-04-03,2016-04-03,1982-11-01,10.8]\n",
"[2016-04-03,2016-04-03,1982-12-01,10.8]\n",
"[2016-04-03,2016-04-03,1983-01-01,10.4]\n",
"[2016-04-03,2016-04-03,1983-02-01,10.4]\n",
"[2016-04-03,2016-04-03,1983-03-01,10.3]\n",
"[2016-04-03,2016-04-03,1983-04-01,10.2]\n",
"[2016-04-03,2016-04-03,1983-05-01,10.1]\n",
"[2016-04-03,2016-04-03,1983-06-01,10.1]\n",
"[2016-04-03,2016-04-03,1983-07-01,9.4]\n",
"[2016-04-03,2016-04-03,1983-08-01,9.5]\n",
"[2016-04-03,2016-04-03,1983-09-01,9.2]\n",
"[2016-04-03,2016-04-03,1983-10-01,8.8]\n",
"[2016-04-03,2016-04-03,1983-11-01,8.5]\n",
"[2016-04-03,2016-04-03,1983-12-01,8.3]\n",
"[2016-04-03,2016-04-03,1984-01-01,8.0]\n",
"[2016-04-03,2016-04-03,1984-02-01,7.8]\n",
"[2016-04-03,2016-04-03,1984-03-01,7.8]\n",
"[2016-04-03,2016-04-03,1984-04-01,7.7]\n",
"[2016-04-03,2016-04-03,1984-05-01,7.4]\n",
"[2016-04-03,2016-04-03,1984-06-01,7.2]\n",
"[2016-04-03,2016-04-03,1984-07-01,7.5]\n",
"[2016-04-03,2016-04-03,1984-08-01,7.5]\n",
"[2016-04-03,2016-04-03,1984-09-01,7.3]\n",
"[2016-04-03,2016-04-03,1984-10-01,7.4]\n",
"[2016-04-03,2016-04-03,1984-11-01,7.2]\n",
"[2016-04-03,2016-04-03,1984-12-01,7.3]\n",
"[2016-04-03,2016-04-03,1985-01-01,7.3]\n",
"[2016-04-03,2016-04-03,1985-02-01,7.2]\n",
"[2016-04-03,2016-04-03,1985-03-01,7.2]\n",
"[2016-04-03,2016-04-03,1985-04-01,7.3]\n",
"[2016-04-03,2016-04-03,1985-05-01,7.2]\n",
"[2016-04-03,2016-04-03,1985-06-01,7.4]\n",
"[2016-04-03,2016-04-03,1985-07-01,7.4]\n",
"[2016-04-03,2016-04-03,1985-08-01,7.1]\n",
"[2016-04-03,2016-04-03,1985-09-01,7.1]\n",
"[2016-04-03,2016-04-03,1985-10-01,7.1]\n",
"[2016-04-03,2016-04-03,1985-11-01,7.0]\n",
"[2016-04-03,2016-04-03,1985-12-01,7.0]\n",
"[2016-04-03,2016-04-03,1986-01-01,6.7]\n",
"[2016-04-03,2016-04-03,1986-02-01,7.2]\n",
"[2016-04-03,2016-04-03,1986-03-01,7.2]\n",
"[2016-04-03,2016-04-03,1986-04-01,7.1]\n",
"[2016-04-03,2016-04-03,1986-05-01,7.2]\n",
"[2016-04-03,2016-04-03,1986-06-01,7.2]\n",
"[2016-04-03,2016-04-03,1986-07-01,7.0]\n",
"[2016-04-03,2016-04-03,1986-08-01,6.9]\n",
"[2016-04-03,2016-04-03,1986-09-01,7.0]\n",
"[2016-04-03,2016-04-03,1986-10-01,7.0]\n",
"[2016-04-03,2016-04-03,1986-11-01,6.9]\n",
"[2016-04-03,2016-04-03,1986-12-01,6.6]\n",
"[2016-04-03,2016-04-03,1987-01-01,6.6]\n",
"[2016-04-03,2016-04-03,1987-02-01,6.6]\n",
"[2016-04-03,2016-04-03,1987-03-01,6.6]\n",
"[2016-04-03,2016-04-03,1987-04-01,6.3]\n",
"[2016-04-03,2016-04-03,1987-05-01,6.3]\n",
"[2016-04-03,2016-04-03,1987-06-01,6.2]\n",
"[2016-04-03,2016-04-03,1987-07-01,6.1]\n",
"[2016-04-03,2016-04-03,1987-08-01,6.0]\n",
"[2016-04-03,2016-04-03,1987-09-01,5.9]\n",
"[2016-04-03,2016-04-03,1987-10-01,6.0]\n",
"[2016-04-03,2016-04-03,1987-11-01,5.8]\n",
"[2016-04-03,2016-04-03,1987-12-01,5.7]\n",
"[2016-04-03,2016-04-03,1988-01-01,5.7]\n",
"[2016-04-03,2016-04-03,1988-02-01,5.7]\n",
"[2016-04-03,2016-04-03,1988-03-01,5.7]\n",
"[2016-04-03,2016-04-03,1988-04-01,5.4]\n",
"[2016-04-03,2016-04-03,1988-05-01,5.6]\n",
"[2016-04-03,2016-04-03,1988-06-01,5.4]\n",
"[2016-04-03,2016-04-03,1988-07-01,5.4]\n",
"[2016-04-03,2016-04-03,1988-08-01,5.6]\n",
"[2016-04-03,2016-04-03,1988-09-01,5.4]\n",
"[2016-04-03,2016-04-03,1988-10-01,5.4]\n",
"[2016-04-03,2016-04-03,1988-11-01,5.3]\n",
"[2016-04-03,2016-04-03,1988-12-01,5.3]\n",
"[2016-04-03,2016-04-03,1989-01-01,5.4]\n",
"[2016-04-03,2016-04-03,1989-02-01,5.2]\n",
"[2016-04-03,2016-04-03,1989-03-01,5.0]\n",
"[2016-04-03,2016-04-03,1989-04-01,5.2]\n",
"[2016-04-03,2016-04-03,1989-05-01,5.2]\n",
"[2016-04-03,2016-04-03,1989-06-01,5.3]\n",
"[2016-04-03,2016-04-03,1989-07-01,5.2]\n",
"[2016-04-03,2016-04-03,1989-08-01,5.2]\n",
"[2016-04-03,2016-04-03,1989-09-01,5.3]\n",
"[2016-04-03,2016-04-03,1989-10-01,5.3]\n",
"[2016-04-03,2016-04-03,1989-11-01,5.4]\n",
"[2016-04-03,2016-04-03,1989-12-01,5.4]\n",
"[2016-04-03,2016-04-03,1990-01-01,5.4]\n",
"[2016-04-03,2016-04-03,1990-02-01,5.3]\n",
"[2016-04-03,2016-04-03,1990-03-01,5.2]\n",
"[2016-04-03,2016-04-03,1990-04-01,5.4]\n",
"[2016-04-03,2016-04-03,1990-05-01,5.4]\n",
"[2016-04-03,2016-04-03,1990-06-01,5.2]\n",
"[2016-04-03,2016-04-03,1990-07-01,5.5]\n",
"[2016-04-03,2016-04-03,1990-08-01,5.7]\n",
"[2016-04-03,2016-04-03,1990-09-01,5.9]\n",
"[2016-04-03,2016-04-03,1990-10-01,5.9]\n",
"[2016-04-03,2016-04-03,1990-11-01,6.2]\n",
"[2016-04-03,2016-04-03,1990-12-01,6.3]\n",
"[2016-04-03,2016-04-03,1991-01-01,6.4]\n",
"[2016-04-03,2016-04-03,1991-02-01,6.6]\n",
"[2016-04-03,2016-04-03,1991-03-01,6.8]\n",
"[2016-04-03,2016-04-03,1991-04-01,6.7]\n",
"[2016-04-03,2016-04-03,1991-05-01,6.9]\n",
"[2016-04-03,2016-04-03,1991-06-01,6.9]\n",
"[2016-04-03,2016-04-03,1991-07-01,6.8]\n",
"[2016-04-03,2016-04-03,1991-08-01,6.9]\n",
"[2016-04-03,2016-04-03,1991-09-01,6.9]\n",
"[2016-04-03,2016-04-03,1991-10-01,7.0]\n",
"[2016-04-03,2016-04-03,1991-11-01,7.0]\n",
"[2016-04-03,2016-04-03,1991-12-01,7.3]\n",
"[2016-04-03,2016-04-03,1992-01-01,7.3]\n",
"[2016-04-03,2016-04-03,1992-02-01,7.4]\n",
"[2016-04-03,2016-04-03,1992-03-01,7.4]\n",
"[2016-04-03,2016-04-03,1992-04-01,7.4]\n",
"[2016-04-03,2016-04-03,1992-05-01,7.6]\n",
"[2016-04-03,2016-04-03,1992-06-01,7.8]\n",
"[2016-04-03,2016-04-03,1992-07-01,7.7]\n",
"[2016-04-03,2016-04-03,1992-08-01,7.6]\n",
"[2016-04-03,2016-04-03,1992-09-01,7.6]\n",
"[2016-04-03,2016-04-03,1992-10-01,7.3]\n",
"[2016-04-03,2016-04-03,1992-11-01,7.4]\n",
"[2016-04-03,2016-04-03,1992-12-01,7.4]\n",
"[2016-04-03,2016-04-03,1993-01-01,7.3]\n",
"[2016-04-03,2016-04-03,1993-02-01,7.1]\n",
"[2016-04-03,2016-04-03,1993-03-01,7.0]\n",
"[2016-04-03,2016-04-03,1993-04-01,7.1]\n",
"[2016-04-03,2016-04-03,1993-05-01,7.1]\n",
"[2016-04-03,2016-04-03,1993-06-01,7.0]\n",
"[2016-04-03,2016-04-03,1993-07-01,6.9]\n",
"[2016-04-03,2016-04-03,1993-08-01,6.8]\n",
"[2016-04-03,2016-04-03,1993-09-01,6.7]\n",
"[2016-04-03,2016-04-03,1993-10-01,6.8]\n",
"[2016-04-03,2016-04-03,1993-11-01,6.6]\n",
"[2016-04-03,2016-04-03,1993-12-01,6.5]\n",
"[2016-04-03,2016-04-03,1994-01-01,6.6]\n",
"[2016-04-03,2016-04-03,1994-02-01,6.6]\n",
"[2016-04-03,2016-04-03,1994-03-01,6.5]\n",
"[2016-04-03,2016-04-03,1994-04-01,6.4]\n",
"[2016-04-03,2016-04-03,1994-05-01,6.1]\n",
"[2016-04-03,2016-04-03,1994-06-01,6.1]\n",
"[2016-04-03,2016-04-03,1994-07-01,6.1]\n",
"[2016-04-03,2016-04-03,1994-08-01,6.0]\n",
"[2016-04-03,2016-04-03,1994-09-01,5.9]\n",
"[2016-04-03,2016-04-03,1994-10-01,5.8]\n",
"[2016-04-03,2016-04-03,1994-11-01,5.6]\n",
"[2016-04-03,2016-04-03,1994-12-01,5.5]\n",
"[2016-04-03,2016-04-03,1995-01-01,5.6]\n",
"[2016-04-03,2016-04-03,1995-02-01,5.4]\n",
"[2016-04-03,2016-04-03,1995-03-01,5.4]\n",
"[2016-04-03,2016-04-03,1995-04-01,5.8]\n",
"[2016-04-03,2016-04-03,1995-05-01,5.6]\n",
"[2016-04-03,2016-04-03,1995-06-01,5.6]\n",
"[2016-04-03,2016-04-03,1995-07-01,5.7]\n",
"[2016-04-03,2016-04-03,1995-08-01,5.7]\n",
"[2016-04-03,2016-04-03,1995-09-01,5.6]\n",
"[2016-04-03,2016-04-03,1995-10-01,5.5]\n",
"[2016-04-03,2016-04-03,1995-11-01,5.6]\n",
"[2016-04-03,2016-04-03,1995-12-01,5.6]\n",
"[2016-04-03,2016-04-03,1996-01-01,5.6]\n",
"[2016-04-03,2016-04-03,1996-02-01,5.5]\n",
"[2016-04-03,2016-04-03,1996-03-01,5.5]\n",
"[2016-04-03,2016-04-03,1996-04-01,5.6]\n",
"[2016-04-03,2016-04-03,1996-05-01,5.6]\n",
"[2016-04-03,2016-04-03,1996-06-01,5.3]\n",
"[2016-04-03,2016-04-03,1996-07-01,5.5]\n",
"[2016-04-03,2016-04-03,1996-08-01,5.1]\n",
"[2016-04-03,2016-04-03,1996-09-01,5.2]\n",
"[2016-04-03,2016-04-03,1996-10-01,5.2]\n",
"[2016-04-03,2016-04-03,1996-11-01,5.4]\n",
"[2016-04-03,2016-04-03,1996-12-01,5.4]\n",
"[2016-04-03,2016-04-03,1997-01-01,5.3]\n",
"[2016-04-03,2016-04-03,1997-02-01,5.2]\n",
"[2016-04-03,2016-04-03,1997-03-01,5.2]\n",
"[2016-04-03,2016-04-03,1997-04-01,5.1]\n",
"[2016-04-03,2016-04-03,1997-05-01,4.9]\n",
"[2016-04-03,2016-04-03,1997-06-01,5.0]\n",
"[2016-04-03,2016-04-03,1997-07-01,4.9]\n",
"[2016-04-03,2016-04-03,1997-08-01,4.8]\n",
"[2016-04-03,2016-04-03,1997-09-01,4.9]\n",
"[2016-04-03,2016-04-03,1997-10-01,4.7]\n",
"[2016-04-03,2016-04-03,1997-11-01,4.6]\n",
"[2016-04-03,2016-04-03,1997-12-01,4.7]\n",
"[2016-04-03,2016-04-03,1998-01-01,4.6]\n",
"[2016-04-03,2016-04-03,1998-02-01,4.6]\n",
"[2016-04-03,2016-04-03,1998-03-01,4.7]\n",
"[2016-04-03,2016-04-03,1998-04-01,4.3]\n",
"[2016-04-03,2016-04-03,1998-05-01,4.4]\n",
"[2016-04-03,2016-04-03,1998-06-01,4.5]\n",
"[2016-04-03,2016-04-03,1998-07-01,4.5]\n",
"[2016-04-03,2016-04-03,1998-08-01,4.5]\n",
"[2016-04-03,2016-04-03,1998-09-01,4.6]\n",
"[2016-04-03,2016-04-03,1998-10-01,4.5]\n",
"[2016-04-03,2016-04-03,1998-11-01,4.4]\n",
"[2016-04-03,2016-04-03,1998-12-01,4.4]\n",
"[2016-04-03,2016-04-03,1999-01-01,4.3]\n",
"[2016-04-03,2016-04-03,1999-02-01,4.4]\n",
"[2016-04-03,2016-04-03,1999-03-01,4.2]\n",
"[2016-04-03,2016-04-03,1999-04-01,4.3]\n",
"[2016-04-03,2016-04-03,1999-05-01,4.2]\n",
"[2016-04-03,2016-04-03,1999-06-01,4.3]\n",
"[2016-04-03,2016-04-03,1999-07-01,4.3]\n",
"[2016-04-03,2016-04-03,1999-08-01,4.2]\n",
"[2016-04-03,2016-04-03,1999-09-01,4.2]\n",
"[2016-04-03,2016-04-03,1999-10-01,4.1]\n",
"[2016-04-03,2016-04-03,1999-11-01,4.1]\n",
"[2016-04-03,2016-04-03,1999-12-01,4.0]\n",
"[2016-04-03,2016-04-03,2000-01-01,4.0]\n",
"[2016-04-03,2016-04-03,2000-02-01,4.1]\n",
"[2016-04-03,2016-04-03,2000-03-01,4.0]\n",
"[2016-04-03,2016-04-03,2000-04-01,3.8]\n",
"[2016-04-03,2016-04-03,2000-05-01,4.0]\n",
"[2016-04-03,2016-04-03,2000-06-01,4.0]\n",
"[2016-04-03,2016-04-03,2000-07-01,4.0]\n",
"[2016-04-03,2016-04-03,2000-08-01,4.1]\n",
"[2016-04-03,2016-04-03,2000-09-01,3.9]\n",
"[2016-04-03,2016-04-03,2000-10-01,3.9]\n",
"[2016-04-03,2016-04-03,2000-11-01,3.9]\n",
"[2016-04-03,2016-04-03,2000-12-01,3.9]\n",
"[2016-04-03,2016-04-03,2001-01-01,4.2]\n",
"[2016-04-03,2016-04-03,2001-02-01,4.2]\n",
"[2016-04-03,2016-04-03,2001-03-01,4.3]\n",
"[2016-04-03,2016-04-03,2001-04-01,4.4]\n",
"[2016-04-03,2016-04-03,2001-05-01,4.3]\n",
"[2016-04-03,2016-04-03,2001-06-01,4.5]\n",
"[2016-04-03,2016-04-03,2001-07-01,4.6]\n",
"[2016-04-03,2016-04-03,2001-08-01,4.9]\n",
"[2016-04-03,2016-04-03,2001-09-01,5.0]\n",
"[2016-04-03,2016-04-03,2001-10-01,5.3]\n",
"[2016-04-03,2016-04-03,2001-11-01,5.5]\n",
"[2016-04-03,2016-04-03,2001-12-01,5.7]\n",
"[2016-04-03,2016-04-03,2002-01-01,5.7]\n",
"[2016-04-03,2016-04-03,2002-02-01,5.7]\n",
"[2016-04-03,2016-04-03,2002-03-01,5.7]\n",
"[2016-04-03,2016-04-03,2002-04-01,5.9]\n",
"[2016-04-03,2016-04-03,2002-05-01,5.8]\n",
"[2016-04-03,2016-04-03,2002-06-01,5.8]\n",
"[2016-04-03,2016-04-03,2002-07-01,5.8]\n",
"[2016-04-03,2016-04-03,2002-08-01,5.7]\n",
"[2016-04-03,2016-04-03,2002-09-01,5.7]\n",
"[2016-04-03,2016-04-03,2002-10-01,5.7]\n",
"[2016-04-03,2016-04-03,2002-11-01,5.9]\n",
"[2016-04-03,2016-04-03,2002-12-01,6.0]\n",
"[2016-04-03,2016-04-03,2003-01-01,5.8]\n",
"[2016-04-03,2016-04-03,2003-02-01,5.9]\n",
"[2016-04-03,2016-04-03,2003-03-01,5.9]\n",
"[2016-04-03,2016-04-03,2003-04-01,6.0]\n",
"[2016-04-03,2016-04-03,2003-05-01,6.1]\n",
"[2016-04-03,2016-04-03,2003-06-01,6.3]\n",
"[2016-04-03,2016-04-03,2003-07-01,6.2]\n",
"[2016-04-03,2016-04-03,2003-08-01,6.1]\n",
"[2016-04-03,2016-04-03,2003-09-01,6.1]\n",
"[2016-04-03,2016-04-03,2003-10-01,6.0]\n",
"[2016-04-03,2016-04-03,2003-11-01,5.8]\n",
"[2016-04-03,2016-04-03,2003-12-01,5.7]\n",
"[2016-04-03,2016-04-03,2004-01-01,5.7]\n",
"[2016-04-03,2016-04-03,2004-02-01,5.6]\n",
"[2016-04-03,2016-04-03,2004-03-01,5.8]\n",
"[2016-04-03,2016-04-03,2004-04-01,5.6]\n",
"[2016-04-03,2016-04-03,2004-05-01,5.6]\n",
"[2016-04-03,2016-04-03,2004-06-01,5.6]\n",
"[2016-04-03,2016-04-03,2004-07-01,5.5]\n",
"[2016-04-03,2016-04-03,2004-08-01,5.4]\n",
"[2016-04-03,2016-04-03,2004-09-01,5.4]\n",
"[2016-04-03,2016-04-03,2004-10-01,5.5]\n",
"[2016-04-03,2016-04-03,2004-11-01,5.4]\n",
"[2016-04-03,2016-04-03,2004-12-01,5.4]\n",
"[2016-04-03,2016-04-03,2005-01-01,5.3]\n",
"[2016-04-03,2016-04-03,2005-02-01,5.4]\n",
"[2016-04-03,2016-04-03,2005-03-01,5.2]\n",
"[2016-04-03,2016-04-03,2005-04-01,5.2]\n",
"[2016-04-03,2016-04-03,2005-05-01,5.1]\n",
"[2016-04-03,2016-04-03,2005-06-01,5.0]\n",
"[2016-04-03,2016-04-03,2005-07-01,5.0]\n",
"[2016-04-03,2016-04-03,2005-08-01,4.9]\n",
"[2016-04-03,2016-04-03,2005-09-01,5.0]\n",
"[2016-04-03,2016-04-03,2005-10-01,5.0]\n",
"[2016-04-03,2016-04-03,2005-11-01,5.0]\n",
"[2016-04-03,2016-04-03,2005-12-01,4.9]\n",
"[2016-04-03,2016-04-03,2006-01-01,4.7]\n",
"[2016-04-03,2016-04-03,2006-02-01,4.8]\n",
"[2016-04-03,2016-04-03,2006-03-01,4.7]\n",
"[2016-04-03,2016-04-03,2006-04-01,4.7]\n",
"[2016-04-03,2016-04-03,2006-05-01,4.6]\n",
"[2016-04-03,2016-04-03,2006-06-01,4.6]\n",
"[2016-04-03,2016-04-03,2006-07-01,4.7]\n",
"[2016-04-03,2016-04-03,2006-08-01,4.7]\n",
"[2016-04-03,2016-04-03,2006-09-01,4.5]\n",
"[2016-04-03,2016-04-03,2006-10-01,4.4]\n",
"[2016-04-03,2016-04-03,2006-11-01,4.5]\n",
"[2016-04-03,2016-04-03,2006-12-01,4.4]\n",
"[2016-04-03,2016-04-03,2007-01-01,4.6]\n",
"[2016-04-03,2016-04-03,2007-02-01,4.5]\n",
"[2016-04-03,2016-04-03,2007-03-01,4.4]\n",
"[2016-04-03,2016-04-03,2007-04-01,4.5]\n",
"[2016-04-03,2016-04-03,2007-05-01,4.4]\n",
"[2016-04-03,2016-04-03,2007-06-01,4.6]\n",
"[2016-04-03,2016-04-03,2007-07-01,4.7]\n",
"[2016-04-03,2016-04-03,2007-08-01,4.6]\n",
"[2016-04-03,2016-04-03,2007-09-01,4.7]\n",
"[2016-04-03,2016-04-03,2007-10-01,4.7]\n",
"[2016-04-03,2016-04-03,2007-11-01,4.7]\n",
"[2016-04-03,2016-04-03,2007-12-01,5.0]\n",
"[2016-04-03,2016-04-03,2008-01-01,5.0]\n",
"[2016-04-03,2016-04-03,2008-02-01,4.9]\n",
"[2016-04-03,2016-04-03,2008-03-01,5.1]\n",
"[2016-04-03,2016-04-03,2008-04-01,5.0]\n",
"[2016-04-03,2016-04-03,2008-05-01,5.4]\n",
"[2016-04-03,2016-04-03,2008-06-01,5.6]\n",
"[2016-04-03,2016-04-03,2008-07-01,5.8]\n",
"[2016-04-03,2016-04-03,2008-08-01,6.1]\n",
"[2016-04-03,2016-04-03,2008-09-01,6.1]\n",
"[2016-04-03,2016-04-03,2008-10-01,6.5]\n",
"[2016-04-03,2016-04-03,2008-11-01,6.8]\n",
"[2016-04-03,2016-04-03,2008-12-01,7.3]\n",
"[2016-04-03,2016-04-03,2009-01-01,7.8]\n",
"[2016-04-03,2016-04-03,2009-02-01,8.3]\n",
"[2016-04-03,2016-04-03,2009-03-01,8.7]\n",
"[2016-04-03,2016-04-03,2009-04-01,9.0]\n",
"[2016-04-03,2016-04-03,2009-05-01,9.4]\n",
"[2016-04-03,2016-04-03,2009-06-01,9.5]\n",
"[2016-04-03,2016-04-03,2009-07-01,9.5]\n",
"[2016-04-03,2016-04-03,2009-08-01,9.6]\n",
"[2016-04-03,2016-04-03,2009-09-01,9.8]\n",
"[2016-04-03,2016-04-03,2009-10-01,10.0]\n",
"[2016-04-03,2016-04-03,2009-11-01,9.9]\n",
"[2016-04-03,2016-04-03,2009-12-01,9.9]\n",
"[2016-04-03,2016-04-03,2010-01-01,9.8]\n",
"[2016-04-03,2016-04-03,2010-02-01,9.8]\n",
"[2016-04-03,2016-04-03,2010-03-01,9.9]\n",
"[2016-04-03,2016-04-03,2010-04-01,9.9]\n",
"[2016-04-03,2016-04-03,2010-05-01,9.6]\n",
"[2016-04-03,2016-04-03,2010-06-01,9.4]\n",
"[2016-04-03,2016-04-03,2010-07-01,9.4]\n",
"[2016-04-03,2016-04-03,2010-08-01,9.5]\n",
"[2016-04-03,2016-04-03,2010-09-01,9.5]\n",
"[2016-04-03,2016-04-03,2010-10-01,9.4]\n",
"[2016-04-03,2016-04-03,2010-11-01,9.8]\n",
"[2016-04-03,2016-04-03,2010-12-01,9.3]\n",
"[2016-04-03,2016-04-03,2011-01-01,9.1]\n",
"[2016-04-03,2016-04-03,2011-02-01,9.0]\n",
"[2016-04-03,2016-04-03,2011-03-01,9.0]\n",
"[2016-04-03,2016-04-03,2011-04-01,9.1]\n",
"[2016-04-03,2016-04-03,2011-05-01,9.0]\n",
"[2016-04-03,2016-04-03,2011-06-01,9.1]\n",
"[2016-04-03,2016-04-03,2011-07-01,9.0]\n",
"[2016-04-03,2016-04-03,2011-08-01,9.0]\n",
"[2016-04-03,2016-04-03,2011-09-01,9.0]\n",
"[2016-04-03,2016-04-03,2011-10-01,8.8]\n",
"[2016-04-03,2016-04-03,2011-11-01,8.6]\n",
"[2016-04-03,2016-04-03,2011-12-01,8.5]\n",
"[2016-04-03,2016-04-03,2012-01-01,8.3]\n",
"[2016-04-03,2016-04-03,2012-02-01,8.3]\n",
"[2016-04-03,2016-04-03,2012-03-01,8.2]\n",
"[2016-04-03,2016-04-03,2012-04-01,8.2]\n",
"[2016-04-03,2016-04-03,2012-05-01,8.2]\n",
"[2016-04-03,2016-04-03,2012-06-01,8.2]\n",
"[2016-04-03,2016-04-03,2012-07-01,8.2]\n",
"[2016-04-03,2016-04-03,2012-08-01,8.1]\n",
"[2016-04-03,2016-04-03,2012-09-01,7.8]\n",
"[2016-04-03,2016-04-03,2012-10-01,7.8]\n",
"[2016-04-03,2016-04-03,2012-11-01,7.7]\n",
"[2016-04-03,2016-04-03,2012-12-01,7.9]\n",
"[2016-04-03,2016-04-03,2013-01-01,8.0]\n",
"[2016-04-03,2016-04-03,2013-02-01,7.7]\n",
"[2016-04-03,2016-04-03,2013-03-01,7.5]\n",
"[2016-04-03,2016-04-03,2013-04-01,7.6]\n",
"[2016-04-03,2016-04-03,2013-05-01,7.5]\n",
"[2016-04-03,2016-04-03,2013-06-01,7.5]\n",
"[2016-04-03,2016-04-03,2013-07-01,7.3]\n",
"[2016-04-03,2016-04-03,2013-08-01,7.3]\n",
"[2016-04-03,2016-04-03,2013-09-01,7.3]\n",
"[2016-04-03,2016-04-03,2013-10-01,7.2]\n",
"[2016-04-03,2016-04-03,2013-11-01,6.9]\n",
"[2016-04-03,2016-04-03,2013-12-01,6.7]\n",
"[2016-04-03,2016-04-03,2014-01-01,6.6]\n",
"[2016-04-03,2016-04-03,2014-02-01,6.7]\n",
"[2016-04-03,2016-04-03,2014-03-01,6.7]\n",
"[2016-04-03,2016-04-03,2014-04-01,6.2]\n",
"[2016-04-03,2016-04-03,2014-05-01,6.2]\n",
"[2016-04-03,2016-04-03,2014-06-01,6.1]\n",
"[2016-04-03,2016-04-03,2014-07-01,6.2]\n",
"[2016-04-03,2016-04-03,2014-08-01,6.2]\n",
"[2016-04-03,2016-04-03,2014-09-01,6.0]\n",
"[2016-04-03,2016-04-03,2014-10-01,5.7]\n",
"[2016-04-03,2016-04-03,2014-11-01,5.8]\n",
"[2016-04-03,2016-04-03,2014-12-01,5.6]\n",
"[2016-04-03,2016-04-03,2015-01-01,5.7]\n",
"[2016-04-03,2016-04-03,2015-02-01,5.5]\n",
"[2016-04-03,2016-04-03,2015-03-01,5.5]\n",
"[2016-04-03,2016-04-03,2015-04-01,5.4]\n",
"[2016-04-03,2016-04-03,2015-05-01,5.5]\n",
"[2016-04-03,2016-04-03,2015-06-01,5.3]\n",
"[2016-04-03,2016-04-03,2015-07-01,5.3]\n",
"[2016-04-03,2016-04-03,2015-08-01,5.1]\n",
"[2016-04-03,2016-04-03,2015-09-01,5.1]\n",
"[2016-04-03,2016-04-03,2015-10-01,5.0]\n",
"[2016-04-03,2016-04-03,2015-11-01,5.0]\n",
"[2016-04-03,2016-04-03,2015-12-01,5.0]\n",
"[2016-04-03,2016-04-03,2016-01-01,4.9]\n",
"[2016-04-03,2016-04-03,2016-02-01,4.9]\n",
"[2016-04-03,2016-04-03,2016-03-01,5.0]\n"
]
}
],
"source": [
"finalProduct.collect.foreach(println)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Visualizing the table in the second, in a more programmatical way, the following is seen. "
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Here is the view of the final schema."
]
},
{
"cell_type": "code",
"execution_count": 42,
"metadata": {
"collapsed": false,
"scrolled": true
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"root\n",
" |-- realtime_start: string (nullable = true)\n",
" |-- realtime_end: string (nullable = true)\n",
" |-- date: string (nullable = true)\n",
" |-- value: string (nullable = true)\n",
"\n"
]
}
],
"source": [
"finalProduct.printSchema"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"It's possible to get the finalProduct DataFrame explain method. Perhaps it's messy? "
]
},
{
"cell_type": "code",
"execution_count": 43,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"== Physical Plan ==\n",
"TungstenProject [rowData#422.realtime_start AS realtime_start#693,rowData#422.realtime_end AS realtime_end#694,rowData#422.date AS date#695,rowData#422.value AS value#696]\n",
" !Generate explode(observations#5), false, false, [rowData#422]\n",
" InMemoryColumnarTableScan [observations#5], (InMemoryRelation [count#0L,file_type#1,limit#2L,observation_end#3,observation_start#4,observations#5,offset#6L,order_by#7,output_type#8L,realtime_end#9,realtime_start#10,sort_order#11,units#12], true, 10000, StorageLevel(true, true, false, true, 1), (Scan JSONRelation[][count#0L,file_type#1,limit#2L,observation_end#3,observation_start#4,observations#5,offset#6L,order_by#7,output_type#8L,realtime_end#9,realtime_start#10,sort_order#11,units#12]), None)\n"
]
}
],
"source": [
"finalProduct.explain"
]
},
{
"cell_type": "code",
"execution_count": 44,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"import java.util.Properties\n",
"val properties = new Properties()\n",
"\n",
"finalProduct.write.mode(org.apache.spark.sql.SaveMode.Overwrite).jdbc(sys.env(\"SQL_CONNECTION\"), \"MyTableName\", properties)\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"#### Did the write table to PostgreSQL work?\n",
"\n",
"Looking at the PostgreSQL database, the table can be found and examined.\n",
"\n",
"Sidebar: I'm asked to vacuum the table in the PostgreSQL pgAmin tool. Since the data is there and it seems ok, I'm going to ignore this for now. If there are ideas as to why this occurs - like adding properties or forcing another schema type on the data, please contact me. I'd be interested in knowing. \n"
]
},
{
"cell_type": "markdown",
"metadata": {
"collapsed": false
},
"source": [
"### How is it possible write this new (partial table of the complex orginal JSON) table to PostgreSQL?\n",
"\n",
"With SQL_CONNECTION string that looks something like this: \n",
"```\n",
"jdbc:postgresql://localhost:5432/mydatabasename?user=postgresusername&password=password\n",
"```\n",
"and some more setup not yet discussed, you declare some Java properties. In this case, (1) data was decidely overwritten and (2) properties are empty as the password and user name are in the connection string. (See:\n",
"http://www.infoobjects.com/spark-connecting-to-a-jdbc-data-source-using-dataframes/ )\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### How is it possible read a table from PostgreSQL?\n",
"\n",
"Can the data be read back from the PostgreSQL database? This seems pretty straight forward:"
]
},
{
"cell_type": "code",
"execution_count": 45,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"+--------------+------------+----------+-----+\n",
"|realtime_start|realtime_end| date|value|\n",
"+--------------+------------+----------+-----+\n",
"| 2016-04-03| 2016-04-03|1948-01-01| 3.4|\n",
"| 2016-04-03| 2016-04-03|1948-02-01| 3.8|\n",
"| 2016-04-03| 2016-04-03|1948-03-01| 4.0|\n",
"| 2016-04-03| 2016-04-03|1948-04-01| 3.9|\n",
"| 2016-04-03| 2016-04-03|1948-05-01| 3.5|\n",
"| 2016-04-03| 2016-04-03|1948-06-01| 3.6|\n",
"| 2016-04-03| 2016-04-03|1948-07-01| 3.6|\n",
"| 2016-04-03| 2016-04-03|1948-08-01| 3.9|\n",
"| 2016-04-03| 2016-04-03|1948-09-01| 3.8|\n",
"| 2016-04-03| 2016-04-03|1948-10-01| 3.7|\n",
"| 2016-04-03| 2016-04-03|1948-11-01| 3.8|\n",
"| 2016-04-03| 2016-04-03|1948-12-01| 4.0|\n",
"| 2016-04-03| 2016-04-03|1949-01-01| 4.3|\n",
"| 2016-04-03| 2016-04-03|1949-02-01| 4.7|\n",
"| 2016-04-03| 2016-04-03|1949-03-01| 5.0|\n",
"| 2016-04-03| 2016-04-03|1949-04-01| 5.3|\n",
"| 2016-04-03| 2016-04-03|1949-05-01| 6.1|\n",
"| 2016-04-03| 2016-04-03|1949-06-01| 6.2|\n",
"| 2016-04-03| 2016-04-03|1949-07-01| 6.7|\n",
"| 2016-04-03| 2016-04-03|1949-08-01| 6.8|\n",
"+--------------+------------+----------+-----+\n",
"only showing top 20 rows\n",
"\n"
]
}
],
"source": [
"val readDF = sqlContext.read.format(\"jdbc\").\n",
" options(Map(\"url\" -> sys.env(\"SQL_CONNECTION\"), \"dbtable\" -> \"MyTableName\")).load()\n",
"\n",
"readDF.show"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"A secondary look:"
]
},
{
"cell_type": "code",
"execution_count": 46,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/plain": [
"Array({\"realtime_start\":\"2016-04-03\",\"realtime_end\":\"2016-04-03\",\"date\":\"1948-01-01\",\"value\":\"3.4\"}, {\"realtime_start\":\"2016-04-03\",\"realtime_end\":\"2016-04-03\",\"date\":\"1948-02-01\",\"value\":\"3.8\"}, {\"realtime_start\":\"2016-04-03\",\"realtime_end\":\"2016-04-03\",\"date\":\"1948-03-01\",\"value\":\"4.0\"}, {\"realtime_start\":\"2016-04-03\",\"realtime_end\":\"2016-04-03\",\"date\":\"1948-04-01\",\"value\":\"3.9\"}, {\"realtime_start\":\"2016-04-03\",\"realtime_end\":\"2016-04-03\",\"date\":\"1948-05-01\",\"value\":\"3.5\"}, {\"realtime_start\":\"2016-04-03\",\"realtime_end\":\"2016-04-03\",\"date\":\"1948-06-01\",\"value\":\"3.6\"}, {\"realtime_start\":\"2016-04-03\",\"realtime_end\":\"2016-04-03\",\"date\":\"1948-07-01\",\"value\":\"3.6\"}, {\"realtime_start\":\"2016-04-03\",\"realtime_end\":\"2016-04-03\",\"date\":\"1948-08-01\",\"value\":\"3.9\"}, {\"realti..."
]
},
"execution_count": 46,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"readDF.toJSON.collect"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### How can Spark to be configured to read / write to a JDBC database?\n",
"\n",
"Edit the ~/.bash_profile file to add:\n",
"\n",
"```\n",
"export SPARK_PKGS=$(cat << END | xargs echo | sed 's/ /,/g'\n",
"org.apache.hadoop:hadoop-aws:2.7.1\n",
"com.databricks:spark-csv_2.10:1.3.0\n",
"com.databricks:spark-avro_2.10:2.0.1\n",
"org.apache.spark:spark-streaming-twitter_2.10:1.5.2\n",
"org.twitter4j:twitter4j-core:4.0.4\n",
"org.apache.hbase:hbase-client:1.1.2\n",
"org.apache.hbase:hbase-common:1.1.2\n",
"org.postgresql:postgresql:9.4.1208\n",
"END\n",
")\n",
"\n",
"function start-spark() {\n",
"spark-shell --packages $SPARK_PKGS --driver-class-path ~/ipynb/postgresql-9.4.1208.jar -deprecation\n",
"}\n",
"```\n",
"\n",
"All the Spark packages above are not needed to make the JDBC connection work. These are only examples of what might be wanted. Also at this time, it was not tested whether org.postgresql:postgresql:9.4.1208 was required in the package list. However, ~/ipynb/postgresql-9.4.1208.jar was required in the -driver-class-path. This jar file can be downloaded from the PostgreSQL site or via maven. The file & path need to be accessible to Spark. "
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## How Did I Make This Jupyter Toree Document?\n",
"\n",
"I used Asim Jalis's blog, https://github.com/asimjalis/apache-toree-quickstart, to install Toree and write this \n",
"article. BTW - Please do not install Toree and Juypter with sudo, just use:\n",
"```\n",
"pip install jupyter\n",
"pip install notebook\n",
"pip install toree\n",
"```\n",
"\n",
"When you get to the step of Toree in Jupyter add the JDBC driver:\n",
"\n",
"```\n",
"SPARK_OPTS=\"--packages=$SPARK_PKGS\"\n",
"jupyter toree install --spark_home=$SPARK_HOME --spark_opts=\"--driver-class-path=/SomPathTo/postgresql-9.4.1208.jar $SPARK_OPTS\"\n",
"```"
]
},
{
"cell_type": "markdown",
"metadata": {
"collapsed": true
},
"source": [
"\n",
"## Summary\n",
"\n",
"I hope the this example and information are a helpful introduction to Spark. Spark has many more features -- like streaming, machine learning and Graphx.\n",
"\n",
"\n",
"### Further Reading on the Web \n",
"\n",
"Spark: Connecting to a jdbc data-source using dataframes - \n",
"http://www.infoobjects.com/spark-connecting-to-a-jdbc-data-source-using-dataframes/\n",
"\n",
"\n",
"Speed your SQL Queries with Spark SQL - \n",
"https://developer.ibm.com/clouddataservices/2015/08/19/speed-your-sql-queries-with-spark-sql/\n",
"\n",
"Note: Discussion uses an older method of reading a PostgreSQL DB from Spark and the method of using --jars to include the PostgreSQL JDBC connection didn't work. (As mentioned above, using --driver-class-path /SomePathTo/postgresql-9.4.1208.jar did work.) \n",
"\n",
"\n",
"Spark SQL, DataFrames and Datasets Guide - \n",
"https://spark.apache.org/docs/latest/sql-programming-guide.html\n",
"\n",
"\n",
"Spark DataFrames with Cassandra - \n",
"https://github.com/datastax/spark-cassandra-connector/blob/master/doc/14_data_frames.md\n",
"\n",
"\n",
"### Books\n",
"One book that is rather easy to follow is 'Big Data Analysis with Spark' by Mohammed Guller.\n",
"\n",
"### Coursework:\n",
"Some courses exist on Spark and Big Data. The number of courses and disparate content make finding courses difficult. I found the San Francisco Galvanize (formerly Zipfian Academy) Data Engineering course helpful in my quest. (Galvanize has other locations in Colorado (multiple locations), Austin and Seattle. See http://www.galvanize.com/ for more information.\n",
"\n",
"### How Did I Make This Document?\n",
"\n",
"I used Asim Jalis's blog, https://github.com/asimjalis/apache-toree-quickstart, to install Toree and write this \n",
"article. BTW - Please do not install Toree and Juypter with sudo, just use:\n",
"```\n",
"pip install jupyter\n",
"pip install notebook\n",
"pip install toree\n",
"```\n",
"\n",
"When you get to the step of Toree in Jupyter add the JDBC driver:\n",
"\n",
"```\n",
"SPARK_OPTS=\"--packages=$SPARK_PKGS\"\n",
"jupyter toree install --spark_home=$SPARK_HOME --spark_opts=\"--driver-class-path=/SomPathTo/postgresql-9.4.1208.jar $SPARK_OPTS\"\n",
"```\n",
"\n",
"\n",
"### Contact:\n",
"You can contact me at stephanwarren on ymail.com\n",
"\n",
"\n"
]
}
],
"metadata": {
"kernelspec": {
"display_name": "Toree",
"language": "",
"name": "toree"
},
"language_info": {
"name": "scala"
}
},
"nbformat": 4,
"nbformat_minor": 0
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment