1-data-preparation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "3f4a1e33-7cb8-4ad9-84a7-4edef3375a3b",
   "metadata": {},
   "source": [
    "# Apache Hudi Core Conceptions (1) - Data Preparation"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d33f2538-0867-490a-9e06-696a4dce1b1f",
   "metadata": {},
   "source": [
    "*Author: [Laurence Geng](https://laurence.blog.csdn.net) @ [https://laurence.blog.csdn.net](https://laurence.blog.csdn.net)*"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "710130ff-7cd8-4e0d-9db9-f3ade8b0b556",
   "metadata": {
    "tags": []
   },
   "source": [
    "Note: \n",
    "\n",
    "1. The public dataset amazon-reviews-pds is accessible on aws global regions, for China regions or non aws users, you can download it with S3 clients to local. \n",
    "\n",
    "2. The recommended configuration for Spark cluster is: 32 vCore，120GB or higher, the master node must have 100GB+ space."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2c556ef6-b984-4c43-b533-f2cb9d7e5c67",
   "metadata": {
    "tags": []
   },
   "source": [
    "## 1. Configuration"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "07e8341d-aae9-4e79-92ae-c6ae0e1ccbf3",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T05:05:44.046660Z",
     "iopub.status.busy": "2023-03-19T05:05:44.046401Z",
     "iopub.status.idle": "2023-03-19T05:05:44.052004Z",
     "shell.execute_reply": "2023-03-19T05:05:44.051119Z",
     "shell.execute_reply.started": "2023-03-19T05:05:44.046636Z"
    },
    "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": 2,
   "id": "f7c48157-54b0-4a95-a4fa-0101e207d225",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T05:05:44.064980Z",
     "iopub.status.busy": "2023-03-19T05:05:44.064764Z",
     "iopub.status.idle": "2023-03-19T05:06:15.879925Z",
     "shell.execute_reply": "2023-03-19T05:06:15.879203Z",
     "shell.execute_reply.started": "2023-03-19T05:05:44.064960Z"
    }
   },
   "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>95</td><td>application_1678096020253_0186</td><td>spark</td><td>idle</td><td><a target=\"_blank\" href=\"http://ip-10-0-129-209.ec2.internal:20888/proxy/application_1678096020253_0186/\" class=\"emr-proxy-link j-6V65AY8X5VHR application_1678096020253_0186\" emr-resource=\"j-6V65AY8X5VHR\n\" application-id=\"application_1678096020253_0186\">Link</a></td><td><a target=\"_blank\" href=\"http://ip-10-0-132-108.ec2.internal:8042/node/containerlogs/container_1678096020253_0186_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": "b49a7f572ea54a1f93483461fa13a6d1",
       "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": "a814345d288d46618666d735308b52db",
       "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": 3,
   "id": "4b896164-83f7-4f11-a084-e25055b36c07",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T05:06:15.881410Z",
     "iopub.status.busy": "2023-03-19T05:06:15.881074Z",
     "iopub.status.idle": "2023-03-19T05:06:16.956372Z",
     "shell.execute_reply": "2023-03-19T05:06:16.954666Z",
     "shell.execute_reply.started": "2023-03-19T05:06:15.881385Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "make_bucket: apache-hudi-core-conceptions\n"
     ]
    }
   ],
   "source": [
    "%%sh\n",
    "aws s3 mb s3://${S3_BUCKET}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ee1ac6af-41ac-4b48-a371-1972d54b3822",
   "metadata": {
    "tags": []
   },
   "source": [
    "## 1. Create Raw Data Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "d8d2259d-c1ad-4543-911a-89f751dd7e08",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T05:06:16.959109Z",
     "iopub.status.busy": "2023-03-19T05:06:16.958583Z",
     "iopub.status.idle": "2023-03-19T05:06:19.356028Z",
     "shell.execute_reply": "2023-03-19T05:06:19.355090Z",
     "shell.execute_reply.started": "2023-03-19T05:06:16.959069Z"
    },
    "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": "03cbd9a322fe4d07b85249e687cdfffa",
       "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": "022c2e1cf8a84462bb4cd2f78866c0bb",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Output()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "drop table if exists all_reviews"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "cff15ddd-0f0c-4a55-be0d-cb281bbe5763",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T05:06:19.358224Z",
     "iopub.status.busy": "2023-03-19T05:06:19.357891Z",
     "iopub.status.idle": "2023-03-19T05:06:20.196654Z",
     "shell.execute_reply": "2023-03-19T05:06:20.195830Z",
     "shell.execute_reply.started": "2023-03-19T05:06:19.358189Z"
    },
    "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": "8b6d388b91b042f1b3453a3be60198b1",
       "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": "a57682caf83c4276898c67f3dd81108c",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Output()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "create external table if not exists all_reviews(\n",
    "    marketplace string, \n",
    "    customer_id string, \n",
    "    review_id string, \n",
    "    product_id string, \n",
    "    product_parent string, \n",
    "    product_title string, \n",
    "    star_rating int, \n",
    "    helpful_votes int, \n",
    "    total_votes int, \n",
    "    vine string, \n",
    "    verified_purchase string, \n",
    "    review_headline string, \n",
    "    review_body string, \n",
    "    review_date date, \n",
    "    year int,\n",
    "    product_category string\n",
    ")\n",
    "PARTITIONED BY (product_category)\n",
    "STORED AS PARQUET\n",
    "LOCATION 's3://amazon-reviews-pds/parquet/';"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "c8794a0c-e753-4aac-a3cf-da159f079e76",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T05:06:20.197940Z",
     "iopub.status.busy": "2023-03-19T05:06:20.197724Z",
     "iopub.status.idle": "2023-03-19T05:06:25.538728Z",
     "shell.execute_reply": "2023-03-19T05:06:25.537869Z",
     "shell.execute_reply.started": "2023-03-19T05:06:20.197919Z"
    },
    "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": "e2640e50227f43f580166140806fe406",
       "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": "d099199f93cf4886a2312698e4748b5c",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Output()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "msck repair table all_reviews;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "7edd7639-a625-4048-94e2-7edb31a13f65",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T05:06:25.540068Z",
     "iopub.status.busy": "2023-03-19T05:06:25.539841Z",
     "iopub.status.idle": "2023-03-19T05:06:59.101907Z",
     "shell.execute_reply": "2023-03-19T05:06:59.100951Z",
     "shell.execute_reply.started": "2023-03-19T05:06:25.540041Z"
    },
    "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": "a6f8ed978d614e94b2dcdfb52ac03947",
       "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": "1f2db8f2b1b44c21843e9ded51f2d213",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Output()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "select * from all_reviews limit 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "5d5f1741-9022-49b9-8b4e-e47f01cd16e2",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T05:06:59.103520Z",
     "iopub.status.busy": "2023-03-19T05:06:59.103235Z",
     "iopub.status.idle": "2023-03-19T05:07:02.538270Z",
     "shell.execute_reply": "2023-03-19T05:07:02.537551Z",
     "shell.execute_reply.started": "2023-03-19T05:06:59.103493Z"
    },
    "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": "276a0354cbe34a319af8742998b8efd0",
       "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": "ebf1f7345a64455f82f329ebb3a2c297",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Output()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "select year, count(1) as count1, concat(cast(round((count(1) / 10000),0) as int), 'W') as count2 from all_reviews group by year order by year asc;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b88d9cc1-3c32-449c-8180-5e179214bcfc",
   "metadata": {},
   "source": [
    "## 2. Create Pruned Data Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "80a75526-b6d5-46db-bacc-4c6dbeed038a",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T05:07:02.540617Z",
     "iopub.status.busy": "2023-03-19T05:07:02.540379Z",
     "iopub.status.idle": "2023-03-19T05:07:29.607261Z",
     "shell.execute_reply": "2023-03-19T05:07:29.606443Z",
     "shell.execute_reply.started": "2023-03-19T05:07:02.540595Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%sh\n",
    "aws s3 rm s3://${S3_BUCKET}/reviews --recursive &>/dev/null"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "a923bf75-7195-460d-93ce-873b91220b29",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T05:07:29.609284Z",
     "iopub.status.busy": "2023-03-19T05:07:29.609028Z",
     "iopub.status.idle": "2023-03-19T05:07:30.477326Z",
     "shell.execute_reply": "2023-03-19T05:07:30.476226Z",
     "shell.execute_reply.started": "2023-03-19T05:07:29.609259Z"
    },
    "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": "d3845c3d5f504e1db53e0615cc55e49c",
       "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": "6e8c25f1edbd4c0fbff766ad1f3e52c8",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Output()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "drop table if exists reviews"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "59f92eb8-ae02-4741-bd0a-ed1bd576aa04",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T05:07:30.479084Z",
     "iopub.status.busy": "2023-03-19T05:07:30.478747Z",
     "iopub.status.idle": "2023-03-19T05:08:30.001897Z",
     "shell.execute_reply": "2023-03-19T05:08:30.001189Z",
     "shell.execute_reply.started": "2023-03-19T05:07:30.479052Z"
    },
    "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": "ea3240e02aa248ccad94e0cd8074775a",
       "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": "685673b2823e409ba7e97aac162a5b46",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Output()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "create table reviews\n",
    "using parquet\n",
    "partitioned by (year)\n",
    "location 's3://${S3_BUCKET}/reviews'\n",
    "as select\n",
    "    uuid() as review_id, \n",
    "    star_rating, \n",
    "    concat(uuid(),uuid(),uuid(),uuid(),uuid(),uuid()) as review_body, \n",
    "    review_date, \n",
    "    year\n",
    "from\n",
    "    all_reviews"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "46472fde-8456-4346-b3e1-1fb139492ece",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T05:08:30.003051Z",
     "iopub.status.busy": "2023-03-19T05:08:30.002818Z",
     "iopub.status.idle": "2023-03-19T05:08:43.360914Z",
     "shell.execute_reply": "2023-03-19T05:08:43.360270Z",
     "shell.execute_reply.started": "2023-03-19T05:08:30.003021Z"
    },
    "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": "7a8c85bc794a46359523876b6d256ab8",
       "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": "035a8d28d04a464a811914485add455a",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Output()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "select review_id from reviews group by review_id having count(1) >= 2;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "38a10cf9-2381-4483-be03-bd2206c72186",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T05:08:43.362119Z",
     "iopub.status.busy": "2023-03-19T05:08:43.361891Z",
     "iopub.status.idle": "2023-03-19T05:08:46.876300Z",
     "shell.execute_reply": "2023-03-19T05:08:46.875552Z",
     "shell.execute_reply.started": "2023-03-19T05:08:43.362097Z"
    },
    "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": "4eefd2208208455f825347bda777d89e",
       "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": "cdbd21c5006c4dff8036a58d8b1e14ff",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Output()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "select count(1) from reviews;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "93ea47f5-2eb4-439a-a513-5e554c5aa6ee",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T05:08:46.877774Z",
     "iopub.status.busy": "2023-03-19T05:08:46.877300Z",
     "iopub.status.idle": "2023-03-19T05:08:52.353310Z",
     "shell.execute_reply": "2023-03-19T05:08:52.352611Z",
     "shell.execute_reply.started": "2023-03-19T05:08:46.877744Z"
    },
    "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": "af104ae3ef2f4a8eb9d7ee8b2fd8a9b4",
       "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": "8dc80868cb424886b6a63733c7bb72f1",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Output()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "select * from reviews limit 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "f386d687-9941-4b3f-9c2b-1be3996bc574",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T05:08:52.354586Z",
     "iopub.status.busy": "2023-03-19T05:08:52.354257Z",
     "iopub.status.idle": "2023-03-19T05:08:55.777958Z",
     "shell.execute_reply": "2023-03-19T05:08:55.777255Z",
     "shell.execute_reply.started": "2023-03-19T05:08:52.354553Z"
    },
    "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": "7afa01b671294930803cf5f8b2ed0d6d",
       "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": "815b213278e84d56857b3d1643720b63",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Output()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "select year, count(1) as count1, concat(cast(round((count(1) / 10000),0) as int), 'W') as count2 from reviews group by year order by year asc;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "ff94a148-16e0-45d7-943a-e0ed076f2952",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-19T05:08:55.779152Z",
     "iopub.status.busy": "2023-03-19T05:08:55.778929Z",
     "iopub.status.idle": "2023-03-19T05:08:57.649643Z",
     "shell.execute_reply": "2023-03-19T05:08:57.648762Z",
     "shell.execute_reply.started": "2023-03-19T05:08:55.779132Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total Size:\t[ 40167017791 ]\n",
      "Total Records:\t[ 160796570 ]\n",
      "Average Size:\t[ 249 ] Bytes\n"
     ]
    }
   ],
   "source": [
    "%%sh\n",
    "total_size=$(aws s3 ls --summarize --recursive \"s3://${S3_BUCKET}/reviews/\" | sed -nE \"s/\\s+Total\\sSize\\:\\s+(\\d*)/\\1/p\")\n",
    "echo -e \"Total Size:\\t[ ${total_size} ]\"\n",
    "echo -e \"Total Records:\\t[ 160796570 ]\"\n",
    "avg_size=$((total_size/160796570))\n",
    "echo -e \"Average Size:\\t[ ${avg_size} ] Bytes\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2e08fe13-c535-49d7-9cef-6439d0500c4d",
   "metadata": {},
   "source": [
    "Note: Hudi talbes' schema is different from reviews table, the average record size of hudi table is [ 175 ] bytes not [ 249 ] Bytes!!\n",
    "This is a question, becuase hudi table has 5 + 2 = 7 extra fields, but it is smaller (not compressed)!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f95b4b86-c4f3-465c-86d8-9b524576e18e",
   "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
}