구글 빅쿼리 완벽 가이드 - 1장

■ 데이터 처리 아키텍처

• 구글 빅쿼리는 쿼리 엔진이 내장된 서버리스(serverless) 서비스로, 확장성이 높은 데이터 웨어 하우스이다. 내장된 쿼리 엔진은 수 테라바이트의 데이터에 대한 SQL 쿼리를 단 몇 초만에 처리할 뿐 아니라, 수 페타바이트의 데이터는 불과 몇 분이면 처리한다. 게다가 인프라를 별도로 관리하지 않고도 인덱스를 만들거나 재구성하지 않아도 성능을 얻을 수 있다.

---

■ 관계형 데이터베이스 관리 시스템

• MySQL, PstgreSQL 같은 OLTP (Online Transaction Processing) 데이터베이스의 주요 장점 중 하나는 SQL (Structured Query Language)를 지원한다는 점이다. 이 장점 덕분에 자바나 파이썬 같은 high-level 언어를 사용하지 않고 데이터 베이스 서버에서 쿼리만 실행하면 된다.
• OLTP 데이터베이스의 장점 : SQL은 단순히 데이터베이스 컬럼에서 raw 데이터를 가져오는 것뿐만 아니라 그 이상의 작업도 가능하다. 파임스탬프 값을 파싱해서 연도와 월을 추출하며 집계, 일부 필터링, 그룹화 및 정렬을 수행할 수 있다. SQL을 사용해 얻을 수 있는 가장 주된 장점은 원하는 데이터만 명시하면 데이터베이스 소프트웨어가 쿼리를 실행하는 최적의 방법을 스스로 찾아낸다는 점이다.
• OLTP 데이터베이스의 단점 : OLTP 데이터베이스는 데이터 기록과 동시에 읽을 수 있도록 데이터 일관성을 확보하는 데 최적화되어 있다. 이것이 가능한 이유는 데이터 무결성을 유지하기 위해 데이터를 매우 조심스럽게 잠그기 때문이다. 따라서 OLTP 데이터 베이스는 전체 데이터셋을 훑어야 하는 애드혹 쿼리 (필요에 따라 즉석에서 작성해 실행하는 쿼리) 실행에는 적합하지 않다.

---

■ 맵리듀스 프레임워크

• OLTP 데이터베이스는 전체 데이터셋의 순회가 필요한 애드혹 쿼리에 적합하지 않아서 순회가 필요한 특수 목적의 분석은 자바나 파이썬 같은 고수준 언어로 수행하였다. 2003년 Jeff Dean, Sanjay Ghemawt는 특수 목적의 연산을 두 단계로 추상화해서 복잡도를 낮췄다. 여기서 두 단계란 키/값 쌍을 처리해 중간 키/값 쌍을 생성하는 map 함수, 동일한 중간 키와 연관된 모든 중간 값을 병합하는 reduce 함수다. MapReduce라고 알려진 이 패러다임은 이후 아파치 하둡 (Apache Hadoop)의 발전을 이끌었다.

---

■ 빅쿼리 : 서버리스, 분산 SQL 엔진

• 빅쿼리는 서버리스 서비스이므로 인프라를 관리할 필요 없이 쿼리를 실행할 수 있다. 그리고 전체 데이터셋에 대한 집계를 수 초에서 수 분내에 처리하는 분석을 수행할 수 있다.

     ▶ 예제 1-1 뉴욕의 자전거 데이터셋에서 연도별 월별 편도 대여 수를 구하는 쿼리

SELECT EXTRACT(YEAR FROM starttime) AS year,
    EXTRACT(MONTH FROM starttime) AS month,
    COUNT(starttime) AS number_one_way
FROM
    `bigquery-public-data.new_york_citibike.citibike_trips`
WHERE
    start_station_name != end_station_name
GROUP BY year, month
ORDER BY year ASC, month ASC 

 

    ▶ 대여된 장비는 자전거로, 이 쿼리는 데이터셋에서 뉴욕의 매월 자전거 편도 대여 수를 합산한다. 이 쿼리로 2013년 7월 뉴욕시에서 자전거 편도 대여가 815,324번 있었음을 알 수 있다.

---

■ 빅쿼리로 작업하기

• 빅쿼리는 어느 정도 중앙 집중화가 되어 있고 보편성을 가지는 데이터 웨어하우스다. 앞에서 본 쿼리는 단일 데이터셋에 적용하였는데, 빅쿼리의 장점은 다른 소스의 데이터셋을 조인하거나 빅쿼리 외부에 저장된 데이터에 쿼리할 때 빛을 발한다.

---

■ 여러 데이터셋에서 통찰력 도출하기

