Work%2Bwith%2Ba%2Bremote%2BSpark%2BSample%2BTwo%2BLocal.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "# Working with an existing remote Spark via HTTP (sample 2)\n",
    "\n",
    "IBM DSX Local provides the interface for Python notebooks to work with an existing remote Spark through HTTP connection and user-friendly sparkmagic commands. This sample notebook shows how to send an SQL request to remote Spark to get a DataFrame.\n",
    "\n",
    "The installation of the remote Spark in this sample is using Horton Data Platform (HDP), which utilizes Livy HTTP REST API. Livy is an open source REST interface for interacting with [Apache Spark](http://spark.apache.org) from anywhere. It supports executing snippets of code or programs in a Spark context that runs locally or in [Apache Hadoop YARN](http://hadoop.apache.org/docs/current/hadoop-yarn/hadoop-yarn-site/YARN.html).\n",
    "\n",
    "This notebook runs on Python 2."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    " ## Table of contents \n",
    "\n",
    "   1.  [Load sparkmagic](#load-sparkmagic)<br>\n",
    "   2.  [Create a connection to remote Spark](#connection-to-remote-spark)<br>\n",
    "   3.  [Send an SQL request to show information about all tables](#show-inf-all-tables)<br>\n",
    "   4.  [Send an SQL request to show the contents of a specific table](#show-inf-one-table)<br>\n",
    "   5.  [Print out information from the returned DataFrame](#print-data-frame)<br>\n",
    "   6.  [Visualize your data using Brunel](#visualize-data)<br>\n",
    "   6.  [Delete the session](#delete-session)<br>        "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "source": [
    "<a id=\"load-sparkmagic\"></a>\n",
    "## 1. Load sparkmagic\n",
    "\n",
    "Sparkmagic is a set of tools for interactively working with remote Spark clusters through Livy, a Spark REST server, in Jupyter notebooks. The Sparkmagic project includes a set of magics for interactively running Spark code in multiple languages, as well as some kernels that you can use to turn Jupyter into an integrated Spark environment.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "11/01/2017 09:03:17 PM - proxy_util - INFO - Set custom headers.\n",
      "11/01/2017 09:03:17 PM - proxy_util - INFO - Set proxy user to be useed with Livy.\n",
      "11/01/2017 09:03:17 PM - proxy_util - INFO - proxy settings set.\n"
     ]
    }
   ],
   "source": [
    "%load_ext sparkmagic.magics\n",
    "import dsx_core_utils\n",
    "dsx_core_utils.setup_livy_sparkmagic()\n",
    "%reload_ext sparkmagic.magics"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "<a id=\"connection-to-remote-spark\"></a>\n",
    "##  2. Create a connection to remote Spark \n",
    "\n",
    "Run the following cell to invoke the user interface for managing Spark. In the user interface, perform the following tasks to create a connection to the remote Spark:\n",
    " * Check **Manage Endpoints**. If you already see an endpoint defined, then your DSX Admin has configured a default DSX Endpoint.\n",
    " * Otherwise, select the **Add Endpoint** tab to create the endpoint of the Livy service URL. Type the Livy service URL in the **Address** field, select the authentication type, and specify the authentication credentials if required. Then, select the **Add endpoint** button.\n",
    " * Select the **Add Session** tab to create a session. Choose the endpoint, type the session name, and choose the language. Then, select the **Create Session** button. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Starting Spark application\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr><th>ID</th><th>YARN Application ID</th><th>Kind</th><th>State</th><th>Spark UI</th><th>Driver log</th><th>Current session?</th></tr><tr><td>135</td><td>application_1509308217126_0029</td><td>pyspark</td><td>idle</td><td><a target=\"_blank\" href=\"http://vend1.fyre.ibm.com:8088/proxy/application_1509308217126_0029/\">Link</a></td><td><a target=\"_blank\" href=\"http://vend6.fyre.ibm.com:8042/node/containerlogs/container_e12_1509308217126_0029_01_000001/user1\">Link</a></td><td>✔</td></tr></table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SparkContext available as 'sc'.\n",
      "HiveContext available as 'sqlContext'.\n"
     ]
    }
   ],
   "source": [
    "%manage_spark"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "<a id=\"show-inf-all-tables\"></a>\n",
    "## 3. Send an SQL request to show information about all tables\n",
    "\n",
    "Send an SQL request to remote Spark to list all tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>tableName</th>\n",
       "      <th>isTemporary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>sample_07</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>sample_08</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>sample_09</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   tableName  isTemporary\n",
       "0  sample_07        False\n",
       "1  sample_08        False\n",
       "2  sample_09        False"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%spark -c sql\n",
    "SHOW TABLES"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "<a id=\"show-inf-one-table\"></a>\n",
    "## 4. Send an SQL request to show the contents of a specific table\n",
    "\n",
    "Send an SQL request to remote Spark to show the __sample_07__ table contents, and return the DataFrame to the local notebook."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>description</th>\n",
       "      <th>total_emp</th>\n",
       "      <th>salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00-0000</td>\n",
       "      <td>All Occupations</td>\n",
       "      <td>134354250</td>\n",
       "      <td>40690</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11-0000</td>\n",
       "      <td>Management occupations</td>\n",
       "      <td>6003930</td>\n",
       "      <td>96150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11-1011</td>\n",
       "      <td>Chief executives</td>\n",
       "      <td>299160</td>\n",
       "      <td>151370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11-1021</td>\n",
       "      <td>General and operations managers</td>\n",
       "      <td>1655410</td>\n",
       "      <td>103780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11-1031</td>\n",
       "      <td>Legislators</td>\n",
       "      <td>61110</td>\n",
       "      <td>33880</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>11-2011</td>\n",
       "      <td>Advertising and promotions managers</td>\n",
       "      <td>36300</td>\n",
       "      <td>91100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>11-2021</td>\n",
       "      <td>Marketing managers</td>\n",
       "      <td>165240</td>\n",
       "      <td>113400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>11-2022</td>\n",
       "      <td>Sales managers</td>\n",
       "      <td>322170</td>\n",
       "      <td>106790</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>11-2031</td>\n",
       "      <td>Public relations managers</td>\n",
       "      <td>47210</td>\n",
       "      <td>97170</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>11-3011</td>\n",
       "      <td>Administrative services managers</td>\n",
       "      <td>239360</td>\n",
       "      <td>76370</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      code                          description  total_emp  salary\n",
       "0  00-0000                      All Occupations  134354250   40690\n",
       "1  11-0000               Management occupations    6003930   96150\n",
       "2  11-1011                     Chief executives     299160  151370\n",
       "3  11-1021      General and operations managers    1655410  103780\n",
       "4  11-1031                          Legislators      61110   33880\n",
       "5  11-2011  Advertising and promotions managers      36300   91100\n",
       "6  11-2021                   Marketing managers     165240  113400\n",
       "7  11-2022                       Sales managers     322170  106790\n",
       "8  11-2031            Public relations managers      47210   97170\n",
       "9  11-3011     Administrative services managers     239360   76370"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%spark -c sql -o df_tab07 --maxrows 10\n",
    "SELECT * FROM sample_07"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "<a id=\"print-data-frame\"></a>\n",
    "## 5. Print out information from the returned DataFrame\n",
    "\n",
    "Print out the first 3 rows and then the total rows from the returned DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>description</th>\n",
       "      <th>total_emp</th>\n",
       "      <th>salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00-0000</td>\n",
       "      <td>All Occupations</td>\n",
       "      <td>134354250</td>\n",
       "      <td>40690</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11-0000</td>\n",
       "      <td>Management occupations</td>\n",
       "      <td>6003930</td>\n",
       "      <td>96150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11-1011</td>\n",
       "      <td>Chief executives</td>\n",
       "      <td>299160</td>\n",
       "      <td>151370</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      code             description  total_emp  salary\n",
       "0  00-0000         All Occupations  134354250   40690\n",
       "1  11-0000  Management occupations    6003930   96150\n",
       "2  11-1011        Chief executives     299160  151370"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_tab07.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "10\n"
     ]
    }
   ],
   "source": [
    "print(len(df_tab07))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "<a id=\"visualize-data\"></a>\n",
    "## 6. Visualize your data using Brunel\n",
    "\n",
    "Apply the Brunel functions to the DataFrame and draw a diagram."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<!--\n",
       "  ~ Copyright (c) 2015 IBM Corporation and others.\n",
       "  ~\n",
       "  ~ Licensed under the Apache License, Version 2.0 (the \"License\");\n",
       "  ~ You may not use this file except in compliance with the License.\n",
       "  ~ You may obtain a copy of the License at\n",
       "  ~\n",
       "  ~     http://www.apache.org/licenses/LICENSE-2.0\n",
       "  ~\n",
       "  ~ Unless required by applicable law or agreed to in writing, software\n",
       "  ~ distributed under the License is distributed on an \"AS IS\" BASIS,\n",
       "  ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\n",
       "  ~ See the License for the specific language governing permissions and\n",
       "  ~ limitations under the License.\n",
       "  -->\n",
       "\n",
       "\n",
       "<link rel=\"stylesheet\" type=\"text/css\" href=\"/dsx-jupyter/ibmdsxuser-1001/1509312160500/nbextensions/brunel_ext/brunel.2.3.css\">\n",
       "<link rel=\"stylesheet\" type=\"text/css\" href=\"/dsx-jupyter/ibmdsxuser-1001/1509312160500/nbextensions/brunel_ext/sumoselect.css\">\n",
       "\n",
       "<style>\n",
       "    \n",
       "</style>\n",
       "\n",
       "<div id=\"controlsid52d0dd90-bf48-11e7-8f92-aa3d3dd6d49f\" class=\"brunel\"/>\n",
       "<svg id=\"visid52d0d75a-bf48-11e7-8f92-aa3d3dd6d49f\" width=\"300\" height=\"300\"></svg>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/javascript": [
       "/*\n",
       " * Copyright (c) 2015 IBM Corporation and others.\n",
       " *\n",
       " * Licensed under the Apache License, Version 2.0 (the \"License\");\n",
       " * You may not use this file except in compliance with the License.\n",
       " * You may obtain a copy of the License at\n",
       " *\n",
       " *     http://www.apache.org/licenses/LICENSE-2.0\n",
       " *\n",
       " * Unless required by applicable law or agreed to in writing, software\n",
       " * distributed under the License is distributed on an \"AS IS\" BASIS,\n",
       " * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\n",
       " * See the License for the specific language governing permissions and\n",
       " * limitations under the License.\n",
       " */\n",
       "\n",
       "require.config({\n",
       "    waitSeconds: 60,\n",
       "    paths: {\n",
       "        'd3': '//cdnjs.cloudflare.com/ajax/libs/d3/4.2.1/d3.min',\n",
       "        'topojson': '//cdnjs.cloudflare.com/ajax/libs/topojson/1.6.20/topojson.min',\n",
       "        'brunel' : '/dsx-jupyter/ibmdsxuser-1001/1509312160500/nbextensions/brunel_ext/brunel.2.3.min',\n",
       "        'brunelControls' : '/dsx-jupyter/ibmdsxuser-1001/1509312160500/nbextensions/brunel_ext/brunel.controls.2.3.min'\n",
       "    },\n",
       "    shim: {\n",
       "       'brunel' : {\n",
       "            exports: 'BrunelD3',\n",
       "            deps: ['d3', 'topojson'],\n",
       "            init: function() {\n",
       "               return {\n",
       "                 BrunelD3 : BrunelD3,\n",
       "                 BrunelData : BrunelData\n",
       "              }\n",
       "            }\n",
       "        },\n",
       "       'brunelControls' : {\n",
       "            exports: 'BrunelEventHandlers',\n",
       "            init: function() {\n",
       "               return {\n",
       "                 BrunelEventHandlers: BrunelEventHandlers,\n",
       "                 BrunelJQueryControlFactory: BrunelJQueryControlFactory\n",
       "              }\n",
       "            }\n",
       "        }\n",
       "\n",
       "    }\n",
       "\n",
       "});\n",
       "\n",
       "require([\"d3\"], function(d3) {\n",
       "    require([\"brunel\", \"brunelControls\"], function(brunel, brunelControls) {\n",
       "        function  BrunelVis(visId) {\n",
       "  \"use strict\";                                                                       // strict mode\n",
       "  var datasets = [],                                      // array of datasets for the original data\n",
       "      pre = function(d, i) { return d },                         // default pre-process does nothing\n",
       "      post = function(d, i) { return d },                       // default post-process does nothing\n",
       "      transitionTime = 200,                                        // transition time for animations\n",
       "      charts = [],                                                       // the charts in the system\n",
       "      vis = d3.select('#' + visId).attr('class', 'brunel');                     // the SVG container\n",
       "\n",
       "  BrunelD3.addDefinitions(vis);                                   // ensure standard symbols present\n",
       "\n",
       "  // Define chart #1 in the visualization //////////////////////////////////////////////////////////\n",
       "\n",
       "  charts[0] = function(parentNode, filterRows) {\n",
       "    var geom = BrunelD3.geometry(parentNode || vis.node(), 0, 0, 1, 1, 5, 65, 113, 0),\n",
       "      elements = [];                                              // array of elements in this chart\n",
       "\n",
       "    // Define groups for the chart parts ///////////////////////////////////////////////////////////\n",
       "\n",
       "    var chart =  vis.append('g').attr('class', 'chart1')\n",
       "      .attr('transform','translate(' + geom.chart_left + ',' + geom.chart_top + ')');\n",
       "    var overlay = chart.append('g').attr('class', 'element').attr('class', 'overlay');\n",
       "    var zoom = d3.zoom().scaleExtent([1/3,3]);\n",
       "    var zoomNode = overlay.append('rect').attr('class', 'overlay')\n",
       "      .attr('x', geom.inner_left).attr('y', geom.inner_top)\n",
       "      .attr('width', geom.inner_rawWidth).attr('height', geom.inner_rawHeight)\n",
       "      .style('cursor', 'move').call(zoom)\n",
       "      .node();\n",
       "    zoomNode.__zoom = d3.zoomIdentity;\n",
       "    chart.append('rect').attr('class', 'background').attr('width', geom.chart_right-geom.chart_left).attr('height', geom.chart_bottom-geom.chart_top);\n",
       "    var interior = chart.append('g').attr('class', 'interior zoomNone')\n",
       "      .attr('transform','translate(' + geom.inner_left + ',' + geom.inner_top + ')')\n",
       "      .attr('clip-path', 'url(#clip_visid52d0d75a-bf48-11e7-8f92-aa3d3dd6d49f_chart1_inner)');\n",
       "    interior.append('rect').attr('class', 'inner').attr('width', geom.inner_width).attr('height', geom.inner_height);\n",
       "    var gridGroup = interior.append('g').attr('class', 'grid');\n",
       "    var axes = chart.append('g').attr('class', 'axis')\n",
       "      .attr('transform','translate(' + geom.inner_left + ',' + geom.inner_top + ')');\n",
       "    vis.append('clipPath').attr('id', 'clip_visid52d0d75a-bf48-11e7-8f92-aa3d3dd6d49f_chart1_inner').append('rect')\n",
       "      .attr('x', 0).attr('y', 0)\n",
       "      .attr('width', geom.inner_rawWidth+1).attr('height', geom.inner_rawHeight+1);\n",
       "\n",
       "    // Scales //////////////////////////////////////////////////////////////////////////////////////\n",
       "\n",
       "    var scale_x = d3.scalePoint().padding(0.5)\n",
       "      .domain(['Administrative services managers', 'Advertising and promotions managers', 'All Occupations', 'Chief executives', 'General and operations managers', 'Legislators', 'Management occupations', 'Marketing managers', 'Public relations managers', 'Sales managers'])\n",
       "      .range([0, geom.inner_width]);\n",
       "    var scale_inner = d3.scaleLinear().domain([0,1])\n",
       "      .range([-0.5, 0.5]);\n",
       "    var scale_y = d3.scaleLinear().domain([0, 160000.02])\n",
       "      .range([geom.inner_height, 0]);\n",
       "    var base_scales = [scale_x, scale_y];                           // untransformed original scales\n",
       "\n",
       "    // Axes ////////////////////////////////////////////////////////////////////////////////////////\n",
       "\n",
       "    axes.append('g').attr('class', 'x axis')\n",
       "      .attr('transform','translate(0,' + geom.inner_rawHeight + ')')\n",
       "      .attr('clip-path', 'url(#clip_visid52d0d75a-bf48-11e7-8f92-aa3d3dd6d49f_chart1_haxis)');\n",
       "    vis.append('clipPath').attr('id', 'clip_visid52d0d75a-bf48-11e7-8f92-aa3d3dd6d49f_chart1_haxis').append('polyline')\n",
       "      .attr('points', '-1,-1000, -1,-1 -5,5, -1000,5, -100,1000, 10000,1000 10000,-1000');\n",
       "    axes.select('g.axis.x').append('text').attr('class', 'title').text('Description').style('text-anchor', 'middle')\n",
       "      .attr('x',geom.inner_rawWidth/2)\n",
       "      .attr('y', geom.inner_bottom - 2.0).attr('dy','-0.27em');\n",
       "    axes.append('g').attr('class', 'y axis')\n",
       "      .attr('clip-path', 'url(#clip_visid52d0d75a-bf48-11e7-8f92-aa3d3dd6d49f_chart1_vaxis)');\n",
       "    vis.append('clipPath').attr('id', 'clip_visid52d0d75a-bf48-11e7-8f92-aa3d3dd6d49f_chart1_vaxis').append('polyline')\n",
       "      .attr('points', '-1000,-10000, 10000,-10000, 10000,' + (geom.inner_rawHeight+1) + ', -1,' + (geom.inner_rawHeight+1) + ', -1,' + (geom.inner_rawHeight+5) + ', -1000,' + (geom.inner_rawHeight+5) );\n",
       "    axes.select('g.axis.y').append('text').attr('class', 'title').text('Mean(Salary)').style('text-anchor', 'middle')\n",
       "      .attr('x',-geom.inner_rawHeight/2)\n",
       "      .attr('y', 4-geom.inner_left).attr('dy', '0.7em').attr('transform', 'rotate(270)');\n",
       "\n",
       "    var axis_bottom = d3.axisBottom(scale_x).ticks(Math.min(10, Math.round(geom.inner_width / 297.0)));\n",
       "    var axis_left = d3.axisLeft(scale_y).ticks(Math.min(10, Math.round(geom.inner_width / 20)));\n",
       "\n",
       "    function buildAxes(time) {\n",
       "      axis_bottom.tickValues(BrunelD3.filterTicks(scale_x))\n",
       "      var axis_x = axes.select('g.axis.x');\n",
       "      BrunelD3.transition(axis_x, time).call(axis_bottom.scale(scale_x)).selectAll('.tick text')\n",
       "        .attr('transform', function() {\n",
       "          var v = this.getComputedTextLength() / Math.sqrt(2)/2;\n",
       "          return 'translate(-' + (v+6) + ',' + v + ') rotate(-45)'\n",
       "      });\n",
       "      var axis_y = axes.select('g.axis.y');\n",
       "      BrunelD3.transition(axis_y, time).call(axis_left.scale(scale_y));\n",
       "    }\n",
       "    zoom.on('zoom', function(t, time) {\n",
       "        t = t ||BrunelD3.restrictZoom(d3.event.transform, geom, this);\n",
       "        scale_y = t.rescaleY(base_scales[1]);\n",
       "        zoomNode.__zoom = t;\n",
       "        interior.attr('class', 'interior ' + BrunelD3.zoomLabel(t.k));;\n",
       "        build(time || -1);\n",
       "    });\n",
       "\n",
       "    // Define element #1 ///////////////////////////////////////////////////////////////////////////\n",
       "\n",
       "    elements[0] = function() {\n",
       "      var original, processed,                           // data sets passed in and then transformed\n",
       "        element, data,                                 // brunel element information and brunel data\n",
       "        selection, merged;                                      // d3 selection and merged selection\n",
       "      var elementGroup = interior.append('g').attr('class', 'element1'),\n",
       "        main = elementGroup.append('g').attr('class', 'main'),\n",
       "        labels = BrunelD3.undoTransform(elementGroup.append('g').attr('class', 'labels').attr('aria-hidden', 'true'), elementGroup);\n",
       "\n",
       "      function makeData() {\n",
       "        original = datasets[0];\n",
       "        if (filterRows) original = original.retainRows(filterRows);\n",
       "        processed = pre(original, 0)\n",
       "          .summarize('salary=salary:mean; description=description:base');\n",
       "        processed = post(processed, 0);\n",
       "        var f0 = processed.field('description'),\n",
       "          f1 = processed.field('salary'),\n",
       "          f2 = processed.field('#row'),\n",
       "          f3 = processed.field('#selection');\n",
       "        var keyFunc = function(d) { return f0.value(d) };\n",
       "        data = {\n",
       "          description:  function(d) { return f0.value(d.row) },\n",
       "          salary:       function(d) { return f1.value(d.row) },\n",
       "          $row:         function(d) { return f2.value(d.row) },\n",
       "          $selection:   function(d) { return f3.value(d.row) },\n",
       "          description_f:function(d) { return f0.valueFormatted(d.row) },\n",
       "          salary_f:     function(d) { return f1.valueFormatted(d.row) },\n",
       "          $row_f:       function(d) { return f2.valueFormatted(d.row) },\n",
       "          $selection_f: function(d) { return f3.valueFormatted(d.row) },\n",
       "          _split:       function(d) { return 'ALL' },\n",
       "          _key:         keyFunc,\n",
       "          _rows:        BrunelD3.makeRowsWithKeys(keyFunc, processed.rowCount())\n",
       "        };\n",
       "      }\n",
       "\n",
       "      // Build element from data ///////////////////////////////////////////////////////////////////\n",
       "\n",
       "      function build(transitionMillis) {\n",
       "        element = elements[0];\n",
       "        var w = 0.9 * Math.abs(scale_x(scale_x.domain()[1]) - scale_x(scale_x.domain()[0]) );\n",
       "        var x = function(d) { return scale_x(data.description(d))};\n",
       "        var h = geom.default_point_size;\n",
       "        var y1 = scale_y.range()[0];\n",
       "        var y2 = function(d) { return scale_y(data.salary(d))};\n",
       "\n",
       "        // Define selection entry operations\n",
       "        function initialState(selection) {\n",
       "          selection\n",
       "            .attr('class', 'element bar filled')\n",
       "        }\n",
       "\n",
       "        // Define selection update operations on merged data\n",
       "        function updateState(selection) {\n",
       "          selection\n",
       "            .each(function(d) {\n",
       "              var width = w, left = x(d) - width/2, \n",
       "              c = y1, d = y2(d), top = Math.min(c,d), height = Math.max(1e-6, Math.abs(c-d));\n",
       "              this.r = {x:left, y:top, w:width, h:height};\n",
       "            })\n",
       "            .attr('x', function(d) { return this.r.x })\n",
       "            .attr('y', function(d) { return this.r.y })\n",
       "            .attr('width', function(d) { return this.r.w })\n",
       "            .attr('height', function(d) { return this.r.h });\n",
       "        }\n",
       "\n",
       "        // Define labeling for the selection\n",
       "        function label(selection, transitionMillis) {\n",
       "\n",
       "          var tooltipLabeling  = {\n",
       "            index: -1, method: 'box', location: ['center', 'top'], inside: true, align: 'middle', pad: 0, dy: 0.7,\n",
       "            fit: true, granularity: 0,\n",
       "            content: function(d) {\n",
       "              return d.row == null ? null : '<span class=\"title\">Description: </span>'\n",
       "\t\t\t+ '<span class=\"field\">' + data.description_f(d) + '</span>'\n",
       "\t\t\t+ '<br/>'\n",
       "\t\t\t+ '<span class=\"title\">Mean(Salary): </span>'\n",
       "\t\t\t+ '<span class=\"field\">' + data.salary_f(d) + '</span>'\n",
       "            }\n",
       "          };\n",
       "          BrunelD3.addTooltip(selection, tooltipLabeling, geom);\n",
       "        }\n",
       "        // Create selections, set the initial state and transition updates\n",
       "        selection = main.selectAll('.element').data(data._rows, function(d) { return d.key });\n",
       "        var added = selection.enter().append('rect');\n",
       "        merged = selection.merge(added);\n",
       "        initialState(added);\n",
       "        selection.filter(BrunelD3.hasData)\n",
       "          .classed('selected', BrunelD3.isSelected(data))\n",
       "          .filter(BrunelD3.isSelected(data)).raise();\n",
       "        updateState(BrunelD3.transition(merged, transitionMillis));\n",
       "        label(merged, transitionMillis);\n",
       "\n",
       "        BrunelD3.transition(selection.exit(), transitionMillis/3)\n",
       "          .style('opacity', 0.5).each( function() {\n",
       "            this.remove(); BrunelD3.removeLabels(this); \n",
       "        });\n",
       "      }\n",
       "\n",
       "      return {\n",
       "        data:           function() { return processed },\n",
       "        original:       function() { return original },\n",
       "        internal:       function() { return data },\n",
       "        selection:      function() { return merged },\n",
       "        makeData:       makeData,\n",
       "        build:          build,\n",
       "        chart:          function() { return charts[0] },\n",
       "        group:          function() { return elementGroup },\n",
       "        fields: {\n",
       "          x:            ['description'],\n",
       "          y:            ['salary'],\n",
       "          key:          ['description']\n",
       "        }\n",
       "      };\n",
       "    }();\n",
       "\n",
       "    function build(time, noData) {\n",
       "      var first = elements[0].data() == null;\n",
       "      if (first) time = 0;                                           // no transition for first call\n",
       "      buildAxes(time);\n",
       "      if ((first || time > -1) && !noData) {\n",
       "        elements[0].makeData();\n",
       "      }\n",
       "      elements[0].build(time);\n",
       "    }\n",
       "\n",
       "    // Expose the following components of the chart\n",
       "    return {\n",
       "      elements : elements,\n",
       "      interior : interior,\n",
       "      scales: {x:scale_x, y:scale_y},\n",
       "      zoom: function(params, time) {\n",
       "          if (params) zoom.on('zoom').call(zoomNode, params, time);\n",
       "          return d3.zoomTransform(zoomNode);\n",
       "      },\n",
       "      build : build\n",
       "    };\n",
       "    }();\n",
       "\n",
       "  function setData(rowData, i) { datasets[i||0] = BrunelD3.makeData(rowData) }\n",
       "  function updateAll(time) { charts.forEach(function(x) {x.build(time || 0)}) }\n",
       "  function buildAll() {\n",
       "    for (var i=0;i<arguments.length;i++) setData(arguments[i], i);\n",
       "    updateAll(transitionTime);\n",
       "  }\n",
       "\n",
       "  return {\n",
       "    dataPreProcess:     function(f) { if (f) pre = f; return pre },\n",
       "    dataPostProcess:    function(f) { if (f) post = f; return post },\n",
       "    data:               function(d,i) { if (d) setData(d,i); return datasets[i||0] },\n",
       "    visId:              visId,\n",
       "    build:              buildAll,\n",
       "    rebuild:            updateAll,\n",
       "    charts:             charts\n",
       "  }\n",
       "}\n",
       "\n",
       "// Data Tables /////////////////////////////////////////////////////////////////////////////////////\n",
       "\n",
       "var table1 = {\n",
       "   summarized: true,\n",
       "   names: ['description', 'salary'], \n",
       "   options: ['string', 'numeric'], \n",
       "   rows: [['Administrative services managers', 76370],\n",
       "  ['Advertising and promotions managers', 91100], ['All Occupations', 40690],\n",
       "  ['Chief executives', 151370], ['General and operations managers', 103780], ['Legislators', 33880],\n",
       "  ['Management occupations', 96150], ['Marketing managers', 113400],\n",
       "  ['Public relations managers', 97170], ['Sales managers', 106790]]\n",
       "};\n",
       "\n",
       "// Call Code to Build the system ///////////////////////////////////////////////////////////////////\n",
       "\n",
       "var v  = new BrunelVis('visid52d0d75a-bf48-11e7-8f92-aa3d3dd6d49f');\n",
       "v.build(table1);\n",
       "\n",
       "    });\n",
       "});"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import brunel\n",
    "\n",
    "%brunel data('df_tab07') x(description) y(salary) mean(salary) bar tooltip(#all) :: width=300, height=300"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "<a id=\"delete-session\"></a>\n",
    "## 7. Delete the remote Spark session\n",
    "\n",
    "Run the following cell to remove your remote Spark session. You can also use the Spark Manager in Step 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "%spark delete -s SESSION_NAME"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## Summary\n",
    "\n",
    "In this notebook, you learned how to send an SQL request to remote Spark to get a DataFrame."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "source": [
    "<div class=\"alert alert-block alert-info\"> Note: To save resources and get the best performance please use the code below to stop the kernel before exiting your notebook.</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "%%javascript\n",
    "Jupyter.notebook.session.delete();"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "<hr>\n",
    "Copyright &copy; IBM Corp. 2017. Released as licensed Sample Materials."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.13"
  },
  "widgets": {
   "state": {
    "df213dbcb56345638762bdd3ac590975": {
     "views": [
      {
       "cell_index": 5
      }
     ]
    }
   },
   "version": "1.2.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}