Glue CrawlerをAurora MySQLへ向けて実行してみる

AWSTemplateFormatVersion: '2010-09-09'
Description: CloudFormation template for Aurora MySQL and related resources for Glue Crawler

Parameters:
  VpcCIDR:
    Description: CIDR block for the VPC
    Type: String
    Default: 10.0.0.0/16

  PrivateSubnet1CIDR:
    Description: CIDR block for Private Subnet 1
    Type: String
    Default: 10.0.1.0/24

  PrivateSubnet2CIDR:
    Description: CIDR block for Private Subnet 2
    Type: String
    Default: 10.0.2.0/24

  DBName:
    Description: Database name
    Type: String
    Default: mydb

  DBUsername:
    Description: Database admin username
    Type: String
    Default: admin
    NoEcho: true

  DBInstanceClass:
    Description: Database instance class
    Type: String
    Default: db.t3.medium

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

  # Subnets
  PrivateSubnet1:
    Type: AWS::EC2::Subnet
    Properties:
      VpcId: !Ref VPC
      AvailabilityZone: !Select
        - 0
        - !GetAZs ''
      CidrBlock: !Ref PrivateSubnet1CIDR
      Tags:
        - Key: Name
          Value: !Sub ${AWS::StackName}-PrivateSubnet1

  PrivateSubnet2:
    Type: AWS::EC2::Subnet
    Properties:
      VpcId: !Ref VPC
      AvailabilityZone: !Select
        - 1
        - !GetAZs ''
      CidrBlock: !Ref PrivateSubnet2CIDR
      Tags:
        - Key: Name
          Value: !Sub ${AWS::StackName}-PrivateSubnet2

  # Route Tables
  PrivateRouteTable:
    Type: AWS::EC2::RouteTable
    Properties:
      VpcId: !Ref VPC
      Tags:
        - Key: Name
          Value: !Sub ${AWS::StackName}-PrivateRouteTable

  PrivateSubnet1RouteTableAssociation:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      RouteTableId: !Ref PrivateRouteTable
      SubnetId: !Ref PrivateSubnet1

  PrivateSubnet2RouteTableAssociation:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      RouteTableId: !Ref PrivateRouteTable
      SubnetId: !Ref PrivateSubnet2

  # Security Groups
  AuroraSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: Security group for Aurora MySQL
      VpcId: !Ref VPC
      Tags:
        - Key: Name
          Value: !Sub ${AWS::StackName}-AuroraSecurityGroup

  SSMSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: Security group for SSM endpoints
      VpcId: !Ref VPC
      SecurityGroupIngress:
        - IpProtocol: tcp
          FromPort: 443
          ToPort: 443
          CidrIp: !Ref VpcCIDR
      Tags:
        - Key: Name
          Value: !Sub ${AWS::StackName}-SSMSecurityGroup

  SSMBastionSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: Security group for SSM Bastion
      VpcId: !Ref VPC
      Tags:
        - Key: Name
          Value: !Sub ${AWS::StackName}-SSMBastionSecurityGroup

  # Security Group Rules
  AuroraIngressFromSSMBastion:
    Type: AWS::EC2::SecurityGroupIngress
    Properties:
      GroupId: !Ref AuroraSecurityGroup
      IpProtocol: tcp
      FromPort: 3306
      ToPort: 3306
      SourceSecurityGroupId: !Ref SSMBastionSecurityGroup
      Description: Allow MySQL access from SSM Bastion

  AuroraIngressSelfReference:
    Type: AWS::EC2::SecurityGroupIngress
    Properties:
      GroupId: !Ref AuroraSecurityGroup
      IpProtocol: tcp
      FromPort: 0
      ToPort: 65535
      SourceSecurityGroupId: !Ref AuroraSecurityGroup
      Description: Allow all inbound traffic from the same security group

  # IAM Roles
  SSMInstanceProfile:
    Type: AWS::IAM::InstanceProfile
    Properties:
      Path: /
      Roles:
        - !Ref SSMInstanceRole

  SSMInstanceRole:
    Type: AWS::IAM::Role
    Properties:
      AssumeRolePolicyDocument:
        Version: '2012-10-17'
        Statement:
          - Effect: Allow
            Principal:
              Service: ec2.amazonaws.com
            Action: sts:AssumeRole
      ManagedPolicyArns:
        - arn:aws:iam::aws:policy/AmazonSSMManagedInstanceCore
        - arn:aws:iam::aws:policy/CloudWatchAgentServerPolicy

  # Secrets Manager
  AuroraSecret:
    Type: AWS::SecretsManager::Secret
    DeletionPolicy: Delete
    UpdateReplacePolicy: Delete
    Properties:
      Name: !Sub ${AWS::StackName}/aurora/credentials
      Description: Aurora database credentials
      GenerateSecretString:
        SecretStringTemplate: !Sub '{"username": "${DBUsername}"}'
        GenerateStringKey: "password"
        PasswordLength: 32
        ExcludeCharacters: '"@/\'

  # Aurora Subnet Group
  DBSubnetGroup:
    Type: AWS::RDS::DBSubnetGroup
    Properties:
      DBSubnetGroupDescription: Subnet group for Aurora MySQL
      SubnetIds:
        - !Ref PrivateSubnet1
        - !Ref PrivateSubnet2
      Tags:
        - Key: Name
          Value: !Sub ${AWS::StackName}-DBSubnetGroup

  # Aurora Parameter Groups
  AuroraClusterParameterGroup:
    Type: AWS::RDS::DBClusterParameterGroup
    Properties:
      Description: Parameter group for Aurora MySQL cluster
      Family: aurora-mysql8.0
      Parameters:
        character_set_server: utf8mb4
        collation_server: utf8mb4_unicode_ci

  AuroraInstanceParameterGroup:
    Type: AWS::RDS::DBParameterGroup
    Properties:
      Description: Parameter group for Aurora MySQL instances
      Family: aurora-mysql8.0
      Parameters:
        max_connections: 1000

  # Aurora Cluster
  AuroraCluster:
    Type: AWS::RDS::DBCluster
    DeletionPolicy: Delete
    UpdateReplacePolicy: Delete
    Properties:
      Engine: aurora-mysql
      EngineVersion: 8.0.mysql_aurora.3.08.1
      DatabaseName: !Ref DBName
      MasterUsername: !Sub '{{resolve:secretsmanager:${AuroraSecret}:SecretString:username}}'
      MasterUserPassword: !Sub '{{resolve:secretsmanager:${AuroraSecret}:SecretString:password}}'
      DBSubnetGroupName: !Ref DBSubnetGroup
      VpcSecurityGroupIds:
        - !Ref AuroraSecurityGroup
      DBClusterParameterGroupName: !Ref AuroraClusterParameterGroup
      BackupRetentionPeriod: 1
      Tags:
        - Key: Name
          Value: !Sub ${AWS::StackName}-AuroraCluster

  AuroraPrimaryInstance:
    Type: AWS::RDS::DBInstance
    DeletionPolicy: Delete
    UpdateReplacePolicy: Delete
    Properties:
      Engine: aurora-mysql
      DBClusterIdentifier: !Ref AuroraCluster
      DBInstanceClass: !Ref DBInstanceClass
      DBParameterGroupName: !Ref AuroraInstanceParameterGroup
      DBSubnetGroupName: !Ref DBSubnetGroup
      PubliclyAccessible: false
      Tags:
        - Key: Name
          Value: !Sub ${AWS::StackName}-AuroraPrimaryInstance

  # Glue Resources
  GlueDatabase:
    Type: AWS::Glue::Database
    Properties:
      CatalogId: !Ref AWS::AccountId
      DatabaseInput:
        Name: !Join ['_', !Split ['-', !Sub '${AWS::StackName}_db']]

  # VPC Endpoints
  S3VPCEndpoint:
    Type: AWS::EC2::VPCEndpoint
    Properties:
      ServiceName: !Sub com.amazonaws.${AWS::Region}.s3
      VpcId: !Ref VPC
      VpcEndpointType: Gateway
      RouteTableIds:
        - !Ref PrivateRouteTable

  SSMVPCEndpoint:
    Type: AWS::EC2::VPCEndpoint
    Properties:
      ServiceName: !Sub com.amazonaws.${AWS::Region}.ssm
      VpcId: !Ref VPC
      VpcEndpointType: Interface
      PrivateDnsEnabled: true
      SubnetIds:
        - !Ref PrivateSubnet1
        - !Ref PrivateSubnet2
      SecurityGroupIds:
        - !Ref SSMSecurityGroup

  SSMMessagesVPCEndpoint:
    Type: AWS::EC2::VPCEndpoint
    Properties:
      ServiceName: !Sub com.amazonaws.${AWS::Region}.ssmmessages
      VpcId: !Ref VPC
      VpcEndpointType: Interface
      PrivateDnsEnabled: true
      SubnetIds:
        - !Ref PrivateSubnet1
        - !Ref PrivateSubnet2
      SecurityGroupIds:
        - !Ref SSMSecurityGroup

  # SSM Bastion
  SSMBastionInstance:
    Type: AWS::EC2::Instance
    Properties:
      InstanceType: t3.micro
      ImageId: '{{resolve:ssm:/aws/service/ami-amazon-linux-latest/al2023-ami-kernel-6.1-x86_64}}'
      SubnetId: !Ref PrivateSubnet1
      IamInstanceProfile: !Ref SSMInstanceProfile
      SecurityGroupIds:
        - !Ref SSMBastionSecurityGroup
      Tags:
        - Key: Name
          Value: !Sub ${AWS::StackName}-SSMBastion