잘못된 CSV 파일 읽기

잘못된 CSV 파일 읽기 (Reading Faulty CSV Files)

CSV 파일은 정말 다양한 모양으로 등장합니다. 어떤 파일은 여기저기 오류가 섞여 있어서 "깔끔하게 읽기" 자체가 어려운 경우가 많죠. 이런 문제를 풀기 위해 DuckDB는 상세한 오류 메시지, 잘못된 줄 건너뛰기, 그리고 잘못된 줄을 임시 테이블에 따로 저장해 두는 기능까지 제공합니다. 덕분에 데이터 정제(cleaning) 단계를 훨씬 수월하게 진행할 수 있습니다.

출처: 공식문서

구조적 오류 (Structural Errors)

DuckDB는 여러 종류의 구조적 오류를 감지하고 건너뛸 수 있습니다. 각 오류를 예제와 함께 살펴보겠습니다. 예제에서는 다음 테이블을 기준으로 삼습니다.

CREATE TABLE people (name VARCHAR, birth_date DATE);

DuckDB가 감지하는 오류 유형은 다음과 같습니다.

CAST: CSV 파일의 어떤 컬럼이 기대한 스키마 값으로 형변환(cast)되지 못할 때 발생합니다. 예를 들어 Pedro,The 90s 줄은 The 90s라는 문자열을 날짜로 바꿀 수 없기 때문에 오류가 됩니다.
MISSING COLUMNS: CSV 파일의 한 줄이 기대한 컬럼 수보다 적을 때 발생합니다. 위 예제에서는 컬럼 두 개를 기대하므로, 값이 하나뿐인 줄(예: Pedro)이 오류가 됩니다.
TOO MANY COLUMNS: CSV의 한 줄이 기대한 컬럼 수보다 많을 때 발생합니다. 위 예제에서 컬럼이 두 개를 넘는 줄(예: Pedro,01-01-1992,pdet)이면 오류입니다.
UNQUOTED VALUE: CSV에서 따옴표로 감싼 값은 반드시 끝에서 닫혀야 합니다. 따옴표가 끝까지 열려 있으면 오류가 됩니다. 예를 들어 스캐너가 quote='"'를 쓴다고 할 때 "pedro"holanda, 01-01-1992 줄은 unquoted value 오류를 일으킵니다.
LINE SIZE OVER MAXIMUM: DuckDB에는 CSV 파일이 가질 수 있는 최대 줄 길이를 정하는 파라미터가 있고, 기본값은 2,097,152 바이트입니다. 스캐너를 max_line_size = 25로 두면 Pedro Holanda, 01-01-1992 줄이 25바이트를 넘으므로 오류가 납니다.
INVALID ENCODING: DuckDB는 UTF-8, UTF-16, Latin-1 인코딩을 지원합니다. 다른 문자를 포함한 줄은 오류가 됩니다. 예: pedro\xff\xff, 01-01-1992 줄은 문제가 됩니다.

CSV 오류 메시지의 구조 (Anatomy of a CSV Error)

기본적으로 CSV를 읽을 때 구조적 오류가 하나라도 발견되면, 스캐너는 즉시 스캔을 멈추고 사용자에게 오류를 던집니다. 이 오류들은 사용자가 CSV 파일에서 직접 문제를 파악할 수 있도록 최대한 많은 정보를 담도록 설계되어 있습니다.

전체 오류 메시지의 예는 다음과 같습니다.

Conversion Error:
CSV Error on Line: 5648
Original Line: Pedro,The 90s
Error when converting column "birth_date". date field value out of range: "The 90s", expected format is (DD-MM-YYYY)

Column date is being converted as type DATE
This type was auto-detected from the CSV file.
Possible solutions:
* Override the type for this column manually by setting the type explicitly, e.g., types={'birth_date': 'VARCHAR'}
* Set the sample size to a larger value to enable the auto-detection to scan more values, e.g., sample_size=-1
* Use a COPY statement to automatically derive types from an existing table.

  file= people.csv
  delimiter = , (Auto-Detected)
  quote = " (Auto-Detected)
  escape = " (Auto-Detected)
  new_line = \r\n (Auto-Detected)
  header = true (Auto-Detected)
  skip_rows = 0 (Auto-Detected)
  date_format = (DD-MM-YYYY) (Auto-Detected)
  timestamp_format =  (Auto-Detected)
  null_padding=0
  sample_size=20480
  ignore_errors=false
  all_varchar=0

