dremio-aws-meetup.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql import SparkSession\n",
    "import os\n",
    "\n",
    "\n",
    "driver_path = \"dremio-jdbc-driver-4.8.0.jar\"\n",
    "\n",
    "os.environ['PYSPARK_SUBMIT_ARGS'] = f'--driver-class-path {driver_path} --jars {driver_path} pyspark-shell'\n",
    "\n",
    "spark = SparkSession \\\n",
    "    .builder \\\n",
    "    .appName(\"AWS Dremio\") \\\n",
    "    .config('spark.driver.bindAddress','localhost') \\\n",
    "    .config(\"spark.executor.extraClassPath\", driver_path) \\\n",
    "    .config(\"spark.driver.extraClassPath\", driver_path) \\\n",
    "    .config(\"spark.jars\", driver_path) \\\n",
    "    .getOrCreate()\n",
    "\n",
    "spark.conf.set(\"spark.sql.execution.arrow.pyspark.enabled\", \"true\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = spark.read \\\n",
    "    .format(\"jdbc\") \\\n",
    "    .option(\"url\", \"jdbc:dremio:direct=localhost:31010\") \\\n",
    "    .option(\"dbtable\", '\"aws-demo-wira\".\"rides_each_day\"') \\\n",
    "    .option(\"user\", \"aws\") \\\n",
    "    .option(\"password\", \"dremio4ws\") \\\n",
    "    .load()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+------+\n",
      "|      dayt|EXPR$1|\n",
      "+----------+------+\n",
      "|2016-01-01|345037|\n",
      "|2016-01-02|312831|\n",
      "|2016-01-03|302878|\n",
      "|2016-01-04|316171|\n",
      "|2016-01-05|343251|\n",
      "|2016-01-06|348516|\n",
      "|2016-01-07|364894|\n",
      "|2016-01-08|392070|\n",
      "|2016-01-09|405825|\n",
      "|2016-01-10|351788|\n",
      "|2016-01-11|342651|\n",
      "|2016-01-12|367390|\n",
      "|2016-01-13|395090|\n",
      "|2016-01-14|396473|\n",
      "|2016-01-15|401289|\n",
      "|2016-01-16|411899|\n",
      "|2016-01-17|379156|\n",
      "|2016-01-18|341481|\n",
      "|2016-01-19|385187|\n",
      "|2016-01-20|382105|\n",
      "+----------+------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = spark.read \\\n",
    "    .format(\"jdbc\") \\\n",
    "    .option(\"url\", \"jdbc:dremio:direct=localhost:31010\") \\\n",
    "    .option(\"dbtable\", '\"aws-demo-wira\".\"rides\"') \\\n",
    "    .option(\"user\", \"aws\") \\\n",
    "    .option(\"password\", \"dremio4ws\") \\\n",
    "    .load()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------+-------------------+-------------------+---------------+-------------+----------------+---------------+---------+-----------------+----------------+------------+-----------+--------+--------+----------+------------+---------------------+------------+\n",
      "|vendor_id|    pickup_datetime|   dropoff_datetime|passenger_count|trip_distance|pickup_longitude|pickup_latitude|rate_code|dropoff_longitude|dropoff_latitude|payment_type|fare_amount|   extra| mta_tax|tip_amount|tolls_amount|improvement_surcharge|total_amount|\n",
      "+---------+-------------------+-------------------+---------------+-------------+----------------+---------------+---------+-----------------+----------------+------------+-----------+--------+--------+----------+------------+---------------------+------------+\n",
      "|        2|2016-01-08 06:01:38|2016-01-08 06:06:15|       1.000000|     1.020000|      -74.006721|      40.735821|        1|       -74.003036|       40.747410|           1|   5.500000|0.500000|0.500000|  1.700000|    0.000000|             0.300000|    8.500000|\n",
      "|        2|2016-01-08 06:01:38|2016-01-08 06:12:15|       2.000000|     2.280000|      -73.968170|      40.755699|        1|       -73.989212|       40.726418|           1|  10.500000|0.500000|0.500000|  2.360000|    0.000000|             0.300000|   14.160000|\n",
      "|        2|2016-01-08 06:01:39|2016-01-08 06:06:20|       1.000000|     0.860000|      -73.997742|      40.722954|        1|       -74.005836|       40.716869|           1|   5.000000|0.500000|0.500000|  1.260000|    0.000000|             0.300000|    7.560000|\n",
      "|        2|2016-01-08 06:01:40|2016-01-08 06:26:43|       1.000000|     3.600000|      -73.987473|      40.744202|        1|       -73.957146|       40.744663|           1|  18.500000|0.500000|0.500000|  5.070000|    5.540000|             0.300000|   30.410000|\n",
      "|        2|2016-01-08 06:01:40|2016-01-08 06:12:27|       1.000000|     1.910000|      -73.988998|      40.726871|        1|       -74.007339|       40.705311|           1|   9.500000|0.500000|0.500000|  2.700000|    0.000000|             0.300000|   13.500000|\n",
      "|        2|2016-01-08 06:01:40|2016-01-08 06:27:54|       1.000000|    18.230000|      -73.789726|      40.646881|        2|       -73.951241|       40.804459|           1|  52.000000|0.000000|0.500000| 11.670000|    5.540000|             0.300000|   70.010000|\n",
      "|        2|2016-01-08 06:01:41|2016-01-08 06:15:53|       1.000000|     3.240000|      -73.970490|      40.796520|        1|       -73.961967|       40.763931|           1|  13.500000|0.500000|0.500000|  2.960000|    0.000000|             0.300000|   17.760000|\n",
      "|        2|2016-01-08 06:01:42|2016-01-08 06:14:04|       5.000000|     1.230000|      -73.972450|      40.749802|        1|       -73.990356|       40.751541|           1|   9.000000|0.500000|0.500000|  2.060000|    0.000000|             0.300000|   12.360000|\n",
      "|        2|2016-01-08 06:01:43|2016-01-08 06:11:37|       1.000000|     2.780000|      -73.974449|      40.742363|        1|       -73.947845|       40.774944|           1|  10.500000|0.500000|0.500000|  2.000000|    0.000000|             0.300000|   13.800000|\n",
      "|        2|2016-01-08 06:01:43|2016-01-08 06:17:21|       1.000000|     4.340000|      -73.989960|      40.767288|        1|       -73.920647|       40.755596|           1|  15.500000|0.500000|0.500000|  3.360000|    0.000000|             0.300000|   20.160000|\n",
      "|        2|2016-01-08 06:01:43|2016-01-08 06:04:18|       4.000000|     0.530000|      -73.989067|      40.730629|        1|       -73.986870|       40.736698|           1|   4.000000|0.500000|0.500000|  1.060000|    0.000000|             0.300000|    6.360000|\n",
      "|        2|2016-01-08 06:01:45|2016-01-08 06:16:24|       5.000000|     3.730000|      -74.003967|      40.731525|        1|       -73.976830|       40.686966|           1|  14.000000|0.500000|0.500000|  3.060000|    0.000000|             0.300000|   18.360000|\n",
      "|        2|2016-01-08 06:01:45|2016-01-08 06:10:37|       1.000000|     2.000000|      -73.990051|      40.746750|        1|       -73.988541|       40.763939|           1|   9.000000|0.500000|0.500000|  2.060000|    0.000000|             0.300000|   12.360000|\n",
      "|        2|2016-01-08 06:01:45|2016-01-08 06:12:01|       1.000000|     2.940000|      -73.987633|      40.719955|        1|       -73.958015|       40.717579|           1|  11.000000|0.500000|0.500000|  1.000000|    0.000000|             0.300000|   13.300000|\n",
      "|        2|2016-01-08 06:01:46|2016-01-08 06:07:05|       1.000000|     1.240000|      -74.001663|      40.741146|        1|       -74.000771|       40.727268|           1|   6.000000|0.500000|0.500000|  2.190000|    0.000000|             0.300000|    9.490000|\n",
      "|        2|2016-01-08 06:01:46|2016-01-08 06:34:26|       2.000000|    16.160000|      -73.815384|      40.666046|        1|       -73.992035|       40.636826|           1|  46.500000|0.500000|0.500000|  9.560000|    0.000000|             0.300000|   57.360000|\n",
      "|        2|2016-01-08 06:01:46|2016-01-08 06:11:10|       2.000000|     1.640000|      -73.997307|      40.719421|        1|       -73.985336|       40.738850|           1|   8.500000|0.500000|0.500000|  2.450000|    0.000000|             0.300000|   12.250000|\n",
      "|        2|2016-01-08 06:01:46|2016-01-08 06:06:58|       1.000000|     1.350000|      -73.991150|      40.750565|        1|       -74.005768|       40.735909|           1|   6.500000|0.500000|0.500000|  2.340000|    0.000000|             0.300000|   10.140000|\n",
      "|        2|2016-01-08 06:01:48|2016-01-08 06:24:09|       1.000000|     5.670000|      -73.989609|      40.730228|        1|       -73.960007|       40.656124|           1|  21.000000|0.500000|0.500000|  7.700000|    0.000000|             0.300000|   30.000000|\n",
      "|        2|2016-01-08 06:01:48|2016-01-08 06:15:33|       1.000000|     5.990000|      -73.975891|      40.755371|        1|       -74.009468|       40.706299|           1|  19.000000|0.500000|0.500000|  4.060000|    0.000000|             0.300000|   24.360000|\n",
      "+---------+-------------------+-------------------+---------------+-------------+----------------+---------------+---------+-----------------+----------------+------------+-----------+--------+--------+----------+------------+---------------------+------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = spark.read \\\n",
    "    .format(\"jdbc\") \\\n",
    "    .option(\"url\", \"jdbc:dremio:direct=localhost:31010\") \\\n",
    "    .option(\"dbtable\", '\"aws-demo-wira\".\"rides_of_each_rate\"') \\\n",
    "    .option(\"user\", \"aws\") \\\n",
    "    .option(\"password\", \"dremio4ws\") \\\n",
    "    .load()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+---------+\n",
      "|         description|num_trips|\n",
      "+--------------------+---------+\n",
      "|                 JFK|   900076|\n",
      "|Nassau or Westche...|    18784|\n",
      "|              Newark|    67288|\n",
      "|          group ride|      408|\n",
      "|     negotiated fare|   134752|\n",
      "|       standard rate| 42505260|\n",
      "+--------------------+---------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+------+\n",
      "|      dayt|EXPR$1|\n",
      "+----------+------+\n",
      "|2016-01-01|345037|\n",
      "|2016-01-02|312831|\n",
      "|2016-01-03|302878|\n",
      "|2016-01-04|316171|\n",
      "|2016-01-05|343251|\n",
      "|2016-01-06|348516|\n",
      "|2016-01-07|364894|\n",
      "|2016-01-08|392070|\n",
      "|2016-01-09|405825|\n",
      "|2016-01-10|351788|\n",
      "|2016-01-11|342651|\n",
      "|2016-01-12|367390|\n",
      "|2016-01-13|395090|\n",
      "|2016-01-14|396473|\n",
      "|2016-01-15|401289|\n",
      "|2016-01-16|411899|\n",
      "|2016-01-17|379156|\n",
      "|2016-01-18|341481|\n",
      "|2016-01-19|385187|\n",
      "|2016-01-20|382105|\n",
      "+----------+------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = df.toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>description</th>\n",
       "      <th>num_trips</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>JFK</td>\n",
       "      <td>900076</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Nassau or Westchester</td>\n",
       "      <td>18784</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Newark</td>\n",
       "      <td>67288</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>group ride</td>\n",
       "      <td>408</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>negotiated fare</td>\n",
       "      <td>134752</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             description  num_trips\n",
       "0                    JFK     900076\n",
       "1  Nassau or Westchester      18784\n",
       "2                 Newark      67288\n",
       "3             group ride        408\n",
       "4        negotiated fare     134752"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "num_trips    7.271095e+06\n",
       "dtype: float64"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.7.6 64-bit",
   "language": "python",
   "name": "python37664bitc79a83e4af134434b7557241e23fe463"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}