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

Comments

Popular posts from this blog

Load Testing Using Gatling

Bengali English word definition in Google Dictionary

Automatically Deploying to Google Appengine from Github using Cloud Build