CSV 자동 감지
CSV 자동 감지 (CSV Auto Detection)
read_csv를 쓸 때 DuckDB는 CSV 스니퍼(CSV sniffer)를 이용해 파일을 어떻게 읽을지 스스로 알아냅니다. CSV 파일은 스스로를 설명하지 못하고 방언(dialect)도 제각각이라, 이 자동 감지 과정이 필요해요. 델리미터(구분자)·따옴표·이스케이프로 이루어진 방언, 각 컬럼의 타입, 헤더 행 유무를 차례로 판별합니다. 기본값은 전부 자동 감지지만, 시스템이 틀렸을 땐 개별 옵션을 직접 덮어쓸 수도 있습니다.
출처: 공식문서
샘플 크기 (Sample Size)
타입 감지는 파일의 샘플을 대상으로 동작합니다. 샘플 크기는 sample_size 파라미터로 조정할 수 있고, 기본값은 20,480행이에요. sample_size를 -1로 설정하면 파일 전체를 읽어 샘플링합니다:
SELECT * FROM read_csv('my_csv_file.csv', sample_size = -1);
샘플링 방식은 파일 종류에 따라 달라집니다. 디스크에 있는 일반 파일이라면 파일 내 여러 위치로 점프해서 샘플을 뽑지만, .gz 압축 CSV나 stdin처럼 점프할 수 없는 파일은 파일 시작 부분에서만 샘플을 가져옵니다.
sniff_csv 함수
CSV 스니퍼를 별도 단계로 실행하려면 sniff_csv(filename) 함수를 쓸 수 있어요. 감지된 CSV 속성을 한 행짜리 테이블로 반환하며, 샘플링할 행 수를 정하는 선택적 sample_size 파라미터를 받습니다.
FROM sniff_csv('my_file.csv');
FROM sniff_csv('my_file.csv', sample_size = 1000);
| 컬럼명 | 설명 | 예시 |
|---|---|---|
| Delimiter | 구분자 | , |
| Quote | 따옴표 문자 | " |
| Escape | 이스케이프 문자 | \ |
| NewLineDelimiter | 줄바꿈 구분자 | \r\n |
| Comment | 주석 문자 | # |
| SkipRows | 건너뛸 행 수 | 1 |
| HasHeader | 헤더 유무 | true |
| Columns | STRUCT의 LIST로 인코딩된 컬럼 타입 | ({'name': 'VARCHAR', 'age': 'BIGINT'}) |
| DateFormat | 날짜 형식 | %d/%m/%Y |
| TimestampFormat | 타임스탬프 형식 | %Y-%m-%dT%H:%M:%S.%f |
| UserArguments | sniff_csv를 호출할 때 준 인자 |
sample_size = 1000 |
| Prompt | CSV를 읽을 준비가 된 프롬프트 | FROM read_csv('my_file.csv', auto_detect=false, delim=',', ...) |
Prompt
Prompt 컬럼에는 스니퍼가 감지한 설정이 담긴 SQL 명령이 들어갑니다.
-- CLI에서 전체 명령을 보려면 line 모드를 쓴다
.mode line
SELECT Prompt FROM sniff_csv('my_file.csv');
Prompt = FROM read_csv('my_file.csv', auto_detect=false, delim=',', quote='"', escape='"', new_line='\n', skip=0, header=true, columns={...});
감지 단계 (Detection Steps)
방언 감지 (Dialect Detection)
방언 감지는 샘플들을 후보 값 집합으로 파싱해 보는 방식으로 동작합니다. (1) 각 행의 컬럼 수가 일관되고, (2) 각 행의 컬럼 수가 가장 많은 방언을 선택해요. 자동 감지 시 고려하는 방언은 다음과 같습니다.
| 파라미터 | 고려 값 |
|---|---|
| delim | , ; \t |
| quote | " ' (빈 값) |
| escape | " ' \ (빈 값) |
예시 파일 flights.csv를 보면:
FlightDate|UniqueCarrier|OriginCityName|DestCityName
1988-01-01|AA|New York, NY|Los Angeles, CA
1988-01-02|AA|New York, NY|Los Angeles, CA
1988-01-03|AA|New York, NY|Los Angeles, CA
이 파일의 방언 감지는 다음과 같이 진행됩니다.
|로 나누면 모든 행이 4개 컬럼으로 분리,로 나누면 2~4행은 3개, 첫 행은 1개 컬럼으로 분리;로 나누면 모든 행이 1개 컬럼으로 분리\t로 나누면 모든 행이 1개 컬럼으로 분리
모든 행이 같은 수의 컬럼으로 나뉘고, 행마다 컬럼이 하나 이상이면 실제로 구분자가 파일에 존재한다는 뜻이에요. 이 예시에서는 |가 구분자로 선택됩니다.
타입 감지 (Type Detection)
방언을 알아낸 뒤에는 각 컬럼의 타입을 추정합니다. 이 단계는 read_csv를 호출할 때만 수행된다는 점에 유의하세요. COPY 문의 경우 복사 대상 테이블의 타입을 그대로 사용합니다. 타입 감지는 컬럼 값을 후보 타입으로 변환해 보는 방식으로 동작하는데, 변환이 실패하면 그 후보 타입은 해당 컬럼의 후보 집합에서 제외됩니다. 모든 샘플을 처리한 뒤 남은 후보 중 우선순위가 가장 높은 타입을 골라요.
기본 후보 타입 집합은 우선순위 순으로 다음과 같습니다.
| Types |
|---|
| NULL |
| BOOLEAN |
| TIME |
| DATE |
| TIMESTAMP |
| TIMESTAMPTZ |
| BIGINT |
| DOUBLE |
| VARCHAR |
모든 값은 VARCHAR로 캐스팅할 수 있기 때문에, 이 타입은 우선순위가 가장 낮아요. 다른 타입으로 못 바꾸면 마지막 폴백으로 컬럼이 VARCHAR가 됩니다. flights.csv에서 FlightDate 컬럼은 DATE로, 나머지 컬럼은 VARCHAR로 캐스팅돼요.
CSV 리더가 고려할 후보 타입 집합은 auto_type_candidates 옵션으로 명시할 수 있습니다. 폴백 타입인 VARCHAR는 지정하든 안 하든 항상 후보에 포함됩니다. auto_type_candidates로 지정 가능한 추가 후보 타입은 우선순위 순으로 다음과 같아요.
| Types |
|---|
| TINYINT |
| SMALLINT |
| INTEGER |
| DECIMAL |
| FLOAT |
자동 감지 가능한 타입 집합은 제한적이어 보여도, 다음 절에서 설명하는 types 옵션을 쓰면 CSV 리더를 임의의 복잡한 타입으로도 읽을 수 있습니다. 타입 감지를 완전히 끄려면 all_varchar 옵션을 쓰세요. 이 옵션이 켜지면 모든 컬럼이 CSV 원본 그대로 VARCHAR로 유지됩니다.
따옴표가 있든 없든(예: "42"와 42) 타입 감지 결과는 같다는 점, 그리고 따옴표로 감싼 필드가 VARCHAR로 변환되는 대신 우선순위가 가장 높은 타입 후보를 찾는다는 점도 알아두면 좋아요.
타입 감지 덮어쓰기 (Overriding Type Detection)
감지된 타입은 types 옵션으로 개별적으로 덮어쓸 수 있습니다. 이 옵션은 두 가지 형태를 받아요.
- 타입 정의의 리스트:
types = ['INTEGER', 'VARCHAR', 'DATE']— CSV 파일에서 등장하는 순서대로 컬럼 타입을 덮어씁니다. - 이름 → 타입 맵:
types = {'quarter': 'INTEGER'}— 개별 컬럼의 타입을 덮어씁니다.
types 옵션에 지정할 수 있는 타입 집합은 auto_type_candidates처럼 제한적이지 않아서, 유효한 타입 정의라면 뭐든 허용됩니다. (유효한 타입 정의를 얻으려면 typeof() 함수나 DESCRIBE 결과의 column_type 컬럼을 활용하세요.) sniff_csv()의 Column 필드는 컬럼 이름과 타입을 담은 struct를 반환하는데, 타입 덮어쓰기의 기준으로 쓸 수 있어요.
헤더 감지 (Header Detection)
헤더 감지는 후보 헤더 행이 다른 행과 타입 면에서 얼마나 다른지를 확인하는 방식으로 동작합니다. flights.csv에서 헤더 행은 전부 VARCHAR 컬럼인 반면, 값에는 FlightDate 컬럼의 DATE 값이 섞여 있죠. 그래서 첫 행을 헤더로 정하고 거기서 컬럼 이름을 뽑습니다. 헤더 행이 없는 파일에서는 column0, column1처럼 컬럼 이름을 생성해요.
모든 컬럼이 VARCHAR 타입이면 헤더를 올바르게 감지할 수 없습니다. 시스템이 헤더 행과 다른 행을 구분할 수 없기 때문이에요. 이 경우엔 파일에 헤더가 있다고 가정하며, header 옵션을 false로 설정해 덮어쓸 수 있습니다.
날짜와 타임스탬프 (Dates and Timestamps)
DuckDB는 기본적으로 타임스탬프·날짜·시간에 ISO 8601 형식을 지원합니다. 하지만 모든 값이 이 표준대로 적힌 건 아니기 때문에, CSV 리더는 dateformat과 timestampformat 옵션도 지원해요. 이 형식 문자열로 날짜나 타임스탬프를 어떻게 읽을지 지정합니다.
자동 감지 과정에서 시스템은 날짜·시간이 다른 표현으로 저장돼 있는지도 알아봅니다. 다만 항상 가능한 건 아니에요. 예를 들어 01-02-2000은 1월 2일로도, 2월 1일로도 해석될 수 있어요. 이런 모호함은 보통 해결됩니다. 나중에 21-02-2000을 만나면 형식이 DD-MM-YYYY임을 알 수 있죠. 21월은 없으니 MM-DD-YYYY는 더 이상 불가능합니다. 데이터만으로 모호함을 해결할 수 없을 때는 시스템의 선호 목록을 따르고, 시스템이 틀렸다면 dateformat·timestampformat 옵션을 직접 지정하면 됩니다.
날짜(dateformat)에서 시스템이 고려하는 형식입니다. 모호한 경우 위쪽 항목이 아래쪽보다 우선 선택돼요(즉 ISO 8601이 MM-DD-YYYY보다 선호).
| dateformat |
|---|
| ISO 8601 |
| %y-%m-%d |
| %Y-%m-%d |
| %d-%m-%y |
| %d-%m-%Y |
| %m-%d-%y |
| %m-%d-%Y |
타임스탬프(timestampformat)에서 시스템이 고려하는 형식입니다. 모호한 경우 위쪽 항목이 우선 선택돼요.
| timestampformat |
|---|
| ISO 8601 |
| %y-%m-%d %H:%M:%S |
| %Y-%m-%d %H:%M:%S |
| %d-%m-%y %H:%M:%S |
| %d-%m-%Y %H:%M:%S |
| %m-%d-%y %I:%M:%S %p |
| %m-%d-%Y %I:%M:%S %p |
| %Y-%m-%d %H:%M:%S.%f |
더 알아보기 (Learn more)
- DuckDB 공식 문서: CSV Auto Detection
- CSV 개요: CSV Overview
- CSV 팁: CSV Tips