Perform this step if you want to generate data for all previous months available in your current CUR files. This is a one off step that is performed manually. We create a temporary table in Athena, and write the output to the S3 location created above, for the member/linked account to access it. We then delete the temporary table - which does not delete the S3 output data.
1 - In the master/payer account go into the Athena service dashboard
2 - Create your query using the template below:
The following statement will copy all columns from the source table if the line_item_usage_account_id matches a specific Account ID. It will output each month into a separate folder by using partitioning on the year and month, and output it to the S3 output folder.
CREATE TABLE (database).temp_table WITH ( format = 'Parquet', parquet_compression = 'GZIP', external_location = 's3://(bucket)/(folder)', partitioned_by=ARRAY['year_1','month_1']) AS SELECT *, year as year_1, month as month_1 FROM "(database)"."(table)" where line_item_usage_account_id like '(account ID)'
Some key points for your queries:
Example of performance with a source CUR of 6.3Gb:
3 - Execute the statement in Athena:
4 - Go into the S3 service dashboard
5 - Go to the output bucket and folder
6 - Verify the data has been populated into the S3 folders
7 - Verify the permissions are correct on the files - there should be multiple Grantees:
8 - Then delete the temp table from Athena by modifying the following code: (this will NOT delete the s3 data)
DROP TABLE (database).temp_table