-
Notifications
You must be signed in to change notification settings - Fork 30
/
query_job_information.sql
54 lines (51 loc) · 1.68 KB
/
query_job_information.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
-- This query will return general information from load jobs run over the specified timeframe.
-- Change this value to change how far in the past the query will search
DECLARE interval_in_days INT64 DEFAULT 7;
BEGIN
WITH
src AS (
SELECT
user_email AS user,
query,
job_id AS jobId,
project_id AS projectId,
start_time AS startTime,
end_time AS endTime,
referenced_tables AS referencedTables,
labels,
ROUND(SAFE_DIVIDE(total_bytes_billed,
POW(1024, 4)) * 6.25, 2) AS onDemandCost,
ROUND(COALESCE(total_bytes_billed,
0), 2) AS totalBytesBilled,
ROUND(COALESCE(total_bytes_billed,
0) / POW(1024, 2), 2) AS totalMegabytesBilled,
ROUND(COALESCE(total_bytes_billed,
0) / POW(1024, 3), 2) AS totalGigabytesBilled,
ROUND(COALESCE(total_bytes_billed,
0) / POW(1024, 4), 2) AS totalTerabytesBilled,
total_slot_ms AS totalSlotMs,
ROUND(SAFE_DIVIDE(total_slot_ms,
TIMESTAMP_DIFF(end_time, start_time, MILLISECOND)), 2) AS approximateSlotCount,
ROW_NUMBER() OVER (PARTITION BY job_id ORDER BY end_time DESC) AS _rnk
FROM
`<project-name>`.`<dataset-region>`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL interval_in_days DAY)
AND CURRENT_TIMESTAMP()
AND job_type = 'QUERY'
AND total_slot_ms IS NOT NULL
AND state = "DONE"
ORDER BY
startTime DESC),
jobsDeduplicated AS (
SELECT
* EXCEPT(_rnk)
FROM
src
WHERE
_rnk = 1 )
SELECT
*
FROM
jobsDeduplicated;
END