INFORMATION_SCHEMA를 사용하여 작업 메타데이터 가져오기

INFORMATION_SCHEMA는 데이터세트, 루틴, 테이블, 뷰, 작업, 예약에 대한 메타데이터에 액세스할 수 있는 일련의 뷰입니다.

INFORMATION_SCHEMA.JOBS_BY_* 큐를 쿼리하여 BigQuery 작업에 대한 실시간 메타데이터를 검색할 수 있습니다. 이 뷰에는 현재 실행 중인 작업은 물론 지난 180일 동안의 완료된 작업 기록도 포함되어 있습니다.

필수 권한

INFORMATION_SCHEMA 테이블을 사용하여 작업 메타데이터를 검색하는 데 적절한 범위의 권한이 필요합니다.

  • JOBS_BY_USER에는 프로젝트에 대한 bigquery.jobs.list가 필요하며 Project Viewer, BigQuery User 역할에 사용할 수 있습니다.
  • JOBS_BY_PROJECT에는 프로젝트에 대한 bigquery.jobs.listAll가 필요하며 Project Owner, BigQuery Admin 역할에 사용할 수 있습니다.
  • JOBS_BY_ORGANIZATION에는 조직에 대한 bigquery.jobs.listAll이 필요하며 Organization Owner 및 Organization Admin 역할에 사용할 수 있습니다. JOBS_BY_ORGANIZATION은 정의된 Google Cloud 조직을 이용하는 사용자에게만 제공됩니다.

스키마

INFORMATION_SCHEMA.JOBS_BY_* 뷰를 쿼리하면 각 BigQuery 작업마다 행이 하나씩 쿼리 결과에 포함됩니다.

  • INFORMATION_SCHEMA.JOBS_BY_USER는 현재 프로젝트에서 현재 사용자가 제출하는 작업만 반환합니다.
  • INFORMATION_SCHEMA.JOBS_BY_PROJECT는 현재 프로젝트에서 제출하는 모든 작업을 반환합니다.
  • INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION은 현재 프로젝트에 연결된 조직에서 제출하는 모든 작업을 반환합니다.

INFORMATION_SCHEMA.JOBS_BY_* 뷰에는 다음과 같은 스키마가 있습니다.

열 이름 데이터 유형 값
creation_time TIMESTAMP (파티션 나누기 열)이 작업의 생성 시간
project_id STRING (클러스터링 열) 프로젝트의 ID.
project_number INTEGER 프로젝트의 번호
user_email STRING (클러스터링 열) 작업을 실행한 사용자의 이메일 주소 또는 서비스 계정.
job_id STRING 작업의 ID. 예: `bquxjob_1234`
job_type STRING 작업의 유형. QUERY, LOAD, EXTRACT, COPY, UNKNOWN일 수 있습니다.
statement_type STRING 유효한 경우 쿼리 문의 유형. 예시: SELECT, INSERT, UPDATE, DELETE.
start_time TIMESTAMP 이 작업의 시작 시간
end_time TIMESTAMP 이 작업의 종료 시간
query STRING SQL 쿼리 텍스트. 참고: JOBS_BY_ORGANIZATION 뷰에는 query 열이 없습니다.
state STRING 작업의 실행 상태. 유효한 상태에는 PENDING, RUNNING, DONE이 있습니다.
reservation_id STRING 해당되는 경우 이 작업에 할당된 기본 예약의 이름.
total_bytes_processed INTEGER 작업에서 처리한 총 바이트
total_slot_ms INTEGER 전체 기간 동안 작업의 슬롯 밀리초.
error_result RECORD 오류 세부정보(해당하는 경우).
cache_hit BOOLEAN 쿼리 결과가 캐시되었는지 여부.
destination_table RECORD 결과에 대한 대상 테이블(해당하는 경우).
referenced_tables RECORD 작업에서 참조하는 테이블의 배열.
labels RECORD 작업에 적용된 라벨의 배열.
timeline RECORD 작업의 쿼리 타임라인. 쿼리 실행 스냅샷을 포함합니다.
job_stages RECORD 작업의 쿼리 단계.

데이터 보관

현재 지난 180일 동안의 작업 기록만 INFORMATION_SCHEMA 작업 보기에 보관됩니다.

리전성

BigQuery의 INFORMATION_SCHEMA 작업 보기는 리전화됩니다. 이러한 뷰를 쿼리하려면 `region-region_name`.INFORMATION_SCHEMA.view 형식의 관련 리전 이름 앞에 프리픽스를 추가해야 합니다.

