Databricks에 Recursive Common Table Expressions 소개

Databricks에서 이제 Recursive Common Table Expressions (CTEs)를 지원합니다. 이는 SQL에서 루프와 순회를 표현하는 기본적인 방법을 제공하며, 계층적이고 그래프 구조의 데이터를 다루는 데 유용합니다. 이 기능들은 SQL 표준과 일치하며 Teradata와 같은 플랫폼에서 사용되는 익숙한 패턴을 따릅니다. Recursive CTEs는 오랫동안 SQL 표준의 일부였으므로, 기존 데이터 웨어하우스에서 이전하는 고객들에게 익숙할 것입니다. Databricks는 또한 Recursive CTE 지원을 Apache Spark™에 기여하여 완전히 오픈 소스로 만들었습니다.

Databricks는 재귀 CTE에 대한 표준 ANSI SQL 구문을 사용합니다. 이에는 RECURSIVE 키워드가 포함됩니다.

이 보이는 것보다 작은 기능은 SQL의 표현력을 크게 향상시키며, 이론적으로 튜링 완전성을 가지게 만듭니다. 즉, 컴퓨터가 수행할 수 있는 모든 계산을 수행할 수 있습니다. 재귀 CTE는 Python이나 외부 도구와 같은 절차적 코드가 필요했던 문제에 대해 구성 가능한 해결책을 제공합니다.

Recursive CTEs 는 이제 Public Preview DBSQL 2025.20 및 Databricks Runtime 17.0에서 사용할 수 있습니다. (곧 Lakeflow Declarative Pipelines에서도 사용 가능합니다). 이 블로그에서는 Recursive CTEs가 어떻게 작동하는지, 그리고 순수 SQL을 사용하여 실제 문제를 어떻게 해결할 수 있는지 살펴보겠습니다.

재귀 CTE 지원의 주요 특징

Databricks의 재귀 CTE 지원에는 다음이 포함됩니다:

• 조직 차트, 폴더, 라우팅 네트워크와 같은 트리 및 그래프와 같은 구조를 순회
• 완전히 오픈 소스이며 Apache Spark™에 통합
• 무한 재귀에 대한 내장 보호 기능 (100단계, 1M 행)
• 사용자 정의 가능한 보호 장치를 사용하여 MAX RECURSION LEVEL
• 제어 가능한 무한 재귀를 사용하기 위한 지원 LIMIT

재귀 CTE는 정규화된 테이블에 계층적 데이터를 저장하는 전통적인 시스템과 유연한 JSON/XML 계층을 생성하는 현대적인 애플리케이션에서 오는 데이터 모두에 잘 작동합니다. 아래에는 JSON 계층 구조에 대한 Variant 데이터 유형을 활용하는 RCTE를 포함한 각 예시를 확인해 보세요.

또한, 재귀 CTE 지원은 기존 데이터베이스 시스템에서의 마이그레이션을 단순화합니다. Teradata와 Postgres는 구문이 동일한 시스템의 예시이며, CONNECT BY 구문을 사용하는 Oracle과 같은 시스템은 쉽게 변환될 수 있습니다.

Recursive CTEs가 어떻게 작동하는지

재귀 CTE는 RECURSIVE 키워드로 정의된 공통 테이블 표현식입니다. 이들은 UNION ALL을 사용하여 두 부분으로 결합됩니다:

1. A 기본 케이스 서브쿼리 — 이것은 한 번 실행되어 재귀를 시작합니다
2. 재귀 단계 서브쿼리 — 이것은 CTE 자체를 가리키며, 새로운 행을 구축하기 위해 반복적으로 적용됩니다.

실행은 기본 쿼리에서 시작합니다. 그런 다음, 각 반복에서 이전 단계의 출력을 사용하여 재귀 단계가 실행됩니다. 새로운 행이 생성되지 않을 때까지 계속됩니다.

