BigQuery UDF 활용하기
feat. 쿼리로 A/B test 결과 출력하기
SQL은 데이터 연산에 필요한 다양한 내장함수를 가지고 있습니다. 하지만 기본적인 연산 이외에 복잡한 로직을 요구하는 함수가 필요한 경우가 있는데요, 이때 활용할 수 있는 것이 UDF 입니다.
이 글에서는 마이리얼트립에서 사용하는 DW인 BigQuery의 UDF 사용법을 알아보고, 수기로 관리되던 A/B test 결과를 쿼리로 출력할 수 있게끔 UDF 를 활용한 사례를 소개해드리겠습니다.
UDF 란?
- User Defined Function 의 약자로, 사용자가 정의한 함수를 의미합니다.
- Python, R 과 같은 프로그래밍 언어와 마찬가지로, SQL에서도 함수를 만드는 것이 가능합니다.
- BigQuery 에서는 SQL 뿐만 아니라 JavaScript를 활용하여 UDF 를 정의할 수 있지만, 본 글에서는 SQL 정의 방법만을 다루겠습니다.
BigQuery UDF 종류
- Temporary(임시) UDF : 쿼리문에 임시로 정의해서 사용하는 방식
- Persistent(영구) UDF : BigQuery 데이터셋에 저장되는 방식으로, 여러 사람과 함께 사용할 경우 유용
Temporary(임시) UDF
쿼리 최상단에 위치, CREATE TEMP FUNCTION 명령어 사용
주의사항
- 함수 파라미터 정의 시 데이터 타입 함께 적기
- RETURNS 뒤에 리턴될 데이터 타입 적기 (RETURNS 자체 생략 가능)
- AS 뒤에 함수 정의
- 함수 정의 부분에 쿼리문을 쓴다면 괄호로 한번 꼭 감싸야함
- 정의 후
;를 꼭 붙여야함
예시
- 반지름을 입력받고, 원의 넓이를 출력해주는 임시 UDF
- 실행시 아래와 같은 결과를 출력하게 됩니다.
Persistent(영구) UDF
CREATE FUNCTION 명령어 사용. 특정 데이터셋 하위에 테이블처럼 저장
- CREATE OR REPLACE FUNCTION 사용시 UDF가 없다면 신규 생성, 있다면 수정
- 정의한 UDF는
dataset_name.function_namee(parameter)꼴로 호출
주의사항
- 함수 파라미터 정의 시 데이터 타입 함께 적기
- RETURNS 뒤에 리턴될 데이터 타입 적기 (RETURNS 자체 생략 가능)
- AS 뒤에 함수 정의
- 함수 정의 부분에 쿼리문을 쓴다면 괄호로 한번 꼭 감싸야함
- 정의 후
;를 꼭 붙여야함
예시
- 두 수치를 입력받고, 증감률을 계산해주는 영구 UDF
- 위와 같이 정의한 함수를 호출하는 방법은 다음과 같습니다.
A/B Test 결과를 출력해주는 UDF 정의하기
마이리얼트립에서는 제품을 개선하기 위해 다양한 실험을 A/B 테스트를 진행하고, 핵심 지표를 기준으로 통계적 검정을 통해 테스트의 결론을 내리고 있습니다.
기존 결과를 산출하던 방식은 A/B Calculator 사이트에서 값을 직접 입력하여 결괏값을 얻곤 했는데요, 이런 방식은 값을 수기로 하나하나 입력해야 한다는 번거로움이 존재합니다.
이를 해결하고자, 쿼리에서 집계된 값을 바탕으로 A/B Test의 결과를 바로 출력해줄 수 있는 UDF 를 정의하게 되었습니다.
시나리오
상품 클릭률 개선을 위해 특정 지면에서의 상품 노출 위치에 대한 A/B Test를 진행한다고 가정해보겠습니다. ( A : 기존 위치, B : 변경 위치)
이 실험에서 가설은 아래와 같습니다.
H0(귀무가설): A안과 B안의 상품 클릭률은 동일할 것이다.H1(대립가설): A안과 B안의 상품 클릭률은 다를 것이다.
=> 보수적으로 양측 검정으로 세팅
- Z값 구하기
udf.z_score(집단 A 모수, 집단 A 전환수, 집단 B 모수, 집단 B 전환수, 신뢰수준)
2. 정규분포표 값 계산 함수
udf.normal_cdf(z값, 평균, 표준편차)
- Z값을 구했다면, 해당 값이 정규분포표 상에서 어떤 확률 값을 가지는지 계산해야 합니다.
3. P value 계산
udf.p_value(집단 A 모수, 집단 A 전환수, 집단 B 모수, 집단 B 전환수, 신뢰수준)
- 검정하고자 하는 가설에 따라 해당 부분의 수식을 바꾸며 p value를 계산 할 수 있습니다.
4. A/B Test 결과 출력
udf.ab_result(집단 A 모수, 집단 A 전환수, 집단 B 모수, 집단 B 전환수, 신뢰수준)
- 앞서 정의한 p value 함수와 신뢰수준을 바탕으로 검정 결괏값을 산출합니다.
실험 결과로 다음과 같은 값을 얻었다면, 정의한 udf.ab_result 함수를 활용하여 A/B Test 결과를 출력할 수 있습니다.
- A 안 : 유입 : 70,000 / 상품클릭 : 1,400 (전환율 : 2.0%)
- B안 : 유입 : 70,000 / 상품클릭 : 1,560 (전환율 : 2.2%)
쿼리의 결과는 A/B Calculator 사이트의 결과와 동일합니다.
이렇게 BigQuery UDF의 정의부터 활용법까지 살펴보았는데요, 사용해보니 편한점이 많아서 자주 정의해서 사용하고 공유해야겠다는 생각이 듭니다.
BigQuery UDF 커뮤니티도 있으니 다양한 함수들 활용해보셔도 좋을것 같네요 : )
- GoogleCloudPlatform/bigquery-utils : 빅쿼리 github UDF 커뮤니티
