dbt 開発で使える SQL スタイルガイドを導入した話

13 minute read

兼業データアナリストの星野（@mochigenmai）です。この記事では dbt 開発で使える SQL スタイルガイドを導入した話について紹介します。

SQL スタイルガイドを導入した背景

現在 dbt を利用したデータパイプライン開発が活発になってきています。データパイプラインは「信頼性の高い分析」を効率的かつ迅速に実現するために構築します。そのため、データの信頼性を担保する仕組みは積極的に導入したほうが良いと考えられます。

今回は以下のような点でデータの信頼性を担保できると考え、 dbt 開発環境に SQLFluff (Linter) を導入しました。

バグの原因になるような曖昧な記述を削除する。Wikipedia の Lint に関する記事では「コンパイラではチェックされないが、バグの原因になるような曖昧な記述についても警告される」と述べられている。dbt と BigQuery であれば、BigQuery コンソールでクエリを試した後、dbt への転記ミスによるエラーを防げる可能性がある。
複数人での開発時に構文を統一化し、コードレビューの労力を削減する。事前にルールが決まっていれば、構文についての議論を避けることができる。

スタイルガイドの方針決め

SQLFluff を動かすためには規約（以降 SQL スタイルガイドと呼ぶ）を作成する必要があるため、作成にあたって以下を実施しました。

既存の SQL スタイルガイドを調査

dbt Labs の dbt Style Guide
GitLab の SQL Style Guide

上記の調査結果を反映した SQL スタイルガイドを SQLFluff に導入

1. dbt Style Guide と GitLab SQL Style Guide の比較

共通点

以下のように明示的な記述にする
- エイリアスをつける時は AS や as を記述する
- JOIN 時の SELECT 内で . と記述する
コンマは行末
カラム名は小文字
JOIN 時の ON はインデントを下げる
GROUP BY や ORDER BY ではカラム名ではなく数字を記述する

相違点

ルール名	dbt Style Guide	GitLab SQL Style Guide
インデントのスペース数	4	2
1 行の最大文字数	80	80 - 100
予約語の書き方	小文字	大文字
NULL や BOOL 値の書き方	小文字	大文字

比較をして分かったこと

dbt Style Guide と GitLab SQL Style Guide 両方とも理解しやすいように記述することを第一として定義していることが分かりました。また、予約語や NULL, BOOL 値の書き方（大文字か小文字）くらいの違いしかないことも分かりました。

余談ですが、GitLab SQL Style Guide では 1 行の最大文字数が 80 文字から 100 文字となっているものの、実際に SQL スタイルガイド設定する値は固定値です。そこで GitLab SQL Style Guide で公開している .sqlfluff ファイルを確認しました。ここでは max_line_length の指定がないため、 SQLFluff のデフォルトである 80 文字（dbt Style Guide とも同じ）になっていることが分かりました。

2. sqlfluff-dbt-starterkit での規約の採用基準

dbt Style Guide と GitLab SQL Style Guide の相違点についての方針を決め、最終的に採用した内容はこちらです。初めはなるべく公式に準拠しようと dbt Style Guide をベースに考えていましたが、最終的なものは GitLab SQL Style Guide ベースとなりました。

ルール名	採用した内容	採用理由
インデントのスペース数	4	dbt Style Guide をベースに考えていたためその名残りです（他のルールが GitLab SQL Style Guide のものなのでこのルールも合わせて 2 にすることを検討中です）
1 行の最大文字数	100	80 文字でテスト運用したところ `.` の記述の箇所で何度か引っかかってしまったため、 GitLab SQL Style Guide の上限である 100 に設定
予約語の書き方	大文字	IDEなどのツールでハイライトされるので小文字でもいいが、社内では複数の DWH を利用していて `.sql` ファイルのデフォルトでハイライトの付かない関数も利用するため、ハイライトが付かなくても区別しやすい大文字にする
NULL や BOOL 値の書き方	大文字	予約語の書き方と統一させるため

テスト運用を通して、詳細に記載したコメントが文字数の制約に引っかかってしまったので、上記のルールとは別でコメントを Lint の対象から除く設定を追加しました。この修正を加えた結果、不要な箇所でのエラーが出にくくなったので実際に運用することにしました。

スタイルガイドの使い方

全てのルールを覚えるのは大変なので、適宜 Linter の指摘に対応することをオススメしています。

ガイドラインを読まずに SQL を書く
.sql ファイルの変更がある Pull Request を作成する
Linter から指摘を受けたらこのガイドラインを確認する
個別ルールにあるベストプラクティスと同じように修正する
徐々にガイドラインの書き方に慣れていく

