[レポート] SQL用リンター「SQLFluff」 #dbtcoalesce

She fluffed out her hair.

モダンデータスタック(MDS)

#dbt

#データ分析

#レポート

#セッションレポート

#SQL

たまちゃん

2021.01.04

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

大阪オフィスの玉井です。

12月7日〜11日の間、Fishtown Analytics社がcoalesceというオンラインイベントを開催していました（SQLを触っている方はピンとくるイベント名ではないでしょうか）。

「Fishtown Analytics社って何やってる会社？」という感じですが、dbtというツールを開発しているベンダーです。dbtについては、下記をご覧ください。

今回は、その中からPresenting: SQLFluffセッションを受講したので、レポートを記します。

イベント概要

公式

Coalesce 2020 online - December 7-11, 2020
- Presenting: SQLFluff - Coalesce 2020 online - December 7-11, 2020

登壇者

Alan Cruickshank, Head of Data with tails.com
- tails.com | Tailored Dog Food Subscription | tails.com

概要

The dbt project at tails.com has over 600 models and 66k lines of code. With multiple contributors to a project and varying SQL backgrounds, it's really difficult to maintain consistent readability and comprehension across a codebase like that by hand.

Python has flake8, Javascript has JSLint, but SQL...?

Listen to this talk from Alan Cruickshank to find out whether SQLFluff might help your teams be more productive with SQL.

私なりの要約

Python等のプログラミング言語には、大体リントツール（フォーマッター）があり、複数人が開発に参加していても、ツール側でコーディングスタイルを揃えることができます。しかし、SQLに関しては、最近までそういうリントツールの類のものがありませんでした。

このセッションでは、SQLFluffというSQL用リントツールに関する紹介・説明がメインとなります。しかし、ツールだけでなく、データ分析プロジェクトにおいて、SQLのコーディングスタイルを一貫させることの意義についても、説明がありました。

セッションレポート

はじめに

チームが物事に正面から取り組むようにするにはどうしたらいいのか？
「その仕事は誰かの仕事ではなく皆の仕事である」と感じて、品質と創造性が高まるようにするにはどうしたらいいのか？

プログラミング言語の「読みやすさ」の重要性

プログラミング言語は「他の誰か」が読むもの

今回はSQLに焦点を当てる
- dbtの話はほとんど無い
私達はコードを「機械に話しかける方法」として扱う
- SQLだったら、データベースに指示する
言語（コード）は、単に書くだけでなく、誰かに読んでもらうことを期待する
- それは、機械ではなく、別の人間であることも
- （言語を読むのは）あなたのチームの同僚かもしれない
- （言語を読むのは）未来のあなたかもしれない
- 誰もが、昔書かれたSQLを読む場面に遭遇した経験があると思う
- 誰もが、過去の自分の（過去の書いたコードの）せいで、今苦しんだことがあるはず
- 自分が何をしたかったか理解するのも難しい時がある
言語は、コミュニケーションの手段としてだけでなく、知識の蓄えとして形成される
多くの人が「dbtリポジトリがSQLの単なる保管場所ではなくなった」と言う
- すべての作業の根底にある定義や事柄に関する知識の貯蔵庫となってきている
- これを読みやすく、簡単に理解できるようにすることは、かなり重要なことだと感じている

Pythonの例

機能としては全く同じPythonコードが2つある
- どちらも問題なく実行できる
左側のコードはめちゃくちゃ読みづらい
- コンピュータにとって、読みやすさは気にならない
- しかし、人間にとっては違う
読みやすさは非常に大事
- dbtプロジェクトを複数人で開発しているとする
- 自分の記憶は完璧ではないので、他人のコードを読んでビルドする
- その時に、何が起こっているのかを理解する必要がある

Pythonは「読みやすさ」の問題を解決するのが、かなり簡単になってきている
Pythonは80年代後半に開発されていたはず
- リリースは91年
flake8 · PyPI
- Pythonのコードチェックツール
- 色々なツールの組み合わせで出来ている
- 「スペースが多すぎる」「関数が正しい方法で書かれていない」などを指摘してくれる
GitHub - psf/black: The uncompromising Python code formatter
- ツール名の由来→ヘンリー・フォードの名言「T型フォードを買う人はどの色でも好きに選べるーーそれが黒色である限りは」より
- 「開発者の（コードの書き方に関する）信念は、全て機械に委ねるべき」という方針のツール
こういったツールを使うことで、他人のコーディングスタイルについて考える時間が無くなる
- もっと大事なことに時間を使えるようになる