예를 들면 다음과 같습니다.

  • 미국 멀티 리전의 데이터를 쿼리하려면 `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT를 사용합니다.
  • EU 멀티 리전의 데이터를 쿼리하려면 `region-eu`.INFORMATION_SCHEMA.JOBS_BY_PROJECT를 사용합니다.
  • asia-northeast1 리전의 데이터를 쿼리하려면 `region-asia-northeast1`.INFORMATION_SCHEMA.JOBS_BY_PROJECT을 사용합니다.

사용 가능한 리전 목록은 여기에서 확인할 수 있습니다.

예

예시 1: 평균 슬롯 사용량

다음 예시에서는 지정된 프로젝트의 지난 7일 동안의 모든 쿼리에 대한 평균 슬롯 사용량을 계산합니다.

기본 프로젝트가 아닌 프로젝트에 대해 쿼리를 실행하려면 `project_id`.`region-region_name`.INFORMATION_SCHEMA.view 형식으로 프로젝트 ID를 추가합니다(예: `myproject`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT).

쿼리를 실행하려면 다음 안내를 따르세요.

Console

  1. Cloud Console에서 BigQuery 웹 UI를 엽니다.

    Cloud Console로 이동

  2. 쿼리 편집기 상자에 다음 표준 SQL 쿼리를 입력합니다. INFORMATION_SCHEMA에는 표준 SQL 구문이 필요합니다. 표준 SQL은 Cloud Console의 기본 구문입니다.

    SELECT
 SUM(total_slot_ms) / (1000*60*60*24*7) AS avg_slots
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
 job_type = "QUERY"
 AND end_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) AND CURRENT_TIMESTAMP()

  3. 실행을 클릭합니다.

CLI

query 명령어를 사용하고 --nouse_legacy_sql 또는 --use_legacy_sql=false 플래그를 사용하여 표준 SQL 구문을 지정합니다. INFORMATION_SCHEMA 쿼리에는 표준 SQL 구문이 필요합니다.

쿼리를 실행하려면 다음을 입력하세요.

bq query --nouse_legacy_sql \
'SELECT
   SUM(total_slot_ms) / (1000*60*60*24*7) AS avg_slots
 FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
 WHERE
   job_type = "QUERY"
   AND end_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) AND CURRENT_TIMESTAMP()'

결과는 다음과 같이 표시됩니다.

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

특정 예약에 대한 사용량은 WHERE reservation_id = "…"로 확인할 수 있습니다. 이는 일정 기간 동안 예약 사용 비율을 결정하는 데 유용할 수 있습니다.

예시 2: 로드 작업 기록

다음 예시에서는 지정된 프로젝트의 일괄 로드 작업을 제출한 모든 사용자 또는 서비스 계정을 나열합니다. 시간 경계가 지정되지 않았으므로 이 쿼리는 사용 가능한 모든 기록을 검사합니다(예: 지난 30일).

기본 프로젝트가 아닌 프로젝트에 대해 쿼리를 실행하려면 `project_id`.`region-region_name`.INFORMATION_SCHEMA.view 형식으로 프로젝트 ID를 추가합니다(예: `myproject`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT).

쿼리를 실행하려면 다음 안내를 따르세요.

Console

  1. Cloud Console에서 BigQuery 웹 UI를 엽니다.

    Cloud Console로 이동

  2. 쿼리 편집기 상자에 다음과 같은 표준 SQL 쿼리를 입력합니다. INFORMATION_SCHEMA에는 표준 SQL 구문이 필요합니다. 표준 SQL은 Cloud Console의 기본 구문입니다.

    SELECT
 DISTINCT(user_email) AS user
FROM
 `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
 job_type = "LOAD"

  3. 실행을 클릭합니다.

CLI

query 명령어를 사용하고 --nouse_legacy_sql 또는 --use_legacy_sql=false 플래그를 사용하여 표준 SQL 구문을 지정합니다. INFORMATION_SCHEMA 쿼리에는 표준 SQL 구문이 필요합니다.

쿼리를 실행하려면 다음을 입력하세요.

