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 형식을 따릅니다.
이 출력에서 각 항목의 의미는 다음과 같습니다.
|
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 문이 포함된 쿼리인 경우 값은 다음 필드가 있는 레코드입니다.
다른 모든 작업의 경우 값은 이 열은 |
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
: 프로젝트 IDREGION_NAME
: 프로젝트 리전VIEW
: 쿼리를 실행할 뷰 이름
예를 들면 `myproject`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
입니다.
쿼리를 실행하려면 다음 안내를 따르세요.
Console
Cloud Console에서 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 페이지를 엽니다.
쿼리 편집기 상자에 다음과 같은 표준 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"
실행을 클릭합니다.
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
Cloud Console에서 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
실행을 클릭합니다.
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_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 페이지를 엽니다.
쿼리 편집기 상자에 다음 표준 SQL 쿼리를 입력합니다.
INFORMATION_SCHEMA
에는 표준 SQL 구문이 필요합니다. 표준 SQL은 Cloud Console의 기본 구문입니다.SELECT job_id, creation_time, query FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_USER WHERE state != "DONE"
실행을 클릭합니다.
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 | +--------------+--------------+----------------------------------------------+
다음 단계
- BigQuery
INFORMATION_SCHEMA
소개에서INFORMATION_SCHEMA
의 개요 참조 INFORMATION_SCHEMA
를 사용하여 예약 메타데이터를 가져오는 방법 알아보기INFORMATION_SCHEMA
를 사용하여 스트리밍 메타데이터를 가져오는 방법 알아보기INFORMATION_SCHEMA
를 사용하여 데이터 세트 메타데이터를 가져오는 방법 알아보기INFORMATION_SCHEMA
를 사용하여 테이블 메타데이터를 가져오는 방법을 알아보세요.INFORMATION_SCHEMA
를 사용하여 뷰 메타데이터를 가져오는 방법을 알아보세요.