AssociatedRolesを使ってCFnでS3とデータをロード＆アンロードできるAurora MySQL5.7を構築する

もきゅりん

2019.09.07

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

おはようございます、もきゅりんです。

CFnを使用してS3とデータをロード＆アンロードできるAurora MySQL5.7を構築したので、まとめておきます。

テンプレートの再利用等で役立てば幸いです。

基本的な利用方法、概要は把握している前提で進ます。

そもそも概要を理解するためのドキュメントはこちらですが、

Amazon S3 バケットのテキストファイルから Amazon Aurora MySQL DB クラスターへのデータのロード

読みやすく簡潔にまとまっている下記弊社ブログもとても参考になります。

Amazon Aurora MySQLとS3間でデータをロード＆アンロードする

上記記事からセットアップ手順の概要を再掲しておきます。

RDSがS3にアクセスするためのIAMロールを作成
DB クラスターパラメーターグループのパラメーター（aws_default_s3_role）にIAMロールのARNを指定。Auroraクラスターを起動。
1のIAMロールをAuroraクラスターにアタッチ
mysqlクライアントからSELECT INTO OUTFILE S3/ LOAD DATA FROM S3を実行

上記1~3の手順をCFnテンプレートで自動化します。

※ ブログをアップした際に気付かなかったのですが、2019/8/29にUpdateかかっており、CFnのドキュメントを英語版にするとプロパティが表示されました。。間抜けでした...

AWS Documentation » AWS CloudFormation » User Guide » Release History

もともとはセットアップ手順3を手動、またはカスタムリソースを利用して設定しなければなりませんでしたが、今回の更新によってすべて自動で対応できるものとなりました。ブログアップ時は手動およびカスタムリソースで対応していました。

前提

DBSubnetは作成済み
DBのSecurityGroupは作成済み(DB-sgでエクスポートされているとする)
S3バケットを作成済み(${ENV}-${AWS::AccountId}-bucketとする)

やること

スタックを作成する
LOAD DATA FROM S3をテストする
オマケ

1. スタックを作成する

スタック作成の前に、Parameter StoreにDBパスワードを格納します。

aws ssm put-parameter --name DBMasterPassword --value 'DBPASSWORD' --type SecureString

スタックを作成します。

aws cloudformation deploy --stack-name demo-aurora-stack \
  --template -file sample1.yml \
  --parameter-overrides \
  DBName="DBNAME" \
  DBMasterUserName="DBMasterUserName" \
  DBSubnetGroupName="DBSubnetGroupName" \
  NameTagPrefix="NameTagPrefix" \
  ENV="ENV" \
  --capabilities CAPABILITY_NAMED_IAM

ImportValueやParamtersを参照している部分は、利用する環境に応じて適宜調整して下さい。

各種パラメータも適宜調整が必要となるかと思います。

AWSTemplateFormatVersion: 2010-09-09
Description: Create Aurora MySQL5.7 Cluster

# ------------------------------------------------------------#
# Parameters
# ------------------------------------------------------------#
Parameters:
  NameTagPrefix:
    Type: String
    Default: test
    Description: Prefix of Name tags.
  ENV:
    Type: String
    Default: stg
    Description: Prefix of Env tags.
  MultiAZ:
    Description: MultiAZ true or false
    Type: String
    Default: false
    AllowedValues:
      - true
      - false
  DBName:
    Description: Enter DB Name
    Type: String
  DBMasterUserName:
    Description: DB MasterUserName
    Type: String
  DBInstanceClass:
    Description: DBInstanceType
    Type: String
    Default: 'db.t3.small'
  DBSubnetGroupName:
    Description: DB SubnetName
    Type: String
  BackupRetentionPeriod:
    Description: BuckUp Generation(1-35)
    Type: String
    Default: '7'
  BackupWindow:
    Description: Enter BackupWindow
    Type: String
    Default: '19:00-19:30'
  MaintenanceWindow:
    Description: Enter MaintenanceWindow(UTC)
    Type: String
    Default: 'sun:19:30-sun:20:00'
