🐍

pyhive + jinjasqlで、prestoに対してprepared statementを実行

2021/12/27に公開

Python

はじめに

Qiitaとダブルポストです。

pythonを使って、prestoに対して、変数付きのSQL（prepared statement）を実行する必要があったので、そのときの経験の整理です。

prepared statementとは

平たくいうと、where句の条件の値のところを変数にしたSQLです。
parameterized queryといわれることもあるようです。

変数の書き方にはいくつかあり、よくあるものは下記になります。

named

select * from user where id =　:id

qmark

select * from user where id =　?

numeric

select * from user where id =　:1

python固有のフォーマットだとこちらになります。

pyformat

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のテンプレート言語で実現できます。

sample.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を返してくれます。

query

select * from user where id in (%(inclause_1)s,%(inclause_2)s,%(inclause_3)s) and status = %(status_4)s

bind_params

{'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を実行

のノウハウが手に入りました。

はじめに

prepared statementとは

PEP 249 とは

PEP 249 でのprepared statementの扱い

PEP 249 に準拠したモジュールの例

Prestoの公式クライアント

pyhive

jinjasqlをかませた理由

まとめ

Discussion