Slide 13
Slide 13 text
JOBビュー / タイムスライス別ビュー利用例
DECLARE base_time TIMESTAMP;
SET base_time = TIMESTAMP_SUB(CURRENT_TIMESTAMP(),
INTERVAL 1 MINUTE);
WITH errors AS (
SELECT
TIMESTAMP_TRUNC(end_time, SECOND) AS time_sec
, reservation_id
, COUNT(DISTINCT job_id) AS num_errors
FROM
`region-us`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
WHERE
base_time <= creation_time
AND base_time <= end_time
GROUP BY 1, 2
)
, jobs AS (
SELECT
period_start AS time_sec
, reservation_id
, COUNT(DISTINCT job_id) AS num_jobs
, SUM(period_slot_ms) / 1000 AS slots
FROM
`region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_ORGANIZATION
WHERE
base_time <= period_start
GROUP BY 1, 2
)
SELECT
* except(num_errors)
, coalesce(num_errors, 0) AS num_errors
FROM jobs
LEFT OUTER JOIN errors USING(time_sec, reservation_id)
ORDER BY time_sec, reservation_id
一定時間枠ごと、Reservationごとのjob数、エラー発生数、利用 SLOT数