📝

BigQueryのStandardSQLのフォーマッターでsqlfluffを使ってみた

2022/08/30に公開

SQL

背景

チームでSQLのコーディング規約を定めたが、コードレビューが大変なので、コーディング規約に沿ってBigQueryのStandardSQLを自動でフォーマットしてほしいなと思った。

比較検討

SQLのフォーマッターで検索して、ヒットしたいくつかのツールを比較した。

ツール名	特徴	URL	BigQuery準拠	開発主体	自動フォーマット機能	カスタマイズ性
sqlfluff	linter兼formatter	https://github.com/sqlfluff/sqlfluff	○	会社	○	○
zetasql-formatter	BigQuery内のSQLフォーマット(zetasql)に準拠している	https://github.com/Matts966/zetasql-formatter	○	個人	○	×
prettier-plugin-bq	Prettierというformatterの拡張機能の一部。Prettierが対応していればどのエディタからでも利用可能	https://github.com/dr666m1/prettier-plugin-bq	○	個人	○	△
sql-lint	※sql-lintはmysqlとpostgreしか対応していなさそうだったので、調査対象から除外した。	https://github.com/joereynolds/sql-lint	×
BigQueryUIのクエリを書式設定	BigQueryGUI上で提供されているクエリ整形機能		○	会社	○	×

どれも導入コストは低そうだが、

BigQueryの自動フォーマットはそもそも微妙なので避けたい
会社でサポートされているものの方が機能拡張が早そう
という理由から、sqlfluffを使うことにした。

sqlfluff

使い方

sqlfluffの導入についての記事は他に記事がたくさんあるので割愛。

CLIの使い方は以下のページを参照。

sqlfluff fix [PATH]

でPATHで渡したディレクトリ配下のSQLファイルをフォーマットする。

設定

sqlfluffはデフォルトで設定されているルールと、.sqlfluffに自分で設定するルールの二つがあり、後者が前者をoverrideする形となる。
デフォルトルールは以下。一部読み違えているところもあるかも。

分類	ルール	設定	規約
sqlfluff	verbose	0	出力されるログレベルの数字
	nocolor	False	出力のカラー
	dialect	None	sqlfluffで使用する言語
	templater	jinja	raw
	rules	all	チェックするルール
	exclude_rules	None	チェックしないルール
	recurse	0	パースする深さ？　0は無制限
	output_line_length	80	出力行の最大文字数
	runaway_limit	10
	ignore	None
	ignore_templated_areas	True
	encoding	autodetect
	disable_noqa	False
	sql_file_exts	.sql,.sql.j2,.dml,.ddl	フォーマッティングする拡張子
	fix_even_unparsable	False	エラーでもフォーマットするか
sqlfluff:indentation	indented_joins	False	joinをindentするか
	indented_ctes	Fakse	cteでindentするか
	indented_using_on	True	usingやonでindentするか
	template_blocks_indent	True
sqlfluff:templater	unwrap_wrapped_queries	True
sqlfluff:templater:jinja	apply_dbt_builtins	True
sqlfluff:rules	tab_space_size	4	タブ or スペースのサイズ
	max_line_length	80	行の最大文字数
	indent_unit	space	インデントはタブかスペースか
	comma_style	trailing	コンマは先頭か末尾か
	allow_scalar	True	select句の1つの要素にエイリアスを付けないようにするかどうか
	single_table_references	consistent	単一テーブルの場合、参照方式を一貫させるか
	unquoted_identifiers_policy	all	違反の場合のフラグの立て方
sqlfluff:rules:L003	hanging_indents	True	インデントが前の行と一致していません
sqlfluff:rules:L007	operator_new_lines	after	オペレーターは、改行の前後にあるための標準に従う必要があります。演算子は改行の後に配置する必要があります
sqlfluff:rules:L010	capitalisation_policy	consistent	キーワードの大文字と小文字が一貫していません。大文字か小文字かどちらでもいいので一貫させる
	ignore_words	None	ルールから無視するワード
	ignore_words_regex	None	ルールから無視するワード（正規表現）
sqlfluff:rules:L011	aliasing	explicit	テーブルの暗黙的なエイリアシング
sqlfluff:rules:L012	aliasing	explicit	列の暗黙的なエイリアシング
sqlfluff:rules:L014	extended_capitalisation_policy	consistent	引用符で囲まれていない識別子のキャピタライゼーションが一貫している
	ignore_words	None	ルールから無視するワード
	ignore_words_regex	None	ルールから無視するワード（正規表現）
sqlfluff:rules:L016	ignore_comment_lines	False	行の長さに関してコメント行は無視する
	ignore_comment_clauses	False	行の長さに関してコメント句は無視する
