#!/usr/bin/env python

# 1. pip install google-cloud-bigquery
# 2. set up your default credentials
# 3. profit

import uuid
from google.cloud import bigquery
client = bigquery.Client()

project = "YOUR_PROJECT"
table   = "YOUR_BILLING_EXPORT_TABLE_v1"

query_job = client.run_async_query(str(uuid.uuid4()), """
    SELECT sku.description, service.description, usage_start_time, usage_end_time, project.id, project.name, cost, labels.value, credits.name, credits.amount
           FROM flatten([%s:%s], credits)
           WHERE (labels.key = 'host' OR labels.key is null)
           ORDER BY usage_start_time
    """ % (project, table))

query_job.begin()
query_job.result()

destination_table = query_job.destination
destination_table.reload()

total = 0
for row in destination_table.fetch_data():
    sku, service, start_time, end_time, project_id, project_name, cost, host, credits_name, credits_amount = row
    cost = float(cost)
    if credits_amount is None:
        credits_amount = 0
    else:
        credits_amount = float(credits_amount)
    start_time = start_time.strftime("%Y-%m-%d %H:%M:%S")
    end_time = end_time.strftime("%Y-%m-%d %H:%M:%S")
    print("%-20s\t%-20s\t%s\t%s\t%-70s\t%s\t%15.4f\t%s\t%15.4f" % (start_time, end_time, project_name, host, service, sku, cost, credits_name, credits_amount))