무한 재귀가 과도한 리소스를 소비하는 것을 방지하기 위해, Databricks는 두 가지 안전 제한을 적용합니다: 최대 재귀 깊이 는 100단계, 행 제한 은 1백만입니다. 두 가지 임계값 중 하나라도 초과하면 쿼리는 오류와 함께 실패합니다.

재귀가 모든 결과를 생성하기 위해 100단계 이상 필요하다고 확신한다면, MAX RECURSION LEVEL 힌트를 사용하여 최대 레벨을 무시할 수 있습니다:

 자세한 내용은 CTE 문서를 참조하십시오.

“bp 공급 거래 및 선박 - 시장 위험에서는 사업부 간의 포트폴리오 계층 보고를 이해하는 것이 우리 사업의 효율적인 운영에 중요합니다. 우리는 기존 코드를 Databricks SQL의 재귀 CTE로 교체함으로써 계층적 데이터 준비 단계를 약 6분에서 약 30초로 줄였습니다. 이는 12배의 향상입니다.” — Dharmik Prajapati, bp Staff Software Engineer

Recursive CTEs를 사용하여 반복적인 작업 해결 예시

트리 및 계층 데이터 탐색: Bill of Materials를 사용하여 필요한 자재 찾기

제조업에서는 모든 제조 부품이 구성 요소의 집합을 필요로 합니다. 각 구성 요소는 더 작은 세트의 개별 부품으로 분해될 수 있습니다. 모든 부품의 완전한 집합을 Bill of Materials (BOM)이라고 합니다. 

BOM은 종종 트리와 같은 구조를 형성하거나, 보다 일반적으로는 방향성 비순환 그래프(DAG)를 형성합니다. 이 예에서는 자전거의 부품을 살펴보며, 각 구성 요소가 정확히 하나의 부모에서 사용되는 트리 구조라고 가정하여 단순화합니다. 

자전거를 만드는 데 필요한 원자재가 얼마나 많은지 계산하려고 합시다. 다음 BOM을 고려해 보세요:

각 행은 구성 요소, 그것이 속한 더 큰 부분, 그리고 부모의 한 단위를 조립하는 데 필요한 구성 요소의 수를 설명합니다.

재귀 CTE는 하나의 목표로 시작합니다: 자전거 하나를 만드는 것. 그것이 기본 케이스입니다. 각 재귀 단계에서, 우리는 구성 요소를 그들의 하위 구성 요소로 분해합니다. 예를 들어, 자전거에는 프레임, 드라이브 트레인, 그리고 두 개의 바퀴가 포함됩니다. 각 바퀴는 차례로 타이어와 32개의 스포크로 구성됩니다. 부품을 더 작은 조각으로 분해하면서 재귀 구조가 명확해집니다.

계층을 완전히 확장한 후, 우리는 중간 구성 요소(부모)를 필터링하여 조립에 필요한 원자재만 남깁니다.

이 쿼리는 한 대의 자전거를 만드는 데 필요한 각 기본 재료의 총량을 계산합니다:

그래프 순회를 기반으로 한 경로 찾기: 한 도시에서 모든 비행 경로 찾기

그래프 데이터 구조를 사용하여 문제를 살펴봅시다. 그래프는 엣지로 연결된 노드의 집합으로 구성됩니다. 이는 요소 쌍 간의 관계나 연결을 나타내는 데 사용됩니다. 그래프 문제를 해결하려면 Python, 복잡한 스크립팅 로직, 또는 외부 라이브러리가 필요했습니다. 이제, 재귀 쿼리를 사용하면 간단해집니다. 

전형적인 그래프 구조 문제는 항공 여행입니다: 어떤 공항에 연속적인 비행을 이용하여 도달할 수 있을까요? 우리가 하루에 존재하는 다음과 같은 비행 세트를 가정해 봅시다:

각 비행은 출발지와 목적지의 IATA 코드와 함께 출발 시간과 도착 시간이 주어집니다.

