Knowing how Hive table storage is organized can help us extract some additional information for S3 read operations for each table.

In most cases (and you can easily adapt this for your specific table storage pattern), tables are stored in a S3 bucket under the following key structure:

s3://<bucket_name>/hive/<database_name>/<table_name>/<partition1>/<partition2>/...

For example, hourly data for orders table can be stored as follows:

s3://cloudsqale/hive/sales.db/orders/created_dt=2018-10-18/hour=00/

After you get the summary information for S3 read operations (see Step #2), it makes sense to look at file types. Analyzing the object keys you can easily summarize information about compressed files such as .gz files.

Later I will use the Hive metadata information to define whether files named like 00000_0 are uncompressed text or ORC files.

select type, count(*) keys, count(distinct key) dist_keys, 
  sum(bytes_sent)/sum(total_time_ms/1000)/(1024*1024) rate_mb_sec, 
  sum(total_time_ms/1000) time_spent,
  sum(bytes_sent)/(cast(1024 as bigint)*1024*1024*1024) terabytes_read
from (
select 
  key,
  case 
    when key like '%.gz' then 'Compressed .gz'
    else 'Other'
  end type,
  bytes_sent,
  total_time_ms
from s3_access_logs 
where event_dt ='{$EVENT_DT}' and operation='REST.GET.OBJECT') t
group by type;

Here is my sample output:

File Types and Object Size Bins

Now let’s see the distribution of file types for each size bin:

select type, size_type, count(*) keys, count(distinct key) dist_keys, 
  sum(bytes_sent)/sum(total_time_ms/1000)/(1024*1024) rate_mb_sec, 
  sum(bytes_sent)/(cast(1024 as bigint)*1024*1024*1024) terabytes_read
from (
select 
  key,
  case 
    when key like '%.gz' then 'Compressed .gz'
    else 'Other'
  end type,
  case 
    when total_size <= 1024*1024 then '<= 1 MB'
    when total_size <= 30*1024*1024 then '<= 30 MB'
    when total_size <= 100*1024*1024 then '<= 100 MB'
    else '> 100 MB'
  end size_type,
  bytes_sent,
  total_time_ms
from s3_access_logs 
where event_dt ='{$EVENT_DT}' and operation='REST.GET.OBJECT') t
group by type, size_type;

Sample output:

See also, S3 Monitoring Step #2 – Read Operations.