BigQuery にエクスポートした請求データから、プロジェクト別の料金を CSV ファイルにして GCS に出力する

やりたいこと

現在 BigQuery へ Google Cloud の請求データをエクスポートしています。

• 月が変わったときに先月分の請求額をプロジェクトごとに出力する
• Cloud Storage（以降 GCS）に CSV ファイルとして書き出す
• 月初に自動で行う

を BigQuery のクエリで実現できるかを検証しました。

クエリ

クエリ全文

DECLARE
  last_month STRING;
SET
  last_month = FORMAT_TIMESTAMP("%Y%m", DATE_ADD(CURRENT_DATE("Asia/Tokyo"), INTERVAL -1 MONTH));

EXECUTE IMMEDIATE
  FORMAT("""
  CREATE TABLE
    `<PROJECT ID>.<TABLE>.billing_test_processed_table_%s`
  PARTITION BY
    RANGE_BUCKET(export_id, GENERATE_ARRAY(0, 1, 1))
  CLUSTER BY
    export_id AS (
    SELECT
      `project`.name,
      ROUND((SUM(CAST(cost AS NUMERIC)) + SUM(IFNULL((
              SELECT
                SUM(CAST(c.amount AS NUMERIC))
              FROM
                UNNEST(credits) AS c), 0))),
          0) AS total_exact,
      CAST(FLOOR(1*RAND()) AS INT64) AS export_id
      FROM
        `<PROJECT ID>.<TABLE>.gcp_billing_export_v1_<請求先アカウントID>`
      WHERE
        invoice.month = '%s'
      GROUP BY
        project.name ) """,
      last_month,
      last_month);

  EXECUTE IMMEDIATE
    FORMAT("""
    EXPORT DATA
      OPTIONS ( uri = 'gs://<BUCKET>/gcp_billing_exports_%s_*.csv',
        format = 'CSV',
        OVERWRITE = TRUE,
        header = TRUE,
        field_delimiter = ',' ) AS (
      SELECT
        * EXCEPT(export_id)
      FROM
        `<PROJECT ID>.<TABLE>.billing_test_processed_table_%s`
      WHERE
        export_id = 0 ) """, last_month, last_month);

クエリの詳細

変数定義

DECLARE
  last_month STRING;
SET
  last_month = FORMAT_TIMESTAMP("%Y%m", DATE_ADD(CURRENT_DATE("Asia/Tokyo"), INTERVAL -1 MONTH));

先月の請求データを取得したいので、クエリ実行日の一か月前の日付を取得します。
また日付形式を invoice.month カラムと合わせて %Y%mとしました。

SELECT 文に変数を使用する

EXECUTE IMMEDIATE
  FORMAT("""
  <クエリ> ) """,
      last_month);

クエリの中に変数を使うときは、EXECUTE IMMEDIATE FORMAT を使います。
変数を使いたい箇所はクエリの中で %s を指定し、""" のあとに変数名を %s の順番に指定します。

パーティション分割テーブルを作成する

CREATE TABLE
    `<PROJECT ID>.<TABLE>.billing_test_processed_table_%s`
  PARTITION BY
    RANGE_BUCKET(export_id, GENERATE_ARRAY(0, 1, 1))
  CLUSTER BY
    export_id AS (
    <クエリ> )

後述のクエリでクエリ実行結果を GCS に出力します。
その際、エクスポート先文字列にワイルドカードを含める必要がありますが、ワイルドカードを指定すると出力結果のデータサイズに関わらず出力ファイルが分割されます。
実行前に分割ファイル数がわからない・出力データが分割される基準がわからないことから、結果を1ファイルに出力したいのですが、ワイルドカードは省略できません。
1ファイル出力を満たすために、ファイルに出力する前に、出力したいデータでパーティション分割テーブルを作成しました。
GENERATE_ARRAY() の第二引数にパーティション分割したい分割数を指定します。

パーティション分割テーブルに格納するデータを取得する

「パーティション分割テーブルを作成する」の<クエリ>に指定する内容です。

SELECT
  `project`.name,
  ROUND((SUM(CAST(cost AS NUMERIC)) + SUM(IFNULL((
          SELECT
            SUM(CAST(c.amount AS NUMERIC))
          FROM
            UNNEST(credits) AS c), 0))), 0) AS total_exact,
  CAST(FLOOR(1*RAND()) AS INT64) AS export_id
FROM
  `<PROJECT ID>.<TABLE>.gcp_billing_export_v1_<請求先アカウントID>`
WHERE
  invoice.month = '%s'    // %s は変数 last_month
GROUP BY
  project.name

2番目のカラム total_exact は下記公式ドキュメントを参考にしました。

日本円の場合、小数点以下は四捨五入するので ROUND() 関数で請求額を整えています。

3番目のカラム export_id はパーティションキーです。
RAND() にかける数は「パーティション分割テーブルを作成する」の GENERATE_ARRAY() の第二引数と同じ数を指定します。

GCS に CSV ファイルとして出力する

EXPORT DATA
  OPTIONS ( uri = 'gs://<BUCKET>/gcp_billing_exports_%s_*.csv',
    format = 'CSV',
    OVERWRITE = TRUE,
    header = TRUE,
    field_delimiter = ',' ) AS (
  SELECT
    * EXCEPT(export_id)
  FROM
    `<PROJECT ID>.<TABLE>.billing_test_processed_table_%s`
  WHERE
    export_id = 0 )

EXPORT DATA OPTIONS() で出力先や形式を指定します。
SELECT カラム指定部分の * EXCEPT(export_id) は、export_id カラム以外の全カラムが出力されます。

クエリのスケジューリング

クエリエディタの「スケジュール」ボタンからクエリ実行のスケジューリングができます。

画像部分は割愛しましたが、スケジュールしたクエリの実行結果を任意のテーブルに書き込むようにスケジュール設定からできるようです。

参考

以上です、どなたかの参考になれば幸いです。