GCP Billing export to BigQuery and Datastudio Visualization

readme.md

GCP billing report in Datastudio

Datastudio Report link

https://datastudio.google.com/s/g3l_HO3I3NA

Send me message/email for requesting access so you can simply copy the report :)

bq_dataaccess_view.sql

SELECT
    timestamp AS Date,
    resource.labels.project_id AS ProjectId,
    protopayload_auditlog.serviceName AS ServiceName,
    protopayload_auditlog.methodName AS MethodName,
    protopayload_auditlog.status.code AS StatusCode,
    protopayload_auditlog.status.message AS StatusMessage,
    protopayload_auditlog.authenticationInfo.principalEmail AS UserId,
    protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobName.jobId AS JobId,
    protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobConfiguration.query.query AS Query,
    protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobConfiguration.query.destinationTable.projectId AS DestinationTableProjectId,
    protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobConfiguration.query.destinationTable.datasetId AS DestinationTableDatasetId,
    protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobConfiguration.query.destinationTable.tableId AS DestinationTableId,
    protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobConfiguration.query.createDisposition AS CreateDisposition,
    protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobConfiguration.query.writeDisposition AS WriteDisposition,
    protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobConfiguration.dryRun AS DryRun,
    protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatus.state AS JobState,
    protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatus.error.code AS JobErrorCode,
    protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatus.error.message AS JobErrorMessage,
    protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.createTime AS JobCreateTime,
    protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.startTime AS JobStartTime,
    protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.endTime AS JobEndTime,
    protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.billingTier AS BillingTier,
    protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalBilledBytes AS TotalBilledBytes,
    protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalProcessedBytes AS TotalProcessedBytes,
    protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalBilledBytes / 1000000000 AS TotalBilledGigabytes,
    (protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalBilledBytes / 1000000000) / 1000 AS TotalBilledTerabytes,
    ((protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalBilledBytes / 1000000000) / 1000) * 5 AS TotalCost,
    1 AS Queries
-- FROM (TABLE_QUERY([google.com:bin-wus-learning-center:bqlogging],'table_id CONTAINS "cloudaudit_googleapis_com_data_access"'))
FROM `google.com:bin-wus-learning-center.bqlogging.cloudaudit_googleapis_com_data_access*`
WHERE
  protopayload_auditlog.serviceName = 'bigquery.googleapis.com'
  AND protopayload_auditlog.methodName = 'jobservice.jobcompleted'
  AND protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.eventName = 'query_job_completed'

datastudio_custom_query.sql

SELECT
    month,
    balance,
    future_balance
FROM
    (SELECT
        FORMAT_TIMESTAMP('%Y%m', usage_end_time) as month,
        SUM(cost) + SUM((SELECT SUM(amount) FROM UNNEST(credits))) as balance,
        null as future_balance
    FROM `google.com:bin-wus-learning-center.billing.gcp_billing_export_v1_019673_59DCE1_43A0E8`
    WHERE FORMAT_TIMESTAMP('%Y%m', usage_end_time) >= FORMAT_DATE('%Y%m', DATE_ADD(CURRENT_DATE(), INTERVAL -3 MONTH))
        AND FORMAT_TIMESTAMP('%Y%m', usage_end_time) != FORMAT_TIMESTAMP('%Y%m', CURRENT_TIMESTAMP())
    GROUP BY month
    ORDER BY month)
    UNION ALL
    (SELECT
       FORMAT_TIMESTAMP('%Y%m', CURRENT_TIMESTAMP()) as month,
       null as balance,
       SLOPE * (EXTRACT(YEAR FROM CURRENT_TIMESTAMP()) * 12 + EXTRACT(MONTH FROM CURRENT_TIMESTAMP())) + INTERCEPT as future_balance
    FROM
        (SELECT SLOPE,
               (SUM_OF_Y - SLOPE * SUM_OF_X) / N AS INTERCEPT,
               CORRELATION
        FROM (
            SELECT N,
                   SUM_OF_X,
                   SUM_OF_Y,
                   CORRELATION * STDDEV_OF_Y / STDDEV_OF_X AS SLOPE,
                   CORRELATION
            FROM (
                SELECT COUNT(*) AS N,
                       SUM(X) AS SUM_OF_X,
                       SUM(Y) AS SUM_OF_Y,
                       STDDEV_POP(X) AS STDDEV_OF_X,
                       STDDEV_POP(Y) AS STDDEV_OF_Y,
                       CORR(X,Y) AS CORRELATION
                FROM (
                    SELECT
                        EXTRACT(YEAR FROM usage_end_time) * 12 + EXTRACT(MONTH FROM usage_end_time) as X,
                        SUM(cost) + SUM((SELECT SUM(amount) FROM UNNEST(credits))) as Y
                    FROM `google.com:bin-wus-learning-center.billing.gcp_billing_export_v1_019673_59DCE1_43A0E8`
                    WHERE FORMAT_TIMESTAMP('%Y%m', usage_end_time) >= FORMAT_DATE('%Y%m', DATE_ADD(CURRENT_DATE(), INTERVAL -3 MONTH))
                    GROUP BY X
                    ORDER BY X
                )
                WHERE X IS NOT NULL AND
                      Y IS NOT NULL))
        )
    )
    UNION ALL
    (SELECT
       FORMAT_DATE('%Y%m', DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH)) as month,
       null as balance,
       SLOPE * (EXTRACT(YEAR FROM DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH)) * 12 + EXTRACT(MONTH FROM DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH))) + INTERCEPT as future_balance
    FROM
        (SELECT SLOPE,
               (SUM_OF_Y - SLOPE * SUM_OF_X) / N AS INTERCEPT,
               CORRELATION
        FROM (
            SELECT N,
                   SUM_OF_X,
                   SUM_OF_Y,
                   CORRELATION * STDDEV_OF_Y / STDDEV_OF_X AS SLOPE,
                   CORRELATION
            FROM (
                SELECT COUNT(*) AS N,
                       SUM(X) AS SUM_OF_X,
                       SUM(Y) AS SUM_OF_Y,
                       STDDEV_POP(X) AS STDDEV_OF_X,
                       STDDEV_POP(Y) AS STDDEV_OF_Y,
                       CORR(X,Y) AS CORRELATION
                FROM (
                    SELECT
                        EXTRACT(YEAR FROM usage_end_time) * 12 + EXTRACT(MONTH FROM usage_end_time) as X,
                        SUM(cost) + SUM((SELECT SUM(amount) FROM UNNEST(credits))) as Y
                    FROM `google.com:bin-wus-learning-center.billing.gcp_billing_export_v1_019673_59DCE1_43A0E8`
                    WHERE FORMAT_TIMESTAMP('%Y%m', usage_end_time) >= FORMAT_DATE('%Y%m', DATE_ADD(CURRENT_DATE(), INTERVAL -3 MONTH))
                    GROUP BY X
                    ORDER BY X
                )
                WHERE X IS NOT NULL AND
                      Y IS NOT NULL))
        )
    )
ORDER BY month