AWS Well-Architected Labs > Cost Optimization > 300 Labs > Level 300: CUR Queries > Database

Database

These are queries for AWS Services under the Database product family .

Use the clipboard in the top right of the text boxes below to copy all of the text to be pasted.

You may need to change variables used as placeholders in your query. ${table_Name} is a common variable which needs to be replaced. Example: cur_db.cur_table

Amazon RDS
Amazon RDS - Monthly Cost grouped by Usage Type and Resource Tag
Amazon DynamoDB
Amazon Redshift
Amazon ElastiCache

Amazon RDS

Query Description

This query will output the daily sum per resource for all RDS purchase options across all RDS usage types.

Pricing

Please refer to the Amazon RDS pricing page .

Sample Output

Images/rds-w-rid.png

Download SQL File

Link to Code

Copy Query

  SELECT bill_payer_account_id,
     line_item_usage_account_id,
     DATE_FORMAT(("line_item_usage_start_date"),
     '%Y-%m-%d') AS day_line_item_usage_start_date, product_instance_type, line_item_operation, line_item_usage_type, line_item_line_item_type, pricing_term, product_product_family , SPLIT_PART(line_item_resource_id,':',7) AS line_item_resource_id,
      CASE product_database_engine
      WHEN '' THEN
      'Not Applicable'
      ELSE product_database_engine
      END AS OS , sum(CASE
      WHEN ("line_item_line_item_type" = 'SavingsPlanCoveredUsage') THEN
      "line_item_usage_amount"
      WHEN ("line_item_line_item_type" = 'DiscountedUsage') THEN
      "line_item_usage_amount"
      WHEN ("line_item_line_item_type" = 'Usage') THEN
      "line_item_usage_amount"
      ELSE 0 END) "usage_quantity", sum ("line_item_unblended_cost") "unblended_cost", sum(CASE
      WHEN ("line_item_line_item_type" = 'SavingsPlanCoveredUsage') THEN
      "savings_plan_savings_plan_effective_cost"
      WHEN ("line_item_line_item_type" = 'SavingsPlanRecurringFee') THEN
      ("savings_plan_total_commitment_to_date" - "savings_plan_used_commitment")
      WHEN ("line_item_line_item_type" = 'SavingsPlanNegation') THEN
      0
      WHEN ("line_item_line_item_type" = 'SavingsPlanUpfrontFee') THEN
      0
      WHEN ("line_item_line_item_type" = 'DiscountedUsage') THEN
      "reservation_effective_cost"
      WHEN ("line_item_line_item_type" = 'RIFee') THEN
      ("reservation_unused_amortized_upfront_fee_for_billing_period" + "reservation_unused_recurring_fee")
      WHEN (("line_item_line_item_type" = 'Fee')
          AND ("reservation_reservation_a_r_n" <> '')) THEN
      0
      ELSE "line_item_unblended_cost" END) "amortized_cost", sum(CASE
      WHEN ("line_item_line_item_type" = 'SavingsPlanRecurringFee') THEN
      (-"savings_plan_amortized_upfront_commitment_for_billing_period")
      WHEN ("line_item_line_item_type" = 'RIFee') THEN
      (-"reservation_amortized_upfront_fee_for_billing_period")
      ELSE 0 END) "ri_sp_trueup", sum(CASE
      WHEN ("line_item_line_item_type" = 'SavingsPlanUpfrontFee') THEN
      "line_item_unblended_cost"
      WHEN (("line_item_line_item_type" = 'Fee')
          AND ("reservation_reservation_a_r_n" <> '')) THEN
      "line_item_unblended_cost"ELSE 0 END) "ri_sp_upfront_fees"
  FROM ${table_name}
  WHERE year = '2020'
          AND (month
      BETWEEN '7'
          AND '9'
          OR month
      BETWEEN '07'
          AND '09')
          AND product_product_name = 'Amazon Relational Database Service'
          AND line_item_line_item_type  in ('DiscountedUsage', 'Usage', 'SavingsPlanCoveredUsage')
  GROUP BY  bill_payer_account_id, line_item_usage_account_id, DATE_FORMAT(("line_item_usage_start_date"),'%Y-%m-%d'), product_instance_type, line_item_operation, line_item_usage_type, line_item_line_item_type, pricing_term, product_product_family, product_database_engine, line_item_line_item_type, line_item_resource_id
  ORDER BY  day_line_item_usage_start_date, usage_quantity, unblended_cost;