# ------------------------------------------------------------#
# Resources
# ------------------------------------------------------------#
Resources:
  # IAM Role for S3 Access
  S3AccessRole:
    Type: AWS::IAM::Role
    Properties:
      RoleName: !Sub '${NameTagPrefix}-${ENV}-DB-AccessS3Role'
      Path: /
      AssumeRolePolicyDocument:
        Statement:
          - Effect: Allow
            Principal:
              Service:
                - 'rds.amazonaws.com'
            Action:
              - 'sts:AssumeRole'
      Policies:
        - PolicyName: !Sub '${NameTagPrefix}-${ENV}-AllowAuroraToReadS3'
          PolicyDocument:
            Version: 2012-10-17
            Statement:
              - Resource:
                  - !Sub 'arn:aws:s3:::${ENV}-${AWS::AccountId}-bucket'
                  - !Sub 'arn:aws:s3:::${ENV}-${AWS::AccountId}-bucket/*'
                Effect: Allow
                Action:
                  - s3:ListBucket
                  - s3:GetObject
                  - s3:GetObjectVersion
                  - s3:AbortMultipartUpload
                  - s3:DeleteObject
                  - s3:GetObject
                  - s3:ListMultipartUploadParts
                  - s3:PutObject

  AuroraMySQL57DBClusterParameterGroup:
    Type: AWS::RDS::DBClusterParameterGroup
    Properties:
      Family: 'aurora-mysql5.7'
      Description: 'Aurora-MySQL57 ClusterParameterGroup'
      Parameters:
        time_zone: 'Asia/Tokyo'
        aws_default_s3_role: !GetAtt S3AccessRole.Arn
  AuroraMySQL57DBOptionGroup:
    Type: 'AWS::RDS::OptionGroup'
    Properties:
      EngineName: 'aurora-mysql'
      MajorEngineVersion: '5.7'
      OptionGroupDescription: 'Aurora MysQL57 DB_RDS_OptionGroup'
      Tags:
        - Key: Name
          Value: Aurora-MySQL57DB-OptionGroup
  AuroraMySQL57DBInstanceParameterGroup:
    Type: AWS::RDS::DBParameterGroup
    Properties:
      Family: 'aurora-mysql5.7'
      Description: 'Aurora-MySQL57 InstanceParameterGroup'
  AuroraMySQL57DBCluster:
    Type: AWS::RDS::DBCluster
    Properties:
      AssociatedRoles:
        - RoleArn: !GetAtt S3AccessRole.Arn
      BackupRetentionPeriod: !Ref BackupRetentionPeriod
      DBClusterIdentifier: !Sub '${NameTagPrefix}-${ENV}-mysql57cluster'
      DBClusterParameterGroupName: !Ref AuroraMySQL57DBClusterParameterGroup
      DBSubnetGroupName: !Ref DBSubnetGroupName
      Engine: aurora-mysql
      DatabaseName: !Ref DBName
      MasterUsername: !Ref DBMasterUserName
      MasterUserPassword: '{{resolve:ssm-secure:DBMasterPassword:1}}'
      Port: 3306
      PreferredBackupWindow: !Ref BackupWindow
      PreferredMaintenanceWindow: !Ref MaintenanceWindow
      StorageEncrypted: True
      EnableCloudwatchLogsExports:
        - error
        - general
        - slowquery
        - audit
      VpcSecurityGroupIds:
        - Fn::ImportValue: !Sub DB-sg
      Tags:
        - Key: Name
          Value: Aurora-MySQL57DB-Cluster
  AuroraMySQL57DBInstance:
    Type: 'AWS::RDS::DBInstance'
    Properties:
      AutoMinorVersionUpgrade: False
      AvailabilityZone: ap-northeast-1a
      DBInstanceClass: !Ref DBInstanceClass
      DBInstanceIdentifier: !Sub '${NameTagPrefix}-${ENV}-mysql57db'
      DBClusterIdentifier: !Ref AuroraMySQL57DBCluster
      DBSubnetGroupName: !Ref DBSubnetGroupName
      Engine: aurora-mysql
      OptionGroupName: !Ref AuroraMySQL57DBOptionGroup
      DBParameterGroupName: !Ref AuroraMySQL57DBInstanceParameterGroup
      PubliclyAccessible: False
      Tags:
        - Key: Name
          Value: AuroraMySQL57DBInstance
# ------------------------------------------------------------#
# Outputs
# ------------------------------------------------------------#
Outputs:
  DBEndpoint:
    Value:
      Fn::GetAtt:
        - AuroraMySQL57DBInstance
        - Endpoint.Address

2. LOAD DATA FROM S3をテストする

今回はLOAD DATA FROM S3をテストします。

(テンプレートではSELECT INTO OUTFILE S3の設定も可能にしています。)

こんなcsvデータを作成します。

$ cat .demodata.csv
No,Name,Age
1,Taro,30
2,Jiro,20
3,Hanako,23
4,Yoshiko,99
5,Masao,450%

まずこれをS3バケットにアップロードします。

$ aws s3 cp demodata.csv s3://${ENV}-${AWS::AccountId}-bucket/demodata.csv
upload: ./demodata.csv to s3://${ENV}-${AWS::AccountId}-bucket/demodata.csv
$ aws s3 ls s3://${ENV}-${AWS::AccountId}-bucket/
2019-09-06 18:27:30         76 demodata.csv

そしたら、Aurora MySQLに接続してSQLコマンドを入力します。デモ用のテーブルを作成します。

MySQL [dbname]> create table demo(No int, Name text, Age int);
Query OK, 0 rows affected (0.03 sec)

