BuildWork
강의자료 목록으로

duckdb로 데이터 조회하기

duckdb select문법

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
    • 목록으로 돌아가기