Back to Table of Contents

Amazon RDS - Monthly Cost grouped by Usage Type and Resource Tag

Query Description

This query will output the total monthly blended costs for RDS grouped by usage type and a specified tag (e.g. Environment:Test,Dev,Prod). The query can be modified to adjust the Cost dataset from Blended to Unblended by adjusting the specified cost column (line_item_blended_cost -> line_item_unblended_cost). This query would be helpful to visualize a quick monthly breakdown of cost components for RDS usage with a specific tag (Environment:Test,Dev,Prod).

Pricing

Please refer to the Amazon RDS pricing page .

Sample Output

Images/monthly_rds_usage_type_by_tag.png

Download SQL File

Link to Code

Copy Query

SELECT
    line_item_usage_type,
    month,
    resource_tags_user_environment,
    SUM(CAST(line_item_blended_cost AS decimal(16,8))) AS sum_line_item_blended_cost
FROM 
    ${table_name}
WHERE year = '2020' AND (month BETWEEN '1' AND '12' OR month BETWEEN '01' AND '12')
AND line_item_product_code='AmazonRDS'
AND resource_tags_user_environment = 'dev'
GROUP BY  
    1,2,3
HAVING sum(line_item_blended_cost) > 0
ORDER BY 
    line_item_usage_type,
    month,
    resource_tags_user_environment;

Back to Table of Contents

Amazon DynamoDB

Query Description

This query will output the total monthly sum per resource for all DynamoDB purchase options (including reserved capacity) across all DynamoDB usage types (including data transfer and storage costs). The unblended cost will be summed and in descending order.

Pricing

Please refer to the DynamoDB pricing page .

Sample Output

Images/dynamodb.png

Download SQL File

Link to Code

Copy Query

SELECT 
  bill_payer_account_id,
  line_item_usage_account_id,
  month,
  product_location,
  SPLIT_PART(line_item_resource_id, 'table/', 2) as line_item_resource_id,
  (CASE
    WHEN line_item_line_item_type LIKE '%Fee' THEN 'DynamoDB Reserved Capacity'
    WHEN line_item_line_item_type = 'DiscountedUsage' THEN 'DynamoDB Reserved Capacity'
    ELSE 'DynamoDB Usage' 
  END) as purchase_option_line_item_line_item_type,
  (CASE
    WHEN product_product_family = 'Data Transfer' THEN 'DynamoDB Data Transfer'
    WHEN product_product_family LIKE '%Storage' THEN 'DynamoDB Storage'
    ELSE 'DynamoDB Usage' 
  END) as usage_type_product_product_family,   
  SUM(CAST(line_item_usage_amount AS double)) as sum_line_item_usage_amount,
  SUM(CAST(line_item_blended_cost AS decimal(16,8))) AS sum_line_item_unblended_cost,
  reservation_reservation_a_r_n
FROM 
  ${table_name}
  WHERE year = '2020' AND (month BETWEEN '7' AND '9' OR month BETWEEN '07' AND '09')
  AND line_item_product_code = 'AmazonDynamoDB'
  AND line_item_line_item_type  in ('DiscountedUsage', 'Usage', 'SavingsPlanCoveredUsage')
GROUP BY
  bill_payer_account_id,
  line_item_usage_account_id,
  month,
  product_location,
  line_item_resource_id,
  line_item_line_item_type,
  product_product_family,
  reservation_reservation_a_r_n
ORDER BY
  sum_line_item_unblended_cost DESC

Back to Table of Contents

Amazon Redshift

Query Description

This query will provide daily unblended and amortized cost as well as usage information per linked account for Amazon Redshift. The output will include detailed information about the resource id (cluster name), usage type, and API operation. The usage amount and cost will be summed and the cost will be in descending order. This query includes RI and SP true up which will show any upfront fees to the account that purchased the pricing model.

