DuckDB Tricks – Part 2

https://2stndard.tistory.com/notice/203

[발간예정][EPL과 유튜브로 배우는 DuckDB] 실습 코드와 데이터

 

TL;DR : DuckDB가 제공하는 데이터 정리, 변환, 요약하는 쿼리에 대해 다룹니다.

 

데이터셋

이 블로그에서 사용하는 데이터셋은 아래와 같은 컨퍼런스 일정을 담은 schedule.csv를 사용합니다. 이 일정에는 시간대, 장소 및 예정된 행사가 포함되어 있습니다.

timeslot,location,event
2024-10-10 9am,room Mallard,Keynote
2024-10-10 10.30am,room Mallard,Customer stories
2024-10-10 10.30am,room Fusca,Deep dive 1
2024-10-10 12.30pm,main hall,Lunch
2024-10-10 2pm,room Fusca,Deep dive 2

CSV 파일의 타임스탬프 수정

실제 사용 사례에서 흔히 볼 수 있듯이, 입력 CSV 파일은 2024-10-10 오전 9시와 같이 불규칙한 타임스탬프로 인해 정리가 잘 되어 있지 않습니다. 따라서 DuckDB의 CSV 리더를 사용하여 schedule.csv 파일을 불러오면, CSV 스니퍼는 첫 번째 열을 VARCHAR 필드로 인식합니다:

 

CREATE TABLE schedule_raw AS
    SELECT * FROM 'https://duckdb.org/data/schedule.csv';

SELECT * FROM schedule_raw;

┌────────────────────┬──────────────┬──────────────────┐
│      timeslot      │   location   │      event       │
│      varchar       │   varchar    │     varchar      │
├────────────────────┼──────────────┼──────────────────┤
│ 2024-10-10 9am     │ room Mallard │ Keynote          │
│ 2024-10-10 10.30am │ room Mallard │ Customer stories │
│ 2024-10-10 10.30am │ room Fusca   │ Deep dive 1      │
│ 2024-10-10 12.30pm │ main hall    │ Lunch            │
│ 2024-10-10 2pm     │ room Fusca   │ Deep dive 2      │
└────────────────────┴──────────────┴──────────────────┘

 

가장 이상적으로, 나중에 쿼리에서 타임슬롯 열을 타임스탬프로 처리할 수 있도록 이 열의 데이터형을 TIMESTAMP로 설정하는 것이 좋습니다. 이를 위해 방금 로드한 테이블을 활용하여 정규 표현식을 기반으로 한 검색 및 교체 작업을 수행함으로써, 문제 있는 엔티티들을 수정하고 형식을 ‘시간.분’ 뒤에 am 또는 pm이 붙는 형태로 통일할 수 있습니다. 그런 다음, 문자열의 am/pm 부분을 추출하는 %p 형식 지정자를 사용하여 strptime 함수로 문자열을 타임스탬프로 변환합니다.

CREATE TABLE schedule_cleaned AS
    SELECT
        timeslot
            .regexp_replace(' (\d+)(am|pm)$', ' \1.00\2')
            .strptime('%Y-%m-%d %H.%M%p') AS timeslot,
        location,
        event
    FROM schedule_raw;

 

가독성을 높이기 위해 dot(.)을 사용하여 함수 체이닝을 사용했습니다. 예를 들어, regexp_replace(string, pattern, replacement)는 string.regexp_replace(pattern, replacement)와 동일합니다. 그 결과는 다음과 같습니다:

┌─────────────────────┬──────────────┬──────────────────┐
│      timeslot       │   location   │      event       │
│      timestamp      │   varchar    │     varchar      │
├─────────────────────┼──────────────┼──────────────────┤
│ 2024-10-10 09:00:00 │ room Mallard │ Keynote          │
│ 2024-10-10 10:30:00 │ room Mallard │ Customer stories │
│ 2024-10-10 10:30:00 │ room Fusca   │ Deep dive 1      │
│ 2024-10-10 12:30:00 │ main hall    │ Lunch            │
│ 2024-10-10 14:00:00 │ room Fusca   │ Deep dive 2      │
└─────────────────────┴──────────────┴──────────────────┘

 

누락된 값 채우기

다음으로, 전체적인 상황을 반영한 일정을 도출하고자 합니다. 즉, 모든 위치에 대한 모든 시간대 정보가 표에 포함되어야 합니다. 이벤트가 지정되지 않은 시간대-위치 조합의 경우, <empty>라는 문자열을 명시적으로 추가하고자 합니다.

