AthenaでS3に置いてあるcsvファイルを取得する際に気をつけること

サンプルクエリ

CREATE EXTERNAL TABLE IF NOT EXISTS test.test (
  `test1` string,
  `test2` string,
  `test3` string,
  `test4` string,
  `test5` string,
  `test6` string 
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
   'separatorChar' = ',',
   'quoteChar' = '\"',
   'escapeChar' = '\\'
   )
STORED AS TEXTFILE
LOCATION 's3://test/test/'
TBLPROPERTIES (
'skip.header.line.count'='1', 
'has_encrypted_data'='false'
)

ちょこっとだけ解説

ROW FORMAT SERDE

SerDeとは、あらゆるデータを入出力できる形式に変換するためのインタフェースです。
csvを扱う際に選択するSerDeは主に２つで、以下になります。

/*データの値に引用符が含まれているか、別の区切り文字やエスケープ文字が含まれている場合*/
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'

/*データに引用符(")で囲まれた値が含まれていない場合*/
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'

csv内のデータに"test"など、引用符で括られたデータがあった場合、
LazySimpleSerDeだと、"test"とそのまま取り込んでしまいます。
OpenCSVSerDeというSerDeを指定すると、引用符で囲まれた文字列を取り出すことできます。
この際、細かい「区切り文字」「引用符」「エスケープ文字」などの設定はWITH SERDEPROPERTIESで、指定することになります。

※ちなみに、どのSerDeも指定しなければ、LazySimpleSerDeが自動的に適用されます。

公式ドキュメント
https://docs.aws.amazon.com/ja_jp/athena/latest/ug/supported-format.html

あと、このあたりに関してはクラスメソッドさんの記事が参考になります。
https://dev.classmethod.jp/cloud/aws/amazon-athena-using-opencsvserde/

STORED AS file_format

テーブルデータのファイル形式を指定するのが、STORED AS file_formatです。
csvファイルはTEXTFILEで指定します。

STORED AS TEXTFILE

※省略すると、デフォルトの TEXTFILE が使用されます。
他のオプションは以下のとおりです。

file_format
align-left
SEQUENCEFILE
TEXTFILE
RCFILE
ORC
PARQUET
AVRO
INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname

LOCATION

テーブルを作成する基のデータがある Amazon S3 の場所を指定します

LOCATION 's3://test/test/'

TBLPROPERTIES

TBLPROPERTIES (
'skip.header.line.count'='1', 
'has_encrypted_data'='false'
)

TBLPROPERTIESでは、'skip.header.line.count'='1', を指定しています。
これは、データのファイルを読み込まない行数を選択するプロパティになります。
今回は１を指定しているので、1行目が読み飛ばされ、２行目以降がデータとして認識されます。

has_encrypted_dataは、trueに設定すると、LOCATION で指定する基になるデータを暗号化することを指定します。省略すると、false が使用されます。