マーケティング担当者にSQLを完全マスターさせた話
普段開発とかしない人達にもデータベースに簡単に触れられるようにしたお話です．
安全なデータベースを作る
本番サービスのデータベースと同等，だけど個人情報的なものは隠しておきたい，よく聞く話ですね．
これについては様々なアプローチがあるようですが，できる限り安定させたい＆バッチでやるにしてもサーバの面倒を見たくない，とう方針のもと，RDSのスナップショットを利用して作成することにしました．
処理の流れ
- RDSが1日1回スナップショットを取っている(これはRDSの機能)
- RDSのスナップショットが取られると，RDSからイベント通知が投げられるので，SNS→SQSへ届くようにしておく(これもRDSの機能)
- CloudWatchでSQSのキュー数を監視するようにしておき，キューが1つ以上になると処理用のインスタンスを立ちあげる設定にしておく(CloudWatch，AutoScalingの機能)
- インスタンス内で以下のような処理を行う
- RDSが取ってくれたスナップショットからリストアする(本番とは別インスタンス)
- リストアされたデータベースへ個人情報をマスクするためのSQLを流す
- マスクされたデータベースのスナップショットを取る
- マスクされたデータベースのスナップショットからリストアして，昨日時点のものと入れかえる
- 処理用インスタンスが死ぬ
これの良い点は，何かに失敗してやりなおしたい時はSQSにキューを投げこんであとは放置にできるところです．また，EC2も必要最低限しか起動しないのでお財布にも優しいはずです．
簡単にアクセスできる仕組みを考える
アクセス方法を検討する
やりたいことは「MySQLのデータベースに接続し，SQLを投げたい」というだけなのですが，これを非エンジニアの人にやってもらおうとすると意外とハードルが高いです．
普段開発業務をしないならターミナルを起動したこともない人が多いでしょうから，「mysqlコマンドで〜」とか言っても通じません．GUIのクライアントを使うにしても接続情報等を良い感じに設定しなければなりませんし，加えて直接接続が制限されていたりするとSSHやProxyの設定もしなければならなかったりして，そうすると「SSHとは〜」みたいな本来無関係のツール／仕組みの説明も必要になってきます．
特定の人しか使わないのであればエンジニアが個別でサポートするのもアリでしょうが，データ抽出のためのサポートというよりはむしろツールの使い方のサポートになりがちで，さらに導入したツールはエンジニアが普段使ってるものではない．．．なんてことになった暁にはツールの使い方を調べながらサポートするという誰一人得をしない状態になります．
そして，ここで一番厄介なのは，導入でモタついてしまうことによって「SQLを投げるのは大変なこと」という認識を持たれてしまうことです．一度そういう認識ができあがってしまうと，よほど必要性のない限り二度と関心を持ってもらえなくなってしまいます．
普段使っているものを利用したい
LiBでは社内のコミュニケーションツールとしてSlackを利用しています．
Slackは非エンジニアにとってはお洒落なチャットツールとして振る舞う一方で，エンジニアにとってはBotやAPI等を利用して機能拡張させられる宝の山です．
なので，Slackをうまく利用できれば 使い方に関してはほとんどゼロサポート で，かつ 全員が日常的に使用するものであるため新しいものを導入しなくて良い と，前述の問題点を一気にクリアできることになります．
SlackからSQLを投げる仕組み
SlackのSlash Commandがお手軽です． /command message のように発言すると特定のAPIへリクエストを投げてくれるSlackの機能で，AWS API Gateway+Lambdaと組み合わせる例をよく見かけます．
基本方針としてはSlash Commandからのリクエストを受けてLambdaが起動し，その中でデータベースへ接続して結果を返してやれればよさそうです．
ただ，ここで気をつけなければならないのがLambdaの仕様と課金体系です．
Lambdaは1回の実行がごく短時間で終わるものであれば，大量に実行しても無料枠内で収まることが多いので料金はほとんど問題になりません．しかし今回の場合，どんなクエリがどれぐらいの頻度で投げられる1のかわからないため，1回の実行時間も頻度も予測することができません．大量のデータが取得されるようなクエリや複雑な集計クエリを馬鹿正直にLambda内から投げてしまうと，実行時間の長期化2+Lambdaのスペックアップによる料金増が重なり，クラウド破産の恐れもあります．
また，Lambdaを限界までスペックアップしても使用可能なメモリがそこまで増えるわけではないので，取得できる件数に制限を付けなければ．．．とか，説明し辛い処理を入れるはめになり複雑化していくのが目に見えています．
SlackからSQL投げる仕組み(改)
基本方針は同様ですが，Lambdaの負担を減らしにいきます．
Lambdaだけでやろうとすると前述の通り破産の恐れがあるので，Lambdaは中継地点としてのみ考えるのが良さそうです．
Lambdaだけでやろうとした場合の問題点は「課金額が青天井になりかねない」ことでした．一方で，課金額が予測できて，かつ自前の処理を動かせるものといえば．．．そう，EC2です．
LambdaからEC2インスタンス内へ処理を投げることができれば，課金額の問題もクリアできそうです．
そこで登場するのがEC2 RunCommandです．RunCommandは，外部から与えたコマンドをEC2の中で実行してくれるサービスです．
EC2を起動しておく必要があるため継続的に料金が発生しますが，インスタンス内で重い処理が走ったからといって課金額が増えることは基本的にはありません3．
改めて方針としては，Slash Commandを受けてLambdaが起動し，Lambdaの処理の中でRunCommandを呼びだします．これならLambdaがやることはRunCommandのAPI呼びだしだけになるのでスペックも最低で良く，所要時間も一瞬です．
あとはRunCommandで呼びだされたEC2インスタンス内でSQLを投げ，結果をSlackに投げ返してやるので，時間のかかりそうな処理はすべてEC2インスタンス内で実行されることになり，Lambdaの課金に怯えなくてよくなります．
実装する
いろいろ必要なのでポイントだけ．
ざっくり全体像
Slash Command
Slackの設定画面からポチポチと追加して設定するだけです．
本番リリースするまでは補完候補に出さないようにしておくとこっそりテストできて良い感じです．
今回は /mask というコマンドを作成しました．
Lambda
コードはこんな感じ．力技です．
ポイントはこのへん．
- EC2はAutoHealingパターンにしてインスタンスを固定せず，所定のタグが付いているインスタンスを選んで使うようにしました．これにより，裏でインスタンスが再作成4されていても問題なくなります．
- SlackのファイルアップロードAPIはチャネル(or DirectMessage)に対してファイルをアップロードするものなので，Botユーザを作ってセッションを開く必要があります．
// RunCommandの対象インスタンスを探す
// インスタンスを固定せず，特定のタグが付いてるものを使う
var find_instance_to_exec = { Filters: [{ Name: 'tag:Role', Values: [ 'slack-mask' ] }] };
ec2.describeInstances(find_instance_to_exec, function(err, data) {
if (err) context.fail(err); // an error occurred
else {
// TODO
var instance_id = data.Reservations[0].Instances[0].InstanceId;
// SSM(RunCommand)のパラメータを作成
// SSMドキュメントを予め作っておけばこの中でコマンドをベタベタ書く必要はなくなるはず．．．
var mask_params = {
DocumentName: 'AWS-RunShellScript',
InstanceIds: [ instance_id ],
// 誰が実行したかがEC2のWebコンソール上から見れるように
Comment: '/mask by ' + params.user_name,
Parameters: {
commands: [
"export LANG=ja_JP.UTF8",
"export TMPDIR=$(mktemp -d -p /mnt/mask)",
"export TMPFILE=$TMPDIR/my.txt",
// インスタンス上でmysql clientが使う
// だいぶやっつけ．扱うデータ量によってはこれだと破滅するのでさらなる工夫が必要
"echo "+ (new Buffer(params.text)).toString('base64') +" | base64 -d | MYSQL_PWD=mysql_password mysql -u mysql_user -h mysql_host -D mysql_database &> $TMPFILE",
// 先頭31行(ヘッダ行含む)をSlackへメッセージとして返答
"curl -X POST --data-urlencode \"payload={\\\"response_type\\\": \\\"ephemeral\\\", \\\"username\\\": \\\"DB\\\", \\\"attachments\\\": [{\\\"text\\\":\\\"\\\`\\\`\\\`"+params.text+"\\\`\\\`\\\`\\\", \\\"mrkdwn_in\\\": [\\\"text\\\"]}, {\\\"text\\\":\\\"\\\`\\\`\\\``head -31 $TMPFILE`\\\`\\\`\\\`\\\", \\\"mrkdwn_in\\\": [\\\"text\\\"]}], \\\"icon_emoji\\\": \\\":mysql:\\\"}\" "+params.response_url,
// 結果が書かれたファイルを圧縮
"gzip $TMPFILE",
// リクエストしたSlackユーザに対し，DirectMessageをオープン
"export CH=$(curl -F user="+params.user_id+" -F token=slack_api_token https://slack.com/api/im.open | jq -r '.channel.id')",
// オープンしたDirectMessageに対しファイルをアップロード
"curl -F file=@$TMPFILE.gz -F initial_comment=\""+params.text+"\" -F channels=$CH -F token=slack_api_token https://slack.com/api/files.upload",
"rm -rf $TMPDIR"
],
executionTimeout: ['180']
},
TimeoutSeconds: 30
};
var req = ssm.sendCommand(mask_params, function(err, data) {
if (err) {
console.log(err, err.stack); // an error occurred
context.fail(err);
} else {
context.succeed("executing query...");
}
});
}
});
使ってもらう
ここまでくればあとはSQLを投げてもらうだけです．
↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓
ややこしいDB接続部分はすべてLambda+RunCommandの中に隠蔽されているので，SQLにだけ集中してもらえます．
また， Only you can see this message とある通り，投げたクエリも結果も本人にしか分からないようにしています．なので気兼ねなく試行錯誤してもらうことができます．
弊社はマーケティング担当者がかなり熱心にSQLを勉強して完全マスターしてくれました．そのおかげで，いままではエンジニアに依頼してテーブルのダンプをもらいエクセルで頑張ってフィルターしていたところを，最初から自分で考えた条件で必要なデータだけを取れるようになり，30分ぐらいかかっていたレポーティングが数十秒で終わるようになったとの報告も頂きました．
まとめ
- ツールの利用方法など本質的でない問題は極力無視できるように工夫しよう
- 新しいツールはどんなに簡単なものであっても学習コストがそれなりにかかる．普段使わないものならなおさら．
- ドキュメントいらずで使えるのが理想．
- Lambdaは一瞬で終わる処理でに．重くなりそうな処理はEC2に流そう．
- 積極的にSQLを書いてもらう．本人にとっても後の処理が楽に／早くなるなどメリットがあるのでがんばってもらう．