How do I query billing information for Amazon Redshift Serverless?

4 minute read
0

I want to query my Amazon Redshift Processing Units (RPU) usage so that I can calculate billing details for Redshift Serverless.

Short description

You incur costs based on the workloads that you run. Workloads are measured in RPU-hours and are accrued on a per-second basis with a 1-minute minimum charge. For more information, see Billing for Amazon Redshift Serverless.

Resolution

To get information about your costs, use a client tool or query editor that's connected to your Amazon Redshift Serverless account to run the following queries. For more information, see Connecting to Amazon Redshift Serverless.

Note: In the following queries, replace AWS_REGION_PRICE with the price per RPU hour for your AWS Region. For information about RPU costs, see Amazon Redshift Serverless on the Amazon Redshift pricing page.

The cost output that you receive from the following queries are approximations. The outputs can vary from the Amazon Redshift Serverless Cost that's displayed in AWS Billing console.

Cost per day

To determine the cost that you incurred each day for the last 7 days, run the following query:

SELECT date_trunc('d',start_time) "Day", 
    (
        sum(charged_seconds)/3600::double precision
    ) * AWS_REGION_PRICE as cost_incurred 
FROM   sys_serverless_usage
GROUP  BY 1
ORDER  BY 1; 

Note: Amazon Redshift Serverless stores only 5-7 days of historical data. You might not be able to retrieve data that's older than 7 days.

Example output:

day                 |    cost_incurred($)
--------------------+--------------------------
2024-05-27 00:00:00 |    1.536    
2024-05-29 00:00:00 |    59.903999999999996    
2024-05-30 00:00:00 |    13.824    
2024-05-31 00:00:00 |    0.768    

RPU usage and cost for a specific day

To check the RPU usage and total cost for the previous day, run the following query. To query other days, change the -1 in dateadd(day,-1,sysdate) to another negative number:

SELECT date_trunc('d',start_time)  as "day",
       (
        sum(charged_seconds)/3600::double precision
       ) * <AWS_REGION_PRICE> as cost_incurred,
       min(compute_capacity) min_rpu,
       max(compute_capacity) max_rpu,
       avg(compute_capacity) avg_rpu,
       max(data_storage) as max_storage
FROM   sys_serverless_usage
WHERE "day" = date_trunc('d', dateadd(day,-1,sysdate))
GROUP  BY 1
ORDER  BY 1 

Example output: 

day                  | cost_incurred ($) |    min_rpu |  max_rpu  |     avg_rpu | max_storage
---------------------+-------------------+------------+-----------+-------------+-------------
2024-05-30 00:00:00  |    13.824         |    0       |  128      |    92.16    | 3166    

RPU usage and cost per query

To get the approximate RPU usage and cost for each query that you ran on a specific date, run the following query:

with
daily_cost as (
    select
        trunc(start_time) "YYY-MM-DD",
        max(compute_capacity) max_compute_capacity,
        (sum(charged_seconds) / 3600:: double precision) * <AWS_REGION_PRICE> as daily_cost
    from
        sys_serverless_usage
    group by
        1
),
daily_queries as (
    SELECT
        *,
        elapsed_time / total_time_for_day:: double precision as perc
    from
        (
            SELECT
                
                query_id,
                user_id,
                query_text,
                trunc(start_time) "Day",
                elapsed_time,
                sum(elapsed_time) over (partition by trunc(start_time)) total_time_for_day
            FROM
                sys_query_history
        )
)
select
    q.*,
    c.daily_cost * q.perc as query_cost,
    c.*
from
    daily_cost c
    join daily_queries q using ("Day") where Day ='<start_date>';
   

Note: Replace YYYY-MM-DD with the specific date to that you want query. The date must be in the YYYY-MM-DD format.

Example output: 

query_id| user_id | day        | elapsed_time | total_time_for_day | perc                   | query_cost             | day        | max_compute_capacity |    daily_cost  | query_text
--------+---------+------------+--------------+--------------------+------------------------+------------------------+------------+----------------------+------------------+--------------------------------------+
5240721 | 101     | 2024-05-30 | 174646       | 22720291           | 0.00768678534971229    | 0.10921384624871222    | 2024-05-30 | 128                  | 14.208         | SELECT table_name as view_name, table_type, schema_name FROM SVV_ALL_TABLES WHERE database_name='dev' AND schema_name='lakeformation_schema' and table_type IN ('VIEW');
                  
5240073 | 101     | 2024-05-30 | 765          | 22720291           | 0.00003367034339480951 | 0.00047838823895345354 | 2024-05-30 | 128                  | 14.208         | select oid, lanname from pg_language order by lanname asc

Important: The output is an approximation. The query determines the total cost incurred on the date and distributes the cost between all launched queries based on the elapsed query time.

Related information

SYS_SERVERLESS_USAGE

SYS_QUERY_HISTORY

AWS OFFICIAL
AWS OFFICIALUpdated a month ago