Querying S3 Logs with Athena and Kinesis Data Firehose

Deliver logs to S3 with Kinesis Data Firehose and query them in Athena, using custom prefixes to register partitions automatically.

Takahiro Iwasa

2019-08-31

3 min read

Athena lets you run SQL queries directly against objects stored in S3 without provisioning servers. This article describes a typical pipeline that delivers logs to S3 through Kinesis Data Firehose and queries them with Athena.

System Architecture

Creating a Kinesis Data Firehose Delivery Stream

This approach uses custom S3 prefixes, which were introduced in February 2019. Firehose can add Apache Hive-style prefixes to S3 object keys, allowing MSCK REPAIR TABLE to create the corresponding Athena partitions automatically.

Choose Create delivery stream and enter a name.

Choose Direct PUT or other sources.

Skip the record processing settings.

Select Amazon S3 as the destination.

Configure the prefix and error prefix with the following format:

Prefix: logs/!{timestamp:'year='yyyy'/month='MM'/day='dd'/hour='HH}/
Error Prefix: error_logs/!{timestamp:'year='yyyy'/month='MM'/day='dd'/hour='HH}/!{firehose:error-output-type}

Adjust Buffer size and Buffer interval based on your requirements.

Use GZIP compression to reduce storage costs.

Create or select an IAM role for the delivery stream.

Streaming Data

To send data to the Kinesis Data Firehose delivery stream programmatically, use the AWS SDK for PHP: FirehoseClient#putRecord method:

1
$client = new FirehoseClient([
2
    'region' => '<AWS_REGION>',
3
    'version' => 'latest',
4
]);
5

6
$data = [
7
    'log_id' => 12345,
8
    'url' => 'https://example.com',
9
];
10

11
$client->putRecord([
12
    'DeliveryStreamName' => '<YOUR_STREAM>',
13
    'Record' => [
14
        'Data' => json_encode($data) . PHP_EOL,
15
    ],
16
]);

Creating an Athena Table

Select Create table from S3 bucket data in the Athena console.

Enter a database name, table name, and the S3 path used by Firehose.

Specify JSON as the data format.

Define columns based on your data structure.

Configure partitions (e.g., year/month/day/hour) to improve query performance.

💡 Tip

Partitioning minimizes the data scanned, significantly reducing costs.

Load the partitions with the following command:

1
MSCK REPAIR TABLE {TABLE_NAME};

Querying the Athena Table

With partitions in place, queries can target specific date ranges instead of scanning the entire dataset:

1
SELECT
2
  *
3
FROM
4
  table_name
5
WHERE
6
  year = 2019
7
  AND month = 8
8
  AND day = 30
9
LIMIT 10;

Best Practices:

Always include partition keys in the WHERE clause.
Use the LIMIT statement to avoid unnecessary scans.

Conclusion

This pipeline delivers logs to S3 through Kinesis Data Firehose and makes them queryable in Athena without provisioning database infrastructure. Custom S3 prefixes tie the services together: when objects are written under year=/month=/day=/hour= prefixes, MSCK REPAIR TABLE can discover and register the partitions instead of requiring an ALTER TABLE ... ADD PARTITION statement for each new hour of logs.

GZIP compression on the Firehose delivery stream reduces storage usage, while partition pruning in the WHERE clause reduces the amount of data scanned by each query. This is particularly important because Athena charges according to the number of bytes scanned.

As data volume grows, adjust the Firehose buffer size and interval. Larger buffers produce fewer, larger S3 objects, which generally improves Athena query performance compared with storing many small files.