Exploring the AWS Pricing API Using CloudQuery AWS Pricing Plugin

AWS Pricing is a publicly available API by AWS that lets you query pricing data for all AWS products and services. Here at CloudQuery, we simplified the process of retrieving this data to any database with our AWS Pricing Plugin.

Using this plugin, you can sync all the data available in the Pricing API to any destination available in our destination integrations. In the query examples below, we’ll use the PostgreSQL Destination Integration.

To begin, first download the CloudQuery CLI, and set up your CloudQuery configuration file by following the AWS Pricing Plugin docs. Then sync your data using our CLI:

cloudquery sync ./cloudquery-config

After completing a successful sync of the AWS pricing data to PostgreSQL, you will see three tables in your database. In this blog, we will focus on two of them: awspricing_service_products and awspricing_service_terms.

In the awspricing_service_products table, you get data about service and product configurations, like the instance type of an EC2 instance. In the awspricing_service_terms table, you get the pricing data, such as price per unit and method of pricing.

In both these tables, the interesting data is in a JSON column. For awspricing_service_products, it's the attributes column.

SELECT
 sku,
 product_family,
 attributes,
 attributes->>'instanceType' as instance_type,
 attributes->>'servicename' as service_name,
 attributes->>'regionCode' as region
FROM awspricing_service_products
WHERE product_family = 'Compute Instance'

In this example, you queried all the Compute Instance data and extracted the instance type, service name, and region from the attributes column (more properties like memory and vCPU are available for compute instances, and many other properties are available for other product families).

To add the pricing data for each of these options, you can join the awspricing_service_terms table using the column SKU, which is a unique identifier for a product (service code), usage type, and operation for an AWS resource.

WITH expanded_price_dimensions AS (
  SELECT
    st.sku,
    st.type,
    st.effective_date,
    jsonb_array_elements(st.price_dimensions) AS price_dimension
  FROM
    awspricing_service_terms AS st
)
SELECT
    sp.sku,
    sp.product_family,
    sp.attributes->>'regionCode' AS region,
    sp.attributes->>'instanceType' AS instance_type,
    epd.effective_date,
    epd.price_dimension->'pricePerUnit'->>'USD' AS price_per_unit_usd,
    epd.price_dimension->>'description' AS price_description,
    epd.price_dimension->>'unit' AS unit
FROM
    awspricing_service_products AS sp
JOIN
    expanded_price_dimensions AS epd
ON
    sp.sku = epd.sku
WHERE
    sp.attributes->>'servicecode' = 'AmazonEC2'
    AND epd.type = 'OnDemand';

Which would result in a table similar to this:

And if you remove the sp.attributes->>'servicecode' = 'AmazonEC2' filter, you will see results similar to this:

While this query is slightly more complex, what you do in the CTE is expand all the price dimensions because in some cases, there are multiple, not just one. After that, you join the data and extract the price, unit type, and description.

With these examples, you are ready to explore the pricing data available from our AWS Pricing Plugin. With simple modifications to these queries, you can get the pricing data for any service and product available from AWS.

Ready to dive deeper into your AWS pricing data? Try CloudQuery for free today and gain comprehensive insights into AWS pricing data. Contact us or join our Community to connect with other users and our engineering team, and explore the full potential of CloudQuery.