sqlfluff:rules:L026	force_enable	False	FROM参照句に存在しないオブジェクトを参照できません。このルールがデフォルトで無効になっている言語は無効のまま
sqlfluff:rules:L028	force_enable	False	参照は、単一のテーブルを持つステートメントで一貫している必要があります。このルールがデフォルトで無効になっている言語は無効のまま
sqlfluff:rules:L029	unquoted_identifiers_policy	aliases	予約語を識別子として使用しないでください。エイリアスは検知しない
	quoted_identifiers_policy	None	すべて検知しない
	ignore_words	None	ルールから無視するワード
	ignore_words_regex	None	ルールから無視するワード（正規表現）
sqlfluff:rules:L030	extended_capitalisation_policy	consistent	関数名の大文字と小文字が一貫していません。大文字でも小文字でも一貫させる
	ignore_words	None	ルールから無視するワード
	ignore_words_regex	None	ルールから無視するワード（正規表現）
sqlfluff:rules:L031	force_enable	False	from句と結合条件でのテーブルエイリアスは避けてください。このルールがデフォルトで無効になっている言語は無効のまま
sqlfluff:rules:L038	select_clause_trailing_comma	forbid	select句内の末尾のコンマは禁止
sqlfluff:rules:L040	capitalisation_policy	consistent	ブール/nullリテラルの大文字化が一貫している
	ignore_words	None	ルールから無視するワード
	ignore_words_regex	None	ルールから無視するワード（正規表現）
sqlfluff:rules:L042	forbid_subquery_in	join	Join句にサブクエリを含めることはできません
sqlfluff:rules:L047	prefer_count_1	False	count（1）といった記法を推奨しない
	prefer_count_0	False	count（0）といった記法を推奨しない
sqlfluff:rules:L051	fully_qualify_join_types	inner	inner joinのinnerは記述する必要がある
sqlfluff:rules:L052	multiline_newline	False	複数行のステートメントの後にセミコロンを新しい行に配置する必要はない
	require_final_semicolon	False	最後のセミコロンが必要でない
sqlfluff:rules:L054	group_by_and_order_by_style	consistent	列参照方法が一貫している
sqlfluff:rules:L057	unquoted_identifiers_policy	all	識別子に特殊文字を使用しないでください。
	quoted_identifiers_policy	all
	allow_space_in_identifier	False
	additional_allowed_characters	"”
	ignore_words	None
	ignore_words_regex	None
sqlfluff:rules:L059	prefer_quoted_identifiers	False	すべての識別子を引用符で囲む必要がない
	ignore_words	None	ルールから無視するワード
	ignore_words_regex	None	ルールから無視するワード（正規表現）
	force_enable	False	このルールがデフォルトで無効になっている言語は無効のまま
sqlfluff:rules:L062	blocked_words	None	禁止ワードなし
sqlfluff:rules:L063	extended_capitalisation_policy	consistent	データ型の大文字化に一貫性がある
	ignore_words	None	ルールから無視するワード
	ignore_words_regex	None	ルールから無視するワード（正規表現）
sqlfluff:rules:L064	preferred_quoted_literal_style	consistent	引用符で囲まれたリテラルの優先引用符の使用法が一貫している
	force_enable	False	このルールがデフォルトで無効になっている言語は無効のまま

実行

実行には公式のdocker imageを使った。

一旦以下のような運用で試してみた。今回はローカルで実行することを前提としている。
実行してみたところ、SQLファイルのいくつかでPARSE ERRORとなっていた。これになると、sqlfluffが正常に実行されるか分からなくなるため、fixをする前に手元でlintをしてPARSE ERRORがないか確認し、他の書き方に変更できないか確認するフローを挟むことにした。

docker imageをローカルにpullしておく
1. docker pull sqlfluff/sqlfluff:1.0.0
sqlfluffのdocker imageからコンテナを起動し、sqlfluffを実行する
1. lint実行
  1. docker run -it --rm -v $PWD:/sql sqlfluff/sqlfluff:1.0.0 lint /path/to/sqlfile --config /path/to/.sqlfluff
  2. 構文解析エラーがある場合は、他の書き方で回避できないかを検討する。
  3. 他の書き方で回避できる
    1. 保守性とlint修正コストを天秤にかけて、後者の方を重視すべき場合は修正してfixを実行する
      1. docker run -it --rm -v $PWD:/sql sqlfluff/sqlfluff:1.0.0 fix /path/to/sqlfile --config /path/to/.sqlfluff
    2. 他の書き方で回避できないは次のどちらかで対応する
      1. lint結果を見て手動で直す
      2. --FIX-EVEN-UNPARSABLEをつけてfixさせた後、構文自体に異常がないかを確かめる

備考

デフォルトではparse errorを含むファイルはfixしないが、--FIX-EVEN-UNPARSABLEを指定することで強制fixできる。

感想

カスタマイズ性が高いので、うまく自分達のルールにフィットさせることができて便利だった。
次はGithub Actionsと絡めてfix自体の自動化を図りたい。

背景

比較検討

sqlfluff

使い方

設定

実行

備考

感想

Discussion