🦋
SQLFluffを試す(BigQuery+VSCode)
はじめに
新しいチームでSQLを書くことになり、自分の好きな書き方とチームのなんとなく共通の書き方が違ったため、SQLFluffを使ってリントしてもらおうと思い立つ。
環境構築
下記、参考に構築詳細割愛 インストール
pip install sqlfluff
やりたいこと
- 前カンマ
- 予約後やNULLなどを大文字、、、
.sqlfluffファイルを配置
.sqlファイルと同じフォルダに配置
.sqlfluffファイルの設定
[sqlfluff]
dialect = bigquery
max_line_length = 200
output_line_length = 200
sql_file_exts = .sql,.bqsql
# バイト制限解除
large_file_skip_byte_limit = 0
# ルールの中から取り除くものを選ぶ
exclude_rules =
# sqlfluff fixでカラムの順序を変えないようにするための設定
structure.column_order
,ambiguous.column_count
,structure.using
# Salesforceはキャメルケースでlowerにすると読みにくくなるため追加
,capitalisation.identifiers
[sqlfluff:indentation]
indent_unit = space
tab_space_size = 4
[sqlfluff:rules:layout.long_lines]
# コメント行は長くても可とする。URLなどが入るため
# 行の長さに関してコメント行は無視する
ignore_comment_lines = True
# 行の長さに関してコメント句は無視する
ignore_comment_clauses = True
[sqlfluff:layout:type:comma]
# 先頭のカンマの強制
line_position = leading
# 前カンマの場合スペース入れない
spacing_after = touch
[sqlfluff:rules:capitalisation.keywords]
# 予約語の大文字小文字
capitalisation_policy = upper
[sqlfluff:rules:capitalisation.functions]
# 関数名の大文字小文字
extended_capitalisation_policy = upper
[sqlfluff:rules:capitalisation.literals]
# リテラル値 (null・true・false) の大文字小文字
capitalisation_policy = upper
[sqlfluff:rules:capitalisation.types]
# データ型の大文字小文字
extended_capitalisation_policy = upper
; [sqlfluff:rules:capitalisation.identifiers]
; # normalize(str, 'NFKC')といった形式で呼び出しいる箇所がnormalize(str, 'nfkc')となってしまう事象回避
; ignore_words = NFD,NFC,NFKD,NFKC
基本操作
# 違反してる箇所確認
sqlfluff lint <dir_path or file_path>
# 違反してる箇所修正
sqlfluff fix <dir_path or file_path>
# SQLのパースが失敗して原因を見る
sqlfluff parse <dir_path or file_path>
備考
sqlfluff fixでエラー
sqlfluff fixで下記が返された時
[1 templating/parsing errors found]
sqlfluff fix で自動で修正できない箇所があるのが原因
sqlfluff parseで違反が発生している行番号が表示されます。すべての違反を修正したら、sqlfluff fix再度実行します。
# SQLのパースが失敗して原因を見る
sqlfluff parse <dir_path or file_path>
デフォルトではparse errorを含むファイルはfixしないが、--FIX-EVEN-UNPARSABLEを指定することで強制fixできるらしい
sqlfluff fixで修正できなかったケース
-- 単純CASE式
-- WHENの前にidがあるパターンだと、fixしてもエラーになる
case id when 1 then true else false end
-- その場合下記の形に修正して再度fix
-- 検索CASE式
case when id = 1 then true else false end
.bqsqlでリントできない
BigQuery Runner for VSCodeを使っているが、そこで使う拡張子で使えない
.bqsqlファイルを SQL として強調表示する構文
# settings.json
{
"files.associations": {
"*.bqsql": "sql"
}
}
ショートカット設定は、自分の環境で動かなかったので未設定
-
BigQuery Runner for VSCodeの分かりやすい設定ガイド
https://developers.lseg.com/en/article-catalog/article/run-google-bigquery-sql-with-vscode
下記の設定で動く
sql_file_exts = .sql,.bqsql
下記で、紹介されているvscode-sqlfluffを使う
- 紹介されている設定をする
- ディレクトリ構造を確認
./
├── .vscode
│ └── settings.json
├── linter
│ └── .sqlfluff
└── sql
├── example1.sql
└── example2.sql
-
.vscode/settings.jsonファイルを作成して記入するか、VScodeの設定に直接記入するのでも良いかと
{
"sqlfluff.config": "${workspaceFolder}/linter/.sqlfluff"
}
- 右クリックの
ドキュメントのフォーマット...でsqlfluffを既定に設定 - 右クリックの
ドキュメントのフォーマットでフォーマットをかける
BigQuerySQLの構文ハイライトとコード スニペット
-
.bqsqlの拡張子も対応してそう → 使ってみたら不満なし
.bqsqlのファイルアイコン設定
使っているアイコンセット 設定(setting.json)
"material-icon-theme.files.associations": {
"*.bqsql": "database"
}
GitHub上で.bqsqlのシンタックスハイライトを有効にする
GitHubでは、.bqsql ファイルをSQLとして強調表示するために、.gitattributes ファイルをリポジトリのルートに作成し、その中に拡張子に対する言語ヒントを設定することができます。
以下は、.gitattributes ファイルの例です。この例では、.bqsql ファイルを SQL として扱うように指定しています。
# .gitattributes
*.bqsql linguist-language=sql
これにより、GitHubは .bqsql ファイルを SQL として解釈し、適切なシンタックスハイライトを提供します。
テーブルリストの検索
BigQuery Runner for VSCodeを使っているが、テーブル検索機能がないと不便に感じる
下記、参考にテーブル検索できるクエリを作成
-- テーブルリストを格納する配列を宣言
DECLARE table_list ARRAY<STRING>;
DECLARE research_table_id STRING;
-- 検索対象のテーブル
SET research_table_id = "検索対象のテーブル名";
-- テーブルリストを取得
SET table_list = (
SELECT ARRAY_AGG(CONCAT(catalog_name,'.',schema_name,'.__TABLES__'))
FROM [PROJECT_ID.]`region-REGION`.INFORMATION_SCHEMA.SCHEMATA
);
-- テーブルリストからテーブルを選択してクエリを実行
EXECUTE IMMEDIATE (
SELECT STRING_AGG(
CONCAT('SELECT CASE type WHEN 1 THEN "table" WHEN 2 THEN "view" ELSE "" END AS table_type,CONCAT("`",project_id,".",dataset_id,".",table_id,"`") AS tabel_id,FORMAT_TIMESTAMP("%Y-%m-%d %H:%M:%S", TIMESTAMP_MILLIS(creation_time), "Asia/Tokyo") as creation_time,FORMAT_TIMESTAMP("%Y-%m-%d %H:%M:%S", TIMESTAMP_MILLIS(last_modified_time), "Asia/Tokyo") as update_time,row_count,size_bytes / 1024 / 1024 / 1024 as size_gb FROM `'
, table
, '` WHERE REGEXP_CONTAINS(table_id, "', research_table_id, '")' )
, ' UNION ALL ')
FROM UNNEST(table_list) AS table
);
参考リンク
Discussion