Kinesis FirehoseでS3に置かれた圧縮したjsonファイルを、それに対してクエリを投げる、というのを検証してたのですが、Hive素人なのでスキーマの作り方もクエリの投げ方も正直あんまり良くわかってませんでした。
そこで下記を参照しながらスキーマの作成とクエリ投入をやってみて、最終的にうまくいきました。
ずーっと日本語記事を読みながらやっていたのですが、これがめちゃくちゃわかりづらい！！！
※理解度には個人差があります
多分知っている人が見たら何となくわかるんでしょうが、恐らくこれを見るのは自分みたいにあまり良く知らないので参考にしながら実際にやってみている、という層だと思います。
最終的に上手く行ってから思ったのは、前提知識がないと読むのがしんどい、ということですね…。
ただもう少し書いといてくれるだけで十分なのに…。
原文も軽く見ましたが、そっちにも書いてないのでそもそも記述されてません。
調べてもまだ中々情報が出てこない上に、クエリ投入時にエラーが出た場合もエラーメッセージが淡白すぎてどこが問題でエラーになってるのかさっぱりわからなくて悪戦苦闘してました。
そんなわけで、今後同じところで困る人が一人でも減るように、自分用メモも兼ねてハマったところについて補足をしておきたいと思います。
概要
リンク先で書いてあることの流れは大まかに下記のとおりです。
- FirehoseでSESの送信イベントログをS3に保存する
送信イベントログはjson形式で、それをFirehoseでS３に保存しています。 - Athenaのテーブルを作成して、クエリを投げる
- ただテーブル作成して投げる場合
- 入れ子になっているjsonに対してテーブル作成してクエリを投げる場合
- 禁止文字を含んでいるものに対してテーブルを作成してクエリを投げる場合
わかりづらいですが、禁止文字を含む項目をマッピングする項目とクエリを投げる項目が分かれています。）
-
hive-json-schemaの紹介
jsonからテーブル作成のためのクエリを生成するツールっぽいのですが、紹介してるわりにちっとも使い方が書いてません…。
使い方の解説をどなたか…。
ハマったところ
入れ子になったjsonに対するテーブル作成について
ハマったところといいつつ、自分はこの辺は割とスムーズに行ったのですが、ちょっとわかりづらいかもしれないので念のため。
サンプルにもありますが、jsonの中にまたjsonとか配列とかが入っている、みたいなケースは多くあります。
そういった場合、内部にあるjsonに対してstruct型を使って、その下の項目について型を定義してやればOKです。
その中にさらにjsonがある場合はさらにその中にstruct型で定義をすればOK。
例にあるものだと、内部にmail{~}とjsonがあり、その中にさらにいくつかのjsonがあるので、それぞれに対してstruct型で定義をしています。
以下引用（全文は貼っていないので、元はリンク先を見てください。）
※一部バッククオート（`）で囲われている項目がありますが、予約語として使われている言葉をそのまま使用するとエラーになるそうです。
そのため、バッククオートで囲うことによってエスケープしてるようです。
mail struct<`timestamp`:string,
source:string,
sourceArn:string,
sendingAccountId:string,
messageId:string,
destination:string,
headersTruncated:boolean,
headers:array<struct<name:string,value:string>>,
commonHeaders:struct<`from`:array<string>,to:array<string>,messageId:string,subject:string>
>
禁止文字そのものについて
まず、禁止文字が色々あることを最初大して理解してませんでした。
項目名（↑の例だと、timestampとかsourceとかのところ）の定義に使用できない文字があります。
記事中だと「:」（コロン）が禁止文字列なので、それがクエリ中の該当箇所に入っているとエラーになります。
あとは「-」（ハイフン）なんかも禁止文字のようです。
例えばHTTPリクエストのログを見たとき、ヘッダとかはハイフンを使った項目がいくつもあったりするので困りますよね。
一応記事中の例では両方「_」（アンダースコア）に変換しています。（コロンにしか触れてませんが…。）
最初は禁止文字があると知らず、なぜエラーになっているかわからずにハマってました。
この辺どっかにまとまってるのかな…？
どうやって回避するかというと、それがWITH SERDEPROPERTIES の部分です。
禁止文字を含む場合のマッピングの仕方について
最初見た時はなんでこんなことをするのかわかりませんでしたが、上記の通り項目名を定義するときに禁止文字が入っているとエラーになります。
なので、WITH SERDEPROPERTIESの項目で、禁止文字列を含んだ項目名を、禁止文字列のない文字列にマッピングし、元のjsonのkeyでは禁止文字列を含んでいたものに対し、テーブル上ではカラム名として別の文字列をあてがうことができます。
記事中では、コロンやハイフンをアンダースコアに変換した文字列にマッピングしています。
式の左側がカラム名に使いたい文字列で、それに対して右側がデータの元の実際の名前です。
"mapping.カラム名に使いたい文字列"="実際の名前" みたいに記述してます。
WITH SERDEPROPERTIES (
"mapping.ses_configurationset"="ses:configuration-set",
"mapping.ses_source_ip"="ses:source-ip",
"mapping.ses_from_domain"="ses:from-domain",
"mapping.ses_caller_identity"="ses:caller-identity"
)
クエリの投げ方
これもまあおまけで書いておくと、ここまでしっかりと下の項目までテーブルを定義しておくと、下の項目までクエリで引っ張ることが出来ます。
記事中では下記のような例が出ています。
SELECT eventtype as Event,
mail.timestamp as Timestamp,
mail.tags.ses_source_ip as SourceIP,
mail.tags.ses_caller_identity as AuthenticatedBy,
mail.commonHeaders."from" as FromAddress,
mail.commonHeaders.to as ToAddress
FROM sesblog2
WHERE eventtype = 'Bounce'
mail{〜}の下の項目を参照する時は上記のようにドットをつけて該当項目の名前を指定しています。
さらにその下の項目を参照する時はその後ろにさらにドットをつけています。
この辺は直感的にわかりやすいかもしれません。
おまけとかtipsとか
Firehoseで配置されたフォルダ構成ではパーティションを自動で切ってもらえない
hiveではフォルダが/bucketname/path/to/log/year=YY/month=MM/day=dd/foo
みたいな構成だと自動でパーティション設定してくれるらしいのですが、FirehoseでS3にデータ配置すると/bucketname/path/to/log/YYYY/MM/DD/fooみたいになるので、自分でパーティションを作成する必要があります。
パーティションがない状態でクエリを投げても１件も引っかかりません。
これを作るには下記のようなクエリを投げる必要があります。
ALTER TABLE database_name.table_name
ADD PARTITION (year='2016',month='08',day='28')
location 's3://elb-access-log/AWSLogs/00000000000000/elasticloadbalancing/ap-northeast-1/2016/08/28/';
※参考
https://qiita.com/r4-keisuke/items/d3d339b76d4368b6b30a
上記の例だと１日ずつパーティションを設定する必要があるのですが、
パーティション数には上限があるらしい（１テーブル２００００まで）ので、１日ずつとか、１時間ずつとかフォルダ分けしている場合はちょっと注意が必要かもしれないです。
※パーティションの上限については下記
https://docs.aws.amazon.com/ja_jp/general/latest/gr/aws_service_limits.html#limits_glue
さすがに対象が多すぎとなるとしんどいので、シェルスクリプトとかで回すといいと思います。
ただ、シェルスクリプト自体も１つ１つの処理実行だとそこそこ時間かかるのと、パーティションを設定するためのクエリでクエリ履歴が埋め尽くされるのが難点です。
データ元にない項目を定義しても値がnullになるだけで問題はない
jsonの出力が一定じゃなくて、いくつかの似たような型のjsonが混ざっていたり、ものによって存在しない項目があったりしても、それらのキーを全て網羅するようにまとめて定義しちゃって問題ないみたいです。
定義したけどデータ元に項目がない場合はnullが入るだけのようで。
逆に元データにある項目を全部定義する必要はないので、元データにあっても使わないような項目はテーブル作成の段階で定義しないようにしてもいいみたいですね。
ざっと書いたので、わかりづらいとか、もっとこうすればみたいな指摘があればいただけると嬉しいです。