liminfo

BigQuery Reference

BigQuery SQL 문법, UDF, 파티셔닝, ML 함수 레퍼런스

16개 결과

BigQuery Reference 소개

BigQuery 레퍼런스는 Google Cloud의 완전 관리형 서버리스 데이터 웨어하우스인 Google BigQuery를 위한 집중형 검색 SQL 치트 시트입니다. 표준 SQL과 달리 BigQuery에는 데이터 분석가, 데이터 엔지니어, ML 실무자가 숙달해야 할 고유한 기능이 있습니다. 중첩·반복 데이터 타입(구조화된 레코드를 위한 STRUCT와 반복 값을 위한 ARRAY), 배열을 행으로 평탄화하는 UNNEST 함수, 엣지 케이스를 우아하게 처리하는 BigQuery 전용 함수들(SAFE_DIVIDE는 0으로 나누기 오류 대신 NULL 반환)이 대표적입니다. 이 레퍼런스는 타입, 함수, ML, DDL, 최적화 5개 카테고리에 걸쳐 이러한 BigQuery 전용 구문을 다룹니다.

함수 카테고리는 대부분의 분석 쿼리를 구동하는 날짜/시간 유틸리티를 포함합니다. TIMESTAMP 컬럼을 사람이 읽을 수 있는 문자열로 포맷하는 FORMAT_TIMESTAMP, 시계열 데이터를 일/주/월/분기/연도 단위로 그룹화하는 DATE_TRUNC, date spine 조인에 사용되는 달력 시퀀스를 생성하는 GENERATE_DATE_ARRAY가 있습니다. QUALIFY 절은 BigQuery에서 가장 강력하지만 잘 알려지지 않은 기능으로, 서브쿼리 없이 SELECT 문에서 직접 윈도우 함수 결과를 필터링하여 "사용자별 최신 행 가져오기" 같은 패턴을 단일 쿼리로 구현할 수 있습니다. ML 카테고리는 완전한 BigQuery ML 워크플로를 다룹니다. 학습을 위한 CREATE MODEL, 모델 평가를 위한 ML.EVALUATE, 새 데이터 스코어링을 위한 ML.PREDICT가 포함됩니다.

DDL과 최적화 섹션은 운영 수준의 BigQuery 사용에 중요합니다. EXPORT DATA는 쿼리 결과를 Cloud Storage에 CSV, JSON, Avro, Parquet 형식으로 직접 내보낼 수 있어 데이터 파이프라인에 유용합니다. MERGE 문은 BigQuery 테이블에서 완전한 UPSERT 작업(삽입 또는 업데이트)을 가능하게 하며, 증분 데이터 로딩 패턴에 필수적입니다. 성능과 비용 최적화를 위해 PARTITION BY는 파티션 프루닝을 가능하게 하는 날짜 파티션 또는 정수 범위 파티션 테이블을 생성합니다. CLUSTER BY는 파티션 내 데이터를 물리적으로 구성하여 필터 쿼리를 빠르게 만듭니다. CREATE FUNCTION(UDF)은 표준 SQL로 표현할 수 없는 복잡한 비즈니스 로직을 위한 재사용 가능한 SQL 또는 JavaScript 함수를 정의합니다.

주요 기능

  • 타입: STRUCT(이름 있는 필드 레코드), ARRAY(반복 값), UNNEST(cross join으로 배열을 행으로 평탄화)
  • 함수: SAFE_DIVIDE(0 나누기 시 NULL), FORMAT_TIMESTAMP, DATE_TRUNC(일/주/월/연), GENERATE_DATE_ARRAY
  • QUALIFY 절: 윈도우 함수 결과 인라인 필터(예: ROW_NUMBER() OVER PARTITION = 1로 중복 제거)
  • BigQuery ML: CREATE MODEL(선형/로지스틱 회귀), ML.EVALUATE(모델 메트릭), ML.PREDICT(스코어링)
  • DDL: GCS로 EXPORT DATA(CSV/JSON/Avro/Parquet), UPSERT를 위한 MERGE(matched/not matched 조건)
  • 최적화: PARTITION BY(날짜/정수 범위 파티셔닝), CLUSTER BY(정렬된 블록 구성)
  • CREATE FUNCTION(UDF): 타입이 지정된 파라미터와 반환 타입이 있는 SQL 및 JavaScript 사용자 정의 함수
  • 모든 예제에 실제 BigQuery SQL 구문과 현실적인 테이블 및 컬럼 이름 사용

자주 묻는 질문

BigQuery에서 STRUCT와 ARRAY의 차이는 무엇인가요?

STRUCT는 이름이 있는 타입 지정 필드들을 담는 컨테이너로, 행 안의 행과 같습니다. 예: STRUCT("John" AS name, 30 AS age)는 단일 중첩 레코드를 생성합니다. ARRAY는 같은 타입의 값들로 이루어진 순서 있는 목록입니다. 이 두 타입은 자주 결합됩니다. STRUCT의 ARRAY는 단일 행에 저장된 반복되는 중첩 레코드를 나타냅니다(일대다 관계처럼). BigQuery는 대용량 데이터셋에서 비용이 큰 JOIN을 피하기 위해 이러한 비정규화된 중첩 구조를 위해 설계되었습니다.

BigQuery에서 UNNEST는 어떻게 동작하고 언제 사용해야 하나요?

