athena-prepared-statements.yaml

AWSTemplateFormatVersion: '2010-09-09'
Parameters:
  S3QueryResultsBucketName:
    Type: String
    Description: Amazon S3 bucket name for storing Amazon Athena query results
  AthenaWorkGroupName:
    Type: String
    Description: (Can be left as default) Amazon Athena WorkGroup name
    Default: "PreparedStatementsWG"
Resources:
  AthenaWorkGroup:
    Type: AWS::Athena::WorkGroup
    Properties:
      Name: !Ref AthenaWorkGroupName
      State: ENABLED
      RecursiveDeleteOption: true
      WorkGroupConfiguration:
        EnforceWorkGroupConfiguration: True
        EngineVersion:
          SelectedEngineVersion: AUTO
        ResultConfiguration:
          EncryptionConfiguration:
            EncryptionOption: SSE_S3
          OutputLocation: !Sub s3://${S3QueryResultsBucketName}/results/
  GlueDatabase:
    Type: AWS::Glue::Database
    Properties:
      CatalogId: !Ref AWS::AccountId
      DatabaseInput:
        Name: athena_prepared_statements
  GlueTableAmazonReviews:
    Type: AWS::Glue::Table
    Properties:
      DatabaseName: !Ref GlueDatabase
      CatalogId: !Ref AWS::AccountId
      TableInput:
        Name: amazon_reviews_parquet
        Parameters:
          EXTERNAL: 'TRUE'
        PartitionKeys:
          - Name: product_category
            Type: string
        StorageDescriptor:
          BucketColumns: []
          Columns:
          - Name: marketplace
            Type: string
          - Name: customer_id
            Type: string
          - Name: review_id
            Type: string
          - Name: product_id
            Type: string
          - Name: product_title
            Type: string
          - Name: star_rating
            Type: bigint
          - Name: helpful_votes
            Type: bigint
          - Name: total_votes
            Type: bigint
          - Name: insight
            Type: string
          - Name: review_headline
            Type: string
          - Name: review_body
            Type: string
          - Name: review_date
            Type: bigint
          - Name: review_year
            Type: bigint
          Compressed: false
          InputFormat: org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat
          Location: s3://aws-bigdata-blog/generated_synthetic_reviews/data/
          NumberOfBuckets: -1
          OutputFormat: org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat
          Parameters: {}
          SerdeInfo:
            Parameters:
              serialization.format: '1'
            SerializationLibrary: org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe
          SkewedInfo:
            SkewedColumnNames: []
            SkewedColumnValueLocationMaps: {}
            SkewedColumnValues: []
          SortColumns: []
          StoredAsSubDirectories: false
        TableType: EXTERNAL_TABLE