tutorials/027 - Amazon Timestream 2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[![AWS SDK for pandas](_static/logo.png \"AWS SDK for pandas\")](https://github.com/aws/aws-sdk-pandas)\n",
    "\n",
    "# 27 - Amazon Timestream - Example 2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reading test data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "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>index</th>\n",
       "      <th>region</th>\n",
       "      <th>az</th>\n",
       "      <th>hostname</th>\n",
       "      <th>measure_kind</th>\n",
       "      <th>measure</th>\n",
       "      <th>time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>us-east-1</td>\n",
       "      <td>us-east-1a</td>\n",
       "      <td>host-fj2hx</td>\n",
       "      <td>cpu_utilization</td>\n",
       "      <td>21.394363</td>\n",
       "      <td>2020-12-08 16:18:47.599597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>us-east-1</td>\n",
       "      <td>us-east-1a</td>\n",
       "      <td>host-fj2hx</td>\n",
       "      <td>memory_utilization</td>\n",
       "      <td>68.563420</td>\n",
       "      <td>2020-12-08 16:18:47.599597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>us-east-1</td>\n",
       "      <td>us-east-1a</td>\n",
       "      <td>host-6kMPE</td>\n",
       "      <td>cpu_utilization</td>\n",
       "      <td>17.144579</td>\n",
       "      <td>2020-12-08 16:18:47.599597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>us-east-1</td>\n",
       "      <td>us-east-1a</td>\n",
       "      <td>host-6kMPE</td>\n",
       "      <td>memory_utilization</td>\n",
       "      <td>73.507870</td>\n",
       "      <td>2020-12-08 16:18:47.599597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>us-east-1</td>\n",
       "      <td>us-east-1a</td>\n",
       "      <td>host-sxj7X</td>\n",
       "      <td>cpu_utilization</td>\n",
       "      <td>26.584865</td>\n",
       "      <td>2020-12-08 16:18:47.599597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125995</th>\n",
       "      <td>125995</td>\n",
       "      <td>eu-north-1</td>\n",
       "      <td>eu-north-1c</td>\n",
       "      <td>host-De8RB</td>\n",
       "      <td>memory_utilization</td>\n",
       "      <td>68.063468</td>\n",
       "      <td>2020-12-08 16:18:47.599597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125996</th>\n",
       "      <td>125996</td>\n",
       "      <td>eu-north-1</td>\n",
       "      <td>eu-north-1c</td>\n",
       "      <td>host-2z8tn</td>\n",
       "      <td>memory_utilization</td>\n",
       "      <td>72.203680</td>\n",
       "      <td>2020-12-08 16:18:47.599597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125997</th>\n",
       "      <td>125997</td>\n",
       "      <td>eu-north-1</td>\n",
       "      <td>eu-north-1c</td>\n",
       "      <td>host-2z8tn</td>\n",
       "      <td>cpu_utilization</td>\n",
       "      <td>29.212219</td>\n",
       "      <td>2020-12-08 16:18:47.599597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125998</th>\n",
       "      <td>125998</td>\n",
       "      <td>eu-north-1</td>\n",
       "      <td>eu-north-1c</td>\n",
       "      <td>host-9FczW</td>\n",
       "      <td>memory_utilization</td>\n",
       "      <td>71.746134</td>\n",
       "      <td>2020-12-08 16:18:47.599597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125999</th>\n",
       "      <td>125999</td>\n",
       "      <td>eu-north-1</td>\n",
       "      <td>eu-north-1c</td>\n",
       "      <td>host-9FczW</td>\n",
       "      <td>cpu_utilization</td>\n",
       "      <td>1.677793</td>\n",
       "      <td>2020-12-08 16:18:47.599597</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>126000 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         index      region           az    hostname        measure_kind  \\\n",
       "0            0   us-east-1   us-east-1a  host-fj2hx     cpu_utilization   \n",
       "1            1   us-east-1   us-east-1a  host-fj2hx  memory_utilization   \n",
       "2            2   us-east-1   us-east-1a  host-6kMPE     cpu_utilization   \n",
       "3            3   us-east-1   us-east-1a  host-6kMPE  memory_utilization   \n",
       "4            4   us-east-1   us-east-1a  host-sxj7X     cpu_utilization   \n",
       "...        ...         ...          ...         ...                 ...   \n",
       "125995  125995  eu-north-1  eu-north-1c  host-De8RB  memory_utilization   \n",
       "125996  125996  eu-north-1  eu-north-1c  host-2z8tn  memory_utilization   \n",
       "125997  125997  eu-north-1  eu-north-1c  host-2z8tn     cpu_utilization   \n",
       "125998  125998  eu-north-1  eu-north-1c  host-9FczW  memory_utilization   \n",
       "125999  125999  eu-north-1  eu-north-1c  host-9FczW     cpu_utilization   \n",
       "\n",
       "          measure                       time  \n",
       "0       21.394363 2020-12-08 16:18:47.599597  \n",
       "1       68.563420 2020-12-08 16:18:47.599597  \n",
       "2       17.144579 2020-12-08 16:18:47.599597  \n",
       "3       73.507870 2020-12-08 16:18:47.599597  \n",
       "4       26.584865 2020-12-08 16:18:47.599597  \n",
       "...           ...                        ...  \n",
       "125995  68.063468 2020-12-08 16:18:47.599597  \n",
       "125996  72.203680 2020-12-08 16:18:47.599597  \n",
       "125997  29.212219 2020-12-08 16:18:47.599597  \n",
       "125998  71.746134 2020-12-08 16:18:47.599597  \n",
       "125999   1.677793 2020-12-08 16:18:47.599597  \n",
       "\n",
       "[126000 rows x 7 columns]"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from datetime import datetime\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "import awswrangler as wr\n",
    "\n",
    "df = pd.read_csv(\n",
    "    \"https://raw.githubusercontent.com/aws/amazon-timestream-tools/master/sample_apps/data/sample.csv\",\n",
    "    names=[\n",
    "        \"ignore0\",\n",
    "        \"region\",\n",
    "        \"ignore1\",\n",
    "        \"az\",\n",
    "        \"ignore2\",\n",
    "        \"hostname\",\n",
    "        \"measure_kind\",\n",
    "        \"measure\",\n",
    "        \"ignore3\",\n",
    "        \"ignore4\",\n",
    "        \"ignore5\",\n",
    "    ],\n",
    "    usecols=[\"region\", \"az\", \"hostname\", \"measure_kind\", \"measure\"],\n",
    ")\n",
    "df[\"time\"] = datetime.now()\n",
    "df.reset_index(inplace=True, drop=False)\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating resources"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "wr.timestream.create_database(\"sampleDB\")\n",
    "wr.timestream.create_table(\"sampleDB\", \"sampleTable\", memory_retention_hours=1, magnetic_retention_days=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Write CPU_UTILIZATION records"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>index</th>\n",
       "      <th>region</th>\n",
       "      <th>az</th>\n",
       "      <th>hostname</th>\n",
       "      <th>measure_kind</th>\n",
       "      <th>cpu_utilization</th>\n",
       "      <th>time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>us-east-1</td>\n",
       "      <td>us-east-1a</td>\n",
       "      <td>host-fj2hx</td>\n",
       "      <td>cpu_utilization</td>\n",
       "      <td>21.394363</td>\n",
       "      <td>2020-12-08 16:18:47.599597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>us-east-1</td>\n",
       "      <td>us-east-1a</td>\n",
       "      <td>host-6kMPE</td>\n",
       "      <td>cpu_utilization</td>\n",
       "      <td>17.144579</td>\n",
       "      <td>2020-12-08 16:18:47.599597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>us-east-1</td>\n",
       "      <td>us-east-1a</td>\n",
       "      <td>host-sxj7X</td>\n",
       "      <td>cpu_utilization</td>\n",
       "      <td>26.584865</td>\n",
       "      <td>2020-12-08 16:18:47.599597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>us-east-1</td>\n",
       "      <td>us-east-1a</td>\n",
       "      <td>host-ExOui</td>\n",
       "      <td>cpu_utilization</td>\n",
       "      <td>52.930970</td>\n",
       "      <td>2020-12-08 16:18:47.599597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>us-east-1</td>\n",
       "      <td>us-east-1a</td>\n",
       "      <td>host-Bwb3j</td>\n",
       "      <td>cpu_utilization</td>\n",
       "      <td>99.134110</td>\n",
       "      <td>2020-12-08 16:18:47.599597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125990</th>\n",
       "      <td>125990</td>\n",
       "      <td>eu-north-1</td>\n",
       "      <td>eu-north-1c</td>\n",
       "      <td>host-aPtc6</td>\n",
       "      <td>cpu_utilization</td>\n",
       "      <td>89.566125</td>\n",
       "      <td>2020-12-08 16:18:47.599597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125992</th>\n",
       "      <td>125992</td>\n",
       "      <td>eu-north-1</td>\n",
       "      <td>eu-north-1c</td>\n",
       "      <td>host-7ZF9L</td>\n",
       "      <td>cpu_utilization</td>\n",
       "      <td>75.510598</td>\n",
       "      <td>2020-12-08 16:18:47.599597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125994</th>\n",
       "      <td>125994</td>\n",
       "      <td>eu-north-1</td>\n",
       "      <td>eu-north-1c</td>\n",
       "      <td>host-De8RB</td>\n",
       "      <td>cpu_utilization</td>\n",
       "      <td>2.771261</td>\n",
       "      <td>2020-12-08 16:18:47.599597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125997</th>\n",
       "      <td>125997</td>\n",
       "      <td>eu-north-1</td>\n",
       "      <td>eu-north-1c</td>\n",
       "      <td>host-2z8tn</td>\n",
       "      <td>cpu_utilization</td>\n",
       "      <td>29.212219</td>\n",
       "      <td>2020-12-08 16:18:47.599597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125999</th>\n",
       "      <td>125999</td>\n",
       "      <td>eu-north-1</td>\n",
       "      <td>eu-north-1c</td>\n",
       "      <td>host-9FczW</td>\n",
       "      <td>cpu_utilization</td>\n",
       "      <td>1.677793</td>\n",
       "      <td>2020-12-08 16:18:47.599597</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>63000 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         index      region           az    hostname     measure_kind  \\\n",
       "0            0   us-east-1   us-east-1a  host-fj2hx  cpu_utilization   \n",
       "2            2   us-east-1   us-east-1a  host-6kMPE  cpu_utilization   \n",
       "4            4   us-east-1   us-east-1a  host-sxj7X  cpu_utilization   \n",
       "6            6   us-east-1   us-east-1a  host-ExOui  cpu_utilization   \n",
       "8            8   us-east-1   us-east-1a  host-Bwb3j  cpu_utilization   \n",
       "...        ...         ...          ...         ...              ...   \n",
       "125990  125990  eu-north-1  eu-north-1c  host-aPtc6  cpu_utilization   \n",
       "125992  125992  eu-north-1  eu-north-1c  host-7ZF9L  cpu_utilization   \n",
       "125994  125994  eu-north-1  eu-north-1c  host-De8RB  cpu_utilization   \n",
       "125997  125997  eu-north-1  eu-north-1c  host-2z8tn  cpu_utilization   \n",
       "125999  125999  eu-north-1  eu-north-1c  host-9FczW  cpu_utilization   \n",
       "\n",
       "        cpu_utilization                       time  \n",
       "0             21.394363 2020-12-08 16:18:47.599597  \n",
       "2             17.144579 2020-12-08 16:18:47.599597  \n",
       "4             26.584865 2020-12-08 16:18:47.599597  \n",
       "6             52.930970 2020-12-08 16:18:47.599597  \n",
       "8             99.134110 2020-12-08 16:18:47.599597  \n",
       "...                 ...                        ...  \n",
       "125990        89.566125 2020-12-08 16:18:47.599597  \n",
       "125992        75.510598 2020-12-08 16:18:47.599597  \n",
       "125994         2.771261 2020-12-08 16:18:47.599597  \n",
       "125997        29.212219 2020-12-08 16:18:47.599597  \n",
       "125999         1.677793 2020-12-08 16:18:47.599597  \n",
       "\n",
       "[63000 rows x 7 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cpu = df[df.measure_kind == \"cpu_utilization\"].copy()\n",
    "df_cpu.rename(columns={\"measure\": \"cpu_utilization\"}, inplace=True)\n",
    "df_cpu"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "rejected_records = wr.timestream.write(\n",
    "    df=df_cpu,\n",
    "    database=\"sampleDB\",\n",
    "    table=\"sampleTable\",\n",
    "    time_col=\"time\",\n",
    "    measure_col=\"cpu_utilization\",\n",
    "    dimensions_cols=[\"index\", \"region\", \"az\", \"hostname\"],\n",
    ")\n",
    "\n",
    "assert len(rejected_records) == 0"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Batch Load MEMORY_UTILIZATION records"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>index</th>\n",
       "      <th>region</th>\n",
       "      <th>az</th>\n",
       "      <th>hostname</th>\n",
       "      <th>measure_kind</th>\n",
       "      <th>memory_utilization</th>\n",
       "      <th>time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>us-east-1</td>\n",
       "      <td>us-east-1a</td>\n",
       "      <td>host-fj2hx</td>\n",
       "      <td>memory_utilization</td>\n",
       "      <td>68.563420</td>\n",
       "      <td>2020-12-08 16:18:47.599597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>us-east-1</td>\n",
       "      <td>us-east-1a</td>\n",
       "      <td>host-6kMPE</td>\n",
       "      <td>memory_utilization</td>\n",
       "      <td>73.507870</td>\n",
       "      <td>2020-12-08 16:18:47.599597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>us-east-1</td>\n",
       "      <td>us-east-1a</td>\n",
       "      <td>host-sxj7X</td>\n",
       "      <td>memory_utilization</td>\n",
       "      <td>22.401424</td>\n",
       "      <td>2020-12-08 16:18:47.599597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>us-east-1</td>\n",
       "      <td>us-east-1a</td>\n",
       "      <td>host-ExOui</td>\n",
       "      <td>memory_utilization</td>\n",
       "      <td>45.440135</td>\n",
       "      <td>2020-12-08 16:18:47.599597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>us-east-1</td>\n",
       "      <td>us-east-1a</td>\n",
       "      <td>host-Bwb3j</td>\n",
       "      <td>memory_utilization</td>\n",
       "      <td>15.042701</td>\n",
       "      <td>2020-12-08 16:18:47.599597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125991</th>\n",
       "      <td>125991</td>\n",
       "      <td>eu-north-1</td>\n",
       "      <td>eu-north-1c</td>\n",
       "      <td>host-aPtc6</td>\n",
       "      <td>memory_utilization</td>\n",
       "      <td>75.686739</td>\n",
       "      <td>2020-12-08 16:18:47.599597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125993</th>\n",
       "      <td>125993</td>\n",
       "      <td>eu-north-1</td>\n",
       "      <td>eu-north-1c</td>\n",
       "      <td>host-7ZF9L</td>\n",
       "      <td>memory_utilization</td>\n",
       "      <td>18.386152</td>\n",
       "      <td>2020-12-08 16:18:47.599597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125995</th>\n",
       "      <td>125995</td>\n",
       "      <td>eu-north-1</td>\n",
       "      <td>eu-north-1c</td>\n",
       "      <td>host-De8RB</td>\n",
       "      <td>memory_utilization</td>\n",
       "      <td>68.063468</td>\n",
       "      <td>2020-12-08 16:18:47.599597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125996</th>\n",
       "      <td>125996</td>\n",
       "      <td>eu-north-1</td>\n",
       "      <td>eu-north-1c</td>\n",
       "      <td>host-2z8tn</td>\n",
       "      <td>memory_utilization</td>\n",
       "      <td>72.203680</td>\n",
       "      <td>2020-12-08 16:18:47.599597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125998</th>\n",
       "      <td>125998</td>\n",
       "      <td>eu-north-1</td>\n",
       "      <td>eu-north-1c</td>\n",
       "      <td>host-9FczW</td>\n",
       "      <td>memory_utilization</td>\n",
       "      <td>71.746134</td>\n",
       "      <td>2020-12-08 16:18:47.599597</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>63000 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         index      region           az    hostname        measure_kind  \\\n",
       "1            1   us-east-1   us-east-1a  host-fj2hx  memory_utilization   \n",
       "3            3   us-east-1   us-east-1a  host-6kMPE  memory_utilization   \n",
       "5            5   us-east-1   us-east-1a  host-sxj7X  memory_utilization   \n",
       "7            7   us-east-1   us-east-1a  host-ExOui  memory_utilization   \n",
       "9            9   us-east-1   us-east-1a  host-Bwb3j  memory_utilization   \n",
       "...        ...         ...          ...         ...                 ...   \n",
       "125991  125991  eu-north-1  eu-north-1c  host-aPtc6  memory_utilization   \n",
       "125993  125993  eu-north-1  eu-north-1c  host-7ZF9L  memory_utilization   \n",
       "125995  125995  eu-north-1  eu-north-1c  host-De8RB  memory_utilization   \n",
       "125996  125996  eu-north-1  eu-north-1c  host-2z8tn  memory_utilization   \n",
       "125998  125998  eu-north-1  eu-north-1c  host-9FczW  memory_utilization   \n",
       "\n",
       "        memory_utilization                       time  \n",
       "1                68.563420 2020-12-08 16:18:47.599597  \n",
       "3                73.507870 2020-12-08 16:18:47.599597  \n",
       "5                22.401424 2020-12-08 16:18:47.599597  \n",
       "7                45.440135 2020-12-08 16:18:47.599597  \n",
       "9                15.042701 2020-12-08 16:18:47.599597  \n",
       "...                    ...                        ...  \n",
       "125991           75.686739 2020-12-08 16:18:47.599597  \n",
       "125993           18.386152 2020-12-08 16:18:47.599597  \n",
       "125995           68.063468 2020-12-08 16:18:47.599597  \n",
       "125996           72.203680 2020-12-08 16:18:47.599597  \n",
       "125998           71.746134 2020-12-08 16:18:47.599597  \n",
       "\n",
       "[63000 rows x 7 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_memory = df[df.measure_kind == \"memory_utilization\"].copy()\n",
    "df_memory.rename(columns={\"measure\": \"memory_utilization\"}, inplace=True)\n",
    "\n",
    "df_memory"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "response = wr.timestream.batch_load(\n",
    "    df=df_memory,\n",
    "    path=\"s3://bucket/prefix/\",\n",
    "    database=\"sampleDB\",\n",
    "    table=\"sampleTable\",\n",
    "    time_col=\"time\",\n",
    "    measure_cols=[\"memory_utilization\"],\n",
    "    dimensions_cols=[\"index\", \"region\", \"az\", \"hostname\"],\n",
    "    measure_name_col=\"measure_kind\",\n",
    "    report_s3_configuration={\"BucketName\": \"error_bucket\", \"ObjectKeyPrefix\": \"error_prefix\"},\n",
    ")\n",
    "assert response[\"BatchLoadTaskDescription\"][\"ProgressReport\"][\"RecordIngestionFailures\"] == 0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Querying CPU_UTILIZATION"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>hostname</th>\n",
       "      <th>region</th>\n",
       "      <th>az</th>\n",
       "      <th>measure_name</th>\n",
       "      <th>measure_value::double</th>\n",
       "      <th>time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>host-OgvFx</td>\n",
       "      <td>us-west-1</td>\n",
       "      <td>us-west-1a</td>\n",
       "      <td>cpu_utilization</td>\n",
       "      <td>39.617911</td>\n",
       "      <td>2020-12-08 19:18:47.600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>host-rZUNx</td>\n",
       "      <td>eu-north-1</td>\n",
       "      <td>eu-north-1a</td>\n",
       "      <td>cpu_utilization</td>\n",
       "      <td>30.793332</td>\n",
       "      <td>2020-12-08 19:18:47.600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>host-t1kAB</td>\n",
       "      <td>us-east-2</td>\n",
       "      <td>us-east-2b</td>\n",
       "      <td>cpu_utilization</td>\n",
       "      <td>74.453239</td>\n",
       "      <td>2020-12-08 19:18:47.600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>host-RdQRf</td>\n",
       "      <td>us-east-1</td>\n",
       "      <td>us-east-1c</td>\n",
       "      <td>cpu_utilization</td>\n",
       "      <td>76.984448</td>\n",
       "      <td>2020-12-08 19:18:47.600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>host-4Llhu</td>\n",
       "      <td>us-east-1</td>\n",
       "      <td>us-east-1c</td>\n",
       "      <td>cpu_utilization</td>\n",
       "      <td>41.862733</td>\n",
       "      <td>2020-12-08 19:18:47.600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>host-2plqa</td>\n",
       "      <td>us-west-1</td>\n",
       "      <td>us-west-1a</td>\n",
       "      <td>cpu_utilization</td>\n",
       "      <td>34.864762</td>\n",
       "      <td>2020-12-08 19:18:47.600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>host-J3Q4z</td>\n",
       "      <td>us-east-1</td>\n",
       "      <td>us-east-1b</td>\n",
       "      <td>cpu_utilization</td>\n",
       "      <td>71.574266</td>\n",
       "      <td>2020-12-08 19:18:47.600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>host-VIR5T</td>\n",
       "      <td>ap-east-1</td>\n",
       "      <td>ap-east-1a</td>\n",
       "      <td>cpu_utilization</td>\n",
       "      <td>14.017491</td>\n",
       "      <td>2020-12-08 19:18:47.600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>host-G042D</td>\n",
       "      <td>us-east-1</td>\n",
       "      <td>us-east-1c</td>\n",
       "      <td>cpu_utilization</td>\n",
       "      <td>60.199068</td>\n",
       "      <td>2020-12-08 19:18:47.600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>host-8EBHm</td>\n",
       "      <td>us-west-2</td>\n",
       "      <td>us-west-2c</td>\n",
       "      <td>cpu_utilization</td>\n",
       "      <td>96.631624</td>\n",
       "      <td>2020-12-08 19:18:47.600</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     hostname      region           az     measure_name  \\\n",
       "0  host-OgvFx   us-west-1   us-west-1a  cpu_utilization   \n",
       "1  host-rZUNx  eu-north-1  eu-north-1a  cpu_utilization   \n",
       "2  host-t1kAB   us-east-2   us-east-2b  cpu_utilization   \n",
       "3  host-RdQRf   us-east-1   us-east-1c  cpu_utilization   \n",
       "4  host-4Llhu   us-east-1   us-east-1c  cpu_utilization   \n",
       "5  host-2plqa   us-west-1   us-west-1a  cpu_utilization   \n",
       "6  host-J3Q4z   us-east-1   us-east-1b  cpu_utilization   \n",
       "7  host-VIR5T   ap-east-1   ap-east-1a  cpu_utilization   \n",
       "8  host-G042D   us-east-1   us-east-1c  cpu_utilization   \n",
       "9  host-8EBHm   us-west-2   us-west-2c  cpu_utilization   \n",
       "\n",
       "   measure_value::double                    time  \n",
       "0              39.617911 2020-12-08 19:18:47.600  \n",
       "1              30.793332 2020-12-08 19:18:47.600  \n",
       "2              74.453239 2020-12-08 19:18:47.600  \n",
       "3              76.984448 2020-12-08 19:18:47.600  \n",
       "4              41.862733 2020-12-08 19:18:47.600  \n",
       "5              34.864762 2020-12-08 19:18:47.600  \n",
       "6              71.574266 2020-12-08 19:18:47.600  \n",
       "7              14.017491 2020-12-08 19:18:47.600  \n",
       "8              60.199068 2020-12-08 19:18:47.600  \n",
       "9              96.631624 2020-12-08 19:18:47.600  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wr.timestream.query(\n",
    "    \"\"\"\n",
    "    SELECT\n",
    "        hostname, region, az, measure_name, measure_value::double, time\n",
    "    FROM \"sampleDB\".\"sampleTable\"\n",
    "    WHERE measure_name = 'cpu_utilization'\n",
    "    ORDER BY time DESC\n",
    "    LIMIT 10\n",
    "\"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Querying MEMORY_UTILIZATION"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>hostname</th>\n",
       "      <th>region</th>\n",
       "      <th>az</th>\n",
       "      <th>measure_name</th>\n",
       "      <th>measure_value::double</th>\n",
       "      <th>time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>host-7c897</td>\n",
       "      <td>us-west-2</td>\n",
       "      <td>us-west-2b</td>\n",
       "      <td>memory_utilization</td>\n",
       "      <td>63.427726</td>\n",
       "      <td>2020-12-08 19:18:47.600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>host-2z8tn</td>\n",
       "      <td>eu-north-1</td>\n",
       "      <td>eu-north-1c</td>\n",
       "      <td>memory_utilization</td>\n",
       "      <td>41.071368</td>\n",
       "      <td>2020-12-08 19:18:47.600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>host-J3Q4z</td>\n",
       "      <td>us-east-1</td>\n",
       "      <td>us-east-1b</td>\n",
       "      <td>memory_utilization</td>\n",
       "      <td>23.944388</td>\n",
       "      <td>2020-12-08 19:18:47.600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>host-mjrQb</td>\n",
       "      <td>us-east-1</td>\n",
       "      <td>us-east-1b</td>\n",
       "      <td>memory_utilization</td>\n",
       "      <td>69.173431</td>\n",
       "      <td>2020-12-08 19:18:47.600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>host-AyWSI</td>\n",
       "      <td>us-east-1</td>\n",
       "      <td>us-east-1c</td>\n",
       "      <td>memory_utilization</td>\n",
       "      <td>75.591467</td>\n",
       "      <td>2020-12-08 19:18:47.600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>host-Axf0g</td>\n",
       "      <td>us-west-2</td>\n",
       "      <td>us-west-2a</td>\n",
       "      <td>memory_utilization</td>\n",
       "      <td>29.720739</td>\n",
       "      <td>2020-12-08 19:18:47.600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>host-ilMBa</td>\n",
       "      <td>us-east-2</td>\n",
       "      <td>us-east-2b</td>\n",
       "      <td>memory_utilization</td>\n",
       "      <td>71.544134</td>\n",
       "      <td>2020-12-08 19:18:47.600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>host-CWdXX</td>\n",
       "      <td>us-west-2</td>\n",
       "      <td>us-west-2c</td>\n",
       "      <td>memory_utilization</td>\n",
       "      <td>79.792799</td>\n",
       "      <td>2020-12-08 19:18:47.600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>host-8EBHm</td>\n",
       "      <td>us-west-2</td>\n",
       "      <td>us-west-2c</td>\n",
       "      <td>memory_utilization</td>\n",
       "      <td>66.082554</td>\n",
       "      <td>2020-12-08 19:18:47.600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>host-dRIJj</td>\n",
       "      <td>us-east-1</td>\n",
       "      <td>us-east-1c</td>\n",
       "      <td>memory_utilization</td>\n",
       "      <td>86.748960</td>\n",
       "      <td>2020-12-08 19:18:47.600</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     hostname      region           az        measure_name  \\\n",
       "0  host-7c897   us-west-2   us-west-2b  memory_utilization   \n",
       "1  host-2z8tn  eu-north-1  eu-north-1c  memory_utilization   \n",
       "2  host-J3Q4z   us-east-1   us-east-1b  memory_utilization   \n",
       "3  host-mjrQb   us-east-1   us-east-1b  memory_utilization   \n",
       "4  host-AyWSI   us-east-1   us-east-1c  memory_utilization   \n",
       "5  host-Axf0g   us-west-2   us-west-2a  memory_utilization   \n",
       "6  host-ilMBa   us-east-2   us-east-2b  memory_utilization   \n",
       "7  host-CWdXX   us-west-2   us-west-2c  memory_utilization   \n",
       "8  host-8EBHm   us-west-2   us-west-2c  memory_utilization   \n",
       "9  host-dRIJj   us-east-1   us-east-1c  memory_utilization   \n",
       "\n",
       "   measure_value::double                    time  \n",
       "0              63.427726 2020-12-08 19:18:47.600  \n",
       "1              41.071368 2020-12-08 19:18:47.600  \n",
       "2              23.944388 2020-12-08 19:18:47.600  \n",
       "3              69.173431 2020-12-08 19:18:47.600  \n",
       "4              75.591467 2020-12-08 19:18:47.600  \n",
       "5              29.720739 2020-12-08 19:18:47.600  \n",
       "6              71.544134 2020-12-08 19:18:47.600  \n",
       "7              79.792799 2020-12-08 19:18:47.600  \n",
       "8              66.082554 2020-12-08 19:18:47.600  \n",
       "9              86.748960 2020-12-08 19:18:47.600  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wr.timestream.query(\n",
    "    \"\"\"\n",
    "    SELECT\n",
    "        hostname, region, az, measure_name, measure_value::double, time\n",
    "    FROM \"sampleDB\".\"sampleTable\"\n",
    "    WHERE measure_name = 'memory_utilization'\n",
    "    ORDER BY time DESC\n",
    "    LIMIT 10\n",
    "\"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Deleting resources"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "wr.timestream.delete_table(\"sampleDB\", \"sampleTable\")\n",
    "wr.timestream.delete_database(\"sampleDB\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.9.14",
   "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.9.14"
  },
  "pycharm": {
   "stem_cell": {
    "cell_type": "raw",
    "metadata": {
     "collapsed": false
    },
    "source": []
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}