analyze-reqs.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f51ed320",
   "metadata": {},
   "outputs": [],
   "source": [
    "from utz import *\n",
    "from humanize import naturalsize"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1e4921bf",
   "metadata": {},
   "source": [
    "[Papermill](https://papermill.readthedocs.io/en/latest/) parameters:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4f4bf3fd",
   "metadata": {
    "tags": [
     "parameters"
    ]
   },
   "outputs": [],
   "source": [
    "name = 'fetch-1'\n",
    "out_fmt = 'csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "82556d7e",
   "metadata": {},
   "outputs": [],
   "source": [
    "def path(basename):\n",
    "    return os.path.join(name, basename)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a4e67631",
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(path('query.sql'), 'r') as f:\n",
    "    query = f.read().rstrip('\\n')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8ccef44d",
   "metadata": {},
   "outputs": [],
   "source": [
    "makedirs(name, exist_ok=True)\n",
    "def save(df, df_name, out_fmt=out_fmt):\n",
    "    if out_fmt == 'csv':\n",
    "        df.to_csv(path(f'{df_name}.csv'))\n",
    "    elif out_fmt in [ 'pqt', 'parquet' ]:\n",
    "        df.to_parquet(path(f'{df_name}.parquet'))\n",
    "    else:\n",
    "        raise ValueError(f'Unrecognize out_fmt: {out_fmt}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f7c51124",
   "metadata": {},
   "outputs": [],
   "source": [
    "har_path = f'{name}.har'\n",
    "har = read_json(har_path)\n",
    "har"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ec342373",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "entries = DF(har.loc['entries', 'log'])\n",
    "entries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9d98e23a",
   "metadata": {},
   "outputs": [],
   "source": [
    "req = pd.json_normalize(entries.request)\n",
    "res = pd.json_normalize(entries.response)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8aaec607",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "headers = req.headers.apply(lambda headers: { h['name']: h['value'] for h in headers }).apply(Series)\n",
    "headers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "904db8b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "ranges = headers.Range.dropna()\n",
    "ranges = ranges.str.extract(r'bytes=(?P<start>\\d+)-(?P<last>\\d+)?')\n",
    "starts = ranges.start.astype(int)\n",
    "ends = ranges['last'].apply(lambda last: nan if isna(last) else int(last) + 1).rename('end')\n",
    "ranges = sxs(starts, ends)\n",
    "ranges"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0f4909da",
   "metadata": {},
   "outputs": [],
   "source": [
    "suffix_range_mask = ~ranges.start.isna() & ranges.end.isna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b331e5b6",
   "metadata": {},
   "outputs": [],
   "source": [
    "e1 = sxs(req.method, req.url, res['content.size'].rename('size'), ranges)\n",
    "e1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "77a69c7b",
   "metadata": {},
   "outputs": [],
   "source": [
    "d1 = e1[e1.url.str.endswith('.duckdb')]\n",
    "d1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e60e0354",
   "metadata": {},
   "outputs": [],
   "source": [
    "d1.method.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "89049bd7",
   "metadata": {},
   "outputs": [],
   "source": [
    "name_idx = d1.url.str.extract(r'.*/(?P<name>(?P<data_idx>\\de\\d).*)')\n",
    "indexed = name_idx['name'].str.contains('-idx').rename('indexed')\n",
    "name_idx = sxs(name_idx, indexed)\n",
    "names = name_idx['name']\n",
    "name_idxs = name_idx.drop_duplicates().set_index('name')\n",
    "nrows = name_idxs.data_idx.apply(lambda s: int(float(s))).rename('nrows')\n",
    "name_idxs = sxs(name_idxs, nrows).sort_values(['nrows', 'indexed'])\n",
    "name_idxs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d6e6c07e",
   "metadata": {},
   "outputs": [],
   "source": [
    "gets = sxs(d1, names)\n",
    "gets = gets[gets['size'] > 0]\n",
    "assert gets.method.unique().tolist() == ['GET']\n",
    "gets = gets[['name', 'size']]\n",
    "gets = (\n",
    "    gets\n",
    "    .merge(\n",
    "        ranges,\n",
    "        how='left',\n",
    "        left_index=True,\n",
    "        right_index=True,\n",
    "    )\n",
    "    .astype({ 'start': int, 'end': int })\n",
    "    [[ 'name', 'start', 'end', 'size', ]]\n",
    ")\n",
    "gets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e043474e",
   "metadata": {},
   "outputs": [],
   "source": [
    "assert (gets.start + gets['size'] == gets.end).all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0f22db2b",
   "metadata": {},
   "outputs": [],
   "source": [
    "save(gets, 'gets')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9a9c22df",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_stats(df):\n",
    "    return dict(\n",
    "        num=len(df),\n",
    "        max=max(df),\n",
    "        min=min(df),\n",
    "    )\n",
    "req_stats = gets.groupby('name')['size'].apply(get_stats)\n",
    "req_stats.index = req_stats.index.set_names('stat', level=1)\n",
    "req_stats = req_stats.reset_index(level=1).pivot(columns='stat', values='size')\n",
    "req_stats.columns.name = ''\n",
    "req_stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b10dbc26",
   "metadata": {},
   "outputs": [],
   "source": [
    "fetched = gets.groupby('name')['size'].sum().rename('fetched')\n",
    "fetched_iec = fetched.apply(naturalsize).rename('fetched_iec')\n",
    "fetched = sxs(fetched, fetched_iec)\n",
    "fetched = (\n",
    "    sxs(\n",
    "        fetched,\n",
    "        name_idxs,\n",
    "    )\n",
    "    .sort_values(['nrows', 'indexed'])\n",
    "    [fetched.columns]\n",
    ")\n",
    "fetched"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "75da22db",
   "metadata": {},
   "outputs": [],
   "source": [
    "import boto3\n",
    "s3 = boto3.client('s3')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "988d021b",
   "metadata": {},
   "outputs": [],
   "source": [
    "bkt = 'duckdb-wasm-test'\n",
    "def get_object_size(name):\n",
    "    res = s3.head_object(Bucket=bkt, Key=name)\n",
    "    return res['ContentLength']\n",
    "\n",
    "s3_size = fetched.index.to_series().apply(get_object_size).rename('size')\n",
    "s3_size_iec = s3_size.apply(naturalsize).rename('size_iec')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "34bae84c",
   "metadata": {},
   "outputs": [],
   "source": [
    "stats = sxs(fetched, s3_size, s3_size_iec, name_idxs[['nrows', 'indexed']]).merge(req_stats, how='left', left_index=True, right_index=True, validate='1:1')\n",
    "stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "feaff5dd",
   "metadata": {},
   "outputs": [],
   "source": [
    "save(stats, 'stats')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3887476c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import plotly.express as px\n",
    "from IPython.display import Image\n",
    "W = 800\n",
    "H = W / 2\n",
    "dims = dict(width=W, height=H)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "db8f0af8",
   "metadata": {},
   "outputs": [],
   "source": [
    "fig = px.scatter(\n",
    "    stats,\n",
    "    x='nrows',\n",
    "    y='fetched',\n",
    "    log_x=True,\n",
    "    log_y=True,\n",
    "    color='indexed',\n",
    "    labels={\n",
    "        'fetched': \"Bytes fetched\",\n",
    "        'nrows': \"Dataset rows\",\n",
    "        'indexed': \"Indexed?\",\n",
    "    },\n",
    ").update_layout(\n",
    "    title=dict(\n",
    "        text=f'Data fetched: `{query}`',\n",
    "        x=0.5,\n",
    "    ),\n",
    "    plot_bgcolor='white',\n",
    ").update_xaxes(\n",
    "    gridcolor='#ccc',\n",
    ").update_yaxes(\n",
    "    gridcolor='#ccc',\n",
    ").update_traces(marker_size=12)\n",
    "fig.write_image(path('fetched.png'), **dims)\n",
    "Image(fig.to_image(**dims))"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "duckdb-wasm-test-3.11.6",
   "language": "python",
   "name": "duckdb-wasm-test-3.11.6"
  },
  "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.11.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}