{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "e6871bca",
   "metadata": {},
   "source": [
    "# Exploratory analysis with Opteryx Core\n",
    "\n",
    "Query a Parquet dataset with SQL inside a notebook, take a fast first look at it, chart a summary with pandas, and iterate rows in Python.\n",
    "\n",
    "This notebook accompanies [Exploratory Analysis in Notebooks](https://docs.opteryx.app/docs/guides/exploratory-analysis) on docs.opteryx.app. It runs in Opteryx Core, in the notebook's own process. No account needed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3b0af33e",
   "metadata": {},
   "outputs": [],
   "source": [
    "%pip install -q opteryx-core pandas pyarrow matplotlib"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d9162d1a",
   "metadata": {},
   "source": [
    "## Get the data\n",
    "\n",
    "The guide queries a `transactions` dataset you won't have, so this notebook uses `space_missions.parquet` instead: 4,630 orbital launch attempts since Sputnik. Its columns stand in for the guide's:\n",
    "\n",
    "| guide | here |\n",
    "|---|---|\n",
    "| `data.transactions` | `data.missions` |\n",
    "| `category` | `Company` |\n",
    "| `amount` | `Price` (launch cost, millions of USD; often missing) |\n",
    "| `ordered_at` | `Lauched_at` (spelled that way in the file) |\n",
    "| `status` | `Mission_Status` |\n",
    "\n",
    "A dataset is a folder, and `data.missions` reads from `./data/missions/`, so the file goes there."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cb61d8bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import urllib.request\n",
    "\n",
    "os.makedirs(\"data/missions\", exist_ok=True)\n",
    "urllib.request.urlretrieve(\n",
    "    \"https://raw.githubusercontent.com/mabel-dev/opteryx-core/main/testdata/missions/space_missions.parquet\",\n",
    "    \"data/missions/space_missions.parquet\",\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "892738a0",
   "metadata": {},
   "source": [
    "## Setup\n",
    "\n",
    "Register the `data` workspace with the `DiskConnector` and open a session."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "70f56425",
   "metadata": {},
   "outputs": [],
   "source": [
    "import opteryx\n",
    "from opteryx.connectors import DiskConnector\n",
    "\n",
    "opteryx.register_workspace(\"data\", DiskConnector)\n",
    "session = opteryx.session()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "44107519",
   "metadata": {},
   "source": [
    "## Querying without loading everything\n",
    "\n",
    "Aggregate in SQL and only pull back the summary. The scan, filter and grouping run inside Opteryx; only one row per company reaches Python.\n",
    "\n",
    "Compare a timestamp column with a `CAST(... AS TIMESTAMP)`, not a bare string."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fc54a3d7",
   "metadata": {},
   "outputs": [],
   "source": [
    "for morsel in session.execute_to_morsels(\"\"\"\n",
    "    SELECT\n",
    "        Company,\n",
    "        COUNT(*)    AS n,\n",
    "        AVG(Price)  AS avg_price\n",
    "      FROM data.missions\n",
    "     WHERE Lauched_at >= CAST('2000-01-01' AS TIMESTAMP)\n",
    "     GROUP BY Company\n",
    "     ORDER BY n DESC\n",
    "\"\"\"):\n",
    "    print(morsel.to_arrow())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a158137d",
   "metadata": {},
   "source": [
    "## A fast first look\n",
    "\n",
    "A sample of rows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0b4fa2a6",
   "metadata": {},
   "outputs": [],
   "source": [
    "for morsel in session.execute_to_morsels(\"SELECT * FROM data.missions LIMIT 20\"):\n",
    "    print(morsel.to_arrow())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "264bb9cb",
   "metadata": {},
   "source": [
    "The row count. For a small result, concatenate the morsels into one Arrow table and show it as pandas:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "526b3b52",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pyarrow\n",
    "\n",
    "morsels = session.execute_to_morsels(\"SELECT COUNT(*) FROM data.missions\")\n",
    "pyarrow.concat_tables(m.to_arrow() for m in morsels).to_pandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f3afcddf",
   "metadata": {},
   "source": [
    "Distinct values and their frequencies:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1ded85c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "morsels = session.execute_to_morsels(\"\"\"\n",
    "    SELECT Mission_Status, COUNT(*) AS n\n",
    "      FROM data.missions\n",
    "     GROUP BY Mission_Status\n",
    "     ORDER BY n DESC\n",
    "\"\"\")\n",
    "pyarrow.concat_tables(m.to_arrow() for m in morsels).to_pandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "81ae201b",
   "metadata": {},
   "source": [
    "## Straight to a chart\n",
    "\n",
    "Group in SQL, convert the small result to pandas, and plot it. This groups by year rather than the guide's month, since launch prices are sparse before 2000."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d338a094",
   "metadata": {},
   "outputs": [],
   "source": [
    "morsels = session.execute_to_morsels(\"\"\"\n",
    "    SELECT\n",
    "        FORMAT_TIMESTAMP('%Y', Lauched_at) AS year,\n",
    "        SUM(Price)                         AS spend\n",
    "      FROM data.missions\n",
    "     WHERE Lauched_at >= CAST('2000-01-01' AS TIMESTAMP)\n",
    "     GROUP BY year\n",
    "     ORDER BY year\n",
    "\"\"\")\n",
    "yearly = pyarrow.concat_tables(morsel.to_arrow() for morsel in morsels).to_pandas()\n",
    "\n",
    "yearly.plot(x=\"year\", y=\"spend\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f48a4472",
   "metadata": {},
   "source": [
    "## Working row by row\n",
    "\n",
    "Iterate a morsel directly when you need Python logic per row. Each row is a named tuple."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "95b2c061",
   "metadata": {},
   "outputs": [],
   "source": [
    "for morsel in session.execute_to_morsels(\"SELECT name, mass, gravity FROM $planets\"):\n",
    "    for row in morsel:\n",
    "        print(row.name, row.mass, row.gravity)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d45b0edb",
   "metadata": {},
   "source": [
    "## Try it against sample data\n",
    "\n",
    "`$planets` is built in and touches no workspace or connector, so it tells you the engine works independently of your data access."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d14d6a92",
   "metadata": {},
   "outputs": [],
   "source": [
    "for morsel in session.execute_to_morsels(\n",
    "    \"SELECT name, mass, gravity FROM $planets ORDER BY mass DESC\"\n",
    "):\n",
    "    print(morsel.to_arrow())"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}