AWS Redshift verfügt über einen UNLOAD-Befehl, der die Ergebnisse einer Abfrage an S3 ausgibt.
Ich füge die Abfrage in einfache Anführungszeichen in Form von UNLOAD ('{query}') ein, was ärgerlich ist, da ich die einfachen Anführungszeichen in der Abfrage umgehen muss.
Wenn Sie beim Schreiben einer Abfrage weiterhin Platzhalter verwenden und einfache Anführungszeichen automatisch eingefügt werden, sind Sie ebenfalls ratlos.
Als Ergebnis verschiedener Studien zur Bewältigung einer solchen Situation habe ich eine Methode gefunden, die verwendet werden kann, ohne dem einfachen Anführungszeichen zu entgehen, und werde sie daher vorstellen.
Die Methode besteht darin, die Abfrage in "$$" einzuschließen.
Wir haben eine Big-Data-Datenbank, die auf AWS Redshift basiert, und Daten werden häufig daraus extrahiert.
Da die zu extrahierenden Daten groß sind, können sie nicht wie auf EC2 geschrieben werden, und das Muster besteht normalerweise darin, sie in S3 zu schreiben und sie dann zur Analyse lokal zu verschieben.
Verwenden Sie also den UNLOAD-Befehl von 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 [ ... ] ]
Nun, ich schreibe eine Abfrage in diesen Teil "('select-statement')", aber in das Dokument
Die Abfrage muss wie folgt in Anführungszeichen gesetzt werden:
('select * from venue order by venueid')
Es gibt.
des Weiteren
Wenn die Abfrage Anführungszeichen enthält (z. B. um einen Literalwert einzuschließen), platzieren Sie das Literal zwischen zwei Paaren einfacher Anführungszeichen. Sie müssen die Abfrage auch in einfache Anführungszeichen setzen.
('select * from venue where venuestate=''NV''')
Und das.
Auch in hier
Wenn die Abfrage ein Anführungszeichen enthält (z. B. um einen Literalwert einzuschließen), muss es im Abfragetext maskiert werden (\ ').
Es scheint, dass einfache Anführungszeichen durch Überlappung oder "\" in der Abfrage maskiert werden müssen.
Befolgen Sie zunächst die Dokumentation und ziehen Sie in Betracht, dem einfachen Anführungszeichen zu entkommen.
Lassen Sie uns als Beispiel eine Abfrage von Python mit dem Paket psycopg2 senden.
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''")
(Oder)query = unload_template.format("select * from address where name = \\'taro\\'")
cur = conn.cursor()
cur.execute(query)
(In Pythons Art ist die Zeichenfolge in einfache Anführungszeichen eingeschlossen, aber es ist schwierig, weiter zu entkommen, so dass es sich um doppelte Anführungszeichen handelt.)
Wenn Sie durch Überlappen von einfachen Anführungszeichen entkommen möchten, müssen Sie zwei entkommen, und wenn Sie mit einem Schrägstrich entkommen, müssen Sie einem Schrägstrich entkommen, sodass Sie zwei Schrägstriche + einfache Anführungszeichen schreiben müssen.
Eine einfache Abfrage wie diese ist immer noch in Ordnung, aber wenn die Bedingungen kompliziert werden und Sie eine große Anzahl von Literalen einbetten müssen, wird es schwierig, sich an das Entkommen zu erinnern, und vor allem ist es problematisch.
Es ist noch komplizierter, wenn Sie über die Verwendung von Platzhaltern nachdenken.
psycopg2 hat eine Abfragekonfigurationsklasse namens psycopg2.sql, und wir verwenden sie.
Wenn Sie eine Zeichenfolge an psycopg2.sql.SQL () übergeben, wird ein Objekt zurückgegeben, das wie eine normale Python-Zeichenfolge aussieht.
Dieses Objekt ist eine großartige Sache, da Sie eine Zeichenfolge mit format () in den in {} eingeschlossenen Teil einfügen können und es als Platzhalter fungiert (dh es wird zusammengestellt und nicht an die Datenbank gesendet).
Verwenden Sie psycopg2.sql.Literal (), um ein Literal einzufügen.
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)
Die von psycopg2.sql.Literal () eingefügte Zeichenfolge wird automatisch in einfache Anführungszeichen gesetzt. Bei Verwendung mit dem Befehl UNLOAD tritt jedoch ein Syntaxfehler auf.
psycopg2.errors.SyntaxError: syntax error at or near "taro"
LINE 1: ...OAD ('select * from address where name = 'taro'') TO 's...
Die erste Idee, dies zu vermeiden, besteht darin, die automatisch eingefügten einfachen Anführungszeichen zu umgehen, aber das funktioniert nicht.
Zum Beispiel, wenn Sie versuchen, einen Schrägstrich zu verwenden
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 () entgeht automatisch dem Backslash, und als Ergebnis werden beide eingefügt und es funktioniert nicht.
Wenn Sie ein weiteres einfaches Anführungszeichen in sql.Literal () einfügen,
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...
Da sql.Literal () dem einfachen Anführungszeichen entgeht, wird ein weiteres einfaches Anführungszeichen eingefügt und funktioniert nicht.
Dies funktioniert nur, wenn Sie vor und nach dem Platzhalter einfache Anführungszeichen hinzufügen.
unload_test.py
query_template = sql.SQL("select * from address where name = '{name}'")
query = query_template.format(name=sql.Literal("taro"))
Wenn die Abfrage jedoch kompliziert wird und die Anzahl der Platzhalter zunimmt, ist es schwierig, alles in einfache Anführungszeichen zu setzen.
Als ich im Internet um den Ozean wanderte, gab es eine Person, die so etwas sagte.
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! Wenn Sie wie UNLOAD (\ $ \ $ {query} \ $ \ $) schreiben, können Sie anscheinend ein einfaches Anführungszeichen verwenden, ohne zu entkommen.
Wenn Sie es versuchen, während Sie sich fragen, ob es wahr ist, wird es funktionieren.
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)
In diesem Fall müssen Sie die Abfrage in UNLOAD () nicht einmal in einfache Anführungszeichen setzen.
Das Einschließen führt übrigens zu einem Syntaxfehler.
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...
Wenn Sie das Abfrageergebnis von Redshift entladen, ist es sehr praktisch, UNLOAD (\ $ \ $ {query} \ $ \ $) zu schreiben, da das einfache Anführungszeichen in der Abfrage nicht maskiert werden muss.
In Bezug auf diese Notation unter Verwendung von $$ sagte der zuvor erwähnte Befragte des Stapelüberlaufs "Postgres-Stil".
Wenn Sie also anstelle von Redshift zur offiziellen PostgreSQL-Dokumentation gehen, finden Sie die folgende Beschreibung.
https://www.postgresql.jp/document/8.0/html/sql-syntax.html
4.1.2.2. String-Konstante mit Dollarzeichen
In den meisten Fällen ist die Syntax zum Angeben von Zeichenfolgenkonstanten in SQL praktisch, aber ich verstehe, dass alle einfachen Anführungszeichen oder Backslashes in der Zielzeichenfolge dupliziert werden müssen. Es wird schwer. Um die Abfrage auch in diesen Situationen besser lesbar zu machen, bietet PostgreSQL eine weitere Möglichkeit, eine Zeichenfolgenkonstante namens "Dollar Quoting" anzugeben. In Dollar zitierte Zeichenfolgenkonstanten sind ein Dollarzeichen ($), ein optionales "Tag" mit null oder mehr Zeichen, ein Dollarzeichen, eine beliebige Folge von Zeichen, aus denen eine Zeichenfolgenkonstante besteht, ein Dollarzeichen Es besteht aus demselben Tag und demselben Dollarzeichen, die zu Beginn des Angebots angegeben wurden. Beispielsweise gibt es zwei Möglichkeiten, die Zeichenfolge "Diannes Pferd" mithilfe von Dollar-Anführungszeichen anzugeben:
$$Dianne's horse$$
$SomeTag$Dianne's horse$SomeTag$
Die in Dollar notierten Zeichenfolgen wurden bereits maskiert, sodass alle Zeichenfolgenkonstanten unverändert geschrieben werden können. Weder der Backslash noch das Dollarzeichen sind etwas Besonderes, es sei denn, die Sequenz stimmt mit dem Start-Tag überein.
Die Notation mit $$ scheint "Dollar Quoting" zu sein.
Es ist auch klar, dass dieses $$ die Anführungszeichen ersetzt, wodurch die Notwendigkeit entfällt, die Abfrage mit dem Befehl UNLOAD in einfache Anführungszeichen zu setzen.
Da Redshift auf PostgreSQL basiert, können Sie diese Syntax auch verwenden.
Recommended Posts