AWS
Tutorials
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 simplifed 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 plugins. In the query examples below, we’ll use the PostgreSQL Destination Plugin.
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-configAfter completing a successful sync of the AWS pricing data to Postgres, 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:
| sku | product_family | region | instance_type | effective_date | price_per_unit_usd | price_description | unit |
|---|---|---|---|---|---|---|---|
| EJ5QZDS2Q5TZUCNP | Compute Instance (bare metal) | me-south-1 | g4dn.metal | 2024-06-01 | 9.7210000000 | $9.721 per Unused Reservation SUSE g4dn.metal instance | Hrs |
| ENA7E6VASMYE9AEW | Compute Instance | ca-central-1 | g4dn.4xlarge | 2024-06-01 | 0.0000000000 | $0.00 per Dedicated Reservation SUSE g4dn.4xlarge instance | Hrs |
| VHGKKHY2K6TR9AFJ | Compute Instance | ap-northeast-2 | r5ad.2xlarge | 2024-06-01 | 0.8000000000 | $0.80 per Dedicated RHEL r5ad.2xlarge Instance | Hrs |
| 36JTR2DQPBVQ3RPX | Compute Instance | ap-northeast-2 | i4i.8xlarge | 2024-06-01 | 0.0000000000 | $0.00 per Reservation Windows with SQL Server i4i.8xlarge instance | Hrs |
| NJW5JWT9Z8TAHHBX | Compute Instance (bare metal) | me-south-1 | r5.metal | 2024-06-01 | 19.0950000000 | $19.095 per Unused Reservation RHEL with SQL Server r5.metal instance | Hrs |
And if you remove the
sp.attributes->>'servicecode' = 'AmazonEC2' filter, you will see results similar to this:| sku | product_family | region | instance_type | effective_date | price_per_unit_usd | price_description | unit |
|---|---|---|---|---|---|---|---|
| ZMZHSJSJK564HSMU | Direct Connect | us-east-2 | None | 2024-05-01 | 1.6500000000 | $1.65 per connected HC-5G port-hour (or partial hour) | hours |
| QBEEWE4UKYRAM42X | Compute Instance | ap-northeast-2 | m5.4xlarge | 2024-06-01 | 0.9720000000 | $0.972 per Unused Reservation Ubuntu Pro m5.4xlarge instance | Hrs |
| VEFTTRHQSPAV5GN | Data Transfer | None | None | 2024-06-01 | 0.0000000000 | $0.00 per GB – US West (Verizon) data transfer | GB |
| 866YX28WTJXGZUE4 | Compute Instance | ap-northeast-2 | r6i.large | 2024-06-01 | 0.4672000000 | $0.4672 per Dedicated RHEL with SQL Standard r6i.large instance | Hrs |
| 4FY48AB87PFP3CQ | GuardDuty | ca-west-1 | None | 2024-05-01 | 0.0000000000 | $0.00 per ACU / month analyzed (free trial) | ACU-Months |
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.
Written by Jonathan Sarig
Jonathan is a software engineer at CloudQuery with a particular interest in machine learning. He primarily works in golang but also has experience in (and a passion for) Rust.
