データ民主化基盤のSQLコーディング規約
こんにちは。マネーフォワード分析推進室の吉住です。
分析推進室では、社内の多くの人が意思決定をする際に見たい情報にハードルなくアクセスできる状態を継続的に実現する「データの民主化」を目指しています。そのために、BigQueryでカジュアルに分析できる基盤の整備に取り組んでいます。(詳しくは、以下の記事を参照ください)
元々は、主に管理会計に分析基盤を利用するため、SSOT(Single Source of Truth: 信頼のおける唯一の情報源)というデータ品質の概念を重要視してきましたが、分析基盤の利用者が増加すると品質の担保が難しくなります。そこで、BigQueryのカジュアルな分析基盤におけるSQLコーディング規約を整理しました。
この記事で、その分析基盤におけるマネーフォワードのSQLコーディング規約をご紹介します。
参考資料
SQLコーディング規約は以下の資料を参考にしています。
SQLコーディング規約一覧
全文小文字を原則とする(既存のフォーマッターが大文字に変換する仕様のため、フォーマッター導入まで手動運用不要)
単一行(ブロック)コメントは「--」とする
複数行コメントは各行で「--」を書く
終了セミコロンは、最終行に単独で書く
1レベルインデントするごとに、スペースを2つ使う
関数・変数の名前は小文字のみにする。また、読みやすくするために、必要に応じて単語をアンダースコアで区切る
テーブル名や列名の略称をつける際は、asを使う
with句などそのコード内でしか使われないテーブルは「_tmp」で区別する
select文で「*」を使わない
from句のテーブル名はプロジェクト名・データセット名から省略せずに書く
andなどの論理演算子の位置は文頭にする
orを括弧で括る
joinのouterは省略する
inner joinはjoinだけでは使わない
right joinは使わず、left joinにする
using使わず、onを使う
group byは1,2,3等列番号は使わず、列名にする
6行以上のサブクエリは避け、使う場合もサブクエリの結果を自然言語でコメントする
create文でorder byは使わない
create文にテーブル自体のdescriptionを記載する
SQLコーディング規約の説明
大文字・小文字について
全文小文字を原則とする
参考資料[1]に従っています。
コメントアウトについて
単一行(ブロック)コメントは「--」とする
BigQueryのクエリエディタでは、「Ctrl + /」のキーボードショートカットで、コメントアウト「--」が入力できます。再度、「Ctrl + /」を押すとコメントアウトが解除されます。
複数行コメントは各行で「--」を書く
BigQueryのクエリエディタでは、複数行を選択し「Ctrl + /」のキーボードショートカットを押すことで、複数行まとめてコメントアウト「--」が入力できます。再度、「Ctrl + /」を押すとコメントアウトが解除されます。
「/* */」は機械的に解析しにくいため、非推奨としました。
終了セミコロンについて
終了セミコロンは、最終行に単独で書く
参考資料[1]に従っています。
インデントについて
1レベルインデントするごとに、スペースを2つ使う
参考資料[1]に従っています。
命名規則について
関数・変数の名前は小文字のみにする。また、読みやすくするために、必要に応じて単語をアンダースコアで区切る
参考資料[1]に従っています。
テーブル名やフィールド名の略称・エイリアス名をつける際は、asを使う
参考資料[2]に従っています。
with句などそのコード内でしか使われないテーブルは「_tmp」で区別する
with句のテーブルか、データセットに存在するテーブルか、区別がないと、可読性が低く、かつ機械的に解析しにくいため、サフィックス「_tmp」で区別しました。
select文の列名について
select文で＊を使わない
「＊」は、可読性が低く、かつ機械的に解析しにくいため、非推奨としました。
from句のテーブル名について
from句のテーブル名はプロジェクト名・データセット名から省略せずに書く
プロジェクト名・データセット名を省略すると、機械的に解析しにくく、移管性も低いため、非推奨としました。
論理演算子について
and等の論理演算子の位置は文頭にする
参考資料[1]の「andなどの論理演算子の位置は、文頭」に従っています。なお、[1]に記載のカンマについては、規約化していません。
(A or B)を括弧で括る
andとorの論理演算が複雑化しやすいため、可読性の観点で、 (A or B)は括弧で括ります。
joinについて
joinのouterは省略する
参考資料[2]に従っています。
inner joinはjoinだけでは使わない
joinだけだと、inner,left,right,full,crossが明示的でないため、可読性の観点で、非推奨です。
right joinは使わず、left joinにする
可読性の観点で、 right joinは非推奨です。
using使わず、onを使う
可読性と移植性の観点で、「on」を推奨します。
group byについて
group byは1,2,3等列番号は使わず、列名にする
group byにおける1,2,3等列番号は、可読性が低く、かつ機械的に解析しにくいため、非推奨としました。
サブクエリ・with句について
6行以上のサブクエリは避け、使う場合もサブクエリの結果を自然言語でコメントする
参考資料[1]に従っています。
create文について
create文でorder byは使わない
不要な集計コスト・時間を削減します(order byは基本的にアドホック分析のみで利用します)。
create文にテーブル自体のdescriptionを記載する
特に、テーブルの概要と主キー(欠損と重複)について記載することを推奨しています。
SQLコーディング規約の検討ポイント
SQLコーディング規約を作成するにあたっては、以下のような観点で検討してきました。
効率性・SPEED
結果の出力が早いコードが良い(時間効率性)
例えば、「create文でorder byは使わない」はこの観点です。
コストが安くなるコードが良い(資源効率性)
書く時間が短くなるコードが良い
例えば、「全文小文字を原則とする」のほうが多少書きやすいと思います。
人にとって読みやすいコード(理解する時間が短くなるコード)が良い(可読性に同じ)
可読性・理解しやすさ
人にとって読みやすいコードが良い
例えば、「テーブル名やフィールド名の略称・エイリアス名をつける際は、asを使う」や「group byは1,2,3等列番号は使わず、列名にする」はこの観点です。
機械にとっても読みやすい(構文解析などしやすい)コードが良い
例えば、「from句のテーブル名はプロジェクト名・データセット名から省略せずに書」いておくと、機械的にリネージを作りやすいと思います。
変更容易性・移植性
BigQuery以外の分析環境・データベースもあるため、他環境でも少ない変更で動くSQLが良い
例えば、コメントアウトや「using使わず、onを使う」などで考慮しています。
BigQuery以外の分析環境をメインで使うようになったとしても、移管しやすいほうが良い
信頼性・SSOT
統一されたコードが良く、SQLコーディング規約が分析基盤利用者に浸透できると良い
SSOTのために、このようなSQLコーディング規約を作成しました。規約が存在しているだけでなく、利用者に浸透させることが重要と考えています。
フォーマッター
データ民主化のためのBigQuery分析基盤では、フォーマッターとしてSQLFluffを導入し、一部のSQLコーディング規約は自動で修正するようにしています。フォーマッターについては今後の記事で発信するかも。
おわりに
マネーフォワード分析推進室は、データ活用やデータドリブンな文化醸成に関する様々な情報を「マネーフォワード・データ＆AI」マガジンで発信しています。よかったらnoteをご覧ください＆マガジンをフォローください！
この記事が気に入ったらサポートをしてみませんか？