FinOps Archives - Big Data Processing

Unveiling the Query

SELECT r, SPLIT(r, "/")[4] AS project_name, ARRAY_REVERSE(SPLIT(r, "/"))[0] AS resource_name, recommender_subtype as action, description, primary_impact.cost_projection.cost_in_local_currency. units AS cost_savings_per_month FROM <your_project>.recommendations.recommendations_export, UNNEST(target_resources) r WHERE recommender_subtype = "CHANGE_MACHINE_TYPE" AND TIMESTAMP_TRUNC(_PARTITIONTIME, DAY) = ( SELECT TIMESTAMP_TRUNC(MAX(_PARTITIONTIME), DAY) FROM <your_project>.recommendations.recommendations_export)

Breaking Down the Query

SELECT: The query selects essential fields like the resource and project names, recommendation action, description, and projected cost savings per month.

FROM: It sources data from recommendations.recommendations_export, extracting target resources using UNNEST.

WHERE: Filters recommendations to focus solely on those advocating for changing machine types. Additionally, it ensures we’re working with the latest data partition.

What It Means for You

By running this query, you gain insights into Compute Engine resources where adjusting machine types could lead to substantial cost savings. Each recommendation is accompanied by a description and projected monthly savings, empowering you to make informed decisions about scaling down resources without sacrificing performance.

Conclusion

Cost optimization in the cloud isn’t just about cutting corners; it’s about strategic resource allocation. With the provided BigQuery query, identifying Compute Engine resources ripe for scale down becomes a streamlined process. Embrace data-driven decision-making to optimize your cloud costs effectively.

Stay tuned for more insights and tips on maximizing the value of your cloud investments!

Remember, when it comes to cloud cost optimization, every adjustment counts. Start uncovering opportunities for savings today with our BigQuery-powered approach. Your bottom line will thank you.

Would you like to dive deeper into any specific aspect or have further queries? Feel free to reach out!

In recent days, I was working on costs optimization on the Google Cloud Platform (GCP) . Google offers recommendations for cost savings and security at the project level. However, managing these recommendations across numerous projects can be arduous, particularly in scenarios like mine where we oversee approximately 100 projects, with limited access to many. Fortunately, redirecting all recommendations to BigQuery and leveraging SQL’s analytical capabilities proved to be a game-changer. Additionally, configuring Looker Studio facilitated streamlined visualization.

In this blog post, I’ll illustrate the process of redirecting GCP recommendations to Google BigQuery and uncovering cost-saving recommendations specifically for idle resources.

Redirecting GCP Recommendations to BigQuery

you will need a service account created at org level with following roles
- roles/bigquery.dataEditor
- roles/recommender.exporter
Choose a project to which you want to send GCP recommandation, in our case we have created a separate project for billing and recommendation. Seperate project makes it easier for access control.
Now navigate to google bigquery and open data transfers from left side menu and click on CREATE TANSFERS.
Choose the Recommander V1 from the option as shown in screenshot below and fill our other information
Once the data transfer is executed successfully you will be able to see following two tables in the dataset
- insights_export
- recommendations_export

Analyse Data For GCP for Cost Saving

Following query will give you list of all the idle resources which could be either deleted or shutdown to save cost. Query will show you project name, resource name, action to be taken, description and how much you will cost you will be saving in your local currency.

SELECT
  r,
  SPLIT(r, "/")[4] AS project_name,
  ARRAY_REVERSE(SPLIT(r, "/"))[0] AS resource_name,
   recommender_subtype as action,
  description,
  primary_impact.cost_projection.cost_in_local_currency. units AS cost_savings_per_month
FROM
  you_project.dataset.recommendations_export,
  UNNEST(target_resources) AS r
WHERE
  TIMESTAMP_TRUNC(_PARTITIONTIME, DAY) = (select TIMESTAMP_TRUNC(max(_PARTITIONTIME), DAY) from your_project.your_dataset.recommendations_export)
  AND primary_impact.category = "COST"
  AND state = "ACTIVE"
  AND recommender LIKE "%IdleResourceRecommender"

Visualizing Data

In order to track if we are implementing this suggestion or not I further created a dashboard using looker studio.

Query for looker studio dashboard.

Following query will give you cost optimization recommendation of last 30 days.

SELECT
  r,
  SPLIT(r, "/")[4] AS project_name,
  ARRAY_REVERSE(SPLIT(r, "/"))[0] AS resource_name,
   recommender_subtype as action,
  description,
  primary_impact.cost_projection.cost_in_local_currency. units AS cost_savings_per_month, 
  state, 
  date(last_refresh_time) as date
FROM
  you_project.dataset.recommendations_export,
  UNNEST(target_resources) AS r
WHERE
  TIMESTAMP_TRUNC(_PARTITIONTIME, DAY) > TIMESTAMP(current_date() - 30)
  AND primary_impact.category = "COST"
  AND state = "ACTIVE"
  AND recommender LIKE "%IdleResourceRecommender"

Dashboard

This dashboard provides valuable insights indicating that our efforts towards cost optimization are bearing fruit. The noticeable decrease in overall recommendations signifies successful implementation of our strategies, affirming that we are indeed on the right track.

Category: FinOps

Optimize Your GCP Cloud Costs: Identifying Compute Engine Resources for Scale Down