{
"cells": [
{
"cell_type": "markdown",
"id": "c43b2226",
"metadata": {},
"source": [
"# Prometheus Metrics Visualization\n",
"\n",
"This notebook visualizes Prometheus metrics data fetched from the query_range API. It demonstrates how to load, transform, and visualize time series metrics for different TiDB server instances."
]
},
{
"cell_type": "markdown",
"id": "2bd24004",
"metadata": {},
"source": [
"## 1. Import Required Libraries\n",
"Import libraries for data manipulation and visualization."
]
},
{
"cell_type": "code",
"execution_count": 2,
"id": "bd67e083",
"metadata": {},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"/Users/ywang/projects/digital-bi-discovery/.conda/lib/python3.12/site-packages/google/cloud/bigquery/__init__.py:237: FutureWarning: %load_ext google.cloud.bigquery is deprecated. Install bigquery-magics package and use `%load_ext bigquery_magics`, instead.\n",
" warnings.warn(\n"
]
}
],
"source": [
"import pandas as pd\n",
"import matplotlib.pyplot as plt\n",
"import seaborn as sns\n",
"import json\n",
"from datetime import datetime\n",
"import os\n",
"import pandas as pd\n",
"import numpy as np\n",
"\n",
"from google.cloud import bigquery\n",
"from datetime import datetime, timedelta\n",
"\n",
"%load_ext autoreload\n",
"%autoreload 2\n",
"\n",
"%load_ext google.cloud.bigquery\n",
"\n",
"\n",
"os.environ[\"GCLOUD_PROJECT\"] = \"gcp-digital-bi-dev\"\n",
"\n",
"client = bigquery.Client()\n",
"\n",
"# Set plot style\n",
"sns.set(style=\"whitegrid\")"
]
},
{
"cell_type": "markdown",
"id": "590b939b",
"metadata": {},
"source": [
"## 2. Load Prometheus Data\n",
"\n",
"Load the Prometheus query_range API response. For demonstration, we use a sample response (as returned by the curl command)."
]
},
{
"cell_type": "code",
"execution_count": 3,
"id": "c9b6b972",
"metadata": {},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"/Users/ywang/projects/digital-bi-discovery/.conda/lib/python3.12/site-packages/google/cloud/bigquery/job/query.py:2057: UserWarning: A progress bar was requested, but there was an error loading the tqdm library. Please install tqdm to use the progress bar functionality.\n",
" query_result = wait_for_query(self, progress_bar_type, max_results=max_results)\n",
"/Users/ywang/projects/digital-bi-discovery/.conda/lib/python3.12/site-packages/google/cloud/bigquery/table.py:2508: UserWarning: A progress bar was requested, but there was an error loading the tqdm library. Please install tqdm to use the progress bar functionality.\n",
" record_batch = self.to_arrow(\n"
]
}
],
"source": [
"%%bigquery df_vector_data\n",
"SELECT *\n",
"FROM\n",
" `gcp-digital-bi-dev.sandbox_dwh_ywang.vector_activities`"
]
},
{
"cell_type": "code",
"execution_count": 4,
"id": "8dae2103",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Column names: ['cluster_id', 'cluster_activity_start_timestamp', 'cluster_activity_end_timestamp']\n"
]
},
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" cluster_activity_start_timestamp | \n",
" cluster_activity_end_timestamp | \n",
"
\n",
" \n",
" \n",
" \n",
" | count | \n",
" 62290.0 | \n",
" 62290.0 | \n",
"
\n",
" \n",
" | mean | \n",
" 1746905726.563172 | \n",
" 1746908033.959062 | \n",
"
\n",
" \n",
" | std | \n",
" 463843.320414 | \n",
" 463766.493015 | \n",
"
\n",
" \n",
" | min | \n",
" 1746028800.0 | \n",
" 1746028800.0 | \n",
"
\n",
" \n",
" | 25% | \n",
" 1746539940.0 | \n",
" 1746541867.5 | \n",
"
\n",
" \n",
" | 50% | \n",
" 1746967530.0 | \n",
" 1746968760.0 | \n",
"
\n",
" \n",
" | 75% | \n",
" 1747291762.5 | \n",
" 1747293412.5 | \n",
"
\n",
" \n",
" | max | \n",
" 1747702710.0 | \n",
" 1747702770.0 | \n",
"
\n",
" \n",
"
\n",
"
\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" cluster_id | \n",
" cluster_activity_start_timestamp | \n",
" cluster_activity_end_timestamp | \n",
"
\n",
" \n",
" \n",
" \n",
" | 0 | \n",
" 10003486226571264478 | \n",
" 1747043730 | \n",
" 1747044360 | \n",
"
\n",
" \n",
" | 1 | \n",
" 10032564126405079395 | \n",
" 1746344580 | \n",
" 1746349470 | \n",
"
\n",
" \n",
" | 2 | \n",
" 10032564126405079395 | \n",
" 1746425280 | \n",
" 1746425910 | \n",
"
\n",
" \n",
" | 3 | \n",
" 10036480496133627496 | \n",
" 1747273200 | \n",
" 1747275240 | \n",
"
\n",
" \n",
" | 4 | \n",
" 10050661290725915051 | \n",
" 1746466110 | \n",
" 1746467280 | \n",
"
\n",
" \n",
"
\n",
"
\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" hour | \n",
" hour_timestamp | \n",
" date_day | \n",
" hour_of_day | \n",
" day_of_week | \n",
" day_name | \n",
" is_weekend | \n",
"
\n",
" \n",
" \n",
" \n",
" | 0 | \n",
" 2025-04-30 16:00:00 | \n",
" 1746028800 | \n",
" 2025-04-30 | \n",
" 16 | \n",
" 2 | \n",
" Wednesday | \n",
" False | \n",
"
\n",
" \n",
" | 1 | \n",
" 2025-04-30 17:00:00 | \n",
" 1746032400 | \n",
" 2025-04-30 | \n",
" 17 | \n",
" 2 | \n",
" Wednesday | \n",
" False | \n",
"
\n",
" \n",
" | 2 | \n",
" 2025-04-30 18:00:00 | \n",
" 1746036000 | \n",
" 2025-04-30 | \n",
" 18 | \n",
" 2 | \n",
" Wednesday | \n",
" False | \n",
"
\n",
" \n",
" | 3 | \n",
" 2025-04-30 19:00:00 | \n",
" 1746039600 | \n",
" 2025-04-30 | \n",
" 19 | \n",
" 2 | \n",
" Wednesday | \n",
" False | \n",
"
\n",
" \n",
" | 4 | \n",
" 2025-04-30 20:00:00 | \n",
" 1746043200 | \n",
" 2025-04-30 | \n",
" 20 | \n",
" 2 | \n",
" Wednesday | \n",
" False | \n",
"
\n",
" \n",
"
\n",
"
\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" hour | \n",
" hour_timestamp | \n",
" date_day | \n",
" hour_of_day | \n",
" day_of_week | \n",
" day_name | \n",
" is_weekend | \n",
" cluster_id | \n",
" is_active | \n",
" activity_start | \n",
" activity_end | \n",
"
\n",
" \n",
" \n",
" \n",
" | 0 | \n",
" 2025-04-30 16:00:00 | \n",
" 1746028800 | \n",
" 2025-04-30 | \n",
" 16 | \n",
" 2 | \n",
" Wednesday | \n",
" False | \n",
" 10878885490924875595 | \n",
" True | \n",
" 1746028800 | \n",
" 1747702770 | \n",
"
\n",
" \n",
" | 1 | \n",
" 2025-04-30 16:00:00 | \n",
" 1746028800 | \n",
" 2025-04-30 | \n",
" 16 | \n",
" 2 | \n",
" Wednesday | \n",
" False | \n",
" 10699792286592114925 | \n",
" True | \n",
" 1746028800 | \n",
" 1746029310 | \n",
"
\n",
" \n",
" | 2 | \n",
" 2025-04-30 16:00:00 | \n",
" 1746028800 | \n",
" 2025-04-30 | \n",
" 16 | \n",
" 2 | \n",
" Wednesday | \n",
" False | \n",
" 10263056296984564649 | \n",
" True | \n",
" 1746028800 | \n",
" 1746035400 | \n",
"
\n",
" \n",
" | 3 | \n",
" 2025-04-30 16:00:00 | \n",
" 1746028800 | \n",
" 2025-04-30 | \n",
" 16 | \n",
" 2 | \n",
" Wednesday | \n",
" False | \n",
" 10818824952762353607 | \n",
" True | \n",
" 1746028800 | \n",
" 1746042450 | \n",
"
\n",
" \n",
" | 4 | \n",
" 2025-04-30 16:00:00 | \n",
" 1746028800 | \n",
" 2025-04-30 | \n",
" 16 | \n",
" 2 | \n",
" Wednesday | \n",
" False | \n",
" 10091272046870238031 | \n",
" True | \n",
" 1746028800 | \n",
" 1746069030 | \n",
"
\n",
" \n",
"
\n",
"
\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" cluster_id | \n",
" start_time | \n",
" end_time | \n",
" duration_minutes | \n",
" day | \n",
" hour | \n",
"
\n",
" \n",
" \n",
" \n",
" | 12755 | \n",
" 10488767978692526405 | \n",
" 2025-04-30 16:00:00 | \n",
" 2025-04-30 17:01:00 | \n",
" 61.0 | \n",
" 2025-04-30 | \n",
" 16 | \n",
"
\n",
" \n",
" | 10051 | \n",
" 10077312289339402773 | \n",
" 2025-04-30 16:00:00 | \n",
" 2025-04-30 16:03:00 | \n",
" 3.0 | \n",
" 2025-04-30 | \n",
" 16 | \n",
"
\n",
" \n",
" | 37994 | \n",
" 10335261911879706063 | \n",
" 2025-04-30 16:00:00 | \n",
" 2025-04-30 16:30:30 | \n",
" 30.5 | \n",
" 2025-04-30 | \n",
" 16 | \n",
"
\n",
" \n",
" | 17415 | \n",
" 10281719015668830318 | \n",
" 2025-04-30 16:00:00 | \n",
" 2025-04-30 16:04:00 | \n",
" 4.0 | \n",
" 2025-04-30 | \n",
" 16 | \n",
"
\n",
" \n",
" | 22725 | \n",
" 10344913140208148585 | \n",
" 2025-04-30 16:00:00 | \n",
" 2025-05-20 00:59:30 | \n",
" 27899.5 | \n",
" 2025-04-30 | \n",
" 16 | \n",
"
\n",
" \n",
"
\n",
"