Redshiftのリモートデータロードについて
この記事は「AWS Advent Calendar 2013」の12/17分の記事となります。
Redshiftは、今までS3やDynamoDBからのロードに対応していましたが、先日のAWS re:Invent 2013でRedshiftが、リモートホストからのロードが可能になったと発表がありました。
これは何かというと、S3やDynamoDBからだけではなく、EC2のインスタンスやEMRから直接データをRedshiftに取り込めるようになったということです。
例えばアプリケーションのログをRedshiftに入れて、集計などに利用しようとした場合、
今まではログファイルをS3に一度アップロードして、それをLOADする方法をとっていたかと思います。
多いのは、サーバのログをtd-agent(fluentd)を使ってS3にアップロードする方式ではないでしょうか。
この方法でも特に問題はないのですが、サイズが大きくなるとアップロード → Redshiftへloadとと待ち時間が発生します。
しかし、今回の発表により、EC2のインスタンスから直接Redshiftにloadできるので、この待ち時間を短縮することが可能です。
（もっとリアルタイムを求めるなら、Kinesisがきっと便利）
今回は、このリモートロードを使ってWebサーバからアプリケーションのログをアップロードすることをイメージしながら解説したいと思います。
事前準備
事前に、以下を準備しておきます。
1. EC2インスタンス（Amazon Linuxを使いました）
2. Redshiftのクラスタ
クラスタの準備については、以前ブログでも書きましたのでそちらを参考にして下さい。
リモートホストからのデータロード設定
１．マネージメントコンソールを開いて、対象のクラスタの詳細画面から、公開鍵とIPアドレスを調べます
２．EC2インスタンスのauthorized_keysに調べた公開鍵を追加します
viでauthorized_keysを開いて、鍵を追記します
vi ~/.ssh/authorized_keys
３．Redshiftクラスタからのアクセスを許可するために、調べたIPアドレスをセキュリティーグループに追加します
４．EC2インスタンスの公開鍵を調べて控えます
cat /etc/ssh/ssh_host_rsa_key.pub
５．マニフェストファイルを作成します
{
"entries": [
{"endpoint":"＜EC2インスタンスのIP＞",
"command": "＜実行するコマンド＞",
"mandatory":true,
"publickey": "＜４．で調べた鍵＞",
"username": "<SSHで入るときのユーザー名＞"},
・・以下繰り返しで、複数のホストを指定可能
]
}今回作成したものの例はこちら
{
"entries": [
{"endpoint":"xxx.xxx.xxx.xxx",
"command": "sh /home/ec2-user/load_log.sh",
"mandatory":true,
"publickey": "AAAAB・・・",
"username": "ec2-user"}
]
}/home/ec2-user/load_log.sh の中身は
sudo cat /var/log/httpd/access_log
６．S3にマニフェストをアップロードします
manifest.jsonなど、適当に名前をつけてS3上の任意のBucketに保存します。
RedshiftのクラスタからS3へマニフェストを取得するので、IAMでユーザーを作成して専用のアクセキー、シークレットキーを使ったほうがいいかもしれない
IAMで専用のユーザーを作成した場合は、S3のbucket policyで以下のようなものを追加すると、manifest.jsonに対してgetする権限を与えることが出来ます。
{
"Version": "2008-10-17",
"Id": "Policy123456",
"Statement": [
{
"Sid": "Stmt123456",
"Effect": "Allow",
"Principal": {
"AWS": "arn:aws:iam::1122334455:user/remote-load-user"
},
"Action": "s3:GetObject",
"Resource": "arn:aws:s3:::＜bucket名＞/manifest.json"
}
]
}これで、リモートロードの準備はできたので、ロードするデータと、ロード先のDBを作成します。
Webサーバ側の設定
今回はとりあえず、ApacheのアクセスログをRedshiftに取り込むことにするので、ログをそのままロードできるように、csv形式のフォーマットへ変更します。
/etc/httpd/conf/httpd.conf を開いて、ログフォーマットを変更します
#LogFormat "%h %l %u %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-Agent}i\"" combined
↓
LogFormat "%h,%u,%t,\"%r\",%>s,%b,\"%{Referer}i\",\"%{User-Agent}i\"" combined
Redshiftにテーブルを作成
アクセスログを格納できるように上記で変更したログフォーマットに合わせてテーブルを作成します。
create table access_log( remote_host varchar, remote_user varchar, request_time varchar, request_first_row varchar, http_status int, response_bytes varchar, referer varchar, user_agent varchar);
データをロード
LOADコマンドを叩いて、リモートロードを実行します
前回書いたブログにworkbenchの設定方法など書かれているので、そちらを参考にSQLを叩ける環境を作ります。
copy ＜ロード先テーブル名＞ from 's3://＜bucket-name＞/＜マニフェストファイルのキー＞' credentials 'aws_access_key_id=XXX;aws_secret_access_key=XXXXXXX' delimiter ',' ←CSV形式なのでカンマを区切り文字に指定 COMPUPDATE OFF ssh;
COMPUPDATEはデフォルト値（ON）のままだと、事前の解析用に一回リモートロード打実行され、その後にもう一度実行されてしまうので、２度実行されたら困るコマンドを実行する場合は、OFFにする必要がある。
で、今回実行したコマンドはこれ
copy access_log from 's3://＜bucket-name＞/manifest.json' credentials 'aws_access_key_id=XXX;aws_secret_access_key=XXXXXXX' delimiter ',' COMPUPDATE OFF ssh;
また、今回はCOPYコマンドを実行した際に、「sorry, you must have a tty to run sudo」とエラーがでたので、visudoで”Defaults requiretty”をコメントアウトしました。
コマンドを実行すると、EC2のインスタンスに対してSSHでログインしてきて、マニフェストファイルのcommandで指定したコマンドが実行されます。
今回の単純なアクセスログを取り込む場合だと、150万行ぐらいのアクセスログが、６秒ぐらいで取り込まれました。
ログローテートのタイミングに合わせて、ロード処理を実行する様にcronにコマンドを実行させるといった方法で、ログを自動的に取り込むことも可能です。
うまくローテートの時間とcronでのLOADを組み合わせれば、比較的リードタイムが短いアクセス解析にも使えそうです。
もっとリアルタイムで処理したい場合はkinesisが良さそうなので、これも調べてそのうちブログに書きたいと思います。
（プレビューのOKが出たけど、実際に使うとなるとサンプルプッログラムを書かないと、検証できなそうなので少し先かな・・）
