Open CSV SerDe 라이브러리를 사용하여 Athena에서 쉼표로 구분된 데이터(CSV)의 테이블을 만듭니다.
직렬화 라이브러리 이름
Open CSV SerDe의 직렬화 라이브러리 이름은 org.apache.hadoop.hive.serde2.OpenCSVSerde
입니다. 소스 코드 정보는 Apache 설명서의 CSV SerDe
Open CSV SerDe 사용
이 SerDe를 사용하려면 ROW FORMAT
SERDE
뒤에 정규화된 클래스 이름을 지정합니다. 또한 다음 예제와 같이 SERDEPROPERTIES
안에 구분 기호를 지정합니다.
...
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
"separatorChar" = ",",
"quoteChar" = "`",
"escapeChar" = "\\"
)
헤더 무시
테이블을 정의할 때 데이터의 헤더를 무시하려면 다음 예제와 같이 skip.header.line.count
테이블 속성을 사용합니다.
TBLPROPERTIES ("skip.header.line.count"="1")
예제는 Amazon VPC 흐름 로그 쿼리 및 Amazon CloudFront 로그 쿼리의 CREATE TABLE
문을 참조하세요.
문자열 데이터에 대한 고려 사항
Open CSV SerDe는 문자열 데이터에 대해 다음과 같은 특성을 갖습니다.
-
큰따옴표(
"
)를 기본 인용 부호로 사용하고 다음과 같은 구분자, 따옴표 및 이스케이프 문자를 지정할 수 있습니다.WITH SERDEPROPERTIES ("separatorChar" = ",", "quoteChar" = "`", "escapeChar" = "\\" )
-
\t
또는\n
을 직접 이스케이프할 수 없습니다. 이스케이프하려면"escapeChar" = "\\"
를 사용합니다. 예시는 Example: Escaping \t or \n을 확인하세요. -
Open CSV SerDe는 CSV 파일에 포함된 줄 바꿈을 지원하지 않습니다.
비문자열 데이터에 대한 고려 사항
STRING
이 아닌 데이터 형식의 경우 Open CSV SerDe는 다음과 같이 동작합니다.
-
BOOLEAN
,BIGINT
,INT
,DOUBLE
데이터 형식을 인식합니다. -
숫자 데이터 형식으로 정의된 열의 빈 값이나 null 값을 인식하지 못하므로 이러한 값을
string
으로 유지합니다. 한 가지 해결 방법은 null 값을 포함한 열을string
으로 생성한 다음CAST
를 사용하여 쿼리의 필드를 숫자 데이터 형식으로 변환하면서 null에 대해 기본값0
을 부여하는 것입니다. 자세한 내용은 AWS 지식 센터의 Athena에서 CSV 데이터를 쿼리하는 경우 HIVE_BAD_DATA: Error parsing field value 오류가 발생합니다를 참조하세요. -
CREATE TABLE
문에서timestamp
데이터 형식으로 지정된 열의 경우 밀리초 단위의 UNIX 숫자 형식(예:1579059880000
)으로 지정되어 있으면TIMESTAMP
데이터를 인식합니다. 예시는 Example: Using the TIMESTAMP type and DATE type specified in the UNIX numeric format을 확인하세요.-
Open CSV SerDe는
"YYYY-MM-DD HH:MM:SS.fffffffff"
(소수점 9자리 정밀도)처럼 JDBC와 호환되는java.sql.Timestamp
형식의TIMESTAMP
는 지원하지 않습니다.
-
-
CREATE TABLE
문에서DATE
데이터 형식으로 지정된 열의 경우 값이 1970년 1월 1일 이후 경과된 일 수를 나타내면 값을 인식합니다. 예를 들어date
데이터 형식을 가진 열의 값18276
은 쿼리 시2020-01-15
로 렌더링됩니다. 이 UNIX 형식에서는 하루를 86,400초로 간주합니다.-
Open CSV SerDe는 다른 형식의
DATE
를 직접 지원하지 않습니다. 다른 형식의 타임스탬프 데이터를 처리하려면 열을string
으로 정의한 다음SELECT
쿼리에서 원하는 결과를 반환하도록 시간 변환 함수를 사용합니다. 자세한 내용은 AWS 지식 센터의 Amazon Athena에서 테이블을 쿼리할 때 TIMESTAMP 결과가 비어 있음 항목을 참조하세요.
-
-
표에서 열을 원하는 형식으로 변환하려면 테이블에 대한 뷰를 생성하고
CAST
를 사용하여 원하는 형식으로 변환할 수 있습니다.
예시
예제: 단순 CSV 데이터 쿼리
다음 예제에서는 위치 s3://amzn-s3-demo-bucket/mycsv/
에 다음과 같은 내용이 포함된 CSV 데이터가 저장되어 있다고 가정합니다.
"a1","a2","a3","a4"
"1","2","abc","def"
"a","a1","abc3","ab4"
CREATE TABLE
문을 사용해 해당 데이터를 바탕으로 한 Athena 테이블을 만듭니다. 다음 예제처럼 ROW FORMAT SERDE
뒤의 OpenCSVSerde
(‘d’가 소문자임)를 참조해 WITH SERDEPROPERTIES
에 문자 구분 기호, 따옴표, 이스케이프 문자를 지정합니다.
CREATE EXTERNAL TABLE myopencsvtable (
col1 string,
col2 string,
col3 string,
col4 string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
'separatorChar' = ',',
'quoteChar' = '"',
'escapeChar' = '\\'
)
STORED AS TEXTFILE
LOCATION 's3://amzn-s3-demo-bucket/mycsv/';
테이블의 모든 값을 쿼리합니다.
SELECT * FROM myopencsvtable;
쿼리는 다음 값을 반환합니다.
col1 col2 col3 col4
-----------------------------
a1 a2 a3 a4
1 2 abc def
a a1 abc3 ab4
예: UNIX 숫자 형식으로 지정된 TIMESTAMP 형식 및 DATE 형식 사용
쉼표로 구분된 데이터를 포함한 다음 3개 열을 생각해보겠습니다. 각 열의 값은 큰따옴표로 묶여 있습니다.
"unixvalue creationdate 18276 creationdatetime 1579059880000","18276","1579059880000"
다음 문은 Athena에서, 지정된 Amazon S3 버킷 위치로부터 테이블을 만듭니다.
CREATE EXTERNAL TABLE IF NOT EXISTS testtimestamp1(
`profile_id` string,
`creationdate` date,
`creationdatetime` timestamp
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
LOCATION 's3://amzn-s3-demo-bucket'
이제 다음 쿼리를 실행합니다.
SELECT * FROM testtimestamp1
이 쿼리는 날짜 및 시간 데이터를 보여주는 다음과 같은 결과를 반환합니다.
profile_id creationdate creationdatetime unixvalue creationdate 18276 creationdatetime 1579146280000 2020-01-15 2020-01-15 03:44:40.000
예: \t 또는 \n 이스케이프
다음 테스트 데이터를 고려하세요.
" \\t\\t\\n 123 \\t\\t\\n ",abc " 456 ",xyz
다음 문은 Athena에서 "escapeChar" = "\\"
를 지정한 테이블을 만듭니다.
CREATE EXTERNAL TABLE test1 (
f1 string,
s2 string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES ("separatorChar" = ",", "escapeChar" = "\\")
LOCATION 's3://amzn-s3-demo-bucket/dataset/test1/'
이제 다음 쿼리를 실행합니다.
SELECT * FROM test1;
\t
또는 \n
을 올바르게 이스케이프하여 이 결과를 반환합니다.
f1 s2 \t\t\n 123 \t\t\n abc 456 xyz