Getting_Started/PySpark_to_Teradataml/HousingPrices_pyspark_to_tdml.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "1f4843a0-e2c4-41b1-a67e-e2a1db5fa0ac",
   "metadata": {},
   "source": [
    "<header>\n",
    "   <p  style='font-size:36px;font-family:Arial; color:#F0F0F0; background-color: #00233c; padding-left: 20pt; padding-top: 20pt;padding-bottom: 10pt; padding-right: 20pt;'>\n",
    "       Housing Prices Prediction - PySpark to Teradataml Conversion\n",
    "  <br>\n",
    "       <img id=\"teradata-logo\" src=\"https://storage.googleapis.com/clearscape_analytics_demo_data/DEMO_Logo/teradata.svg\" alt=\"Teradata\" style=\"width: 125px; height: auto; margin-top: 20pt;\">\n",
    "    </p>\n",
    "</header>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ef6a10ab-8a8b-4d74-87cc-50ef5d25b351",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233c'><b>Introduction:</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><code>teradatamlspk</code> is the Python package name of Teradata product pyspark2teradataml. The teradatamlspk package is built as an extension of teradataml - a Teradata Package for Python.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Syntax and user accessibility of the teradatamlspk APIs are kept similar to PySpark APIs. This allows the existing PySpark workloads that runs on Spark engine to easily run on Teradata Vantage using ClearScape Analytics with minimal changes to the PySpark workloads.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><code>teradatamlspk</code> offers a function called <code>pyspark2teradataml</code> that enables conversion of a PySpark script to a teradatamlspk Python script. This function also generates HTML report for the conversion, which is useful for users to understand the changes done and carry out any manual changes in the generated teradatamlspk script, so that the script can be run on Vantage.</p>\n",
    "\n",
    "\n",
    "<center><img src=\"images/Conv_Architecture.png\" width=800 height=800/></center>\n",
    "\n",
    "<br>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The teradatamlspk package supports the following Vantage systems:</p>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>VantageCloud Lake</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>VantageCloud Enterprise</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>VantageCore</li></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Based on the connection to Vantage, the teradatamlspk package requires the following minimum software versions be installed on Vantage:</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>For connection to Teradata Vantage with Analytics Database only:</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>On Analytics Database:</p>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>For VantageCore system, Analytics Database 17.20 or later</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>For VantageCloud Enterprise system, Analytics Database 17.20 or later</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>For VantageCloud Lake system, Analytics Database 20.00 or later</li></p>\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "50712a6f-bf87-4a07-9f19-e527071caad0",
   "metadata": {
    "tags": []
   },
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>1. Connect to Vantage.</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "538057fb-4fc2-4a6d-9b59-adf0cd1843e6",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let us start by checking the version of the teradataml installed. The Openml functions used in this notebook will require Version 20.0.0.0.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9333700e-da18-4992-9af6-7077e74aaa69",
   "metadata": {},
   "outputs": [],
   "source": [
    "pip show teradataml"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "167bd8b2-cf27-4c0f-a1ff-3910b12d9e63",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Note: </b><i>If the VM has lower version, please uncomment the below code and execute the cell.  After the cell executes, please restart the kernel. The simplest way to restart the Kernel is by typing zero zero: <b> 0 0</b></i></p>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "46f79851-b9da-4ab5-8bf9-5fcd45de5fd5",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "!pip install --upgrade teradataml"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0764fc75-0c44-4ca7-9cc9-28ad8dcc685c",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the section, we import the required libraries and set environment variables and environment paths (if required).</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cacb22a4-a95c-4193-ae6e-5ac58bb42ced",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "# # '%%capture' suppresses the display of installation steps of the following packages\n",
    "!pip install teradatamlspk"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e6c1ffbd-8939-4cb3-875d-c08bdf39a669",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Note: </b><i>If the above install command is executed, be sure to restart the kernel after executing those lines to bring the installed libraries into memory. The simplest way to restart the Kernel is by typing zero zero: <b> 0 0</b></i></p>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6e0d7be9-03d4-4efd-a472-a59315bd1912",
   "metadata": {},
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "warnings.simplefilter(action='ignore', category=DeprecationWarning)\n",
    "warnings.simplefilter(action='ignore', category=RuntimeWarning)\n",
    "warnings.simplefilter(action='ignore', category=FutureWarning)\n",
    "warnings.simplefilter(action='ignore', category=UserWarning)\n",
    "import json\n",
    "import getpass\n",
    "import pandas as pd\n",
    "import datetime\n",
    "from teradataml import *\n",
    "\n",
    "display.max_rows = 5\n",
    "\n",
    "from teradatamlspk import pyspark2teradataml"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "276112a2-978b-4898-9bb2-9e34a5e17c77",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will be prompted to provide the password. We will enter the password, press the Enter key, and then use the down arrow to go to the next cell. Begin running steps with Shift + Enter keys.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b5a211f9-8b70-476e-85a1-f56acf231850",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ~/JupyterLabRoot/UseCases/startup.ipynb\n",
    "eng = create_context(host = 'host.docker.internal', username='demo_user', password = password)\n",
    "print(eng)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4ee4bfb5-97ee-4990-994e-8955d9407b5e",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "execute_sql('''SET query_band='DEMO=HousingPrices_pyspark_to_tdml.ipynb;' UPDATE FOR SESSION; ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fce13d3f-b1ba-4b51-940e-8070142a8e31",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>2. Getting Data for This Demo</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have provided data for this demo on cloud storage. We have the option of either running the demo using foreign tables to access the data without using any storage on our environment or downloading the data to local storage, which may yield somewhat faster execution. However, we need to consider available storage. There are two statements in the following cell, and one is commented out. We may switch which mode we choose by changing the comment string.</p>   \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ee3185e9-7999-4468-afb2-81f3b37fb6ca",
   "metadata": {},
   "outputs": [],
   "source": [
    "#%run -i ../run_procedure.py \"call get_data('DEMO_HousingPrices_cloud');\"\n",
    " # Takes about 50 seconds\n",
    "%run -i  ~/JupyterLabRoot/UseCases/run_procedure.py \"call get_data('DEMO_HousingPrices_local');\"\n",
    " # Takes about 50 secs"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d296a59c-2bba-4c17-b482-c63cdf9e887a",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Optional step – We should execute the below step only if we want to see the status of databases/tables created and space used.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0d1d60a2-b011-4d41-9293-2a92c2de684a",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i  ~/JupyterLabRoot/UseCases/run_procedure.py \"call space_report();\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6b69bbc9-61a2-4d64-bd79-fe9ef928e7c2",
   "metadata": {
    "tags": []
   },
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>3. Call function pyspark2teradataml for conversion.</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "79280ef7-e922-4055-b99e-b78a44860aa0",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The first step of the process is to convert PySpark script to teradatamlspk script.</p>\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>PySpark Script Details</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The PySpark script is a simple script which does some initial data analysis on the Housing Price dataset. Data Transformation using StandardScalar and then uses LinearRegression to predict the house value based on the various features provided and display the metrics for LinearRegression</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will take the following steps to achieve this migration.</p>\n",
    "\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Step1:</b> Run pyspark2teradataml with PySpark Script as Input</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Step 2:</b> Review the HTML Report</li></p>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this step we  pass the PySpark script as input to the function.</p>\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1ff48eb4-d4c5-414d-9306-76f7461726d5",
   "metadata": {},
   "outputs": [],
   "source": [
    "pyspark2teradataml('Predicting_House_Prices_Pyspark.py')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "49089334-7cb6-4a0d-942d-4539f7dcb482",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The utility function has generated the Python script with teradatamlspk syntax and an HTML report for the conversion.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The generated Python script may or may not run directly on Vantage.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The utility function pyspark2teradataml takes care of most of the conversion, but there may be some instances where generated script requires additional manual changes.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><i><b>Note: We can see both the files in the left side pane for files</b></i></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "adf90dff-7aa9-42ec-888a-38d9d30ad14c",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>4. Understanding the HTML report.</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The generated HTML file contains notes for the script with line number and a note it is applicable for. Notes are in three different colors as specified in the following list:</p>\n",
    "\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><code style= 'color:#000000'><b>black:</b></code> Notes which are colored in black do not need any attention. These notes give additional information about the APIs used in script to user.</li>\n",
    "\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><code style= 'color:#0000FF'><b>blue:</b></code> Notes which are colored in blue need user attention. These APIs have functionality but there may be some differences in functionality when compared with PySpark. These notes specify the exact differences for you to change the references of those APIs manually in script.</li>\n",
    "\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><code style= 'color:#FF0000'><b>red:</b></code> Notes which are colored in red need user attention. These APIs do not have functionality in teradatamlspk. You need to achieve the functionality through some other ways.</li></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1d1e2740-1dc6-439a-920b-4cab26643824",
   "metadata": {},
   "outputs": [],
   "source": [
    "from IPython.display import HTML\n",
    "HTML(filename='Predicting_House_Prices_Pyspark_tdmlspk.html')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "44d7ac5d-ef81-4db3-ac52-7d1572d0f287",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this step, review the HTML report and act on the items accordingly.</p>\n",
    "\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><code>Line 19:</code> This line is colored in black, so no action is needed on this line. The report says RegressionMetrics uses RDD and teradatamlspk does not support RDD based API’s. So, the utility pyspark2teradataml removed it.</li>\n",
    "\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><code>Line 58:</code> This line is colored in blue, which requires user attention on this line. The report says getOrCreate accepts Vantage connection parameters. So, user should pass connection parameters here.</li>\n",
    "\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><code>Line 77:</code> This line is colored in black, so no action is needed on this line.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><code>Line 108:</code> This line is colored in black, so no action is needed on this line.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><code>Line 108:</code> This line is colored in blue, which requires user attention on this line. The report says header is mandatory if the script is reading the file from local file system instead of reading it from cloud storage.</li>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>So,\n",
    "    <li>If the corresponding CSV file does not have header, then add a header.</li>\n",
    "<li>If the file has header, then user do not need to take any action though this is mentioned in blue color.</li></ul>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><code>Line 149:</code> This line is colored in blue, which requires user attention on this line. The report says sort operation is not propagated to next API. The script used in this example does not have any line of code where output of sort API is passed to input of other API. So, no action is taken on this line.</li>\n",
    "\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><code>Line 237:</code> This line is colored in black, so no action is needed on this line.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><code>Line 256:</code> This line is colored in blue, which requires user attention on this line. The report says StandardScaler function needs additional column called id to be present in input DataFrame. The report also says one can use function monotonically_increasing_id to create the column and advises to look at User Guide. So, this line is modified in the script to have id column for DataFrame. Along with this, the report also says the outputCol argument is not significant and output of transform returns all input columns along with scaled columns in output. And StandardScaler is an ML function. Unlike PySpark, teradatamlspk returns columns instead of vectors as mentioned in the \"Important Notes\" section in HTML. So, the script is modified by replacing the Vector with actual Columns.</li>\n",
    "\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><code>Line 277:</code> This line is colored in black, so no action is needed on this line.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><code>Line 386:</code> This line is colored in red, which requires user attention and action on this line. RegressionMetrics is not supported, so user should change it to RegressionEvaluator to make use of it’s functions. Since the script already uses RegressionEvaluator, the line 386 is commented out manually. Note that lines 386, 392, 398 and 404 uses RegressionEvaluator. So they all are commented out even though these lines are not mentioned in report.</li></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Apart from these ones, line 291 needs a change.\n",
    "As mentioned in \"Important Notes\" section, ML functions do not accept vectors and it accepts multiple columns.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>So, the function LinearSVC in line 291 is changed to accept list of feature columns. Once all these changes are done, you can run the script on Vantage.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ef58d541-3042-494a-b34c-8983c0ad9bec",
   "metadata": {
    "tags": []
   },
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>5. Changes to the converted script.</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4f4f8839-0336-431d-9004-245c3413f83c",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>As mentioned in the above html report We will have to make changes to code.</p>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>As mentioned on line 58 we will have to change the Vantage connection parameters.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><code> spark = TeradataSession.builder.master(\"local[2]\").appName(\"Linear-Regression-California-Housing\").getOrCreate(host=getpass.getpass('Enter host: '), user=getpass.getpass('Enter user: '), password=getpass.getpass('Enter password: ')) </code></p>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The below parameters should be entered for this environment</p>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><code>host:</code> Enter Machine Name. (copy the hostname from dashboard. Ex: <b style = 'font-size:12px;font-family:Arial;color:#00233C'> test-12345.env.clearscape.teradata.com</b>) </li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><code>user_name:</code> User Name is \"demo_user\" </li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><code>password:</code> Password for the CSAE environment </li> </p>\n",
    "\n",
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>5.1 Fetch Data in DataFrame </b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>On line 108 we will have to change the Data reading from google cloud storage.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><code>housing_df = spark.read.csv(path=HOUSING_DATA, schema=schema, header=True).cache()</code></p>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here, we will create a dataframe from Vantage Table which we have loaded through the get_data procedure by reading from google cloud storage.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><code>housing_df = DataFrame(in_schema(\"DEMO_HousingPrices\",\"Housing_Data\"))</code></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f0624019-6f9e-4881-af13-8dad3caf57d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "housing_df = DataFrame(in_schema(\"DEMO_HousingPrices\",\"Housing_Data\"))\n",
    "housing_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "916565f7-8089-4a39-a212-9216b47bf214",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>5.2 Changes to StandardScalar function </b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>As mentioned in the HTML report on line 256, the StandardScaler function needs additional column called id to be present in input DataFrame. We can use function monotonically_increasing_id to create the column and advises to look at User Guide. So, we will have to modify the script to have id column for DataFrame using the below code.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><code>from teradatamlspk.sql.functions import monotonically_increasing_id\n",
    "assembled_df = assembled_df.withColumn(\"id\", monotonically_increasing_id())\n",
    "assembled_df.show(5)</code></p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6eaf4eb7-1297-4d81-87f7-28e3a45ff2a7",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Use the StandardScalar function</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><code>standardScaler = StandardScaler(inputCol=[\"totbdrms\", \"pop\", \"houshlds\", \"medinc\", \"rmsperhh\", \"popperhh\", \"bdrmsperrm\"], outputCol=\"features_scaled\")</code></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><code>from teradatamlspk.sql.functions import monotonically_increasing_id\n",
    "assembled_df = assembled_df.withColumn(\"id\", monotonically_increasing_id())\n",
    "    assembled_df.show(5)</code></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Fit the DataFrame to the scaler</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><code>scaled_df = standardScaler.fit(assembled_df).transform(assembled_df)</code></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><code>scaled_df.select([\"totbdrms\", \"pop\", \"houshlds\", \"medinc\", \"rmsperhh\", \"popperhh\", \"bdrmsperrm\"]).show(10, truncate=False)</code></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "799a29de-7945-4908-9b38-a8114e35dbb7",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>As mentioned in the above steps the changes are to be made in the script which is to be executed in Vantage using the teradatamlspk syntax.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "60d0fdd5-ef22-40bf-a685-de41dee3d744",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>6. Executing the updated script.</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6554909e-7239-48dc-880c-9c0d78f8aa63",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>After making the recommended changes we will have the updated script which can be executed on Vantage.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>There is an updated script<code>(UpdatedPredicting_House_Prices_Pyspark_tdmlspk.py)</code> uploaded here which will execute all the functions on teradata using teradataml function library.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>For executing the updated script the below parameters should be entered for this environment </p>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><code>host:</code> Host Name is  <b>\"host.docker.internal\"</b> </li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><code>user_name:</code> User Name is <b>\"demo_user\" </b></li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><code>password:</code> Password for the CSAE environment </li> </p>\n",
    "\n",
    "<p style = 'font-size:12px;font-family:Arial;color:#00233C'><b><i>Note: Some comments are added in the updated script manually for better understanding of the output.</i></b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c76dd9e9-5857-4aca-b378-4c0199b70367",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i UpdatedPredicting_House_Prices_Pyspark_tdmlspk.py"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ead5a451-cf32-426c-ac12-f9d75a61d4be",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b> Conclusion</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Thus, using the function called <code>pyspark2teradataml</code> from the <code>teradatamlspk</code> package we have seen the ease with which we can convert a PySpark script to a teradatamlspk Python script. The generated HTML report makes it easy for users to understand and make any manual changes needed in the generated teradatamlspk script, so that the script can be run on Vantage.</p>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Limitations and Considerations</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Limitations and considerations when using the teradatamlspk package.</p>\n",
    "\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>PySpark Resilient Distributed Dataset (RDD) based APIs are not applicable for Teradata Vantage. Because PySpark stores data in RDD format and Vantage stores in different format.</li>\n",
    "\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>PySpark streaming APIs are not supported.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>pandas style DataFrame APIs offered by PySpark are not supported.</li></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f6c67551-2be2-4229-aeb9-b8e91fbe8650",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>7. Cleanup</b></p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "964c8cbd-d1cf-414e-9516-a1f0e0cd02ed",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Databases and Tables</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The following code will clean up tables and databases created above.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fade4a6a-e834-4862-8b31-ea410348a89a",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i  ~/JupyterLabRoot/UseCases/run_procedure.py \"call remove_data('DEMO_HousingPrices');\" \n",
    "#Takes 40 seconds"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7b70f253-4f3f-436e-8723-83567c544b3a",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>If you have updated the teradataml package, reinstall the package by uncommenting and running the below code cell.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dd021b2f-69cc-41cc-9d76-dc687263d505",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "#!pip install teradataml==17.20.0.6 --force-reinstall"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ab2f1ed1-ab39-4547-b776-52bd51da3358",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>Resources</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let’s look at the elements we have available for reference for this notebook:</p>\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>Dataset Details</b>\n",
    "<p>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><code>longitude:</code> A measure of how far west a house is; a higher value is farther west</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><code>latitude:</code> A measure of how far north a house is; a higher value is farther north</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><code>housingMedianAge:</code> Median age of a house within a block; a lower number is a newer building</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><code>totalRooms:</code> Total number of rooms within a block</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><code>totalBedrooms:</code> Total number of bedrooms within a block</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><code>population:</code> Total number of people residing within a block</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><code>households:</code> Total number of households, a group of people residing within a home unit, for a block</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><code>medianIncome:</code> Median income for households within a block of houses (measured in tens of thousands of US Dollars)</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><code>medianHouseValue:</code> Median house value for households within a block (measured in US Dollars)</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><code>oceanProximity:</code> Location of the house w.r.t ocean/sea</li></p>\n",
    "<b style = 'font-size:18px;font-family:Arial;color:#00233C'>Filters:</b> \n",
    "    <li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Industry:</b> Real Estate</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Functionality:</b> PySpark to teradataml conversion</li> \n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Use Case:</b> Housing Prices</li></p>\n",
    "<b style = 'font-size:18px;font-family:Arial;color:#00233C'>Related Resources:</b>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><a href = 'https://teradata.seismic.com/app#/doccenter/dc7eb2cf-bd2e-462a-a056-fcc02c9fd2f2/doc/%252Fddb2fe9eb1-f754-d3df-07fa-0724e0ddd3e9%252FdfODEyNmNlZmEtZmM4Mi00ODUyLTgzZTAtOTEzMTBlODQ5YjUw%252CPT0%253D%252CRGVtbw%253D%253D%252FdfYmI3ODY3ZDQtM2Q4Zi00ZTk5LTg2ZDYtNjBlZTk4ODY2YTY4%252CPT0%253D%252CRXh0ZXJuYWwgQXVkaWVuY2Vz%252Flf2e777d25-587b-4009-a9ce-b3a605c3554d//?mode=view&searchId=a8b70941-59fb-4da3-b4e0-69ad0e68b21b'>Migrate PySpark Workloads to Teradata to Fast Track AI/ML and Minimize Costs</a> </li>\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7ddd63d8-3a9d-4fc2-a0cd-289260e56dd6",
   "metadata": {
    "tags": []
   },
   "source": [
    "<footer style=\"padding-bottom:35px; background:#f9f9f9; border-bottom:3px solid #00233C\">\n",
    "    <div style=\"float:left;margin-top:14px\">ClearScape Analytics™</div>\n",
    "    <div style=\"float:right;\">\n",
    "        <div style=\"float:left; margin-top:14px\">\n",
    "            © 2024 Teradata. All rights reserved.\n",
    "        </div>\n",
    "    </div>\n",
    "</footer>"
   ]
  }
 ],
 "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.9.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}