notebooks/var-benchmark/main.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# atoti Value-at-Risk benchmark\n",
    "\n",
    "This notebook is a benchmark template used to expose atoti performances over ClickHouse by reproducing a benchmark detailed in a [blog post by Altinity on calculating Value-at-Risk (VaR)](https://altinity.com/blog/clickhouse-vs-redshift-performance-for-fintech-risk-management). Check out the [full ](https://www.activeviam.com/blog/atoti-for-risk-management-aggregation-comparison-with-the-clickhouse-benchmark/)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "  \n",
    "<div style=\"text-align:center\"><a href=\"https://www.atoti.io/?utm_source=gallery&utm_content=var-benchmark\" target=\"_blank\" rel=\"noopener noreferrer\"><img src=\"https://data.atoti.io/notebooks/banners/discover.png\" alt=\"atoti\" /></a></div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import time\n",
    "\n",
    "import atoti"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "session = atoti.create_session(\n",
    "    \"RiskBenchmark\", config={\"java_options\": [\"-XX:MaxDirectMemorySize=12G\"]}\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load risk dataset\n",
    "dataset = session.read_parquet(\"dataset.parquet\", partitioning=\"hash32(str0)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cube = session.create_cube(dataset)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cube.schema"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Disable the aggregate cache\n",
    "cube.aggregates_cache.capacity = -1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define vector aggregation and value at risk\n",
    "h, m, l = cube.hierarchies, cube.measures, cube.levels\n",
    "\n",
    "m[\"PnlVector\"] = atoti.agg.sum(dataset[\"arrFloat\"])\n",
    "m[\"ValueAtRisk\"] = atoti.array.quantile(m[\"PnlVector\"], q=0.95)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a hierarchy to access vector elements\n",
    "cube.create_parameter_hierarchy_from_members(\"Scenarios\", list(range(0, 1000)))\n",
    "m[\"PnL at index\"] = m[\"PnlVector\"][l[\"Scenarios\"]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Creating hierarchies for numerical columns required for querying."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "h[\"int10\"] = [dataset[\"int10\"]]\n",
    "h[\"int11\"] = [dataset[\"int11\"]]\n",
    "h[\"int12\"] = [dataset[\"int12\"]]\n",
    "h[\"int13\"] = [dataset[\"int13\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "atoti": {
     "height": 688,
     "widget": {
      "mapping": {
       "columns": [
        "ALL_MEASURES"
       ],
       "measures": [
        "[Measures].[PnlVector]",
        "[Measures].[ValueAtRisk]"
       ],
       "rows": [
        "[Dataset].[dttime1].[dttime1]",
        "[Dataset].[dttime11].[dttime11]"
       ]
      },
      "query": {
       "mdx": "SELECT NON EMPTY Crossjoin(Hierarchize(Descendants({[Dataset].[dttime1].[AllMember]}, 1, SELF_AND_BEFORE)), Hierarchize(Descendants({[Dataset].[dttime11].[AllMember]}, 1, SELF_AND_BEFORE))) ON ROWS, NON EMPTY {[Measures].[PnlVector], [Measures].[ValueAtRisk]} ON COLUMNS FROM [Dataset]",
       "updateMode": "once"
      },
      "serverKey": "default",
      "widgetKey": "pivot-table"
     }
    }
   },
   "outputs": [],
   "source": [
    "session.link()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Number of facts in the cube\n",
    "cube.query(m[\"contributors.COUNT\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Query Benchmark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def Q1():\n",
    "    return cube.query(m[\"ValueAtRisk\"], levels=[l[\"str0\"]], mode=\"raw\")\n",
    "\n",
    "\n",
    "def Q2():\n",
    "    return cube.query(\n",
    "        m[\"ValueAtRisk\"],\n",
    "        levels=[\n",
    "            l[\"str0\"],\n",
    "            l[\"str1\"],\n",
    "            l[\"int10\"],\n",
    "            l[\"int11\"],\n",
    "            l[\"dttime10\"],\n",
    "            l[\"dttime11\"],\n",
    "        ],\n",
    "        mode=\"raw\",\n",
    "    )\n",
    "\n",
    "\n",
    "def Q3():\n",
    "    return cube.query(\n",
    "        m[\"ValueAtRisk\"],\n",
    "        levels=[\n",
    "            l[\"str0\"],\n",
    "            l[\"str1\"],\n",
    "            l[\"str2\"],\n",
    "            l[\"str3\"],\n",
    "            l[\"int10\"],\n",
    "            l[\"int11\"],\n",
    "            l[\"int12\"],\n",
    "            l[\"int13\"],\n",
    "            l[\"dttime10\"],\n",
    "            l[\"dttime11\"],\n",
    "            l[\"dttime12\"],\n",
    "            l[\"dttime13\"],\n",
    "        ],\n",
    "        mode=\"raw\",\n",
    "    )\n",
    "\n",
    "\n",
    "def Q4():\n",
    "    return cube.query(\n",
    "        m[\"PnL at index\"],\n",
    "        levels=[l[\"str0\"], l[\"Scenarios\"]],\n",
    "        condition=(l[\"str1\"] == \"KzORBHFRuFFOQm\"),\n",
    "        mode=\"raw\",\n",
    "    )\n",
    "\n",
    "\n",
    "# Iterations\n",
    "iterations = 10\n",
    "\n",
    "\n",
    "def benchmark(name, task):\n",
    "    # Warmup\n",
    "    for w in range(10):\n",
    "        task()\n",
    "\n",
    "    start = time.time()\n",
    "    for i in range(iterations):\n",
    "        task()\n",
    "    elapsed = time.time() - start\n",
    "\n",
    "    print(f\"{name} average query time {round(1000*elapsed/iterations)} ms\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "benchmark(\"Q1\", Q1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "benchmark(\"Q2\", Q2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "benchmark(\"Q3\", Q3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "benchmark(\"Q4\", Q4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Launch queries manually"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "Q1()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "Q2()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "Q3()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "Q4()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "  \n",
    "<div style=\"text-align:center\"><a href=\"https://www.atoti.io/?utm_source=gallery&utm_content=var-benchmark\" target=\"_blank\" rel=\"noopener noreferrer\"><img src=\"https://data.atoti.io/notebooks/banners/discover-try.png\" alt=\"atoti\" /></a></div>"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}