duckdb로 데이터 조회하기

DuckDB GUI에서 자주 쓰는 SELECT 문법 규칙

핵심 요약

duckdb에서는 CSV, Parquet, JSON 같은 로컬 파일을 바로 조회하는 기능이 강력함

⇒ 속도, SQL 전용 table을 생성하지 않아도 바로 조회 가능

sql

SELECT * FROM 'data.csv';
SELECT * FROM read_csv('data.csv', all_varchar = true);
SELECT * FROM 'data.parquet';

1. 파일을 테이블처럼 직접 SELECT

DuckDB는 파일 경로를 FROM에 바로 사용 가능

sql

SELECT *
FROM 'sales.csv';

Parquet도 동일한 구조로 읽음

⇒ 대규모의 데이터의 경우 csv로 읽을 경우 속도가 느려 Parquet 으로 변환추천

sql

SELECT *
FROM 'sales.parquet';

여러 파일은 glob(*) 패턴으로 읽을 수 있음

⇒ 여러개의 동일한 헤더를 가진 csv를 별도의 병합 없이 바로 읽을 수 있음.

sql

SELECT *
FROM 'logs/*.csv';

하위 폴더까지 포함하려면 다음처럼 가능

sql

SELECT *
FROM 'logs/**/*.csv';

/** 과 /*의 차이

sql

FROM 'logs/**/*.csv';
-- /** 은 logs/hello/hello.csv  logs/hello.csv 가능

FROM 'logs/*/*.csv'; 
-- /*은 log/hello/hello.csv 가능 logs/hello.csv 반드시 logs/하위폴더/*.csv의 구조

2. `read_csv()` 테이블 함수

read_csv()는 CSV 파일을 테이블처럼 반환하는 DuckDB 테이블 함수
FROM 절에서 테이블처럼 사용

sql

SELECT *
FROM read_csv('sales.csv');

SELECT *
FROM read_csv('sales.csv', auto_detect = true);
-- auto_detect = true : CSV 파일의 구조를 자동으로 추론하게 하는 옵션

3. named parameter 문법: `옵션명 = 값`

DuckDB 테이블 함수는 함수 인자 안에서 all_varchar = true, header = true 과 같이 옵션을 설정할 수 있음

sql

SELECT *
FROM read_csv(
    'sales.csv',
    delim = ',', -- 구분자를 , 로
    header = true, -- 첫줄을 헤더로
    all_varchar = true -- 모든 값을 문자로 읽겠다고 선언
);

-- csv 파일 구조를 기반으로 타입을 감지하지 못할 때 아래와 같이 선언 가능
SELECT *
FROM read_csv(
    'data.csv',
    delim = ',',
    header = true,
    columns = {
        'id': 'BIGINT',
        'name': 'VARCHAR',
        'created_at': 'DATE'
    }
);

주의할 점:

위치 인자 파일 경로가 먼저 옴
그 뒤 옵션은 옵션명 = 값 형태로 사용
Boolean 값은 true, false를 사용
문자열은 작은따옴표를 사용
옵션은 , 로 연결

4. `all_varchar = true`

CSV 컬럼 타입 자동 추론을 끄고 모든 컬럼을 VARCHAR로 읽음
엑셀에서 만든 CSV, 주민번호, 코드값, 앞자리 0이 있는 값, 날짜처럼 보이지만 문자로 유지해야 하는 값에 유용

sql

SELECT *
FROM read_csv('students.csv', all_varchar = true);

예시:

sql

SELECT student_id, phone, zip_code
FROM read_csv('students.csv', all_varchar = true);

이 옵션은 read_csv()에서 지원되며, COPY 문에는 적용되지 않음.

5. `auto_detect = true/false`

CSV 구분자, 따옴표, 헤더, 컬럼 타입 등을 DuckDB가 자동 감지할지 정함
기본값은 true

sql

SELECT *
FROM read_csv('sales.csv', auto_detect = true);

자동 감지를 끄고 직접 지정도 가능

sql

SELECT *
FROM read_csv(
    'sales.csv',
    auto_detect = false,
    delim = ',',
    header = true,
    columns = {
        'order_id': 'VARCHAR',
        'order_date': 'DATE',
        'amount': 'DOUBLE'
    }
);

6. `header = true/false`

첫 줄을 컬럼명으로 설정할지를 체크 ⇒ 기본값이 false
다만 auto_detect가 기본값이 true이기 때문에 기본 csv구조를 보고 header를 감지함.

sql

SELECT *
FROM read_csv('sales.csv', header = true);

헤더가 없는 파일이면 다음처럼 읽음

sql

SELECT *
FROM read_csv('sales_no_header.csv', header = false);

컬럼명을 직접 줄 수도 있음

sql

SELECT *
FROM read_csv(
    'sales_no_header.csv',
    header = false,
    names = ['order_id', 'order_date', 'amount']
);

7. `delim`, `sep`, `quote`, `escape`

CSV 구분자와 따옴표 규칙을 지정 가능

sql

SELECT *
FROM read_csv('sales_pipe.csv', delim = '|', header = true);

sep는 delim의 별칭

sql

SELECT *
FROM read_csv('sales_semicolon.csv', sep = ';', header = true);

따옴표 문자를 바꾸려면 quote를 사용

sql

SELECT *
FROM read_csv('sales.csv', quote = '"', escape = '"');

8. `columns = {...}` 구조체 문법

컬럼명과 타입을 직접 지정할 때 DuckDB의 구조체 문법을 사용

sql

SELECT *
FROM read_csv(
    'sales.csv',
    columns = {
        'order_id': 'VARCHAR',
        'order_date': 'DATE',
        'amount': 'DECIMAL(12,2)'
    }
);

columns를 지정하면 스키마 자동 감지는 꺼짐.

9. 오류가 있는 CSV 읽기

잘못된 행을 무시하고 읽고 싶으면 ignore_errors를 사용

sql

SELECT *
FROM read_csv('dirty.csv', ignore_errors = true);

오류 행 정보를 별도 테이블에 저장하려면 store_rejects를 사용

sql

SELECT *
FROM read_csv(
    'dirty.csv',
    store_rejects = true,
    rejects_table = 'reject_errors',
    rejects_scan = 'reject_scans'
);

10. `CREATE TABLE AS FROM`

파일을 읽어 바로 테이블로 만들 때 SELECT *를 생략가능

sql

CREATE TABLE sales AS
    FROM 'sales.csv';

일반 형태는 다음과 같음.

sql

CREATE TABLE sales AS
    SELECT * FROM 'sales.csv';

11. 리스트 리터럴: `[...]`

여러 파일을 명시할 때 DuckDB 리스트 문법을 사용

sql

SELECT *
FROM read_csv(['sales_jan.csv', 'sales_feb.csv']);

select 추천 패턴

CSV 구조를 모를 때

sql

FROM sniff_csv('data.csv');

sql

-- 1,000행 정도를 보고 CSV 구조/타입 추론
SELECT *
FROM read_csv('data.csv', sample_size = 1000);

-- 100,000행 정도를 보고 추론
SELECT *
FROM read_csv('data.csv', sample_size = 100000);

-- 전체 파일을 보고 추론
SELECT *
FROM read_csv('data.csv', sample_size = -1);
-- sample_size = -1 CSV 자동 감지에 파일 전체를 사용하라는 의미 
-- 다만 파일의 크기가 클 경우 시간이 오래걸림

모든 값을 문자로 안전하게 확인할 때

sql

SELECT *
FROM read_csv('data.csv', all_varchar = true);

구분자가 특이한 CSV

sql

SELECT *
FROM read_csv('data.psv', delim = '|', header = true);

여러 파일을 하나로 읽기

sql

SELECT *
FROM read_csv('data/*.csv', union_by_name = true);

원본 파일명까지 추적

sql

SELECT filename, *
FROM read_csv('data/*.csv', union_by_name = true);

참고 자료

DuckDB CSV Import: https://duckdb.org/docs/current/data/csv/overview
DuckDB CSV Auto Detection: https://duckdb.org/docs/current/data/csv/auto_detection
DuckDB FROM and JOIN Clauses: https://duckdb.org/docs/current/sql/query_syntax/from
DuckDB UI Extension: https://duckdb.org/docs/current/core_extensions/ui

목록으로 돌아가기