Commited Use Discounts用にクエリを書いてみた


毎月の請求データから、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`として出したんですけど、最終的には使ってないです。
色々考えてたら別になくてもいっかと思ってそのまま放置してあります。

## こんな感じ

![New_Query.png](https://qiita-image-store.s3.amazonaws.com/0/8637/0692f769-d7cd-549e-12e9-e22aae13e0aa.png "New_Query.png")

ほぼ動かしっぱなしのプロジェクトが正しくでてたのでたぶん大丈夫でしょう。
これを見ながら三年後も予想して予約するリソース数を計算すればいいと思います。

ここからあぶれた分のリソースについてはこれまで通り継続利用割引が適用されますよ。

来月の請求がどうなるか楽しみだ。

## Purcaseしてみる

やってみましょう。

![スクリーンショット 2017-04-04 9.33.54.png](https://qiita-image-store.s3.amazonaws.com/0/8637/5ebaba27-e80f-228e-04d5-5eda44ae6ee2.png "スクリーンショット 2017-04-04 9.33.54.png")

もうキャンセルできんからな？と強めに押されます。
こわE。

![スクリーンショット 2017-04-04 9.34.28.png](https://qiita-image-store.s3.amazonaws.com/0/8637/61416b55-9f3a-24a6-923b-9e4c0bb06fa1.png "スクリーンショット 2017-04-04 9.34.28.png")


こんな感じになりました。

![スクリーンショット 2017-04-04 9.11.25.png](https://qiita-image-store.s3.amazonaws.com/0/8637/4e467565-6aa1-0b9f-92ea-b080c01dace1.png "スクリーンショット 2017-04-04 9.11.25.png")

## 注意事項

**※これを使うことによりGCPの請求が予想外のものになっても当方では責任は負いかねますのでご了承ください。**