読者です 読者をやめる 読者になる 読者になる

Amazon Athena で「郵便番号データ(CSV 形式)」を検索する

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

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

aws.amazon.com

サンプルテーブル 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

今後 Athena を活用したいところ & まとめ

現在 AWS のサービスログで,直接 S3 からログをダウンロードして分析したり,Elasticsearch に突っ込んで分析したりしている場合がある.

  • ELB アクセスログ
  • CloudFront アクセスログ
  • VPC フローログ
  • CloudTrail ログ

アドホックに実行するための準備が面倒だなと感じていたため,今後は Athena を使おうと思う.ただし,パーティショニング設定は理解して使う必要があるから別途仕様を整理する.ただまぁ,1TB / $5 だからそう簡単に課金されないと思う.

またアドホックに実行するとしても,頻繁に使うにしては GUI が厳しいかもと思ってたら,先週のアップデートで QuickSight と連携したみたいだし,Redash 連携もできそうだし,Athena をバックエンドとして使う形が一般的になる気がする.

今まで AWS のサービスログに対してアドホックに分析する環境を作って無く,運用上のボトルネックに感じていたため,Athena 最高!という感じ.積極的に使うぞ!

年賀状を送るときに(笑)

タイミング良く年賀状の時期!もし年賀状を送るときに郵便番号がわからなかったら Athena で検索すれば調べられる!最高に便利だ(笑)

(素直に公式サービスを使おう!)

www.post.japanpost.jp

関連記事

elb_logs テーブルで Athena を試したり,独自データを検索するなど,基本的な操作を学ぶときに参考になった.

dev.classmethod.jp

Athena リリース直後にすぐ出た BigQuery との比較記事で「おー,Athena 凄そう」と感じることができた記事だった.

data.gunosy.io

導入しようと考えている CloudFront アクセスログの検索だった.スキーマを用意してもらっててすぐに試せそう.

blog.manabusakai.com

ログ構造的に CloudTrail ログの検索は難しそうだった.

dev.classmethod.jp

Athena のベースになっている Presto も勉強して,Presto の特性を理解しておくと良さそうだなと感じた.

repeatedly.github.io