content/arrow_and_duckdb_example.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "2e750d4f-68dd-407a-9560-1d4ff4e7e382",
   "metadata": {},
   "source": [
    "# Arrow and DuckDB Example Notebook\n",
    "\n",
    "Demonstrating using Apache Arrow with the DuckDB client via SQL.\n",
    "\n",
    "## References\n",
    "\n",
    "- [Apache Arrow](https://arrow.apache.org/docs/index.html)\n",
    "- [DuckDB](https://duckdb.org/docs/)\n",
    "- [SQL](https://en.wikipedia.org/wiki/SQL)\n",
    "- [Iris Dataset](https://archive.ics.uci.edu/ml/datasets/Iris)\n",
    "    - Creator: R.A. Fisher\n",
    "    - Donor: Michael Marshall (MARSHALL%PLU '@' io.arc.nasa.gov)\n",
    "    - Host: Dua, D. and Graff, C. (2019). UCI Machine Learning Repository [http://archive.ics.uci.edu/ml]. Irvine, CA: University of California, School of Information and Computer Science."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "ef7e0554-6c68-4522-83eb-4f4271ae0eeb",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pathlib\n",
    "import urllib\n",
    "\n",
    "import duckdb\n",
    "import pyarrow.csv as csv\n",
    "import pyarrow.parquet as parquet\n",
    "\n",
    "data_link = \"https://archive.ics.uci.edu/ml/machine-learning-databases/iris/iris.data\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "0f69ca2a-024e-4f1b-9531-f1a64bdcb9f0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pyarrow.Table\n",
       "sepal length: float\n",
       "sepal width: float\n",
       "petal length: float\n",
       "petal width: float\n",
       "class: string\n",
       "----\n",
       "sepal length: [[5.1,4.9,4.7,4.6,5,...,6.7,6.3,6.5,6.2,5.9]]\n",
       "sepal width: [[3.5,3,3.2,3.1,3.6,...,3,2.5,3,3.4,3]]\n",
       "petal length: [[1.4,1.4,1.3,1.5,1.4,...,5.2,5,5.2,5.4,5.1]]\n",
       "petal width: [[0.2,0.2,0.2,0.2,0.2,...,2.3,1.9,2,2.3,1.8]]\n",
       "class: [[\"Iris-setosa\",\"Iris-setosa\",\"Iris-setosa\",\"Iris-setosa\",\"Iris-setosa\",...,\"Iris-virginica\",\"Iris-virginica\",\"Iris-virginica\",\"Iris-virginica\",\"Iris-virginica\"]]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# use duckdb to select remote iris CSV data\n",
    "iris_table = duckdb.connect().execute(\n",
    "    f\"\"\"\n",
    "    /* install and load httpfs */\n",
    "    INSTALL httpfs;\n",
    "    LOAD httpfs;\n",
    "    /* run a select on the dataset */\n",
    "    SELECT * from read_csv(\n",
    "        '{data_link}',\n",
    "        delim=',',\n",
    "        header=False,\n",
    "        columns={{\n",
    "            'sepal length':'FLOAT',\n",
    "            'sepal width': 'FLOAT',\n",
    "            'petal length':'FLOAT',\n",
    "            'petal width': 'FLOAT',\n",
    "            'class': 'VARCHAR'\n",
    "        }}\n",
    "    )\n",
    "    \"\"\"\n",
    ").arrow()\n",
    "iris_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "d148d185-971b-4900-8ac3-0c25b0eb67ce",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[PosixPath('iris.parquet')]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# write a parquet file from iris data\n",
    "parquet.write_table(\n",
    "    table=iris_table,\n",
    "    where=\"iris.parquet\",\n",
    ")\n",
    "list(pathlib.Path(\".\").glob(\"*.parquet\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "a16e737c-0e6b-41d7-86bd-9c8b3a176f26",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pyarrow.Table\n",
       "file_row_number: int64\n",
       "sepal length: float\n",
       "sepal width: float\n",
       "----\n",
       "file_row_number: [[0,1,2,3,4,...,145,146,147,148,149]]\n",
       "sepal length: [[5.1,4.9,4.7,4.6,5,...,6.7,6.3,6.5,6.2,5.9]]\n",
       "sepal width: [[3.5,3,3.2,3.1,3.6,...,3,2.5,3,3.4,3]]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# use duckdb to select sepal data with row number as a pyarrow table\n",
    "sepal_table = duckdb.connect().execute(\n",
    "    f\"\"\"\n",
    "    SELECT file_row_number, \"sepal length\", \"sepal width\"\n",
    "    FROM read_parquet('iris.parquet', file_row_number=true);\n",
    "    \"\"\"\n",
    ").arrow()\n",
    "sepal_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "c980b15e-9e18-4921-8276-6163b97d91e4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pyarrow.Table\n",
       "file_row_number: int64\n",
       "petal length: float\n",
       "petal width: float\n",
       "----\n",
       "file_row_number: [[0,1,2,3,4,...,145,146,147,148,149]]\n",
       "petal length: [[1.4,1.4,1.3,1.5,1.4,...,5.2,5,5.2,5.4,5.1]]\n",
       "petal width: [[0.2,0.2,0.2,0.2,0.2,...,2.3,1.9,2,2.3,1.8]]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# use duckdb to select petal data with row number as a pyarrow table\n",
    "petal_table = duckdb.connect().execute(\n",
    "    f\"\"\"\n",
    "    SELECT file_row_number, \"petal length\", \"petal width\"\n",
    "    FROM read_parquet('iris.parquet', file_row_number=true);\n",
    "    \"\"\"\n",
    ").arrow()\n",
    "petal_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "2aa72e04-fc6b-4453-b290-4434e98f3499",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pyarrow.Table\n",
       "file_row_number: int64\n",
       "class: string\n",
       "----\n",
       "file_row_number: [[0,1,2,3,4,...,145,146,147,148,149]]\n",
       "class: [[\"Iris-setosa\",\"Iris-setosa\",\"Iris-setosa\",\"Iris-setosa\",\"Iris-setosa\",...,\"Iris-virginica\",\"Iris-virginica\",\"Iris-virginica\",\"Iris-virginica\",\"Iris-virginica\"]]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# use duckdb to select iris class data with row number as a pyarrow table\n",
    "class_table = duckdb.connect().execute(\n",
    "    f\"\"\"\n",
    "    SELECT file_row_number, class\n",
    "    FROM read_parquet('iris.parquet', file_row_number=true);\n",
    "    \"\"\"\n",
    ").arrow()\n",
    "class_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "cf1f7274-ac15-4f50-b8f7-a37a87e1f766",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pyarrow.Table\n",
       "sepal length: float\n",
       "sepal width: float\n",
       "petal length: float\n",
       "petal width: float\n",
       "class: string\n",
       "----\n",
       "sepal length: [[5.1,4.9,4.7,4.6,5,...,6.7,6.3,6.5,6.2,5.9]]\n",
       "sepal width: [[3.5,3,3.2,3.1,3.6,...,3,2.5,3,3.4,3]]\n",
       "petal length: [[1.4,1.4,1.3,1.5,1.4,...,5.2,5,5.2,5.4,5.1]]\n",
       "petal width: [[0.2,0.2,0.2,0.2,0.2,...,2.3,1.9,2,2.3,1.8]]\n",
       "class: [[\"Iris-setosa\",\"Iris-setosa\",\"Iris-setosa\",\"Iris-setosa\",\"Iris-setosa\",...,\"Iris-virginica\",\"Iris-virginica\",\"Iris-virginica\",\"Iris-virginica\",\"Iris-virginica\"]]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# use duckdb to join pyarrow table data using the python variable names as tables\n",
    "joined_result = duckdb.connect().execute(\n",
    "    f\"\"\"\n",
    "    SELECT \n",
    "        sepal_table.\"sepal length\",\n",
    "        sepal_table.\"sepal width\",\n",
    "        petal_table.\"petal length\",\n",
    "        petal_table.\"petal width\",\n",
    "        class_table.class\n",
    "    FROM sepal_table\n",
    "    LEFT JOIN petal_table ON\n",
    "        petal_table.file_row_number = sepal_table.file_row_number\n",
    "    LEFT JOIN class_table ON\n",
    "        class_table.file_row_number = sepal_table.file_row_number\n",
    "    \"\"\"\n",
    ").arrow()\n",
    "joined_result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "5f4ba3cb-92e4-40a3-b3f8-e9bc92d9b892",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# double check that our joined result is the same as the original parquet file\n",
    "joined_result.equals(parquet.read_table(\"iris.parquet\"))"
   ]
  }
 ],
 "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.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}