Skip to content

Hive on MR Query Deep Dive

Overview

This dashboard displays Hive query metrics for queries using MR as the execution engine. It helps answer the following questions: - How many Hive on MR queries are there? What are the average duration and IO volume? - What is the operation type distribution (QUERY / CTAS / INSERT, etc.)? - How do the duration trends for each operation change over time? - What are the details of specific queries and their table IO lineage?

Prerequisites

Data sources: hive_query_metrics, hive_table_io_metrics (filtered by execution_engine='mr')

Grafana variables:
$hive_operation — Hive operation type (multi-select, includes All)
$__interval_ms, $__unixEpochFrom(), $__unixEpochTo()

Variable query: SELECT DISTINCT operation FROM hive_query_metrics WHERE execution_engine='mr' AND operation IS NOT NULL ORDER BY operation

Panel Descriptions

Overview Statistics (y=0, 4 stats)

Total Hive-MR Queries (stat)

Purpose: Displays the total number of Hive on MR queries within the time range.

SQL Query: 

SELECT COUNT(*) AS value
FROM hive_query_metrics
WHERE timestamp_ms >= ($__unixEpochFrom() * 1000)
  AND timestamp_ms <= ($__unixEpochTo() * 1000)
  AND execution_engine='mr'

Avg Hive-MR Duration (stat)

Purpose: Displays the average query duration. Thresholds: <5s green, 5-30s yellow, >30s red.

Hive-MR IO Bytes (stat)

Purpose: Displays the total input byte count.

SQL Query: 

SELECT SUM(input_bytes) AS value
FROM hive_query_metrics WHERE execution_engine='mr' AND input_bytes IS NOT NULL

Hive-MR Table Events (stat)

Purpose: Displays the total number of table IO events.

SQL Query: 

SELECT COUNT(*) AS value
FROM hive_table_io_metrics WHERE execution_engine='mr'

Operation Distribution (piechart)

Purpose: Shows the query count distribution by operation type (QUERY / CREATETABLE / INSERT, etc.) using a donut chart.

SQL Query: 

SELECT operation AS metric, COUNT(*) AS value
FROM hive_query_metrics
WHERE execution_engine='mr' AND operation IS NOT NULL
GROUP BY operation ORDER BY value DESC

Usage: If QUERY accounts for a very high proportion while INSERT/CTAS is low, this suggests a read-heavy workload dominated by ad-hoc queries.

Duration by Operation (timeseries)

Purpose: Shows average duration trends by operation type, filterable by the $hive_operation variable.

SQL Query: 

SELECT (FLOOR(timestamp_ms / $__interval_ms) * $__interval_ms / 1000) AS time,
       operation AS metric, AVG(duration_ms) AS value
FROM hive_query_metrics
WHERE execution_engine='mr' AND duration_ms IS NOT NULL
  AND operation IN ($hive_operation)
GROUP BY 1, 2 ORDER BY 1, 2

Usage: A sudden increase in duration for a specific operation may be related to data volume changes or underlying MR Job anomalies.

Detail Tables (y=12)

Query Detail (table)

Purpose: Displays complete details for the most recent 200 Hive on MR queries.

SQL Query: 

SELECT FROM_UNIXTIME(timestamp_ms/1000) AS time, query_id, operation, user_name,
       success, duration_ms, input_bytes, output_bytes, input_rows, output_rows
FROM hive_query_metrics
WHERE execution_engine='mr' AND operation IN ($hive_operation)
ORDER BY timestamp_ms DESC LIMIT 200

Column Descriptions:

Column Description Unit
query_id Hive query unique ID -
operation Operation type -
success OK (green) / FAIL (red) -
input_bytes Input bytes bytes
output_bytes Output bytes bytes
input_rows / output_rows Input/output row count count

Table IO Detail (table)

Purpose: Displays table-level IO lineage (which tables are read/written).

SQL Query: 

SELECT FROM_UNIXTIME(timestamp_ms/1000) AS time, query_id, table_name,
       table_type, operation, user_name
FROM hive_table_io_metrics
WHERE execution_engine='mr' AND operation IN ($hive_operation)
ORDER BY timestamp_ms DESC LIMIT 200

Column Descriptions:

Column Description
table_name Table name
table_type INPUT (blue) / OUTPUT (green)
operation Operation type

Usage: Use this panel to trace data lineage and understand which tables are frequently read or written.

The top navigation bar provides quick access to: - Overview — Platform overview - Spark — Spark engine detailed view - MapReduce — MR engine detailed view - Hive on Spark — Hive on Spark query analysis - Spark / MR / Hive — All-engine consolidated dashboard

Notes

  • The execution_engine='mr' filter depends on the hive.execution.engine configuration reported by the Hive Hook.
  • Hive 3.x defaults to Tez as the execution engine; ensure it is configured to mr for queries to appear in this dashboard.
  • Table IO data depends on hive.exec.post.hooks being correctly configured.