LOAD DATA FROM S3 ステートメントを使ってデータをロードします。

列名を含む最初のヘッダー行をスキップするため、IGNORE 1 LINESを利用します。

構文はこちらを記載されております。

Amazon S3 バケットのテキストファイルから Amazon Aurora MySQL DB クラスターへのデータのロード

MySQL [dbname]> LOAD DATA FROM S3  's3://${ENV}-${AWS::AccountId}-bucket/demodata.csv'
    ->     INTO TABLE demo
    ->     FIELDS TERMINATED BY ','
    ->     IGNORE 1 LINES;
Query OK, 5 rows affected (0.09 sec)
Records: 5  Deleted: 0  Skipped: 0  Warnings: 0

確認します。

MySQL [dbname]> select * from demo;
+------+---------+------+
| No   | Name    | Age  |
+------+---------+------+
|    1 | Taro    |   30 |
|    2 | Jiro    |   20 |
|    3 | Hanako  |   23 |
|    4 | Yoshiko |   99 |
|    5 | Masao   |  450 |
+------+---------+------+
5 rows in set (0.01 sec)

ちゃんとロードされました。

3. オマケ

PubliclyAccessible: Falseの場合の利用のために、VPCエンドポイント作成のためのCFnテンプレートも記載しておきます。

AWSTemplateFormatVersion: 2010-09-09
Description: VPC S3Endpoint Create

# ------------------------------------------------------------#
# Parameters
# ------------------------------------------------------------#
Parameters:
  NameTagPrefix:
    Type: String
    Default: test
    Description: Prefix of Name tags.
  ENV:
    Type: String
    Default: stg
    Description: Prefix of Env tags.
# ------------------------------------------------------------#
# Resources
# ------------------------------------------------------------#
Resources:
  VPCS3Endpoint:
    Type: AWS::EC2::VPCEndpoint
    Properties:
      RouteTableIds:
        - Fn::ImportValue: !Sub '${NameTagPrefix}-${ENV}-private-rtb'
      ServiceName: !Sub com.amazonaws.${AWS::Region}.s3
      VpcId:
        Fn::ImportValue: !Sub '${NameTagPrefix}-${ENV}-vpc'

# ------------------------------------------------------------#
# Outputs
# ------------------------------------------------------------#
Outputs:
  VPCS3Endpoint:
    Value: !Ref VPCS3Endpoint
    Export:
      Name: !Sub '${NameTagPrefix}-${ENV}-VPC-S3endpoint'

最後に

ほぼ1週間前、ドキュメントに更新がかかっていたことを見過ごして、更新前のブログでは余計な手間と時間を費やしていました。

望む機能は本当にないか要確認、ですね。

いい勉強でした。

以上です。

どなたかのお役に立てば幸いです。

AssociatedRolesを使ってCFnでS3とデータをロード＆アンロードできるAurora MySQL5.7を構築する

前提

やること

1. スタックを作成する

2. LOAD DATA FROM S3をテストする

3. オマケ

最後に

参考

イベント

EVENT【5/29（水）大阪】Alteryxの使い方から導入メリットまですべてがわかるセミナー＆ワークショップ

EVENT【5/15（水）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/8リモート】クラスメソッドのフリーランスエンジニア会社説明会〜フィンテック / リテール業界案件特集〜を開催します

EVENT【5/28（火）】AWSを最大活用するための1dayカンファレンス

EVENT【5/17（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【4/25（木）リモート】Google Cloudに携わるエンジニアのキャリア～クラスメソッド会社説明会～

AssociatedRolesを使ってCFnでS3とデータをロード＆アンロードできるAurora MySQL5.7を構築する

前提

やること

1. スタックを作成する

2. LOAD DATA FROM S3をテストする

3. オマケ

最後に

参考

イベント

EVENT【5/29（水）大阪】Alteryxの使い方から導入メリットまですべてがわかるセミナー＆ワークショップ

EVENT【5/15（水）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/8リモート】クラスメソッドのフリーランスエンジニア会社説明会 〜フィンテック / リテール 業界案件特集〜 を開催します

EVENT【5/28（火）】AWSを最大活用するための1dayカンファレンス

EVENT【5/17（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【5/10（金） 名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【4/25（木）リモート】Google Cloudに携わるエンジニアのキャリア～クラスメソッド会社説明会～

関連記事

Amazon Connect Customer Profilesのデータソース自動更新と更新時間変更について

VPCフローログの送信先についてまとめてた

【CDK】AWS Step FunctionsでS3のCOPY Objectを実行

Invalid principal in policyが出たら確認したい！IAMロール・IAMユーザーが削除されるとPrincipal要素のARNがプリンシパルIDに置き換わります

EVENT【5/8リモート】クラスメソッドのフリーランスエンジニア会社説明会〜フィンテック / リテール業界案件特集〜を開催します

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！