Athenaで入れ子のjsonにクエリを投げる方法が分かりづらかったので整理する

Kinesis FirehoseでS3に置かれた圧縮したjsonファイルを、それに対してクエリを投げる、というのを検証してたのですが、Hive素人なのでスキーマの作り方もクエリの投げ方も正直あんまり良くわかってませんでした。

そこで下記を参照しながらスキーマの作成とクエリ投入をやってみて、最終的にうまくいきました。

日本語記事
https://aws.amazon.com/jp/blogs/news/create-tables-in-amazon-athena-from-nested-json-and-mappings-using-jsonserde/

元記事
https://aws.amazon.com/jp/blogs/big-data/create-tables-in-amazon-athena-from-nested-json-and-mappings-using-jsonserde/

ずーっと日本語記事を読みながらやっていたのですが、これがめちゃくちゃわかりづらい!!!
※理解度には個人差があります

多分知っている人が見たら何となくわかるんでしょうが、恐らくこれを見るのは自分みたいにあまり良く知らないので参考にしながら実際にやってみている、という層だと思います。
最終的に上手く行ってから思ったのは、前提知識がないと読むのがしんどい、ということですね…。
ただもう少し書いといてくれるだけで十分なのに…。
原文も軽く見ましたが、そっちにも書いてないのでそもそも記述されてません。

調べてもまだ中々情報が出てこない上に、クエリ投入時にエラーが出た場合もエラーメッセージが淡白すぎてどこが問題でエラーになってるのかさっぱりわからなくて悪戦苦闘してました。

そんなわけで、今後同じところで困る人が一人でも減るように、自分用メモも兼ねてハマったところについて補足をしておきたいと思います。

概要

リンク先で書いてあることの流れは大まかに下記のとおりです。

  1. FirehoseでSESの送信イベントログをS3に保存する
    送信イベントログはjson形式で、それをFirehoseでS3に保存しています。
  2. Athenaのテーブルを作成して、クエリを投げる
    • ただテーブル作成して投げる場合
    • 入れ子になっているjsonに対してテーブル作成してクエリを投げる場合
    • 禁止文字を含んでいるものに対してテーブルを作成してクエリを投げる場合
      わかりづらいですが、禁止文字を含む項目をマッピングする項目とクエリを投げる項目が分かれています。)
  3. 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みたいになるので、自分でパーティションを作成する必要があります。
パーティションがない状態でクエリを投げても1件も引っかかりません。
これを作るには下記のようなクエリを投げる必要があります。

elbログを対象としたテーブルにパーティションを作成する場合
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

上記の例だと1日ずつパーティションを設定する必要があるのですが、
パーティション数には上限があるらしい(1テーブル20000まで)ので、1日ずつとか、1時間ずつとかフォルダ分けしている場合はちょっと注意が必要かもしれないです。
※パーティションの上限については下記
https://docs.aws.amazon.com/ja_jp/general/latest/gr/aws_service_limits.html#limits_glue
さすがに対象が多すぎとなるとしんどいので、シェルスクリプトとかで回すといいと思います。
ただ、シェルスクリプト自体も1つ1つの処理実行だとそこそこ時間かかるのと、パーティションを設定するためのクエリでクエリ履歴が埋め尽くされるのが難点です。

データ元にない項目を定義しても値がnullになるだけで問題はない

jsonの出力が一定じゃなくて、いくつかの似たような型のjsonが混ざっていたり、ものによって存在しない項目があったりしても、それらのキーを全て網羅するようにまとめて定義しちゃって問題ないみたいです。
定義したけどデータ元に項目がない場合はnullが入るだけのようで。
逆に元データにある項目を全部定義する必要はないので、元データにあっても使わないような項目はテーブル作成の段階で定義しないようにしてもいいみたいですね。

ざっと書いたので、わかりづらいとか、もっとこうすればみたいな指摘があればいただけると嬉しいです。

続きを読む

VPC内に置いたElasticsearch ServiceにEC2のプロキシ経由でアクセスする

先日のアップデートでElasticsearch ServiceをVPC内で使えるようになりました。
https://aws.amazon.com/jp/blogs/news/amazon-elasticsearch-service-now-supports-vpc/

