Partition Projection in AWS Athena

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

Takahiro Iwasa

2021-10-08

3 min read

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:

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

Uploading Data

Some test data can then be uploaded to the S3 bucket:

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

Fetching data from the 2021/09 partition confirms the projection works:

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

Expected result:

1
1  hello  2021/09

The 2021/10 partition behaves the same way:

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

Expected result:

1
2  world  2021/10

Cleaning Up

Remove the resources provisioned by this example with:

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

Conclusion

Deploying a Glue table with partition projection enabled let Athena query newly uploaded year_month partitions immediately, with no MSCK REPAIR TABLE step in between. Setting projection.enabled: true alongside projection.year_month.range: 2021/09,NOW means Athena computes which partitions are valid mathematically, from the range and format, rather than looking them up in the Glue catalog — so there’s no catalog entry to update as each new month’s data lands. That computation depends entirely on the storage.location.template matching the actual S3 key layout, and since NOW is evaluated in UTC, any range meant to track a local calendar month needs an explicit offset like NOW+9HOURS or the projected partitions will drift from the data by however many hours the timezone is off.

Back to posts