Use the Python SWAT Package on the SAS Viya Platform.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Use the Python SWAT Package on the SAS Viya Platform"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import swat\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "import sys\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "pd.set_option('display.max_columns', None)\n",
    "\n",
    "print(f'Python version:{sys.version.split(\"|\")[0]}')\n",
    "print(f'swat version:{swat.__version__}')\n",
    "print(f'pandas version:{pd.__version__}')\n",
    "print(f'numpy version:{np.__version__}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Connect to the CAS Server\n",
    "**To connect to the CAS server, you need:**\n",
    "- the host name, \n",
    "- the port number, \n",
    "- authentication\n",
    "\n",
    "**Be aware that connecting to the CAS server can be implemented in various ways, so you might need to see your system administrator about how to make a connection. Please follow company policy regarding authentication.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "######## SAS VIYA ON AZURE MARKETPLACE CONNECTION #################################################\n",
    "casport = 5570\n",
    "cashost = 'sas-cas-server-default-client'\n",
    "conn = swat.CAS(cashost, casport, password=os.environ.get('ACCESS_TOKEN'))\n",
    "###################################################################################################\n",
    "\n",
    "######## EXTERNAL JUPYTERHUB ACCESS USING BINARY PROTOCOL###################\n",
    "#cashost ='<CAS_IP>'\n",
    "#conn = swat.CAS(cashost, casport, '<##user##>', '<##password##>')\n",
    "############################################################################\n",
    "\n",
    "#######################EXTERNAL JUPYTERHUB ACCESS USING HTTP PROTOCOL ##############################################################################\n",
    "#conn = swat.CAS('https://<##prefix##>.<##region##>.cloudapp.azure.com/cas-shared-default-http', username='<##user##>', password ='<##password##>')\n",
    "####################################################################################################################################################"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "type(conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Test the CAS connection and view the SAS Viya version."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.about()['About']['Viya Version']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Explore the Available Data on the CAS Server "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### a. View Available Caslibs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.caslibInfo()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### b. View Available Data Source Files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.fileInfo(caslib = 'samples')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.fileInfo(caslib = 'casuser')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### c. View Available CAS Tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.tableInfo(caslib = 'samples')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.tableInfo(caslib = 'casuser')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Load Data into Memory on the CAS Server (Client Side)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.read_csv(r'https://support.sas.com/documentation/onlinedoc/viya/exampledatasets/home_equity.csv', ## Client-side file to load into memory\n",
    "              casout = {'name':'home_equity',                                                          ## Output in-memory CAS table information\n",
    "                        'caslib':'casuser', \n",
    "                        'replace':True})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.tableInfo(caslib = 'casuser')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Explore the CAS Table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### a. Reference the CAS table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "castbl = conn.CASTable('home_equity', caslib = 'casuser')\n",
    "\n",
    "display(type(castbl), castbl)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "castbl.tableDetails()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### b. Preview the CAS Table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can execute the SWAT head method on a CASTable object to return five rows to your Python client. The head method executes in the CAS server, and the CAS server returns five rows to the Python client as a SASDataFrame object."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = castbl.head()\n",
    "\n",
    "display(type(df), df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### c. Basic CAS Table Exploration"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "View the number of rows and columns in a CAS table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "castbl.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Show CAS table column information."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "castbl.columnInfo()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Find the count of unique values in a CAS table using the SWAT package value_counts method. The CAS server summarizes the data and return a series to the Python client."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = (castbl                ## CAS table\n",
    "      .JOB                  ## CAS table column\n",
    "      .value_counts()       ## SWAT package value_counts method\n",
    ")\n",
    "\n",
    "\n",
    "## Display the type and value of the df object\n",
    "display(type(df), df)\n",
    "\n",
    "\n",
    "## Plot the Series on the Python client using Pandas\n",
    "df.plot(kind='bar', figsize=(8,6));"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### d. Execute SQL in the CAS Server"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Load the fedSQL action set to execute SQL in CAS.\n",
    "conn.loadActionSet('fedSQL')\n",
    "\n",
    "## Store a simple SQL query\n",
    "myQuery = '''\n",
    "    select Reason, count(*) as TotalCount\n",
    "        from casuser.home_equity\n",
    "        group by Reason\n",
    "        order by TotalCount desc\n",
    "'''\n",
    "\n",
    "## Execute the query in the CAS server\n",
    "cr = conn.execDirect(query = myQuery)\n",
    "\n",
    "display(type(cr), cr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cr['Result Set']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Prepre the CAS Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "castbl.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### a. Create CAS Table Columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create two new columns in the CAS table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "castbl.eval('DIFF = VALUE - MORTDUE')\n",
    "castbl.eval(\"LOAN_STATUS = ifc(BAD=0,'Paid','Default')\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "View the CASTable object."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "display(castbl)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "castbl.params"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Execute the head method on the CASTable object. Notice that the new columns were created."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "castbl.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### b. Create a New CAS Table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create a new CAS table that contains the two new columns from above."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "castbl.copyTable(casout={'name':'home_equity_final', \n",
    "                         'caslib':'casuser', \n",
    "                         'label':'home_equity with two new calculated columns'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Confirm that the table is available."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.tableInfo(caslib = 'casuser')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Reference and preview the new **HOME_EQUITY_FINAL** CAS table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_castbl = conn.CASTable('HOME_EQUITY_FINAL', caslib = 'casuser')\n",
    "final_castbl.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### c. Save the CAS Table to the Data Source\n",
    "Save a CAS table to a caslib's data source. This is simalar to saving a DataFrame back to disk using a to_ method in pandas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "save_file_types = ['parquet', 'sashdat', 'csv']\n",
    "\n",
    "for ftype in save_file_types:\n",
    "    final_castbl.save(name = f'home_equity_final.{ftype}', caslib = 'casuser')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "View the newly saved files in the **Casuser** caslib."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.fileInfo(caslib = 'casuser')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. Session-Scope versus Global-Scope Tables\n",
    "By default, when you load a table into memory, the table has session scope. This means that the table is available only to the session that it was created in. For ad hoc data access and analysis, session-scope tables are preferred because session-scope tables do not require access control checks or any form of locking for concurrent access.\n",
    "\n",
    "The only disadvantage to a session-scope table is that no other sessions can access the same table. For example, if you want shared access to a single copy of an in-memory table, then a session-scope table does not work. In that case, a global-scope table can provide the shared access.\n",
    "\n",
    "**Session-scope tables**\n",
    "- Best used for general purpose programming.\n",
    "- Typically provide better performance than global-scope tables because concurrency locks are not used.\n",
    "\n",
    "**Global-scope tables**\n",
    "- Best used for tables that are accessed by a large number of users, especially other SAS Viya visual interfaces.\n",
    "- A global-scope table cannot be replaced. You must drop it and load the replacement data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### a. Session-Scope Tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.tableInfo(caslib = 'casuser')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "View the data source files in the **Casuser** caslib. Notice the parquet file is available."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.fileInfo(caslib = 'casuser')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### b. Global-Scope Tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Load the server side parquet file into memory and promote it to global scope. This enables other SAS Viya applications and users who have access to the caslib to access the CAS table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.loadTable(path = 'home_equity_final.parquet', caslib = 'casuser',   ## Input file to load into memory\n",
    "               casout = {'name':'home_equity_final_global',                     ## Output CAS table information\n",
    "                         'caslib':'casuser', \n",
    "                         'promote':True})                          "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "View available CAS tables. Notice that the Global value is 1."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.tableInfo(caslib = 'casuser')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### c. Open SAS Visual Analytics and Access the CAS Table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "View available CAS tables in SAS Visual Analytics."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### d. Drop a Global-Scope Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ## Terminate the connection to the CAS server\n",
    "conn.terminate()\n",
    "\n",
    "## Reconnect to the CAS server\n",
    "conn = swat.CAS(cashost, casport, password=os.environ.get('ACCESS_TOKEN'))\n",
    "\n",
    "## View available CAS tables in the Casuser caslib\n",
    "conn.tableInfo(caslib = 'casuser')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Drop a CAS table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.dropTable('home_equity_final_global', caslib = 'casuser')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### e. Delete a Data Source File"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "deleteDataSourceFiles = ['home_equity_final.parquet', 'home_equity_final.sashdat', 'home_equity_final.csv']\n",
    "\n",
    "for file in deleteDataSourceFiles:\n",
    "    conn.deleteSource(source = file, caslib = 'casuser')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. Terminate the CAS Connection\n",
    "When you are done, it's best practice to terminate your CAS connection."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.terminate()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "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.16"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}