Verwenden von parametrisierten Abfragen

Sie können parametrisierte Athena-Abfragen verwenden, um dieselbe Abfrage mit unterschiedlichen Parameterwerten zur Ausführungszeit erneut auszuführen und SQL-Injection-Angriffe zu verhindern. In Athena können parametrisierte Abfragen in beliebigen DML-Abfragen oder vorbereiteten SQL-Anweisungen die Form von Ausführungsparametern annehmen.

• Abfragen mit Ausführungsparametern können in einem einzigen Schritt durchgeführt werden und sind nicht arbeitsgruppenspezifisch. Sie platzieren Fragezeichen in jeder DML-Abfrage für die Werte, die Sie parametrisieren möchten. Wenn Sie die Abfrage ausführen, deklarieren Sie die Werte der Ausführungsparameter sequenziell. Die Deklaration von Parametern und die Zuweisung von Werten für die Parameter können in derselben Abfrage erfolgen, jedoch entkoppelt. Im Gegensatz zu vorbereiteten Anweisungen können Sie die Arbeitsgruppe auswählen, wenn Sie eine Abfrage mit Ausführungsparametern senden.

• Vorbereitete Anweisungen erfordern zwei separate SQL-Anweisungen: PREPARE und EXECUTE. Zunächst definieren Sie die Parameter in der PREPARE-Anweisung. Dann führen Sie eine EXECUTE-Anweisung aus, die Werte für die von Ihnen definierten Parameter bereitstellt. Vorbereitete Anweisungen sind arbeitsgruppenspezifisch. Sie können sie nicht außerhalb des Kontextes der Arbeitsgruppe, zu der sie gehören, ausführen.

Überlegungen und Einschränkungen

• Parametrisierte Abfragen werden in Athena-Engine-Version 2 und höheren Versionen unterstützt. Weitere Informationen über Athena-Engine-Versionen finden Sie unter Athena-Engine-Versionierung.

• Derzeit werden parametrisierte Abfragen nur für SELECT-, INSERT INTO-, CTAS- und UNLOAD-Anweisungen.

• In parametrisierten Abfragen sind Parameter positionell und werden mit ? angegeben. Parametern werden Werte nach ihrer Reihenfolge in der Abfrage zugewiesen. Benannte Parameter werden nicht unterstützt.

• Derzeit können ?-Parameter nur in der WHERE-Klausel platziert werden. Syntax wie SELECT ? FROM table wird nicht unterstützt.

• Fragezeichen-Parameter können nicht in doppelte oder einfache Anführungszeichen gesetzt werden (d. h. '?' und "?" sind keine gültige Syntax).

• Damit SQL-Ausführungsparameter als Zeichenfolgen behandelt werden, müssen sie in einfache Anführungszeichen und nicht in doppelte Anführungszeichen eingeschlossen werden.

• Bei Bedarf können Sie die CAST-Funktion verwenden, wenn Sie einen Wert für einen parametrisierten Begriff eingeben. Wenn Sie beispielsweise über eine Spalte des date-Typs verfügen, den Sie in einer Abfrage parametrisiert haben, und Sie das Datum 2014-07-05 abfragen möchten, wird CAST('2014-07-05' AS DATE) bei Eingabe des Parameterwerts das Ergebnis zurückgegeben.

• Vorbereitete Anweisungen sind arbeitsgruppenspezifisch und die Namen vorbereiteter Anweisungen müssen innerhalb der Arbeitsgruppe eindeutig sein.

• IAM-Berechtigungen für vorbereitete Anweisungen sind erforderlich. Weitere Informationen finden Sie unter Zugriff auf vorbereitete Anweisungen zulassen.

• Abfragen mit Ausführungsparametern in der Athena-Konsole sind auf maximal 25 Fragezeichen beschränkt.

Abfragen mithilfe von Ausführungsparametern

Sie können Fragezeichen-Platzhalter in jeder DML-Abfrage verwenden, um eine parametrisierte Abfrage zu erstellen, ohne zuerst eine vorbereitete Anweisung zu erstellen. Um diese Abfragen auszuführen, können Sie die Athena-Konsole oder das AWS CLI oder das AWS-SDK verwenden und die Variablen im execution-parameters Argument deklarieren.

Ausführen von Abfragen mit Ausführungsparametern in der Athena-Konsole

