「工作」檢視畫面

`PROJECT_ID`.`region-REGION_NAME`.INFORMATION_SCHEMA.JOBS

CREATE TEMP FUNCTION isBillable(error_result ANY TYPE)
AS (
  -- You aren't charged for queries that return an error.
  error_result IS NULL
  -- However, canceling a running query might incur charges.
  OR error_result.reason = 'stopped'
);

-- BigQuery hides the number of bytes billed on all queries against tables with
-- row-level security.
CREATE TEMP FUNCTION isMaybeUsingRowLevelSecurity(
  job_type STRING, tib_billed FLOAT64, error_result ANY TYPE)
AS (
  job_type = 'QUERY'
  AND tib_billed IS NULL
  AND isBillable(error_result)
);

WITH
  query_params AS (
    SELECT
      date 'START_DATE' AS start_date,  -- inclusive
      date 'END_DATE' AS end_date,  -- inclusive
  ),
  usage_with_multiplier AS (
    SELECT
      job_type,
      error_result,
      creation_time,
      -- Jobs are billed by end_time in PST8PDT timezone, regardless of where
      -- the job ran.
      EXTRACT(date FROM end_time AT TIME ZONE 'PST8PDT') billing_date,
      total_bytes_billed / 1024 / 1024 / 1024 / 1024 total_tib_billed,
      CASE statement_type
        WHEN 'SCRIPT' THEN 0
        WHEN 'CREATE_MODEL' THEN 50 * PRICE_PER_TIB
        ELSE PRICE_PER_TIB
        END AS multiplier,
    FROM `region-REGION_NAME`.INFORMATION_SCHEMA.JOBS
  )
SELECT
  billing_date,
  sum(total_tib_billed * multiplier) estimated_charge,
  sum(total_tib_billed) estimated_usage_in_tib,
  countif(isMaybeUsingRowLevelSecurity(job_type, total_tib_billed, error_result))
    AS jobs_using_row_level_security,
FROM usage_with_multiplier, query_params
WHERE
  1 = 1
  -- Filter by creation_time for partition pruning.
  AND date(creation_time) BETWEEN date_sub(start_date, INTERVAL 2 day) AND end_date
  AND billing_date BETWEEN start_date AND end_date
  AND isBillable(error_result)
GROUP BY billing_date
ORDER BY billing_date;

SELECT
  SUM(total_slot_ms) / (1000 * 60 * 60 * 24 * 7) AS avg_slots
FROM
  `region-REGION_NAME`.INFORMATION_SCHEMA.JOBS
WHERE
  -- Filter by the partition column first to limit the amount of data scanned.
  -- Eight days allows for jobs created before the 7 day end_time filter.
  creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 8 DAY) AND CURRENT_TIMESTAMP()
  AND job_type = 'QUERY'
  AND statement_type != 'SCRIPT'
  AND end_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) AND CURRENT_TIMESTAMP();

+------------+
| avg_slots  |
+------------+
| 3879.1534  |
+------------+

SELECT
  priority,
  COUNT(*) active_jobs
FROM
  `region-REGION_NAME`.INFORMATION_SCHEMA.JOBS
WHERE
  creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 hour)
  AND job_type = 'QUERY'
GROUP BY priority;

+-------------+-------------+
| priority    | active_jobs |
+-------------+-------------+
| INTERACTIVE |           2 |
| BATCH       |           3 |
+-------------+-------------+

SELECT
  user_email AS user,
  COUNT(*) num_jobs
FROM
  `region-REGION_NAME`.INFORMATION_SCHEMA.JOBS
WHERE
  job_type = 'LOAD'
GROUP BY
  user_email;

+--------------+
| user         |
+--------------+
| abc@xyz.com  |
| xyz@xyz.com  |
| bob@xyz.com  |
+--------------+

SELECT
    DATE(creation_time) as day,
    destination_table.project_id as project_id,
    destination_table.dataset_id as dataset_id,
    destination_table.table_id as table_id,
    COUNT(job_id) AS load_job_count
 FROM
   `region-REGION_NAME`.INFORMATION_SCHEMA.JOBS
 WHERE
    creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 8 DAY) AND CURRENT_TIMESTAMP()
    AND job_type = "LOAD"
GROUP BY
    day,
    project_id,
    dataset_id,
    table_id
ORDER BY
    day DESC;

+-------------+------------+-------------+----------+-----------------+
|day          | project_id | dataset_id  | table_id | load_job_count  |
+-------------+------------+-------------+----------+-----------------+
| 2020-10-10  | my_project | dataset1    | orders   | 58              |
| 2020-10-10  | my_project | dataset1    | product  | 20              |
| 2020-10-10  | my_project | dataset1    | sales    | 11              |
+-------------+------------+-------------+----------+-----------------+

