002.Extracting data from Tableau extract to Pandas dataframe with Tableau Hyper API.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "eb274cc3",
   "metadata": {},
   "source": [
    "# Extracting data from Tableau extract to Pandas dataframe with Tableau Hyper API"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5af56fb7",
   "metadata": {},
   "source": [
    "# Download of Tableau Hyper APIs"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fd460ffe",
   "metadata": {},
   "source": [
    "In this workbook I used the Hyper API 0.0.16123 for Python that you can download from [this page](https://www.tableau.com/support/releases/hyper-api/0.0.16123)."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "945dae77",
   "metadata": {},
   "source": [
    "# Setting up environment"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bac69f0e",
   "metadata": {},
   "source": [
    "I advice you to make a Python virtual environment and then install the API in it. You can follow [this guide](https://help.tableau.com/current/api/hyper_api/en-us/docs/hyper_api_installing.html) to learn how to install the downloaded files."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "948ca469",
   "metadata": {},
   "source": [
    "## Modules"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "0454ebd6",
   "metadata": {},
   "outputs": [],
   "source": [
    "import shutil\n",
    "from pathlib import Path\n",
    "import pandas as pd\n",
    "from tableauhyperapi import HyperProcess, Telemetry, Connection, TableName, HyperException"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "355f87c9",
   "metadata": {},
   "source": [
    "## Variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "d4df1a90",
   "metadata": {},
   "outputs": [],
   "source": [
    "hyperPath='02.Unpackaged Tableau workbooks/Data/Datasources/salaries-by-college-type (1) (salaries-by-college-type (1)).hyper'\n",
    "workingDir='03.Working folder'\n",
    "workingFile=workingDir+'/working.hyper'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5ef5cf17",
   "metadata": {},
   "source": [
    "## Functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "28ccfa98",
   "metadata": {},
   "outputs": [],
   "source": [
    "def denormalizeHyper(src,dst):\n",
    "    path_to_database = Path(shutil.copy(src=src,dst=dst)).resolve()\n",
    "    return \"File created: %s\" % (workingFile)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "621e0e56",
   "metadata": {},
   "outputs": [],
   "source": [
    "def getSchemaNames(database):\n",
    "    with HyperProcess(telemetry=Telemetry.SEND_USAGE_DATA_TO_TABLEAU) as hyper:\n",
    "        with Connection(endpoint=hyper.endpoint,database=database) as connection:\n",
    "            schema_names= connection.catalog.get_schema_names()\n",
    "    return schema_names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "8ce4f9c4",
   "metadata": {},
   "outputs": [],
   "source": [
    "def getTableNames(database,schema):\n",
    "    with HyperProcess(telemetry=Telemetry.SEND_USAGE_DATA_TO_TABLEAU) as hyper:\n",
    "        with Connection(endpoint=hyper.endpoint,database=database) as connection:\n",
    "            table_names = connection.catalog.get_table_names(schema=schema)\n",
    "    return table_names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "c1bfe925",
   "metadata": {},
   "outputs": [],
   "source": [
    "def rowsInTable(database,schema,table):\n",
    "    with HyperProcess(telemetry=Telemetry.SEND_USAGE_DATA_TO_TABLEAU) as hyper:\n",
    "        with Connection(endpoint=hyper.endpoint,database=database) as connection:\n",
    "            table_name = TableName(schema, table)\n",
    "            rows_in_table = connection.execute_list_query(query=f\"SELECT * FROM {table_name}\")\n",
    "    return rows_in_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "a9d3d58e",
   "metadata": {},
   "outputs": [],
   "source": [
    "def rowsToPandas(rows_in_table):\n",
    "    dfRows = pd.DataFrame(rows_in_table)\n",
    "    return dfRows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "3a1c837b",
   "metadata": {},
   "outputs": [],
   "source": [
    "def columnNamesPrint(database,schema,table):\n",
    "    with HyperProcess(telemetry=Telemetry.SEND_USAGE_DATA_TO_TABLEAU) as hyper:\n",
    "        with Connection(endpoint=hyper.endpoint,database=database) as connection:\n",
    "            table_name = TableName(schema, table)\n",
    "            table_definition = connection.catalog.get_table_definition(name=table_name)\n",
    "            print(\"List of columns of %s table:\" % (table))\n",
    "            for column in table_definition.columns:\n",
    "                print(f'''- {column.name} \n",
    "    type={column.type} and nullability={column.nullability}''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "18f28436",
   "metadata": {},
   "outputs": [],
   "source": [
    "def columnNamesList(database,schema,table):\n",
    "    with HyperProcess(telemetry=Telemetry.SEND_USAGE_DATA_TO_TABLEAU) as hyper:\n",
    "        with Connection(endpoint=hyper.endpoint,database=database) as connection:\n",
    "            table_name = TableName(schema, table)\n",
    "            table_definition = connection.catalog.get_table_definition(name=table_name)\n",
    "            col=[]\n",
    "            for column in table_definition.columns:\n",
    "                col.append(column.name.unescaped)\n",
    "    return col"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b67d0da8",
   "metadata": {},
   "source": [
    "## Runnung extraction"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "29d5063f",
   "metadata": {},
   "source": [
    "First of all, we have to denormalize data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "f126ef06",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'File created: 03.Working folder/working.hyper'"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "denormalizeHyper(hyperPath,workingFile)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f3a071ff",
   "metadata": {},
   "source": [
    "Since this point, we will go further using the _working.hyper_ file and not the original as it is normalized."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "873ad5a3",
   "metadata": {},
   "source": [
    "next, we wanna investigate the _schemas_ included in the extract"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "dc06b019",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[SchemaName('Extract'), SchemaName('public')]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "getSchemaNames(workingFile)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ac248786",
   "metadata": {},
   "source": [
    "and, for both of them, get a list of underlying tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "f25eddea",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[TableName('Extract', 'Extract')]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "getTableNames(workingFile,'Extract')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "f6a1ab6b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "getTableNames(workingFile,'public')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7ac1ea47",
   "metadata": {},
   "source": [
    "Easily, we can assume that the data of our interests are stored in the data with _schema=Extract_ and _table=Extract_, so we will query it using the _rowsInTable_ function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "5387cd71",
   "metadata": {},
   "outputs": [],
   "source": [
    "rows=rowsInTable(workingFile,'Extract','Extract')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b23d6a6a",
   "metadata": {},
   "source": [
    "At this point we are ready to move our data from the rows variable to a Pandas dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "2586c530",
   "metadata": {},
   "outputs": [],
   "source": [
    "df=rowsToPandas(rows)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "5ba56d14",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Massachusetts Institute of Technology (MIT)</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>72200</td>\n",
       "      <td>126000</td>\n",
       "      <td>76800</td>\n",
       "      <td>99200</td>\n",
       "      <td>168000</td>\n",
       "      <td>220000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Polytechnic University of New York, Brooklyn</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>62400</td>\n",
       "      <td>114000</td>\n",
       "      <td>66800</td>\n",
       "      <td>96000</td>\n",
       "      <td>143000</td>\n",
       "      <td>190000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Worcester Polytechnic Institute (WPI)</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>61000</td>\n",
       "      <td>114000</td>\n",
       "      <td>80000</td>\n",
       "      <td>91200</td>\n",
       "      <td>137000</td>\n",
       "      <td>180000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Carnegie Mellon University (CMU)</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>61800</td>\n",
       "      <td>111000</td>\n",
       "      <td>63300</td>\n",
       "      <td>80100</td>\n",
       "      <td>150000</td>\n",
       "      <td>209000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Rensselaer Polytechnic Institute (RPI)</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>61100</td>\n",
       "      <td>110000</td>\n",
       "      <td>71600</td>\n",
       "      <td>85500</td>\n",
       "      <td>140000</td>\n",
       "      <td>182000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              0            1      2       3  \\\n",
       "0   Massachusetts Institute of Technology (MIT)  Engineering  72200  126000   \n",
       "1  Polytechnic University of New York, Brooklyn  Engineering  62400  114000   \n",
       "2         Worcester Polytechnic Institute (WPI)  Engineering  61000  114000   \n",
       "3              Carnegie Mellon University (CMU)  Engineering  61800  111000   \n",
       "4        Rensselaer Polytechnic Institute (RPI)  Engineering  61100  110000   \n",
       "\n",
       "       4      5       6       7  \n",
       "0  76800  99200  168000  220000  \n",
       "1  66800  96000  143000  190000  \n",
       "2  80000  91200  137000  180000  \n",
       "3  63300  80100  150000  209000  \n",
       "4  71600  85500  140000  182000  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "53887ac9",
   "metadata": {},
   "source": [
    "As shown in the previous output we are still missing the column names, so we will extract them using the _columnNamesList_ function and then use the list to map the data frame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "08d06b92",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "List of columns of Extract table:\n",
      "- \"School Name\" \n",
      "    type=TEXT and nullability=Nullability.NULLABLE\n",
      "- \"School Type\" \n",
      "    type=TEXT and nullability=Nullability.NULLABLE\n",
      "- \"Starting Median Salary\" \n",
      "    type=BIG_INT and nullability=Nullability.NULLABLE\n",
      "- \"Mid-Career Median Salary\" \n",
      "    type=BIG_INT and nullability=Nullability.NULLABLE\n",
      "- \"Mid-Career 10th Percentile Salary\" \n",
      "    type=BIG_INT and nullability=Nullability.NULLABLE\n",
      "- \"Mid-Career 25th Percentile Salary\" \n",
      "    type=BIG_INT and nullability=Nullability.NULLABLE\n",
      "- \"Mid-Career 75th Percentile Salary\" \n",
      "    type=BIG_INT and nullability=Nullability.NULLABLE\n",
      "- \"Mid-Career 90th Percentile Salary\" \n",
      "    type=BIG_INT and nullability=Nullability.NULLABLE\n"
     ]
    }
   ],
   "source": [
    "columnNamesPrint(workingFile,'Extract','Extract')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "f56f0159",
   "metadata": {},
   "outputs": [],
   "source": [
    "col=columnNamesList(workingFile,'Extract','Extract')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "babc8a0f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['School Name',\n",
       " 'School Type',\n",
       " 'Starting Median Salary',\n",
       " 'Mid-Career Median Salary',\n",
       " 'Mid-Career 10th Percentile Salary',\n",
       " 'Mid-Career 25th Percentile Salary',\n",
       " 'Mid-Career 75th Percentile Salary',\n",
       " 'Mid-Career 90th Percentile Salary']"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "col"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "3ceb9739",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.columns=col"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "f740394c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>School Name</th>\n",
       "      <th>School Type</th>\n",
       "      <th>Starting Median Salary</th>\n",
       "      <th>Mid-Career Median Salary</th>\n",
       "      <th>Mid-Career 10th Percentile Salary</th>\n",
       "      <th>Mid-Career 25th Percentile Salary</th>\n",
       "      <th>Mid-Career 75th Percentile Salary</th>\n",
       "      <th>Mid-Career 90th Percentile Salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Massachusetts Institute of Technology (MIT)</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>72200</td>\n",
       "      <td>126000</td>\n",
       "      <td>76800</td>\n",
       "      <td>99200</td>\n",
       "      <td>168000</td>\n",
       "      <td>220000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Polytechnic University of New York, Brooklyn</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>62400</td>\n",
       "      <td>114000</td>\n",
       "      <td>66800</td>\n",
       "      <td>96000</td>\n",
       "      <td>143000</td>\n",
       "      <td>190000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Worcester Polytechnic Institute (WPI)</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>61000</td>\n",
       "      <td>114000</td>\n",
       "      <td>80000</td>\n",
       "      <td>91200</td>\n",
       "      <td>137000</td>\n",
       "      <td>180000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Carnegie Mellon University (CMU)</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>61800</td>\n",
       "      <td>111000</td>\n",
       "      <td>63300</td>\n",
       "      <td>80100</td>\n",
       "      <td>150000</td>\n",
       "      <td>209000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Rensselaer Polytechnic Institute (RPI)</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>61100</td>\n",
       "      <td>110000</td>\n",
       "      <td>71600</td>\n",
       "      <td>85500</td>\n",
       "      <td>140000</td>\n",
       "      <td>182000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    School Name  School Type  \\\n",
       "0   Massachusetts Institute of Technology (MIT)  Engineering   \n",
       "1  Polytechnic University of New York, Brooklyn  Engineering   \n",
       "2         Worcester Polytechnic Institute (WPI)  Engineering   \n",
       "3              Carnegie Mellon University (CMU)  Engineering   \n",
       "4        Rensselaer Polytechnic Institute (RPI)  Engineering   \n",
       "\n",
       "   Starting Median Salary  Mid-Career Median Salary  \\\n",
       "0                   72200                    126000   \n",
       "1                   62400                    114000   \n",
       "2                   61000                    114000   \n",
       "3                   61800                    111000   \n",
       "4                   61100                    110000   \n",
       "\n",
       "   Mid-Career 10th Percentile Salary  Mid-Career 25th Percentile Salary  \\\n",
       "0                              76800                              99200   \n",
       "1                              66800                              96000   \n",
       "2                              80000                              91200   \n",
       "3                              63300                              80100   \n",
       "4                              71600                              85500   \n",
       "\n",
       "   Mid-Career 75th Percentile Salary  Mid-Career 90th Percentile Salary  \n",
       "0                             168000                             220000  \n",
       "1                             143000                             190000  \n",
       "2                             137000                             180000  \n",
       "3                             150000                             209000  \n",
       "4                             140000                             182000  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "venv",
   "language": "python",
   "name": "venv"
  },
  "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.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}