Wenn Sie eine parametrisierte Abfrage ausführen, die Ausführungsparameter (Fragezeichen) in der Athena-Konsole enthält, werden Sie aufgefordert, die Werte in der Reihenfolge einzugeben, in der die Fragezeichen in der Abfrage auftreten.

So führen Sie eine Abfrage mit Ausführungsparametern aus

1. Geben Sie im Athena-Editor eine Abfrage mit Fragezeichen-Platzhaltern ein, wie im folgenden Beispiel gezeigt.

   SELECT * FROM "my_database"."my_table"
   WHERE year = ? and month= ? and day= ?

2. Wählen Sie Ausführen aus.

3. In dem Dialogfeld Enter parameters (Parameter eingeben) geben Sie für jedes der Fragezeichen in der Abfrage einen Wert in der richtigen Reihenfolge ein.

   

4. Wenn Sie alle Parameter eingegeben haben, wählen Sie Run (Ausführen) aus. Der Editor zeigt die Abfrageergebnisse für die von Ihnen eingegebenen Parameterwerte an.

Jetzt können Sie einen der folgenden Schritte ausführen:

• Geben Sie verschiedene Parameterwerte für dieselbe Abfrage ein und wählen Sie anschließend Run again (Erneut ausführen) aus.

• Um alle Werte, die Sie eingegeben haben, gleichzeitig zu löschen, wählen Sie Clear (Löschen) aus.

• Um die Abfrage direkt zu bearbeiten (z. B. um Fragezeichen hinzuzufügen oder zu entfernen), schließen Sie zunächst das Dialogfeld Enter parameters (Parameter eingeben).

• Um die parametrisierte Abfrage für eine spätere Verwendung zu speichern, wählen Sie Save (Speichern) oder Save as (Speichern als) aus und geben Sie der Abfrage dann einen Namen. Weitere Informationen zur Verwendung von gespeicherten Abfragen finden Sie unter Verwenden von gespeicherten Abfragen.

Als Annehmlichkeit merkt sich das Dialogfeld Enter parameters (Parameter eingeben) die Werte, die Sie zuvor für die Abfrage eingegeben haben, solange Sie dieselbe Registerkarte im Abfrage-Editor verwenden.

Ausführen von Abfragen mit Ausführungsparametern mithilfe von AWS CLI

Um Abfragen mit Ausführungsparametern auszuführen, verwenden Sie den start-query-execution Befehl und geben Sie im Argument eine parametrisierte Abfrage anquery-string. AWS CLI Geben Sie daraufhin im execution-parameters-Argument die Werte für die Ausführungsparameter an. Das folgende Beispiel illustriert diese Technik.

aws athena start-query-execution 
--query-string "SELECT * FROM table WHERE x = ? AND y = ?"
--query-execution-context "Database"="default" 
--result-configuration "OutputLocation"="s3://DOC-EXAMPLE-BUCKET;/..."
--execution-parameters "1" "2"

Abfragen mit vorbereiteten Anweisungen

Sie können eine vorbereitete Anweisung für die wiederholte Ausführung derselben Abfrage mit unterschiedlichen Abfrageparametern verwenden. Eine vorbereitete Anweisung enthält Parameterplatzhalter, deren Werte zur Ausführungszeit angegeben werden.

Anmerkung

Die maximale Anzahl vorbereiteter Anweisungen in einer Arbeitsgruppe beträgt 1 000.

SQL-Anweisungen

Sie können die SQL-Anweisungen PREPARE, EXECUTE und DEALLOCATE PREPARE verwenden, um parametrisierte Abfragen im Abfrage-Editor der Athena-Konsole auszuführen.

• Um Parameter anzugeben, bei denen Sie normalerweise Literalwerte verwenden würden, verwenden Sie Fragezeichen in der PREPARE-Anweisung.

• Um die Parameter beim Ausführen der Abfrage durch Werte zu ersetzen, verwenden Sie die USING-Klausel in der EXECUTE-Anweisung.

• Um eine vorbereitete Anweisung aus den vorbereiteten Anweisungen in einer Arbeitsgruppe zu entfernen, verwenden Sie die DEALLOCATE PREPARE-Anweisung.

Die folgenden Abschnitte enthalten zusätzliche Details zu jeder dieser Aussagen.

PREPARE

