hw07.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "0",
   "metadata": {},
   "source": [
    "# Apache Spark for complex queries\n",
    "\n",
    "## Data Management Homework 7\n",
    "\n",
    "In this assignment we will use \n",
    "[Apache Spark](https://spark.apache.org/): \n",
    "a popular framework for optimal distributed processing on large amount of data. \n",
    "The objective of is to use Apache Spark to translate and execute some queries of the TPCx-BB bigdata benchmark.  \n",
    "[TPCx-BB](https://www.tpc.org/tpc_documents_current_versions/current_specifications5.asp) \n",
    "or simply \"Big Bench\" is a common benchmark suite to evaluate the system performance on big data analytics and machine learning algorithms. \n",
    "We will focus on big data analytical queries, which are expressed in SQL. \n",
    "\n",
    "Spark is a framework available in multiple languages: Scala, Java, Python, R. In this exercise, we will use Python.\n",
    "\n",
    "## Setup\n",
    "\n",
    "### Jupyter Lab\n",
    "\n",
    "If you are not familiar with the Jupyter Lab environment, check out these resources from the official website: \n",
    "[example notebook](https://jupyter.org/try), \n",
    "[docs](https://jupyterlab.readthedocs.io/en/stable/getting_started/overview.html). \n",
    "\n",
    "Quick reference:\n",
    "- This is a cell. A cell can contain either Markdown text (such as this one) or code. Everything in jupyter notebook is a cell.\n",
    "    - Click on the plus on the top bar to add a new cell\n",
    "    - You can double-click on a text cell to edit iy using Markdown\n",
    "    - You can run a cell by either using the button \"play\" at the top bar or by using the \"shift + enter\" key combination\n",
    "    - Running a code cell executes it\n",
    "    - Running a text cell formats the text\n",
    "- Once you run a cell it stays in memory! So code will be run based on which order you execute cells, even if you execute a cell that is below another one before\n",
    "- General rule #1: try to arrange cell step-by-stop from top to bottom. If anything breaks, try to execute every cell from the top\n",
    "- General rule #2: if you are stuck or a cell is blocked during execution re-run the kernel from the top bar menu\n",
    " \n",
    "### Contents\n",
    "You can navigate through this exercise contents with the file explorer on the left.  \n",
    "The contents are \"extracted\" from the \n",
    "[TPCx-BB](https://www.tpc.org/tpc_documents_current_versions/current_specifications5.asp) \n",
    "benchmark source folder. \n",
    "Please refer to the link if you want to have a broader overview and/or additional information TPCx-BB. \n",
    "Since this exercise differs from the actual benchmark, only a subset of its content are reported here:\n",
    "- `queries/` contains 30 SQL/Spark queries, some of which are to be ported to Spark in this exercise. Every query `qxx/` folder (`xx` = number) contains\n",
    "    - `engineLocalSettings.conf`: TPC related, disregard\n",
    "    - `engineLocalSettings.sql`: TPC related, disregard\n",
    "    - `explain_qxx.sql`: *query content* in \"explanatory\" format\n",
    "    - `qxx.sql`: *query content* in TPC exec format\n",
    "    - `run.sh`: TPC related, disregard\n",
    "    - `results/qxx-result`: contains the expect result in plain-text. You should compare this with your query output (example provided later)\n",
    "- `spark_table_schemas`: contains schema information for every table in the dataset. Not relevant for the implementation\n",
    "- `TPCx-BB-dataset`: contains all the tables in separate folder. Refer to it for table names\n",
    "\n",
    "**Do not modify** `spark_table_schemas` or `TPCx-BB-dataset` contents as it may compromise your solution.\n",
    "\n",
    "### Guidelines\n",
    "You must use the Spark SQL module to solve this exercise. Refer to the official documentation:\n",
    "> Spark SQL: https://spark.apache.org/docs/latest/sql-programming-guide.html\n",
    "\n",
    "We will work with *DataFrames*: a Spark data type used to represent collections of data, including database Tables. \n",
    "You are strongly recommended to refer to the DataFrame API reference within the Spark SQL module during the exercise implementation. \n",
    "There you will find methods, functions and further datatypes which are equivalent to SQL operations. \n",
    "> DataFrame Reference: https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/dataframe.html\n",
    "The Spark DataFrame API resembles the one of Pandas library.\n",
    " \n",
    "Reference: \n",
    " [PySpark API documentation](https://spark.apache.org/docs/latest/api/python/reference/index.html)\n",
    "\n",
    "#### Reading TCPx-BB queries\n",
    "The SQL queries files (`explain_qxx.sql` and `qxx.sql`) are taken directly from the TPCx-BB benchmark suite \n",
    "and therefore might contain \"extra\" SQL statements and comments, \n",
    "which are functional to the TCPx-BB original benchmark (e.g. `hive` instructions, `EXPLAIN`, etc.). \n",
    "Your goal is to extract and translate the SQL query only, disregarding irrelevant statements/instructions for the purpose of this exercise.  \n",
    "Additionally, queries might contain *template* variables, in the form `${qxx_variable_name}`. \n",
    "You can find all relative templates in the `query/queryParameters.sql` file."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1",
   "metadata": {},
   "source": [
    "## Environment preparation\n",
    "**Make sure to read through and run the following code cells before starting the exercise** \n",
    "\n",
    "### Install PySpark\n",
    "\n",
    "Setup of python environment done through `pyproject.toml` specifications and \n",
    "[_poetry_](https://python-poetry.org/docs/)\n",
    "on project root directory.\n",
    "Follow `README.md` instructions to install the dependencies in a freshly created virtual environment."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2",
   "metadata": {},
   "source": [
    "### Import PySpark\n",
    "\n",
    "Whenever working with Spark, you need to either start a Spark Session or join one.\n",
    "The Spark Session Builder will handle under-the-hood the architecture of the framework discussed in class \n",
    "and give us an entry point to programming with Spark.\n",
    "It will create an application UI panel at `localhost:4040` by default.\n",
    "Go check it to see info regarding driver, executors and jobs for the current configuration.\n",
    " "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql import SparkSession\n",
    "from pyspark.sql.functions import *\n",
    "\n",
    "# when run locally, spark has one (master) node with its own jvm and no cluster manager is created\n",
    "spark = SparkSession.builder.master(\"local\").appName(\"Homework 07\").getOrCreate()\n",
    "\n",
    "spark"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4",
   "metadata": {},
   "source": [
    "# Spark API\n",
    "\n",
    "The building block of the Spark API is its Resilient Distributed Dataset (RDD) API.\n",
    "In the RDD API, there are two types of operations: transformations, which define a new dataset based on previous ones,\n",
    "and actions, which kick off a job to execute on a cluster.\n",
    "On top of Spark's RDD API, high level APIs are provided e.g. Dataframe API and Machine Learning API.\n",
    "We will focus on the former. \n",
    "\n",
    "\n",
    "# PySpark Dataframes\n",
    "\n",
    "Dataframes are a data structure for data manipulation.\n",
    "A [PySpark Dataframe](https://spark.apache.org/docs/latest/api/python/reference/index.html) \n",
    "is represented as a 2-dimensional labeled data structure with columns of potentially different types.\n",
    "Similar to what a spreadsheet or SQL table looks like.\n",
    "Most functionality and API of \n",
    "[Pandas](https://pandas.pydata.org/docs/)\n",
    "data analysis library is proposed in PySpark with support for distributed collections of data.\n",
    "\n",
    "To start, let's see how to create a dataframe, visualize the data in it and retrieve its basic properties."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creating a DataFrame from scratch\n",
    "df = spark.createDataFrame(\n",
    "    data=[(1, \"Alice\", 33), (2, \"Bob\", 45), (3, \"Charlie\", 50)],\n",
    "    schema=[\"id\", \"name\", \"age\"],\n",
    ")\n",
    "\n",
    "# visualize dataframe representation\n",
    "df.show()  # by default shows first 20 rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\n",
    "    f\"Type: {type(df)}\",  # PySpark Dataframe is not equal to Pandas Dataframe !\n",
    "    f\"First top n=2 rows: {df.head(2)}\",  # rows returned in a list\n",
    "    f\"Column title names: {df.columns}\",\n",
    "    f\"Column title names with its types: {df.dtypes}\",\n",
    "    f'Selecting a column: {df.select(\"name\")}',  # it's still a DataFrame\n",
    "    f'Avoid pandas notation (less features): {df[\"name\"]}',  # Column object with lesser features than PySpark DataFrame\n",
    "    f'Selecting more than one columns: {df.select([\"name\", \"age\"])}',  # it's still a DataFrame\n",
    "    sep=\"\\n\\n\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# print the values of the selected columns\n",
    "df.select([\"name\", \"age\"]).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# get statistics retrieved from a dataframe, computationally expensive, similar to Pandas\n",
    "df.describe().show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9",
   "metadata": {},
   "source": [
    "## Column operations on DataFrame\n",
    "\n",
    "Column operations generate a new DataFrame that need to be stored in a variable to be saved.\n",
    "It does not change the original DataFrame, but creates a new modified one from it.\n",
    "\n",
    "The following operations on column are presented:\n",
    "- creation\n",
    "- renaming\n",
    "- deletion\n",
    "\n",
    "The method `col('...')` is the one responsible \n",
    "to return a column given its name.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "10",
   "metadata": {},
   "outputs": [],
   "source": [
    "# add a column where the 'age' is increase by +2\n",
    "df_col_added = df.withColumn(colName=\"age in 2 years\", col=col(\"age\") + 2)\n",
    "df_col_added.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "11",
   "metadata": {},
   "outputs": [],
   "source": [
    "# remove the 'id' column\n",
    "df_col_removed = df_col_added.drop(\"id\")\n",
    "df_col_removed.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "12",
   "metadata": {},
   "outputs": [],
   "source": [
    "# rename the 'name' column\n",
    "df_col_renamed = df_col_removed.withColumnRenamed(\n",
    "    existing=\"name\", new=\"name_column_renamed\"\n",
    ")\n",
    "\n",
    "df_col_renamed.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "13",
   "metadata": {},
   "source": [
    "## Row operations on DataFrame\n",
    "\n",
    "The result from this operations need to be saved in a new variable since it does not change the original dataframe. \n",
    "Same logic as column operations.\n",
    "\n",
    "The following operations on rows are presented:\n",
    "- creation\n",
    "- update\n",
    "- deletion\n",
    "\n",
    "The examples provided are scoped within the context of handling null missing values in dataframe records."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "14",
   "metadata": {},
   "outputs": [],
   "source": [
    "# update a row setting a null value\n",
    "df_null = df.withColumn(\n",
    "    colName=\"age\", col=when(col(\"age\") >= 50, None).otherwise(col(\"age\"))\n",
    ")\n",
    "\n",
    "df_null = df_null.withColumn(\n",
    "    colName=\"id\", col=when(col(\"Name\") == \"Bob\", None).otherwise(col(\"id\"))\n",
    ")\n",
    "\n",
    "df_null.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "15",
   "metadata": {},
   "source": [
    "Missing values are generally referred as `NA`, which is a sentinel value.\n",
    "Further explanation in \n",
    "[pandas](https://pandas.pydata.org/docs/user_guide/missing_data.html#missing-data),\n",
    "[pyspark](https://spark.apache.org/docs/3.1.1/api/python/reference/api/pyspark.sql.DataFrame.na.html?highlight=na#pyspark.sql.DataFrame.na)\n",
    "specific docs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "16",
   "metadata": {},
   "outputs": [],
   "source": [
    "# remove rows if containing any null value in field attributes\n",
    "df_without_null_rows = df_null.dropna()\n",
    "# same as df_null.na.drop(), df_null.dropna(how='any'), df_null.na.drop(how='any')\n",
    "df_without_null_rows.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "17",
   "metadata": {},
   "outputs": [],
   "source": [
    "# remove rows only if null missing values is present in the specified column\n",
    "df_without_id_null = df_null.dropna(subset=[\"id\"])\n",
    "df_without_id_null.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "18",
   "metadata": {},
   "outputs": [],
   "source": [
    "# replace null missing values with the one provided, must correspond to column type\n",
    "df_filled_nulls = df_without_id_null.fillna(-1)\n",
    "df_filled_nulls.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "19",
   "metadata": {},
   "source": [
    "## Filter operations\n",
    "\n",
    "Fundamental to implement conditions on data.\n",
    "We pick again our old non-modified dataframe.\n",
    "The 3 fundamental boolean operators for filters are:\n",
    "- & and\n",
    "- | or\n",
    "- ~ not\n",
    "\n",
    "Remember to put brackets for more than one condition inside filter\n",
    "e.g. `.filter((...) & (...) | (...))`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "20",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_people_forty = df.filter(condition=(40 <= df.age) & (df.age < 50))\n",
    "df_people_forty.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "21",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "22",
   "metadata": {},
   "outputs": [],
   "source": [
    "# remember the select operation presented at the start to chose only relevant columns\n",
    "df_people_forty_anonymized = df_people_forty.select([\"id\", \"age\"])\n",
    "df_people_forty_anonymized.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "23",
   "metadata": {},
   "source": [
    "## Join\n",
    "\n",
    "Join columns of another Dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "24",
   "metadata": {},
   "outputs": [],
   "source": [
    "# new faculties dataframe\n",
    "df_faculties = spark.createDataFrame(\n",
    "    data=[(1, \"INF\"), (2, \"ECO\")],\n",
    "    schema=[\"id\", \"faculty\"],\n",
    ")\n",
    "\n",
    "df_faculties.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "25",
   "metadata": {},
   "outputs": [],
   "source": [
    "# add a column to the original df to perform join\n",
    "df_faculty_assigned = df.withColumn(\n",
    "    colName=\"faculty\", col=when(df.name == \"Bob\", 2).otherwise(\"1\")\n",
    ")\n",
    "\n",
    "df_faculty_assigned.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "26",
   "metadata": {},
   "outputs": [],
   "source": [
    "# JOIN operation\n",
    "df_joined = df_faculty_assigned.join(\n",
    "    other=df_faculties, on=df_faculty_assigned.faculty == df_faculties.id\n",
    ")\n",
    "\n",
    "df_joined.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "27",
   "metadata": {},
   "source": [
    "## Aggregates\n",
    "\n",
    "GroupBy functionality is implemented as the method `.groupBy()` in spark dataframe API.\n",
    "It then allows you to use aggregate functionality `.agg()` on the resulting object \n",
    "that can perform any aggregate operation you have already seen in SQL.\n",
    "It involves a combination of splitting the object, applying a function on the data \n",
    "and recombine the result.\n",
    "Example of aggregate operations available: `.count()`, `.sum()`, `.mean()`, `.max()`, `.min()`, ...\n",
    "Do not use the alternative with lowercase letters `.groupby()` as it is for pandas compatibility."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "28",
   "metadata": {},
   "outputs": [],
   "source": [
    "# get the average age of the faculty\n",
    "df_faculty_avg_age = df_faculty_assigned.groupBy(df_faculty_assigned.faculty).agg(\n",
    "    avg(df_faculty_assigned.age)\n",
    ")\n",
    "\n",
    "df_faculty_avg_age.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "29",
   "metadata": {},
   "source": [
    "### Alias\n",
    "\n",
    "Correspondent of 'AS' sql keyword. \n",
    "Allow data structure to be referenced using an alternative name.\n",
    "Can be applied to dataframes, columns.\n",
    "If in the same expression you are renaming a column \n",
    "and want to use it in another function, \n",
    "reference to such column with `col('aliasedName')`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "30",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_alias = df.alias(\"df_alias\")\n",
    "df_alias.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "31",
   "metadata": {},
   "outputs": [],
   "source": [
    "# from previous example of aggregates\n",
    "df_faculty_avg_age = df_faculty_assigned.groupBy(df_faculty_assigned.faculty).agg(\n",
    "    avg(df_faculty_assigned.age).alias(\"Average Age\")\n",
    ")\n",
    "\n",
    "df_faculty_avg_age.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "32",
   "metadata": {},
   "source": [
    "## OrderBy\n",
    "\n",
    "Returns a new sorted dataframe by the specified column(s).\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "33",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_faculty_avg_age.orderBy(desc(col=\"Average Age\")).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "34",
   "metadata": {},
   "source": [
    "## Others\n",
    "\n",
    "Important functions from SQL have their own correspondent, \n",
    "you should be able to complete the assignment with the ones listed.\n",
    "Since there are many solutions to reach the same goal \n",
    "for the query translation exercise, \n",
    "just check out the documentation for further references:\n",
    "[PySpark Dataframe API](https://spark.apache.org/docs/latest/api/python/reference/index.html) "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "35",
   "metadata": {},
   "source": [
    "## Define Helper Function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "36",
   "metadata": {},
   "outputs": [],
   "source": [
    "# load table from TPCxx-BB dataset. Returning a dataframe read from parquet format\n",
    "get_table = lambda table: spark.read.option(\"header\", \"true\").parquet(\n",
    "    f\"TPCx-BB-dataset/{table}.ptxt\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "37",
   "metadata": {},
   "source": [
    "## Explore the dataset\n",
    "\n",
    "You can use `get_table` to load current dataset tables. A table in Spark is stored as a *DataFrame* - see reference in the exercise intro."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "38",
   "metadata": {},
   "outputs": [],
   "source": [
    "# load the current table\n",
    "customer = get_table(\"customer\")\n",
    "customer.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "39",
   "metadata": {},
   "outputs": [],
   "source": [
    "# show the 1st row of the customer table\n",
    "customer.show(n=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "40",
   "metadata": {},
   "outputs": [],
   "source": [
    "# display the table schema, which in Spark is a set of [column, type, nullable]\n",
    "customer.schema"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "41",
   "metadata": {},
   "outputs": [],
   "source": [
    "# or for a nice pretty print tree view of it\n",
    "customer.printSchema()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "42",
   "metadata": {},
   "source": [
    "## Sample query translation\n",
    "Refer to `queries/q00/explain_q00.sql`. The code below is a valid translation of that query using SparkSQL. You can use any methods in the Spark SQL DataFrame class to implement your solution."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "43",
   "metadata": {},
   "source": [
    "### Query 0\n",
    "Find the amount of items sold by their category.  \n",
    "Only in certain categories sold in specific stores are considered,\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "44",
   "metadata": {},
   "outputs": [],
   "source": [
    "# look into TCPx-BB-dataset/ directory to check all the available tables.\n",
    "# gather tables needed\n",
    "\n",
    "s = get_table(\"store_sales\")\n",
    "i = get_table(\"item\")\n",
    "\n",
    "q01_i_category_id_IN = [1, 2, 3]\n",
    "q01_ss_store_sk_IN = [10, 20, 33, 40, 50]\n",
    "\n",
    "query0_solution = (\n",
    "    s.join(other=i, on=s.ss_item_sk == i.i_item_sk)\n",
    "    .filter(condition=i.i_category_id.isin(q01_i_category_id_IN))\n",
    "    .filter(condition=s.ss_store_sk.isin(q01_ss_store_sk_IN))\n",
    "    .groupBy(i.i_category)\n",
    "    .count()\n",
    "    .select(\"i_category\", \"count\")\n",
    ")\n",
    "\n",
    "query0_solution.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "45",
   "metadata": {},
   "source": [
    "The cell below is a shortcut to display the results file of q00 without navigating to the file.  \n",
    "The `!` symbol followed by a bash command (`cat` in this case) can be used as in-cell access to the terminal "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "46",
   "metadata": {},
   "outputs": [],
   "source": [
    "## check the result\n",
    "!cat queries/q00/results/q00-result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "47",
   "metadata": {},
   "source": [
    "# [YOUR SOLUTION BELOW]\n",
    "Write the query description in a Markdown cell, followed by a code cell with the query implementation.  \n",
    "Query descriptions can be found in the TCPx-BB specification, page 93: https://www.tpc.org/tpc_documents_current_versions/current_specifications5.asp\n",
    "and in the _/queries_ folder of this exercise.\n",
    "\n",
    "You should implement all the queries assigned in the homework sheet."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "48",
   "metadata": {},
   "source": [
    "## 1) Query 07\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "49",
   "metadata": {},
   "outputs": [],
   "source": [
    "# implementation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "50",
   "metadata": {},
   "outputs": [],
   "source": [
    "# check the result\n",
    "!cat queries/q07/results/q07-result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "51",
   "metadata": {},
   "source": [
    "## 2) Modified Query 07\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "52",
   "metadata": {},
   "source": [
    "## 2.a)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "53",
   "metadata": {},
   "outputs": [],
   "source": [
    "# implementation"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "54",
   "metadata": {},
   "source": [
    "## 2.b)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "55",
   "metadata": {},
   "outputs": [],
   "source": [
    "# implementation"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "56",
   "metadata": {},
   "source": [
    "## 2.c)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "57",
   "metadata": {},
   "outputs": [],
   "source": [
    "# implementation"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "58",
   "metadata": {},
   "source": [
    "## 2.d)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "59",
   "metadata": {},
   "outputs": [],
   "source": [
    "# implementation"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "60",
   "metadata": {},
   "source": [
    "## 3) Query 09\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "61",
   "metadata": {},
   "outputs": [],
   "source": [
    "# implementation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "62",
   "metadata": {},
   "outputs": [],
   "source": [
    "# check the result\n",
    "!cat queries/q09/results/q09-result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "63",
   "metadata": {},
   "source": [
    "## 4) Query 20\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "64",
   "metadata": {},
   "outputs": [],
   "source": [
    "# implementation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "65",
   "metadata": {},
   "outputs": [],
   "source": [
    "# check the result\n",
    "!cat queries/q20/results/q20-result-queryonly"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "66",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Resource saver: gracefully stop the spark session :)\n",
    "# spark.stop()\n",
    "\n",
    "# hope to see you using clusters with Spark in Databricks platform\n",
    "# on the cloud as AWS, Azure, GCP\n",
    "# for your Data Engineering projects :D"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}