SQLの話

SQLはPythonより古い
- SQLの元となった仕様は74年リリース
SQLは一つの言語である、というのはかなり不正確になってきている
- オリジナルのSQL
- 異なるバージョンのSQL
- RedshiftからSnowflakeやBigQueryに移行したことがある人は、気持ちがわかるだろう
SQL同士の違いは、完全に違うわけではないし、見た目も一緒に見えるが、実はかなり異なるもの…というのが難しい
- MySQLとBigQueryでは引用符の違いがある
dbt、Airflowなどのツールを使う場合、チームが共同作業で使うのは純粋なSQLではない
- しかし、最終的にできるもの（コンパイルされるもの）はSQLである

コーディングスタイルを指摘するのは気まずい問題

コードレビューの例
スライドは、Fishtown Analytics社のクレア氏の最初のコミット時のスクリーンショット
- クレア氏はFishtown Analytics社の新入社員として、CEOのトリスタン氏にレビューを依頼している
CEOは、内容（ロジック）は問題ないが、インデントを修正するよう回答した
- 彼は一貫したコードスタイルの重要性を知っている
- 彼は規範を持つことの重要性を知っている
- そして、誰かがそういうことをフィードバックしないといけないのも知っている
- しかし、こういったフィードバックを行うことに気まずさを感じている
- 「相手がこういうフィードバックを受け取ったら嫌だろうな」みたいなことも思ってしまう

レビューの方法いろいろ

その1：一切レビューをしない
- 作業効率が著しく損なわれる
- 最終的に全員が悲しみに暮れることになる

その2：人間がレビューをする
- 普通の方法
- ロジックとコードスタイルの両方を人間が見直す
- コードスタイルへの指摘は、不愉快さや気まずさを感じる
- そのうち、レビューをしなくなる→その1と同じ結末へ

その3:：機械がレビューをする
- これが本当に必要な方法
- チーム全員が自分自身のコーディングスタイルに責任を持つことを支援する
- 人間のレビュアーはスタイルではなくロジックに注目するようになる
- ロジックのレビューは本質的なものなので、生産的なことをしているように感じるため、人間も嫌な思いはしない
Pythonはこれができるが、SQLは今までこれをやる方法が無かった

SQLFluffについて

SQLFluffとは

自社のHack Dayの一環として、前述した問題を解決できるようなツールを作った
GitHub - sqlfluff/sqlfluff: A SQL linter and auto-formatter for Humans
- SQLの自動リントツール
- 今では40人以上のコントリビューターがいる
- 1日の1000以上ダウンロードされている

SQLFluffはコマンドラインツール
sqlfluff lint test.sql
- 特定のSQLをリントする
スライドの上部の例では3つの指摘がされている
- 1行目のインデントがおかしい
- いくつかの演算子の間にスペースがない
- 最後に無駄なスペースがぶら下がっている
sqlfluff lint test_folder
- フォルダ下のすべてのsqlファイルをリントする

CIパイプラインでも機能する
- チェックに引っかかった場合の終了コードは0以外の値になる
- これをCIチェックの中に入れることが出来る
スライドのスクリーンショットは、自社のdbtプロジェクト
- 自分が書いたSQLについて、非常に残酷で明確なフィードバックを受けている
- しかし、私はこれについて、誰に対しても悪い感情は抱かない
- フィードバックをしているのは機械だから
- 私はチームの誰とも不愉快なレビューをしていない
当初、このプロジェクトは、600のモデルにまたがる66000行のSQLがあった
- すべてが不整合にフォーマットされていた
- Pythonのプロジェクトでは、flake8をリンターとして使用していたことを思い出した
- このプロジェクトでもSQLFluffをリンターツールとして導入した

SQLFluffの実際の例

スライド下部に2つのクエリがある
- 左のクエリを、右のようにフォーマットできる
- SQLFluffのコマンドを実行すると「それらを修正しますか？」と聞かれるので、YESを選ぶと、クエリがフォーマットされる
フォルダ（下のファイルすべて）にも機能する
- 巨大なdbtプロジェクトなど