という流れを案内しています。

スタイルガイドの内容

最終確定したルールの説明は sqlfluff-dbt-starterkit の guideline.md に記載しています。 SQLFluff の Rules Reference を元に作成しました。

記事執筆時点での内容は以下となります。

Rule_LT01 : 末尾の空白は不要（ほか）

末尾の空白は不要
カンマの直前に space を入れない
演算子は space で囲む
コンマの後には space を入れる（コメントが続く場合は除く）
WITH 句の AS の後に space を入れる
JOIN 句では USING の後に space を入れる
不要な space は使用しない
引用符「'」は 1 つずつの space で囲む

アンチパターン

SELECT
    a
FROM foo••

ベストプラクティス

SELECT
    a
FROM foo

Rule_LT02 : 空白に tab と space を混在させない（ほか）

空白に tab と space を混在させない
インデントの space は 4 の倍数で統一させる
ON や USING の行頭もインデントをつける

アンチパターン

SELECT
••→a
FROM foo

ベストプラクティス

SELECT
••••a
FROM foo

Rule_LT03 : 演算子の前後で改行を行う場合は改行の後に演算子を使用する

アンチパターン

SELECT
    a +
    b
FROM foo

ベストプラクティス

SELECT
    a
    + b
FROM foo

Rule_LT04 : コンマは行末に記載する

アンチパターン

SELECT
    a
    , b
    , c
FROM foo
– 行頭と行末が混在している
SELECT
    a
    , b,
    c
FROM foo

ベストプラクティス

SELECT
    a,
    b,
    c
FROM foo

Rule_LT05 : コメント行以外は 100 文字以内にする

（サンプル割愛）

Rule_LT06 : 関数名の直後に括弧を記載する

アンチパターン

SELECT
    sum•(a)
FROM foo

ベストプラクティス

SELECT
    sum(a)
FROM foo

Rule_LT07 : WITH 句の閉じ括弧は、WITH とインデントを揃える

アンチパターン

WITH zoo AS (
    SELECT a FROM foo
••••)
SELECT * FROM zoo

ベストプラクティス

WITH zoo AS (
    SELECT a FROM foo
)
SELECT * FROM zoo

Rule_LT08 : CTE の閉じ括弧の後は改行する

続けて記載する場合は閉じ括弧の後にカンマをつける

アンチパターン

WITH plop AS (
    SELECT * FROM foo
)
SELECT a FROM plop

ベストプラクティス

WITH plop AS (
    SELECT * FROM foo
)
SELECT a FROM plop

Rule_LT09 : SELECT 句で複数カラムを指定する場合は改行する

アンチパターン

SELECTt a, b
FOM foo
-- カラムが 1 つの場合で改行するとエラーになる
SELECT
    a
FROM foo

ベストプラクティス

SELECT
    a,
    b
FROM foo
-- カラムが 1 つの場合は改行しない
SELECT a
FROM foo

Rule_LT10 : SELECT 修飾子（DISTINCTなど）は SELECT と同一行に記載する

アンチパターン

SELECT
    DISTINCT a,
    b
FROM x

ベストプラクティス

SELECT DISTINCT
    a,
    b
FROM x

Rule_LT11 : UNION は改行で囲む

アンチパターン

SELECT 'a' AS col UNION ALL
SELECT 'b' AS col

ベストプラクティス

SELECT 'a' AS col
UNION ALL
SELECT 'b' AS col

Rule_LT12 : ファイルの末尾には改行のみの行を入れる

アンチパターン

SELECT
    a
FROM foo$
-- インデントのみの行で終わっている
SELECT
••••a
FROM
••••foo
••••$
-- 最終行がセミコロンで終わっているのに、改行がない
SELECT
    a
FROM foo
;$
-- 複数の改行で終わっている
SELECT
    a
FROM foo
$

ベストプラクティス

SELECT
    a
FROM foo
$
-- インデントで終わっている場合は、インデントをなくす
SELECT
••••a
FROM
••••foo
$
-- セミコロンの後に改行する
SELECT
    a
FROM foo
;
$

Rule_LT13 : ファイルは改行や space で始めてはいけない

アンチパターン

^
SELECT
    a
FROM foo
-- インデントされた行で始めてもエラーになる
••••SELECT
••••a
FROM
••••foo

ベストプラクティス

^SELECT
    a
FROM foo
 -- コメントアウトで始まるのも問題なし
 ^/*
 This is a description of my SQL code.
 */
