bsql_csv_to_csv_etl.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# CSV to CSV with BlazingSQL\n",
    "\n",
    "[This Notebook](https://github.com/gumdropsteve/silent-disco/blob/master/bsql_csv_to_csv_etl.ipynb) shows ETL writing a sample of data from 3 CSV files in the [gumdropsteve/turbo-telegram](https://github.com/gumdropsteve/turbo-telegram) repository to this repository's data directory."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Data Check**\n",
    "\n",
    "The next cell will check to make sure the data used in this demo is downloaded as expected. \n",
    "\n",
    "If you do not a clone of the turbo-telegram repo ([v0.0.2-beta](https://github.com/gumdropsteve/turbo-telegram/tree/0.0.2-beta) or [later](https://github.com/gumdropsteve/turbo-telegram)) in the same directory as this repo (silent-disco), an emply `turbo-telegram` directory and `turbo-telegram/data` sub-directory will be made and the data will be downloaded to `turbo-telegram/data`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "downloading nyc_taxi_feb15.csv to ../turbo-telegram/data\n",
      "making data directory for this repo\n"
     ]
    }
   ],
   "source": [
    "from os import path, system\n",
    "\n",
    "if not path.exists('../turbo-telegram'):\n",
    "    print('making blank turbo-telegram repo & data directory')\n",
    "    system('mkdir ../trubo-telegram')\n",
    "    system('mkdir ../trubo-telegram/data')\n",
    "\n",
    "base_url = 'https://raw.githubusercontent.com/gumdropsteve/turbo-telegram/0.0.3-beta/data'\n",
    "files = ['nyc_taxi_jan15', 'nyc_taxi_feb15', 'nyc_taxi_march15']\n",
    "    \n",
    "for fn in files:\n",
    "    if not path.isfile(f'../turbo-telegram/data/{fn}.csv'):\n",
    "        print(f'downloading {fn}.csv to ../turbo-telegram/data')\n",
    "        system(f'wget -P ../turbo-telegram/data {base_url}/{fn}.csv')\n",
    "        \n",
    "if not path.exists('data'):\n",
    "    print('making data directory for this repo')\n",
    "    system('mkdir data')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## ETL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "BlazingContext ready\n",
      "CPU times: user 9.6 s, sys: 2.53 s, total: 12.1 s\n",
      "Wall time: 12.2 s\n"
     ]
    },
    {
     "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>VendorID</th>\n",
       "      <th>tpep_pickup_datetime</th>\n",
       "      <th>tpep_dropoff_datetime</th>\n",
       "      <th>passenger_count</th>\n",
       "      <th>trip_distance</th>\n",
       "      <th>pickup_x</th>\n",
       "      <th>pickup_y</th>\n",
       "      <th>RateCodeID</th>\n",
       "      <th>store_and_fwd_flag</th>\n",
       "      <th>dropoff_x</th>\n",
       "      <th>dropoff_y</th>\n",
       "      <th>payment_type</th>\n",
       "      <th>fare_amount</th>\n",
       "      <th>extra</th>\n",
       "      <th>mta_tax</th>\n",
       "      <th>tip_amount</th>\n",
       "      <th>tolls_amount</th>\n",
       "      <th>improvement_surcharge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>2015-03-29 04:35:02</td>\n",
       "      <td>2015-03-29 05:06:31</td>\n",
       "      <td>2</td>\n",
       "      <td>8.46</td>\n",
       "      <td>-8237041.014</td>\n",
       "      <td>4976969.222</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>-8231378.729</td>\n",
       "      <td>4965405.816</td>\n",
       "      <td>2</td>\n",
       "      <td>29.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2015-02-17 18:56:56</td>\n",
       "      <td>2015-02-17 19:07:43</td>\n",
       "      <td>2</td>\n",
       "      <td>2.75</td>\n",
       "      <td>-8237333.173</td>\n",
       "      <td>4971417.775</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>-8233394.119</td>\n",
       "      <td>4970193.574</td>\n",
       "      <td>2</td>\n",
       "      <td>11.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>12.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>2015-02-04 22:23:41</td>\n",
       "      <td>2015-02-04 22:37:13</td>\n",
       "      <td>1</td>\n",
       "      <td>2.80</td>\n",
       "      <td>-8235801.885</td>\n",
       "      <td>4977181.703</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>-8236263.055</td>\n",
       "      <td>4972415.178</td>\n",
       "      <td>1</td>\n",
       "      <td>11.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2.55</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>2015-01-07 12:26:10</td>\n",
       "      <td>2015-01-07 12:46:03</td>\n",
       "      <td>1</td>\n",
       "      <td>3.70</td>\n",
       "      <td>-8235904.650</td>\n",
       "      <td>4977015.194</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>-8239372.343</td>\n",
       "      <td>4969838.949</td>\n",
       "      <td>1</td>\n",
       "      <td>15.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>3.26</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>2015-01-17 18:39:27</td>\n",
       "      <td>2015-01-17 18:44:30</td>\n",
       "      <td>1</td>\n",
       "      <td>0.92</td>\n",
       "      <td>-8235334.770</td>\n",
       "      <td>4975885.027</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>-8236558.611</td>\n",
       "      <td>4977024.164</td>\n",
       "      <td>2</td>\n",
       "      <td>5.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>999995</th>\n",
       "      <td>1</td>\n",
       "      <td>2015-03-19 06:17:23</td>\n",
       "      <td>2015-03-19 06:20:22</td>\n",
       "      <td>1</td>\n",
       "      <td>0.80</td>\n",
       "      <td>-8233500.281</td>\n",
       "      <td>4977410.447</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>-8234279.939</td>\n",
       "      <td>4975993.217</td>\n",
       "      <td>1</td>\n",
       "      <td>4.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.05</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>999996</th>\n",
       "      <td>1</td>\n",
       "      <td>2015-01-01 19:44:50</td>\n",
       "      <td>2015-01-01 19:58:00</td>\n",
       "      <td>1</td>\n",
       "      <td>3.10</td>\n",
       "      <td>-8237107.259</td>\n",
       "      <td>4971248.563</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>-8236611.268</td>\n",
       "      <td>4976348.066</td>\n",
       "      <td>1</td>\n",
       "      <td>12.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2.55</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>999997</th>\n",
       "      <td>1</td>\n",
       "      <td>2015-02-15 16:58:12</td>\n",
       "      <td>2015-02-15 17:03:35</td>\n",
       "      <td>1</td>\n",
       "      <td>0.40</td>\n",
       "      <td>-8235828.213</td>\n",
       "      <td>4978243.614</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>-8234988.255</td>\n",
       "      <td>4977852.252</td>\n",
       "      <td>1</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.16</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>999998</th>\n",
       "      <td>1</td>\n",
       "      <td>2015-03-08 19:58:02</td>\n",
       "      <td>2015-03-08 20:08:31</td>\n",
       "      <td>1</td>\n",
       "      <td>1.90</td>\n",
       "      <td>-8236211.248</td>\n",
       "      <td>4973078.115</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>-8234247.665</td>\n",
       "      <td>4976628.927</td>\n",
       "      <td>1</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.50</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>999999</th>\n",
       "      <td>2</td>\n",
       "      <td>2015-03-27 15:04:57</td>\n",
       "      <td>2015-03-27 15:06:59</td>\n",
       "      <td>5</td>\n",
       "      <td>0.38</td>\n",
       "      <td>-8235472.356</td>\n",
       "      <td>4973184.032</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>-8234950.886</td>\n",
       "      <td>4973435.099</td>\n",
       "      <td>2</td>\n",
       "      <td>3.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1000000 rows × 18 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        VendorID tpep_pickup_datetime tpep_dropoff_datetime  passenger_count  \\\n",
       "0              2  2015-03-29 04:35:02   2015-03-29 05:06:31                2   \n",
       "1              2  2015-02-17 18:56:56   2015-02-17 19:07:43                2   \n",
       "2              1  2015-02-04 22:23:41   2015-02-04 22:37:13                1   \n",
       "3              1  2015-01-07 12:26:10   2015-01-07 12:46:03                1   \n",
       "4              2  2015-01-17 18:39:27   2015-01-17 18:44:30                1   \n",
       "...          ...                  ...                   ...              ...   \n",
       "999995         1  2015-03-19 06:17:23   2015-03-19 06:20:22                1   \n",
       "999996         1  2015-01-01 19:44:50   2015-01-01 19:58:00                1   \n",
       "999997         1  2015-02-15 16:58:12   2015-02-15 17:03:35                1   \n",
       "999998         1  2015-03-08 19:58:02   2015-03-08 20:08:31                1   \n",
       "999999         2  2015-03-27 15:04:57   2015-03-27 15:06:59                5   \n",
       "\n",
       "        trip_distance     pickup_x     pickup_y  RateCodeID  \\\n",
       "0                8.46 -8237041.014  4976969.222           1   \n",
       "1                2.75 -8237333.173  4971417.775           1   \n",
       "2                2.80 -8235801.885  4977181.703           1   \n",
       "3                3.70 -8235904.650  4977015.194           1   \n",
       "4                0.92 -8235334.770  4975885.027           1   \n",
       "...               ...          ...          ...         ...   \n",
       "999995           0.80 -8233500.281  4977410.447           1   \n",
       "999996           3.10 -8237107.259  4971248.563           1   \n",
       "999997           0.40 -8235828.213  4978243.614           1   \n",
       "999998           1.90 -8236211.248  4973078.115           1   \n",
       "999999           0.38 -8235472.356  4973184.032           1   \n",
       "\n",
       "       store_and_fwd_flag    dropoff_x    dropoff_y  payment_type  \\\n",
       "0                       N -8231378.729  4965405.816             2   \n",
       "1                       N -8233394.119  4970193.574             2   \n",
       "2                       N -8236263.055  4972415.178             1   \n",
       "3                       N -8239372.343  4969838.949             1   \n",
       "4                       N -8236558.611  4977024.164             2   \n",
       "...                   ...          ...          ...           ...   \n",
       "999995                  N -8234279.939  4975993.217             1   \n",
       "999996                  N -8236611.268  4976348.066             1   \n",
       "999997                  N -8234988.255  4977852.252             1   \n",
       "999998                  N -8234247.665  4976628.927             1   \n",
       "999999                  N -8234950.886  4973435.099             2   \n",
       "\n",
       "        fare_amount  extra  mta_tax  tip_amount  tolls_amount  \\\n",
       "0              29.0    0.5      0.5        0.00           0.0   \n",
       "1              11.0    1.0      0.5        0.00           0.0   \n",
       "2              11.5    0.5      0.5        2.55           0.0   \n",
       "3              15.5    0.0      0.5        3.26           0.0   \n",
       "4               5.5    0.0      0.5        0.00           0.0   \n",
       "...             ...    ...      ...         ...           ...   \n",
       "999995          4.5    0.0      0.5        1.05           0.0   \n",
       "999996         12.0    0.0      0.5        2.55           0.0   \n",
       "999997          5.0    0.0      0.5        1.16           0.0   \n",
       "999998          9.0    0.5      0.5        1.50           0.0   \n",
       "999999          3.5    0.0      0.5        0.00           0.0   \n",
       "\n",
       "        improvement_surcharge  \n",
       "0                       30.30  \n",
       "1                       12.80  \n",
       "2                       15.35  \n",
       "3                        0.30  \n",
       "4                        0.30  \n",
       "...                       ...  \n",
       "999995                   6.35  \n",
       "999996                   0.00  \n",
       "999997                   6.96  \n",
       "999998                  11.80  \n",
       "999999                   4.30  \n",
       "\n",
       "[1000000 rows x 18 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "from blazingsql import BlazingContext\n",
    "bc = BlazingContext()\n",
    "\n",
    "# identify path to this directory (cwd) & above directory (main)\n",
    "from os import getcwd\n",
    "cwd = getcwd()\n",
    "main = '/'.join(cwd.split('/')[:-1])\n",
    "\n",
    "# create table from from 3 csv files stored in turbo-telegram repo\n",
    "bc.create_table('taxi', f'{main}/turbo-telegram/data/nyc_taxi_*.csv', header=0)\n",
    "\n",
    "# overwrite table w/ 1m samples of the prior tables's 1.9m rows \n",
    "bc.create_table('taxi', bc.sql('select * from taxi').to_pandas().sample(1000000))\n",
    "\n",
    "# query the whole table & store results as csv in this repo's data directory\n",
    "bc.sql('select * from taxi').to_csv('data/nyc_yellow_cab.csv', index=False)\n",
    "\n",
    "# forget the table exists\n",
    "bc.drop_table('taxi')\n",
    "\n",
    "# create a table from the file we just wrote & see how it looks\n",
    "bc.create_table('new_taxi', f'{cwd}/data/nyc_yellow_cab.csv', header=0)\n",
    "bc.sql('select * from new_taxi')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Runtime Details"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "# packages in environment at /opt/conda-environments/rapids-nightly:\n",
      "#\n",
      "# Name                    Version                   Build  Channel\n",
      "blazingsql                0.13.0a         cuda10.0_py37_267    blazingsql-nightly/label/cuda10.0\n",
      "\n",
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    }
   ],
   "source": [
    "conda list blazingsql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Fri Apr 10 10:10:57 2020       \n",
      "+-----------------------------------------------------------------------------+\n",
      "| NVIDIA-SMI 440.48.02    Driver Version: 440.48.02    CUDA Version: 10.2     |\n",
      "|-------------------------------+----------------------+----------------------+\n",
      "| GPU  Name        Persistence-M| Bus-Id        Disp.A | Volatile Uncorr. ECC |\n",
      "| Fan  Temp  Perf  Pwr:Usage/Cap|         Memory-Usage | GPU-Util  Compute M. |\n",
      "|===============================+======================+======================|\n",
      "|   0  Tesla T4            Off  | 00000000:00:1E.0 Off |                    0 |\n",
      "| N/A   41C    P0    27W /  70W |    670MiB / 15109MiB |      0%      Default |\n",
      "+-------------------------------+----------------------+----------------------+\n",
      "                                                                               \n",
      "+-----------------------------------------------------------------------------+\n",
      "| Processes:                                                       GPU Memory |\n",
      "|  GPU       PID   Type   Process name                             Usage      |\n",
      "|=============================================================================|\n",
      "|    0     30009      C   ...-environments/rapids-nightly/bin/python   423MiB |\n",
      "+-----------------------------------------------------------------------------+\n"
     ]
    }
   ],
   "source": [
    "!nvidia-smi"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "RAPIDS Nightly",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}