BigQueryでデータマートを作成するときのtips

はじめに

データ分析の現場でデータマートを作成するときのtipsを書きました。

ここで言うデータマートはいわゆる中間テーブル

• 生のログを分析しやすいように加工したテーブル
• 集計結果のテーブル
• ダッシュボードの呼び出し元テーブル

などを指します。

クエリ部分はBigQueryに限定して書いていますが、
考え方は他のSQLにも適用できると思います。

記事の最後にtipsを適用したクエリ例を載せています。

tips

クエリの説明を書く

• まずは「何をしているクエリなのか」ファイル名で説明する

• ファイル名だけでは足りない情報をクエリ冒頭にコメントとして書く

  • クエリの役割
  • ほかのクエリとの関連性や前後関係

マジックナンバーなどの定数を冒頭で宣言する

• 保守性が高まる

• 定数名からマジックナンバーの意味、役割がわかる

UDFを使う

• UDF（User Defined Function）：ユーザー関数定義

• 使いまわしができる

  • コーディングのコストが下がる
  • レビューのコストが下がる
  • バグ混入のリスクが下がる

• 読みやすくなる

  • 単純に読む量が減る
  • 関数から挙動が予想できるので枝葉に捕われない、全体の流れを追いやすい

• 参考

  • ユーザー定義の関数  |  BigQuery  |  Google Cloud

  • BigQueryでユーザー定義関数（UDF）は武器になるという話 - ZOZO TECH BLOG

datasetから呼び出すテーブル名は``で括る

• 下記を一目で区別するため
  • datasetから呼び出したテーブル(``で括る)
  • クエリ内で作成したテーブル(``で括らない)

スキーマ設定をするDescriptionを書く

• テーブル概要を把握するためだけに作成クエリや定義書を探して読むのは手間

• BQの画面上でテーブルやカラムの説明を見れると便利
  
  

画像引用：
BigQuery上のメタデータをコード管理する - istyle Tech Blog

partitionを設定する

• スキャン量を減らしてパフォーマンスを高めるため
• 課金額を減らすため
• partitionが無い場合、たとえWHERE句やLIMITを書いたとしてもBigQuery はテーブル全体をフルスキャンする
• 参考
  • 分割テーブルの作成  |  BigQuery  |  Google Cloud

出力テーブルは主キーや日付でORDER BYする

• プレビュー上で見やすい
  • データの中身を理解しやすい
  • 出力結果に違和感がないかの確認もしやすい

テーブル作成日時のカラムを作成

• 正直これは慣習的にやっています
  「分析の中間テーブル」くらいであればテーブル更新時刻を見れば事足りるかもしれません

• テーブルの役割や作り方によってはcreated_atやupdated_atなどのカラム名が適切なケースがあると思います

クエリ例

ここまでのtipsを適用すると下記のようなクエリになります。
ファイル名：create_sales_data_mart.sql

-- このクエリはどういうテーブルを作成しているのか
-- 作成したテーブルは何に使っているのか。
-- ほかのクエリとの前後関係があれば書く。このクエリを実行する前提条件とか書く。

-- 定数宣言
DECLARE target_first_date DATE DEFAULT '2023-04-01';
DECLARE target_last_date DATE DEFAULT '2023-07-23';

-- UDF
CREATE TEMPORARY FUNCTION CALC_FY_FROM_DATE(base_date DATE)
  -- 日付から年度を算出する関数
  AS (
    CONCAT(
      'fy_',
      CAST(
        IF(EXTRACT(MONTH FROM base_date) >= 4
        ,EXTRACT(YEAR FROM base_date)
        ,EXTRACT(YEAR FROM base_date)-1
        ) AS STRING)
      )
    )
;

-- スキーマ設定
CREATE
OR  REPLACE TABLE `project_name.dataset_name.create_tbl_name`
  (
  xxx_date_col DATE OPTIONS(description='xxxの日付'),
  xxx_fy INTEGER OPTIONS(description='xxxの年度'),
  col_1 STRING OPTIONS(description='カラム1の説明。「各カテゴリ値が何を意味するか」などのメタデータもここに書く'),
  col_2 INTEGER OPTIONS(description='カラム2の説明'),
  col_3 TIMESTAMP OPTIONS(description='カラム3の説明'),
  update_datetime DATETIME OPTIONOS(description='テーブル作成日時')
  )
  PARTITION BY 
    TIMESTAMP_TRUNK(xxx_date, MONTH)
  OPTIONS(
    description="""
    このテーブルは●●から●●して作成
    ●●や●●に利用
    """
    )　AS (

-- （※）ここからテーブル作成クエリ
WITH hoge_tbl AS(
  SELECT
    col_1,
    col_2,
    col_3,    
  FROM 
    `project_name.dataset_name.tbl_name_hoge`
  ) 
SELECT
  t1.xxx_date,
  CALC_FY_FROM_DATE(t1.xxx_date) AS xxx_fy, -- UDFを使っているのでSELECT句がスッキリする
  t1.col_1,
  t2.col_2,
  t2.col_3,
  CURRENT_DATETIME('Asia/Tokyo') AS update_datetime,
FROM 
 `project_name.dataset_name.tbl_name_fuga` t1
WHERE 
  target_first_date <= xxx_date
  AND xxx_date <= target_last_date -- 冒頭で宣言した定数を使っている。条件変更したいときも該当箇所を探すことなく冒頭の定数を変えるだけ
LEFT JOIN
  hoge_tbl t2
  ON t1.col_1 = t2.col_1  
ORDER BY
  xxx_date
)
;

クエリが長そうに見えますが、コードレビューなどでメインで読むのは(※)の行からで十分です。

さいごに

「UDFの利用」や「データマートのdescriptionを書く」など細かいtipsばかりですが、
コードやテーブルを見る際の負担が減り、こういった積み重ねがチームでの生産性を少しずつ向上させると考えています。

誰かの生産性向上の一助になれば幸いです！