Partition Projection in AWS Athena

Use Athena partition projection to query new partitions without adding each one to the Glue Data Catalog.

Takahiro Iwasa

2021-10-08

3 min read

Athena partition projection, introduced in June 2020, allows Athena to calculate partition locations instead of retrieving each partition from the Glue Data Catalog. This removes the need to run MSCK REPAIR TABLE whenever a partition is added.

Configuration

The GlueTable.TableInput.Parameters section of the CloudFormation template (lines 27–35) enables partition projection with the following 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

With partition projection enabled on the Glue table, Athena can query newly uploaded year_month partitions without running MSCK REPAIR TABLE.

The combination of projection.enabled: true and projection.year_month.range: 2021/09,NOW makes Athena derive valid partitions from the configured range and format instead of looking them up in the Glue Data Catalog. No catalog entry needs to be added when data for a new month arrives.

The storage.location.template must match the actual S3 key layout. Because NOW is evaluated in UTC, use an explicit offset such as NOW+9HOURS when the projected range must follow a different time zone.

Back to posts