UUUMエンジニアブログ

UUUMのエンジニアによる技術ブログです

Athena + Glue + (Terraform)でいい感じにファイル上のデータを集計しよう

システムユニットのt_u_a_kです。ブログ登場は初めてです。私は業務で少々大きめのデータの集計ということをやっていますが、その際にはAWSのAthenaとGlueを試しました。手軽でよかったので紹介します。

AthenaとGlueについて

まずAthenaについてですが、これはS3上のデータに対するクエリサービスです。データベースに対するクエリサービスではなく、S3上のテキストファイル(もしくはそれらを圧縮したりしたもの)に対してデータ構造を定義し、いわゆるSQLを使って普通にクエリが書けます。この時点でAthenaのようなサービスや仕組みに触れたことがない人にとっては「は？」って感じですね。AthenaはPrestoというFacebookが開発したクエリエンジンを使っていて、大きなデータでも爆速で結果が返ってきます。Presto公式ではFacebook自身が300PB以上のデータの分析に利用していると書いています。ここまで来るとでかすぎて想像もつきませんが、通常の利用であれば本当に驚くほどのスピードでクエリの結果が返ってきます。

aws.amazon.com

次にGlueです。GlueはいわゆるETLサービスというやつです。最近だとデータウェアハウスを構築するところも多いと思いますが、いろんなところにいろんな形で存在しているデータを変換したり移動させたりいい感じに集めるときに使うサービスというのが非常に雑な説明です。Glueにはデータカタログという機能があり、これがAthenaと一緒に使える機能です。Athenaはクエリサービスと書きましたが、Glueで定義したデータカタログをいわゆるデータベースやテーブルと見てクエリをすることができます。これではわかりにくいと思うので下で説明していきます。

aws.amazon.com

今回使うデータ

例えばこんなデータがあったとします。

# hikakin.csv
video_id,title,channel_id,views,date
zW4HJkuFFtc,【ランキング】ヒカキンがガチでウマいと思うセブンの商品トップ３！【2019年5月編】,UCZf__ehlCEBPop-_sldpBUQ,1826863,20190518
VY0EXQqSeRc,顔面が大変なことになりました…,UCZf__ehlCEBPop-_sldpBUQ,1650048,20190517
MgeD8MB6fyg,金属アレルギー検査したらまさかの結果が…【1900万円の時計のその後】,UCZf__ehlCEBPop-_sldpBUQ,1906878,20190515

