###########################################################################
# Author: Sudhir Gupta
# Date: 26-Nov-2018
# Version: 1.0
###########################################################################

AWSTemplateFormatVersion: 2010-09-09
Description: >-
 AWS CF Template to create following resources
 VPC, 
 2 public subnets, 
 Security group, 
 IGW, S3 EndPoint
 Redshift Clusterc with IAM Role for Spectrum, WLM Queues & Custom Cluster Parameter Group, 
 Different Cloudwatch alarms based on environment, 
 AWS Glue Catalog, 
 Glue Crawler, 
 Sample Glue Catalog/External tables for Non-Produnction environments,
 Set of Tags 

Parameters:
  DatabaseName:
    Description: The name of the first database to be created when the cluster is created
    Type: String
    Default: rsdev01
    AllowedPattern: '([a-z]|[0-9])+' 

  VPCCIDR:
    Description: CIDR address for the VPC to be created.
    Type: String
    Default: 10.0.0.0/16
    AllowedPattern: >-
      ^(([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])\.){3}([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])(\/(1[6-9]|2[0-8]))$
    ConstraintDescription: CIDR block parameter must be in the form x.x.x.x/16-28
  
  PublicSubnet1:
    Description: CIDR address for VPC Public subnet to be created in AZ1.
    Type: String
    Default: 10.0.0.0/20
    AllowedPattern: >-
      ^(([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])\.){3}([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])(\/(1[6-9]|2[0-8]))$
    ConstraintDescription: CIDR block parameter must be in the form x.x.x.x/16-28
    
  PublicSubnet2:
    Description: CIDR address for  VPC Public subnet to be created in AZ2.
    Type: String
    Default: 10.0.16.0/20
    AllowedPattern: >-
      ^(([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])\.){3}([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])(\/(1[6-9]|2[0-8]))$
    ConstraintDescription: CIDR block parameter must be in the form x.x.x.x/16-28
  
  destCIDRpublic:
    Description: Destination CIDR for Public route / InternetGateway
    Type: String
    Default: 0.0.0.0/0
  
  SubscriptionEmail:
    Type: String
    Description: Email address to notify when an API activity has triggered an alarm
    Default: "sudhig@amazon.com"
    #AllowedPattern: ^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.]+$
  
  PortNumber:
    Description: The port number on which the cluster accepts incoming connections.
    Type: Number
    Default: '8192'
  
  ClusterType:
    Description: The type of cluster
    Type: String
    Default: single-node
    AllowedValues:
      - single-node
      - multi-node
    ConstraintDescription: must be single-node or multi-node.
  
  NumberOfNodes:
    Description: >-
      The number of compute nodes in the cluster. For multi-node clusters, the
      NumberOfNodes parameter must be greater than 1
    Type: Number
    Default: '1'
  
  NodeType:
    Description: The type of node to be provisioned
    Type: String
    Default: dc1.large
    AllowedValues:
      - dc1.large
      - dc1.8xlarge
      - dc2.large
      - dc2.8xlarge
      - ds2.xlarge
      - ds2.8xlarge
      
  SnapshotIdentifierName:
    Description: Leave it blank for new cluster. Enter Snapshot Identifier only if you want to restore from snapshot. 
    Type: String
    Default: rslab-ds2-xl-4n
 
  SnapshotAccountNumber:
    Description: "AWS Account number of Snapshot (Leave it blank, if snapshot is created in current AWS Account)"
    Type: String
    Default: '413094830157'

  Maintenancewindow:
    Description: Maintenance Window for Redshift Cluster
    Type: String
    Default: 'sat:05:00-sat:05:30'
  
  kmskey:
    Description: Existing KMS key ID
    Type: String
    Default: ''
  
  MasterUsername:
    Description: >-
      The user name that is associated with the master user account for the
      cluster that is being created
    Type: String
    Default: rsadmin
    AllowedPattern: '([a-z])([a-z]|[0-9])*'
    ConstraintDescription: must start with a-z and contain only a-z or 0-9.
  
  MasterUserPassword:
    Description: >-
      The password that is associated with the master user account for the cluster that is being created. Example: Welcome123
    Type: String
    NoEcho: 'true'
    MinLength: '4'
    MaxLength: '64'
    AllowedPattern: >-
     ^(?=.*[a-z])(?=.*[A-Z])(?=.*[0-9])(?!._*[@/\\\"']).*$
    ConstraintDescription: >-
     Must contain only alphanumeric characters. Example: Welcome123
    
  S3BucketForSpectrum:
        Default: awspsa-redshift-lab
        Type: String
        Description: Enter existing S3 Bucket contains data files for Redshift Spectrum
        
  GlueCatalogDatabase:
        Type: String
        Description: The name of the Glue Catalog database.
        Default: spectrumdb
        
  SpectrumTableName1:
        Type: String
        Default: testspectrumtable1
        Description: Name of Sample table to be created in Glue catalog.
        
  GlueCrawlerName:
        Type: String
        Default: TestCrawler1
        Description:  Name of the Glue Crawler to be created for sample S3 bucket for non-Prod Environment
        
  GlueTablePrefix:
        Type: String
        Default: tbl_
        Description: Prefix for tables Crawled by GLue Crawler

###########################################################################
# Mandatory tags that will be added to all resources that support tags
###########################################################################
  
  TagName:
    Type: String
    Description: Unique friendly name as required by the your company tagging strategy document and will be added to tag.
    Default: 'RedshiftLab'

  TagEnvironment:
    Type: String
    AllowedValues:
      - Development
      - Test
      - Integration
      - Production
    Description: The environment key is used to designate the production level of the associated AWS resource.
    Default: Development

  TagApplication:
    Type: String
    Description: The application key is used to designate the application of the associated AWS resource. In this capacity ?application? does not refer to an installed software component, but rather the overall business application that the resource supports.
    Default: 'RedshiftLabApp'
    ConstraintDescription: must contain only alphabetic characters in lower case.

  TagApplicationVersion:
    Type: String
    Description: The version key is used to designate the specific version of the application.  Format should be Pattern - "#.#.#"
    Default: '1.0.0'

  TagInfrastructureVersion:
    Type: String
    Description: The version key is used to designate the specific version of the underlying infrastructure.  Format should be Pattern - "#.#.#"
    Default: '1.0.0'
    
  TagTier:
    Type: String
    AllowedValues:
      - data
      - web
      - application
    Description: The tier key is used to designate the functional tier of the associated AWS resource.
    Default: data

  TagProjectCostCenter:
    Type: String
    Description: The cost center key is used to designate the your company cost center associated with project of the given AWS resource.
    Default: '12345'
    
  TagOwner:
    Type: String
    Description: The owner key is used to designate the your company individual associated with the given AWS resource.
    Default: 'awspsa-redshift-team@amazon.com'
  
  TagSecurityContact:
    Type: String
    Description: The owner key is used to designate the your company individual associated with the given AWS resource.
    Default: 'awspsa-redshift-team@amazon.com'
    
  TagConfidentiality:
    Type: String
    AllowedValues:
      - Public
      - Private
      - Confidential
      - PII/UCI
      - None
    Description: The Confidentiality tag is used to designate the confidentiality classification of the data that is associated with the resource.
    Default: None
    
  TagCompliance:
    Type: String
    AllowedValues:
      - HIPAA
      - SOX
      - FIPS
      - PCI
      - Other
      - None
    Description: The Compliance tag is used to specific the Compliance level for the resource.
    Default: None

###############################################################################
# Parameter groups
###############################################################################

Metadata:
  AWS::CloudFormation::Interface:
    ParameterGroups:
      -
        Label:
          default: Environment of Application
        Parameters:
          - TagEnvironment
      -
        Label:
          default: VPC Parameters
        Parameters:
          - VPCCIDR
          - PublicSubnet1
          - PublicSubnet2
          - destCIDRpublic  
      -
        Label:
          default: Redshift Cluster Parameters
        Parameters:
          - DatabaseName
          - MasterUsername
          - MasterUserPassword
          - ClusterType
          - NumberOfNodes
          - NodeType
          - PortNumber
          - Maintenancewindow
          - SubscriptionEmail
          - kmskey
          - S3BucketForSpectrum
          - GlueCatalogDatabase
          - GlueCrawlerName
          - SpectrumTableName1
          - GlueTablePrefix           
      -
        Label:
          default: Mandatory Custom Tags
        Parameters:
          - TagName
          - TagEnvironment
          - TagApplication
          - TagApplicationVersion
          - TagInfrastructureVersion
          - TagTier
          - TagProjectCostCenter
          - TagOwner
          - TagSecurityContact
          - TagConfidentiality
          - TagCompliance

###############################################################################
# Mapping
###############################################################################
Mappings:
  Environments:
    Production:
      ShortCode: p
    Test:
      ShortCode: t
    Integration:
      ShortCode: i
    Development:
      ShortCode: d

###############################################################################
# Conditions
###############################################################################
Conditions:
  IsMultiNodeCluster: !Equals [!Ref ClusterType, 'multi-node']
  CreateProdResources: !Equals [ !Ref TagEnvironment, 'Production']
  CreateDevResources: !Not [!Equals [!Ref TagEnvironment, Production]]
  IsSnapshotSpecified:
    Fn::Not:
    - Fn::Equals:
      - ''
      - Ref: SnapshotIdentifierName
  IsSnapshotAccountSpecified:
    Fn::Not:
    - Fn::Equals:
      - ''
      - Ref: SnapshotAccountNumber

###############################################################################
# Resources
###############################################################################   
Resources:
#Create virtual private network. 
  VPC:
    Type: 'AWS::EC2::VPC'
    Properties:
      InstanceTenancy: default
      EnableDnsSupport: true
      EnableDnsHostnames: true
      CidrBlock: !Ref VPCCIDR
      Tags:
        - 
          Key: Name
          Value: !Join [ "-", [ !Ref TagName, "VPC" ] ]
        -
          Key: Environment
          Value: !Ref TagEnvironment
        -
          Key: ProjectCostCenter
          Value: !Ref TagProjectCostCenter
          
  VPCPublicSubnet1:
    Type: 'AWS::EC2::Subnet'
    Properties:
      VpcId: !Ref VPC
      AvailabilityZone: !Select 
        - '0'
        - !GetAZs ''
      CidrBlock: !Ref PublicSubnet1
      Tags:
        - Key: Name
          Value: PublicSubnet1
        - Key: Network
          Value: Public
        -
          Key: Environment
          Value: !Ref TagEnvironment
        -
          Key: ProjectCostCenter
          Value: !Ref TagProjectCostCenter
                  
  VPCPublicSubnet2:
    Type: 'AWS::EC2::Subnet'
    Properties:
      VpcId: !Ref VPC
      AvailabilityZone: !Select 
        - '1'
        - !GetAZs ''
      CidrBlock: !Ref PublicSubnet2
      Tags:
        - Key: Name
          Value: PublicSubnet2
        - Key: Network
          Value: Public
        -
          Key: Environment
          Value: !Ref TagEnvironment
        -
          Key: ProjectCostCenter
          Value: !Ref TagProjectCostCenter       
          
  InternetGateway:
    Type: 'AWS::EC2::InternetGateway'
    Properties:
      Tags:
        - Key: Name
          Value: IGW
        -
          Key: Environment
          Value: !Ref TagEnvironment
        -
          Key: ProjectCostCenter
          Value: !Ref TagProjectCostCenter
          
  AttachGateway:
    Type: 'AWS::EC2::VPCGatewayAttachment'
    Properties:
      VpcId: !Ref VPC
      InternetGatewayId: !Ref InternetGateway
      
  PublicRoutetable:
    Type: 'AWS::EC2::RouteTable'
    Properties:
      VpcId: !Ref VPC
      Tags:
        - Key: Application
          Value: VPC
        - Key: Name
          Value: PublicRoutetable
        - Key: Network
          Value: Private
        -
          Key: Environment
          Value: !Ref TagEnvironment
        -
          Key: ProjectCostCenter
          Value: !Ref TagProjectCostCenter
          
  Publicroute:
    Type: 'AWS::EC2::Route'
    DependsOn: InternetGateway
    Properties:
      RouteTableId: !Ref PublicRoutetable
      DestinationCidrBlock: !Ref destCIDRpublic
      GatewayId: !Ref InternetGateway
      
  Public1RTAssoc:
    Type: 'AWS::EC2::SubnetRouteTableAssociation'
    Properties:
      SubnetId: !Ref VPCPublicSubnet1
      RouteTableId: !Ref PublicRoutetable
  Public2RTAssoc:
    Type: 'AWS::EC2::SubnetRouteTableAssociation'
    Properties:
      SubnetId: !Ref VPCPublicSubnet2
      RouteTableId: !Ref PublicRoutetable
      
  S3Endpoint:
    Type: AWS::EC2::VPCEndpoint
    Properties:
        PolicyDocument:
            Version: 2012-10-17
            Statement:
                - Effect: Allow
                  Principal: '*'
                  Action:
                    - 's3:GetObject'
                  Resource: '*'
        RouteTableIds:
            - !Ref PublicRoutetable
        ServiceName:  !Join [ "", ["com.amazonaws.",!Ref 'AWS::Region',".s3"] ]
        VpcId: !Ref VPC
          
  LambdaSecurityGroup:
    Type: 'AWS::EC2::SecurityGroup'
    Properties:
      VpcId: !Ref VPC
      GroupDescription: Security Group for AWS Lambda function to launch query in the Redshift cluster
          
  SecurityGroup:
    Type: 'AWS::EC2::SecurityGroup'
    Properties:
      VpcId: !Ref VPC
      GroupDescription: Security Group for the Redshift cluster
      SecurityGroupIngress:
        - IpProtocol: tcp
          FromPort: !Ref PortNumber
          ToPort: !Ref PortNumber
          CidrIp: 205.251.234.0/24
        - IpProtocol: tcp
          FromPort: !Ref PortNumber
          ToPort: !Ref PortNumber
          SourceSecurityGroupId: !Ref LambdaSecurityGroup
  sns:
    Type: 'AWS::SNS::Topic'
    Properties:
      Subscription:
        - Endpoint: !Ref SubscriptionEmail
          Protocol: Email
      TopicName: !Join [ "-", [!Ref 'AWS::StackName',"sns"] ]

###            
#Create IAM Role assumed by the crawler and Redshift Spectrum.
  CFNMySpectrumRole:
    Type: AWS::IAM::Role
    Properties:
      RoleName: !Join [ "-", [!Ref 'AWS::StackName', "SpectrumRole"] ]
      AssumeRolePolicyDocument:
        Version: "2012-10-17"
        Statement:
          -
            Effect: "Allow"
            Principal:
              Service:
                - "redshift.amazonaws.com"
                - "glue.amazonaws.com"
            Action:
              - "sts:AssumeRole"
      Path: "/"
      Policies:
        -
          PolicyName: "spectrum-glue-required-access-policy"
          PolicyDocument:
            Version: "2012-10-17"
            Statement:
              -
                Effect: "Allow"
                Action:
                    - s3:GetBucketLocation
                    - s3:GetObject
                    - s3:ListMultipartUploadParts
                    - s3:ListBucket
                    - s3:ListBucketMultipartUploads
                Resource:
                    - !Join ['', ["arn:aws:s3:::", !Ref S3BucketForSpectrum]]
                    - !Join ['', ["arn:aws:s3:::", !Ref S3BucketForSpectrum, "/*"]]
#                    - arn:aws:s3:::sudhig-tph-1000-data
#                    - arn:aws:s3:::sudhig-tph-1000-data/*
              -
                Effect: Allow
                Action:
                    - glue:CreateDatabase
                    - glue:DeleteDatabase
                    - glue:GetDatabase
                    - glue:GetDatabases
                    - glue:UpdateDatabase
                    - glue:CreateTable
                    - glue:DeleteTable
                    - glue:BatchDeleteTable
                    - glue:UpdateTable
                    - glue:GetTable
                    - glue:GetTables
                    - glue:BatchCreatePartition
                    - glue:CreatePartition
                    - glue:DeletePartition
                    - glue:BatchDeletePartition
                    - glue:UpdatePartition
                    - glue:GetPartition
                    - glue:GetPartitions
                    - glue:BatchGetPartition
                Resource:
                    - "*"

#If you want to create new S3 Bucket for spectrum then uncomment 
#  S3BucketForSpectrum:
#    Type: "AWS::S3::Bucket"
#    DeletionPolicy: Delete
#    Properties:
#        AccessControl: Private
#        BucketName: !Join ['', [!Ref SpectrumBucket, !Ref GlueCatalogDB, ]]
                                 
  RedshiftClusterParameterGroup:
    Type: 'AWS::Redshift::ClusterParameterGroup'
    Properties:
      Description: Cluster parameter group
      ParameterGroupFamily: redshift-1.0
      Parameters:
        - ParameterName: enable_user_activity_logging
          ParameterValue: 'true'
        - ParameterName: require_ssl
          ParameterValue: 'true'
        - ParameterName: "wlm_json_configuration"
          ParameterValue: "[{\"user_group\":[\"etlusers\"],\"query_group\":[\"biextract\"],\"query_concurrency\":5,\"max_execution_time\":1800000,\"memory_percent_to_use\":40},{\"user_group\":[\"rousers\"],\"query_concurrency\":10,\"max_execution_time\":120000,\"memory_percent_to_use\":30},{\"query_concurrency\":5,\"max_execution_time\":300000,\"memory_percent_to_use\":30}]"
      Tags:
        -
          Key: Name
          Value: !Join [ "-", [ !Ref TagName, "Primary Cluster Parameter group" ] ]
        -
          Key: Environment
          Value: !Ref TagEnvironment
        -
          Key: Application
          Value: !Ref TagApplication
        -
          Key: ApplicationVersion
          Value: !Ref TagApplicationVersion
        -
          Key: InfrastructureVersion
          Value: !Ref TagInfrastructureVersion
        -
          Key: Tier
          Value: !Ref TagTier
        -
          Key: ProjectCostCenter
          Value: !Ref TagProjectCostCenter
        -
          Key: Owner
          Value: !Ref TagOwner

                      
  RedshiftClusterSubnetGroup:
    Type: 'AWS::Redshift::ClusterSubnetGroup'
    Properties:
      Description: Cluster subnet group
      SubnetIds:
        - !Ref VPCPublicSubnet1
        - !Ref VPCPublicSubnet2
      Tags:
        -
          Key: Name
          Value: !Join [ "-", [ !Ref TagName, !Ref 'AWS::StackName', "Primary Redshift Cluster Subnet group" ] ]
        -
          Key: Environment
          Value: !Ref TagEnvironment
        -
          Key: Application
          Value: !Ref TagApplication
        -
          Key: ApplicationVersion
          Value: !Ref TagApplicationVersion
        -
          Key: InfrastructureVersion
          Value: !Ref TagInfrastructureVersion
        -
          Key: Tier
          Value: !Ref TagTier
        -
          Key: ProjectCostCenter
          Value: !Ref TagProjectCostCenter
        -
          Key: Owner
          Value: !Ref TagOwner

  RedshiftCluster:
    Type: 'AWS::Redshift::Cluster'
    DependsOn: CFNMySpectrumRole
    Properties:
      ClusterType: !Ref ClusterType
      NumberOfNodes: !If 
        - IsMultiNodeCluster
        - !Ref NumberOfNodes
        - !Ref 'AWS::NoValue'
      NodeType: !Ref NodeType
      DBName: !Ref DatabaseName
      KmsKeyId: !Ref kmskey
      Encrypted: 'true'
      Port: !Ref PortNumber
      MasterUsername: !Ref MasterUsername
      MasterUserPassword: !Ref MasterUserPassword
      ClusterParameterGroupName: !Ref RedshiftClusterParameterGroup
      SnapshotIdentifier: !If 
        - IsSnapshotSpecified
        - !Ref SnapshotIdentifierName
        - !Ref 'AWS::NoValue'
      OwnerAccount: !If 
        - IsSnapshotAccountSpecified
        - !Ref SnapshotAccountNumber
        - !Ref 'AWS::NoValue'
      VpcSecurityGroupIds:
        - !Ref SecurityGroup
      PreferredMaintenanceWindow: !Ref Maintenancewindow
      PubliclyAccessible: 'true'
      ClusterSubnetGroupName: !Ref RedshiftClusterSubnetGroup
      IamRoles:
        - 'Fn::GetAtt':
            - CFNMySpectrumRole
            - Arn
      Tags:
        -
          Key: Name
          Value: !Join [ "-", [ !Ref TagName, !Ref 'AWS::StackName', "Redshift-Cluster" ] ]
        -
          Key: Environment
          Value: !Ref TagEnvironment
        -
          Key: Application
          Value: !Ref TagApplication
        -
          Key: ApplicationVersion
          Value: !Ref TagApplicationVersion
        -
          Key: InfrastructureVersion
          Value: !Ref TagInfrastructureVersion
        -
          Key: Tier
          Value: !Ref TagTier
        -
          Key: ProjectCostCenter
          Value: !Ref TagProjectCostCenter
        -
          Key: Owner
          Value: !Ref TagOwner
        -
          Key: Confidentiality
          Value: !Ref TagConfidentiality
        -
          Key: Compliance
          Value: !Ref TagCompliance
            
  DiskSpacealarmredshift:
    Type: 'AWS::CloudWatch::Alarm'
    DependsOn: RedshiftCluster
    Properties:
      MetricName: !Join 
        - ''
        - - !Ref RedshiftCluster
          - High-PercentageDiskSpaceUsed
      AlarmDescription: !Join 
        - ''
        - - DiskSpace Utilization > 85% for
          - !Ref RedshiftCluster
      Namespace: AWS/Redshift
      Statistic: Average
      Period: '300'
      EvaluationPeriods: '3'
      Threshold: '85'
      AlarmActions:
        - !Ref sns
      Dimensions:
        - Name: ClusterIdentifier
          Value: !Ref RedshiftCluster
      ComparisonOperator: GreaterThanThreshold
      Unit: Percent
      
  HighCPUutilizationalarmredshift:
    Type: 'AWS::CloudWatch::Alarm'
    DependsOn: RedshiftCluster
    Condition: CreateProdResources
    Properties:
      MetricName: !Join 
        - ''
        - - !Ref RedshiftCluster
          - High-CPUUtilization
      AlarmDescription: !Join 
        - ''
        - - CPUUtilization > 95% for last 30 min for cluster
          - !Ref RedshiftCluster
      Namespace: AWS/Redshift
      Statistic: Average
      Period: '900'
      EvaluationPeriods: '2'
      Threshold: '95'
      AlarmActions:
        - !Ref sns
      Dimensions:
        - Name: ClusterIdentifier
          Value: !Ref RedshiftCluster
      ComparisonOperator: GreaterThanThreshold
      Unit: Percent
      
  HighReadLatencyalarmredshift:
    Type: 'AWS::CloudWatch::Alarm'
    Condition: CreateProdResources
    DependsOn: RedshiftCluster
    #Condition: IsProd
    Properties:
      MetricName: !Join 
        - ''
        - - !Ref RedshiftCluster
          - High-ReadLatency
      AlarmDescription: !Join 
        - ''
        - - ReadLatency is high for
          - !Ref RedshiftCluster
      Namespace: AWS/Redshift
      Statistic: Average
      Period: '300'
      EvaluationPeriods: '3'
      Threshold: '0.3'
      AlarmActions:
        - !Ref sns
      Dimensions:
        - Name: ClusterIdentifier
          Value: !Ref RedshiftCluster
      ComparisonOperator: GreaterThanThreshold
      Unit: Percent

###      
# Create an AWS Glue database
  GlueCatalogDB:
    Type: 'AWS::Glue::Database'
    Properties:
      CatalogId: !Ref AWS::AccountId
      DatabaseInput:
          Name: !Ref GlueCatalogDatabase
          Description: AWS Glue Catalog database to hold metadata.

###
#  Create an AWS Glue table from a known path s3://crawler-public-us-east-1/flight/2016/csv/. Replace path, if needed.
  CFNTableFlights:
    # Creating the table waits for the Glue catalog database to be created
    DependsOn: GlueCatalogDB
    Condition: CreateDevResources
    Type: AWS::Glue::Table
    Properties:
      CatalogId: !Ref AWS::AccountId
      DatabaseName: !Ref GlueCatalogDatabase
      TableInput:
        Name: !Ref SpectrumTableName1
        Description: Define the first few columns of the flights table
        TableType: EXTERNAL_TABLE
        Parameters: {
    "classification": "csv"
  }
#       ViewExpandedText: String
        PartitionKeys:
        # Data is partitioned by month
        - Name: mon
          Type: bigint
        StorageDescriptor:
          OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
          Columns:
          - Name: year
            Type: bigint
          - Name: quarter
            Type: bigint
          - Name: month
            Type: bigint
          - Name: day_of_month
            Type: bigint			
          InputFormat: org.apache.hadoop.mapred.TextInputFormat
          Location: s3://crawler-public-us-east-1/flight/2016/csv/
          SerdeInfo:
            Parameters:
              field.delim: ","
            SerializationLibrary: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
# Partition 1
# Create an AWS Glue partition  
  CFNPartitionMon1:
    DependsOn: CFNTableFlights
    Condition: CreateDevResources
    Type: AWS::Glue::Partition
    Properties:
      CatalogId: !Ref AWS::AccountId
      DatabaseName: !Ref GlueCatalogDatabase
      TableName: !Ref SpectrumTableName1
      PartitionInput:
        Values:
        - 1
        StorageDescriptor:
          OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
          Columns:
          - Name: mon
            Type: bigint
          InputFormat: org.apache.hadoop.mapred.TextInputFormat
          Location: s3://crawler-public-us-east-1/flight/2016/csv/mon=1/
          SerdeInfo:
            Parameters:
              field.delim: ","
            SerializationLibrary: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
# Partition 2
# Create an AWS Glue partition 
  CFNPartitionMon2:
    DependsOn: CFNTableFlights
    Condition: CreateDevResources
    Type: AWS::Glue::Partition
    Properties:
      CatalogId: !Ref AWS::AccountId
      DatabaseName: !Ref GlueCatalogDatabase
      TableName: !Ref SpectrumTableName1
      PartitionInput:
        Values:
        - 2
        StorageDescriptor:
          OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
          Columns:
          - Name: mon
            Type: bigint
          InputFormat: org.apache.hadoop.mapred.TextInputFormat
          Location: s3://crawler-public-us-east-1/flight/2016/csv/mon=2/
          SerdeInfo:
            Parameters:
              field.delim: ","
            SerializationLibrary: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

# Partition 3
# Create an AWS Glue partition 
  CFNPartitionMon3:
    DependsOn: CFNTableFlights
    Condition: CreateDevResources
    Type: AWS::Glue::Partition
    Properties:
      CatalogId: !Ref AWS::AccountId
      DatabaseName: !Ref GlueCatalogDatabase
      TableName: !Ref SpectrumTableName1
      PartitionInput:
        Values:
        - 3
        StorageDescriptor:
          OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
          Columns:
          - Name: mon
            Type: bigint
          InputFormat: org.apache.hadoop.mapred.TextInputFormat
          Location: s3://crawler-public-us-east-1/flight/2016/csv/mon=3/
          SerdeInfo:
            Parameters:
              field.delim: ","
            SerializationLibrary: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

 #Create a crawler to crawl the flights data on a public S3 bucket
  CFNCrawlerFlights:
    Type: AWS::Glue::Crawler
    Condition: CreateDevResources
    Properties:
      Name: !Ref GlueCrawlerName
      Role: !GetAtt CFNMySpectrumRole.Arn
      #Classifiers: none, use the default classifier
      Description: AWS Glue crawler to crawl flights data
      #Schedule: none, use default run-on-demand
      DatabaseName: !Ref GlueCatalogDatabase
      Targets:
        S3Targets:
          # Public S3 bucket with the flights data
          - Path: "s3://crawler-public-us-east-1/flight/2016/csv"
      TablePrefix: !Ref GlueTablePrefix
      SchemaChangePolicy:
        UpdateBehavior: "UPDATE_IN_DATABASE"
        DeleteBehavior: "LOG"

###############################################################################
# Output Parameters
###############################################################################     
Outputs:

  VPCID:
    Description: Created VPC (VPC-ID)
    Value: !Ref VPC
    Export: 
        Name: !Sub "${AWS::StackName}-VPC"

  VPCPublicSubnetID1:
    Description: Created VPCPublicSubnet1 
    Value: !Ref VPCPublicSubnet1
    Export: 
        Name: !Sub "${AWS::StackName}-VPCPublicSubnet1"
    
  VPCPublicSubnetID2:
    Description: Created VPCPublicSubnet2
    Value: !Ref VPCPublicSubnet2
    Export:  
        Name: !Sub "${AWS::StackName}-VPCPublicSubnet2"
        
  ClusterEndpoint:
    Description: Redshift Cluster endpoint
    Value: !Join 
      - ':'
      - - !GetAtt 
          - RedshiftCluster
          - Endpoint.Address
        - !GetAtt 
          - RedshiftCluster
          - Endpoint.Port
          
  RedshiftClusterName:
    Description: Name of the Redshift Cluster
    Value: !Ref RedshiftCluster
    
  RedshiftParameterGroupName:
    Description: Name of the Redshift Parameter Group
    Value: !Ref RedshiftClusterParameterGroup
    
  RedshiftClusterSubnetGroupName:
    Description: Name of the Cluster Subnet Group
    Value: !Ref RedshiftClusterSubnetGroup
    
  RedshiftDatabaseName:
    Description: Name of the Redshift Database
    Value: !Ref DatabaseName
    
  RedshiftClusterIAMRole:
    Description: IAM Role assigned to Redshift cluster & Glue Catalog
    Value: !GetAtt CFNMySpectrumRole.Arn

  GlueCatalogDBName:
    Description: Name of the AWS Glue Catalog Database
    Value: !Ref GlueCatalogDB

  GlueCrawlerName:
    Description: Name of the AWS Glue Crawler
    Condition: CreateDevResources
    Value: !Ref CFNCrawlerFlights

  GlueCatalogTable1:
    Description: Name of the external table (Flight table) created in GlueCatalog for Spectrum
    Condition: CreateDevResources
    Value: !Ref CFNTableFlights

  GlueCatalogTable2:
    Description: Name of the external table created in GlueCatalog using Glue Crawler
    Condition: CreateDevResources
    Value: !Ref CFNCrawlerFlights