Hudi_Clustering_Spark_SQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "2f81e824",
   "metadata": {},
   "source": [
    "# Configurations: Spark-Hudi-S3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "96015f0a",
   "metadata": {},
   "outputs": [],
   "source": [
    "from typing import *\n",
    "\n",
    "from pyspark import Row\n",
    "from pyspark.sql import SparkSession\n",
    "from pyspark.sql.functions import col\n",
    "\n",
    "# Initialize Spark session\n",
    "spark = SparkSession.builder \\\n",
    "    .appName(\"Hudi Table\") \\\n",
    "    .config(\"spark.serializer\", \"org.apache.spark.serializer.KryoSerializer\") \\\n",
    "    .config(\"spark.jars.packages\", \"org.apache.hudi:hudi-spark3.4-bundle_2.12:0.14.0,org.apache.hadoop:hadoop-aws:3.2.4,com.amazonaws:aws-java-sdk:1.12.262\") \\\n",
    "    .config(\"spark.sql.catalog.spark_catalog\", \"org.apache.spark.sql.hudi.catalog.HoodieCatalog\") \\\n",
    "    .config(\"spark.sql.extensions\", \"org.apache.spark.sql.hudi.HoodieSparkSessionExtension\") \\\n",
    "    .config(\"spark.hadoop.fs.s3a.aws.credentials.provider\", \"com.amazonaws.auth.profile.ProfileCredentialsProvider\") \\\n",
    "    .config(\"spark.hadoop.fs.s3a.path.style.access\", \"true\") \\\n",
    "    .config(\"spark.hadoop.fs.s3a.impl\", \"org.apache.hadoop.fs.s3a.S3AFileSystem\") \\\n",
    "    .getOrCreate()\n",
    "\n",
    "print(\"Spark Running\")\n",
    "\n",
    "s3_path = \"s3a://my-bucket/dip\"\n",
    "\n",
    "# Access SparkContext\n",
    "sc = spark.sparkContext"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fc3ae3f8",
   "metadata": {},
   "source": [
    "# CREATE HUDI TABLE: Spark SQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "953be0f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "spark.sql(\n",
    "    \"\"\"CREATE TABLE customer_data \n",
    "        (InvoiceNo STRING, StockCode STRING, Description STRING, Quantity STRING, InvoiceDate STRING, UnitPrice STRING,\n",
    "        CustomerID STRING, Country STRING)\n",
    "       USING HUDI \n",
    "       LOCATION 's3a://my-bucket/dip/demo'\"\"\"\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5afa5966",
   "metadata": {},
   "source": [
    "# INGEST DATA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "2358dd9d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DataFrame[]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spark.sql(\n",
    "    \"\"\"CREATE OR REPLACE TEMPORARY VIEW cust_temp USING csv\n",
    "            OPTIONS (path \"online_retail.csv\", header true)\"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "6cdb5409",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "24/02/20 11:13:20 WARN AutoRecordKeyGenerationUtils$: Precombine field  will be ignored with auto record key generation enabled\n",
      "                                                                                \r"
     ]
    },
    {
     "data": {
      "text/plain": [
       "DataFrame[]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spark.sql(\"INSERT INTO customer_data SELECT * FROM cust_temp\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "455e7fb2",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count(1)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1083818</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count(1)\n",
       "0   1083818"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spark.sql(\"SELECT COUNT(*) FROM customer_data\").toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "282424f7",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>_hoodie_commit_time</th>\n",
       "      <th>_hoodie_commit_seqno</th>\n",
       "      <th>_hoodie_record_key</th>\n",
       "      <th>_hoodie_partition_path</th>\n",
       "      <th>_hoodie_file_name</th>\n",
       "      <th>InvoiceNo</th>\n",
       "      <th>StockCode</th>\n",
       "      <th>Description</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>InvoiceDate</th>\n",
       "      <th>UnitPrice</th>\n",
       "      <th>CustomerID</th>\n",
       "      <th>Country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20240212153945516</td>\n",
       "      <td>20240212153945516_0_0</td>\n",
       "      <td>20240212153945516_0_0</td>\n",
       "      <td></td>\n",
       "      <td>6cd84a53-80a3-44a1-847a-35e72424b918-0_0-14-43...</td>\n",
       "      <td>536365</td>\n",
       "      <td>85123A</td>\n",
       "      <td>WHITE HANGING HEART T-LIGHT HOLDER</td>\n",
       "      <td>6</td>\n",
       "      <td>12/1/10 8:26</td>\n",
       "      <td>2.55</td>\n",
       "      <td>17850</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20240212153945516</td>\n",
       "      <td>20240212153945516_0_1</td>\n",
       "      <td>20240212153945516_0_7</td>\n",
       "      <td></td>\n",
       "      <td>6cd84a53-80a3-44a1-847a-35e72424b918-0_0-14-43...</td>\n",
       "      <td>536366</td>\n",
       "      <td>22633</td>\n",
       "      <td>HAND WARMER UNION JACK</td>\n",
       "      <td>6</td>\n",
       "      <td>12/1/10 8:28</td>\n",
       "      <td>1.85</td>\n",
       "      <td>17850</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20240212153945516</td>\n",
       "      <td>20240212153945516_0_2</td>\n",
       "      <td>20240212153945516_0_10</td>\n",
       "      <td></td>\n",
       "      <td>6cd84a53-80a3-44a1-847a-35e72424b918-0_0-14-43...</td>\n",
       "      <td>536367</td>\n",
       "      <td>22745</td>\n",
       "      <td>POPPY'S PLAYHOUSE BEDROOM</td>\n",
       "      <td>6</td>\n",
       "      <td>12/1/10 8:34</td>\n",
       "      <td>2.1</td>\n",
       "      <td>13047</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20240212153945516</td>\n",
       "      <td>20240212153945516_0_3</td>\n",
       "      <td>20240212153945516_0_11</td>\n",
       "      <td></td>\n",
       "      <td>6cd84a53-80a3-44a1-847a-35e72424b918-0_0-14-43...</td>\n",
       "      <td>536367</td>\n",
       "      <td>22748</td>\n",
       "      <td>POPPY'S PLAYHOUSE KITCHEN</td>\n",
       "      <td>6</td>\n",
       "      <td>12/1/10 8:34</td>\n",
       "      <td>2.1</td>\n",
       "      <td>13047</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20240212153945516</td>\n",
       "      <td>20240212153945516_0_4</td>\n",
       "      <td>20240212153945516_0_15</td>\n",
       "      <td></td>\n",
       "      <td>6cd84a53-80a3-44a1-847a-35e72424b918-0_0-14-43...</td>\n",
       "      <td>536367</td>\n",
       "      <td>22623</td>\n",
       "      <td>BOX OF VINTAGE JIGSAW BLOCKS</td>\n",
       "      <td>3</td>\n",
       "      <td>12/1/10 8:34</td>\n",
       "      <td>4.95</td>\n",
       "      <td>13047</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>20240212153945516</td>\n",
       "      <td>20240212153945516_0_5</td>\n",
       "      <td>20240212153945516_0_19</td>\n",
       "      <td></td>\n",
       "      <td>6cd84a53-80a3-44a1-847a-35e72424b918-0_0-14-43...</td>\n",
       "      <td>536367</td>\n",
       "      <td>21777</td>\n",
       "      <td>RECIPE BOX WITH METAL HEART</td>\n",
       "      <td>4</td>\n",
       "      <td>12/1/10 8:34</td>\n",
       "      <td>7.95</td>\n",
       "      <td>13047</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>20240212153945516</td>\n",
       "      <td>20240212153945516_0_6</td>\n",
       "      <td>20240212153945516_0_27</td>\n",
       "      <td></td>\n",
       "      <td>6cd84a53-80a3-44a1-847a-35e72424b918-0_0-14-43...</td>\n",
       "      <td>536370</td>\n",
       "      <td>22727</td>\n",
       "      <td>ALARM CLOCK BAKELIKE RED</td>\n",
       "      <td>24</td>\n",
       "      <td>12/1/10 8:45</td>\n",
       "      <td>3.75</td>\n",
       "      <td>12583</td>\n",
       "      <td>France</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>20240212153945516</td>\n",
       "      <td>20240212153945516_0_7</td>\n",
       "      <td>20240212153945516_0_35</td>\n",
       "      <td></td>\n",
       "      <td>6cd84a53-80a3-44a1-847a-35e72424b918-0_0-14-43...</td>\n",
       "      <td>536370</td>\n",
       "      <td>22629</td>\n",
       "      <td>SPACEBOY LUNCH BOX</td>\n",
       "      <td>24</td>\n",
       "      <td>12/1/10 8:45</td>\n",
       "      <td>1.95</td>\n",
       "      <td>12583</td>\n",
       "      <td>France</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>20240212153945516</td>\n",
       "      <td>20240212153945516_0_8</td>\n",
       "      <td>20240212153945516_0_36</td>\n",
       "      <td></td>\n",
       "      <td>6cd84a53-80a3-44a1-847a-35e72424b918-0_0-14-43...</td>\n",
       "      <td>536370</td>\n",
       "      <td>22659</td>\n",
       "      <td>LUNCH BOX I LOVE LONDON</td>\n",
       "      <td>24</td>\n",
       "      <td>12/1/10 8:45</td>\n",
       "      <td>1.95</td>\n",
       "      <td>12583</td>\n",
       "      <td>France</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>20240212153945516</td>\n",
       "      <td>20240212153945516_0_9</td>\n",
       "      <td>20240212153945516_0_39</td>\n",
       "      <td></td>\n",
       "      <td>6cd84a53-80a3-44a1-847a-35e72424b918-0_0-14-43...</td>\n",
       "      <td>536370</td>\n",
       "      <td>21731</td>\n",
       "      <td>RED TOADSTOOL LED NIGHT LIGHT</td>\n",
       "      <td>24</td>\n",
       "      <td>12/1/10 8:45</td>\n",
       "      <td>1.65</td>\n",
       "      <td>12583</td>\n",
       "      <td>France</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  _hoodie_commit_time   _hoodie_commit_seqno      _hoodie_record_key  \\\n",
       "0   20240212153945516  20240212153945516_0_0   20240212153945516_0_0   \n",
       "1   20240212153945516  20240212153945516_0_1   20240212153945516_0_7   \n",
       "2   20240212153945516  20240212153945516_0_2  20240212153945516_0_10   \n",
       "3   20240212153945516  20240212153945516_0_3  20240212153945516_0_11   \n",
       "4   20240212153945516  20240212153945516_0_4  20240212153945516_0_15   \n",
       "5   20240212153945516  20240212153945516_0_5  20240212153945516_0_19   \n",
       "6   20240212153945516  20240212153945516_0_6  20240212153945516_0_27   \n",
       "7   20240212153945516  20240212153945516_0_7  20240212153945516_0_35   \n",
       "8   20240212153945516  20240212153945516_0_8  20240212153945516_0_36   \n",
       "9   20240212153945516  20240212153945516_0_9  20240212153945516_0_39   \n",
       "\n",
       "  _hoodie_partition_path                                  _hoodie_file_name  \\\n",
       "0                         6cd84a53-80a3-44a1-847a-35e72424b918-0_0-14-43...   \n",
       "1                         6cd84a53-80a3-44a1-847a-35e72424b918-0_0-14-43...   \n",
       "2                         6cd84a53-80a3-44a1-847a-35e72424b918-0_0-14-43...   \n",
       "3                         6cd84a53-80a3-44a1-847a-35e72424b918-0_0-14-43...   \n",
       "4                         6cd84a53-80a3-44a1-847a-35e72424b918-0_0-14-43...   \n",
       "5                         6cd84a53-80a3-44a1-847a-35e72424b918-0_0-14-43...   \n",
       "6                         6cd84a53-80a3-44a1-847a-35e72424b918-0_0-14-43...   \n",
       "7                         6cd84a53-80a3-44a1-847a-35e72424b918-0_0-14-43...   \n",
       "8                         6cd84a53-80a3-44a1-847a-35e72424b918-0_0-14-43...   \n",
       "9                         6cd84a53-80a3-44a1-847a-35e72424b918-0_0-14-43...   \n",
       "\n",
       "  InvoiceNo StockCode                         Description Quantity  \\\n",
       "0    536365    85123A  WHITE HANGING HEART T-LIGHT HOLDER        6   \n",
       "1    536366     22633              HAND WARMER UNION JACK        6   \n",
       "2    536367     22745          POPPY'S PLAYHOUSE BEDROOM         6   \n",
       "3    536367     22748           POPPY'S PLAYHOUSE KITCHEN        6   \n",
       "4    536367     22623       BOX OF VINTAGE JIGSAW BLOCKS         3   \n",
       "5    536367     21777         RECIPE BOX WITH METAL HEART        4   \n",
       "6    536370     22727           ALARM CLOCK BAKELIKE RED        24   \n",
       "7    536370     22629                 SPACEBOY LUNCH BOX        24   \n",
       "8    536370     22659             LUNCH BOX I LOVE LONDON       24   \n",
       "9    536370     21731       RED TOADSTOOL LED NIGHT LIGHT       24   \n",
       "\n",
       "    InvoiceDate UnitPrice CustomerID         Country  \n",
       "0  12/1/10 8:26      2.55      17850  United Kingdom  \n",
       "1  12/1/10 8:28      1.85      17850  United Kingdom  \n",
       "2  12/1/10 8:34       2.1      13047  United Kingdom  \n",
       "3  12/1/10 8:34       2.1      13047  United Kingdom  \n",
       "4  12/1/10 8:34      4.95      13047  United Kingdom  \n",
       "5  12/1/10 8:34      7.95      13047  United Kingdom  \n",
       "6  12/1/10 8:45      3.75      12583          France  \n",
       "7  12/1/10 8:45      1.95      12583          France  \n",
       "8  12/1/10 8:45      1.95      12583          France  \n",
       "9  12/1/10 8:45      1.65      12583          France  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spark.sql(\"SELECT * FROM customer_data LIMIT 10\").toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d12b07c4",
   "metadata": {},
   "source": [
    "# Run Clustering"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "e04d360e",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------------+----------------+---------+-------------------+\n",
      "|        timestamp|input_group_size|    state|involved_partitions|\n",
      "+-----------------+----------------+---------+-------------------+\n",
      "|20240220114608983|               1|COMPLETED|                  *|\n",
      "+-----------------+----------------+---------+-------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "spark.sql(\"\"\"\n",
    "    CALL run_clustering(\n",
    "        table => 'customer_data',\n",
    "        op => 'scheduleandexecute',\n",
    "        options => 'hoodie.clustering.async.max.commits=4,\n",
    "                    hoodie.clustering.plan.strategy.small.file.limit=629145600,\n",
    "                    hoodie.clustering.plan.strategy.sort.columns=Country,\n",
    "                    hoodie.write.lock.filesystem.path = file://tmp/hudilock/dip_hudi,\n",
    "                    hoodie.fs.atomic_creation.support = s3a,\n",
    "                    hoodie.clustering.plan.strategy.target.file.max.bytes=1073741824'\n",
    "    )\n",
    "\"\"\").show()"
   ]
  }
 ],
 "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.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}