federated_query_demo.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "8AdUt3HiUrc3"
   },
   "source": [
    "# Querying Multiple Data Formats \n",
    "In this notebook, we will cover: \n",
    "- How to create and then join BlazingSQL tables from CSV, Parquet, and GPU DataFrame (GDF) sources. \n",
    "\n",
    "## Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import cudf\n",
    "from blazingsql import BlazingContext"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "aMwNKxePSwOp"
   },
   "source": [
    "## Import packages and create BlazingContext\n",
    "You can think of the BlazingContext much like a SparkContext; this is where information such as FileSystems you have registered and Tables you have created will be stored. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 35
    },
    "colab_type": "code",
    "id": "azZ7l2q7odYT",
    "outputId": "a5302d6e-307e-45c5-a682-c786cc999a40"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "BlazingContext ready\n"
     ]
    }
   ],
   "source": [
    "# start up BlazingSQL\n",
    "bc = BlazingContext()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "N2bqpDEnZyQf"
   },
   "source": [
    "### Create Table from CSV\n",
    "Here we create a BlazingSQL table directly from a comma-separated values (CSV) file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "HhRhj-ZvZygH"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pyblazing.apiv2.context.BlazingTable at 0x7f2a31333da0>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# define column names and types\n",
    "column_names = ['diagnosis_result', 'radius', 'texture', 'perimeter']\n",
    "column_types = ['float32', 'float32', 'float32', 'float32']\n",
    "\n",
    "# identify local directory path \n",
    "cwd = os.getcwd()\n",
    "# add path to data\n",
    "data_path = cwd + '/data/cancer_data_00.csv'\n",
    "\n",
    "# create table from CSV file\n",
    "bc.create_table('data_00', data_path, dtype=column_types, names=column_names)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "HJFz-mqZTJ5Z"
   },
   "source": [
    "### Create Table from Parquet\n",
    "Here we create a BlazingSQL table directly from an Apache Parquet file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "HJuvtJDYTMyb"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pyblazing.apiv2.context.BlazingTable at 0x7f2a300b7588>"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create table from Parquet file\n",
    "bc.create_table('data_01', cwd + '/data/cancer_data_01.parquet')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "98HJFrt5TRa0"
   },
   "source": [
    "### Create Table from GPU DataFrame\n",
    "Here we use cuDF to create a GPU DataFrame (GDF), then use BlazingSQL to create a table from that GDF.\n",
    "\n",
    "The GDF is the standard memory representation for the RAPIDS AI ecosystem."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "14GwxmLsTV_p",
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pyblazing.apiv2.context.BlazingTable at 0x7f2a300b9080>"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# define column names and types\n",
    "column_names = ['compactness', 'symmetry', 'fractal_dimension']\n",
    "column_types = ['float32', 'float32', 'float32', 'float32']\n",
    "\n",
    "# make GDF with cuDF (uses relative path)\n",
    "gdf_02 = cudf.read_csv('data/cancer_data_02.csv', dtype=column_types, names=column_names)\n",
    "\n",
    "# create BlazingSQL table from GDF\n",
    "bc.create_table('data_02', gdf_02)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "9DAZShZ2y-Nx"
   },
   "source": [
    "# Join Tables Together \n",
    "\n",
    "Now we can use BlazingSQL to join all three data formats in a single federated query. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 1000
    },
    "colab_type": "code",
    "id": "HOYSFebvzGcX",
    "outputId": "ad133dfd-540e-4142-8f12-a4a70d803bb6",
    "scrolled": true
   },
   "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>diagnosis_result</th>\n",
       "      <th>radius</th>\n",
       "      <th>texture</th>\n",
       "      <th>perimeter</th>\n",
       "      <th>area</th>\n",
       "      <th>smoothness</th>\n",
       "      <th>compactness</th>\n",
       "      <th>symmetry</th>\n",
       "      <th>fractal_dimension</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>120.0</td>\n",
       "      <td>1033.0</td>\n",
       "      <td>0.115</td>\n",
       "      <td>0.149000004</td>\n",
       "      <td>0.209000006</td>\n",
       "      <td>0.063000001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>86.0</td>\n",
       "      <td>563.0</td>\n",
       "      <td>0.082</td>\n",
       "      <td>0.059999999</td>\n",
       "      <td>0.178000003</td>\n",
       "      <td>0.056000002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>94.0</td>\n",
       "      <td>578.0</td>\n",
       "      <td>0.113</td>\n",
       "      <td>0.229000002</td>\n",
       "      <td>0.207000002</td>\n",
       "      <td>0.077</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>122.0</td>\n",
       "      <td>1094.0</td>\n",
       "      <td>0.094</td>\n",
       "      <td>0.107000001</td>\n",
       "      <td>0.170000002</td>\n",
       "      <td>0.057</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>87.0</td>\n",
       "      <td>566.0</td>\n",
       "      <td>0.098</td>\n",
       "      <td>0.081</td>\n",
       "      <td>0.18900001</td>\n",
       "      <td>0.058000002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>83.0</td>\n",
       "      <td>477.0</td>\n",
       "      <td>0.128</td>\n",
       "      <td>0.170000002</td>\n",
       "      <td>0.209000006</td>\n",
       "      <td>0.075999998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>83.0</td>\n",
       "      <td>477.0</td>\n",
       "      <td>0.128</td>\n",
       "      <td>0.170000002</td>\n",
       "      <td>0.209000006</td>\n",
       "      <td>0.075999998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>86.0</td>\n",
       "      <td>535.0</td>\n",
       "      <td>0.116</td>\n",
       "      <td>0.123000003</td>\n",
       "      <td>0.213000014</td>\n",
       "      <td>0.067999996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>87.0</td>\n",
       "      <td>545.0</td>\n",
       "      <td>0.104</td>\n",
       "      <td>0.143999994</td>\n",
       "      <td>0.196999997</td>\n",
       "      <td>0.067999996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>132.0</td>\n",
       "      <td>1123.0</td>\n",
       "      <td>0.097</td>\n",
       "      <td>0.246000007</td>\n",
       "      <td>0.24000001</td>\n",
       "      <td>0.078000002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>83.0</td>\n",
       "      <td>524.0</td>\n",
       "      <td>0.090</td>\n",
       "      <td>0.037999999</td>\n",
       "      <td>0.147</td>\n",
       "      <td>0.059</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>124.0</td>\n",
       "      <td>1076.0</td>\n",
       "      <td>0.110</td>\n",
       "      <td>0.169</td>\n",
       "      <td>0.191</td>\n",
       "      <td>0.059999999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>0.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>83.0</td>\n",
       "      <td>524.0</td>\n",
       "      <td>0.090</td>\n",
       "      <td>0.037999999</td>\n",
       "      <td>0.147</td>\n",
       "      <td>0.059</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>94.0</td>\n",
       "      <td>633.0</td>\n",
       "      <td>0.098</td>\n",
       "      <td>0.109999999</td>\n",
       "      <td>0.18900001</td>\n",
       "      <td>0.060999997</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>83.0</td>\n",
       "      <td>527.0</td>\n",
       "      <td>0.081</td>\n",
       "      <td>0.037999999</td>\n",
       "      <td>0.147</td>\n",
       "      <td>0.059</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>0.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>83.0</td>\n",
       "      <td>527.0</td>\n",
       "      <td>0.081</td>\n",
       "      <td>0.037999999</td>\n",
       "      <td>0.147</td>\n",
       "      <td>0.059</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>706.0</td>\n",
       "      <td>0.104</td>\n",
       "      <td>0.155000001</td>\n",
       "      <td>0.186000004</td>\n",
       "      <td>0.063000001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>1.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>706.0</td>\n",
       "      <td>0.104</td>\n",
       "      <td>0.155000001</td>\n",
       "      <td>0.186000004</td>\n",
       "      <td>0.063000001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>87.0</td>\n",
       "      <td>561.0</td>\n",
       "      <td>0.088</td>\n",
       "      <td>0.077</td>\n",
       "      <td>0.181000009</td>\n",
       "      <td>0.057</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>0.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>96.0</td>\n",
       "      <td>699.0</td>\n",
       "      <td>0.094</td>\n",
       "      <td>0.050999999</td>\n",
       "      <td>0.157000005</td>\n",
       "      <td>0.055</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>0.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>371.0</td>\n",
       "      <td>0.123</td>\n",
       "      <td>0.122000001</td>\n",
       "      <td>0.189999998</td>\n",
       "      <td>0.068999998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>0.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>96.0</td>\n",
       "      <td>657.0</td>\n",
       "      <td>0.114</td>\n",
       "      <td>0.136999995</td>\n",
       "      <td>0.203000009</td>\n",
       "      <td>0.067999996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>0.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>96.0</td>\n",
       "      <td>646.0</td>\n",
       "      <td>0.105</td>\n",
       "      <td>0.201000005</td>\n",
       "      <td>0.194999993</td>\n",
       "      <td>0.072999999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>413.0</td>\n",
       "      <td>0.090</td>\n",
       "      <td>0.075000003</td>\n",
       "      <td>0.162</td>\n",
       "      <td>0.066</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>1.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>97.0</td>\n",
       "      <td>713.0</td>\n",
       "      <td>0.091</td>\n",
       "      <td>0.071000002</td>\n",
       "      <td>0.162</td>\n",
       "      <td>0.057</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>0.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>83.0</td>\n",
       "      <td>506.0</td>\n",
       "      <td>0.099</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>0.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>532.0</td>\n",
       "      <td>0.097</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>87.0</td>\n",
       "      <td>572.0</td>\n",
       "      <td>0.077</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>1.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>83.0</td>\n",
       "      <td>477.0</td>\n",
       "      <td>0.128</td>\n",
       "      <td>0.170000002</td>\n",
       "      <td>0.193000004</td>\n",
       "      <td>0.064999998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>0.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>83.0</td>\n",
       "      <td>477.0</td>\n",
       "      <td>0.128</td>\n",
       "      <td>0.170000002</td>\n",
       "      <td>0.193000004</td>\n",
       "      <td>0.064999998</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>286</th>\n",
       "      <td>1.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>97.0</td>\n",
       "      <td>659.0</td>\n",
       "      <td>0.114</td>\n",
       "      <td>0.159999996</td>\n",
       "      <td>0.207000002</td>\n",
       "      <td>0.059999999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>287</th>\n",
       "      <td>0.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>274.0</td>\n",
       "      <td>0.102</td>\n",
       "      <td>0.064999998</td>\n",
       "      <td>0.182000011</td>\n",
       "      <td>0.068999998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>288</th>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>88.0</td>\n",
       "      <td>520.0</td>\n",
       "      <td>0.127</td>\n",
       "      <td>0.193000004</td>\n",
       "      <td>0.234999999</td>\n",
       "      <td>0.074000001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>289</th>\n",
       "      <td>0.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>86.0</td>\n",
       "      <td>520.0</td>\n",
       "      <td>0.108</td>\n",
       "      <td>0.127000004</td>\n",
       "      <td>0.196999997</td>\n",
       "      <td>0.067999996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>290</th>\n",
       "      <td>1.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>94.0</td>\n",
       "      <td>643.0</td>\n",
       "      <td>0.098</td>\n",
       "      <td>0.114</td>\n",
       "      <td>0.188000008</td>\n",
       "      <td>0.063999996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>291</th>\n",
       "      <td>0.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>685.0</td>\n",
       "      <td>0.099</td>\n",
       "      <td>0.071999997</td>\n",
       "      <td>0.159000009</td>\n",
       "      <td>0.059</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292</th>\n",
       "      <td>1.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>97.0</td>\n",
       "      <td>645.0</td>\n",
       "      <td>0.105</td>\n",
       "      <td>0.187000006</td>\n",
       "      <td>0.224999994</td>\n",
       "      <td>0.068999998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>293</th>\n",
       "      <td>0.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>261.0</td>\n",
       "      <td>0.077</td>\n",
       "      <td>0.088</td>\n",
       "      <td>0.233999997</td>\n",
       "      <td>0.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>294</th>\n",
       "      <td>0.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>261.0</td>\n",
       "      <td>0.077</td>\n",
       "      <td>0.088</td>\n",
       "      <td>0.233999997</td>\n",
       "      <td>0.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>295</th>\n",
       "      <td>1.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>104.0</td>\n",
       "      <td>783.0</td>\n",
       "      <td>0.084</td>\n",
       "      <td>0.100000001</td>\n",
       "      <td>0.185000002</td>\n",
       "      <td>0.052999999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>296</th>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>88.0</td>\n",
       "      <td>559.0</td>\n",
       "      <td>0.102</td>\n",
       "      <td>0.126000002</td>\n",
       "      <td>0.172000006</td>\n",
       "      <td>0.063999996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>297</th>\n",
       "      <td>1.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>121.0</td>\n",
       "      <td>1075.0</td>\n",
       "      <td>0.099</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>298</th>\n",
       "      <td>1.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>94.0</td>\n",
       "      <td>648.0</td>\n",
       "      <td>0.094</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>299</th>\n",
       "      <td>1.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>122.0</td>\n",
       "      <td>1076.0</td>\n",
       "      <td>0.090</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300</th>\n",
       "      <td>0.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>466.0</td>\n",
       "      <td>0.088</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301</th>\n",
       "      <td>0.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>96.0</td>\n",
       "      <td>652.0</td>\n",
       "      <td>0.113</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>302</th>\n",
       "      <td>0.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>663.0</td>\n",
       "      <td>0.090</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>303</th>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>87.0</td>\n",
       "      <td>555.0</td>\n",
       "      <td>0.102</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>304</th>\n",
       "      <td>0.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>244.0</td>\n",
       "      <td>0.098</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>305</th>\n",
       "      <td>0.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>244.0</td>\n",
       "      <td>0.098</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>306</th>\n",
       "      <td>1.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>104.0</td>\n",
       "      <td>781.0</td>\n",
       "      <td>0.097</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>307</th>\n",
       "      <td>1.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>120.0</td>\n",
       "      <td>1040.0</td>\n",
       "      <td>0.095</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>308</th>\n",
       "      <td>1.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>83.0</td>\n",
       "      <td>506.0</td>\n",
       "      <td>0.099</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>309</th>\n",
       "      <td>1.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>578.0</td>\n",
       "      <td>0.119</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>310</th>\n",
       "      <td>1.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>97.0</td>\n",
       "      <td>659.0</td>\n",
       "      <td>0.114</td>\n",
       "      <td>0.159999996</td>\n",
       "      <td>0.230000004</td>\n",
       "      <td>0.071000002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>311</th>\n",
       "      <td>0.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>552.0</td>\n",
       "      <td>0.074</td>\n",
       "      <td>0.050999999</td>\n",
       "      <td>0.138999999</td>\n",
       "      <td>0.052999999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>312</th>\n",
       "      <td>0.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>77.0</td>\n",
       "      <td>443.0</td>\n",
       "      <td>0.097</td>\n",
       "      <td>0.071999997</td>\n",
       "      <td>0.208000004</td>\n",
       "      <td>0.059999999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>313</th>\n",
       "      <td>0.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>86.0</td>\n",
       "      <td>520.0</td>\n",
       "      <td>0.108</td>\n",
       "      <td>0.127000004</td>\n",
       "      <td>0.192000002</td>\n",
       "      <td>0.059999999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>314</th>\n",
       "      <td>0.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>685.0</td>\n",
       "      <td>0.099</td>\n",
       "      <td>0.071999997</td>\n",
       "      <td>0.208000004</td>\n",
       "      <td>0.059999999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>315</th>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>88.0</td>\n",
       "      <td>559.0</td>\n",
       "      <td>0.102</td>\n",
       "      <td>0.126000002</td>\n",
       "      <td>0.191</td>\n",
       "      <td>0.066</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>316 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     diagnosis_result  radius  texture  perimeter    area  smoothness  \\\n",
       "0                 1.0    11.0     21.0      120.0  1033.0       0.115   \n",
       "1                 0.0    17.0     21.0       86.0   563.0       0.082   \n",
       "2                 1.0    19.0     26.0       94.0   578.0       0.113   \n",
       "3                 1.0    19.0     11.0      122.0  1094.0       0.094   \n",
       "4                 0.0    10.0     17.0       87.0   566.0       0.098   \n",
       "5                 1.0    16.0     19.0       83.0   477.0       0.128   \n",
       "6                 0.0    22.0     16.0       83.0   477.0       0.128   \n",
       "7                 0.0    17.0     21.0       86.0   535.0       0.116   \n",
       "8                 0.0    10.0     17.0       87.0   545.0       0.104   \n",
       "9                 1.0    23.0     16.0      132.0  1123.0       0.097   \n",
       "10                1.0    16.0     19.0       83.0   524.0       0.090   \n",
       "11                1.0    21.0     18.0      124.0  1076.0       0.110   \n",
       "12                0.0    22.0     16.0       83.0   524.0       0.090   \n",
       "13                1.0    19.0     26.0       94.0   633.0       0.098   \n",
       "14                1.0    16.0     19.0       83.0   527.0       0.081   \n",
       "15                0.0    22.0     16.0       83.0   527.0       0.081   \n",
       "16                1.0    10.0     12.0      100.0   706.0       0.104   \n",
       "17                1.0    22.0     26.0      100.0   706.0       0.104   \n",
       "18                0.0    10.0     17.0       87.0   561.0       0.088   \n",
       "19                0.0    12.0     23.0       96.0   699.0       0.094   \n",
       "20                0.0    18.0     12.0       72.0   371.0       0.123   \n",
       "21                0.0    12.0     23.0       96.0   657.0       0.114   \n",
       "22                0.0    12.0     23.0       96.0   646.0       0.105   \n",
       "23                0.0    10.0     18.0       74.0   413.0       0.090   \n",
       "24                1.0    11.0     21.0       97.0   713.0       0.091   \n",
       "25                0.0    22.0     16.0       83.0   506.0       0.099   \n",
       "26                0.0    14.0     14.0       85.0   532.0       0.097   \n",
       "27                0.0    10.0     17.0       87.0   572.0       0.077   \n",
       "28                1.0    16.0     19.0       83.0   477.0       0.128   \n",
       "29                0.0    22.0     16.0       83.0   477.0       0.128   \n",
       "..                ...     ...      ...        ...     ...         ...   \n",
       "286               1.0    11.0     21.0       97.0   659.0       0.114   \n",
       "287               0.0    12.0     13.0       60.0   274.0       0.102   \n",
       "288               0.0    10.0     17.0       88.0   520.0       0.127   \n",
       "289               0.0    17.0     21.0       86.0   520.0       0.108   \n",
       "290               1.0    19.0     26.0       94.0   643.0       0.098   \n",
       "291               0.0    23.0     27.0       95.0   685.0       0.099   \n",
       "292               1.0    11.0     21.0       97.0   645.0       0.105   \n",
       "293               0.0    16.0     17.0       59.0   261.0       0.077   \n",
       "294               0.0     9.0     26.0       59.0   261.0       0.077   \n",
       "295               1.0    21.0     18.0      104.0   783.0       0.084   \n",
       "296               0.0    10.0     17.0       88.0   559.0       0.102   \n",
       "297               1.0    14.0     13.0      121.0  1075.0       0.099   \n",
       "298               1.0    19.0     26.0       94.0   648.0       0.094   \n",
       "299               1.0    19.0     11.0      122.0  1076.0       0.090   \n",
       "300               0.0    11.0     11.0       80.0   466.0       0.088   \n",
       "301               0.0    12.0     23.0       96.0   652.0       0.113   \n",
       "302               0.0    23.0     27.0       95.0   663.0       0.090   \n",
       "303               0.0    10.0     17.0       87.0   555.0       0.102   \n",
       "304               0.0    16.0     17.0       59.0   244.0       0.098   \n",
       "305               0.0     9.0     26.0       59.0   244.0       0.098   \n",
       "306               1.0    21.0     18.0      104.0   781.0       0.097   \n",
       "307               1.0    11.0     21.0      120.0  1040.0       0.095   \n",
       "308               1.0    16.0     19.0       83.0   506.0       0.099   \n",
       "309               1.0    22.0     25.0       90.0   578.0       0.119   \n",
       "310               1.0    11.0     21.0       97.0   659.0       0.114   \n",
       "311               0.0    14.0     14.0       85.0   552.0       0.074   \n",
       "312               0.0    25.0     21.0       77.0   443.0       0.097   \n",
       "313               0.0    17.0     21.0       86.0   520.0       0.108   \n",
       "314               0.0    23.0     27.0       95.0   685.0       0.099   \n",
       "315               0.0    10.0     17.0       88.0   559.0       0.102   \n",
       "\n",
       "     compactness     symmetry fractal_dimension  \n",
       "0    0.149000004  0.209000006       0.063000001  \n",
       "1    0.059999999  0.178000003       0.056000002  \n",
       "2    0.229000002  0.207000002             0.077  \n",
       "3    0.107000001  0.170000002             0.057  \n",
       "4          0.081   0.18900001       0.058000002  \n",
       "5    0.170000002  0.209000006       0.075999998  \n",
       "6    0.170000002  0.209000006       0.075999998  \n",
       "7    0.123000003  0.213000014       0.067999996  \n",
       "8    0.143999994  0.196999997       0.067999996  \n",
       "9    0.246000007   0.24000001       0.078000002  \n",
       "10   0.037999999        0.147             0.059  \n",
       "11         0.169        0.191       0.059999999  \n",
       "12   0.037999999        0.147             0.059  \n",
       "13   0.109999999   0.18900001       0.060999997  \n",
       "14   0.037999999        0.147             0.059  \n",
       "15   0.037999999        0.147             0.059  \n",
       "16   0.155000001  0.186000004       0.063000001  \n",
       "17   0.155000001  0.186000004       0.063000001  \n",
       "18         0.077  0.181000009             0.057  \n",
       "19   0.050999999  0.157000005             0.055  \n",
       "20   0.122000001  0.189999998       0.068999998  \n",
       "21   0.136999995  0.203000009       0.067999996  \n",
       "22   0.201000005  0.194999993       0.072999999  \n",
       "23   0.075000003        0.162             0.066  \n",
       "24   0.071000002        0.162             0.057  \n",
       "25          null         null              null  \n",
       "26          null         null              null  \n",
       "27          null         null              null  \n",
       "28   0.170000002  0.193000004       0.064999998  \n",
       "29   0.170000002  0.193000004       0.064999998  \n",
       "..           ...          ...               ...  \n",
       "286  0.159999996  0.207000002       0.059999999  \n",
       "287  0.064999998  0.182000011       0.068999998  \n",
       "288  0.193000004  0.234999999       0.074000001  \n",
       "289  0.127000004  0.196999997       0.067999996  \n",
       "290        0.114  0.188000008       0.063999996  \n",
       "291  0.071999997  0.159000009             0.059  \n",
       "292  0.187000006  0.224999994       0.068999998  \n",
       "293        0.088  0.233999997              0.07  \n",
       "294        0.088  0.233999997              0.07  \n",
       "295  0.100000001  0.185000002       0.052999999  \n",
       "296  0.126000002  0.172000006       0.063999996  \n",
       "297         null         null              null  \n",
       "298         null         null              null  \n",
       "299         null         null              null  \n",
       "300         null         null              null  \n",
       "301         null         null              null  \n",
       "302         null         null              null  \n",
       "303         null         null              null  \n",
       "304         null         null              null  \n",
       "305         null         null              null  \n",
       "306         null         null              null  \n",
       "307         null         null              null  \n",
       "308         null         null              null  \n",
       "309         null         null              null  \n",
       "310  0.159999996  0.230000004       0.071000002  \n",
       "311  0.050999999  0.138999999       0.052999999  \n",
       "312  0.071999997  0.208000004       0.059999999  \n",
       "313  0.127000004  0.192000002       0.059999999  \n",
       "314  0.071999997  0.208000004       0.059999999  \n",
       "315  0.126000002        0.191             0.066  \n",
       "\n",
       "[316 rows x 9 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# grab everything from 00 & 02, area & smoothness from 01\n",
    "query = '''\n",
    "        SELECT \n",
    "            a.*, \n",
    "            b.area, b.smoothness, \n",
    "            c.* \n",
    "        FROM \n",
    "            data_00 AS a\n",
    "        LEFT JOIN \n",
    "            data_01 AS b\n",
    "            ON (a.perimeter = b.perimeter)\n",
    "        LEFT JOIN \n",
    "            data_02 AS c\n",
    "            ON (b.compactness = c.compactness)\n",
    "        '''\n",
    "\n",
    "# join the tables together (type(gdf)==cudf.core.dataframe.Dataframe)\n",
    "gdf = bc.sql(query)\n",
    "\n",
    "# display result\n",
    "gdf"
   ]
  },
  {
   "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",
    "Check out our [docs](https://docs.blazingdb.com) 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": [
    "McVBO7GHRDzz"
   ],
   "name": "BlazingSQL_Federated_Query_Demo.ipynb",
   "provenance": [],
   "toc_visible": true
  },
  "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}