bq query --nouse_legacy_sql \
'SELECT
   DISTINCT(user_email) AS user
 FROM
   `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
 WHERE
   job_type = "LOAD"'

결과는 다음과 같이 표시됩니다.

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

예시 3: 가장 비용이 많이 드는 작업

다음 예시에서는 오늘 날짜의 조직에서 가장 많은 바이트를 검색한 작업 5개를 찾는 방법을 보여줍니다. 로드, 내보내기, 쿼리와 같은 추가 정보를 쿼리하려면 statement_type을 추가로 필터링할 수 있습니다.

기본 프로젝트가 아닌 프로젝트에 대해 쿼리를 실행하려면 `project_id`.`region-region_name`.INFORMATION_SCHEMA.view 형식으로 프로젝트 ID를 추가합니다(예: `myproject`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT).

쿼리를 실행하려면 다음 안내를 따르세요.

Console

  1. Cloud Console에서 BigQuery 웹 UI를 엽니다.

    Cloud Console로 이동

  2. 쿼리 편집기 상자에 다음 표준 SQL 쿼리를 입력합니다. INFORMATION_SCHEMA에는 표준 SQL 구문이 필요합니다. 표준 SQL은 Cloud Console의 기본 구문입니다.

    SELECT
 job_id,
 user_email,
 total_bytes_processed
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
WHERE EXTRACT(DATE FROM  creation_time) = current_date()
ORDER BY total_bytes_processed DESC
LIMIT 5

  3. 실행을 클릭합니다.

CLI

query 명령어를 사용하고 --nouse_legacy_sql 또는 --use_legacy_sql=false 플래그를 사용하여 표준 SQL 구문을 지정합니다. INFORMATION_SCHEMA 쿼리에는 표준 SQL 구문이 필요합니다.

쿼리를 실행하려면 다음을 입력하세요.

bq query --nouse_legacy_sql \
'SELECT
   job_id,
   user_email,
   total_bytes_processed
 FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
 WHERE EXTRACT(DATE FROM  creation_time) = current_date()
 ORDER BY total_bytes_processed DESC
 LIMIT 5'

결과는 다음과 같이 표시됩니다.

  +--------------+--------------+---------------------------+
  | job_id       |  user_email  |  total_bytes_processed    |
  +--------------+--------------+---------------------------+
  | bquxjob_1    |  abc@xyz.com |    999999                 |
  +--------------+--------------+---------------------------+
  | bquxjob_2    |  def@xyz.com |    888888                 |
  +--------------+--------------+---------------------------+
  | bquxjob_3    |  ghi@xyz.com |    777777                 |
  +--------------+--------------+---------------------------+
  | bquxjob_4    |  jkl@xyz.com |    666666                 |
  +--------------+--------------+---------------------------+
  | bquxjob_5    |  mno@xyz.com |    555555                 |
  +--------------+--------------+---------------------------+

예시 4: 대기 중인 작업 및 실행 중인 작업

다음 예시는 현재 사용자가 시작했으며 현재 대기중이거나 실행중 상태인 작업을 나열합니다.

기본 프로젝트가 아닌 프로젝트에 대해 쿼리를 실행하려면 `project_id`.`region-region_name`.INFORMATION_SCHEMA.view 형식으로 프로젝트 ID를 추가합니다(예: `myproject`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT).

쿼리를 실행하려면 다음 안내를 따르세요.

Console

  1. Cloud Console에서 BigQuery 웹 UI를 엽니다.

    Cloud Console로 이동

  2. 쿼리 편집기 상자에 다음 표준 SQL 쿼리를 입력합니다. INFORMATION_SCHEMA에는 표준 SQL 구문이 필요합니다. 표준 SQL은 Cloud Console의 기본 구문입니다.

    SELECT
 job_id,
 creation_time,
 query
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_USER
WHERE state != "DONE"

  3. 실행을 클릭합니다.

CLI

query 명령어를 사용하고 --nouse_legacy_sql 또는 --use_legacy_sql=false 플래그를 사용하여 표준 SQL 구문을 지정합니다. INFORMATION_SCHEMA 쿼리에는 표준 SQL 구문이 필요합니다.

쿼리를 실행하려면 다음을 입력하세요.

bq query --nouse_legacy_sql \
'SELECT
   job_id,
   creation_time,
   query
 FROM `region-us`.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 |
  +--------------+--------------+----------------------------------------------+
  | bquxjob_4    |  2019-10-10 00:00:03 UTC  |  SELECT ... FROM dataset.table4 |
  +--------------+--------------+----------------------------------------------+
  | bquxjob_5    |  2019-10-10 00:00:04 UTC  |  SELECT ... FROM dataset.table5 |
  +--------------+--------------+----------------------------------------------+

다음 단계