チームとして、既にSQLの書き方についてのルールが存在する場合がある
- SQLFluffは特定のルールのON/OFFができる
スライド左上はCustom Config
- 適用するフォーマットの設定ができる
- SQLの種類（方言）としてSnowflakeを指定
- 適用ルールは、項番3と6を指定
スライド右下のdbtのクエリをリントする
- SQLFluff実行時のスクショが右上
- ルールの3と6に引っかかっていることがわかる
- 結果は右下のクエリ
- RANK関数まわりのインデントが修正されている

SQLFluffには、複数の方言に共通の構造を適用する共通のSQLパーサーがある
- RedshiftでもSnowflakeでもBigQueryでも、どのクエリでも、共通の要素というものがある
まもなくリリースされる新機能
- SQLを照会して、どのテーブルやカラムを参照しているか理解する
- プロジェクトの異なる部分間の依存関係を処理することについては、色々なユースケースがある
- ステージング環境で、プロジェクト内のいくつかのスキーマのみがステージングエリアに依存させたい時など
- 機能としては非常に初期段階なので、完成はまだまだ

まとめ

全てのSQL文を修正するようなツールにしようとは思っていない
- 機械側が実施する修正は、あくまで「推測」
- 最悪、人間にとって、その修正は単なる間違いである可能性もある
是非、継続的インテグレーションに組み込んでほしい
dbtパッケージを沢山使用しているのであれば、プレリリース版を推奨
リントのルールはカスタマイズ可能（コンマの先頭か末尾か問題など）

SQLの自動フォーマットをやる意味

流れを止めて、より良いコードを書くことが出来る
- 私たちは皆、いつもたくさんのSQLを使って仕事をしている
- SQLFluffを入れて、不整合なクエリが書かれる流れを止める
- 後は、時間の経過とともによくなってくる
会話に集中できるようになる
- 大文字小文字問題やインデントなどの議論は、一方が勝ち、一方が負けたような感覚になる
- SQLFluffは、SQLの書き方に関するルールは、コンフィグファイルに落とし込める
- バージョン管理もできるので（=変更の場合はプルリクエスト）、より生産的なSQLの書き方に関する会話ができる
新しいメンバーにとっても良い
- 新メンバーは標準化されたSQLを当たり前だと思って見る
- （コンマに対する意見ではなく）より生産的なものに焦点を当てて活動できる
コードレビューがもっと楽しくなる
業界についても考えてほしい
- データ分析のSQLは多くが非公開であり、それについてコラボレーションすることはできない
オープンソースはここ数年ですごく成長していると思う
- SQLFluffもdbtもオープンソース
- これらは、データ分析を共有するコミュティを奨励し、私達が行った共通の事を共有することである
dbtパッケージを自分の組織に導入するところを想像してほしい
- パッケージ内のSQLが何をやっているのか、理解に時間がかかるだろう
- なぜなら、自分たちのSQLのスタイルと異なるから
もし、コミュティ全体でSQLの書き方が一貫していたとしたら？
- 仕事の仕方だけでなく、雇用やトレーニングの仕方まで変わってくるだろう
- 別の会社からジョインしてきたメンバーは、既にSQLのスタイルを理解していることになる
- すぐに共同作業が始められる

SQLFluffプロジェクトに参加してくれた多くの人に感謝する
- dbtコミュニテイからの参加者が多かった
- dbtコミュニテイが無ければSQLFluffは存在していなかった
現在、2つのリリースバージョンがある
- dbtパッケージを広範囲で使っていたり、複雑な巨大SQLがある場合は、プレリリース版をオススメ
- dbtマクロでチェックに引っかかった場合、参照先がソースファイルにマッピングされるようになった
このツールはみんなが使うべきだと思っている
- ぜひコミュニティに参加してほしい

おわりに

確かにSQLは、他のコードよりも「こういう風に書くべき」という議論が少ない印象がありました（セッション中でも触れられた「前コンマ」「後コンマ」など）。特に、データ分析用途のSQLクエリを他人がレビューするというのは、国内のデータ分析の現場においては、少ないような気がします。

しかし、ITエンジニア以外（例えば営業やマーケター）の人たちが、自分たちでSQLを書いて、どんどんデータを分析していこうとする流れは、国内においても、着々と増えてきている印象が（個人的な肌感覚ですが）あります。こうやって、SQLでデータ分析を推進する人たちが増えていけばいくほど、生成されるSQLクエリも、千差万別な書き方になると思われるので、こういうリンターを使って、半強制的にクエリの書き方を統一するのは、良い試みかと思いました。