はじめに
Zenn とダブルポストです。
pythonを使って、prestoに対して、変数付きのSQL（prepared statement）を実行する必要があったので、そのときの経験の整理です。
prepared statementとは
平たくいうと、where句の条件の値のところを変数にしたSQLです。
変数の書き方にはいくつかあり、よくあるものは下記になります。
select * from user where id = :id
select * from user where id = ?
select * from user where id = :1
python固有のフォーマットだとこちらになります。
select * from user where id = %(id)s
これらは、PEP 249にもまとめてあります。
PEP 249 とは
DBアクセスするpythonモジュールを作る際、同じような使い方できるように定義されたAPI（インターフェース）です。
モジュールを利用する側としては、コネクションやカーソルオブジェクトにどのようなメソッドが定義されているかをおさえていれば、PEP 249を守って実装されたモジュールを同じように使えて便利です。
モジュールごとに独自に実装されていると、そのたびに新しい使い方を覚えることになるので、こういうガイドライン的なものがあるのはありがたいです。
PEP 249 でのprepared statementの扱い
query = 'select * from user where id = %(id)s'
params = {'id': 100}
cursor = ...
cursor.execute(query, params)
こうした場合に、
select * from user where id = '100'
のように、
- 変数に対してエスケープ処理
- エスケープ処理したものをクエリに代入
したSQLを作成して、実行、というのが期待されます。
PEP 249 に準拠したモジュールの例
普段、どのDBを使ってるか次第ですが、このあたりが触れる機会が比較的多いのではないでしょうか。
mysql connectorだと、このような使い方になります。
select_stmt = "SELECT * FROM employees WHERE emp_no = %(emp_no)s"
cursor.execute(select_stmt, { 'emp_no': 2 })
Prestoの公式クライアント
presto-python-client になります。
インターフェース自体は、PEP 249にもとづいています。
ですが、cursor#executeの実装をみると、
def execute(self, operation, params=None):
self._query = prestodb.client.PrestoQuery(self._request, sql=operation)
result = self._query.execute()
self._iterator = iter(result)
return result
変数を代入するためのparamsを使っていない実装になっています...
つまり、prepared statementの実行はできないということです。
issueもあがっています。
pyhive
というわけで、別のクライアントモジュールを探しました。
pyhiveのprestoのcursor#executeは、parametersのエスケープ処理+代入をやっています。
if parameters is None:
sql = operation
else:
sql = operation % _escaper.escape_args(parameters)
若干、脱線ですが、見てのとおり、pythonの文字列は、%を演算子扱いにして、formatされた文字列に、変数を代入する仕組みがあります。これはC言語のsprintf()からの流れのようです。
つまり、pyhive[presto]でprepared statementを使うためには、SQL内の変数をpyformatで記述する必要があります。
jinjasqlをかませた理由
SQLがソースコードに埋め込めるくらいの行数であれば、コード内でpyformatで記述した文字列をつくれば事足ります。
案件によってはそうもいかないことが多々あります。
今回の場合、
- SQLの種類が10以上と多い
- SQLが100行こえていて、ファイルとして外だししたい
- テーブル名も変数扱いしたい
- in句にいれる値も変数扱いしたい
といったことを乗り越えないと、実装と運用で厳しいと感じたので、
SQLファイル -> pyformat形式に変換 + 代入するためのdict作成 -> prepared statement実行
という段階を踏むことにしました。
jinjasqlという、SQLのテンプレート言語で実現できます。
select * from {{ table_name | sqlsafe }} where id in {{ ids | inclause }} and status = {{ status }}
j = JinjaSql(param_style='pyformat')
template = Path('sample.sql').read_text()
data = {
'table_name': 'user',
'ids': [1, 20, 300],
'status': 'active'
}
query, bind_params = j.prepare_query(template, data)
下記のように、pyformat形式のqueryと、それに対応するdictionaryを返してくれます。
select * from user where id in (%(inclause_1)s,%(inclause_2)s,%(inclause_3)s) and status = %(status_4)s
{'inclause_1': 1, 'inclause_2': 20, 'inclause_3': 300, 'status_4': 'active'}
みてのとおり、jinjasqlはエスケープ処理をしてくれません。その点は注意しましょう。
あとは、prepared statementに対応するメソッドにわたすだけです。
cursor.execute(query, bind_params)
まとめ
- pyhive[presto] + pyformat で、prestoに対してprepared statementを実行
- pyhive[presto] + jinjasql + pyformatで、prestoに対して、SQLファイルからprepared statementを実行
のノウハウが手に入りました。