Bereitet eine Anweisung vor, die zu einem späteren Zeitpunkt ausgeführt werden soll. Vorbereitete Anweisungen werden in der aktuellen Arbeitsgruppe mit dem von Ihnen angegebenen Namen gespeichert. Die Anweisung kann Parameter anstelle von Literalen enthalten, die beim Ausführen der Abfrage ersetzt werden sollen. Parameter, die durch Werte ersetzt werden sollen, werden durch Fragezeichen gekennzeichnet.

Syntax

PREPARE statement_name FROM statement

In der Tabelle unten werden diese Parameter beschrieben.

Parameter	Beschreibung
statement_name	Der Name der zu erstellenden Anweisung. Der Name muss innerhalb der Arbeitsgruppe eindeutig sein.
statement	SELECT-, CTAS- oder INSERT INTO-Abfrage.

PREPARE-Beispiele

Die folgenden Beispiele zeigen die Verwendung der PREPARE-Anweisung. Fragezeichen kennzeichnen die Werte, die von der EXECUTE-Anweisung beim Ausführen der Abfrage geliefert werden sollen.

PREPARE my_select1 FROM
SELECT * FROM nation

PREPARE my_select2 FROM
SELECT * FROM "my_database"."my_table" WHERE year = ?

PREPARE my_select3 FROM
SELECT order FROM orders WHERE productid = ? and quantity < ?

PREPARE my_insert FROM
INSERT INTO cities_usa (city, state)
SELECT city, state
FROM cities_world
WHERE country = ?

PREPARE my_unload FROM
UNLOAD (SELECT * FROM table1 WHERE productid < ?)
TO 's3://DOC-EXAMPLE-BUCKET/'
WITH (format='PARQUET')

EXECUTE

Führt eine vorbereitete Anweisung aus. Werte für Parameter werden in der USING-Klausel angegeben.

Syntax

EXECUTE statement_name [USING value1 [ ,value2, ... ] ]

statement_name ist der Name der vorbereiteten Anweisung. value1 und value2 sind die Werte, die für die Parameter in der Anweisung anzugeben sind.

EXECUTE-Beispiele

Im folgenden Beispiel wird die vorbereitete Anweisung my_select1 ausgeführt, die keine Parameter enthält.

EXECUTE my_select1

Im folgenden Beispiel wird die vorbereitete Anweisung my_select2 ausgeführt, die einen einzelnen Parameter enthält.

EXECUTE my_select2 USING 2012

Im folgenden Beispiel wird die vorbereitete Anweisung my_select3 ausgeführt, die über zwei Parameter verfügt.

EXECUTE my_select3 USING 346078, 12

Das folgende Beispiel liefert einen Zeichenfolgenwert für einen Parameter in der vorbereiteten Anweisung my_insert.

EXECUTE my_insert USING 'usa'

Das folgende Beispiel liefert einen Zahlenwert für den productid-Parameter in der vorbereiteten Anweisung my_unload.

EXECUTE my_unload USING 12

DEALLOCATE PREPARE

Entfernt die vorbereitete Anweisung mit dem angegebenen Namen aus der Liste der vorbereiteten Anweisungen in der aktuellen Arbeitsgruppe.

Syntax

DEALLOCATE PREPARE statement_name

statement_name ist der Name der vorbereiteten Anweisung, die entfernt werden soll.

Beispiel

Im folgenden Beispiel wird die vorbereitete Anweisung my_select1 aus der aktuellen Arbeitsgruppe entfernt.

DEALLOCATE PREPARE my_select1

Vorbereitete Anweisungen ohne die USING-Klausel in der Athena-Konsole ausführen

Wenn Sie eine vorhandene vorbereitete Anweisung mit der Syntax EXECUTE prepared_statement im Abfrage-Editor ausführen, öffnet Athena das Dialogfeld Enter parameters (Parameter eingeben), sodass Sie die Werte eingeben können, die normalerweise in die USING-Klausel der EXECUTE ... USING-Anweisung eingegeben werden.

So führen Sie eine vorbereitete Anweisung mit dem Dialogfeld Enter parameters (Parameter eingeben) aus

1. Im Abfrage-Editor verwenden Sie anstelle der Syntax EXECUTE prepared_statement USING value1, Value2 ... die Syntax EXECUTE prepared_statement.

