メンバーズ CUR でやってみよう！Cloud Intelligence Dashboards カスタマイズ編

CREATE OR REPLACE VIEW "account_map_original" AS 
SELECT DISTINCT
  line_item_usage_account_id account_id
, MAX_BY(bill_payer_account_id, line_item_usage_start_date) parent_account_id
, MAX_BY(line_item_usage_account_id, line_item_usage_start_date) account_name
, MAX_BY(line_item_usage_account_id, line_item_usage_start_date) account_email_id
FROM
  "cid_cur"."cur"
GROUP BY line_item_usage_account_id

CREATE OR REPLACE VIEW account_map AS
SELECT *
FROM
  (
 VALUES
     ROW ('111111111111', 'Account1')
   , ROW ('222222222222', 'Account2')
   , ROW ('333333333333', 'Account3')
)  ignored_table_name (
    account_id,   --mandatory
    account_name  --mandatory
)

$ pip3 install --upgrade cid-cmd
$ cid-cmd update --force --recursive

〜 中略 〜

? [dashboard-id] Please select dashboard from the list: CUDOS Dashboard v5 (cudos-v5)
? [athena-database] Select AWS Athena database to use as default: cid_cur
? [athena-workgroup] Select Amazon Athena workgroup to use: CID
? [cur-table-name-and-db] Please select CUR table: cid_cur.cur

? Enter Cost Allocation Tags to be added to datasets(WARNING: this can affect performance. Choose only the strict minimum) 
  ✔ Looks good
  --- Selected Items ---
  --- Selected Items (empty) ---
  --- Items Available To Add ---
❯   tag_cm_billing_group

? Enter Cost Allocation Tags to be added to datasets(WARNING: this can affect performance. Choose only the strict minimum) 
❯ ✔ Looks good
  --- Selected Items ---
  ✓ [1] tag_cm_billing_group

? [timezone] Please select timezone for datasets scheduled refresh.: Asia/Tokyo

? [dataset-resource-view-override] The existing dataset is different. Override?: proceed and override

? Select taxonomy fields to add as dashboard filters and group by fields 
❯ ✔ Looks good
  --- Selected Items ---
  ✓ [1] payer_account_id
  ✓ [2] account_id
  ✓ [3] account_name
  ✓ [4] tag_cm_billing_group

#######
####### CUDOS Dashboard v5 is available at: https://ap-northeast-1.quicksight.aws.amazon.com/sn/dashboards/cudos-v5
#######
~ $ 

Month,CurrencyConversionRate
2025-01-01,156.86
2025-02-01,151.96
2025-03-01,149.27
2025-04-01,144.27
2025-05-01,144.92
2025-06-01,144.42
2025-07-01,146.83
2025-08-01,148.31

    CREATE EXTERNAL TABLE IF NOT EXISTS currencyconversionrate (
        Month date,
        CurrencyConversionRate double
    )
    ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
    WITH SERDEPROPERTIES ('field.delim' = ',')
    STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
    LOCATION 's3://cid-{account_id}-shared/CurrencyConversionRate'
    TBLPROPERTIES (
        'classification' = 'csv',
        'skip.header.line.count' = '1'
    );

switch( ${Currency},
    "$", {unblended_cost},
    "¥", {unblended_cost} * {currencyconversionrate},
    0
)

# 為替ファイル
2025-06-01,144.42
2025-07-01,146.83

# 通貨（$）表示
2025-06, 0.07K 
2025-07, 0.09K

# 通貨（¥）表示
2025-06, 10.11K・・・144.42 * 0.07 
2025-07, 13.18K・・・146.83 * 0.09