dbtとsqlfluffに向き合う

Change Log

• wtnvenga様にご指摘いただきまして、L014のルールを修正いたしました。

概要

• dbtとsqlfluffの詳しい設定に関して、ドキュメントを参考にしながら向き合ってみました
• ただきっちり綺麗にはいかなかったので誰か教えて欲しいと思い声を上げました(記事にしました)

https://docs.sqlfluff.com/en/stable/configuration.html#dbt-project-configuration

リポジトリはこちらに公開しています
https://github.com/tenajima/dbt_sqlfluff

パッケージのインストール

方針としてはsqlfluffもdbtもpipでインストールできるので、パッケージ管理をpoetryに寄せます。
poetryのインストールはこちらを参照してください。
使いたい場所でpoetry initして、必要な情報を入力してください。

poetry init

dbtとsqlfluffをインストールします。今回は他にPythonを使う場面がないとして、dev dependencyなどはあまり考えずにインストールしちゃいます。
また、僕はbigquery用のdbtをインストールしますが、ご自身の用途に合わせてください。

poetry add dbt-bigquery sqlfluff

dbtのプロジェクト作成

まずは仮想環境を有効化する必要があります。

poetry shell

dbt init

これだとさらにdbtのプロジェクトがサブディレクトリとしてできるのは気になる方もいるかもしれないです...
ただサンプルプロジェクトとかみてみるとdbtというディレクトリを切ってやっているようなのであまり変じゃないのかも。

プロジェクト名はdbtというプロジェクトにしました。
ワーキングディレクトリをそこに移して、.sqlfluffを作ります

cd dbt
touch .sqlfluff
touch .sqlfluffignore

この時点でのディレクトリ構成

.
├── dbt <- (ここで作業していく)
│   ├── README.md
│   ├── analyses
│   ├── data
│   ├── dbt_packages
│   ├── dbt_project.yml
│   ├── logs
│   │   └── dbt.log
│   ├── macros
│   ├── models
│   │   └── example
│   │       ├── my_first_dbt_model.sql
│   │       ├── my_second_dbt_model.sql
│   │       └── schema.yml
│   ├── snapshots
│   ├── target
│   └── tests
├── logs
│   └── dbt.log
├── poetry.lock
└── pyproject.toml

.sqlfluffを書いていく

初期段階

まずはこの時点で一度sqlfluffを実行してみます

 sqlfluff lint ./models/

するとこんな感じで出ます

== [models/example/my_first_dbt_model.sql] FAIL                                                
L:   1 | P:   1 | L050 | Files must not begin with newlines or whitespace.                     
== [models/example/my_second_dbt_model.sql] FAIL                                               
L:   1 | P:   1 | L050 | Files must not begin with newlines or whitespace.                     
All Finished 📜 🎉!     

基本的な設定を書いていく

予約語を小文字にするのかなどのsqlの設定を書いていきます。
dbt style guideに則った設定を心がけています。

*[sqlfluff:rules]
tab_space_size = 4
max_line_length = 80
indent_unit = space
comma_style = trailing

[sqlfluff:rules:L010]
capitalisation_policy = lower

[sqlfluff:rules:L011]
# Aliasing preference for tables
aliasing = explicit

[sqlfluff:rules:L012]
# Aliasing preference for columns
aliasing = explicit

[sqlfluff:rules:L014]
extended_capitalisation_policy = lower
unquoted_identifiers_policy = column_aliases

[sqlfluff:rules:L016]
ignore_comment_lines = True*

dbt用の設定を書く

[sqlfluff]
templater = dbt

を追加すると、sqlfluff-templater-dbtをインストールしろと怒られると思うのでインストールします。

poetry add sqlfluff-templater-dbt

sqlfluffのドキュメントに従って必要な設定を書いていきます

.sqlfluffignoreに

dbt_packages/
macros/

.sqlfluffに

[sqlfluff:templater:dbt]
project_dir = .
profiles_dir = ~/.dbt/
profile = test-project(自分なりに書き換えてね)
target = dev

これでsqlfluff lint ./models/example/my_first_dbt_model.sqlを実行すると

dbt found more than one package with the name "dbt" included in this project. Package names must be unique in a project. Please rename one of these packages.

というふうに怒られるのでdbt_project.ymlのnameを変更します
before

# Name your project! Project names should contain only lowercase characters
# and underscores. A good package name should reflect your organization's
# name or the intended use of these models
name: 'dbt'
version: '1.0.0'
config-version: 2
(以下省略)

after

# Name your project! Project names should contain only lowercase characters
# and underscores. A good package name should reflect your organization's
# name or the intended use of these models
name: 'tenajima_no_dbt_project'
version: '1.0.0'
config-version: 2
(以下省略)

ここまできてsqlfluff lint ./models/example/my_first_dbt_model.sqlを実行するとPythonのエラーが出ずに実行できます。

fixしてみる

jinjaの空白を消したり、asを抜いたり、予約語に大文字を混ぜたりしています。
before

/*
    Welcome to your first dbt model!
    Did you know that you can also configure models directly within SQL files?
    This will override configurations stated in dbt_project.yml

    Try changing "table" to "view" below
*/

{{config(materialized='table')}}

with source_data as (

    select 1 as id, 10 as age
    union all
    select null as id, 15 as age

)

SELECT id customer_id, age customer_age
from source_data

/*
    Uncomment the line below to remove records with null `id` values
*/

-- where id is not null

after

/*
    Welcome to your first dbt model!
    Did you know that you can also configure models directly within SQL files?
    This will override configurations stated in dbt_project.yml

    Try changing "table" to "view" below
*/

{{config(materialized='table')}}

with source_data as (

    select
        1 as id,
        10 as age
    union all
    select
        null as id,
        15 as age

)

select
    id as customer_id,
    age as customer_age
from source_data

/*
    Uncomment the line below to remove records with null `id` values
*/

-- where id is not null

するとjinjaの部分がうまくスペースをあけてくれていないのがわかります。
sqlfluff lint ./models/example/my_first_dbt_model.sqlを実行すると

Jinja tags should have a single whitespace on either side.

と怒られますが、fixでは修正してくれません。
むー、困った。すいませんこれは手動で解決しております。
この解決法知ってる方いたら教えてください。

最後に

消化不良で終わってしまい申し訳ありません。
sqlfluffとdbtの設定に関してわかりやすい例が見つからなかったので備忘録的に残してみました。
ここからは助けていただきたいです🙏