Querying S3 Logs with Athena and Kinesis Data Firehose

Piping logs into S3 through Kinesis Data Firehose and querying them with Athena, using custom prefixes so partitions register automatically.

Takahiro Iwasa

2019-08-31

3 min read

Athena lets you run SQL queries directly against objects stored in S3, without provisioning any infrastructure. The setup below covers a typical pipeline: delivering logs into S3 through Kinesis Data Firehose, then querying them with Athena.

System Architecture

Creating Kinesis Data Firehose

Custom S3 prefixes were introduced in February 2019, which makes this possible: Firehose can write Apache Hive-style prefixes for S3 object keys, and those prefixes let MSCK REPAIR TABLE create partitions in Athena automatically.

Press 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

Data can be streamed to the Kinesis Data Firehose delivery stream programmatically. The AWS SDK for PHP: FirehoseClient#putRecord method is one way to do it:

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 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.

Partitions are loaded with the following command:

1
MSCK REPAIR TABLE {TABLE_NAME};

Querying 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 delivered logs into S3 through Kinesis Data Firehose and made them queryable in Athena without provisioning any database infrastructure. The custom S3 prefix feature is what ties it together: writing objects under year=/month=/day=/hour= prefixes means MSCK REPAIR TABLE can discover and register partitions on its own, instead of requiring ALTER TABLE ... ADD PARTITION for every new hour of logs. GZIP compression on the Firehose delivery stream and partition pruning in the WHERE clause work together to keep both storage and per-query scan costs down, which matters directly since Athena bills by bytes scanned. The one thing worth tuning as volume grows is the Firehose buffer size and interval — larger buffers produce fewer, bigger S3 objects, which tends to make Athena queries faster than a large number of small files would.