[BigQuery] Surrogate key를 활용하여 JOIN 성능 높이기 | LIM

Surrogate Key(대체키) 란

테이블의 입력 데이터의 어떤 열을 기본키(Primary Key)로 하지 않고 시스템쪽에 독립적으로 할당된 키를 의미한다. 

 

Natural Key(자연키) 란

입력 데이터 자체의 열을 기본키로 하는 경우

 

 

Surrogate Key는 언제 사용해야 하는가

1. Natural Key의 부적합성

자연키는 비즈니스 도메인에서 사용되는 실제 속성(ex. 주민등록번호, 이메일주소) 을 기본키로 사용한다. 하지만 자연키가 유일성, 불변성, 간결성 등의 요구 사항을 충족시키지 못하는 경우가 있을 수 있다. 예를 들어, 이름과 생년월일로 구성된 자연키는 중복되는 경우가 발생할 수 있기 때문이다. 이런 경우 surrogate key 를 활용해 각 레코드를 고유하게 식별할 수 있다. 

 

2. 성능 및 조인의 복잡성

Surrogate Key의 경우 작은 크기의 정수 또는 GUID와 같은 단순한 값으로 구성되기 때문에 자연키보다 작은 크기를 가지고 있을 가능성이 높다. 따라서 인덱스 크기를 줄이고 검색 및 조인 성능을 향상시킬 수 있다. 또한, 자연키의 조인이 복잡한 경우에는 surrogate key 를 사용하여 조인 작업을 단순화할 수 있다. 

-> 이 방법이 이번에 내가 적용한 방법이다. 

 

3. 향후 변경 가능성

자연키는 비즈니스 요구사항에 의해 변경될 수 있다. 예를 들어 이메일 주소가 변경되었거나 주민등록번호가 잘못 입력된 경우에는 자연 키를 수정해야 한다. 하지만 surrogate key 의 경우 의미가 없기 때문에 비즈니스 요구사항에 따라 자연 키를 변경할 필요가 없다.

 

4. 다중 컬럼 키

복합키(composite key) 는 둘 이상의 컬럼을 조합하여 기본키로 사용한다. 그러나 복합키는 복잡성을 증가시킬 수 있고, 변경이 필요한 경우에는 번거로울 수 있다. surrogate key 를 사용하면 단일 컬럼으로 구성된 기본 키를 유지하면서 다중 컬럼 키의 이점을 얻을 수 있다. 

 

 

Surrogate Key 를 사용한 이유

현재 외부에서 주는 데이터를 BigQuery 에 적재하고 있는데 쌓이는 데이터 양이 꽤나 많은 작업이었다.
이대로 계속 주는대로 쌓다가는 빅쿼리의 저장 비용 + JOIN 하는데 드는 쿼리 비용이 많이 나올 것이라 예상했다. 

 

🧐 JOIN 하는데 쿼리 비용이 많이 나올 것이라 예상한 이유

현재 쌓이는 데이터가 조금 특이하게 들어왔다. 

 

서로 다른 테이블인데 앞의 4개는 서로 동일하고 마지막 컬럼의 종류만 다른 형태였다. 

A부터 D까지가 동일한 시간이나 위치에 관한 것이었다면 E1과 E2는 각각 해당 위치에서 관찰하는 서로 다른 종류의 데이터였던 것이다. 

❗️위 데이터를 그대로 유지하면 발생할 수 있는 문제점

- 동일한 데이터(column A ~ column D) 를 중복으로 계속해서 저장해야 함 -> 저장 비용 증가

- 두 테이블을 한번에 보기 위해서 column A ~ column D 까지 JOIN 해야함 -> 복잡성 증가

 

위 두가지 케이스를 고려해 surrogate key 를 도입하기로 했다. 

 

 

BigQuery 에서 Surrogate Key 생성하기

https://cloud.google.com/bigquery/docs/reference/standard-sql/hash_functions

Hash functions  |  BigQuery  |  Google Cloud

FARM_FINGERPRINT를 이용해서 surrgate key 를 생성할 수 있다. 

FARM_FINGERPRINT는 해시함수로 특정 문자열을 입력으로 받으면 숫자로 반환한다. 

 

1. 먼저 여러개의 컬럼(ColumnA ~ ColumnD)들을 STRUCT 형태로 변환한다.
struct 형태고 변환하게 되면 Python Dict 형태처럼 변환된다.
ex) {"datetime": "2023-06-01 23:00:00", "latitude": 37, "longitude": 127}

 

2. TO_JSON_STRING 을 이용하여 JSON-formatted String 으로 변환한다. 

 

3. 그 다음 FARM_FINGERPRINT 해시 함수를 통해 숫자로 변환한다.

select FARM_FINGERPRINT('{"datetime": "2023-06-01 23:00:00", "latitude": 37, "longitude": 127}')

>> -6381889470698750336

 

이렇게 진행하면 총 4개의 컬럼 (Column A ~ Column D) 을 한 개의 surrogate key 컬럼으로 관리할 수 있어 JOIN 을 할 때 속도도 빨라질 뿐더러 복잡성도 줄어든다. 

 

다만, surrogate key 로 관리되면 해당 surrogate key 가 어떤 조합으로 나온건지 알 수 없기 때문에 이를 관리할 수 있는 테이블을 하나 더 만들어야 한다. 

 

현재는 아래 3개 테이블을 surrogate key 를 통해 JOIN 해서 확인하고 있다.

 

 

 

---

📚참고

https://medium.com/google-cloud/how-to-work-with-array-and-structs-in-bigquery-9c0a2ea584a6

How to work with Array and Structs in BigQuery