사람이 오전 8시에 BEG 공항에 도착하고 그날 가능한 모든 여행 경로를 찾고 싶다고 가정해 보세요.

이는 자연스럽게 반복적인 문제로 제기됩니다. 새로운 도시를 발견할 때마다, 우리는 도착 시간 이후 에 출발하는 모든 항공편을 찾습니다. 이 때문에, 재귀 CTE에서는 모든 공항에서의 도착 시간을 추적합니다.  

이것은 도달 가능한 모든 공항의 집합을 생성하며, 필요한 비행 횟수와 비행 세트를 함께 제공합니다.

이 쿼리는 스케줄 제약 조건을 고려하여 도달 가능한 모든 목적지를 탐색하는 사용자를 돕는데 도움이 될 수 있습니다. 여행 계획, 패키지 라우팅, 또는 운송 물류와 같은 응용 프로그램을 지원합니다.

이전 예제에서, 우리는 컬럼 이름을 WITH RECURSIVE ... AS (...) 절에서 정의했습니다. 여기서는 앵커 쿼리에서 그것들을 정의합니다. 두 접근법 모두 Databricks에서의 재귀 CTE에서 유효합니다.

반정형 및 비정형 데이터 순회: JSON 파일에 저장된 모든 직원 찾기

전통적인 시스템들은 종종 계층적 데이터를 엄격한, 정규화된 테이블에 저장합니다. 한편, 현대 애플리케이션들은 자주 유연한 JSON/XML 계층을 생성합니다. Databricks의 재귀 CTE와 VARIANT 타입의 결합은 이러한 데이터 패턴을 원활하게 이전할 수 있게 해주며, 전통적인 정규화된 데이터와 유연한 JSON/XML 구조를 한 시스템에서 쿼리할 수 있게 해줍니다.

이 예제에서는 (상대적으로 작은) 회사 계층 구조가 주어집니다. 하지만 완전히 구조화된 테이블 대신 JSON 형태로 제공됩니다:

테이블에서 모든 직원과 그들의 직위 목록을 원한다고 가정해봅시다. 회사의 사람들의 필드는 동일한 스키마를 따르지 않습니다: 일부는 직접 부하를 가지고 있고, 일부는 없습니다; 일부는 위치를 가지고 있고, 일부는 없습니다! Databricks의 VARIANT 데이터 타입을 사용하면, JSON의 중첩 구조를 완전히 탐색하기 위해 재귀 CTE 내에서 모든 필요한 공통점을 사용할 수 있고, 그들의 차이점은 무시할 수 있습니다.

재귀의 기본 케이스 는 루트 직원의 전체 JSON 데이터로, 그들의 부하직원 목록이 포함되어 있습니다. 각 재귀 단계에서, 쿼리는 각 부하직원의 데이터를 처리하고 부하직원이 없는 직원에 도달할 때까지 과정을 반복합니다.

이 예제에 대한 재귀 쿼리는 다음과 같습니다:

여기서 모든 CTE는 WITH RECURSIVE 블록 아래에 있지만, 실제 재귀가 필요한 것만 재귀로 취급됩니다. Databricks는 어떤 것이 재귀가 필요한지 스스로 판단할 수 있을 만큼 똑똑합니다 - 심지어 모두 표시하더라도요!

쿼리의 출력 결과:

시작하는 방법

Databricks의 재귀 CTE를 시작하려면 문서를 읽어보세요. 

Databricks SQL에 대해 더 알아보려면 우리의 웹사이트 를 방문하거나 문서를 읽어보십시오. 또한 Databricks SQL 제품 투어를 확인해보십시오. 기존의 웨어하우스를 뛰어난 사용자 경험과 더 낮은 총 비용을 가진 고성능, 서버리스 데이터 웨어하우스로 이전하려면 Databricks SQL이 해결책입니다 — 무료로 시도해보십시오.

 

(이 글은 AI의 도움을 받아 번역되었습니다. 원문이 궁금하시다면 여기를 클릭해 주세요)