• 뉴욕시의 자전거 대여 데이터와 미국 국립해양대기청의 날씨 데이터와 결합해 비 오는 날에 자전거 대여가 적은지 알아보자.

    ▶ 예제 1-3 비오는 날에 자전거 대여가 적은지 확인하기 위한 쿼리

WITH bicycle_rentals AS (
  SELECT
    COUNT(starttime) as num_trips, 
    EXTRACT(DATE from starttime) as trip_date
  FROM `bigquery-public-data.new_york_citibike.citibike_trips`
  GROUP BY trip_date
),

rainy_days AS
(
SELECT
  date,
  (MAX(prcp) > 5) AS rainy
FROM (
  SELECT
    wx.date AS date,   
    IF (wx.element = 'PRCP', wx.value/10, NULL) AS prcp
  FROM
   `bigquery-public-data.ghcn_d.ghcnd_2016` AS wx
  WHERE  
    wx.id = 'USW00094728'
)
GROUP BY 
  date
)

SELECT
  ROUND(AVG(bk.num_trips)) AS num_trips,
  wx.rainy
FROM bicycle_rentals AS bk
JOIN rainy_days AS wx
ON wx.date = bk.trip_date
GROUP BY wx.rainy

 

    ▶ 쿼리를 실행하면 뉴욕에서는 비가 올 때 자전거 이용률이 20% 정도 떨어진다는 사실을 알 수 있다.

---

■ ETL, EL, ELT

• 데이터 웨어하우스를 사용하는 전통적인 방법은 ETL(추출, 변환, 로드) 프로세스로 시작한다. 이 프로세스는 소스 위치에서 raw 데이터를 추출하고 변환한 후 데이터 웨어하우스로 로드한다. 빅쿼리는 ETL을 매력적인 방법으로 만드는 매우 효율적인 컬럼 스토리지 포맷을 가지고 있다. 아파치 밤(Apache Beam) 또는 아파치 스파크로 만든 그림 1-2에서 볼 수 있듯, raw 데이터에서 필요한 만큼 추출하고 변환해 필요한 정리나 집계를 수행한 후 빅쿼리에 로드한다.

그림1-2 빅쿼리를 사용한 ETL 참조 아키텍처는 클라우드 데이터플로우에서 실행되는 아파치 빔 파이프라인을 사용해 스트리밍 및 배치 데이터를 하나의 코드로 모두 처리할 수 있다.

• 아파치 빔이나 아파치 스파크로 ETL 파이프라인을 구축하는 것이 일반적이지만, 빅쿼리 하나로도 구축이 가능하다. 빅쿼리에서는 컴퓨팅과 스토리지가 분리되어 있어서 구글 클라우드 스토리지에 현재 저장된 CSV(또는 JSON, 아브로) 파일에 빅쿼리 SQL 쿼리를 실행할 수 있으며, 이 기능을 통합 쿼리라고 한다. 이 통합 쿼리를 사용해서 구글 클라우드 스토리지에 저장된 데이터로부터 SQL 쿼리로 데이터를 추출하고 해당 데이터를 변환한 다음, 그 결과를 빅쿼리 테이블로 저장할 수 있다.
• 변환이 필요하지 않으면 빅쿼리는 CSV, JSON 또는 아브로 같은 네이티브 스토리지에 직접 접근할 수 있다. 즉 EL(추측 및 로드) 워크플로우도 가능하다. 데이터를 데이터 웨어하우스로 로드하는 이유는 네이티브 스토리지에 저장할 때 쿼리 성능이 가장 효율적이기 때문이다.
• 가능하다면 EL 워크플로우를 설계하고, 변환이 필요한 경우에만 ETL 워크플로우로 넘어가는 것이 좋다. 변환이 SQL으로만 구현하기 어렵거나 파이프라인에 데이터를 빅쿼리로 스트리밍해야 한다면 아파치 빔 파이프라인을 만들어 클라우드 데이터플로우를 사용하는 서버리스 방식으로 실행한다. 빔/데이터플로우는 프로그래밍 코드이므로 이것으로 ETL 파이프라인을 구현하면 파이프라인을 지속적 통합(Continuous Integration) 및 단위 테스트 시스템과 통합이 잘 된다는 장점이 있다.
• ETL. EL 워크플로우 외에도 빅쿼리는 ELT 워크플로우를 구현할 수 있다. ELT는 원시 데이터를 그대로 추출해 로드한 다음 빅쿼리 뷰로 해당 데이터를 즉시 변환하는 방법이다. 예를 들면 특정 시각을 현지 시각으로 수정해야 할지 결정하고자 행마다 값을 탐색해야 하는 경우가 이에 해당한다. ELT 워크 플로우는 프로토타이핑에 유용하다.