이를 위해 먼저 CROSS JOIN을 사용하여 가능한 모든 조합을 포함하는 timeslot_location_combinations 테이블을 생성합니다. 그런 다음 LEFT JOIN을 사용하여 이 조합 테이블과 원본 테이블을 연결합니다. 마지막으로 coalesce 함수를 사용하여 NULL 값을 <empty> 문자열로 대체합니다.

CROSS JOIN 절은 조인 조건을 명시하지 않고 FROM 절에 테이블을 나열하는 것과 동일합니다. CROSS JOIN을 명시적으로 지정함으로써, 우리는 데카트 곱을 계산하려는 의도를 나타내는 것이며, 이는 대용량 테이블에서 처리 비용이 많이 드는 연산이므로 대부분의 경우 피해야 합니다.

CREATE TABLE timeslot_location_combinations AS 
    SELECT timeslot, location
    FROM (SELECT DISTINCT timeslot FROM schedule_cleaned)
    CROSS JOIN (SELECT DISTINCT location FROM schedule_cleaned);

CREATE TABLE schedule_filled AS
    SELECT timeslot, location, coalesce(event, '<empty>') AS event
    FROM timeslot_location_combinations
    LEFT JOIN schedule_cleaned
        USING (timeslot, location)
    ORDER BY ALL;

SELECT * FROM schedule_filled;

┌─────────────────────┬──────────────┬──────────────────┐
│      timeslot       │   location   │      event       │
│      timestamp      │   varchar    │     varchar      │
├─────────────────────┼──────────────┼──────────────────┤
│ 2024-10-10 09:00:00 │ main hall    │ <empty>          │
│ 2024-10-10 09:00:00 │ room Fusca   │ <empty>          │
│ 2024-10-10 09:00:00 │ room Mallard │ Keynote          │
│ 2024-10-10 10:30:00 │ main hall    │ <empty>          │
│ 2024-10-10 10:30:00 │ room Fusca   │ Deep dive 1      │
│ 2024-10-10 10:30:00 │ room Mallard │ Customer stories │
│ 2024-10-10 12:30:00 │ main hall    │ Lunch            │
│ 2024-10-10 12:30:00 │ room Fusca   │ <empty>          │
│ 2024-10-10 12:30:00 │ room Mallard │ <empty>          │
│ 2024-10-10 14:00:00 │ main hall    │ <empty>          │
│ 2024-10-10 14:00:00 │ room Fusca   │ Deep dive 2      │
│ 2024-10-10 14:00:00 │ room Mallard │ <empty>          │
├─────────────────────┴──────────────┴──────────────────┤
│ 12 rows                                     3 columns │
└───────────────────────────────────────────────────────┘

 

WITH 절을 사용하여 모든 내용을 하나의 쿼리로 통합할 수도 있습니다:

WITH timeslot_location_combinations AS (
    SELECT timeslot, location
    FROM (SELECT DISTINCT timeslot FROM schedule_cleaned)
    CROSS JOIN (SELECT DISTINCT location FROM schedule_cleaned)
)
SELECT timeslot, location, coalesce(event, '<empty>') AS event
FROM timeslot_location_combinations
LEFT JOIN schedule_cleaned
    USING (timeslot, location)
ORDER BY ALL;

반복되는 데이터 변환 단계

데이터를 정리하고 변환하는 과정은 대개 후속 분석에 가장 알맞은 형태로 데이터를 다듬는 여러 단계로 진행됩니다. 이런 작업은 보통 CREATE TABLE ... AS SELECT 문을 반복적으로 사용해서, 새로운 테이블을 계속 만들어내는 방식으로 이뤄집니다.

예를 들어, 앞에서 schedule_raw, schedule_cleaned, schedule_filled 테이블을 차례대로 만들었습니다. 만약 타임스탬프 정리 단계를 생략하고 싶다면, schedule_filled를 만들 때 schedule_cleaned 대신 schedule_raw를 사용해서 쿼리를 다시 작성해야 합니다. 이렇게 하면 반복적이고 실수하기 쉬운 작업이 많아질 뿐 아니라, 예전 쿼리를 깜빡 잊고 수정하지 않으면 쓸데없는 임시 데이터가 데이터베이스에 쌓이게 됩니다.

