template.yaml

Parameters:

  DBName:
    Default: postgres
    Description: My database
    Type: String
    MinLength: '1'
    MaxLength: '64'
    AllowedPattern: '[a-zA-Z][a-zA-Z0-9]*'
    ConstraintDescription: Must begin with a letter and contain only alphanumeric characters.

  DBUsername:
    NoEcho: 'true'
    Description: Username for MySQL database access
    Type: String
    MinLength: '1'
    MaxLength: '16'
    AllowedPattern: '[a-zA-Z][a-zA-Z0-9]*'
    ConstraintDescription: must begin with a letter and contain only alphanumeric characters.

  DBPassword:
    NoEcho: 'true'
    Description: Password MySQL database access
    Type: String
    MinLength: '8'
    MaxLength: '41'
    AllowedPattern: '[a-zA-Z0-9]*'
    ConstraintDescription: must contain only alphanumeric characters.

Resources:

  ScheduledRule:
    Type: AWS::Events::Rule
    Properties:
      ScheduleExpression: "cron(0 14 * * ? *)"
      State: "ENABLED"
      Targets: 
        - 
          Arn: !GetAtt ETL.Arn
          Id: AWSETL
  
  PermissionForEventsToInvokeLambda:
    Type: AWS::Lambda::Permission
    Properties:
      FunctionName: !GetAtt ETL.Arn
      Action: lambda:InvokeFunction
      Principal: events.amazonaws.com
      SourceArn: !GetAtt ScheduledRule.Arn

  ETL:
    Type: AWS::Lambda::Function
    Properties:
      Runtime: python3.7
      Role: !GetAtt ETLRole.Arn
      Handler: lambda_function.lambda_handler
      MemorySize: 512
      Timeout: 5
      Environment:
        Variables: 
          database: !Ref DBName
          endpoint: !GetAtt DBInstance.Endpoint.Address
          jh: https://raw.githubusercontent.com/datasets/covid-19/master/data/time-series-19-covid-combined.csv
          nyt: https://raw.githubusercontent.com/nytimes/covid-19-data/master/us.csv
          password: !Ref DBPassword
          port: !GetAtt DBInstance.Endpoint.Port
          region: us-east-1
          sns: !Ref ETLSNS
          user: !Ref DBUsername
      Code: lambda/
      Layers:
        - arn:aws:lambda:us-east-1:251566558623:layer:python37-layer-pandas-gbq:1
        - arn:aws:lambda:us-east-1:898466741470:layer:psycopg2-py37:3

  ETLRole:
    Type: AWS::IAM::Role
    Properties:
      AssumeRolePolicyDocument:
        Version: 2012-10-17
        Statement:
          - Effect: Allow
            Principal: 
              Service:
                - lambda.amazonaws.com
            Action:
              - sts:AssumeRole
      Path: /
      Policies:
        - PolicyName: SNS-Log-VPC
          PolicyDocument:
            Version: 2012-10-17
            Statement:
              - Effect: Allow
                Action:
                  - logs:*
                Resource: "*"
              - Effect: Allow
                Action: 
                  - sns:*
                Resource: "*"
       
  LambdaFunctionLogGroup:
    Type: "AWS::Logs::LogGroup"
    DependsOn: "ETL"
    Properties: 
      RetentionInDays: 7
      LogGroupName: !Join ["", ["/aws/lambda/", !Ref ETL]]

  ETLSNS:
    Type: AWS::SNS::Topic
    Properties:
      Subscription:
        - Endpoint: "bansiawspro@gmail.com"
          Protocol: email
  
  DBInstance:
    Type: AWS::RDS::DBInstance
    Properties:
      AllocatedStorage: 20
      BackupRetentionPeriod: 0
      DBName: !Ref DBName
      DBInstanceClass: db.t2.micro
      DBSecurityGroups: 
        - !Ref DBSecurityGroup
      Engine: postgres
      MasterUsername: !Ref DBUsername
      MasterUserPassword: !Ref DBPassword
      MaxAllocatedStorage: 20
      MultiAZ: false  

  DBSecurityGroup:
    Type: AWS::RDS::DBSecurityGroup
    Properties:
      DBSecurityGroupIngress:
        CIDRIP: 0.0.0.0/0
      GroupDescription: "SG for RDS"