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を指定子ましたが、他にも様々なサポートが用意されているので、機会があれば利用していきたいと思います。