1_Load_Base_Demo_Data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "oriented-setup",
   "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",
    "       VantageCloud Lake Base Data Loading\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",
    "<hr>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eleven-bargain",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import the minimal number of packages\n",
    "\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "\n",
    "import json\n",
    "import pandas as pd\n",
    "import teradatasql\n",
    "\n",
    "from teradataml import *"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "expanded-vietnam",
   "metadata": {},
   "source": [
    "# Section 1 - Load Data #\n",
    "\n",
    "### For this demo, load two BFS Tables and one OFS table ###\n",
    "1. **Demo.Customers_BFS** Customer Dimension table for banking data set.\n",
    "2. **Demo.Accounts_Mapping_BFS** Dimension table for mapping Customers to Accounts Fraud Detection data set.\n",
    "3. **Demo_OFS.Txn_History** Transaction history table for use with the OAF standalone demo."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "criminal-polls",
   "metadata": {},
   "outputs": [],
   "source": [
    "# /* -------------------------------------------------------- */\n",
    "# -- Perform this as SYSDBA.\n",
    "# -- Load demo.Customer_BFS from S3\n",
    "# /* -------------------------------------------------------- */\n",
    "\n",
    "# load vars json\n",
    "with open('vars.json', 'r') as f:\n",
    "    session_vars = json.load(f)\n",
    "\n",
    "name = session_vars['hierarchy']['SYSDBA']['username']\n",
    "pwd = session_vars['hierarchy']['SYSDBA']['password']\n",
    "\n",
    "bucket = session_vars['environment']['bucket']\n",
    "\n",
    "with teradatasql.connect(host = session_vars['environment']['host'], \n",
    "                     user = name, \n",
    "                     password = pwd) as con:\n",
    "    cur = con.cursor()\n",
    "    \n",
    "    try:\n",
    "        cur.execute('DROP TABLE demo.Customers_BFS')\n",
    "    except Exception as e:\n",
    "        if str(e.args).find(\"3807\") >= 1:\n",
    "            pass\n",
    "        else:\n",
    "            raise\n",
    "            \n",
    "    \n",
    "        qry = f'''\n",
    "        CREATE TABLE demo.Customers_BFS,\n",
    "        STORAGE = TD_NDSSTORAGE\n",
    "        AS (\n",
    "            SELECT\n",
    "             id,\n",
    "             customer_identifier,\n",
    "             firstname,\n",
    "             lastname,\n",
    "             email,\n",
    "             phone,\n",
    "             birthday,\n",
    "             streetaddress,\n",
    "             city,\n",
    "             state,\n",
    "             zipcode,\n",
    "             latitude,\n",
    "             longitude,\n",
    "             num_accounts\n",
    "            FROM\n",
    "            (\n",
    "                LOCATION = '/s3/{bucket}.s3.amazonaws.com/DEMO/CUSTOMERS_BFS/manifest.json'\n",
    "                MANIFEST = 'TRUE'\n",
    "                AUTHORIZATION = Repositories.PubAuth\n",
    "            ) as D\n",
    "            ) WITH DATA;\n",
    "            '''\n",
    "        \n",
    "        cur.execute(qry)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "proper-congo",
   "metadata": {},
   "outputs": [],
   "source": [
    "# /* -------------------------------------------------------- */\n",
    "# -- Perform this as SYSDBA.\n",
    "# -- Load demo.Accounts_Mapping_BFS from source file\n",
    "# /* -------------------------------------------------------- */\n",
    "\n",
    "# load vars json\n",
    "with open('vars.json', 'r') as f:\n",
    "    session_vars = json.load(f)\n",
    "\n",
    "name = session_vars['hierarchy']['SYSDBA']['username']\n",
    "pwd = session_vars['hierarchy']['SYSDBA']['password']\n",
    "\n",
    "bucket = session_vars['environment']['bucket']\n",
    "\n",
    "with teradatasql.connect(host = session_vars['environment']['host'], \n",
    "                     user = name, \n",
    "                     password = pwd) as con:\n",
    "    cur = con.cursor()\n",
    "    \n",
    "    try:\n",
    "        cur.execute('DROP TABLE Demo.Accounts_Mapping_BFS')\n",
    "    except Exception as e:\n",
    "        # Table already exists\n",
    "        if str(e.args).find(\"3807\") >= 1:\n",
    "            pass\n",
    "        else:\n",
    "            raise\n",
    "            \n",
    "    \n",
    "    qry = f'''\n",
    "    CREATE MULTISET TABLE demo.Accounts_Mapping_BFS\n",
    "    AS (\n",
    "        SELECT nameOrig,\n",
    "         customer_identifier\n",
    "        FROM\n",
    "        (\n",
    "            LOCATION = '/s3/{bucket}.s3.amazonaws.com/DEMO/ACCOUNTS_MAPPING_BFS/manifest.json'\n",
    "            MANIFEST = 'TRUE'\n",
    "            AUTHORIZATION = Repositories.PubAuth\n",
    "        ) as D\n",
    "        ) WITH DATA;\n",
    "    '''\n",
    "    cur.execute(qry)\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "sitting-sweden",
   "metadata": {},
   "outputs": [],
   "source": [
    "# /* -------------------------------------------------------- */\n",
    "# -- Perform this as SYSDBA.\n",
    "# -- Create the transaction history OFS table for the OAF demo\n",
    "# /* -------------------------------------------------------- */\n",
    "\n",
    "# load vars json\n",
    "with open('vars.json', 'r') as f:\n",
    "    session_vars = json.load(f)\n",
    "\n",
    "name = session_vars['hierarchy']['SYSDBA']['username']\n",
    "pwd = session_vars['hierarchy']['SYSDBA']['password']\n",
    "\n",
    "with teradatasql.connect(host = session_vars['environment']['host'], \n",
    "                     user = name, \n",
    "                     password = pwd) as con:\n",
    "    cur = con.cursor()\n",
    "    \n",
    "    try:\n",
    "        cur.execute('DROP TABLE demo_ofs.Txn_History')\n",
    "    except Exception as e:\n",
    "        # Table already exists\n",
    "        if str(e.args).find(\"3807\") >= 1:\n",
    "            pass\n",
    "        elif str(e.args).find(\"4880\") >= 1:\n",
    "            pass\n",
    "        else:\n",
    "            raise\n",
    "            \n",
    "    \n",
    "    qry = '''\n",
    "    CREATE TABLE demo_ofs.Txn_History,\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",
    "    AS (\n",
    "    /* We can use a SELECT statement to populate the table\n",
    "    using WITH DATA */\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",
    "    \n",
    "    WITH DATA;\n",
    "    '''\n",
    "    try:\n",
    "        cur.execute(qry)\n",
    "    except Exception as e:\n",
    "        # Table already exists\n",
    "        if str(e.args).find(\"3803\") >= 1:\n",
    "            pass "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bottom-drink",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.8.8"
  },
  "toc-showcode": false,
  "toc-showmarkdowntxt": false
 },
 "nbformat": 4,
 "nbformat_minor": 5
}