첫 번째 블록은 오류가 발생한 위치에 대한 정보를 알려줍니다. 줄 번호, 원본 CSV 줄, 어떤 필드가 문제였는지가 담겨 있죠.

Conversion Error:
CSV Error on Line: 5648
Original Line: Pedro,The 90s
Error when converting column "birth_date". date field value out of range: "The 90s", expected format is (DD-MM-YYYY)

두 번째 블록은 해결 방법 후보를 제시합니다.

Column date is being converted as type DATE
This type was auto-detected from the CSV file.
Possible solutions:
* Override the type for this column manually by setting the type explicitly, e.g., types={'birth_date': 'VARCHAR'}
* Set the sample size to a larger value to enable the auto-detection to scan more values, e.g., sample_size=-1
* Use a COPY statement to automatically derive types from an existing table.

이 필드의 타입이 자동 감지된 것이므로, VARCHAR로 직접 지정하거나 데이터셋 전체를 활용해 타입 감지를 시도하라고 안내하고 있습니다.

마지막 블록은 오류를 일으킬 수 있는 스캐너 옵션 일부를 보여주면서, 그것이 자동 감지(Auto-Detected)된 것인지 사용자가 직접 설정한 것인지 표시합니다.

ignore_errors 옵션 사용하기

여러 구조적 오류가 섞인 CSV 파일을 읽되, 그냥 건너뛰고 정상 데이터만 가져오고 싶을 때가 있습니다. 이때 ignore_errors 옵션을 쓰면 됩니다. 이 옵션을 켜면 CSV 파서가 오류를 일으킬 줄은 무시하고 읽어냅니다. 아래 예제는 CAST 오류를 보여주지만, 아까 살펴본 구조적 오류 중 어떤 것이라도 잘못된 줄을 건너뛰게 만든다는 점을 기억하세요.

예를 들어 faulty.csv라는 파일이 있다고 합시다.

Pedro,31
Oogie Boogie, three

파일을 읽을 때 첫 번째 컬럼을 VARCHAR, 두 번째 컬럼을 INTEGER로 지정하면, three라는 문자열을 INTEGER로 바꿀 수 없기 때문에 로딩이 실패합니다.

다음 쿼리는 캐스팅 오류를 던질 것입니다.

FROM read_csv('faulty.csv', columns = {'name': 'VARCHAR', 'age': 'INTEGER'});

그러나 ignore_errors를 켜면 파일의 두 번째 줄을 건너뛰고, 온전한 첫 번째 줄만 출력합니다.

FROM read_csv(
    'faulty.csv',
    columns = {'name': 'VARCHAR', 'age': 'INTEGER'},
    ignore_errors = true
);

출력:

name	age
Pedro	31

한 가지 알아두면 좋은 점: CSV 파서는 프로젝션 푸시다운(projection pushdown) 최적화의 영향을 받습니다. 만약 name 컬럼만 선택한다면 두 줄 모두 유효한 것으로 간주됩니다. age에서의 캐스팅 오류가 아예 발생하지 않기 때문이죠.

SELECT name
FROM read_csv('faulty.csv', columns = {'name': 'VARCHAR', 'age': 'INTEGER'});

출력:

name
Pedro
Oogie Boogie

잘못된 CSV 줄 가져오기 (Retrieving Faulty CSV Lines)

잘못된 CSV 파일을 읽는 것도 중요하지만, 데이터 정제 작업에서는 정확히 어떤 줄이 손상되었고 그 줄에서 파서가 어떤 오류를 발견했는지 아는 것도 필요합니다. 이런 상황을 위해 DuckDB는 CSV Rejects Table 기능을 제공합니다. 기본적으로 이 기능은 임시 테이블 두 개를 만듭니다.

reject_scans: CSV 스캐너의 파라미터 정보를 저장합니다.
reject_errors: 각 잘못된 CSV 줄과 해당 줄이 어떤 CSV 스캐너에서 발생했는지에 대한 정보를 저장합니다.

구조적 오류 섹션에서 설명한 모든 오류는 이 rejects 테이블에 저장됩니다. 또한 한 줄에 여러 오류가 있으면 같은 줄에 대해 오류 하나당 하나씩, 여러 개의 엔트리가 저장됩니다.

