demos/netops/01-generator.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Generate simulated infrastructure telemetry "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: pytimeparse in /User/.pythonlibs/lib/python3.6/site-packages (1.1.8)\n",
      "Looking in indexes: https://test.pypi.org/simple/\n",
      "Requirement already up-to-date: v3io-generator in /User/.pythonlibs/lib/python3.6/site-packages (0.0.27.dev0)\n",
      "Requirement already satisfied: faker in /User/.pythonlibs/lib/python3.6/site-packages (1.0.7)\n",
      "Requirement already satisfied: text-unidecode==1.2 in /User/.pythonlibs/lib/python3.6/site-packages (from faker) (1.2)\n",
      "Requirement already satisfied: six>=1.10 in /conda/lib/python3.6/site-packages (from faker) (1.12.0)\n",
      "Requirement already satisfied: python-dateutil>=2.4 in /conda/lib/python3.6/site-packages (from faker) (2.8.0)\n",
      "Requirement already up-to-date: pyarrow in /User/.pythonlibs/lib/python3.6/site-packages (0.13.0)\n",
      "Requirement already satisfied, skipping upgrade: six>=1.0.0 in /conda/lib/python3.6/site-packages (from pyarrow) (1.12.0)\n",
      "Requirement already satisfied, skipping upgrade: numpy>=1.14 in /conda/lib/python3.6/site-packages (from pyarrow) (1.16.4)\n"
     ]
    }
   ],
   "source": [
    "# Install requiered packages if needed (only once)\n",
    "!pip install pytimeparse\n",
    "!pip install -i https://test.pypi.org/simple/ v3io-generator --upgrade\n",
    "!pip install faker\n",
    "!pip install pyarrow --upgrade"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import time\n",
    "import yaml\n",
    "import pandas as pd\n",
    "import datetime\n",
    "import itertools\n",
    "\n",
    "# DB Connection\n",
    "import v3io_frames as v3f\n",
    "\n",
    "# Data generator\n",
    "from v3io_generator import metrics_generator, deployment_generator"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "General definitions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "env: SAVE_TO_KV=True\n",
      "env: DEPLOYMENT_TABLE=netops_devices\n"
     ]
    }
   ],
   "source": [
    "%env SAVE_TO_KV = True                 \n",
    "%env DEPLOYMENT_TABLE = netops_devices "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create Metadata\n",
    "the following section will create a list of devices which are scattered in multiple datacenters"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def _create_deployment():\n",
    "    print('creating deployment')\n",
    "    # Create meta-data factory\n",
    "    dep_gen = deployment_generator.deployment_generator()\n",
    "    faker=dep_gen.get_faker()\n",
    "\n",
    "    # Design meta-data\n",
    "    dep_gen.add_level(name='company',number=2,level_type=faker.company)\n",
    "    dep_gen.add_level('data_center',number=2,level_type=faker.street_name)\n",
    "    dep_gen.add_level('device',number=2,level_type=faker.msisdn)\n",
    "\n",
    "    # Create meta-data\n",
    "    deployment_df = dep_gen.generate_deployment()\n",
    "    return deployment_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def _is_deployment_exist(path):\n",
    "    # Checking shared path for the devices table\n",
    "    return os.path.exists(f'/v3io/bigdata/{path}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def _get_deployment_from_kv(path):\n",
    "    print(f'Retrieving deployment from {path}')\n",
    "    # Read the devices table from our KV store\n",
    "    deployment_df = client.read(backend='kv', table=path)\n",
    "    \n",
    "    # Reset index to column\n",
    "    deployment_df.index.name = 'device'\n",
    "    deployment_df = deployment_df.reset_index()\n",
    "    return deployment_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def _save_deployment_to_kv(path, df, client=v3f.Client('framesd:8081')):\n",
    "    # Save deployment to our KV store\n",
    "    client.write(backend='kv', table='netops_devices',dfs=df, index_cols=['device'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_or_create_deployment(path, save_to_cloud=False, client=v3f.Client('framesd:8081')):\n",
    "    if _is_deployment_exist(path):\n",
    "        # Get deployment from KV\n",
    "        deployment_df = _get_deployment_from_kv(path)\n",
    "    else:\n",
    "        # Create deployment\n",
    "        deployment_df = _create_deployment()\n",
    "        \n",
    "        if save_to_cloud:\n",
    "            _save_deployment_to_kv(path, deployment_df, client)\n",
    "\n",
    "    return deployment_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create our DB client\n",
    "client = v3f.Client('framesd:8081')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Retrieving deployment from netops_devices\n"
     ]
    },
    {
     "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>device</th>\n",
       "      <th>company</th>\n",
       "      <th>data_center</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1416256267840</td>\n",
       "      <td>Washington-Lambert</td>\n",
       "      <td>Tamara_Greens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9909218583192</td>\n",
       "      <td>Ortega__Nelson_and_Parrish</td>\n",
       "      <td>Gallagher_Springs</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9999032632997</td>\n",
       "      <td>Ortega__Nelson_and_Parrish</td>\n",
       "      <td>Chen_Rest</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0243526550889</td>\n",
       "      <td>Washington-Lambert</td>\n",
       "      <td>Nicole_Trail</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2588823083436</td>\n",
       "      <td>Ortega__Nelson_and_Parrish</td>\n",
       "      <td>Chen_Rest</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6842603445464</td>\n",
       "      <td>Ortega__Nelson_and_Parrish</td>\n",
       "      <td>Gallagher_Springs</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>9025508674926</td>\n",
       "      <td>Washington-Lambert</td>\n",
       "      <td>Tamara_Greens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5079628775825</td>\n",
       "      <td>Washington-Lambert</td>\n",
       "      <td>Nicole_Trail</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          device                     company        data_center\n",
       "0  1416256267840          Washington-Lambert      Tamara_Greens\n",
       "1  9909218583192  Ortega__Nelson_and_Parrish  Gallagher_Springs\n",
       "2  9999032632997  Ortega__Nelson_and_Parrish          Chen_Rest\n",
       "3  0243526550889          Washington-Lambert       Nicole_Trail\n",
       "4  2588823083436  Ortega__Nelson_and_Parrish          Chen_Rest\n",
       "5  6842603445464  Ortega__Nelson_and_Parrish  Gallagher_Springs\n",
       "6  9025508674926          Washington-Lambert      Tamara_Greens\n",
       "7  5079628775825          Washington-Lambert       Nicole_Trail"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "deployment_df = get_or_create_deployment(os.environ['DEPLOYMENT_TABLE'], os.environ['SAVE_TO_KV'])\n",
    "deployment_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Read from our KV to make sure we have backup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>company</th>\n",
       "      <th>data_center</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>device</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6842603445464</th>\n",
       "      <td>Ortega__Nelson_and_Parrish</td>\n",
       "      <td>Gallagher_Springs</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1416256267840</th>\n",
       "      <td>Washington-Lambert</td>\n",
       "      <td>Tamara_Greens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9909218583192</th>\n",
       "      <td>Ortega__Nelson_and_Parrish</td>\n",
       "      <td>Gallagher_Springs</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9999032632997</th>\n",
       "      <td>Ortega__Nelson_and_Parrish</td>\n",
       "      <td>Chen_Rest</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0243526550889</th>\n",
       "      <td>Washington-Lambert</td>\n",
       "      <td>Nicole_Trail</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2588823083436</th>\n",
       "      <td>Ortega__Nelson_and_Parrish</td>\n",
       "      <td>Chen_Rest</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9025508674926</th>\n",
       "      <td>Washington-Lambert</td>\n",
       "      <td>Tamara_Greens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5079628775825</th>\n",
       "      <td>Washington-Lambert</td>\n",
       "      <td>Nicole_Trail</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  company        data_center\n",
       "device                                                      \n",
       "6842603445464  Ortega__Nelson_and_Parrish  Gallagher_Springs\n",
       "1416256267840          Washington-Lambert      Tamara_Greens\n",
       "9909218583192  Ortega__Nelson_and_Parrish  Gallagher_Springs\n",
       "9999032632997  Ortega__Nelson_and_Parrish          Chen_Rest\n",
       "0243526550889          Washington-Lambert       Nicole_Trail\n",
       "2588823083436  Ortega__Nelson_and_Parrish          Chen_Rest\n",
       "9025508674926          Washington-Lambert      Tamara_Greens\n",
       "5079628775825          Washington-Lambert       Nicole_Trail"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# verify the table is written \n",
    "client.read(backend='kv', table='netops_devices')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Add initial values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>device</th>\n",
       "      <th>company</th>\n",
       "      <th>data_center</th>\n",
       "      <th>cpu_utilization</th>\n",
       "      <th>latency</th>\n",
       "      <th>packet_loss</th>\n",
       "      <th>throughput</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1416256267840</td>\n",
       "      <td>Washington-Lambert</td>\n",
       "      <td>Tamara_Greens</td>\n",
       "      <td>70</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>290</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9909218583192</td>\n",
       "      <td>Ortega__Nelson_and_Parrish</td>\n",
       "      <td>Gallagher_Springs</td>\n",
       "      <td>70</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>290</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9999032632997</td>\n",
       "      <td>Ortega__Nelson_and_Parrish</td>\n",
       "      <td>Chen_Rest</td>\n",
       "      <td>70</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>290</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0243526550889</td>\n",
       "      <td>Washington-Lambert</td>\n",
       "      <td>Nicole_Trail</td>\n",
       "      <td>70</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>290</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2588823083436</td>\n",
       "      <td>Ortega__Nelson_and_Parrish</td>\n",
       "      <td>Chen_Rest</td>\n",
       "      <td>70</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>290</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          device                     company        data_center  \\\n",
       "0  1416256267840          Washington-Lambert      Tamara_Greens   \n",
       "1  9909218583192  Ortega__Nelson_and_Parrish  Gallagher_Springs   \n",
       "2  9999032632997  Ortega__Nelson_and_Parrish          Chen_Rest   \n",
       "3  0243526550889          Washington-Lambert       Nicole_Trail   \n",
       "4  2588823083436  Ortega__Nelson_and_Parrish          Chen_Rest   \n",
       "\n",
       "   cpu_utilization  latency  packet_loss  throughput  \n",
       "0               70        0            0         290  \n",
       "1               70        0            0         290  \n",
       "2               70        0            0         290  \n",
       "3               70        0            0         290  \n",
       "4               70        0            0         290  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "deployment_df['cpu_utilization'] = 70\n",
    "deployment_df['latency'] = 0\n",
    "deployment_df['packet_loss'] = 0\n",
    "deployment_df['throughput'] = 290\n",
    "deployment_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Generate simulated metrics per device\n",
    "Metrics schema (describe simulated values) is read from `metrics_configuration.yaml`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/conda/lib/python3.6/site-packages/ipykernel_launcher.py:3: YAMLLoadWarning: calling yaml.load() without Loader=... is deprecated, as the default Loader is unsafe. Please read https://msg.pyyaml.org/load for full details.\n",
      "  This is separate from the ipykernel package so we can avoid doing imports until\n"
     ]
    }
   ],
   "source": [
    "# Load metrics configuration from YAML file\n",
    "with open('configurations/metrics_configuration.yaml', 'r') as f:\n",
    "    metrics_configuration = yaml.load(f)\n",
    "\n",
    "# Create metrics generator based on YAML configuration\n",
    "met_gen = metrics_generator.Generator_df(metrics_configuration, user_hierarchy=deployment_df, initial_timestamp=time.time())\n",
    "metrics = met_gen.generate_range(start_time=datetime.datetime.now(),\n",
    "                                 end_time=datetime.datetime.now()+datetime.timedelta(hours=1),\n",
    "                                 as_df=True,\n",
    "                                 as_iterator=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>cpu_utilization</th>\n",
       "      <th>cpu_utilization_is_error</th>\n",
       "      <th>latency</th>\n",
       "      <th>latency_is_error</th>\n",
       "      <th>packet_loss</th>\n",
       "      <th>packet_loss_is_error</th>\n",
       "      <th>throughput</th>\n",
       "      <th>throughput_is_error</th>\n",
       "      <th>is_error</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>timestamp</th>\n",
       "      <th>data_center</th>\n",
       "      <th>company</th>\n",
       "      <th>device</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">2019-06-17 14:39:24.404731</th>\n",
       "      <th>Tamara_Greens</th>\n",
       "      <th>Washington-Lambert</th>\n",
       "      <th>1416256267840</th>\n",
       "      <td>68.340178</td>\n",
       "      <td>False</td>\n",
       "      <td>4.736444</td>\n",
       "      <td>False</td>\n",
       "      <td>3.086756</td>\n",
       "      <td>False</td>\n",
       "      <td>255.270204</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gallagher_Springs</th>\n",
       "      <th>Ortega__Nelson_and_Parrish</th>\n",
       "      <th>9909218583192</th>\n",
       "      <td>79.825575</td>\n",
       "      <td>False</td>\n",
       "      <td>5.369881</td>\n",
       "      <td>False</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>False</td>\n",
       "      <td>244.680344</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Chen_Rest</th>\n",
       "      <th>Ortega__Nelson_and_Parrish</th>\n",
       "      <th>9999032632997</th>\n",
       "      <td>74.625916</td>\n",
       "      <td>False</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>False</td>\n",
       "      <td>0.810500</td>\n",
       "      <td>False</td>\n",
       "      <td>229.439649</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Nicole_Trail</th>\n",
       "      <th>Washington-Lambert</th>\n",
       "      <th>0243526550889</th>\n",
       "      <td>64.806974</td>\n",
       "      <td>False</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>False</td>\n",
       "      <td>1.177091</td>\n",
       "      <td>False</td>\n",
       "      <td>240.797342</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Chen_Rest</th>\n",
       "      <th>Ortega__Nelson_and_Parrish</th>\n",
       "      <th>2588823083436</th>\n",
       "      <td>57.561720</td>\n",
       "      <td>False</td>\n",
       "      <td>2.732402</td>\n",
       "      <td>False</td>\n",
       "      <td>3.374598</td>\n",
       "      <td>False</td>\n",
       "      <td>238.602076</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                                       cpu_utilization  \\\n",
       "timestamp                  data_center       company                    device                           \n",
       "2019-06-17 14:39:24.404731 Tamara_Greens     Washington-Lambert         1416256267840        68.340178   \n",
       "                           Gallagher_Springs Ortega__Nelson_and_Parrish 9909218583192        79.825575   \n",
       "                           Chen_Rest         Ortega__Nelson_and_Parrish 9999032632997        74.625916   \n",
       "                           Nicole_Trail      Washington-Lambert         0243526550889        64.806974   \n",
       "                           Chen_Rest         Ortega__Nelson_and_Parrish 2588823083436        57.561720   \n",
       "\n",
       "                                                                                       cpu_utilization_is_error  \\\n",
       "timestamp                  data_center       company                    device                                    \n",
       "2019-06-17 14:39:24.404731 Tamara_Greens     Washington-Lambert         1416256267840                     False   \n",
       "                           Gallagher_Springs Ortega__Nelson_and_Parrish 9909218583192                     False   \n",
       "                           Chen_Rest         Ortega__Nelson_and_Parrish 9999032632997                     False   \n",
       "                           Nicole_Trail      Washington-Lambert         0243526550889                     False   \n",
       "                           Chen_Rest         Ortega__Nelson_and_Parrish 2588823083436                     False   \n",
       "\n",
       "                                                                                        latency  \\\n",
       "timestamp                  data_center       company                    device                    \n",
       "2019-06-17 14:39:24.404731 Tamara_Greens     Washington-Lambert         1416256267840  4.736444   \n",
       "                           Gallagher_Springs Ortega__Nelson_and_Parrish 9909218583192  5.369881   \n",
       "                           Chen_Rest         Ortega__Nelson_and_Parrish 9999032632997  0.000000   \n",
       "                           Nicole_Trail      Washington-Lambert         0243526550889  0.000000   \n",
       "                           Chen_Rest         Ortega__Nelson_and_Parrish 2588823083436  2.732402   \n",
       "\n",
       "                                                                                       latency_is_error  \\\n",
       "timestamp                  data_center       company                    device                            \n",
       "2019-06-17 14:39:24.404731 Tamara_Greens     Washington-Lambert         1416256267840             False   \n",
       "                           Gallagher_Springs Ortega__Nelson_and_Parrish 9909218583192             False   \n",
       "                           Chen_Rest         Ortega__Nelson_and_Parrish 9999032632997             False   \n",
       "                           Nicole_Trail      Washington-Lambert         0243526550889             False   \n",
       "                           Chen_Rest         Ortega__Nelson_and_Parrish 2588823083436             False   \n",
       "\n",
       "                                                                                       packet_loss  \\\n",
       "timestamp                  data_center       company                    device                       \n",
       "2019-06-17 14:39:24.404731 Tamara_Greens     Washington-Lambert         1416256267840     3.086756   \n",
       "                           Gallagher_Springs Ortega__Nelson_and_Parrish 9909218583192     0.000000   \n",
       "                           Chen_Rest         Ortega__Nelson_and_Parrish 9999032632997     0.810500   \n",
       "                           Nicole_Trail      Washington-Lambert         0243526550889     1.177091   \n",
       "                           Chen_Rest         Ortega__Nelson_and_Parrish 2588823083436     3.374598   \n",
       "\n",
       "                                                                                       packet_loss_is_error  \\\n",
       "timestamp                  data_center       company                    device                                \n",
       "2019-06-17 14:39:24.404731 Tamara_Greens     Washington-Lambert         1416256267840                 False   \n",
       "                           Gallagher_Springs Ortega__Nelson_and_Parrish 9909218583192                 False   \n",
       "                           Chen_Rest         Ortega__Nelson_and_Parrish 9999032632997                 False   \n",
       "                           Nicole_Trail      Washington-Lambert         0243526550889                 False   \n",
       "                           Chen_Rest         Ortega__Nelson_and_Parrish 2588823083436                 False   \n",
       "\n",
       "                                                                                       throughput  \\\n",
       "timestamp                  data_center       company                    device                      \n",
       "2019-06-17 14:39:24.404731 Tamara_Greens     Washington-Lambert         1416256267840  255.270204   \n",
       "                           Gallagher_Springs Ortega__Nelson_and_Parrish 9909218583192  244.680344   \n",
       "                           Chen_Rest         Ortega__Nelson_and_Parrish 9999032632997  229.439649   \n",
       "                           Nicole_Trail      Washington-Lambert         0243526550889  240.797342   \n",
       "                           Chen_Rest         Ortega__Nelson_and_Parrish 2588823083436  238.602076   \n",
       "\n",
       "                                                                                       throughput_is_error  \\\n",
       "timestamp                  data_center       company                    device                               \n",
       "2019-06-17 14:39:24.404731 Tamara_Greens     Washington-Lambert         1416256267840                False   \n",
       "                           Gallagher_Springs Ortega__Nelson_and_Parrish 9909218583192                False   \n",
       "                           Chen_Rest         Ortega__Nelson_and_Parrish 9999032632997                False   \n",
       "                           Nicole_Trail      Washington-Lambert         0243526550889                False   \n",
       "                           Chen_Rest         Ortega__Nelson_and_Parrish 2588823083436                False   \n",
       "\n",
       "                                                                                       is_error  \n",
       "timestamp                  data_center       company                    device                   \n",
       "2019-06-17 14:39:24.404731 Tamara_Greens     Washington-Lambert         1416256267840     False  \n",
       "                           Gallagher_Springs Ortega__Nelson_and_Parrish 9909218583192     False  \n",
       "                           Chen_Rest         Ortega__Nelson_and_Parrish 9999032632997     False  \n",
       "                           Nicole_Trail      Washington-Lambert         0243526550889     False  \n",
       "                           Chen_Rest         Ortega__Nelson_and_Parrish 2588823083436     False  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.concat(itertools.chain(metrics))\n",
    "df.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Save to Iguazio Time-series Database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# uncomment the line below if you want to reset the TSDB table \n",
    "client.delete(backend='tsdb', table='netops_metrics_jupyter')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create a new table, need to specify estimated sample rate\n",
    "client.create(backend='tsdb', table='netops_metrics_jupyter', attrs={'rate': '1/m'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# write the dataframe into the time-seried DB, note the company,data_center,device indexes are automatically converted to search optimized labels\n",
    "client.write(backend='tsdb', table='netops_metrics_jupyter', dfs=df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Verify that the data was written"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>avg(cpu_utilization)</th>\n",
       "      <th>avg(latency)</th>\n",
       "      <th>avg(packet_loss)</th>\n",
       "      <th>avg(throughput)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>time</th>\n",
       "      <th>company</th>\n",
       "      <th>data_center</th>\n",
       "      <th>device</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2019-06-17 14:34:46</th>\n",
       "      <th>Ortega__Nelson_and_Parrish</th>\n",
       "      <th>Chen_Rest</th>\n",
       "      <th>9999032632997</th>\n",
       "      <td>68.078416</td>\n",
       "      <td>1.423847</td>\n",
       "      <td>0.897193</td>\n",
       "      <td>244.903743</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-06-17 14:39:46</th>\n",
       "      <th>Ortega__Nelson_and_Parrish</th>\n",
       "      <th>Chen_Rest</th>\n",
       "      <th>9999032632997</th>\n",
       "      <td>68.533801</td>\n",
       "      <td>2.367653</td>\n",
       "      <td>0.844910</td>\n",
       "      <td>246.186991</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-06-17 14:44:46</th>\n",
       "      <th>Ortega__Nelson_and_Parrish</th>\n",
       "      <th>Chen_Rest</th>\n",
       "      <th>9999032632997</th>\n",
       "      <td>70.377798</td>\n",
       "      <td>2.096841</td>\n",
       "      <td>0.455499</td>\n",
       "      <td>252.081668</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-06-17 14:49:46</th>\n",
       "      <th>Ortega__Nelson_and_Parrish</th>\n",
       "      <th>Chen_Rest</th>\n",
       "      <th>9999032632997</th>\n",
       "      <td>87.306579</td>\n",
       "      <td>47.314863</td>\n",
       "      <td>21.452884</td>\n",
       "      <td>159.983865</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-06-17 14:54:46</th>\n",
       "      <th>Ortega__Nelson_and_Parrish</th>\n",
       "      <th>Chen_Rest</th>\n",
       "      <th>9999032632997</th>\n",
       "      <td>67.573655</td>\n",
       "      <td>2.729573</td>\n",
       "      <td>0.856969</td>\n",
       "      <td>250.914966</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-06-17 14:59:46</th>\n",
       "      <th>Ortega__Nelson_and_Parrish</th>\n",
       "      <th>Chen_Rest</th>\n",
       "      <th>9999032632997</th>\n",
       "      <td>69.896618</td>\n",
       "      <td>1.819913</td>\n",
       "      <td>0.666666</td>\n",
       "      <td>251.590818</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-06-17 15:04:46</th>\n",
       "      <th>Ortega__Nelson_and_Parrish</th>\n",
       "      <th>Chen_Rest</th>\n",
       "      <th>9999032632997</th>\n",
       "      <td>69.737311</td>\n",
       "      <td>2.025129</td>\n",
       "      <td>0.734578</td>\n",
       "      <td>249.674377</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-06-17 15:09:46</th>\n",
       "      <th>Ortega__Nelson_and_Parrish</th>\n",
       "      <th>Chen_Rest</th>\n",
       "      <th>9999032632997</th>\n",
       "      <td>72.079085</td>\n",
       "      <td>1.606980</td>\n",
       "      <td>0.915787</td>\n",
       "      <td>255.811956</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-06-17 15:14:46</th>\n",
       "      <th>Ortega__Nelson_and_Parrish</th>\n",
       "      <th>Chen_Rest</th>\n",
       "      <th>9999032632997</th>\n",
       "      <td>72.180327</td>\n",
       "      <td>1.475628</td>\n",
       "      <td>1.772953</td>\n",
       "      <td>252.170768</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-06-17 15:19:46</th>\n",
       "      <th>Ortega__Nelson_and_Parrish</th>\n",
       "      <th>Chen_Rest</th>\n",
       "      <th>9999032632997</th>\n",
       "      <td>84.834458</td>\n",
       "      <td>45.076050</td>\n",
       "      <td>25.396808</td>\n",
       "      <td>166.740806</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                          avg(cpu_utilization)  \\\n",
       "time                company                    data_center device                                \n",
       "2019-06-17 14:34:46 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997             68.078416   \n",
       "2019-06-17 14:39:46 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997             68.533801   \n",
       "2019-06-17 14:44:46 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997             70.377798   \n",
       "2019-06-17 14:49:46 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997             87.306579   \n",
       "2019-06-17 14:54:46 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997             67.573655   \n",
       "2019-06-17 14:59:46 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997             69.896618   \n",
       "2019-06-17 15:04:46 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997             69.737311   \n",
       "2019-06-17 15:09:46 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997             72.079085   \n",
       "2019-06-17 15:14:46 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997             72.180327   \n",
       "2019-06-17 15:19:46 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997             84.834458   \n",
       "\n",
       "                                                                          avg(latency)  \\\n",
       "time                company                    data_center device                        \n",
       "2019-06-17 14:34:46 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997      1.423847   \n",
       "2019-06-17 14:39:46 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997      2.367653   \n",
       "2019-06-17 14:44:46 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997      2.096841   \n",
       "2019-06-17 14:49:46 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997     47.314863   \n",
       "2019-06-17 14:54:46 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997      2.729573   \n",
       "2019-06-17 14:59:46 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997      1.819913   \n",
       "2019-06-17 15:04:46 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997      2.025129   \n",
       "2019-06-17 15:09:46 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997      1.606980   \n",
       "2019-06-17 15:14:46 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997      1.475628   \n",
       "2019-06-17 15:19:46 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997     45.076050   \n",
       "\n",
       "                                                                          avg(packet_loss)  \\\n",
       "time                company                    data_center device                            \n",
       "2019-06-17 14:34:46 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997          0.897193   \n",
       "2019-06-17 14:39:46 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997          0.844910   \n",
       "2019-06-17 14:44:46 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997          0.455499   \n",
       "2019-06-17 14:49:46 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997         21.452884   \n",
       "2019-06-17 14:54:46 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997          0.856969   \n",
       "2019-06-17 14:59:46 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997          0.666666   \n",
       "2019-06-17 15:04:46 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997          0.734578   \n",
       "2019-06-17 15:09:46 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997          0.915787   \n",
       "2019-06-17 15:14:46 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997          1.772953   \n",
       "2019-06-17 15:19:46 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997         25.396808   \n",
       "\n",
       "                                                                          avg(throughput)  \n",
       "time                company                    data_center device                          \n",
       "2019-06-17 14:34:46 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997       244.903743  \n",
       "2019-06-17 14:39:46 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997       246.186991  \n",
       "2019-06-17 14:44:46 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997       252.081668  \n",
       "2019-06-17 14:49:46 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997       159.983865  \n",
       "2019-06-17 14:54:46 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997       250.914966  \n",
       "2019-06-17 14:59:46 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997       251.590818  \n",
       "2019-06-17 15:04:46 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997       249.674377  \n",
       "2019-06-17 15:09:46 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997       255.811956  \n",
       "2019-06-17 15:14:46 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997       252.170768  \n",
       "2019-06-17 15:19:46 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997       166.740806  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "client.read(backend='tsdb', query='select avg(cpu_utilization), avg(latency) , avg(packet_loss) , avg(throughput)  from netops_metrics_jupyter group by company, data_center, device',\n",
    "            start=\"now-1d\", end='now+1d', multi_index=True, step='5m').head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Save the generated dataset to parquet for future reproducability "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "mkdir: cannot create directory ‘data’: File exists\n"
     ]
    }
   ],
   "source": [
    "# craete directory if doesnt exist \n",
    "!mkdir data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pyarrow as pa\n",
    "from pyarrow import parquet as pq"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "#write the dataframe into a parquet (on iguazio file system)\n",
    "version = '1.0'\n",
    "filepath = 'data/netops_metrics.v{}.parquet'.format(version)\n",
    "pq.write_table(pa.Table.from_pandas(df), filepath)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reading the data from parquet into the time-series DB\n",
    "if we want to reproduce the same results we can rebuild the TSDB from the saved parquet file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# uncomment the line below if you want to reset the TSDB table \n",
    "client.delete(backend='tsdb', table='netops_metrics_jupyter')\n",
    "client.create(backend='tsdb', table='netops_metrics_jupyter', attrs={'rate': '1/m'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>cpu_utilization</th>\n",
       "      <th>cpu_utilization_is_error</th>\n",
       "      <th>latency</th>\n",
       "      <th>latency_is_error</th>\n",
       "      <th>packet_loss</th>\n",
       "      <th>packet_loss_is_error</th>\n",
       "      <th>throughput</th>\n",
       "      <th>throughput_is_error</th>\n",
       "      <th>is_error</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>timestamp</th>\n",
       "      <th>data_center</th>\n",
       "      <th>company</th>\n",
       "      <th>device</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">2019-06-17 14:39:24.404731</th>\n",
       "      <th>Tamara_Greens</th>\n",
       "      <th>Washington-Lambert</th>\n",
       "      <th>1416256267840</th>\n",
       "      <td>68.340178</td>\n",
       "      <td>False</td>\n",
       "      <td>4.736444</td>\n",
       "      <td>False</td>\n",
       "      <td>3.086756</td>\n",
       "      <td>False</td>\n",
       "      <td>255.270204</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gallagher_Springs</th>\n",
       "      <th>Ortega__Nelson_and_Parrish</th>\n",
       "      <th>9909218583192</th>\n",
       "      <td>79.825575</td>\n",
       "      <td>False</td>\n",
       "      <td>5.369881</td>\n",
       "      <td>False</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>False</td>\n",
       "      <td>244.680344</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Chen_Rest</th>\n",
       "      <th>Ortega__Nelson_and_Parrish</th>\n",
       "      <th>9999032632997</th>\n",
       "      <td>74.625916</td>\n",
       "      <td>False</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>False</td>\n",
       "      <td>0.810500</td>\n",
       "      <td>False</td>\n",
       "      <td>229.439649</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Nicole_Trail</th>\n",
       "      <th>Washington-Lambert</th>\n",
       "      <th>0243526550889</th>\n",
       "      <td>64.806974</td>\n",
       "      <td>False</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>False</td>\n",
       "      <td>1.177091</td>\n",
       "      <td>False</td>\n",
       "      <td>240.797342</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Chen_Rest</th>\n",
       "      <th>Ortega__Nelson_and_Parrish</th>\n",
       "      <th>2588823083436</th>\n",
       "      <td>57.561720</td>\n",
       "      <td>False</td>\n",
       "      <td>2.732402</td>\n",
       "      <td>False</td>\n",
       "      <td>3.374598</td>\n",
       "      <td>False</td>\n",
       "      <td>238.602076</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                                       cpu_utilization  \\\n",
       "timestamp                  data_center       company                    device                           \n",
       "2019-06-17 14:39:24.404731 Tamara_Greens     Washington-Lambert         1416256267840        68.340178   \n",
       "                           Gallagher_Springs Ortega__Nelson_and_Parrish 9909218583192        79.825575   \n",
       "                           Chen_Rest         Ortega__Nelson_and_Parrish 9999032632997        74.625916   \n",
       "                           Nicole_Trail      Washington-Lambert         0243526550889        64.806974   \n",
       "                           Chen_Rest         Ortega__Nelson_and_Parrish 2588823083436        57.561720   \n",
       "\n",
       "                                                                                       cpu_utilization_is_error  \\\n",
       "timestamp                  data_center       company                    device                                    \n",
       "2019-06-17 14:39:24.404731 Tamara_Greens     Washington-Lambert         1416256267840                     False   \n",
       "                           Gallagher_Springs Ortega__Nelson_and_Parrish 9909218583192                     False   \n",
       "                           Chen_Rest         Ortega__Nelson_and_Parrish 9999032632997                     False   \n",
       "                           Nicole_Trail      Washington-Lambert         0243526550889                     False   \n",
       "                           Chen_Rest         Ortega__Nelson_and_Parrish 2588823083436                     False   \n",
       "\n",
       "                                                                                        latency  \\\n",
       "timestamp                  data_center       company                    device                    \n",
       "2019-06-17 14:39:24.404731 Tamara_Greens     Washington-Lambert         1416256267840  4.736444   \n",
       "                           Gallagher_Springs Ortega__Nelson_and_Parrish 9909218583192  5.369881   \n",
       "                           Chen_Rest         Ortega__Nelson_and_Parrish 9999032632997  0.000000   \n",
       "                           Nicole_Trail      Washington-Lambert         0243526550889  0.000000   \n",
       "                           Chen_Rest         Ortega__Nelson_and_Parrish 2588823083436  2.732402   \n",
       "\n",
       "                                                                                       latency_is_error  \\\n",
       "timestamp                  data_center       company                    device                            \n",
       "2019-06-17 14:39:24.404731 Tamara_Greens     Washington-Lambert         1416256267840             False   \n",
       "                           Gallagher_Springs Ortega__Nelson_and_Parrish 9909218583192             False   \n",
       "                           Chen_Rest         Ortega__Nelson_and_Parrish 9999032632997             False   \n",
       "                           Nicole_Trail      Washington-Lambert         0243526550889             False   \n",
       "                           Chen_Rest         Ortega__Nelson_and_Parrish 2588823083436             False   \n",
       "\n",
       "                                                                                       packet_loss  \\\n",
       "timestamp                  data_center       company                    device                       \n",
       "2019-06-17 14:39:24.404731 Tamara_Greens     Washington-Lambert         1416256267840     3.086756   \n",
       "                           Gallagher_Springs Ortega__Nelson_and_Parrish 9909218583192     0.000000   \n",
       "                           Chen_Rest         Ortega__Nelson_and_Parrish 9999032632997     0.810500   \n",
       "                           Nicole_Trail      Washington-Lambert         0243526550889     1.177091   \n",
       "                           Chen_Rest         Ortega__Nelson_and_Parrish 2588823083436     3.374598   \n",
       "\n",
       "                                                                                       packet_loss_is_error  \\\n",
       "timestamp                  data_center       company                    device                                \n",
       "2019-06-17 14:39:24.404731 Tamara_Greens     Washington-Lambert         1416256267840                 False   \n",
       "                           Gallagher_Springs Ortega__Nelson_and_Parrish 9909218583192                 False   \n",
       "                           Chen_Rest         Ortega__Nelson_and_Parrish 9999032632997                 False   \n",
       "                           Nicole_Trail      Washington-Lambert         0243526550889                 False   \n",
       "                           Chen_Rest         Ortega__Nelson_and_Parrish 2588823083436                 False   \n",
       "\n",
       "                                                                                       throughput  \\\n",
       "timestamp                  data_center       company                    device                      \n",
       "2019-06-17 14:39:24.404731 Tamara_Greens     Washington-Lambert         1416256267840  255.270204   \n",
       "                           Gallagher_Springs Ortega__Nelson_and_Parrish 9909218583192  244.680344   \n",
       "                           Chen_Rest         Ortega__Nelson_and_Parrish 9999032632997  229.439649   \n",
       "                           Nicole_Trail      Washington-Lambert         0243526550889  240.797342   \n",
       "                           Chen_Rest         Ortega__Nelson_and_Parrish 2588823083436  238.602076   \n",
       "\n",
       "                                                                                       throughput_is_error  \\\n",
       "timestamp                  data_center       company                    device                               \n",
       "2019-06-17 14:39:24.404731 Tamara_Greens     Washington-Lambert         1416256267840                False   \n",
       "                           Gallagher_Springs Ortega__Nelson_and_Parrish 9909218583192                False   \n",
       "                           Chen_Rest         Ortega__Nelson_and_Parrish 9999032632997                False   \n",
       "                           Nicole_Trail      Washington-Lambert         0243526550889                False   \n",
       "                           Chen_Rest         Ortega__Nelson_and_Parrish 2588823083436                False   \n",
       "\n",
       "                                                                                       is_error  \n",
       "timestamp                  data_center       company                    device                   \n",
       "2019-06-17 14:39:24.404731 Tamara_Greens     Washington-Lambert         1416256267840     False  \n",
       "                           Gallagher_Springs Ortega__Nelson_and_Parrish 9909218583192     False  \n",
       "                           Chen_Rest         Ortega__Nelson_and_Parrish 9999032632997     False  \n",
       "                           Nicole_Trail      Washington-Lambert         0243526550889     False  \n",
       "                           Chen_Rest         Ortega__Nelson_and_Parrish 2588823083436     False  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# read the parquet into memory and print the head \n",
    "pqdf = pq.read_table(filepath).to_pandas()\n",
    "pqdf.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# write the dataframe into the time-seried DB, uncomment the line below\n",
    "client.write(backend='tsdb', table='netops_metrics_jupyter', dfs=pqdf)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>avg(cpu_utilization)</th>\n",
       "      <th>avg(latency)</th>\n",
       "      <th>avg(packet_loss)</th>\n",
       "      <th>avg(throughput)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>time</th>\n",
       "      <th>company</th>\n",
       "      <th>data_center</th>\n",
       "      <th>device</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2019-06-17 14:34:54</th>\n",
       "      <th>Ortega__Nelson_and_Parrish</th>\n",
       "      <th>Chen_Rest</th>\n",
       "      <th>9999032632997</th>\n",
       "      <td>67.675289</td>\n",
       "      <td>1.139078</td>\n",
       "      <td>0.797778</td>\n",
       "      <td>239.154164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-06-17 14:39:54</th>\n",
       "      <th>Ortega__Nelson_and_Parrish</th>\n",
       "      <th>Chen_Rest</th>\n",
       "      <th>9999032632997</th>\n",
       "      <td>68.178614</td>\n",
       "      <td>2.367653</td>\n",
       "      <td>0.857688</td>\n",
       "      <td>246.453351</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-06-17 14:44:54</th>\n",
       "      <th>Ortega__Nelson_and_Parrish</th>\n",
       "      <th>Chen_Rest</th>\n",
       "      <th>9999032632997</th>\n",
       "      <td>70.869440</td>\n",
       "      <td>2.096841</td>\n",
       "      <td>0.440598</td>\n",
       "      <td>253.142512</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-06-17 14:49:54</th>\n",
       "      <th>Ortega__Nelson_and_Parrish</th>\n",
       "      <th>Chen_Rest</th>\n",
       "      <th>9999032632997</th>\n",
       "      <td>86.957390</td>\n",
       "      <td>47.429515</td>\n",
       "      <td>21.464396</td>\n",
       "      <td>159.796418</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-06-17 14:54:54</th>\n",
       "      <th>Ortega__Nelson_and_Parrish</th>\n",
       "      <th>Chen_Rest</th>\n",
       "      <th>9999032632997</th>\n",
       "      <td>67.901543</td>\n",
       "      <td>2.638625</td>\n",
       "      <td>0.839577</td>\n",
       "      <td>249.824000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-06-17 14:59:54</th>\n",
       "      <th>Ortega__Nelson_and_Parrish</th>\n",
       "      <th>Chen_Rest</th>\n",
       "      <th>9999032632997</th>\n",
       "      <td>70.016103</td>\n",
       "      <td>1.796209</td>\n",
       "      <td>0.666666</td>\n",
       "      <td>252.405314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-06-17 15:04:54</th>\n",
       "      <th>Ortega__Nelson_and_Parrish</th>\n",
       "      <th>Chen_Rest</th>\n",
       "      <th>9999032632997</th>\n",
       "      <td>69.325575</td>\n",
       "      <td>2.141810</td>\n",
       "      <td>0.734578</td>\n",
       "      <td>249.794588</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-06-17 15:09:54</th>\n",
       "      <th>Ortega__Nelson_and_Parrish</th>\n",
       "      <th>Chen_Rest</th>\n",
       "      <th>9999032632997</th>\n",
       "      <td>72.047269</td>\n",
       "      <td>1.490299</td>\n",
       "      <td>0.915787</td>\n",
       "      <td>255.727727</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-06-17 15:14:54</th>\n",
       "      <th>Ortega__Nelson_and_Parrish</th>\n",
       "      <th>Chen_Rest</th>\n",
       "      <th>9999032632997</th>\n",
       "      <td>72.510986</td>\n",
       "      <td>1.475628</td>\n",
       "      <td>1.772953</td>\n",
       "      <td>251.612780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-06-17 15:19:54</th>\n",
       "      <th>Ortega__Nelson_and_Parrish</th>\n",
       "      <th>Chen_Rest</th>\n",
       "      <th>9999032632997</th>\n",
       "      <td>85.081636</td>\n",
       "      <td>45.076050</td>\n",
       "      <td>25.412336</td>\n",
       "      <td>166.853232</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                          avg(cpu_utilization)  \\\n",
       "time                company                    data_center device                                \n",
       "2019-06-17 14:34:54 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997             67.675289   \n",
       "2019-06-17 14:39:54 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997             68.178614   \n",
       "2019-06-17 14:44:54 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997             70.869440   \n",
       "2019-06-17 14:49:54 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997             86.957390   \n",
       "2019-06-17 14:54:54 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997             67.901543   \n",
       "2019-06-17 14:59:54 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997             70.016103   \n",
       "2019-06-17 15:04:54 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997             69.325575   \n",
       "2019-06-17 15:09:54 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997             72.047269   \n",
       "2019-06-17 15:14:54 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997             72.510986   \n",
       "2019-06-17 15:19:54 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997             85.081636   \n",
       "\n",
       "                                                                          avg(latency)  \\\n",
       "time                company                    data_center device                        \n",
       "2019-06-17 14:34:54 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997      1.139078   \n",
       "2019-06-17 14:39:54 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997      2.367653   \n",
       "2019-06-17 14:44:54 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997      2.096841   \n",
       "2019-06-17 14:49:54 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997     47.429515   \n",
       "2019-06-17 14:54:54 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997      2.638625   \n",
       "2019-06-17 14:59:54 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997      1.796209   \n",
       "2019-06-17 15:04:54 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997      2.141810   \n",
       "2019-06-17 15:09:54 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997      1.490299   \n",
       "2019-06-17 15:14:54 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997      1.475628   \n",
       "2019-06-17 15:19:54 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997     45.076050   \n",
       "\n",
       "                                                                          avg(packet_loss)  \\\n",
       "time                company                    data_center device                            \n",
       "2019-06-17 14:34:54 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997          0.797778   \n",
       "2019-06-17 14:39:54 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997          0.857688   \n",
       "2019-06-17 14:44:54 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997          0.440598   \n",
       "2019-06-17 14:49:54 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997         21.464396   \n",
       "2019-06-17 14:54:54 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997          0.839577   \n",
       "2019-06-17 14:59:54 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997          0.666666   \n",
       "2019-06-17 15:04:54 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997          0.734578   \n",
       "2019-06-17 15:09:54 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997          0.915787   \n",
       "2019-06-17 15:14:54 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997          1.772953   \n",
       "2019-06-17 15:19:54 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997         25.412336   \n",
       "\n",
       "                                                                          avg(throughput)  \n",
       "time                company                    data_center device                          \n",
       "2019-06-17 14:34:54 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997       239.154164  \n",
       "2019-06-17 14:39:54 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997       246.453351  \n",
       "2019-06-17 14:44:54 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997       253.142512  \n",
       "2019-06-17 14:49:54 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997       159.796418  \n",
       "2019-06-17 14:54:54 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997       249.824000  \n",
       "2019-06-17 14:59:54 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997       252.405314  \n",
       "2019-06-17 15:04:54 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997       249.794588  \n",
       "2019-06-17 15:09:54 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997       255.727727  \n",
       "2019-06-17 15:14:54 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997       251.612780  \n",
       "2019-06-17 15:19:54 Ortega__Nelson_and_Parrish Chen_Rest   9999032632997       166.853232  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# verify the table is written \n",
    "client.read(backend='tsdb', query='select avg(cpu_utilization) , avg(latency) , avg(packet_loss) , avg(throughput)  from netops_metrics_jupyter group by company, data_center, device',\n",
    "            start=\"now-1d\", end='now+1d', multi_index=True, step='5m').head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}