はじめに
本記事ではPythonでの組み込みSQL開発において、SQLのリンタ機能と変数組み込みを両立させる方法について解説します。
よくある開発手法の問題点
早速ですが、PythonでSQLを作成する時、どのように実装するでしょうか？
私は直近まで以下のように記述していました。
def main(yyyymm, table_name):
sql = f"""
SELECT
col1,
col2,
FROM
{table_name}
WHERE
yyyymm = {yyyymm}
"""
これで内部の変数を動的に変更できますが、これではSQLにエラーがあっても誰も教えてくれません。実行時まで問題に気づけず、大幅にタイムロスをしてしまった経験はないでしょうか？
実際、上記のクエリにはエラーが含まれていますし、私も数えきれないほどやらかしてきました。
それにシンタックスハイライトも効かないので見づらいですし、SELECTをSELETとタイポしていても教えてくれません。
この問題は開発効率を落とすだけではなく、SQLの品質にも影響があります。
共通のSQLフォーマッタが存在しないので記法がバラバラになったり、微修正でエラーが残ってしまったりなど……
「組み込み変数を使うこと」と「リンタを適用させること」を両立できないことが本当に不便で、でも組み込みSQLを使わないといけない場面が多く、ずっと困っていました。
ですが試行錯誤の末、変数の組み込みとリンタの両立に成功しました。設定方法を紹介します。
本記事ではその設定方法について紹介します。
準備
使用ライブラリ
- sqlfluff
- jinja
サンプルコード
GitHubのリポジトリに実行可能な環境を用意しました。Devcontainer で環境ごと再現できるので、VSCode から起動すればそのまま試せます。
※Windows向けの設定になっており、環境変数HOMEのを使用する構成になっています。ご自身の環境に合わせて適宜編集してください
※Devcontainerでの起動を推奨しますが、ファイル構造や設定はローカル開発でも参考になるかと思います
実践
ひとまず細かい設定は後回しにして、どのような挙動になるのかを紹介します。
フォーマッタ起動
Devcontainerを起動して、エラーのあるSQLを覗いてみます。
当然select -> seletでエラーです。修正すると、
余分なカンマが存在するのでエラーを吐いています。
また、sqlfluffの設定で命令構文はすべて大文字にすることを指定しているので、そこも注意されています。
この程度の問題なら SQLFluff のフォーマッタで自動修正できます。Shift+Alt+F（Windows） でフォーマットしましょう。
自動できれいになりました。
フォーマット機能はSQLファイルならではですが、組み込み変数を持つSQLでは通常の設定だけだと{{ ... }} を認識してくれないので困っていました。
カスタムマクロを用いて解決したのですが、それはまた後程紹介します。
Pythonからの読み込み
main関数は以下のようになっています。
(※jinja_wrapperは自作のモジュールです。後ほど解説します。)
from my_jinja.jinja_wrapper import JinjaTemplater
def main():
jj = JinjaTemplater(folder_path="./sql/")
rendered_sql_1 = jj.render(
sql_file="example1.sql",
table_name="test_table_1",
yyyymm='202508'
)
print("\n=======================\n")
print(rendered_sql_1)
print("\n=======================\n")
rendered_sql_2_list = jj.render_multi(
sql_file="example2.sql",
table_name="test_table_2",
yyyymm='202508'
)
for sql_2 in rendered_sql_2_list:
print(sql_2)
print("\n=======================\n")
if __name__ == "__main__":
main()
出力結果
=======================
SELECT
col1,
col2,
col3
FROM
test_table_1
WHERE yyyymm = '202508'
=======================
（以下略）
ご覧の通り、example1.sqlに変数を組み込むことに成功しました。
エディタによる強力なコード補完と、変数組み込みをPythonから実行できる。この機能が強力だとわかっていただけそうでしょうか？
解説
ここからは、具体的な設定やファイルの解説を行います。
動かすだけならこれ以降は必要ないかと思いますが、カスタマイズの前にはぜひご覧ください。
sqlfluff設定
sqlfluffで重要なのは、設定ファイルとマクロの定義です。
SQLファイルで {{ string_var('yyyymm') }} のように記述してリンタがエラーを出していないのは、string_varをマクロとして定義しているからです。
マクロは./sqlfluff_macros/macro.sqlに定義しています。
{% macro string_var(name) %}
{{ "'" ~ (var(name) | string) ~ "'" }}
{% endmacro %}
今回は中身を使用していないので定義だけですが、dbtのカスタムマクロに転用しても動作します。
（ちなみに、dbtにおけるカスタムマクロを使用することもできます）
これはJinjaの機能の一部で、.sqlfluffファイルにてマクロのあるフォルダを指定することで、リンタであるsqlfluffから認知できるようになります。
# マクロのパスを指定
[sqlfluff:templater:jinja]
load_macros_from_path = ./sqlfluff_macros/
(以下略)
sqlfluffにはほかにもコード規約に関する設定が多数ありますが、そのあたりは本記事では取り扱わないこととします。
Python側設定
自作コードであるjinja_wrapperの一部を紹介します。
class JinjaTemplater:
def __init__(self, folder_path: str):
self.env = Environment(
loader=FileSystemLoader(folder_path),
trim_blocks=True,
lstrip_blocks=True,
)
self.env.globals["string_var"] = jm.string_var
self.env.globals["var"] = jm.var
(以下略)
ここで重要なのは以下の部分です。
self.env.globals["string_var"] = jm.string_var
self.env.globals["var"] = jm.var
sqlfluffではstring_varやvarが定義されていましたが、Python（Jinja）側ではまだそのことを知りません。だから組み込み変数の実際の挙動を記述する必要がありました。
from jinja2 import pass_context
@pass_context
def string_var(context, name):
return_value = "'" + context.get(name, None) + "'"
if return_value is None:
raise ValueError(f"Variable '{name}' not found in context.")
return return_value
@pass_context
def var(context, name):
return_value = context.get(name, None)
if return_value is None:
raise ValueError(f"Variable '{name}' not found in context.")
return return_value
結論として、SQLFluff（リンタ）と Jinja（Python 実行時）それぞれに同じマクロを定義する必要がありました。見た目はスマートでも、実装は力技に近いアプローチな気もしています。
ちなみに、わざわざstring_varを定義した理由ですが、例えば以下のようにしても記述はできます。
しかしエディタからは{{ var("yyyymm") }}全体が文字列として認識されてしまい、視認性が悪かったからです。それが許容できるのであれば、わざわざstring_varを定義する必要はないかと思います。
select *
from table
where yyyymm = '{{ var("yyyymm") }}'
その他設定
VSCodeまわり
ワークスペースの設定として記述するため、.vscode/ 配下に設定を記述しています。
{
"[sql]": {
"editor.defaultFormatter": "dorzey.vscode-sqlfluff",
"editor.formatOnType": true,
"editor.tabSize": 2,
"editor.insertSpaces": true,
},
"files.autoSave": "off",
"sqlfluff.executablePath": "/usr/local/bin/sqlfluff"
}
DB指定
GitHubの.sqlfluffファイルでは、以下のような設定があります。
[sqlfluff]
dialect = athena
templater = jinja
これは、sqlfluffの影響範囲内ではSQLをAmazon Athena形式で解釈する、という意味になります。
とはいえ複数のDBが混在することもあるかと思います。
そんな時は、example2.sqlのように、冒頭にコメントを挿入すればOKです。
-- sqlfluff:dialect:redshift
DELETE FROM {{ var('table_name') }}
WHERE yyyymm = {{ string_var('yyyymm') }}; -- noqa: CV06
INSERT INTO {{ var('table_name') }}
SELECT
col3,
col4
FROM source_table
WHERE {{ string_var('yyyymm') }}
また上記コードの-- noqa: CV06は、行ごとに特定の警告を無視することができます。どうしても警告が消えない場合、このように解決するのも手段の1つです。
さいごに
組み込みSQLは便利な反面、SQLの品質が担保しにくい危うい手法だと思っています。
大量のSQLを組み込みSQLで実装し運用したこともありますが、視認性が悪く保守性も悪くなってしまいました。
組み込みSQLを使う方はぜひ、今回紹介した開発環境を参考にしていただきSQL品質と開発効率を高めてください！