企業のデータ分析体制ってどうやって作るの？(マスキング処理とAWS CLIのcron設定による自動化を解説)

はじめに

どうも、未経験からエンジニア転職を目指しているもきおです。
企業(データベース)のデータ分析ってどうやってやってんの？
エンジニアをこれから目指す方でこう言ったこと思ったことはないでしょうか？

今回は実務案件を踏まえて企業のデータ分析体制ってどんな感じで作っているか、どういったことが分析において重要なのかを未経験の分際で解説してみたいと思います。

補足：この記事は実務案件の補足記事となりますので良ければそちらの記事もご覧ください

この記事では主にマスキング、定期実行処理に絞って記載しているので分析、可視化等の全体像をご覧になりたい方は上記リンク記事をご覧ください。

こんな人に読んで欲しい

・企業のデータ分析ってどうやってるか知りたい方
・個人情報流出を防ぐのマスキング方法を知りたい方
・AWSサービスってコマンドで作成出来ることを感動を知りたい方
・Linuxコマンドの定期実行ってどうやってやるか知りたい方

データ分析体制を整える際重要なこと

重要なことは以下の2点かなと思います。

1.個人情報のマスキング処理
2.分析データ更新の自動化

1.データ分析をする際に極めて重要なことは個人情報の流出を防ぐことです。
そりゃそうだろって感じですよね、分かります。

個人情報の流出を防ぐために個人情報のマスキング処理というものが必要になってくると思います。
マスキングって言うと下記画像のように塗り潰したりモザイクかけたりして個人情報を見えなくするってイメージすると思うのですがデータ分析によるマスキングは少し違います。

データ分析の場合は例えばmokio→dummy_name1みたいな感じでデータの置換処理を行うことです。
この置換処理をどうやってやったかは後ほど説明します。

2.データは日々蓄積されていきます。
やはり最新に更新されたデータで分析を行う必要がありますしデータ分析は毎日のように行われます。
しかしデータ元からデータを引っ張ってマスキング処理をしてそのデータをデータ分析するツールで分析できる状態までもっていく一連の流れをいちいち手作業で処理していくのは手間でしかありません。
よって一連の流れを自動化する必要があります。

定期実行処理

マスキング処理構成図

・本番DBからデータを取ってきてマスキングし、マスキングデータを抽出するまでの一連の流れ
①MasterDBの自動スナップショットからマスキング用RDSの作成
②マスキングRDSにマスキング処理
③スナップショットを取得
④取得したスナップショットをS3にエクスポート

この一連の流れをAWS CLIで書き起こしシェルスクリプト化。
シェルスクリプトファイルをcronで定期実行設定を行い一連の流れを自動化しました。

ここでAWS CLIやらcronやらわけわかんないやつ出てきたぞと思う方もいらっしゃるかもしれないのでAWS CLI、cronについて簡単にまとめていきたいと思います。

AWS CLI

AWSコマンドラインインターフェイスの略でコマンド操作によってAWSサービスの構築ができる。

自分も含め初学者はAWSサービス環境を構築(EC2やRDS等の作成)の際、GUI操作(まあ言ってしまえば画面上でインスタンス名書いたりぽちぽちクリックしたりすること)にて作成してきたと思うがこのAWS CLIコマンドを使用すればEC2やRDSを始めとしてさまざまなAWSサービスがコマンド実行で作成できます。

例えば

S3バケット作成

$ aws s3 mb s3://bucket-name

とEC2のLinux上で記載して実行するだけでS3バケットが作成できちゃいます。
いちいちAWSマネジメントコンソール上でS3項目開いてバケット作成ボタン押してバケット名書いて作成みたいな工程をする手間が無くなります。

またAWS CLIに関しては後ほど別記事にて記載していきたいと思います。
GUI操作では自動実行処理に限界があったため今回AWS CLIを使用しました。

cron

cronはあらかじめ作成した実行ファイルを決められた時間、頻度で定期実行してくれる機能のこと。
例としてはこんな感じ

* 18 * * * bash /home/ec2-user/exports3.sh

これだと毎日18時(UTCで)にexports3.shという実行ファイルを定期実行してくれます。

これらをふまえて定期実行するためのシェルスクリプトファイルは以下
※S3バケット、RDSのサブネットグループ、パラメーターグループ、KMSkey、IAMロール等都度作り直す必要がない物に関しては予めGUI操作にて作成しております。

シェルスクリプト、飛ばし読みしてOK

#!/bin/bash -l

#絶対パスを変数格納
SCRIPT_DIR=[絶対パス]
echo ${SCRIPT_DIR}

#既存のS3オブジェクトファイル削除
${SCRIPT_DIR} s3 rm s3://[S3バケット名]/[S3ファイル名] --recursive

#最新のスナップショット名を取得
SNAPSHOT_NAME=$(${SCRIPT_DIR} rds describe-db-snapshots \
--db-instance-identifier aws-and-infra-web \
--query 'reverse(sort_by(DBSnapshots,&SnapshotCreateTime))[0].DBSnapshotArn' --output text)

echo ${SNAPSHOT_NAME}

#変数に格納
db_new_instance=[作成したいデータベース識別子の名前]
instance_class=db.t2.micro
subnet_group=[指定したいサブネットグループ名]
security_group_id=[セキュリティグループID]
parameter_group_name=[パラメーターグループ名]
region=[指定したければリージョン名(任意)]
snapshot=SNAPSHOT_NAME

