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분 정도 소요
카일스쿨 유튜브 채널을 만들었습니다. 데이터 분석, 커리어에 대한 내용을 공유드릴 예정입니다.
PM을 위한 데이터 리터러시 강의를 만들었습니다. 문제 정의, 지표, 실험 설계, 문화 만들기, 로그 설계, 회고 등을 담은 강의입니다
이 글이 도움이 되셨거나 의견이 있으시면 댓글 남겨주셔요.
