Data_Engineering_Exploration.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "fuzzy-carbon",
   "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",
    "       Flexible, Scalable Storage and Analytics with VantageCloud Lake\n",
    "  <br>\n",
    "       <img id=\"teradata-logo\" src=\"images/TeradataLogo.png\" alt=\"Teradata\" style=\"width: 125px; height: auto; margin-top: 20pt;\">\n",
    "    </p>\n",
    "</header>\n",
    "\n",
    "<br>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Teradata's VantageCloud Lake Edition is the only enterprise-class analytics platform that enables powerful, open, and flexible analytics against data in any location; Data Lakes, third-party systems, and traditional EDW.</p>\n",
    "\n",
    "<hr>\n",
    "\n",
    "<b style = 'font-size:28px;font-family:Arial;color:#00233C'>Environment Overview</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>This demonstration will illustrate how users can can use Vantage to access data from a variety of external sources, choose from multiple physical and virtual storage tiers, and then derive valuable analytic and business insights at extreme scale with minimal cost.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Data Architects and Engineers have a choice of a broad data locality and storage types that can help optimize the balance between cost, accessibility, and analytic performance.  These storage types include:</p>\n",
    "\n",
    "<table style = 'width:100%;table-layout:fixed;'>\n",
    "    <tr>\n",
    "        <td style = 'vertical-align:top;font-size:16px;font-family:Arial;color:#00233C' width = '40%'>\n",
    "            <ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "                <li>Direct-attached block storage for high-speed, tactical workloads.  Most common for smaller, dimensional tables, or tables that support tactical, near-real-time query patterns.</li>\n",
    "                <br>\n",
    "                <li>An object-storage-based filesystem named the Vantage Object Filesystem (OFS) which features:\n",
    "                    <ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "                        <li>Massively scalable low-cost storage</li>\n",
    "                        <li>Data sharing across multiple processing clusters</li>\n",
    "                <li>Full ACID compliance and optimized for performance including caching, optimized spool, indexed metadata</li>\n",
    "                    </ul>\n",
    "                </li>\n",
    "                <br>\n",
    "                <li>Open Object Storage; which can include Data Lakes (including formats such as Delta), IIOT data, Data Marketplaces, and many other third-party ETL and data streaming systems.</li>\n",
    "            </ol>\n",
    "        </td>\n",
    "        <td><img src = 'images/Lake_Architecture.png' width = '800'></td>\n",
    "    </tr>\n",
    "</table>\n",
    "\n",
    "<b style = 'font-size:28px;font-family:Arial;color:#00233C'>Demonstration Overview</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>This notebook consists of three primary demonstrations designed to briefly review these storage types and how they can be mixed transparently for analytical processing.  </p>\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li><b>Demo 1 - The Object Filesystem (OFS)</b>. Show how users can load data, execute queries, as well as how leverage Time Travel capabilities</li>\n",
    "    <li><b>Demo 2 - Open Object Storage</b> - Data Discovery and analytics against data in S3, and an illustration of how to simplify access for users</li>\n",
    "    <li><b>Demo 3 - Analytics</b> - Combine data across storage tiers and apply powerful, scalable ClearScape Analytics Functions</li>\n",
    "    </ol>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "vertical-liberty",
   "metadata": {},
   "source": [
    "<hr>\n",
    "\n",
    "<b style = 'font-size:18px;font-family:Arial;color:#00233C'>Connect to Vantage</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>This notebook uses the Vantage Plugin for Jupyter; a Jupyter Lab Extension that provides native SQL processing, administrative magic functions, and visualiztion capabilities.  In order to conntect to our Vantage system, the user needs to create a connection - this connection is stored in the user's profile and doesn't need to be recreated each time.</p> "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "placed-dakota",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%addconnect name=lake_demo, host=XXX.XXX.XXX.XXX"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "historic-fellow",
   "metadata": {},
   "outputs": [],
   "source": [
    "%chconnect name=lake_demo, host=XXX.XXX.XXX.XXX"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "formal-venice",
   "metadata": {},
   "outputs": [],
   "source": [
    "%disconnect name=lake_demo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "random-footage",
   "metadata": {},
   "outputs": [],
   "source": [
    "%connect lake_demo, user=data_engineer, hidewarnings=True"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "intermediate-today",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<p style = 'font-size:28px;font-family:Arial;color:#00233C'><b>Demonstration 1 - Vantage Object FileSystem (OFS)</b></p>\n",
    "\n",
    "<table style = 'width:100%;table-layout:fixed;'>\n",
    "    <tr>\n",
    "        <td style = 'vertical-align:top' width = '40%'>\n",
    "            <ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "                <li>Immutable storage, objects are versioned to guarantee consistency</li>\n",
    "                <br>\n",
    "                <li>The Object File System (OFS) index is represented by a tree structure with a single root with multiple leaf nodes:\n",
    "                    <br>\n",
    "                    <ul><br>\n",
    "                        <li>One internal index structure is created for each OFS table</li>\n",
    "                    <br>\n",
    "                        <li>Internal indexes provide efficiency in multiple ways:<ul>\n",
    "                            <li>Filtering out selected rows</li>\n",
    "                            <li>Pre-positioning index entries across AMPs</li>\n",
    "                            <li>Join column redistributions</li>\n",
    "                            </ul></li>\n",
    "                        <br>\n",
    "                        <li>Set of statistics values for up to 64 columns carried in the root (for each leaf) and the leaf objects (for each data object)</li>\n",
    "                    <br>\n",
    "                <li>Each Root and Leaf node are in their own object, separate from the data objects</li>\n",
    "                        <br>\n",
    "                <li>With Columnar format, each Data Object contains one Row Group broken into Pages within Column Chunks</li></ul></li>\n",
    "            </ul>\n",
    "        </td>\n",
    "        <td><img src = 'images/OFS_Columnar.png' width = '600'></td>\n",
    "    </tr>\n",
    "</table>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "primary-injury",
   "metadata": {},
   "source": [
    "<hr>\n",
    "\n",
    "<b style = 'font-size:18px;font-family:Arial;color:#00233C'>Create the OFS Table</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>For purposes of demonstration, the user will DROP the table prior to loading so as to show how a new table can be created.</p> \n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Tables can be defined as generally normally done with any RDBMS.  However, in this case we will show some specifics that are unique to Vantage.</p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'><li><b>First</b>, the \"STORAGE\" clause is set to indicate the data will be stored in the OFS framework.</li>\n",
    "    <li><b>Second</b>, the SELECT that is used to populate the table consists of a specific query type designed to read data directly from an Object Store.</li> </ul>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Finally, the table can be queried as any normal relational table.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "compliant-thread",
   "metadata": {},
   "outputs": [],
   "source": [
    "DROP TABLE demo.Txn_History_OFS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ecological-backing",
   "metadata": {},
   "outputs": [],
   "source": [
    "/* Load the data into OFS directly from S3\n",
    "*/\n",
    "\n",
    "--EXPLAIN\n",
    "CREATE TABLE demo.Txn_History_OFS,\n",
    "\n",
    "    /*Note the use of STORAGE here to specify OFS\n",
    "    This can also be set to a user and database default */\n",
    "    \n",
    "    STORAGE=TD_OFSSTORAGE\n",
    "    \n",
    "    /* We can use a SELECT statement to populate the table\n",
    "    using WITH DATA */\n",
    "    AS (\n",
    "\n",
    "        /*Provide json path information to identify the proper fields */\n",
    "        SELECT Payload.txn_id txn_id,\n",
    "            Payload.step step,\n",
    "            Payload.\"type\" \"txn_type\",\n",
    "            CAST(Payload.amount AS FLOAT) amount,\n",
    "            Payload.nameOrig nameOrig,\n",
    "            CAST(Payload.oldbalanceOrig AS FLOAT) oldbalanceOrig,\n",
    "            CAST(Payload.newbalanceOrig AS FLOAT) newbalanceOrig,\n",
    "            Payload.nameDest nameDest,\n",
    "            CAST(Payload.oldbalanceDest AS FLOAT) oldbalanceDest,\n",
    "            CAST(Payload.newbalanceDest AS FLOAT) newbalanceDest,\n",
    "            CAST(Payload.isFraud AS INTEGER) isFraud,\n",
    "            CAST(Payload.isFlaggedFraud AS INTEGER) isFlaggedFraud\n",
    "\n",
    "        FROM (\n",
    "            LOCATION = '/s3/s3.amazonaws.com/trial-datasets/FraudReduction/'\n",
    "            AUTHORIZATION = Repositories.PubAuth\n",
    ") AS D\n",
    ") WITH DATA;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "material-figure",
   "metadata": {},
   "outputs": [],
   "source": [
    "/* Run a basic query against the data \n",
    "Note we can use AS OF syntax to select a specific version\n",
    "Visualize results */\n",
    "\n",
    "SELECT txn_type, COUNT(isFraud), SUM(isFraud) fraud_txns\n",
    "FROM demo.Txn_History_OFS \n",
    "\n",
    "GROUP BY 1;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "deadly-happiness",
   "metadata": {},
   "outputs": [],
   "source": [
    "%chart x=txn_type, y=fraud_txns, title=\"Count of Fraudulent Transactions\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "capital-december",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<p style = 'font-size:28px;font-family:Arial;color:#00233C'><b>Time Travel with OFS</b></p>\n",
    "\n",
    "<table style = 'width:100%;table-layout:fixed;'>\n",
    "    <tr>\n",
    "        <td style = 'vertical-align:top' width = '40%'>\n",
    "            <ul style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Benefits of Time Travel</b>\n",
    "                <br>\n",
    "                <br>\n",
    "                <li>Querying the database based on how the data looked at some point in the past</li>\n",
    "                <br>\n",
    "                <li>If there were problems with a data load, users can go back to a point in time where the data is clean/consistent</li>\n",
    "                 <br>\n",
    "                 <li>Analytics can be re-run it keeping the inputs the same for one or more tables at any point in time</li>\n",
    "                <br>\n",
    "            </ul>\n",
    "            <ul style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Features</b>\n",
    "                <br>\n",
    "                <br>\n",
    "                <li>supported through SELECT AS OF syntax</li>\n",
    "                <br>\n",
    "                <li>Versions come with a system default retention period</li>\n",
    "                <br>\n",
    "                <li>Versioning allows readers to not be blocked by writers, and vice versa</li>\n",
    "                <br>\n",
    "                <li>A transactionally consistent read is always provided by using the last committed version</li>\n",
    "            </ul>               \n",
    "                </td>\n",
    "        <td><img src = 'images/Time_Travel.png' width = '400'></td>\n",
    "    </tr>\n",
    "</table>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "british-community",
   "metadata": {},
   "source": [
    "<hr>\n",
    "\n",
    "<b style = 'font-size:18px;font-family:Arial;color:#00233C'>Time Travel Demonstration</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>As discussed above, OFS tables have a defined retention period over which versions are stored.  This can be viewed by executing a SHOW STORAGE ACCESS statement to see the internal bucket information, including retention period.</p> \n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Next, some additional data will be inserted into the table.  Executing a query gainst the VersionInfo view can show the version history</p>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Finally, the table can be queried using AS OF syntax to illustrate simple row counts before and after changes</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "unable-trick",
   "metadata": {},
   "outputs": [],
   "source": [
    "--Show the internal S3 bucket and retention (time travel) policy\n",
    "SHOW STORAGE ACCESS TD_OFSSTORAGE;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "integral-crisis",
   "metadata": {},
   "outputs": [],
   "source": [
    "/* Insert some additional rows into our OFS Table */\n",
    "INSERT INTO demo.Txn_History_OFS\n",
    "\n",
    "        /*Provide json path information to identify the proper fields */\n",
    "        SELECT TOP 10000 Payload.txn_id txn_id,\n",
    "            Payload.step step,\n",
    "            Payload.\"type\" \"txn_type\",\n",
    "            CAST(Payload.amount AS FLOAT) amount,\n",
    "            Payload.nameOrig nameOrig,\n",
    "            CAST(Payload.oldbalanceOrig AS FLOAT) oldbalanceOrig,\n",
    "            CAST(Payload.newbalanceOrig AS FLOAT) newbalanceOrig,\n",
    "            Payload.nameDest nameDest,\n",
    "            CAST(Payload.oldbalanceDest AS FLOAT) oldbalanceDest,\n",
    "            CAST(Payload.newbalanceDest AS FLOAT) newbalanceDest,\n",
    "            CAST(Payload.isFraud AS INTEGER) isFraud,\n",
    "            CAST(Payload.isFlaggedFraud AS INTEGER) isFlaggedFraud\n",
    "\n",
    "        FROM (\n",
    "            LOCATION = '/s3/s3.amazonaws.com/trial-datasets/FraudReduction/'\n",
    "            AUTHORIZATION = repositories.PubAuth\n",
    "           ) D"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "breathing-devil",
   "metadata": {},
   "outputs": [],
   "source": [
    "-- Show Table Versioning for Time Travel capabilities\n",
    "SELECT TableName, OFSStartVersionNumber, VersionTimeStamp FROM DBC.MultiVersionInfoTblV\n",
    "WHERE DatabaseName = 'demo'\n",
    "AND TableName = 'Txn_History_OFS'\n",
    "ORDER BY 2;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "appreciated-coverage",
   "metadata": {},
   "outputs": [],
   "source": [
    "/* Count the number of rows after each version\n",
    "Replace the timestamps as necessary \n",
    "Or use the version number */\n",
    "\n",
    "SELECT 'Original' as Ver, COUNT(*) \n",
    "FROM demo.Txn_History_OFS \n",
    "    --AS OF TIMESTAMP '2023-02-13 11:19:28.360000-04:00'\n",
    "    AS OF 0\n",
    "\n",
    "GROUP BY 1\n",
    "\n",
    "UNION ALL \n",
    "\n",
    "SELECT 'New Ver' as Ver, COUNT(*) \n",
    "FROM demo.Txn_History_OFS \n",
    "    --AS OF TIMESTAMP '2023-02-13 15:20:04.580000-04:00'\n",
    "    AS OF 1\n",
    "\n",
    "GROUP BY 1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "whole-hypothetical",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<p style = 'font-size:28px;font-family:Arial;color:#00233C'><b>Demonstration 2 - Data in External Object Stores</b></p>\n",
    "\n",
    "<table style = 'width:100%;table-layout:fixed;'>\n",
    "    <tr>\n",
    "        <td style = 'vertical-align:top' width = '40%'>\n",
    "            <ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "                <li>Vast quantities of data stored in Object Storage\n",
    "                    <br>\n",
    "                <ul>\n",
    "                    <br>\n",
    "                    <li>Data Lakes</li>\n",
    "                    <br>\n",
    "                    <li>IOT/IIOT</li>\n",
    "                    <br>\n",
    "                    <li>Marketplaces</li><br>\n",
    "                    </ul></li>\n",
    "                <br>\n",
    "                <li>Vantage can read this data directly using built-in capabilities:\n",
    "                    <br>\n",
    "                    <ul><br>\n",
    "                        <li>At massive scale/parallelism</li>\n",
    "                    <br>\n",
    "                        <li>Automatic schema discovery</li>\n",
    "                        <br>\n",
    "                            <li>Read optimizations/object filtering</li>\n",
    "                            </ul></li>\n",
    "                <br>\n",
    "                <li>Administrators can create objects to enable easy access/analysis:\n",
    "                <ul><br>\n",
    "                    <li>Foreign Tables that contain object locations and definitions</li>\n",
    "                    <br>\n",
    "                    <li>Views to simplify access for users</li>\n",
    "                    </ul>\n",
    "                </li>\n",
    "            </ul>\n",
    "        </td>\n",
    "        <td><img src = 'images/Data_Marketplace.png' width = '600'></td>\n",
    "    </tr>\n",
    "</table>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "simplified-passion",
   "metadata": {},
   "source": [
    "<hr>\n",
    "\n",
    "<b style = 'font-size:18px;font-family:Arial;color:#00233C'>Exploration and Access Data in Object Stores</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The illustrations below review some common query types for investigating data in object storage.  Adding a \"RETURNTYPE\" parameter to the query can return additional information about the data:</p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li><b>NOSREAD_KEYS</b> will return objects keys/key paths, length in bytes, etc.</li>\n",
    "    <li><b>NOSREAD_SCHEMA</b> will return the objects schema as interpreted by Vantage</li>\n",
    "    </ul>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The Data Engineer can then create a <b>Foreign Table</b> which acts as a virtual pointer to the data as it lies in Object Storage.  Optionally, this table can contain filtering mechanisms to assist in minimizing download volumes and improving performance.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "expensive-astrology",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "SELECT TOP 5 *\n",
    "\n",
    "FROM (\n",
    "    LOCATION = '/s3/s3.amazonaws.com/td-usecases-data-store/retail_sample_data/FSCustomerJourney/ich_banking/'\n",
    "    AUTHORIZATION = repositories.PubAuth\n",
    ") AS D;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ethical-request",
   "metadata": {},
   "outputs": [],
   "source": [
    "/* Investigate object structure */\n",
    "\n",
    "SELECT Location, ObjectLength\n",
    "\n",
    "FROM (\n",
    "    LOCATION = '/s3/s3.amazonaws.com/td-usecases-data-store/retail_sample_data/FSCustomerJourney/ich_banking/'\n",
    "    AUTHORIZATION = repositories.PubAuth\n",
    "    RETURNTYPE = 'NOSREAD_KEYS'\n",
    ") AS D"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "affiliated-valuation",
   "metadata": {},
   "outputs": [],
   "source": [
    "/* Investigate object schema */\n",
    "\n",
    "SELECT TOP 12 *\n",
    "\n",
    "FROM (\n",
    "    LOCATION = '/s3/s3.amazonaws.com/td-usecases-data-store/retail_sample_data/FSCustomerJourney/ich_banking/'\n",
    "    AUTHORIZATION = repositories.PubAuth\n",
    "    RETURNTYPE = 'NOSREAD_SCHEMA'\n",
    ") AS D\n",
    "ORDER BY 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "living-couple",
   "metadata": {},
   "outputs": [],
   "source": [
    "DROP TABLE demo.Banking_History_S3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ethical-style",
   "metadata": {},
   "outputs": [],
   "source": [
    "/* Create the Foreign Table */\n",
    "\n",
    "CREATE FOREIGN TABLE demo.Banking_History_S3,\n",
    "    EXTERNAL SECURITY repositories.PubAuth\n",
    "    (\n",
    "    customer_skey                 integer,\n",
    "    customer_identifier           varchar(26),\n",
    "    customer_cookie               varchar(50),\n",
    "    customer_online_id            varchar(3),\n",
    "    customer_offline_id           integer,\n",
    "    customer_type                 varchar(90),\n",
    "    customer_days_active          integer,\n",
    "    interaction_session_number      bigint,\n",
    "    interaction_timestamp         timestamp with time zone,\n",
    "    interaction_source            varchar(100),\n",
    "    interaction_type              varchar(100),\n",
    "    sales_channel            varchar(100))\n",
    "USING\n",
    "(\n",
    "     LOCATION  ('/s3/s3.amazonaws.com/td-usecases-data-store/retail_sample_data/FSCustomerJourney/ich_banking/')\n",
    "     MANIFEST  ('FALSE')\n",
    "     ROWFORMAT  ('{\"field_delimiter\":\",\",\"record_delimiter\":\"\\n\",\"character_set\":\"LATIN\"}')\n",
    "     STOREDAS  ('TEXTFILE')\n",
    "     HEADER  ('FALSE')\n",
    "     STRIP_EXTERIOR_SPACES  ('FALSE')\n",
    "     STRIP_ENCLOSING_CHAR  ('\"')\n",
    ")\n",
    "NO PRIMARY INDEX ;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "simplified-hardware",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT TOP 10 * FROM demo.Banking_History_S3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "occupied-problem",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT COUNT(*) FROM demo.Banking_History_S3"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "settled-diamond",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<p style = 'font-size:28px;font-family:Arial;color:#00233C'><b>Demonstration 3 - Join data across storage types</b></p>\n",
    "\n",
    "<table style = 'width:100%;table-layout:fixed;font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <tr style = 'text-align:center'>\n",
    "        <td><b>Customers_BFS, Accounts_Mapping_BFS</b>\n",
    "        <br>Two dimension tables, stored in local BFS Storage on the Primary Cluster.</td>\n",
    "                <td><b>Txn_History_OFS</b>\n",
    "            <br>A large fact table with financial transaction detail information.  Data is stored in the Vantage Object Filesystem.</td>      \n",
    "        <td><b>Banking_History_S3</b>\n",
    "            <br>An extremely large external table containing Banking History. Data is stored as-is in S3.</td>\n",
    "    </tr>\n",
    "    </table>\n",
    "    <table style = 'width:100%;table-layout:fixed;font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <tr>\n",
    "        <td><img src = 'images/EBS_Tables.png'></td>\n",
    "        <td><img src = 'images/OFS_Tables.png'></td>\n",
    "        <td><img src = 'images/S3_Tables.png'></td>\n",
    "    </tr>\n",
    "</table>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "brutal-amplifier",
   "metadata": {},
   "outputs": [],
   "source": [
    "/* Find banking users who have had a fradulent event in their cashapp account*/\n",
    "\n",
    "SELECT TOP 10\n",
    "\n",
    "txn.amount,\n",
    "c.firstname,\n",
    "c.lastname\n",
    "\n",
    "\n",
    "FROM demo.txn_history_ofs txn, demo.Customers_BFS c, demo.Accounts_Mapping_BFS am\n",
    "\n",
    "\n",
    "WHERE txn.isFraud = 1 AND\n",
    "    txn.nameOrig= am.nameOrig AND\n",
    "    am.customer_identifier = c.customer_identifier \n",
    "\n",
    "GROUP BY 1,2,3"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "twenty-impossible",
   "metadata": {},
   "source": [
    "<hr>\n",
    "\n",
    "<b style = 'font-size:18px;font-family:Arial;color:#00233C'>Bringing it all together with ClearScape Analytics Functions</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>This query will add two new elements to the analysis; adding in the large fact table stored natively in S3, and wrapping everything in a multi-row pattern analysis function named nPath. The goal of this analysis is identifying offline behavioral patterns leading to \"Fraud\" - a potentially valuable analysis that can directly feed more accurate Fraud prediction or early identification.</p>\n",
    "\n",
    "\n",
    "<table style = 'width:100%;table-layout:fixed;font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <tr style = 'text-align:top'>\n",
    "        <td style = 'width:30%'>\n",
    "        <ol>\n",
    "            <br>\n",
    "            <li>Use the Join conditions above to identify Fraud in mobile app</li>\n",
    "            <br>\n",
    "            <li>Join the fradulent users to their offline identities</li>\n",
    "            <br>\n",
    "            <li>Pass this data to Teradata nPath function to idenfify offline bevaviors</li>\n",
    "            <br>\n",
    "        </ol></td>\n",
    "        <td><img src = 'images/Life_Of_Query.png'></td>\n",
    "    </tr>\n",
    "</table>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "wired-junior",
   "metadata": {},
   "outputs": [],
   "source": [
    "/* Investigate user banking activities that preceeded a fraudulent online transaction */\n",
    "\n",
    "SELECT * FROM NPATH\n",
    "    (ON (\n",
    "        SELECT\n",
    "                bh.customer_identifier,\n",
    "                bh.interaction_session_number,\n",
    "                bh.interaction_timestamp,\n",
    "                bh.interaction_type,\n",
    "                txn.amount,\n",
    "                c.firstname,\n",
    "                c.lastname\n",
    "\n",
    "\n",
    "                FROM demo.txn_history_ofs txn, demo.Banking_History_S3 bh, \n",
    "                    demo.Customers_BFS c, demo.Accounts_Mapping_BFS am\n",
    "\n",
    "\n",
    "                WHERE txn.isFraud = 1 AND\n",
    "                    txn.nameOrig= am.nameOrig AND\n",
    "                    am.customer_identifier = bh.customer_identifier AND\n",
    "                    am.customer_identifier = c.customer_identifier\n",
    "        )\n",
    "        \n",
    "        PARTITION BY interaction_session_number\n",
    "        ORDER BY interaction_timestamp\n",
    "    USING\n",
    "        MODE (NONOVERLAPPING)\n",
    "        SYMBOLS (TRUE AS A)\n",
    "        PATTERN ('A*')\n",
    "        RESULT (FIRST (interaction_timestamp of A) as start_time, \n",
    "                FIRST (customer_identifier of A) as customer_identifier,\n",
    "                FIRST (firstname of A) as firstname, \n",
    "                FIRST (lastname of A) as lastname,\n",
    "                FIRST (amount of A) as fraud_amount,\n",
    "                ACCUMULATE (interaction_type of ANY(A)) as path,\n",
    "                FIRST (interaction_session_number of A) as interaction_session_number\n",
    "                \n",
    "    ));"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "tamil-fruit",
   "metadata": {},
   "source": [
    "<hr>\n",
    "\n",
    "<p style = 'font-size:28px;font-family:Arial;color:#00233C'><b>Seamlessly Join Data Across the Data Fabric</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>QueryGrid</b> provides seamless, high-performance data access with pushdown processing and parallel data movement across systems in heterogeneous analytical environments, including hybrid and multi-cloud.</p>\n",
    "\n",
    "<table style = 'width:75%;table-layout:fixed;font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <tr style = 'text-align:top'>\n",
    "        <td style = 'width:50%'>\n",
    "        <ol>\n",
    "            <br>\n",
    "            <li><b>Orchestrate</b> query execution across databases and platforms without migrating data</li>\n",
    "            <br>\n",
    "            <li><b>Launch</b> queries in place and execute pushdown processing across systems</li>\n",
    "            <br>\n",
    "            <li><b>Join</b> and transfer data across tables and systems from multiple sources</li>\n",
    "            <br>\n",
    "            <li><b>Reduce</b> data movement and minimize egress charges</li>\n",
    "            <br>\n",
    "        </ol></td>\n",
    "        <td><img src = 'images/QG_Overview.png' width = '400' ></td>\n",
    "    </tr>\n",
    "</table>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>For this demonstration, the <b>Transaction History</b> data set has been moved to an external RDBMS connected via VantageCloud Lake QueryGrid.  After executing this query, administrators can view detailed telemetry on data transfer rates, query pushdown, and other metrics in the <b>VantageCloud Lake Console.</b>  Alternatively, users can run an <b>EXPLAIN</b> to show the QueryGrid operations.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "approximate-effort",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT * FROM NPATH \n",
    "    (ON ( \n",
    "        SELECT \n",
    "                bh.customer_identifier, \n",
    "                bh.interaction_session_number, \n",
    "                bh.interaction_timestamp, \n",
    "                bh.interaction_type, \n",
    "                txn.amount, \n",
    "                c.firstname, \n",
    "                c.lastname \n",
    " \n",
    " \n",
    "                FROM demo_schema.txn_history@fs_postgres txn, demo.Banking_History_S3 bh,  \n",
    "                    demo.Customers_BFS c, demo.Accounts_Mapping_BFS am \n",
    " \n",
    " \n",
    "                WHERE txn.isFraud = 1 AND \n",
    "                    txn.nameOrig= am.nameOrig AND \n",
    "                    am.customer_identifier = bh.customer_identifier AND \n",
    "                    am.customer_identifier = c.customer_identifier \n",
    "        ) \n",
    "         \n",
    "        PARTITION BY interaction_session_number \n",
    "        ORDER BY interaction_timestamp \n",
    "    USING \n",
    "        MODE (NONOVERLAPPING) \n",
    "        SYMBOLS (TRUE AS A) \n",
    "        PATTERN ('A*') \n",
    "        RESULT (FIRST (interaction_timestamp of A) as start_time,  \n",
    "                FIRST (customer_identifier of A) as customer_identifier, \n",
    "                FIRST (firstname of A) as firstname,  \n",
    "                FIRST (lastname of A) as lastname, \n",
    "                FIRST (amount of A) as fraud_amount, \n",
    "                ACCUMULATE (interaction_type of ANY(A)) as path, \n",
    "                FIRST (interaction_session_number of A) as interaction_session_number )); "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "perceived-reggae",
   "metadata": {},
   "outputs": [],
   "source": [
    "%disconnect lake_demo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "celtic-scroll",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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"
  },
  "toc-autonumbering": false,
  "toc-showmarkdowntxt": true,
  "toc-showtags": false
 },
 "nbformat": 4,
 "nbformat_minor": 5
}