UseCases/Text_Term_Frequency/Text_Term_Frequency_SQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "cbb86ed0-ab87-4be9-ba6d-f8638e4c12cb",
   "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",
    "       Text Term Frequency Analysis (SQL)\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": "2856fe20-ab2b-4c2a-8356-1ffada0d0a53",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Introduction</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "This demo will analyze the text in rows of the table to find the TF-IDF or Term Frequency-Inverse Document Frequency is an indicator of a term's importance in a specific document based on the entire corpus of documents. This is a demonstration of Vantage capabilities for functional demos e.g.\n",
    "    <li style = 'font-size:16px;font-family:Arial;color:#00233C'> NGramSplitter Function - tokenizes (splits) an input stream of text and outputs n multigrams (called n-grams) based on the specified Reset, Punctuation, and Delimiter syntax elements.</li>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> This notebook demonstrate how the function is used in SQL kernel, there is a similar notebook which shows the same features in Python kernel. </p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "874836db-8227-4d7b-804a-ce61ad8fc922",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Steps</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li style = 'font-size:16px;font-family:Arial;color:#00233C'> Connect to Vantage and read the dataset. </li>\n",
    "    <li style = 'font-size:16px;font-family:Arial;color:#00233C'> Use NGramSplitter SQL to create a table of grams of n-size. </li>\n",
    "    <li style = 'font-size:16px;font-family:Arial;color:#00233C'> Express SQL to calculate TF-IDF and store the output in a table. </li>\n",
    "    <li style = 'font-size:16px;font-family:Arial;color:#00233C'> Basic visualization to show top 30 important terms. </li>\n",
    "</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b6a96ec0-cea6-4e0c-93c6-3428cf1af998",
   "metadata": {
    "tags": []
   },
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>1. Connect to Vantage </b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e08813ce-b83f-4f35-87ce-1a557c8eeee2",
   "metadata": {
    "tags": []
   },
   "source": [
    "<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": "changing-rocket",
   "metadata": {},
   "outputs": [],
   "source": [
    "%connect local, hidewarnings=true"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a3c76e60-3c21-4171-a845-b2063325997d",
   "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": "a2e5d196-75ba-4dfe-a1d1-fb4971ce6905",
   "metadata": {},
   "outputs": [],
   "source": [
    "SET query_band='DEMO=Text_Term_Frequency_SQL.ipynb;' UPDATE FOR SESSION;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4cf17d91-fe2c-48e5-8e78-4f6601bac9c9",
   "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": "bcb2442d-0b5d-49ea-a2eb-9b27c9b27d54",
   "metadata": {},
   "outputs": [],
   "source": [
    "CALL get_data('DEMO_Retail_cloud');    -- takes about 25 seconds, estimated space: 0 MB\n",
    "--CALL get_data('DEMO_Retail_local');     -- takes about 50 seconds, estimated space: 23 MB"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7fcd0260-28e9-47d3-bf46-2d32d54753de",
   "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>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1a9eb706-4ac5-4527-b853-4b3d4677f010",
   "metadata": {},
   "outputs": [],
   "source": [
    "CALL space_report();  -- optional, takes about 10 seconds"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "diagnostic-allen",
   "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'>Let us check the data sample</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "initial-accountability",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT TOP 5 * FROM DEMO_Retail.Web_Comment;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "suffering-jewel",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>2. Use the NGram Splitter SQL Function</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>NGram function will split the corpus of documents into \"terms\" (grams) of selected size.  Specifically, this example will create a table called \"tbl_grams\" that is the result of splitting each \"document\" (review) into two-word chunks (grams).  Each row in this table includes;\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>The two-word chunk (ngram).</li>\n",
    "    <li>The source review id (row_id).</li>\n",
    "     <li>Chunk length (n).</li>\n",
    "     <li>The count of this chunk in the review (frequency).</li>\n",
    "     <li>The count of this chunk in all the reviews (totalcnt)</li>\n",
    "</ol>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "The splitting algorithm can be controlled with delimeters, punctuation indicators, etc.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "private-equality",
   "metadata": {},
   "outputs": [],
   "source": [
    "%var ngram=2 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "treated-skill",
   "metadata": {},
   "outputs": [],
   "source": [
    "CREATE MULTISET TABLE tbl_grams AS (\n",
    "    SELECT * FROM NGramSplitter ( \n",
    "        ON ( SELECT * FROM \"DEMO_Retail\".\"Web_Comment\" )   \n",
    "        USING \n",
    "            TextColumn('comment_text') \n",
    "            Accumulate('comment_id') \n",
    "            Grams('${ngram}') \n",
    "            OverLapping('TRUE') \n",
    "            ConvertToLowerCase('TRUE') \n",
    "            Delimiter(' ') \n",
    "            Punctuation('[`~#^&*()-]') \n",
    "            OutputTotalGramCount('TRUE') \n",
    "            NGramColName('ngram') \n",
    "            GramLengthColName('n') \n",
    "            FrequencyColName('frequency') \n",
    "            TotalCountColName('totalcnt') \n",
    "    ) as ngram_out\n",
    "    )\n",
    "WITH DATA\n",
    "PRIMARY INDEX (comment_id);"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "75ce662f-77de-4304-bede-88dec0bc0c6b",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let us see how the comment in the table looks like and how its ngram looks. We have taken one comment.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8b6d561a-680a-436f-a5dd-ad02c3dd378b",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT * FROM DEMO_Retail.Web_Comment WHERE comment_id = 30;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c7066268-573b-47ba-a092-6fd826a66539",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT * FROM tbl_grams WHERE comment_id = 30;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2b2ad1ad-ff53-4c21-87e9-b466a9f97a55",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the table created above we can see the NGram function applied to the web comment column. We can see the frequency and the total number of times the ngram appear in the column.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "chronic-voluntary",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>3. Create the TF-IDF Table</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>TF-IDF</b> or <b>Term Frequency-Inverse Document Frequency</b> is an indicator of a term's <b>importance</b> in a specific document based on the entire corpus of documents.  This value is calculated by taking the Product of:\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Term Frequency = (Number of Terms in the Document)/(Number of Terms in the Corpus)</li>\n",
    "    <li>Inverse Document Frequency = Natural Log((Total Number of Documents)/(Number of Documents with the Term))</li>\n",
    " </ul>   \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "This can be accomplished in SQL using the results table created using NGgram Splitter function in above step:</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "martial-simple",
   "metadata": {},
   "outputs": [],
   "source": [
    "CREATE MULTISET TABLE tbl_tf_idf AS (\n",
    "    SELECT gr.comment_id AS comment_id,\n",
    "        gr.\"ngram\" AS term,\n",
    "        CAST(CAST(gr.frequency AS FLOAT) / CAST(gr.totalcnt AS FLOAT) AS DECIMAL(10,6)) AS tf,\n",
    "        CAST(LN(sel_docs.tot_docs / sel_docs.tot_term) AS DECIMAL(10,6)) AS idf,\n",
    "        CAST(idf * tf AS DECIMAL(10,6)) AS tf_idf\n",
    "    FROM tbl_grams AS gr\n",
    "    -- get the number of docs where each term exists\n",
    "    LEFT JOIN (\n",
    "        SELECT \"ngram\", tot_term , tot_docs \n",
    "        FROM (\n",
    "            (SELECT \"ngram\",COUNT(*) AS tot_term\n",
    "            FROM tbl_grams\n",
    "            GROUP BY \"ngram\") terms\n",
    "            -- get the total doc count and join it to the table\n",
    "            CROSS JOIN (SELECT COUNT(DISTINCT comment_id) AS tot_docs\n",
    "            FROM tbl_grams ) AS sum_docs\n",
    "        )\n",
    "    ) sel_docs\n",
    "    ON gr.\"ngram\" = sel_docs.\"ngram\"\n",
    "    WHERE tf_idf > .5\n",
    ")\n",
    "WITH DATA\n",
    "PRIMARY INDEX (comment_id);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "clean-duplicate",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT * FROM tbl_tf_idf WHERE comment_id = 30;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "academic-paraguay",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>4. Visualize the Results</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "Builtin Teradata Jupyter modules can provide simple visualizations of the data. Let us plot the top used 30 terms from our data.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "joined-validation",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT TOP 30 term, tf_idf\n",
    "FROM tbl_tf_idf\n",
    "ORDER BY tf_idf DESC;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "injured-worth",
   "metadata": {},
   "outputs": [],
   "source": [
    "%chart x = tf_idf, y = term, title = \"Terms by importance\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f23faab6-35b7-46c2-a271-f7361be8de3d",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "In this plot we are plotting the top 30 terms which are used in the reviews. </p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1d702b1a-28d3-4aa4-8598-3e2251c43244",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>5.  Cleanup </b>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'> <b>Worktables </b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e6519c55-6a89-47d5-b3e2-04c0118ddf2c",
   "metadata": {},
   "outputs": [],
   "source": [
    "DROP TABLE tbl_grams;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "western-major",
   "metadata": {},
   "outputs": [],
   "source": [
    "DROP TABLE tbl_tf_idf;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ce4c415b-1198-41f8-9e8a-261c91f98867",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;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": "aa6562dd-d8e9-4a90-95a5-0921f43ef6b9",
   "metadata": {},
   "outputs": [],
   "source": [
    "CALL remove_data('DEMO_Retail');-- takes about 5 seconds, optional if you want to use the data later"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "56f0fe75-7830-4bb5-9c65-03d336d18a95",
   "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 NGramSplitter Function Reference: <a href = 'https://docs.teradata.com/r/Teradata-VantageTM-Analytics-Database-Analytic-Functions-17.20/Text-Analytic-Functions/NGramSplitter'>https://docs.teradata.com/r/Teradata-VantageTM-Analytics-Database-Analytic-Functions-17.20/Text-Analytic-Functions/NGramSplitter</a></li>\n",
    "  \n",
    "</ul>\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "chinese-maine",
   "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
}