Pricing

Please refer to the Redshift pricing page . Please refer to the Redshift Cost Optimization Whitepaper for Cost Optimization techniques.

Sample Output

Images/redshiftwrid.png

Download SQL File

Link to Code

Copy Query

SELECT 
  bill_payer_account_id,
  line_item_usage_account_id,
  DATE_FORMAT((line_item_usage_start_date),'%Y-%m-%d') AS day_line_item_usage_start_date,
  product_instance_type,
  SPLIT_PART(line_item_resource_id,':',7) as split_line_item_resource_id,
  line_item_operation,
  line_item_usage_type,
  line_item_line_item_type,
  pricing_term,
  product_usage_family,
  product_product_family,
  sum(CASE 
  WHEN ("line_item_line_item_type" = 'SavingsPlanCoveredUsage') THEN "line_item_usage_amount" 
  WHEN ("line_item_line_item_type" = 'DiscountedUsage') THEN "line_item_usage_amount" 
  WHEN ("line_item_line_item_type" = 'Usage') THEN "line_item_usage_amount" ELSE 0 END) "usage_quantity",
  sum ("line_item_unblended_cost") "unblended_cost",
  sum(CASE
      WHEN ("line_item_line_item_type" = 'SavingsPlanCoveredUsage') THEN "savings_plan_savings_plan_effective_cost" 
      WHEN ("line_item_line_item_type" = 'SavingsPlanRecurringFee') THEN ("savings_plan_total_commitment_to_date" - "savings_plan_used_commitment") 
      WHEN ("line_item_line_item_type" = 'SavingsPlanNegation') THEN 0
      WHEN ("line_item_line_item_type" = 'SavingsPlanUpfrontFee') THEN 0
      WHEN ("line_item_line_item_type" = 'DiscountedUsage') THEN "reservation_effective_cost"  
      WHEN ("line_item_line_item_type" = 'RIFee') THEN ("reservation_unused_amortized_upfront_fee_for_billing_period" + "reservation_unused_recurring_fee")
      WHEN (("line_item_line_item_type" = 'Fee') AND ("reservation_reservation_a_r_n" <> '')) THEN 0 ELSE "line_item_unblended_cost" END) "amortized_cost",
sum(CASE
      WHEN ("line_item_line_item_type" = 'SavingsPlanRecurringFee') THEN (-"savings_plan_amortized_upfront_commitment_for_billing_period") 
      WHEN ("line_item_line_item_type" = 'RIFee') THEN (-"reservation_amortized_upfront_fee_for_billing_period") ELSE 0 END) "ri_sp_trueup",
sum(CASE
      WHEN ("line_item_line_item_type" = 'SavingsPlanUpfrontFee') THEN "line_item_unblended_cost"
      WHEN (("line_item_line_item_type" = 'Fee') AND ("reservation_reservation_a_r_n" <> '')) THEN "line_item_unblended_cost"ELSE 0 END) "ri_sp_upfront_fees"
FROM 
  ${table_name}
WHERE
  year = '2020' AND (month BETWEEN '7' AND '9' OR month BETWEEN '07' AND '09')
  AND product_product_name = 'Amazon Redshift'
  AND line_item_line_item_type  in ('DiscountedUsage', 'Usage', 'SavingsPlanCoveredUsage')
GROUP BY
  1,2,3,4,5,6,7,8,9,10,11
ORDER BY
  day_line_item_usage_start_date,
  product_product_family,
  unblended_cost DESC;

Back to Table of Contents

Amazon ElastiCache

Query Description

This query will output the total monthly sum per resource for all Amazon ElastiCache purchase options (including reserved instances) across all ElastiCache instances types. The unblended and amortized cost will be summed and in descending order.

Pricing

Please refer to the Amazon ElastiCache pricing page .

Sample Output

Images/elasticache.png

Download SQL File

Link to Code

Copy Query

