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_FOLDER에는 상위 폴더에 bigquery.jobs.listAll이 필요하며 폴더의 Folder Admin 및 BigQuery Admin에 사용할 수 있습니다.
  • JOBS_BY_ORGANIZATION에는 조직에 대한 bigquery.jobs.listAll이 필요하며 Organization bigquery.resourceAdmin, Organization Owner 및 Organization Admin 역할에 사용할 수 있습니다. JOBS_BY_ORGANIZATION은 정의된 Google Cloud 조직을 이용하는 사용자에게만 제공됩니다.

세분화된 BigQuery 권한에 대한 자세한 내용은 역할 및 권한을 참조하세요.

스키마

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

  • INFORMATION_SCHEMA.JOBS_BY_USER는 현재 프로젝트에서 현재 사용자가 제출하는 작업만 반환합니다.
  • INFORMATION_SCHEMA.JOBS_BY_PROJECT는 현재 프로젝트에서 제출하는 모든 작업을 반환합니다.
  • INFORMATION_SCHEMA.JOBS_BY_FOLDER는 현재 프로젝트의 상위 폴더에 제출된 모든 작업을 반환합니다. 여기에는 상위 폴더 아래의 하위 폴더에 있는 작업도 포함됩니다.
  • INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION은 현재 프로젝트에 연결된 조직에서 제출하는 모든 작업을 반환합니다.

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

열 이름 데이터 유형 값
creation_time TIMESTAMP (파티션 나누기 열) 이 작업의 생성 시간 파티션 나누기는 이 타임스탬프의 UTC 시간을 기반으로 합니다.
project_id STRING (클러스터링 열) 프로젝트의 ID
project_number INTEGER 프로젝트의 번호
folder_numbers RECORD 프로젝트에 가장 가까운 리프 폴더부터 시작하여 프로젝트의 상위 항목에 있는 폴더의 Google 계정 및 ID 관리(GAIA) ID입니다. 이 열은 JOBS_BY_FOLDER에만 채워집니다.
user_email STRING (클러스터링 열) 작업을 실행한 사용자의 이메일 주소 또는 서비스 계정
job_id STRING 작업의 ID. 예를 들면 bquxjob_1234입니다.
job_type STRING 작업의 유형. QUERY, LOAD, EXTRACT, COPY, null일 수 있습니다. 작업 유형 null은 스크립트 작업 문 평가 또는 구체화된 뷰 새로고침과 같은 내부 작업을 나타냅니다.
statement_type STRING 유효한 경우 쿼리 문의 유형. 예시: SELECT, INSERT, UPDATE, DELETE.
priority STRING 이 작업의 우선순위
start_time TIMESTAMP 이 작업의 시작 시간
end_time TIMESTAMP 이 작업의 종료 시간
query STRING SQL 쿼리 텍스트. 참고: JOBS_BY_PROJECT 뷰에만 query 열이 있습니다.
state STRING 작업의 실행 상태. 유효한 상태에는 PENDING, RUNNING, DONE이 있습니다.
reservation_id STRING 해당되는 경우 이 작업에 할당된 기본 예약의 이름 예약에 할당된 프로젝트에서 작업을 실행한 경우 reservation-admin-project:reservation-location.reservation-name 형식을 따릅니다.
total_bytes_processed INTEGER 작업에서 처리한 총 바이트
total_slot_ms INTEGER 전체 기간 동안 작업의 슬롯 밀리초
error_result RECORD ErrorProto로서 오류 세부정보(해당되는 경우)
cache_hit BOOLEAN 이 작업의 쿼리 결과가 캐시에서 제공되었는지 여부
destination_table RECORD 결과에 대한 대상 테이블(해당하는 경우)
referenced_tables RECORD 작업에서 참조하는 테이블의 배열
labels RECORD key, value 문자열로서 작업에 적용된 라벨의 배열
timeline RECORD 작업의 쿼리 타임라인. 쿼리 실행 스냅샷을 포함합니다.
job_stages RECORD 작업의 쿼리 단계
total_bytes_billed INTEGER 프로젝트가 주문형 가격 책정을 사용하도록 구성된 경우 이 필드에는 작업에 대해 청구되는 총 바이트가 포함됩니다. 프로젝트가 정액제를 사용하도록 구성된 경우 바이트 요금이 청구되지 않으며 이 필드는 참고용이 됩니다.

데이터 보관

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

리전성

BigQuery의 INFORMATION_SCHEMA 작업 보기는 리전화됩니다. 이러한 뷰를 쿼리하려면 리전 한정자를 사용해야 합니다.

예시

예시 1: 평균 슬롯 사용량

다음 예시에서는 지정된 프로젝트의 지난 7일 동안의 모든 쿼리에 대한 평균 슬롯 사용량을 계산합니다. 이 계산은 일주일 동안 일관된 슬롯 사용량을 가진 프로젝트에서 가장 정확합니다. 프로젝트의 슬롯 사용량이 일정하지 않으면 이 사용량 수치가 예상보다 낮을 수 있습니다.

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

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

Console

  1. Cloud Console에서 BigQuery 페이지를 엽니다.

    BigQuery 페이지로 이동

  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
 -- 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 end_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) AND CURRENT_TIMESTAMP()

  3. 실행을 클릭합니다.

bq

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
   -- 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  |
  +------------+

특정 예약에 대한 사용량은 WHERE reservation_id = "…"로 확인할 수 있습니다. 이는 일정 기간 동안 예약 사용 비율을 결정하는 데 유용할 수 있습니다. 스크립트 작업의 경우 상위 작업은 하위 작업의 총 슬롯 사용량도 보고합니다. 중복 계산이 방지되도록 WHERE statement_type != "SCRIPT"를 사용하여 상위 작업을 제외합니다.

개별 작업의 평균 슬롯 사용량을 확인하려면 total_slot_ms / TIMESTAMP_DIFF(end_time,start_time, MILLISECOND)를 사용합니다.

예시 2: 로드 작업 기록

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

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

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

Console

  1. Cloud Console에서 BigQuery 페이지를 엽니다.

    BigQuery 페이지로 이동

  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. 실행을 클릭합니다.

bq

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 페이지를 엽니다.

    BigQuery 페이지로 이동

  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. 실행을 클릭합니다.

bq

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 페이지를 엽니다.

    BigQuery 페이지로 이동

  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. 실행을 클릭합니다.

bq

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 |
  +--------------+--------------+----------------------------------------------+

다음 단계