Basic Hands-on.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "1adcb2bc-1144-4f59-b56b-942299ca445f",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "## Create a DataFrame with first_name and age columns and four rows of data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "45113372-258f-4a0a-b515-c9339a7f05d5",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+---+\n|first_name|age|\n+----------+---+\n|       ABC| 30|\n|       XYZ| 25|\n|       PQR| 35|\n|       GHI| 28|\n+----------+---+\n\n"
     ]
    }
   ],
   "source": [
    "from pyspark.sql import SparkSession\n",
    "from pyspark.sql import Row\n",
    "\n",
    "spark = SparkSession.builder \\\n",
    "    .appName(\"Create DataFrame\") \\\n",
    "    .getOrCreate()\n",
    "\n",
    "data = [('ABC', 30),\n",
    "        ('XYZ', 25),\n",
    "        ('PQR', 35),\n",
    "        ('GHI', 28)]\n",
    "\n",
    "rdd = spark.sparkContext.parallelize(data)\n",
    "row_rdd = rdd.map(lambda x: Row(first_name=x[0], age=int(x[1])))\n",
    "\n",
    "df = spark.createDataFrame(row_rdd)\n",
    "\n",
    "df.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "84075e51-8d75-4b2a-9239-9d23a788b027",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "## View the contents of the DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "e3ecabeb-9579-4161-8a86-10148c30c9ef",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+---+\n|first_name|age|\n+----------+---+\n|       ABC| 30|\n|       XYZ| 25|\n|       PQR| 35|\n|       GHI| 28|\n+----------+---+\n\n"
     ]
    }
   ],
   "source": [
    "df.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "8828323b-7db5-440a-abce-258733b49295",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "## Add a column life_stage  to the DataFrame that returns “child” if the age is 12 or under, “teenager” if the age is between 13 and 19, and “adult” if the age is 20 or older.\n",
    "(Note: You can refer Spark API reference document for functions)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "7cedb350-3101-4e1c-9bd2-68abe875f405",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+---+----------+\n|first_name|age|life_stage|\n+----------+---+----------+\n|       ABC| 30|     adult|\n|       XYZ| 25|     adult|\n|       PQR| 35|     adult|\n|       GHI| 28|     adult|\n+----------+---+----------+\n\n"
     ]
    }
   ],
   "source": [
    "from pyspark.sql.functions import when\n",
    "\n",
    "df = df.withColumn('life_stage',\n",
    "                   when(df['age'] <= 12, 'child')\n",
    "                   .when((df['age'] >= 13) & (df['age'] <= 19), 'teenager')\n",
    "                   .otherwise('adult'))\n",
    "df.show()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "e4d4aa9b-6407-4c4c-b3a4-414968bc4562",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "## Save the DataFrame in a named Parquet table and then access the table using the table name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "f8dc7a5e-5fe6-4ad1-a779-ac7d1137f9f9",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+---+----------+\n|first_name|age|life_stage|\n+----------+---+----------+\n|       XYZ| 25|     adult|\n|       PQR| 35|     adult|\n|       GHI| 28|     adult|\n|       ABC| 30|     adult|\n+----------+---+----------+\n\n"
     ]
    }
   ],
   "source": [
    "df.write.mode('overwrite').saveAsTable('employee')\n",
    "\n",
    "spark.sql(\"SELECT * FROM employee\").show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "091f4f6b-18b8-4565-a128-9b831383b5b0",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "## Use SQL query to insert a few more rows of data into the table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "c8a63858-d692-4849-a0b0-ab4deb5268b9",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+---+----------+\n|first_name|age|life_stage|\n+----------+---+----------+\n|       WXY| 16|  teenager|\n|       XYZ| 25|     adult|\n|       PQR| 35|     adult|\n|       GHI| 28|     adult|\n|       STV| 22|     adult|\n|       ABC| 30|     adult|\n+----------+---+----------+\n\n"
     ]
    }
   ],
   "source": [
    "additional_data = [\n",
    "    ('STV', 22, 'adult'),\n",
    "    ('WXY', 16, 'teenager')\n",
    "]\n",
    "\n",
    "additional_df = spark.createDataFrame(additional_data, ['first_name', 'age', 'life_stage'])\n",
    "\n",
    "additional_df.createOrReplaceTempView(\"additional_data\")\n",
    "\n",
    "spark.sql(\"\"\"\n",
    "    INSERT INTO employee (first_name, age, life_stage)\n",
    "    SELECT first_name, age, life_stage FROM additional_data\n",
    "\"\"\")\n",
    "\n",
    "spark.sql(\"SELECT * FROM employee\").show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "13906c3c-1839-4e79-9bbc-dced07820184",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "## Write a sql query that returns the teenagers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "bffefb07-1f06-4f8a-8012-059242adc156",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+---+----------+\n|first_name|age|life_stage|\n+----------+---+----------+\n|       WXY| 16|  teenager|\n+----------+---+----------+\n\n"
     ]
    }
   ],
   "source": [
    "teenagers_query = \"\"\"\n",
    "    SELECT * FROM employee\n",
    "    WHERE life_stage = 'teenager'\n",
    "\"\"\"\n",
    "spark.sql(teenagers_query).show()"
   ]
  }
 ],
 "metadata": {
  "application/vnd.databricks.v1+notebook": {
   "dashboards": [],
   "environmentMetadata": null,
   "language": "python",
   "notebookMetadata": {
    "pythonIndentUnit": 4
   },
   "notebookName": "Basic Hands-on",
   "widgets": {}
  },
  "language_info": {
   "name": "python"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}