Amazon Athenaでtimestampのデータ型を参照したら「HIVE_BAD_DATA…」のエラーが出たときの対処

データアナリティクス事業本部の荒木です。

初めてAthenaを触ったのですが、S3に保管したCSVファイルをAthenaから読み込んだときに以下のようなエラーが出たので原因等についてまとめたいと思います。

HIVE_BAD_DATA: Error parsing field value '2021-09-29 13:27:21.000' for field 0: For input string: "2021-09-29 13:27:21.000"

本題

原因

今回事象が発生した際の条件等は以下になります。
timestamp型のデータが必ずしも読み込めなくなるわけでなく、ある条件で事象が発生すると思われます。

• S3に保管したCSVファイルを読み込む
• CSVファイル内の全てのデータが"で囲まれている
• 日付に関するデータがjava.sql.Timestamp(YYYY-MM-DD HH:MM:SS.fff)の形式である

上記の内容を踏まえ今回のエラーについては以下のことが原因かと思います。

• CSVデータ用のAthenaテーブルを作成&データが"で囲まれていたため、SerDeにOpenCSVSerDeを指定していた
• OpenCSVSerDeを使用する場合、日付のデータはUNIXのTIMESTAMP数値(1579059880000)を使用する必要がある
• 日付データにjava.sql.Timestampを使用する場合はLazySimpleSerDeを使用する必要がある

詳細

今回作成したAthenaテーブルのDDLとのサンプルデータは以下です。

"time_test"
"2021-09-20 13:27:21.000"
"2021-09-21 13:27:21.000"
"2021-09-22 13:27:21.000"
"2021-09-23 13:27:21.000"
"2021-09-24 13:27:21.000"

CREATE EXTERNAL TABLE IF NOT EXISTS cm_araki_test.test_tbl2 (
TIME_TEST TIMESTAMP
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES ('field.delim' = ',' , 'escapeChar' = '//' , 'quoteChar'= '"' )
LOCATION 's3://cm-araki/Athena/location/test/' 
TBLPROPERTIES ( 'skip.header.line.count' = '1' , 'write.compression' = 'NONE' , 'has_encrypted_data' = 'FALSE' )

今回全てのCSVデータが"で囲まれていたためOpenCSVSerDeを指定していましたが、日付データにjava.sql.Timestampを使用する場合はLazySimpleSerDeを使用する必要があるということですね。。

データに二重引用符 (") で囲まれた値が含まれる場合は、Athena で OpenCSV SerDe を使用して値を逆シリアル化できます。データに二重引用符 (") で囲まれた値が含まれていない場合は、SerDe の指定を省略できます。この場合、Athena はデフォルトの LazySimpleSerDe を使用します。

データに UNIX の TIMESTAMP 数値 (1579059880000 など) がある場合は、OpenCSVSerDe を使用します。データが java.sql.Timestamp 形式を使用する場合は、LazySimpleSerDe を使用します。

対応策

今回の事象が発生しないようにするためには以下の対応をする必要があるかと思います。

• 日付データを全てUNIXのTIMESTAMP数値に変更する
• データの"を全て外し、LazySimpleSerDeを使用したテーブルを作成する

しかし、どちらもデータをいじる必要があるため、本番環境で行うことは現実的ではないかもしれません。。。
今回はAthenaからデータの参照ができればよかったので、timestamp型だった列をvarchar型に変更しjava.sql.Timestampのまま文字列として参照することでエラーは出なくなりました。

まとめ

あまり今回のようなエラーについて書いてある記事がなかったので、何かしらの役に立てれば幸いです。

参照

CSV を処理するための OpenCSVSerDe
Amazon Athena のデータ型