어쩐지 오늘은

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가 중요한 경우 추천
쉘 커맨드에서 데이터를 처리하는 것처럼 사용할 수 있음
문법 정리
문법을 잘 정리된 형태로 보면 좋을 것 같아, 별도의 웹페이지로 제작함
    
BigQuery Guide Book - 빅쿼리 가이드북 - PIPE Cheatsheet 참고
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 DESC 
    
PIPE
  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 PIPE 사용법 : SQL의 새로운 형태

댓글