BigQuery – Food for iThought

BigQuery 에서 PIVOT 하는 방법

BigQuery 에서2021년 7월 19일, PIVOT 기능이 신규 추가되었다.
PIVOT 이란 테이블의 열과 행을 변경하는 것을 말한다.
해당 PIVOT 기능에 대한 문법과 유의점에 대해 알아보자.

1. Syntax

--PIVOT 연산자
FROM from_item[, ...] pivot_operator

pivot_operator:
    PIVOT(
        aggregate_function_call [as_alias][, ...]
        FOR input_column
        IN ( pivot_column [as_alias][, ...] )
    ) [AS alias]

as_alias:
    [AS] alias

2. Example

다음과 같은 테이블이 있다고 해보자.

product	sales	quarter	year
Kale	51	Q1	2020
Kale	23	Q2	2020
Kale	45	Q3	2020
Kale	3	Q4	2020
Kale	70	Q1	2021
Kale	85	Q2	2021
Apple	77	Q1	2020
Apple	0	Q2	2020
Apple	1	Q1	2021

table_A

위 table A 를 아래 table B 와 같이 변경하고 싶다면,

product	year	Q1	Q2	Q3	Q4
Apple	2020	77	0	NULL	NULL
Apple	2021	1	NULL	NULL	NULL
Kale	2020	51	23	45	3
Kale	2021	70	85	NULL	NULL

table_B

다음과 같이 쿼리를 작성하면 된다.

SELECT * FROM
 table_A
 PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))

3. 유의사항

PIVOT 하고자 하는 테이블에서 내가 참조하지 않는 속성이 있는 경우,
명확하게 PIVOT 앞의 FROM 절에서 명시를 해주어야 한다.

그렇지 않을 경우, PIVOT 결과 값이 왜곡된다.
(행 중복)

3.1. Bad Case

단순히 쿼터 별로 합계를 보고 싶다고 할 때(연도와 상관 없이),

아래와 같이 쿼리를 작성하지 말자.

SELECT product, Q1, Q2, Q3, Q4 FROM
 table_A
 PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))

이에 대한 Result 는 다음과 같다.

product	Q1	Q2	Q3	Q4
Kale	51	23	45	3
Kale	70	85	NULL	NULL
Apple	77	0	NULL	NULL
Apple	1	NULL	NULL	NULL

3.1. 잘못된 결과

3.2. Good Case

참조하고자 하는 속성 중 year 가 없는 경우, 다음과 같이 명시해주어야 한다.

SELECT product, Q1, Q2, Q3, Q4 FROM
(SELECT product, quarter, sales FROM table_A)
PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))

이에 대한 Result 는 다음과 같다.

product	Q1	Q2	Q3	Q4
Kale	121	108	45	3
Apple	78	0	NULL	NULL

3.2. 잘된 결과

BigQuery 랜덤 샘플링

RAND() 함수를 활용한 BigQuery 랜덤 샘플링

랜덤 샘플링은 RAND() 함수를 사용하면 된다.

N개의 행(row)을 랜덤 샘플링하는 쿼리는 다음과 같다.

--샘플 크기가 100개 행이라고 가정했을 때,

SELECT *
FROM `project.dataset.table`
WHERE RAND() < 100/(SELECT COUNT(*) FROM  `project.dataset.table`)
;

BigQuery 에서 TO_CHAR 사용해서 date 로부터 YYYY-MM 추출하기

BigQuery 를 사용하다보면, Oracle 이나 타 SQL 에 비교해서 가장 짜증나는 부분이
BigQuery 에서는 TO_CHAR 함수가 없다는 것이다.

즉, 특정 date 속성에서 ‘YYYY-MM’ string 을 추출하고자 한다면

Oracle 에서는

--Oracle SQL
SELECT TO_CHAR(<date>, 'YYYY-MM') AS <colname>

위와 같이 간결한 쿼리문이 BigQuery 에서는

--BigQuery
SELECT CONCAT(CAST(FORMAT_DATE("%E4Y", CAST(<date> as date)) AS string),'-',CAST(FORMAT_DATE("%m", CAST(<date> as date)) AS string)) AS <colname>

위와 같이 매우 번거롭게 쿼리를 작성해야 한다.

BigQuery ARRAY 속성 EXCEPT 적용, REPLACE

1. * EXCEPT

BigQuery 는 편리하게도 SELECT 문에서 전체 속성을 지정하는 Wildcard(*) 를 사용 시,
특정 속성을 제외할 수 있는 문법을 제공한다.

