How to Enable Partition Projection in AWS Athena

Takahiro Iwasa

10/8/2021

2 min read

Athena

Athena Partition Projection, introduced in June 2020, eliminates the need to run MSCK REPAIR TABLE to add new partitions manually.

Building Backend

The key point in the CloudFormation template is the GlueTable.TableInput.Parameters section of the GlueTable definition (lines 27–35). This section enables Partition Projection with the following important properties:

projection.enabled: Enables partition projection.
projection.year_month.format: Specifies the date format for partitions.
projection.year_month.range: Defines the range for partitions, supporting NOW with offsets (e.g., NOW+9HOURS).

💡 Tip

The projection.year_month.range property is treated as UTC. If you intend to use a different timezone, you need to specify the time difference using a format like NOW+9HOURS.

For more details, please refer to the official documentation.

1
AWSTemplateFormatVersion: "2010-09-09"
2
Description: Stack for Athena partition projection sample
3
Resources:
4
  S3:
5
    Type: AWS::S3::Bucket
6
    Properties:
7
      BucketName: athena-partition-projection-logs
8
      BucketEncryption:
9
        ServerSideEncryptionConfiguration:
10
          - ServerSideEncryptionByDefault:
11
              SSEAlgorithm: AES256
12

13
  GlueDatabase:
14
    Type: AWS::Glue::Database
15
    Properties:
16
      DatabaseInput:
17
        Name: sample
18
      CatalogId: !Ref AWS::AccountId
19

20
  GlueTable:
21
    Type: AWS::Glue::Table
22
    Properties:
23
      DatabaseName: !Ref GlueDatabase
24
      CatalogId: !Ref AWS::AccountId
25
      TableInput:
26
        TableType: EXTERNAL_TABLE
27
        Parameters:
28
          classification: json
29
          "projection.enabled": true
30
          "projection.year_month.format": yyyy/MM
31
          "projection.year_month.interval": 1
32
          "projection.year_month.interval.unit": MONTHS
33
          "projection.year_month.range": 2021/09,NOW
34
          "projection.year_month.type": date
35
          "storage.location.template": s3://athena-partition-projection-logs/${year_month}
36
        StorageDescriptor:
37
          Columns:
38
            - Name: id
39
              Type: int
40
            - Name: message
41
              Type: string
42
          Location: !Sub s3://${S3}/
43
          InputFormat: org.apache.hadoop.mapred.TextInputFormat
44
          OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
45
          Compressed: false
46
          NumberOfBuckets: 0
47
          SerdeInfo:
48
            SerializationLibrary: org.openx.data.jsonserde.JsonSerDe
49
            Parameters:
50
              paths: id,message
51
          StoredAsSubDirectories: false
52
        PartitionKeys:
53
          - Name: year_month
54
            Type: string
55
        Retention: 0
56
        Name: sample_logs

Deploy the CloudFormation stack with the following command:

aws cloudformation deploy \
  --template-file stack.yml \
  --stack-name athena-partition-projection-sample

Uploading Data

Upload data to the S3 bucket for testing:

echo '{"id": 1, "message": "hello"}' > 2021-09.json
echo '{"id": 2, "message": "world"}' > 2021-10.json
aws s3 cp 2021-09.json s3://athena-partition-projection-logs/2021/09/
aws s3 cp 2021-10.json s3://athena-partition-projection-logs/2021/10/

Verify that the files are uploaded:

aws s3 ls s3://athena-partition-projection-logs/2021/

Querying Data

Run the following SQL query to fetch data from the 2021/09 partition:

1
SELECT * FROM "sample"."sample_logs"
2
WHERE year_month = '2021/09'
3
LIMIT 10;

Expected result:

1
1  hello  2021/09

Similarly, query the 2021/10 partition:

1
SELECT * FROM "sample"."sample_logs"
2
WHERE year_month = '2021/10'
3
LIMIT 10;

Expected result:

1
2  world  2021/10

Cleaning Up

Clean up all the AWS resources provisioned during this example with the following command:

aws s3 rm --recursive s3://athena-partition-projection-logs
aws cloudformation delete-stack --stack-name athena-partition-projection-sample