SQL 쿼리 빌더의 두 가지 유형

AI 요약

SQL 코드는 사람이 읽기 좋도록 설계된 언어지만, 실제로는 프로그램이 데이터베이스 질의를 만들 때 생성하는 경우가 많습니다. SQL 문법은 영어와 비슷한 겉모습에 비해 규칙이 복잡하고 절의 순서도 정해져 있어, 애플리케이션은 SQL 쿼리 빌더를 사용하곤 합니다. 이 글은 쿼리 빌더를 겉으로 비슷한 두 유형으로 나눠, 내부 동작과 장단점을 설명합니다.

파이프라인 순서가 드러내는 차이

FunSQL에서 성별 조건을 적용하고 출생 연도로 정렬한 뒤 100개를 제한하고 환자 ID를 선택하는 쿼리는 From, Where, Order, Limit, Select 노드를 잇는 파이프라인으로 작성합니다. Ruby의 Active Record, PHP의 Laravel Query Builder, C#의 EF/LINQ, R의 dbplyr도 겉보기에는 비슷한 방식으로 쿼리를 조립합니다.

하지만 노드 순서를 바꿔 Order와 Limit을 Where 앞에 놓으면 결과가 달라지는지가 구현 유형을 가릅니다. FunSQL, EF/LINQ, dbplyr에서는 결과가 달라집니다. ‘가장 오래된 남성 환자 100명’을 찾던 쿼리가 ‘가장 오래된 환자 100명 중 남성’을 찾게 됩니다. Active Record와 Laravel에서는 순서를 바꿔도 결과가 달라지지 않습니다.

데이터 지향 쿼리 빌더

FunSQL, EF/LINQ, dbplyr는 파이프라인 노드를 실제 데이터 처리 단계처럼 해석합니다. 각 노드는 입력 데이터를 변환하고 다음 노드로 넘기는 작업을 나타냅니다. 쿼리 빌더가 데이터베이스 내용을 직접 읽는 것은 아니므로, 파이프라인은 최종 SQL 쿼리가 내야 할 결과를 지정합니다. 파이프라인 순서가 의미를 가지며 SQL로 바꾸는 과정에서 중첩 쿼리가 필요할 수 있습니다.

SQL 절은 FROM, JOIN, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT 순으로 배열됩니다. 파이프라인에서 LIMIT이 WHERE보다 먼저 오면 이 순서를 그대로 SQL 한 문장에 반영하기 어렵습니다. SQL-92는 쿼리를 다른 쿼리의 FROM 절 안에 중첩하는 방법을 도입했습니다. 쿼리 빌더는 파이프라인을 SQL 문법 순서에 맞는 작은 단위로 나누고, 각 단위를 쿼리로 만든 뒤 서로 중첩합니다. 결과적으로 복잡한 파이프라인은 여러 겹의 SQL과 반복되는 구문으로 표현될 수 있습니다.

문법 지향 쿼리 빌더

Active Record와 Laravel은 같은 파이프라인 모양을 제공하지만, 노드가 데이터 처리 단계를 뜻하지는 않습니다. 대신 노드마다 SQL 문법 트리의 SELECT, FROM, WHERE, ORDER BY 같은 슬롯을 채웁니다. 각 슬롯에 들어가는 내용만 같다면 슬롯을 채운 순서는 결과에 영향을 주지 않습니다. 글은 이 방식을 ‘문법 지향’ 쿼리 빌더라고 부릅니다. 이 유형은 구현하기 쉽고 SQL 기능을 폭넓게 지원할 수 있지만, SQL 문법 자체를 인터페이스로 드러내므로 절 순서의 제약을 그대로 이어받습니다.

반면 데이터 지향 빌더는 쿼리를 데이터 처리 노드로 나타냅니다. 필요한 노드를 조합해 파이프라인을 구성하기 쉽지만, 제공하는 노드가 충분한지와 SQL로 변환할 수 있는지가 관건입니다. EF/LINQ와 dbplyr는 각각 범용 쿼리 프레임워크인 LINQ와 dplyr를 SQL에 맞게 확장했습니다. 파이프라인을 실제 메모리 데이터로 처리하면 데이터베이스 인덱스를 활용하지 못해 비효율적이므로, 실행 대신 파이프라인 전체를 SQL로 변환합니다. 이 기법을 SQL pushdown이라고 합니다.

SQL pushdown의 한계와 FunSQL

범용 프레임워크는 SQL과 완전히 호환되도록 설계되지 않았습니다. 프레임워크의 표현을 SQL로 변환하지 못하는 경우가 있고, 반대로 SQL 기능 중에는 해당 프레임워크의 파이프라인으로 표현할 방법이 없는 것도 있습니다. 글은 SQL-86의 조인·필터·그룹화·집계·상관 서브쿼리에서 SQL-92의 다양한 조인과 중첩 쿼리, SQL:1999의 재귀 쿼리와 데이터 큐브, SQL:2003의 윈도 함수까지 기능 범위가 넓어졌다고 짚습니다. 저자는 LINQ와 dplyr가 이 기능 전체를 따라가지 못해, 이를 바탕으로 SQL을 생성하면 상당한 기능이 접근 불가능하다고 설명합니다.

