Athena ALB Access Logs Suddenly Returning Null Fields Since 2025-08-19

Question

# Short description:

Athena queries on ALB access logs suddenly started returning NULL for all fields (except partitioned ones) since 2025/08/19.

# Problem detail:

Athena queries on ALB access logs suddenly started returning `NULL` for all fields (except partitioned ones) since 2025/08/19.

I created an Athena table with partition projection as described here:  
https://docs.aws.amazon.com/athena/latest/ug/create-alb-access-logs-table-partition-projection.html

It worked fine before, but after 2025/08/19, all non-partition fields became NULL.

Example query and result:

```
-- SQL --
SELECT 
   day,
   count(*) as total_logs,
   sum(case when elb is null then 1 else 0 end) as null_elb_count,
   sum(case when elb is not null then 1 else 0 end) as not_null_elb_count
FROM __MY_ALB_ACCESS_LOGS_ATHENA_TABLE__   
WHERE day >= '2025/08/16' and day <= '2025/08/31'
GROUP BY day
ORDER BY day
```

the query result is just like:

```
-- Result --
day	total_logs	null_elb_count	not_null_elb_count
2025/08/16	31701	0	31701
2025/08/17	52345	0	52345
2025/08/18	40747	0	40747
2025/08/19	39976	1904	38072
2025/08/20	41735	20548	21187
2025/08/21	38892	20201	18691
2025/08/22	36417	36417	0
2025/08/23	28350	28350	0
2025/08/24	28515	28515	0
2025/08/25	33806	33806	0
2025/08/26	32176	32176	0
2025/08/27	30166	30166	0
2025/08/28	33688	33688	0
2025/08/29	33541	33541	0
2025/08/30	44073	44073	0
2025/08/31	31461	31461	0
```

All of our ALB access log Athena tables in this AWS account have the same issue.

Has there been a recent change in ALB log format or Athena support that could cause this?

Is there any change in ALB access logs format or Athena configuration after 2025/08/19? Or did anyone else encounter the same issue?

Riku_Kobayashi · Accepted Answer

Hello.

It seems that around August 19th, a field that is not listed in the AWS documentation was added to the ALB access log.  
This added field is likely causing the query results to be null.

The following blog introduces a Japanese blog that creates a table with the following query, which makes it possible to query logs whose field names are temporarily unknown.  
https://dev.classmethod.jp/articles/alb-log-additional-column-athena-table/

```
CREATE EXTERNAL TABLE IF NOT EXISTS alb_access_logs (
            type string,
            time string,
            elb string,
            client_ip string,
            client_port int,
            target_ip string,
            target_port int,
            request_processing_time double,
            target_processing_time double,
            response_processing_time double,
            elb_status_code int,
            target_status_code string,
            received_bytes bigint,
            sent_bytes bigint,
            request_verb string,
            request_url string,
            request_proto string,
            user_agent string,
            ssl_cipher string,
            ssl_protocol string,
            target_group_arn string,
            trace_id string,
            domain_name string,
            chosen_cert_arn string,
            matched_rule_priority string,
            request_creation_time string,
            actions_executed string,
            redirect_url string,
            lambda_error_reason string,
            target_port_list string,
            target_status_code_list string,
            classification string,
            classification_reason string,
            conn_trace_id string,
            additional_column string
            )
            PARTITIONED BY
            (
             day STRING
            )
            ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
            WITH SERDEPROPERTIES (
            'serialization.format' = '1',
            'input.regex' = 
        '([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*):([0-9]*) ([^ ]*)[:-]([0-9]*) ([-.0-9]*) ([-.0-9]*) ([-.0-9]*) (|[-0-9]*) (-|[-0-9]*) ([-0-9]*) ([-0-9]*) "([^ ]*) (.*) (- |[^ ]*)" "([^"]*)" ([A-Z0-9-_]+) ([A-Za-z0-9.-]*) ([^ ]*) "([^"]*)" "([^"]*)" "([^"]*)" ([-.0-9]*) ([^ ]*) "([^"]*)" "([^"]*)" "([^ ]*)" "([^\s]+?)" "([^\s]+)" "([^ ]*)" "([^ ]*)" ?([^ ]*)? ?(.*)?'
            )
            LOCATION 's3://amzn-s3-demo-bucket/AWSLogs//elasticloadbalancing//'
            TBLPROPERTIES
            (
             "projection.enabled" = "true",
             "projection.day.type" = "date",
             "projection.day.range" = "2022/01/01,NOW",
             "projection.day.format" = "yyyy/MM/dd",
             "projection.day.interval" = "1",
             "projection.day.interval.unit" = "DAYS",
             "storage.location.template" = "s3://amzn-s3-demo-bucket/AWSLogs//elasticloadbalancing//${day}"
            )
```

re:Post Agent · Answer

This issue appears to be related to a potential change in the ALB access log format that's causing your Athena queries to return NULL values for non-partitioned fields. Based on the data you've provided, there seems to be a gradual transition starting on 2025/08/19, with the issue becoming complete by 2025/08/22.

This type of problem typically occurs when there's a mismatch between the log format and the table schema/SerDe configuration. The RegexSerDe used for ALB access logs is particularly sensitive to format changes.

Here are some potential causes and solutions:

1. **Log Format Change**: AWS may have updated the ALB access log format. This has happened in the past when new fields were added to the logs.

2. **Table Schema Update Needed**: You might need to update your table schema to match the new log format. The CREATE TABLE statement for ALB access logs includes a regex pattern that needs to match the exact format of your logs.

3. **Inconsistent Behavior**: The transition period (2025/08/19-2025/08/21) where some records work and others don't suggests a rolling update to the ALB logging system.

To resolve this issue:

1. Check a few raw log files from before and after the issue started to see if there are format differences.

2. Update your table definition with the latest CREATE TABLE statement from AWS documentation, which should include any new fields and the correct regex pattern.

3. If you're using partition projection, ensure your partition configuration is still correct.

4. As a temporary workaround, you could query specific date ranges separately (before and after the change) using different table definitions.

This appears to be similar to issues that have occurred after previous Athena or ALB updates, where queries that previously worked start failing or returning unexpected results.  
**Sources**  
[ATHENA INTERNAL_ERROR_QUERY_ENGINE | AWS re:Post](https://repost.aws/questions/QU5waA_wjSS6GNrycQjS2K8A/athena-internal-error-query-engine)  
[Query fails (inconsistency) after last athena update | AWS re:Post](https://repost.aws/questions/QUBYSENVX_SkS0maC6ca1hvQ/query-fails-inconsistency-after-last-athena-update)  
[Create the table for ALB access logs - Amazon Athena](https://docs.aws.amazon.com/athena/latest/ug/create-alb-access-logs-table.html)

Athena ALB Access Logs Suddenly Returning Null Fields Since 2025-08-19

Short description:

Problem detail:

Add your answer

Relevant content