对系统元数据视图（Information Schema）​的系统视图提交 SQL 查询，可帮助用户直观查询作业运行情况和资源使用情况，本文提供 7 种典型场景的 SQL 查询示例，帮助用户理解系统元数据视图使用方法。

查询昨日作业历史 #

该查询用于获取指定租户昨日的作业明细，并按作业开始时间倒序展示，查询时显式选择字段，避免视图新增字段影响下游同步任务。

SELECT
 task_id,
 task_name,
 user_id,
 queue_name,
 status,
 engine_type,
 start_time,
 end_time,
 duration,
 sum_cuh,
 origin,
 region
FROM emr_system_catalog.default.emr_job_history
WHERE date = date_format(date_sub(current_date(), 1), 'yyyyMMdd')
ORDER BY start_time DESC
LIMIT 100;

统计昨日作业成功率与资源消耗 #

该查询用于按队列聚合昨日作业数量、失败作业数、平均耗时和总 CU 时，适合用于日常资源治理和作业健康巡检。

SELECT
  queue_name,
  COUNT(*) AS job_count,
  SUM(CASE WHEN status = 'SUCCESS' THEN 1 ELSE 0 END) AS success_count,
  SUM(CASE WHEN status <> 'SUCCESS' THEN 1 ELSE 0 END) AS non_success_count,
  ROUND(AVG(duration), 2) AS avg_duration_seconds,
  ROUND(SUM(sum_cuh), 2) AS total_cuh
FROM emr_system_catalog.default.emr_job_history
WHERE date = date_format(date_sub(current_date(), 1), 'yyyyMMdd')
GROUP BY queue_name
ORDER BY total_cuh DESC;

定位昨日耗时 Top 作业 #

该查询用于定位昨日耗时最长的作业，并输出 SQL、提交来源和资源峰值信息，适合排查慢作业、异常 SQL 或资源参数不合理问题。

SELECT
  task_id,
  task_name,
  user_id,
  queue_name,
  engine_type,
  duration,
  sum_cuh,
  origin,
  driver_cpu_peak_percent,
  executor_cpu_peak_percent,
  driver_mem_peak_percent,
  executor_mem_peak_percent,
  query_sql
FROM emr_system_catalog.default.emr_job_history
WHERE date = date_format(date_sub(current_date(), 1), 'yyyyMMdd')
ORDER BY duration DESC
LIMIT 20;

查看队列资源水位 #

该查询用于查看指定租户昨日各队列的 CPU、内存和 CU 分配率峰值，以及排队作业峰值。排队作业峰值较高且资源分配率长期接近 1 时，通常需要进一步评估队列容量或作业并发策略。

SELECT
  queue_name,
  ROUND(MAX(cu_allocated_rate), 4) AS max_cu_allocated_rate,
  ROUND(AVG(cu_allocated_rate), 4) AS avg_cu_allocated_rate,
  ROUND(MAX(cpu_usage_rate), 4) AS max_cpu_usage_rate,
  ROUND(AVG(cpu_usage_rate), 4) AS avg_cpu_usage_rate,
  ROUND(MAX(mem_usage_rate), 4) AS max_mem_usage_rate,
  ROUND(AVG(mem_usage_rate), 4) AS avg_mem_usage_rate,
  MAX(job_pending) AS max_pending_jobs,
  MAX(job_running) AS max_running_jobs
FROM emr_system_catalog.default.emr_resource_queue
WHERE date = date_format(date_sub(current_date(), 1), 'yyyyMMdd')
GROUP BY queue_name
ORDER BY max_cu_allocated_rate DESC;

查看队列高水位时间点 #

该查询用于找出队列资源分配率或排队作业数较高的时间片，适合定位容量不足、突发作业冲击或调度拥塞时段。

SELECT
  timestamp_str,
  queue_name,
  cu_total,
  cu_allocated,
  cu_allocated_rate,
  cpu_usage_rate,
  mem_usage_rate,
  job_running,
  job_pending
FROM emr_system_catalog.default.emr_resource_queue
WHERE date = date_format(date_sub(current_date(), 1), 'yyyyMMdd')
  AND (cu_allocated_rate >= 0.8 OR job_pending > 0)
ORDER BY timestamp_str ASC, cu_allocated_rate DESC;

查看计算组资源使用 #

该查询用于查看指定租户昨日计算组资源指标原始快照。由于 metrics_json 为 JSON 字符串，用户可以根据当前 SQL 引擎支持的 JSON 函数提取具体指标。

SELECT
  timestamp_str,
  queue_name,
  compute_group_id,
  compute_group_name,
  type,
  metrics_json
FROM emr_system_catalog.default.emr_resource_compute_group
WHERE date = date_format(date_sub(current_date(), 1), 'yyyyMMdd')
ORDER BY timestamp_str DESC
LIMIT 200;

按计算组类型统计快照数量 #

该查询用于检查 common 与 spark 类型计算组在指定日期是否存在资源快照数据，可作为数据可用性巡检的一部分。

SELECT
  type,
  COUNT(*) AS snapshot_count,
  COUNT(DISTINCT compute_group_id) AS compute_group_count,
  MIN(timestamp_str) AS first_snapshot_time,
  MAX(timestamp_str) AS last_snapshot_time
FROM emr_system_catalog.default.emr_resource_compute_group
WHERE date = date_format(date_sub(current_date(), 1), 'yyyyMMdd')
GROUP BY type
ORDER BY snapshot_count DESC;