기본 키와 외래 키 제약 조건이 정식 출시(GA)되어 더 빠른 쿼리를 가능하게 합니다

August 12, 2024 in 엔지니어링 블로그

이 포스트 공유하기

데이터브릭스 런타임 15.2 와 데이터브릭스 SQL 2024.30부터 기본 키(Primary Key, PK) 및 외래 키(Foreign Key, FK) 제약 조건의 정식 버전(GA)을 발표하게 되어 매우 기쁘게 생각합니다. 이번 릴리즈는 매주 수백 명의 활성 고객이 참여한 매우 성공적인 공개 프리뷰에 이은 것으로, 레이크하우스 내에서 데이터 무결성과 관계형 데이터 관리를 향상시키는 데 있어 중요한 이정표가 될 것입니다.

또한, 데이터브릭스에서는 이제 이러한 제약 조건을 사용하여 쿼리를 최적화하고 쿼리 실행 계획에서 불필요한 작업을 제거하여 훨씬 더 빠른 성능을 제공할 수 있습니다.

기본 키 및 외래 키 제약 조건

기본 키(PK)와 외래 키(FK)는 관계형 데이터베이스의 필수 요소로, 데이터 모델링의 기본 구성 요소 역할을 합니다. 이들은 스키마의 데이터 관계에 대한 정보를 사용자, 도구 및 애플리케이션에 제공하고 제약 조건을 활용하여 쿼리 속도를 높이는 최적화를 가능하게 합니다. 기본 키와 외래 키는 이제 Unity Catalog에서 호스팅되는 Delta Lake 테이블에 일반적으로 사용할 수 있습니다.

SQL 언어

테이블을 만들 때 제약 조건을 정의할 수 있습니다:

CREATE TABLE Users (
    UserID INT NOT NULL PRIMARY KEY,
    UserName STRING,
    Email STRING,
    SignUpDate DATE
);

위의 예에서는 UserID 열에 기본 키 제약 조건을 정의합니다. 데이터브릭스에서는 열 그룹에 대한 제약 조건도 지원합니다.

기존 Delta 테이블을 수정하여 제약 조건을 추가하거나 제거할 수도 있습니다:

CREATE TABLE Products (
    ProductID INT NOT NULL,
    ProductName STRING,
    Price DECIMAL(10,2),
    CategoryID INT
);
ALTER TABLE Products ADD CONSTRAINT products_pk PRIMARY KEY (ProductID);
ALTER TABLE Products DROP CONSTRAINT products_pk;

여기서는 기존 테이블의 ProductID라는 Not null 컬럼에 products_pk라는 기본 키를 만듭니다. 이 작업을 성공적으로 실행하려면 테이블의 소유자여야 합니다. 제약 조건 이름은 스키마 내에서 고유해야 합니다.
그 다음 이어지는 명령은 이름을 지정하여 기본 키를 제거합니다.

외래 키에도 동일한 프로세스가 적용됩니다. 다음 표에서는 테이블 생성 시 두 개의 외래 키를 정의합니다:

