Google Cloud Platform/Bigquery

빅쿼리 Streaming Insert golang 샘플 2017.04.24
웹로그 데이터를 빅쿼리를 이용하여 리포팅 하기 2017.01.09
빅쿼리 Query Plan을 이용한 쿼리 실행 분석 2016.09.19
빅쿼리의 셔플링 2016.08.23
빅쿼리 스트리밍 데이터 로딩하기 2016.08.05 4
빅쿼리 데이터 로딩하기 2016.07.25 2

빅쿼리 Streaming Insert golang 샘플

whitechoi 2017. 4. 24. 22:50

2017. 4. 24. 22:50

빅쿼리 Streaming Insert - go lang 샘플

최유석

기본준비사항

프로젝트 과금 설정(https://support.google.com/cloud/answer/6293499#enable-billing),

빅쿼리 API활성화(https://console.cloud.google.com/flows/enableapi?apiid=bigquery&_ga=1.216323729.601314171.1492654432),

Cloud SDK 설치(https://cloud.google.com/sdk/downloads),

Client Libraries 설치 및 OAuth2.0 인증(로컬환경 실행 시) (https://cloud.google.com/bigquery/docs/reference/libraries#client-libraries-install-go),

Service account(또는 default service account사용)와 Key 생성이 되어있으며 Key 파일이 OS환경변수로 GOOGLE_APPLICATION_CREDENTIALS 지정 되어있다고 가정한다.( https://cloud.google.com/docs/authentication)

또한 go lang(https://golang.org/)설치 및 기본 설정은 되어있다고 가정한다.

여기서는 go 1.8버전을 기준으로 테스트하였다.

테이블 생성

빅쿼리에 "gotest"라는 데이터 셋을 생성하고 해당 데이터셋에 "gotable"이라는 이름으로 테이블을 생성한다. 생성할 테이블의 스키마정보는 다음과 같다.

Name:STRING,Count:INTEGER

생성된 테이블 정보

빅쿼리 Streaming Insert 샘플 코드 - Go lang

package main
import (
           "fmt"
           "log"

           "cloud.google.com/go/bigquery"
           "golang.org/x/net/context"
)

type Item struct {
           Name string
           Count int
}

// Save implements the ValueSaver interface.
func (i *Item) Save() (map[string]bigquery.Value, string, error) {
           return map[string]bigquery.Value{
                      "Name": i.Name,
                      "Count": i.Count,
           }, "", nil
}

func main() {
           ctx := context.Background()

           // Sets your Google Cloud Platform project ID.
           projectID := "cystest-1" //프로젝트ID
           datasetID := "gotest" //데이터셋ID
           tableID := "gotable" //테이블ID

           // Creates a client.
           client, err := bigquery.NewClient(ctx, projectID)
           if err != nil {
                      log.Fatalf("Failed to create client: %v", err)
           }
           insertRows(client, datasetID, tableID)
}

func insertRows(client *bigquery.Client, datasetID, tableID string) error {
           ctx := context.Background()

           // [START bigquery_insert_stream]
           u := client.Dataset(datasetID).Table(tableID).Uploader()
           items := []*Item{
                      // Item implements the ValueSaver interface.
                      // 아래에 테이블에 삽입할 레코드를 입력한다.
                      {Name: "n1", Count: 7},
                      {Name: "n2", Count: 2},
                      {Name: "n3", Count: 1},
           }
           if err := u.Put(ctx, items); err != nil {
                      return err
           }
           // [END bigquery_insert_stream]
           fmt.Printf("insert complete\n")
           return nil
}

코드 실행하기( .go파일이 위치한 폴더에서)

go run gotest.go

*빌드는 생략한다.

코드실행 결과 확인하기

빅쿼리에 Streaming Insert로 데이터를 적재하게되면 Streaming buffer에 먼저 기록되고 실제 테이블에 값이 저장되기까지 최대 90분까지 소요될 수 있다. 따라서 테이블의 preview로는 바로 확인 안 될 수 있으니 간단한 조회 쿼리를 실행해서 확인한다.

좌측 상단의 [COMPOSE QUERY] 버튼으로 쿼리 입력 창을 활성화하고 다음 쿼리를 입력하고 실행한다.

SELECT * FROM gotest.gotable

쿼리 실행결과

쿼리를 실행하면 정상적으로 테이블에 데이터가 추가된 것을 확인할 수 있다.

참고자료

https://cloud.google.com/bigquery/streaming-data-into-bigquery

https://godoc.org/cloud.google.com/go/bigquery

https://github.com/GoogleCloudPlatform/google-cloud-go/blob/master/bigquery/uploader.go

https://github.com/GoogleCloudPlatform/golang-samples/blob/master/bigquery/snippets/snippet.go

'Google Cloud Platform > Bigquery' 카테고리의 다른 글

웹로그 데이터를 빅쿼리를 이용하여 리포팅 하기 (0)	2017.01.09
빅쿼리 Query Plan을 이용한 쿼리 실행 분석 (0)	2016.09.19
빅쿼리의 셔플링 (0)	2016.08.23
빅쿼리 스트리밍 데이터 로딩하기 (4)	2016.08.05
빅쿼리 데이터 로딩하기 (2)	2016.07.25

웹로그 데이터를 빅쿼리를 이용하여 리포팅 하기

whitechoi 2017. 1. 9. 23:07

2017. 1. 9. 23:07

웹로그 데이터를 빅쿼리를 이용하여 리포팅 하기

최유석

Fluentd를 이용해서 스트림 데이터를 BigQuery에 로딩해보자.

Fluentd에 대한 자세한 내용은 http://bcho.tistory.com/1115을 참고하기 바란다.

이 글에서는 구글 컴퓨트 엔진의 VM에 NGINX로 웹 서버를 구성하고, HTTP로 NGINX에 접근(access)하는 로그를 Fluentd로 수집하고 BigQuery에 저장한다. 그리고 BigQuery에 저장한 데이터를 구글 스프레드 시트의 차트와 구글 클라우드 데이터랩을 이용해서 시각화하는 방법에 대해서 설명하겠다.

Fluentd 설치

예제는 Google Cloud에서 Ubuntu Linux 14.x VM에서 Fluentd를 이용하여 NGINX 웹 서버로 구성된 VM에서 발생하는 트래픽 데이터(access log)를 수집하고 BigQuery에 데이터를 로딩한다.

VM 생성하기

먼저, Fluentd를 설치할 VM을 생성해보자.

VM을 생성하기 위해 컴퓨트 엔진 인스턴스 리스트(VM instances) 페이지에서 “+CREATE INSTANCE” 버튼을 클릭한다.

아래 그림과 같이 VM을 생성할 때, “Identity and API access” 부분에 “Allow full access to all Cloud APIs”를 선택한다. 이를 선택해서 이 VM이 모든 구글 클라우드 API에 대한 접근 권한 (BigQuery 포함)을 가지도록 한다.

그리고 “Boot disk”부분에서 Change버튼을 클릭하여 OS를 “Ubuntu 14.04 LTS”로 변경한다.

또한, 이후 NGINX로 웹 서버를 구성하고 웹 브라우저를 통해 HTTP로 접근되는 액세스 로그를 Fluentd를 이용해서 수집하고 해당 데이터를 시각화할 예정이다. 따라서 “Firewall”항목에서 “Allow HTTP traffic” 항목을 체크하여 HTTP 트래픽에 대한 방화벽을 허용한 상태로 VM을 생성한다.

Fluentd td-agent 설치하기

앞에서 생성한 VM에 Fluentd의 로그 수집 에이전트인 td-agent를 설치한다.

td-agent는 OS나, 또는 같은 OS라도 OS 버전 별로 설치 방법이 다르기 때문에,만약 다른 OS를 설치할 것이라면, 각각의 OS의 버전 별 설치 방법은 http://www.fluentd.org를 참고하기 바란다.

여기서는 Ubuntu 14.x를 기준으로 진행 한다.

다음 명령어를 실행하면 td-agent가 설치된다.

% curl -L https://toolbelt.treasuredata.com/sh/install-ubuntu-trusty-td-agent2.sh | sh

설치가 끝난 후 에이전트를 실행해서 확인해보자. 다음 명령으로 agent를 실행한 후에,

% sudo /etc/init.d/td-agent restart

실행이 끝난 후에 다음 명령으로 설치를 확인한다.

% sudo /etc/init.d/td-agent status

참고 (td-agent 관련 명령어)
td-agent 기동 - $sudo /etc/init.d/td-agent start
td-agent 정지 - $sudo /etc/init.d/td-agent stop
td-agent 재시작 - $sudo /etc/init.d/td-agent restart
td-agent 상태확인 - $sudo /etc/init.d/td-agent status

빅쿼리에 데이터를 저장하기 위한 구성하기

td-agent설치가 끝났으면 VM에서 td-agent로 빅쿼리에 데이터를 저장하기 위한 플러그인을 설치하고 해당 데이터들이 저장 될 빅쿼리의 데이터셋과 테이블을 생성한다. 그리고 nginx설치 후 발생한 브라우저 트래픽(HTTP)에 대한 액세스 로그를 수집하고 빅쿼리에 저장하기 위한 td-agent설정을 해보자.

빅쿼리 플러그인 설치

Fluentd에서 빅쿼리로 데이터를 저장하기 위한 빅쿼리 플러그인을 설치한다.

% sudo td-agent-gem install fluent-plugin-bigquery

빅쿼리 테이블 생성하기

td-agent를 통해서 수집된 데이터를 저장할 빅쿼리의 데이터셋과 테이블을 생성해보자.

*앞서 생성한 컴퓨트 엔진 VM과 동일한 프로젝트에서 진행한다.

먼저, 빅쿼리 웹 콘솔(https://bigquery.cloud.google.com/)에 접속한다.

데이터셋은 아래 그림과 같이 빅쿼리 웹 콘솔 화면에서 해당 프로젝트 우측의 드롭 다운메뉴에서 Create new dataset메뉴를 이용하여, “bq_test”라는 이름으로 생성한다.

테이블을 생성하기 위해 앞에서 생성한 데이터셋(“bq_test”)의 우측의 +모양의 아이콘을 클릭한다. 먼저 테이블 명은 “access_log” 으로 생성한다.

그리고, 데이터가 없이 스키마만 정의된 빈 테이블로 생성하기 위해 Source Data부분에서 “Create empty table”에 체크하고

생성할 테이블의 스키마는 https://raw.githubusercontent.com/GoogleCloudPlatform/bigquery-fluentd-docker-sample/master/schema.json에 있다. 이 스키마 파일의 내용을 복사해서 빅쿼리 웹 콘솔에서 아래 그림과 같이 Schema 부분에서 Edit as Text를 클릭하여 입력 형식을 변경한 상태에서 붙여넣고 테이블을 생성한다.

td-agent설정하기

td-agent에서 설정파일을 수정하여 nginx로 수집될 로그 데이터가 빅쿼리에 저장될 수 있도록 설정해보자. 설정 파일은 /etc/td-agent/td-agent.conf 에 있으며, 이 파일에 vi등의 편집기를 이용해서 기존의 내용을 삭제하고 다음의 내용으로 변경한다.

# collecting nginx access log
<source>
type tail
format apache
path /var/log/nginx/access.log
tag nginx.access
pos_file /var/log/td-agent/nginx.pos
</source>
# forwarding to bigquery plugin
<match nginx.access>
type bigquery
auth_method compute_engine
project "<Project ID>"
dataset bq_test
table access_log
time_format %s
time_field time
fetch_schema true
#deprecated field_integer time
</match>

설정 파일(td-agent.conf )의 주요 부분을 살펴보면

● 크게 <source>…</source>부분은 앞으로 구성할 nginx 웹 서버로 발생하는 액세스 로그를 수집하는 부분에 대한 설정이고, <match nginx.access>…</match>부분은 앞에서 수집된 데이터를 빅쿼리에 저장하는 부분에 대한 설정이다.

● auth_method, project, dataset, table : 데이터를 저장될 bigquery의 project, dataset, table 명을 정의한다. 그리고 auth_method를 통해서 인증 방법을 설정하는데, 일반적으로는 service account에 대한 json 파일을 사용하는데, 여기서는 구글 클라우드 내에 VM을 생성하였고, 앞에서 VM 생성시에 Bigquery에 대한 접근 권한을 이미 주었기 때문에, 인증 방식을 compute_engine으로 설정하면 된다.

● fetch_schema : true로 설정하면 저장하고자 하는 bigquery 테이블의 스키마 정보를 자동으로 가져온다. fetch_schema대신에 schema_path라는 옵션을 사용해서 JSON으로 정의된 스키마 파일 경로를 직접 지정할 수 도 있다.

**td-agent 설정에서 field_*(Integer, string, float, 등) 속성이 fluentd 버전이 올라가면서 deprecated되어 작동되지 않는다. td-agent 설정 마지막 줄 옵션 field_integer time을 삭제해야 동작된다.

NGINX로 웹 서버 구성하기

지금까지 데이터를 수집하고 저장하기 위한 Fluentd와 빅쿼리의 구성을 하였다. 이제 NGINX로 웹 서버를 구성하고 트래픽을 발생시켜서 액세스 로그를 생성하고 빅쿼리에서 저장되는 데이터를 확인해보자.

NGINX설치하기

앞에서 Fluentd를 설치한 VM에서 우분투(데비안 계열)의 패키지 관리 도구인 apt의 다음 명령어를 이용해서 패키지 인덱스 정보를 업데이트하고 NGINX를 설치한다.

%sudo apt-get update && sudo apt-get install -y nginx

nginx 설치가 끝났다면, 해당VM의 external IP를 클릭하고,

웹 브라우저에서 다음과 같이 nginx가 정상적으로 구동 되는지 확인한다.

NGINX 설정하기

Nginx가 정상적으로 동작하는 것을 확인하였다면 앞서 td-agent설정에서 수집될 데이터의 포맷을 “apache”로 설정하였기 때문에 nginx에서 수집될 로그의 포맷을 combined형식으로 변경해야 한다. nginx의 설정 파일은 /etc/nginx/nginx.conf에 있으며, vi등의 편집기를 이용해서 해당 파일 중간의 Logging Setting부분에서 다음과 같이 변경한다.

… //앞 뒤 부분 생략
##
# Logging Settings
##

access_log /var/log/nginx/access.log combined;
…

Nginx 로그 파일 권한 변경

nginx 설치 후 기본적으로 /var/log/nginx/access.log 파일에 대한 읽기 권한이 root사용자에게만 있다. 이후 td-agent에서 nginx로그 파일을 읽어서 빅쿼리에 저장할 수 있게 이 파일(/var/log/nginx/access.log)에 대한 권한 변경이 필요하다.

root로 사용자를 다음의 명령어로 변경하고

%sudo su

다음의 명령어를 실행해서 nginx 액세스 로그 파일의 권한을 변경한다.

%chmod 644 /var/log/nginx/access.log

권한 변경이 끝나면 다음의 명령어로 다시 기존 사용자로 돌아온다.

%exit

빅쿼리 데이터 저장 테스트

지금까지 Fluentd로 웹 서버(nginx) 로그데이터를 수집하고 빅쿼리로 저장하기 위한 모든 설정이 끝났다. 이제 웹 브라우저로 접근해서 트래픽을 발생시키고 이 데이터가 빅쿼리에 실제로 저장되는 지 확인해보자.

먼저, 앞에서 설정한 내용들을 적용하기위해, 다음 명령어를 이용해서 td-agent와 nginx를 재 시작한다.

%sudo /etc/init.d/td-agent restart && sudo /etc/init.d/nginx restart

다음으로, 웹 브라우저에서 해당 VM의 external IP로 접속해서 nginx에 액세스 로그를 생성되게 한다. 컴퓨트 엔진 인스턴스 리스트(VM instances) 페이지에서 앞에서 구성한 VM의 우측의 External IP를 클릭하여 nginx 웹 서버로 접근한다.

* HTTP에 대한 데이터만 수집되게 설정하였기 때문에, 웹 브라우저에서 http://External IP/ 형태로 접근한 데이터에 대해서만 수집된다.

td-agent를 통해서 nginx에서 수집된 로그데이터가 빅쿼리에 저장된다. 이제 빅쿼리 웹 콘솔에서 로그 데이터가 빅쿼리에 정상적으로 저장되었는지 확인하기 위해 다음의 쿼리를 실행한다.

SELECT * FROM [bq_test.access_log] LIMIT 1000

앞에서 웹 브라우저로 접근하여 생성된 nginx액세스 로그 데이터를 확인할 수 있어야한다.

*참고 : Fluentd를 통해 초기 로그값을 저장하는 데까지 수 분이 소요될 수 있다.

구글 스프레드 시트로 빅쿼리 쿼리 실행하기

지금까지 구글 클라우드에서 컴퓨트 엔진의 VM과 빅쿼리 및 오픈소스로 제공되는Fluentd의 td-agent, nginx(웹 서버)를 구성해서 웹 서버에서 발생하는 로그 데이터를 수집하고 빅쿼리에 저장하고 쿼리를 실행해서 저장된 데이터를 확인하기 까지 테스트를 하였다.

지금까지 구성한 것을 토대로 빅쿼리와 구글 스프레드 시트를 연동해서 데이터를 시각화(Visualization) 하는 방법에 대해서 설명하겠다.

빅쿼리와 구글 스프레드 시트

빅쿼리는 구글 스프레드 시트와 쉬운 연동이 가능하며, 구글 스프레드 시트는 빅쿼리의 쿼리 실행을 위한 앱스 스크립트(Apps Script)를 가지고 있다. 또한 실행한 빅쿼리의 쿼리 결과를 시트에 저장하고 구글 스프레드 시트에서 제공하는 차트를 이용해서 데이터에 대한 시각화를 손쉽게 할 수 있으며, 사용자가 지정한 시간에 주기적으로 쿼리를 실행해서 실행된 쿼리 정보와 차트를 자동으로 업데이트 할 수 있는 기능을 제공한다. 이를 이용하면, 전문적인 데이터 분석가 또는 과학자가 아니더라도 데이터 분석을 쉽게 가능하게 한다.

구글 스프레드 시트와 빅쿼리를 연동해서 사용자가 사용하기 쉽도록 빅쿼리와 구글 스프레드 시트 통합 예제(Example of BigQuery and Google Spreadsheet Integration)를 제공하며, 이 글에서는 이 예제를 활용한다.

구글 스프레드 시트에서 빅쿼리 API 활성화하기

앞에서 설명한 것처럼, 구글 스프레드 시트에서 빅쿼리로 쿼리를 실행하기 위해서 빅쿼리 API를 활성화해야 한다. 빅쿼리 API를 활성화하기 위해 다음과 같이 진행한다.

1. Example of BigQuery and Google Spreadsheet Integration를 열고

상단의 메뉴에서 [파일 → 사본 만들기] 를 실행해서 해당 파일을 자기 계정의 구글 드라이브로 복사한다.

*이후 진행되는 모든 부분은 구글 드라이브에 복사한 스프레드 시트 파일을 사용한다.

2. 스프레드 시트의 상단 메뉴에서 [도구 → 스크립트 편집기]를 실행한다.

*스크립트 편집기를 실행하면 bq_query.gs라는 파일이 열려 있다. 이 파일에 작성된 내용은 앱스 스크립트로 작성되어 있으며, 이 스크립트가 동작해서 구글 스프레드 시트에서 빅쿼리로 쿼리를 실행하고, 쿼리 결과를 시트에 저장하고, 쿼리 결과에 대한 차트를 만들어 준다.

3. bq_query.gs 중간의 <<PUT YOUR SPREADSHEET URL HERE>>부분을 복사한 스프레드 시트의 URL(스크립트 편집기 URL 아님)로 변경하고,

<<PUT YOUR PROJECT ID HERE>>부분을 앞에서 구성한 컴퓨트 엔진의VM 및 빅쿼리를 포함하고 있는 프로젝트 ID로 변경하고 저장한다.

↓↓↓↓↓ (각각의 사용자의 환경에 맞게 변경한다.)

4. 스크립트 편집기 상단의 메뉴에서 [리소스 → 고급 Google 서비스]를 실행하고 빅쿼리 API가 활성화(녹색) 되어있는지 확인하고, 만약 활성화 되어 있지 않다면 활성화 한다.

그리고 [고급 Google 서비스]의 설정 메뉴 하단의 “Google 개발자 콘솔” 링크를 클릭하여 구글 개발자 콘솔(Google Developer Console)의 API Manager페이지로 이동해서 Google Cloud APIs부분의 BigQuery API를 클릭하고

빅쿼리 API 페이지로 이동해서 “ENABLE” 버튼을 클릭하여 API를 활성화 한다. “DISABLE”로 바뀌면 활성화 된 상태이다.

이제 빅쿼리 API를 활성화 하였으면, 창을 닫고 다시 스크립트 편집기로 이동해서 [고급 Google 서비스] 설정메뉴에서 확인을 클릭한다.

*만약, “Google 개발자 콘솔” 링크로 이동할 때, 해당 스프레드 시트와 연결된 새로운 프로젝트 ID를 생성할 지를 묻는 창이 나오면 확인을 선택한다.

빅쿼리 공개 데이터 셋에 쿼리 실행하기

이제 구글 스프레드 시트에서 빅쿼리의 쿼리 실행, 쿼리 결과 저장, 쿼리 결과에 대한 차트 생성에 대한 구성을 마쳤다. 복사한 스프레드 시트 파일로 이동해서 구글 빅쿼리에서 제공하는 공개 데이터셋에 쿼리를 실행해보자.

쿼리 실행 정보

스프레드 시트에서 BQ Queries 시트로 이동하면 이미 작성되어 있는 “gsod_temperature_LINE” 라는 이름의 쿼리를 확인 할 수 있다. 이 쿼리는 구글 빅쿼리에서 제공하는 공개 데이터 셋인 GSOD climate database에서 년도 별 온도 데이터를 평균, 최저, 최대 값으로 집계한다.

쿼리 실행하기

스프레드 시트 상단의 메뉴[Dashboard → Run All BQ Queries]를 실행하면, 쿼리가 실행된다.

*최초 쿼리 실행 시 권한 부여에 대한 부분이 나오면 각각 [계속] , [허용] 버튼을 클릭하여 진행한다.

쿼리 실행이 완료되면, gsod_temperature라는 이름의 새로운 시트가 생성되며, 다음과 같이 쿼리 실행의 결과가 저장되어 있는 것을 확인할 수 있다..

그리고 BigQuery Results Visualization시트로 이동하면, 다음과 같이 gsod_temperature라는 이름으로 앞에서 실행한 쿼리 결과에 대해 차트가 새로 생성되어 있는 것을 확인할 수 있다.

Fluentd로 수집한 데이터에 쿼리하기

앞서 공개 데이터 셋에 대한 쿼리 테스트를 하였다. 이제 앞에서 구성한 컴퓨트 엔진의 VM에Fluentd 및 NGINX로 수집하여 빅쿼리에 저장한 로그데이터에 대해서 쿼리를 실행해보도록 하자.

다시 BQ Queries 시트로 이동해서 앞에서 실행한 공개 데이터 셋의 쿼리 다음 줄에 아래의 내용을 입력한다.

query name은 “access_log_LINE” interval (min)은 1로 입력하고 아래의 쿼리를 query항목에 입력한다.

SELECT
STRFTIME_UTC_USEC(time * 1000000, "%Y-%m-%d %H:%M:%S") as tstamp,
count(*) as rps
FROM bq_test.access_log
GROUP BY tstamp ORDER BY tstamp DESC;

*쿼리는 반드시 시트의 셀 하나에 포함되도록 넣어야 한다. 다음과 같이 해당 셀을 더블 클릭해서 복사하거나 해당 셀을 지정한 상태에서 위의 fx부분에 쿼리를 복사한다.

앞에서와 같이 [Dashboard → Run All BQ Queries]으로 쿼리를 실행한다. 실행이 완료되면, access_log라는 시트가 새로 생성되며 해당 쿼리의 결과가 저장되어 있음을 확인할 수 있으며,

다시 BigQuery Results Visualization시트를 열면 access_log라는 이름으로 실행한 쿼리 결과에 대한 차트가 새롭게 생성되어 있는 것을 확인할 수 있다.

*만약 차트가 생성되지 않는다면, BQ Queries시트에서 추가한 query name의 끝에 “_LINE”을 포함하고 있는 지 확인하길 바란다.

로드 시뮬레이션 및 자동화 구성하기

앞에서의 쿼리 실행 결과를 보면 웹 서버로 구성한 VM(Compute Engine Instance)에 아직 많은 접근이 이루어 지지 않았기 때문에 차트에 나타나는 데이터가 작다. 이번에는 아파치 벤치마킹 툴(Apach Bench)을 이용해서 부하를 줘서 브라우저 트래픽을 증가시키고 쿼리 실행을 한 결과를 시뮬레이션 해보고 구글 스프레드 시트의 스크립터 편집기의 트리거라는 기능을 이용해서 자동화 쿼리를 구성해보도록 한다.

로드 시뮬레이션

아파치 벤치마킹 툴을 이용한 다음의 명령어로 100개의 클라이언트로 1,000,000개의 부하를 주자. <YOUR_EXTERNAL_IP>는 해당 VM의 external IP를 입력한다.

%ab -c 100 -n 1000000 http://<YOUR_EXTERNAL_IP>/

*로컬 컴퓨터에 아파치 벤치마킹 툴이 설치되어 있지 않다면, 다른 컴퓨트 엔진 인스턴스(VM)으로 명령을 실행해도 되며, 테스트 용도이기 때문에 앞에서 구성을 완료한 VM 즉, 부하를 받을 VM에서 위의 명령어를 실행해도 무방하다.

이제 스프레드 시트파일에서 [Dashboard → Run All BQ Queries]로 쿼리를 실행하면 access_log 시트에서 좀 더 많아진 쿼리 결과를 확인할 수 있을 것이다. 또한, BigQuery Results Visualization시트를 열면 차트의 그래프가 많아진 쿼리 결과를 확인할 수 있을 것이다.

자동화 쿼리 구성하기

앞에서는 사용자가 직접 쿼리를 수행해야만 해당 쿼리 결과 및 차트의 변화를 확인할 수 있었다.

이제 구글 스프레드 시트의 스크립트 편집기에서 제공하는 트리거라는 기능을 이용해서 사용자가 지정한 특정 시간 간격으로 자동으로 쿼리를 실행하고 지속적으로 쿼리 결과 및 차트를 업데이트하는 방법에 대해서 알아보고자 한다.

1. 스프레드 시트에서 다시 상단의 메뉴 [도구 → 스크립트 편집기]를 실행한다. 스크립트 편집기에서 상단의 메뉴 [리소스 → 현재 프로젝트의 트리거]를 실행한다. 아래와 같이 설정된 트리거가 없을 것이다. [트리거가 설정되어 있지 않습니다. 여기를 클릭하여 트리거를 추가하세요.] 부분을 클릭하여 트리거를 구성해보자

2. 트리거 설정 메뉴에서 실행 부분에는 “runQueries”로 설정하고 이벤트 부분에서는 “시간 기반”, “분 타이머”, “매분” 으로 설정한다. 이렇게 하면 bq_query.gs가 1분 단위로 실행되고 자동으로 쿼리 실행의 결과 및 차트가 자동으로 업데이트 된다. 설정이 끝났으면, 해당 트리거의 내용을 저장한다.

3. 다시 스프레드 시트의 BigQuery Results Visualization 시트로 이동하면, “access_log”의 그래프가 매 분마다 새로고침 되는 것을 확인할 수 있다. 또한, 웹 서버로 구성한 VM에 트래픽이 거의 없기 때문에 변화를 확인하기는 어렵다. 따라서 다시 아파치 벤치마킹 툴을 이용해서 부하를 주면 access_log에 대한 차트가 다음과 같이 거의 실시간(1분단위)으로 변화하는 모습을 확인할 수 있을 것이다.

*이 예제에서는 스크립트 트리거(script trigger)와 쿼리 간격(query interval)을 모두 1분으로 설정하였다. 이것은 스크립트가 매 1분마다 동작하고 쿼리들 역시 매 1분마다 불러오게 됨을 의미한다. 만약 스크립트에서 시간당 한번씩 트리거하도록 설정하고 쿼리 간격을 1분으로 유지한다면, 쿼리는 한시간에 한번만 불러오게 된다. 일반적으로 쿼리는 가져오길 원하는 주기만큼 트리거되도록 스크립트를 설정해야 한다. 쿼리 호출에 대한 간격을 없애려고 한다면 쿼리 간격을 0(min)으로 설정하면 된다. 또한, 모든 쿼리 실행을 비활성화하려면, 스크립트 트리거를 삭제하면 된다.

*트리거를 설정해둔 경우 계속 스크립트가 실행되며 쿼리 실행이 이루어지기 때문에 아래의 순서로 트리거를 삭제하길 권장한다.

bq_query.gs 스크립트의 자동 실행을 중지하기 위해서는 스크립트 편집기의 상단 메뉴 [리소스 → 현재 프로젝트의 트리거]에서 앞에서 설정한 "runQueries" 트리거를 삭제하고 저장한다.

데이터랩(Datalab)으로 데이터 시각화하기

구글 클라우드 데이터랩(Google Cloud Datalab)은 오픈소스로 공개된 주피터 노트북(Jupyter)을 구글 클라우드 플랫폼에 맞게 기능을 추가하여 제공되는 서비스이다. 기본이 되는 주피터가 오픈소스이기에 데이터랩 역시 오픈소스로 공개되어있다.

데이터랩은 파이썬(python)을 기본언어로 지원하며, 구글 클라우드의 빅쿼리와 연동등에 사용할 수 있게 SQL과 자바스크립트를 지원한다. 또한 머신러닝의 딥러닝 프레임웍인 텐서플로우도 지원하고 있다.

데이터랩에서 연동할 수 있는 데이터는 구글 클라우드에서 제공하는 컴퓨트엔진의 VM, 빅쿼리, Cloud Storage등이 있다.

데이터랩은 오픈소스로 공개되어 있으며, 데이터랩 사용에 따른 별도의 추가요금이 발생하지 않는다. 따라서, VM에 설치해서 실행하거나, 로컬 환경에 구성해서 사용할 수 있다. 또한, 도커로 패키징되어 있어서 도커 환경만 있다면 손쉽게 설치 및 실행을 할 수 있다.

Google Cloud SDK 설치하기

데이터랩은 사용자 인증 및 설치에 Google Cloud SDK(이하 gcloud SDK)를 사용하고 이후 진행과정에서 gcloud SDK에서 제공하는 주요한 명령어 도구의 사용이 필요하기 때문에 먼저, gcloud SDK를 각각의 OS환경에 맞게

https://cloud.google.com/sdk/downloads 에서 다운로드 받아서 설치한다.

설치가 끝났으면 다음의 명령어로 초기화를 실행한다.

%gcloud init

먼저, “Create a new configuration”을 선택하여 새롭게 구성한다.

다음으로, 해당 configuration을 저장할 이름을 입력한다. 임의로 입력해도 되며, 여기서는 편의상 “fluentd-test”로 지정하였다.

다음으로 로그인 계정을 선택하는 부분으로 사용자가 앞에서 구글 클라우드 콘솔에 로그인한 계정을 선택한다.

앞에서 로그인 계정을 선택하면, 구글 클라우드에서 사용 중인 프로젝트의 리스트가 나온다. 이 중에서 앞에서 컴퓨트엔진과 빅쿼리를 사용중인 프로젝트로 선택한다.

뒤의 컴퓨트 엔진 구성부분은 여기에서는 필요하지 않으니 n을 입력하고 넘어간다.

gcloud SDK설정 작업이 마무리 되었다.

*지금까지 gcloud SDK를 설정한 정보는 다음의 명령어로 확인 가능하다. 만약 프로젝트ID 또는 로그인 계정이 잘못 설정된 경우 다시 초기화를 진행하기 바란다.

데이터랩 설치

이글에서는 구글 클라우드 컴퓨트 엔진의 VM에 데이터랩을 설치하고 데이터랩을 사용해서 데이터 분석하는 방법에 대해 알아보겠다.

앞에서 설치한 gcloud SDK를 실행하고 다음의 명령어로 데이터랩 구성 정보가 도커로 사전 정의되어 있는 YAML파일을 다운로드한다.

%gsutil cp gs://cloud-datalab/server.yaml ./datalab-server.yaml

YAML파일을 다운로드 하였다면, 데이터랩을 구성하고 실행할 VM을 생성하기 위해 gcloud SDK에서 다음의 명령어를 실행한다.

%gcloud compute instances create "instance-name" \
   --project "project-id" \
   --zone "zone" \
   --image-family "container-vm" \
   --image-project "google-containers" \
   --metadata-from-file "google-container-manifest=datalab-server.yaml" \
   --machine-type "n1-highmem-2" \
   --scopes "cloud-platform"

Copy & Paste용

%gcloud compute instances create "instance-name" --project "project-id" --zone "zone" --image-family container-vm" --image-project "google-containers" --metadata-from-file "google-container-manifest=datalab-server.yaml" --machine-type "n1-highmem-2" --scopes "cloud-platform"

gcloud SDK의 compute 명령어로 VM(instances)을 생성하기 위한 명령어이며, 사용자에 맞게 변경이 필요한 부분은 “instance-name” 은 생성하기를 원하는 VM이름으로 설정하고, "project-id"는 현재 사용하고 있는 프로젝트의 ID를 입력한다. “zone”은 생성할 VM이 위치할 zone을 지정한다. 여기서는 앞에서 이용한 VM과 같은 “us-central1-a”으로 진행한다.

SSH 터널링

데이터랩을 구성하는 VM을 앞에서 생성하였다면, 데이터랩은 해당 VM에서 자동으로 실행되고 있다. 이제 SSH 터널링으로 로컬환경에서 데이터랩VM에 접속해서 사용하기 위해 데이터랩이 설치된 VM에서 SSH설정 변경이 필요하다.

따라서, 컴퓨트 엔진의 VM리스트 페이지(VM instances)로 이동해서 해당 VM의 SSH로 접속한다.

데이터랩VM에 SSH로 접속하였다면, vi등의 편집기를 이용해서 ssh 설정파일(/etc/ssh/sshd_config)의 내용을 수정하고 저장한다. (root권한 필요)

…
PasswordAuthentication no → PasswordAuthentication yes
…

SSH 설정 파일의 내용을 수정하고 해당내용을 적용하기 위해서 SSH 서비스를 다음의 명령어로 다시 시작한다.

%sudo service ssh restart

설정 및 SSH서비스 재시작이 끝났다면 해당 SSH연결은 닫는다.

다시 gcloud SDK명령줄 도구로 이동해서 다음의 명령어를 실행해서 SSH 터널링을 구성한다.

%gcloud compute ssh --quiet \
--project "project-id" \
--zone "zone" \
--ssh-flag="-N" \
--ssh-flag="-L" \
--ssh-flag="localhost:8081:localhost:8080" \
"${USER}@instance-name"

Copy & Paste용

%gcloud compute ssh --quiet --project "project-id" --zone "zone" --ssh-flag="-N" --ssh-flag="-L" --ssh-flag="localhost:8081:localhost:8080" "${USER}@instance-name"

SSH터널링으로 VM에서 8080포트에 실행되고 있는 데이터랩을 로컬에서 8081포트로 접속할 수 있게 정의한다. 이외에 변경이 필요한 내용으로 "project-id"는 현재 사용중인 프로젝트ID를 입력하고, "zone"부분은 데이터랩VM을 생성한 zone위치를 입력한다. 그리고 "${USER}@instance-name" 부분에서 “${USER}”는 해당 VM에 접속할 사용자명(gcloud SDK 초기화 설정에서 선택한 로그인 계정)으로 변경하고 @뒤의 “instance-name”은 앞에서 생성한 데이터랩VM의 이름으로 변경한다.

*주의 : 위의 명령어를 실행한 상태가 유지되어야 데이터랩에 로컬에서 접속할 수 있으며, gcloud SDK창을 닫거나 해당 명령어 실행을 취소하면 데이터랩에 접속할 수 없다.

데이터랩에서 빅쿼리 쿼리 실행하기

데이터랩 접속하기

데이터랩VM을 생성하고 SSH 터널링을 실행한 상태라면, 웹 브라우저를 실행하고 http://localhost:8081 로 접속한다. 접속을 하면 앞에서 생성한 VM에서 실행중인 Google Cloud Datalab화면을 볼 수 있으며, 이제 데이터랩의 노트북을 이용해서 분석을 진행할 수 있다.

빅쿼리 쿼리 실행하기

데이터랩에 접속하였으면, 앞에서 구글 스프레드 시트를 이용해서 실행했던 빅쿼리의 쿼리를 실행해보자.

데이터랩은 노트북이라는 단위로 사용자가 실행한 코드 및 입력한 텍스트 등의 정보를 관리한다.

먼저 데이터랩에서 “+Notebook”버튼을 클릭하고 새로운 노트북을 생성하고, 생성한 노트북에서 “+Add Code”버튼을 이용해서 생성한 코드블럭에 SQL, 파이썬스크립트, 자바스크립트등의 코드를 입력하여 실행할 수 있고, “+Add Markdown” 버튼으로 생성한 텍스트 편집 상자에는 간단한 텍스트 또는 이미지를 입력할 수 있다.

먼저, “+Add Markdown” 버튼을 클릭해서 텍스트 상자를 만들고 다음의 내용을 입력한다.

빅쿼리 쿼리 실행하기

다음으로 “+Add Code”버튼을 클릭하여 코드블럭을 생성하고 아래의 내용을 입력한다.

%%sql
SELECT
STRFTIME_UTC_USEC(time * 1000000, "%Y-%m-%d %H:%M:%S") as tstamp,
count(*) as rps
FROM bq_test.access_log
GROUP BY tstamp ORDER BY tstamp DESC;

%%sql은 빅쿼리의 sql을 실행하겠다고 명시하는 부분이며 아래의 쿼리 내용은 구글 스프레드 시트로 실행한 쿼리와 같다.

*Google Cloud SDK로 프로젝트 및 사용자에 대한 인증이 이루어지기 때문에 따로 프로젝트ID를 명시하지 않아도 된다.

상단의 메뉴에서 “RUN” 버튼을 클릭하면 위의 쿼리가 실행되고 결과를 확인할 수 있다.

Google Cloud Datalab을 이용한 데이터 시각화

데이터랩의 노트북에서는 빅쿼리에 저장된 데이터를 구글 차트API를 사용하여 SQL로 조회한 데이터를 간단한 명령어로 차트를 생성하고 시각화 할 수 있으며, 다양한 유형의 차트를 지원한다. 또한, 파이썬 코드를 이용해서 데이터를 자유롭게 가공하고 파이썬의 matplotlib를 이용해서 데이터를 그래프로 나타낼 수도 있다. 이글에서는 구글 차트API를 이용한 명령으로 차트를 생성해서 시각화하는 방법에 대해서 알아본다.

모듈에 데이터 저장하기

데이터를 구글 차트 또는 파이썬 matplotlib등으로 시각화하기 위해서는 SQL을 실행한 결과를 모듈로 저장하고, 데이터가 저장된 해당 모듈을 이용해야한다. SQL로 실행한 결과를 모듈로 저장하기위해 다음과 같이 진행한다.

먼저 “+Add Markdown”버튼을 클릭하여 텍스트 상자를 만들고 아래와 같이 입력한다.

빅쿼리 쿼리 실행 결과 모듈에 저장하기

다음으로 “+Add Code” 버튼을 클릭하여 생성한 코드블럭에 다음의 코드를 입력한다.

%%sql --module=log_data
SELECT
STRFTIME_UTC_USEC(time * 1000000, "%Y-%m-%d %H:%M:%S") as tstamp,
count(*) as rps
FROM bq_test.access_log
GROUP BY tstamp ORDER BY tstamp DESC;

%%sql뒤의 –module=log_data 부분은 아래의 쿼리실행의 결과를 지정한 log_data라는 모듈에 저장하겠다는 의미이다..

*데이터를 모듈에 저장하는 부분으로 “RUN”버튼을 클릭하여 실행하면 별 다른 에러가 없는 경우, 변화가 없으며 지정한 모듈에 저장이 된 것이다.

구글 차트로 시각화하기

이제 앞에서 저장한 모듈의 데이터를 이용해서 구글 차트를 생성하는 명령어를 이용해서 차트를 생성하여 시각화를 해보자. 구글 차트를 생성하기 위해 다음과 같이 진행한다.

빅쿼리에 SQL로 쿼리를 실행한 결과를 모듈에 저장하였다면, “+Add Markdown”으로 다음의 내용을 입력한다.

area 차트로 시각화하기

다음으로 “+Add Code”로 코드블럭을 생성하고 다음의 내용을 입력하고 “RUN”버튼으로 코드를 실행한다.

%%chart area --fields tstamp,rps --data=log_data

다음과 같이 차트가 생성되는 것을 확인할 수 있다.

구글 차트로 area 차트를 그리는 명령어로, --fields 부분은 앞서 모듈에 저장된 데이터 중에서 tstamp, rps 필드의 데이터를 읽는 부분으로,

여기에 정의한 필드를 읽어서 차트로 그려준다.

다음은 구글 차트로 제공되는 여러가지 유형의 차트로 시각화한 결과이니 참고하기 바란다.

1. pie 차트

2. columns 차트

3. line 차트

4. scatter 차트

마무리

지금까지 구글 컴퓨트 엔진의 VM에 nginx웹 서버를 구성하고 Fluentd로 실시간으로 웹 서버에서 발생한 액세스로그를 수집하고 이 데이터를 빅쿼리의 테이블에 저장하고 구글 스프레드 시트 및 구글 클라우드 데이터랩을 이용해서 쿼리를 수행하고 시각화하는 방법에 대해서 알아보았다. 지금까지 설명한 방법 이외에도 구글 클라우드 에서는 다양한 형태로 데이터를 수집하고 처리하고 시각화하는 데이터 분석을 위한 여러가지 서비스들을 제공하니

https://cloud.google.com/products/

https://cloud.google.com/solutions/

을 참고하길 바란다.

실습자원 정리하기

지금까지 실습에 사용한 VM, 빅쿼리 테이블 데이터 등의 리소스를 그대로 둘 경우 지속적으로 비용이 과금되고 Free Trial기간이 종료되면 유료 과금 될 수 있다. 따라서 실습에 사용한 자원 및 데이터를 정리하자.

1. 구글 클라우드 플랫폼의 자원(VM, 빅쿼리 테이블)을 정리하기 위해서 구글 클라우드 콘솔(Google Cloud Console)페이지로 접속해서 앞에서 사용한 프로젝트가 선택된 상태에서 아래와 같이 “Manage project setting” 버튼을 클릭한다.

2. 상단 메뉴에서 “DELETE PROJECT” 버튼을 클릭하고 지금까지 사용한 프로젝트를 삭제한다.

3. 구글 스프레드 시트 파일의 삭제는 위해 본인 계정의 구글 드라이브로 접속하여 해당 파일을 선택하고 마우스 오른쪽 버튼을 클릭하여 하단의 삭제 버튼을 클릭하여 삭제한다.

참고 자료

https://cloud.google.com/solutions/real-time/fluentd-bigquery

https://cloud.google.com/datalab/docs/quickstarts/quickstart-gcp-frontend

'Google Cloud Platform > Bigquery' 카테고리의 다른 글

빅쿼리 Streaming Insert golang 샘플 (0)	2017.04.24
빅쿼리 Query Plan을 이용한 쿼리 실행 분석 (0)	2016.09.19
빅쿼리의 셔플링 (0)	2016.08.23
빅쿼리 스트리밍 데이터 로딩하기 (4)	2016.08.05
빅쿼리 데이터 로딩하기 (2)	2016.07.25

빅쿼리 Query Plan을 이용한 쿼리 실행 분석

whitechoi 2016. 9. 19. 13:09

2016. 9. 19. 13:09

빅쿼리 Query Plan을 이용한 쿼리 실행 분석

최유석

개요

일반적으로 대부분의 데이터베이스의 SQL (MS-SQL, MySQL, Oracle, 등)에서 제공하는 Query Explain Plan(쿼리 실행 계획)기능은 사용자가 쿼리를 실행하기전에 쿼리를 분석할 수 있도록 쿼리가 실행되는 각각의 과정에 대해서 예상정보를 제공한다. 이 정보를 통해 해당 되는 쿼리에 대해서 성능에 대한 향상, 실행 오류 방지 등 쿼리를 실행하기 이전에 최적화 할 수 있게 도와준다. 빅쿼리에서도 이와 유사한 기능으로 Query Plan(쿼리 계획)을 제공한다. 이글에서는 빅쿼리의 Query Plan을 통해 쿼리 실행을 분석하는 방법에 대해서 알아보고자 한다.

빅쿼리와 다른 SQL엔진의 차이점

앞에서 언급한 것처럼 다른 SQL엔진은 쿼리를 실행하기 전에 해당되는 쿼리에 대한 예상 실행정보를 각각의 SQL에서 제공하는 Explain(각각 제공하는 이름은 다르지만 일반적인 의미)문을 적용해서 쿼리를 실행하고 분석해서 쿼리 튜닝을 하게 된다. 하지만 빅쿼리는 다른 SQL엔진과 다른 방식으로 실행이 완료된 쿼리에 대해서 쿼리 실행의 각 단계에 대한 정보를 메타데이터 형태로 제공한다. 또한 일반적인 Explain문이 빅쿼리에 따로 없기 때문에 직접 실행할 수 없으며, 실행이 완료된 쿼리에 대해서 Query Plan정보를 자동적으로 제공해준다.

빅쿼리의 부담 없는 쿼리 실행

빅쿼리는 구글 클라우드 플랫폼의 관리형 서비스로 구글에서 전체적인 시스템에 대한 관리를 하기 때문에 사용자 입장에서는 빅쿼리에 데이터를 저장하고 분석하는 등, 빅쿼리가 제공하는 서비스를 사용하기만 하면 된다. 따라서 일반적인 데이터베이스처럼 잘못된 쿼리로 인한 오류로 인해 데이터베이스에 문제를 일으키는 경우에 대해서 걱정 할 필요가 없다. 단지 쿼리가 실패할 뿐이며, 바로 다시 쿼리를 수정해서 실행할 수 있다. 또한 쿼리를 실행하기전에 쿼리에 대한 Validation (유효성 검사)기능을 제공하기 때문에 잘못된 쿼리의 경우 대부분 여기에서 걸러진다. 추가적으로Validation 을 통해 쿼리오류를 확인하지 못했다고 하더라도 쿼리가 잘못되거나 기타 다른 문제로 쿼리가 실패해서 오류가 발생되는 경우, 실패한 쿼리에 대해서는 비용이 부과되지 않기 때문에 부담이 적다.

빅쿼리에서 쿼리 실행 분석에 대한 접근

빅쿼리의 아키텍처를 보면 일반적인 데이터베이스에서 제공하는 Key, Index가 없으며, 기본적으로 쿼리요청의 대상이 되는 각각의 열에 대해서 Full Scan이다. 따라서 일반적으로 고려하는 Key, Index 기반의 쿼리 튜닝으로 방향이 아닌, 빅쿼리의 Query Plan에서 제공하는 메타데이터를 토대로 쿼리 실행단계에서 소요된 작업대기, 읽기, 쓰기, CPU연산 등에 대한 상대적인 시간 비율을 기준으로 실행 된 쿼리에 대한 분석 및 최적화 방향을 고려해야 한다.

*사실 빅쿼리의 쿼리 최적화는 쿼리실행이 열에 대한 풀스캔이고, 거기에 대한 비용이 발생하는 구조기 때문에 쿼리 비용자체를 감소시키기는 어렵다. 주요한 방향으로 생각해야 할 부분은 쿼리 실행 성능의 향상이다.

빅쿼리의 Query Plan 인터페이스

빅쿼리 웹 콘솔(Web UI)

빅쿼리 웹 콘솔(Web UI)에서는 쿼리를 실행하고 나서 다음과 같이 Explanation 버튼을 클릭하면 쿼리 실행에 대한 각각의 스테이지로 정보가 간략하게 나타나고 각각의 스테이지에 있는 세모모양의 아이콘을 클릭하면 각각의 스테이지 별로 쿼리 실행의 세부정보가 나타난다.

API

빅쿼리에서 API형태로 제공하는 Query Plan 정보는 실행이 완료된 쿼리 결과에 대해서 자동으로 해당 쿼리 Job의 리소스로 포함되어 7일간 보관된다. 이 기간 안에 Job.get() 메소드를 호출하게 되면 응답 결과에 포함 된 JSON 배열 형식의 queryPlan 속성에서 상세한 쿼리 실행 정보를 얻을 수 있다.

빅쿼리 Query plan의 메타데이터

STAGE 메타데이터

API속성	Web UI	설명
Id	Stage x	스테이지의 고유한 정수 ID (해당 Query Plan 범위 한정)
recordsRead	Input	스테이지에서 읽은 행의 수
recordsWritten	Output	스테이지가 작성한 행의 수

상대적인 시간비율에 따른 메타데이터

API속성	Web UI	설명
waitRatioAvg		작업 대기에 소요된 평균시간
waitRatioMax		작업 대기에 소요된 최대시간
readRatioAvg		입력데이터의 읽기에 소요된 평균시간
readRatioMax		입력데이터의 읽기에 소요된 최대시간
computeRatioAvg		CPU연산에 소요된 평균시간
computeRatioMax		CPU연산에 소요된 최대시간
writeRatioAvg		출력데이터의 쓰기에 소요된 평균시간
writeRatioMax		출력데이터의 쓰기에 소요된최대시간

*빅쿼리 웹 콘솔에서는 AVG, MAX는 따로 표시되지 않는다. 또한, 정확한 수치를 확인하기 위해서는 API를 이용한 접근이 필요하다.

간단한 쿼리를 이용한 기본적인 메타데이터 정보 확인하기

먼저 다음의 쿼리를 빅쿼리 웹 콘솔에서 실행하고, Query Plan에 대한 메타데이터 정보를 확인해보자.

SELECT COUNT(*)
FROM [publicdata:samples.shakespeare]
WHERE corpus = 'hamlet'

쿼리를 실행하고 Explanation 탭을 클릭하면 다음과 같이 Query Plan에 대한 정보를 확인 할 수 있다.

먼저 좌측의 Stage 뒤의 1, 2가 실행 된 해당 쿼리에 대한 고유한 ID이며,우측 Input, Output 아래의 수치가 해당 쿼리에 대한 각각의 스테이지에서 읽거나 기록한 행의 수이다. 중간의 Stage timing 아래에 나타나는 부분이 해당 쿼리가 실행되는 각각의 스테이지에서 [스케줄에 대한 작업대기, 읽기, CPU연산, 쓰기]에 의해서 소요된 시간 비율들을 나타내며, 위의 쿼리의 경우 실행 데이터의 크기가 작기 때문에 각각의 상대적인 시간비율들의 평균값과 최대값이 같게 나타난다.

이번에는 다음과 같이 시간비율의 편차를 보기 위해 쿼리의 대상이 되는 데이터의 크기가 크고 쿼리 연산이 복잡한 경우를 살펴보자.

SELECT language, SUM(views) as views

FROM [bigquery-samples:wikipedia_benchmark.Wiki1B]

WHERE regexp_match(title,"G.*o.*o.*g")

GROUP by language

ORDER by views DESC

위의 결과에서 보듯이 스테이지1에서 데이터의 읽기와 CPU연산에 소요된 시간 비율의 평균과 최대치의 차이가 발생하는 것을 확인할 수 있다.

*사용자가 실행하는 쿼리와 대상이 되는 데이터의 크기, 분포 등에 따라서 위와 같이 쿼리 실행 단계의 각각의 상대적인 시간비율들의 평균, 최대 값의 차이가 발생하게 된다.

STEP(스테이지의 세부 단계)의 메타데이터 정보

Query Plan에서 제공하는 각각의 스테이지 내부에서의 확인 가능한 세부적인 메타데이터 정보는 다음과 같다.

STEP(세부 속성)	설명
READ	입력 테이블 또는 중간 결과에서 하나 이상의 열을 읽는다.
WRITE	출력 테이블 또는 중간 결과에 대해 하나 이상의 열을 기록한다.
COMPUTE	대부분의 식에 대한 모든 계산 및 내장된 함수의 호출한다
FILTER	WHERE, OMIT IF, HAVING절을 구현하는 연산자이다.
SORT	정렬 연산을 나타내며 열의 키와 정렬방향을 포함한다.
AGGREGATE	집계연산(예:GROUP BY)을 나타내며, 여러 단계로 분할된다.
LIMIT	LIMIT절을 구현하는 연산자이다.
JOIN	JOIN연산을 나타내며, JOIN의 종류와 JOIN에 사용되는 열의 정보를 포함한다.
ANALYTIC_FUNCTION	분석 기능(CF. window functions)을 호출한다.
USER_DEFINED_FUNCTION	사용자 정의 함수를 호출한다

간단한 쿼리를 이용한 상세한 메타데이터 정보 확인하기

앞서 메타데이터 정보를 확인하기 위해 처음 실행 했던 쿼리를 이용해서 Query Plan의 각각의 세부적인 단계(STEP)에 대한 정보를 빅쿼리 웹 콘솔(Web UI)과 API를 통해서 각각 확인해보자.

빅쿼리 웹 콘솔(Web UI) 에서 STEP정보 확인하기

앞서 간단하게 메타데이터 정보를 확인하기 위해 처음 실행 했던 쿼리의 Explanation을 통해서 쿼리 실행의 각각의 단계(STEP)에 대한 정보를 확인해보자. 실행 된 쿼리의 Explanation에서 각각의 스테이지 좌측에 나타나는 삼각형모양의 아이콘을 클릭하면 각각의 스테이지에 대한 세부적인 메타데이터 정보를 확인 할 수 있다.

먼저 스테이지 1, 2에서 READ, AGGREGATE, WRITE스탭을 포함하고 있는 것을 볼 수 있다.

스테이지1에서는 READ스탭에서 [publicdata:samples.shakespeare] 테이블의 대상이 되는 "corpus" 열을 읽어서 값이 "hamlet"인 행을 찾는다. 그 다음 AGGREGATE스탭에서 앞서 읽은 행을 카운트하고 WRITE에서 __stage1_output 이라는 식별자에 기록을 한다.

스테이지2에서는 스테이지1에서 __stage1_output에 기록한 정보를 받아서 READ스탭에서 스테이지1의 작업의 카운트한 정보를 읽고 AGGREGATE스탭에서 카운트 된 정보에 대한 합산을 하고 WRITE스탭에서 합산한 결과를 출력한다.

API를 이용해서 STEP정보 확인하기

구글에서 제공하는 APIs Explorer을 사용해서 빅쿼리의 Query Plan정보를 간단하게 API를 호출하고 결과를 확인해보도록 하자.

Job ID 확인

앞서 언급한 것처럼, 빅쿼리 Query Plan정보는 실행된 쿼리에 대한 결과만 확인 할 수 있으며, Query Plan정보를 얻기 위해서는 실행된 쿼리에 대한 정보 중 하나인 Job ID가 필요하다. 빅쿼리 웹 콘솔(Web UI)에서 좌측상단 Query History클릭하고 앞서 실행한 쿼리정보에 대한Job ID의 값을 확인한다.(복사하거나 창을 열어둔다.)

*빅쿼리 웹 콘솔의 Query History에서 Job ID 는 Project ID:Job ID 형식으로 되어있으니, job.get() 메소드를 호출할 때는 각각 나눠서 입력해야 한다.

Google APIs Explorer 페이지 접속

다음으로 APIs Explorer에서 빅쿼리 Job.get()메소드 테스트 페이지인 다음의 주소로 접속한다.

https://developers.google.com/apis-explorer/#p/bigquery/v2/bigquery.jobs.get

권한 부여

빅쿼리의 Job.get() 메소드를 사용하기 위해서는 빅쿼리에 대한 읽기 또는 전체 관리권한이 필요하다. 따라서 해당되는 권한을 임시로 부여하기 위해 우측의 off로 되어있는 버튼을 클릭하고 해당되는 권한범위를 체크하고(필요한 범위는 기본값으로 체크되어 있다.) Authorize를 클릭해서 실행 권한을 부여한다.

Job.get()메소드를 사용하기 위한 권한 범위URI는 위의 화면에서 체크된 항목과 같다.

*한 가지만 허용해도 Job.get() 메소드를 사용할 수 있다.

Job.get() 메소드 실행하기

API 사용에 대한 권한범위를 허용하고 버튼이 ON되었으면 해당 프로젝트 ID와 위에서 확인한 Job ID를 입력하고 Execute버튼을 클릭해서 실행한다.

Job.get() 메소드 실행결과 확인

아래에 실행된 결과를 보면 Job.get()메소드에 대해서 GET으로 성공적으로 요청이 이루어졌으며, 응답된 결과로 해당 Job에 대한 모든 정보를 나타내며(해당 속성만 필드를 지정해서 실행할 수 있다.), 쿼리 실행 정보를 얻기 위한 queryPlan속성은 statistics속성의 하위 속성인 query안에 포함되어 있다.

*속성 관계: statistics > query > queryPlan

"queryPlan": [

{

"name": "Stage 1",

"id": "1",

"waitRatioAvg": 1,

"waitRatioMax": 1,

"readRatioAvg": 0.8650635652173914,

"readRatioMax": 0.8650635652173914,

"computeRatioAvg": 0.47558847826086953,

"computeRatioMax": 0.47558847826086953,

"writeRatioAvg": 0.19849230434782608,

"writeRatioMax": 0.19849230434782608,

"recordsRead": "164656",

"recordsWritten": "1",

"steps": [

{

"kind": "READ",

"substeps": [

"corpus",

"FROM publicdata:samples.shakespeare",

"WHERE EQUAL(corpus, 'hamlet')"

]

{

"kind": "AGGREGATE",

"substeps": [

"COUNT_STAR() AS f0_"

]

{

"kind": "WRITE",

"substeps": [

"f0_",

"TO __stage1_output"

]

}

]

{

"name": "Stage 2",

"id": "2",

"waitRatioAvg": 1,

"waitRatioMax": 1,

"readRatioAvg": 0,

"readRatioMax": 0,

"computeRatioAvg": 0.05080473913043479,

"computeRatioMax": 0.05080473913043479,

"writeRatioAvg": 0.12621304347826087,

"writeRatioMax": 0.12621304347826087,

"recordsRead": "1",

"recordsWritten": "1",

"steps": [

{

"kind": "READ",

"substeps": [

"f0_",

"FROM __stage1_output AS publicdata:samples.shakespeare"

]

{

"kind": "AGGREGATE",

"substeps": [

"SUM_OF_COUNTS(f0_) AS f0_"

]

{

"kind": "WRITE",

"substeps": [

"f0_",

"TO __output"

]

}

]

}

앞서 언급한대로 쿼리 실행의 상대적인 시간 비율들의 정확한 수치를 포함해서, 빅쿼리 웹 콘솔(Web UI)에서 확인한 정보와 동일한 쿼리 실행 정보를 확인 할 수 있다.

*Job.get()메소드의 Query Plan정보를 확인할 수 있는 queryPlan속성은 기록된 정보의 출력만 가능하다.

Query Plan결과 해석 및 해결 방안

Query Plan의 결과에 대해서 앞서 언급한대로 작업대기, 읽기, CPU 연산, 쓰기의 상대적인 시간 비율을 토대로 해석이 이루어져야 한다. 따라서 Query Plan 정보를 통해 쿼리의 문제를 분석하고 해결하는 방법을 알아보도록 하자.

평균 및 최대 시간 사이에 큰 차이가 발생하는 경우

평균, 최대시간의 차이가 발생하는 이유는 데이터가 고르지 못하게 비대칭 분포된 경우이다. 이러한 비대칭 분포를 가지는 데이터들은 불균형하게 분포 되어 있기 때문에 쿼리 실행 시 오버헤드를 발생시켜 쿼리가 실행되는 속도를 느리게 만들 수 있다.

평균, 최대 시간의 차이가 발생하는 일반적인 원인으로 쿼리에서 JOIN, GROUP BY, PARTITION 절에서 사용할 데이터가 NULL, empty, default 값을 포함하고 있는 경우이다. 따라서 이미 데이터가 고르게 분포되어 있지 않기 때문에 쿼리를 실행 하는 과정에서 편차가 발생한다.

트러블 슈팅방법으로 TOP COUNT문에 중간결과를 넣어서, 해당 쿼리 데이터에서 가장 일반적인 값을 분산된 데이터의 Key로써 확인한다.

* 빅쿼리는 Key가 없다

데이터의 비대칭을 완화하기 위해서는 필터링을 적용해서 NULL, empty, default값과 같은 쿼리에서 불 필요한 데이터를 감소시켜야 한다. 필터링을 하기위한 방법으로는 고르지 못한 값들에 대해서 쿼리를 실행하고, 나머지 값에 대해서 쿼리를 실행한다.

추가적인 방법으로 고르지 못한 데이터를 작은 크기로 세분화하는 방법을 고려한다. 이 경우 세분화 한 데이터 그룹들을 재결합할 때 집계 연산자 사용의 주의가 필요하다.

예) 비대칭분포를 가지는 불균형 데이터 세분화

세분화 전

SELECT ... GROUP BY a

세분화 후

SELECT ... FROM (SELECT ... (GROUP by a, b) GROUP BY a)

중간 스테이지에서 읽기 또는 쓰기에 대부분의 시간이 소요된 경우

이전 스테이지에서 데이터가 예상보다 많이 생성되는 경우로, 이전 스테이지에서 필터링과 JOIN연산을 사용해서 생성되는 데이터의 크기를 줄인다.

입력테이블의 읽기에 대부분의 시간이 소요된 경우

입력테이블 읽기가 비용이 큰 경우이다. 따라서 명확한 필터를 사용하여 불 필요한 데이터의 크기를 감소시키는 방법과 테이블에 대한 파티셔닝, 즉 작은 테이블들로 분할을 해서 대상이 되는 테이블 크기를 줄이는 방법을 통해 쿼리의 성능 향상을 기대해 볼 수 있다.

스케줄의 대기에 대부분의 시간이 소요된 경우

쿼리의 스케줄에 많은 작업이 포함되어 있는 경우이다. 만약 작업 시간 자체가 중요한 경우가 아니라면 기다린다. 즉시 작업이 완료 되어야하는 등의 작업 시간이 중요한 경우는 빅쿼리의 slots을 추가해서 사용하는 것을 고려해본다. 여기서 말하는 slots는 CPU연산이 이루어지는 서버계층을 의미한다.

출력결과를 쓰는데 대부분의 시간이 소요된 경우

처음 입력된 데이터를 읽은 결과 보다 쿼리가 실행되면서 더 많은 데이터를 내보내는 경우로 볼 수 있으며, 출력하기 전에 일부의 데이터의 필터링으로 불 필요한 데이터의 양을 줄여본다.

CPU 연산에 대부분의 시간이 소요된 경우

CPU의 연산에 시간이 많이 소요되는 쿼리의 경우, I/O보다 쿼리 실행과정에서 데이터의 변환 및 프로세싱에 더 많은 시간이 소요되는 경우로 볼 수 있다. 보통 사용자 정의 함수(User defined functions) 또는 JSON데이터, 정규표현식, 등을 포함하는 복잡한 쿼리에서 많이 발생하기 때문에, 필터링을 통해 복잡한 쿼리에서 사용되는 데이터의 크기를 줄여야 한다. 또한 자주 사용되는 쿼리라면, 일부 식에 대한 사전 연산등을 고려하는 것도 하나의 방법이다.

결론

앞서 빅쿼리의 Query Plan(쿼리 계획)을 통해 쿼리 실행을 분석하는 방법에 대해서 알아보았다. 빅쿼리가 일반적인 데이터베이스 시스템들과 다른 구조를 가지고 있고 실행된 쿼리에 대해서만 Query Plan정보를 제공하기 때문에 제한적인 부분은 있다. 실행 한 쿼리에 대해서 Query Plan의 상대적인 시간비율을 지표로 세부적인 스탭을 고려해서 데이터의 비대칭 분포를 완화시키거나, 불 필요한 데이터의 크기를 줄여서 성능을 향상 시키는 방법이 주요한 쿼리의 최적화 방향으로 보여진다.

참고자료

https://cloud.google.com/bigquery/query-plan-explanation

https://cloud.google.com/bigquery/docs/reference/v2/jobs/get

https://cloud.google.com/bigquery/docs/reference/v2/jobs

'Google Cloud Platform > Bigquery' 카테고리의 다른 글

빅쿼리 Streaming Insert golang 샘플 (0)	2017.04.24
웹로그 데이터를 빅쿼리를 이용하여 리포팅 하기 (0)	2017.01.09
빅쿼리의 셔플링 (0)	2016.08.23
빅쿼리 스트리밍 데이터 로딩하기 (4)	2016.08.05
빅쿼리 데이터 로딩하기 (2)	2016.07.25

빅쿼리의 셔플링

whitechoi 2016. 8. 23. 18:01

2016. 8. 23. 18:01

In-memory query execution in Google BigQuery

빅쿼리의 In-memory query 실행

최유석

원글 주소 : https://cloud.google.com/blog/big-data/2016/08/in-memory-query-execution-in-google-bigquery

원글 작성자 : Hossein Ahmadi, BigQuery Technical Lead

개요

빅쿼리는 대규모의 데이터에 대해서 실시간에 가까운 쿼리실행 속도를 제공한다. 빅쿼리는 높은 성능을 제공하기 위해서 모든 연산이 메모리에서 이루어진다. 이러한 쿼리 실행 속도의 배경에는 빅쿼리의 심플한 아키텍쳐 구조와, 빠른 쿼리 실행이 가능하게 하는 메모리 기반의 연산, 그리고 페타바이트 급의 분석이 가능하게 하는 확장성 있는 데이터의 재분할(repartitioning), 셔플(shuffle) 기능이 있다. 이 글에서는 빅쿼리의 셔플(shuffle)에 대해서 자세히 알아보고, 구글의 페타비트급(petabit-scale) 네트워킹 기술(Jupiter)을 활용해 어떻게 높은 성능으로 메모리상에서 쿼리 실행을 가능하게 하는지 알아보고자 한다.

구글의 빅데이터 분석

시작하기에 앞서 구글의 경우, 구글의 수많은 서비스들(YouTube, Gmail. Search,등) 각각의 이용자들만 해도 수억 명에 이른다. 따라서 자연스럽게 구글 내부적으로 빅데이터를 분석하기 위한 기술들이 개발되고 사용되어 왔다. 구글은 오픈소스 플랫폼을 지향하는 모습으로 빅데이터를 포함한 여러 기술들의 논문을 공개해왔다. 몇가지 사례를 보면, Apache Hadoop의 근간이 되는 GFS와 MapReduce, Apache HBase의 근간이 되는 Big Table등이 있으며, BigQuery는 2010년 문서로 공개된 Dremel이라는 기술을 근간으로 한다.

빅쿼리의 성능과 인프라

빅쿼리의 쿼리 실행의 대한 내용을 알아보기에 앞서, 먼저 예제를 통해 빅쿼리의 성능과 인프라에 관련한 부분을 알아보도록 하자.

Producer (producer_id) {
void SendRow(row, consumer_id) : Called to send a row to a given consumer
on behalf of this producer.
}
Consumer (consumer_id) {
string ReceiveRow() : Called to receive one row for this consumer.
}
Controller {
StartShuffle() : Called before any producers or consumers start sending or
receiving rows.
EndShuffle() : Called after all producers and consumers have successfully
sent and received all rows.
}

위의 API들은 공유 메모리의 개념을 제공하도록 설계되었기 때문에, 데이타 프로세싱 파이프라인상에서, 데이타를 파티셔닝하는데 범용적으로 사용될 수 있다.

빅쿼리 셔플(shuffle)의 개념

빅쿼리 셔플의 기본 동작은 다음의 그림과 같이 설명될 수 있다.

(출처 : https://cloud.google.com/blog/big-data/2016/08/in-memory-query-execution-in-google-bigquery )

빅쿼리의 셔플은 많은 Producer들이 효율적으로 원격지의 머신의 메모리에 데이타를 저장할 수 있도록 하며, Consumer역시 높은 처리량으로 동시 읽기가 가능하다. 특히 Producer는 인접한 메모리 블록에 생성된 행(rows)에 로그(Log)하고 색인(index)을 남긴다.

이 색인(index)은 Consumer가 해당 행을 효율적으로 검색하고 읽을 수 있도록 해준다

빅쿼리 셔플의 복합적인 동작

(출처 : https://cloud.google.com/blog/big-data/2016/08/in-memory-query-execution-in-google-bigquery )

재분할한 데이타를 메모리에 저장하는 특징이외에도, 빅쿼리 셔플은 또 다른 측면에서 MapReduce스타일의 셔플과 다르다. MapReduce 스타일의 셔플은 모든 행이 재정렬 된 다음에 데이타를 접근할 수 있는데 반하여 빅쿼리에서는 producers에 의해 셔플된 각각 행(rows)은 바로 workers에 의해 접근이 가능하다. 그래서 파이프 라인에서 분산된 작업을 수행 하게 할 수 있게 한다.

빅쿼리의 데이터 분할(partitioning)

데이터의 파티셔닝(partitioning)은 BigQuery의 쿼리의 성능에 상당한 영향을 미친다.

제대로 된 결과를 얻기 위해서는 Consumer와, Provider를 적절한 숫자로 맞추는 것이 중요하다. (빅쿼리가 자동으로 수행)

최적화 되지 않은 데이터의 분할로 쿼리가 매우 느리게 실행되거나, 심지어는 자원의 제약으로 실패할 수 도 있다.

빅쿼리의 파티셔닝은 데이터 크기, 백그라운드의 부하, 기타 요인에 기초하여 쿼리에 사용 된 연산자의 종류에 따라 파티셔닝(분할)을 지능적으로 선택하는 동적 분할 메카니즘을 사용한다. 이로인하여 빅쿼리는 데이타의 특정 분포나, 키에 따른 정렬에 따른 오버헤드 없이 임의의 데이터 셋에 대한 효율적인 쿼리 실행을 할 수 있게 한다.

빅쿼리에서 콜로서스를 활용한 이점

빅쿼리는 페타바이트의 데이터에 쿼리를 사용할 수 있다. 대부분의 케이스에서 빅쿼리는 인메모리 셔플링을 통하여 데이타를 재분할 하는데, 메모리만 사용할 경우, 연산 비용이 매우 크기 때문에, 이를 해결하기 위해서 콜로서스 파일 시스템에 경우에 따라 데이타를 메모리에서 부터 이동하여 저장한다.

디스크의 경우 메모리에 비해서 많이 느리기 때문에, 빅쿼리에서는 디스크 억세스를 최소화하는 방법으로 성능 문제를 최소화한다.

빅쿼리는 multi-tenant service 이다.

빅쿼리는 사용자가 인프라를 공유해서 사용하는 멀티 테넌트 (multi-tenant) 서비스로 모든 고객이 쿼리를 실행하기 위해 VM의 클러스터의 크기를 조정하고 배포하거나, 리소스(자원)에 대한 프로비저닝이 필요 없다. 그렇게 하기 위해 빅쿼리의 셔플은 메모리에서 대부분의 쿼리를 실행 할 수 있는 지능적인 메모리 자원 관리 시스템을 사용한다. 빅쿼리는 고객으로부터 발생하는 부하의 변동에 따라 즉각적으로 적응한다.

결론

모든 빅쿼리의 쿼리는 하나 또는 다수의 셔플 동작을 포함하고, 단일 행 데이터 또는 페타바이트의 쿼리 데이터를 전송하는데 동일한 인프라를 사용한다. 구글 네트워크 기술과 함께 긴밀한 통합(tight intergration)으로 만들어내는 빅쿼리 셔플의 극한의 유연성은 빅쿼리의 사용자가 모든 규모에서 빠른 데이터 분석을 할 수 있게 한다.

참고자료

https://cloud.google.com/blog/big-data/2016/08/in-memory-query-execution-in-google-bigquery

https://cloud.google.com/blog/big-data/2016/01/anatomy-of-a-bigquery-query

https://cloud.google.com/blog/big-data/2016/01/bigquery-under-the-hood

'Google Cloud Platform > Bigquery' 카테고리의 다른 글

빅쿼리 Streaming Insert golang 샘플 (0)	2017.04.24
웹로그 데이터를 빅쿼리를 이용하여 리포팅 하기 (0)	2017.01.09
빅쿼리 Query Plan을 이용한 쿼리 실행 분석 (0)	2016.09.19
빅쿼리 스트리밍 데이터 로딩하기 (4)	2016.08.05
빅쿼리 데이터 로딩하기 (2)	2016.07.25

빅쿼리 스트리밍 데이터 로딩하기

whitechoi 2016. 8. 5. 15:55

2016. 8. 5. 15:55

빅쿼리 스트리밍 데이터 로딩하기

최유석

개요

구글 빅쿼리에서는 비동기 처리방식인 Job을 통해서 데이터를 로딩하는 방법 외에도 실시간으로 데이터를 로딩 할 수 있는 방법으로 스트리밍(Streaming) API를 제공한다. 이 글에서는 구글 빅쿼리에서 제공하는 스트리밍 API를 이용해서 실시간으로 데이터를 로딩하는 방법을 알아보도록 하자.

스트리밍(Streaming) API

비동기 연산인 Job을 통한 데이터 로딩방식이 아닌 API형태로 tabledata().insertAll() 메소드를 제공하여 한번에 하나씩 레코드를 삽입하는 방법을 제공한다. 하나의 레코드에는 하나 또는 다수의 행을 포함할 수 있다.

*레코드는 빅쿼리가 지원하는 데이터 타입으로 하나 이상, 다수의 데이터 필드에 대한 일종의 집합이다.

*스트리밍 API를 이용한 데이터 로딩은 partition table에 적용되지 않는다.

스트리밍 로딩의 할당량 정책(quota policy)

스트리밍 API를 사용한 데이터로딩은 실시간으로 처리되는 부분으로 Job의 데이터로딩(batch) 방법에 비해서 처리 가능한 데이터의 크기와 행의 수가 제한적이다.

최대 행 사이즈 : 1MB
HTTP 요청 사이즈 제한 : 10MB
초당 최대 입력 가능 행 수 : 테이블 당 100,000 행, 프로젝트 당 1,000,000 행
요청당 최대 입력 가능 행 수 : 제한은 없으나, 최대 500 행을 권장
초당 최대 바이트 크기 : 100MB

*스트리밍 데이터 로딩 시에 위의 정책을 초과하는 경우에 에러가 발생한다. 따라서 그러한 경우는 Job을 통한 데이터 로딩을 권장한다.

데이터 일관성 보장

데이터에 대한 일관성을 보장할 수 있는 옵션으로 각각의 입력 행에 대해서 insertId를 적용할 수 있다. insertId는 행 입력 시 최소1분 정도 빅쿼리가 기억하고 있게 된다.

*이미 입력된 행에 대해서 insertId를 통해 중복된 데이터는 추가로 입력되지 않으며, 만약 변경된 데이터(행)를 입력하면 해당 insertId를 가진 데이터(행)는 변경된 데이터(행)로 입력된다.

템플릿 테이블

templateSuffix 속성을 통해서 지정한 대상 테이블을 기본 템플릿으로 하여 같은 스키마를 가진 새로운 테이블을 자동으로 생성할 수 있다.

templateSuffix의 값은 사용자가 임의로 설정할 수 있다.

<대상 테이블> + <templateSuffix> 형태로 새로운 테이블이 생성된다.

Tabledata: insertAll정의

Tabledata는 API형태로 제공되는 Resource type으로 스트리밍 데이터 로딩을 지원하는 insertAll 메소드를 포함하고 있다.

HTTP 요청(request) 기본형식

POST https://www.googleapis.com/bigquery/v2/projects/projectId/datasets/datasetId/tables/tableId/insertAll

POST요청으로 각각 해당되는 project, dataset, table ID를 설정하면 된다.

insertAll 권한 범위

스트리밍 요청을 할 때 VM인스턴스가 위의 각각의 권한 범위를 나타내는 URI중에서

적어도 하나의 범위의 권한을 가지고 있어야 한다.

Google API 접근 범위에 대한 참고 https://developers.google.com/identity/protocols/googlescopes

Request body 구조

{
"kind": "bigquery#tableDataInsertAllRequest",
"skipInvalidRows": boolean,
"ignoreUnknownValues": boolean,
"templateSuffix": string,
"rows": [
{
"insertId": string,
"json": {
(key): (value)
}
}
]
}

필수(Required) 속성

kind : 응답의 자원 유형 - 사용하는 프로그래밍 언어에 따라서 직접적으로 사용이 필요한 경우가 있다.
rows[] : 삽입할 데이터(행)들을 포함한다.
rows[].json :
json형태로 데이터를 입력 가능하다. key(스키마): value(입력 값)의 구조를 가지며, 대상 테이블의 스키마 구조에 대응되는 형태로 스키마와 값을 입력하여야 한다. 하나이상의 행에 대한 입력이 가능하다.

선택적(Optional) 속성

rows[].insertId : 앞서 설명한 데이터의 일관성을 보장하기 위한 옵션이다.
templateSuffix : 앞서 설명한 것처럼, String 타입의 값으로 사용자가 임의의 값을 설정하여 데이터 로딩을 실행하면 <대상 테이블> + <templateSuffix>형태로 새로운 테이블이 자동생성되고 해당 요청에 대한 데이터가 로딩된다.
skipInvalidRows : 적합하지 않은 행이 존재하더라도 모든 유효한 행의 데이터를 삽입한다. boolean형으로 기본값은 false이다.
ignoreUnknownValues: 테이블의 스키마와 일치하지 않는 유형의 값은 무시된다. boolean형으로 기본값은 false이다.

Response body구조

kind : 응답 자원 유형을 나타낸다. - 사용하는 프로그래밍 언어에 따라서 직접적으로 사용이 필요한 경우가 있다.
insertErrors[] : 실패한 요청의 경우 해당 오류에 대한 내용을 json배열형태로 반환한다.

※앞서 스트리밍 데이터 로딩을 위한 기초적인 내용을 설명하였으며, Node.js를 활용하여 실제로 스트리밍으로 데이터를 로딩하여 보도록 하자.

VM환경 준비하기

프로젝트 선택

기존의 프로젝트를 선택하거나,

또는 새로 생성하고 진행한다.

VM준비(VM 생성 or 기존의 VM사용)

스트리밍 API를 사용해서 데이터로딩을 하기위해서 리눅스 기반의 VM인스턴스를 준비한다.

주의할 점으로 해당 VM은 앞서 설명한, tabledata().insertAll() 메소드를 접근해서 사용 할 수 있는 범위의 권한을 가지고 있어야 한다.

이글에서는 기존의 VM을 사용했으며, 테스트에 사용한 VM의 정보는 다음과 같다.

Name : test
Boot disk : Ubuntu 14.04 LTS / SSD 10GB

*리눅스 기반의 각자가 사용하기 편리한 다른 OS이미지를 포함하여, disk의 용량이나 타입도 각자의 환경에 맞춰서 적용해도 상관없다.

Access scopes : Allow full access to all Cloud APIs

편의를 위해 구글 클라우드의 모든 서비스에 Access 가능한 범위를 지정하였다.

*앞서 언급한 tabledata().insertAll() 메소드를 사용할 수 있는 권한 범위를 적용하여야 한다.

나머지는 기본값으로 설정

선택사항

Firewalls : Allow HTTP traffic, Allow HTTPS traffic 체크

이 글에서는 HTTP, HTTP(S)의 방화벽 규칙을 설정하고 진행한다.

*단순 스트리밍 로딩을 위해서는 따로 HTTP(S)트래픽을 허용하지 않아도 위의 다른 GCP서비스에 대한 접근 범위 설정을 통해 Streaming API에 사용에 대한 권한을 가지게 된다. 때문에 무리없이 실행은 가능하다. 하지만 실제 어플리케이션 구성에서는 구성환경에 맞춰서 HTTP, HTTP(S)에 대한 트래픽을 허용해기 위해 방화벽 규칙을 설정해야 될 것이다.

VM인스턴스 접근 범위 확인하기

Access scopes에 대한 적합한 설정이 되어있어야 한다는 것만 명심하자.

또한 Access scopes는 VM을 생성 할 때만 적용 가능하다.

VM 신규 생성 시 주의사항

Identity and API access에서 적합한 Access scopes를 설정하고 생성해야 한다.

BigQuery API 활성화

스트리밍 데이터 로딩을 실행하기 위해서는 BigQuery API를 활성화 해야 한다. 다음의 BigQuery API페이지로 이동한다.

https://console.developers.google.com/apis/api/bigquery

상단의 ENABLE를 클릭하여 BigQuery API를 활성화 한다.

만약, 이미 BigQuery API가 활성화 되어 있다면 다음 단계로 바로 이동한다.

Google Cloud SDK설치

기본적으로 구글 클라우드 SDK(command line interface) 가 설치되어 있다는 가정하에 진행한다.

만약 설치가 안되어 있다면 다음의 주소를 참고하여 설치하도록 한다.

https://cloud.google.com/sdk

Auth 인증

다음으로 Default Credentials정보를 인증하기 위해 SSH터미널에 접속한다.

auth인증을 위해 다음 명령어를 실행한다.

%gcloud auth login

만약 위의 명령어로 진행이 되지 않는다면 아래 명령어를 입력하여 진행한다.

% gcloud auth application-default login

위의 명령어를 입력하고 y를 입력하고 아래에 나타난 링크를 복사하여 브라우저에서 해당 링크로 접속한다.

해당 계정을 선택하면 다음과 같은 화면이 나타나고 허용을 클릭한다.

다음과 같이 인증코드가 생성된다.

인증코드를 복사하여 앞서 코드를 검증하는 부분에 붙여 넣고 엔터를 입력하면

Default Credentials 인증이 성공한 것을 확인할 수 있다.

Node.js 환경 준비하기

이번 예제에서는 기본적으로 node.js와 npm이 설치되어 있다고 가정하고 진행한다.

이 글은 node.js 4.4.7버전과 npm 2.15.8버전을 기준으로 작성하였다.

또한 root계정으로 진행한다. 편의를 위해 express프로젝트로 진행한다.

Express generator 설치

Express generator설치를 통해서 기본적인 Express 프로젝트의 구조로 생성하기 위해 다음의 명령어를 실행한다.

% npm install express-generator -g

Express 프로젝트 생성

Express 프로젝트를 생성하기 위해 다음의 명령어를 실행한다.

% express --session --ejs --css stylus test

다음 명령어를 실행하여 프로젝트 생성을 마무리한다.

% cd test && npm install

Express 프로젝트 및 프레임워크에 대해서는 http://bcho.tistory.com/887

http://bcho.tistory.com/888 글을 참고하기 바란다.

Bigquery API Client Library 설치

구글 빅쿼리는 npm으로 node.js Library를 제공한다. 다음의 명령어를 실행해서 Bigquery API Client Library를 설치한다.

% npm install googleapis --save

스트리밍 데이터 로딩하기

이제 스트리밍 데이터 로딩을 위한 준비는 완료되었다. Node.js와 스트리밍 API를 사용해서 데이터 로딩을 해보도록 한다.

Streaming API 기본 예제 - Node.js

vi등의 편집기를 이용해서 app.js파일을 열고 다음과 같은 내용을 추가한다.

var google = require('googleapis');
var bigquery = google.bigquery('v2');

google.auth.getApplicationDefault(function(err, authClient) {
if (err) {
console.log('Authentication failed because of ', err);
return;
}
if (authClient.createScopedRequired && authClient.createScopedRequired()) {
var scopes = ['https://www.googleapis.com/auth/cloud-platform'];
authClient = authClient.createScoped(scopes);
}

var request = {
// TODO: Change placeholders below to appropriate parameter values for

the 'insertAll' method:

// Project ID of the destination table.
projectId: "",
// Dataset ID of the destination table.
datasetId: "",
// Table ID of the destination table.
tableId: "",
resource: {},
// Auth client
auth: authClient
};

bigquery.tabledata.insertAll(request, function(err, result) {
if (err) {
console.log(err);
} else {
console.log(result);
}
});
});

Node 예제 구성요소 안내

google.auth.getApplicationDefault() {}

앞서 Auth 인증한 Default Credentials 정보를 확인한다.

var scopes = ['https://www.googleapis.com/auth/cloud-platform'];
위에 설명한 것처럼, 스트리밍 로딩을 위한 VM생성 시에 가지고 있는 접근 범위 에 따라서 적합한 범위의 URI가 입력되어야 한다.

https://www.googleapis.com/auth/cloud-platform 은 구글 클라우드 플랫폼의 모든 서비스에 대한 Full Access 범위를 나타난다.

var request의 내부 내용을 살펴보면 기본적으로 확인 할 내용은 다음과 같다.

projectId: "",

""안에 해당하는 프로젝트 ID,

datasetId: "",

""안에 해당하는 데이터셋의 ID,

tableId: "",

""안에 해당하는 대상 테이블의 ID

resource: {} 에서 중괄호 안에 위에서 설명한 기본 Request body의 형식에 맞게 실제 입력할 데이터의 내용을 입력한다.

bigquery.tabledata.insertAll() {}부분에서 데이터 로딩이 실행되는 부분으로, 만약 에러가 있다면 에러에 대한 내용을 반환한다.

스트리밍 로딩 테스트

이제 예제 코드에 대한 대략적인 구조를 파악하였다면 스트리밍 API를 사용해서 실제 테이블에 데이터를 입력해보자.

테이블 정보

테스트에 사용할 테이블은 앞서 구글 스토리지를 활용한 데이터 로딩에서 생성한 csv_test 테이블을 사용하도록 한다. 테이블 csv_test는 다음과 같은 스키마를 가지고 있다.

Node.js 코드 작성하기

projectId: “사용자의 프로젝트ID”
datastId: “데이터를 입력할 테이블을 가지고 있는 데이터셋ID”
tableId: “실제로 데이터를 로딩 할 테이블Id”

resource: { 입력 할 데이터 }

위에서 설명한 request body의 형식으로 대상 테이블의 스키마에 대응되는 적합한 값을 입력해야 한다. 입력을 완료하고 파일을 저장한다.

데이터 로딩 실행

이제 앞서 작성한 내용의 데이터를 로딩하기 위해 다음 명령어를 실행해서 Node.js서버를 구동한다.

%npm start

해당 스트리밍 로딩 요청에 대한 tableDataInsertAllResponse로 응답이 완료됨을 확인할 수 있다. 응답이 완료된 것을 확인하고 ctrl+c를 입력하여 실행을 중지한다.

데이터 로딩 결과 확인하기

이제 빅쿼리 웹 콘솔(Web UI)로 이동해서 간단한 쿼리 실행을 통해 데이터가 정상적으로 입력되었는지 확인해보자.

쿼리 실행하기

좌측 상단의 COMPOSE QUERY 버튼을 클릭한다.

다음과 같이 쿼리를 입력하고 RUN QUERY 버튼을 클릭하여 쿼리를 실행한다.

SELECT word, word_count, corpus, corpus_date

FROM load_test.csv_test

WHERE word_count=1111

쿼리가 실행되었다. 앞서 입력한 데이터를 확인 할 수 있다.

템플릿 테이블을 이용한 테이블 자동생성

이제 앞서 설명한 templateSuffix속성을 활용해 csv_test 테이블을 템플릿으로 하여 같은 스키마를 가진 새로운 테이블을 생성해보자.

Node.js 코드 작성하기

vi편집기등으로 app.js파일을 열고 다음의 내용을 추가하고 파일을 저장한다.

templateSuffix: "String형의 임의의 값"

자동 테이블 생성 및 데이터 로딩 실행

템플릿 테이블을 활용해서 자동으로 새로운 테이블 생성 및 데이터로딩을 위해 node.js 서버를 실행한다.

%npm start

응답이 완료되었다. ctrl+c를 입력하여 실행을 중지한다.

자동 테이블 생성 및 데이터 로딩 결과 확인하기

이제 다시 빅쿼리 웹 콘솔로 이동해서 테이블이 자동으로 생성되고 입력한 데이터가 제대로 로딩되었는지 확인한다. (새로고침이 필요할 수 있다.)

먼저 자동 생성된 테이블 부터 확인한다. 앞서 설명한 것처럼 <대상 테이블> + <templateSuffix>의 형태로 csv_test1234라는 새로운 테이블이 생성되었다. 템플릿의 대상이 되는 테이블인 csv_test와 동일한 스키마를 가진다.

데이터 로딩 결과 확인하기 - 자동생성 테이블

이제 자동으로 생성된 테이블을 확인하였다면 해당 테이블에 스트리밍으로 입력한 데이터를 확인해보도록한다.

쿼리 실행하기

COMPOSE QURERY버튼을 클릭해서 다음과 같은 쿼리를 입력하고 RUN QUERY 버튼을 클릭하여 쿼리를 실행한다.

SELECT word, word_count, corpus, corpus_date

FROM load_test.csv_test1234

WHERE word_count=1111

쿼리가 실행되었다. 앞서 입력한 데이터를 확인 할 수 있다.

*날짜 단위 또는 각각의 사용자 단위 등으로 테이블을 분할하여 생성할 필요가 있을 때,

templateSuffix의 값에 따라서 자동으로 테이블을 생성하여 데이터를 입력할 수 있기 때문에 매우 유용한 기능이다.

자동 테이블 생성 참고사항

templateSuffix속성을 활용해서 자동으로 테이블 생성하고 데이터를 입력한 경우에 빅쿼리

웹콘솔에서 바로 Preview를 클릭하면 스트리밍 버퍼에서 데이터 로딩을 통해 입력한 데이터를

가지고 있어서 입력한 값이 바로 나타나지 않을 수 있다.

*스트리밍 데이터 입력에 수초가 소요될 수 있으며, 입력된 데이터를 복사하고,

내보내기에 사용 될 수 있기까지 90분까지 소요 될 수 있다

데이터 일관성 확인

앞서 insertId를 통해 데이터의 일관성을 보장해주는 부분에 설명하였다. 이제 실제로 insertId를 사용해 데이터가 어떻게 변화하는지 확인해본다.

Node.js 코드 작성하기

vi등의 편집기를 이용해서 app.js파일을 열고 다음의 내용을 추가하고, 입력할 데이터의 내용을 수정한다.

insertId: "String형의 임의의 값"

입력 데이터의 경우 앞서 테스트한 결과와 구분을 위해 다른 값으로 변경한다.

데이터 로딩 실행 - insertId

insertId가 적용된 데이터로딩을 하기 위해서 node.js서버를 실행한다.

%npm start

응답이 완료되었으면 빅쿼리 웹 콘솔로 이동해서 입력된 데이터를 확인한다.

데이터 로딩 결과 확인하기 - insertId

빅쿼리 웹 콘솔에서 COMPOSE QUERY를 클릭하고 다음의 쿼리를 입력한다.

SELECT word, word_count, corpus, corpus_date

FROM load_test.csv_test1234

WHERE word_count=2222

RUN QUERY를 클릭하여 쿼리를 실행하고 입력된 데이터를 확인한다.

쿼리 수행결과를 통해, 정상적으로 입력된 데이터를 확인 할 수 있다.

데이터 중복 입력 확인하기

다시 SSH터미널에서 ctrl+c를 입력하여 서버 실행을 중지하고 app.js의 내용을 변경하지 않고, 다시 한번 node.js서버를 실행한다.

데이터 중복 확인 - 쿼리 테스트

빅쿼리 웹 콘솔로 이동해서 위와 동일한 쿼리를 실행해서 결과를 확인한다.

쿼리를 실행하면 앞서 입력한 데이터(행)만 나타난다. 같은 insertId를 가지고 있기 때문에 중복된 데이터가 추가로 입력되지 않는다.

*insertId가 없는 경우, 스트리밍 로딩을 할 때 데이터의 중복에 관계없이 새로운 데이터(행)가 추가된다. 각각 테스트해보길 권장한다.

데이터 변경하기 - Node.js 코드 수정하기

SSH 터미널에서 node.js서버를 중지하고 vi등의 편집기로 app.js파일을 열고 데이터의 내용을 변경하고 저장한다.

데이터 변경 후 로딩 실행 - insertId

앞서 같은 insertId를 가진 상태에서 변경한 데이터를 로딩하기 위해 다음 명령어를 실행해서 node.js서버를 구동한다.

%npm start

응답을 확인하고 웹 콘솔로 이동해서 입력된 데이터를 확인해보자.

데이터 변경 입력 후, 로딩 결과 확인하기

기존에 데이터가 어떻게 변화하였는 지 확인하기 위해 빅쿼리 웹 콘솔에서 앞서 실행한 쿼리를 입력하고 결과를 확인한다.

SELECT word, word_count, corpus, corpus_date

FROM load_test.csv_test1234

WHERE word_count=2222

앞서 위의 쿼리 실행을 통해 데이터가 로딩된 결과를 확인했었다. 하지만 해당 쿼리가

실행되면 조회되는 데이터가 없음을 확인 할 수 있다

이번에는 변경한 데이터의 word_count값인 3333으로 WHERE 조건을 변경하고 쿼리를 실행해보자

SELECT word, word_count, corpus, corpus_date

FROM load_test.csv_test1234

WHERE word_count=3333

변경해서 입력한 데이터에 대한 조회결과가 나타난다. 이와 같이 같은 행에 대한 insertId가 적용된 상태에서 데이터가 변경되어 다시 입력되는 경우, 기존의 입력된 데이터는 변경된다.

*insertId의 경우 데이터의 일관성을 확인하고 보장하기 위해 좋은 수단이 될 수 있으나, 앞서 언급한 것처럼 1분정도만 보장되는 시간적인 제약이 있으니 참고하기 바란다.

결론

빅쿼리의 스트리밍 데이터 로딩의 경우, 몇가지 제약적인 부분은 있지만, 실시간으로 대량의 이벤트 로그 분석, 실시간으로 데이터와 연동하는 dashboard등에 활용하기 좋다. 또한 빅쿼리의 대용량의 데이터 분석 뿐만 아니라 실시간의 데이터 로딩, 분석을 융합적으로 활용하면, 급변하는 시장에서 빠르게 대응 할 수 있는 또 하나의 대안이나 수단으로 이용할 수 있을 것이다.

참고자료

https://cloud.google.com/bigquery/streaming-data-into-bigquery

https://cloud.google.com/bigquery/docs/reference/v2/tabledata/insertAll

https://cloud.google.com/bigquery/quota-policy#streaminginserts

'Google Cloud Platform > Bigquery' 카테고리의 다른 글

빅쿼리 Streaming Insert golang 샘플 (0)	2017.04.24
웹로그 데이터를 빅쿼리를 이용하여 리포팅 하기 (0)	2017.01.09
빅쿼리 Query Plan을 이용한 쿼리 실행 분석 (0)	2016.09.19
빅쿼리의 셔플링 (0)	2016.08.23
빅쿼리 데이터 로딩하기 (2)	2016.07.25

빅쿼리 데이터 로딩하기

whitechoi 2016. 7. 25. 08:58

2016. 7. 25. 08:58

구글 클라우드 시작하기

구글 빅쿼리 데이터 로딩하기

최유석

개요

구글의 대용량 데이터 분석 서비스인 빅쿼리에 구글 클라우드 스토리지를 활용하여 CSV, JSON형식의 데이터를 로드하고 테이블을 생성하는 방법에 대해서 알아보도록 한다.

또한,빅쿼리에서는 데이터 파일의 단일 업로드뿐만 아니라 병렬 업로드(다중파일 동시 업로드)도 제공한다.따라서, 이번 글의 뒤쪽에서는 단일CSV파일로 데이터로딩을 해보고, 그 CSV파일을 여러 개의 파일로 분할하여 병렬로 데이터를 로드해서 각각의 처리 속도를 확인해본다.

그리고 CSV 병렬 업로드를 위해 분할한 데이터를 이용하여 JSON, Avro포맷으로 변환하여 각각의 형식에 따라 동일데이터라도 데이터 로딩시간이 어떻게 달라지는지 확인해보도록 하자

구글 클라우드 스토리지(Google Cloud Storage : GCS)

구글 클라우드 스토리지는 구글 클라우드 플랫폼에서 지원하는 BLOB(Binary large object) Store로 구글 클라우드 플랫폼의 모든 서비스들과 연계하여 사용이 가능하다.구글 클라우드 플랫폼에서 제공하는 파일,미디어 등의 저장에 특화된 서비스이다.

GCS 스토리지 클래스

Standard:

높은 data availability(99.9%)와low latency을 가지고 있어서 데이터에 대한 빠른 응답속도(ms)를 보인다.따라서 데이터에 대한 빠른 접근,빈번한 접근이 필요한 경우에 사용하기 적합하다.활용하기 적합한 예로는 웹사이트 컨텐츠,모바일 또는 게임 어플리케이션, 등이 있다.

Durable Reduced Availability (DRA) :

Standard에 비해 약간 낮은 가격과 data availability(99%)를 가지고 있다.

데이터 백업,배치(batch) 작업에 사용하기 적합한 클래스이다.

Nearline : data archiving 이나 online backup, 재해복구(disaster recovery)용도로 사용하기 적합한 클래스로 가장 저렴한 비용으로 이용할 수 있다. 데이터 접근에 시간단위가 소요되는AWS의 glacier서비스등에 비해서 매우 빠른 속도로 초 단위(대략3초)로 다른 클라우드 업체들의 유사서비스들에 비해서 매우 높은 성능을 가진다.

*구글 클라우드 스토리지 클래스 모두 동일한 API를 사용하고 AWS의 S3와 API호환되서 API사용에 대한 부담이 적다.

GCS 기본 구성요소

프로젝트(Project) :

최상위 컨테이너로 구글 클라우드 스토리지의 모든 자원(resources)은 프로젝트 위에서 생성되고 관리된다. 또한 스토리지 자원들에 대하여 권한제어가 가능하다.

버켓(Bucket) :

데이터 또는 파일을 저장하고 관리하기 위한 컨테이너이다.구글 클라우드 스토리지에서의 최소단위인 Object(각각의 데이터 또는 파일단위)가 저장되는 공간이며, 버켓 생성 시에 앞서 설명한 클래스와 데이터가 저장될 위치를 지정하여야 하고 생성 후에는 변경이 불가능하다.새로 생성하거나 다른 버켓으로 이동시키는 등의 방법을 사용하여야 한다.

오브젝트(Object) :

실제로 구글 클라우드 스토리지에 저장되는 각각의 데이터 또는 파일을 의미한다. 하나의 오브젝트는 최대 5TB까지 저장할 수 있다.

빅쿼리 기본구조

*빅쿼리에 대한 개념, 아키텍쳐 등의 기본적인 이해가 필요하다면 아래 주소의 정보를 참고하기 바란다.

http://bcho.tistory.com/category/%EB%B9%85%EB%8D%B0%EC%9D%B4%ED%83%80/Google%20BigQuery

프로젝트(Project)

빅쿼리에서 프로젝트는 하위 구조인 데이터셋을 가질 수 있으며,하나의 프로젝트 안에는 다수의 데이터셋을 가질 수 있다. 또한 사용자와 데이터셋에 대한 권한 제어를 포함한 전반적인 리소스의 관리를 담당한다.

데이터셋(Dataset)

하나 이상의 테이블을 가질 수 있는 테이블에 대한 집합으로 테이블을 관리한다. 빅쿼리에서 권한제어를 할 수 있는 최소 단위로 데이터셋에 대한 권한부여를 통해 다른 사용자와 데이터를 공유 할 수 있다. (MySQL의 database와 비슷한 개념이다.)

테이블(Table)

실제 데이터가 저장되는 테이블이다. 스키마를 가지고 있으며 행(row)단위 업데이트는 지원되지 않는다.테이블에 대한 권한제어는 따로 적용되지 않으며 해당 테이블을 가지고 있는 부모 데이터 셋으로부터 상속받는다.

잡(Job)

잡은 쿼리, 데이터 로딩, 삭제, 복사, 내보내기 등의 명령을 수행하는 비동기식 연산으로 4가지 구성요소를 가지고 있다.

잡(Job)의 구성요소

○ Reference - job ID : 유니크 한 이름

○ Configuration - job task : 수행하는 작업의 종류

○ Status - job state : 오류와 경고를 포함한 상태

○ Statistics - job statistics : 수행내역을 제공하는 통계

잡의 구성요소들은 향후 감사(Audit)등의 목적을 위해서 로그를 남기게 된다. 또한 잡은 실행 도중 취소가 가능하다. 하지만 취소하였다 하더라도 해당 명령에 대한 프로세싱은 이루어지기 때문에 비용이 발생한다.

데이터 타입

STRING : UTF-8인코딩. 최대 2MB
BYTES : base64로 인코딩 된 이진 데이터
INTEGER : 64 bit signed integer
FLOAT : Double precision, floating-point format
BOOLEAN : CSV format: true or false (case insensitive), or 1 or 0

JSON format: true or false (case insensitive)

RECORD : A Collection of one or more field
TIMESTAMP

*데이터 타입 중 RECORD의 경우 중첩 및 반복을 허용하고 JSON과 같이 여러 개의 데이터 값을 가질 수 있다. 불필요한 반복 및 중첩이 많아지면 쿼리를 포함한 여러 가지 작업들이 어려워지니 가급적 피하는 게 좋다.

데이터 필드의 모드

REQUIRED : NULL값을 허용하지 않음
NULLABLE : NULL값을 허용(기본값)
REPEATED : NULL값을 허용하고 1개 또는 배열 형태로 여러 개의 값을 가질 수 있으며 JSON, Avro포맷에서만 사용가능하다.

데이터 로딩 포맷

CSV
JSON(newline-delimited)
Avro
Cloud Datastore backups
Google sheets

*스키마에 따라서 CSV, JSON, Avro 모두 flat data를 지원하지만, 스키마에 중첩되거나 반복되는 필드를 가진 경우 JSON, Avro만 지원한다.

*기본적으로 데이터에 줄바꿈(개행문자)이 포함되어 있는 경우 JSON, Avro가 빠르다.

데이터 로딩 지원도구(Tools)

Web UI : 웹 브라우저 기반으로 제공하는 빅쿼리 전용 웹 콘솔로 비전문가도 사용하기 쉽다.
Command-line interface (CLI) : 파이썬 기반의 명령어 도구가 제공된다.
REST API (POST) : POST요청으로 데이터 로딩을 할 수 있는 REST형태의 API를 제공하며, 재개 가능(Resumable)업로드, 다중 부분(Multipart) 업로드의 두가지 옵션을 제공한다.
Streaming API : 잡을 사용해서 대규모의 데이터를 로드하는 대신 한번에 하나의 레코드를 삽입할 수 있는 API를 제공한다.
Third-party ETL tools and connectors : 각종 3party의 ETL(Extract, Transform, Load) 툴 및 시각화, 개발환경 등과의 연동할 수 있도록 커넥터를 제공한다.

데이터 로딩(Load job)제한

*하루 기준으로 실패를 포함하여 최대 테이블 당 1,000개, 프로젝트 당 10,000개의 잡을 처리할 수 있다.

최대 행(row) 또는 cell 크기(데이터 포맷에 따라 구분)

Data Format	Max limit
CSV	2 MB (row and cell size)
JSON	2 MB (row size)
Avro	16 MB (block size)

*하나의 테이블 당 최대 10,000개의컬럼(columns)까지 생성 할 수 있다.

데이터 파일 최대크기 (데이터 포맷에 따라 구분)

File Type	Compressed	Uncompressed
CSV	4 GB	• With quoted new-lines in values: 4 GB • Without new-lines in values: 5 TB
JSON	4 GB	5 TB
Avro	Compressed Avro files are not supported, but compressed data blocks are. BigQuery supports the DEFLATE codec.	5 TB (2 MB for the file header)

* Avro포맷의 경우 파일에 대한 압축은 지원되지 않음

* 데이터 로딩동시 업로드 파일은 최대 10,000개까지 지원한다.

구글 클라우드 스토리지를 활용하여 CSV, JSON 형식의 데이터 로딩하기

프로젝트지정

기존의 프로젝트를 선택하여 진행하거나

새로 생성해서 진행한다.

구글 클라우드 스토리지에 데이터 업로드하기

구글 클라우드 스토리지에서 CSV, JSON파일을 업로드하여 사용하기 위해 좌측상단 탭의 스토리지 메뉴로 이동한다.

버켓(bucket)생성하기

Name : load-bigquery (임의의 값)
Storage class : Standard (각자의 프로젝트 환경에 적용할 클래스를 선택한다.)
Location : Asia (원하는 지역을 선택해도 무방하다.)

CSV, JSON파일 업로드

본 예제에서는 다음의 파일들을 이용한다. 서로 동일한 테이블 데이터를 가지고 있다.

CSV : shakespeare.csv
JSON : shakespeare.json

상당의 UPLOAD FILES를 클릭 후 파일을 선택하고 업로드를 진행한다.

업로드가 완료되었다.

이제 빅쿼리로 이동해서 앞서 구글 클라우드 스토리지에 업로드한 CSV, JSON형식의 데이터를 사용해서 테이블을 생성하고 데이터를 로드해보자

데이터 로딩하기

구글 클라우드 콘솔의 좌측 상단 탭을 클릭하여 빅쿼리 웹 UI로 이동한다.

데이터셋(dataset)생성하기

프로젝트명 오른쪽에 있는 화살표 박스를 클릭하여 데이터셋을 생성한다

데이터셋에 생성 할 ID를 입력하고 나머지는 기본값으로 데이터셋을 생성한다

Dataset ID : load_test (임의의 값)
Data location : unspecified (unspecified, US, EU)
Data expiration : Never (영구히 저장하거나 또는 만료기간 지정가능 – 자동 삭제됨)

*Data location은 데이터셋이 가지고 있는 테이블을 포함하여 데이터가 실제로 위치할 위치를 지정할 수 있다. 제공되는 옵션으로는 지정하지않거나, 미국, 유럽을 선택할 수 있다.

Dataset이 생성되었다. 앞서 임의로 생성한 데이터셋 load_test 위에 테이블을 생성해보자.

테이블 생성하기 - CSV

구글 클라우드 스토리지에 저장한 CSV 파일을 이용해서 데이터를 로딩하여 테이블을 생성해보자

먼저 데이터셋 우측의 +모양의 버튼을 클릭하여 테이블 생성 메뉴를 불러온다

CSV포맷의 데이터를 로드하고 테이블을 생성하기 위해 내용을 입력하고 필요한 옵션을 설정하여 테이블을 생성한다..

Source Data

Location : Google Cloud Storage - gs://load-bigquery/shakespeare.csv

데이터를 가져올 위치로 앞서 구글 클라우드 스토리지에 저장한 CSV파일의 URI를 입력한다

*구글 클라우드 스토리지 URI 형식 : gs://<bucket>/<object>

* Location의 다른 옵션

File Upload : 로컬 파일을 업로드하여 사용할 수 있다.
Google Drive : 구글드라이브에 저장된 파일을 공유한 링크를 이용할 수 있다.
None(create empty table) : 스카마만 구성하여 빈 테이블을 생성할 수 있다.

File format : CSV

Location에서 none(빈테이블 생성)을 제외한 모든 옵션에서 CSV, JSON, Avro포맷을 사용 할 수 있다.

*특수한 경우로 각각의 위치에 따라서 추가적으로 지원되는 포맷이 다르다.

구글 클라우드 스토리지: Google Cloud Datastore backup files

구글 드라이브: Google Sheets

Destination Table

Table name : load_test . csv_test

Destination Table은 앞서 생성한 데이터셋인 load_test를 지정하고 뒤에 생성 할 테이블 이름을 입력한다.

*테이블의 경우 ( . ) 으로 구분하여 <project id>:<dataset id>.<table name> 형태로 쿼리에 사용할 수 있다. 프로젝트 ID의 경우 해당 프로젝트에서 작업하는 경우 생략 가능하다.

Table type : Native table

빅쿼리 내부 스토리지에 저장되는 테이블)

Table type 추가옵션 : External table

빅쿼리 내부 스토리지가 아닌 구글 클라우드 스토리지나 구글 드라이브에 저장되는 테이블로 빠른 생성 및 수정이 가능하지만, 빅쿼리의 쿼리성능을 높이는 컬럼 기반 구조를 사용할 수 없게 되어 성능저하가 발생한다.

Schema

Edit as Text를 클릭하고 다음과 같이 "name:type" 형태(CSV기준)로 입력하거나,

word:STRING, word_count:INTEGER, corpus:STRING, corpus_date:INTEGER

또는 Add Field를 클릭하여 각각 필드를 추가하여 Name을 입력하고 Type을 지정한다.

Field delimiter:Comma ( 구분자로 사용할 요소를 선택 )

Header rows to skip : 1 (최상단의 행부터 행단위로 데이터를 스킵하고 해당 예제에서는 상단에 스키마 정보가 있는 상태라서 1행을 스킵하고 진행한다.)

*데이터만 입력하는 경우 기본값인 0으로 입력한다.

테이블을 생성 시에 기본적으로 고려해야할 사항들에 대해서 언급하였으며,나머지는 기본값으로 입력하고 Create Table을 클릭하여 생성한다.

테이블이 생성되었다.

테이블 테스트 - CSV

좌측상단의 COMPOSE QUERY를 클릭하여 테이블이 정상적으로 생성되었는지 간단한 쿼리를 실행해서 테스트해보자.

다음은 테이블의 모든 데이터를 읽어와10개값만 보여주는 SELECT쿼리이다.

SELECT word, word_count, corpus, corpus_date

FROM load_test.csv_test

ORDER BY word_count DESC LIMIT 10

RUN QUERY버튼을 클릭하여 쿼리를 실행한다.

테스트로 입력한 쿼리가 정상적으로 실행되었다.

다음으로는 JSON형식의 데이터 파일을 로드 하여 테이블을 생성해보자.

데이터 로딩하기 - JSON

데이터셋(load_test)의 우측 + 버튼을 클릭하여 테이블을 생성한다.

JSON포맷의 데이터를 로드하고 테이블을 생성하기 위해 내용을 입력하고 필요한 옵션을 설정하여 테이블을 생성한다. .

Source Data

Location : gs://load-bigquery/shakespeare.json

JSON 파일도 마찬가지로 구글 클라우드 스토리지의 JSON파일 URI를 입력한다

File format : JSON(Newline Delimited)

Destination Table

Table name : json_test(임의의 값)

Schema

Edit as Text에 다음과 같이 텍스트로 JSON형태의 스키마를 입력한다.

        "name": "word",

        "type": "STRING",

        "mode": "REQUIRED"

},

        "name": "word_count",

        "type": "INTEGER",

        "mode": "REQUIRED"

},

        "name": "corpus",

        "type": "STRING",

        "mode": "REQUIRED"

},

        "name": "corpus_date",

        "type": "INTEGER",

        "mode": "REQUIRED"

*Add Field로 각각 생성해도 상관없다.각각의 환경에 따라서 사용하기 편한 방식으로 이용하도록 하자.

다음과 같이 스키마 정보가 정상적으로 입력된 것을 확인 할 수 있다.

이제 나머지 값은 기본값으로 하여 Create Table 버튼을 클릭하여 테이블을 생성해보자.

JSON포맷의 데이터로 생성한 테이블이 생성되었다.

테이블 테스트 – JSON

COMPOSE QUERY를 클릭하여 CSV로 입력한 데이터와 같은 데이터를 가지고 있기 때문에, 앞에서 CSV를 테스트한 쿼리를 이용하여 테스트해보자.

SELECT word, word_count, corpus, corpus_date

FROM load_test.json_test

ORDER BY word_count DESC LIMIT 10

RUN QUERY버튼을 클릭하여 쿼리를 실행해보자

테스트로 입력한 쿼리가 정상적으로 실행되었다.

*구글 클라우드 스토리지를 이용하여 데이터 교환의 표준 포맷이라고 할 수 있는 CSV, JSON형식의 데이터를 이용해 테이블을 생성하고 데이터를 로드하였다. 위의 예제에서는 데이터를 로딩하는 방법에 대한 설명하기 위해 작은 용량의 데이터를 가지고 진행하였다.하지만 빅쿼리는 기본적으로 대용량의 데이터를 실시간에 가까운 속도로 처리하고 분석하기 위해 설계되었고,GB,TB급의 데이터라도 매우 빠른 속도로 로딩이 가능하다. 따라서 위의 예제의 데이터보다는 비교적 큰 데이터를 이용해 앞서 언급한 단일, 병렬, 포맷에 따른 데이터 로딩시간을 확인해보도록 한다

업로드 속도 비교하기

시작하기전에 먼저 각각의 방식에서 속도(성능)를 확인하는 것이 주요 목적으로

각각의 업로드 방법에 대한상세한 설명은 생략한다.

기본 준비사항

원본 데이터

아래는 원본 테이블의 정보이다. 2014년의 항공편에 대한 데이터를 가지고 있으며 용량은 1GB가 약간 넘으며, 6,303,310개의 행(Rows)으로 이루어져 있다.

bq 명령어 도구

앞서 언급한 Command-line interface로 제공되는 도구이다. 파이썬 기반의 도구로 Google Cloud SDK에 포함되어 있으며, 기타 SSH를 사용하는 경우 따로 설치도 가능하다. 이후 진행 할 단일/병렬 업로드에 bq도구를 활용하도록 하겠다.

VM인스턴스 준비

테스트를 위해 Google Compute Engine의 VM인스턴스를 하나 생성하거나 기존의 VM인스턴스를 사용한다.

테스트에 사용한 VM은

machine type : n1-standard-1 (1 vCPU, 3.75 GB memory)
zone : asia-east1-b
disk : 10GB SSD
OS : ubuntu-1404-trusty-v20160627
나머지는 기본값으로 설정

VM이 생성되면 이후 테스트를 진행하기 위해bq도구를 사용하기 위해 SSH에 접속한다.

CSV 단일 파일 업로드

데이터 준비

미리 구글 클라우드 스토리지에 데이터파일을 저장한다.

하나의 CSV 데이터파일과 해당파일을 6개의 파일로 분할한 파일들이다.

다음 명령어를 통해 CSV데이터의 업로드를 실행한다.

bq load --skip_leading_rows=1 \

--source_format=CSV bigquery-1369:load_test.flight_csv_single \

gs://load-bigquery/csv/flight.csv \

./schema_flight_performance.json

bq : bq를 쓰기위한 명령어
load : 데이터 로딩을 위한 명령어
--skip_leading_rows=1 : 웹 UI의 Header rows to skip 옵션과 동일
--source_format=CSV : 포맷 지정

bigquery-1369:load_test.flight_csv_single :

생성 할 테이블 위치 <project id>:<dataset id>.<table name

형태로 테이블을 지정한다.(웹 UI의 Table name과 동일)

gs://load-bigquery/csv/flight.csv :

가져올 데이터 위치(웹 UI의 구글 클라우드

스토리지 URI와 동일)

./schema_flight_performance.json : 사용할 스키마 내용 또는 파일

테이블이 생성 되었다.

웹 콘솔의 job History를 통해데이터 로딩에 소요된 시간을 확인해보자.

해당 잡의 시작시간과 종료시간을 보면CSV 단일 데이터 로딩에 46초가 소요된 걸 확인할 수 있다. (여러 번 테스트 해본 결과 테스트 결과 40초 ~ 60초 정도 소요됨)

다음 bq명령어로도 확인 가능하다

bq ls -j

CSV 병렬 업로드

빅쿼리에서는 제공 하는 와일드카드 "*"로 동일 패턴의 String을 가진 여러 파일에 대한 동시 업로드를 사용할 수 있으며,

['gs://my-bucket/file-name-*.json']형태로 사용할 수 있다.

다음 명령어를 통해CSV데이터의 병렬 업로드를 실행한다.

bq load --skip_leading_rows=1 \

--source_format=CSV bigquery-1369:load_test.flight_csv_multi \

gs://load-bigquery/csv/flight0*.csv \

./schema_flight_performance.json

생성한 테이블 명 : flight_csv_multi

가져올 데이터 위치(와일드카드 사용) :

gs://load-bigquery/csv/flight0*.csv

테이블이 생성되었다.

웹 콘솔의 job History를 통해 데이터 로딩에 소요된 시간을 확인해보자.

해당 CSV 병렬데이터 로딩에 37초가 소요된 걸 확인할 수 있다.

(여러 번 테스트 해본 결과 테스트 결과 36초 ~ 50초 정도 소요됨)

*단일 업로드에 46초, 병렬 업로드에 37초가 소요되었다.9초의 차이가 발생한다. 당연한 결과지만 병렬 업로드가 더 빠르다.따라서 이 뒤의 JSON, Avro

포맷의 단일파일 업로드는 제외하고 진행한다.

JSON병렬 업로드

앞서 CSV병렬 업로드에 사용한 6개의 파일을 JSON으로 변환하고 변환된 파일들을 이용하여 병렬 업로드를 실행하고 속도를 확인해보자. 변환과정은 생략한다.

데이터 준비

CSV -> JSON으로 변환한 파일을 구글 클라우드 스토리지에 저장한다.

CSV와 비교했을 때 데이터 파일의 크기가 확연하게 커졌다. 차이가 발생하는 이유는 JSON의 경우 구조상 모든 데이터가 스키마를 포함하게 되어 데이터를 포함한 JSON파일의 크기가 커지게 된다.

다음 명령어를 통해 JSON포맷의 병렬 업로드를 실행한다.

bq load --source_format=NEWLINE_DELIMITED_JSON \

bigquery-1369:load_test.flight_json_Multi \

gs://load-bigquery/json/flight*.json \

./schema_flight_performance.json

--source_format=NEWLINE_DELIMITED_JSON : JSON 포맷으로 지정한다.

테이블이 생성 되었다.

웹 콘솔의 job History를 통해 데이터 로딩에 소요된 시간을 확인해보자.

JSON포맷의병렬 데이터 로딩에 28초가 소요된 걸 확인할 수 있다.

(여러 번 테스트 해본 결과 20초 ~ 30초 정도 소요됨)

*CSV와 비교했을 때 JSON이 더 빠른 데이터로딩 속도를 보인다.

Avro 병렬 업로드

마지막으로 JSON과 동일한 방법으로 CSV병렬 업로드에 사용한 6개의 파일을 Avro포맷으로 변환하여 병렬 업로드를 하여 속도를 확인해보자. 변환과정은 생략한다.

데이터 준비

CSV -> Avro으로 변환한 파일을 구글 클라우드 스토리지에 저장한다.

같은 데이터를 가지고 있지만 CSV, JSON포맷과 비교했을 때데이터 파일의 크기가 작다.

다음 명령어를 통해 Avro포맷의 병렬 업로드를 실행한다.

bq load --source_format=AVRO \

bigquery-1369:load_test.flight_avro_Multi \

gs://load-bigquery/avro/flight*.avro

--source_format=AVRO : Avro포맷으로 지정한다

Avro의 경우 데이터파일에서 스키마의 정보를 포함하고 있기에 스키마를 따로 지정하지 않아도 된다.

테이블이 생성 되었다

웹 콘솔의 job History를 통해 데이터 로딩에 소요된 시간을 확인해보자.

Avro포맷의병렬 데이터 로딩에 40초가 소요된 걸 확인할 수 있다.

(여러 번 테스트 해본 결과 35 ~ 45초 정도 소요됨)

*여러 번 테스트 한 결과 CSV 병렬 업로드 보다 빠르고 JSON보다는 느리다.

데이터 로딩 결과(시간)

	CSV(단일)	CSV(다중)	JSON(다중)	Avro(다중)
업로드 시간	40초 ~ 60초	36초 ~ 50초	20초 ~ 30초	35 ~ 45초

결론

빅쿼리가 지원하는 데이터 포맷(CSV,JSON, Avro)을 이용해동일한 데이터에 대해서단일 파일 데이터 로딩을 포함하여, 단일 데이터파일을 분할하여 여러 개로 나눠서 병렬(다중파일 동시 업로드)데이터 로딩을 테스트하였다.다음과 같은 결과를 확인할 수 있었다.

로딩 속도는 CSV > Avro > JSON

데이터의 크기(비용적인 측면)는 Avro < CSV < JSON

물론 데이터의 크기가 더 커진다면 결과가 달라 질 수도 있겠지만, 위에서 테스트한 정보를 토대로 이야기하면 1GB의 용량에 6백만 행을 가진 데이터가 작지 않은 데이터라고 볼 수 있으나, 현재와 같이 빅데이터가 일반화 되어가는 시기에 수십,수백GB에서 TB까지(또는 그 이상)의데이터의 로딩의 여러가지 측면(비용,속도)을 생각하면, Avro포맷을 고려해 보는 것도 좋은 선택이 될 수 있을 것이다.

참고자료

https://cloud.google.com/bigquery/docs/

https://cloud.google.com/bigquery/loading-data

https://cloud.google.com/bigquery/docs/loading-data-cloud-storage

https://cloud.google.com/bigquery/data-types

https://cloud.google.com/bigquery/data-formats

https://cloud.google.com/bigquery/bq-command-line-tool

'Google Cloud Platform > Bigquery' 카테고리의 다른 글

빅쿼리 Streaming Insert golang 샘플 (0)	2017.04.24
웹로그 데이터를 빅쿼리를 이용하여 리포팅 하기 (0)	2017.01.09
빅쿼리 Query Plan을 이용한 쿼리 실행 분석 (0)	2016.09.19
빅쿼리의 셔플링 (0)	2016.08.23
빅쿼리 스트리밍 데이터 로딩하기 (4)	2016.08.05

PREV 이전 1 NEXT 다음