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.

CUR Query Library uses placeholder variables, indicated by a dollar sign and curly braces (${ }). ${table_name} and ${date_filter} are common placeholder variables used throughout CUR Query Library, which must be replaced before a query will run. For example, if your CUR table is called cur_table and is in a database called cur_db, you would replace ${table_name} with cur_db.cur_table. For ${date_filter}, you have multiple options. See Filtering by Date in the CUR Query Library Help section for additional details.

Table of Contents

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 split_line_item_resource_id,
  product_database_engine,
  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) AS sum_line_item_usage_amount, 
  SUM(line_item_unblended_cost) AS 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) AS sum_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) AS sum_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) AS sum_ri_sp_upfront_fees
FROM 
  ${table_name} 
WHERE 
  ${date_filter} 
  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') 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),
  product_database_engine,
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,
  DATE_FORMAT(line_item_usage_start_date,'%Y-%m') AS month_line_item_usage_start_date,
  resource_tags_user_environment,
  SUM(CAST(line_item_blended_cost AS DECIMAL(16,8))) AS sum_line_item_blended_cost
FROM 
  ${table_name}
WHERE 
  ${date_filter}
  AND line_item_product_code='AmazonRDS'
  AND resource_tags_user_environment = 'dev'
GROUP BY  
  line_item_usage_type,
  DATE_FORMAT(line_item_usage_start_date,'%Y-%m'),
  resource_tags_user_environment
HAVING 
  SUM(line_item_blended_cost) > 0
ORDER BY 
  line_item_usage_type,
  month_line_item_usage_start_date,
  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,
  DATE_FORMAT(line_item_usage_start_date,'%Y-%m') AS month_line_item_usage_start_date,
  product_location,
  SPLIT_PART(line_item_resource_id, 'table/', 2) AS line_item_resource_id,
  CASE
    WHEN line_item_usage_type LIKE '%CapacityUnit%' THEN 'DynamoDB Provisioned Capacity'
    WHEN line_item_usage_type LIKE '%HeavyUsage%' THEN 'DynamoDB Provisioned Capacity'
    WHEN line_item_usage_type LIKE '%RequestUnit%' THEN 'DynamoDB On-Demand Capacity'
    ELSE 'DynamoDB Usage'
  END AS case_line_item_usage_type,
  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 case_purchase_option,
  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 case_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_blended_cost,
  SUM(CAST(reservation_unused_quantity AS DOUBLE)) AS sum_reservation_unused_quantity,
  SUM(CAST(reservation_unused_recurring_fee AS DECIMAL(16,8))) AS sum_reservation_unused_recurring_fee,
  reservation_reservation_a_r_n
FROM 
  ${table_name} 
WHERE 
  {$date_filter}
  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,
  DATE_FORMAT(line_item_usage_start_date,'%Y-%m'),
  product_location,
  SPLIT_PART(line_item_resource_id, 'table/', 2),
  6, -- refers to case_line_item_usage_type
  7, -- refers to case_purchase_option
  8, -- refers to case_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) AS sum_line_item_usage_amount,
  SUM(line_item_unblended_cost) AS 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) AS sum_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) AS sum_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) AS ri_sp_upfront_fees
FROM 
  ${table_name} 
WHERE 
  ${date_filter} 
  AND product_product_name = 'Amazon Redshift'
  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,
  SPLIT_PART(line_item_resource_id,':',7),
  line_item_operation,
  line_item_usage_type,
  line_item_line_item_type,
  pricing_term,
  product_usage_family,
  product_product_family
ORDER BY 
  day_line_item_usage_start_date,
  product_product_family,
  sum_line_item_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') 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 AS case_purchase_option,
  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) AS sum_line_item_usage_amount,
  SUM(CASE
    WHEN line_item_line_item_type = 'SavingsPlanNegation' THEN 0 
    ELSE line_item_unblended_cost 
  END) AS 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) AS sum_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) AS sum_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) AS ri_sp_upfront_fees
FROM 
  ${table_name} 
WHERE 
  ${date_filter} 
  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'),
    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