AWS re:Invent 2016 で発表されてからもう1ヶ月も過ぎてしまったけど，今さらながら Amazon Athena を実際に試してみた．検証記事は既に多く出てて，積極的に読んでいたけど，実際に動かしてみようと思った．

現在まだ東京リージョンには対応して無く，今回はバージニアリージョンを使った．ただし，S3 をバックエンドにする場合，リージョンが異なっていてもアクセスできるため，問題なく使えた．

サンプルテーブル `elb_logs`

Athena のチュートリアル用に ELB アクセスログのサンプルデータが入った elb_logs が用意されていて，すぐに Athena を試すことができる．最初に試して雰囲気を感じることができた．既に多くの人が試しているし，今回は割愛する．さっそく独自データを用意して Athena を試してみた！

検証 : 郵便番号データを検索する

1. データセット

今回 Athena を試すときに以下の条件に合致するデータセットを探していた．Athena は大量データを対象にしてもパーティショニング設定をすることによってスキャン範囲を限定して軽量に動くけど，今回は試行錯誤することも考えて，フルスキャン可能な小さなデータ量にしたいと考えた．

CSV 形式でダウンロードできること
複数ファイルになっていること
フルスキャンしても Athena 破産しないデータ量に収まっていること

結果的に日本郵政が公式に提供している「郵便番号データ（読み仮名データの促音・拗音を小書きで表記するもの）」が，全ての条件をクリアしていた．

CSV を ZIP にしている
都道府県別データと全国一括データを選択できる
全国一括データの CSV も 12MB 程度

www.post.japanpost.jp

2. データ変換 & データアップロード

都道府県別データ47個をダウンロードした後に以下の変換をする．

# ZIP → CSV に展開する（データ変換をするため & Athena は ZIP をサポートしていないため）
$ find . -name "*.zip" | xargs -n 1 unzip
# ZIP を削除する
$ rm *.zip
# SJIS → UTF-8 に文字コードを変換する
$ find . -name "*.CSV" | xargs -n 1 nkf -w --overwrite
# " を除去する（Mac で実行するため sed は BSD 互換になっている）
$ find . -name "*.CSV" | xargs -n 1 sed -i '' 's/\"//g'
# 半角スペースを除去する（Mac で実行するため sed は BSD 互換になっている）
$ find . -name "*.CSV" | xargs -n 1 sed -i '' 's/ //g'
# CSV → GZ に圧縮する
$ find . -name "*.CSV" | xargs -n 1 gzip

最終的に .CSV.gz ファイルが47個用意できた．

$ ls -1 *.CSV.gz | wc -l
      47

S3 にアップロードする．今回はバケット直下に zipcodes ディレクトリを用意して，そこに置いた．

f:id:kakku22:20161230084932p:plain

3. テーブル作成

S3 にデータを準備したら，次は Athena 側にテーブルを作成する．

データベースは sampledb を使う
テーブル名は zipcodes とする
配置した S3 パスを指定する（末尾の / を忘れずに！）
- s3://xxx/zipcodes/

f:id:kakku22:20161230083735p:plain

次にデータフォーマットとして CSV を選択する．JSON も選べるんだ！

f:id:kakku22:20161230085223p:plain

次にカラム定義を設定する．CSV を1行 READ して必要なカラム数を最初から表示してくれる...的なアシストは無くて，地道に Add a column を押した．全てのカラムを定義する必要はなく，今回は9カラムを定義することにした（日本郵政の README に書かれている通り，実際には計15カラム存在する）．

www.post.japanpost.jp

No.	データ概要	データフォーマット	Athena カラム名	Athena データタイプ
1	全国地方公共団体コード	半角数字	gov_code	int
2	（旧）郵便番号（5桁）	半角数字	old_zipcode	int
3	郵便番号（7桁）	半角数字	zipcode	int
4	都道府県名	半角カタカナ	prefecture_kana	string
5	市区町村名	半角カタカナ	city_kan	string
6	町域名	半角カタカナ	address_kana	string
7	都道府県名	漢字	prefecture_char	string
8	市区町村名	漢字	city_char	string
9	町域名	漢字	address_char	string

f:id:kakku22:20161230085420p:plain

パーティショニング設定はスキップした．アクセスログなど日付別にディレクトリが分割されているデータセットを扱う場合には必須の設定になりそう．

最終的に以下のクエリが自動的に生成されて，テーブルが作成できた．

CREATE EXTERNAL TABLE IF NOT EXISTS sampledb.zipcodes (
  `gov_code` int,
  `old_zipcode` int,
  `zipcode` int,
  `prefecture_kana` string,
  `city_kana` string,
  `address_kana` string,
  `prefecture_char` string,
  `city_char` string,
  `address_char` string 
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
  'serialization.format' = ',',
  'field.delim' = ','
) LOCATION 's3://xxx/zipcodes/';

4. クエリ実行

SQL ライクに実行することができた．試しにオフィス（渋谷マークシティ）の郵便番号を検索してみた．

f:id:kakku22:20161230091817p:plain

GROUP BY も書ける．

f:id:kakku22:20161230100103p:plain

Athena 便利だ！

ハマった点

郵便番号検索を動かすまでに数回ハマった点がある．全て「2. データ変換」に反映してあるけど，簡単に残しておく．結論として Athena を使う場合，データセットの事前準備が重要になる．

1. SJIS

SJIS と知らずに Athena でクエリを投げたら完全に文字化けをしてしまった．nkf で変換して対応した．

f:id:kakku22:20161230110422p:plain

2. ダブルクオート

郵便番号データは以下のように文字列を " で囲っていた．Athena では除去されずに取り込まれてしまうため，事前に sed で除去した．ちなみに int で定義されているのに " で囲まれているカラムもあって謎だった．

01101,"064  ","0640941","ﾎｯｶｲﾄﾞｳ","ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ","ｱｻﾋｶﾞｵｶ","北海道","札幌市中央区","旭ケ丘",0,0,1,0,0,0

f:id:kakku22:20161230110535p:plain

3. 半角スペース

2カラム目にある「旧郵便番号」は「3桁」と「5桁」の場合があった．「3桁」の場合に半角スペースが2個付いていて，int なのに文字数を合わせているデータ構造になっていた．Athena で取り込むと int ではないため，値が全て飛んでしまって，ブランクになってしまった．よって，同じく sed でブランクを除去した．

01101,"064  ","0640941","ﾎｯｶｲﾄﾞｳ","ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ","ｱｻﾋｶﾞｵｶ","北海道","札幌市中央区","旭ケ丘",0,0,1,0,0,0
01106,"06122","0612261","ﾎｯｶｲﾄﾞｳ","ｻｯﾎﾟﾛｼﾐﾅﾐｸ","ﾐｽﾏｲ1ｼﾞｮｳ","北海道","札幌市南区","簾舞一条",0,0,1,0,0,0

f:id:kakku22:20161230111008p:plain