{
"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": [
"import awswrangler as wr\n",
"import pandas as pd\n",
"from datetime import datetime\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_cols=[\"memory_utilization\"],\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",
" 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",
"\"\"\")"
]
},
{
"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",
" 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",
"\"\"\")"
]
},
{
"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
}