[Athena] OpenCSVSerDe使用時のデータ型の挙動をまとめてみた

CSVファイルを読むAthenaのテーブルを定義する際、日付（date）や日時（timestamp）型が思ったように扱えなくて困りました。 Athenaを使っていれば比較的すぐぶち当たりそうな内容にも関わらず、あまりまとまった情報が見当たらずハマってしまったので、少しまとめてみました。

簡単に結論

AthenaでOpenCSVSerDeを使うと、テーブルのカラム型として日付や日時などを指定しても、その型にはならないようです。 LazySimpleSerDeを使えば想定したような動きとなります。 OpenCSVSerDeは主に各カラムのデータが囲み文字で囲まれている際に使用するものですが、単純に囲み文字があるからという理由で採用してしまうと、思った通りの型としてAthenaが動作してくれないという事態になってしまうので、気を付ける必要がありました。

検証

LazySimpleSerDeとOpenCSVSerDeを使って簡単なデータを読む検証を行いました。

検証環境

• リージョン: 全てのAWSリソースはus-east-1

LazySimpleSerDeを使う

まずはLazySimpleSerDeを使ってCSVをAthenaで見てみます。ちなみに、下記のCREATE TABLE文のように、特にSerDeを指定しないような場合もLazySimpleSerDeが使われます。

Athenaでテーブルを作るクエリ

CREATE TABLE catalog_lazy_simple_serde(
  sutoringu string,
  seisu int,
  hiduke date,
  jikoku timestamp,
  seisu_small smallint,
  seisu_big bigint
)
ROW FORMAT DELIMITED
  FIELDS TERMINATED BY ','
LOCATION 's3://bucket-name/catalog/simple/'
TBLPROPERTIES (
  'has_encrypted_data'='false',
  'skip.header.line.count'='1'
);

テストファイル

sutoringu,seisu,hiduke,jikoku,seisu_small,seisubig
aaa,123,2022-06-23,2020-05-08 17:56:00.000,123,123

早速作ったテーブルを見てみます。各カラムが想定通りの型になっているかを確かめたいので、簡単な演算をいれています。

Athenaでテーブルを作るクエリ

select
  sutoringu || 'xyz',
  seisu + 100,
  hiduke + interval '2' month,
  jikoku + interval '2' month,
  seisu_small + 100,
  seisu_big + 100
from catalog_lazy_simple_serde;

seisuなどはきちんと数値として足し算され、日付と日時も想定通りの動きとなっていることが確認できました。

OpenCSVSerDeを使う

では本題、OpenCSVSerDeを使ってみます。

テーブルを作成するクエリとしては以下のようになります。 SerDeの指定周りが少し変わっているだけで、カラムのデータ型は先ほどと同様です。

Athenaでテーブルを作るクエリ

CREATE EXTERNAL TABLE `catalog_open_csv_serde`(
  `sutoringu` string,
  `seisu` int,
  `hiduke` date,
  `jikoku` datetime,
  `seisu_small` smallint,
  `seisubig` bigint)
ROW FORMAT SERDE
  'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
  'escapeChar'='\\\\',
  'quoteChar'='\\"',
  'separatorChar'=',')
LOCATION
  's3://bucket-name/catalog/quoted/'
TBLPROPERTIES (
  'classification'='csv',
  'skip.header.line.count'='1');

OpenCSVSerDeを使うのでデータの方も、各値を囲み文字で囲ったものに変更します。

テストファイル

"sutoringu","seisu","hiduke","jikoku","seisu_small","seisubig"
"aaa","123","2022-06-23","2020-05-08 17:56:00.000","123","123"

先ほどと同じように簡単な演算を含むクエリでデータを取得してみます。

確認クエリ

select
  sutoringu || 'xyz',
  seisu + 100,
  hiduke + interval '2' month,
  jikoku + interval '2' month,
  seisu_small + 100,
  seisu_big + 100
from catalog_open_csv_serde;

エラーが出ました。

Error parsing field value "2022-06-23" for field 2

とのことなので、「日付型としてパースできないよ！」と言っているようです。 2022-06-23なのでパースできるだろ！と言いたいところですが、それは人間の都合なのでダメなものはダメってことみたいです。。。

OpenCSVSerDeで型をきちんと扱いたい

日付や日時を整数値で与える

ドキュメントによると、日付や日時を整数値として指定した場合はうまく読めるようなので、その方法でできるか試してみます。 テーブル定義は特に変える必要はありませんが、先ほどのテストとファイルを別の場所に置いたりした関係で新しくテーブルを作成するクエリを載せます。

Athenaでテーブルを作るクエリ

CREATE EXTERNAL TABLE `catalog_open_csv_serde_num`(
  `sutoringu` string,
  `seisu` int,
  `hiduke` date,
  `jikoku` datetime,
  `seisu_small` smallint,
  `seisubig` bigint)
ROW FORMAT SERDE
  'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
  'escapeChar'='\\\\',
  'quoteChar'='\\"',
  'separatorChar'=',')
