S3 成本优化 – Part 1 最佳优化实践

CREATE EXTERNAL TABLE `inventory_list`(
    `bucket` string, 
    `key` string, 
    `size` bigint, 
    `last_modified_date` string, 
`storage_class` string)
  PARTITIONED BY (dt string)
  ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
  STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.SymlinkTextInputFormat'
  OUTPUTFORMAT  'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'
  LOCATION
's3://inventory/testinventory/data/hive'

-- 按照某天生成的清单，查询桶内有多少存储类型：
Select distinct storage_class from inventory_list where dt='YYYY-MM-DD-00-00'
-- 比如按照2020-05-20生成的清单查询：
Select distinct storage_class from inventory_list where dt='2020-05-20-00-00'


-- 查询每个存储类型有多少个文件，文件量多大：(根据上个语句查询出来总共有多少存储类型，按照每个存储类型进行单独查询)
select count(*) as count ,sum(size)/1073741824 as GB from inventory_list where storage_class = 'STANDARD' and dt='YYYY-MM-DD-00-00'
select count(*) as count ,sum(size)/1073741824 as GB from inventory_list where storage_class = 'GLACIER' and dt='YYYY-MM-DD-00-00'
select count(*) as count ,sum(size)/1073741824 as GB from inventory_list where storage_class = 'XXXXX' and dt='YYYY-MM-DD-00-00'
select count(*) as count ,sum(size)/1073741824 as GB from inventory_list where storage_class = 'XXXXX' and dt='YYYY-MM-DD-00-00'
select count(*) as count ,sum(size)/1073741824 as GB from inventory_list where storage_class = 'XXXXX' and dt='YYYY-MM-DD-00-00'
-- 查询每个storage class，0KB的文件有多少
select count(*) as count  from inventory_list where storage_class = 'STANDARD'  and size=0 and dt='YYYY-MM-DD-00-00'
select count(*) as count  from inventory_list where storage_class = 'XXXXXX'  and size=0 and dt='YYYY-MM-DD-00-00'
-- 查询标准的storage class,  大于0KB小于128KB的文件有多少。
select count(*) as count ,sum(size)/1073741824 as GB from inventory_list where storage_class = 'STANDARD'  and size > 0 and size < 128*1024 and dt='YYYY-MM-DD-00-00'
-- 查询标准的storage class,  小于8KB的文件有多少。
select count(*) as count ,sum(size)/1073741824 as GB from inventory_list where storage_class = 'STANDARD'  and size <= 8*1024 and dt='YYYY-MM-DD-00-00'
-- 查询某一些文件夹下的文件大小和文件数量有多大，之后可能会排除某些文件夹不做转换。
select count(*) as count ,sum(size)/1073741824 as GB from inventory_list where key like  'logs%' and dt='YYYY-MM-DD-00-00'
-- 查询30天以内创建的standard存储类型文件具体数量和大小：
select count(*) as count ,sum(size)/1073741824 as GB from inventory_list where storage_class = 'STANDARD' and from_iso8601_timestamp(last_modified_date) > CAST((current_timestamp - interval '30' day) AS DATE) and dt='YYYY-MM-DD-00-00'
-- 如果需要使用S3批处理进行存储类型转换，那么按照特定要求生成我们需要的S3 批处理使用的csv文件，之后使用Athena“save as”功能下载之后，上传到某个存储桶作为 S3批处理的输入文件
Select bucket,key from inventory_list where key not like 'logs%' and size >  128*1024 and storage_class = 'STANDARD' and dt='YYYY-MM-DD-00-00'

-- 查询某个桶的上个月整月详细用量
SELECT
bill_billing_period_start_date,
bill_billing_period_end_date,
line_item_resource_id,
product_product_name,
line_item_operation,
sum(CAST(line_item_unblended_cost AS DOUBLE)) cost,
sum(CAST(line_item_usage_amount AS DOUBLE)) "usageamount(GB/count)"
FROM
default.cur_table
WHERE
 product_product_name = 'Amazon Simple Storage Service'
 and line_item_resource_id='bucket_name'
 and bill_billing_period_start_date >= date_trunc('month',DATE_ADD('day', -1, date_trunc('month', now())))
 and bill_billing_period_end_date <= date_trunc('month', now())
GROUP BY
 1,2,3,4,5
ORDER BY
sum(CAST(line_item_unblended_cost AS DOUBLE))  desc;

-- 比如查找所有1年前生成的日志文件列表：
Select bucket,key from inventory_list where key like 'logs%' and storage_class = 'STANDARD' and from_iso8601_timestamp(last_modified_date) < CAST((current_timestamp - interval '365' day) AS DATE) and dt='YYYY-MM-DD-00-00'