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

# はじめに

[Zenn](https://zenn.dev/nslender/articles/715c8b72e38f0d) とダブルポストです。

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

# prepared statementとは

平たくいうと、where句の条件の値のところを変数にしたSQLです。

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

```sql:named
select * from user where id =　:id
```

```sql:qmark
select * from user where id =　?
```

```sql:numeric
select * from user where id =　:1
```

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


```sql:pyformat
select * from user where id =　%(id)s
```

これらは、PEP 249にもまとめてあります。

https://www.python.org/dev/peps/pep-0249/#paramstyle


# PEP 249 とは

DBアクセスするpythonモジュールを作る際、同じような使い方できるように定義されたAPI（インターフェース）です。
モジュールを利用する側としては、コネクションやカーソルオブジェクトにどのようなメソッドが定義されているかをおさえていれば、PEP 249を守って実装されたモジュールを同じように使えて便利です。
モジュールごとに独自に実装されていると、そのたびに新しい使い方を覚えることになるので、こういうガイドライン的なものがあるのはありがたいです。

https://www.python.org/dev/peps/pep-0249/#introduction

## PEP 249 でのprepared statementの扱い

```python
query = 'select * from user where id =　%(id)s'
params = {'id': 100}
cursor = ... 
cursor.execute(query, params)
```

こうした場合に、

```sql
select * from user where id = '100'
```

のように、

* 変数に対してエスケープ処理
* エスケープ処理したものをクエリに代入

したSQLを作成して、実行、というのが期待されます。

https://www.python.org/dev/peps/pep-0249/#id15


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

普段、どのDBを使ってるか次第ですが、このあたりが触れる機会が比較的多いのではないでしょうか。

* [MySQL Connector/Python](https://dev.mysql.com/doc/connector-python/en/connector-python-introduction.html)
* [Psycopg](https://www.psycopg.org/docs/)


mysql connectorだと、このような使い方になります。

```python
select_stmt = "SELECT * FROM employees WHERE emp_no = %(emp_no)s"
cursor.execute(select_stmt, { 'emp_no': 2 })
```

https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html

# Prestoの公式クライアント

[presto-python-client](https://github.com/prestodb/presto-python-client) になります。

https://prestodb.io/resources.html#language-python

インターフェース自体は、PEP 249にもとづいています。


ですが、cursor#executeの実装をみると、

```python
    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
```

https://github.com/prestodb/presto-python-client/blob/master/prestodb/dbapi.py#L234

変数を代入するためのparamsを使っていない実装になっています...
つまり、prepared statementの実行はできないということです。

issueもあがっています。

https://github.com/prestodb/presto-python-client/issues/92



# pyhive

というわけで、別のクライアントモジュールを探しました。
pyhiveのprestoのcursor#executeは、parametersのエスケープ処理+代入をやっています。


```python
        if parameters is None:
            sql = operation
        else:
            sql = operation % _escaper.escape_args(parameters)
```

https://github.com/dropbox/PyHive/blob/master/pyhive/presto.py#L256

若干、脱線ですが、見てのとおり、pythonの文字列は、%を演算子扱いにして、formatされた文字列に、変数を代入する仕組みがあります。これはC言語のsprintf()からの流れのようです。

https://docs.python.org/3.9/library/stdtypes.html#old-string-formatting

つまり、pyhive[presto]でprepared statementを使うためには、SQL内の変数をpyformatで記述する必要があります。

# jinjasqlをかませた理由

SQLがソースコードに埋め込めるくらいの行数であれば、コード内でpyformatで記述した文字列をつくれば事足ります。
案件によってはそうもいかないことが多々あります。

今回の場合、

* SQLの種類が10以上と多い
* SQLが100行こえていて、ファイルとして外だししたい
* テーブル名も変数扱いしたい
* in句にいれる値も変数扱いしたい

といったことを乗り越えないと、実装と運用で厳しいと感じたので、

SQLファイル -> pyformat形式に変換 + 代入するためのdict作成 -> prepared statement実行
という段階を踏むことにしました。

jinjasqlという、SQLのテンプレート言語で実現できます。

https://github.com/sripathikrishnan/jinjasql


```sql:sample.sql
select * from {{ table_name | sqlsafe }} where id in {{ ids | inclause }} and status = {{ status }}
```

```python
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に対応するメソッドにわたすだけです。

```python
cursor.execute(query, bind_params)
```

# まとめ

* pyhive[presto] + pyformat で、prestoに対してprepared statementを実行
* pyhive[presto] + jinjasql + pyformatで、prestoに対して、SQLファイルからprepared statementを実行

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