Athena views with partition projection

Question

I created a table for query AWS SES event logs on Athena and turn on partition projection for date.

```
TBLPROPERTIES (
 'projection.enabled'='true',
 'projection.date.type'='date',
 'projection.date.format'='yyyy/MM/dd',
 'projection.date.interval'='1',
 'projection.date.interval.unit'='DAYS',
 'projection.date.range'='2024/01/01,NOW',
 'storage.location.template'='s3://aws-ses-event-logs/${date}'
)
```

I want to query SES recipients for SEND event, then it is necessary to flatten them into multiples rows. To query easier , I created a view but the view does not take advantages of partition projection (it scanned all data instead of single date).

My question is what is the correct way to create view with flatten data and take advantage of partition projection?

Please refer to my view query and articles below:
```
CREATE OR REPLACE VIEW ses_send_event AS
SELECT 
	eventtype as eventtype, 
	mail.messageId as mailmessageid,
	mail.timestamp as mailtimestamp,
	mail.source as mailsource,
	mail.sendingAccountId as mailsendingAccountId,
	mail.commonHeaders.subject as mailsubject,
	mail.tags.ses_configurationset as mailses_configurationset,
	mail.tags.ses_source_ip as mailses_source_ip,
	mail.tags.ses_from_domain as mailses_from_domain,
	mail.tags.ses_outgoing_ip as mailses_outgoing_ip,
	send_mail_recipients.recipient as mailto
FROM
	ses_event_logs as sm,
	(
		SELECT
			messageid,
			recipient
		FROM
			(
				SELECT
					mail.messageId as messageid,
					mail.commonHeaders.to as recipients
				FROM
					ses_event_logs
				WHERE 
					eventtype='Send'
			) AS unnest_recipients
			CROSS JOIN UNNEST(unnest_recipients.recipients) t (recipient)
	) AS send_mail_recipients

WHERE
	sm.eventtype = 'Send' AND sm.mail.messageid = send_mail_recipients.messageid
```

Reference:
- [SES data format](https://docs.aws.amazon.com/ses/latest/dg/event-publishing-retrieving-sns-contents.html)
- [Analyze Amazon SES event log ](https://aws.amazon.com/blogs/messaging-and-targeting/analyzing-amazon-ses-event-data-with-aws-analytics-services/)

re:Post Agent · Accepted Answer

Creating a view that takes advantage of partition projection while flattening data for AWS SES event logs requires a careful approach. The main issue with your current view is that it doesn't pass through the partition column (date) from the base table, which is essential for Athena to leverage partition projection.

To create a view that both flattens the data and benefits from partition projection, you should modify your view definition to include the partition column. Here's an improved version of your view:

```sql
CREATE OR REPLACE VIEW ses_send_event AS
SELECT 
sm.eventtype as eventtype, 
sm.mail.messageId as mailmessageid,
sm.mail.timestamp as mailtimestamp,
sm.mail.source as mailsource,
sm.mail.sendingAccountId as mailsendingAccountId,
sm.mail.commonHeaders.subject as mailsubject,
sm.mail.tags.ses_configurationset as mailses_configurationset,
sm.mail.tags.ses_source_ip as mailses_source_ip,
sm.mail.tags.ses_from_domain as mailses_from_domain,
sm.mail.tags.ses_outgoing_ip as mailses_outgoing_ip,
t.recipient as mailto,
sm.date as date  -- Include the partition column
FROM
ses_event_logs sm
CROSS JOIN UNNEST(sm.mail.commonHeaders.to) AS t(recipient)
WHERE
sm.eventtype = 'Send'
```

This modified view definition includes several key changes:

1. It includes the `date` column from the base table, which is crucial for partition projection to work.
2. It simplifies the query by using a single CROSS JOIN UNNEST operation instead of a subquery.
3. It applies the 'Send' event type filter directly in the main query.

When using this view, make sure to include the `date` column in your WHERE clause to take advantage of partition projection. For example:

```sql
SELECT * FROM ses_send_event
WHERE date = '2024/01/15'
```

This query will use partition projection and only scan the data for the specified date.

Remember that Athena doesn't use table properties of views for partition projection configuration. The projection is applied based on the underlying table's properties. By including the partition column in the view and using it in your queries, you ensure that Athena can push down the partition filtering to the base table, thus leveraging the partition projection setup.

**Sources**  
[Use partition projection with Amazon Athena - Amazon Athena](https://docs.aws.amazon.com/athena/latest/ug/partition-projection.html)  
[Create a table for AWS WAF S3 logs in Athena using partition projection - Amazon Athena](https://docs.aws.amazon.com/athena/latest/ug/create-waf-table-partition-projection.html)  
[Create the table for ALB access logs in Athena using partition projection - Amazon Athena](https://docs.aws.amazon.com/athena/latest/ug/create-alb-access-logs-table-partition-projection.html)

Athena views with partition projection

Add your answer

Relevant content