毎月の請求データから、Commited Use Discountsを申請するために月にどれだけのGCEリソース使ってるかを全部ならして見るためのクエリを書いてみました。
Commited Use Discountsとは？
Sustained Use Discounts(継続利用割引)よりも更に安くなるやつです。
こないだのNext17で発表されました。
継続利用割引は月の何%使ってたかで割引がきいてたんですが、これを予め使うことが分かっているなら先に予約しちゃえよ！ってことで割り引くやつです。
これで取った分は使わなくても料金がかかりますので、確実に使うだろうというリソース分だけ設定した方がいいでしょう。
ここから溢れた分は継続利用割引が適用されるので、もしかしたらこれとの組み合わせによっては併用せず継続利用割引のみ使った方が安くなる可能性はあるんじゃないかと思います。
対象者
- コンソールのレコメンドに言われるがままにマシンタイプを変える人
- カスタムマシンタイプとスタンダードを混在して使ってる人
- 請求書を見て自分で計算するのがめんどくさい人
- よくインスタンスを消したり作ったりする人
一応請求書から計算しても出せるはずです。
usage_amountの単位がカスタムマシンタイプとスタンダードで違ってたりしてツラいところを吸収してならしてくれます。
クエリ
長くて泥臭いクエリを書いてしまいました。
月別、プロジェクト別、リージョン別に出力されます。<project>.<dataset>.<billing_table>は自分のデータに置き換えてください。
#standardSQL
SELECT
EXTRACT(YEAR FROM s.summary_month) year,
EXTRACT(MONTH FROM s.summary_month) month,
s.project_id,
s.region,
ROUND(SUM(
CASE core_ram
WHEN 'CORE' THEN cpu_count * monthly_cpu_count
ELSE NULL END
), 2) monthly_core,
ROUND(SUM(
CASE
WHEN core_ram = 'CORE' AND monthly_mem_size IS NOT NULL THEN monthly_cpu_count * monthly_mem_size
ELSE monthly_mem_size
END
), 2) monthly_ram
FROM (
SELECT
b.summary_month,
b.project_id,
b.region,
b.discount,
b.core_ram,
b.cpu_count,
CASE
WHEN core_ram = 'CORE' THEN ROUND(SUM(usage_amount) / 60 / 60 / 24 / date_count, 4)
ELSE NULL
END monthly_cpu_count,
CASE
WHEN core_ram = 'RAM' AND machine_type = 'CUSTOM' THEN ROUND(SUM(usage_amount) / 60 / 60 / 24 / date_count / 1024 / 1024 / 1024, 4)
WHEN machine_type = 'STANDARD' THEN
CASE cpu_count
WHEN 1 THEN 3.75
WHEN 2 THEN 7.5
WHEN 4 THEN 15
WHEN 8 THEN 30
WHEN 16 THEN 60
WHEN 32 THEN 120
WHEN 64 THEN 240
ELSE NULL
END
WHEN machine_type = 'HIGHMEM' THEN
CASE cpu_count
WHEN 2 THEN 13
WHEN 4 THEN 26
WHEN 8 THEN 52
WHEN 16 THEN 104
WHEN 32 THEN 208
WHEN 64 THEN 416
ELSE NULL
END
WHEN machine_type = 'HIGHCPU' THEN
CASE cpu_count
WHEN 2 THEN 1.8
WHEN 4 THEN 3.6
WHEN 8 THEN 7.2
WHEN 16 THEN 14.4
WHEN 32 THEN 28.8
WHEN 64 THEN 57.6
ELSE NULL
END
ELSE NULL
END monthly_mem_size
FROM (SELECT
-- monthly
TIMESTAMP(DATE_TRUNC(DATE(end_time), MONTH)) AS summary_month,
-- date count of month
DATE_DIFF(
DATE_ADD( DATE_TRUNC( DATE_ADD(DATE(end_time), INTERVAL 1 MONTH), MONTH), INTERVAL -1 DAY), DATE_TRUNC(DATE(end_time), MONTH),
DAY
) + 1 AS date_count,
-- Discount Record
REGEXP_CONTAINS(resource_type, r'Sustained Use Discount') discount,
-- CPU or RAM
CASE
WHEN REGEXP_CONTAINS(resource_type, r'Core|CPU') THEN 'CORE'
WHEN REGEXP_CONTAINS(resource_type, r'Ram') THEN 'RAM'
ELSE 'UNKNOWN'
END core_ram,
-- Custom Instance is 1 CPU
IFNULL(CAST(REGEXP_EXTRACT(resource_type, r'N1 (\d{1,2}) VCPU') AS INT64), 1) cpu_count,
-- instance region
REGEXP_EXTRACT(resource_type, r' running in (.*)') region,
-- machine_type
CASE
WHEN REGEXP_CONTAINS(resource_type, r'Standard') THEN 'STANDARD'
WHEN REGEXP_CONTAINS(resource_type, r'Highcpu') THEN 'HIGHCPU'
WHEN REGEXP_CONTAINS(resource_type, r'Highmem') THEN 'HIGHMEM'
WHEN REGEXP_CONTAINS(resource_type, r'Custom') THEN 'CUSTOM'
ELSE NULL
END machine_type,
resource_type,
usage.amount usage_amount,
end_time,
cost,
project.id project_id
FROM
`<project>.<dataset>.<billing_table>`
WHERE
product = 'Compute Engine'
AND REGEXP_CONTAINS(usage.unit, r'seconds|byte-seconds')
AND REGEXP_CONTAINS(resource_type, r'Custom|Standard|Highmem|Highcpu')
AND REGEXP_CONTAINS(resource_type, r'Licensing|Preemptible') = FALSE ) b
GROUP BY
b.summary_month,
b.project_id,
b.region,
b.cpu_count,
b.discount,
b.core_ram,
b.machine_type,
date_count ) s
WHERE
s.discount = FALSE
GROUP BY
s.summary_month,
s.project_id,
s.region
ORDER BY
summary_month DESC,
project_id,
region
マシンタイプ別にメモリ数を条件分岐させたりどうかと思うところが多々あるのでこうしたらいいよ！とかあったらやさしく教えてください。
あとBillingの出力値が変わったら要修正ですね。
継続利用割引のデータもdiscountとして出したんですけど、最終的には使ってないです。
色々考えてたら別になくてもいっかと思ってそのまま放置してあります。
こんな感じ
ほぼ動かしっぱなしのプロジェクトが正しくでてたのでたぶん大丈夫でしょう。
これを見ながら三年後も予想して予約するリソース数を計算すればいいと思います。
ここからあぶれた分のリソースについてはこれまで通り継続利用割引が適用されますよ。
来月の請求がどうなるか楽しみだ。
Purcaseしてみる
やってみましょう。
もうキャンセルできんからな？と強めに押されます。
こわE。
こんな感じになりました。
注意事項
※これを使うことによりGCPの請求が予想外のものになっても当方では責任は負いかねますのでご了承ください。