[GCP] 빅쿼리 CLI 기본 명령어 이용방법

GCP의 빅쿼리를 간단하게 알아보고, 빅쿼리 CLI를 이용하는 방법을 알아보겠습니다.

빅쿼리(BigQuery)란?

• 구글 클라우드(GCP)에서 제공하는 완전 관리형 데이터 분석 플랫폼
  • 서버관리 없음, 쿼리로 데이터를 조회하는 서비스형 솔루션
• 저장비용과 조회비용이 있음
  • 구글 클라우드 저장소와 다른 빅쿼리 저장소 비용있음
• 웹UI, CLI 환경에서 이용 가능
• 칼럼 기반으로 데이터를 저장
  • 키나, 인덱스 개념 없음
  • 갱신, 삭제 불가
• 데이터 복제를 통해 장애 복구에 대비함

BigQuery 문서

BigQuery 문서  |  Google Cloud

빅쿼리 구성

• 빅쿼리 데이터는 관계형 데이터베이스와 유사한 형태로 프로젝트, 데이터셋트, 테이블로 구성
  • 프로젝트: 구글 클라우드 구성의 기본 단위. 여러개의 데이터셋트를 가질 수 있임
  • 데이터셋트: 관계형 데이터베이스의 데이터베이스와 동일. 여러개의 테이블을 가질 수 있음
  • 테이블: 관계형 데이터베이스의 테이블과 동일. 파티션을 이용하여 데이터를 구성하는 것이 좋음
    • 테이블을 생성하고, 빅쿼리 저장소에 데이터를 저장함

빅쿼리 CLI

빅쿼리는 파이썬 기반의 CLI 도구인 bq를 이용해서 명령을 실행할 수 있습니다. 기본적인 명령과 사용방법에 대해서 알아보겠습니다.

 

BigQuery 명령줄 도구, 명령줄 도구 참조

명령줄 도구 참조  |  BigQuery  |  Google Cloud

bq 명령어 목록

명령	설명	사용법
cancel	작업 취소를 요청	bq cancel job_id
cp	테이블을 다른 테이블로 복사	bq cp dataset.old_table dataset2.new_table
extract	destination_uris로 데이터를 추출. -m 옵션으로 모델만 추출 가능	bq extract ds.table gs://mybucket/table.csv
get-iam-policy	리소스에 대한 IAM 정책을 반환	bq get-iam-policy proj:ds
head	테이블에 행을 표시	bq head dataset.table
help	전체 또는 선택된 명령에 대한 도움말 출력	bq ls help
init	.bigqueryrc 설정 파일 작성	bq init
insert	테이블에 행을 삽입	bq insert dataset.table /tmp/mydata.json
load	destination_table에 데이터를 입력	bq load ds.new_tbl gs://mybucket/info.csv ./info_schema.json
ls	명명 된 컬렉션에 포함 된 개체를 나열	bq ls mydataset
mk	데이터 세트, 테이블, 뷰 또는 전송 구성(transfer configuration)을 작성	bq mk new_dataset
mkdef	GCS와 같은 외부 테이블에 대한 정의를 JSON으로 생성	bq mkdef 'gs://bucket/file.csv' field1:integer,field2:string
partition	소스 테이블을 파티션 된 테이블로 복사	bq partition source_table_prefix destination_partitioned_table
query	쿼리를 실행	bq query 'select count(*) from publicdata:samples.shakespeare'
rm	데이터 세트, 테이블, 전송 구성 또는 예약을 삭제	bq rm ds.table
set-iam-policy	리소스에 대한 IAM 정책을 설정	bq set-iam-policy ds /tmp/policy.json
shell	대화식 bq 세션을 실행	bq shell
show	객체에 대한 모든 정보를 표시	bq show dataset
update	데이터 세트, 테이블, 뷰 또는 전송 구성을 업데이트	bq update --description "Dataset description" existing_dataset
version	bq 버전을 반환	bq version
wait	작업이 완료 될 때까지 대기	bq wait job_id

ls: 데이터세트, 테이블 목록 확인

ls는 데이터세트와 테이블 같은 개체의 목록을 확인합니다. 프로젝트 정보를 전달하지 않으면 프로젝트를 조회하고, 프로젝트를 전달하면 데이터세트와 테이블 정보를 확인합니다.

프로젝트 목록 확인

$ bq ls
     projectId       friendlyName
 ----------------- -----------------
  project-id-1       project-id-1
  project-id-2       project-id-2

프로젝트의 데이터세트 확인

$ bq --project_id=project-id-1 ls
   datasetId
 --------------
  dataset-01
  dataset-02

테이블 확인

빅쿼리에서 정보를 확인할 때는 프로젝트명:데이터세트명.테이블명 형태로 표현합니다. 다음은 project-id-1 프로젝트의 dataset-01 데이터세트의 테이블을 확인하는 명령어 입니다.

$ bq ls project-id-1:dataset-01
               tableId                 Type    Labels    Time Partitioning     Clustered Fields
 ------------------------------------ ------- -------- ---------------------- ------------------
  table-01                             TABLE            DAY (field: cyymmdd)
  table-02                             TABLE            DAY (field: cyymmdd)

show: 개체 정보 확인

show는 개체의 정보를 확인하는 명령입니다.

데이터세트 정보 확인

$ bq show dataset-01
Dataset project-id-1:dataset-01

   Last modified                    ACLs                   Labels  
 ----------------- -------------------------------------- -------- 
  06 Jul 06:37:21   Owners:                                        
                      user@gmail.com,           
                      projectOwners                                
                    Writers:                                       
                      projectWriters                               
                    Readers:                                       
                      projectReaders  

