여러 고유 키를 표현하고, 외래 키에서 참조하며, RELY를 사용하여 조인과 DISTINCT 쿼리를 최적화하세요.
작성자: Yan Yan, Yuchuan Huang, Xinyi Yu , Gengliang Wang
데이터 팀은 파이프라인에서 고유성 보장을 자주 유지하지만, 테이블 스키마에 이를 유연하게 표현할 방법이 없으면 사용자와 도구가 이를 검색할 수 없고 쿼리 최적화가 안전하게 활용할 수 없습니다. 비즈니스 엔터티에는 고유한 것으로 알려진 하나 이상의 값 또는 값의 조합이 있는 경우가 많습니다. 예를 들어 users 테이블은 user_id을(를) 기본 행 식별자로 사용하는 동시에 데이터 모델에 username, 이메일 주소, (workspace_id, external_user_id)에 대한 별도의 고유성 보장을 기록할 수 있습니다.
Databricks는 이미 PRIMARY KEY 및 FOREIGN KEY 제약 조건을 지원합니다. 기본 키는 테이블의 행을 식별하고, 외래 키는 이를 참조하여 관계를 설명할 수 있습니다. 하지만 테이블에는 하나의 기본 키만 가질 수 있으며, 해당 열은 null을 허용하지 않아야 합니다. Databricks SQL 및 Databricks Runtime 18.2 이상의 Unity Catalog Delta 테이블에 대한 공개 미리 보기로 이제 제공되는 UNIQUE 제약 조건은 다른 고유성 보장을 기록하는 방법을 추가합니다. 테이블은 여러 개의 UNIQUE 제약 조건을 선언할 수 있으며, 해당 열은 null을 허용할 수 있고 외래 키가 이를 참조할 수 있습니다. 이러한 제약 조건을 검증하고 RELY(으)로 표시한 후, 쿼리 최적화는 이를 사용하여 불필요한 조인과 집계를 제거할 수도 있습니다.
PRIMARY KEY 및 UNIQUE은(는) 둘 다 고유성을 기록하지만 서로 다른 스키마 역할을 합니다.
| 기능 | PRIMARY KEY | UNIQUE |
|---|---|---|
| 스키마 역할 | 기본 행 식별자 | 추가 고유성 보장 |
| 테이블당 제약 조건 | 하나 | 여러 개 |
| Null 허용 열 | 아니요 | 예 |
다음 테이블에는 기본 키와 두 개의 UNIQUE 제약 조건이 있습니다.
두 개의 고유 제약 조건은 서로 다른 종류의 고유성을 보여줍니다. users_external_uq은(는) 열 조합에 적용됩니다. 각 (workspace_id, external_user_id) 쌍은 두 값 중 하나가 다른 쌍에 나타날 수 있더라도 고유해야 합니다. users_optional_id_uq은(는) null을 허용하는 열에 적용됩니다. 다음 행은 어떤 값이 해당 보장을 만족하는지 보여줍니다.
user_id | optional_identifier | 만족함 UNIQUE (optional_identifier)? |
|---|---|---|
| 1 | email:alice@example.com | 예 |
| 2 | NULL | 예 |
| 3 | NULL | 예 |
| 4 | email:bob@example.com | 예 |
| 5 | email:alice@example.com | 아니요 - 행 1과 중복됨 |
Databricks의 기본 키 및 외래 키 제약 조건과 마찬가지로 UNIQUE 제약 조건은 정보 제공용이며 데이터를 쓸 때 강제되지 않습니다. 예를 들어 UNIQUE (optional_identifier)을(를) 선언해도 null이 아닌 중복 값을 포함하는 쓰기가 실패하지 않습니다.
이러한 분리를 통해 스키마는 수집 파이프라인, 업스트림 시스템 또는 기타 데이터 품질 프로세스에 의해 유지되는 고유성 보장을 기록할 수 있습니다. 또한 이 보장은 사용자와 도구, 응용 프로그램이 검사할 수 있는 테이블 메타데이터가 됩니다.
외래 키는 기본 키 외에도 UNIQUE 제약 조건을 참조할 수 있습니다. 예를 들어 위에 선언된 복합 UNIQUE 제약 조건은 다음과 같이 참조할 수 있습니다.
참조된 부모 열은 외래 키가 타겟으로 하는 고유 제약 조건을 식별합니다. 자식 열과 부모 열은 위치로 대응됩니다. workspace_id은(는) workspace_id에 매핑되고, source_user_id은(는) external_user_id에 매핑됩니다. 이름이 일치할 필요는 없지만 유형과 위치는 대응되어야 합니다. REFERENCES (부모) 열은 UNIQUE 제약 조건 정의에 있는 열과 같은 순서로 나열되어야 합니다. 그렇지 않으면 제약 조건은 여전히 유효하지만 조인 제거가 자동으로 실행되지 않습니다.
단일 열 UNIQUE 제약 조건을 참조하려면 부모 열의 이름을 명시적으로 지정하세요. 예를 들어 REFERENCES users (username)을(를) 사용합니다. 부모 열 목록이 생략되면 REFERENCES users은(는) 대신 테이블의 기본 키를 타겟으로 합니다.
기본적으로 고유 제약 조건은 NORELY(으)로 생성됩니다. 의도된 스키마 의미 체계를 기록하지만 Databricks는 정확성에 의존하는 쿼리 재작성에는 이를 사용하지 않습니다. 다음 문은 이 기본값으로 제약 조건을 생성합니다.
데이터를 검증하고 파이프라인이 보장을 계속 유지하도록 보장한 후에는 RELY(으)로 제약 조건을 삭제하고 다시 생성할 수 있습니다.
이 예에서 CASCADE은(는) users_username_uq을(를) 참조하는 외래 키도 삭제하며, 고유 제약 조건이 다시 추가된 후에 다시 생성해야 합니다. CASCADE이(가) 없으면 DROP CONSTRAINT은(는) 기본 RESTRICT 동작을 사용하며 종속 외래 키가 존재할 때 거부됩니다.
고유 제약 조건을 참조하는 외래 키도 RELY(으)로 선언할 수 있습니다.
RELY은(는) 각 제약 조건에 대해 독립적으로 설정됩니다. 기존 데이터를 검증하거나 향후 쓰기에 대해 제약 조건을 강제하지 않습니다. Databricks가 쿼리를 최적화할 때 제약 조건이 유효하다고 가정할 수 있도록 허용합니다. 데이터가 신뢰된 제약 조건을 위반하는 경우 해당 제약 조건을 기반으로 한 재작성으로 인해 잘못된 결과가 생성될 수 있습니다. 따라서 검증되고 지속적으로 유지되는 보장에만 RELY을(를) 사용해야 합니다.
RELY을(를) 사용하면 고유 제약 조건을 통해 적절한 조인 및 집계 재작성에 필요한 정보를 제공할 수 있습니다. 아래 예시에서 볼 수 있듯이, 최적화 적용 여부는 널 허용 여부를 포함하여 쿼리에 필요한 다른 보장 사항에도 따라 달라집니다. 이 최적화 기능은 Photon 활성화 컴퓨팅에서 사용할 수 있습니다. 기본 키 및 외래 키 블로그에서는 UNIQUE 제약 조건으로도 활성화할 수 있는 제약 조건 기반 조인 및 집계 최적화에 대해 설명합니다. 해당 게시물이 게시된 이후, RELY 지원이 외래 키에 이어 이제 UNIQUE 제약 조건까지 확대되었습니다.
동일한 데이터를 기반으로 구축된 일부 대시보드는 users의 속성이 필요하므로, 팀에서는 대개 user_events과(와) users을(를) 항상 조인하는 공유 뷰를 생성합니다. user_events의 정보만 필요한 대시보드도 뷰에서 해당 조인을 상속받을 수 있습니다. 따라서 users의 열을 선택하지 않더라도 생성된 SQL은 아래 쿼리처럼 보일 수 있습니다.
쿼리가 users의 열을 반환하지 않더라도, 최적화 도구는 일반적으로 조인을 제거할 수 없습니다. 일치하는 사용자가 존재하지 않는 경우 내부 조인(inner join)으로 인해 이벤트가 제거되거나, 여러 사용자가 동일한 사용자 이름을 가진 경우 이벤트가 중복될 수 있기 때문입니다.
신뢰된 제약 조건은 이 두 가지 가능성을 모두 배제합니다. UNIQUE 제약 조건은 일치하는 사용자가 최대 한 명임을 보장하고, 외래 키는 일치하는 사용자가 존재함을 보장합니다. user_events.username 역시 NOT NULL이므로, 모든 이벤트에는 정확히 하나의 일치 항목이 존재합니다. 따라서 조인으로 인해 결과가 변경될 수 없으며, 최적화 도구는 user_events에서 event_id을(를) 직접 읽을 수 있습니다.
앞서 언급한 기본 키 및 외래 키 블로그에서는 최대 한 개라는 보장만 필요한 관련 LEFT OUTER JOIN 사례를 보여줍니다. 이 INNER JOIN 예시에서는 일치 항목이 존재함을 설정하기 위해 외래 키도 필요합니다.
NORELY 제약 조건을 사용하면 최적화된 계획은 조인을 유지합니다.
두 제약 조건 모두 RELY을(를) 사용할 때, 최적화 도구는 모든 이벤트에 정확히 하나의 일치하는 사용자가 있음을 파악하고 조인을 제거할 수 있습니다.
이 예시에서 user_events.username은(는) NOT NULL이지만, 널 허용(nullable)인 경우에도 최적화가 적용됩니다. Databricks는 조인을 제거하고 user_events.username에 IS NOT NULL 필터를 추가하여 내부 조인의 널값 처리를 유지합니다.
10 TB 스케일 팩터를 나타내는 TPC-DS SF10000 데이터셋에서 이 최적화를 벤치마킹했습니다. 288억 개의 행을 가진 store_sales 테이블을 6,500만 개의 행을 가진 customer 테이블과 조인할 때, customer.c_customer_sk을(를) UNIQUE(으)로 선언하고 store_sales.ss_customer_sk에서 외래 키를 정의했습니다. 두 제약 조건 모두에 RELY을(를) 적용한 결과, Databricks는 조인을 제거하고 셔플의 99.99% 이상을 없애며 중앙값 엔진 실행 시간을 23.6초에서 3.0초로 단축하여 7.9배의 속도 향상을 이루어냈습니다.
BI 도구와 쿼리 작성기는 조인으로 인해 중복 행이 생성될 수 있으므로 방어적으로 DISTINCT을(를) 추가하는 경우가 많습니다. 선택된 값이 이미 고유한 경우 DISTINCT을(를) 사용해도 제거되는 것은 없지만, 최적화 도구가 해당 연산이 불필요함을 증명하지 못하는 한 여전히 집계와 셔플이 필요할 수 있습니다. 신뢰된 UNIQUE 제약 조건이 그 증거를 제공할 수 있습니다.
다음 두 가지 제약 조건이 있는 user_identifiers 테이블을 고려해 보세요.
UNIQUE (user_id) RELY(여기서 user_id은(는) NOT NULL임)UNIQUE (optional_identifier) RELY(여기서 optional_identifier은(는) 널 허용임)테이블에는 두 제약 조건을 모두 만족하는 다음과 같은 데이터가 포함되어 있습니다.
user_id의 경우, 최적화 도구는 고유성 및 널 아님 보장을 함께 사용할 수 있습니다.
최적화된 계획에는 집계가 포함되지 않습니다.
신뢰된 제약 조건으로 인해 모든 user_id이(가) 이미 고유하다는 것이 확인되므로 집계가 사라집니다.
optional_identifier에 대한 동등한 쿼리는 최적화 도구가 널 허용 여부도 고려하는 이유를 보여줍니다.
최적화된 계획은 집계를 유지합니다.
두 제약 조건 모두 RELY을(를) 사용하지만, 널 허용 제약 조건은 위에 표시된 두 개의 NULL 행을 허용합니다. SQL DISTINCT은(는) 이러한 행 들을 하나로 병합해야 하므로 집계가 여전히 필요합니다.
쿼리가 누락된 널 아님 보장을 제공하는 경우, 널 허용 UNIQUE 제약 조건을 통해 여전히 최적화를 활성화할 수 있습니다.
최적화 도구는 집계를 제거할 수 있습니다.
필터는 널 허용 UNIQUE 의미 체계에 따라 반복될 수 있는 값을 제거합니다. 남은 값들은 고유함이 보장되므로 Databricks에서 다시 집계를 피할 수 있습니다.
10 TB 스케일 팩터를 나타내는 동일한 TPC-DS SF10000 데이터셋에서 이 최적화를 벤치마킹했습니다. 288억 개의 행을 가진 store_sales 테이블에서 (ss_item_sk, ss_ticket_number)을(를) 복합 UNIQUE 제약 조건으로 선언하고 해당 열에 대해 DISTINCT 쿼리를 실행했습니다. RELY을(를) 사용한 결과, Databricks는 DISTINCT 집계를 제거하고 셔플의 99.99% 이상을 없애며 중앙값 엔진 실행 시간을 33.7초에서 2.8초로 단축하여 12.0배의 속도 향상을 이루어냈습니다.
RELY을(를) 사용하기 전에 데이터가 보장 조건을 만족하는지 확인하세요. 널 허용 UNIQUE 제약 조건의 경우, UNIQUE은(는) 해당 행의 반복을 허용하므로 NULL이(가) 포함된 행은 제외하세요. 이 쿼리는 중복된 널이 아닌 식별자를 찾습니다.
복합 UNIQUE 제약 조건의 경우, 제약 조건이 걸린 모든 컬럼으로 그룹화하고 값이 널(null)일 수 있는 각 컬럼에 IS NOT NULL 조건을 추가하세요. 또한 레프트 안티 조인(left anti join)을 사용하여 일치하는 부모가 없는 하위 행을 찾을 수 있습니다.
이러한 검사는 현재 데이터를 검증합니다. RELY을(를) 활성화한 후에도 수집 또는 데이터 품질 파이프라인에서 보증 사항이 계속 유지되어야 합니다.
제약 조건을 선언하거나 다시 생성한 후 여러 방법으로 해당 정의를 확인할 수 있습니다.
SHOW CREATE TABLEDESCRIBE TABLE EXTENDEDTABLE_CONSTRAINTSKEY_COLUMN_USAGECONSTRAINT_TABLE_USAGECONSTRAINT_COLUMN_USAGEREFERENTIAL_CONSTRAINTS전체 생성, 변경, 검사 및 삭제 구문은 Databricks의 제약 조건 및 CONSTRAINT 절을 참조하세요.
UNIQUE을(를) 단계적으로 도입할 수 있습니다.
NORELY을(를) 사용하여 기존 고유성 보장을 선언합니다.UNIQUE 제약 조건에 RELY을(를) 사용하고, 모든 해당 하위 값에 일치하는 부모 행이 있는지 확인한 후에만 외래 키에 사용합니다.EXPLAIN을(를) 사용하여 계획을 비교하고 적격한 재작성을 확인합니다.UNIQUE 제약 조건을 사용하면 테이블에 단일 널(null) 허 용 불가 PRIMARY KEY 외에도 여러 고유성 보장을 기록할 수 있으며, 제약 조건이 걸린 컬럼은 값이 널(null)일 수 있습니다. 외래 키는 기본 키뿐만 아니라 UNIQUE 제약 조건도 참조할 수 있습니다. UNIQUE 제약 조건은 기본적으로 정보 제공용입니다. 검증 후 RELY을(를) 사용하면 Databricks에서 이를 활용하여 적격한 조인 및 DISTINCT 쿼리를 최적화할 수 있습니다.
(이 글은 AI의 도움을 받아 번역되었습니다. 원문이 궁금하시다면 여기를 클릭해 주세요)
블로그를 구독하고 최신 게시물을 이메일로 받아보세요.