이런 불편을 줄이기 위해, 대화형 분석을 할 때는 CREATE OR REPLACE 문을 사용하여 같은 테이블 이름으로 계속 덮어쓰는 것이 훨씬 편리한 경우가 많습니다.

CREATE OR REPLACE TABLE table_name AS
    ...
    FROM table_name
    ...;

 

이 방법을 활용하면 다음과 같이 분석을 수행할 수 있습니다:

CREATE OR REPLACE TABLE schedule AS
    SELECT * FROM 'https://duckdb.org/data/schedule.csv';

CREATE OR REPLACE TABLE schedule AS
    SELECT
        timeslot
            .regexp_replace(' (\d+)(am|pm)$', ' \1.00\2')
            .strptime('%Y-%m-%d %H.%M%p') AS timeslot,
        location,
        event
    FROM schedule;

CREATE OR REPLACE TABLE schedule AS
    WITH timeslot_location_combinations AS (
        SELECT timeslot, location
        FROM (SELECT DISTINCT timeslot FROM schedule)
        CROSS JOIN (SELECT DISTINCT location FROM schedule)
    )
    SELECT timeslot, location, coalesce(event, '<empty>') AS event
    FROM timeslot_location_combinations
    LEFT JOIN schedule
        USING (timeslot, location)
    ORDER BY ALL;

SELECT * FROM schedule;

 

이 방법을 쓰면 중간에 어떤 단계를 건너뛰더라도, 그 다음 단계를 따로 수정하지 않고 바로 분석을 이어갈 수 있습니다.

또한, 이제는 테이블을 직접 삭제하지 않아도 스크립트를 처음부터 다시 실행할 수 있습니다. CREATE OR REPLACE 문이 기존 테이블을 알아서 새 걸로 바꿔주기 때문이죠.

열에 대한 체크섬 계산

테이블의 각 열에 대해 체크섬을 계산하면 여러모로 도움이 됩니다. 예를 들어, 어떤 작업 전후로 열의 값이 달라졌는지 확인할 때 유용하죠. schedule 테이블의 체크섬을 계산하려면 다음과 같이 하면 됩니다:

SELECT bit_xor(md5_number(COLUMNS(*)::VARCHAR))
FROM schedule;

 

무슨 일이 벌어지고 있는지 살펴보면 이렇습니다. 먼저 열 이름들을 조회해서, 각각의 값을 모두 VARCHAR로 변환합니다. 그리고 md5_number 함수를 이용해 각 값을 숫자형 MD5 해시로 바꿉니다. 이 해시값들을 다시 bit_xor 집계 함수로 합칩니다. 이렇게 하면 각 열마다 하나씩 HUGEINT(INT128) 값이 만들어지고, 이 숫자를 활용해서 테이블의 내용을 서로 비교할 수 있게 됩니다.

위 스크립트에서 해당 쿼리를 실행하면 아래와 같이 결과가 나타납니다.

┌──────────────────────────────────────────┬──────────┬──────────────────────────────────────────┐
│                 timeslot                 │ location │                  event                   │
│                  int128                  │  int128  │                  int128                  │
├──────────────────────────────────────────┼──────────┼──────────────────────────────────────────┤
│ -162418013182718436871288818115274808663 │        0 │ -135609337521255080720676586176293337793 │
└──────────────────────────────────────────┴──────────┴──────────────────────────────────────────┘

체크섬 쿼리를 위한 매크로 만들기

새로운 query_table 함수를 사용하여 체크섬 쿼리를 테이블 매크로로 변환할 수 있습니다:

CREATE MACRO checksum(table_name) AS TABLE
    SELECT bit_xor(md5_number(COLUMNS(*)::VARCHAR))
    FROM query_table(table_name);

 

이렇게 하면 스케줄 테이블에서 다음과 같이 간단히 호출할 수 있습니다(DuckDB의 FROM-first 구문도 활용합니다):

FROM checksum('schedule');

┌──────────────────────────────────────────┬──────────┬──────────────────────────────────────────┐
│                 timeslot                 │ location │                  event                   │
│                  int128                  │  int128  │                  int128                  │
├──────────────────────────────────────────┼──────────┼──────────────────────────────────────────┤
│ -162418013182718436871288818115274808663 │        0 │ -135609337521255080720676586176293337793 │
└──────────────────────────────────────────┴──────────┴──────────────────────────────────────────┘

 

<출처: https://duckdb.org/2024/10/11/duckdb-tricks-part-2>