duckdb_from_cloud_geoparquet_benchmark.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "cb142fe8-3606-4e91-921c-7132d92a9cca",
   "metadata": {},
   "source": [
    "# DuckDB benchmark - Azure vs AWS \n",
    "\n",
    "Benchmarking the wall-clock time to find the nearest point in a 12 mln row GeoParquet dataset that consists of several partitions"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5f9a0576-bfc2-44ba-a8e6-e216e50c98e6",
   "metadata": {},
   "source": [
    "## Azure"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "b448cf7f-f018-48ba-821f-ab49f0ec2f46",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-02-10T15:17:24.854613Z",
     "iopub.status.busy": "2024-02-10T15:17:24.853893Z",
     "iopub.status.idle": "2024-02-10T15:19:04.222330Z",
     "shell.execute_reply": "2024-02-10T15:19:04.222103Z",
     "shell.execute_reply.started": "2024-02-10T15:17:24.854580Z"
    }
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "00f6c14404f34904a318128ff57ff6fd",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, layout=Layout(width='auto'), style=ProgressStyle(bar_color='black'))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "            tr_name       lon        lat    bearing  utm_crs  coastline_name  \\\n",
      "0  cl31596tr1519446 -4.317812  52.216587  326.57959    32630           31596   \n",
      "\n",
      "                                            geometry  __null_dask_index__  \\\n",
      "0  [1, 2, 0, 0, 0, 2, 0, 0, 0, 85, 239, 221, 110,...              3726912   \n",
      "\n",
      "  quadkey     distance  \n",
      "0   qk031  4105.767123  \n",
      "CPU times: user 37.9 s, sys: 2.93 s, total: 40.8 s\n",
      "Wall time: 1min 39s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "import os\n",
    "import pathlib\n",
    "\n",
    "import dotenv\n",
    "import duckdb\n",
    "import geopandas as gpd\n",
    "\n",
    "dotenv.load_dotenv(override=True)\n",
    "azure_connection_string = os.getenv(\"AZURE_CONNECTION_STRING\")\n",
    "\n",
    "con = duckdb.connect(database=\":memory:\", read_only=False)\n",
    "\n",
    "# Install and load the spatial extension\n",
    "con.execute(\"INSTALL spatial;\")\n",
    "con.execute(\"LOAD spatial;\")\n",
    "con.execute(\"LOAD azure;\")\n",
    "con.execute(f\"SET azure_storage_connection_string = '{azure_connection_string}';\")\n",
    "\n",
    "# # Path to your Parquet file(s)\n",
    "transects_href = \"azure://transects/gcts-2000m.parquet/**/*.parquet\"\n",
    "\n",
    "# Specify your point of interest (longitude, latitude)\n",
    "point_x, point_y = -4.29, 52.19  # Example coordinates\n",
    "point_wkt = (\n",
    "    gpd.GeoSeries.from_xy([point_x], [point_y], crs=4326).to_crs(3857).to_wkt().item()\n",
    ")\n",
    "your_radius = 10000.0  # Ensure radius is treated as DOUBLE\n",
    "\n",
    "# Adjust this query with your actual geometry column name and Parquet file path\n",
    "query = f\"\"\"\n",
    "SELECT *, ST_Distance(ST_GeomFromWKB(geometry), ST_GeomFromText('{point_wkt}')) AS distance\n",
    "FROM '{transects_href}'\n",
    "WHERE ST_DWithin(ST_GeomFromWKB(geometry), ST_GeomFromText('{point_wkt}'), {your_radius})\n",
    "ORDER BY distance\n",
    "LIMIT 1;\n",
    "\"\"\"\n",
    "\n",
    "# Execute the query and fetch the result as a DataFrame\n",
    "nearest_result = con.execute(query).fetchdf()\n",
    "\n",
    "# Display the nearest geometry and its distance from the input point\n",
    "print(nearest_result)\n",
    "\n",
    "# Close the DuckDB connection\n",
    "con.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "565a176d-b9e4-4cbf-bcb7-b2be29bf2564",
   "metadata": {},
   "source": [
    "## AWS S3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "516a1a53-18f6-4a93-87f7-7bc98454ad5b",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-02-10T15:38:45.688527Z",
     "iopub.status.busy": "2024-02-10T15:38:45.675483Z",
     "iopub.status.idle": "2024-02-10T15:40:27.687946Z",
     "shell.execute_reply": "2024-02-10T15:40:27.687685Z",
     "shell.execute_reply.started": "2024-02-10T15:38:45.688485Z"
    }
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "de4386d28b2f4fcd8df718980dca1ff6",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, layout=Layout(width='auto'), style=ProgressStyle(bar_color='black'))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "            tr_name       lon        lat    bearing  utm_crs  coastline_name  \\\n",
      "0  cl31596tr1519446 -4.317812  52.216587  326.57959    32630           31596   \n",
      "\n",
      "                                            geometry  __null_dask_index__  \\\n",
      "0  [1, 2, 0, 0, 0, 2, 0, 0, 0, 85, 239, 221, 110,...              3726912   \n",
      "\n",
      "  quadkey     distance  \n",
      "0   qk031  4105.767123  \n",
      "CPU times: user 35.2 s, sys: 3.53 s, total: 38.7 s\n",
      "Wall time: 1min 41s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "import os\n",
    "import pathlib\n",
    "\n",
    "import dotenv\n",
    "import duckdb\n",
    "import geopandas as gpd\n",
    "\n",
    "dotenv.load_dotenv(override=True)\n",
    "aws_access_key_id = os.getenv(\"AWS_ACCESS_KEY_ID\")\n",
    "aws_secret_access_key = os.getenv(\"AWS_SECRET_ACCESS_KEY\")\n",
    "\n",
    "con = duckdb.connect(database=\":memory:\", read_only=False)\n",
    "\n",
    "\n",
    "# Install and load the spatial extension\n",
    "con.execute(\"LOAD spatial;\")\n",
    "con.execute(\"LOAD httpfs;\")\n",
    "con.execute(f\"SET s3_region = 'eu-west-2';\")\n",
    "con.execute(f\"SET s3_access_key_id = '{aws_access_key_id}';\")\n",
    "con.execute(f\"SET s3_secret_access_key = '{aws_secret_access_key}';\")\n",
    "\n",
    "# # Path to your Parquet file(s)\n",
    "transects_href = \"s3://coastmonitor/gcts-2000m.parquet/**/*.parquet\"\n",
    "\n",
    "# Specify your point of interest (longitude, latitude)\n",
    "point_x, point_y = -4.29, 52.19  # Example coordinates\n",
    "point_wkt = (\n",
    "    gpd.GeoSeries.from_xy([point_x], [point_y], crs=4326).to_crs(3857).to_wkt().item()\n",
    ")\n",
    "your_radius = 10000.0  # Ensure radius is treated as DOUBLE\n",
    "\n",
    "# Adjust this query with your actual geometry column name and Parquet file path\n",
    "query = f\"\"\"\n",
    "SELECT *, ST_Distance(ST_GeomFromWKB(geometry), ST_GeomFromText('{point_wkt}')) AS distance\n",
    "FROM '{transects_href}'\n",
    "WHERE ST_DWithin(ST_GeomFromWKB(geometry), ST_GeomFromText('{point_wkt}'), {your_radius})\n",
    "ORDER BY distance\n",
    "LIMIT 1;\n",
    "\"\"\"\n",
    "\n",
    "# Execute the query and fetch the result as a DataFrame\n",
    "nearest_result = con.execute(query).fetchdf()\n",
    "\n",
    "# Display the nearest geometry and its distance from the input point\n",
    "print(nearest_result)\n",
    "\n",
    "# Close the DuckDB connection\n",
    "con.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e6389cb4-4b6f-45e1-988f-67e2b547748b",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:jl-full] *",
   "language": "python",
   "name": "conda-env-jl-full-py"
  },
  "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
}