Spark-SQL-CSV-with-Python.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = sqlContext.read.format('com.databricks.spark.csv').options(header='true', inferschema='true').load('Uber-Jan-Feb-FOIL.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- dispatching_base_number: string (nullable = true)\n",
      " |-- date: string (nullable = true)\n",
      " |-- active_vehicles: integer (nullable = true)\n",
      " |-- trips: integer (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df.registerTempTable(\"uber\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Row(dispatching_base_number=u'B02598')\n",
      "Row(dispatching_base_number=u'B02764')\n",
      "Row(dispatching_base_number=u'B02765')\n",
      "Row(dispatching_base_number=u'B02617')\n",
      "Row(dispatching_base_number=u'B02682')\n",
      "Row(dispatching_base_number=u'B02512')\n"
     ]
    }
   ],
   "source": [
    "distinct_bases = sqlContext.sql(\"select distinct dispatching_base_number from uber\")\n",
    "for b in distinct_bases.collect(): print b"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------------------+-------+\n",
      "|dispatching_base_number|    cnt|\n",
      "+-----------------------+-------+\n",
      "|                 B02764|1914449|\n",
      "|                 B02617| 725025|\n",
      "|                 B02682| 662509|\n",
      "|                 B02598| 540791|\n",
      "|                 B02765| 193670|\n",
      "|                 B02512|  93786|\n",
      "+-----------------------+-------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sqlContext.sql(\"\"\"select distinct(`dispatching_base_number`), \n",
    "                                sum(`trips`) as cnt from uber group by `dispatching_base_number` \n",
    "                                order by cnt desc\"\"\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------+------+\n",
      "|     date|   cnt|\n",
      "+---------+------+\n",
      "|2/20/2015|100915|\n",
      "|2/14/2015|100345|\n",
      "|2/21/2015| 98380|\n",
      "|2/13/2015| 98024|\n",
      "|1/31/2015| 92257|\n",
      "+---------+------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sqlContext.sql(\"\"\"select distinct(`date`), \n",
    "                                sum(`trips`) as cnt from uber group by `date` \n",
    "                                order by cnt desc limit 5\"\"\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "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.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}