#RDSリストア
${SCRIPT_DIR} rds restore-db-instance-from-db-snapshot \
--db-instance-identifier ${db_new_instance} \
--db-snapshot-identifier ${SNAPSHOT_NAME} \
--db-instance-class ${instance_class} \
--db-subnet-group-name ${subnet_group} \
--vpc-security-group-ids ${security_group_id} \
--db-parameter-group-name ${parameter_group_name} \
--availability-zone ${region} \
--no-multi-az \

#リストアRDS使用可能になるまで待機
${SCRIPT_DIR} rds wait db-instance-available --db-instance-identifier ${db_new_instance} --region ap-northeast-1

#マスキング処理
mysql --defaults-extra-file=/var/tmp/mysql.conf \
-h dokugaku-masking.c4ntgclabrz3.ap-northeast-1.rds.amazonaws.com -u de_master \
 dokugaku_engineer < ~/masking.sql

sleep 10

#スナップショットを取得
${SCRIPT_DIR} rds create-db-snapshot \
--db-instance-identifier ${db_new_instance} \
--db-snapshot-identifier [作成したいスナップショット識別子名]

#スナップショット取得開始まで少々時間がかかるので5分待機
sleep 5m

#最新のスナップショット名取得
SNAPSHOT_NAME=$(aws rds describe-db-snapshots \
--db-instance-identifier ${db_new_instance} \
--query 'reverse(sort_by(DBSnapshots,&SnapshotCreateTime))[0].DBSnapshotArn' --output text)

echo ${SNAPSHOT_NAME};

#日時で変化する識別子名の作成
NOW_TIME=$(date "+%Y%m%d%H")
EXPORT_NAME="myexport-${NOW_TIME}"
echo ${NOW_TIME};
echo ${EXPORT_NAME};

ARN_NAME=[エクスポート先のS3バケットのarn名]
KMS_KEY_ID=[作成したkmsキーのID]


# RDSのスナップショットをS3にエクスポート
${SCRIPT_DIR} rds start-export-task \
--export-task-identifier ${EXPORT_NAME} \
--source-arn ${SNAPSHOT_NAME} \
--s3-bucket-name ${ARN_NAME} \
--kms-key-id ${KMS_KEY_ID}

#エクスポートに40分程度かかるため
sleep 1h

#S3ファイル名変更
${SCRIPT_DIR} s3 mv s3://[ファイルが格納されているS3バケット名]/${EXPORT_NAME}  s3://rds-for-s3/my-s3-export3 --recursive

#スナップショット削除
${SCRIPT_DIR} rds delete-db-snapshot \
--db-snapshot-identifier ${SNAPSHOT_NAME}

# DBの削除
${SCRIPT_DIR} rds delete-db-instance \
--db-instance-identifier  \
--skip-final-snapshot ${db_new_instance}

• 今回詰まったのは以下
  • 最新のスナップショットデータを取ってくるコマンド作成
  • RDSスナップショットをS3エクスポートする際の識別子名を都度変更
  • 時間がかかるコマンドのsleepでの時間調整

とりわけS3へエクスポートする識別子を削除するコマンドがないのが非常に困りました。

そのせいでループして実行すると既にその識別子名があるので実行できないというエラーが発生してしまいます。そこで日時を取得し、それをくっつけた名前を識別子として変数格納することで識別子名が重複するというエラーを解消しました。

マスキング処理

今回マスキング処理において重要な点は以下の2点でした。
・本番環境のデータベースに負荷をかけずにデータのマスキング処理を行う
・マスキング前の元データを分析者含め閲覧できないようにする

今回本番環境のDBからDumpデータを生成し、マスキング処理用のRDSに流し込んでも良かったのだがデータベースへの負荷が考えられたのでRDSのスナップショットからマスキング用RDSへデータの復元(リストア作業)を行うことにしました。

元データを閲覧できないようにするため、マスキングしたデータのスナップショットをS3にエクスポートした後はマスキングRDSを削除しています。

今回の案件ではユーザー名とe-mailのマスキング処理を行いました。
処理自体は非常にシンプルでデータを復元したマスキング用RDSへSQL文を流し込むだけです。

マスキング処理のコードは以下

masking.sql

#!/bin/sh
use [マスキングするテーブル名];

#usernameのマスキング処理 `new_dummy_id`で記載
update users set username= replace(username, username, concat('new_dummy_',id));

#emailのマスキング処理 `new_dummy_email_id`で記載
update users set email= replace(email, left(email,instr(email,'@')- 1), concat('new_dummy_email_',id));

ユーザー名、e-mail共に置換処理を行なっています。
このコードもシェルスクリプトファイルとしてあらかじめEC2上で作成し、それを定期実行用のシェルスクリプトファイルに埋め込んで使用しています。

マスキング処理後はこういったデータに置換されます。
一意である必要があるのでidに紐付けて末尾を変動させる形を取りました。

あとがき

今回の流れであればAWS Glueを使用しマスキング処理を行えば良いというアドバイスをいただきました。まさにおっしゃる通りでそちらで機能実装できればよかったのですが納期の兼ね合いで学習コストがかかりそうなものは省く必要がありました。

今後Glueでもマスキング処理を実装し、また記事にしたいと思います。

長々と書いてしまいましたがここまで見ていただけた方は何名いるでしょうか？笑
最後までご覧いただき本当にありがとうございます！！

最後まで見たよ！って方、この記事が少しでも良いと感じていただけましたらLGTMポチッとしていただけますと幸いです。