가령, 속성 a, b, c, d, e, f 중 a, b, d, e, f 만 선택하고 싶을 경우 (=c 제외)

--아래와 같이 일일히 속성을 지정하는 것 대신,
SELECT a, b, d, e
FROM table


--다음과 같이 * EXCEPT 를 적용하면 효율적이다
SELECT * EXCEPT(c)
FROM table

2. ARRAY 속성 관련 제한사항

그러나 * EXCEPT 는 그 편리함에도 불구하고,
제외할 속성으로 ARRAY 내 nested column 을 특정할 수 없다는 단점이 있다.

(ARRAY, UNNEST 에 대해 잘 모른다면 다음 포스팅 참고)

3. REPLACE 사용하기

가령, ARRAY 속성 b 를 가진 테이블을 UNNEST 하여
속성 a, b.1, b.2, b.3, c 중 a, b.2, b.3, c 만 선택하고 싶을 경우 (=b.1 제외)

--직관적으로는 아래와 같이 쿼리를 하고 싶지만 실행 오류가 뜬다.
SELECT * EXCEPT(b.1)
FROM table, UNNEST(b) AS b


--이 때는 REPLACE 를 적용해 다음과 같이 쿼리해야 한다.
SELECT * REPLACE (ARRAY(SELECT AS STRUCT * EXCEPT(1) FROM UNNEST(b)) AS b)
FROM table

BigQuery 여러 날짜 별 테이블 한 번에 쿼리

1. 테이블 구성

BigQuery 는 GA 와 연계해서 사용하는 경우,
일반적으로 GA 데이터는 일 (daily) 단위의 별개 테이블로 BigQuery에 적재된다.

events_ & events_intraday_ tables in BigQuery for GA4 (Google Analytics 4) - Optimize Smart

출처 : Optimize Smart

가령, 위 화면 좌측 Explorer에 events_ (9) 라는 테이블은
events_20210115
events_20210114
events_20210113
…
events_20210107
총 9개의 일 별 테이블이 있는 것이다.

events 라는 테이블 명에 ‘_YYYYMMDD’ 형태의 Suffix 가 적용되었고,
총 테이블 개수는 괄호 안에 ‘_(9)’ 와 같이 표현된다.

2. 여러 테이블 쿼리하기

위와 같은 테이블 구성에서, 그렇다면 여러 테이블들을 어떻게 한 번에 호출할까?

여러 테이블을 일일히 UNION 하는 방식도 있겠으나,
BigQuery 에서는 보다 효율적인 문법을 제시한다.

Wildcard 와 _TABLE_SUFFIX 를 사용을 통해서다.

events 테이블 중 2021-01-09 부터 2021-01-12 까지의 테이블을 조회해야 한다고 가정해보자.

--아래와 같은 방식 대신,
SELECT *
FROM 'dbrt-ga4.analytics_207472454.events_20210109'
UNION ALL 
SELECT *
FROM 'dbrt-ga4.analytics_207472454.events_20210110'
UNION ALL 
SELECT *
FROM 'dbrt-ga4.analytics_207472454.events_20210111'
UNION ALL 
SELECT *
FROM 'dbrt-ga4.analytics_207472454.events_20210112'



-- Wildcard 와 _TABLE_SUFFIX 를 사용하는 것이 더 효율적이다.
SELECT *
FROM 'dbrt-ga4.analytics_207472454.events_*'
WHERE PARSE_DATE('%Y%m%d', _TABLE_SUFFIX) BETWEEN DATE('2021-01-09') AND DATE('2021-01-12')

참고
https://cloud.google.com/bigquery/docs/querying-wildcard-tables

BigQuery UNNEST 친절한 설명

1. ARRAY 란?

Oracle 과 같이 기존에 전통적인 RDBMS 문법을 기반으로 쿼리를 배웠을 경우,
BigQuery 에서 이런 저런 데이터를 다루다 보면 간혹 낯선 풍경과 조우하는 경우가 있다.

그 중 하나가 BigQuery 의 데이터형(data type) 중 하나인 ARRAY 이다.

BigQuery에서 배열은 데이터 유형이 동일한 0개 이상의 값으로 구성된 순서가 지정된 목록을 의미합니다.
Google BigQuery Doc

별로 와닿지 않는 정의다…
풀어서 설명을 해보도록 하겠다.

일반적으로 기업에서 BigQuery 를 사용한다고 하면,
Google Analytics (이하 GA) 와 연동해서 사용하는 경우가 많으므로
GA 데이터를 샘플 삼아 설명하도록 하겠다.

GA 는 Web 에서 고객이 남기는 행동들을 Log 형태로 수집을 하고,
수집된 raw data는 JSON 형식에서 대략 다음과 같다.

[
  {
    "visitId": "123456789",
    "visitStartTime": "1622124798",
    "totals": {
      "visits": "1",
      "hits": "1",
      "pageviews": "1"
    }
  }
]

(참고) visitStartTime 값이 왜 1622124798 인지 궁금하신 분들은 관련 포스팅을 참고

JSON 문법을 잘 모르더라도,
위 내용을 아래와 같이 직관적으로 파악할 수 있을 것이다.

👨 visitId 가 123456789 이고
🕒 1622124798 UNIX time 에 들어와서
🔢 총 방문 수를 1회, hit 수는 1회, 페이지 조회 수는 1회 기록했구나.

자, 그러면 위 JSON 데이터를 쿼리가 가능하도록 table 형태로 보면 어떨까?

2. Why UNNEST?

위 데이터를 표로 만들 경우, 결과는 다음과 같을 것이다.

visitId	visitStartTime	totals
123456789	1622124798	“visits”: “1”, “hits”: “1”, “pageviews”: “1”

table1. UNNEST 전

자, 한 번 보자.
딱 봐도 문제가 있지 않은가? (불편 +1)

totals 컬럼의 값을 펼쳐서 추가적인 컬럼으로 만들고 싶은 욕구가 생긴다.
마치 R 에서의 reshape 패키지 cast 함수처럼.

visitId	visitStartTime	totals.visits	totals.hits	totals.pageviews
123456789	1622124798	1	1	1

table2. UNNEST 후

table1 에서 table2 로 변경을 해주는 작업.
이것이 바로 UNNEST 함수다.

3. UNNEST Syntax

UNNEST 의 사용 문법은 다음과 같다.

--visitId 별 페이지 조회 수

SELECT visitId, t.pageviews
FROM table1, UNNEST(totals) AS t

테이블 내 값을 컬럼으로 펼치는 과정에서 CROSS JOIN 이 들어가기 때문에,
FROM table1 뒤에 쉼표(,) 가 붙는다.

UNNEST(totals) 에 대한 alias 가 필수는 아니지만,
복잡한 JOIN 이 들어간 쿼리에서 해당 속성을 특정할 수 있어 사용을 권고한다.

외 참고 링크

Google 에서 직접 GA 샘플 데이터셋을 제공하기도 하니,
실무적인 연습이 필요한 분은 아래 링크를 참고.
Google Analytics sample dataset for BigQuery

R 과 BigQuery 연계 (bigrquery, DBI)

본 포스팅에서는 R 과 BigQuery를 연계하는 방법을 다룬다.

구체적으로, R 에서 함수를 호출해 BigQuery에 쿼리를 날리고
해당 쿼리 결과 값을 로컬에 있는 R 로 가져와
dataframe object로 저장하기까지의 과정을 다룬다.

이를 위해 사용되는 R 패키지는 DBI, bigrquery, dbplyr 총 3가지다.
각 패키지의 역할은 간략하게 다음과 같다.

1. DBI
BigQuery를 비롯한 포괄적인 R/DBMS database 와의 인터페이스를 제공하는 패키지

2. bigrquery
BigQuery에 특정된 인터페이스를 취급하는 패키지

3. dbplyr
dplyr를 SQL 로 번역해주는 역할을 하는 패키지. 데이터 조작을 위해 필요하다.

아래 코드에서는 이해를 목적으로, 함수 별로 앞에 ‘패키지명’:: 을 명시함으로써 구분을 해 놓았다.
예외 적으로, dbplyr 의 경우는 명시하지 않았고 다만 ‘%>%’ 가 포함된 코드는 dbplyr 가 사용된 것이다.

#패키지 설치 및 로드

install.packages("bigrquery")
install.packages("DBI")
install.packages("dbplyr")

library(bigrquery)
library(DBI)
library(dbplyr)

##연결 셋팅

#요금 청구 대상이 되는 프로젝트 ID 입력
billing <- "project id"
#쿼리하고자 하는 프로젝트 ID 입력
projectName <- "project id"
#쿼리하고자 하는 데이터셋 입력
datasetName <- "dataset"

