ps_sep_health_checker.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "13c25eb7",
   "metadata": {
    "tags": [
     "hide-input"
    ]
   },
   "source": [
    "# Input Parameters"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "add19578",
   "metadata": {},
   "source": [
    "This paragraph will set up the connection parameters to execute the notebook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "84bd019e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "username: ········\n",
      "password: ········\n"
     ]
    }
   ],
   "source": [
    "import getpass\n",
    "import subprocess\n",
    "import time\n",
    "from datetime import datetime, timedelta\n",
    "        \n",
    "input_file='./input_health_check_configs.json'\n",
    "# input_file='/Users/satya.dixit/Documents/Tools/HealthCheckerTool/Git/ps-health-status-checker/input_health_check_configs.json'\n",
    "\n",
    "hostname='https://sbe-official-demo-warp-speed.fieldeng.starburstdata.net'\n",
    "port='443'\n",
    "role='sysadmin'\n",
    "username = getpass.getpass(prompt='username: ')\n",
    "password = getpass.getpass(prompt='password: ')\n",
    "catalog='query_logger'\n",
    "schema='public'\n",
    "# change duration below, default 3 months\n",
    "days=90        \n",
    "\n",
    "# If either username or password are missing, exit the script without proceeding further\n",
    "if username.strip() == '' or password.strip() == '':\n",
    "    print(\"username / password cannot be empty. Please try again.\")\n",
    "    exit()\n",
    "    \n",
    "current_date = datetime.now()\n",
    "analysis_start_date = (current_date - timedelta(days)).strftime(\"%Y-%m-%d\")\n",
    "analysis_end_date = current_date.strftime(\"%Y-%m-%d\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ad2f666e",
   "metadata": {},
   "source": [
    "# Main Code"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2edfc0a4",
   "metadata": {},
   "source": [
    "This paragraph will process the input json file and execute the sql queries in trino"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "b4b6a233",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Executing the query for KPI -  hourly_cpu_metrics ..\n",
      "Done.\n",
      "Executing the query for KPI -  daily_cpu_metrics ..\n",
      "Done.\n",
      "Executing the query for KPI -  hourly_memory_metrics ..\n",
      "Done.\n",
      "Executing the query for KPI -  daily_memory_metrics ..\n",
      "Done.\n",
      "Executing the query for KPI -  hourly_node_count ..\n",
      "Done.\n",
      "Executing the query for KPI -  minutely_cpu_metrics ..\n",
      "Done.\n",
      "Executing the query for KPI -  minutely_memory_metrics ..\n",
      "Done.\n",
      "Executing the query for KPI -  minutely_node_count ..\n",
      "Done.\n",
      "Executing the query for KPI -  query_trends ..\n",
      "Done.\n",
      "Executing the query for KPI -  query_failure_rate ..\n",
      "Done.\n",
      "Executing the query for KPI -  query_failure_rate_by_query_type ..\n",
      "Done.\n",
      "Executing the query for KPI -  query_failure_by_query_type ..\n",
      "Done.\n",
      "Executing the query for KPI -  query_failure_by_error_type ..\n",
      "Done.\n",
      "Executing the query for KPI -  query_failure_by_error_type_and_name ..\n",
      "Done.\n",
      "Executing the query for KPI -  queries_per_minute ..\n",
      "Done.\n",
      "Executing the query for KPI -  data_processed ..\n",
      "Done.\n",
      "Executing the query for KPI -  query_perf_and_time_metrics ..\n",
      "Done.\n",
      "Executing the query for KPI -  top_x_execution_time ..\n",
      "Done.\n",
      "Executing the query for KPI -  top_x_planning_time ..\n",
      "Done.\n",
      "Executing the query for KPI -  top_x_queued_time ..\n",
      "Done.\n",
      "Executing the query for KPI -  top_x_scheduled_time ..\n",
      "Done.\n",
      "Executing the query for KPI -  top_x_analysis_time ..\n",
      "Done.\n",
      "Executing the query for KPI -  top_x_cpu_time ..\n",
      "Done.\n",
      "Executing the query for KPI -  top_x_data_scanned ..\n",
      "Done.\n",
      "Executing the query for KPI -  top_x_splits_completed ..\n",
      "Done.\n",
      "Executing the query for KPI -  sample_errors ..\n",
      "Done.\n"
     ]
    }
   ],
   "source": [
    "#!/usr/bin/env python3\n",
    "# coding: utf-8\n",
    "import trino\n",
    "from trino.dbapi import connect\n",
    "from trino.auth import BasicAuthentication\n",
    "import json\n",
    "import logging\n",
    "from datetime import datetime\n",
    "import pandas as pd\n",
    "\n",
    "# Initialize logging\n",
    "logfile = \"health_check\" + (datetime.now()).strftime(\"%Y_%m_%d_%H_%m_%s\") + \".log\"\n",
    "logging.basicConfig(filename=logfile, level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')\n",
    "\n",
    "# If either username or password are missing, exit the script without proceeding further\n",
    "if username.strip() == '' or password.strip() == '':\n",
    "    print(\"username / password cannot be empty. Please try again.\")\n",
    "    exit()\n",
    "\n",
    "# Load JSON configuration file\n",
    "with open(input_file) as json_file:\n",
    "    data = json.load(json_file)\n",
    "    query_details = data\n",
    "\n",
    "# Define connection parameters\n",
    "conn = connect(\n",
    "    host=hostname,\n",
    "    port=port,\n",
    "    catalog=catalog,\n",
    "    verify=True,\n",
    "    schema=schema,\n",
    "    roles=role,\n",
    "    auth=BasicAuthentication(username, password),\n",
    "    http_headers={'Authorization': 'Bearer mytoken'}\n",
    ")\n",
    "\n",
    "# Create an empty dictionary to store query results\n",
    "query_results = {}\n",
    "\n",
    "# Function to fetch data with pagination\n",
    "def fetch_data_with_pagination(cursor, chunk_size=1000):\n",
    "    result_data = []\n",
    "    while True:\n",
    "        rows = cursor.fetchmany(chunk_size)\n",
    "        if not rows:\n",
    "            break\n",
    "        result_data.extend(rows)\n",
    "    return result_data\n",
    "\n",
    "for kpi, kpi_details in query_details.items():\n",
    "    for query_name, query_config in kpi_details.items():\n",
    "        query_text = query_config['query_text'].replace('analysis_start_date', analysis_start_date).replace('analysis_end_date', analysis_end_date)\n",
    "        \n",
    "        try:\n",
    "            cur = conn.cursor()\n",
    "            print('Executing the query for KPI - ', query_name, '..')\n",
    "            cur.execute(query_text)\n",
    "            result_data = fetch_data_with_pagination(cur)\n",
    "            result = pd.DataFrame(result_data, columns=[desc[0] for desc in cur.description])\n",
    "            if not result.empty:\n",
    "                query_results[query_name] = result\n",
    "                print('Done.')\n",
    "                logging.info(f\"Query '{query_name}' executed successfully. Result stored as DataFrame.\")\n",
    "            else:\n",
    "                print(f\"Error executing query '{query_name}': No data returned\")\n",
    "\n",
    "        except Exception as e:\n",
    "            print(f\"Error executing query '{query_name}': {str(e)}\")\n",
    "        finally:\n",
    "            if cur:\n",
    "                cur.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c212f37f",
   "metadata": {},
   "source": [
    "# Cluster Health"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "aefdfca7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "        <iframe\n",
       "            width=\"100%\"\n",
       "            height=\"650\"\n",
       "            src=\"http://127.0.0.1:8061/\"\n",
       "            frameborder=\"0\"\n",
       "            allowfullscreen\n",
       "            \n",
       "        ></iframe>\n",
       "        "
      ],
      "text/plain": [
       "<IPython.lib.display.IFrame at 0x1585b29d0>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import time\n",
    "import pandas as pd\n",
    "import dash\n",
    "from dash import dcc, html\n",
    "from dash.dependencies import Input, Output, State\n",
    "import plotly.express as px\n",
    "import redis\n",
    "import json\n",
    "\n",
    "# Create DataFrames from query_results\n",
    "df1 = query_results['daily_cpu_metrics']\n",
    "df2 = query_results['hourly_cpu_metrics']\n",
    "df3 = query_results['daily_memory_metrics']\n",
    "df4 = query_results['hourly_memory_metrics']\n",
    "df5 = query_results['hourly_node_count']\n",
    "df6 = query_results['minutely_cpu_metrics']\n",
    "df7 = query_results['minutely_memory_metrics']\n",
    "df8 = query_results['minutely_node_count']\n",
    "\n",
    "\n",
    "# Initialize the Dash app\n",
    "app = dash.Dash(__name__)\n",
    "\n",
    "# Define the layout of the app\n",
    "app.layout = html.Div([\n",
    "    html.H1(\"CPU / Memory Usage & Node Availability\"),\n",
    "    \n",
    "    # Dropdown filter for dataset selection\n",
    "    dcc.Dropdown(\n",
    "        id='dataset-selector',\n",
    "        options=[\n",
    "            {'label': 'Daily CPU Usage', 'value': 'df1'},\n",
    "            {'label': 'Hourly CPU Usage', 'value': 'df2'},\n",
    "            {'label': 'Daily Memory Usage', 'value': 'df3'},\n",
    "            {'label': 'Hourly Memory Usage', 'value': 'df4'},\n",
    "            {'label': 'Hourly Node Count', 'value': 'df5'},\n",
    "            {'label': 'Minutely CPU Usage', 'value': 'df6'},\n",
    "            {'label': 'Minutely Memory Usage', 'value': 'df7'},            \n",
    "            {'label': 'Minutely Node Count', 'value': 'df8'},            \n",
    "        ],\n",
    "        value='df6',  # Default selection\n",
    "    ),\n",
    "    \n",
    "    # Chart to display the selected dataset\n",
    "    dcc.Graph(id='line-chart', style={'width': '1000px', 'height': '500px'})\n",
    "\n",
    "])\n",
    "\n",
    "# Define a callback function to update the chart based on the selected dataset\n",
    "@app.callback(\n",
    "    Output('line-chart', 'figure'),\n",
    "    Input('dataset-selector', 'value')\n",
    ")\n",
    "def update_chart(selected_dataset):\n",
    "    mapping = {\n",
    "        'df1': (df1, 'query_date', ['daily_median_sys_cpu', 'daily_median_proc_cpu'], 'Daily CPU Usage'),\n",
    "        'df2': (df2, 'query_date_hour', ['hourly_median_sys_cpu', 'hourly_median_proc_cpu'], 'Hourly CPU Usage'),\n",
    "        'df3': (df3, 'query_date', ['daily_median_qry_memory', 'daily_median_heap_memory'], 'Daily Memory Usage'),  # Adjust column names as necessary\n",
    "        'df4': (df4, 'query_date_hour', ['hourly_median_qry_memory', 'hourly_median_heap_memory'], 'Hourly Memory Usage'),\n",
    "        'df5': (df5, 'query_date_hour', ['hourly_median_node_count', 'hourly_avg_node_count'], 'Hourly Node Count'),\n",
    "        'df6': (df6, 'query_minute', ['minutely_median_sys_cpu', 'minutely_median_proc_cpu'], 'Minutely CPU Usage'),\n",
    "        'df7': (df7, 'query_minute', ['minutely_median_qry_memory', 'minutely_median_heap_memory'], 'Minutely Memory Usage'),\n",
    "        'df8': (df8, 'query_minute', ['minutely_node_count'], 'Minutely Node Count'),\n",
    "    }\n",
    "\n",
    "    df, x_col, y_cols, title = mapping[selected_dataset]\n",
    "    fig = px.line(df, x=x_col, y=y_cols, title=title)\n",
    "\n",
    "    return fig\n",
    "\n",
    "\n",
    "# Run the app\n",
    "if __name__ == '__main__':\n",
    "    app.run(port=8061,debug=True)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2c623610",
   "metadata": {},
   "source": [
    "# Query Health"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "610e68f3",
   "metadata": {},
   "source": [
    "Query Trends"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "5eb39fa9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "        <iframe\n",
       "            width=\"100%\"\n",
       "            height=\"650\"\n",
       "            src=\"http://127.0.0.1:8062/\"\n",
       "            frameborder=\"0\"\n",
       "            allowfullscreen\n",
       "            \n",
       "        ></iframe>\n",
       "        "
      ],
      "text/plain": [
       "<IPython.lib.display.IFrame at 0x14f53ed10>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df = query_results['query_trends']\n",
    "\n",
    "filtered_df = df\n",
    "\n",
    "# Initialize the Dash app\n",
    "app = dash.Dash(__name__)\n",
    "\n",
    "# Define the layout of the app\n",
    "app.layout = html.Div([\n",
    "    html.H1(\"Query Trends\"),\n",
    "    \n",
    "    # Dynamic multi-select slicer based on unique values in the 'query_type' column\n",
    "    dcc.Checklist(\n",
    "        id='query-type-slicer',\n",
    "        options=[\n",
    "            {'label': query_type, 'value': query_type}\n",
    "            for query_type in filtered_df['query_type'].unique()  # Generate options dynamically\n",
    "        ],\n",
    "        value=filtered_df['query_type'].unique().tolist(),  # Default selection (all unique values)\n",
    "    ),\n",
    "    \n",
    "    # Chart to display the sliced data\n",
    "    dcc.Graph(id='bar-chart'),\n",
    "])\n",
    "\n",
    "# Define a callback function to update the chart based on the selected slicer values\n",
    "@app.callback(\n",
    "    Output('bar-chart', 'figure'),\n",
    "    Input('query-type-slicer', 'value')\n",
    ")\n",
    "def update_chart(selected_query_types):\n",
    "    # Filter data by selected query types\n",
    "    filtered_df = df[df['query_type'].isin(selected_query_types)]\n",
    "    \n",
    "    # Group the data by 'query_date' and 'query_type' and aggregate the count of failed queries\n",
    "    grouped_df = filtered_df.groupby(['query_date', 'query_type'])['num_queries'].sum().reset_index()\n",
    "    \n",
    "    # Create the bar chart with 'query_date' on the x-axis, 'failed_queries_cnt' on the y-axis, and color by 'query_type'\n",
    "    fig = px.bar(grouped_df, x='query_date', y='num_queries', color='query_type',\n",
    "                 labels={'query_date': 'Query Date', 'num_queries': 'Query Count'},\n",
    "                 title='Query Count By Query Type')\n",
    "    return fig\n",
    "\n",
    "# Run the app\n",
    "if __name__ == '__main__':\n",
    "    app.run_server(port=8062,debug=True)\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c4231dab",
   "metadata": {},
   "source": [
    "Query Failure Rate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "96a837fa",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "        <iframe\n",
       "            width=\"100%\"\n",
       "            height=\"650\"\n",
       "            src=\"http://127.0.0.1:8063/\"\n",
       "            frameborder=\"0\"\n",
       "            allowfullscreen\n",
       "            \n",
       "        ></iframe>\n",
       "        "
      ],
      "text/plain": [
       "<IPython.lib.display.IFrame at 0x14f517610>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df = query_results['query_failure_rate_by_query_type']\n",
    "\n",
    "filtered_df=df\n",
    "\n",
    "# Initialize the Dash app\n",
    "app = dash.Dash(__name__)\n",
    "\n",
    "# Define the layout of the app\n",
    "app.layout = html.Div([\n",
    "    html.H1(\"Query Failure Rate\"),\n",
    "    \n",
    "    # Dynamic multi-select slicer based on unique values in the 'query_type' column\n",
    "    dcc.Checklist(\n",
    "        id='query-type-slicer',\n",
    "        options=[\n",
    "            {'label': query_type, 'value': query_type}\n",
    "            for query_type in filtered_df['query_type'].unique()  # Generate options dynamically\n",
    "        ],\n",
    "        value=filtered_df['query_type'].unique().tolist(),  # Default selection (all unique values)\n",
    "    ),\n",
    "    \n",
    "    # Chart to display the sliced data\n",
    "    dcc.Graph(id='bar-chart'),\n",
    "])\n",
    "\n",
    "# Define a callback function to update the chart based on the selected slicer values\n",
    "@app.callback(\n",
    "    Output('bar-chart', 'figure'),\n",
    "    Input('query-type-slicer', 'value')\n",
    ")\n",
    "def update_chart(selected_query_types):\n",
    "    # Filter data by selected query types\n",
    "    filtered_df = df[df['query_type'].isin(selected_query_types)]\n",
    "    \n",
    "    # Group the data by 'query_date' and 'query_type' and aggregate the count of failed queries\n",
    "    grouped_df = filtered_df.groupby(['query_date', 'query_type'])['query_failure_rate'].sum().reset_index()\n",
    "    \n",
    "    # Create the bar chart with 'query_date' on the x-axis, 'failed_queries_cnt' on the y-axis, and color by 'query_type'\n",
    "    fig = px.bar(grouped_df, x='query_date', y='query_failure_rate', color='query_type',\n",
    "                 labels={'query_date': 'Query Date', 'query_failure_rate': 'Error Rate'},\n",
    "                 title='Query Failure Rate By Query Type')\n",
    "    return fig\n",
    "\n",
    "# Run the app\n",
    "if __name__ == '__main__':\n",
    "    app.run_server(debug=True, port=8063)\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "373f2ac8",
   "metadata": {},
   "source": [
    "Failed queries by the query type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "62551eec",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "        <iframe\n",
       "            width=\"100%\"\n",
       "            height=\"650\"\n",
       "            src=\"http://127.0.0.1:8064/\"\n",
       "            frameborder=\"0\"\n",
       "            allowfullscreen\n",
       "            \n",
       "        ></iframe>\n",
       "        "
      ],
      "text/plain": [
       "<IPython.lib.display.IFrame at 0x15810d090>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m\n",
      "\u001b[1;31mKeyError\u001b[0m                                  Traceback (most recent call last)\n",
      "File \u001b[1;32m~/anaconda3/lib/python3.11/site-packages/pandas/core/indexes/base.py:3790\u001b[0m, in \u001b[0;36mIndex.get_loc\u001b[1;34m(\n",
      "    self=Index(['query_date', 'usr', 'query_type', 'failed_queries_cnt'], dtype='object'),\n",
      "    key='error_type'\n",
      ")\u001b[0m\n",
      "\u001b[0;32m   3789\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n",
      "\u001b[1;32m-> 3790\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_engine\u001b[38;5;241m.\u001b[39mget_loc(casted_key)\n",
      "        casted_key \u001b[1;34m= 'error_type'\u001b[0m\u001b[1;34m\n",
      "        \u001b[0mself \u001b[1;34m= Index(['query_date', 'usr', 'query_type', 'failed_queries_cnt'], dtype='object')\u001b[0m\n",
      "\u001b[0;32m   3791\u001b[0m \u001b[38;5;28;01mexcept\u001b[39;00m \u001b[38;5;167;01mKeyError\u001b[39;00m \u001b[38;5;28;01mas\u001b[39;00m err:\n",
      "\n",
      "File \u001b[1;32mindex.pyx:152\u001b[0m, in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[1;34m()\u001b[0m\n",
      "\n",
      "File \u001b[1;32mindex.pyx:181\u001b[0m, in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[1;34m()\u001b[0m\n",
      "\n",
      "File \u001b[1;32mpandas/_libs/hashtable_class_helper.pxi:7080\u001b[0m, in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[1;34m()\u001b[0m\n",
      "\n",
      "File \u001b[1;32mpandas/_libs/hashtable_class_helper.pxi:7088\u001b[0m, in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[1;34m()\u001b[0m\n",
      "\n",
      "\u001b[1;31mKeyError\u001b[0m: 'error_type'\n",
      "\n",
      "The above exception was the direct cause of the following exception:\n",
      "\n",
      "\u001b[1;31mKeyError\u001b[0m                                  Traceback (most recent call last)\n",
      "\u001b[1;31mKeyError\u001b[0m: 'error_type'\n",
      "\n",
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m\n",
      "\u001b[1;31mKeyError\u001b[0m                                  Traceback (most recent call last)\n",
      "File \u001b[1;32m~/anaconda3/lib/python3.11/site-packages/pandas/core/frame.py:8872\u001b[0m, in \u001b[0;36mDataFrame.groupby\u001b[1;34m(\n",
      "    self=      query_date                   usr       que...  NA                   1\n",
      "\n",
      "[1251 rows x 4 columns],\n",
      "    by=['query_date', 'error_type'],\n",
      "    axis=0,\n",
      "    level=None,\n",
      "    as_index=True,\n",
      "    sort=True,\n",
      "    group_keys=True,\n",
      "    observed=<no_default>,\n",
      "    dropna=True\n",
      ")\u001b[0m\n",
      "\u001b[0;32m   8869\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m level \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m \u001b[38;5;129;01mand\u001b[39;00m by \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m:\n",
      "\u001b[0;32m   8870\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mTypeError\u001b[39;00m(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mYou have to supply one of \u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mby\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m and \u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mlevel\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n",
      "\u001b[1;32m-> 8872\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m DataFrameGroupBy(\n",
      "        self \u001b[1;34m=       query_date                   usr       query_type  failed_queries_cnt\n",
      "0     2024-07-12     sa.john.bergamini           SELECT                 490\n",
      "1     2024-07-12      sa.angel.asensio           SELECT                   1\n",
      "2     2024-07-12  sa.victor.coustenobl               NA                   2\n",
      "3     2024-07-12         cache_service           INSERT                 753\n",
      "4     2024-07-12   sa.scarlett.whedbee           SELECT                  30\n",
      "...          ...                   ...              ...                 ...\n",
      "1246  2024-04-13      stargate_service           SELECT                  27\n",
      "1247  2024-04-12         matthew.duran           SELECT                  53\n",
      "1248  2024-04-12         matthew.duran  DATA_DEFINITION                   2\n",
      "1249  2024-04-12         cache_service           INSERT                 313\n",
      "1250  2024-04-12         matthew.duran               NA                   1\n",
      "\n",
      "[1251 rows x 4 columns]\u001b[0m\u001b[1;34m\n",
      "        \u001b[0mby \u001b[1;34m= ['query_date', 'error_type']\u001b[0m\u001b[1;34m\n",
      "        \u001b[0maxis \u001b[1;34m= 0\u001b[0m\u001b[1;34m\n",
      "        \u001b[0mlevel \u001b[1;34m= None\u001b[0m\u001b[1;34m\n",
      "        \u001b[0mas_index \u001b[1;34m= True\u001b[0m\u001b[1;34m\n",
      "        \u001b[0msort \u001b[1;34m= True\u001b[0m\u001b[1;34m\n",
      "        \u001b[0mgroup_keys \u001b[1;34m= True\u001b[0m\u001b[1;34m\n",
      "        \u001b[0mobserved \u001b[1;34m= <no_default>\u001b[0m\u001b[1;34m\n",
      "        \u001b[0mdropna \u001b[1;34m= True\u001b[0m\n",
      "\u001b[0;32m   8873\u001b[0m     obj\u001b[38;5;241m=\u001b[39m\u001b[38;5;28mself\u001b[39m,\n",
      "\u001b[0;32m   8874\u001b[0m     keys\u001b[38;5;241m=\u001b[39mby,\n",
      "\u001b[0;32m   8875\u001b[0m     axis\u001b[38;5;241m=\u001b[39maxis,\n",
      "\u001b[0;32m   8876\u001b[0m     level\u001b[38;5;241m=\u001b[39mlevel,\n",
      "\u001b[0;32m   8877\u001b[0m     as_index\u001b[38;5;241m=\u001b[39mas_index,\n",
      "\u001b[0;32m   8878\u001b[0m     sort\u001b[38;5;241m=\u001b[39msort,\n",
      "\u001b[0;32m   8879\u001b[0m     group_keys\u001b[38;5;241m=\u001b[39mgroup_keys,\n",
      "\u001b[0;32m   8880\u001b[0m     observed\u001b[38;5;241m=\u001b[39mobserved,\n",
      "\u001b[0;32m   8881\u001b[0m     dropna\u001b[38;5;241m=\u001b[39mdropna,\n",
      "\u001b[0;32m   8882\u001b[0m )\n",
      "\n",
      "File \u001b[1;32m~/anaconda3/lib/python3.11/site-packages/pandas/core/groupby/groupby.py:1274\u001b[0m, in \u001b[0;36mGroupBy.__init__\u001b[1;34m(\n",
      "    self=<pandas.core.groupby.generic.DataFrameGroupBy object>,\n",
      "    obj=      query_date                   usr       que...  NA                   1\n",
      "\n",
      "[1251 rows x 4 columns],\n",
      "    keys=['query_date', 'error_type'],\n",
      "    axis=0,\n",
      "    level=None,\n",
      "    grouper=None,\n",
      "    exclusions=None,\n",
      "    selection=None,\n",
      "    as_index=True,\n",
      "    sort=True,\n",
      "    group_keys=True,\n",
      "    observed=<no_default>,\n",
      "    dropna=True\n",
      ")\u001b[0m\n",
      "\u001b[0;32m   1271\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mdropna \u001b[38;5;241m=\u001b[39m dropna\n",
      "\u001b[0;32m   1273\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m grouper \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m:\n",
      "\u001b[1;32m-> 1274\u001b[0m     grouper, exclusions, obj \u001b[38;5;241m=\u001b[39m get_grouper(\n",
      "        obj \u001b[1;34m=       query_date                   usr       query_type  failed_queries_cnt\n",
      "0     2024-07-12     sa.john.bergamini           SELECT                 490\n",
      "1     2024-07-12      sa.angel.asensio           SELECT                   1\n",
      "2     2024-07-12  sa.victor.coustenobl               NA                   2\n",
      "3     2024-07-12         cache_service           INSERT                 753\n",
      "4     2024-07-12   sa.scarlett.whedbee           SELECT                  30\n",
      "...          ...                   ...              ...                 ...\n",
      "1246  2024-04-13      stargate_service           SELECT                  27\n",
      "1247  2024-04-12         matthew.duran           SELECT                  53\n",
      "1248  2024-04-12         matthew.duran  DATA_DEFINITION                   2\n",
      "1249  2024-04-12         cache_service           INSERT                 313\n",
      "1250  2024-04-12         matthew.duran               NA                   1\n",
      "\n",
      "[1251 rows x 4 columns]\u001b[0m\u001b[1;34m\n",
      "        \u001b[0mgrouper \u001b[1;34m= None\u001b[0m\u001b[1;34m\n",
      "        \u001b[0mgrouper, exclusions, obj \u001b[1;34m= (None, None,       query_date                   usr       query_type  failed_queries_cnt\n",
      "0     2024-07-12     sa.john.bergamini           SELECT                 490\n",
      "1     2024-07-12      sa.angel.asensio           SELECT                   1\n",
      "2     2024-07-12  sa.victor.coustenobl               NA                   2\n",
      "3     2024-07-12         cache_service           INSERT                 753\n",
      "4     2024-07-12   sa.scarlett.whedbee           SELECT                  30\n",
      "...          ...                   ...              ...                 ...\n",
      "1246  2024-04-13      stargate_service           SELECT                  27\n",
      "1247  2024-04-12         matthew.duran           SELECT                  53\n",
      "1248  2024-04-12         matthew.duran  DATA_DEFINITION                   2\n",
      "1249  2024-04-12         cache_service           INSERT                 313\n",
      "1250  2024-04-12         matthew.duran               NA                   1\n",
      "\n",
      "[1251 rows x 4 columns])\u001b[0m\u001b[1;34m\n",
      "        \u001b[0mexclusions \u001b[1;34m= None\u001b[0m\u001b[1;34m\n",
      "        \u001b[0mkeys \u001b[1;34m= ['query_date', 'error_type']\u001b[0m\u001b[1;34m\n",
      "        \u001b[0maxis \u001b[1;34m= 0\u001b[0m\u001b[1;34m\n",
      "        \u001b[0mlevel \u001b[1;34m= None\u001b[0m\u001b[1;34m\n",
      "        \u001b[0msort \u001b[1;34m= True\u001b[0m\u001b[1;34m\n",
      "        \u001b[0mlib.no_default \u001b[1;34m= <no_default>\u001b[0m\u001b[1;34m\n",
      "        \u001b[0mobserved is lib.no_default \u001b[1;34m= True\u001b[0m\u001b[1;34m\n",
      "        \u001b[0mobserved \u001b[1;34m= <no_default>\u001b[0m\u001b[1;34m\n",
      "        \u001b[0mlib \u001b[1;34m= <module 'pandas._libs.lib' from '/Users/satya.dixit/anaconda3/lib/python3.11/site-packages/pandas/_libs/lib.cpython-311-darwin.so'>\u001b[0m\u001b[1;34m\n",
      "        \u001b[0mself.dropna \u001b[1;34m= True\u001b[0m\u001b[1;34m\n",
      "        \u001b[0mself \u001b[1;34m= <pandas.core.groupby.generic.DataFrameGroupBy object at 0x15f9dba90>\u001b[0m\n",
      "\u001b[0;32m   1275\u001b[0m         obj,\n",
      "\u001b[0;32m   1276\u001b[0m         keys,\n",
      "\u001b[0;32m   1277\u001b[0m         axis\u001b[38;5;241m=\u001b[39maxis,\n",
      "\u001b[0;32m   1278\u001b[0m         level\u001b[38;5;241m=\u001b[39mlevel,\n",
      "\u001b[0;32m   1279\u001b[0m         sort\u001b[38;5;241m=\u001b[39msort,\n",
      "\u001b[0;32m   1280\u001b[0m         observed\u001b[38;5;241m=\u001b[39m\u001b[38;5;28;01mFalse\u001b[39;00m \u001b[38;5;28;01mif\u001b[39;00m observed \u001b[38;5;129;01mis\u001b[39;00m lib\u001b[38;5;241m.\u001b[39mno_default \u001b[38;5;28;01melse\u001b[39;00m observed,\n",
      "\u001b[0;32m   1281\u001b[0m         dropna\u001b[38;5;241m=\u001b[39m\u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mdropna,\n",
      "\u001b[0;32m   1282\u001b[0m     )\n",
      "\u001b[0;32m   1284\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m observed \u001b[38;5;129;01mis\u001b[39;00m lib\u001b[38;5;241m.\u001b[39mno_default:\n",
      "\u001b[0;32m   1285\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28many\u001b[39m(ping\u001b[38;5;241m.\u001b[39m_passed_categorical \u001b[38;5;28;01mfor\u001b[39;00m ping \u001b[38;5;129;01min\u001b[39;00m grouper\u001b[38;5;241m.\u001b[39mgroupings):\n",
      "\n",
      "File \u001b[1;32m~/anaconda3/lib/python3.11/site-packages/pandas/core/groupby/grouper.py:1009\u001b[0m, in \u001b[0;36mget_grouper\u001b[1;34m(\n",
      "    obj=      query_date                   usr       que...  NA                   1\n",
      "\n",
      "[1251 rows x 4 columns],\n",
      "    key=['query_date', 'error_type'],\n",
      "    axis=0,\n",
      "    level=None,\n",
      "    sort=True,\n",
      "    observed=False,\n",
      "    validate=True,\n",
      "    dropna=True\n",
      ")\u001b[0m\n",
      "\u001b[0;32m   1007\u001b[0m         in_axis, level, gpr \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;01mFalse\u001b[39;00m, gpr, \u001b[38;5;28;01mNone\u001b[39;00m\n",
      "\u001b[0;32m   1008\u001b[0m     \u001b[38;5;28;01melse\u001b[39;00m:\n",
      "\u001b[1;32m-> 1009\u001b[0m         \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mKeyError\u001b[39;00m(gpr)\n",
      "        gpr \u001b[1;34m= 'error_type'\u001b[0m\n",
      "\u001b[0;32m   1010\u001b[0m \u001b[38;5;28;01melif\u001b[39;00m \u001b[38;5;28misinstance\u001b[39m(gpr, Grouper) \u001b[38;5;129;01mand\u001b[39;00m gpr\u001b[38;5;241m.\u001b[39mkey \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m:\n",
      "\u001b[0;32m   1011\u001b[0m     \u001b[38;5;66;03m# Add key to exclusions\u001b[39;00m\n",
      "\u001b[0;32m   1012\u001b[0m     exclusions\u001b[38;5;241m.\u001b[39madd(gpr\u001b[38;5;241m.\u001b[39mkey)\n",
      "\n",
      "\u001b[1;31mKeyError\u001b[0m: 'error_type'\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df = query_results['query_failure_by_query_type']\n",
    "\n",
    "filtered_df=df\n",
    "\n",
    "# Initialize the Dash app\n",
    "app = dash.Dash(__name__)\n",
    "\n",
    "# Define the layout of the app\n",
    "app.layout = html.Div([\n",
    "    html.H1(\"Failed Queries Count By Query Type\"),\n",
    "    \n",
    "    # Dynamic multi-select slicer based on unique values in the 'query_type' column\n",
    "    dcc.Checklist(\n",
    "        id='query-type-slicer',\n",
    "        options=[\n",
    "            {'label': query_type, 'value': query_type}\n",
    "            for query_type in filtered_df['query_type'].unique()  # Generate options dynamically\n",
    "        ],\n",
    "        value=filtered_df['query_type'].unique().tolist(),  # Default selection (all unique values)\n",
    "    ),\n",
    "    \n",
    "    # Chart to display the sliced data\n",
    "    dcc.Graph(id='bar-chart'),\n",
    "])\n",
    "\n",
    "# Define a callback function to update the chart based on the selected slicer values\n",
    "@app.callback(\n",
    "    Output('bar-chart', 'figure'),\n",
    "    Input('query-type-slicer', 'value')\n",
    ")\n",
    "def update_chart(selected_query_types):\n",
    "    # Filter data by selected query types\n",
    "    filtered_df = df[df['query_type'].isin(selected_query_types)]\n",
    "    \n",
    "    # Group the data by 'query_date' and 'query_type' and aggregate the count of failed queries\n",
    "    grouped_df = filtered_df.groupby(['query_date', 'query_type'])['failed_queries_cnt'].sum().reset_index()\n",
    "    \n",
    "    # Create the bar chart with 'query_date' on the x-axis, 'failed_queries_cnt' on the y-axis, and color by 'query_type'\n",
    "    fig = px.bar(grouped_df, x='query_date', y='failed_queries_cnt', color='query_type',\n",
    "                 labels={'query_date': 'Query Date', 'failed_queries_cnt': 'Failed Queries Count'},\n",
    "                 title='')\n",
    "    return fig\n",
    "\n",
    "# Run the app\n",
    "if __name__ == '__main__':\n",
    "    app.run_server(debug=True, port=8064)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d813b180",
   "metadata": {},
   "source": [
    "Failed Queries Count By Error Type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "9e36c95a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "        <iframe\n",
       "            width=\"100%\"\n",
       "            height=\"650\"\n",
       "            src=\"http://127.0.0.1:8065/\"\n",
       "            frameborder=\"0\"\n",
       "            allowfullscreen\n",
       "            \n",
       "        ></iframe>\n",
       "        "
      ],
      "text/plain": [
       "<IPython.lib.display.IFrame at 0x168968cd0>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m\n",
      "\u001b[1;31mKeyError\u001b[0m                                  Traceback (most recent call last)\n",
      "File \u001b[1;32m~/anaconda3/lib/python3.11/site-packages/pandas/core/indexes/base.py:3790\u001b[0m, in \u001b[0;36mIndex.get_loc\u001b[1;34m(\n",
      "    self=Index(['query_date', 'usr', 'error_type', 'error...     'failed_queries_cnt'],\n",
      "      dtype='object'),\n",
      "    key='query_type'\n",
      ")\u001b[0m\n",
      "\u001b[0;32m   3789\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n",
      "\u001b[1;32m-> 3790\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_engine\u001b[38;5;241m.\u001b[39mget_loc(casted_key)\n",
      "        casted_key \u001b[1;34m= 'query_type'\u001b[0m\u001b[1;34m\n",
      "        \u001b[0mself \u001b[1;34m= Index(['query_date', 'usr', 'error_type', 'error_code', 'error_name',\n",
      "       'failed_queries_cnt'],\n",
      "      dtype='object')\u001b[0m\n",
      "\u001b[0;32m   3791\u001b[0m \u001b[38;5;28;01mexcept\u001b[39;00m \u001b[38;5;167;01mKeyError\u001b[39;00m \u001b[38;5;28;01mas\u001b[39;00m err:\n",
      "\n",
      "File \u001b[1;32mindex.pyx:152\u001b[0m, in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[1;34m()\u001b[0m\n",
      "\n",
      "File \u001b[1;32mindex.pyx:181\u001b[0m, in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[1;34m()\u001b[0m\n",
      "\n",
      "File \u001b[1;32mpandas/_libs/hashtable_class_helper.pxi:7080\u001b[0m, in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[1;34m()\u001b[0m\n",
      "\n",
      "File \u001b[1;32mpandas/_libs/hashtable_class_helper.pxi:7088\u001b[0m, in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[1;34m()\u001b[0m\n",
      "\n",
      "\u001b[1;31mKeyError\u001b[0m: 'query_type'\n",
      "\n",
      "The above exception was the direct cause of the following exception:\n",
      "\n",
      "\u001b[1;31mKeyError\u001b[0m                                  Traceback (most recent call last)\n",
      "\u001b[1;31mKeyError\u001b[0m: 'query_type'\n",
      "\n"
     ]
    }
   ],
   "source": [
    "import plotly.express as px\n",
    "import pandas as pd\n",
    "import dash\n",
    "from dash import dcc, html\n",
    "from dash.dependencies import Input, Output\n",
    "\n",
    "# Assuming query_results is already populated\n",
    "df = query_results['query_failure_by_error_type']\n",
    "\n",
    "# Define the dropdown options for error types\n",
    "error_type_options = [{'label': error_type, 'value': error_type} for error_type in df['error_type'].unique()]\n",
    "error_type_options.insert(0, {'label': 'ALL', 'value': 'ALL'})\n",
    "\n",
    "# Define the dropdown options for users\n",
    "usr_options = [{'label': usr, 'value': usr} for usr in df['usr'].unique()]\n",
    "usr_options.insert(0, {'label': 'ALL', 'value': 'ALL'})\n",
    "\n",
    "# Initialize the Dash app\n",
    "app = dash.Dash(__name__)\n",
    "\n",
    "# Define the layout of the app\n",
    "app.layout = html.Div([\n",
    "    html.H1(\"Failed Queries Count by Error Type\"),\n",
    "    \n",
    "    # Dropdown for selecting error types\n",
    "    html.Div([\n",
    "        html.Label(\"Error Types\"),\n",
    "        dcc.Dropdown(\n",
    "            id='error-type-dropdown',\n",
    "            options=error_type_options,\n",
    "            value=['ALL'],  # Default selection includes 'ALL'\n",
    "            multi=True,  # Allow multiple selections\n",
    "            clearable=True,  # Allow clearing selections\n",
    "            placeholder=\"Select error type(s)\",  # Placeholder text\n",
    "        ),\n",
    "    ], style={'padding': '10px'}),\n",
    "    \n",
    "    # Dropdown for selecting users\n",
    "    html.Div([\n",
    "        html.Label(\"Users\"),\n",
    "        dcc.Dropdown(\n",
    "            id='usr-dropdown',\n",
    "            options=usr_options,\n",
    "            value=['ALL'],  # Default selection includes 'ALL'\n",
    "            multi=True,  # Allow multiple selections\n",
    "            clearable=True,  # Allow clearing selections\n",
    "            placeholder=\"Select users\",  # Placeholder text\n",
    "        ),\n",
    "    ], style={'padding': '10px'}),\n",
    "    \n",
    "    # Chart to display the sliced data\n",
    "    dcc.Graph(id='bar-chart'),\n",
    "])\n",
    "\n",
    "# Define a callback function to update the chart based on the selected slicer values\n",
    "@app.callback(\n",
    "    Output('bar-chart', 'figure'),\n",
    "    Input('error-type-dropdown', 'value'),\n",
    "    Input('usr-dropdown', 'value')\n",
    ")\n",
    "def update_chart(selected_error_types, selected_usr):\n",
    "    # Filter data by selected error types\n",
    "    filtered_df = df.copy()\n",
    "    if 'ALL' not in selected_error_types:\n",
    "        filtered_df = filtered_df[filtered_df['error_type'].isin(selected_error_types)]\n",
    "    \n",
    "    # Filter data by selected users\n",
    "    if 'ALL' not in selected_usr:\n",
    "        filtered_df = filtered_df[filtered_df['usr'].isin(selected_usr)]\n",
    "    \n",
    "    # Group the data by 'query_date' and 'error_type' and aggregate the count of failed queries\n",
    "    grouped_df = filtered_df.groupby(['query_date', 'error_type'])['failed_queries_cnt'].sum().reset_index()\n",
    "    \n",
    "    # Create the bar chart with 'query_date' on the x-axis, 'failed_queries_cnt' on the y-axis, and color by 'error_type'\n",
    "    fig = px.bar(grouped_df, x='query_date', y='failed_queries_cnt', color='error_type',\n",
    "                 labels={'query_date': 'Query Date', 'failed_queries_cnt': 'Failed Queries Count'},\n",
    "                 title='')\n",
    "    return fig\n",
    "\n",
    "# Run the app\n",
    "if __name__ == '__main__':\n",
    "    app.run_server(debug=True, port=8065)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f5645536",
   "metadata": {},
   "source": [
    "Failed Queries By Error Name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "ffdd356f",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "        <iframe\n",
       "            width=\"100%\"\n",
       "            height=\"650\"\n",
       "            src=\"http://127.0.0.1:8066/\"\n",
       "            frameborder=\"0\"\n",
       "            allowfullscreen\n",
       "            \n",
       "        ></iframe>\n",
       "        "
      ],
      "text/plain": [
       "<IPython.lib.display.IFrame at 0x28f4ff910>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import plotly.express as px\n",
    "import pandas as pd\n",
    "import dash\n",
    "from dash import dcc, html\n",
    "from dash.dependencies import Input, Output, State\n",
    "\n",
    "# Assuming query_results is already populated\n",
    "df = query_results['query_failure_by_error_type_and_name']\n",
    "\n",
    "# Define the dropdown options for query types\n",
    "query_type_options = [{'label': query_type, 'value': query_type} for query_type in df['query_type'].unique()]\n",
    "query_type_options.insert(0, {'label': 'ALL', 'value': 'ALL'})\n",
    "\n",
    "# Define the dropdown options for error types\n",
    "error_type_options = [{'label': error_type, 'value': error_type} for error_type in df['error_type'].unique()]\n",
    "error_type_options.insert(0, {'label': 'ALL', 'value': 'ALL'})\n",
    "\n",
    "# Define the dropdown options for usr\n",
    "usr_options = [{'label': usr, 'value': usr} for usr in df['usr'].unique()]\n",
    "usr_options.insert(0, {'label': 'ALL', 'value': 'ALL'})\n",
    "\n",
    "# Initialize the Dash app\n",
    "app = dash.Dash(__name__)\n",
    "\n",
    "# Define the layout of the app\n",
    "app.layout = html.Div([\n",
    "    html.H1(\"Failed Queries Count By Error Name\"),\n",
    "    \n",
    "    # Dropdown for selecting query types\n",
    "    html.Div([\n",
    "        html.Label(\"Query Types\"),\n",
    "        dcc.Dropdown(\n",
    "            id='query-type-dropdown',\n",
    "            options=query_type_options,\n",
    "            value=['ALL'],  # Default selection includes 'ALL'\n",
    "            multi=True,  # Allow multiple selections\n",
    "            clearable=True,  # Allow clearing selections\n",
    "            placeholder=\"Select query type(s)\",  # Placeholder text\n",
    "        ),\n",
    "    ], style={'padding': '10px'}),\n",
    "    \n",
    "    # Dropdown for selecting error types\n",
    "    html.Div([\n",
    "        html.Label(\"Error Types\"),\n",
    "        dcc.Dropdown(\n",
    "            id='error-type-dropdown',\n",
    "            options=error_type_options,\n",
    "            value=['ALL'],  # Default selection includes 'ALL'\n",
    "            multi=True,  # Allow multiple selections\n",
    "            clearable=True,  # Allow clearing selections\n",
    "            placeholder=\"Select error type(s)\",  # Placeholder text\n",
    "        ),\n",
    "    ], style={'padding': '10px'}),\n",
    "\n",
    "    # Dropdown for selecting usr\n",
    "    html.Div([\n",
    "        html.Label(\"Users\"),\n",
    "        dcc.Dropdown(\n",
    "            id='usr-dropdown',\n",
    "            options=usr_options,\n",
    "            value=['ALL'],  # Default selection includes 'ALL'\n",
    "            multi=True,  # Allow multiple selections\n",
    "            clearable=True,  # Allow clearing selections\n",
    "            placeholder=\"Select users\",  # Placeholder text\n",
    "        ),\n",
    "    ], style={'padding': '10px'}),\n",
    "    \n",
    "    # Search bar for error names\n",
    "    html.Div([\n",
    "        html.Label(\"Search Error Names\"),\n",
    "        dcc.Input(\n",
    "            id='search-error-names',\n",
    "            type='text',\n",
    "            placeholder='Search error names...',\n",
    "            style={'width': '100%', 'padding': '10px'}\n",
    "        ),\n",
    "    ], style={'padding': '10px'}),\n",
    "\n",
    "    # Chart to display the sliced data\n",
    "    dcc.Graph(id='bar-chart'),\n",
    "])\n",
    "\n",
    "# Define a callback function to update the chart based on the selected slicer values\n",
    "@app.callback(\n",
    "    Output('bar-chart', 'figure'),\n",
    "    Input('query-type-dropdown', 'value'),\n",
    "    Input('error-type-dropdown', 'value'),\n",
    "    Input('usr-dropdown', 'value'),\n",
    "    Input('search-error-names', 'value')\n",
    ")\n",
    "def update_chart(selected_query_types, selected_error_types, selected_usr, search_error_name):\n",
    "    # Filter data by selected query types\n",
    "    filtered_df = df.copy()\n",
    "    if 'ALL' not in selected_query_types:\n",
    "        filtered_df = filtered_df[filtered_df['query_type'].isin(selected_query_types)]\n",
    "    \n",
    "    # Filter data by selected error types\n",
    "    if 'ALL' not in selected_error_types:\n",
    "        filtered_df = filtered_df[filtered_df['error_type'].isin(selected_error_types)]\n",
    "    \n",
    "    # Filter data by selected users\n",
    "    if 'ALL' not in selected_usr:\n",
    "        filtered_df = filtered_df[filtered_df['usr'].isin(selected_usr)]\n",
    "\n",
    "    # Filter data by search error name\n",
    "    if search_error_name:\n",
    "        filtered_df = filtered_df[filtered_df['error_code_name'].str.contains(search_error_name, case=False, na=False)]\n",
    "\n",
    "    # Group the data by 'query_date' and 'error_code_name' and aggregate the count of failed queries\n",
    "    grouped_df = filtered_df.groupby(['query_date', 'error_code_name'])['failed_queries_cnt'].sum().reset_index()\n",
    "    \n",
    "    # Create the bar chart with 'query_date' on the x-axis, 'failed_queries_cnt' on the y-axis, and color by 'error_code_name'\n",
    "    fig = px.bar(grouped_df, x='query_date', y='failed_queries_cnt', color='error_code_name',\n",
    "                 labels={'query_date': 'Query Date', 'failed_queries_cnt': 'Failed Queries Count'},\n",
    "                 title='')\n",
    "\n",
    "    # Update the layout to include all error names selected by default\n",
    "    fig.update_layout(\n",
    "        legend_title_text='Error Names',\n",
    "        legend=dict(\n",
    "            itemsizing='constant',\n",
    "            itemclick='toggleothers',  # Click to isolate the error name\n",
    "        ),\n",
    "        legend_traceorder=\"normal\",\n",
    "    )\n",
    "    \n",
    "    return fig\n",
    "\n",
    "# Run the app\n",
    "if __name__ == '__main__':\n",
    "    app.run_server(debug=True, port=8066)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cfd4f36e",
   "metadata": {},
   "source": [
    "Queries Per Minute"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "b9875905",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "        <iframe\n",
       "            width=\"100%\"\n",
       "            height=\"650\"\n",
       "            src=\"http://127.0.0.1:8067/\"\n",
       "            frameborder=\"0\"\n",
       "            allowfullscreen\n",
       "            \n",
       "        ></iframe>\n",
       "        "
      ],
      "text/plain": [
       "<IPython.lib.display.IFrame at 0x28fc4e350>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import plotly.express as px\n",
    "import pandas as pd\n",
    "import dash\n",
    "from dash import dcc, html\n",
    "from dash.dependencies import Input, Output\n",
    "\n",
    "# Assuming query_results is already populated\n",
    "df = query_results['queries_per_minute']\n",
    "\n",
    "# Convert 'query_date' to a date-time data type if it's not already\n",
    "df['query_date'] = pd.to_datetime(df['query_date'])\n",
    "\n",
    "# Define the dropdown options for query types\n",
    "query_type_options = [{'label': query_type, 'value': query_type} for query_type in df['query_type'].unique()]\n",
    "query_type_options.insert(0, {'label': 'ALL', 'value': 'ALL'})\n",
    "\n",
    "# Define the dropdown options for query dates\n",
    "query_date_options = [{'label': str(date), 'value': str(date)} for date in df['query_date'].dt.date.unique()]\n",
    "query_date_options.insert(0, {'label': 'ALL', 'value': 'ALL'})\n",
    "\n",
    "# Define the dropdown options for usr\n",
    "usr_options = [{'label': usr, 'value': usr} for usr in df['usr'].unique()]\n",
    "usr_options.insert(0, {'label': 'ALL', 'value': 'ALL'})\n",
    "\n",
    "# Initialize the Dash app\n",
    "app1 = dash.Dash(__name__)\n",
    "\n",
    "# Define the layout of the app\n",
    "app1.layout = html.Div([\n",
    "    html.H1(\"Queries Per Minute\"),\n",
    "    \n",
    "    # Dropdown for selecting query types\n",
    "    html.Div([\n",
    "        html.Label(\"Query Types\"),\n",
    "        dcc.Dropdown(\n",
    "            id='query-type-slicer-1',\n",
    "            options=query_type_options,\n",
    "            value=['ALL'],  # Default selection includes 'ALL'\n",
    "            multi=True,  # Allow multiple selections\n",
    "            clearable=True,  # Allow clearing selections\n",
    "            placeholder=\"Select query types\",  # Placeholder text\n",
    "        ),\n",
    "    ], style={'padding': '10px'}),\n",
    "    \n",
    "    # Dropdown for selecting query dates\n",
    "    html.Div([\n",
    "        html.Label(\"Query Dates\"),\n",
    "        dcc.Dropdown(\n",
    "            id='query-date-slicer-1',\n",
    "            options=query_date_options,\n",
    "            value=['ALL'],  # Default selection includes 'ALL'\n",
    "            multi=True,  # Allow multiple selections\n",
    "            clearable=True,  # Allow clearing selections\n",
    "            placeholder=\"Select query dates\",  # Placeholder text\n",
    "        ),\n",
    "    ], style={'padding': '10px'}),\n",
    "    \n",
    "    # Dropdown for selecting usr\n",
    "    html.Div([\n",
    "        html.Label(\"Users\"),\n",
    "        dcc.Dropdown(\n",
    "            id='usr-slicer-1',\n",
    "            options=usr_options,\n",
    "            value=['ALL'],  # Default selection includes 'ALL'\n",
    "            multi=True,  # Allow multiple selections\n",
    "            clearable=True,  # Allow clearing selections\n",
    "            placeholder=\"Select users\",  # Placeholder text\n",
    "        ),\n",
    "    ], style={'padding': '10px'}),\n",
    "    \n",
    "    # Placeholder for the selected chart\n",
    "    dcc.Graph(id='selected-chart-1'),\n",
    "])\n",
    "\n",
    "# Define callback function to update the selected chart\n",
    "@app1.callback(\n",
    "    Output('selected-chart-1', 'figure'),\n",
    "    Input('query-type-slicer-1', 'value'),\n",
    "    Input('query-date-slicer-1', 'value'),\n",
    "    Input('usr-slicer-1', 'value')\n",
    ")\n",
    "def update_selected_chart(selected_query_types, selected_query_dates, selected_usr):\n",
    "    \n",
    "    # Filter the data frame based on selected query types\n",
    "    if 'ALL' in selected_query_types:\n",
    "        filtered_df = df  # Include all data if 'ALL' is selected\n",
    "    else:\n",
    "        filtered_df = df[df['query_type'].isin(selected_query_types)]\n",
    "    \n",
    "    # Further filter the data frame based on selected query dates\n",
    "    if 'ALL' not in selected_query_dates:\n",
    "        filtered_df = filtered_df[filtered_df['query_date'].dt.date.isin([pd.to_datetime(date).date() for date in selected_query_dates])]\n",
    "    \n",
    "    # Further filter the data frame based on selected usr\n",
    "    if 'ALL' not in selected_usr:\n",
    "        filtered_df = filtered_df[filtered_df['usr'].isin(selected_usr)]\n",
    "    \n",
    "    # Create a scatter plot for the selected data frame\n",
    "    selected_fig = px.scatter(filtered_df, x='query_date', y='query_minute', size='num_queries', color='num_queries', hover_name='num_queries')\n",
    "    \n",
    "    return selected_fig\n",
    "\n",
    "# Run the app\n",
    "if __name__ == '__main__':\n",
    "    app1.run_server(debug=True, port=8067)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8e1eba24",
   "metadata": {},
   "source": [
    "Data Processed Over Time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "a0a4cb13",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "        <iframe\n",
       "            width=\"100%\"\n",
       "            height=\"650\"\n",
       "            src=\"http://127.0.0.1:8068/\"\n",
       "            frameborder=\"0\"\n",
       "            allowfullscreen\n",
       "            \n",
       "        ></iframe>\n",
       "        "
      ],
      "text/plain": [
       "<IPython.lib.display.IFrame at 0x2b74ef450>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "\n",
    "import pandas as pd\n",
    "import plotly.express as px\n",
    "from dash import Dash, dcc, html\n",
    "from dash.dependencies import Input, Output\n",
    "\n",
    "# Assuming query_results is already populated\n",
    "df = query_results['data_processed']\n",
    "\n",
    "# Convert 'query_date' to a date-time data type\n",
    "df['query_date'] = pd.to_datetime(df['query_date'])\n",
    "\n",
    "# Create a list of KPIs to plot\n",
    "kpis = [\n",
    "    'avg_total_rows', 'avg_output_rows', 'avg_total_bytes',\n",
    "    'avg_output_bytes', 'avg_physical_input_bytes',\n",
    "    'avg_physical_input_rows', 'avg_completed_splits'\n",
    "]\n",
    "\n",
    "# Initialize the Dash app\n",
    "app = Dash(__name__)\n",
    "\n",
    "# Define the layout of the app\n",
    "app.layout = html.Div([\n",
    "    html.H1(\"Data Processed Over Time\"),\n",
    "    \n",
    "    dcc.Dropdown(\n",
    "        id='kpi-selector',\n",
    "        options=[{'label': kpi, 'value': kpi} for kpi in kpis],\n",
    "        value=kpis[0],\n",
    "        multi=False\n",
    "    ),\n",
    "    \n",
    "    dcc.Graph(id='line-chart')\n",
    "])\n",
    "\n",
    "# Define a callback function to update the chart based on the selected KPI\n",
    "@app.callback(\n",
    "    Output('line-chart', 'figure'),\n",
    "    Input('kpi-selector', 'value')\n",
    ")\n",
    "def update_chart(selected_kpi):\n",
    "    # Filter data for the selected KPI\n",
    "    fig = px.line(df, x='query_date', y=selected_kpi, \n",
    "                  labels={'query_date': 'Query Date', selected_kpi: selected_kpi},\n",
    "                  title=f'{selected_kpi}',\n",
    "                  template='plotly_dark')\n",
    "    \n",
    "    # Customize hover information\n",
    "    fig.update_traces(mode='lines+markers', hovertemplate=f'Query Date: %{{x}}<br>{selected_kpi}: %{{y}}<extra></extra>')\n",
    "    \n",
    "    return fig\n",
    "\n",
    "# Run the app\n",
    "if __name__ == '__main__':\n",
    "    app.run_server(debug=True, port=8068)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d86f48d3",
   "metadata": {},
   "source": [
    "Query Performance And Time Metrics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "0934157d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "        <iframe\n",
       "            width=\"100%\"\n",
       "            height=\"650\"\n",
       "            src=\"http://127.0.0.1:8069/\"\n",
       "            frameborder=\"0\"\n",
       "            allowfullscreen\n",
       "            \n",
       "        ></iframe>\n",
       "        "
      ],
      "text/plain": [
       "<IPython.lib.display.IFrame at 0x2b8998150>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import plotly.express as px\n",
    "from dash import Dash, dcc, html\n",
    "from dash.dependencies import Input, Output\n",
    "\n",
    "# Assuming query_results is already populated\n",
    "df = query_results['query_perf_and_time_metrics']\n",
    "\n",
    "# Convert 'query_date' to a date-time data type\n",
    "df['query_date'] = pd.to_datetime(df['query_date'])\n",
    "\n",
    "# Create a list of KPIs to plot\n",
    "kpis = [\n",
    "    'avg_cpu_time_secs', 'avg_wall_time_secs', 'avg_queued_time_secs',\n",
    "    'avg_resource_waiting_time_secs', 'avg_analysis_time_secs',\n",
    "    'avg_execution_time_secs', 'avg_planning_time_secs', 'avg_scheduled_time_secs'\n",
    "]\n",
    "\n",
    "# Initialize the Dash app\n",
    "app = Dash(__name__)\n",
    "\n",
    "# Define the layout of the app\n",
    "app.layout = html.Div([\n",
    "    html.H1(\"Query Performance and Time Metrics\"),\n",
    "    \n",
    "    dcc.Dropdown(\n",
    "        id='kpi-selector',\n",
    "        options=[{'label': kpi, 'value': kpi} for kpi in kpis],\n",
    "        value=kpis[0],\n",
    "        multi=False\n",
    "    ),\n",
    "    \n",
    "    dcc.Graph(id='line-chart')\n",
    "])\n",
    "\n",
    "# Define a callback function to update the chart based on the selected KPI\n",
    "@app.callback(\n",
    "    Output('line-chart', 'figure'),\n",
    "    Input('kpi-selector', 'value')\n",
    ")\n",
    "def update_chart(selected_kpi):\n",
    "    # Filter data for the selected KPI\n",
    "    fig = px.line(df, x='query_date', y=selected_kpi, \n",
    "                  labels={'query_date': 'Query Date', selected_kpi: selected_kpi},\n",
    "                  title=f'{selected_kpi} Trend Over Time',\n",
    "                  template='plotly_dark')\n",
    "    \n",
    "    # Customize hover information\n",
    "    fig.update_traces(mode='lines+markers', hovertemplate=f'Query Date: %{{x}}<br>{selected_kpi}: %{{y}}<extra></extra>')\n",
    "    \n",
    "    # Set y-axis tick format to plain to avoid automatic unit conversion\n",
    "    fig.update_yaxes(tickformat=\"\")\n",
    "\n",
    "    return fig\n",
    "\n",
    "# Run the app\n",
    "if __name__ == '__main__':\n",
    "    app.run_server(debug=True, port=8069)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "52c12314",
   "metadata": {},
   "source": [
    "Top X Queries Analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "f633b2ad",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "        <iframe\n",
       "            width=\"100%\"\n",
       "            height=\"650\"\n",
       "            src=\"http://127.0.0.1:8070/\"\n",
       "            frameborder=\"0\"\n",
       "            allowfullscreen\n",
       "            \n",
       "        ></iframe>\n",
       "        "
      ],
      "text/plain": [
       "<IPython.lib.display.IFrame at 0x28f9d04d0>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import dash\n",
    "from dash import dcc, html\n",
    "from dash.dependencies import Input, Output\n",
    "\n",
    "max_display_chars = 50\n",
    "# Define a list of DataFrame keys and their corresponding labels\n",
    "data_frames = {\n",
    "    'top_x_data_scanned': 'Top X Data Scanned',\n",
    "    'top_x_splits_completed': 'Top X Completed splits',\n",
    "    'top_x_cpu_time': 'Top X CPU Time',\n",
    "    'top_x_execution_time': 'Top X Execution Time',\n",
    "    'top_x_scheduled_time': 'Top X Scheduled Time',\n",
    "    'top_x_analysis_time': 'Top X Analysis Time',\n",
    "    'top_x_planning_time': 'Top X Planning Time'\n",
    "}\n",
    "\n",
    "\n",
    "# Initialize the Dash app\n",
    "app = dash.Dash(__name__)\n",
    "\n",
    "# Define the layout of the app\n",
    "app.layout = html.Div([\n",
    "    html.H1(\"Top X Queries Analysis\"),\n",
    "    \n",
    "    # Dropdown for selecting the DataFrame\n",
    "    dcc.Dropdown(\n",
    "        id='data-frame-dropdown',\n",
    "        options=[{'label': label, 'value': key} for key, label in data_frames.items()],\n",
    "        value=list(data_frames.keys())[0],  # Default selection\n",
    "    ),\n",
    "    \n",
    "    # Dropdown for selecting query_date\n",
    "    dcc.Dropdown(\n",
    "        id='query-date-dropdown',\n",
    "        options=[],\n",
    "        multi=False,  # Allow only one selection\n",
    "        placeholder=\"Select query date\",\n",
    "    ),\n",
    "    \n",
    "    # Dropdown for selecting query_type\n",
    "    dcc.Dropdown(\n",
    "        id='query-type-dropdown',\n",
    "        options=[],\n",
    "        multi=False,  # Allow only one selection\n",
    "        placeholder=\"Select query type\",\n",
    "    ),\n",
    "    \n",
    "    # Table to display the filtered results\n",
    "    dcc.Loading(\n",
    "        id=\"loading-table\",\n",
    "        type=\"circle\",\n",
    "        children=[\n",
    "            html.Table(\n",
    "                id='query-results-table',\n",
    "                className='table',  # Apply CSS class for table styling\n",
    "                children=[\n",
    "                    html.Tr([html.Th(col) for col in []]),  # Empty headers initially\n",
    "                ],\n",
    "            ),\n",
    "        ],\n",
    "    ),\n",
    "    \n",
    "    # Hidden div to store the selected query\n",
    "    html.Div(id='selected-query', style={'display': 'none'}),\n",
    "])\n",
    "\n",
    "# Define a callback function to populate the date and type dropdowns based on the selected DataFrame\n",
    "@app.callback(\n",
    "    Output('query-date-dropdown', 'options'),\n",
    "    Output('query-type-dropdown', 'options'),\n",
    "    Input('data-frame-dropdown', 'value')\n",
    ")\n",
    "def update_dropdown_options(selected_data_frame):\n",
    "    # Replace this with your actual data frames and columns\n",
    "    if selected_data_frame == 'top_x_data_scanned':\n",
    "        df = query_results['top_x_data_scanned']\n",
    "    if selected_data_frame == 'top_x_splits_completed':\n",
    "        df = query_results['top_x_splits_completed']        \n",
    "    elif selected_data_frame == 'top_x_planning_time':\n",
    "        df = query_results['top_x_planning_time']\n",
    "    elif selected_data_frame == 'top_x_cpu_time':\n",
    "        df = query_results['top_x_cpu_time']\n",
    "    elif selected_data_frame == 'top_x_execution_time':\n",
    "        df = query_results['top_x_execution_time']\n",
    "    elif selected_data_frame == 'top_x_scheduled_time':\n",
    "        df = query_results['top_x_scheduled_time']     \n",
    "    elif selected_data_frame == 'top_x_analysis_time':\n",
    "        df = query_results['top_x_analysis_time']     \n",
    "    elif selected_data_frame == 'top_x_queued_time':\n",
    "        df = query_results['top_x_queued_time']   \n",
    "    else:\n",
    "        # Handle unknown data frame key\n",
    "        df = query_results['top_x_planning_time']   \n",
    "#         df = pd.DataFrame()  # Empty DataFrame\n",
    "    \n",
    "    # Populate date and type dropdown options based on the selected DataFrame\n",
    "    date_options = [{'label': date, 'value': date} for date in df['query_date'].unique()]\n",
    "    type_options = [{'label': query_type, 'value': query_type} for query_type in df['query_type'].unique()]\n",
    "    \n",
    "    return date_options, type_options\n",
    "\n",
    "# Define a callback function to update the table and selected query\n",
    "@app.callback(\n",
    "    Output('query-results-table', 'children'),\n",
    "    Output('selected-query', 'children'),\n",
    "    Input('query-date-dropdown', 'value'),\n",
    "    Input('query-type-dropdown', 'value'),\n",
    "    Input('data-frame-dropdown', 'value')\n",
    ")\n",
    "def update_table_and_query(selected_date, selected_type, selected_data_frame):\n",
    "    if selected_data_frame == 'top_x_data_scanned':\n",
    "        df = query_results['top_x_data_scanned']\n",
    "    if selected_data_frame == 'top_x_splits_completed':\n",
    "        df = query_results['top_x_splits_completed']        \n",
    "    elif selected_data_frame == 'top_x_planning_time':\n",
    "        df = query_results['top_x_planning_time']\n",
    "    elif selected_data_frame == 'top_x_cpu_time':\n",
    "        df = query_results['top_x_cpu_time']\n",
    "    elif selected_data_frame == 'top_x_execution_time':\n",
    "        df = query_results['top_x_execution_time']\n",
    "    elif selected_data_frame == 'top_x_scheduled_time':\n",
    "        df = query_results['top_x_scheduled_time']     \n",
    "    elif selected_data_frame == 'top_x_analysis_time':\n",
    "        df = query_results['top_x_analysis_time']     \n",
    "    elif selected_data_frame == 'top_x_queued_time':\n",
    "        df = query_results['top_x_queued_time']   \n",
    "    else:\n",
    "        # Handle unknown data frame key\n",
    "        df = query_results['top_x_data_scanned']   \n",
    "#         df = pd.DataFrame()  # Empty DataFrame\n",
    "    \n",
    "    # Filter the DataFrame based on selected date and type\n",
    "    filtered_df = df \n",
    "    # filtered_df = df.drop(columns=['query'])\n",
    "    if selected_date:\n",
    "        filtered_df = filtered_df[filtered_df['query_date'] == selected_date]\n",
    "    if selected_type:\n",
    "        filtered_df = filtered_df[filtered_df['query_type'] == selected_type]\n",
    "    \n",
    "#     Generate the HTML table with borders and truncated query column\n",
    "    \n",
    "    table = html.Table(\n",
    "        [\n",
    "            html.Tr([html.Th(col) for col in df.columns]),\n",
    "            *[html.Tr([\n",
    "                html.Td(\n",
    "                    filtered_df.iloc[i]['query'][:max_display_chars] + (\n",
    "                        '...' if len(filtered_df.iloc[i]['query']) > max_display_chars else ''), \n",
    "                    title=filtered_df.iloc[i]['query'],  # Full query for tooltip\n",
    "                    className='tooltip', \n",
    "                    **{'data-tooltip': filtered_df.iloc[i]['query']}\n",
    "                ) if col == 'query' else html.Td(filtered_df.iloc[i][col]) \n",
    "                for col in df.columns\n",
    "            ]) for i in range(len(filtered_df))],\n",
    "        ],\n",
    "        className='table-bordered',  # Apply CSS class for table borders\n",
    "    )\n",
    "  \n",
    "    # Determine the selected query text (if any)\n",
    "    selected_query = None\n",
    "    if dash.callback_context.triggered:\n",
    "        trigger_id = dash.callback_context.triggered[0]['prop_id'].split('.')[0]\n",
    "        if trigger_id == 'query-results-table':\n",
    "            selected_query = filtered_df.iloc[dash.callback_context.triggered[0]['row_id']]['query']\n",
    "    \n",
    "    return table, selected_query\n",
    "\n",
    "# Run the app\n",
    "if __name__ == '__main__':\n",
    "    app.run_server(debug=True, port=8070)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cdf5dde2",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.11.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}