AWS Redshift a une commande UNLOAD qui renvoie les résultats d'une requête à S3.
Je place la requête entre guillemets simples sous la forme de UNLOAD ('{query}'), ce qui est ennuyeux car je dois échapper aux guillemets simples dans la requête.
De plus, lorsque j'écris une requête à la main, je ne sais toujours pas si j'utilise toujours des espaces réservés et les guillemets simples sont automatiquement insérés.
À la suite de diverses études pour faire face à une telle situation, j'ai trouvé une méthode qui peut être utilisée sans échapper au guillemet simple, je vais donc la présenter.
La méthode consiste à placer la requête dans "$$".
Nous avons une base de données Big Data construite sur AWS Redshift, et les données en sont souvent extraites.
Étant donné que les données à extraire sont volumineuses, elles ne peuvent pas être écrites telles quelles sur EC2, et le modèle consiste généralement à les écrire dans S3, puis à les déplacer localement pour analyse.
Utilisez donc la commande UNLOAD de Redshift.
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_UNLOAD.html
UNLOAD ('select-statement')
TO 's3://object-path/name-prefix'
authorization
[ option [ ... ] ]
Eh bien, j'écris une requête dans cette partie "('select-statement')", mais dans le document
La requête doit être placée entre guillemets comme indiqué ci-dessous:
('select * from venue order by venueid')
Il y a.
plus loin
Si la requête contient des guillemets (par exemple, pour entourer une valeur littérale), placez le littéral entre deux paires de guillemets simples. Vous devez également mettre la requête entre guillemets simples.
('select * from venue where venuestate=''NV''')
Et cela.
Aussi, dans ici
S'il y a un guillemet dans la requête (par exemple, pour inclure une valeur littérale), il doit être échappé (\ ') dans le texte de la requête.
Il semble que les guillemets simples doivent être échappés par chevauchement ou "\" dans la requête.
Tout d'abord, suivez la documentation et envisagez d'échapper le guillemet simple.
À titre d'exemple, soumettons une requête à partir de Python en utilisant le package psycopg2.
unload_test.py
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''")
(Ou)query = unload_template.format("select * from address where name = \\'taro\\'")
cur = conn.cursor()
cur.execute(query)
(À la manière de Python, la chaîne de caractères est entourée de guillemets simples, mais il est difficile d'échapper davantage, il s'agit donc de guillemets doubles)
Si vous voulez échapper en superposant des guillemets simples, vous devez en échapper deux, et si vous échappez avec une barre oblique inverse, vous devez échapper une barre oblique inverse, vous devez donc écrire deux barres obliques inverses + guillemets simples.
Une simple requête comme celle-ci est toujours acceptable, mais lorsque les conditions se compliquent et que vous devez intégrer beaucoup de littéraux, il est difficile de se souvenir d'échapper, et surtout, c'est ennuyeux.
C'est encore plus compliqué lorsque vous pensez à utiliser des espaces réservés.
psycopg2 a une classe de configuration de requête appelée psycopg2.sql, et utilisons-la.
Passer une chaîne à psycopg2.sql.SQL () retournera un objet qui ressemble à une chaîne Python ordinaire.
Cet objet est une bonne chose que vous pouvez insérer une chaîne avec format () dans la partie incluse dans {}, et il agit comme un espace réservé (c'est-à-dire qu'il n'est pas envoyé à la base de données après avoir été assemblé).
Utilisez psycopg2.sql.Literal () pour insérer un littéral.
unload_test.py
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)
La chaîne de caractères insérée par psycopg2.sql.Literal () est automatiquement placée entre guillemets simples, mais lorsqu'elle est utilisée avec la commande UNLOAD, une erreur de syntaxe se produit.
psycopg2.errors.SyntaxError: syntax error at or near "taro"
LINE 1: ...OAD ('select * from address where name = 'taro'') TO 's...
La première idée pour éviter cela est d'échapper aux guillemets simples insérés automatiquement, mais cela ne fonctionne pas.
Par exemple, si vous essayez d'utiliser une barre oblique inverse
unload_test.py
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 () échappe automatiquement la barre oblique inverse, et par conséquent les deux sont insérés et cela ne fonctionne pas.
De plus, si vous mettez un autre guillemet simple dans sql.Literal (),
unload_test.py
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...
Puisque sql.Literal () échappe le guillemet simple, un autre guillemet simple sera inclus et cela ne fonctionnera pas.
Cela ne fonctionne que si vous ajoutez des guillemets simples avant et après l'espace réservé.
unload_test.py
query_template = sql.SQL("select * from address where name = '{name}'")
query = query_template.format(name=sql.Literal("taro"))
Cependant, lorsque la requête se complique et que le nombre d'espaces réservés augmente, il est difficile de tout mettre entre guillemets simples.
Quand je me promenais dans l'océan sur Internet, il y avait une personne qui a dit quelque chose comme ça.
https://stackoverflow.com/a/55962613
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='
;
Eureka! Apparemment, si vous écrivez comme UNLOAD (\ $ \ $ {query} \ $ \ $), vous pouvez utiliser des guillemets simples sans échapper.
Si vous l'essayez en vous demandant si c'est vrai, cela fonctionnera.
unload_test.py
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)
Dans ce cas, vous n'avez même pas besoin de mettre la requête entre guillemets simples dans UNLOAD ().
À propos, le renfermer entraînera une erreur de syntaxe.
unload_test.py
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...
Lors du DÉCHARGEMENT du résultat de la requête depuis Redshift, il est très pratique d'écrire UNLOAD (\ $ \ $ {query} \ $ \ $) car il n'est pas nécessaire d'échapper au guillemet simple dans la requête.
Concernant cette notation utilisant $$, le répondant du débordement de pile mentionné plus haut a dit "style postgres".
Donc, si vous allez dans la documentation officielle de PostgreSQL au lieu de Redshift, vous trouverez la description suivante.
https://www.postgresql.jp/document/8.0/html/sql-syntax.html
4.1.2.2. Constante chaîne avec signe dollar
Dans la plupart des cas, la syntaxe pour spécifier les constantes de chaîne en SQL est pratique, mais je comprends que s'il y a beaucoup de guillemets simples ou de barres obliques inverses dans la chaîne cible, ils doivent tous être dupliqués. Ça va être difficile. Pour rendre la requête plus lisible même dans ces situations, PostgreSQL fournit un autre moyen de spécifier une constante de chaîne appelée "guillemet dollar". Les constantes de chaîne entre guillemets en dollars sont un signe dollar ($), une "balise" facultative de zéro caractère ou plus, un signe dollar, toute séquence de caractères constituant une constante de chaîne, un signe dollar, ceci Il se compose de la même étiquette et du même signe dollar que ceux spécifiés au début du devis. Par exemple, il existe deux façons de spécifier la chaîne "Dianne's horse" à l'aide de citations en dollars:
$$Dianne's horse$$
$SomeTag$Dianne's horse$SomeTag$
Les chaînes entre guillemets en dollars ont déjà été échappées, donc toutes les constantes de chaîne peuvent être écrites telles quelles. Ni la barre oblique inverse ni le signe dollar ne sont spéciaux, sauf si la séquence correspond à la balise de début.
La notation utilisant $$ semble être "cotation en dollars".
Il est également clair que ce $$ remplace les guillemets, ce qui élimine le besoin de mettre la requête entre guillemets simples avec la commande UNLOAD.
Puisque Redshift est basé sur PostgreSQL, vous pouvez également utiliser ces syntaxes.
Recommended Posts