Snowflake のクエリ内変数と、executemany のテーブル指定に identifier が使えない話

Snowflake を Python から利用していて、クエリ文字列に変数をバインドする方法がややこしかったので共有します。クエリ内でプログラムから変数を渡す %s, :1, ? などですね:

Using the Python Connector — Snowflake Documentation

con.cursor().execute(
    "INSERT INTO testtable(col1, col2) "
    "VALUES(%s, %s)", (
        789,
        'test string3'
    ))

パラメータスタイルをコネクション生成時に選ぶ必要がある

どんな方法で変数を表現するかを Snowflake へのコネクション生成時に 指定する必要があります。公式ドキュメントにも以下の様に記載されています:

Using the Python Connector — Snowflake Documentation

Snowflake supports the following types of binding:
* pyformat and format, which bind data on the client.
* qmark and numeric, which bind data on the server.

github から enum を見ても、以下の4種類が指定できることが分かります（デフォルトは pyformat）

snowflake-connector-python/connection.py at main · snowflakedb/snowflake-connector-python · GitHub

SUPPORTED_PARAMSTYLES = {
    "qmark",
    "numeric",
    "format",
    "pyformat",
}

このため、例えば numeric を指定したコネクションで、%s のような pyformat でクライアント側で処理しなければならないプレースホルダを指定した場合、変数置換されずにそのままクエリ実行しようとしてしまい、構文エラーとなります。

INSERT 時の executemany に、identifier が使えない

次に少しニッチな話ですが、INSERT 文を実行する際にテーブル名などの Snowflake オブジェクトを文字列で与えて、プログラムから動的に与えられるようにした場合の挙動が、execute と executemany で違う件を共有します。

execute

まずは execute から。これは分かりやすく、クライアントサイド(以下の例はpyformat)でも、サーバーサイド(以下の例はnumeric)でも素直に変数置換されています。

# paramstyle: pyformat -> PASS
con.cursor().execute("INSERT INTO identifier(%s) (col1) VALUES(%s);",
  ('testtable', 'test'))

# paramstyle: numeric -> PASS
con.cursor().execute("INSERT INTO identifier(:1) (col1) VALUES(:2);",
  ('testtable', 'test'))

executemany

次に executemany です。これは変数をリストや配列で渡すことで複数クエリを実行する関数です。しかしクライアントサイドで変数置換しても、サーバサイドで行っても、どちらもエラーとなってしまいます。

executemany, paramstyle: pyformat

クライアントサイド(pyformat)の場合、以下のエラーとなります:

# paramstyle: pyformat -> FAIL
con.cursor().executemany(
    "INSERT INTO identifier(%s) (col1) VALUES(%s);",
    (('testtable', 'test1'), ('testtable', 'test2')))
# -> TypeError: not all arguments converted during string formatting

プログラムを見ると分かるのですが、executemany は何故か VALUES の中のみが置換対象となっていて、最終的には以下のような処理が実行されてエラーとなります:

"%s" % ('testtable', 'test1')
-> TypeError: not all arguments converted during string formatting

プレースホルダが1つなのに対し、2つの変数を置換しようとしているため、エラーとなります。

executemany, paramstyle: numeric

サーバサイド(numeric)の場合もエラーとなりますが、エラー内容が少し異なります:

# paramstyle: numeric -> FAIL
con.cursor().executemany(
    "INSERT INTO identifier(:1) (col1) VALUES(:2);",
    (('testtable', 'test1'), ('testtable', 'test2')))
# -> SQL compilation error: Bind variable for object 1 not set

:1 に対応する変数が設定されていない、という内容のエラーのようですが、引数を見ると分かる通り変数は2つずつ渡されているため、 :1 は testtable として渡しているはずです。おそらくこちらの処理も、VALUES のみが置換対象となっているように見えます。

結論: (executemany ではテーブル名を動的に変更できない)

結局、executemany では変数部分を動的に変更することしかできないようです。

テーブル名は変数で渡したいけれど、executemany で実行するテーブル名はそれぞれ固定である場合は、文字列内で f"INSERT INTO {table_name} VALUES..." と変数展開するのが早そうですが、Snowflakeへ渡すクエリの自由度が高くなってしまいます。identifier をテーブルに利用して唯一パスしたのが、サーバサイド(numeric)でテーブル名を変数展開するパターンでした:

# paramstyle: numeric -> PASS
con.cursor().executemany(
    """INSERT INTO identifier("{table_name}") (col1) VALUES(:1);""",
    (('test1'), ('test2')))

同じ様な方法でクライアントサイド(pyformat)を試してもエラーとなってしまうため、注意してください:

# paramstyle: pyformat -> FAIL
con.cursor().executemany(
    """INSERT INTO identifier("{table_name}") (col1) VALUES(%s);""",
    (('test1'), ('test2')))

今回、この1つ目の paramstyle の問題と2つ目の executemany の問題が同時に起きていて、原因が分かると難しくないのですが調査に時間がかかってしまいました。。SET文を使って Snowflake 側で宣言した変数を使うとどうなるかなど、まだ試していないことも多いですが、何かの役に立てば幸いです。他にもSQLインジェクションを防ぐ手立てとして、知見がある方がいらっしゃればぜひ教えてください。

/home/by-natures/dev*

データ界隈で働くエンジニアとしての技術的なメモと、たまに普通の日記。