Reject Scans

CSV Reject Scans 테이블은 다음 정보를 반환합니다.

Column name	Description	Type
scan_id	DuckDB 내부에서 해당 스캐너를 나타내는 ID	UBIGINT
file_id	하나의 스캐너가 여러 파일을 다룰 수 있으므로, 스캐너 안에서 파일을 구분하는 ID	UBIGINT
file_path	파일 경로	VARCHAR
delimiter	사용된 구분자(예: `;`)	VARCHAR
quote	사용된 따옴표(예: `"`)	VARCHAR
escape	사용된 이스케이프(예: `"`)	VARCHAR
newline_delimiter	사용된 개행 구분자(예: `\r\n`)	VARCHAR
skip_rows	파일 상단에서 건너뛴 줄 수	UINTEGER
has_header	파일에 헤더가 있는지	BOOLEAN
columns	파일의 스키마(모든 컬럼 이름과 타입)	VARCHAR
date_format	날짜 타입에 사용된 형식	VARCHAR
timestamp_format	타임스탬프 타입에 사용된 형식	VARCHAR
user_arguments	사용자가 직접 설정한 추가 스캐너 파라미터	VARCHAR

Reject Errors

CSV Reject Errors 테이블은 다음 정보를 반환합니다.

Column name	Description	Type
scan_id	DuckDB 내부에서 해당 스캐너를 나타내는 ID. rejects scans 테이블과 조인에 사용	UBIGINT
file_id	스캐너 안에서 파일을 구분하는 ID. rejects scans 테이블과 조인에 사용	UBIGINT
line	오류가 발생한 CSV 파일의 줄 번호	UBIGINT
line_byte_position	오류가 발생한 줄 시작의 바이트 위치	UBIGINT
byte_position	오류가 발생한 바이트 위치	UBIGINT
column_idx	오류가 특정 컬럼에서 발생했다면 그 컬럼의 인덱스	UBIGINT
column_name	오류가 특정 컬럼에서 발생했다면 그 컬럼의 이름	VARCHAR
error_type	발생한 오류의 유형	ENUM
csv_line	원본 CSV 줄	VARCHAR
error_message	DuckDB가 생성한 오류 메시지	VARCHAR

파라미터 (Parameters)

아래 파라미터들은 read_csv 함수에서 CSV Rejects Table을 설정할 때 사용됩니다.

Name	Description	Type	Default
store_rejects	true로 설정하면 파일의 오류를 건너뛰고 기본 rejects 임시 테이블에 저장	BOOLEAN	False
rejects_scan	잘못된 CSV 파일의 스캔 정보가 저장되는 임시 테이블 이름	VARCHAR	reject_scans
rejects_table	잘못된 CSV 줄 정보가 저장되는 임시 테이블 이름	VARCHAR	reject_errors
rejects_limit	rejects 테이블에 기록할 잘못된 레코드의 상한. 제한을 두지 않을 때는 0	BIGINT	0

잘못된 CSV 줄 정보를 rejects 테이블에 저장하려면 store_rejects 옵션을 true로 설정하기만 하면 됩니다.

FROM read_csv(
    'faulty.csv',
    columns = {'name': 'VARCHAR', 'age': 'INTEGER'},
    store_rejects = true
);

그런 다음 reject_scans와 reject_errors 테이블을 조회해 거부된 튜플 정보를 확인할 수 있습니다.

FROM reject_scans;

출력:

scan_id	file_id	file_path	delimiter	quote	escape	newline_delimiter	skip_rows	has_header	columns	date_format	timestamp_format	user_arguments
5	0	faulty.csv	,	"	"	\n	0	false	{'name': 'VARCHAR','age': 'INTEGER'}			store_rejects=true

FROM reject_errors;

출력:

scan_id	file_id	line	line_byte_position	byte_position	column_idx	column_name	error_type	csv_line	error_message
5	0	2	10	23	2	age	CAST	Oogie Boogie, three	Error when converting column "age". Could not convert string " three" to 'INTEGER'

더 알아보기 (Learn more)

DuckDB 공식 문서에서 CSV 읽기에 대한 더 자세한 내용을 확인하세요: CSV Import
형변환·자동 감지와 관련된 파라미터(types, sample_size, all_varchar)에 대한 전체 목록은 CSV 파라미터 문서를 참고하세요.