which is the best way to query GCP usage cost from BigQuery in java?

Question

while researching on bigquery, pricing depends on amount of data processed so that have to query cost data in a efficient manner.

// Aim is to get cost usage for June month, values may update after June month also as a adjustment.
// so I am querying like this by assuming after next month's 10 th there won't be any updates.
SELECT
  *
EXCEPT (a, b, c) // some unwanted columns
FROM `project.dataset.gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX` where invoice.month = "202306" and export_time<= TIMESTAMP("2023-07-10");

But this query proccessing more bytes. is there any efficient way to achieve this?

Did you try to narrow the export time with a lower bound? `and export_time >= TIMESTAMP("2023-06-01")` — guillaume blaquiere, Jul 24 '23 at 16:37
It's classical BigQuery optimisation. It depends on what you do, what you want, the most common queries, their frequency, their latency,... — guillaume blaquiere, Jul 25 '23 at 11:41

score 2 · Accepted Answer · answered Jul 26 '23 at 22:34

According to @guillaume blaquiere, you have to replace your query with this one: and export_time >= TIMESTAMP("2023-06-01") to narrow down the expot time. You can also use _PARTITIONTIME:

SELECT
  *
EXCEPT (a, b, c) // some unwanted columns
FROM `project.dataset.gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX`
WHERE _PARTITIONTIME BETWEEN TIMESTAMP("2023-06-01") AND TIMESTAMP("2023-06-30")
AND export_time <= TIMESTAMP("2023-07-10");

Posting this answer as a Community Wiki for the benefit of the community that might encounter this use case in the future.

Please feel free to edit this answer for additional information and if there are other possible workarounds/direct solutions for this use case.

which is the best way to query GCP usage cost from BigQuery in java?

1 Answers1