Amazon RDS or AuroraにあるデータをBigQueryで分析する方法
ツルオカ要件
- Amazon RDS or AuroraにあるデータをBigQueryで分析したい
- データベース内にある情報のうち分析に必要ないデータ（メールアドレスなど個人情報）は持ち出しせずAWS内にとどめたい
案1. RDSの標準エクスポート機能を使ってS3の米国リージョンにエクスポートしBigQuery Omniで参照
- 2025/2時点でBigQuery OmniがS3の東京リージョンに対応していない点が肝
- 今後対応される可能性があるとすればこちらがスマート
- RDSの標準エクスポート機能にデータの選別はできないのでS3にはすべてのデータが出力（Parquet形式）
- 個人情報などはBigQueryの列レベルセキュリティなどで制御
- AWSからデータを持ち出さない（移行しない）のが楽
- ref: BigQuery OmniでAmazon S3のデータをクエリしてみた - G-gen Tech Blog
案2. AWS DMSで一部データをマスキング後にS3へエクスポートしBigQueryへ定時Transfer
- RDSの標準エクスポート機能ではデータ加工ができないのでDMSを間に挟み実現する方法
- マスキングだけではなく列ごと削除するなど柔軟な加工が可能
- S3にエクスポートする際に不要なデータを削っているので安心（リージョンも東京で良い）
- BigQueryへデータ移行しないといけないので、BigQueryストレージ側へデータが蓄積されれる（ストレージコストもかかる）
主な流れ
- RDSからDMSで個人情報などをマスクしてS3にエクスポート
- Google Cloud側でBigQuery Transferを設定しS3 ReadonlyのIAMを指定してバッチ取得
DMSを使う理由
RDS→S3へ直接スナップショットExportが最も楽だが、データ分析に不要な個人情報などは転送したくないため一部加工（間引く）目的で利用する。DMSは任意の列をマスキングすることが可能。
全カラム転送してよい場合は普通にスナップショットをエクスポートすれば良い:
ツルオカDMS(Database Migration Service)とは
名前の通りデータベースのマイグレーションツール
- 簡単に、データベースのデータをAWSへ移行するマネージドサービス
- 同種類のデータベースエンジンに加え、異なる種類間のデータ移行にも対応
- 移行元はRDBMSだけでなく、NoSQLデータベースも利用可能
DMSのマスキングについて
例）C6BGJ566669Kという値をマスキングする場合
| 種類 | 変換例 |
|---|---|
Digits Mask（例: 数値を#に変換） |
C#BGJ######K |
| Digits Randomize（例: 数値をランダムに変換） | C1BGJ842170K |
| Hashing Mask（値全体をハッシュ化） | 7CB06784764C9030CCC41E25C15339FEB293FFE9B329A72B5FED564E99900C75 |
逆に言うとマスキング対象は数字（numeric value）か全体ハッシュ化の2パターンしかないのでそれで要件が満たせるか確認するべき。ハッシュは「ソルトなしSHA256」で推測できなくはないので、漏洩リスクが致命傷なデータあるいはそもそも分析で一切使わないのであれば削除したほうが良い。
例1）SHA256の推測（一般用語"hello"で正しい）
例2）SHA256の推測（"foo@gmail.com"が正しい）
特定列の削除
上記マスキングでも十分かもしれないが列ごと削除してしまうこともできる。
ツルオカAWS DMS におけるレプリケーション
AWS DMS では、「データベースレプリケーション」の仕組みを使ってデータを移行します。
DMSのレプリケーションプロセスは、フルロード + 変更データキャプチャ（CDC） の2つの方式がありま.す。
レプリケーション方式 説明
フルロード 一括で全データをコピー（初回のみ）
CDC（変更データキャプチャ） 変更されたデータのみをリアルタイムで同期
ツルオカ
dms-vpc-roleの作成
すでに作成済みでも以下のエラーが出る場合は、一度削除し再作成するとうまく行った。
Trust Relationship
{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "AWSDMSVPCPolicyTemplate",
"Effect": "Allow",
"Principal": {
"Service": "dms.amazonaws.com"
},
"Action": "sts:AssumeRole",
"Condition": {
"StringEquals": {
"aws:SourceAccount": "072651350417"
}
}
}
]
}
Permission Policy
{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "CreateNetworkInterface",
"Effect": "Allow",
"Action": [
"ec2:CreateNetworkInterface",
"ec2:DeleteNetworkInterface",
"ec2:ModifyNetworkInterfaceAttribute"
],
"Resource": [
"arn:aws:ec2:*:072651350417:network-interface/*",
"arn:aws:ec2:*:072651350417:instance/*",
"arn:aws:ec2:*:072651350417:subnet/*",
"arn:aws:ec2:*:072651350417:security-group/*"
]
},
{
"Sid": "DescribeVPC",
"Effect": "Allow",
"Action": [
"ec2:DescribeAvailabilityZones",
"ec2:DescribeInternetGateways",
"ec2:DescribeSubnets",
"ec2:DescribeVpcs",
"ec2:DescribeSecurityGroups",
"ec2:DescribeDhcpOptions",
"ec2:DescribeNetworkInterfaces"
],
"Resource": "*"
}
]
}
dms-cloudwatch-logs-roleの作成
Trust Relationship
{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "AWSDMSCloudWatchPolicyTemplate",
"Effect": "Allow",
"Principal": {
"Service": "dms.amazonaws.com"
},
"Action": "sts:AssumeRole",
"Condition": {
"StringEquals": {
"aws:SourceAccount": "072651350417"
}
}
}
]
}
Permission Policy
{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "AllowDescribeOnAllLogGroups",
"Effect": "Allow",
"Action": [
"logs:DescribeLogGroups"
],
"Resource": [
"arn:aws:logs:*:072651350417:log-group:*"
]
},
{
"Sid": "AllowDescribeOfAllLogStreamsOnDmsTasksLogGroup",
"Effect": "Allow",
"Action": [
"logs:DescribeLogStreams"
],
"Resource": [
"arn:aws:logs:*:072651350417:log-group:dms-tasks-*",
"arn:aws:logs:*:072651350417:log-group:dms-serverless-replication-*"
]
},
{
"Sid": "AllowCreationOfDmsLogGroups",
"Effect": "Allow",
"Action": [
"logs:CreateLogGroup"
],
"Resource": [
"arn:aws:logs:*:072651350417:log-group:dms-tasks-*",
"arn:aws:logs:*:072651350417:log-group:dms-serverless-replication-*:log-stream:"
]
},
{
"Sid": "AllowCreationOfDmsLogStream",
"Effect": "Allow",
"Action": [
"logs:CreateLogStream"
],
"Resource": [
"arn:aws:logs:*:072651350417:log-group:dms-tasks-*:log-stream:dms-task-*",
"arn:aws:logs:*:072651350417:log-group:dms-serverless-replication-*:log-stream:dms-serverless-*"
]
},
{
"Sid": "AllowUploadOfLogEventsToDmsLogStream",
"Effect": "Allow",
"Action": [
"logs:PutLogEvents"
],
"Resource": [
"arn:aws:logs:*:072651350417:log-group:dms-tasks-*:log-stream:dms-task-*",
"arn:aws:logs:*:072651350417:log-group:dms-serverless-replication-*:log-stream:dms-serverless-*"
]
}
]
}
費用
シングルAZのt2.microであれば無料枠で結構使える
AWS DMS では、AWS 無料利用枠の一部として、Single-AZ dms.t2.micro インスタンスを月あたり最大 750 時間利用できます。機能によって、使用量に応じたコストがかかります。AWS DMS Fleet Advisor と AWS DMS SC については、使用したストレージの分のみお支払いいただきます。AWS DMS 移行オプションでは、レプリケーションインスタンスを使用するかサーバーレスオプションを使用するかにかかわらず、使用した容量に対して時間単位でお支払いいただきます。