SELECT
    a
FROM
    foo

Rule_RF01 : FROM 句に存在しないオブジェクトは記載しない

アンチパターン

SELECT
    vee.a
FROM foo

ベストプラクティス

SELECT
    a
FROM foo

Rule_RF02 : 複数のテーブルを参照するときは、修飾子を使用する

アンチパターン

SELECT a, b
FROM foo
LEFT JOIN vee ON vee.a = foo.a

ベストプラクティス

SELECT foo.a, vee.b
FROM foo
LEFT JOIN vee ON vee.a = foo.a

Rule_RF03 : 修飾子の使用有無は統一する

アンチパターン

SELECT
    a,
    foo.b
FROM foo

ベストプラクティス

SELECT
    a,
    b
FROM foo
-- よりいい書き方
SELECT
    foo.a,
    foo.b
FROM foo

Rule_RF04 : 予約語や関数名を修飾子に使用しない

アンチパターン

SELECT
    sum.a
FROM foo AS sum

ベストプラクティス

SELECT
    vee.a
FROM foo AS vee

Rule_RF05 : 識別子に特殊文字（「.」「,」「(」「)」「-」以外のもの）を使用しない

アンチパターン

CREATE TABLE DBO.ColumnNames
(
    [Internal Space] INT,
    [Greater>Than] INT,
    [Less<Than] INT,
    Number# INT
)

ベストプラクティス

CREATE TABLE DBO.ColumnNames
(
    [Internal_Space] INT,
    [GreaterThan] INT,
    [LessThan] INT,
    NumberVal INT
)

Rule_RF06 : カラム名を不必要に「"」で囲わない

アンチパターン

SELECT 123 AS "foo"

ベストプラクティス

SELECT 123 AS foo

Rule_ST01 : CASE 文では、ELSE NULL は冗長なので不要

アンチパターン

SELECT
    CASE
        WHEN name LIKE '%cat%' THEN 'meow'
        WHEN name LIKE '%dog%' THEN 'woof'
        ELSE null
    END
FOM x

ベストプラクティス

SELECT
    CASE
        WHEN name LIKE '%cat%' THEN 'meow'
        WHEN name LIKE '%dog%' THEN 'woof'l
    END
FOM x

Rule_ST02 : CASE 文を使用しなくていい場合は使用しない

アンチパターン

SELECT
    CASE
        WHEN fab > 0 THEN TRUE
        ELSE FALSE
    END AS is_fab
FROM fancy_table
-- CASE 文で NULL を埋める場合もエラーになる
SELECT
    CASE
        WHEN fab is null THEN 0
        ELSE fab
    END AS fab_clean
FROM fancy_table

ベストプラクティス

SELECT
    COALESCE(fab > 0, FALSE) AS is_fab
FROM fancy_table
-- NULL を埋める場合は COALESCE を使用する
SELECT
    COALESCE(fab, 0) AS fab_clean
FROM fancy_table

Rule_ST03 : 使わない CTE は定義しない

アンチパターン

WITH cte1 AS (
  SELECT a
  FROM t
),
cte2 AS (
  SELECT b
  FROM u
)
SELECT *
FROM cte1

ベストプラクティス

WITH cte1 AS (
  SELECT a
  FROM t
)
SELECT *
FROM cte1

Rule_ST04 : CASE 文のネストはばらす

アンチパターン

SELECT
  CASE
    WHEN species = 'Cat' THEN 'Meow'
    ELSE
    CASE
       WHEN species = 'Dog' THEN 'Woof'
    END
  END as sound
FROM mytable

ベストプラクティス

SELECT
  CASE
    WHEN species = 'Cat' THEN 'Meow'
    WHEN species = 'Dog' THEN 'Woof'
  END AS sound
FROM mytable

Rule_ST05 : JOIN 句にサブクエリは使用しない、やるなら CTE を使用する

アンチパターン

SELECT
    a.x, a.y, b.z
FROM a
JOIN (
    SELECT x, z FROM b
) USING(x)

ベストプラクティス

WITH c AS (
    SELECT x, z FROM b
)
SELECT
    a.x, a.y, c.z
FROM a
JOIN c USING(x)

Rule_ST06 : * を使用する場合は一番最初に記載し単純なものから順に記載する

アンチパターン

SELECT
    a,
    *,
    ROW_NUMBER() OVER(PARTITION BY id ORDER BY date) AS y,
    b
FROM x

ベストプラクティス

SELECT
    *,
    a,
    b,
    ROW_NUMBER() OVER(PARTITION BY id ORDER BY date) AS y
