如何通过Amazon Athena+CUR查询特定EC2实例的实际成本

核心字段映射说明 #

先明确CUR中与EC2实例标识、计费类型相关的关键字段，解决列名匹配问题：

• 实例ID：
  • 按需实例：从line_item_resource_id（格式为arn:aws:ec2:region:account-id:instance/i-xxxxxx）中提取，用split_part(line_item_resource_id, '/', 2)获取纯实例ID
  • Savings Plans覆盖的实例：直接用savings_plans_covered_instance_id字段
• 自定义标签：以resource_tags_user_为前缀，比如Name标签对应resource_tags_user_Name，业务标签对应resource_tags_user_BusinessUnit
• 计费类型区分：
  • 按需：line_item_line_item_type = 'Usage'且product_service_code = 'AmazonEC2'
  • EC2实例型Savings Plans：savings_plans_type = 'EC2 Instance Savings Plans'

完整查询语句 #

以下SQL会合并按需实例的直接成本和Savings Plans分摊到实例的成本，按实例ID、标签汇总，可直接修改过滤条件定位特定实例：

WITH on_demand_cost AS (
    SELECT
        -- 提取按需实例ID
        split_part(line_item_resource_id, '/', 2) AS instance_id,
        -- 替换为你需要的标签字段
        resource_tags_user_Name AS instance_name,
        resource_tags_user_BusinessUnit AS business_unit,
        SUM(CAST(line_item_unblended_cost AS DECIMAL(18, 6))) AS on_demand_total
    FROM
        -- 替换为你的CUR表名（格式：cur_db.cur_table）
        your_cur_database.your_cur_table
    WHERE
        product_service_code = 'AmazonEC2'
        AND line_item_line_item_type = 'Usage'
        -- 可选：过滤特定时间范围
        AND line_item_usage_start_date >= DATE('2024-01-01')
        AND line_item_usage_end_date < DATE('2024-02-01')
        -- 可选：过滤特定实例ID或标签
        -- AND split_part(line_item_resource_id, '/', 2) = 'i-0abc123def456'
        -- AND resource_tags_user_Name = 'Production-WebServer-01'
    GROUP BY
        split_part(line_item_resource_id, '/', 2),
        resource_tags_user_Name,
        resource_tags_user_BusinessUnit
),
savings_plans_cost AS (
    SELECT
        savings_plans_covered_instance_id AS instance_id,
        -- 关联标签：从按需实例记录匹配标签（SP记录标签可能不全）
        od.instance_name,
        od.business_unit,
        -- 计算SP分摊到该实例的成本：前期摊销+ recurring费用
        SUM(
            CAST(savings_plans_amortized_upfront_cost_for_usage AS DECIMAL(18, 6)) +
            CAST(savings_plans_recurring_fee_for_usage AS DECIMAL(18, 6))
        ) AS sp_total
    FROM
        your_cur_database.your_cur_table sp
    LEFT JOIN
        on_demand_cost od ON sp.savings_plans_covered_instance_id = od.instance_id
    WHERE
        savings_plans_type = 'EC2 Instance Savings Plans'
        AND savings_plans_covered_instance_id IS NOT NULL
        -- 可选：过滤时间范围，需和按需部分一致
        AND line_item_usage_start_date >= DATE('2024-01-01')
        AND line_item_usage_end_date < DATE('2024-02-01')
    GROUP BY
        savings_plans_covered_instance_id,
        od.instance_name,
        od.business_unit
)
-- 合并两类成本，计算总成本
SELECT
    COALESCE(od.instance_id, sp.instance_id) AS instance_id,
    COALESCE(od.instance_name, sp.instance_name) AS instance_name,
    COALESCE(od.business_unit, sp.business_unit) AS business_unit,
    COALESCE(od.on_demand_total, 0) AS on_demand_cost,
    COALESCE(sp.sp_total, 0) AS savings_plans_cost,
    COALESCE(od.on_demand_total, 0) + COALESCE(sp.sp_total, 0) AS total_cost
FROM
    on_demand_cost od
FULL OUTER JOIN
    savings_plans_cost sp ON od.instance_id = sp.instance_id
-- 可选：过滤最终结果中的特定实例
-- WHERE COALESCE(od.instance_id, sp.instance_id) = 'i-0abc123def456'
ORDER BY
    total_cost DESC;

关键注意事项 #

1. 替换占位符：将your_cur_database.your_cur_table改为你实际的CUR数据库和表名
2. 标签字段调整：根据你实际使用的标签名修改resource_tags_user_*字段，若使用AWS默认标签（如aws:cloudformation:stack-name），前缀为resource_tags_aws_
3. 时间范围：按需和Savings Plans部分的时间范围需保持一致，确保成本统计周期统一
4. Savings Plans适配：如果使用Compute Savings Plans，只需将savings_plans_type改为'Compute Savings Plans'即可
5. 空值处理：用COALESCE处理某类成本为0的情况（比如实例仅用按需或仅被SP覆盖）

内容的提问来源于stack exchange，提问作者Mahyar