UseCases/Credit_Card_Data_Preparation/Credit_Card_Data_Preparation_SQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "6190d421-3eb0-4cd4-a5d7-e3472140ecd1",
   "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",
    "       Credit Card Fraud Detection - Data Cleansing and Feature Engineering Pipeline\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": "9e758467-3d86-4780-a34e-d94f6e7af44e",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Introduction</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "This is a demonstration of Vantage capabilities for functional demos e.g.\n",
    "    <li style = 'font-size:16px;font-family:Arial;color:#00233C'> Data Cleansing Functions - like  TD_GetFutileColumns, TD_SimpleImputeFit and TD_SimpleImputeTransform </li>\n",
    "        <li style = 'font-size:16px;font-family:Arial;color:#00233C'> Data Exploration Functions - like  TD_ColumnSummary and TD_CategoicalSummary </li>\n",
    "    <li style = 'font-size:16px;font-family:Arial;color:#00233C'> Feature Engineering Functions - like TD_BinCodeFit & Transform, TD_OrdinalEncodingFit & Transform, TD_OnehotencodingFit & Transform, TD_ScaleFit & Transform and TD_ColumnTransformer </li>\n",
    "</p>\n",
    "<br>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "In a typical Data Science project there are multiple pre-processing steps involved to process the raw incoming data before it can actually be used in a model for predictions. On an estimate about 70-80% of the time and effort goes into the pre-processing steps. With the help of Vantage's in Db functions we can perform these functions very effectively and at scale.\n",
    "In this demo notebook we are using a sample financial data of credit card application with target of loan defaulters, we will go through the general pre-processing steps that are involved in getting the source data and making the data useable for model creation.\n",
    "</p>  "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4bd09c05-ee95-457c-bad8-4705972f3775",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e17b951b-96ad-4838-89b9-e417347f5685",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>1. Connect to Vantage.</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>You will be prompted to provide the password. Enter your password, press Enter, then use down arrow to go to next cell.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5636ce90-1f17-47a4-9567-b288ded744b9",
   "metadata": {},
   "outputs": [],
   "source": [
    "%connect local, hidewarnings=true"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b623063d-60ec-43fc-ad0e-28ddb685a0d8",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Setup for execution of notebook. Begin running steps with Shift + Enter keys.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8a423113-3432-4fad-b2af-127dfc23f6e2",
   "metadata": {},
   "outputs": [],
   "source": [
    "Set query_band='DEMO=CreditCardFraud.ipynb;' update for session;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "435d11ff-12d6-4ffc-9a00-dc2aa4b87cba",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'> <b>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. In this demo since we are using Temporal table we will be creating databases and tables in local storage and use them in the notebook. Please execute the procedure in the next cell.</p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0d887126-d474-40e1-a845-0219e2c03aaa",
   "metadata": {},
   "outputs": [],
   "source": [
    "--call get_data('DEMO_CreditCard_cloud');    -- takes about 20 seconds, estimated space: 0 MB\n",
    "call get_data('DEMO_CreditCard_local');     -- takes about 35 seconds, estimated space: 11 MB"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d962e96f-9bf1-4a1e-8293-dacf3f9c4b63",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Optional step – if you want to see status of databases/tables created and space used.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6c4eb17a-4400-4954-9f9d-6485d9435999",
   "metadata": {},
   "outputs": [],
   "source": [
    "call space_report();  -- optional, takes about 10 seconds"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "23572866-f0e6-4885-9c2f-2d7b32466966",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'> <b> Access data in Vantage  </b> </p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>For this demo, data is already resident in Object Storage which we are accessing via ReadNOS, create a reference to the table, and sample the contents using the get_data procedure used above.  Data could just as easily reside in permanent tables, another RDBMS, or another Vantage system.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "afc3ac55-976d-4e80-bdd6-7198783e0660",
   "metadata": {},
   "outputs": [],
   "source": [
    "sel top 5 * from DEMO_CreditCard.Credit_Card;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "043f2d9c-9cc0-4fa9-bb23-ff4ebd4f66ed",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d5269fde-69b4-460f-8f2b-669e1181952b",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'> <b> 2. Checking data demographics  </b> </p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>TD_ColumnSummary </b>function  displays Column name, datatype and other demographics like count of NULLs etc for each specified input table column</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f8c30f61-46d6-4983-9f12-62ed67238085",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT * FROM TD_ColumnSummary (\n",
    " ON DEMO_CreditCard.Credit_Card AS InputTable\n",
    " USING\n",
    " TargetColumns ('[:]')\n",
    ") AS dt;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "02d47fa9-b98a-4884-b67b-7e8ed3e288cd",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In below sql we are checking the null percentage of columns</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e7ffc4e7-82f1-4656-b182-e325e0ae294f",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT columnname, datatype, nullpercentage FROM TD_ColumnSummary (\n",
    " ON DEMO_CreditCard.Credit_Card AS InputTable\n",
    " USING\n",
    " TargetColumns ('[:]')\n",
    ") AS dt\n",
    "where \n",
    "nullpercentage > 0\n",
    "order by 3 desc;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8be39f15-abd7-4c99-8fc7-ff5fa6a503dd",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>As the column HOUSETYPE_MODE has more than 50% of null values we can remove this column from our model calculations.<br>\n",
    "    Let's check the other varchar columns. \n",
    "OCCUPATION_TYPE also has high % of null values.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c3b362ea-66d1-40b1-88fb-2fb65a4d8593",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>TD_CATEGORICALSUMMARY </b>function   displays the distinct values and their counts for each specified input table column</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5ff1e61a-aeb9-43f7-b4a4-770858abe776",
   "metadata": {},
   "outputs": [],
   "source": [
    "Create volatile table cateogrySummaryTable as (\n",
    "SELECT * FROM TD_CATEGORICALSUMMARY (\n",
    "ON DEMO_CreditCard.Credit_Card as inputtable\n",
    "USING\n",
    "TargetColumns('CODE_GENDER'\n",
    ",'NAME_CONTRACT_TYPE'\n",
    ",'NAME_FAMILY_STATUS'\n",
    ",'FLAG_OWN_CAR'\n",
    ",'OCCUPATION_TYPE')\n",
    ") AS dt)With data \n",
    "on commit preserve rows;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a6e5110b-64ad-47aa-b812-c3f4b2049fb7",
   "metadata": {},
   "outputs": [],
   "source": [
    "select top 5* from cateogrySummaryTable;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2ecf779a-19c6-4d39-a5f3-6f2548f8f48d",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>TD_GETFUTILECOLUMNS </b>function displays the categorical columns which will have no effect on the model i.e if all the values are same or unique or If the count of distinct values in the columns divided by the count of the total number of rows in the input\n",
    "table is greater than or equal to the threshold value</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9778d7d9-e996-4c50-b6e5-16bc90d1f2d9",
   "metadata": {},
   "outputs": [],
   "source": [
    "Select * from TD_getFutileColumns(\n",
    "ON DEMO_CreditCard.Credit_Card as inputtable partition by any\n",
    "ON cateogrySummaryTable as categorytable Dimension\n",
    "USING\n",
    "CategoricalSummaryColumn('ColumnName') \n",
    "ThresholdValue(0.05)\n",
    ")As dt;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eae82675-e250-40d4-be70-3998701c2c4d",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here we can see that FLAG_OWN_CAR will have no effect on the model as all the values in this column are same so we can remove this column from model creation</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3101c1a3-2437-4289-9902-8445c884ec3b",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let us check the values in the OCCUPATION_TYPE column to see what we can do for the NULLs in the column \n",
    "</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d23d12ce-c1e2-4e3b-9cfb-f18666a27387",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT * FROM cateogrySummaryTable where columnname = 'OCCUPATION_TYPE' order by DistinctValueCount desc;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "recognized-academy",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'> <b> Impute Missing Values  </b> </p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>TD_SimpleImputeFit </b>will output a table with the values that will be used to substitute the missing values<br>\n",
    "    <b>TD_SimpleImputeTransform</b> will return the input data set with the missing values filled in.\n",
    "Verify the NULL values have been removed.<br>\n",
    "    *Note one can also use the Fit table as input to <b>TD_ColumnTransformer</b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "sudden-performer",
   "metadata": {},
   "outputs": [],
   "source": [
    "-- fit the SimpleImpute function on categorical columns\n",
    "SELECT * FROM TD_SimpleImputeFit (\n",
    "    ON DEMO_CreditCard.Credit_Card as InputTable\n",
    "    OUT VOLATILE TABLE OutputTable(impute_fit_cat_output)\n",
    "    USING\n",
    "    ColsForLiterals ('OCCUPATION_TYPE')\n",
    "    Literals ('not provided')\n",
    ") as dt;\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b6aad626-325d-458c-a1dc-fab9c112d629",
   "metadata": {},
   "outputs": [],
   "source": [
    "Create volatile table occupationimputetable as (\n",
    "SELECT * FROM TD_SimpleImputeTransform (\n",
    " ON DEMO_CreditCard.Credit_Card as InputTable\n",
    " ON impute_fit_cat_output AS FitTable DIMENSION\n",
    ") AS dt)With data \n",
    "on commit preserve rows;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "26fa00c0-8ba8-4e0b-b41c-79c5b6a0a9b3",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>TD_SimpleImputeFit and TD_SimpleImputeTransform function works on integer columns also and we can use them for filling the missing column values based on min/max/mean/median of the values in the column</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d9cc0ecf-9bc6-42fc-93fa-8a125706640c",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In model creation we usually prefer Numerical inputs instead of Characters, now let us check how many distinct values we have for our character columns so that we can encode them in numerals</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "double-military",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT columnname,count(distinctvalue) FROM cateogrySummaryTable \n",
    "group by 1 order by 2; "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f7bd6dc2-e37b-4b10-b737-761c5d0b299b",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We are not using FLAG_OWN_CAR column in model creation, for other columns we can use <b>TD_OneHotEncodingFit</b> and <b>TD_OrdinalEncodingFit</b> and transform functions to convert character categories to numerals, for that we need to check the exact values present in the columns</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "parliamentary-revolution",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT columnname,distinctvalue FROM cateogrySummaryTable where columnname in \n",
    "('CODE_GENDER', 'NAME_CONTRACT_TYPE')\n",
    "order by 1; "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "880845b0-19b1-49e5-8907-ec35177e50e0",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0a8a6f24-ebe1-4006-aab6-bfd7672898f7",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'> <b> 3. Feature Engineering Functions  </b> </p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>TD_OneHotEncodingFit </b>outputs a table of attributes and categorical values to input to <b>TD_OneHotEncodingTransform </b> which encodes them as one-hot numeric vectors.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f81f739d-1a50-4ff9-a9df-7b8215374ff0",
   "metadata": {},
   "outputs": [],
   "source": [
    "CREATE VOLATILE TABLE onehotencodingfittable AS (\n",
    "SELECT * FROM TD_OneHotEncodingFit (\n",
    " ON DEMO_CreditCard.Credit_Card AS InputTable\n",
    " USING\n",
    "  TargetColumn ('CODE_GENDER','NAME_CONTRACT_TYPE')\n",
    "  OtherColumnName ('other')\n",
    "  IsInputDense ('true')\n",
    "  CategoryCounts(2,2)\n",
    "  Approach('Auto')    \n",
    " ) AS dt\n",
    ") WITH DATA\n",
    "ON COMMIT PRESERVE ROWS;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6d0bfa70-f646-4a64-9f50-e64e355617b6",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can check how the fit table looks like</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c8399c7e-ca75-460a-b054-41cb8f532559",
   "metadata": {},
   "outputs": [],
   "source": [
    "select * from onehotencodingfittable;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d69ec7b0-b07d-4c54-bb16-d07d5351b4b8",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>For categorical columns which have many values we can use <b>TD_OrdinalEncoding</b> instead</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8bc94059-b400-4ce6-9b2b-213376ccdeec",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT * FROM TD_OrdinalEncodingFit (\n",
    "ON DEMO_CreditCard.Credit_Card AS InputTable\n",
    "OUT volatile table outputtable (ordinalencodingfittable)\n",
    "USING\n",
    "  TargetColumn ('NAME_FAMILY_STATUS','OCCUPATION_TYPE')\n",
    "  DefaultValue (-1)\n",
    ") as dt;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "763bdc0b-d4fc-4444-b6c6-5139b781859d",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>TD_BinCodeFit and TD_BinCodeTransform </b>bin-codes the\n",
    "specified input table columns. Bin-coding is typically used to convert numeric data to categorical data by binning the numeric data into multiple numeric bins (intervals).</p> "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b61a2ede-9732-46b2-ba62-7edbc9111376",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>For variable width bins, we need to provide the bin table to the function. Let's create the table and use that in the TD_BinCodeFit function</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "befb1671-7366-4094-88f1-2061a3a92061",
   "metadata": {},
   "outputs": [],
   "source": [
    "create table FitInputTable (ColumnName varchar(20), MinValue integer, MaxValue integer, Label varchar(20));"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fe17b9b4-a0b6-4489-bc7b-8b6bdfa18599",
   "metadata": {},
   "outputs": [],
   "source": [
    "insert into FitInputTable values('age', 0, 18, '1-Children');\n",
    "insert into FitInputTable values('age', 19, 25, '2-Young Adult');    \n",
    "insert into FitInputTable values('age', 26, 45, '3-Middle Adult');\n",
    "insert into FitInputTable values('age', 46, 60, '4-Old Adult');    \n",
    "insert into FitInputTable values('age', 61 ,120, '5-Senior Citizen');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "73e57ec7-71db-438b-98f8-94d9e763898c",
   "metadata": {},
   "outputs": [],
   "source": [
    "create volatile table FitOutputTable as (\n",
    "SELECT * FROM TD_BincodeFit(\n",
    "ON DEMO_CreditCard.Credit_Card as InputTable\n",
    "ON FitInputTable as FitInput Dimension\n",
    "USING\n",
    "TargetColumns('age')\n",
    "MethodType('Variable-Width')\n",
    "MinValueColumn('MinValue')\n",
    "MaxValueCOlumn('MaxValue')\n",
    "LabelColumn('Label')\n",
    "TargetColNames('ColumnName')\n",
    ") AS dt\n",
    ") with data\n",
    "ON COMMIT PRESERVE ROWS;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "32a69cf1-ae9b-48ff-9e01-d9cd55dbd58a",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The fit table looks like below:</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f9113863-074d-407a-8afc-365fd667d87d",
   "metadata": {},
   "outputs": [],
   "source": [
    "select * from FitOutputTable;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "48c2ed48-0a96-473d-97c4-0f7792fa1666",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>TD_ScaleFit and TDScaleTransform </b>scales specified input\n",
    "table columns i.e perform the specific scale methods like standard deviation, mean etc to the input columns </p> "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4d646e29-bf90-4c64-aab9-6ffd049a02ff",
   "metadata": {},
   "outputs": [],
   "source": [
    "select * from TD_scaleFit(\n",
    "on DEMO_CreditCard.Credit_Card  as InputTable\n",
    "OUT VOLATILE TABLE OutputTable(scaleFitOut)\n",
    "using\n",
    "TargetColumns('amt_income_total')\n",
    "MissValue('Keep')\n",
    "ScaleMethod('range')\n",
    "GlobalScale('f')\n",
    ")as dt;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "def2b5b6-9a2d-4a72-be0a-d1835ed6a960",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'> <b> TD_ColumnTransformer  </b> </p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The TD_ColumnTransformer function transforms the entire dataset in a single operation. You only need\n",
    "to provide the FIT tables to the function, and the function runs all transformations that you require in a\n",
    "single operation. Running all the it table transformations together in one-go gives approx. 30% performance improvement over running each transformation sequentially.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fd1faa82-b74a-46c7-856b-31a50529a456",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let us put all the fit tables we have created and transform the dataset</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0c96cd06-29cd-4e8d-8a04-3afa65004e6d",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT * FROM TD_ColumnTransformer(\n",
    "    ON (SELECT * FROM TD_ColumnTransformer(\n",
    "    ON DEMO_CreditCard.Credit_Card AS InputTable PARTITION BY ANY \n",
    "    ON FitOutputTable AS BincodeFitTable DIMENSION \n",
    "    ON onehotencodingfittable AS OneHotEncodingFitTable DIMENSION \n",
    "    ON scaleFitOut AS ScaleFitTable DIMENSION \n",
    "    ON impute_fit_cat_output AS SimpleImputeFitTable DIMENSION\n",
    "    ) as dt) AS InputTable PARTITION BY ANY \n",
    "    ON ordinalencodingfittable AS OrdinalEncodingFitTable DIMENSION ) as dt1\n",
    "    ;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2289d186-bb9e-4c23-a7d3-18d237620dac",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can create a separate intermediate table after performing all the transformations and removing all the columns from the original table which are not needed further.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "liable-wrapping",
   "metadata": {},
   "outputs": [],
   "source": [
    "Create multiset table Transformed_data as(\n",
    "SELECT SK_ID_CURR,\"TARGET\",CNT_CHILDREN,AMT_INCOME_TOTAL,NAME_FAMILY_STATUS,REGION_POPULATION_RELATIVE\n",
    "    ,substr(age,1,1) as \"AGE_GROUP\",FLAG_MOBIL,FLAG_EMP_PHONE,CNT_FAM_MEMBERS,OCCUPATION_TYPE,CODE_GENDER_1 as Male,CODE_GENDER_0 as Female,\n",
    "    \"NAME_CONTRACT_TYPE_1\" as \"REVOLVING_LOANS\",\"NAME_CONTRACT_TYPE_0\" as \"CASH_LOANS\"\n",
    "    FROM TD_ColumnTransformer(\n",
    "    ON (SELECT * FROM TD_ColumnTransformer(\n",
    "    ON DEMO_CreditCard.Credit_Card AS InputTable PARTITION BY ANY \n",
    "    ON FitOutputTable AS BincodeFitTable DIMENSION \n",
    "    ON onehotencodingfittable AS OneHotEncodingFitTable DIMENSION \n",
    "    ON scaleFitOut AS ScaleFitTable DIMENSION \n",
    "    ON impute_fit_cat_output AS SimpleImputeFitTable DIMENSION\n",
    "    ) as dt) AS InputTable PARTITION BY ANY \n",
    "    ON ordinalencodingfittable AS OrdinalEncodingFitTable DIMENSION ) as dt1 ) WITH DATA\n",
    "; "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dependent-automation",
   "metadata": {},
   "outputs": [],
   "source": [
    "select top 5 * from Transformed_data;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b1814912-3d04-410d-b0ba-4c4b795c4ad6",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now that we’ve shown you how you can use ClearScape in-database functions for preparing the data, you’ve now got a set of data that is cleansed and processed you could proceed to use this as an input in data science model creation. \n",
    "</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b1734a76-d93b-4991-8011-756966b6017b",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ba1cde98-0cbe-4ccb-94aa-2193cc407922",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>4. Cleanup</b> </p>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Worktables</b> </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "318e8e00-bf75-4e92-bc92-322b84b89cda",
   "metadata": {},
   "outputs": [],
   "source": [
    "DROP TABLE Transformed_data;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "03f04dc9-762e-49f5-a557-0b83329937f9",
   "metadata": {},
   "outputs": [],
   "source": [
    "DROP TABLE FitInputTable;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a304a4b0-7802-4d70-bbdd-dae74558d5db",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'> <b>Database 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": "f47fe183-b4ee-4169-91cd-f5f8d3df37c0",
   "metadata": {},
   "outputs": [],
   "source": [
    "call remove_data('DEMO_CreditCard');-- takes about 5 seconds, optional if you want to use the data later"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "85c34404-b9f3-4133-bd4d-31b05b50fd92",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5c0dd647-1687-4f10-967e-1e7c10de2ce9",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'> <b> 5. Conclusion </b> </p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this notebook we have seen some of the Teradata Vantage Clearscape's new inDb functions for data cleansing, data exploration and feature engineering. Many of these functions can be applied in one go using the TD_COLUMNTRANSFORM function which gives is approx. 30% faster than serial processing.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8f6c48eb-3742-4dea-8519-944b002a86ee",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Reference Links:</b></p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "        <li>Teradata Analytic Function Reference:\n",
    "        <a href = 'https://docs.teradata.com/r/Teradata-VantageCloud-Lake/Analyzing-Your-Data/Analytics-Database-Analytic-Functions/Analytics-Database-Analytic-Functions-Overview'>\n",
    "        https://docs.teradata.com/r/Teradata-VantageCloud-Lake/Analyzing-Your-Data/Analytics-Database-Analytic-Functions/Analytics-Database-Analytic-Functions-Overview</a></li>\n",
    "  \n",
    "</ul>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "efa00671-7e69-467d-9adf-4890e2dcf7ec",
   "metadata": {},
   "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",
    "            Copyright © Teradata Corporation - 2023. All Rights Reserved\n",
    "        </div>\n",
    "    </div>\n",
    "</footer>"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Teradata SQL",
   "language": "Teradata SQL",
   "name": "teradatasql"
  },
  "language_info": {
   "codemirror_mode": "Teradata SQL",
   "file_extension": ".tdrs",
   "mimetype": "application/vnd.teradata.resultset",
   "name": "Teradata SQL",
   "nbconvert_exporter": "",
   "pygments_lexer": "",
   "version": "16.20"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}