Pages

Tuesday, January 15, 2019

Analysing Google Cloud Platform Billing Data

No matter if you are a large enterprise or small startup, or even individual developers, you probably need to to know what you are paying for when it comes to cloud bill. in GCP you can export your billing data to Google BigQuery and then run queries on it to know where you are spending more which can help you control the cost you are incurring to run the infrastructure and app.

Daily spend
SELECT
  India_date,
   ROUND(SUM(cost)) cost
FROM (
  SELECT
    DATE(DATETIME(usage_end_time,"Asia/Kolkata")) India_date,
    cost
  FROM
    `YOUR_BILLING_EXPORT_TABLE`
  WHERE
    project.id = 'YOUR_PROJECT_ID'
    AND _PARTITIONTIME >= TIMESTAMP("2019-01-16")
    AND _PARTITIONTIME <= TIMESTAMP("2019-01-20") )
GROUP BY
  India_date
Order by India_date
Result
GCP Cost per day, taking timezone into account.


 Cost By Services
    SELECT
      item,
      cost
    FROM (
          SELECT
            service.description item,
            ROUND(SUM(cost)) cost
          FROM
            `YOUR_BILLING_EXPORT_TABLE`
          WHERE
               project.id = 'YOUR_PROJECT_ID'
            AND _PARTITIONTIME >= TIMESTAMP("2018-11-01")
            AND _PARTITIONTIME <= TIMESTAMP("2018-11-30")
          GROUP BY
            service.description )
    ORDER BY
      cost desc;
Result

Cost by GCP Service


Similarly, other queries can be executed for drilling further down within a service. i.e. the App Engine cost includes Cloud Datastore, networking, disks etc. One can find the breakdown with the following query.

 Cost by SKU for a specific service 
 SELECT
      item,
      cost
    FROM (
          SELECT
            sku.description item,
            ROUND(SUM(cost)) cost
          FROM
            `YOUR_BILING_EXPORT_TABLE`
          WHERE
             project.id = 'YOUR_PROJECT_ID'
            AND _PARTITIONTIME >= TIMESTAMP("2018-11-01")
            AND _PARTITIONTIME <= TIMESTAMP("2018-11-30")
            AND  service.description = 'App Engine'
          GROUP BY
            sku.description  )
    ORDER BY
      cost desc;
Result

No comments:

Post a Comment