Glue StudioでAurora Serverless v1からデータを取得して結合するGlue Jobを作ってみた

AWSTemplateFormatVersion: "2010-09-09"

Parameters:
  EnvironmentName:
    Type: String

  VPCCIDR:
    Type: String
    Default: 10.192.0.0/16

  PrivateSubnetCIDR1:
    Type: String
    Default: 10.192.0.0/24

  PrivateSubnetCIDR2:
    Type: String
    Default: 10.192.1.0/24

Resources:
  VPC:
    Type: AWS::EC2::VPC
    Properties:
      CidrBlock: !Ref VPCCIDR
      EnableDnsSupport: true
      EnableDnsHostnames: true
      Tags:
        - Key: Name
          Value: !Sub ${EnvironmentName}-VPC

  # Private Subnetのネットワーク設定
  PrivateSubnet1:
    Type: AWS::EC2::Subnet
    Properties:
      AvailabilityZone: !Select [0, !GetAZs ""]
      CidrBlock: !Ref PrivateSubnetCIDR1
      VpcId: !Ref VPC
      Tags:
        - Key: Name
          Value: !Sub ${EnvironmentName}-PrivateSubnet1

  PrivateRouteTable1:
    Type: AWS::EC2::RouteTable
    Properties:
      VpcId: !Ref VPC
      Tags:
        - Key: Name
          Value: !Sub ${EnvironmentName}-PrivateRouteTable1

  PrivateSubnetRouteTableAssociation1:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      SubnetId: !Ref PrivateSubnet1
      RouteTableId: !Ref PrivateRouteTable1

  # Private Subnetのネットワーク設定
  PrivateSubnet2:
    Type: AWS::EC2::Subnet
    Properties:
      AvailabilityZone: !Select [1, !GetAZs ""]
      CidrBlock: !Ref PrivateSubnetCIDR2
      VpcId: !Ref VPC
      Tags:
        - Key: Name
          Value: !Sub ${EnvironmentName}-PrivateSubnet2

  PrivateRouteTable2:
    Type: AWS::EC2::RouteTable
    Properties:
      VpcId: !Ref VPC
      Tags:
        - Key: Name
          Value: !Sub ${EnvironmentName}-PrivateRouteTable2

  PrivateSubnetRouteTableAssociation2:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      SubnetId: !Ref PrivateSubnet2
      RouteTableId: !Ref PrivateRouteTable2

  # セキュリティグループ
  GlueConnectionSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: GlueConnectionSecurityGroup
      VpcId: !Ref VPC
      Tags:
        - Key: Name
          Value: !Sub ${EnvironmentName}-GlueConnectionSecurityGroup

  SelfRefGlueConnectionSecurityGroupIgress:
    Type: AWS::EC2::SecurityGroupIngress
    Properties:
      GroupId: !GetAtt GlueConnectionSecurityGroup.GroupId
      IpProtocol: tcp
      FromPort: "0"
      ToPort: "65535"
      SourceSecurityGroupId: !GetAtt GlueConnectionSecurityGroup.GroupId

  AuroraSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: AuroraSecurityGroup
      VpcId: !Ref VPC
      Tags:
        - Key: Name
          Value: !Sub ${EnvironmentName}-AuroraSecurityGroup
      SecurityGroupIngress:
        - IpProtocol: tcp
          FromPort: 5432
          ToPort: 5432
          SourceSecurityGroupId: !Ref GlueConnectionSecurityGroup


  # エンドポイントの設定
  EndpointS3:
    Type: AWS::EC2::VPCEndpoint
    Properties:
      RouteTableIds:
        - !Ref PrivateRouteTable1
        - !Ref PrivateRouteTable2
      ServiceName: !Sub com.amazonaws.${AWS::Region}.s3
      VpcEndpointType: Gateway
      VpcId: !Ref VPC

  EndpointSecretManager:
    Type: AWS::EC2::VPCEndpoint
    Properties:
      VpcEndpointType: Interface
      ServiceName: !Sub com.amazonaws.${AWS::Region}.secretsmanager
      VpcId: !Ref VPC
      PrivateDnsEnabled: True
      SubnetIds: 
        - !Ref PrivateSubnet1
        - !Ref PrivateSubnet2
      SecurityGroupIds:
        - !Ref GlueConnectionSecurityGroup

-- パスワードの文字列は自分のものに変更してください。
CREATE USER glue_user WITH PASSWORD 'パスワードの文字列';
CREATE DATABASE glue_sample_database;
GRANT ALL PRIVILEGES ON DATABASE glue_sample_database TO glue_user;

CREATE TABLE name (
  id INTEGER PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE price (
  id INTEGER PRIMARY KEY,
  price INTEGER NOT NULL
);

INSERT INTO name (id, name) VALUES
(1, 'Apple'),
(2, 'Orange'),
(3, 'Grape'),
(4, 'Pineapple'),
(5, 'Peach');

INSERT INTO price (id, price) VALUES
(1, 150),
(2, 120),
(3, 390),
(4, 400),
(5, 350);

AWSTemplateFormatVersion: "2010-09-09"
Description: Creating Glue Studio Execution Role for Aurora Data

Parameters:
  MartBucketName:
    Description: Backet Name for Data Mart.
    Type: String
  SecretARNforAuroraUser:
    Description: Secret ARN for Aurora User .
    Type: String

Resources:
  GlueStudioExecutionRole:
    Type: AWS::IAM::Role
    Properties:
      # Role名はAWSGlueServiceRole-Studio-から始まる名前に変更してください。
      RoleName: AWSGlueServiceRole-Studio-CM-nayuts
      AssumeRolePolicyDocument:
        Version: "2012-10-17"
        Statement:
          - Effect: Allow
            Principal:
              Service: glue.amazonaws.com
            Action: "sts:AssumeRole"
      Path: "/"
      Policies:
        - PolicyName: GlueStudioS3AccessPolicy
          PolicyDocument:
            Version: "2012-10-17"
            Statement:
              - Effect: "Allow"
                Action: [
                  "s3:*"
                ]
                Resource: [
                  !Sub "arn:aws:s3:::${MartBucketName}",
                  !Sub "arn:aws:s3:::${MartBucketName}/*"
                ]
        - PolicyName: GlueStudioSecretManagerAccessPolicy
          PolicyDocument:
            Version: "2012-10-17"
            Statement:
              - Effect: "Allow"
                Action: [
                  "secretsmanager:GetSecretValue"
                ]
                Resource: [
                  !Sub "${SecretARNforAuroraUser}"
                ]
      ManagedPolicyArns:
        - arn:aws:iam::aws:policy/service-role/AWSGlueServiceRole