select 
  bill_payer_account_id,
  line_item_usage_account_id, 
  DATE_FORMAT((line_item_usage_start_date),'%Y-%m-01') AS month_line_item_usage_start_date,
  SPLIT_PART(line_item_resource_id,':',7) as split_line_item_resource_id,
  SPLIT_PART(line_item_usage_type ,':',2) AS split_line_item_usage_type,
(CASE 
  WHEN ("line_item_line_item_type" = 'DiscountedUsage') THEN 'Reserved Instance'
  WHEN ("line_item_line_item_type" = 'Usage') THEN 'OnDemand' ELSE 'Others' END) purchase_option_line_item_line_item_type,
sum(CASE 
  WHEN ("line_item_line_item_type" = 'SavingsPlanCoveredUsage') THEN "line_item_usage_amount" 
  WHEN ("line_item_line_item_type" = 'DiscountedUsage') THEN "line_item_usage_amount" 
  WHEN ("line_item_line_item_type" = 'Usage') THEN "line_item_usage_amount" ELSE 0 END) sum_line_item_usage_amount,
  sum(CASE
    WHEN ("line_item_line_item_type" = 'SavingsPlanNegation') THEN 0 ELSE "line_item_unblended_cost" END) sum_line_item_unblended_cost,
  sum(CASE
    WHEN ("line_item_line_item_type" = 'SavingsPlanCoveredUsage') THEN "savings_plan_savings_plan_effective_cost"
    WHEN ("line_item_line_item_type" = 'SavingsPlanRecurringFee') THEN ("savings_plan_total_commitment_to_date" - "savings_plan_used_commitment")
    WHEN ("line_item_line_item_type" = 'SavingsPlanNegation') THEN 0
    WHEN ("line_item_line_item_type" = 'SavingsPlanUpfrontFee') THEN 0
    WHEN ("line_item_line_item_type" = 'DiscountedUsage') THEN "reservation_effective_cost"
    WHEN ("line_item_line_item_type" = 'RIFee') THEN ("reservation_unused_amortized_upfront_fee_for_billing_period" + "reservation_unused_recurring_fee")
    WHEN (("line_item_line_item_type" = 'Fee') AND ("reservation_reservation_a_r_n" <> '')) THEN 0 ELSE "line_item_unblended_cost" END) "amortized_cost",
  sum(CASE
    WHEN ("line_item_line_item_type" = 'SavingsPlanRecurringFee') THEN (-"savings_plan_amortized_upfront_commitment_for_billing_period")
    WHEN ("line_item_line_item_type" = 'RIFee') THEN (-"reservation_amortized_upfront_fee_for_billing_period")
    WHEN ("line_item_line_item_type" = 'SavingsPlanNegation') THEN (-"line_item_unblended_cost" ) ELSE 0 END) "ri_sp_trueup",
  sum(CASE
    WHEN ("line_item_line_item_type" = 'SavingsPlanUpfrontFee') THEN "line_item_unblended_cost"
    WHEN (("line_item_line_item_type" = 'Fee') AND ("reservation_reservation_a_r_n" <> '')) THEN "line_item_unblended_cost"ELSE 0 END) "ri_sp_upfront_fees"      
FROM 
    ${table_name}
WHERE 
    year = '2020' AND (month BETWEEN '7' AND '9' OR month BETWEEN '07' AND '09')
    AND product_product_name = 'Amazon ElastiCache'
    AND product_product_family = 'Cache Instance'
    AND line_item_line_item_type  in ('DiscountedUsage', 'Usage', 'SavingsPlanCoveredUsage')
GROUP BY  
    DATE_FORMAT((line_item_usage_start_date),'%Y-%m-01'), 
    bill_payer_account_id, 
    line_item_usage_account_id, 
    line_item_line_item_type, 
    line_item_resource_id, 
    line_item_usage_type
ORDER BY  
    month_line_item_usage_start_date,
    sum_line_item_usage_amount desc, 
    sum_line_item_unblended_cost

Back to Table of Contents

CUR queries are provided as is. We recommend validating your data by comparing it against your monthly bill and Cost Explorer prior to making any financial decisions. If you wish to provide feedback on these queries, there is an error, or you want to make a suggestion, please email: curquery@amazon.com