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.

After you get the first impression about your S3 buckets by looking at their size, number of objects and daily growth rate (see Step #1), it is time to investigate I/O operations in detail.

Let’s start with read operations. Now we need to use S3 Access Logs to get the detailed information about all performed S3 REST.GET.OBJECT operations.

The first step in Amazon S3 monitoring is to check the current state of your S3 buckets and how fast they grow. You can easily get this information from the CloudWatch Management console, running a AWS CLI command or AWS SDK script.

Bucket Size

Here is an example of AWS CLI command to get the size of a bucket for every day within --start-time and --end-time date range:

aws cloudwatch get-metric-statistics \
  --metric-name BucketSizeBytes --namespace AWS/S3 \
  --start-time 2018-10-01T00:00:00Z --end-time 2018-10-08T00:00:00Z \
  --statistics Maximum --unit Bytes --region us-east-1 \
  --dimensions Name=BucketName,Value=cloudsqale Name=StorageType,Value=StandardStorage \
  --period 86400 --query 'Datapoints[*].[Timestamp, Maximum]' \
  --output text | sort  | python cloudwatch_s3_metrics.py