2. Wählen Sie Ausführen aus. Das Dialogfeld Enter parameters (Parameter eingeben) wird angezeigt.

   

3. Geben Sie die Werte der Reihe nach in das Dialogfeld Execution parameters (Ausführungsparameter) ein. Da der Originaltext der Abfrage nicht sichtbar ist, müssen Sie sich an die Bedeutung der einzelnen Positionsparameter erinnern oder die vorbereitete Anweisung als Referenz zur Verfügung haben.

4. Wählen Sie Ausführen aus.

Erstellen von vorbereiteten Anweisungen mit dem AWS CLI

Um eine vorbereitete Anweisung AWS CLI zu erstellen, können Sie einen der folgenden athena Befehle verwenden:

• Verwenden Sie den create-prepared-statement-Befehl und geben Sie eine Abfrageanweisung mit Ausführungsparametern an.

• Verwenden Sie den start-query-execution-Befehl und geben Sie eine Abfragezeichenfolge an, die die PREPARE-Syntax verwendet.

Verwenden create-prepared-statement

Definieren Sie in einem create-prepared-statement-Befehl den Abfragetext im query-statement-Argument, wie im folgenden Beispiel.

aws athena create-prepared-statement 
--statement-name PreparedStatement1 
--query-statement "SELECT * FROM table WHERE x = ?" 
--work-group athena-engine-v2

Verwendung start-query-execution und die PREPARE-Syntax

Verwenden Sie den start-query-execution-Befehl. Setzen Sie die PREPARE-Anweisung in das query-string-Argument, wie im folgenden Beispiel gezeigt:

aws athena start-query-execution 
--query-string "PREPARE PreparedStatement1 FROM SELECT * FROM table WHERE x = ?" 
--query-execution-context '{"Database": "default"}' 
--result-configuration '{"OutputLocation": "s3://DOC-EXAMPLE-BUCKET/..."}'

Ausführen von vorbereiteten Anweisungen mit dem AWS CLI

Um eine vorbereitete Anweisung mit dem auszuführen AWS CLI, können Sie Werte für die Parameter angeben, indem Sie eine der folgenden Methoden verwenden:

• Verwenden Sie das execution-parameters-Argument.

• Verwenden Sie die EXECUTE ... USING-SQL-Syntax im query-string-Argument.

Verwenden des Arguments execution-parameters

Bei diesem Ansatz verwenden Sie den start-query-execution-Befehl und geben den Namen einer vorhandenen vorbereiteten Anweisung im query-string-Argument ein. Geben Sie daraufhin im execution-parameters-Argument die Werte für die Ausführungsparameter an. Die folgende Beispielrichtlinie zeigt diese Methode.

aws athena start-query-execution 
--query-string "Execute PreparedStatement1" 
--query-execution-context "Database"="default" 
--result-configuration "OutputLocation"="s3://DOC-EXAMPLE-BUCKET/..."
--execution-parameters "1" "2"

Verwenden von EXECUTE ... VERWENDEN der SQL-Syntax

Um eine vorhandene vorbereitete Anweisung mithilfe der EXECUTE ... USING-Syntax auszuführen, verwenden Sie den start-query-execution-Befehl und platzieren sowohl den Namen der vorbereiteten Anweisung als auch die Parameterwerte in das query-string-Argument, wie im folgenden Beispiel:

aws athena start-query-execution 
--query-string "EXECUTE PreparedStatement1 USING 1"
--query-execution-context '{"Database": "default"}' 
--result-configuration '{"OutputLocation": "s3://DOC-EXAMPLE-BUCKET/..."}'

Vorbereitete Anweisungen auflisten

Um die vorbereiteten Anweisungen für eine bestimmte Arbeitsgruppe aufzulisten, können Sie den list-prepared-statements AWS CLI Befehl Athena oder die ListPreparedStatementsAthena-API-Aktion verwenden. Der Parameter --work-group muss angegeben werden.

aws athena list-prepared-statements --work-group primary

Weitere Ressourcen

Lesen Sie die folgenden verwandten Beiträge im AWS Big Data-Blog.

• Die Wiederverwendbarkeit und Sicherheit mithilfe von in Amazon Athena parametrisierten Abfragen verbessern

• In Amazon Athena parametrisierte Abfragen verwenden, um Daten als Service bereitzustellen