AWSTemplateFormatVersion: "2010-09-09"
Metadata:
    Generator: "former2"
Description: ""
Parameters:
  BucketProductsAccount:
    Type: String    
    Description: "Enter the S3 source buckets created by the ProductsAccount CF"

  BucketExternalAccount:
    Type: String    
    Description: "Enter the S3 source buckets created by the ExternalAccount CF"

  createTableRole:
    Type: String    
    Description: "Enter the role arn that will be used to create tables and databases"

Resources:    

  executeQueryFunction:
    Type: AWS::Lambda::Function
    Properties:
      Role: !Ref createTableRole
      Runtime: "python2.7"
      Timeout: 300
      Handler: "index.handler"
      Code: 
        ZipFile: |
          import boto3
          import cfnresponse
          import time
          client = boto3.client('athena')
          def handler(event, context):
              if event['RequestType'] != 'Delete':
                try:
                    query = event['ResourceProperties']['QueryString']
                    queryoutput = event['ResourceProperties']['queryoutput']
                    response = client.start_query_execution(QueryString=query, ResultConfiguration={'OutputLocation': queryoutput})
                except Exception as e:
                    print(e)
                    cfnresponse.send(event, context, cfnresponse.SUCCESS, {}, "CustomResourcePhysicalID")
              cfnresponse.send(event, context, cfnresponse.SUCCESS, {}, "CustomResourcePhysicalID")
              return "Success"


  executeCrawlerFunction:
      Type: AWS::Lambda::Function
      Properties:
        Role: !GetAtt 'executeQueryRole.Arn'
        Runtime: "python2.7"
        Timeout: 300
        Handler: "index.handler"
        Code: 
          ZipFile: |
              import boto3
              import cfnresponse
              import time
                  
              client = boto3.client('glue')
                  
              def handler(event, context):
                  print (event)

                  if event['RequestType'] != 'Delete':
                      try:
                          name = event['ResourceProperties']['Name']
                          response = client.start_crawler(Name=event['ResourceProperties']['Name'])
                          print(event['ResourceProperties']['Name'])
                          print ("exito")
                          cfnresponse.send(event, context, cfnresponse.SUCCESS, {}, "CustomResourcePhysicalID")
                      except Exception as e:
                          print(e)
                          cfnresponse.send(event, context, cfnresponse.FAILURE, {}, "CustomResourcePhysicalID")
                  else :
                    cfnresponse.send(event, context, cfnresponse.SUCCESS, {}, "CustomResourcePhysicalID")
                    print ('delete')
                  return "Success"
  
  MyCustomResource: 
      Type: "Custom::TestLambdaCrossStackRef"
      DependsOn: gluee88e99c
      Properties: 
        ServiceToken:
          !GetAtt 'executeCrawlerFunction.Arn'
        Name: "amazonreviews"

  executeCreateTable1Query:
    Type: Custom::executeAthenaQuery
    Properties:
      ServiceToken: !GetAtt executeQueryFunction.Arn
      queryoutput:  !Join ["",[ "s3://", !Ref BucketExternalAccount, "/queryresults" ] ]
      QueryString: !Sub |
                    CREATE EXTERNAL TABLE `gdelt.events`(
                        `globaleventid` int COMMENT '', 
                        `day` int COMMENT '', 
                        `monthyear` int COMMENT '', 
                        `year` int COMMENT '', 
                        `fractiondate` float COMMENT '', 
                        `actor1code` string COMMENT '', 
                        `actor1name` string COMMENT '', 
                        `actor1countrycode` string COMMENT '', 
                        `actor1knowngroupcode` string COMMENT '', 
                        `actor1ethniccode` string COMMENT '', 
                        `actor1religion1code` string COMMENT '', 
                        `actor1religion2code` string COMMENT '', 
                        `actor1type1code` string COMMENT '', 
                        `actor1type2code` string COMMENT '', 
                        `actor1type3code` string COMMENT '', 
                        `actor2code` string COMMENT '', 
                        `actor2name` string COMMENT '', 
                        `actor2countrycode` string COMMENT '', 
                        `actor2knowngroupcode` string COMMENT '', 
                        `actor2ethniccode` string COMMENT '', 
                        `actor2religion1code` string COMMENT '', 
                        `actor2religion2code` string COMMENT '', 
                        `actor2type1code` string COMMENT '', 
                        `actor2type2code` string COMMENT '', 
                        `actor2type3code` string COMMENT '', 
                        `isrootevent` boolean COMMENT '', 
                        `eventcode` string COMMENT '', 
                        `eventbasecode` string COMMENT '', 
                        `eventrootcode` string COMMENT '', 
                        `quadclass` int COMMENT '', 
                        `goldsteinscale` float COMMENT '', 
                        `nummentions` int COMMENT '', 
                        `numsources` int COMMENT '', 
                        `numarticles` int COMMENT '', 
                        `avgtone` float COMMENT '', 
                        `actor1geo_type` int COMMENT '', 
                        `actor1geo_fullname` string COMMENT '', 
                        `actor1geo_countrycode` string COMMENT '', 
                        `actor1geo_adm1code` string COMMENT '', 
                        `actor1geo_lat` float COMMENT '', 
                        `actor1geo_long` float COMMENT '', 
                        `actor1geo_featureid` int COMMENT '', 
                        `actor2geo_type` int COMMENT '', 
                        `actor2geo_fullname` string COMMENT '', 
                        `actor2geo_countrycode` string COMMENT '', 
                        `actor2geo_adm1code` string COMMENT '', 
                        `actor2geo_lat` float COMMENT '', 
                        `actor2geo_long` float COMMENT '', 
                        `actor2geo_featureid` int COMMENT '', 
                        `actiongeo_type` int COMMENT '', 
                        `actiongeo_fullname` string COMMENT '', 
                        `actiongeo_countrycode` string COMMENT '', 
                        `actiongeo_adm1code` string COMMENT '', 
                        `actiongeo_lat` float COMMENT '', 
                        `actiongeo_long` float COMMENT '', 
                        `actiongeo_featureid` int COMMENT '', 
                        `dateadded` int COMMENT '', 
                        `sourceurl` string COMMENT '')
                      ROW FORMAT SERDE 
                        'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' 
                      STORED AS INPUTFORMAT 
                        'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' 
                      OUTPUTFORMAT 
                        'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
                      LOCATION
                        's3://${BucketExternalAccount}/gdeltevents'
                      TBLPROPERTIES (
                        'has_encrypted_data'='false')

  executeCreateTable3Query:
    Type: Custom::executeAthenaQuery
    Properties:
      ServiceToken: !GetAtt executeQueryFunction.Arn
      queryoutput: !Join ["",[ "s3://", !Ref BucketExternalAccount, "/queryresults" ] ]
      QueryString: !Sub |
                  
                    CREATE EXTERNAL TABLE `gdelt.countries`(
                      `code` string, 
                      `country` string)
                    ROW FORMAT DELIMITED 
                      FIELDS TERMINATED BY '\t' 
                    STORED AS INPUTFORMAT 
                      'org.apache.hadoop.mapred.TextInputFormat' 
                    OUTPUTFORMAT 
                      'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
                    LOCATION
                      's3://${BucketExternalAccount}/lakeformationcrossaccount/countries'
                    TBLPROPERTIES (
                      'skip.header.line.count'='1', 
                      'transient_lastDdlTime'='1559938070')

  executeCreateTable4Query:
    Type: Custom::executeAthenaQuery
    Properties:
      ServiceToken: !GetAtt executeQueryFunction.Arn
      queryoutput:  !Join ["",[ "s3://", !Ref BucketExternalAccount, "/queryresults" ] ]
      QueryString: !Sub |
                    CREATE EXTERNAL TABLE `gdelt.eventcodes`(
                      `code` string, 
                      `description` string)
                    ROW FORMAT DELIMITED 
                      FIELDS TERMINATED BY '\t' 
                    STORED AS INPUTFORMAT 
                      'org.apache.hadoop.mapred.TextInputFormat' 
                    OUTPUTFORMAT 
                      'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
                    LOCATION
                      's3://${BucketExternalAccount}/eventcodes/CAMEO.eventcodes.txt'
                    TBLPROPERTIES (
                      'skip.header.line.count'='1', 
                      'transient_lastDdlTime'='1559938024')

  executeCreateTable5Query:
    Type: Custom::executeAthenaQuery
    Properties:
      ServiceToken: !GetAtt executeQueryFunction.Arn
      queryoutput: !Join ["",[ "s3://", !Ref BucketExternalAccount, "/queryresults" ]] 
      QueryString: !Sub |
                    CREATE EXTERNAL TABLE `gdelt.groups`(
                      `group` string, 
                      `description` string)
                    ROW FORMAT DELIMITED 
                      FIELDS TERMINATED BY '\t' 
                    STORED AS INPUTFORMAT 
                      'org.apache.hadoop.mapred.TextInputFormat' 
                    OUTPUTFORMAT 
                      'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
                    LOCATION
                      's3://${BucketExternalAccount}/groups/CAMEO.knowngroup.txt'
                    TBLPROPERTIES (
                      'skip.header.line.count'='1', 
                      'transient_lastDdlTime'='1559938063')

  executeCreateTable6Query:
    Type: Custom::executeAthenaQuery
    Properties:
      ServiceToken: !GetAtt executeQueryFunction.Arn
      queryoutput:  !Join ["",[ "s3://", !Ref BucketExternalAccount, "/queryresults" ] ]
      QueryString: !Sub |
                    CREATE EXTERNAL TABLE `gdelt.types`(
                      `type` string, 
                      `description` string)
                    ROW FORMAT DELIMITED 
                      FIELDS TERMINATED BY '\t' 
                    STORED AS INPUTFORMAT 
                      'org.apache.hadoop.mapred.TextInputFormat' 
                    OUTPUTFORMAT 
                      'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
                    LOCATION
                      's3://${BucketExternalAccount}/types/CAMEO.type.txt'
                    TBLPROPERTIES (
                      'skip.header.line.count'='1', 
                      'transient_lastDdlTime'='1559938044')

  # gdeltDatabase:
  #     Type: AWS::Glue::Database
  #     Properties:
  #       CatalogId: !Ref AWS::AccountId
  #       DatabaseInput:
  #         Name: "gdelt"
  #         Description: "the gdelt Project monitors the world's broadcast, print, and web news from nearly every corner of every country in over 100 languages and identifies the people, locations, organizations, themes, sources, emotions, counts, quotes, images and events driving our global society every second of every day, creating a free open platform for computing on the entire world."
  #         LocationUri: "https://www.gdeltproject.org/"
  #         Parameters:
  #           key1 : "value1"
  #           key2 : "value2"

  # MyDatabase:
  #     Type: AWS::Glue::Database
  #     Properties:
  #       CatalogId: !Ref AWS::AccountId
  #       DatabaseInput:
  #         Name: "amznrevw"
  #         Description: "Database with Amazon Reviews"
  #         LocationUri: "https://registry.opendata.aws/amazon-reviews/"
  #         Parameters:
  #           key1 : "value1"
  #           key2 : "value2"

  iam4558afc:
      Type: AWS::IAM::Role
      Properties:
          Path: "/service-role/"
          RoleName: "AWSGlueServiceRole-rolee2"
          AssumeRolePolicyDocument: "{\"Version\":\"2012-10-17\",\"Statement\":[{\"Effect\":\"Allow\",\"Principal\":{\"Service\":\"glue.amazonaws.com\"},\"Action\":\"sts:AssumeRole\"}]}"
          MaxSessionDuration: 3600

  gluee88e99c:
      Type: AWS::Glue::Crawler
      DependsOn: iam4558afc
      Properties:
          Name: "amazonreviews"
          Role: !Ref createTableRole
          DatabaseName: amznrevw
          Classifiers: []
          Configuration: "{\"Version\":1}"
          TablePrefix: ""
          SchemaChangePolicy: 
              UpdateBehavior: "UPDATE_IN_DATABASE"
          Targets: 
              S3Targets: 
                - 
                  Path: !Join ["",[ "s3://", !Ref BucketProductsAccount, "/reviews2/" ] ]
                  Exclusions: []
              JdbcTargets: []

  executeQueryRole:
      Type: AWS::IAM::Role
      Properties:
        AssumeRolePolicyDocument:
          Version: '2012-10-17'
          Statement:
          - Effect: Allow
            Principal:
              Service:
              - lambda.amazonaws.com
              - glue.amazonaws.com
            Action:
            - sts:AssumeRole
        Path: /
        Policies:
        - PolicyName: root
          PolicyDocument:
            Version: '2012-10-17'
            Statement:
            - Action:
              - s3:*
              - athena:*
              - glue:*
              - logs:CreateLogGroup
              - logs:CreateLogStream
              - logs:PutLogEvents
              Resource: '*'
              Effect: Allow

