tutorials/example_covid19.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# COVID19 Data Exploration\n",
    "\n",
    "This example is to demonstrate how to use Fugue SQL to do data analytics. You will see how we keep the backbone inside SQL while using Fugue extensions to accomplish certain things.\n",
    "\n",
    "Also pay attention to how we iterate on the problem: from partial to all, from local to spark. It's very efficient to do thing in this way because it can maximize your iteration speed.\n",
    "\n",
    "This dataset is from [Kaggle Novel Corona Virus 2019 Dataset](https://www.kaggle.com/sudalairajkumar/novel-corona-virus-2019-dataset).\n",
    "\n",
    "## First of all, I want to make the experiment environment more friendly\n",
    "\n",
    "There is small amount of python code to write in order to run each piece of Fugue SQL, however, I want to make that even simpler using [magic](https://ipython.readthedocs.io/en/stable/interactive/magics.html). So firstly I registered a new magic `%%sql` so I can directly write Fugue SQL in cells.\n",
    "\n",
    "I also feel that the built in `PRINT` can't print fat tables nicely in this environment, so I write my own print extension `pprint` to render the dataframes as pandas dataframes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from fugue import NativeExecutionEngine, DataFrames, PandasDataFrame, Schema, Transformer, IterableDataFrame, ArrayDataFrame\n",
    "from fugue_sql import FugueSQLWorkflow\n",
    "from fugue_spark import SparkExecutionEngine\n",
    "from IPython.display import display, HTML\n",
    "from IPython.core.magic import (register_cell_magic, register_line_cell_magic)\n",
    "from typing import Iterable, Dict, Any\n",
    "from datetime import datetime\n",
    "\n",
    "@register_cell_magic\n",
    "def sql(line, cell):\n",
    "    dag = FugueSQLWorkflow()\n",
    "    dag(cell)\n",
    "    dag.run(NativeExecutionEngine if line==\"\" else line)\n",
    "    \n",
    "def pprint(dfs:DataFrames, rows = 10, title=None):\n",
    "    if title is not None:\n",
    "        print(title)\n",
    "    for df in dfs.values():\n",
    "        pdf= PandasDataFrame(df.head(rows), df.schema)\n",
    "        display(pdf.native)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Now I start to explore the data\n",
    "Now I want to take a peek at the example datasets and also try to test the magic and `pprint`. The following sql will run on `NativeExecutionEngine` because no engine specified"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "confirmed = LOAD CSV \"../data/confirmed.csv\"(header=true)\n",
    "OUTPUT USING pprint(rows=20)\n",
    "death = LOAD CSV \"../data/death.csv\"(header=true)\n",
    "OUTPUT USING pprint"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The table is very fat, each date is a column. It's not straightforward and not easy to do data analysis on such data structure. So I need to pivot the table so each date becomes a row. In pandas and Spark, they have their own ways to pivot table, neither is simple, plus I notice additional steps is needed to convert the column name to a date, so a great idea is to write a transformer to do that as a whole, so this logic becomes platform and scale agnostic.\n",
    "\n",
    "I have many options to write a transformer, but it seems implementing the interface is the best way because\n",
    "\n",
    "* the output schema is dependent on parameters, so schema hint is not an option\n",
    "* for each transform, it needs to reshape data according the schema, this preprocess is partition agnostic, so if we put that into `on_init`, it will run only once for each physical partition."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class Pivot(Transformer):\n",
    "    def get_output_schema(self, df):\n",
    "        fields = [f for f in df.schema.fields if not f.name.startswith(\"_\")]\n",
    "        col = self.params.get_or_throw(\"col\",str)\n",
    "        return Schema(fields)+f\"date:datetime,{col}:int\"\n",
    "    \n",
    "    def on_init(self, df):\n",
    "        self.value_col = self.params.get_or_throw(\"col\",str)\n",
    "        self.cols = [f for f in df.schema.names if not f.startswith(\"_\")]\n",
    "        self.date_cols = {f:datetime.strptime(f[1:], '%m_%d_%y') \n",
    "                          for f in df.schema.names if f.startswith(\"_\")}\n",
    "        \n",
    "    def transform(self, df):\n",
    "        def get_rows():\n",
    "            for row in df.as_dict_iterable():\n",
    "                res = [row[k] for k in self.cols]\n",
    "                for k,v in self.date_cols.items():\n",
    "                    yield res + [v,int(row[k])]\n",
    "        return IterableDataFrame(get_rows(), self.output_schema)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the above `transform` code, I choose to consume the input as iterable and use `IterableDataFrame` as the output, so it will minimize the memory usage. Think about that, if the df is large, without treating it in the pure streaming way, the original data and pivot table can use large amount of memory, and it is unnecessary.\n",
    "\n",
    "Now, I want to test this transformer locally. So I use `NativeExecutionEngine` to run this. Before transforming, I use a `SELECT LIMIT` to make the input data really small, so it can run quickly on native python.\n",
    "\n",
    "**It's very important to have this validation step**\n",
    "\n",
    "* Make sure you add a filtering statement to make the input data small because it's to test correctness not scalability.\n",
    "* Running on `NativeExecutionEngine` is extremely fast to find errors, and more importantly, all errors will be more explicit than running on cluster. Plus, at this step, if you see any errors, you can add `print` inside the transformer code to help debug, they will all display.\n",
    "* Consider using mock data as the input as well, Fugue SQL is very unit-testable, you should consider making this part one of your unittests (with mock input data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "LOAD CSV \"../data/confirmed.csv\"(header=true)\n",
    "SELECT * WHERE iso3 = 'USA' LIMIT 10\n",
    "confirmed = TRANSFORM USING Pivot(col=\"confirmed\")\n",
    "OUTPUT USING pprint\n",
    " \n",
    "LOAD CSV \"../data/death.csv\"(header=true)\n",
    "SELECT * WHERE iso3 = 'USA' LIMIT 10\n",
    "death = TRANSFORM USING Pivot(col=\"death\")\n",
    "OUTPUT USING pprint"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Bring it to Spark!\n",
    "\n",
    "It seems it works well, now let's start using `SparkExecutionEngine` to make it run on Spark. I need to join the 2 dataframes so each row will have `confirmed` and `death` because I always want to use them together in later steps.\n",
    "\n",
    "So here, I use a `JOIN` statement. Notice I `PERSIST` the select output because I will both `pprint` and `SAVE` the same output, persisting it will avoid rerun in the execution plan. Instead of adding persist explicitly, here is an [alternative solution](useful_config.ipynb#Auto-Persist)\n",
    "\n",
    "Also, saving intermediate data into a persistent storage is often a good idea for exploration. It's like a checkpoint."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql SparkExecutionEngine\n",
    "LOAD CSV \"../data/confirmed.csv\"(header=true)\n",
    "SELECT * WHERE iso3 = 'USA'\n",
    "confirmed = TRANSFORM USING Pivot(col=\"confirmed\")\n",
    " \n",
    "LOAD CSV \"../data/death.csv\"(header=true)\n",
    "SELECT * WHERE iso3 = 'USA'\n",
    "death = TRANSFORM USING Pivot(col=\"death\")\n",
    "\n",
    "SELECT \n",
    "    confirmed.Combined_Key AS key,\n",
    "    confirmed.Admin2 AS city,\n",
    "    confirmed.Province_State AS state,\n",
    "    Population AS population,\n",
    "    confirmed.date,\n",
    "    confirmed.confirmed,\n",
    "    death\n",
    "FROM confirmed INNER JOIN death\n",
    "    ON confirmed.Combined_Key = death.Combined_Key AND confirmed.date = death.date\n",
    "PERSIST\n",
    "    \n",
    "OUTPUT USING pprint\n",
    "SAVE OVERWRITE \"/tmp/covid19.parquet\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you can see I load the parquet back and do data analytics using SQL, and output using `pprint`. Here you should take a break and use differet `SELECT` to explore the data by yourself for a moment."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql SparkExecutionEngine\n",
    "data = LOAD \"/tmp/covid19.parquet\"\n",
    "\n",
    "SELECT DISTINCT city, state, population FROM data\n",
    "OUTPUT USING pprint\n",
    "\n",
    "SELECT state, date, SUM(confirmed) AS confirmed, SUM(death) AS death\n",
    "    FROM data GROUP BY state, date\n",
    "OUTPUT USING pprint"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Visualize it\n",
    "\n",
    "It will be useful to see the confirmed vs death chart for different partitions (I should be able to define the partition, either on city or state or something else). So again, my problem becomes, for a certain partition, I want to draw a chart, and in the end, it can be collected on driver to display in the notebook.\n",
    "\n",
    "This again is a typical transformation. For each partition, I draw the picture save to a common storage and return the path, so the driver can collect them and render together.\n",
    "\n",
    "And again, how should I write a transformer? It need to use the partition key information, so the most convenient way is still implementing the interface because this way, the partition values are easy to access.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "from uuid import uuid4\n",
    "import os\n",
    "from IPython.display import SVG\n",
    "\n",
    "class Draw(Transformer):\n",
    "    def get_output_schema(self, df):\n",
    "        return self.key_schema + \"path:str\"\n",
    "    \n",
    "    def transform(self, df):\n",
    "        root = self.params.get_or_throw(\"path\", str)\n",
    "        pdf = df.as_pandas()[[\"date\",\"confirmed\",\"death\"]]\n",
    "        pdf=pdf.set_index(\"date\")\n",
    "        fig = pdf.plot(title=self.cursor.key_value_dict.__repr__()).get_figure()\n",
    "        path = os.path.join(root, str(uuid4()))\n",
    "        fig.savefig(path, format=\"svg\")\n",
    "        res = self.cursor.key_value_array + [path]\n",
    "        return ArrayDataFrame([res], self.output_schema)\n",
    "\n",
    "\n",
    "def render_all(df:Iterable[Dict[str,Any]]) -> None:\n",
    "    for row in df:\n",
    "        display(SVG(filename=row[\"path\"]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's first test it using native python on a small data set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "data = LOAD \"/tmp/covid19.parquet\"\n",
    "SELECT * WHERE key = 'Adams, Mississippi, US'\n",
    "TRANSFORM PREPARTITION BY key USING Draw(path=\"/tmp\")\n",
    "OUTPUT USING render_all"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It seems wrong, the number should not jump between 0 and 200. AH, it's because we didn't sort the date. So we can sort the date inside the transformer before drawing, or we can `PRESORT` in Fugue SQL like below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "data = LOAD \"/tmp/covid19.parquet\"\n",
    "SELECT * WHERE key = 'Adams, Mississippi, US'\n",
    "TRANSFORM PREPARTITION BY key PRESORT date USING Draw(path=\"/tmp\")\n",
    "OUTPUT USING render_all"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Also you can see it rendered twice, it's because the `plot` inside the transformer is also rendered here because it's NativeExecutionEngine. This shouldn't happen when using `SparkExecutionEngine`, so we are fine with it.\n",
    "\n",
    "We only want to draw the charts on the 10 states with highest death numbers, so the following code, we firstly find the top 10 states, then we use `SEMI JOIN` to filter the dataset and then we aggregate and use the transformer to plot.\n",
    "\n",
    "Notice in the last `SELECT`, we can use inline `TRANSFORM`. Fugue SQL is more flexible than standard SQL."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql SparkExecutionEngine\n",
    "data = LOAD \"/tmp/covid19.parquet\"\n",
    "\n",
    "top10 = \n",
    "    SELECT state, SUM(death) AS death \n",
    "    FROM data GROUP BY state \n",
    "    ORDER BY death DESC LIMIT 10 PERSIST\n",
    "\n",
    "data = \n",
    "    SELECT * FROM data \n",
    "    LEFT SEMI JOIN top10 ON data.state = top10.state\n",
    "    WHERE date > '2020-04-01'\n",
    "\n",
    "SELECT state, date, SUM(confirmed) AS confirmed, SUM(death) AS death\n",
    "    GROUP BY state, date\n",
    "    \n",
    "# this step is to sort the output by death, the output of Draw doesn't have death, so we need join\n",
    "SELECT a.*, death FROM \n",
    "    (TRANSFORM PREPARTITION BY state PRESORT date USING Draw(path=\"/tmp\")) AS a\n",
    "    INNER JOIN top10 ON a.state = top10.state\n",
    "    ORDER BY death DESC\n",
    "    \n",
    "OUTPUT USING render_all"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Summary\n",
    "\n",
    "The way we solve this problem is very platform and scale agnostic. No matter you run it on native python with small data or on Spark with very large data, you may no longer need to change the code."
   ]
  }
 ],
 "metadata": {
  "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}