Skip to content

Tag: finops

Unlocking GCP Cost Optimization Using Recommendation and BigQuery: A FinsOps GuideCopy HDFS data from on-prem to cloud storage using distcp

Unlocking GCP Cost Optimization Using Recommendation and BigQuery: A FinsOps Guide

Published by Gaurang on March 5, 2024

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.

Leave a Comment