UNNEST는 ARRAY를 받아 각 요소를 별도의 행으로 변환합니다. FROM 절에서 cross join(묵시적 쉼표 또는 명시적)과 함께 사용합니다. SELECT item FROM my_table, UNNEST(tags) AS item은 원래 행마다 태그 하나씩 행을 반환합니다. UNNEST는 BigQuery 스키마의 중첩/반복 필드 쿼리에 필수적입니다(Firestore 내보내기, GA4 이벤트 데이터, 스토리지 효율성을 위해 설계된 스키마에서 흔함). STRUCT 필드와 결합: SELECT tag.name, tag.value FROM my_table, UNNEST(tag_array) AS tag.

SAFE_DIVIDE란 무엇이고 나누기 연산자보다 나은 이유는?

표준 나누기 연산자(/)는 분모가 0일 때 "division by zero" 오류를 발생시킵니다. SAFE_DIVIDE(분자, 분모)는 오류 대신 NULL을 반환합니다. 이는 사용자 코호트에 대한 비율이나 평균을 계산하는 분석 쿼리에서 중요합니다. 일부 코호트는 사용자나 이벤트가 0개일 수 있으며, 하나의 0 값이 전체 쿼리를 실패시킬 수 있습니다. sessions이 0이 될 수 있을 때는 revenue / sessions 대신 SAFE_DIVIDE(revenue, sessions)를 사용하세요.

BigQuery의 QUALIFY 절은 어떻게 동작하나요?

QUALIFY는 윈도우 함수 결과를 기반으로 행을 필터링합니다. WHERE 절과 비슷하지만 윈도우 함수 이후에 평가됩니다. QUALIFY 없이는 서브쿼리가 필요합니다. SELECT * FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY ts DESC) AS rn FROM events) WHERE rn = 1. QUALIFY를 사용하면: SELECT * FROM events QUALIFY ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY ts DESC) = 1. 이것이 중복 제거의 표준 패턴입니다. 사용자별 최신 이벤트만 유지합니다.

PARTITION BY와 CLUSTER BY가 BigQuery 성능을 어떻게 향상시키나요?

PARTITION BY는 날짜 컬럼이나 정수 범위를 기반으로 테이블을 세그먼트(파티션)로 나눕니다. 쿼리가 파티션 컬럼을 필터링할 때(예: WHERE event_date = "2024-01-01"), BigQuery는 전체 테이블 대신 해당 파티션만 스캔합니다. 처리되는 바이트와 비용이 크게 줄어듭니다. CLUSTER BY는 클러스터 컬럼 기준으로 각 파티션 내 데이터를 물리적으로 정렬합니다. 클러스터 컬럼을 필터링하는 쿼리(예: WHERE user_id = 12345)는 더 적은 스토리지 블록을 스캔합니다. 가장 자주 사용하는 날짜 필터로 파티셔닝하고, 그 다음으로 선택적인 필터 컬럼으로 클러스터링하세요.

BigQuery ML이란 무엇이고 어떻게 사용하나요?

BigQuery ML은 데이터를 Python이나 R로 내보내지 않고 SQL에서 직접 ML 모델을 학습, 평가, 사용할 수 있게 합니다. CREATE MODEL은 model_type(linear_reg, logistic_reg, boosted_tree_classifier, kmeans 등)을 정의하고 SELECT 쿼리로 학습합니다. ML.EVALUATE는 학습된 모델의 성능 지표(RMSE, 정확도, AUC 등)를 반환합니다. ML.PREDICT는 새 데이터에 모델을 적용하고 예측을 반환합니다. Python 환경 관리나 데이터 이동 없이 ML 기능을 원하는 분석가에게 이상적입니다.

BigQuery에서 MERGE 문으로 UPSERT를 어떻게 구현하나요?

MERGE는 대상 테이블과 소스 사이에 행이 일치하는지 여부에 따라 조건부 INSERT, UPDATE, DELETE를 수행합니다. 패턴: MERGE target T USING source S ON T.id = S.id WHEN MATCHED THEN UPDATE SET T.value = S.value WHEN NOT MATCHED THEN INSERT (id, value) VALUES (S.id, S.value). 이것이 증분 데이터 로딩의 표준 패턴입니다. 기존 레코드는 업데이트하고 새 레코드는 삽입하는 것을 단일 원자적 작업으로 수행합니다. MERGE는 대용량 테이블에서 DELETE + INSERT보다 효율적입니다.

BigQuery에서 UDF(사용자 정의 함수)를 어떻게 만들고 사용하나요?

CREATE FUNCTION으로 영구 UDF를 정의합니다. CREATE FUNCTION my_dataset.my_fn(x INT64) RETURNS INT64 AS (x * 2). 현재 쿼리 세션에서만 유효한 임시 UDF는 CREATE TEMP FUNCTION을 사용합니다. UDF는 SQL(단순 표현식용) 또는 JavaScript(복잡한 문자열 처리, 파싱, 로직용)로 작성할 수 있습니다. 내장 함수처럼 호출합니다. SELECT my_dataset.my_fn(column) FROM table. UDF는 여러 쿼리에 반복될 비즈니스 로직(예: 회계 분기 계산, PII 필드 마스킹)을 캡슐화하는 데 유용합니다.