5-cow-clustering.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "44288d4b-1602-44ad-9881-f0e510a30d76",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Apache Hudi Core Conceptions (5) - COW: Clustering"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "83431d2a-fdca-4dd4-b08c-230b3abd0fca",
   "metadata": {
    "tags": []
   },
   "source": [
    "## 1. Configuration"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "030c74b6-c1ea-4df3-bac4-20c9040dfc78",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:27:39.195872Z",
     "iopub.status.busy": "2023-03-19T02:27:39.195564Z",
     "iopub.status.idle": "2023-03-19T02:27:43.347184Z",
     "shell.execute_reply": "2023-03-19T02:27:43.346324Z",
     "shell.execute_reply.started": "2023-03-19T02:27:39.195844Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-rw-r--r--   1 emr-notebook hdfsadmingroup   61421977 2023-03-19 02:27 /tmp/hudi-spark-bundle.jar\n"
     ]
    }
   ],
   "source": [
    "%%sh\n",
    "# deploy hudi bundle jar\n",
    "hdfs dfs -copyFromLocal -f /usr/lib/hudi/hudi-spark-bundle.jar /tmp/hudi-spark-bundle.jar\n",
    "hdfs dfs -ls /tmp/hudi-spark-bundle.jar"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "2ec4c106-0651-493c-b9e7-3ef7b0c623be",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:27:43.348740Z",
     "iopub.status.busy": "2023-03-19T02:27:43.348501Z",
     "iopub.status.idle": "2023-03-19T02:27:43.366493Z",
     "shell.execute_reply": "2023-03-19T02:27:43.365781Z",
     "shell.execute_reply.started": "2023-03-19T02:27:43.348715Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "Current session configs: <tt>{'conf': {'spark.jars': 'hdfs:///tmp/hudi-spark-bundle.jar', 'spark.serializer': 'org.apache.spark.serializer.KryoSerializer', 'spark.sql.extensions': 'org.apache.spark.sql.hudi.HoodieSparkSessionExtension', 'spark.sql.catalog.spark_catalog': 'org.apache.spark.sql.hudi.catalog.HoodieCatalog'}, 'proxyUser': 'user_bluishglc', 'kind': 'spark'}</tt><br>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "No active sessions."
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%configure -f\n",
    "{\n",
    "    \"conf\" : {\n",
    "        \"spark.jars\":\"hdfs:///tmp/hudi-spark-bundle.jar\",            \n",
    "        \"spark.serializer\":\"org.apache.spark.serializer.KryoSerializer\",\n",
    "        \"spark.sql.extensions\":\"org.apache.spark.sql.hudi.HoodieSparkSessionExtension\",\n",
    "        \"spark.sql.catalog.spark_catalog\":\"org.apache.spark.sql.hudi.catalog.HoodieCatalog\"\n",
    "    }\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "bc32a2d6-445f-4f77-8af9-04a345e6dc68",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:27:43.368032Z",
     "iopub.status.busy": "2023-03-19T02:27:43.367807Z",
     "iopub.status.idle": "2023-03-19T02:27:43.372640Z",
     "shell.execute_reply": "2023-03-19T02:27:43.371769Z",
     "shell.execute_reply.started": "2023-03-19T02:27:43.368010Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "env: S3_BUCKET=apache-hudi-core-conceptions\n"
     ]
    }
   ],
   "source": [
    "%env S3_BUCKET=apache-hudi-core-conceptions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "4ddf9830-9202-45a7-aa5f-94c9f1d11995",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:27:43.374241Z",
     "iopub.status.busy": "2023-03-19T02:27:43.374032Z",
     "iopub.status.idle": "2023-03-19T02:28:13.355913Z",
     "shell.execute_reply": "2023-03-19T02:28:13.355011Z",
     "shell.execute_reply.started": "2023-03-19T02:27:43.374221Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Starting Spark application\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tbody><tr><th>ID</th><th>YARN Application ID</th><th>Kind</th><th>State</th><th>Spark UI</th><th>Driver log</th><th>User</th><th>Current session?</th></tr><tr><td>94</td><td>application_1678096020253_0180</td><td>spark</td><td>idle</td><td><a target=\"_blank\" href=\"http://ip-10-0-129-209.ec2.internal:20888/proxy/application_1678096020253_0180/\" class=\"emr-proxy-link j-6V65AY8X5VHR application_1678096020253_0180\" emr-resource=\"j-6V65AY8X5VHR\n",
       "\" application-id=\"application_1678096020253_0180\">Link</a></td><td><a target=\"_blank\" href=\"http://ip-10-0-132-108.ec2.internal:8042/node/containerlogs/container_1678096020253_0180_01_000001/livy\">Link</a></td><td>None</td><td>✔</td></tr></tbody></table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SparkSession available as 'spark'.\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<style>.jp-OutputArea-child .jupyter-widgets.jp-OutputArea-output { overflow: auto; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "7d4ae92b1af542b0bc2362cce45cc766",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(HBox(children=(HTML(value='Type:'), Button(description='Table', layout=Layout(width='70px'), st…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "4e4967da83044ef88b5257b4f72e4de1",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Output()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "set S3_BUCKET=apache-hudi-core-conceptions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "a26b4bca-2c9f-4df8-bae2-b6f410d234eb",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:28:13.357387Z",
     "iopub.status.busy": "2023-03-19T02:28:13.357013Z",
     "iopub.status.idle": "2023-03-19T02:28:13.363614Z",
     "shell.execute_reply": "2023-03-19T02:28:13.362929Z",
     "shell.execute_reply.started": "2023-03-19T02:28:13.357358Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "env: WORKSPACE=/home/emr-notebook/apache-hudi-core-conceptions\n"
     ]
    }
   ],
   "source": [
    "%env WORKSPACE=/home/emr-notebook/apache-hudi-core-conceptions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "6041fe4a-ef77-4519-aa37-9fd6feb3434e",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:28:13.364758Z",
     "iopub.status.busy": "2023-03-19T02:28:13.364477Z",
     "iopub.status.idle": "2023-03-19T02:28:13.526616Z",
     "shell.execute_reply": "2023-03-19T02:28:13.525419Z",
     "shell.execute_reply.started": "2023-03-19T02:28:13.364727Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/home/emr-notebook/apache-hudi-core-conceptions/hudi-stat.sh\n"
     ]
    }
   ],
   "source": [
    "%%sh\n",
    "# make workspace\n",
    "mkdir -p $WORKSPACE\n",
    "# deploy hudi-stat.sh, a utility shell script to output hudi table status\n",
    "wget https://raw.githubusercontent.com/bluishglc/apache-hudi-core-conceptions/master/hudi-stat.sh -O $WORKSPACE/hudi-stat.sh &>/dev/null\n",
    "chmod a+x $WORKSPACE/hudi-stat.sh\n",
    "ls $WORKSPACE/hudi-stat.sh"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "435d81cc-0e9d-4eaa-805b-2c28ff765b6e",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:28:13.528587Z",
     "iopub.status.busy": "2023-03-19T02:28:13.527871Z",
     "iopub.status.idle": "2023-03-19T02:28:13.535815Z",
     "shell.execute_reply": "2023-03-19T02:28:13.534955Z",
     "shell.execute_reply.started": "2023-03-19T02:28:13.528546Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style>\n",
       "table {float:left}\n",
       "</style>\n"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%html\n",
    "<style>\n",
    "table {float:left}\n",
    "</style>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "07a926b5-08a4-4b5f-a8db-d2dd6e239566",
   "metadata": {
    "tags": []
   },
   "source": [
    "## 2. Test Case 1 - Sync Clustering ( Inline Schedule + Inline Execute )"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ef523d6b-0976-4b44-b924-c5526bce23f6",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "### 2.1. Test Plan\n",
    "\n",
    "Step No.|Action|Volume / Partition |Storage\n",
    ":--------:|:------|:------|:----------\n",
    "1|Insert|96MB|+1 Small File\n",
    "2|Insert|213MB|+1 Max File +1 Small File\n",
    "3|Insert|182MB|+1 Max File +1 Small File +2 Clustered Files"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ee4c3d26-6790-4d3f-b468-88fb765bef94",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 2.2. Key Settings\n",
    "\n",
    "KEY|DEFAULT VALUE|SET VALUE\n",
    ":---|:---|:---\n",
    "hoodie.clustering.inline|false|true\n",
    "hoodie.clustering.schedule.inline|false|false\n",
    "hoodie.clustering.async.enabled|false|false\n",
    "hoodie.clustering.inline.max.commits|4|3\n",
    "hoodie.clustering.plan.strategy.target.file.max.bytes|1073741824 / 1GB|314572800 / 300MB\n",
    "hoodie.clustering.plan.strategy.small.file.limit|314572800 / 300MB|209715200 / 200MB\n",
    "hoodie.clustering.plan.strategy.sort.columns|-|review_date\n",
    "hoodie.parquet.small.file.limit|104857600 / 100MB | 0\n",
    "hoodie.copyonwrite.record.size.estimate|1024|175"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cbb044c2-c560-4ef5-a432-1459b2b0184d",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-02-25T02:57:45.037101Z",
     "iopub.status.busy": "2023-02-25T02:57:45.036681Z",
     "iopub.status.idle": "2023-02-25T02:58:07.353493Z",
     "shell.execute_reply": "2023-02-25T02:58:07.352596Z",
     "shell.execute_reply.started": "2023-02-25T02:57:45.037060Z"
    },
    "tags": []
   },
   "source": [
    "### 2.3. Set Variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "ec227cb2-7934-4c0b-a156-17555d0bf51f",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:28:13.538364Z",
     "iopub.status.busy": "2023-03-19T02:28:13.538142Z",
     "iopub.status.idle": "2023-03-19T02:28:14.455418Z",
     "shell.execute_reply": "2023-03-19T02:28:14.454734Z",
     "shell.execute_reply.started": "2023-03-19T02:28:13.538342Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<style>.jp-OutputArea-child .jupyter-widgets.jp-OutputArea-output { overflow: auto; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "1fc45230584e44e7ab9fdcb6bec406ea",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(HBox(children=(HTML(value='Type:'), Button(description='Table', layout=Layout(width='70px'), st…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "85c0a35fd37345ffbed4cd6a793fddaa",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Output()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "set TABLE_NAME=reviews_cow_clustering_1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "af107b02-40e6-4c04-9ec2-05f2a04d2774",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:28:14.457666Z",
     "iopub.status.busy": "2023-03-19T02:28:14.457347Z",
     "iopub.status.idle": "2023-03-19T02:28:14.462248Z",
     "shell.execute_reply": "2023-03-19T02:28:14.461564Z",
     "shell.execute_reply.started": "2023-03-19T02:28:14.457636Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "env: TABLE_NAME=reviews_cow_clustering_1\n"
     ]
    }
   ],
   "source": [
    "%env TABLE_NAME=reviews_cow_clustering_1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bf9f3e19-bde1-46c7-b0cf-fa4d30244da0",
   "metadata": {},
   "source": [
    "### 2.4. Create Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "c7e14f8e-d1ab-4a09-9285-645780cd2040",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:28:14.463816Z",
     "iopub.status.busy": "2023-03-19T02:28:14.463397Z",
     "iopub.status.idle": "2023-03-19T02:28:20.581721Z",
     "shell.execute_reply": "2023-03-19T02:28:20.580836Z",
     "shell.execute_reply.started": "2023-03-19T02:28:14.463736Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%sh\n",
    "aws s3 rm s3://${S3_BUCKET}/${TABLE_NAME} --recursive &>/dev/null\n",
    "rm -rf ${WORKSPACE}/${TABLE_NAME}\n",
    "sleep 5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "bc129c8c-720a-4b59-ab69-e62fde049dd4",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:28:20.583212Z",
     "iopub.status.busy": "2023-03-19T02:28:20.582955Z",
     "iopub.status.idle": "2023-03-19T02:28:23.952391Z",
     "shell.execute_reply": "2023-03-19T02:28:23.951383Z",
     "shell.execute_reply.started": "2023-03-19T02:28:20.583184Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<style>.jp-OutputArea-child .jupyter-widgets.jp-OutputArea-output { overflow: auto; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "e94573e099df48128dac67e4603f1c79",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(HBox(), EncodingWidget(children=(VBox(children=(HTML(value='Encoding:'), Dropdown(description='…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "a1ccbbac4e0144748f9b52f4b96808c6",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Output()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "drop table if exists ${TABLE_NAME}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "de3784b9-9661-47d7-a7f1-fa4db6e48b7d",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:28:23.954438Z",
     "iopub.status.busy": "2023-03-19T02:28:23.953772Z",
     "iopub.status.idle": "2023-03-19T02:28:27.302395Z",
     "shell.execute_reply": "2023-03-19T02:28:27.301615Z",
     "shell.execute_reply.started": "2023-03-19T02:28:23.954401Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<style>.jp-OutputArea-child .jupyter-widgets.jp-OutputArea-output { overflow: auto; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "8519d5cb97c64715bec49f5de13baa24",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(HBox(), EncodingWidget(children=(VBox(children=(HTML(value='Encoding:'), Dropdown(description='…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "a993f10fa544459c81a356160ecd7725",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Output()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "create table if not exists ${TABLE_NAME}(\n",
    "    review_id string, \n",
    "    star_rating int, \n",
    "    review_body string, \n",
    "    review_date date, \n",
    "    year long,\n",
    "    timestamp long,\n",
    "    parity int\n",
    ")\n",
    "using hudi\n",
    "location 's3://${S3_BUCKET}/${TABLE_NAME}'\n",
    "partitioned by (parity)\n",
    "options ( \n",
    "    type = 'cow',  \n",
    "    primaryKey = 'review_id', \n",
    "    preCombineField = 'timestamp',\n",
    "    hoodie.clustering.inline = 'true',\n",
    "    hoodie.clustering.schedule.inline = 'false',\n",
    "    hoodie.clustering.async.enabled = 'false',\n",
    "    hoodie.clustering.inline.max.commits = '3',\n",
    "    hoodie.clustering.plan.strategy.small.file.limit = '209715200',\n",
    "    hoodie.clustering.plan.strategy.target.file.max.bytes = '314572800',\n",
    "    hoodie.clustering.plan.strategy.sort.columns = 'review_date',\n",
    "    hoodie.parquet.small.file.limit = '0',\n",
    "    hoodie.copyonwrite.record.size.estimate = '175'\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "52c86110-0dd8-49cb-9e5c-db659e915ae2",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 2.5. Insert 96MB / +1 Small File"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "f593c3f9-b0b2-4950-96a4-06428f3ad161",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:28:27.303706Z",
     "iopub.status.busy": "2023-03-19T02:28:27.303485Z",
     "iopub.status.idle": "2023-03-19T02:29:26.792259Z",
     "shell.execute_reply": "2023-03-19T02:29:26.791483Z",
     "shell.execute_reply.started": "2023-03-19T02:28:27.303685Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<style>.jp-OutputArea-child .jupyter-widgets.jp-OutputArea-output { overflow: auto; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "bff09950b19c425a893ae756f30a790d",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(HBox(), EncodingWidget(children=(VBox(children=(HTML(value='Encoding:'), Dropdown(description='…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "ee65b026948f417ea2d590f451bad447",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Output()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "insert into \n",
    "    ${TABLE_NAME}\n",
    "select \n",
    "    review_id, \n",
    "    star_rating, \n",
    "    review_body, \n",
    "    review_date, \n",
    "    year,\n",
    "    unix_timestamp(current_timestamp()) as timestamp,\n",
    "    mod(crc32(review_id), 2) as parity\n",
    "from\n",
    "    reviews\n",
    "where\n",
    "    year = 2003;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "8dcdeac8-e953-4bdd-856a-c478bd52f7a8",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:29:26.793921Z",
     "iopub.status.busy": "2023-03-19T02:29:26.793588Z",
     "iopub.status.idle": "2023-03-19T02:29:41.863563Z",
     "shell.execute_reply": "2023-03-19T02:29:41.862695Z",
     "shell.execute_reply.started": "2023-03-19T02:29:26.793884Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "[ TIMELINE ]\n",
      "\n",
      "╔═════╤═══════════════════╤════════╤═══════════╤═════════════╤═════════════╤═════════════╗\n",
      "║ No. │ Instant           │ Action │ State     │ Requested   │ Inflight    │ Completed   ║\n",
      "║     │                   │        │           │ Time        │ Time        │ Time        ║\n",
      "╠═════╪═══════════════════╪════════╪═══════════╪═════════════╪═════════════╪═════════════╣\n",
      "║ 0   │ 20230319022828271 │ commit │ COMPLETED │ 03-19 02:28 │ 03-19 02:28 │ 03-19 02:29 ║\n",
      "╚═════╧═══════════════════╧════════╧═══════════╧═════════════╧═════════════╧═════════════╝\n",
      "\n",
      "[ COMMITS ]\n",
      "\n",
      "╔═══════════════════╤═════════════════════╤═══════════════════╤═════════════════════╤══════════════════════════╤═══════════════════════╤══════════════════════════════╤══════════════╗\n",
      "║ CommitTime        │ Total Bytes Written │ Total Files Added │ Total Files Updated │ Total Partitions Written │ Total Records Written │ Total Update Records Written │ Total Errors ║\n",
      "╠═══════════════════╪═════════════════════╪═══════════════════╪═════════════════════╪══════════════════════════╪═══════════════════════╪══════════════════════════════╪══════════════╣\n",
      "║ 20230319022828271 │ 191.6 MB            │ 2                 │ 0                   │ 2                        │ 1155127               │ 0                            │ 0            ║\n",
      "╚═══════════════════╧═════════════════════╧═══════════════════╧═════════════════════╧══════════════════════════╧═══════════════════════╧══════════════════════════════╧══════════════╝\n",
      "\n",
      "[ STORAGE ]\n",
      "\n",
      "/home/emr-notebook/apache-hudi-core-conceptions/reviews_cow_clustering_1\n",
      "├── [ 96M 02:29:41]  parity=0\n",
      "│   └── [ 96M 02:29:13]  3b538027-3bad-49f6-b2bf-d86c802dc37e-0_0-34-628_20230319022828271.parquet\n",
      "└── [ 96M 02:29:41]  parity=1\n",
      "    └── [ 96M 02:29:13]  edd8a697-47d4-44c5-a3e6-3f7ba5d51a08-0_1-34-629_20230319022828271.parquet\n"
     ]
    }
   ],
   "source": [
    "%%sh\n",
    "${WORKSPACE}/hudi-stat.sh s3://${S3_BUCKET}/${TABLE_NAME} timeline commits storage"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "49d18b4b-187c-4d78-8680-61638307d829",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 2.6. Insert 213MB / +1 Max File +1 Small File"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "8ddba28f-865c-434e-8f5b-21d09c0daddc",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:29:41.865027Z",
     "iopub.status.busy": "2023-03-19T02:29:41.864785Z",
     "iopub.status.idle": "2023-03-19T02:30:49.378033Z",
     "shell.execute_reply": "2023-03-19T02:30:49.377209Z",
     "shell.execute_reply.started": "2023-03-19T02:29:41.865000Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<style>.jp-OutputArea-child .jupyter-widgets.jp-OutputArea-output { overflow: auto; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "c38013a865c24a25915d2b2b19531bd2",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(HBox(), EncodingWidget(children=(VBox(children=(HTML(value='Encoding:'), Dropdown(description='…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "fc7050c7866e43b2baa655266617bc1b",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Output()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "insert into \n",
    "    ${TABLE_NAME}\n",
    "select \n",
    "    review_id, \n",
    "    star_rating, \n",
    "    review_body, \n",
    "    review_date, \n",
    "    year,\n",
    "    unix_timestamp(current_timestamp()) as timestamp,\n",
    "    mod(crc32(review_id), 2) as parity\n",
    "from\n",
    "    reviews\n",
    "where\n",
    "    year in (2004, 2005);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "cc1da694-7f0b-443a-bdc9-d9d2b9815682",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:30:49.379586Z",
     "iopub.status.busy": "2023-03-19T02:30:49.379263Z",
     "iopub.status.idle": "2023-03-19T02:31:06.624426Z",
     "shell.execute_reply": "2023-03-19T02:31:06.623593Z",
     "shell.execute_reply.started": "2023-03-19T02:30:49.379552Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "[ TIMELINE ]\n",
      "\n",
      "╔═════╤═══════════════════╤════════╤═══════════╤═════════════╤═════════════╤═════════════╗\n",
      "║ No. │ Instant           │ Action │ State     │ Requested   │ Inflight    │ Completed   ║\n",
      "║     │                   │        │           │ Time        │ Time        │ Time        ║\n",
      "╠═════╪═══════════════════╪════════╪═══════════╪═════════════╪═════════════╪═════════════╣\n",
      "║ 0   │ 20230319022828271 │ commit │ COMPLETED │ 03-19 02:28 │ 03-19 02:28 │ 03-19 02:29 ║\n",
      "╟─────┼───────────────────┼────────┼───────────┼─────────────┼─────────────┼─────────────╢\n",
      "║ 1   │ 20230319022943245 │ commit │ COMPLETED │ 03-19 02:29 │ 03-19 02:30 │ 03-19 02:30 ║\n",
      "╚═════╧═══════════════════╧════════╧═══════════╧═════════════╧═════════════╧═════════════╝\n",
      "\n",
      "[ COMMITS ]\n",
      "\n",
      "╔═══════════════════╤═════════════════════╤═══════════════════╤═════════════════════╤══════════════════════════╤═══════════════════════╤══════════════════════════════╤══════════════╗\n",
      "║ CommitTime        │ Total Bytes Written │ Total Files Added │ Total Files Updated │ Total Partitions Written │ Total Records Written │ Total Update Records Written │ Total Errors ║\n",
      "╠═══════════════════╪═════════════════════╪═══════════════════╪═════════════════════╪══════════════════════════╪═══════════════════════╪══════════════════════════════╪══════════════╣\n",
      "║ 20230319022943245 │ 426.7 MB            │ 4                 │ 0                   │ 2                        │ 2570430               │ 0                            │ 0            ║\n",
      "╟───────────────────┼─────────────────────┼───────────────────┼─────────────────────┼──────────────────────────┼───────────────────────┼──────────────────────────────┼──────────────╢\n",
      "║ 20230319022828271 │ 191.6 MB            │ 2                 │ 0                   │ 2                        │ 1155127               │ 0                            │ 0            ║\n",
      "╚═══════════════════╧═════════════════════╧═══════════════════╧═════════════════════╧══════════════════════════╧═══════════════════════╧══════════════════════════════╧══════════════╝\n",
      "\n",
      "[ STORAGE ]\n",
      "\n",
      "/home/emr-notebook/apache-hudi-core-conceptions/reviews_cow_clustering_1\n",
      "├── [309M 02:31:06]  parity=0\n",
      "│   ├── [ 96M 02:29:13]  3b538027-3bad-49f6-b2bf-d86c802dc37e-0_0-34-628_20230319022828271.parquet\n",
      "│   ├── [120M 02:30:37]  4a602af0-9978-453d-b1b1-271e407efbfe-0_0-81-1327_20230319022943245.parquet\n",
      "│   └── [ 93M 02:30:32]  a005d99e-3c2a-4871-a976-4616b5cd250c-0_1-81-1328_20230319022943245.parquet\n",
      "└── [309M 02:31:06]  parity=1\n",
      "    ├── [ 94M 02:30:32]  6d6efe87-3467-42fe-b86a-51db7d3c21dc-0_3-81-1330_20230319022943245.parquet\n",
      "    ├── [120M 02:30:37]  c041cce4-1ca8-4d67-b172-7eb65f60a829-0_2-81-1329_20230319022943245.parquet\n",
      "    └── [ 96M 02:29:13]  edd8a697-47d4-44c5-a3e6-3f7ba5d51a08-0_1-34-629_20230319022828271.parquet\n"
     ]
    }
   ],
   "source": [
    "%%sh\n",
    "${WORKSPACE}/hudi-stat.sh s3://${S3_BUCKET}/${TABLE_NAME} timeline commits storage"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6ff12b6d-d624-4932-aaf8-efaca98b413b",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 2.7. Insert 182MB / +1 Max File +1 Small File"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "63be0ec0-67e8-4767-a51c-24f5e2b6a25b",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:31:06.625978Z",
     "iopub.status.busy": "2023-03-19T02:31:06.625713Z",
     "iopub.status.idle": "2023-03-19T02:34:38.508472Z",
     "shell.execute_reply": "2023-03-19T02:34:38.507621Z",
     "shell.execute_reply.started": "2023-03-19T02:31:06.625949Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<style>.jp-OutputArea-child .jupyter-widgets.jp-OutputArea-output { overflow: auto; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "bda255cf68974f3eabdf1b45239b7925",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(HBox(), EncodingWidget(children=(VBox(children=(HTML(value='Encoding:'), Dropdown(description='…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "7b69aedd358d485fabe0d8fb8e972a9b",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Output()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "insert into \n",
    "    ${TABLE_NAME}\n",
    "select \n",
    "    review_id, \n",
    "    star_rating, \n",
    "    review_body, \n",
    "    review_date, \n",
    "    year,\n",
    "    unix_timestamp(current_timestamp()) as timestamp,\n",
    "    mod(crc32(review_id), 2) as parity\n",
    "from\n",
    "    reviews\n",
    "where\n",
    "    year = 2007;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "3cd2a646-fab5-49b4-81d1-d89729157174",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:34:38.509711Z",
     "iopub.status.busy": "2023-03-19T02:34:38.509446Z",
     "iopub.status.idle": "2023-03-19T02:35:00.625395Z",
     "shell.execute_reply": "2023-03-19T02:35:00.624294Z",
     "shell.execute_reply.started": "2023-03-19T02:34:38.509688Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "[ TIMELINE ]\n",
      "\n",
      "╔═════╤═══════════════════╤═══════════════╤═══════════╤═════════════╤═════════════╤═════════════╗\n",
      "║ No. │ Instant           │ Action        │ State     │ Requested   │ Inflight    │ Completed   ║\n",
      "║     │                   │               │           │ Time        │ Time        │ Time        ║\n",
      "╠═════╪═══════════════════╪═══════════════╪═══════════╪═════════════╪═════════════╪═════════════╣\n",
      "║ 0   │ 20230319022828271 │ commit        │ COMPLETED │ 03-19 02:28 │ 03-19 02:28 │ 03-19 02:29 ║\n",
      "╟─────┼───────────────────┼───────────────┼───────────┼─────────────┼─────────────┼─────────────╢\n",
      "║ 1   │ 20230319022943245 │ commit        │ COMPLETED │ 03-19 02:29 │ 03-19 02:30 │ 03-19 02:30 ║\n",
      "╟─────┼───────────────────┼───────────────┼───────────┼─────────────┼─────────────┼─────────────╢\n",
      "║ 2   │ 20230319023107794 │ commit        │ COMPLETED │ 03-19 02:31 │ 03-19 02:31 │ 03-19 02:32 ║\n",
      "╟─────┼───────────────────┼───────────────┼───────────┼─────────────┼─────────────┼─────────────╢\n",
      "║ 3   │ 20230319023207236 │ replacecommit │ COMPLETED │ 03-19 02:32 │ 03-19 02:32 │ 03-19 02:34 ║\n",
      "╚═════╧═══════════════════╧═══════════════╧═══════════╧═════════════╧═════════════╧═════════════╝\n",
      "\n",
      "[ COMMITS ]\n",
      "\n",
      "╔═══════════════════╤═════════════════════╤═══════════════════╤═════════════════════╤══════════════════════════╤═══════════════════════╤══════════════════════════════╤══════════════╗\n",
      "║ CommitTime        │ Total Bytes Written │ Total Files Added │ Total Files Updated │ Total Partitions Written │ Total Records Written │ Total Update Records Written │ Total Errors ║\n",
      "╠═══════════════════╪═════════════════════╪═══════════════════╪═════════════════════╪══════════════════════════╪═══════════════════════╪══════════════════════════════╪══════════════╣\n",
      "║ 20230319023207236 │ 978.4 MB            │ 4                 │ 0                   │ 2                        │ 5920271               │ 0                            │ 0            ║\n",
      "╟───────────────────┼─────────────────────┼───────────────────┼─────────────────────┼──────────────────────────┼───────────────────────┼──────────────────────────────┼──────────────╢\n",
      "║ 20230319023107794 │ 364.2 MB            │ 4                 │ 0                   │ 2                        │ 2194714               │ 0                            │ 0            ║\n",
      "╟───────────────────┼─────────────────────┼───────────────────┼─────────────────────┼──────────────────────────┼───────────────────────┼──────────────────────────────┼──────────────╢\n",
      "║ 20230319022943245 │ 426.7 MB            │ 4                 │ 0                   │ 2                        │ 2570430               │ 0                            │ 0            ║\n",
      "╟───────────────────┼─────────────────────┼───────────────────┼─────────────────────┼──────────────────────────┼───────────────────────┼──────────────────────────────┼──────────────╢\n",
      "║ 20230319022828271 │ 191.6 MB            │ 2                 │ 0                   │ 2                        │ 1155127               │ 0                            │ 0            ║\n",
      "╚═══════════════════╧═════════════════════╧═══════════════════╧═════════════════════╧══════════════════════════╧═══════════════════════╧══════════════════════════════╧══════════════╝\n",
      "\n",
      "[ STORAGE ]\n",
      "\n",
      "/home/emr-notebook/apache-hudi-core-conceptions/reviews_cow_clustering_1\n",
      "├── [980M 02:35:00]  parity=0\n",
      "│   ├── [239M 02:33:25]  14d97218-db8b-480e-bde7-63a5ecba8014-0_1-158-2111_20230319023207236.parquet\n",
      "│   ├── [ 96M 02:29:13]  3b538027-3bad-49f6-b2bf-d86c802dc37e-0_0-34-628_20230319022828271.parquet\n",
      "│   ├── [119M 02:32:01]  442ead5f-66b4-4f6b-a2fd-1310dbad04de-0_0-131-2065_20230319023107794.parquet\n",
      "│   ├── [120M 02:30:37]  4a602af0-9978-453d-b1b1-271e407efbfe-0_0-81-1327_20230319022943245.parquet\n",
      "│   ├── [ 63M 02:31:50]  6dff2086-c83a-4a6c-8d30-409c4ae001ce-0_1-131-2066_20230319023107794.parquet\n",
      "│   ├── [ 93M 02:30:32]  a005d99e-3c2a-4871-a976-4616b5cd250c-0_1-81-1328_20230319022943245.parquet\n",
      "│   └── [250M 02:33:23]  d9eb9f8e-38f9-4f1c-aa87-e3568cb2867d-0_0-158-2110_20230319023207236.parquet\n",
      "└── [981M 02:35:00]  parity=1\n",
      "    ├── [229M 02:33:22]  1df48004-9324-4dab-a009-50d03a488fd2-0_3-158-2113_20230319023207236.parquet\n",
      "    ├── [261M 02:33:25]  2d6ad359-950c-4dd4-b47e-8c835fe7b43b-0_2-158-2112_20230319023207236.parquet\n",
      "    ├── [119M 02:32:00]  5b630029-65a9-40e4-9570-edff5fcef9b2-0_2-131-2067_20230319023107794.parquet\n",
      "    ├── [ 63M 02:31:50]  6d397469-c098-4d0f-a85b-29fb4648f60a-0_3-131-2068_20230319023107794.parquet\n",
      "    ├── [ 94M 02:30:32]  6d6efe87-3467-42fe-b86a-51db7d3c21dc-0_3-81-1330_20230319022943245.parquet\n",
      "    ├── [120M 02:30:37]  c041cce4-1ca8-4d67-b172-7eb65f60a829-0_2-81-1329_20230319022943245.parquet\n",
      "    └── [ 96M 02:29:13]  edd8a697-47d4-44c5-a3e6-3f7ba5d51a08-0_1-34-629_20230319022828271.parquet\n"
     ]
    }
   ],
   "source": [
    "%%sh\n",
    "${WORKSPACE}/hudi-stat.sh s3://${S3_BUCKET}/${TABLE_NAME} timeline commits storage"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "00bdc485-3043-484c-8603-0e630184d445",
   "metadata": {
    "tags": []
   },
   "source": [
    "## 3. Test Case 2 - Async Clustering ( Offline Schedule, Offline Execute )"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "02869779-0ffc-42f5-87f2-791bf152dff2",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 3.1. Test Plan\n",
    "\n",
    "Step No.|Action|Volume / Partition |Storage\n",
    ":--------:|:------|:------|:----------\n",
    "1|Insert|96MB|+1 Small File\n",
    "2|Insert|213MB|+1 Max File +1 Small File\n",
    "3|Insert|182MB|+1 Max File +1 Small File\n",
    "4|Offline Schedule|N/A|N/A\n",
    "5|Offline Eexecute|491MB|+2 Clustered Files"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1166a654-4057-4309-8ccb-2976b5fc0ee9",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 3.2. Key Settings\n",
    "\n",
    "KEY|DEFAULT VALUE|SET VALUE\n",
    ":---|:---|:---\n",
    "hoodie.clustering.inline|false|false\n",
    "hoodie.clustering.schedule.inline|false|false\n",
    "hoodie.clustering.async.enabled|false|true\n",
    "hoodie.clustering.async.max.commits|4|3\n",
    "hoodie.clustering.plan.strategy.target.file.max.bytes|1073741824 / 1GB|314572800 / 300MB\n",
    "hoodie.clustering.plan.strategy.small.file.limit|314572800 / 300MB|209715200 / 200MB\n",
    "hoodie.clustering.plan.strategy.sort.columns|-|review_date\n",
    "hoodie.parquet.small.file.limit|104857600 / 100MB | 0\n",
    "hoodie.copyonwrite.record.size.estimate|1024|175"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "992230f3-5f68-4a33-954c-3e1e6e120f06",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-02-25T02:57:45.037101Z",
     "iopub.status.busy": "2023-02-25T02:57:45.036681Z",
     "iopub.status.idle": "2023-02-25T02:58:07.353493Z",
     "shell.execute_reply": "2023-02-25T02:58:07.352596Z",
     "shell.execute_reply.started": "2023-02-25T02:57:45.037060Z"
    },
    "tags": []
   },
   "source": [
    "### 3.3. Set Variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "63bf2e47-d172-4f81-949c-2b46c408e99e",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:35:00.627245Z",
     "iopub.status.busy": "2023-03-19T02:35:00.626970Z",
     "iopub.status.idle": "2023-03-19T02:35:01.563868Z",
     "shell.execute_reply": "2023-03-19T02:35:01.563177Z",
     "shell.execute_reply.started": "2023-03-19T02:35:00.627218Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<style>.jp-OutputArea-child .jupyter-widgets.jp-OutputArea-output { overflow: auto; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "7ef4384d5be44489b52a95b6e1c4824f",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(HBox(children=(HTML(value='Type:'), Button(description='Table', layout=Layout(width='70px'), st…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "3a54dd6775ce48bc91245f76ac318bdb",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Output()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "set TABLE_NAME=reviews_cow_clustering_2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "3cb3dd19-a55e-4ebd-ab6d-cb1c2cdef2b3",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:35:01.565053Z",
     "iopub.status.busy": "2023-03-19T02:35:01.564830Z",
     "iopub.status.idle": "2023-03-19T02:35:01.569109Z",
     "shell.execute_reply": "2023-03-19T02:35:01.568486Z",
     "shell.execute_reply.started": "2023-03-19T02:35:01.565032Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "env: TABLE_NAME=reviews_cow_clustering_2\n"
     ]
    }
   ],
   "source": [
    "%env TABLE_NAME=reviews_cow_clustering_2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0ba3031f-f92c-486d-a0de-3f141410f2bf",
   "metadata": {},
   "source": [
    "### 3.4. Create Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "bdcd022c-8a54-463d-9f3e-fbfb92969328",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:35:01.570223Z",
     "iopub.status.busy": "2023-03-19T02:35:01.570013Z",
     "iopub.status.idle": "2023-03-19T02:35:07.930781Z",
     "shell.execute_reply": "2023-03-19T02:35:07.929571Z",
     "shell.execute_reply.started": "2023-03-19T02:35:01.570202Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%sh\n",
    "aws s3 rm s3://${S3_BUCKET}/${TABLE_NAME} --recursive &>/dev/null\n",
    "rm -rf ${WORKSPACE}/${TABLE_NAME}\n",
    "sleep 5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "2dd45b7f-cce2-4aec-99ec-7359d6d060e7",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:35:07.933353Z",
     "iopub.status.busy": "2023-03-19T02:35:07.932908Z",
     "iopub.status.idle": "2023-03-19T02:35:08.762467Z",
     "shell.execute_reply": "2023-03-19T02:35:08.761681Z",
     "shell.execute_reply.started": "2023-03-19T02:35:07.933276Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<style>.jp-OutputArea-child .jupyter-widgets.jp-OutputArea-output { overflow: auto; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "fa5e8aca8b0e471989eea27c56d58685",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(HBox(), EncodingWidget(children=(VBox(children=(HTML(value='Encoding:'), Dropdown(description='…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "782bf3e56e854728b465144f5cba85c7",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Output()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "drop table if exists ${TABLE_NAME}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "f740f107-0ab8-46b7-bdd3-206571a40c09",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:35:08.770536Z",
     "iopub.status.busy": "2023-03-19T02:35:08.770205Z",
     "iopub.status.idle": "2023-03-19T02:35:11.154399Z",
     "shell.execute_reply": "2023-03-19T02:35:11.153349Z",
     "shell.execute_reply.started": "2023-03-19T02:35:08.770502Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<style>.jp-OutputArea-child .jupyter-widgets.jp-OutputArea-output { overflow: auto; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "646b4fcb5e824f3fbe03c289faf3ae4e",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(HBox(), EncodingWidget(children=(VBox(children=(HTML(value='Encoding:'), Dropdown(description='…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "1bb5c99b150148f6becd3f4d992a745f",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Output()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "-- for async mode: \n",
    "-- 1. some clustering properties works as table properties, i.e. hoodie.clustering.async.enabled and hoodie.clustering.async.max.commits\n",
    "-- 2. all clustering async & plan properties will be overwritten by spark job level configuration via --hoodie-conf or --props\n",
    "-- so, to be simple, do NOT set any clustering-ralated properties on table, always set by --hoodie-conf or --props\n",
    "-- so, comment out clustering related settings, just keep them as reference.\n",
    "create table if not exists ${TABLE_NAME}(\n",
    "    review_id string, \n",
    "    star_rating int, \n",
    "    review_body string, \n",
    "    review_date date, \n",
    "    year long,\n",
    "    timestamp long,\n",
    "    parity int\n",
    ")\n",
    "using hudi\n",
    "location 's3://${S3_BUCKET}/${TABLE_NAME}'\n",
    "partitioned by (parity)\n",
    "options ( \n",
    "    type = 'cow',  \n",
    "    primaryKey = 'review_id', \n",
    "    preCombineField = 'timestamp',\n",
    "    -- hoodie.clustering.inline = 'false',\n",
    "    -- hoodie.clustering.schedule.inline = 'false',\n",
    "    -- hoodie.clustering.async.enabled = 'true',\n",
    "    -- hoodie.clustering.async.max.commits = '3',\n",
    "    -- hoodie.clustering.plan.strategy.small.file.limit = '209715200',\n",
    "    -- hoodie.clustering.plan.strategy.target.file.max.bytes = '314572800',\n",
    "    -- hoodie.clustering.plan.strategy.sort.columns = 'review_date',\n",
    "    hoodie.parquet.small.file.limit = '0',\n",
    "    hoodie.copyonwrite.record.size.estimate = '175'\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "970171f5-9d20-4683-98f7-29b1add0bf73",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 3.5. Insert 96MB / +1 Small File"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "68ba92de-f185-4316-a8f6-cec6ef2a5883",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:35:11.158271Z",
     "iopub.status.busy": "2023-03-19T02:35:11.157913Z",
     "iopub.status.idle": "2023-03-19T02:35:54.685151Z",
     "shell.execute_reply": "2023-03-19T02:35:54.684511Z",
     "shell.execute_reply.started": "2023-03-19T02:35:11.158233Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<style>.jp-OutputArea-child .jupyter-widgets.jp-OutputArea-output { overflow: auto; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "5beb9f84bf9c471493e7e4b798ad29fe",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(HBox(), EncodingWidget(children=(VBox(children=(HTML(value='Encoding:'), Dropdown(description='…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "d390e95a832c4a67b68ba514b50ee5e4",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Output()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "insert into \n",
    "    ${TABLE_NAME}\n",
    "select \n",
    "    review_id, \n",
    "    star_rating, \n",
    "    review_body, \n",
    "    review_date, \n",
    "    year,\n",
    "    unix_timestamp(current_timestamp()) as timestamp,\n",
    "    mod(crc32(review_id), 2) as parity\n",
    "from\n",
    "    reviews\n",
    "where\n",
    "    year = 2003;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "53d697ae-e73b-4218-a5f9-78c5f71c0d8e",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:35:54.686363Z",
     "iopub.status.busy": "2023-03-19T02:35:54.686138Z",
     "iopub.status.idle": "2023-03-19T02:36:09.947339Z",
     "shell.execute_reply": "2023-03-19T02:36:09.946504Z",
     "shell.execute_reply.started": "2023-03-19T02:35:54.686341Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "[ TIMELINE ]\n",
      "\n",
      "╔═════╤═══════════════════╤════════╤═══════════╤═════════════╤═════════════╤═════════════╗\n",
      "║ No. │ Instant           │ Action │ State     │ Requested   │ Inflight    │ Completed   ║\n",
      "║     │                   │        │           │ Time        │ Time        │ Time        ║\n",
      "╠═════╪═══════════════════╪════════╪═══════════╪═════════════╪═════════════╪═════════════╣\n",
      "║ 0   │ 20230319023511699 │ commit │ COMPLETED │ 03-19 02:35 │ 03-19 02:35 │ 03-19 02:35 ║\n",
      "╚═════╧═══════════════════╧════════╧═══════════╧═════════════╧═════════════╧═════════════╝\n",
      "\n",
      "[ COMMITS ]\n",
      "\n",
      "╔═══════════════════╤═════════════════════╤═══════════════════╤═════════════════════╤══════════════════════════╤═══════════════════════╤══════════════════════════════╤══════════════╗\n",
      "║ CommitTime        │ Total Bytes Written │ Total Files Added │ Total Files Updated │ Total Partitions Written │ Total Records Written │ Total Update Records Written │ Total Errors ║\n",
      "╠═══════════════════╪═════════════════════╪═══════════════════╪═════════════════════╪══════════════════════════╪═══════════════════════╪══════════════════════════════╪══════════════╣\n",
      "║ 20230319023511699 │ 191.6 MB            │ 2                 │ 0                   │ 2                        │ 1155127               │ 0                            │ 0            ║\n",
      "╚═══════════════════╧═════════════════════╧═══════════════════╧═════════════════════╧══════════════════════════╧═══════════════════════╧══════════════════════════════╧══════════════╝\n",
      "\n",
      "[ STORAGE ]\n",
      "\n",
      "/home/emr-notebook/apache-hudi-core-conceptions/reviews_cow_clustering_2\n",
      "├── [ 96M 02:36:09]  parity=0\n",
      "│   └── [ 96M 02:35:45]  ccd39a23-9805-48c1-84f9-3ee7b51d337d-0_0-217-2975_20230319023511699.parquet\n",
      "└── [ 96M 02:36:09]  parity=1\n",
      "    └── [ 96M 02:35:45]  2aab727c-ee25-47f4-a4b1-95660d9f67f0-0_1-217-2976_20230319023511699.parquet\n"
     ]
    }
   ],
   "source": [
    "%%sh\n",
    "${WORKSPACE}/hudi-stat.sh s3://${S3_BUCKET}/${TABLE_NAME} timeline commits storage"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "331f3396-e9e8-4cf4-9c7d-aeda1173b7f0",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 3.6. Insert 213MB / +1 Max File +1 Small File"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "86502862-dfe3-46f2-b0db-a0a0850409dc",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:36:09.948890Z",
     "iopub.status.busy": "2023-03-19T02:36:09.948639Z",
     "iopub.status.idle": "2023-03-19T02:37:11.468653Z",
     "shell.execute_reply": "2023-03-19T02:37:11.467238Z",
     "shell.execute_reply.started": "2023-03-19T02:36:09.948864Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<style>.jp-OutputArea-child .jupyter-widgets.jp-OutputArea-output { overflow: auto; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "9d604dcee1e3441fa405827fc9a99d9d",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(HBox(), EncodingWidget(children=(VBox(children=(HTML(value='Encoding:'), Dropdown(description='…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "869167780c4c48e19ce2a9d26904c2f0",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Output()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "insert into \n",
    "    ${TABLE_NAME}\n",
    "select \n",
    "    review_id, \n",
    "    star_rating, \n",
    "    review_body, \n",
    "    review_date, \n",
    "    year,\n",
    "    unix_timestamp(current_timestamp()) as timestamp,\n",
    "    mod(crc32(review_id), 2) as parity\n",
    "from\n",
    "    reviews\n",
    "where\n",
    "    year in (2004, 2005);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "f17d5f39-654f-4b30-bc94-9ac668b7cc48",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:37:11.470264Z",
     "iopub.status.busy": "2023-03-19T02:37:11.469912Z",
     "iopub.status.idle": "2023-03-19T02:37:28.676765Z",
     "shell.execute_reply": "2023-03-19T02:37:28.675899Z",
     "shell.execute_reply.started": "2023-03-19T02:37:11.470230Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "[ TIMELINE ]\n",
      "\n",
      "╔═════╤═══════════════════╤════════╤═══════════╤═════════════╤═════════════╤═════════════╗\n",
      "║ No. │ Instant           │ Action │ State     │ Requested   │ Inflight    │ Completed   ║\n",
      "║     │                   │        │           │ Time        │ Time        │ Time        ║\n",
      "╠═════╪═══════════════════╪════════╪═══════════╪═════════════╪═════════════╪═════════════╣\n",
      "║ 0   │ 20230319023511699 │ commit │ COMPLETED │ 03-19 02:35 │ 03-19 02:35 │ 03-19 02:35 ║\n",
      "╟─────┼───────────────────┼────────┼───────────┼─────────────┼─────────────┼─────────────╢\n",
      "║ 1   │ 20230319023610998 │ commit │ COMPLETED │ 03-19 02:36 │ 03-19 02:36 │ 03-19 02:37 ║\n",
      "╚═════╧═══════════════════╧════════╧═══════════╧═════════════╧═════════════╧═════════════╝\n",
      "\n",
      "[ COMMITS ]\n",
      "\n",
      "╔═══════════════════╤═════════════════════╤═══════════════════╤═════════════════════╤══════════════════════════╤═══════════════════════╤══════════════════════════════╤══════════════╗\n",
      "║ CommitTime        │ Total Bytes Written │ Total Files Added │ Total Files Updated │ Total Partitions Written │ Total Records Written │ Total Update Records Written │ Total Errors ║\n",
      "╠═══════════════════╪═════════════════════╪═══════════════════╪═════════════════════╪══════════════════════════╪═══════════════════════╪══════════════════════════════╪══════════════╣\n",
      "║ 20230319023610998 │ 426.7 MB            │ 4                 │ 0                   │ 2                        │ 2570430               │ 0                            │ 0            ║\n",
      "╟───────────────────┼─────────────────────┼───────────────────┼─────────────────────┼──────────────────────────┼───────────────────────┼──────────────────────────────┼──────────────╢\n",
      "║ 20230319023511699 │ 191.6 MB            │ 2                 │ 0                   │ 2                        │ 1155127               │ 0                            │ 0            ║\n",
      "╚═══════════════════╧═════════════════════╧═══════════════════╧═════════════════════╧══════════════════════════╧═══════════════════════╧══════════════════════════════╧══════════════╝\n",
      "\n",
      "[ STORAGE ]\n",
      "\n",
      "/home/emr-notebook/apache-hudi-core-conceptions/reviews_cow_clustering_2\n",
      "├── [309M 02:37:28]  parity=0\n",
      "│   ├── [ 96M 02:35:45]  ccd39a23-9805-48c1-84f9-3ee7b51d337d-0_0-217-2975_20230319023511699.parquet\n",
      "│   ├── [120M 02:37:01]  e1113e10-f3f6-47d2-8d16-e87247e1de52-0_0-264-3674_20230319023610998.parquet\n",
      "│   └── [ 93M 02:36:56]  ede547da-a234-405d-b5b9-793f16e3d5f5-0_1-264-3675_20230319023610998.parquet\n",
      "└── [309M 02:37:28]  parity=1\n",
      "    ├── [ 96M 02:35:45]  2aab727c-ee25-47f4-a4b1-95660d9f67f0-0_1-217-2976_20230319023511699.parquet\n",
      "    ├── [120M 02:37:01]  9b076783-eb57-4c75-9029-955aa746d565-0_2-264-3676_20230319023610998.parquet\n",
      "    └── [ 94M 02:36:57]  a9f1643b-2c8d-405b-8170-3e6ced3b51b8-0_3-264-3677_20230319023610998.parquet\n"
     ]
    }
   ],
   "source": [
    "%%sh\n",
    "${WORKSPACE}/hudi-stat.sh s3://${S3_BUCKET}/${TABLE_NAME} timeline commits storage"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1e098aa2-c590-4b28-9935-3994cc7e3e83",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 3.7. Insert 182MB / +1 Max File +1 Small File"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "4bd5a1d7-b6bf-4e8a-9b27-5a316819dd54",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:37:28.678320Z",
     "iopub.status.busy": "2023-03-19T02:37:28.678032Z",
     "iopub.status.idle": "2023-03-19T02:38:32.348105Z",
     "shell.execute_reply": "2023-03-19T02:38:32.347388Z",
     "shell.execute_reply.started": "2023-03-19T02:37:28.678275Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<style>.jp-OutputArea-child .jupyter-widgets.jp-OutputArea-output { overflow: auto; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "f01c004ef00f40e99bdc62ebb153c7ec",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(HBox(), EncodingWidget(children=(VBox(children=(HTML(value='Encoding:'), Dropdown(description='…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "16c0bfb69ec242c1b26aa25b276fbdea",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Output()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "insert into \n",
    "    ${TABLE_NAME}\n",
    "select \n",
    "    review_id, \n",
    "    star_rating, \n",
    "    review_body, \n",
    "    review_date, \n",
    "    year,\n",
    "    unix_timestamp(current_timestamp()) as timestamp,\n",
    "    mod(crc32(review_id), 2) as parity\n",
    "from\n",
    "    reviews\n",
    "where\n",
    "    year = 2007;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "5079341a-0a03-4398-8ad8-c67ef7a07b85",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:38:32.349221Z",
     "iopub.status.busy": "2023-03-19T02:38:32.349002Z",
     "iopub.status.idle": "2023-03-19T02:38:48.756103Z",
     "shell.execute_reply": "2023-03-19T02:38:48.755257Z",
     "shell.execute_reply.started": "2023-03-19T02:38:32.349199Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "[ TIMELINE ]\n",
      "\n",
      "╔═════╤═══════════════════╤════════╤═══════════╤═════════════╤═════════════╤═════════════╗\n",
      "║ No. │ Instant           │ Action │ State     │ Requested   │ Inflight    │ Completed   ║\n",
      "║     │                   │        │           │ Time        │ Time        │ Time        ║\n",
      "╠═════╪═══════════════════╪════════╪═══════════╪═════════════╪═════════════╪═════════════╣\n",
      "║ 0   │ 20230319023511699 │ commit │ COMPLETED │ 03-19 02:35 │ 03-19 02:35 │ 03-19 02:35 ║\n",
      "╟─────┼───────────────────┼────────┼───────────┼─────────────┼─────────────┼─────────────╢\n",
      "║ 1   │ 20230319023610998 │ commit │ COMPLETED │ 03-19 02:36 │ 03-19 02:36 │ 03-19 02:37 ║\n",
      "╟─────┼───────────────────┼────────┼───────────┼─────────────┼─────────────┼─────────────╢\n",
      "║ 2   │ 20230319023729768 │ commit │ COMPLETED │ 03-19 02:37 │ 03-19 02:37 │ 03-19 02:38 ║\n",
      "╚═════╧═══════════════════╧════════╧═══════════╧═════════════╧═════════════╧═════════════╝\n",
      "\n",
      "[ COMMITS ]\n",
      "\n",
      "╔═══════════════════╤═════════════════════╤═══════════════════╤═════════════════════╤══════════════════════════╤═══════════════════════╤══════════════════════════════╤══════════════╗\n",
      "║ CommitTime        │ Total Bytes Written │ Total Files Added │ Total Files Updated │ Total Partitions Written │ Total Records Written │ Total Update Records Written │ Total Errors ║\n",
      "╠═══════════════════╪═════════════════════╪═══════════════════╪═════════════════════╪══════════════════════════╪═══════════════════════╪══════════════════════════════╪══════════════╣\n",
      "║ 20230319023729768 │ 364.2 MB            │ 4                 │ 0                   │ 2                        │ 2194714               │ 0                            │ 0            ║\n",
      "╟───────────────────┼─────────────────────┼───────────────────┼─────────────────────┼──────────────────────────┼───────────────────────┼──────────────────────────────┼──────────────╢\n",
      "║ 20230319023610998 │ 426.7 MB            │ 4                 │ 0                   │ 2                        │ 2570430               │ 0                            │ 0            ║\n",
      "╟───────────────────┼─────────────────────┼───────────────────┼─────────────────────┼──────────────────────────┼───────────────────────┼──────────────────────────────┼──────────────╢\n",
      "║ 20230319023511699 │ 191.6 MB            │ 2                 │ 0                   │ 2                        │ 1155127               │ 0                            │ 0            ║\n",
      "╚═══════════════════╧═════════════════════╧═══════════════════╧═════════════════════╧══════════════════════════╧═══════════════════════╧══════════════════════════════╧══════════════╝\n",
      "\n",
      "[ STORAGE ]\n",
      "\n",
      "/home/emr-notebook/apache-hudi-core-conceptions/reviews_cow_clustering_2\n",
      "├── [491M 02:38:48]  parity=0\n",
      "│   ├── [ 63M 02:38:11]  04ec1882-303e-4ed9-be92-9d8f1a4f8bb9-0_1-314-4413_20230319023729768.parquet\n",
      "│   ├── [119M 02:38:21]  443ee080-865a-43d8-9977-aa7f57bd9111-0_0-314-4412_20230319023729768.parquet\n",
      "│   ├── [ 96M 02:35:45]  ccd39a23-9805-48c1-84f9-3ee7b51d337d-0_0-217-2975_20230319023511699.parquet\n",
      "│   ├── [120M 02:37:01]  e1113e10-f3f6-47d2-8d16-e87247e1de52-0_0-264-3674_20230319023610998.parquet\n",
      "│   └── [ 93M 02:36:56]  ede547da-a234-405d-b5b9-793f16e3d5f5-0_1-264-3675_20230319023610998.parquet\n",
      "└── [491M 02:38:48]  parity=1\n",
      "    ├── [ 96M 02:35:45]  2aab727c-ee25-47f4-a4b1-95660d9f67f0-0_1-217-2976_20230319023511699.parquet\n",
      "    ├── [ 63M 02:38:11]  72a43254-638c-430e-adb6-44861cebf14e-0_3-314-4415_20230319023729768.parquet\n",
      "    ├── [120M 02:37:01]  9b076783-eb57-4c75-9029-955aa746d565-0_2-264-3676_20230319023610998.parquet\n",
      "    ├── [ 94M 02:36:57]  a9f1643b-2c8d-405b-8170-3e6ced3b51b8-0_3-264-3677_20230319023610998.parquet\n",
      "    └── [119M 02:38:21]  ea0cbc10-634f-4a50-bed9-bebfec4721b8-0_2-314-4414_20230319023729768.parquet\n"
     ]
    }
   ],
   "source": [
    "%%sh\n",
    "${WORKSPACE}/hudi-stat.sh s3://${S3_BUCKET}/${TABLE_NAME} timeline commits storage"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0e56f987-da37-4cc5-9aa4-bfbabc80c2dd",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 3.8. Offline Schedule"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "38f967c8-5dd4-419f-b35b-37aa7007c9e9",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:38:48.757697Z",
     "iopub.status.busy": "2023-03-19T02:38:48.757412Z",
     "iopub.status.idle": "2023-03-19T02:39:13.221011Z",
     "shell.execute_reply": "2023-03-19T02:39:13.220104Z",
     "shell.execute_reply.started": "2023-03-19T02:38:48.757671Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%sh\n",
    "# it's required for current user (emr-notebook) to get sudo permission\n",
    "sudo -u hadoop spark-submit \\\n",
    "  --jars '/usr/lib/hudi/hudi-spark-bundle.jar' \\\n",
    "  --class 'org.apache.hudi.utilities.HoodieClusteringJob' \\\n",
    "  /usr/lib/hudi/hudi-utilities-bundle.jar \\\n",
    "  --spark-memory '4g' \\\n",
    "  --mode 'schedule' \\\n",
    "  --base-path \"s3://${S3_BUCKET}/${TABLE_NAME}\" \\\n",
    "  --table-name \"${TABLE_NAME}\" \\\n",
    "  --hoodie-conf \"hoodie.clustering.async.enabled=true\" \\\n",
    "  --hoodie-conf \"hoodie.clustering.async.max.commits=3\" \\\n",
    "  --hoodie-conf \"hoodie.clustering.plan.strategy.small.file.limit=209715200\" \\\n",
    "  --hoodie-conf \"hoodie.clustering.plan.strategy.target.file.max.bytes=314572800\" \\\n",
    "  --hoodie-conf \"hoodie.clustering.plan.strategy.sort.columns=review_date\" > ${WORKSPACE}/${TABLE_NAME}.schedule.out &>/dev/null"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "54846a2b-c826-48c6-8e8c-6b03cb05064f",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:39:13.225004Z",
     "iopub.status.busy": "2023-03-19T02:39:13.224749Z",
     "iopub.status.idle": "2023-03-19T02:39:28.433318Z",
     "shell.execute_reply": "2023-03-19T02:39:28.432553Z",
     "shell.execute_reply.started": "2023-03-19T02:39:13.224977Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "[ TIMELINE ]\n",
      "\n",
      "╔═════╤═══════════════════╤═══════════════╤═══════════╤═════════════╤═════════════╤═════════════╗\n",
      "║ No. │ Instant           │ Action        │ State     │ Requested   │ Inflight    │ Completed   ║\n",
      "║     │                   │               │           │ Time        │ Time        │ Time        ║\n",
      "╠═════╪═══════════════════╪═══════════════╪═══════════╪═════════════╪═════════════╪═════════════╣\n",
      "║ 0   │ 20230319023511699 │ commit        │ COMPLETED │ 03-19 02:35 │ 03-19 02:35 │ 03-19 02:35 ║\n",
      "╟─────┼───────────────────┼───────────────┼───────────┼─────────────┼─────────────┼─────────────╢\n",
      "║ 1   │ 20230319023610998 │ commit        │ COMPLETED │ 03-19 02:36 │ 03-19 02:36 │ 03-19 02:37 ║\n",
      "╟─────┼───────────────────┼───────────────┼───────────┼─────────────┼─────────────┼─────────────╢\n",
      "║ 2   │ 20230319023729768 │ commit        │ COMPLETED │ 03-19 02:37 │ 03-19 02:37 │ 03-19 02:38 ║\n",
      "╟─────┼───────────────────┼───────────────┼───────────┼─────────────┼─────────────┼─────────────╢\n",
      "║ 3   │ 20230319023905713 │ replacecommit │ REQUESTED │ 03-19 02:39 │ -           │ -           ║\n",
      "╚═════╧═══════════════════╧═══════════════╧═══════════╧═════════════╧═════════════╧═════════════╝\n",
      "\n",
      "[ COMMITS ]\n",
      "\n",
      "╔═══════════════════╤═════════════════════╤═══════════════════╤═════════════════════╤══════════════════════════╤═══════════════════════╤══════════════════════════════╤══════════════╗\n",
      "║ CommitTime        │ Total Bytes Written │ Total Files Added │ Total Files Updated │ Total Partitions Written │ Total Records Written │ Total Update Records Written │ Total Errors ║\n",
      "╠═══════════════════╪═════════════════════╪═══════════════════╪═════════════════════╪══════════════════════════╪═══════════════════════╪══════════════════════════════╪══════════════╣\n",
      "║ 20230319023729768 │ 364.2 MB            │ 4                 │ 0                   │ 2                        │ 2194714               │ 0                            │ 0            ║\n",
      "╟───────────────────┼─────────────────────┼───────────────────┼─────────────────────┼──────────────────────────┼───────────────────────┼──────────────────────────────┼──────────────╢\n",
      "║ 20230319023610998 │ 426.7 MB            │ 4                 │ 0                   │ 2                        │ 2570430               │ 0                            │ 0            ║\n",
      "╟───────────────────┼─────────────────────┼───────────────────┼─────────────────────┼──────────────────────────┼───────────────────────┼──────────────────────────────┼──────────────╢\n",
      "║ 20230319023511699 │ 191.6 MB            │ 2                 │ 0                   │ 2                        │ 1155127               │ 0                            │ 0            ║\n",
      "╚═══════════════════╧═════════════════════╧═══════════════════╧═════════════════════╧══════════════════════════╧═══════════════════════╧══════════════════════════════╧══════════════╝\n",
      "\n",
      "[ STORAGE ]\n",
      "\n",
      "/home/emr-notebook/apache-hudi-core-conceptions/reviews_cow_clustering_2\n",
      "├── [491M 02:38:48]  parity=0\n",
      "│   ├── [ 63M 02:38:11]  04ec1882-303e-4ed9-be92-9d8f1a4f8bb9-0_1-314-4413_20230319023729768.parquet\n",
      "│   ├── [119M 02:38:21]  443ee080-865a-43d8-9977-aa7f57bd9111-0_0-314-4412_20230319023729768.parquet\n",
      "│   ├── [ 96M 02:35:45]  ccd39a23-9805-48c1-84f9-3ee7b51d337d-0_0-217-2975_20230319023511699.parquet\n",
      "│   ├── [120M 02:37:01]  e1113e10-f3f6-47d2-8d16-e87247e1de52-0_0-264-3674_20230319023610998.parquet\n",
      "│   └── [ 93M 02:36:56]  ede547da-a234-405d-b5b9-793f16e3d5f5-0_1-264-3675_20230319023610998.parquet\n",
      "└── [491M 02:38:48]  parity=1\n",
      "    ├── [ 96M 02:35:45]  2aab727c-ee25-47f4-a4b1-95660d9f67f0-0_1-217-2976_20230319023511699.parquet\n",
      "    ├── [ 63M 02:38:11]  72a43254-638c-430e-adb6-44861cebf14e-0_3-314-4415_20230319023729768.parquet\n",
      "    ├── [120M 02:37:01]  9b076783-eb57-4c75-9029-955aa746d565-0_2-264-3676_20230319023610998.parquet\n",
      "    ├── [ 94M 02:36:57]  a9f1643b-2c8d-405b-8170-3e6ced3b51b8-0_3-264-3677_20230319023610998.parquet\n",
      "    └── [119M 02:38:21]  ea0cbc10-634f-4a50-bed9-bebfec4721b8-0_2-314-4414_20230319023729768.parquet\n"
     ]
    }
   ],
   "source": [
    "%%sh\n",
    "${WORKSPACE}/hudi-stat.sh s3://${S3_BUCKET}/${TABLE_NAME} timeline commits storage"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4d3095c2-2ee1-4aa0-9f91-4205e9865b89",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 3.9. Offline Eexecute 491MB / +2 Clustered Files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "68836317-7694-4e78-a80b-105cbdbd9251",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:39:28.435202Z",
     "iopub.status.busy": "2023-03-19T02:39:28.434857Z",
     "iopub.status.idle": "2023-03-19T02:42:15.229045Z",
     "shell.execute_reply": "2023-03-19T02:42:15.228178Z",
     "shell.execute_reply.started": "2023-03-19T02:39:28.435164Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%sh\n",
    "# no need to provide --hoodie-conf setting again for execute, they are only used for schedule\n",
    "# for execute, if there are requested replacecommits in timeline, they will be executed via this job.\n",
    "# it's required for current user (emr-notebook) to get sudo permission\n",
    "sudo -u hadoop spark-submit \\\n",
    "  --jars '/usr/lib/hudi/hudi-spark-bundle.jar' \\\n",
    "  --class 'org.apache.hudi.utilities.HoodieClusteringJob' \\\n",
    "  /usr/lib/hudi/hudi-utilities-bundle.jar \\\n",
    "  --spark-memory '4g' \\\n",
    "  --mode 'execute' \\\n",
    "  --base-path \"s3://${S3_BUCKET}/${TABLE_NAME}\" \\\n",
    "  --table-name \"${TABLE_NAME}\" > ${WORKSPACE}/${TABLE_NAME}.execute.out &>/dev/null"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "2e63ca8f-a1ad-4f64-bb63-58d39cceb348",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:42:15.230636Z",
     "iopub.status.busy": "2023-03-19T02:42:15.230357Z",
     "iopub.status.idle": "2023-03-19T02:42:35.553798Z",
     "shell.execute_reply": "2023-03-19T02:42:35.552896Z",
     "shell.execute_reply.started": "2023-03-19T02:42:15.230609Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "[ TIMELINE ]\n",
      "\n",
      "╔═════╤═══════════════════╤═══════════════╤═══════════╤═════════════╤═════════════╤═════════════╗\n",
      "║ No. │ Instant           │ Action        │ State     │ Requested   │ Inflight    │ Completed   ║\n",
      "║     │                   │               │           │ Time        │ Time        │ Time        ║\n",
      "╠═════╪═══════════════════╪═══════════════╪═══════════╪═════════════╪═════════════╪═════════════╣\n",
      "║ 0   │ 20230319023511699 │ commit        │ COMPLETED │ 03-19 02:35 │ 03-19 02:35 │ 03-19 02:35 ║\n",
      "╟─────┼───────────────────┼───────────────┼───────────┼─────────────┼─────────────┼─────────────╢\n",
      "║ 1   │ 20230319023610998 │ commit        │ COMPLETED │ 03-19 02:36 │ 03-19 02:36 │ 03-19 02:37 ║\n",
      "╟─────┼───────────────────┼───────────────┼───────────┼─────────────┼─────────────┼─────────────╢\n",
      "║ 2   │ 20230319023729768 │ commit        │ COMPLETED │ 03-19 02:37 │ 03-19 02:37 │ 03-19 02:38 ║\n",
      "╟─────┼───────────────────┼───────────────┼───────────┼─────────────┼─────────────┼─────────────╢\n",
      "║ 3   │ 20230319023905713 │ replacecommit │ COMPLETED │ 03-19 02:39 │ 03-19 02:39 │ 03-19 02:42 ║\n",
      "╚═════╧═══════════════════╧═══════════════╧═══════════╧═════════════╧═════════════╧═════════════╝\n",
      "\n",
      "[ COMMITS ]\n",
      "\n",
      "╔═══════════════════╤═════════════════════╤═══════════════════╤═════════════════════╤══════════════════════════╤═══════════════════════╤══════════════════════════════╤══════════════╗\n",
      "║ CommitTime        │ Total Bytes Written │ Total Files Added │ Total Files Updated │ Total Partitions Written │ Total Records Written │ Total Update Records Written │ Total Errors ║\n",
      "╠═══════════════════╪═════════════════════╪═══════════════════╪═════════════════════╪══════════════════════════╪═══════════════════════╪══════════════════════════════╪══════════════╣\n",
      "║ 20230319023905713 │ 978.4 MB            │ 4                 │ 0                   │ 2                        │ 5920271               │ 0                            │ 0            ║\n",
      "╟───────────────────┼─────────────────────┼───────────────────┼─────────────────────┼──────────────────────────┼───────────────────────┼──────────────────────────────┼──────────────╢\n",
      "║ 20230319023729768 │ 364.2 MB            │ 4                 │ 0                   │ 2                        │ 2194714               │ 0                            │ 0            ║\n",
      "╟───────────────────┼─────────────────────┼───────────────────┼─────────────────────┼──────────────────────────┼───────────────────────┼──────────────────────────────┼──────────────╢\n",
      "║ 20230319023610998 │ 426.7 MB            │ 4                 │ 0                   │ 2                        │ 2570430               │ 0                            │ 0            ║\n",
      "╟───────────────────┼─────────────────────┼───────────────────┼─────────────────────┼──────────────────────────┼───────────────────────┼──────────────────────────────┼──────────────╢\n",
      "║ 20230319023511699 │ 191.6 MB            │ 2                 │ 0                   │ 2                        │ 1155127               │ 0                            │ 0            ║\n",
      "╚═══════════════════╧═════════════════════╧═══════════════════╧═════════════════════╧══════════════════════════╧═══════════════════════╧══════════════════════════════╧══════════════╝\n",
      "\n",
      "[ STORAGE ]\n",
      "\n",
      "/home/emr-notebook/apache-hudi-core-conceptions/reviews_cow_clustering_2\n",
      "├── [980M 02:42:35]  parity=0\n",
      "│   ├── [ 63M 02:38:11]  04ec1882-303e-4ed9-be92-9d8f1a4f8bb9-0_1-314-4413_20230319023729768.parquet\n",
      "│   ├── [228M 02:41:09]  2ff9af42-bbc5-4ed2-9060-3b8ede592ea0-0_0-4-20_20230319023905713.parquet\n",
      "│   ├── [119M 02:38:21]  443ee080-865a-43d8-9977-aa7f57bd9111-0_0-314-4412_20230319023729768.parquet\n",
      "│   ├── [261M 02:41:09]  65b2764f-77d1-42c8-bf2d-0055d2ad6fc8-0_1-4-21_20230319023905713.parquet\n",
      "│   ├── [ 96M 02:35:45]  ccd39a23-9805-48c1-84f9-3ee7b51d337d-0_0-217-2975_20230319023511699.parquet\n",
      "│   ├── [120M 02:37:01]  e1113e10-f3f6-47d2-8d16-e87247e1de52-0_0-264-3674_20230319023610998.parquet\n",
      "│   └── [ 93M 02:36:56]  ede547da-a234-405d-b5b9-793f16e3d5f5-0_1-264-3675_20230319023610998.parquet\n",
      "└── [981M 02:42:35]  parity=1\n",
      "    ├── [ 96M 02:35:45]  2aab727c-ee25-47f4-a4b1-95660d9f67f0-0_1-217-2976_20230319023511699.parquet\n",
      "    ├── [231M 02:41:08]  33870246-a3bc-4669-b3b1-a9c0209a9cb7-0_3-4-23_20230319023905713.parquet\n",
      "    ├── [ 63M 02:38:11]  72a43254-638c-430e-adb6-44861cebf14e-0_3-314-4415_20230319023729768.parquet\n",
      "    ├── [120M 02:37:01]  9b076783-eb57-4c75-9029-955aa746d565-0_2-264-3676_20230319023610998.parquet\n",
      "    ├── [ 94M 02:36:57]  a9f1643b-2c8d-405b-8170-3e6ced3b51b8-0_3-264-3677_20230319023610998.parquet\n",
      "    ├── [258M 02:41:08]  be8e9d45-142f-4a7b-a780-b22de873f9c8-0_2-4-22_20230319023905713.parquet\n",
      "    └── [119M 02:38:21]  ea0cbc10-634f-4a50-bed9-bebfec4721b8-0_2-314-4414_20230319023729768.parquet\n"
     ]
    }
   ],
   "source": [
    "%%sh\n",
    "${WORKSPACE}/hudi-stat.sh s3://${S3_BUCKET}/${TABLE_NAME} timeline commits storage"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "004697ae-2c51-4107-9bf0-60b0713fe6be",
   "metadata": {
    "tags": []
   },
   "source": [
    "## 4. Test Case 3 - Async Clustering ( Offline Schedule + Offline Execute )"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6d8be26b-fbe2-4be3-b228-9c559b1d7f40",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 4.1. Test Plan\n",
    "\n",
    "Step No.|Action|Volume / Partition |Storage\n",
    ":--------:|:------|:------|:----------\n",
    "1|Insert|96MB|+1 Small File\n",
    "2|Insert|213MB|+1 Max File +1 Small File\n",
    "3|Insert|182MB|+1 Max File +1 Small File\n",
    "4|Offline Schedule + Eexecute|491MB|+2 Clustered Files"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a1e7d641-3d45-4b84-9a77-16edf6f416a6",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 4.2. Key Settings\n",
    "\n",
    "KEY|DEFAULT VALUE|SET VALUE\n",
    ":---|:---|:---\n",
    "hoodie.clustering.inline|false|false\n",
    "hoodie.clustering.schedule.inline|false|false\n",
    "hoodie.clustering.async.enabled|false|true\n",
    "hoodie.clustering.async.max.commits|4|3\n",
    "hoodie.clustering.plan.strategy.target.file.max.bytes|1073741824 / 1GB|314572800 / 300MB\n",
    "hoodie.clustering.plan.strategy.small.file.limit|314572800 / 300MB|209715200 / 200MB\n",
    "hoodie.clustering.plan.strategy.sort.columns|-|review_date\n",
    "hoodie.parquet.small.file.limit|104857600 / 100MB | 0\n",
    "hoodie.copyonwrite.record.size.estimate|1024|175"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1fb7dc06-61d1-43b4-a43e-c7cd9ff2adb6",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-02-25T02:57:45.037101Z",
     "iopub.status.busy": "2023-02-25T02:57:45.036681Z",
     "iopub.status.idle": "2023-02-25T02:58:07.353493Z",
     "shell.execute_reply": "2023-02-25T02:58:07.352596Z",
     "shell.execute_reply.started": "2023-02-25T02:57:45.037060Z"
    },
    "tags": []
   },
   "source": [
    "### 4.3. Set Variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "c3a03aa0-ed08-4ece-90ee-8a04d33b0a1e",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:42:35.555840Z",
     "iopub.status.busy": "2023-03-19T02:42:35.555415Z",
     "iopub.status.idle": "2023-03-19T02:42:43.012540Z",
     "shell.execute_reply": "2023-03-19T02:42:43.011773Z",
     "shell.execute_reply.started": "2023-03-19T02:42:35.555742Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<style>.jp-OutputArea-child .jupyter-widgets.jp-OutputArea-output { overflow: auto; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "17f46a2dc84243a19cc854910743280a",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(HBox(children=(HTML(value='Type:'), Button(description='Table', layout=Layout(width='70px'), st…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "d32f405f39264c13ad9f63f536adaa97",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Output()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "set TABLE_NAME=reviews_cow_clustering_3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "66ea5a4a-f5a6-44ca-9ddd-344522c1cc88",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:42:43.014144Z",
     "iopub.status.busy": "2023-03-19T02:42:43.013843Z",
     "iopub.status.idle": "2023-03-19T02:42:43.018723Z",
     "shell.execute_reply": "2023-03-19T02:42:43.017975Z",
     "shell.execute_reply.started": "2023-03-19T02:42:43.014113Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "env: TABLE_NAME=reviews_cow_clustering_3\n"
     ]
    }
   ],
   "source": [
    "%env TABLE_NAME=reviews_cow_clustering_3"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2f20cdee-1a1f-44bf-be57-2bfbf6aeab7a",
   "metadata": {},
   "source": [
    "### 4.4. Create Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "36fc8a4e-423c-472a-85bf-c464b099c930",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:42:43.020093Z",
     "iopub.status.busy": "2023-03-19T02:42:43.019802Z",
     "iopub.status.idle": "2023-03-19T02:42:49.080472Z",
     "shell.execute_reply": "2023-03-19T02:42:49.079504Z",
     "shell.execute_reply.started": "2023-03-19T02:42:43.020063Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%sh\n",
    "aws s3 rm s3://${S3_BUCKET}/${TABLE_NAME} --recursive &>/dev/null\n",
    "rm -rf ${WORKSPACE}/${TABLE_NAME}\n",
    "sleep 5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "c6477a30-4af3-4abe-9219-9324c924afa0",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:42:49.083190Z",
     "iopub.status.busy": "2023-03-19T02:42:49.082925Z",
     "iopub.status.idle": "2023-03-19T02:42:49.916592Z",
     "shell.execute_reply": "2023-03-19T02:42:49.915914Z",
     "shell.execute_reply.started": "2023-03-19T02:42:49.083163Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<style>.jp-OutputArea-child .jupyter-widgets.jp-OutputArea-output { overflow: auto; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "c834955c99f2499da45bc096d61698f9",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(HBox(), EncodingWidget(children=(VBox(children=(HTML(value='Encoding:'), Dropdown(description='…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "1d62cb5b54a040c18bdc4320c57f82a2",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Output()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "drop table if exists ${TABLE_NAME}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "fafa88ef-e062-4adf-86bf-564528715b3e",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:42:49.917790Z",
     "iopub.status.busy": "2023-03-19T02:42:49.917552Z",
     "iopub.status.idle": "2023-03-19T02:42:52.258419Z",
     "shell.execute_reply": "2023-03-19T02:42:52.257599Z",
     "shell.execute_reply.started": "2023-03-19T02:42:49.917767Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<style>.jp-OutputArea-child .jupyter-widgets.jp-OutputArea-output { overflow: auto; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "01dfb35fc7224f45b31971f9a50daf43",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(HBox(), EncodingWidget(children=(VBox(children=(HTML(value='Encoding:'), Dropdown(description='…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "68abb4b6516548caa7345777860c921d",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Output()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "-- for async mode: \n",
    "-- 1. some clustering properties works as table properties, i.e. hoodie.clustering.async.enabled and hoodie.clustering.async.max.commits\n",
    "-- 2. all clustering async & plan properties will be overwritten by spark job level configuration via --hoodie-conf or --props\n",
    "-- so, to be simple, do NOT set any clustering-ralated properties on table, always set by --hoodie-conf or --props\n",
    "-- so, comment out clustering related settings, just keep them as reference.\n",
    "create table if not exists ${TABLE_NAME}(\n",
    "    review_id string, \n",
    "    star_rating int, \n",
    "    review_body string, \n",
    "    review_date date, \n",
    "    year long,\n",
    "    timestamp long,\n",
    "    parity int\n",
    ")\n",
    "using hudi\n",
    "location 's3://${S3_BUCKET}/${TABLE_NAME}'\n",
    "partitioned by (parity)\n",
    "options ( \n",
    "    type = 'cow',  \n",
    "    primaryKey = 'review_id', \n",
    "    preCombineField = 'timestamp',\n",
    "    -- hoodie.clustering.inline = 'false',\n",
    "    -- hoodie.clustering.schedule.inline = 'false',\n",
    "    -- hoodie.clustering.async.enabled = 'true',\n",
    "    -- hoodie.clustering.async.max.commits = '3',\n",
    "    -- hoodie.clustering.plan.strategy.small.file.limit = '209715200',\n",
    "    -- hoodie.clustering.plan.strategy.target.file.max.bytes = '314572800',\n",
    "    -- hoodie.clustering.plan.strategy.sort.columns = 'review_date',\n",
    "    hoodie.parquet.small.file.limit = '0',\n",
    "    hoodie.copyonwrite.record.size.estimate = '175'\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0f800040-bb4f-41c6-8612-0beda2fae8fa",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 4.5. Insert 96MB / +1 Small File"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "38c23a76-db2b-425f-bee3-eac06216cd62",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:42:52.260080Z",
     "iopub.status.busy": "2023-03-19T02:42:52.259728Z",
     "iopub.status.idle": "2023-03-19T02:43:49.862936Z",
     "shell.execute_reply": "2023-03-19T02:43:49.862288Z",
     "shell.execute_reply.started": "2023-03-19T02:42:52.260046Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<style>.jp-OutputArea-child .jupyter-widgets.jp-OutputArea-output { overflow: auto; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "63475fa2d04043dbabcffce7b507c090",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(HBox(), EncodingWidget(children=(VBox(children=(HTML(value='Encoding:'), Dropdown(description='…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "b3aa2461ae1a4760a8d9bbf24774effd",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Output()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "insert into \n",
    "    ${TABLE_NAME}\n",
    "select \n",
    "    review_id, \n",
    "    star_rating, \n",
    "    review_body, \n",
    "    review_date, \n",
    "    year,\n",
    "    unix_timestamp(current_timestamp()) as timestamp,\n",
    "    mod(crc32(review_id), 2) as parity\n",
    "from\n",
    "    reviews\n",
    "where\n",
    "    year = 2003;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "ade7d5bb-85b0-47d3-9df1-9e4402377472",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:43:49.864112Z",
     "iopub.status.busy": "2023-03-19T02:43:49.863882Z",
     "iopub.status.idle": "2023-03-19T02:44:04.969892Z",
     "shell.execute_reply": "2023-03-19T02:44:04.969043Z",
     "shell.execute_reply.started": "2023-03-19T02:43:49.864090Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "[ TIMELINE ]\n",
      "\n",
      "╔═════╤═══════════════════╤════════╤═══════════╤═════════════╤═════════════╤═════════════╗\n",
      "║ No. │ Instant           │ Action │ State     │ Requested   │ Inflight    │ Completed   ║\n",
      "║     │                   │        │           │ Time        │ Time        │ Time        ║\n",
      "╠═════╪═══════════════════╪════════╪═══════════╪═════════════╪═════════════╪═════════════╣\n",
      "║ 0   │ 20230319024252739 │ commit │ COMPLETED │ 03-19 02:42 │ 03-19 02:43 │ 03-19 02:43 ║\n",
      "╚═════╧═══════════════════╧════════╧═══════════╧═════════════╧═════════════╧═════════════╝\n",
      "\n",
      "[ COMMITS ]\n",
      "\n",
      "╔═══════════════════╤═════════════════════╤═══════════════════╤═════════════════════╤══════════════════════════╤═══════════════════════╤══════════════════════════════╤══════════════╗\n",
      "║ CommitTime        │ Total Bytes Written │ Total Files Added │ Total Files Updated │ Total Partitions Written │ Total Records Written │ Total Update Records Written │ Total Errors ║\n",
      "╠═══════════════════╪═════════════════════╪═══════════════════╪═════════════════════╪══════════════════════════╪═══════════════════════╪══════════════════════════════╪══════════════╣\n",
      "║ 20230319024252739 │ 191.6 MB            │ 2                 │ 0                   │ 2                        │ 1155127               │ 0                            │ 0            ║\n",
      "╚═══════════════════╧═════════════════════╧═══════════════════╧═════════════════════╧══════════════════════════╧═══════════════════════╧══════════════════════════════╧══════════════╝\n",
      "\n",
      "[ STORAGE ]\n",
      "\n",
      "/home/emr-notebook/apache-hudi-core-conceptions/reviews_cow_clustering_3\n",
      "├── [ 96M 02:44:04]  parity=0\n",
      "│   └── [ 96M 02:43:38]  217941a5-c3d2-40d4-aa8f-e2e8410a217e-0_0-378-4567_20230319024252739.parquet\n",
      "└── [ 96M 02:44:04]  parity=1\n",
      "    └── [ 96M 02:43:38]  4dce02e4-5cc0-45d6-ab4b-2037c7ff652e-0_1-378-4568_20230319024252739.parquet\n"
     ]
    }
   ],
   "source": [
    "%%sh\n",
    "${WORKSPACE}/hudi-stat.sh s3://${S3_BUCKET}/${TABLE_NAME} timeline commits storage"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "20f1b7e1-9c98-48c6-9a01-c8c27dc359e1",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 4.6. Insert 213MB / +1 Max File +1 Small File"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "c4c7eb72-c2a3-4bc7-9b98-a835c3c7dd69",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:44:04.971432Z",
     "iopub.status.busy": "2023-03-19T02:44:04.971174Z",
     "iopub.status.idle": "2023-03-19T02:45:12.684624Z",
     "shell.execute_reply": "2023-03-19T02:45:12.683983Z",
     "shell.execute_reply.started": "2023-03-19T02:44:04.971405Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<style>.jp-OutputArea-child .jupyter-widgets.jp-OutputArea-output { overflow: auto; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "536f46bbd064402e9e0db81a00a7b875",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(HBox(), EncodingWidget(children=(VBox(children=(HTML(value='Encoding:'), Dropdown(description='…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "58d6b4267ff54d62b1e156719e448afa",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Output()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "insert into \n",
    "    ${TABLE_NAME}\n",
    "select \n",
    "    review_id, \n",
    "    star_rating, \n",
    "    review_body, \n",
    "    review_date, \n",
    "    year,\n",
    "    unix_timestamp(current_timestamp()) as timestamp,\n",
    "    mod(crc32(review_id), 2) as parity\n",
    "from\n",
    "    reviews\n",
    "where\n",
    "    year in (2004, 2005);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "f4efab55-9d8a-450a-a747-b2e496e03f8d",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:45:12.685927Z",
     "iopub.status.busy": "2023-03-19T02:45:12.685685Z",
     "iopub.status.idle": "2023-03-19T02:45:30.517305Z",
     "shell.execute_reply": "2023-03-19T02:45:30.516206Z",
     "shell.execute_reply.started": "2023-03-19T02:45:12.685905Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "[ TIMELINE ]\n",
      "\n",
      "╔═════╤═══════════════════╤════════╤═══════════╤═════════════╤═════════════╤═════════════╗\n",
      "║ No. │ Instant           │ Action │ State     │ Requested   │ Inflight    │ Completed   ║\n",
      "║     │                   │        │           │ Time        │ Time        │ Time        ║\n",
      "╠═════╪═══════════════════╪════════╪═══════════╪═════════════╪═════════════╪═════════════╣\n",
      "║ 0   │ 20230319024252739 │ commit │ COMPLETED │ 03-19 02:42 │ 03-19 02:43 │ 03-19 02:43 ║\n",
      "╟─────┼───────────────────┼────────┼───────────┼─────────────┼─────────────┼─────────────╢\n",
      "║ 1   │ 20230319024406012 │ commit │ COMPLETED │ 03-19 02:44 │ 03-19 02:44 │ 03-19 02:45 ║\n",
      "╚═════╧═══════════════════╧════════╧═══════════╧═════════════╧═════════════╧═════════════╝\n",
      "\n",
      "[ COMMITS ]\n",
      "\n",
      "╔═══════════════════╤═════════════════════╤═══════════════════╤═════════════════════╤══════════════════════════╤═══════════════════════╤══════════════════════════════╤══════════════╗\n",
      "║ CommitTime        │ Total Bytes Written │ Total Files Added │ Total Files Updated │ Total Partitions Written │ Total Records Written │ Total Update Records Written │ Total Errors ║\n",
      "╠═══════════════════╪═════════════════════╪═══════════════════╪═════════════════════╪══════════════════════════╪═══════════════════════╪══════════════════════════════╪══════════════╣\n",
      "║ 20230319024406012 │ 426.7 MB            │ 4                 │ 0                   │ 2                        │ 2570430               │ 0                            │ 0            ║\n",
      "╟───────────────────┼─────────────────────┼───────────────────┼─────────────────────┼──────────────────────────┼───────────────────────┼──────────────────────────────┼──────────────╢\n",
      "║ 20230319024252739 │ 191.6 MB            │ 2                 │ 0                   │ 2                        │ 1155127               │ 0                            │ 0            ║\n",
      "╚═══════════════════╧═════════════════════╧═══════════════════╧═════════════════════╧══════════════════════════╧═══════════════════════╧══════════════════════════════╧══════════════╝\n",
      "\n",
      "[ STORAGE ]\n",
      "\n",
      "/home/emr-notebook/apache-hudi-core-conceptions/reviews_cow_clustering_3\n",
      "├── [309M 02:45:30]  parity=0\n",
      "│   ├── [120M 02:45:02]  0f79f5eb-a973-4de6-83ee-7111c273288c-0_0-425-4836_20230319024406012.parquet\n",
      "│   ├── [ 93M 02:44:59]  1988ad3b-e4e2-44ae-8861-8e443a9c9039-0_1-425-4837_20230319024406012.parquet\n",
      "│   └── [ 96M 02:43:38]  217941a5-c3d2-40d4-aa8f-e2e8410a217e-0_0-378-4567_20230319024252739.parquet\n",
      "└── [309M 02:45:30]  parity=1\n",
      "    ├── [ 94M 02:44:57]  231f30f4-91cd-4539-9a0f-d85c46d962b4-0_3-425-4839_20230319024406012.parquet\n",
      "    ├── [ 96M 02:43:38]  4dce02e4-5cc0-45d6-ab4b-2037c7ff652e-0_1-378-4568_20230319024252739.parquet\n",
      "    └── [120M 02:45:03]  aff51fa4-52ec-4e6a-9115-8c8108de2af1-0_2-425-4838_20230319024406012.parquet\n"
     ]
    }
   ],
   "source": [
    "%%sh\n",
    "${WORKSPACE}/hudi-stat.sh s3://${S3_BUCKET}/${TABLE_NAME} timeline commits storage"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "40debeda-12fb-4781-b44c-0d72ff19f8ff",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 4.7. Insert 182MB / +1 Max File +1 Small File"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "e5fcb991-264a-4966-a235-285fafdc5422",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:45:30.518958Z",
     "iopub.status.busy": "2023-03-19T02:45:30.518662Z",
     "iopub.status.idle": "2023-03-19T02:46:32.126795Z",
     "shell.execute_reply": "2023-03-19T02:46:32.125961Z",
     "shell.execute_reply.started": "2023-03-19T02:45:30.518920Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<style>.jp-OutputArea-child .jupyter-widgets.jp-OutputArea-output { overflow: auto; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "684572ae72cc4295a4abe105d02a34ac",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(HBox(), EncodingWidget(children=(VBox(children=(HTML(value='Encoding:'), Dropdown(description='…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "ac74b1b07ff8450c93102d397fb0e7fd",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Output()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "insert into \n",
    "    ${TABLE_NAME}\n",
    "select \n",
    "    review_id, \n",
    "    star_rating, \n",
    "    review_body, \n",
    "    review_date, \n",
    "    year,\n",
    "    unix_timestamp(current_timestamp()) as timestamp,\n",
    "    mod(crc32(review_id), 2) as parity\n",
    "from\n",
    "    reviews\n",
    "where\n",
    "    year = 2007;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "87b77ce1-04db-4118-b133-7e4850def52a",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:46:32.127990Z",
     "iopub.status.busy": "2023-03-19T02:46:32.127738Z",
     "iopub.status.idle": "2023-03-19T02:46:48.417629Z",
     "shell.execute_reply": "2023-03-19T02:46:48.416796Z",
     "shell.execute_reply.started": "2023-03-19T02:46:32.127968Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "[ TIMELINE ]\n",
      "\n",
      "╔═════╤═══════════════════╤════════╤═══════════╤═════════════╤═════════════╤═════════════╗\n",
      "║ No. │ Instant           │ Action │ State     │ Requested   │ Inflight    │ Completed   ║\n",
      "║     │                   │        │           │ Time        │ Time        │ Time        ║\n",
      "╠═════╪═══════════════════╪════════╪═══════════╪═════════════╪═════════════╪═════════════╣\n",
      "║ 0   │ 20230319024252739 │ commit │ COMPLETED │ 03-19 02:42 │ 03-19 02:43 │ 03-19 02:43 ║\n",
      "╟─────┼───────────────────┼────────┼───────────┼─────────────┼─────────────┼─────────────╢\n",
      "║ 1   │ 20230319024406012 │ commit │ COMPLETED │ 03-19 02:44 │ 03-19 02:44 │ 03-19 02:45 ║\n",
      "╟─────┼───────────────────┼────────┼───────────┼─────────────┼─────────────┼─────────────╢\n",
      "║ 2   │ 20230319024531489 │ commit │ COMPLETED │ 03-19 02:45 │ 03-19 02:45 │ 03-19 02:46 ║\n",
      "╚═════╧═══════════════════╧════════╧═══════════╧═════════════╧═════════════╧═════════════╝\n",
      "\n",
      "[ COMMITS ]\n",
      "\n",
      "╔═══════════════════╤═════════════════════╤═══════════════════╤═════════════════════╤══════════════════════════╤═══════════════════════╤══════════════════════════════╤══════════════╗\n",
      "║ CommitTime        │ Total Bytes Written │ Total Files Added │ Total Files Updated │ Total Partitions Written │ Total Records Written │ Total Update Records Written │ Total Errors ║\n",
      "╠═══════════════════╪═════════════════════╪═══════════════════╪═════════════════════╪══════════════════════════╪═══════════════════════╪══════════════════════════════╪══════════════╣\n",
      "║ 20230319024531489 │ 364.2 MB            │ 4                 │ 0                   │ 2                        │ 2194714               │ 0                            │ 0            ║\n",
      "╟───────────────────┼─────────────────────┼───────────────────┼─────────────────────┼──────────────────────────┼───────────────────────┼──────────────────────────────┼──────────────╢\n",
      "║ 20230319024406012 │ 426.7 MB            │ 4                 │ 0                   │ 2                        │ 2570430               │ 0                            │ 0            ║\n",
      "╟───────────────────┼─────────────────────┼───────────────────┼─────────────────────┼──────────────────────────┼───────────────────────┼──────────────────────────────┼──────────────╢\n",
      "║ 20230319024252739 │ 191.6 MB            │ 2                 │ 0                   │ 2                        │ 1155127               │ 0                            │ 0            ║\n",
      "╚═══════════════════╧═════════════════════╧═══════════════════╧═════════════════════╧══════════════════════════╧═══════════════════════╧══════════════════════════════╧══════════════╝\n",
      "\n",
      "[ STORAGE ]\n",
      "\n",
      "/home/emr-notebook/apache-hudi-core-conceptions/reviews_cow_clustering_3\n",
      "├── [491M 02:46:48]  parity=0\n",
      "│   ├── [120M 02:45:02]  0f79f5eb-a973-4de6-83ee-7111c273288c-0_0-425-4836_20230319024406012.parquet\n",
      "│   ├── [ 93M 02:44:59]  1988ad3b-e4e2-44ae-8861-8e443a9c9039-0_1-425-4837_20230319024406012.parquet\n",
      "│   ├── [ 96M 02:43:38]  217941a5-c3d2-40d4-aa8f-e2e8410a217e-0_0-378-4567_20230319024252739.parquet\n",
      "│   ├── [ 63M 02:46:14]  271e32d7-ddd9-4b88-8a05-3b2a623c8245-0_1-475-5116_20230319024531489.parquet\n",
      "│   └── [119M 02:46:21]  42bade86-b0f7-46c4-a9e6-ea42988a4417-0_0-475-5115_20230319024531489.parquet\n",
      "└── [491M 02:46:48]  parity=1\n",
      "    ├── [ 94M 02:44:57]  231f30f4-91cd-4539-9a0f-d85c46d962b4-0_3-425-4839_20230319024406012.parquet\n",
      "    ├── [ 96M 02:43:38]  4dce02e4-5cc0-45d6-ab4b-2037c7ff652e-0_1-378-4568_20230319024252739.parquet\n",
      "    ├── [119M 02:46:21]  549031ef-7621-46c9-b7a8-18dfedf3bd6b-0_2-475-5117_20230319024531489.parquet\n",
      "    ├── [120M 02:45:03]  aff51fa4-52ec-4e6a-9115-8c8108de2af1-0_2-425-4838_20230319024406012.parquet\n",
      "    └── [ 63M 02:46:12]  f9a704d3-7105-482a-a41a-a3b9346e0525-0_3-475-5118_20230319024531489.parquet\n"
     ]
    }
   ],
   "source": [
    "%%sh\n",
    "${WORKSPACE}/hudi-stat.sh s3://${S3_BUCKET}/${TABLE_NAME} timeline commits storage"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e81e2c4d-ef6a-4786-ac61-e57bb3750dab",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 4.8. Offline Schedule + Eexecute 491MB / +2 Clustered Files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "68c9f1ad-346b-408d-afc6-72203763c32c",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:46:48.419098Z",
     "iopub.status.busy": "2023-03-19T02:46:48.418839Z",
     "iopub.status.idle": "2023-03-19T02:49:51.172717Z",
     "shell.execute_reply": "2023-03-19T02:49:51.171836Z",
     "shell.execute_reply.started": "2023-03-19T02:46:48.419063Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%sh\n",
    "# it's required for current user (emr-notebook) to get sudo permission\n",
    "sudo -u hadoop spark-submit \\\n",
    "  --jars '/usr/lib/hudi/hudi-spark-bundle.jar' \\\n",
    "  --class 'org.apache.hudi.utilities.HoodieClusteringJob' \\\n",
    "  /usr/lib/hudi/hudi-utilities-bundle.jar \\\n",
    "  --spark-memory '4g' \\\n",
    "  --mode 'scheduleAndExecute' \\\n",
    "  --base-path \"s3://${S3_BUCKET}/${TABLE_NAME}\" \\\n",
    "  --table-name \"${TABLE_NAME}\" \\\n",
    "  --hoodie-conf \"hoodie.clustering.async.enabled=true\" \\\n",
    "  --hoodie-conf \"hoodie.clustering.async.max.commits=3\" \\\n",
    "  --hoodie-conf \"hoodie.clustering.plan.strategy.small.file.limit=209715200\" \\\n",
    "  --hoodie-conf \"hoodie.clustering.plan.strategy.target.file.max.bytes=314572800\" \\\n",
    "  --hoodie-conf \"hoodie.clustering.plan.strategy.sort.columns=review_date\" > ${WORKSPACE}/${TABLE_NAME}.schedule.out &>/dev/null"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "faa167dd-fff0-4500-9139-e0d21c296d9a",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:49:51.174244Z",
     "iopub.status.busy": "2023-03-19T02:49:51.173954Z",
     "iopub.status.idle": "2023-03-19T02:50:11.086499Z",
     "shell.execute_reply": "2023-03-19T02:50:11.085633Z",
     "shell.execute_reply.started": "2023-03-19T02:49:51.174210Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "[ TIMELINE ]\n",
      "\n",
      "╔═════╤═══════════════════╤═══════════════╤═══════════╤═════════════╤═════════════╤═════════════╗\n",
      "║ No. │ Instant           │ Action        │ State     │ Requested   │ Inflight    │ Completed   ║\n",
      "║     │                   │               │           │ Time        │ Time        │ Time        ║\n",
      "╠═════╪═══════════════════╪═══════════════╪═══════════╪═════════════╪═════════════╪═════════════╣\n",
      "║ 0   │ 20230319024252739 │ commit        │ COMPLETED │ 03-19 02:42 │ 03-19 02:43 │ 03-19 02:43 ║\n",
      "╟─────┼───────────────────┼───────────────┼───────────┼─────────────┼─────────────┼─────────────╢\n",
      "║ 1   │ 20230319024406012 │ commit        │ COMPLETED │ 03-19 02:44 │ 03-19 02:44 │ 03-19 02:45 ║\n",
      "╟─────┼───────────────────┼───────────────┼───────────┼─────────────┼─────────────┼─────────────╢\n",
      "║ 2   │ 20230319024531489 │ commit        │ COMPLETED │ 03-19 02:45 │ 03-19 02:45 │ 03-19 02:46 ║\n",
      "╟─────┼───────────────────┼───────────────┼───────────┼─────────────┼─────────────┼─────────────╢\n",
      "║ 3   │ 20230319024707167 │ replacecommit │ COMPLETED │ 03-19 02:47 │ 03-19 02:47 │ 03-19 02:49 ║\n",
      "╚═════╧═══════════════════╧═══════════════╧═══════════╧═════════════╧═════════════╧═════════════╝\n",
      "\n",
      "[ COMMITS ]\n",
      "\n",
      "╔═══════════════════╤═════════════════════╤═══════════════════╤═════════════════════╤══════════════════════════╤═══════════════════════╤══════════════════════════════╤══════════════╗\n",
      "║ CommitTime        │ Total Bytes Written │ Total Files Added │ Total Files Updated │ Total Partitions Written │ Total Records Written │ Total Update Records Written │ Total Errors ║\n",
      "╠═══════════════════╪═════════════════════╪═══════════════════╪═════════════════════╪══════════════════════════╪═══════════════════════╪══════════════════════════════╪══════════════╣\n",
      "║ 20230319024707167 │ 978.4 MB            │ 4                 │ 0                   │ 2                        │ 5920271               │ 0                            │ 0            ║\n",
      "╟───────────────────┼─────────────────────┼───────────────────┼─────────────────────┼──────────────────────────┼───────────────────────┼──────────────────────────────┼──────────────╢\n",
      "║ 20230319024531489 │ 364.2 MB            │ 4                 │ 0                   │ 2                        │ 2194714               │ 0                            │ 0            ║\n",
      "╟───────────────────┼─────────────────────┼───────────────────┼─────────────────────┼──────────────────────────┼───────────────────────┼──────────────────────────────┼──────────────╢\n",
      "║ 20230319024406012 │ 426.7 MB            │ 4                 │ 0                   │ 2                        │ 2570430               │ 0                            │ 0            ║\n",
      "╟───────────────────┼─────────────────────┼───────────────────┼─────────────────────┼──────────────────────────┼───────────────────────┼──────────────────────────────┼──────────────╢\n",
      "║ 20230319024252739 │ 191.6 MB            │ 2                 │ 0                   │ 2                        │ 1155127               │ 0                            │ 0            ║\n",
      "╚═══════════════════╧═════════════════════╧═══════════════════╧═════════════════════╧══════════════════════════╧═══════════════════════╧══════════════════════════════╧══════════════╝\n",
      "\n",
      "[ STORAGE ]\n",
      "\n",
      "/home/emr-notebook/apache-hudi-core-conceptions/reviews_cow_clustering_3\n",
      "├── [980M 02:50:10]  parity=0\n",
      "│   ├── [120M 02:45:02]  0f79f5eb-a973-4de6-83ee-7111c273288c-0_0-425-4836_20230319024406012.parquet\n",
      "│   ├── [ 93M 02:44:59]  1988ad3b-e4e2-44ae-8861-8e443a9c9039-0_1-425-4837_20230319024406012.parquet\n",
      "│   ├── [ 96M 02:43:38]  217941a5-c3d2-40d4-aa8f-e2e8410a217e-0_0-378-4567_20230319024252739.parquet\n",
      "│   ├── [ 63M 02:46:14]  271e32d7-ddd9-4b88-8a05-3b2a623c8245-0_1-475-5116_20230319024531489.parquet\n",
      "│   ├── [119M 02:46:21]  42bade86-b0f7-46c4-a9e6-ea42988a4417-0_0-475-5115_20230319024531489.parquet\n",
      "│   ├── [275M 02:48:34]  858318da-8c8b-41dd-8ec1-90d379599057-0_0-5-22_20230319024707167.parquet\n",
      "│   └── [214M 02:48:29]  bc97e42d-4bfb-4b4d-83a0-3e8fe47d70c1-0_1-5-23_20230319024707167.parquet\n",
      "└── [981M 02:50:10]  parity=1\n",
      "    ├── [ 94M 02:44:57]  231f30f4-91cd-4539-9a0f-d85c46d962b4-0_3-425-4839_20230319024406012.parquet\n",
      "    ├── [ 96M 02:43:38]  4dce02e4-5cc0-45d6-ab4b-2037c7ff652e-0_1-378-4568_20230319024252739.parquet\n",
      "    ├── [119M 02:46:21]  549031ef-7621-46c9-b7a8-18dfedf3bd6b-0_2-475-5117_20230319024531489.parquet\n",
      "    ├── [257M 02:48:33]  77d50022-2064-4bc0-9081-0265f4f2644c-0_2-5-24_20230319024707167.parquet\n",
      "    ├── [120M 02:45:03]  aff51fa4-52ec-4e6a-9115-8c8108de2af1-0_2-425-4838_20230319024406012.parquet\n",
      "    ├── [233M 02:48:32]  e4c7227d-59b8-4dd8-8bcb-b0a732f2317d-0_3-5-25_20230319024707167.parquet\n",
      "    └── [ 63M 02:46:12]  f9a704d3-7105-482a-a41a-a3b9346e0525-0_3-475-5118_20230319024531489.parquet\n"
     ]
    }
   ],
   "source": [
    "%%sh\n",
    "${WORKSPACE}/hudi-stat.sh s3://${S3_BUCKET}/${TABLE_NAME} timeline commits storage"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c9bddac8-2e03-4cbb-8853-6833f63e96f4",
   "metadata": {
    "tags": []
   },
   "source": [
    "## 5. Test Case 4 - Semi Async Clustering ( Inline Schedule，Offline Execute )"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "819764fd-cd64-4f2a-8870-9ddf09341d04",
   "metadata": {},
   "source": [
    "NOTE:\n",
    "\n",
    "This mode has a problem, under this mode, both `hoodie.clustering.inline.max.commits` or `hoodie.clustering.async.max.commits` does NOT work. This behavior is defferent from comapcation"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a49f3d86-1eec-49be-9eb5-78c5064bce62",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 5.1. Test Plan\n",
    "\n",
    "Step No.|Action|Volume / Partition |Storage\n",
    ":--------:|:------|:------|:----------\n",
    "1|Insert|96MB|+1 Small File\n",
    "2|Insert|213MB|+1 Max File +1 Small File\n",
    "3|Insert|182MB|+1 Max File +1 Small File\n",
    "4|Offline Eexecute|96MB|+1 Clustered File\n",
    "5|Insert|14.6MB|+1 Small File\n",
    "6|Offline Eexecute|503MB|+2 Clustered Files"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2bc71f37-3a53-45ab-8eed-466899a2d01d",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 5.2. Key Settings\n",
    "\n",
    "KEY|DEFAULT VALUE|SET VALUE\n",
    ":---|:---|:---\n",
    "hoodie.clustering.inline|false|false\n",
    "hoodie.clustering.schedule.inline|false|true\n",
    "hoodie.clustering.async.enabled|false|false\n",
    "hoodie.clustering.plan.strategy.target.file.max.bytes|1073741824 / 1GB|314572800 / 300MB\n",
    "hoodie.clustering.plan.strategy.small.file.limit|314572800 / 300MB|209715200 / 200MB\n",
    "hoodie.clustering.plan.strategy.sort.columns|-|review_date\n",
    "hoodie.parquet.small.file.limit|104857600 / 100MB | 0\n",
    "hoodie.copyonwrite.record.size.estimate|1024|175"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "50fe4d6c-b78f-49d2-a524-d01b41ae5678",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-02-25T02:57:45.037101Z",
     "iopub.status.busy": "2023-02-25T02:57:45.036681Z",
     "iopub.status.idle": "2023-02-25T02:58:07.353493Z",
     "shell.execute_reply": "2023-02-25T02:58:07.352596Z",
     "shell.execute_reply.started": "2023-02-25T02:57:45.037060Z"
    },
    "tags": []
   },
   "source": [
    "### 5.3. Set Variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "0dab4969-c56e-4938-878d-dcc24c95e3a1",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:50:11.088559Z",
     "iopub.status.busy": "2023-03-19T02:50:11.088206Z",
     "iopub.status.idle": "2023-03-19T02:50:18.566722Z",
     "shell.execute_reply": "2023-03-19T02:50:18.565813Z",
     "shell.execute_reply.started": "2023-03-19T02:50:11.088519Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<style>.jp-OutputArea-child .jupyter-widgets.jp-OutputArea-output { overflow: auto; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "9af86653d4a74ddea8597851da92c8fe",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(HBox(children=(HTML(value='Type:'), Button(description='Table', layout=Layout(width='70px'), st…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "4b8f9fb451e44708b7064af00ccdcdc8",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Output()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "set TABLE_NAME=reviews_cow_clustering_4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "fa1dfb76-1755-439f-97bb-5a64e78aa86e",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:50:18.568104Z",
     "iopub.status.busy": "2023-03-19T02:50:18.567858Z",
     "iopub.status.idle": "2023-03-19T02:50:18.572530Z",
     "shell.execute_reply": "2023-03-19T02:50:18.571718Z",
     "shell.execute_reply.started": "2023-03-19T02:50:18.568082Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "env: TABLE_NAME=reviews_cow_clustering_4\n"
     ]
    }
   ],
   "source": [
    "%env TABLE_NAME=reviews_cow_clustering_4"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "85d90d6c-0615-479e-a60a-afef4764178d",
   "metadata": {},
   "source": [
    "### 5.4. Create Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "91677f08-c8d0-4055-b708-cf11d148b3a4",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:50:18.574104Z",
     "iopub.status.busy": "2023-03-19T02:50:18.573805Z",
     "iopub.status.idle": "2023-03-19T02:50:24.810006Z",
     "shell.execute_reply": "2023-03-19T02:50:24.808752Z",
     "shell.execute_reply.started": "2023-03-19T02:50:18.574074Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%sh\n",
    "aws s3 rm s3://${S3_BUCKET}/${TABLE_NAME} --recursive &>/dev/null\n",
    "rm -rf ${WORKSPACE}/${TABLE_NAME}\n",
    "sleep 5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "47091422-382f-4517-adab-7d7706f1e737",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:50:24.813046Z",
     "iopub.status.busy": "2023-03-19T02:50:24.812777Z",
     "iopub.status.idle": "2023-03-19T02:50:25.654072Z",
     "shell.execute_reply": "2023-03-19T02:50:25.653358Z",
     "shell.execute_reply.started": "2023-03-19T02:50:24.813014Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<style>.jp-OutputArea-child .jupyter-widgets.jp-OutputArea-output { overflow: auto; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "4b00fcec480049c8bddf93cdafe2cd15",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(HBox(), EncodingWidget(children=(VBox(children=(HTML(value='Encoding:'), Dropdown(description='…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "d38d066eeb484771b830f25c1a933c02",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Output()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "drop table if exists ${TABLE_NAME}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "92622bf2-688c-45b9-bb01-3fae12174b3d",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:50:25.655395Z",
     "iopub.status.busy": "2023-03-19T02:50:25.655131Z",
     "iopub.status.idle": "2023-03-19T02:50:27.977921Z",
     "shell.execute_reply": "2023-03-19T02:50:27.977074Z",
     "shell.execute_reply.started": "2023-03-19T02:50:25.655372Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<style>.jp-OutputArea-child .jupyter-widgets.jp-OutputArea-output { overflow: auto; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "f1e9d201762a45f08b9858c8b4552a07",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(HBox(), EncodingWidget(children=(VBox(children=(HTML(value='Encoding:'), Dropdown(description='…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "22c737314cef44429d5ce4a514a6fdbc",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Output()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "create table if not exists ${TABLE_NAME}(\n",
    "    review_id string, \n",
    "    star_rating int, \n",
    "    review_body string, \n",
    "    review_date date, \n",
    "    year long,\n",
    "    timestamp long,\n",
    "    parity int\n",
    ")\n",
    "using hudi\n",
    "location 's3://${S3_BUCKET}/${TABLE_NAME}'\n",
    "partitioned by (parity)\n",
    "options ( \n",
    "    type = 'cow',  \n",
    "    primaryKey = 'review_id', \n",
    "    preCombineField = 'timestamp',\n",
    "    hoodie.clustering.inline = 'false',\n",
    "    hoodie.clustering.schedule.inline = 'true',\n",
    "    hoodie.clustering.async.enabled = 'false',\n",
    "    -- NOT work for this mode\n",
    "    hoodie.clustering.inline.max.commits = '3',\n",
    "    -- NOT work for this mode\n",
    "    hoodie.clustering.async.max.commits = '3',\n",
    "    hoodie.clustering.plan.strategy.small.file.limit = '209715200',\n",
    "    hoodie.clustering.plan.strategy.target.file.max.bytes = '314572800',\n",
    "    hoodie.clustering.plan.strategy.sort.columns = 'review_date',\n",
    "    hoodie.parquet.small.file.limit = '0',\n",
    "    hoodie.copyonwrite.record.size.estimate = '175'\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "87b6f8a3-2835-41ed-aff0-9dab05e8b6c2",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 5.5. Insert 96MB / +1 Small File"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "e0701ba9-197a-4b64-8a28-1d58049f174f",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:50:27.979206Z",
     "iopub.status.busy": "2023-03-19T02:50:27.978982Z",
     "iopub.status.idle": "2023-03-19T02:51:27.453751Z",
     "shell.execute_reply": "2023-03-19T02:51:27.451617Z",
     "shell.execute_reply.started": "2023-03-19T02:50:27.979184Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<style>.jp-OutputArea-child .jupyter-widgets.jp-OutputArea-output { overflow: auto; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "53edb999bf6646baa722f610525f86a9",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(HBox(), EncodingWidget(children=(VBox(children=(HTML(value='Encoding:'), Dropdown(description='…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "aa268ead209c49d899ad443c9a0c81c0",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Output()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "insert into \n",
    "    ${TABLE_NAME}\n",
    "select \n",
    "    review_id, \n",
    "    star_rating, \n",
    "    review_body, \n",
    "    review_date, \n",
    "    year,\n",
    "    unix_timestamp(current_timestamp()) as timestamp,\n",
    "    mod(crc32(review_id), 2) as parity\n",
    "from\n",
    "    reviews\n",
    "where\n",
    "    year = 2003;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "c0d0ab36-69bb-42ed-8dcb-8cbef542ef88",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:51:27.455508Z",
     "iopub.status.busy": "2023-03-19T02:51:27.455166Z",
     "iopub.status.idle": "2023-03-19T02:51:42.956425Z",
     "shell.execute_reply": "2023-03-19T02:51:42.955560Z",
     "shell.execute_reply.started": "2023-03-19T02:51:27.455473Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "[ TIMELINE ]\n",
      "\n",
      "╔═════╤═══════════════════╤═══════════════╤═══════════╤═════════════╤═════════════╤═════════════╗\n",
      "║ No. │ Instant           │ Action        │ State     │ Requested   │ Inflight    │ Completed   ║\n",
      "║     │                   │               │           │ Time        │ Time        │ Time        ║\n",
      "╠═════╪═══════════════════╪═══════════════╪═══════════╪═════════════╪═════════════╪═════════════╣\n",
      "║ 0   │ 20230319025028437 │ commit        │ COMPLETED │ 03-19 02:50 │ 03-19 02:50 │ 03-19 02:51 ║\n",
      "╟─────┼───────────────────┼───────────────┼───────────┼─────────────┼─────────────┼─────────────╢\n",
      "║ 1   │ 20230319025121426 │ replacecommit │ REQUESTED │ 03-19 02:51 │ -           │ -           ║\n",
      "╚═════╧═══════════════════╧═══════════════╧═══════════╧═════════════╧═════════════╧═════════════╝\n",
      "\n",
      "[ COMMITS ]\n",
      "\n",
      "╔═══════════════════╤═════════════════════╤═══════════════════╤═════════════════════╤══════════════════════════╤═══════════════════════╤══════════════════════════════╤══════════════╗\n",
      "║ CommitTime        │ Total Bytes Written │ Total Files Added │ Total Files Updated │ Total Partitions Written │ Total Records Written │ Total Update Records Written │ Total Errors ║\n",
      "╠═══════════════════╪═════════════════════╪═══════════════════╪═════════════════════╪══════════════════════════╪═══════════════════════╪══════════════════════════════╪══════════════╣\n",
      "║ 20230319025028437 │ 191.6 MB            │ 2                 │ 0                   │ 2                        │ 1155127               │ 0                            │ 0            ║\n",
      "╚═══════════════════╧═════════════════════╧═══════════════════╧═════════════════════╧══════════════════════════╧═══════════════════════╧══════════════════════════════╧══════════════╝\n",
      "\n",
      "[ STORAGE ]\n",
      "\n",
      "/home/emr-notebook/apache-hudi-core-conceptions/reviews_cow_clustering_4\n",
      "├── [ 96M 02:51:42]  parity=0\n",
      "│   └── [ 96M 02:51:14]  4f7a3b4c-e36a-44f1-b2cd-f36f30367f07-0_0-539-5271_20230319025028437.parquet\n",
      "└── [ 96M 02:51:42]  parity=1\n",
      "    └── [ 96M 02:51:14]  706dbc1f-d3b0-45f0-95b6-33bc17a386f0-0_1-539-5272_20230319025028437.parquet\n"
     ]
    }
   ],
   "source": [
    "%%sh\n",
    "${WORKSPACE}/hudi-stat.sh s3://${S3_BUCKET}/${TABLE_NAME} timeline commits storage"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b17ff215-c6c6-46db-afe0-89ee101ceb80",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 5.6. Insert 213MB / +1 Max File +1 Small File"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "b465bffa-b354-402a-ab65-80bbb0a2f188",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:51:42.958003Z",
     "iopub.status.busy": "2023-03-19T02:51:42.957739Z",
     "iopub.status.idle": "2023-03-19T02:52:54.494127Z",
     "shell.execute_reply": "2023-03-19T02:52:54.493303Z",
     "shell.execute_reply.started": "2023-03-19T02:51:42.957973Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<style>.jp-OutputArea-child .jupyter-widgets.jp-OutputArea-output { overflow: auto; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "64f952a024364baca3130735ecdd9c83",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(HBox(), EncodingWidget(children=(VBox(children=(HTML(value='Encoding:'), Dropdown(description='…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "6c8a1e07c0ee46cca31dad46e0cc0047",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Output()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "insert into \n",
    "    ${TABLE_NAME}\n",
    "select \n",
    "    review_id, \n",
    "    star_rating, \n",
    "    review_body, \n",
    "    review_date, \n",
    "    year,\n",
    "    unix_timestamp(current_timestamp()) as timestamp,\n",
    "    mod(crc32(review_id), 2) as parity\n",
    "from\n",
    "    reviews\n",
    "where\n",
    "    year in (2004, 2005);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "245bc526-52c4-44d4-b70b-b55dce3c9c60",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:52:54.495719Z",
     "iopub.status.busy": "2023-03-19T02:52:54.495377Z",
     "iopub.status.idle": "2023-03-19T02:53:11.166825Z",
     "shell.execute_reply": "2023-03-19T02:53:11.165973Z",
     "shell.execute_reply.started": "2023-03-19T02:52:54.495682Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "[ TIMELINE ]\n",
      "\n",
      "╔═════╤═══════════════════╤═══════════════╤═══════════╤═════════════╤═════════════╤═════════════╗\n",
      "║ No. │ Instant           │ Action        │ State     │ Requested   │ Inflight    │ Completed   ║\n",
      "║     │                   │               │           │ Time        │ Time        │ Time        ║\n",
      "╠═════╪═══════════════════╪═══════════════╪═══════════╪═════════════╪═════════════╪═════════════╣\n",
      "║ 0   │ 20230319025028437 │ commit        │ COMPLETED │ 03-19 02:50 │ 03-19 02:50 │ 03-19 02:51 ║\n",
      "╟─────┼───────────────────┼───────────────┼───────────┼─────────────┼─────────────┼─────────────╢\n",
      "║ 1   │ 20230319025121426 │ replacecommit │ REQUESTED │ 03-19 02:51 │ -           │ -           ║\n",
      "╟─────┼───────────────────┼───────────────┼───────────┼─────────────┼─────────────┼─────────────╢\n",
      "║ 2   │ 20230319025144123 │ commit        │ COMPLETED │ 03-19 02:51 │ 03-19 02:52 │ 03-19 02:52 ║\n",
      "╚═════╧═══════════════════╧═══════════════╧═══════════╧═════════════╧═════════════╧═════════════╝\n",
      "\n",
      "[ COMMITS ]\n",
      "\n",
      "╔═══════════════════╤═════════════════════╤═══════════════════╤═════════════════════╤══════════════════════════╤═══════════════════════╤══════════════════════════════╤══════════════╗\n",
      "║ CommitTime        │ Total Bytes Written │ Total Files Added │ Total Files Updated │ Total Partitions Written │ Total Records Written │ Total Update Records Written │ Total Errors ║\n",
      "╠═══════════════════╪═════════════════════╪═══════════════════╪═════════════════════╪══════════════════════════╪═══════════════════════╪══════════════════════════════╪══════════════╣\n",
      "║ 20230319025144123 │ 426.7 MB            │ 4                 │ 0                   │ 2                        │ 2570430               │ 0                            │ 0            ║\n",
      "╟───────────────────┼─────────────────────┼───────────────────┼─────────────────────┼──────────────────────────┼───────────────────────┼──────────────────────────────┼──────────────╢\n",
      "║ 20230319025028437 │ 191.6 MB            │ 2                 │ 0                   │ 2                        │ 1155127               │ 0                            │ 0            ║\n",
      "╚═══════════════════╧═════════════════════╧═══════════════════╧═════════════════════╧══════════════════════════╧═══════════════════════╧══════════════════════════════╧══════════════╝\n",
      "\n",
      "[ STORAGE ]\n",
      "\n",
      "/home/emr-notebook/apache-hudi-core-conceptions/reviews_cow_clustering_4\n",
      "├── [309M 02:53:11]  parity=0\n",
      "│   ├── [120M 02:52:42]  059ec60c-0ad9-4bc7-a7ff-ea072c3e38ae-0_0-593-5588_20230319025144123.parquet\n",
      "│   ├── [ 93M 02:52:36]  2b7af39a-fa93-4886-b8b3-c87ac1993208-0_1-593-5589_20230319025144123.parquet\n",
      "│   └── [ 96M 02:51:14]  4f7a3b4c-e36a-44f1-b2cd-f36f30367f07-0_0-539-5271_20230319025028437.parquet\n",
      "└── [309M 02:53:11]  parity=1\n",
      "    ├── [ 94M 02:52:39]  0a1b5de2-f2f1-4e57-b1eb-78edd21a5fdb-0_3-593-5591_20230319025144123.parquet\n",
      "    ├── [ 96M 02:51:14]  706dbc1f-d3b0-45f0-95b6-33bc17a386f0-0_1-539-5272_20230319025028437.parquet\n",
      "    └── [120M 02:52:42]  e57207f5-b309-4da0-a84e-1ec865d4f845-0_2-593-5590_20230319025144123.parquet\n"
     ]
    }
   ],
   "source": [
    "%%sh\n",
    "${WORKSPACE}/hudi-stat.sh s3://${S3_BUCKET}/${TABLE_NAME} timeline commits storage"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b8d20628-f332-48d8-8590-02f0bb9b24c1",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 5.7. Insert 182MB / +1 Max File +1 Small File"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "bb1f461a-5ff4-44d2-a4f9-6f0fb2ff3a29",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:53:11.168359Z",
     "iopub.status.busy": "2023-03-19T02:53:11.168106Z",
     "iopub.status.idle": "2023-03-19T02:54:14.710809Z",
     "shell.execute_reply": "2023-03-19T02:54:14.710064Z",
     "shell.execute_reply.started": "2023-03-19T02:53:11.168332Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<style>.jp-OutputArea-child .jupyter-widgets.jp-OutputArea-output { overflow: auto; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "126aaa7cd7b24dbc9f3fc11e05194c0f",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(HBox(), EncodingWidget(children=(VBox(children=(HTML(value='Encoding:'), Dropdown(description='…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "b685426e1e4b42a990ebd20778d6e2bc",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Output()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "insert into \n",
    "    ${TABLE_NAME}\n",
    "select \n",
    "    review_id, \n",
    "    star_rating, \n",
    "    review_body, \n",
    "    review_date, \n",
    "    year,\n",
    "    unix_timestamp(current_timestamp()) as timestamp,\n",
    "    mod(crc32(review_id), 2) as parity\n",
    "from\n",
    "    reviews\n",
    "where\n",
    "    year = 2007;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "4f1adcf2-235a-483c-97c9-a90015921c18",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:54:14.712387Z",
     "iopub.status.busy": "2023-03-19T02:54:14.712066Z",
     "iopub.status.idle": "2023-03-19T02:54:31.972322Z",
     "shell.execute_reply": "2023-03-19T02:54:31.971341Z",
     "shell.execute_reply.started": "2023-03-19T02:54:14.712354Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "[ TIMELINE ]\n",
      "\n",
      "╔═════╤═══════════════════╤═══════════════╤═══════════╤═════════════╤═════════════╤═════════════╗\n",
      "║ No. │ Instant           │ Action        │ State     │ Requested   │ Inflight    │ Completed   ║\n",
      "║     │                   │               │           │ Time        │ Time        │ Time        ║\n",
      "╠═════╪═══════════════════╪═══════════════╪═══════════╪═════════════╪═════════════╪═════════════╣\n",
      "║ 0   │ 20230319025028437 │ commit        │ COMPLETED │ 03-19 02:50 │ 03-19 02:50 │ 03-19 02:51 ║\n",
      "╟─────┼───────────────────┼───────────────┼───────────┼─────────────┼─────────────┼─────────────╢\n",
      "║ 1   │ 20230319025121426 │ replacecommit │ REQUESTED │ 03-19 02:51 │ -           │ -           ║\n",
      "╟─────┼───────────────────┼───────────────┼───────────┼─────────────┼─────────────┼─────────────╢\n",
      "║ 2   │ 20230319025144123 │ commit        │ COMPLETED │ 03-19 02:51 │ 03-19 02:52 │ 03-19 02:52 ║\n",
      "╟─────┼───────────────────┼───────────────┼───────────┼─────────────┼─────────────┼─────────────╢\n",
      "║ 3   │ 20230319025312356 │ commit        │ COMPLETED │ 03-19 02:53 │ 03-19 02:53 │ 03-19 02:54 ║\n",
      "╚═════╧═══════════════════╧═══════════════╧═══════════╧═════════════╧═════════════╧═════════════╝\n",
      "\n",
      "[ COMMITS ]\n",
      "\n",
      "╔═══════════════════╤═════════════════════╤═══════════════════╤═════════════════════╤══════════════════════════╤═══════════════════════╤══════════════════════════════╤══════════════╗\n",
      "║ CommitTime        │ Total Bytes Written │ Total Files Added │ Total Files Updated │ Total Partitions Written │ Total Records Written │ Total Update Records Written │ Total Errors ║\n",
      "╠═══════════════════╪═════════════════════╪═══════════════════╪═════════════════════╪══════════════════════════╪═══════════════════════╪══════════════════════════════╪══════════════╣\n",
      "║ 20230319025312356 │ 364.3 MB            │ 4                 │ 0                   │ 2                        │ 2194714               │ 0                            │ 0            ║\n",
      "╟───────────────────┼─────────────────────┼───────────────────┼─────────────────────┼──────────────────────────┼───────────────────────┼──────────────────────────────┼──────────────╢\n",
      "║ 20230319025144123 │ 426.7 MB            │ 4                 │ 0                   │ 2                        │ 2570430               │ 0                            │ 0            ║\n",
      "╟───────────────────┼─────────────────────┼───────────────────┼─────────────────────┼──────────────────────────┼───────────────────────┼──────────────────────────────┼──────────────╢\n",
      "║ 20230319025028437 │ 191.6 MB            │ 2                 │ 0                   │ 2                        │ 1155127               │ 0                            │ 0            ║\n",
      "╚═══════════════════╧═════════════════════╧═══════════════════╧═════════════════════╧══════════════════════════╧═══════════════════════╧══════════════════════════════╧══════════════╝\n",
      "\n",
      "[ STORAGE ]\n",
      "\n",
      "/home/emr-notebook/apache-hudi-core-conceptions/reviews_cow_clustering_4\n",
      "├── [491M 02:54:31]  parity=0\n",
      "│   ├── [ 63M 02:53:54]  032d90b1-2654-4e37-bd81-336cf83e2abd-0_1-649-5913_20230319025312356.parquet\n",
      "│   ├── [120M 02:52:42]  059ec60c-0ad9-4bc7-a7ff-ea072c3e38ae-0_0-593-5588_20230319025144123.parquet\n",
      "│   ├── [ 93M 02:52:36]  2b7af39a-fa93-4886-b8b3-c87ac1993208-0_1-593-5589_20230319025144123.parquet\n",
      "│   ├── [119M 02:54:03]  42cc2080-19c9-49ba-ab5d-d18dd72529bb-0_0-649-5912_20230319025312356.parquet\n",
      "│   └── [ 96M 02:51:14]  4f7a3b4c-e36a-44f1-b2cd-f36f30367f07-0_0-539-5271_20230319025028437.parquet\n",
      "└── [491M 02:54:31]  parity=1\n",
      "    ├── [ 94M 02:52:39]  0a1b5de2-f2f1-4e57-b1eb-78edd21a5fdb-0_3-593-5591_20230319025144123.parquet\n",
      "    ├── [ 96M 02:51:14]  706dbc1f-d3b0-45f0-95b6-33bc17a386f0-0_1-539-5272_20230319025028437.parquet\n",
      "    ├── [ 63M 02:53:54]  90e573a0-5d74-4208-ba7d-6a98c021b7c2-0_3-649-5915_20230319025312356.parquet\n",
      "    ├── [119M 02:54:04]  b0f81684-4978-4284-9b76-dc6e151c44fa-0_2-649-5914_20230319025312356.parquet\n",
      "    └── [120M 02:52:42]  e57207f5-b309-4da0-a84e-1ec865d4f845-0_2-593-5590_20230319025144123.parquet\n"
     ]
    }
   ],
   "source": [
    "%%sh\n",
    "${WORKSPACE}/hudi-stat.sh s3://${S3_BUCKET}/${TABLE_NAME} timeline commits storage"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a6495b74-f28d-4a5c-806e-81a85ab1ec8f",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 5.8. Offline Eexecute 96MB / +1 Clustered File"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "e4092073-0258-42ac-8e62-51f1e4412bee",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:54:31.973804Z",
     "iopub.status.busy": "2023-03-19T02:54:31.973554Z",
     "iopub.status.idle": "2023-03-19T02:55:55.585349Z",
     "shell.execute_reply": "2023-03-19T02:55:55.584535Z",
     "shell.execute_reply.started": "2023-03-19T02:54:31.973773Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%sh\n",
    "# it's required for current user (emr-notebook) to get sudo permission\n",
    "sudo -u hadoop spark-submit \\\n",
    "  --jars '/usr/lib/hudi/hudi-spark-bundle.jar' \\\n",
    "  --class 'org.apache.hudi.utilities.HoodieClusteringJob' \\\n",
    "  /usr/lib/hudi/hudi-utilities-bundle.jar \\\n",
    "  --spark-memory '4g' \\\n",
    "  --mode 'execute' \\\n",
    "  --base-path \"s3://${S3_BUCKET}/${TABLE_NAME}\" \\\n",
    "  --table-name \"${TABLE_NAME}\" > ${WORKSPACE}/${TABLE_NAME}.execute.out &>/dev/null"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "b39c694d-1d14-4139-ae3c-3afd0ad82f98",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:55:55.587216Z",
     "iopub.status.busy": "2023-03-19T02:55:55.586864Z",
     "iopub.status.idle": "2023-03-19T02:56:12.592132Z",
     "shell.execute_reply": "2023-03-19T02:56:12.591317Z",
     "shell.execute_reply.started": "2023-03-19T02:55:55.587174Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "[ TIMELINE ]\n",
      "\n",
      "╔═════╤═══════════════════╤═══════════════╤═══════════╤═════════════╤═════════════╤═════════════╗\n",
      "║ No. │ Instant           │ Action        │ State     │ Requested   │ Inflight    │ Completed   ║\n",
      "║     │                   │               │           │ Time        │ Time        │ Time        ║\n",
      "╠═════╪═══════════════════╪═══════════════╪═══════════╪═════════════╪═════════════╪═════════════╣\n",
      "║ 0   │ 20230319025028437 │ commit        │ COMPLETED │ 03-19 02:50 │ 03-19 02:50 │ 03-19 02:51 ║\n",
      "╟─────┼───────────────────┼───────────────┼───────────┼─────────────┼─────────────┼─────────────╢\n",
      "║ 1   │ 20230319025121426 │ replacecommit │ COMPLETED │ 03-19 02:51 │ 03-19 02:54 │ 03-19 02:55 ║\n",
      "╟─────┼───────────────────┼───────────────┼───────────┼─────────────┼─────────────┼─────────────╢\n",
      "║ 2   │ 20230319025144123 │ commit        │ COMPLETED │ 03-19 02:51 │ 03-19 02:52 │ 03-19 02:52 ║\n",
      "╟─────┼───────────────────┼───────────────┼───────────┼─────────────┼─────────────┼─────────────╢\n",
      "║ 3   │ 20230319025312356 │ commit        │ COMPLETED │ 03-19 02:53 │ 03-19 02:53 │ 03-19 02:54 ║\n",
      "╚═════╧═══════════════════╧═══════════════╧═══════════╧═════════════╧═════════════╧═════════════╝\n",
      "\n",
      "[ COMMITS ]\n",
      "\n",
      "╔═══════════════════╤═════════════════════╤═══════════════════╤═════════════════════╤══════════════════════════╤═══════════════════════╤══════════════════════════════╤══════════════╗\n",
      "║ CommitTime        │ Total Bytes Written │ Total Files Added │ Total Files Updated │ Total Partitions Written │ Total Records Written │ Total Update Records Written │ Total Errors ║\n",
      "╠═══════════════════╪═════════════════════╪═══════════════════╪═════════════════════╪══════════════════════════╪═══════════════════════╪══════════════════════════════╪══════════════╣\n",
      "║ 20230319025312356 │ 364.3 MB            │ 4                 │ 0                   │ 2                        │ 2194714               │ 0                            │ 0            ║\n",
      "╟───────────────────┼─────────────────────┼───────────────────┼─────────────────────┼──────────────────────────┼───────────────────────┼──────────────────────────────┼──────────────╢\n",
      "║ 20230319025144123 │ 426.7 MB            │ 4                 │ 0                   │ 2                        │ 2570430               │ 0                            │ 0            ║\n",
      "╟───────────────────┼─────────────────────┼───────────────────┼─────────────────────┼──────────────────────────┼───────────────────────┼──────────────────────────────┼──────────────╢\n",
      "║ 20230319025121426 │ 191.8 MB            │ 2                 │ 0                   │ 2                        │ 1155127               │ 0                            │ 0            ║\n",
      "╟───────────────────┼─────────────────────┼───────────────────┼─────────────────────┼──────────────────────────┼───────────────────────┼──────────────────────────────┼──────────────╢\n",
      "║ 20230319025028437 │ 191.6 MB            │ 2                 │ 0                   │ 2                        │ 1155127               │ 0                            │ 0            ║\n",
      "╚═══════════════════╧═════════════════════╧═══════════════════╧═════════════════════╧══════════════════════════╧═══════════════════════╧══════════════════════════════╧══════════════╝\n",
      "\n",
      "[ STORAGE ]\n",
      "\n",
      "/home/emr-notebook/apache-hudi-core-conceptions/reviews_cow_clustering_4\n",
      "├── [587M 02:56:12]  parity=0\n",
      "│   ├── [ 63M 02:53:54]  032d90b1-2654-4e37-bd81-336cf83e2abd-0_1-649-5913_20230319025312356.parquet\n",
      "│   ├── [120M 02:52:42]  059ec60c-0ad9-4bc7-a7ff-ea072c3e38ae-0_0-593-5588_20230319025144123.parquet\n",
      "│   ├── [ 96M 02:55:25]  0785d1f7-da29-4fbd-a8f1-a411b417d8a4-0_0-2-2_20230319025121426.parquet\n",
      "│   ├── [ 93M 02:52:36]  2b7af39a-fa93-4886-b8b3-c87ac1993208-0_1-593-5589_20230319025144123.parquet\n",
      "│   ├── [119M 02:54:03]  42cc2080-19c9-49ba-ab5d-d18dd72529bb-0_0-649-5912_20230319025312356.parquet\n",
      "│   └── [ 96M 02:51:14]  4f7a3b4c-e36a-44f1-b2cd-f36f30367f07-0_0-539-5271_20230319025028437.parquet\n",
      "└── [587M 02:56:12]  parity=1\n",
      "    ├── [ 94M 02:52:39]  0a1b5de2-f2f1-4e57-b1eb-78edd21a5fdb-0_3-593-5591_20230319025144123.parquet\n",
      "    ├── [ 96M 02:51:14]  706dbc1f-d3b0-45f0-95b6-33bc17a386f0-0_1-539-5272_20230319025028437.parquet\n",
      "    ├── [ 63M 02:53:54]  90e573a0-5d74-4208-ba7d-6a98c021b7c2-0_3-649-5915_20230319025312356.parquet\n",
      "    ├── [119M 02:54:04]  b0f81684-4978-4284-9b76-dc6e151c44fa-0_2-649-5914_20230319025312356.parquet\n",
      "    ├── [ 96M 02:55:25]  cb5e46b2-3f30-4d2c-92ea-225eb82d6dbc-0_1-2-3_20230319025121426.parquet\n",
      "    └── [120M 02:52:42]  e57207f5-b309-4da0-a84e-1ec865d4f845-0_2-593-5590_20230319025144123.parquet\n"
     ]
    }
   ],
   "source": [
    "%%sh\n",
    "${WORKSPACE}/hudi-stat.sh s3://${S3_BUCKET}/${TABLE_NAME} timeline commits storage"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a76e5171-487d-4130-887d-84f910cdf57c",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 5.9. Insert 14.6MB / +1 Small File"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "33cd47da-495c-41a1-9e66-1007fefbed82",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:56:12.593807Z",
     "iopub.status.busy": "2023-03-19T02:56:12.593542Z",
     "iopub.status.idle": "2023-03-19T02:56:58.209121Z",
     "shell.execute_reply": "2023-03-19T02:56:58.208327Z",
     "shell.execute_reply.started": "2023-03-19T02:56:12.593779Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<style>.jp-OutputArea-child .jupyter-widgets.jp-OutputArea-output { overflow: auto; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "e9ebaf3bc416496fa3a8c105bde41f4b",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(HBox(), EncodingWidget(children=(VBox(children=(HTML(value='Encoding:'), Dropdown(description='…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "383bce9ded9e45d79437ac6e32721e56",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Output()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "insert into \n",
    "    ${TABLE_NAME}\n",
    "select \n",
    "    review_id, \n",
    "    star_rating, \n",
    "    review_body, \n",
    "    review_date, \n",
    "    year,\n",
    "    unix_timestamp(current_timestamp()) as timestamp,\n",
    "    mod(crc32(review_id), 2) as parity\n",
    "from\n",
    "    reviews\n",
    "where\n",
    "    year = 1998;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "61207241-074c-4c1b-b75e-d50604732fae",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:56:58.210808Z",
     "iopub.status.busy": "2023-03-19T02:56:58.210461Z",
     "iopub.status.idle": "2023-03-19T02:57:12.546532Z",
     "shell.execute_reply": "2023-03-19T02:57:12.545720Z",
     "shell.execute_reply.started": "2023-03-19T02:56:58.210773Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "[ TIMELINE ]\n",
      "\n",
      "╔═════╤═══════════════════╤═══════════════╤═══════════╤═════════════╤═════════════╤═════════════╗\n",
      "║ No. │ Instant           │ Action        │ State     │ Requested   │ Inflight    │ Completed   ║\n",
      "║     │                   │               │           │ Time        │ Time        │ Time        ║\n",
      "╠═════╪═══════════════════╪═══════════════╪═══════════╪═════════════╪═════════════╪═════════════╣\n",
      "║ 0   │ 20230319025028437 │ commit        │ COMPLETED │ 03-19 02:50 │ 03-19 02:50 │ 03-19 02:51 ║\n",
      "╟─────┼───────────────────┼───────────────┼───────────┼─────────────┼─────────────┼─────────────╢\n",
      "║ 1   │ 20230319025121426 │ replacecommit │ COMPLETED │ 03-19 02:51 │ 03-19 02:54 │ 03-19 02:55 ║\n",
      "╟─────┼───────────────────┼───────────────┼───────────┼─────────────┼─────────────┼─────────────╢\n",
      "║ 2   │ 20230319025144123 │ commit        │ COMPLETED │ 03-19 02:51 │ 03-19 02:52 │ 03-19 02:52 ║\n",
      "╟─────┼───────────────────┼───────────────┼───────────┼─────────────┼─────────────┼─────────────╢\n",
      "║ 3   │ 20230319025312356 │ commit        │ COMPLETED │ 03-19 02:53 │ 03-19 02:53 │ 03-19 02:54 ║\n",
      "╟─────┼───────────────────┼───────────────┼───────────┼─────────────┼─────────────┼─────────────╢\n",
      "║ 4   │ 20230319025620022 │ commit        │ COMPLETED │ 03-19 02:56 │ 03-19 02:56 │ 03-19 02:56 ║\n",
      "╟─────┼───────────────────┼───────────────┼───────────┼─────────────┼─────────────┼─────────────╢\n",
      "║ 5   │ 20230319025652851 │ replacecommit │ REQUESTED │ 03-19 02:56 │ -           │ -           ║\n",
      "╚═════╧═══════════════════╧═══════════════╧═══════════╧═════════════╧═════════════╧═════════════╝\n",
      "\n",
      "[ COMMITS ]\n",
      "\n",
      "╔═══════════════════╤═════════════════════╤═══════════════════╤═════════════════════╤══════════════════════════╤═══════════════════════╤══════════════════════════════╤══════════════╗\n",
      "║ CommitTime        │ Total Bytes Written │ Total Files Added │ Total Files Updated │ Total Partitions Written │ Total Records Written │ Total Update Records Written │ Total Errors ║\n",
      "╠═══════════════════╪═════════════════════╪═══════════════════╪═════════════════════╪══════════════════════════╪═══════════════════════╪══════════════════════════════╪══════════════╣\n",
      "║ 20230319025620022 │ 30.9 MB             │ 2                 │ 0                   │ 2                        │ 177984                │ 0                            │ 0            ║\n",
      "╟───────────────────┼─────────────────────┼───────────────────┼─────────────────────┼──────────────────────────┼───────────────────────┼──────────────────────────────┼──────────────╢\n",
      "║ 20230319025312356 │ 364.3 MB            │ 4                 │ 0                   │ 2                        │ 2194714               │ 0                            │ 0            ║\n",
      "╟───────────────────┼─────────────────────┼───────────────────┼─────────────────────┼──────────────────────────┼───────────────────────┼──────────────────────────────┼──────────────╢\n",
      "║ 20230319025144123 │ 426.7 MB            │ 4                 │ 0                   │ 2                        │ 2570430               │ 0                            │ 0            ║\n",
      "╟───────────────────┼─────────────────────┼───────────────────┼─────────────────────┼──────────────────────────┼───────────────────────┼──────────────────────────────┼──────────────╢\n",
      "║ 20230319025121426 │ 191.8 MB            │ 2                 │ 0                   │ 2                        │ 1155127               │ 0                            │ 0            ║\n",
      "╟───────────────────┼─────────────────────┼───────────────────┼─────────────────────┼──────────────────────────┼───────────────────────┼──────────────────────────────┼──────────────╢\n",
      "║ 20230319025028437 │ 191.6 MB            │ 2                 │ 0                   │ 2                        │ 1155127               │ 0                            │ 0            ║\n",
      "╚═══════════════════╧═════════════════════╧═══════════════════╧═════════════════════╧══════════════════════════╧═══════════════════════╧══════════════════════════════╧══════════════╝\n",
      "\n",
      "[ STORAGE ]\n",
      "\n",
      "/home/emr-notebook/apache-hudi-core-conceptions/reviews_cow_clustering_4\n",
      "├── [603M 02:57:12]  parity=0\n",
      "│   ├── [ 63M 02:53:54]  032d90b1-2654-4e37-bd81-336cf83e2abd-0_1-649-5913_20230319025312356.parquet\n",
      "│   ├── [120M 02:52:42]  059ec60c-0ad9-4bc7-a7ff-ea072c3e38ae-0_0-593-5588_20230319025144123.parquet\n",
      "│   ├── [ 96M 02:55:25]  0785d1f7-da29-4fbd-a8f1-a411b417d8a4-0_0-2-2_20230319025121426.parquet\n",
      "│   ├── [ 93M 02:52:36]  2b7af39a-fa93-4886-b8b3-c87ac1993208-0_1-593-5589_20230319025144123.parquet\n",
      "│   ├── [ 15M 02:56:46]  3f4a6e1e-25df-4346-adf1-59f963979945-0_0-702-6016_20230319025620022.parquet\n",
      "│   ├── [119M 02:54:03]  42cc2080-19c9-49ba-ab5d-d18dd72529bb-0_0-649-5912_20230319025312356.parquet\n",
      "│   └── [ 96M 02:51:14]  4f7a3b4c-e36a-44f1-b2cd-f36f30367f07-0_0-539-5271_20230319025028437.parquet\n",
      "└── [603M 02:57:12]  parity=1\n",
      "    ├── [ 94M 02:52:39]  0a1b5de2-f2f1-4e57-b1eb-78edd21a5fdb-0_3-593-5591_20230319025144123.parquet\n",
      "    ├── [ 15M 02:56:45]  683c2211-838f-48de-9631-0b619ccfdce1-0_1-702-6017_20230319025620022.parquet\n",
      "    ├── [ 96M 02:51:14]  706dbc1f-d3b0-45f0-95b6-33bc17a386f0-0_1-539-5272_20230319025028437.parquet\n",
      "    ├── [ 63M 02:53:54]  90e573a0-5d74-4208-ba7d-6a98c021b7c2-0_3-649-5915_20230319025312356.parquet\n",
      "    ├── [119M 02:54:04]  b0f81684-4978-4284-9b76-dc6e151c44fa-0_2-649-5914_20230319025312356.parquet\n",
      "    ├── [ 96M 02:55:25]  cb5e46b2-3f30-4d2c-92ea-225eb82d6dbc-0_1-2-3_20230319025121426.parquet\n",
      "    └── [120M 02:52:42]  e57207f5-b309-4da0-a84e-1ec865d4f845-0_2-593-5590_20230319025144123.parquet\n"
     ]
    }
   ],
   "source": [
    "%%sh\n",
    "${WORKSPACE}/hudi-stat.sh s3://${S3_BUCKET}/${TABLE_NAME} timeline commits storage"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bba7f182-8d2f-45a3-88f2-38d38d226445",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 5.10. Offline Eexecute 503MB / +2 Clustered Files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "b8be3bb1-bcf4-4158-a111-9cbc50af5020",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T02:57:12.548484Z",
     "iopub.status.busy": "2023-03-19T02:57:12.548113Z",
     "iopub.status.idle": "2023-03-19T03:00:11.434826Z",
     "shell.execute_reply": "2023-03-19T03:00:11.433930Z",
     "shell.execute_reply.started": "2023-03-19T02:57:12.548441Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%sh\n",
    "# it's required for current user (emr-notebook) to get sudo permission\n",
    "sudo -u hadoop spark-submit \\\n",
    "  --jars '/usr/lib/hudi/hudi-spark-bundle.jar' \\\n",
    "  --class 'org.apache.hudi.utilities.HoodieClusteringJob' \\\n",
    "  /usr/lib/hudi/hudi-utilities-bundle.jar \\\n",
    "  --spark-memory '4g' \\\n",
    "  --mode 'execute' \\\n",
    "  --base-path \"s3://${S3_BUCKET}/${TABLE_NAME}\" \\\n",
    "  --table-name \"${TABLE_NAME}\" > ${WORKSPACE}/${TABLE_NAME}.execute.out &>/dev/null"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "e1edf4ad-c992-4c16-9733-c4ecc2758f9b",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T03:00:11.436307Z",
     "iopub.status.busy": "2023-03-19T03:00:11.436049Z",
     "iopub.status.idle": "2023-03-19T03:00:32.620137Z",
     "shell.execute_reply": "2023-03-19T03:00:32.619205Z",
     "shell.execute_reply.started": "2023-03-19T03:00:11.436281Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "[ TIMELINE ]\n",
      "\n",
      "╔═════╤═══════════════════╤═══════════════╤═══════════╤═════════════╤═════════════╤═════════════╗\n",
      "║ No. │ Instant           │ Action        │ State     │ Requested   │ Inflight    │ Completed   ║\n",
      "║     │                   │               │           │ Time        │ Time        │ Time        ║\n",
      "╠═════╪═══════════════════╪═══════════════╪═══════════╪═════════════╪═════════════╪═════════════╣\n",
      "║ 0   │ 20230319025028437 │ commit        │ COMPLETED │ 03-19 02:50 │ 03-19 02:50 │ 03-19 02:51 ║\n",
      "╟─────┼───────────────────┼───────────────┼───────────┼─────────────┼─────────────┼─────────────╢\n",
      "║ 1   │ 20230319025121426 │ replacecommit │ COMPLETED │ 03-19 02:51 │ 03-19 02:54 │ 03-19 02:55 ║\n",
      "╟─────┼───────────────────┼───────────────┼───────────┼─────────────┼─────────────┼─────────────╢\n",
      "║ 2   │ 20230319025144123 │ commit        │ COMPLETED │ 03-19 02:51 │ 03-19 02:52 │ 03-19 02:52 ║\n",
      "╟─────┼───────────────────┼───────────────┼───────────┼─────────────┼─────────────┼─────────────╢\n",
      "║ 3   │ 20230319025312356 │ commit        │ COMPLETED │ 03-19 02:53 │ 03-19 02:53 │ 03-19 02:54 ║\n",
      "╟─────┼───────────────────┼───────────────┼───────────┼─────────────┼─────────────┼─────────────╢\n",
      "║ 4   │ 20230319025620022 │ commit        │ COMPLETED │ 03-19 02:56 │ 03-19 02:56 │ 03-19 02:56 ║\n",
      "╟─────┼───────────────────┼───────────────┼───────────┼─────────────┼─────────────┼─────────────╢\n",
      "║ 5   │ 20230319025652851 │ replacecommit │ COMPLETED │ 03-19 02:56 │ 03-19 02:57 │ 03-19 03:00 ║\n",
      "╚═════╧═══════════════════╧═══════════════╧═══════════╧═════════════╧═════════════╧═════════════╝\n",
      "\n",
      "[ COMMITS ]\n",
      "\n",
      "╔═══════════════════╤═════════════════════╤═══════════════════╤═════════════════════╤══════════════════════════╤═══════════════════════╤══════════════════════════════╤══════════════╗\n",
      "║ CommitTime        │ Total Bytes Written │ Total Files Added │ Total Files Updated │ Total Partitions Written │ Total Records Written │ Total Update Records Written │ Total Errors ║\n",
      "╠═══════════════════╪═════════════════════╪═══════════════════╪═════════════════════╪══════════════════════════╪═══════════════════════╪══════════════════════════════╪══════════════╣\n",
      "║ 20230319025652851 │ 1007.7 MB           │ 4                 │ 0                   │ 2                        │ 6098255               │ 0                            │ 0            ║\n",
      "╟───────────────────┼─────────────────────┼───────────────────┼─────────────────────┼──────────────────────────┼───────────────────────┼──────────────────────────────┼──────────────╢\n",
      "║ 20230319025620022 │ 30.9 MB             │ 2                 │ 0                   │ 2                        │ 177984                │ 0                            │ 0            ║\n",
      "╟───────────────────┼─────────────────────┼───────────────────┼─────────────────────┼──────────────────────────┼───────────────────────┼──────────────────────────────┼──────────────╢\n",
      "║ 20230319025312356 │ 364.3 MB            │ 4                 │ 0                   │ 2                        │ 2194714               │ 0                            │ 0            ║\n",
      "╟───────────────────┼─────────────────────┼───────────────────┼─────────────────────┼──────────────────────────┼───────────────────────┼──────────────────────────────┼──────────────╢\n",
      "║ 20230319025144123 │ 426.7 MB            │ 4                 │ 0                   │ 2                        │ 2570430               │ 0                            │ 0            ║\n",
      "╟───────────────────┼─────────────────────┼───────────────────┼─────────────────────┼──────────────────────────┼───────────────────────┼──────────────────────────────┼──────────────╢\n",
      "║ 20230319025121426 │ 191.8 MB            │ 2                 │ 0                   │ 2                        │ 1155127               │ 0                            │ 0            ║\n",
      "╟───────────────────┼─────────────────────┼───────────────────┼─────────────────────┼──────────────────────────┼───────────────────────┼──────────────────────────────┼──────────────╢\n",
      "║ 20230319025028437 │ 191.6 MB            │ 2                 │ 0                   │ 2                        │ 1155127               │ 0                            │ 0            ║\n",
      "╚═══════════════════╧═════════════════════╧═══════════════════╧═════════════════════╧══════════════════════════╧═══════════════════════╧══════════════════════════════╧══════════════╝\n",
      "\n",
      "[ STORAGE ]\n",
      "\n",
      "/home/emr-notebook/apache-hudi-core-conceptions/reviews_cow_clustering_4\n",
      "├── [1.1G 03:00:32]  parity=0\n",
      "│   ├── [ 63M 02:53:54]  032d90b1-2654-4e37-bd81-336cf83e2abd-0_1-649-5913_20230319025312356.parquet\n",
      "│   ├── [120M 02:52:42]  059ec60c-0ad9-4bc7-a7ff-ea072c3e38ae-0_0-593-5588_20230319025144123.parquet\n",
      "│   ├── [ 96M 02:55:25]  0785d1f7-da29-4fbd-a8f1-a411b417d8a4-0_0-2-2_20230319025121426.parquet\n",
      "│   ├── [ 93M 02:52:36]  2b7af39a-fa93-4886-b8b3-c87ac1993208-0_1-593-5589_20230319025144123.parquet\n",
      "│   ├── [ 15M 02:56:46]  3f4a6e1e-25df-4346-adf1-59f963979945-0_0-702-6016_20230319025620022.parquet\n",
      "│   ├── [119M 02:54:03]  42cc2080-19c9-49ba-ab5d-d18dd72529bb-0_0-649-5912_20230319025312356.parquet\n",
      "│   ├── [ 96M 02:51:14]  4f7a3b4c-e36a-44f1-b2cd-f36f30367f07-0_0-539-5271_20230319025028437.parquet\n",
      "│   ├── [234M 02:58:57]  54b92369-e46f-4155-bb33-27d48d2bfb31-0_1-4-25_20230319025652851.parquet\n",
      "│   └── [270M 02:58:57]  6ab6b9c2-04a2-4154-a970-bbf5b1e3e609-0_0-4-24_20230319025652851.parquet\n",
      "└── [1.1G 03:00:32]  parity=1\n",
      "    ├── [253M 02:58:56]  07af292f-b6a4-4bb5-96e6-9e9dbb6c0110-0_2-4-26_20230319025652851.parquet\n",
      "    ├── [ 94M 02:52:39]  0a1b5de2-f2f1-4e57-b1eb-78edd21a5fdb-0_3-593-5591_20230319025144123.parquet\n",
      "    ├── [251M 02:58:57]  528deef0-b2f1-4c41-a2f8-7b67055762f7-0_3-4-27_20230319025652851.parquet\n",
      "    ├── [ 15M 02:56:45]  683c2211-838f-48de-9631-0b619ccfdce1-0_1-702-6017_20230319025620022.parquet\n",
      "    ├── [ 96M 02:51:14]  706dbc1f-d3b0-45f0-95b6-33bc17a386f0-0_1-539-5272_20230319025028437.parquet\n",
      "    ├── [ 63M 02:53:54]  90e573a0-5d74-4208-ba7d-6a98c021b7c2-0_3-649-5915_20230319025312356.parquet\n",
      "    ├── [119M 02:54:04]  b0f81684-4978-4284-9b76-dc6e151c44fa-0_2-649-5914_20230319025312356.parquet\n",
      "    ├── [ 96M 02:55:25]  cb5e46b2-3f30-4d2c-92ea-225eb82d6dbc-0_1-2-3_20230319025121426.parquet\n",
      "    └── [120M 02:52:42]  e57207f5-b309-4da0-a84e-1ec865d4f845-0_2-593-5590_20230319025144123.parquet\n"
     ]
    }
   ],
   "source": [
    "%%sh\n",
    "${WORKSPACE}/hudi-stat.sh s3://${S3_BUCKET}/${TABLE_NAME} timeline commits storage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f584e077-8417-42d5-96a7-53f7a698bb3e",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Spark",
   "language": "scala",
   "name": "sparkkernel"
  },
  "language_info": {
   "codemirror_mode": "text/x-scala",
   "file_extension": ".sc",
   "mimetype": "text/x-scala",
   "name": "scala",
   "pygments_lexer": "scala"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}