SELECT
   job_id,
  creation_time,
  user_email,
   error_result
 FROM `region-REGION_NAME`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  error_result.reason != "Null"
ORDER BY
  creation_time DESC
LIMIT 3;

+------------+--------------------------+------------------+-------------------------------------+
| job_id     | creation_time            | user_email       | error_result                        |
+------------+--------------------------+------------------+-------------------------------------+
| bquxjob_1  | 2020-10-10 00:00:00 UTC  | abc@example.com  | Column 'col1' has mismatched type...|
| bquxjob_2  | 2020-10-11 00:00:00 UTC  | xyz@example.com  | Column 'col1' has mismatched type...|
| bquxjob_3  | 2020-10-11 00:00:00 UTC  | bob@example.com  | Column 'col1' has mismatched type...|
+------------+--------------------------+------------------+-------------------------------------+

SELECT
  job_id,
  job_type,
  state,
  creation_time,
  start_time,
  user_email
 FROM `region-REGION_NAME`.INFORMATION_SCHEMA.JOBS
 WHERE
  state!="DONE" AND
  creation_time <= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 MINUTE)
ORDER BY
  creation_time ASC;

+--------+----------+---------+--------------------------------+--------------------------------+------------------+
| job_id | job_type | state   | creation_time                  | start_time                     | user_email       |
+--------+----------+---------+--------------------------------+--------------------------------+------------------+
| bquxjob_1  | QUERY    | RUNNING | 2023-05-03 05:07:22.818000 UTC | 2023-05-03 05:07:22.905000 UTC | abc@example.com  |
| bquxjob_2  | QUERY    | PENDING | 2023-05-01 02:05:47.925000 UTC | 2023-05-01 02:05:47.998000 UTC | xyz@example.com  |
| bquxjob_3  | QUERY    | PENDING | 2023-05-01 02:05:47.925000 UTC | 2023-05-01 02:05:47.998000 UTC | abc@example.com  |
+--------+----------+---------+--------------------------------+--------------------------------+------------------+

SELECT
 job_id,
FROM
 `region-REGION_NAME`.INFORMATION_SCHEMA.JOBS
WHERE
 TIMESTAMP_TRUNC(creation_time, DAY) = '2024-06-12'
 AND job_creation_reason.code IS NULL
LIMIT 3;

+-----------+
| job_id    |                                          |
+-----------+
| bquxjob_1 |
| bquxjob_2 |
| bquxjob_3 |
+-----------+

SELECT
 job_id,
 statement_type,
 priority,
 cache_hit,
 job_creation_reason.code AS job_creation_reason_code,
 total_bytes_billed,
 total_bytes_processed,
 total_slot_ms,
 state,
 error_result.message AS error_result_message,
FROM
 `region-REGION_NAME`.INFORMATION_SCHEMA.JOBS
WHERE
 TIMESTAMP_TRUNC(creation_time, DAY) = '2024-06-12'
 AND job_id = '2Lm09bHxDEsoVK8zwzWJomLHU_Ud%1910479b151' -- queryId

+-----------+----------------+-------------+-----------+--------------------------+--------------------+---------------------+---------------+-------+----------------------+
| job_id    | statement_type | priority    | cache_hit | job_creation_reason_code | total_bytes_billed | total_bytes_processed | total_slot_ms | state | error_result_message |
+-----------+----------------+-------------+-----------+--------------------------+--------------------+---------------------+---------------+-------+----------------------+
| bquxjob_1 | SELECT         | INTERACTIVE | false     | null                     | 161480704          | 161164718             | 3106          | DONE  | null                 |
+-----------+----------------+-------------+-----------+--------------------------+--------------------+---------------------+---------------+-------+----------------------+

SELECT
 job_id,
 job_creation_reason.code AS job_creation_reason_code
FROM
 `region-REGION_NAME`.INFORMATION_SCHEMA.JOBS
WHERE
 TIMESTAMP_TRUNC(creation_time, DAY) = '2024-06-12'
 AND job_creation_reason.code IS NOT NULL
 AND job_creation_reason.code != 'REQUESTED'
LIMIT 3

+-----------+--------------------------+
| job_id    | job_creation_reason_code |
+-----------+--------------------------+
| bquxjob_1 | LARGE_RESULTS            |
| bquxjob_2 | LARGE_RESULTS            |
| bquxjob_3 | LARGE_RESULTS            |
+-----------+--------------------------+

SELECT
  user_email,
  SUM(total_bytes_billed) AS bytes_billed
