BigQueryStandardSQLChecklist

BigQuery StandardSQLのレビュー観点表です。

目的

BigQuery StandardSQLを複数人で利用し、運用にのせていく場合、早い段階からコードの品質担保する必要があります。 そのためのベストプラクティスを共有できるように、クエリレビュー観点をまとめます。

ソースをRawでコピー&ペーストすると、マークダウン対応のエディタ内で使いやすいです。

これらの観点に合わせることも重要ですが、プロジェクトの中で一貫性を保つことはもっと重要です。 特に、このレビュー観点を守るためにコードの後方互換性を壊すようなことは絶対にしないで下さい。 一貫性を崩すべき場合も有ります。疑問に思ったときは、あなたの判断を優先してください。そして、躊躇せずに質問して下さい。

クエリレビュー観点表

P0：プロダクトフェーズで必須

• 社内のコーディング規約を守っているか
• JOINはLEFT/INNER/FULLを使い分けているか
• ONやWHERE、ウィンドウフレーム（ROWS、RANGE）は境界値で正常動作するか
• ARRAY_AGGやSTRING_AGGは固定長か、可変長ならユースケースのオーダーで制限を超えないか
• 指数的爆発するJOINを行っていないか
• シャーディングは1,000テーブル未満か
• タイムゾーンは明らかか
• 再現性の確保が必要なのに、副作用を持つ関数を使っていないか
• EXTERNAL_QUERYやJavaScriptユーザー定義関数の型は適切か
• 0始まりの文字列がINT64で比較されていないか
• パーティション分割は上限（4,000）以内か
• シャーディングは1クエリの参照テーブル上限（1,000）以内か
• 有効精度に合わせてNUMERIC,FLOAT64を選んでいるか
• UNIONを使う場合SELECTの列順が同一である保証をしているか
• 暗黙的にユニーク性を期待していないか

P1：Data/Developer eXperienceを低下する

• 理解に十分なコメントがついているか
• データの値域は定義されているか
• エイリアスに意味のある名前がついているか
• 列挙型にBOOLを使わず無理にINT64でフラグ管理していないか
• DATEで十分なのにDATETIMEを使っていないか
• 定義から外れた値はERRORで拒否または無効化、許容できるか
• 標準関数で代替できる関数を自作していないか
• なんでもREGEXP_*にしてないか
• RANK/ROW_NUMBERは正しく使い分けているか

P2：パフォーマンス改善に有効

• クエリ パフォーマンスの最適化の概要に沿っているか
  • 最終結果の無駄なSELECTを避ける
  • パーティション分割を検討する
  • データを予めJOINして非正規化する
  • 外部データソースを避ける
  • 過剰なワイルドカード テーブル参照を避ける
  • JOINを使用する前にSELECTやWHERE、ON、GROUP BY、HAVINGでデータを削減する
  • WITH句やサブクエリの繰り返しを避ける
  • 日付別シャーディングよりパーティション分割を選ぶ
  • テーブルの過度なシャーディングを避ける
  • SQLクエリ経由のデータの繰り返し結合や変換を避けて実体化する
  • JavaScriptユーザー定義関数を避ける
  • 精度が許すなら近似集計関数を選ぶ
  • パフォーマンスが最大化するようにクエリ オペレーションを並べ替える
  • JOINは最大、最小、2番目に大きい、3番目に大きいテーブルの順に結合する
  • 大規模な結果セットの実体化を避ける
  • 大規模な並べ替えを避け、LIMITを使用する
  • 自己結合よりウィンドウ関数を選ぶ
  • パーティションの大きさに偏りがあるなら早めにフィルタする
  • CROSS JOINは事前に十分小さくするか、ウィンドウ関数を利用する
  • DMLによる更新、挿入は単一行より一括を選ぶ
• 最終結果、ウィンドウ関数以外でORDER BYが使われていないか
• SUM(IF(x))で計算できないか
• よく使うフィルタがあればクラスタ化する