make-dbs.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "7f763589",
   "metadata": {},
   "source": [
    "Given a \"large\" DataFrame:\n",
    "1. save various-sized prefixes as `.parquet`s in S3\n",
    "2. Convert them to `.duckdb` files (with and without a `UNIQUE INDEX`), also in S3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8f56e1e0",
   "metadata": {},
   "outputs": [],
   "source": [
    "from utz import *"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6552fe94",
   "metadata": {},
   "source": [
    "[Papermill](https://papermill.readthedocs.io/en/latest/) parameters:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f5d26b17",
   "metadata": {
    "tags": [
     "parameters"
    ]
   },
   "outputs": [],
   "source": [
    "bkt = 'duckdb-wasm-test'\n",
    "keys = [ '1e5', '2e5', '5e5', '1e6', '2e6', '4e6', '6e6', ]\n",
    "full_parquet_path = None  # default: s3://<bkt>/<last key>.parquet, e.g. s3://duckdb-wasm-test/6e6.parquet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9029e4f1",
   "metadata": {},
   "outputs": [],
   "source": [
    "pre = f's3://{bkt}'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "da5a34b7",
   "metadata": {},
   "outputs": [],
   "source": [
    "if full_parquet_path is None:\n",
    "    last = keys[-1]\n",
    "    full_parquet_path = f'{pre}/{last}.parquet'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0ba21bc4",
   "metadata": {},
   "source": [
    "## Load full DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d30aa4b3",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = read_parquet(full_parquet_path)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3f4c4e46",
   "metadata": {},
   "outputs": [],
   "source": [
    "def make_parquet(k):\n",
    "    \"\"\"Slice a prefix from `df`, write new Parquet to S3.\"\"\"\n",
    "    print(k)\n",
    "    n = int(float(k))\n",
    "    d = df.iloc[:n]\n",
    "    out_path = f'{pre}/{k}.parquet'\n",
    "    if out_path != full_parquet_path:\n",
    "        d.to_parquet(out_path) "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ee659690",
   "metadata": {},
   "source": [
    "## For each \"key\" in `keys`, write a `.parquet` file to S3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "423c967e",
   "metadata": {},
   "outputs": [],
   "source": [
    "for k in keys:\n",
    "    make_parquet(k)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "649eba49",
   "metadata": {},
   "outputs": [],
   "source": [
    "import boto3\n",
    "s3 = boto3.client('s3')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "87da08e3",
   "metadata": {},
   "outputs": [],
   "source": [
    "def make_db(idx, index=False):\n",
    "    \"\"\"Make a `.duckdb` file from `.parquet` (optionally including a `UNIQUE INDEX` on `id`).\"\"\"\n",
    "    out_name = f'{idx}-idx' if index else f'{idx}'\n",
    "    db_path = f'{out_name}.duckdb'\n",
    "    pqt_path = f'{idx}.parquet'\n",
    "    with duckdb.connect(db_path) as con:\n",
    "        con.sql(f\"CREATE TABLE crashes AS SELECT * FROM '{pre}/{pqt_path}';\")\n",
    "        if index:\n",
    "            con.sql(f\"CREATE UNIQUE INDEX crashes_id_idx ON crashes (id);\")\n",
    "    with open(db_path, 'rb') as f:\n",
    "        s3.put_object(Bucket=bkt, Key=db_path, Body=f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ebe16154",
   "metadata": {},
   "outputs": [],
   "source": [
    "def make_dbs(k):\n",
    "    \"\"\"Make two `.duckdb`s from a `.parquet` (one with index, one without).\"\"\"\n",
    "    make_db(k)\n",
    "    make_db(k, index=True)    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b94221e3",
   "metadata": {},
   "source": [
    "## Make indexed and unindexed `.duckdb`s for each `.parquet`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ef97c610",
   "metadata": {},
   "outputs": [],
   "source": [
    "for k in keys:\n",
    "    print(k)\n",
    "    make_dbs(k)"
   ]
  }
 ],
 "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
}