Athena query analysis all data, rather than specified columns

Hi,

no you are not missing anything, the behaviour you are seeing is normal, the ALB log files are compressed text files, and the table used in the documentation you mentioned is not partitioned.

This means that to find the rows you need in your query Athena still needs to open and scan all file to make sure there are no rows needed by the query in that file. As for the column the log file stores all columns for a specific row (it is just a text string) in the same file; so to extract the columns you need Athena still need to scan all files.

Now, if you want to optimize the cost of your query you have some options:

1. modify the Athena table and introduce Partition Projection using a new partition column for the date (based on the documentation the log files are actually stored with a prefix structure that include yyyy/mm/dd . for example:

CREATE EXTERNAL TABLE IF NOT EXISTS alb_log_partition_projection (
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 string,
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
)
PARTITIONED BY(mydate 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://yourS3bucket/AWSLogs/youraccount/elasticloadbalancing/us-east-1/'
TBLPROPERTIES (
'has_encrypted_data'='false',
"projection.enabled" = "true",
"projection.mydate.type" = "date",
"projection.mydate.format" = "yyyy/MM/dd",
"projection.mydate.range" = "2021/01/01,NOW",
"projection.datehour.interval" = "1",
"projection.datehour.interval.unit" = "DAY",
"storage.location.template" = "s3://yourS3bucket/AWSLogs/youraccount/elasticloadbalancing/us-east-1/${mydate}"
)

2. if you want to further optimize the cost of your queries and also performance , you could use a CTAS query in Athena to create an new table in Apache Parquet format (highly compressed and columnar format) and define the partitions you need (probably just date) . In this case not only your WHERE clause will limit the files to scan based on the time interval you are interested in , but it will only scan the column you are interested in. For Example:

CREATE TABLE alb_parquet3
WITH (
      format = 'Parquet',
      write_compression = 'SNAPPY',
      partitioned_by = ARRAY['mydate'],
      external_location = 's3://yourS3bucket/data-lake/alb-pqt/')
as select * from "alb_log_partition_projection" 

or

CREATE TABLE alb_parquet3
WITH (
      format = 'Parquet',
      write_compression = 'SNAPPY',
      partitioned_by = ARRAY['mydate'],
      external_location = 's3://yourS3bucket/data-lake/alb-pqt/')
as select *, substr(time,1,10) as mydate from "alb_logs" 

with approach 2, you will then need to schedule a batch to insert data in the new table. Having partitioned the table by day you will need to do the insert once a day to avoid duplicate. Otherwise you can make the process to convert the data a bit more complex and you could drop the partition for the current day, delete the prefix for the current date from S3 and finally call the insert with a where clause on data only for the current day. In this case you could even run the incremental load process every few hours or every few minutes.

In this case having the original table with partition projection allows you to scan less data during the incremental process.

you can also check this knowledge base article.

hope this helps