FROM x

Rule_ST07 : USING を使用するのではなく、ON を使用する

アンチパターン

SELECT
    table_a.field_1,
    table_b.field_2
FROM
    table_a
INNER JOIN table_b USING (id)

ベストプラクティス

SELECT
    table_a.field_1,
    table_b.field_2
FROM
    table_a
INNER JOIN table_b
    ON table_a.id = table_b.id

Rule_ST08 : DISTINCT使用時は括弧で囲まない

アンチパターン

SELECT DISTINCT(a), b FROM foo

ベストプラクティス

SELECT DISTINCT a, b FROM foo

Rule_TQ01 : SP_プレフィックスは使用しない（T-SQLのストアドプロシージャには）

アンチパターン

CREATE PROCEDURE dbo.sp_pull_data
AS
SELECT
    ID,
    DataDate,
    CaseOutput
FROM table1

ベストプラクティス

CREATE PROCEDURE dbo.pull_data
AS
SELECT
    ID,
    DataDate,
    CaseOutput
FROM table1
-- または USP_プレフィックスを使用する
CREATE PROCEDURE dbo.usp_pull_data
AS
SELECT
    ID,
    DataDate,
    CaseOutput
FROM table1

まとめ

dbt 開発に際して SQL を静的解析する SQLFluff (Linter) を導入し、併せて Linter の動作規約となる SQL スタイルガイドも導入しました。

最終的な SQL スタイルガイドは GitLab SQL Style Guide ベースとなり、コメントを Lint の対象から除くことにしました。

dbt 開発環境に SQLFluff (Linter) をサクッと導入したい人は kazaneya/sqlfluff-dbt-starterkit を参考にしてみてください。

Twitter Facebook LinkedIn

SQL スタイルガイドを導入した背景

スタイルガイドの方針決め

1. dbt Style Guide と GitLab SQL Style Guide の比較

共通点

相違点

比較をして分かったこと

2. sqlfluff-dbt-starterkit での規約の採用基準

スタイルガイドの使い方

スタイルガイドの内容

Rule_LT01 : 末尾の空白は不要（ほか）

Rule_LT02 : 空白に tab と space を混在させない（ほか）

Rule_LT03 : 演算子の前後で改行を行う場合は改行の後に演算子を使用する

Rule_LT04 : コンマは行末に記載する

Rule_LT05 : コメント行以外は 100 文字以内にする

Rule_LT06 : 関数名の直後に括弧を記載する

Rule_LT07 : WITH 句の閉じ括弧は、WITH とインデントを揃える

Rule_LT08 : CTE の閉じ括弧の後は改行する

Rule_LT09 : SELECT 句で複数カラムを指定する場合は改行する

Rule_LT10 : SELECT 修飾子（DISTINCTなど）は SELECT と同一行に記載する

Rule_LT11 : UNION は改行で囲む

Rule_LT12 : ファイルの末尾には改行のみの行を入れる

Rule_LT13 : ファイルは改行や space で始めてはいけない

Rule_RF01 : FROM 句に存在しないオブジェクトは記載しない

Rule_RF02 : 複数のテーブルを参照するときは、修飾子を使用する

Rule_RF03 : 修飾子の使用有無は統一する

Rule_RF04 : 予約語や関数名を修飾子に使用しない

Rule_RF05 : 識別子に特殊文字（「.」「,」「(」「)」「-」以外のもの）を使用しない

Rule_RF06 : カラム名を不必要に「"」で囲わない

Rule_ST01 : CASE 文では、ELSE NULL は冗長なので不要

Rule_ST02 : CASE 文を使用しなくていい場合は使用しない

Rule_ST03 : 使わない CTE は定義しない

Rule_ST04 : CASE 文のネストはばらす

Rule_ST05 : JOIN 句にサブクエリは使用しない、やるなら CTE を使用する

Rule_ST06 : * を使用する場合は一番最初に記載し単純なものから順に記載する

Rule_ST07 : USING を使用するのではなく、ON を使用する

Rule_ST08 : DISTINCT使用時は括弧で囲まない

Rule_TQ01 : SP_プレフィックスは使用しない（T-SQLのストアドプロシージャには）

まとめ

You May Also Enjoy

新しいツールを調査するときの心得

Tableauの拡張機能「Write-Back」でExcel対応をなくせるか

INFORMATION_SCHEMAを使用してBigQueryのViewの定義をDataformに移管する

大学のデータ分析の授業で Google Cloud を活用した話（登壇文字起こし）