🦋

SQLFluffを試す(BigQuery+VSCode)

2023/12/28に公開

はじめに

新しいチームでSQLを書くことになり、自分の好きな書き方とチームのなんとなく共通の書き方が違ったため、SQLFluffを使ってリントしてもらおうと思い立つ。

環境構築

下記、参考に構築詳細割愛
https://dev.classmethod.jp/articles/sqlfluff-2/
インストール

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

2024/08/24追記

[sqlfluff]
exclude_rules = ST06,CP02 # カラム順不同,変数名の大文字小文字チェックを無効にする

上記だと誤判定される

エラー確認
$ sqlfluff lint -v
該当エラー
WARNING    Tried to denylist unknown rules references: ['CP02 # カラム順不同', '変数名の大文字小文字チェックを無効にする']  
[sqlfluff]
exclude_rules =
    # 変数名の大文字小文字チェックを無効にする
    CP02
    # カラム順不同
    ,ST06

基本操作

# 違反してる箇所確認
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>

https://stackoverflow.com/questions/71824282/sqlfluff-always-returns-templating-parsing-errors
デフォルトでは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を使っているが、そこで使う拡張子で使えない
https://zenn.dev/minodisk/articles/418c4ea7aee79e
https://marketplace.visualstudio.com/items?itemName=minodisk.bigquery-runner
.bqsqlファイルを SQL として強調表示する構文

# settings.json
{
  "files.associations": {
    "*.bqsql": "sql"
  }
}

ショートカット設定は、自分の環境で動かなかったので未設定

sql_file_exts = .sql,.bqsql

下記で、紹介されているvscode-sqlfluffを使う
https://dev.classmethod.jp/articles/sqlfluff-2/
https://github.com/sqlfluff/vscode-sqlfluff

  1. 紹介されている設定をする
  • ディレクトリ構造を確認
./
├── .vscode
│   └── settings.json
├── linter
│   └── .sqlfluff
└── sql
    ├── example1.sql
    └── example2.sql
  • .vscode/settings.json ファイルを作成して記入するか、VScodeの設定に直接記入するのでも良いかと
{
    "sqlfluff.config": "${workspaceFolder}/linter/.sqlfluff"
}
  1. 右クリックのドキュメントのフォーマット...sqlfluffを既定に設定
  2. 右クリックのドキュメントのフォーマットでフォーマットをかける

BigQuerySQLの構文ハイライトとコード スニペット

  • .bqsqlの拡張子も対応してそう → 使ってみたら不満なし

https://marketplace.visualstudio.com/items?itemName=shinichi-takii.sql-bigquery

.bqsqlのファイルアイコン設定

使っているアイコンセット
https://marketplace.visualstudio.com/items?itemName=PKief.material-icon-theme
設定(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を使っているが、テーブル検索機能がないと不便に感じる
下記、参考にテーブル検索できるクエリを作成
https://zenn.dev/yuichi_dev/articles/a463c46d9168c8

-- テーブルリストを格納する配列を宣言
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
);

参考リンク

https://docs.sqlfluff.com/en/stable/rules.html
https://zenn.dev/sasakky/articles/bigquery_format
https://hama-matcha-notebook.tech/it技術/snowflake/【sql】snowflakeで使う際のsqlfluffでフォーマットを考えてみた/301
https://qiita.com/Ross/items/1a64139b11a37b856ec6
https://zenn.dev/daisuke_harato/articles/0852c43e4d1d6a

Discussion