BigQuery - Table Sampling

▷ 들어가며

ML 모델을 학습시키기 위해서 무작위 추출된 데이터셋이 필요한 경우가 간간이 생긴다. 이때 자주 사용되는 함수가 RAND() 이다.

SELECT *
  FROM `firebase-public-project.analytics_153293282.events_*`
 WHERE RAND() < 0.1;

BigQuery에서 RAND()를 사용할 때 고려할 점은 랜덤 샘플링을 위해서 전체 테이블을 스캔해야 하므로 이로 인한 시간 소요 및 비용 발생을 들 수 있다.

▷ BigQuery 테이블 샘플링

TABLESAMPLE 문법

BigQuery 에서는 샘플링을 위해 비용 효율적인 TABLESAMPLE 구문을 제공한다. 이를 이용하여 RAND() 사용시의 문제를 개선할 수 있는지 살펴보도록 하자.

TABLESAMPLE 절(clause)을 추가하여 샘플링을 수행하는 문장은 다음과 같다.

(참고 - BigQuery에서는 *`로 끝나는 테이블을 Wildcard Table 이라고 부른다.)

SELECT *
  FROM `firebase-public-project.analytics_153293282.events_*` TABLESAMPLE SYSTEM (1 PERCENT)

문법적인 구조를 먼저 살펴보면 TABLESAMPLE 키워드가 FROM 절의 테이블 이름 바로 다음에 위치하고 있다. 이후 SYSTEM (1 PERCENT) 구문으로 전체 테이블에서 샘플링할 비율을 퍼센트 단위로 지정해주면 된다.

주의할 점은 테이블 이름은 영속 테이블(Permanant Table)이나 임시 테이블(Temporary Table)과 같은 Base Table 이어야 하고 이외에 테이블과 동등하게 다루워지는 서브쿼리, View, TVF(Table Valued Function) 또는 UNNEST()가 위치해서는 안된다. 또한 IN 서브쿼리내에서의 사용은 허용되지 않는다.

테이블 샘플링 동작 방식

다음으로 샘플링이 동작하는 방식을 확인하기 위해 몇 가지 테스트 쿼리를 실행해 보자.

SELECT COUNT(1) AS cnt
  FROM `firebase-public-project.analytics_153293282.events_*`;

Row	cnt
1	5700000

위의 와일드 카드 테이블의 총 레코드 수는 5,700,000 임을 알 수 있다. 다음으로 1 Percent 샘플링한 후의 레코드를 보면 다음과 같다.

SELECT COUNT(*) AS cnt 
  FROM `firebase-public-project.analytics_153293282.events_*` TABLESAMPLE SYSTEM (1 PERCENT);

Row	cnt
1	50000

50,000개의 샘플을 리턴해 준다. 조금 줄여서 0.1 Percent만 가져와 보자.

SELECT COUNT(*) AS cnt 
  FROM `firebase-public-project.analytics_153293282.events_*` TABLESAMPLE SYSTEM (0.1 PERCENT);

Row	cnt
1	50000

앞서와 동일하게 50,000 레코드가 샘플링되었다. 이상하다. 이를 이해하기 위해서는 BigQuery에서 테이블이 저장되는 방식을 이해할 필요가 있다.

BigQuery 테이블은 데이터 블록들로 구성되어 스토리지에 저장된다.

TABLESAMPLE 절은 테이블이 나뉘어 저장된 여러 데이터 블록 중에 임의의 블럭들을 선택하여 해당 블럭의 모든 행들을 읽어 들인다.

따라서 샘플링의 Granularity(기본단위)가 데이터 블럭의 수에 제한을 받는다. 하나의 데이터 블럭에 저장되는 행의 개수에 의해 결정된다.
(역자 주 - 샘플링된 행의 갯수는 하나의 데이터 블럭이 저장하고 있는 행의 숫자 X 블럭 개수가 된다.)

앞서 예시 쿼리에서 0.1 Percent와 1 Percent 샘플링된 행의 수가 50,000으로 동일했던 이유는 예시 테이블의 경우 하나의 데이터 블럭에 저장된 행의 개수가 50,000 이기 때문이다.

여러 블럭 중 하나를 임의 선택하면 0.1 Percent에 해당하는 5,700개 샘플 수를 넘어서나 Granularity가 50,000 단위이기 때문에 최소값인 50,000 행이 추출되었다.

잠깐, 이상한 점이 있지 않은가?

와일드카드 테이블의 전체 레코드 수가 5,700,000 이라고 했으니 1 Percent는 57,000이 되었어야 하는데 결과는 50,000이었다.
역시 Granularity와 관련이 있을 것 같다. 50,000 단위로 샘플링이 가능하니 TABLESAMPLE이 추출하는 샘플의 갯수는 퍼센티지에 따라서 50,000, 100,000, 150,000 행의 순으로 늘어갈 것이다.

퍼센티지를 1% 씩 올려가며 테스트 해보면 아래와 같은 결과를 확인할 수 있다.

percent	expected	sample
1	57,000	50,000
2	114,000	100,000
3	171,000	150,000
4	228,000	200,000
5	285,000	250,000
6	342,000	300,000
7	399,000	350,000
8	456,000	450,000
9	513,000	500,000
10	570,000	550,000

실험결과로부터 유추할 수 있는 것은 추출된 샘플수(sample)가 기댓값(expected)을 넘어서지 않도록 블럭 개수를 늘려가며 해당 블럭들의 모든 행을 읽어온다는 것이다.

1~7 Percent까지는 블럭수를 하나씩 늘려가서 샘플을 가져오다가 8 Percent로 넘어갈 때는 두 개의 블럭을 추가하여도 expected값을 넘지 않으므로 두 개 블럭의 포함된 행의 개수인 100,000 이 늘어난 것을 확인할 수 있다.

*주의 - 블럭에 저장된 행의 개수는 테이블마다 상이하며 같은 테이블의 블럭이라고 하더라도 블럭내 행의 개수는 조금씩 차이가 날 수 있다.

지금까지 간단하게 BigQuery의 테이블 샘플링 동작 방식을 고찰해 보았다.

테이블 샘플링 활용 예시

TABLESAMPLE 절은 다른 구문과 어울려 사용이 가능한데 WHERE절과 같이 사용하면 다음과 같다. 이 경우 샘플링 이후에 WHERE절에 의한 필터링이 수행된다.

SELECT *
  FROM dataset.my_table TABLESAMPLE SYSTEM (50 PERCENT)
 WHERE customer_id = 1

JON 구문에서도 사용 가능하다. 이 경우도 마찬가지로 JOIN후에 샘플링이 이루어지는 것이 아니라 샘플링된 결과 테이블간에 JOIN 연산이 수행된다.

SELECT *
  FROM dataset.table1 T1 TABLESAMPLE SYSTEM (10 PERCENT)
  JOIN dataset.table2 T2 TABLESAMPLE SYSTEM (20 PERCENT)
 USING (customer_id)

도입부에서 언급했던 RAND() 함수는 각각의 행에 대해서 샘플링을 할지 말지를 확률적으로 선택하는 방식이라고 한다면, TABLESAMPLE 은 블럭단위로 샘플링을 할지 말지를 결정한다.

TABLESAMPLE 방식은 데이터 블럭이 가지는 단위 행의 개수(Granularity)에 따라서 정확한 비율의 샘플을 추출하지 못할 수 있으므로 정확한 비율이 필요한 경우에는 LIMIT을 같이 혼용해서 사용할 수 있다.

SELECT COUNT(1) AS cnt FROM (
  SELECT * 
    FROM `firebase-public-project.analytics_153293282.events_*`  TABLESAMPLE SYSTEM (2 PERCENT)
   LIMIT 57000; -- 전체 레코드의 1%

LIMIT은 상수값과 어울려 사용되기 때문에 변수를 사용하기 위해서는 약간의 트릭이 필요하다.

DECLARE totals INT64 DEFAULT (
  SELECT COUNT(1) AS cnt
    FROM `firebase-public-project.analytics_153293282.events_*`
);

SELECT COUNT(1) AS cnt FROM (
  SELECT * 
    FROM `firebase-public-project.analytics_153293282.events_*` TABLESAMPLE SYSTEM (2 PERCENT)
   WHERE TRUE QUALIFY ROW_NUMBER() OVER () < totals * 0.01
)

위의 예시에서는 테이블의 전체 레코드 수를 변수라 totals에 먼저 계산해 놓은 다음 LIMIT 대신 Window 함수인 ROW_NUMBER()를 이용하여 행의 개수를 제한하고 있다. 여기서 QUALIFY 구문은 집계 함수에 대해서 HAVING으로 필터링 조건을 적용하는 것과 유사하게 윈도우 함수의 결과에 대해서 필터링 조건을 걸 때 사용되는 구문이다.

수행 결과는 다음과 같다.

Row	cnt
1	56999

물론 RAND()와 혼용도 가능하다. 필요시 LIMIT과 조합하여 필요한 샘플의 규모를 조정할 수 있을 것이다.

SELECT *
  FROM dataset.my_table TABLESAMPLE SYSTEM (20 PERCENT)
 WHERE RAND() < 0.1

▷ 기타

BigQuery 테이블 샘플링은 외부 테이블 (External Table)에 대해서도 동작한다.
또한 BigQuery Streamin Insert를 사용하는 경우 Streaming Buffer도 하나의 데이터 블럭으로 간주되어 확률적으로 샘플에 포함될 수 있다.

마지막으로 Partitioned된 테이블과 Clustered된 테이블의 경우를 생각해 보자.
파티셔닝의 경우는 파티션 키에 의한 분할에 의해서, 클러스터링은 클러스터링 키에 의한 정렬에 의해서 비슷한 속성의 데이터들이 같은 데이터 블럭에 몰려있을 수 있다.
이 경우 샘플링을 할 경우 편향된 샘플을 얻을 수 있으므로 주의가 필요하다.

▷ 참고자료

• https://cloud.google.com/bigquery/docs/table-sampling
• https://towardsdatascience.com/advanced-random-sampling-in-bigquery-sql-7d4483b580bb?gi=b299a837e4ab