$ bq show --format=prettyjson dataset-01
{
  "access": [
    {
      "role": "WRITER", 
      "specialGroup": "projectWriters"
    } ]
}

테이블 정보 확인

$ bq show project-id-1:dataset-01.table-01
Table project-id-1:dataset-01.table-01

   Last modified                 Schema                Total Rows   Total Bytes   Expiration    Time Partitioning     Clustered Fields   Labels
 ----------------- ---------------------------------- ------------ ------------- ------------ ---------------------- ------------------ --------
  29 Jun 12:21:15   |- col_01: string                   139057249    10580853036                DAY (field: cyymmdd)
                    |- col_02: string
                    |- col_03: string
                    |- col_04: string
                    |- col_05: string
                    |- col_06: integer
                    |- col_07: integer
                    |- col_08: integer
                    |- col_09: integer
                    |- col_10: date

query: SQL 쿼리 실행

빅쿼리는 표준 SQL과 레거시 SQL 두 가지 방식의 SQL을 지원합니다. 처음 개발 당시에 빅쿼리 SQL이라는 비표준 SQL로 구현되었으나, 빅쿼리 2.0 부터는 표준 SQL로 구현되었습니다. 세부 방식에 약간의 차이가 존재합니다. 여기서는 표준 SQL을 이용하여 데이터를 처리하도록 하겠습니다.

 

표준 SQL 쿼리 구문, 표준 SQL로 마이그레이션
BigQuery syntax (legacy, standard)

표준 SQL로 마이그레이션  |  BigQuery  |  Google Cloud

 

CLI 환경에서는 --use_legacy_sql=false 옵션을 전달하여 표준 SQL 사용여부를 선택할 수 있습니다.

테이블 조회

다음은 project-id-1:dataset-01.table-02의 정보를 조회하는 쿼리를 실행하는 예제입니다. 프로젝트는 따로 project_id 설정값으로 전달하였습니다.

$ bq query \
--project_id=project-id-1 \
--use_legacy_sql=false \
"select * from dataset-01.table-02 limit 10"

# 특정 로케이션의 데이터를 조회하는 경우 
$ bq query \
--project_id=project-id-1 \
--use_legacy_sql=false \
--location=asia-northeast3 \
"select * from dataset-01.table-02 limit 10"

표준 SQL 함수 API 사용

빅쿼리는 표준 SQL에서 제공하는 다양한 함수 API를 기본적으로 제공합니다. 다음은 EXTRACT 함수를 이용하여 date_column에서 년도를 추출하는 예제입니다.

 

BigQuery 표쥰 SQL 함수 API

표준 SQL의 날짜 함수  |  BigQuery  |  Google Cloud

$ bq query \
--project_id=project-id-1 \
--use_legacy_sql=false \
"select EXTRACT(ISOYEAR FROM date_column) as Col01 from dataset-01.table-02 limit 10"

load: 데이터 입력

load는 원천의 데이터를 읽어와서 입력하는 명령어 입니다. 데이터를 입력할 때 원천 데이터의 타입, 파티셔닝 정보 등을 전달하여 적재할 수 있습니다.

빅쿼리는 하이브 파티션 레이아웃을 이용한 파티션 구분을 지원합니다. 기본 파티션 구분자인 =을 이용하면 자동으로 인식합니다. 다음은 하이브 파티셔닝 데이터를 빅쿼리에 적재하는 예제입니다.

$ bq load --replace \
--source_format=ORC \
--time_partitioning_type=DAY \
--time_partitioning_field=col_01 \
--hive_partitioning_mode=CUSTOM \
--hive_partitioning_source_uri_prefix=gs://gs-location/table-01/{col_01:DATE}/ \
dataset-01.table-01 \
gs://gs-location/table-01/*

hive_partitioning_mode: 하이브 파티션 스키마 감지 모드

하이브 파티션 키는 Cloud Storage에서 데이터를 쿼리할 때 일반 열로 표시되며, 다음 세 가지 모드의 하이브 파티션 스키마 감지가 지원됩니다.

• AUTO: 키 이름과 유형이 자동 감지됩니다. STRING, INTEGER, DATE, TIMESTAMP와 같은 유형이 감지될 수 있습니다.
• STRINGS: 키 이름이 STRING 유형으로 자동 추론됩니다.
• CUSTOM: 파티션 키 스키마가 소스 URI 프리픽스에 인코딩됩니다.

커스텀 파티션 키 스키마 제공

CUSTOM 감지 모드를 사용하려면 소스 URI 프리픽스 필드에 스키마를 인코딩해야 합니다. CUSTOM 모드를 이용하면 각 파티션 키에 대해 사용자가 지정한 유형을 사용할 수 있습니다. 값은 지정된 유형에 유효하도록 파싱해야 하며, 그렇지 않으면 쿼리가 실패합니다.

예를 들어 source_uri_prefix를 gs://my_bucket/my_table/{dt:DATE}/{val:STRING}으로 설정하면 val이 STRING으로, dt가 DATE로 처리되고, gs://my_bucket/my_table을 일치하는 파일의 소스 URI 프리픽스로 내보냅니다.

mkdef: External 테이블 정보 생성

mkdef는 json, orc 파일의 정보를 이용하여 테이블 정의를 생성합니다. mk명령으로 테이블을 생성할 때 이 정보를 이용합니다.

bq mkdef \
--source_format=ORC \
--hive_partitioning_mode=CUSTOM \
--hive_partitioning_source_uri_prefix=gs://gs-location/table-01/{cyymmdd:DATE}/ \
gs://gs-location/table-01/* > ./gs_table.json

mk: External 생성

mkdef로 생성한 테이블 정보로 External 테이블을 생성합니다.

bq mk --external_table_definition=./gs_table.json \
project-id-1:dataset-01.gs_table