getting_started_notebooks/basics/blazingsql/getting_started_with_blazingsql.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Getting Started with BlazingSQL\n",
    "*By Winston Robson*\n",
    "\n",
    "In this notebook, we will cover how to read and query CSV files with cuDF and BlazingSQL."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "aMwNKxePSwOp"
   },
   "source": [
    "#### BlazingSQL install check\n",
    "The next cell checks that you have BlazingSQL installed, and offers to install it if not (making sure the notebook will run as expected)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"You've got BlazingSQL set up perfectly! Let's get started with SQL in RAPIDS AI!\""
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import sys \n",
    "# point import path notebooks-contrib/utils\n",
    "sys.path.append('../../../utils/')\n",
    "\n",
    "from sql_check import bsql_start\n",
    "# check that BlazingSQL is installed\n",
    "bsql_start()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Download Data\n",
    "This cell will check if you have the data for this demo, and, if you don't, will download it for you."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "creating blazingsql directory\n",
      "\n",
      "--2020-01-21 17:53:55--  https://s3.amazonaws.com/blazingsql-colab/Music.csv\r\n",
      "Resolving s3.amazonaws.com (s3.amazonaws.com)... 52.216.139.221\r\n",
      "Connecting to s3.amazonaws.com (s3.amazonaws.com)|52.216.139.221|:443... connected.\r\n",
      "HTTP request sent, awaiting response... 200 OK\r\n",
      "Length: 10473 (10K) [text/csv]\r\n",
      "Saving to: ‘../../../data/blazingsql/Music.csv’\r\n",
      "\r\n",
      "\r",
      "Music.csv             0%[                    ]       0  --.-KB/s               \r",
      "Music.csv           100%[===================>]  10.23K  --.-KB/s    in 0s      \r\n",
      "\r\n",
      "2020-01-21 17:53:56 (203 MB/s) - ‘../../../data/blazingsql/Music.csv’ saved [10473/10473]\r\n",
      "\r\n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "\n",
    "# relative path to data \n",
    "data_dir = '../../../data/blazingsql/'\n",
    "# file name\n",
    "fn = 'Music.csv'\n",
    "\n",
    "# does folder exist?\n",
    "if not os.path.exists(data_dir):\n",
    "    print('creating blazingsql directory\\n')\n",
    "    # create folder\n",
    "    os.system('mkdir ../../data/blazingsql')\n",
    "\n",
    "# do we have music file?\n",
    "if not os.path.isfile(data_dir + fn):\n",
    "    !wget -P ../../../data/blazingsql 'https://s3.amazonaws.com/blazingsql-colab/Music.csv'\n",
    "else:\n",
    "    print(\"You've got the data!\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create BlazingContext\n",
    "You can think of BlazingContext much like a Spark Context (i.e. information such as FileSystems registered & Tables created will be stored)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 35
    },
    "colab_type": "code",
    "id": "ZR_vWwtMcvvY",
    "outputId": "c78cc40a-f7d8-4ac5-c255-d99edd03b785"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "BlazingContext ready\n"
     ]
    }
   ],
   "source": [
    "from blazingsql import BlazingContext\n",
    "# start up BlazingSQL\n",
    "bc = BlazingContext()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "N2bqpDEnZyQf"
   },
   "source": [
    "## Read CSV\n",
    "First we need to download a CSV file. Then we use cuDF to read the CSV file, which gives us a GPU DataFrame (GDF). \n",
    "\n",
    "To learn more about the GDF and how it enables end to end workloads on rapids, check out *[The GPU DataFrame (GDF) and cuDF in RAPIDS AI](https://blog.blazingdb.com/blazingsql-part-1-the-gpu-dataframe-gdf-and-cudf-in-rapids-ai-96ec15102240)*."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "HhRhj-ZvZygH"
   },
   "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>ARTIST</th>\n",
       "      <th>RATING</th>\n",
       "      <th>YEAR</th>\n",
       "      <th>LOCATION</th>\n",
       "      <th>FESTIVAL_SET</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Arcade Fire</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2018.0</td>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Justice</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2018.0</td>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Florence and The Machine</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2018.0</td>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Odesza</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2018.0</td>\n",
       "      <td>Indio</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bon Iver</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>Indio</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     ARTIST  RATING    YEAR   LOCATION  FESTIVAL_SET\n",
       "0               Arcade Fire    10.0  2018.0  Las Vegas           1.0\n",
       "1                   Justice    10.0  2018.0  Las Vegas           1.0\n",
       "2  Florence and The Machine    10.0  2018.0  Las Vegas           1.0\n",
       "3                    Odesza    10.0  2018.0      Indio           1.0\n",
       "4                  Bon Iver    10.0  2017.0      Indio           1.0"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import cudf\n",
    "\n",
    "# cudf (gpu) dataframe from csv \n",
    "gdf = cudf.read_csv(data_dir + fn)\n",
    "\n",
    "# let's see how it looks\n",
    "gdf.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "HJFz-mqZTJ5Z"
   },
   "source": [
    "## Create a Table\n",
    "Now we just need to create a table. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "HJuvtJDYTMyb"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pyblazing.apiv2.context.BlazingTable at 0x7f11c00a0cf8>"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# BlazingSQL table from cuDF DataFrame \n",
    "bc.create_table('music', gdf)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "98HJFrt5TRa0"
   },
   "source": [
    "## Query a Table\n",
    "That's it! Now when you can write a SQL query the data will get processed on the GPU with BlazingSQL, and the output will be a GPU DataFrame (GDF) inside RAPIDS!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 1000
    },
    "colab_type": "code",
    "id": "14GwxmLsTV_p",
    "outputId": "144b7601-5363-49f8-d5af-13e80917672c"
   },
   "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>ARTIST</th>\n",
       "      <th>RATING</th>\n",
       "      <th>LOCATION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Arcade Fire</td>\n",
       "      <td>10.0</td>\n",
       "      <td>Las Vegas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Justice</td>\n",
       "      <td>10.0</td>\n",
       "      <td>Las Vegas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Florence and The Machine</td>\n",
       "      <td>10.0</td>\n",
       "      <td>Las Vegas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Odesza</td>\n",
       "      <td>10.0</td>\n",
       "      <td>Indio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bon Iver</td>\n",
       "      <td>10.0</td>\n",
       "      <td>Indio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>LA Philharmonic + Sigur Ros</td>\n",
       "      <td>10.0</td>\n",
       "      <td>LA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Sigur Ros</td>\n",
       "      <td>10.0</td>\n",
       "      <td>Malmo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Arcade Fire</td>\n",
       "      <td>10.0</td>\n",
       "      <td>Indio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Escort</td>\n",
       "      <td>9.0</td>\n",
       "      <td>San Francisco</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Phoenix</td>\n",
       "      <td>9.0</td>\n",
       "      <td>Berkeley</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        ARTIST  RATING       LOCATION\n",
       "0                  Arcade Fire    10.0      Las Vegas\n",
       "1                      Justice    10.0      Las Vegas\n",
       "2     Florence and The Machine    10.0      Las Vegas\n",
       "3                       Odesza    10.0          Indio\n",
       "4                     Bon Iver    10.0          Indio\n",
       "5  LA Philharmonic + Sigur Ros    10.0             LA\n",
       "6                    Sigur Ros    10.0          Malmo\n",
       "7                  Arcade Fire    10.0          Indio\n",
       "8                       Escort     9.0  San Francisco\n",
       "9                      Phoenix     9.0       Berkeley"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# query artist, rating & location for 10 events with a rating of at least 7\n",
    "result_gdf = bc.sql('SELECT ARTIST, RATING, LOCATION FROM music where RATING >= 7 LIMIT 10')\n",
    "\n",
    "# display dataframe (type(result_gdf)==cudf.core.dataframe.DataFrame)\n",
    "result_gdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>ARTIST</th>\n",
       "      <th>RATING</th>\n",
       "      <th>LOCATION</th>\n",
       "      <th>FESTIVAL_SET</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>Thundercat</td>\n",
       "      <td>3.0</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>Odesza</td>\n",
       "      <td>4.0</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Oh Wonder</td>\n",
       "      <td>6.0</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>RAC</td>\n",
       "      <td>6.0</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Alt-J</td>\n",
       "      <td>6.0</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Rufus Du Sol</td>\n",
       "      <td>7.0</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Above and Beyond</td>\n",
       "      <td>4.0</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              ARTIST  RATING       LOCATION  FESTIVAL_SET\n",
       "49        Thundercat     3.0  San Francisco           1.0\n",
       "43            Odesza     4.0  San Francisco           1.0\n",
       "25         Oh Wonder     6.0  San Francisco           1.0\n",
       "19               RAC     6.0  San Francisco           1.0\n",
       "16             Alt-J     6.0  San Francisco           1.0\n",
       "9       Rufus Du Sol     7.0  San Francisco           1.0\n",
       "40  Above and Beyond     4.0  San Francisco           1.0"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# define query \n",
    "query = '''\n",
    "        select \n",
    "            ARTIST, RATING, \n",
    "            LOCATION, FESTIVAL_SET \n",
    "        from \n",
    "            music \n",
    "            where LOCATION = 'San Francisco'\n",
    "            '''\n",
    "\n",
    "# pull events in San Francisco, CA\n",
    "gdf = bc.sql(query)\n",
    "\n",
    "# sample 7 rows by converting to pandas \n",
    "gdf.to_pandas().sample(7)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "wygAeTIFTm2X"
   },
   "source": [
    "# You're Ready to Rock\n",
    "And... thats it! You are now live with BlazingSQL.\n",
    "\n",
    "\n",
    "Check out our [docs](https://docs.blazingdb.com) or [Twitter](https://twitter.com/blazingsql) to get fancy or to learn more about how BlazingSQL works with the rest of [RAPIDS AI](https://rapids.ai/)."
   ]
  }
 ],
 "metadata": {
  "accelerator": "GPU",
  "colab": {
   "collapsed_sections": [],
   "name": "blazingsql_demo.ipynb",
   "provenance": []
  },
  "kernelspec": {
   "display_name": "Python 3",
   "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.6.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}