워크플로우	구조	사용해야 하는 경우
EL	♣구글 클라우드 스토리지의 파일에서 데이터를 추출한다. 빅쿼리의 기본 스토리지에 불러온다. ♣클라우드 컴포저, 클라우드 Function 또는 예약된 쿼리에서 실행한다.	♣히스토리 데이터를 배치 단위로 불러오는 경우. ♣정기적으로 로그 파일을 불러오는 경우 (예: 하루에 한 번)
ETL	♣펍/섭(Pub/Sup), 구글 클라우드 스토리지, 클라우드 스패너(Spanner), 클라우드 SQL 등에서 데이터를 추출한다. ♣클라우드 데이터플로우를 데이터를 변환한다. ♣데이터플로우 파이프라인을 통해 빅쿼리 테이블에 저장한다.	♣원시 데이터를 빅쿼리에 불러오기 전에 품질 관리, 변환 또는 보강해야 하는 경우. ♣데이터 불러오기가 지속적으로 필요한 경우. ♣CI/CD 시스템과 통합하고 모든 구성 요소에서 장치 테스트를 수행하려는 경우.
ELT	♣구글 클라우드 스토리지의 파일에서 데이터를 추출한다. ♣빅쿼리에서 원시 형식으로 데이터를 저장한다. ♣빅쿼리 뷰로 데이터를 즉시 변환한다.	♣데이터를 사용하려면 어떤 종류의 변환이 필요한지 아직 모르는 실험 데이터셋. ♣변환이 SQL로 표현될 수 있는 모든 프로덕션 데이터셋.

---

■ 강력한 분석

• 데이터 웨어하우스는 저장된 데이터로 작업할 수 있는 분석의 종류가 많다는 장점이 있다. 빅쿼리 웹 UI에서 Search in Data Studio 버튼을 클릭하면 그림 1-3처럼 자전거 편도 대여의 월별 차이를 빠르게 시각화할 수 있다.
• 빅쿼리는 배열 및 복잡한 조인을 포함한 SQL:2011의 모든 기능을 지원한다. 특히 배열 지원으로 중첩되거나 반복되는 필드를 평평하게 할 필요 없이 계층 데이터(JSON 레코드 등)를 빅쿼리에 저장할 수 있다.

그림 1-3 월별 편도 대여에 대한 데이터 스튜디오 시각화

• 표준 SQL에 대한 또 다른 빅쿼리 확장 기능은 머신러닝 모델 생성과 배치 예측 작업을 지원한다. 요점은 빅쿼리에서 데이터를 내보내지 않고도 모델을 학습하고 예측할 수 있는 점이다.
• 데이터 웨어하우스는 다양한 유형의 데이터를 저장할 수 있다. 빅쿼리는 숫자 및 텍스트 컬럼뿐만 아니라 지리 공간 데이터와 계층 데이터 등 다양한 유형의 데이터를 저장할 수 있다.
• 빅쿼리는 배치 데이터와 스트리밍 데이터 수집 모두 지원한다. REST API를 통해 빅쿼리로 직접 데이터를 스트리밍할 수 있다.

---

■ 관리의 단순함

• 빅쿼리의 디자인 고려 사항 중 하나는 사용자가 인프라에 신경 쓰는 것보다 데이터로부터 얻을 수 있는 통찰력에 집중하도록 유도하는 것이다. 빅쿼리의 스토리지는 완전한 관리형이므로 데이터를 적재할 때 스토리지의 종류나 속도 및 비용의 트레이드오프를 생각할 필요가 없다. 
• SQL 쿼리는 데이터셋의 모든 컬럼을 필터링하며, 빅쿼리는 필요한 쿼리 계획 및 최적화를 처리한다.
• 쿼리는 자동으로 수천 대의 머신으로 확장되고 병렬로 실행된다. 이 대규모 병렬화를 활성화하고자 따로 작업을 수행하지 않아도 된다. 머신 자체가 작업의 여러 단계를 처리하도록 투명하게 프로비저닝되어 어떤 방식으로든 해당 머신을 설정할 필요가 없다.
• 인프라스트럭처를 직접 구축하지 않아도 되므로 보안을 신경 써야 하는 번거로움도 줄어든다. 빅쿼리의 데이터는 저장 및 전송 시 자동으로 암호화된다. 빅쿼리를 사용하면 데이터베이스 관리 작업이 최소화되어서 분석가들이 데이터에서 통찰력을 얻는 데 집중할 수 있는 시간을 확보할 수 있다.