Update AWS Cost and Usage Report Columns

update-aws-cost-and-usage-report-columns.sql

/* 
    Dynamically creates Redshift "alter column" SQL for AWS "Cost and Usage Reports" based on the "tagmapping" data 
    Note: Redshift column names are case-sensitive so the first query renames the duplicate usertags (if any)
*/
update awsbillingYYYYMM_tagmapping
    set usertag = b.usertag || '_' || cast(sequence as varchar)
from awsbillingYYYYMM_tagmapping a
inner join (
    select row_number() over (
            partition by lower(usertag) order by usertag desc
            ) as sequence
        ,usertag
    from awsbillingYYYYMM_tagmapping
    ) b on a.usertag = b.usertag
    and sequence <> 1
;

select
    listagg('alter table awsbillingYYYYMM rename column ' || remappedusertag || ' to "' || usertag,'"; \n') 
        within group (order by usertag) || '";'
from awsbillingYYYYMM_tagmapping
;

/*
alter table awsbillingYYYYMM rename column userTag0 to "aws:user"; 
alter table awsbillingYYYYMM rename column userTag1 to "aws:group";
alter table awsbillingYYYYMM rename column userTag2 to "aws:product";
...etc
*/