con <- DBI::dbConnect(
  bigrquery::bigquery(),
  project = "project id",
  dataset = "dataset",
  billing = billing
)

ds <- bigrquery::bq_dataset(projectName , datasetName)

#인증
bigrquery::bq_auth()

#사용하고자 하는 sql문 입력, 이때 syntax는 BigQuery
sql_tmp <- paste0("SELECT column
                  FROM `project id.`.dataset.table;")

#sql 호출
tb <- bigrquery::bq_dataset_query(ds,
                       query = sql_tmp,
                       billing = billing,
                       quiet = NA)

#테이블을 dataframe 형태로 다운로드
tb_df <- bigrquery::bq_table_download(tb) %>% as.data.frame()

Reference
https://cloud.google.com/architecture/data-science-with-r-on-gcp-eda

Google Analytics 데이터 가져오기 (Data Export)

Google Analytics 로부터 데이터를 가져오는, 추출하는 방법은 총 4가지가 있다.

1. UI 다운로드

웹(https://analytics.google.com/)에서 GA에 접속해
Export 를 클릭해 원하는 파일 유형 (PDF, Google Sheets, Excel, CSV)으로 로컬에 다운로드 한다.

※ 제한사항 : 한 번에 최대 5,000 행까지만 다운로드 가능하다.

2. Unsampled Report (GA 360 Only)

웹(https://analytics.google.com/)에서 GA에 접속해
Export > Unsampled Report 를 클릭해 CSV 형태로 로컬에 다운로드 한다.

샘플링되지 않은 보고서를 생성할 때는 Google 서버 측에 상당한 처리 부하가 발생하므로
GA의 엔터프라이즈 버전인 GA 360에서만,
그 중에서도 권한 부여된 계정에 한해서만 가능하다.

※ 제한사항 : 한 번에 최대 3,000,000 행까지만 다운로드 가능하다. 기간이 1년 이상일 경우, Google 에서는 오류 방지를 위해 분할 추출을 권장한다.

3. BigQuery API

BigQuery 를 사용한다는 가정 하에, BigQuery 를 Google Analytics 와 연계해
GA 데이터를 BigQuery로 추출할 수 있는 기능이다.

이 때, 주의할 점은 모든 GA 데이터를 가져올 수 없다는 것이다.
가령 성별, 연령대 또는 관심사 등의 데이터는 추출이 불가능하다.

가져올 수 있는 데이터는 오직 BigQuery API 스키마에서 정의된 속성에 한해서만 가능하며
해당 스키마는 아래 링크를 통해 확인 가능하다 :
BigQuery Export API 스키마 링크

※ 제한사항 : Export 가능한 속성이 제한된다.

4. Core Reporting API (★추천)

Core Reporting API 는 Google 측에서 GA 데이터를 타 시스템과 연계할 수 있도록
제공하는 API 방식의 인터페이스다.

목적 자체가 호환성에 있기 때문에,
Java, Python, PHP, JavaScript 기반의 시스템과 연계 가능하며 가이드까지 제공한다.

Core Rerpoting API 를 통해 다음 작업이 가능하다 :

GA 데이터 기반으로 커스텀 대시보드를 제작
리포팅 업무 자동화
타 비즈니스 어플리케이션과 GA 데이터 연계

Core Reporting API 방식을 적극 추천하는 이유는,
단연 접근 가능한 속성과 디멘션이 가장 포괄적이기 때문이다.

앞서 살펴본 3번 BigQuery Export API 에서 지원하지 않는 속성들을
(ex.연령대, 성별, 관심사 등) 대부분 제공하며,
Custom 속성/디멘션까지 조회 가능하다.

해당 스키마는 아래 링크를 통해 확인 가능하다 (+Expand All 클릭) :
Core Reporting API 스키마 링크

단, 고객 식별자 값과 같은 개인민감정보(PII)들은 조회가 불가하니 이 점 알아두자.
(필자의 경우, hashed 값도 추출 불가했는데 가능한 경우 댓글 바람)

※ 제한사항 : API Request 최대 2,000 건으로 제한

↑ 다음 포스팅에서는 Core Reporting API 를 활용해 R 로 GA 데이터를 가져오는 방법(googleAnalyticsR 패키지)에 대해 구체적으로 다루어보도록 하겠다.

Reference
https://medium.com/@larsgundersen/how-to-get-the-google-analytics-360-core-reporting-api-to-return-unsampled-data-e83ce87fc176