これまではパブリックなエンドポイントしか使えず、VPC内からアクセスするにはパブリックサブネットからの通信が必要でした。
VPC内に配置できれば、通信も全部VPC内で完結しますし、よりセキュアに使うことができるようになりますね。

さて、VPC内に配置したときに起きる問題の一つが、 直接ローカルからKibanaを見ることができない という問題です。
パブリックサブネットに配置してセキュリティグループで接続許可すれば行けるだろう、と最初は思ったんですが、どうやら無理みたいです。
※うまく行った、という方がいたらご教示ください…。

これを解決するためにどうするかについてはいろいろ調べてみました。
1. ALBを使う。
こちらを参考
https://dev.classmethod.jp/cloud/aws/make-public-kibana-amazon-es/
メリットは管理するサーバが増えないこと。
デメリットは、Elasticsearch ServiceのプライベートIPは可変なのに対し、ALBはターゲットにIPかEC2インスタンスしか設定できず、ターゲットのIPを定期更新するなどしなければならないこと。
1. EC2にKibanaをインストールして、Easticsearch Serviceを参照するようにする。
こちらを参考
https://dev.classmethod.jp/cloud/aws/make-public-kibana-amazon-es2/

メリットは、ドメイン指定が可能なのでElasticsearch ServiceのIPが変わっても大丈夫なことなど。 デメリットは、Kibanaの部分がEC2依存になること。
1. Windowsのインスタンスを立ててそこにリモートデスクトップでアクセスし、Windowsインスタンス内のブラウザからKibanaにアクセスする。
読んで字のごとくです。
メリットはやることが単純明快なことかな?
デメリットは大抵のケースで余計なEC2インスタンスを増やさざるを得ないこと。
1. EC2をプロキシサーバにする。(今回の方法)
EC2にKibanaを入れるパターンに近いですが、KibanaもElasticsearch Serviceのものを使用するので、Kibanaの可用性をElasticsearch Service(つまりAmazon側)に依存させたままにできるのがメリットです。
デメリットはこちらもアクセスがEC2依存になること。

今回は調べてもVPC内に配置したElasticsearch Serviceを参照するために使用したという例が見つからなかった、プロキシサーバを使う例について書いてみます。
※別の目的でプロキシサーバ経由にする例はたくさんありましたが、いずれも今回のアップデート以前の情報でした。
今回の例ではnginxを使用しますが、別にApacheでもなんでもいいと思います。

方法

パブリックサブネットにEC2インスタンスを用意する。

特に何も考えずにAmazon Linuxでよいと思います。
インスタンスタイプはとりあえずt2.microでもいいですが、
使ってるうちに負荷がしんどいなと思ったら適宜変更しましょう。
当然のことながら、SSHに加えhttpでアクセスできるようにセキュリティグループを設定します。
場合によっては踏み台サーバと共用とかでも大丈夫かなと。
※今回の例ではhttpを使用し、httpsではやらないようにします。
httpsを使用する例はこちら。(今回の設定もこちらを参考にしています。)
http://inokara.hateblo.jp/entry/2016/10/22/123538

用意したEC2からElasticsearch Serviceクラスタにアクセスできるようにする。

セキュリティグループの設定およびアクセスポリシーを適切に設定します。
セキュリティグループだけにしてもいいですし、リソースベース、IPベース、IAMユーザ・ロールベース等ポリシー設定できるのでその時の要件に沿って適切に設定すればOK。
http://docs.aws.amazon.com/ja_jp/elasticsearch-service/latest/developerguide/es-createupdatedomains.html#es-createdomain-configure-access-policies
余談ですが、Elasticserch Serviceの設定変更には意外と時間がかかるので、なるべく設定変更が少ないほうが気持ちが楽だと思います。

EC2インスタンスにnginxをインストールする。

深く考えずにyumでOKです。

sudo yum -y install nginx

/etc/nginx/nginx.confを編集する。

50行目付近に

