定期的に取得したCSVファイルをもとにAthenaのテーブルを作成したのでメモ。

CSVデータ形式を解析するためのライブラリ(SerDes)

AthenaではCSVデータ形式を解析するためのライブラリ(SerDes)が2つあります。
・LazySimpleSerDe：データに引用符で囲まれた値が含まれていない場合
・OpenCSVSerDe：データの値に引用符が含まれているか、別の区切り文字やエスケープ文字が含まれている場合

参考：サポートされる SerDes およびデータ形式

使い分けは上記の通り、CSVのデータに引用符(「"」や「'」)で囲まれた値があるかどうかです。
詳しくは上記のリンク先を参照してください。

使用例

想定するフォルダ構成

test-bucket
　└test-folder
　　└2018
　　　└10
　　　　└test20181001.csv
　　　　└test20181002.csv

想定するファイル内容

test_date,test_id,test_num
2018-10-01,1111,4
2018-10-01,2222,11
2018-10-01,3333,9

以下、実際にテーブルを作成するCREATE TABLE文の例を記載します。(前提：test_dbが作成済み。まだの場合はおまけ参照)

LazySimpleSerDeを使用した場合。

LazySimpleSerDe

CREATE EXTERNAL TABLE IF NOT EXISTS test_db.test_table (
  `test_date` date,
  `test_id` string,
  `test_num` int
)
PARTITIONED BY (
  year string,
  month string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
  'serialization.format' = ',',
  'field.delim' = ','
)
LOCATION 's3://test-bucket/test-folder/'
TBLPROPERTIES (
  'has_encrypted_data'='false',
  'skip.header.line.count'='1'
)
;

OpenCSVSerdeを使用した場合(カラムのタイプはstringにする)

OpenCSVSerde

CREATE EXTERNAL TABLE IF NOT EXISTS test_db.test_table2 (
  `test_date` string,
  `test_id` string,
  `test_num` string
)
PARTITIONED BY (
  year string,
  month string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
   'separatorChar' = ',',
   'quoteChar' = '\"',
   'escapeChar' = '\\'
   )
LOCATION 's3://test-bucket/test-folder/'
TBLPROPERTIES (
  'has_encrypted_data'='false',
  'skip.header.line.count'='1'
)
;

今回はヘッダーが各CSVに含まれる想定なので、CREATE TABLE文には以下を指定しています。

ヘッダー部分をスキップさせる

'skip.header.line.count'='1'

パーティションを使用しているため、以下のようにパーティション追加が必要です。

PARTITION追加

ALTER TABLE log_analysis.test ADD IF NOT EXISTS PARTITION (
  year='2018',
  month='10'
)
LOCATION 's3://test-bucket/test-folder/2018/10/'
;

おまけ

データベースの作成

クエリで作成

以下を実行すると作成できます。

CREATE DATABASE test_db;

コンソールで作成

AWS Glueで作成する。Athenaコンソール上の以下の赤枠のリンクから遷移。

赤枠「データベースの追加」を押して、データベースの追加ダイアログを表示。

データベース名を入力し作成を押すと作成される。

PARTITION

Athenaではクエリによって読み込まれたデータ容量で課金されます。
パーティションを設定することで、パーティションで読み込むフォルダを制限することができます。
以下は、よく使うと思われるパーティション関連のクエリを記載します。

パーティション一覧表示

SHOW PARTITIONS test_db.test_table;

パーティション削除

ALTER TABLE test_db.test_table DROP PARTITION (year='2018', month='10')

RegexSerDe

今回は対象データがCSVファイルだったため、LazySimpleSerDe、OpenCSVSerDeを使用しました。
RegexSerDeを使用すると正規表現でカラムにできます。
詳細は公式のブログで使用しているので、そちらを参照してみてください。(ブログですがチュートリアルっぽい)
Analyzing Data in S3 using Amazon Athena
S3のデータをAmazon Athenaを使って分析する ※日本語

おわり

Athenaを使うとS3に保存したCSVファイルやログファイルに対して簡単にクエリを投げられて便利ですね。
ただし読み込まれるデータによって課金されため、闇雲に使用するとAthena破産につながるので注意は必要みたいです。

ひとまず、今回は以上です。

参考

サポートされる SerDes およびデータ形式　※上で記載したリンクと同じ
DDL ステートメント
 CREATE DATABASE
CREATE TABLE
Analyzing Data in S3 using Amazon Athena　　※上で記載したリンクと同じ
S3のデータをAmazon Athenaを使って分析する　※上で記載したリンクと同じ

S3バケットのCSVファイルにクエリ発行するためにAthenaのテーブルを作成する