FROM
  `region-REGION_NAME`.INFORMATION_SCHEMA.JOBS
WHERE
  job_type = 'QUERY'
  AND statement_type != 'SCRIPT'
GROUP BY
  user_email;

+---------------------+--------------+
| user_email          | bytes_billed |
+---------------------+--------------+
| bob@example.com     | 2847932416   |
| alice@example.com   | 1184890880   |
| charles@example.com | 10485760     |
+---------------------+--------------+

SELECT
  TIMESTAMP_TRUNC(end_time, HOUR) AS time_window,
  SUM(total_bytes_billed) AS bytes_billed
FROM
  `region-REGION_NAME`.INFORMATION_SCHEMA.JOBS
WHERE
  job_type = 'QUERY'
  AND statement_type != 'SCRIPT'
GROUP BY
  time_window
ORDER BY
  time_window DESC;

+-------------------------+--------------+
| time_window             | bytes_billed |
+-------------------------+--------------+
| 2022-05-17 20:00:00 UTC | 1967128576   |
| 2022-05-10 21:00:00 UTC | 0            |
| 2022-04-15 17:00:00 UTC | 41943040     |
+-------------------------+--------------+

SELECT
  t.project_id,
  t.dataset_id,
  t.table_id,
  COUNT(*) AS num_references
FROM
  `region-REGION_NAME`.INFORMATION_SCHEMA.JOBS, UNNEST(referenced_tables) AS t
GROUP BY
  t.project_id,
  t.dataset_id,
  t.table_id
ORDER BY
  num_references DESC;

+------------+------------+----------+----------------+
| project_id | dataset_id | table_id | num_references |
+------------+------------+----------+----------------+
| my_project | dataset1   | orders   | 58             |
| my_project | dataset1   | products | 40             |
| other_proj | dataset1   | accounts | 12             |
+------------+------------+----------+----------------+

SELECT
  project_id,
  -- Implicitly defaulted to LegacySQL since the query dialect was not specified
  -- in the request.
  COUNTIF(query_dialect = 'DEFAULT_LEGACY_SQL') AS default_legacysql_query_jobs,
  -- Explicitly requested LegacySQL.
  COUNTIF(query_dialect = 'LEGACY_SQL') AS legacysql_query_jobs,
FROM
  `region-REGION_NAME`.INFORMATION_SCHEMA.JOBS
WHERE
  query_dialect = 'DEFAULT_LEGACY_SQL'
  OR query_dialect = 'LEGACY_SQL'
GROUP BY
  project_id
ORDER BY
  default_legacysql_query_jobs DESC,
  legacysql_query_jobs DESC;

SELECT
  destination_table.table_id,
  SUM(total_modified_partitions) AS total_modified_partitions
FROM
  `region-REGION_NAME`.INFORMATION_SCHEMA.JOBS
WHERE
  DATE(creation_time, "America/Los_Angeles") = CURRENT_DATE()
GROUP BY
  table_id
ORDER BY
  total_modified_partitions DESC

SELECT ROUND(SAFE_DIVIDE(total_slot_ms,TIMESTAMP_DIFF(end_time, start_time, MILLISECOND)), 1) as avg_slots_per_ms
FROM `region-REGION_NAME`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE job_id = 'JOB_ID'

+------------------+
| avg_slots_per_ms |
+------------------+
|             17.0 |
+------------------+

SELECT
 job_id,
 query,
 user_email,
 total_slot_ms
FROM `my_project`.`region-REGION_NAME`.INFORMATION_SCHEMA.JOBS
WHERE EXTRACT(DATE FROM  creation_time) = current_date()
ORDER BY total_slot_ms DESC
LIMIT 3

SELECT
 job_id,
 query,
 user_email,
 total_bytes_processed
FROM `my_project`.`region-REGION_NAME`.INFORMATION_SCHEMA.JOBS
WHERE EXTRACT(DATE FROM  creation_time) = current_date()
ORDER BY total_bytes_processed DESC
LIMIT 3

+-----------+---------------------------------+-----------------------+---------------+
| job_id    | query                           | user_email            | total_slot_ms |
+-----------+---------------------------------+--------------------------+------------+
| bquxjob_1 | SELECT ... FROM dataset.table1  | bob@example.com       | 80,000        |
| bquxjob_2 | SELECT ... FROM dataset.table2  | alice@example.com     | 78,000        |
| bquxjob_3 | SELECT ... FROM dataset.table3  | charles@example.com   | 75,000        |
+-----------+---------------------------------+-----------------------+---------------+

SELECT
  query,
  query_info.resource_warning
