BigQuery PIPE 사용법 : SQL의 새로운 형태
- 2024년 연말에 새로 생긴 BigQuery Pipe 사용법에 대해 작성한 글입니다
- 이 글은 2025년 3월 초 기준으로 작성되었습니다
- 파이프 문법 Cheatsheet가 궁금하면 BigQuery 가이드북을 방문해주세요
- 키워드 : BigQuery Pipe, BigQuery PIPE syntax
BigQuery Pipe Syntax
- 이 부분은 구글의 논문인 SQL Has Problems. We Can Fix Them: Pipe Syntax In SQL을 참고했음
기존 SQL의 문제점
- SQL은 데이터 작업을 위한 표준 언어가 되었고, 대부분의 SQL 엔진 대부분 비슷한 문법을 사용함
- 하지만 SQL은 배우기 어렵고, 사용하기 어렵고, 확장하기 어려움
- 이런 문제를 해결하기 위해 새로운 언어를 개발해서 제안한 사람들이 있으나, 새로운 언어를 채택하는 과정은 반발심도 있어서 쉽지 않음
- SQL은 다음과 같은 문제점이 존재함
- 고정된 구문 순서
- 중복된 구문(예 : 필터링)
- 서브 쿼리 사용
- 내부 -> 외부의 데이터 흐름
- 동일한 컬럼들의 반복
고정된 구문 순서
- SELECT - FROM - WHERE - GROUP BY 순서가 고정되어 있어서 유연성이 떨어짐
- 문법과 의미 관점에서 순서가 다름
- 데이터는 FROM에서 나오기 때문에, FROM 절부터 생각해야 함
- 그러나 쿼리의 시작이 SELECT라 헷갈리게 됨
- 처음 SQL을 학습할 때 많은 분들이 이 부분에 대해 어려워 함
중복된 구문(예 : 필터링)
- 데이터를 필터링할 때, WHERE, HAVING, QUALIFY를 사용할 수 있음
- 이 3개가 사용되는 경우는 모두 다르지만, 결국에 데이터를 필터링할 때 사용됨. 그래서 처음 SQL을 공부하는 사람이 헷갈리게 됨. 언제 WHERE? 언제 HAVING?
- 아래 쿼리를 보면 WHERE, HAVING, QUALIFY에 대해 작성되어 있음. 궁극적으로 이걸 하나로 통일할 수는 없을까?
SELECT
customer_name,
order_date,
SUM(amount) AS total,
RANK() OVER (PARTITION BY customer_name ORDER BY SUM(amount) DESC) AS rk
FROM customers
JOIN orders ON customers.id = orders.customer_id
WHERE region = 'East' -- 집계 전 필터링
GROUP BY customer_name, order_date
HAVING SUM(amount) > 1000 -- 집계 후 필터링
QUALIFY rk = 1
서브 쿼리 사용
- 다중 집계, 계산된 표현의 재사용 등을 위해 서브 쿼리를 사용해야 함
- 서브 쿼리는 들여쓰기가 많아져서 가독성을 해치고, 편집할 때 실수를 유발할 가능성이 생김
- 간단한 연산이지만 서브 쿼리가 필요한 경우
- 두 번 이상 합산하기
- 지원되는 세 곳(WHERE, HAVING, QUALIFY) 이외의 위치에 필터링을 해야할 때, 서브 쿼리로 한번 감싸서 처리해야 함
- BigQuery ML을 사용해서 임베딩 모델을 예측하고, 그 후에 예측할 때 서브 쿼리를 사용해야 함(2번 연속으로 서브 쿼리 사용)
SELECT *
FROM ML.PREDICT(
MODEL `my_project.imdb_classifier`,
(
SELECT *
FROM ML.PREDICT(
MODEL `my_project.nnlm_embedding_model`,
(SELECT '<text>' AS input, 7 AS rating)
)
)
)
내부 -> 외부의 데이터 흐름
- 서브 쿼리, JOIN 등이 혼합된 쿼리를 볼 때 제일 안쪽에 있는 FROM을 확인하고, 바깥쪽으로 쿼리문을 읽어야 함
- 이 과정이 직관적이지 않고, 쿼리를 보는 시간을 늘림
- 아래 쿼리를 보면 FROM customer 부터 이해를 하고, 그 안에 있는 부분 => 바깥을 이해해야 함
SELECT
c_count,
COUNT(*) AS custdist
FROM (
SELECT
c_custkey,
COUNT(o_orderkey) c_count
FROM customer
LEFT OUTER JOIN orders ON c_custkey = o_custkey
AND o_comment NOT LIKE '%unusual%packages%'
GROUP BY c_custkey
) AS c_orders
GROUP BY
c_count
ORDER BY
custdist DESC,
c_count DESC;
동일한 컬럼들의 반복
- SELECT, GROUP BY, ORDER BY 등에 중복된 컬럼을 사용하는 경우가 있음
- 하나의 컬럼을 변경할 때, 다른 곳을 모두 변경해야 함(물론 GROUP BY는 GROUP BY ALL로 대체할 수 있긴 함)
- 아래 쿼리에서 SELECT에 있는 컬럼을 바꾸면 GROUP BY, ORDER BY도 수정해야 할 수 있음
SELECT
department,
employee_name,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM employees
GROUP BY
department,
employee_name,
salary
ORDER BY
department,
rank
GoogleSQL의 시도 : PIPE 구문 추가
- GoogleSQL에선 새로운 언어를 만드는 방법이 아닌 기존 SQL 기능을 확장해서 문제를 해결하려고 함
- SQL에 data flow syntax를 추가했고 확장하기 쉽고 유연하게 되었음
- data flow syntax는 쉘 커맨드의 파이프 구문, 스파크의 체이닝과 유사하다고 보면 됨
- SQL에서 표준 절은 정의된 하나의 순서로 발생함. 파이프 구문을 사용하면 임의의 순서로 연산을 만들 수 있음
FROM Produce
|> WHERE item != 'bananas' AND category IN ('fruit', 'nut')
|> AGGREGATE COUNT(*) AS num_items, SUM(sales) AS total_sales
GROUP BY item
|> ORDER BY item DESC
BigQuery PIPE 문법의 특징
- Linear Flow : 순차적 실행
- Reduced Nesting : 서브쿼리를 사용하지 않음
- 재생산성이 가능하고 직관적임
- FROM 부터 시작함
- 파이프 연산자로
|>을 사용함 - 파이프 연산자는 유효한 쿼리 뒤에 계속 추가할 수 있음
- 아래 이미지를 보면 파이프 문법에선 의미론적인 순서와 동일해짐
파이프 문자(|>)를 사용하는 이유
- 친숙함과 선례 관점
- (1) 유닉스 파이프는 널리 이해되는 패러다임으로, 이 구문과 동작을 친숙하고 이해하기 쉽게 만듦
- (2) 다른 데이터 처리 언어(예: KQL)도 파이프 구문을 사용함
- (3) 파이프와 같은 데이터 흐름은 Pandas DataFrames와 Apache Beam 등의 데이터 처리 프레임워크에서도 사용됨
- 기술적 관점
- (1) 파이프 연산자를 사용하면 어디서 구문이 시작되는지 명확히 알 수 있음
- (2) 파싱하는 방법이 간단해져서 확장성이 증가됨
- (3) 파이프 연산자는 이전 단계의 결과에 존재하는 컬럼만 접근할 수 있음. 명확해지고 예측이 가능해짐
- (4) 파이프 연산자를 사용해서 순차적으로 연산을 진행할 수 있음
- 가독성 관점
- (1) 언제 파이프 연산자로 전환되는지 인식하기 수월함
(2) 여러 절로 구성된 파이프 연산자를 하나의 논리적인 단위로 인식할 수 있게 해줌
|> AGGREGATE SUM(sales) AS total_sales, AVG(price) AS avg_price GROUP BY region, product- (3) 구조적 가시성 : 쿼리를 순차적인 N개의 연산으로 표현 가능. 쿼리의 논리적 흐름을 시각적으로 더 잘 반영함
- 파이프 문자로
|을 사용할 수 있지만, 비트 연산에 사용되기 때문에 헷갈리고 충돌이 생기게 됨. 그래서|>을 선택함
BigQuery PIPE 문법
- 공식 문서에서 확인할 수 있고, 앞으로 계속 늘어날 것으로 예상
- 일반적으로 사용하는 FROM, SELECT, JOIN, WHERE, LIMIT, ORDER BY은 동일하다고 생각하면 됨
- 아래에선 PIPE 문법 중 특별한 부분만 기록
SET
- SELECT * REPLACE(expression AS col)와 동일한 표현
- 컬럼을 다른 값으로 대체함
(
SELECT 1 AS x, 11 AS y
UNION ALL
SELECT 2 AS x, 22 AS y
)
|> SET x = x * x, y = 3;
/*
+---+---+
| x | y |
+---+---+
| 1 | 3 |
| 4 | 3 |
+---+---+
*/
EXTEND
- SELECT *, new_col과 동일한 표현
- 기존 테이블의 모든 컬럼에 새로운 컬럼 추가
- 파이프 문법에선 이전 결과에 나오는 컬럼만 선택할 수 있어서 EXTEND를 사용해야 할 수 있음
(
SELECT 'apples' AS item, 2 AS sales
UNION ALL
SELECT 'carrots' AS item, 8 AS sales
)
|> EXTEND item IN ('carrots', 'oranges') AS is_orange;
/*
+---------+-------+------------+
| item | sales | is_orange |
+---------+-------+------------+
| apples | 2 | FALSE |
| carrots | 8 | TRUE |
+---------+-------+------------+
*/
AGGREGATE
- 전체 테이블 또는 컬럼의 값에 대한 집계 수행
전체 테이블 집계
-- Full-table aggregation ( SELECT 'apples' AS item, 2 AS sales UNION ALL SELECT 'bananas' AS item, 5 AS sales UNION ALL SELECT 'apples' AS item, 7 AS sales ) |> AGGREGATE COUNT(*) AS num_items, SUM(sales) AS total_sales; /* +-----------+-------------+ | num_items | total_sales | +-----------+-------------+ | 3 | 14 | +-----------+-------------+ */GROUP BY와 함께 사용해서 집계
-- Aggregation with grouping ( SELECT 'apples' AS item, 2 AS sales UNION ALL SELECT 'bananas' AS item, 5 AS sales UNION ALL SELECT 'apples' AS item, 7 AS sales ) |> AGGREGATE COUNT(*) AS num_items, SUM(sales) AS total_sales GROUP BY item; /* +---------+-----------+-------------+ | item | num_items | total_sales | +---------+-----------+-------------+ | apples | 2 | 9 | | bananas | 1 | 5 | +---------+-----------+-------------+ */GROUP AND ORDER BY를 사용하면 컬럼을 반복적으로 입력하지 않아도 괜찮음
-- Aggregation with grouping and order by ( SELECT 'apples' AS item, 2 AS sales UNION ALL SELECT 'bananas' AS item, 5 AS sales UNION ALL SELECT 'apples' AS item, 7 AS sales ) |> AGGREGATE COUNT(*) AS num_items, SUM(sales) AS total_sales GROUP AND ORDER BY item;
PIVOT
- StandardSQL의 PIVOT 함수를 사용할 수도 있음
- 열(Row)을 컬럼(Column)으로 회전
(
SELECT "kale" AS product, 51 AS sales, "Q1" AS quarter
UNION ALL
SELECT "kale" AS product, 4 AS sales, "Q1" AS quarter
UNION ALL
SELECT "kale" AS product, 45 AS sales, "Q2" AS quarter
UNION ALL
SELECT "apple" AS product, 8 AS sales, "Q1" AS quarter
UNION ALL
SELECT "apple" AS product, 10 AS sales, "Q2" AS quarter
)
|> PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2'));
/*
+---------+----+------+
| product | Q1 | Q2 |
+---------+-----------+
| kale | 55 | 45 |
| apple | 8 | 10 |
+---------+----+------+
*/
UNPIVOT
- 컬럼(Column)을 열(Row)로 회전
(
SELECT 'kale' as product, 55 AS Q1, 45 AS Q2
UNION ALL
SELECT 'apple', 8, 10
)
|> UNPIVOT(sales FOR quarter IN (Q1, Q2));
/*
+---------+-------+---------+
| product | sales | quarter |
+---------+-------+---------+
| kale | 55 | Q1 |
| kale | 45 | Q2 |
| apple | 8 | Q1 |
| apple | 10 | Q2 |
+---------+-------+---------+
*/
UNION ALL BY NAME
- UNION ALL을 할 때 이름으로 자동으로 매칭해줌(UNION ALL만 사용하면 순서도 맞춰줘야 함
SELECT 1 AS one_digit, 10 AS two_digit
|> UNION ALL BY NAME
(SELECT 20 AS two_digit, 2 AS one_digit);
/*
+-----------+-----------+
| one_digit | two_digit |
+-----------+-----------+
| 1 | 10 |
| 2 | 20 |
+-----------+-----------+
*/
CALL
- 테이블 값 함수(Table Value Function, TVF) 호출
- TVF가 궁금하면 공식 문서 참고
FROM input_table
|> CALL tvf1(arg1)
|> CALL tvf2(arg2, arg3);
WINDOW
- 윈도우 함수 계산하고 싶을 때 사용
- 윈도우 함수는 AGGREGATE와 사용하는 것이 아니고 WINDOW를 명시해서 사용해야 함
(
SELECT 'apples' AS item, 2 AS sales
UNION ALL
SELECT 'bananas' AS item, 5 AS sales
UNION ALL
SELECT 'carrots' AS item, 8 AS sales
)
|> WINDOW SUM(sales) OVER() AS total_sales;
/*
+---------+-------+-------------+
| item | sales | total_sales |
+---------+-------+-------------+
| apples | 2 | 15 |
| bananas | 5 | 15 |
| carrots | 8 | 15 |
+---------+-------+-------------+
*/
DROP
- 컬럼을 제외하고 싶을 때 사용
SELECT 'apples' AS item, 2 AS sales, 'fruit' AS category
|> DROP sales, category;
/*
+--------+
| item |
+--------+
| apples |
+--------+
*/
RENAME
- 컬럼의 이름을 변경하고 싶은 경우 사용
SELECT 1 AS x, 2 AS y, 3 AS z
|> AS t
|> RENAME y AS renamed_y
|> SELECT *, t.y AS t_y;
/*
+---+-----------+---+-----+
| x | renamed_y| z | t_y |
+---+-----------+---+-----+
| 1 | 2 | 3 | 2 |
+---+-----------+---+-----+
*/
AS
- Input 테이블에 대해 Alias를 지정하고 싶을 때 사용
- WITH base 대신 AS를 바로 사용해서 쓸 수 있음
(
SELECT "000123" AS id, "apples" AS item, 2 AS sales
UNION ALL
SELECT "000456" AS id, "bananas" AS item, 5 AS sales
) AS sales_table
|> AGGREGATE SUM(sales) AS total_sales GROUP BY id, item
-- The sales_table alias is now out of scope. We must introduce a new one.
|> AS t1
|> JOIN (SELECT 456 AS id, "yellow" AS color) AS t2
ON CAST(t1.id AS INT64) = t2.id
|> SELECT t2.id, total_sales, color;
/*
+-----+-------------+--------+
| id | total_sales| color |
+-----+-------------+--------+
| 456 | 5 | yellow |
+-----+-------------+--------+
*/
언제 유용할까?
- BigQuery 처음 학습하는 분들에게 추천
- 그러나 회사에서 BigQuery를 사용하지 않으면 애매함(다른 DB에선 아직 지원을 안할 수 있으므로)
- Text to SQL을 구현할 때 추천
- 기존의 SQL 쿼리에서 파싱이 어려웠는데, 이젠 명확하게 쿼리를 분석하기 좋음
- 자연어로 쿼리를 생성할 때, 파이프를 사용하는 것이 더 유리할 것으로 예상
- 유사하게 Programmatic하게 쿼리를 생성할 때도 추천
- 데이터 엔지니어링 파이프라인
- 데이터 엔지니어링 파이프라인 중 Flow가 중요한 경우 추천
- 쉘 커맨드에서 데이터를 처리하는 것처럼 사용할 수 있음
문법 정리
- 문법을 잘 정리된 형태로 보면 좋을 것 같아, 별도의 웹페이지로 제작함
StandardSQL vs Pipe 쿼리 비교
- 초보자를 위한 BigQuery(SQL) 입문 강의에서 진행한 연습 문제 쿼리를 비교
StandardSQL
SELECT p.type1, COUNT(tp.id) AS pokemon_cnt FROM ( SELECT id, trainer_id, pokemon_id, status FROM basic.trainer_pokemon WHERE status IN ("Active", "Training") ) AS tp LEFT JOIN basic.pokemon AS p ON tp.pokemon_id = p.id WHERE type1 = "Grass" GROUP BY type1 ORDER BY 2 DESCPIPE
FROM basic.trainer_pokemon AS tp |> WHERE status IN ('Active', 'Training') |> LEFT JOIN basic.pokemon AS p ON tp.pokemon_id = p.id |> WHERE type1 = 'Grass' |> AGGREGATE COUNT(tp.id) AS pokemon_cnt GROUP BY p.type1 |> ORDER BY pokemon_cnt DESC
- 확실히 더 직관적이고, 짧아짐
Firebase, 앱 로그 데이터의 퍼널 쿼리 비교
- BigQuery 활용편(퍼널 분석, 리텐션 분석)에서 진행한 쿼리 비교
StandardSQL
WITH base AS ( SELECT event_date, event_timestamp, event_name, user_id, user_pseudo_id, platform, MAX(IF(event_param.key = "firebase_screen", event_param.value.string_value, NULL)) AS firebase_screen, MAX(IF(event_param.key = "session_id", event_param.value.string_value, NULL)) AS session_id FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS event_param WHERE event_date BETWEEN "2022-08-01" AND "2022-08-18" GROUP BY ALL ), filter_event_and_concat_event_and_screen AS ( SELECT * EXCEPT(event_name, firebase_screen, event_timestamp), CONCAT(event_name, "-", firebase_screen) AS event_name_with_screen, DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime FROM base WHERE event_name IN ("screen_view", "click_payment") ) SELECT event_date, # 일자별로 퍼널별 유저 수 쿼리 event_name_with_screen, CASE WHEN event_name_with_screen = "screen_view-welcome" THEN 1 WHEN event_name_with_screen = "screen_view-home" THEN 2 WHEN event_name_with_screen = "screen_view-food_category" THEN 3 WHEN event_name_with_screen = "screen_view-restaurant" THEN 4 WHEN event_name_with_screen = "screen_view-cart" THEN 5 WHEN event_name_with_screen = "click_payment-cart" THEN 6 ELSE NULL END AS step_number, COUNT(DISTINCT user_pseudo_id) AS cnt FROM filter_event_and_concat_event_and_screen GROUP BY ALL HAVING step_number IS NOT NULL ORDER BY event_date, step_number- PIPE
- 좀 더 간결하게 만들 수 있을 것 같지만 일단 이정도로 올려봄
FROM advanced.app_logs |> CROSS JOIN UNNEST(event_params) AS event_param |> WHERE event_date BETWEEN "2022-08-01" AND "2022-08-18" AND event_name IN ("screen_view", "click_payment") |> AGGREGATE MAX(IF(event_param.key = "firebase_screen", event_param.value.string_value, NULL)) AS firebase_screen, MAX(IF(event_param.key = "session_id", event_param.value.string_value, NULL)) AS session_id GROUP BY event_date, event_timestamp, event_name, user_id, user_pseudo_id, platform |> SELECT * EXCEPT(event_name, firebase_screen, event_timestamp), CONCAT(event_name, "-", firebase_screen) AS event_name_with_screen, DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime |> EXTEND CASE WHEN event_name_with_screen = "screen_view-welcome" THEN 1 WHEN event_name_with_screen = "screen_view-home" THEN 2 WHEN event_name_with_screen = "screen_view-food_category" THEN 3 WHEN event_name_with_screen = "screen_view-restaurant" THEN 4 WHEN event_name_with_screen = "screen_view-cart" THEN 5 WHEN event_name_with_screen = "click_payment-cart" THEN 6 ELSE NULL END AS step_number |> WHERE step_number IS NOT NULL |> AGGREGATE COUNT(DISTINCT user_pseudo_id) AS cnt GROUP BY event_date, event_name_with_screen, step_number |> ORDER BY event_date, step_number
StandardSQL -> PIPE 마이그레이션 가이드
- 1주일 정도 파이프 문법을 사용하면서 어떻게 옮겨야 할까? 생각해보고 작성한 내용입니다. 추후에 더 추가될 수도 있음
- FROM 절은 항상 쿼리의 시작
- 기존: FROM 절이 SELECT 절 뒤에 위치
- 파이프: FROM 절을 쿼리의 가장 앞으로 이동
- JOIN은 FROM 절 안에 포함하거나 파이프 연산자로 분리
- 기존: FROM 절 안에서 JOIN 사용
- 파이프: FROM 절 안에 JOIN을 그대로 사용하거나, 별도의 파이프 연산자로 분리
- WHERE 절은 필터링이 필요한 위치에 파이프 연산자를 사용하여 배치
- 기존: WHERE 절은 FROM 절과 GROUP BY 절 사이에 위치
- 파이프: WHERE 절을 필요한 위치에 파이프 연산자로 연결
- SELECT 절은 주로 쿼리의 뒷부분에 파이프 연산자를 사용하여 배치
- 기존: SELECT 절이 쿼리의 맨 앞에 위치
- 파이프: SELECT 절을 필요한 위치에 파이프 연산자로 연결, 주로 쿼리의 뒷부분에 배치
- GROUP BY와 집계 함수는 AGGREGATE 파이프 연산자를 사용
- 기존: GROUP BY와 집계 함수를 SELECT 절과 함께 사용
- 파이프: GROUP BY와 집계 함수를 AGGREGATE 파이프 연산자로 결합하여 사용
- 서브쿼리는 파이프 연산자를 사용하여 단계적으로 처리
- 기존: 여러 개의 중첩된 서브쿼리 사용
- 파이프: 파이프 연산자를 사용하여 서브쿼리를 단계적으로 연결(AS 사용)
- 불필요한 절과 구문은 제거하고 쿼리를 간소화한다.
- 기존: 복잡하고 중복된 구문 사용
- 파이프: 파이프 연산자를 활용하여 쿼리를 간소화하고 가독성 향상
참고 자료
- SQL Has Problems. We Can Fix Them: Pipe Syntax In SQL
- BigQuery Pipe 공식 문서
- Revolutionizing SQL with pipe syntax, now available in BigQuery and Cloud Logging
- 글 작성하는데 걸린 시간 : 167분
- 하고자 하는 이야기, 개요 정리 : 10분
- 초안 글 작성 : 112분
- 초안 후 수정 : 45분
- 이 개념에 대한 학습은 150분 정도 소요
카일스쿨 유튜브 채널을 만들었습니다. 데이터 사이언스, 성장, 리더십, BigQuery 등을 이야기할 예정이니, 관심 있으시면 구독 부탁드립니다 :)
PM을 위한 데이터 리터러시 강의를 만들었습니다. 문제 정의, 지표, 실험 설계, 문화 만들기, 로그 설계, 회고 등을 담은 강의입니다
이 글이 도움이 되셨거나 다양한 의견이 있다면 댓글 부탁드립니다 :)
