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

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

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

시작하기 전에

사용자에게 이 문서의 각 작업을 수행하는 데 필요한 권한을 부여하는 Identity and Access Management(IAM) 역할을 부여합니다.

필수 권한

INFORMATION_SCHEMA 테이블을 사용하여 작업 메타데이터를 검색하려면 다음 IAM 권한이 필요합니다.

JOBS_BY_USER에는 다음이 필요합니다.
- 프로젝트에 대한 bigquery.jobs.list 권한
다음과 같이 사전 정의된 각 IAM 역할에는 위의 권한이 포함되어 있습니다.
- Project Viewer
- BigQuery User
JOBS_BY_PROJECT에는 다음이 필요합니다.
- 프로젝트에 대한 bigquery.jobs.listAll 권한
다음과 같이 사전 정의된 각 IAM 역할에는 위의 권한이 포함되어 있습니다.
- Project Owner
- BigQuery Admin
JOBS_BY_FOLDER에는 다음이 필요합니다.
- 상위 폴더의 bigquery.jobs.listAll
다음과 같이 사전 정의된 각 IAM 역할에는 위의 권한이 포함되어 있습니다.
- Folder Admin
- BigQuery Admin
JOBS_BY_ORGANIZATION에는 다음이 필요합니다.
- 조직에 대한 bigquery.jobs.listAll 권한
다음과 같이 사전 정의된 각 IAM 역할에는 위의 권한이 포함되어 있습니다.
- 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`	`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`	이 작업의 우선순위 유효한 값은 `INTERACTIVE` 및 `BATCH`입니다.
`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입니다(있는 경우). (미리보기)
`session_info`	`RECORD`	이 작업이 실행된 세션에 대한 세부정보입니다(있는 경우). (미리보기)
`dml_statistics`	`RECORD`	작업이 DML 문이 포함된 쿼리인 경우 값은 다음 필드가 있는 레코드입니다. `inserted_row_count`: 삽입된 행의 수 `deleted_row_count`: 삭제된 행의 수 `updated_row_count`: 업데이트된 행의 수 다른 모든 작업의 경우 값은 `NULL`입니다. 이 열은 `INFORMATION_SCHEMA.JOBS_BY_USER` 및 `INFORMATION_SCHEMA.JOBS_BY_PROJECT` 뷰에 있습니다.
`bi_engine_statistics`	`RECORD`	프로젝트가 BI Engine SQL 인터페이스를 사용하도록 구성된 경우 이 필드에는 BiEngineStatistics가 포함됩니다. 그 외의 경우에는 `NULL`입니다.
`total_modified_partitions`	`INTEGER`	작업에서 수정한 총 파티션 수입니다. 이 필드는 `LOAD` 및 `QUERY` 작업에 채워집니다.

데이터 보관

지난 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

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

BigQuery 페이지로 이동

쿼리 편집기 상자에 다음 표준 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()

실행을 클릭합니다.

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

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

BigQuery 페이지로 이동
쿼리 편집기 상자에 다음과 같은 표준 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"
```
참고: INFORMATION_SCHEMA 뷰 이름은 대소문자를 구분합니다.
실행을 클릭합니다.

bq

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

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

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

BigQuery 페이지로 이동
쿼리 편집기 상자에 다음 표준 SQL 쿼리를 입력합니다. INFORMATION_SCHEMA에는 표준 SQL 구문이 필요합니다. 표준 SQL은 Cloud Console의 기본 구문입니다.
```
SELECT
 job_id,
 user_email,
 total_bytes_billed
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
WHERE EXTRACT(DATE FROM  creation_time) = current_date()
ORDER BY total_bytes_billed DESC
LIMIT 5
```
참고: INFORMATION_SCHEMA 뷰 이름은 대소문자를 구분합니다.
실행을 클릭합니다.

bq

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

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

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

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

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

BigQuery 페이지로 이동
쿼리 편집기 상자에 다음 표준 SQL 쿼리를 입력합니다. INFORMATION_SCHEMA에는 표준 SQL 구문이 필요합니다. 표준 SQL은 Cloud Console의 기본 구문입니다.
```
SELECT
 job_id,
 creation_time,
 query
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_USER
WHERE state != "DONE"
```
참고: INFORMATION_SCHEMA 뷰 이름은 대소문자를 구분합니다.
실행을 클릭합니다.

bq

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

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

다음 단계

BigQuery INFORMATION_SCHEMA 소개에서 INFORMATION_SCHEMA의 개요 참조
INFORMATION_SCHEMA를 사용하여 예약 메타데이터를 가져오는 방법 알아보기
INFORMATION_SCHEMA를 사용하여 스트리밍 메타데이터를 가져오는 방법 알아보기
INFORMATION_SCHEMA를 사용하여 데이터 세트 메타데이터를 가져오는 방법 알아보기
INFORMATION_SCHEMA를 사용하여 테이블 메타데이터를 가져오는 방법을 알아보세요.
INFORMATION_SCHEMA를 사용하여 뷰 메타데이터를 가져오는 방법을 알아보세요.