I am using BigQuery to get GCP usage data using queries. I have a single query to get all details from GCP exported Bill data to BigQuery.
select sum(cost) as cost, sum(credits.amount) as credit FROM `TABLENAME`
LEFT JOIN UNNEST(credits) AS credits
WHERE invoice.month="202404"
AND
select sum(cost) as cost FROM `TABLENAME`
WHERE invoice.month="202404"
in both queries, I am getting different cost values. Why I am getting like this?
Above is a sample query.
Below one is my actual query. Here I am aggregating hourly data as daily data by filtering zero cost and credit amounts.
in this scenario, I am getting multiple duplicate rows in result.
It's Bigquery cost export data.
SELECT
billing_account_id,
service.id AS service_id,
service.description AS service_des,
sku.id AS sku_id,
sku.description AS sku_des,
FORMAT_DATETIME('%Y-%m-%d', usage_start_time) AS usage_date,
location.location AS location,
location.region AS region,
location.country AS country,
location.zone AS zone,
invoice.month AS invoice_mon,
currency,
AVG(currency_conversion_rate) as currency_conversion_rate,
cost_type,
resource.name AS resource_name,
resource.global_name AS res_global_name,
SUM(usage.amount_in_pricing_units) AS usage_amount_pricing_units,
usage.pricing_unit AS pricing_unit,
SUM(cost) AS cost,
SUM(cost_at_list) AS cost_at_list,
transaction_type,
seller_name,
adjustment_info.id AS adjustment_info_id,
adjustment_info.description AS adjustment_des,
adjustment_info.type AS adjustment_type,
adjustment_info.mode AS adjustment_mode,
SUM(price.effective_price) AS effective_price,
SUM(price.pricing_unit_quantity) AS pricing_unit_quantity,
project.id AS project_id,
project.number AS project_number,
project.name AS project_name,
SUM(IFNULL(credits.amount, 0)) AS credit_amount,
credits.type AS credit_type,
credits.id AS credit_id,
credits.name as credit_name,
credits.full_name AS credit_full_name,
TO_JSON_STRING(labels) AS labels,
TO_JSON_STRING(system_labels) AS system_labels
FROM `TABLENAME`
LEFT JOIN UNNEST(credits) AS credits
WHERE (cost!=0 OR credits.amount!=0) and invoice.month="202404"
GROUP BY
billing_account_id,
service_id,
service_des,
sku_id,
sku_des,
usage_date,
location,
region,
country,
zone,
invoice_mon,
currency,
cost_type,
resource_name,
res_global_name,
usage.pricing_unit,
transaction_type,
seller_name,
adjustment_info_id,
adjustment_des,
adjustment_type,
adjustment_mode,
project_id,
project_number,
project_name,
credit_type,
credit_full_name,
credit_id,
credit_name,
labels,
system_labels
HAVING
(cost!=0 OR credit_amount!=0)
ORDER BY
usage_date
This is my actual query, What changes I have to do to get the results without duplicates.
LinkedIn
Twitter