FROM
  `region-REGION_NAME`.INFORMATION_SCHEMA.JOBS
WHERE
 creation_time BETWEEN TIMESTAMP("2022-12-01")
 AND TIMESTAMP("2022-12-08")
 AND query_info.resource_warning IS NOT NULL
LIMIT 3;

WITH resource_warnings AS (
  SELECT
    EXTRACT(DATE FROM creation_time) AS creation_date
  FROM
    `region-REGION_NAME`.INFORMATION_SCHEMA.JOBS
  WHERE
    creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 14 DAY)
    AND query_info.resource_warning IS NOT NULL
)
SELECT
  creation_date,
  COUNT(1) AS warning_counts
FROM
  resource_warnings
GROUP BY creation_date
ORDER BY creation_date DESC;

SELECT
  project_id,
  job_id,
  reservation_id,
  EXTRACT(DATE FROM creation_time) AS creation_date,
  TIMESTAMP_DIFF(end_time, start_time, SECOND) AS job_duration_seconds,
  job_type,
  user_email,
  total_bytes_billed,

  -- Average slot utilization per job is calculated by dividing total_slot_ms by the millisecond duration of the job

  SAFE_DIVIDE(job.total_slot_ms,(TIMESTAMP_DIFF(job.end_time, job.start_time, MILLISECOND))) AS job_avg_slots,
  query,

  -- Determine the max number of slots used at ANY stage in the query.
  -- The average slots might be 55. But a single stage might spike to 2000 slots.
  -- This is important to know when estimating number of slots to purchase.

  MAX(SAFE_DIVIDE(unnest_job_stages.slot_ms,unnest_job_stages.end_ms - unnest_job_stages.start_ms)) AS jobstage_max_slots,

  -- Check if there's a job that requests more units of works (slots). If so you need more slots.
  -- estimated_runnable_units = Units of work that can be scheduled immediately.
  -- Providing additional slots for these units of work accelerates the query,
  -- if no other query in the reservation needs additional slots.

  MAX(unnest_timeline.estimated_runnable_units) AS estimated_runnable_units
FROM `region-REGION_NAME`.INFORMATION_SCHEMA.JOBS AS job
  CROSS JOIN UNNEST(job_stages) as unnest_job_stages
  CROSS JOIN UNNEST(timeline) AS unnest_timeline
WHERE project_id = 'my_project'
  AND statement_type != 'SCRIPT'
  AND DATE(creation_time) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AND CURRENT_DATE()
GROUP BY 1,2,3,4,5,6,7,8,9,10
ORDER BY job_id;

+-----------+-----------+----------------+---------------+----------------------+----------+-----------------+--------------------+--------------+--------------------------------+--------------------+--------------------------+
|project_id | job_id    | reservation_id | creation_date | job_duration_seconds | job_type | user_email      | total_bytes_billed | job_avg_slots| query                          | jobstage_max_slots | estimated_runnable_units |
+-----------+-----------+----------------+---------------+----------------------+----------+-----------------+--------------------+--------------+--------------------------------+--------------------+--------------------------+
| project1  | bquxjob1  | reservation1   | 2020-10-10    | 160                  | LOAD     | abc@example.com | 161480704          | 2890         | SELECT ... FROM dataset.table1 | 2779.1534          | 8293                     |
| project1  | bquxjob2  | reservation2   | 2020-12-10    | 120                  | LOAD     | abc@example.com | 161480704          | 2890         | SELECT ... FROM dataset.table1 | 2779.1534          | 8768                     |
| project1  | bquxjob3  | reservation1   | 2020-12-10    | 120                  | LOAD     | abc@example.com | 161480704          | 2890         | SELECT ... FROM dataset.table1 | 1279.1534          | 8738                     |
+-----------+-----------+----------------+---------------+----------------------+----------+-----------------+--------------------+--------------+--------------------------------+--------------------+--------------------------+

SELECT
  `bigquery-public-data`.persistent_udfs.job_url(
    project_id || ':us.' || job_id) AS job_url,
  query_info.performance_insights
FROM
  `region-REGION_NAME`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  DATE(creation_time) >= CURRENT_DATE - 30 -- scan 30 days of query history
  AND job_type = 'QUERY'
  AND state = 'DONE'
  AND error_result IS NULL
  AND statement_type != 'SCRIPT'
  AND EXISTS ( -- Only include queries which had performance insights
    SELECT 1
    FROM UNNEST(
      query_info.performance_insights.stage_performance_standalone_insights
    )
    WHERE slot_contention OR insufficient_shuffle_quota
    UNION ALL
    SELECT 1
    FROM UNNEST(
      query_info.performance_insights.stage_performance_change_insights
    )
    WHERE input_data_change.records_read_diff_percentage IS NOT NULL
  );

