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 AdminBigQuery Admin에 사용할 수 있습니다.
  • JOBS_BY_ORGANIZATION에는 조직에 대한 bigquery.jobs.listAll이 필요하며 Organization bigquery.resourceAdmin, Organization OwnerOrganization 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 REPEATED INTEGER 프로젝트에 가장 가까운 리프 폴더부터 시작하여 프로젝트의 상위 항목에 있는 폴더의 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, SCRIPT입니다. 유효한 값 목록은 QueryStatementType을 참조하세요.
priority STRING 이 작업의 우선순위 유효한 값은 INTERACTIVEBATCH입니다.
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 형식을 따릅니다.

이 출력에서 각 항목의 의미는 다음과 같습니다.

  • RESERVATION_ADMIN_PROJECT: 예약을 관리하는 Google Cloud 프로젝트의 이름
  • 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 프로젝트가 주문형 가격 책정을 사용하도록 구성된 경우 이 필드에는 작업에 대해 청구되는 총 바이트가 포함됩니다. 프로젝트가 정액제를 사용하도록 구성된 경우 바이트 요금이 청구되지 않으며 이 필드는 참고용이 됩니다.
parent_job_id STRING 상위 작업의 ID입니다(있는 경우).
transaction_id STRING 이 작업이 실행된 트랜잭션의 ID입니다(있는 경우). (미리보기)
dml_statistics RECORD

작업이 DML 문이 포함된 쿼리인 경우 값은 다음 필드가 있는 레코드입니다.

  • inserted_row_count: 삽입된 행의 수
  • deleted_row_count: 삭제된 행의 수
  • updated_row_count: 업데이트된 행의 수

다른 모든 작업의 경우 값은 NULL입니다.

이 열은 INFORMATION_SCHEMA.JOBS_BY_USERINFORMATION_SCHEMA.JOBS_BY_PROJECT 뷰에 있습니다.

데이터 보관

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

발언 인용

쿼리 작업의 요약 비용을 찾기 위해 INFORMATION_SCHEMA.JOBS_BY_*를 쿼리할 때는 SCRIPT 문 유형을 제외합니다. 그렇지 않으면 일부 값이 두 번 계산됩니다. SCRIPT 행에는 이 작업의 일부로 실행된 모든 하위 작업의 요약 값이 포함됩니다.

리전성

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

예시

예시 1: 평균 슬롯 사용량

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

기본 프로젝트가 아닌 프로젝트에 대해 쿼리를 실행하려면 다음 형식으로 프로젝트 ID를 추가합니다.

`PROJECT_ID`.`region-REGION_NAME`.INFORMATION_SCHEMA.VIEW
다음을 바꿉니다.

  • PROJECT_ID: 프로젝트 ID
  • REGION_NAME: 프로젝트 리전
  • VIEW: 쿼리를 실행할 뷰 이름

예: `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일).

기본 프로젝트가 아닌 프로젝트에 대해 쿼리를 실행하려면 다음 형식으로 프로젝트 ID를 추가합니다.

`PROJECT_ID`.`region-REGION_NAME`.INFORMATION_SCHEMA.VIEW
예: `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을 추가로 필터링할 수 있습니다.

기본 프로젝트가 아닌 프로젝트에 대해 쿼리를 실행하려면 다음 형식으로 프로젝트 ID를 추가합니다.

`PROJECT_ID`.`region-REGION_NAME`.INFORMATION_SCHEMA.VIEW
예: `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: 대기 중인 작업 및 실행 중인 작업

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

기본 프로젝트가 아닌 프로젝트에 대해 쿼리를 실행하려면 다음 형식으로 프로젝트 ID를 추가합니다.

`PROJECT_ID`.`region-REGION_NAME`.INFORMATION_SCHEMA.VIEW
예: `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 |
  +--------------+--------------+----------------------------------------------+
  

다음 단계