概要
AWS Redshiftにはクエリの結果をS3に出力するUNLOADコマンドがあります。
UNLOAD ('{query}') といった形でクエリをシングルクオートで囲むのですが、それゆえにクエリ内でシングルクオートをエスケープせねばならず面倒です。
またクエリを手書きしているときはまだしもプレースホルダなどを使っていてシングルクオートが自動で挿入される場合は途方に暮れてしまいます。
こうした事態に対処すべく色々検討した結果、シングルクオートをエスケープせずに使える方法が見つかったのでご紹介します。
それはクエリを"$$"で囲むという方法です。
背景
弊社ではAWS RedshiftにビッグデータのDBを構築しており、そこからのデータ抽出がよく行われます。
抽出されるデータが大きいのでEC2上にそのまま書き出すわけにもいかず、S3に書き出してからローカルに移して分析するといったパターンが常です。
そこでRedshiftのUNLOADコマンドを使います。
UNLOAD ('select-statement')
TO 's3://object-path/name-prefix'
authorization
[ option [ ... ] ]
さて、この"('select-statement')"の部分にクエリを書くのですが、ドキュメントには
クエリは、次に示すように引用符で囲む必要があります:
('select * from venue order by venueid')
とあります。
さらに
クエリに引用符が含まれている場合 (たとえば、リテラル値を囲むためなど)、リテラルを 2 組の一重引用符の間に置きます。
また、クエリを一重引用符で囲む必要もあります。
('select * from venue where venuestate=''NV''')
とのこと。
またこちらには
クエリの中に引用符がある (例えば、リテラル値を囲むため) 場合は、クエリテキスト内でエスケープする (') 必要があります。
とあり、クエリ内ではシングルクオートは二つ重ねるか"\"によってエスケープする必要があるようです。
対策
素朴な対処法
まずはドキュメントに従い、シングルクオートをエスケープすることを考えます。
例としてPythonからpsycopg2パッケージを使ってクエリを投入してみましょう。
import psycopg2
conn = psycopg2.connect(
host=host,
dbname=dbname,
port=port,
user=user,
password=password
)
unload_template = "UNLOAD ('{query}') TO 's3://bucket/key' CREDENTIALS 'aws_iam_role=arn:aws:iam::xxxxxxxxxxxx' delimiter '\t' allowoverwrite"
query = unload_template.format(query="select * from address where name = ''taro''")
(または)query = unload_template.format("select * from address where name = \\'taro\\'")
cur = conn.cursor()
cur.execute(query)
(Pythonのお作法的に文字列はシングルクオートで囲むのですが、そうするとさらにエスケープせねばならず大変なのでダブルクオートにしています)
シングルクオートを重ねてエスケープする場合は二つ、バックスラッシュでエスケープする場合はバックスラッシュもエスケープする必要があるのでバックスラッシュ二つ＋シングルクオートを書くことになります。
このような単純なクエリならまだ大丈夫なのですが、条件が複雑になりリテラルを大量に埋め込む必要が出てくると忘れずにエスケープすることが困難になりますし、何より面倒です。
またプレースホルダを使う場合を考えてみると、さらにややこしくなります。
psycopg2にはpsycopg2.sqlというクエリ構成用のクラスがあり、それを使ってみましょう。
psycopg2.sql.SQL()に文字列を渡すと、Pythonの普通の文字列のようなオブジェクトが返ってきます。
このオブジェクトは{}で囲んだ部分にformat()で文字列を挿入することができ、またそれがプレースホルダとして機能する(つまり組み立てられてからDBに送られない)優れモノです。
リテラルを挿入する場合はpsycopg2.sql.Literal()を使います。
import psycopg2
from psycopg2 import sql
conn = psycopg2.connect(
host=host,
dbname=dbname,
port=port,
user=user,
password=password
)
unload_template = sql.SQL("UNLOAD ('{query}') TO 's3://bucket/key' CREDENTIALS 'aws_iam_role=arn:aws:iam::xxxxxxxxxxxx' delimiter '\t' allowoverwrite")
query_template = sql.SQL("select * from address where name = {name}")
query = query_template.format(name=sql.Literal("taro"))
unload = unload_template.format(query=query)
cur = conn.cursor()
cur.execute(unload)
psycopg2.sql.Literal()で挿入された文字列は自動でシングルクオートで囲まれますが、UNLOADコマンドで使うとシンタックスエラーになってしまいます。
psycopg2.errors.SyntaxError: syntax error at or near "taro"
LINE 1: ...OAD ('select * from address where name = 'taro'') TO 's...
これを回避する方策として、自動で挿入されるシングルクオートをエスケープするというのがまず思いつきますが、なかなかうまく行きません。
例えばバックスラッシュを使おうとすると、
query_template = sql.SQL("select * from address where name = \\{name}")
query = query_template.format(name=sql.Literal("taro\\"))
psycopg2.errors.SyntaxError: syntax error at or near "taro"
LINE 1: ...OAD ('select * from address where name = '\taro\\'') TO 's...
sql.Literal()がバックスラッシュを自動でエスケープしてくれて、結果として二つとも挿入されてしまい上手くいきません。
またsql.Literal()のなかにもう一つシングルクオートを入れると、
query_template = sql.SQL("select * from address where name = {name}")
query = query_template.format(name=sql.Literal("'taro'"))
psycopg2.errors.SyntaxError: syntax error at or near "taro"
LINE 1: ...OAD ('select * from address where name = '''taro'''') TO 's...
sql.Literal()がシングルクオートをエスケープしてくれるので一つ多くシングルクオートが入ってしまい、上手くいきません。
プレースホルダの前後にシングルクオートを追加すればようやくうまくいきます。
query_template = sql.SQL("select * from address where name = '{name}'")
query = query_template.format(name=sql.Literal("taro"))
ただし、複雑なクエリになりプレースホルダが増えてくると全てシングルクオートで囲むのは大変です。
冴えた対処法
インターネットの大海を彷徨っていると、こんなことを言っている人がいました。
You can also use postgres style :
unload
($$
select * from table where id='ABC'
$$)
to 's3://bucket/queries_results/20150324/table_dump/'
credentials 'aws_access_key_id=;aws_secret_access_key='
;
エウレカ！どうやらUNLOAD($$ {query} $$)のように書くとエスケープせずにシングルクオートが使えるとのことです。
本当なのかと思いつつ試してみると、うまくいきます。
unload_template = sql.SQL("UNLOAD ($$ {query} $$) TO 's3://bucket/key' CREDENTIALS 'aws_iam_role=arn:aws:iam::xxxxxxxxxxxx' delimiter '\t' allowoverwrite")
query_template = sql.SQL("select * from address where name = {name}")
query = query_template.format(name=sql.Literal("taro"))
unload = unload_template.format(query=query)
この場合、UNLOAD()のなかでクエリをシングルクオートで囲む必要もありません。
ちなみに、囲むと文法エラーになります。
unload_template = sql.SQL("UNLOAD ('$$ {query} $$') TO 's3://bucket/key' CREDENTIALS 'aws_iam_role=arn:aws:iam::xxxxxxxxxxxx' delimiter '\t' allowoverwrite")
query_template = sql.SQL("select * from address where name = {name}")
query = query_template.format(name=sql.Literal("taro"))
unload = unload_template.format(query=query)
psycopg2.errors.SyntaxError: syntax error at or near "taro"
LINE 1: ...('$$ select * from address where name = 'taro' $$') t...
結論
Redshiftからクエリ結果をUNLOADする際は、UNLOAD($$ {query} $$)のように書くとクエリ内のシングルクオートをエスケープする必要がなくなり大変便利。
(おまけ)そもそもこの"$$"とは何なのか
この$$を使った記法について、先ほどのstackoverflowの回答者は"postgres style"と言っていました。
というわけでRedshiftではなくPostgreSQLの公式ドキュメントを見に行くと以下のような記述があります。
4.1.2.2. ドル記号で引用符付けされた文字列定数
たいていの場合SQLにおける文字列定数の指定構文は便利ですが、対象とする文字列内に多くの単一引用符やバックスラッシュがあると、それらを全て二重にしなければなりませんので理解しづらくなります。 こうした状況においても問い合わせの可読性をより高めるためにPostgreSQLは、"ドル引用符付け"という他の文字列定数の指定方法を提供します。 ドル引用符付けされた文字列定数は、ドル記号($)、省略可能な0個以上の文字から成る"タグ"、ドル記号、文字列定数を構成する任意の文字の並び、ドル記号、この引用符付けの始めに指定したものと同じタグ、ドル記号から構成されます。 例えば、"Dianne's horse"という文字列をドル引用符付けを使用して指定する方法には、以下の2つがあります。
$$Dianne's horse$$
$SomeTag$Dianne's horse$SomeTag$
ドル引用符付けされた文字列はすでにエスケープされていますので、文字列定数は全てそのまま記述することができます。
その並びが開始タグに一致しない限り、バックスラッシュもドル記号も特別なものではありません。
$$を使った記法は「ドル引用符付け」というもののようです。
この$$が引用符の代わりになっているということから、これを使うとUNLOADコマンドでクエリをシングルクオートで囲む必要がなくなるのもよくわかります。
RedshiftはPostgreSQLをベースにしていますから、こうした文法も使えるのでしょう。