SELECT
 *
FROM
 `region-REGION_NAME`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
 job_id LIKE '%metadata_cache_refresh%'
 AND creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 6 HOUR)
ORDER BY start_time desc
LIMIT 10;

DECLARE querytext STRING DEFAULT(
  SELECT query
  FROM `region-REGION_NAME`.INFORMATION_SCHEMA.JOBS
  WHERE job_id = 'JOB_ID'
  LIMIT 1
);

SELECT
  start_time,
  end_time,
  project_id,
  job_id,
  TIMESTAMP_DIFF(end_time, start_time, SECOND) AS run_secs,
  total_bytes_processed / POW(1024, 3) AS total_gigabytes_processed,
  query
FROM
  `region-REGION_NAME`.INFORMATION_SCHEMA.JOBS
WHERE
  query = querytext
  AND total_bytes_processed > 0
  AND creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
ORDER BY 5 DESC
LIMIT 3;

SELECT
  job_id,
  user_email,
  total_bytes_billed
FROM
  `region-REGION_NAME`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
WHERE
  EXTRACT(DATE FROM  creation_time) = current_date()
ORDER BY
  total_bytes_billed DESC
LIMIT 3;

+--------------+--------------+---------------------------+
| job_id       |  user_email  |  total_bytes_billed       |
+--------------+--------------+---------------------------+
| bquxjob_1    |  abc@xyz.com |    999999                 |
| bquxjob_2    |  def@xyz.com |    888888                 |
| bquxjob_3    |  ghi@xyz.com |    777777                 |
+--------------+--------------+---------------------------+

SELECT
  job_id,
  creation_time,
  query
FROM
  `region-REGION_NAME`.INFORMATION_SCHEMA.JOBS_BY_USER
WHERE
  state != 'DONE';

+--------------+---------------------------+---------------------------------+
| job_id       |  creation_time            |  query                          |
+--------------+---------------------------+---------------------------------+
| bquxjob_1    |  2019-10-10 00:00:00 UTC  |  SELECT ... FROM dataset.table1 |
| bquxjob_2    |  2019-10-10 00:00:01 UTC  |  SELECT ... FROM dataset.table2 |
| bquxjob_3    |  2019-10-10 00:00:02 UTC  |  SELECT ... FROM dataset.table3 |
+--------------+---------------------------+---------------------------------+

SELECT
    DATE(creation_time) as day,
    project_id as source_project_id,
    SUM(total_bytes_processed) AS total_bytes_processed
 FROM
   `region-REGION_NAME`.INFORMATION_SCHEMA.JOBS
 WHERE
    creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 8 DAY) AND CURRENT_TIMESTAMP()
    AND job_type = "EXTRACT"
GROUP BY
    day,
    source_project_id
ORDER BY
    day DESC;

SELECT
    DATE(creation_time) as day,
    project_id as source_project_id,
CONCAT(destination_table.project_id,":",destination_table.dataset_id,".",destination_table.table_id) as destination_table,
    COUNT(job_id) AS copy_job_count
 FROM
   `region-REGION_NAME`.INFORMATION_SCHEMA.JOBS
 WHERE
    creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 8 DAY) AND CURRENT_TIMESTAMP()
    AND job_type = "COPY"
GROUP BY
    day,
    source_project_id,
    destination_table
ORDER BY
    day DESC;

SELECT
    job_id, reservation_id, edition,
    total_slot_ms, total_bytes_processed, state
FROM `region-REGION_NAME`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 6 HOUR)
    AND user_email = "bigquery-adminbot@system.gserviceaccount.com"
    AND query LIKE "CALL BQ.OPTIMIZE_STORAGE(%)";

SELECT
   job_id,
   user_email,
   start_time,
   end_time,
   TIMESTAMP_DIFF(end_time, start_time, SECOND) AS duration_seconds,
   total_bytes_processed,
   reservation_id,
   CASE
     WHEN reservation_id IS NULL THEN 'PAYG (On-demand)'
     WHEN reservation_id != '' THEN 'Reservation'
     ELSE 'Unknown'
   END AS compute_type,
   query
 FROM
   `region-REGION_NAME`.INFORMATION_SCHEMA.JOBS
 WHERE
   job_type = 'QUERY'
   AND end_time IS NOT NULL
   -- Filter for queries containing the specified pattern (case-insensitive)
   AND REGEXP_CONTAINS(LOWER(query), r"export table metadata from")
 ORDER BY
   start_time DESC
 LIMIT 3;