DuckDB Tricks - Part 1

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

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

 

TL;DR : 간단한 예제 데이터 세트를 통해 DuckDB를 사용할 때 유용한 몇 가지 요령을 소개합니다.

 

이 블로그 글에서는 DuckDB 실 사례에서 유용하게 사용되는 다섯 가지 DuckDB 활용법을 소개합니다. 

예제 데이터 세트 만들기

먼저, 이 블로그 글의 나머지 부분에서 사용할 데이터 세트를 만들어 보겠습니다. 이를 위해 테이블을 정의하고, 데이터를 입력한 다음 CSV 파일로 내보낼 것입니다.

CREATE TABLE example (s STRING, x DOUBLE);
INSERT INTO example VALUES ('foo', 10/9), ('bar', 50/7), ('qux', 9/4);
COPY example TO 'example.csv';

 

DuckDB의 SQL에는 위의 SQL보다 간단히 작성할 수 있는 방법이 있습니다. 이를 “friendly SQL” 절이라고 합니다.

여기서는 VALUES 절을 FROM-first 구문과 결합하여 SELECT 절을 생략할 수 있습니다. 이를 통해 데이터 생성 스크립트를 원래 크기의 약 60% 수준으로 압축할 수 있습니다. 새로운 구문에서는 스키마 정의를 생략하고 단 한 줄의 명령어로 CSV 파일을 생성합니다.

COPY (FROM VALUES ('foo', 10/9), ('bar', 50/7), ('qux', 9/4) t(s, x))
TO 'example.csv';

 

위의 두 코드는 모두 다음과 같은 CSV 파일을 생성합니다. 

s,x
foo,1.1111111111111112
bar,7.142857142857143
qux,2.25

 

부동소수점 숫자의 보기 좋은 출력

부동소수점 숫자를 출력할 때 소수 부분이 읽거나 비교하기 어려울 수 있습니다. 예를 들어, 다음 쿼리는 1과 8 사이의 세 개의 숫자를 반환하지만, 소수 부분의 차이로 인해 출력된 너비가 매우 다릅니다.

SELECT x
FROM 'example.csv';

┌────────────────────┐
│         x          │
│       double       │
├────────────────────┤
│ 1.1111111111111112 │
│  7.142857142857143 │
│               2.25 │
└────────────────────┘

 

소수점 뒤 자릿수를 고정하여 열을 DECIMAL 형식으로 변환하면, 다음과 같이 보기 좋게 출력할 수 있습니다.

SELECT x::DECIMAL(15, 3) AS x
FROM 'example.csv';

┌───────────────┐
│       x       │
│ decimal(15,3) │
├───────────────┤
│         1.111 │
│         7.143 │
│         2.250 │
└───────────────┘

 

또 다른 방법으로 printf 또는 format 함수를 사용하는 방법이 있습니다. 예를 들어:

SELECT printf('%.3f', x)
FROM 'example.csv';

 

하지만 이러한 방식은 기억하기 어려운 서식 문자열을 명시적으로 지정해야 한다는 단점이 있습니다. 더 큰 문제는 위의 문장이 문자열 값을 반환하기 때문에 이후의 작업(예: 정렬)을 수행하기가 더 어려워진다는 점입니다. 따라서 부동 소수점 숫자의 정밀도를 온전히 유지해야 할 필요가 없다면, 대부분의 사용 사례에서 DECIMAL 값으로 형변환하는 것이 더 바람직한 해결책입니다.

테이블 스키마 복사하기

LIMIT 0를 사용하면 데이터를 복사하지 않고 테이블의 스키마만 복사할 수 있습니다.

DROP TABLE example;
CREATE TABLE example AS
    FROM 'example.csv';
CREATE TABLE tbl AS
    FROM example
    LIMIT 0;

 

그러면 소스 테이블과 동일한 스키마를 가진 빈 테이블이 생성됩니다:

DESCRIBE tbl;

┌─────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │  null   │   key   │ default │  extra  │
│   varchar   │   varchar   │ varchar │ varchar │ varchar │ varchar │
├─────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ s           │ VARCHAR     │ YES     │         │         │         │
│ x           │ DOUBLE      │ YES     │         │         │         │
└─────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘

 

 

데이터 섞기

때로는 데이터를 섞어서 순서에 약간의 무작위성을 부여해야 할 때가 있습니다. 비결정론적으로 데이터를 섞으려면, random() 함수를 사용하여 임의의 값을 기준으로 정렬하면 됩니다.

FROM 'example.csv' ORDER BY random();

┌─────────┬────────────────────┐
│    s    │         x          │
│ varchar │       double       │
├─────────┼────────────────────┤
│ qux     │               2.25 │
│ foo     │ 1.1111111111111112 │
│ bar     │  7.142857142857143 │
└─────────┴────────────────────┘

 

재현 가능한 셔플링하는 것은 조금 더 까다롭습니다. 이 경우는 rowid 의사 열의 해시 값을 기준으로 정렬하면 가능합니다. 이 열은 물리적 테이블에서만 사용할 수 있으므로, 먼저 CSV 파일을 테이블에 로드한 다음 다음과 같이 셔플링 작업을 수행해야 합니다.

CREATE OR REPLACE TABLE example AS FROM 'example.csv';
FROM example ORDER BY hash(rowid + 42);

┌─────────┬────────────────────┐
│    s    │         x          │
│ varchar │       double       │
├─────────┼────────────────────┤
│ bar     │  7.142857142857143 │
│ qux     │               2.25 │
│ foo     │ 1.1111111111111112 │
└─────────┴────────────────────┘

 

+42는 첫 번째 행의 위치를 약간 이동시키기 위해 사용했습니다. hash(0)은 가능한 최소값인 0을 반환하므로, 정렬에 이를 사용하면 첫 번째 행이 제자리에 그대로 남게 됩니다.

CSV 로더에서 데이터형 지정하기

DuckDB의 CSV 로더는 BOOLEAN, BIGINT, DOUBLE, TIME, DATE, TIMESTAMP 및 VARCHAR 등의 데이터 타입을 자동으로 감지합니다. 경우에 따라 특정 열에 대해 감지된 데이터형을 이 목록에 없는 다른 데이터형으로 설정하고 싶을 수 있습니다. 예를 들어, 처음부터 열 x를 DECIMAL 값으로 처리하고 싶다면, read_csv 함수의 types 인수를 사용하여 열별로 이를 설정할 수 있습니다.

CREATE OR REPLACE TABLE example AS
    FROM read_csv('example.csv', types = {'x': 'DECIMAL(15, 3)'});

 

이 결과는 다음과 같습니다. 

 

FROM example;

┌─────────┬───────────────┐
│    s    │       x       │
│ varchar │ decimal(15,3) │
├─────────┼───────────────┤
│ foo     │         1.111 │
│ bar     │         7.143 │
│ qux     │         2.250 │
└─────────┴───────────────┘

 

<출처: https://duckdb.org/2024/08/19/duckdb-tricks-part-1>