[Bigquery] 기본키와 외래키 (pk, fk)

2023년 7월 15일 GCP 블로그에서 빅쿼리의 기본키와 외래키와 관련된 글을 발행했습니다. 여기서는 기본키와 외래키를 추가해보고 어떤 이점이 있는지 확인해 봅니다.

제한사항

빅쿼리에서는 다음과 같은 제한사항이 있으므로 사용자가 이를 확인하고 조심해야 합니다.

• 기본키 및 외래키에는 값 제약조건이 적용되지 않음 (사용자는 값이 해당 제약조건과 일치하는지 확인해야 하고 그렇지 않으면 잘못된 결과가 발생할 수 있음)
  • 확인 사항
    • 기본키에는 고유한 값이 있어야함
    • 기본키는 16개의 컬럼을 초과할 수 없음
    • 외래키에는 기본키 테이블 열에 있는 값이 있어야함 (NULL허용)
    • 기본키와 외래키 타입은 BIGNUMERIC, BOOLEAN, DATE, DATETIME, INT64, NUMERIC, STRING, TIMESTAMP 중 하나만 허용
• 기본키와 외래키는 최상위 열에만 설정할 수 있음
• 기본키의 이름은 지정할 수 없음
• 기본키 컬럼에는 기본값이 포함될 수 없음
• 기본키 제약조건이 있는 테이블의 이름은 변경할 수 없음
• 기본키와 외래키 관계가 있는 테이블은 같은 데이터 세트에 있어야 함
• 테이블에는 최대 64개의 외래키까지 가능
• 외래키는 동일한 테이블의 열을 참조할 수 없음
• 기본키 제약조건 또는 외래키 제약조건의 일부인 필드 이름을 바꾸거나 유형을 변경할 수 없음

기본키와 외래키 정의

테이블을 생성할 때, 존재하는 테이블에 추가할 때, 기존 제약 조건을 수정 및 삭제와 같은 형식을 다음과 같이 진행할 수 있습니다.

아래는 제약 조건을 추가하여 테이블을 생성하는 DDL 입니다.

CREATE TABLE `inventory` (
 inv_date_sk INT64 REFERENCES date_dim(d_date_sk) NOT ENFORCED,
 inv_item_sk INT64 REFERENCES item(i_item_sk) NOT ENFORCED,
 inv_warehouse_sk INT64 REFERENCES warehouse(w_warehouse_sk) NOT ENFORCED,
 inv_quantity_on_hand INT64,
 PRIMARY KEY(inv_date_sk, inv_item_sk, inv_warehouse_sk) NOT ENFORCED
);

기존 인벤토리 테이블에 제약 조건을 추가하는 방법은 다음과 같습니다.

ALTER table inventory ADD primary key(inv_date_sk, inv_item_sk, inv_warehouse_sk) NOT ENFORCED,
ADD FOREIGN KEY(inv_date_sk) references date_dim(d_date_sk) NOT ENFORCED,
ADD FOREIGN KEY(inv_item_sk) references item(i_item_sk) NOT ENFORCED,
ADD FOREIGN KEY(inv_warehouse_sk) references warehouse(w_warehouse_sk) NOT ENFORCED;

현재는 제약조건을 추가할 때, NOT ENFORCED 를 포함해야 합니다.

제약조건을 추가하면 다음과 같이 빅쿼리 콘솔에서 표시가 됩니다.

제약조건을 설정을 하는 이유

결국 빅쿼리에서도 제약조건을 추가한 이유는 쿼리 최적화를 더 잘하게 만들기 위함입니다. 내부 조인 제거, 외부 조인 제거 및 조인 재정렬에서 쿼리 최적화를 기대할 수 있습니다.

예시

먼저 left 테이블의 fk 컬럼은 right 테이블의 pk와 1개는 연결이 되어 있다 가정하고 제약조건을 추가하였습니다.

전체 컬럼 조회

SELECT *
FROM `left` as left inner join `right` as right on (base.fk = right.pk)

먼저 제약조건을 추가하지 않은 쿼리 결과입니다.

제약 조건을 건 다음 실행한 결과입니다.

제약조건이 쿼리 최적화에 도움이 되지 않은 것을 볼 수 있습니다.

좌변 테이블 컬럼만 조회

이번엔 공식 문서에 나와있는 것처럼 좌변 테이블의 컬럼만 가져오는 테스트를 진행해 봅니다.

SELECT left.*
FROM `left` as left inner join `right` as right on (base.fk = right.pk)

제약조건 걸려있지 않은 경우와 제약조건 걸려있는 경우

제약조건으로 인해 join이 제거되었지만 기대했던 것처럼 성능이 좋아지진 않은 것을 확인했습니다.

우변 테이블만 전체조회, 각 테이블에만 존재하는 컬럼 조회

제약조건을 건 테이블 쿼리는 최적화가 되지 않아 제약조건을 걸지 않은 쿼리와 동일한 결과가 출력되었습니다.

집계 조회

SELECT count(*)
FROM `left` as left inner join `right` as right on (base.fk = right.pk)

제약조건 걸려있지 않은 경우와 제약조건 걸려있는 경우

집계의 경우, 위와 같이 제약 조건을 추가한 경우에 성능이 향상된 것을 확인할 수 있습니다.

외부 조인 (left join, right join)의 경우는 요구사항이 더 적습니다. left join으로 효율을 얻으려면 우변 테이블의 조인 키가 고유해야 하고 왼쪽 열만 선택해야 합니다. right join의 경우, 좌변 테이블의 조인 키가 고유해야 하고 오른쪽 열만 선택해야 합니다.

요약

빅쿼리에 pk, fk 개념은 존재하지만 기존 RDB와는 다르게 사용자가 제한 사항들을 체크해야함

쿼리 최적화가 자동으로 적용되기 위해선 조인을 잘 사용해야함

pk, fk로 얻을 수 있는 효율은 크지 않음

pk, fk로 쿼리 최적화를 얻을 수 있는 경우, 집계 쿼리가 가장 큰 성능 향상을 기대할 수 있음