BigQueryのSQLにSQLFluffを利用した話

はじめに

私が主担当しているプロダクトは、Cloud SQLをはじめアプリケーションのログはすべてBIgQueryへ集約しており、データサイエンティストチームでなくても、 簡単にデータアクセスが可能になっています(もちろん情報の取り扱いや危険性については周知しています)。

また一方で、プロダクト開発の一貫として、「自分が開発したものは自分で効果測定しましょう」というルールを設けています。 効果測定の際に利用するSQLは、その正誤確認やメンバーの新規参入のために、GitHubでバージョン管理されPullRequestでのコードレビューをするべきです。

予約語は大文字にしてください」「インデントがズレています」「テーブル名にバッククォートがあるない」、、 これらはレビュイーにとっても指摘してほしい箇所ではないことは自明です。 一方で、効果測定で分析のために発行するSQLは膨大で目grepは難しい。

私の大好きな名著にもありますが

コーディングが常に一定のルールに従って行われる体制作りも大切でしょう。ルールが守られているかどうかは、コーディング中にツールで自動チェックされるようにしておきます

ということで、早急にリンターを入れる必要がありました。

SQLに特化したリンター

ESLintやPrettierなど、プログラミング言語を対象としたコードフォーマッターは有名ですが、SQLに特化したリンターについてはあまり知られていません。 PrettierにもSQL対応されたPluginがあるので、試してみました。

$ yarn add -D prettier prettier-plugin-sql

実行前のSQL

with const As (
    select
        365 * 2 as interval_date,
)

seLect
    *,
fRom
    project-name.data-set-name.schema-name,
    const

prettierの実行

$ yarn prettier --write db.sql

実行結果

with
  const As (
    select
      365 * 2 as interval_date,
  )
seLect
  *,
fRom
  project - name.data -
set
  - name.schema - name,
  const

参照先のテーブル名及びプロジェクト、データセット名はクォートで囲まれていないと文字列扱いになり、改行されてしまいます。

当然、BigQueryのコンソール画面上でもエラーが出てしまいました。

また仮に、参照先をバッククォートで囲った場合、インデントの調整はされるが予約語の統一はデフォルトではされないようでした。 (設定ファイル書けばいいんでしょうけども正直面倒。。)

with
  const As (
    select
      365 * 2 as interval_date,
  )
seLect
  *,
fRom
  `project-name`.`data-set-name`.`schema-name`,
  const

SQLFluffの発見

代替となる良い方法はないものかと探していたところ、SQLFluffなるものを発見。さっそく使ってみます。 実行環境は以下です。

Python 3.9.7
pip 21.2.3

sqlfluffのインストール

$ pip install sqlfluff

$ sqlfluff --version
sqlfluff, version 0.13.1

設定ファイルを配置

[sqlfluff]
dialect = bigquery
$ sqlfluff lint db.sql
== [db.sql] FAIL
L:   1 | P:   6 | L045 | Query defines CTE "const" but does not use it.
L:   1 | P:  12 | L010 | Keywords must be consistently lower case.
L:   2 | P:   5 | L036 | Select targets should be on a new line unless there is
                       | only one select target.
L:   3 | P:  33 | L038 | Trailing comma in select statement forbidden
L:   6 | P:   1 | L010 | Keywords must be consistently lower case.
L:   6 | P:   1 | L044 | Query produces an unknown number of result columns.
L:   7 | P:   6 | L038 | Trailing comma in select statement forbidden
L:   8 | P:   1 | L010 | Keywords must be consistently lower case.
L:   9 | P:  22 |  PRS | Line 9, Position 22: Found unparsable section:
                       | '-set-name.schema-name,\n const'
All Finished 📜 🎉!

一部解釈できないとのことなので手動で修正して再実行

$ sqlfluff fix db.sql
==== finding fixable violations ====
== [db.sql] FAIL
L:   1 | P:  12 | L010 | Keywords must be consistently lower case.
L:   2 | P:   5 | L036 | Select targets should be on a new line unless there is
                       | only one select target.
L:   3 | P:  33 | L038 | Trailing comma in select statement forbidden
L:   6 | P:   1 | L010 | Keywords must be consistently lower case.
L:   7 | P:   6 | L038 | Trailing comma in select statement forbidden
L:   8 | P:   1 | L010 | Keywords must be consistently lower case.
                                ==== fixing violations ====
6 fixable linting violations found
Are you sure you wish to attempt to fix these? [Y/n] ...
Attempting fixes...
Persisting Changes...
== [db.sql] PASS
                                Done. Please check your files to confirm.
All Finished 📜 🎉!1,  1.94s/it]
  [3 unfixable linting violations found]

実行結果

with const as (
    select 365 * 2 as interval_date        
)

select
    *
from
    project-name.`data-set-name`.schema-name,
    const

設定をもっと細かく追加したい場合は、.sqlfluffにルールを追加します。 リファレンスも直感的でわかりやすいので、ありがたいですね。

[sqlfluff]
dialect = bigquery
exclude_rules = L034

[sqlfluff:rules:L010] # keyword
capitalisation_policy = upper

[sqlfluff:rules:L014] # column
extended_capitalisation_policy = lower

[sqlfluff:rules:L030] # function
extended_capitalisation_policy = upper

[sqlfluff:rules:L040] # boolean/null literal
capitalisation_policy = upper

再度write実行した結果

WITH const AS (
    SELECT 365 * 2 AS interval_date
)

SELECT
    *
FROM
    project-name.`data-set-name`.schema-name,
    const

こちらでやりたいことはできました。

利用してみての所感

分析用のSQLは抽出方法の思考に意識をかなり注力しているので、チェックツールとして機能してもらえるだけでもかなり楽になりました。

一方で、SQLSluffは解析にかなり時間がかかるツールなので、1,000行程度のSQLで10~20秒待たされることもあります。 なるべくプロダクト初期に導入しておいて、huskyなどで差分ファイルのみをチェックするようにしておきたいところです。

また今回は、dialectにBigQueryを指定子ましたが、他にも様々なサポートが用意されているので、機会があれば利用していきたいと思います。