nginx.confの一部
        include /etc/nginx/default.d/*.conf;
        location / {
        }

みたいに空で設定されているところがあるので、下記のようにします。
※抜粋、略記しています。

nginx.confの一部
        include /etc/nginx/default.d/*.conf;
        location / {
            proxy_pass https://xxxxxxxxxxxxxx.amazonaws.com/;
        }

といった感じに、proxy_pathでVPCエンドポイントを指定します。
httpsじゃなくてもいいみたいですが、httpsで問題なくアクセスできます。

nginxを起動する。

nginxのサービスを起動します。ついでに自動起動もONにしておきましょう。

sudo service nginx start
sudo chkconfig nginx on

これで
http://{{設定したインスタンスのパブリックIP}}/_plugin/kibana
にアクセスしてやれば、EC2を経由して該当のElasticsearch ClusterのKibanaにアクセスできます。

課題とかおまけとか

後ろに_plugin/kibanaをつけないでアクセスするとElasticsearchにもアクセスできちゃうので、それを良しとするかどうかは要検討です。
逆にローカルから手軽にデータ投入できたりもするわけですが…。どこまで許容するかですね。
そこを気にするのであれば、nginxの設定とかでディレクトリごとにアクセス制御するとかが必要かもしれません。

あと、Elasticsearch Serviceを配置したVPCにVPN接続している場合はこんなことしなくてもVPNエンドポイントを指定して直接参照できる気がするので、試した方いたらご教示くださると嬉しいです。

ALBでElasticsearch Serviceのインスタンスを指定できるようになればいいんですけどねえ。

続きを読む

Ansibleを使ってAmazon LinuxにMackerelのAgentをインストールする

意図

MackerelのAgent、EC2にインストールするときの手順は公式にあるのですが、対象サーバが複数台あると面倒なので、Ansible使って一気にインストールとエージェントの起動までしたいなあ、と思いました。

やり方

手順を見る限り、

  1. シェルスクリプトでリポジトリを登録
  2. yumでインストール
  3. コマンドでAPIキーの設定
  4. エージェント起動

ぐらいなので、簡単なPlaybookを一つ書いてしまえば十分かなあと。
てなわけで、下記のようにしてみました。

軽くこちらを参考にしてます。

インベントリファイル

hosts
[web]
xxx.xxx.xxx.xxx
yyy.yyy.yyy.yyy

[all:vars]
ansible_ssh_user=ec2-user
ansible_ssh_private_key_file=/path/to/ec2.pem

webのところに該当のホストのIPまたはパブリックDNSを記載。
SSHユーザはデフォルトのec2-user
鍵を指定してSSHするなら該当のホストに対するSSHに必要な秘密鍵のパスを記載
オプションで指定するのが面倒なのでここに書いてますが、
とりあえずSSHが通ればなんでもいいです(適当)
そもそもSSHが通らないとAnsible使えないのと、本題とそれるので細かくは割愛

Playbook

下記のようにしてみました。

Mackerel.yml
---
- hosts: all
  become: yes
  tasks:
    - name: Import GPG Key
      rpm_key: state=present key=https://mackerel.io/assets/files/GPG-KEY-mackerel

    - name: Install mackerel repo(Amazonlinux)
      yum: name=http://yum.mackerel.io/amznlinux/latest/x86_64/mackerel-repo-1-0.noarch.rpm state=present

    - name: Install yum mackerel angent
      yum: name=mackerel-agent state=present

    - name: Configure /etc/mackerel-agent/mackerel-agent.conf
      command: mackerel-agent init -apikey="<YOUR_API_KEY>"

    - name: Start mackerel-agent
      service: name=mackerel-agent state=started enabled=yes

インストール手順中にあるシェルスクリプトの中身ですが、

  1. MackerelのドメインからGPGキーをダウンロードしてインポート
  2. rpmコマンドでリポジトリ追加

を行っているだけでした。前半の2つのタスクがそれに該当します。
シェルスクリプトそのまま使っても良かったんですが、
既にリポジトリを追加済みだった場合、
シェルスクリプトがエラーを返してしまいそこでfailedになってしまうので、
同様のことをPlaybookで行うこととしました。

rpm_keyモジュールでGPGキーのインポートができて、
yumモジュールでリポジトリ追加ができるとのことなので、そちらで行いました。
Ansible本当に便利だなあ…。
<YOUR_API_KEY>のところは自分のAPIキーに置き換えてください。

あとは
ansible-playbook -i hosts Mackerel.yml
で行けるはずです。(hostsもMackerel.ymlもカレントディレクトリにある前提です。)

注意事項

AmazonLinuxでない場合URLが微妙に違ったりするので、Install mackerel repoのURLを変えないとダメだと思います。
RPMパッケージを使う場合(AmazonLinux以外)の手順は下記。
それ以外のところは公式サイトを。
https://mackerel.io/ja/docs/entry/howto/install-agent/rpm

何かあればご指摘くださると助かります。

続きを読む

AWS Lambda上でサーバ時刻がよくわからないことになって困った話

↓の記事を元にElasticSearchにRDSのスロークエリログを取り込もうとしていた時に困ったこと。
http://dev.classmethod.jp/cloud/aws/rds-mysql-slowquerylog-to-es/

RDSのログは仕様上1時間毎にローテーションされ、
slowquery/mysql-slowquery.log.xx
の形で保存されます。
xxの部分は、UTCの時刻でxx時までのログ、という形。
つまるところ、UTCの時刻で0000〜0059までのログだったらxxは1
0100〜0159だったらxxは2、という感じ。
あくまでもUTCの時刻なので、JSTの時刻からは-9時間してやる必要があります。

該当の記事では、サーバ時刻を使用して、その1時間前のログを対象にする、
といった処理にしているのですが、これをそのまま使用したところハマってしまいました。
あれれー、スロークエリ発生させてみたはずなのに入ってたり入ってなかったりするぞー。

何度も同じログを参照している

若干ソースをいじってどのスロークエリログを対象にしているかCloudWatch Logsに吐き出させてみたのですが、なんと何時間か同じログを参照していました。
Scheduled Eventで1時間毎に動かしていたのですが、3時間ぐらいずつ、同じログを見ていた模様。

ソース上では下記のように、pythonのプログラムでサーバ時刻を取得して動的に対象ファイル名を変化させています。
以下は抜粋です。

ソース抜粋
import boto3
import re
import os
import json
from datetime import datetime
from dateutil import tz, zoneinfo
from botocore.awsrequest import AWSRequest
from botocore.auth import SigV4Auth
from botocore.endpoint import PreserveAuthSession
from botocore.credentials import Credentials

R = re.compile(TIME_REGEX)
NOW = datetime.now()
INDEX = INDEX_PREFIX + "-" + datetime.strftime(NOW, "%Y%m%d")
TYPE = RDS_ID
SLOWQUERYLOG_PREFIX = "slowquery/mysql-slowquery.log."

def lambda_handler(event, context):
    client = boto3.client("rds")
    db_files = client.describe_db_log_files(DBInstanceIdentifier=RDS_ID)

    log_filename = SLOWQUERYLOG_PREFIX + str(NOW.hour)

でも、対象ファイルが同じということは、サーバ時刻の取得のところが何かおかしいのかなと思い、
今度はNOWの時刻をCloudWatch Logsに吐き出させてみました。

サーバ時刻が3時間ぐらい変わっていない!

確認したところ、1時間毎に動かしている処理が、3回ずつぐらい全く同じ時刻を出していました。
それも寸分違わず。(すいませんがログはここには貼れません…。)
そりゃ同じログ参照しちゃいますね…。
軽く探してみたものの同現象に遭遇している人はすぐ見つからず。
同じようなことになった方いらっしゃいませんかね?

Lambda上でサーバ時刻を取得してはいけない?

上記のような問題があるために、サーバ時刻を取得して利用しようと思うと想定と違った挙動になってしまいます。
Lambdaはサーバ管理のあたりがブラックボックスなのでこのあたりどうなってるのかよくわかりませんね

とりあえず、ntpサーバから時刻取得

現在時刻を取得して何かに利用する処理を入れたいときは、
ntpサーバから時刻を取得したほうがよさそうです。
そうすると今度はntpサーバからの時刻取得に失敗した際どうするのか、
というのを考慮しなければいけませんが…。

続きを読む