빅쿼리 INFORMATION_SCHEMA

 

 

베하 !

문땅훈과 루피입니다😊

 

 

오늘은 '빅쿼리 INFORMATION_SCHEMA'에 대해 알아보겠습니다.

 

 

 

빅쿼리의 INFORMATION_SCHEMA란?

BigQuery는 구글 클라우드 플랫폼에서 제공하는 서버리스 데이터 웨어하우징 솔루션입니다.
INFORMATION_SCHEMA는 빅쿼리에서 제공하는 시스템 카탈로그 시스템으로 데이터베이스, 테이블, 뷰, 함수 등 데이터베이스 메타데이터에 대한 정보를 쿼리할 수 있는 뷰들의 모음입니다.
이 정보는 데이터베이스 관리자나 개발자들에게 데이터베이스 내부 구조와 데이터에 대한 중요한 통찰력을 제공합니다.

 

INFORMATION_SCHEMA의 주요 뷰

빅쿼리의 INFORMATION_SCHEMA에는 여러 가지 뷰들이 있습니다.

주요 뷰들을 간략히 소개해 드리겠습니다.

 

1. SCHEMATA: 데이터베이스에 있는 스키마(데이터베이스) 목록을 제공합니다.
2. TABLES: 데이터베이스 내의 테이블 목록과 해당 테이블의 속성 정보를 제공합니다.
3. COLUMNS: 데이터베이스 내의 모든 테이블과 해당 테이블의 컬럼 정보를 제공합니다.
4. VIEWS: 데이터베이스 내에 생성된 뷰들에 대한 정보를 제공합니다.
5. ROUTINES: 저장 프로시저, 함수 등 루틴에 대한 정보를 제공합니다.
6. PARTITIONS: 테이블 파티션 정보를 제공합니다. (만약 테이블이 파티셔닝되어 있다면)
7. STATISTICS: 테이블과 인덱스의 통계 정보를 제공합니다.

 

종류

INFORMATION_SCHEMA 뷰	리소스 유형
INFORMATION_SCHEMA.SCHEMATA	데이터 세트
INFORMATION_SCHEMA.SCHEMATA_OPTIONS	데이터 세트 옵션
INFORMATION_SCHEMA.JOBS_BY_*	작업
INFORMATION_SCHEMA.JOBS_TIMELINE_BY_*	작업 타임라인
INFORMATION_SCHEMA.OBJECT_PRIVILEGES	액세스 제어
INFORMATION_SCHEMA.RESERVATION*	예약
INFORMATION_SCHEMA.CAPACITY_COMMITMENT*	예약 용량 약정
INFORMATION_SCHEMA.ASSIGNMENT*	예약 할당
INFORMATION_SCHEMA.ROUTINES	루틴
INFORMATION_SCHEMA.ROUTINE_OPTIONS	루틴 옵션
INFORMATION_SCHEMA.PARAMETERS	루틴 매개변수
INFORMATION_SCHEMA.STREAMING_TIMELINE_BY_*	스트리밍 데이터
INFORMATION_SCHEMA.TABLE*	테이블
INFORMATION_SCHEMA.COLUMN*	테이블 열
INFORMATION_SCHEMA.PARTITIONS	테이블 파티션
INFORMATION_SCHEMA.TABLE_SNAPSHOTS	테이블 스냅샷
INFORMATION_SCHEMA.VIEWS	뷰
INFORMATION_SCHEMA.SESSIONS_BY_*	세션

 

제한 사항

• BigQuery INFORMATION_SCHEMA 쿼리는 GoogleSQL 구문이어야 합니다. INFORMATION_SCHEMA는 legacy SQL을 지원하지 않습니다.
• INFORMATION_SCHEMA 쿼리 결과는 캐시되지 않습니다.
• INFORMATION_SCHEMA 뷰는 DDL 문에서 사용할 수 없습니다.

 

INFORMATION_SCHEMA 활용 예시

1. 데이터베이스 내의 테이블 목록 확인하기

• 특정 데이터베이스(데이터셋) 내의 모든 테이블 목록과 테이블의 생성 시간을 확인 가능

SELECT table_name, table_type, creation_time
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'your_dataset_name';

 

 

2. 특정 테이블의 컬럼 정보 조회하기

• 특정 테이블의 컬럼 목록과 데이터 타입, NULL 허용 여부를 조회

SELECT column_name, data_type, is_nullable
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = 'your_dataset_name'
AND table_name = 'your_table_name';

 

 

3. 특정 뷰 정보 확인하기

• 특정 뷰의 이름과 해당 뷰의 정의(쿼리)를 확인

SELECT view_name, view_definition
FROM INFORMATION_SCHEMA.VIEWS
WHERE table_schema = 'your_dataset_name'
AND view_name = 'your_view_name';

 

 

4. 단일 dataset에서 만들어진 모든 테이블에 대한 모든 세부 정보 찾기

SELECT * 
FROM 'MyDatabase'.INFORMATION_SCHEMA.TABLES;

 

 

5. 발생 횟수와 함께 모든 열 이름 표시

SELECT
	column_name,
	COUNT(table_name) as No_times_occ
	FROM'MyDatabase'.INFORMATION_SCHEMA.COLUMN;

 

 

6. 특정 데이터 유형의 열에 대한 테이블 경로 얻기

SELECT
	CONCAT(table_catalog,".",table_schema,".",table_name) AS table_path, table_name, column_name, data_type
FROM 'MyDatabase'.INFORMATION_SCHEMA.COLUMNS'
WHERE 데이터 유형 = 'INT64'

 

 

7. 분당 총 슬롯 사용량 확인

SELECT
res.period_start,
SUM(jobs.period_slot_ms) / 1000 / 60 AS period_slot_minutes,
ANY_VALUE(res.slots_assigned) AS slot_assigned,
ANY_VALUE(res.slots_max_assigned) AS slots_max_assigned
FROM
`region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_ORGANIZATION jobs
JOIN
`region-us`.INFORMATION_SCHEMA.RESERVATIONS_TIMELINE_BY_PROJECT res
ON TIMESTAMP_TRUNC(jobs.period_start, MINUTE) = res.period_start AND jobs.reservation_id = res.reservation_id
WHERE jobs.job_creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY) AND CURRENT_TIMESTAMP()
GROUP BY period_start
ORDER BY period_start DESC;

 

마무리

INFORMATION_SCHEMA는 빅쿼리 데이터베이스와 테이블, 뷰 등에 대한 메타데이터 정보를 쿼리하는 강력한 도구입니다. 그리고 데이터베이스의 구조를 이해하고 데이터를 분석하는 데 도움이 되는 중요한 정보를 제공해줍니다.
이 함수를 사용하여 데이터베이스 관리자나 분석가들은 INFORMATION_SCHEMA를 적극 활용하여 데이터베이스와 데이터에 대한 이해를 높이고 효율적인 데이터 분석 작업을 수행할 수 있습니다.

 

 

 

 

그럼 또 다음에 만나요 ~

 

 

베빠 ! 😀

 

 

 

 

[참고 자료]

구글 빅쿼리 INFORMATION_SCHEMA - BESPINGLOBAL

BigQuery INFORMATION_SCHEMA 소개  |  Google Cloud