皆様ご存知のヒカキンさんのチャンネルHikakinTVの動画について、2019年5月のある時点でのデータです。1行目が動画IDや動画タイトルといったヘッダー、2行目以降がデータになっています。他にもこのような似たデータを用意しました。0214mexがはじめしゃちょーさんでtsuriyokaが釣りよかでしょうさんの動画に関するデータです。釣りよかでしょうさんに関してはサブチャンネルのデータも混ざっています。これらがそれぞれhikakin.csv、hajime.csv、tsuriyoka.csvという名前で存在している、もっというとUUUM所属の各チャンネルごとにこのようなデータがあると仮定して、Athenaでクエリをかける準備をしていきます。 (※0214mexなどを見ても何のこと?と思う方もいらっしゃるかもしれませんが、はじめしゃちょーさんのYouTubeチャンネルについてはhttps://www.youtube.com/user/0214mexというURLでもアクセスできるようになっており、他のチャンネルでもこれに似た形でURLが表記されることがあります。)

# 0214mex.csv
video_id,title,channel_id,views,date
dmpYE9u3cr4,"【50,000枚】3年間集めた遊戯王カード全部売ったら金持ちになったwwwww",UCgMPP6RRjktV7krOfyUewqw,1280160,20190520
xUFNcNQ24Ho,家に隠されたドラゴンボールがガチで見つからない件。,UCgMPP6RRjktV7krOfyUewqw,1055697,20190519
STA6C9ftvmw,コンビニ弁当をミキサーでドロドロにしても味が分かる説,UCgMPP6RRjktV7krOfyUewqw,969160,20190517
# tsuriyoka.csv
video_id,title,channel_id,views,date
d0XrWwauyQY,むねお船は初心者に優しいらしい,UC4QadOSsJu54Qs8z99shRiQ,136100,20190521
dwXSckt1lM4,庭でイカを料理してたら変なの来た,UC4QadOSsJu54Qs8z99shRiQ,215405,20190520
1AuvYVUMNGs,リフォームした家の壁に缶スプレーでらくがきしてみた！,UCD7-Ocp4InwPKzwiq_U-Abg,230155,20190519
ew0HdXG3SKQ,ドラム缶でオリジナルBBQコンロを作る！,UCD7-Ocp4InwPKzwiq_U-Abg,168260,20190521
j5aQ_Y3_I_U,新居キッチンの紹介します！,UCRT-74ovdMKOFBC96w_gfyQ,105864,20190521
YC1OnPK2fWE,ミルフィーユカツで新潟のB級グルメ作ってみた！,UCRT-74ovdMKOFBC96w_gfyQ,73117,20190520

S3へのデータの保存

AthenaはS3上のデータに対するクエリサービスなので、まずはデータをS3に置かなければいけません。今回は

  • 2019年5月のデータ
  • チャンネルごとに別ファイル

となっているので、仮にデータが増えるとすれば別の月のデータだったり、違うチャンネルでファイルが作られるということが考えられます。このような場合にはS3に保存する際に

  • s3://my-bucket/year=2019/month=5/channel=hikakintv/hikakin.csv
  • s3://my-bucket/year=2019/month=5/channel=0214mex/hajime.csv
  • s3://my-bucket/year=2019/month=5/channel=tsuriyoka/tsuriyoka.csv

というふうに/key=value/のパーティションを作ってあげることで、Athenaでクエリをかける際にまるでyearやchannelといったカラムがあるようにクエリをすることができます。channelのカラムが1チャンネル1つに対応していないじゃないかとかchannelキーに対するvalueのルールがおかしいんじゃないかといった意見もあるかと思いますが、今回tsuriyoka.csvに複数チャンネルのデータが入ってしまっているため、ファイルの中を見る前はパーティションを厳密にチャンネル単位で分割することができません。パーティションについては細かくすればするほどクエリの条件を細かく設定できるので料金面で有利になる一方、ファイルをS3に設置するコストが高くなってしまうなどもあるので、どの程度の粒度で分析や集計を行いたいかによってパーティション設計は考えていくべきだと思います。自動的にS3にファイルが流れてくるような状況から作るのであれば、パーティション設計はますます重要です。

docs.aws.amazon.com

データ構造を定義する

今回のデータを見たときに、どういうクエリがかけるでしょうか。(本来はどういうことを知りたいかが先に来るべきですが今回はちょっと置いておきます。) 例えば、

select sum(views) as total_views
from s3上のデータ
where year = 2019 and month = 5 
and channel in ('hikakintv`, 'mex0214`)

とか

select videoid, views
from s3上のデータ
where year = 2019 and month = 5 
order by views desc
limit 5

などがあるでしょうか。別の年月やチャンネルが増えればもっといろんなパターンが出てくるとは思いますが、共通しているのはs3上のデータをテーブルとして、CSVの各カラムやパーティションをselect文に入れたいということです。これをやるためのデータ構造をGlueで定義してみましょう。

Glueでデータカタログを定義する

AthenaでクエリをするためにGlueでデータカタログを定義しますが、データカタログを定義するとはデータベースとテーブル(およびその中のカラム)を定義することです。Glueにおけるデータベースとはいろんな場所にあるデータを統一的に扱うためのメタデータストアになりますが、データベースが必ずどこかのバケットと紐づくなどは無いので、実際あまり意識する必要はありません。必要に応じて作っておけばいいものになります。今回作成したデータベースについても名前以外は何も決めていない状態です。

f:id:xxuxa_k:20190928063135p:plain

次はテーブルです。今回のようにAthenaでのクエリを目的としたテーブル作成においては、テーブルとバケットが1:1だと思っておくのがよいと思います。テーブルに指定する内容は大きくわけてスキーマとそれ以外のオプションになります。オプションの項目としてはどのようにデータを解釈させるかを決めるHadoopのフォーマットなどがあります。かなり細かく指定できるようですが、私もすべては把握できていません。いろんな形式のデータを使ってみたりすることでだんだんわかるようになってくるのだと思います。今回作ってみたblog_test_tableは以下のようなデータカタログ設定になっています。

f:id:xxuxa_k:20190929052618p:plain

f:id:xxuxa_k:20190928063532p:plain

Terraformの利用

↑の画像に出したデータカタログの内容をAWSコンソール上から設定することは可能です。しかし、設定の初期などはパーティションを変えることがあったり、なぜかAthena側でデータカタログのロードにうまくいかなかったりと、最初から作り直したほうがいろいろと楽な場合が多いです。(個人的にはHadoopだったり分散処理に関する知識が足りないと十分にエラー調査していくことが難しいのかなと思っています。) 作り直すたびにAWSコンソール上でポチポチやっていたこともあったのですがつらすぎるので、データカタログはTerraform化してしまいましょう。テーブルの追加も作り直しもterraform apply一撃で済むようになることでかなり作業が楽になるのと、クエリを書いて調査・分析をするという本質的な作業に時間を割くことができるようになります。もちろんTerraformの代わりにCloud Formationもありだと思います。 データカタログ部分のterraformコードはこのような感じです。もともとはv0.11系で動くように書いていたのですが、今回ブログを書くにあたってv0.12.9で動作するように書き換えました。(terraform 0.12updateコマンドがあるので基本的には自動で書き換えてくれましたが、一部自分で直すような感じになりました。) ポイントとしてはstorage_descriptor.ser_de_infoかなと思います。S3に保存してあるファイルの形式によってここが変わってきます。今回はCSVを使っていますが、TSV、JSON、Hadoop形式などにも対応していて、形式に合わせてSerDeというシリアライズ/デシリアライズライブラリにオプションを与えることになります。

locals {
  blog_database_name   = "test_catalog_db"
  blog_bucket_location = "s3://xxx/yyyy/..../"
}

resource "aws_glue_catalog_database" "test_catalog_db" {
  name         = local.blog_database_name
  description  = ""
  location_uri = ""
  parameters   = {}
}

resource "aws_glue_catalog_table" "blog_test_table" {
  name          = "blog_test_table"
  description   = ""
  database_name = local.blog_database_name

  table_type = "EXTERNAL_TABLE"

  parameters = {
    EXTERNAL                 = "TRUE"
    has_encrypted_data       = "false"
    classification           = "csv"
    "skip.header.line.count" = "1"
  }

  partition_keys {
    name    = "year"
    type    = "int"
    comment = ""
  }
  partition_keys {
    name    = "month"
    type    = "int"
    comment = ""
  }
  partition_keys {
    name    = "channel"
    type    = "string"
    comment = ""
  }

  storage_descriptor {
    input_format  = "org.apache.hadoop.mapred.TextInputFormat"
    location      = local.blog_bucket_location
    output_format = "org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat"

    ser_de_info {
      name                  = "blog_test_ser_de"
      serialization_library = "org.apache.hadoop.hive.serde2.OpenCSVSerde"

      parameters = {
        quoteChar     = "\""
        separatorChar = ","
      }
    }

    columns {
      name    = "video_id"
      type    = "string"
      comment = ""
    }
    columns {
      name    = "title"
      type    = "string"
      comment = ""
    }
    columns {
      name    = "channel_id"
      type    = "string"
      comment = ""
    }
    columns {
      name    = "views"
      type    = "int"
      comment = ""
    }
    columns {
      name    = "date"
      type    = "string"
      comment = ""
    }
  }
}

データカタログ部分についてはこれだけです。CSVの形式の変更などがあったときもコード変更とterraformの適用だけです。S3バケットもterraform化できていればより完璧です。 docs.aws.amazon.com

Athenaでクエリする

あとはAthenaでクエリをするだけです。AWS CLIからだと結構柔軟に設定できますが、今回はAthenaのコンソール上からやります。忘れてはいけないことですが、S3にデータを追加したら、MSCK REPAIR TABLE test_catalog_db.blog_test_table;というSQLを流しておきましょう。これをやらないとAthenaが新たに追加されたファイルを認識できなくなります。

f:id:xxuxa_k:20190928065421p:plain

select * from blog_test_tableをしてみると、3.22秒と出ました。仮にこれがRDSであればどう考えても遅すぎますが、Athenaはあまりに小さいデータに対してはRDBMSよりもパフォーマンスが悪くなります。逆に大きいデータに対してはとんでもないスピードで結果を返してきます。今回についてはクエリを投げられるようになることが目的なのでここまでにしておきますが、実際にやったことはバケットとデータカタログの設定だけなのでかなり簡単にCSVのようなデータに対してもクエリが投げられることがわかったと思います。もちろん形式はCSVだけでなく、多様な形式をサポートしています。

f:id:xxuxa_k:20190928080139p:plain

料金について

Athenaはスキャンしたデータに対する従量課金のため、スキャンデータを減らすことが利用料金を減らす一番の近道です。同時にスキャンデータを減らすことは処理速度を上げることにもつながります。 データは圧縮形式でも扱えるため圧縮してS3に入れる、Parquetなどの列形式へ変換するなどの方法も役に立つと思います。

まとめ

AthenaとGlueのデータ集計での利用について書いてみました。今回ほど小さいファイルであればSQLiteを使うのもありだと思いますが、実際にはそこそこのサイズがあると思うので、ファイル上のデータについて単純なクエリをしたいということだとこれが一番簡単な気がします。AWSマネージドなので管理コストも最小限です。

最後に

やっと1回ブログ書けた、、、。システムユニットでは一緒に働いてくれる方を募集中です。

recruit.uuum.co.jp