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

 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のクラスタ

クラスタの準備については、以前ブログでも書きましたのでそちらを参考にして下さい。

リモートホストからのデータロード設定

1.マネージメントコンソールを開いて、対象のクラスタの詳細画面から、公開鍵とIPアドレスを調べます


2.EC2インスタンスのauthorized_keysに調べた公開鍵を追加します

viでauthorized_keysを開いて、鍵を追記します

vi ~/.ssh/authorized_keys


3.Redshiftクラスタからのアクセスを許可するために、調べたIPアドレスをセキュリティーグループに追加します


4.EC2インスタンスの公開鍵を調べて控えます

cat /etc/ssh/ssh_host_rsa_key.pub

5.マニフェストファイルを作成します

{ 
   "entries": [ 
     {"endpoint":"<EC2インスタンスのIP>", 
       "command": "<実行するコマンド>",
       "mandatory":true, 
       "publickey": "<4.で調べた鍵>", 
       "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


6.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)のままだと、事前の解析用に一回リモートロード打実行され、その後にもう一度実行されてしまうので、2度実行されたら困るコマンドを実行する場合は、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万行ぐらいのアクセスログが、6秒ぐらいで取り込まれました。

ログローテートのタイミングに合わせて、ロード処理を実行する様にcronにコマンドを実行させるといった方法で、ログを自動的に取り込むことも可能です。

うまくローテートの時間とcronでのLOADを組み合わせれば、比較的リードタイムが短いアクセス解析にも使えそうです。

もっとリアルタイムで処理したい場合はkinesisが良さそうなので、これも調べてそのうちブログに書きたいと思います。
(プレビューのOKが出たけど、実際に使うとなるとサンプルプッログラムを書かないと、検証できなそうなので少し先かな・・)