FunSQL은 기존 프레임워크를 SQL에 맞춘 것이 아니라, SQL 기능을 표현하도록 새로 설계한 데이터 지향 쿼리 빌더입니다. 글에 따르면 상관 서브쿼리와 lateral join은 Bind 노드로, 집계와 윈도 함수는 Group 및 Partition 노드로, 재귀 쿼리는 Iterate 노드로 표현합니다. 저자는 FunSQL이 복잡한 데이터 처리 파이프라인과 SQL의 폭넓은 기능을 함께 다루는 쿼리 빌더라고 주장합니다. 각 노드에 명확한 데이터 처리 의미가 있으므로, 나중에는 파이프라인을 직접 실행하는 범용 쿼리 프레임워크로 발전시킬 여지도 있다고 덧붙입니다.

Lobsters 반응

• @lalitm — 글을 읽으면서 계속 고개를 끄덕였습니다. 그런데 끝까지 읽고도 BigQuery의 파이프 문법[1]을 다루지 않아 꽤 놀랐습니다. 만능 해결책은 아니지만, 데이터베이스가 pipe SQL을 지원하면 이 글과 일반적으로 SQL에 제기되는 비판을 상당 부분 해결하는 데 큰 진전이 될 것 같습니다. [1] https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/pipe-syntax
  • @willhbr — 파이프 문법은 제게 정말 ‘아하!’ 하는 순간이었습니다. SQL을 꽤 능숙하게 다루지만, 파이프라인은 조합하기가 훨씬 쉽습니다. LIMIT이나 WHERE를 넣어 빠르게 테스트하려고 쿼리 일부를 주석 처리하거나 다시 풀 때도 다른 부분을 고칠 필요가 없어 정말 편합니다. 저는 Google에서 일하지만, 문법에 대한 제 열정은 직장과 무관합니다.
• @veqq — 요약하면, 거의 파이프 문법인 두 가지를 설명하는 글입니다. 선언형 DSL에서는 인수 순서에 의미를 부여하는 방법을 생각해 왔습니다. 즉 파이프 기호 없이 파이프 문법을 쓰는 방식입니다. 현재는 인수를 거의 어떤 순서로든 쓸 수 있는데, 순서에 의미를 두면 파서를 오히려 단순하게 만들 수 있습니다. 그래서 다음과 같이 쓸 수 있습니다: (df/select :id :from person :where |(= ($ :gender-concept-id) 8507) :sort < :year-of-birth :limit 100).
  • @veqq — tech.ml이나 Qi가 쓰는 -> 방식도 있는데, 저는 그다지 좋아하지 않습니다. 매크로를 쓰면 다음처럼 모든 작업을 효율적으로 실행할 수 있습니다. 다만 대부분의 apply- 함수가 아직 비공개라 그대로 실행되지는 않습니다: (->> person (df/where {:gender-concept-id 8507}) (df/join visit-occurrence) (df/where |(>= ($ :visit-start-year) 2020)) (df/select :id [[:visit-start-year :year-of-birth] :age-at-visit |(- ($ :visit-start-year) ($ :year-of-birth))]) (df/where |(>= ($ :age-at-visit) 65)) (df/exclude death) (df/sort > :age-at-visit) (df/select :id) (df/distinct true) (df/limit 100)). 이런 방식은 예시 데이터를 주면 이미 작동합니다.
  • @veqq — declarative-dsls를 df로 가져오면 다음처럼 쓸 수 있습니다: (->> person (df/select :where {:gender-concept-id 8507} :from) (df/select :join visit-occurrence :from) (df/select :where |(>= ($ :visit-start-year) 2020) :from) (df/select :id [[:visit-start-year :year-of-birth] :age-at-visit |(- ($ :visit-start-year) ($ :year-of-birth))] :from) (df/select :where |(>= ($ :age-at-visit) 65) :from) (df/select :exclude death :from) (df/select :sort > :by [:age-at-visit] :from) (df/select :id :from) (df/select :distinct true :from) (df/select :limit 100 :from)). 또는 한 번의 select에서 :join, 여러 :where, :exclude, :sort, :distinct, :limit을 함께 지정할 수도 있습니다. 여러 :where가 순서대로 작동합니다. 조인을 하면 새 select가 필요합니다. 첫 번째 방식을 쓰도록 함수를 직접 공개할까요? 그런 API를 사람들이 좋아할까요? 순서에 의미를 두는 편이 낫다고 생각합니다. 다만 사용하기 쉽도록 기본 select도 제공할 수 있습니다.

원문: Mechanical Rabbit / 번역·요약: Trawling