RDS RIのリソースレベル適用状況をCUR2.0から把握する by DuckDB

brew install duckdb

duckdb --version
# v1.2.0 5f5512b827
duckdb
# v1.2.0 5f5512b827
# Enter ".help" for usage hints.
# Connected to a transient in-memory database.
# Use ".open FILENAME" to reopen on a persistent database.
# D

### 適宜変更ください
bucket="EXAMPLE-DOC-BUCKET"
prefix="cur2"
export_name="daily-export"

### ファイルの確認
aws s3 ls ${bucket}/${prefix}/${export_name}/data/BILLING_PERIOD=2025-02/
# 2025-02-14 07:37:06   60149849 daily-export-00001.snappy.parquet
# 2025-02-14 07:37:07   60158854 daily-export-00002.snappy.parquet
# 2025-02-14 07:37:07   60391747 daily-export-00003.snappy.parquet

### ファイルのダウンロード
aws s3 cp s3://${bucket}/${prefix}/${export_name}/data/BILLING_PERIOD=2025-02/ . --recursive
ls
# daily-export-00001.snappy.parquet
# daily-export-00002.snappy.parquet
# daily-export-00003.snappy.parquet

-- ファイル名や期間は適宜変更ください
SELECT
  line_item_usage_account_name as AWSAccount,
  regexp_replace(line_item_resource_id, '^arn:aws:.+:\d+:', '') as Resource,
  product_instance_type as InstanceType,
  CAST(SUM(line_item_unblended_cost) as INTEGER) as OnDemandCost
FROM './daily-export-*.parquet'
WHERE
  pricing_term = 'OnDemand'
  AND product_instance_type LIKE 'db.%'
  AND line_item_usage_type LIKE '%Usage:db.%'
  AND line_item_usage_start_date BETWEEN DATE '2025-02-03' AND DATE '2025-02-09'
GROUP BY AWSAccount, Resource, InstanceType
HAVING CAST(SUM(line_item_unblended_cost) as INTEGER) > 0
ORDER BY AWSAccount ASC, OnDemandCost DESC;

-- ファイル名や期間は適宜変更ください
SELECT
  line_item_usage_account_name as AWSAccount,
  regexp_replace(line_item_resource_id, '^arn:aws:.+:\d+:', '') as Resource,
  product_instance_type as InstanceType,
  CAST(SUM(reservation_effective_cost) as INTEGER) as EffectiveCost
FROM './daily-export-*.parquet'
WHERE
  pricing_term = 'Reserved'
  AND product_instance_type LIKE 'db.%'
  AND line_item_usage_type LIKE '%Usage:db.%'
  AND line_item_usage_start_date BETWEEN DATE '2025-02-03' AND DATE '2025-02-09'
GROUP BY AWSAccount, Resource, InstanceType
HAVING CAST(SUM(reservation_effective_cost) as INTEGER) > 0
ORDER BY AWSAccount ASC, EffectiveCost DESC;

-- ファイル名や期間は適宜変更ください
SELECT
  line_item_usage_account_name as AWSAccount,
  regexp_replace(line_item_resource_id, '^arn:aws:.+:\d+:', '') as Resource,
  product_instance_type as InstanceType,
  pricing_term as PricingTerm,
  CAST(SUM(line_item_unblended_cost) as INTEGER) +
    CAST(SUM(reservation_effective_cost) as INTEGER) as Cost,
  CAST(SUM(pricing_public_on_demand_cost) as INTEGER) -
    CAST(SUM(line_item_unblended_cost) as INTEGER) -
    CAST(SUM(reservation_effective_cost) as INTEGER) as Saving
FROM './daily-export-*.parquet'
WHERE
  product_instance_type LIKE 'db.%'
  AND line_item_usage_type LIKE '%Usage:db.%'
  AND line_item_usage_start_date BETWEEN DATE '2025-02-03' AND DATE '2025-02-09'
GROUP BY AWSAccount, Resource, InstanceType, PricingTerm
HAVING
  CAST(SUM(line_item_unblended_cost) as INTEGER) > 0
  OR CAST(SUM(reservation_effective_cost) as INTEGER) > 0
ORDER BY AWSAccount ASC, Resource ASC;