CREATE TABLE Purchases (
    PurchaseID INT PRIMARY KEY,
    UserID INT,
    ProductID INT,
    PurchaseDate DATE,
    Quantity INT,
    FOREIGN KEY (UserID) REFERENCES Users(UserID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

제약 조건과 관련된 구문 및 연산에 대한 자세한 내용은 CREATE TABLE 과 ALTER TABLE 문에 대한 설명서를 참조하세요.

기본 키 와 외래 키 제약 조건은 데이터브릭스 엔진에서 강제 적용되지 않지만, 데이터 무결성 관계를 나타내는 데 유용할 수 있습니다. 데이터브릭스에서는 대신 수집 파이프라인의 일부로 업스트림에서 기본 키 제약 조건을 적용할 수 있습니다. 적용 제약 조건에 대한 자세한 내용은 델타 라이브 테이블로 데이터 품질 관리하기를 참조하세요. 데이터브릭스는 또한 NOT NULL 및 CHECK 제약 조건도 지원합니다(자세한 내용은 제약 조건 설명서를 참조하세요).

파트너 에코시스템

최신 버전의 Tableau 및 PowerBI와 같은 도구 및 응용 프로그램은 JDBC 및 ODBC 커넥터를 통해 데이터브릭스에서 기본 키 및 외래 키 관계를 자동으로 가져와서 활용할 수 있습니다.

제약 조건 보기

테이블에 정의된 기본 키 및 외래 키 제약 조건을 보는 방법에는 여러 가지가 있습니다. DESCRIBE TABLE EXTENDED 와 같은 SQL 명령을 사용하여 제약 조건 정보를 간단히 볼 수도 있습니다:

> DESCRIBE TABLE EXTENDED Purchases

... (omitting other outputs)
# Constraints	
purchases_pk             PRIMARY KEY (`PurchaseID`)
purchases_products_fk    FOREIGN KEY (`ProductID`) REFERENCES `main`
                         .`example`.`products` (`ProductID`)
purchases_users_fk       FOREIGN KEY (`UserID`) REFERENCES `main`
                         .`example`.`users` (`UserID`)

카탈로그 탐색기와 엔티티 관계 다이어그램(Entity Relationship Diagram)

카탈로그 탐색기(Catalog Explorer)를 통해 제약 조건 정보를 볼 수도 있습니다:

Catalog Explorer and Entity Relationship Diagram

각 기본 키 및 외래 키 열에는 이름 옆에 작은 키 아이콘이 있습니다.

또한 카탈로그 탐색기의 엔티티 관계 다이어그램을 사용하여 기본 키와 외래 키 정보 및 테이블 간의 관계를 시각화할 수 있습니다. 아래는 users 와 products 라는 두 테이블을 참조하는 purchases 테이블의 예입니다:

INFORMATION SCHEMA

다음 INFORMATION_SCHEMA 테이블은 제약 조건 정보도 제공합니다:

TABLE_CONSTRAINTS: 카탈로그 내의 모든 기본 키와 외래 키 제약 조건에 대한 메타데이터를 설명합니다.
KEY_COLUMN_USAGE: 카탈로그 내의 기본 키 또는 외래 키 제약 조건의 열을 나열합니다.
CONSTRAINT_TABLE_USAGE: 카탈로그에서 테이블을 참조하는 제약 조건을 설명합니다.
CONSTRAINT_COLUMN_USAGE: 카탈로그의 컬럼을 참조하는 제약 조건을 설명합니다.
REFERENTIAL_CONSTRAINTS: 카탈로그에 정의된 참조(외래 키) 제약 조건을 설명합니다.

최적화를 활성화하려면 RELY 옵션을 사용하세요.

기본 키 제약 조건이 유효하다는 것을 알고 있는 경우(예를 들어, 데이터 파이프라인 또는 ETL 작업에서 이를 강제했기 때문에) 다음과 같이 RELY 옵션으로 제약 조건을 지정하여 제약 조건을 기반으로 최적화를 활성화할 수 있습니다:

PRIMARY KEY (c_customer_sk) RELY

RELY 옵션을 사용하면 데이터 무결성이 유지된다고 사용자가 보장하는 것이기 때문에, 데이터브릭스에서 제약 조건의 유효성에 따라 쿼리를 최적화할 수 있습니다. 제약 조건이 RELY로 표시되어 있지만 데이터가 제약 조건을 위반하는 경우에는 쿼리가 잘못된 결과를 반환할 수 있으므로 주의하세요.

제약 조건에 대해 RELY 옵션을 지정하지 않으면 기본값은 NORELY이며, 이 경우 정보 제공 또는 통계 목적으로 제약 조건을 계속 사용할 수 있지만 쿼리가 올바르게 실행되는 데는 제약 조건에 의존하지 않습니다.

RELY 옵션과 이를 활용한 최적화는 현재 기본 키에 사용할 수 있으며 외래 키에 대해서도 곧 제공될 예정입니다.

예를 들어 ALTER TABLE을 사용하여 테이블의 기본 키를 수정하여 RELY 또는 NORELY로 변경할 수 있습니다:

ALTER TABLE customer DROP PRIMARY KEY;
ALTER TABLE customer ADD PRIMARY KEY (c_customer_sk) RELY

불필요한 집계를 제거하여 쿼리 속도 향상

RELY 기본 키 제약 조건으로 수행할 수 있는 간단한 최적화 중 하나는 불필요한 집계를 제거하는 것입니다. 예를 들어 RELY를 사용하여 기본 키가 있는 테이블에 distinct 연산을 적용하는 쿼리를 예로 들어 보겠습니다:

SELECT DISTINCT c_customer_sk FROM customer;

불필요한 DISTINCT 연산을 제거할 수 있습니다:

SELECT c_customer_sk FROM customer;

보시다시피 이 쿼리는 RELY 기본 키 제약 조건의 유효성에 의존하므로 고객 테이블에 중복된 고객 ID가 있는 경우 변환된 쿼리는 잘못된 중복 결과를 반환합니다. RELY 옵션을 설정한 경우 제약 조건의 유효성을 적용할 책임은 사용자에게 있습니다.

기본 키가 NORELY(기본값)인 경우 옵티마이저는 쿼리에서 DISTINCT 연산을 제거하지 않습니다. 그러면 실행 속도가 느려질 수 있지만 중복이 있더라도 항상 올바른 결과를 반환합니다. 기본 키가 RELY인 경우 데이터브릭스는 DISTINCT 연산을 제거하여 쿼리 속도를 크게 높일 수 있습니다(위 예의 경우 약 2배)

불필요한 join을 제거하여 쿼리 속도 향상

RELY 기본 키로 수행할 수 있는 또 다른 매우 유용한 최적화는 불필요한 join을 제거하는 것입니다. 쿼리가 join 조건 외에는 참조되지 않는 테이블을 조인하는 경우, 최적화 도구는 join이 불필요하다고 판단하여 쿼리 계획에서 join을 제거할 수 있습니다.

예를 들어, customer 테이블의 기본 키인 PRIMARY KEY (c_customer_sk) RELY를 기준으로 store_sales와 customer 라는 두 테이블을 조인하는 쿼리가 있다고 가정해 보겠습니다.

SELECT SUM(ss_quantity)
FROM store_sales ss
LEFT JOIN customer c
ON ss_customer_sk = c_customer_sk;

기본 키가 없는 경우 store_sales 의 각 행이 customer 의 여러 행과 일치할 가능성이 있으므로 올바른 합계 값을 계산하려면 join을 실행해야 합니다. 하지만 customer 테이블이 기본 키에 join되어 있으므로 join이 store_sales의 각 행에 대해 하나의 행을 출력한다는 것을 우리는 알고 있습니다.

따라서 쿼리에는 실제로는 팩트 테이블 store_sales의 ss_quantity 열만 필요합니다. 따라서 쿼리 최적화 도구는 쿼리에서 join을 완전히 제거하여 쿼리를 다음과 같이 변환할 수 있습니다: (역자 주. LEFT JOIN 임을 유의하세요. EQUI JOIN 이라면 join을 제거할 수 없습니다)

SELECT SUM(ss_quantity)
FROM store_sales ss

이 예에서는 전체 join을 피함으로써 쿼리 속도가 1.5분에서 6초로 최적화되어 훨씬 더 빠르게 실행됩니다. 그리고 join에 제거할 수 있는 테이블이 많은 경우 그 이점은 훨씬 더 커질 수 있습니다!

왜 이런 쿼리를 실행하냐고 물어보실 수도 있습니다. 사실 생각보다 훨씬 더 흔한 일입니다! 한 가지 일반적인 이유는 사용자가 여러 팩트 테이블과 차원 테이블을 join하는 등 여러 테이블을 함께 join하는 뷰를 구성하기 때문입니다. 이러한 뷰에 대한 쿼리를 작성할 때 전체 테이블이 아닌 일부 테이블의 컬럼만 사용하는 경우가 많으므로 최적화 도구는 각 쿼리에서 필요하지 않은 테이블에 대한 join을 제거할 수 있습니다. 이 패턴은 많은 비즈니스 인텔리전스(BI) 도구에서도 흔히 볼 수 있으며, 쿼리가 일부 테이블의 컬럼만 사용하는 경우에도 스키마에서 많은 테이블을 join하는 쿼리를 생성하는 경우가 많습니다.