Load AWS usage/billing data to Oracle

1. install aws cli and config using your ID and secret access key
2. setup cronjob in your database server to run following script:

#!/usr/bi/bash
billing_file_prex='XXXXX-aws-billing-detailed-line-items-with-resources-and-tags-'
mon=`date '+%Y-%m'`
billing_file="s3://cummins_aws_billing/$billing_file_prex$mon.csv.zip"
aws s3 cp $billing_file ./aws.csv.zip
unzip -o ./aws.csv.zip
mv "$billing_file_prex$mon.csv" aws.csv

Please replace XXXXX with your own account. You can also change the dir where you want to save your file.

3. In Oracle DB, create a dir EXT_TAB_DATA pointing to the file dir you use for the dowload

4. create an Oracle external table like this:

CREATE TABLE  "AWS_BILLING_EXTERNAL" 
   ( "INVOICEID" VARCHAR2(1000), 
 "PAYERACCOUNTID" VARCHAR2(1000), 
 "LINKEDACCOUNTID" VARCHAR2(2000), 
 "RECORDTYPE" VARCHAR2(1000), 
 "RECORDID" VARCHAR2(1000), 
 "PRODUCTNAME" VARCHAR2(2000), 
 "RATEID" VARCHAR2(1000), 
 "SUBSCRIPTIONID" VARCHAR2(1000), 
 "PRICINGPLANID" VARCHAR2(1000), 
 "USAGETYPE" VARCHAR2(200), 
 "OPERATION" VARCHAR2(1000), 
 "AVAILABILITYZONE" VARCHAR2(1000), 
 "RESERVEDINSTANCE" VARCHAR2(3000), 
 "ITEMDESCRIPTION" VARCHAR2(2000), 
 "USAGESTARTDATE" VARCHAR2(1000), 
 "USAGEENDDATE" VARCHAR2(1000), 
 "USAGEQUANTITY" VARCHAR2(1000), 
 "BLENDEDRATE" VARCHAR2(2000), 
 "BLENDEDCOST" VARCHAR2(3000), 
 "UNBLENDEDRATE" VARCHAR2(3000), 
 "UNBLENDEDCOST" VARCHAR2(1000), 
 "RESOURCEID" VARCHAR2(1000), 
 "AWS_CREATEDBY" VARCHAR2(3000), 
 "USER_APPLICATION" VARCHAR2(1000), 
 "USER_BILL_GROUP" VARCHAR2(1000), 
 "USER_ENVIRONMENT" VARCHAR2(1000), 
 "USER_NAME" VARCHAR2(1000)
   ) 
   ORGANIZATION EXTERNAL 
    ( TYPE ORACLE_LOADER
      DEFAULT DIRECTORY "EXT_TAB_DATA"
      ACCESS PARAMETERS
      ( RECORDS DELIMITED BY NEWLINE
    BADFILE ext_tab_data:'aws.bad'
    LOGFILE ext_tab_data:'aws.log'
    DISCARDFILE ext_tab_data:'aws-discarded.log'
    skip 1
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
    MISSING FIELD VALUES ARE NULL
    (
        InvoiceID, 
 PayerAccountId, 
 LinkedAccountId, 
 RecordType, 
 RecordId, 
 ProductName, 
 RateId, 
 SubscriptionId, 
    PricingPlanId, 
 UsageType, 
 Operation, 
 AvailabilityZone, 
 ReservedInstance, 
 ItemDescription, 
 UsageStartDate, 
 UsageEndDate, 
 UsageQuantity, 
 BlendedRate, 
 BlendedCost, 
 UnBlendedRate, 
 UnBlendedCost, 
 ResourceId, 
 aws_createdBy, 
 user_Application, 
 user_Bill_Group, 
 user_Environment,
 user_Name
    )
                 )
      LOCATION
       ( 'aws.csv'
       )
    )
   REJECT LIMIT UNLIMITED
/