LOCATION
  's3://bucket-name/catalog/quoted_num/'
TBLPROPERTIES (
  'classification'='csv',
  'skip.header.line.count'='1');

読み込むファイルの方も日付と日時に整数値を指定しています。

• hiduke: 123
• jikoku: 123

テストファイル

"sutoringu","seisu","hiduke","jikoku","seisu_small","seisubig"
"aaa","123","123","123","123","123"

同じクエリで中身を確認してみます。

確認クエリ

select
  sutoringu || 'xyz',
  seisu + 100,
  hiduke + interval '2' month,
  jikoku + interval '2' month,
  seisu_small + 100,
  seisu_big + 100
from catalog_open_csv_serde_num;

きちんと結果が出力されました！ 2ヶ月ずらしているので分かりにくいですが、dateとtimestampの中身の数値は以下のような計算で算出されているようです。

• date
  • 1970/01/01からN日後の日付となる
  • 0 ⇒ 1970/01/01
  • 1 ⇒ 1970/01/02
• datetime
  • 1970/01/01 00:00:00.000からNミリ秒後の日時となる
  • 0 ⇒ 1970/01/01 00:00:00.000
  • 1 ⇒ 1970/01/01 00:00:00.001
  • 1000 ⇒ 1970/01/01 00:00:01.000
  • 60000 ⇒ 1970/01/01 00:01:00.000

datetimeについてはミリ秒単位なので、このブログを書いてる2022年とかの数値にするためにはかなり大きい値を指定することになります。

おまけとして、日時の文字列から指定すべき整数値を出力するPythonスクリプトを置いておきます。

日時の文字列から整数値を得るスクリプト

from datetime import datetime
dt = datetime.fromisoformat('2019-08-29 19:11:28.385')
print(str(dt.timestamp()).replace(".", ""))
# '1567073488385'

キャストしたViewを作成する

元データを整数値に直すというのはインパクトが大きいので、一旦stringで読んで後でキャストする方法です。 ひと手間かかってしまいますが、これが一番現実的な解になるかと思います。

キャストしたViewを作るクエリ

create view catalog_open_csv_serde_view as (
select
  sutoringu,
  cast(seisu as int) as seisu,
  cast(hiduke as date) as hiduke,
  cast(jikoku as timestamp) as jikoku,
  cast(seisu_small as smallint) as seisu_small,
  cast(seisu_big as bigint) as seisu_big
from catalog_lazy_simple_serde
);

Athena以外での挙動

Redshift Spectrum

今回の動作確認はAthenaで行っていますが、Athenaのテーブルの裏側の仕組みはGlueデータカタログなので、この挙動はAthena以外でも同じことになると予想できます。 具体的には、Redshift Spectrumを用いた外部テーブルにおいても、上記と同様の動きとなることを確認しています。

例外: Glueのcreate_dinamic_frame_from_catalogでの挙動

Glueジョブでは、Glueデータカタログを経由してS3にあるファイルをDynamicFrame（以降DF）に読み込むことができます。この場合もAthenaと同一のテーブルを使用することができるのですが、残念ながらこの際には上で検証したようなデータのキャストは行われないようです。 具体的に書くと以下のような挙動に見えました。

• date型で定義したとしてもDFではstring型として読まれる
  • 整数値で指定した場合でも日付としての解釈はなく、数値のstring型として読まれる
• timestamp型で定義したとしてもDFではstring型として読まれる
  • 整数値で指定した場合でも日付としての解釈はなく、数値のstring型として読まれる
• bigint型で定義したとしてもDFではstring型として読まれる
• smallint型で定義したとしてもDFではstring型として読まれる
• int型だけ（？）はなぜかDFでもint型として読まれる

なお、この挙動はOpenCSVSerDeに限らず、LazySimpleSerDeを使ったテーブルにおいても同様のようでした。 なので、Glueジョブでデータカタログからファイルを読む際には、自分でのキャストが必須と考える必要がありそうです。 （そうなってしまうとデータカタログから読む利点って何なんでしょうね？正直利点がないようにも思えます。）

まとめ

• AthenaでOpenCSVSerDeを使うと、テーブルのカラム型として日付や日時などを指定しても、その型にはならない
• LazySimpleSerDeを使えば想定したような動きとなるが、各カラムに囲み文字（"など）がある場合にはそのままでは使えない
• OpenCSVSerDeを使った場合でも、日付や日時を数値で渡せばうまくいくが、CSVデータの変換をする必要があるので、これからデータ設計をする場合にしか使えない
  • これからデータ設計するとしても、数値だと理解しづらいので（直感的に理解しやすいことがメリットである）CSVの格納データとしては採用しづらい
• テーブル定義では各カラムを文字列型で定義して、それをキャストするViewを用意するのが現実的だと考えられる

参考情報

• Amazon Athena のデータ型
• Athena で空の TIMESTAMP クエリ結果をトラブルシューティング
• CSV を処理するための OpenCSVSerDe
• Amazon Athena 新しくサポートされた OpenCSVSerDeを使ってみた