Uso de consultas parametrizadas
Puede usar consultas parametrizadas de Athena para volver a ejecutar la misma consulta con valores de parámetros diferentes en el momento de la ejecución y ayudar a evitar ataques de inyección de código SQL. En Athena, las consultas parametrizadas pueden adoptar la forma de parámetros de ejecución en cualquier consulta DML o instrucciones preparadas de SQL.
-
Las consultas con parámetros de ejecución se pueden realizar en un solo paso y no son específicas del grupo de trabajo. Coloque signos de interrogación en cualquier consulta de DML para los valores que desea parametrizar. Al ejecutar la consulta, declara los valores de los parámetros de ejecución de manera secuencial. La declaración de parámetros y la asignación de valores para los parámetros se pueden hacer en la misma consulta, pero de manera desacoplada. A diferencia de las instrucciones preparadas, puede seleccionar el grupo de trabajo cuando envía una consulta con parámetros de ejecución.
-
Las instrucciones preparadas requieren dos instrucciones SQL separadas:
PREPARE
yEXECUTE
. Primero, defina los parámetros en la instrucciónPREPARE
. A continuación, ejecute una declaraciónEXECUTE
que proporcione los valores de los parámetros que ha definido. Las instrucciones preparadas son específicas del grupo de trabajo; no puede ejecutarlas fuera del contexto del grupo de trabajo al que pertenecen.
Condiciones y limitaciones
-
Las consultas parametrizadas se admiten únicamente en la versión 2 o una versión posterior del motor Athena. Para obtener más información acerca de las versiones de motor Athena, consulte Control de versiones del motor Athena.
-
Actualmente, las consultas parametrizadas solo se admiten para instrucciones
SELECT
,INSERT INTO
,CTAS
yUNLOAD
. -
En las consultas parametrizadas, los parámetros son posicionales y se indican mediante
?
. A los parámetros se les asignan valores según su orden en la consulta. Los parámetros con nombre no se admiten. -
En la actualidad, los parámetros
?
solo se pueden colocar en la cláusulaWHERE
. La sintaxis del tipoSELECT ? FROM table
no es compatible. -
Los parámetros de interrogación no se pueden poner entre comillas simples o dobles (es decir,
'?'
y"?"
no son sintaxis válidas). -
Las instrucciones preparadas son específicas del grupo de trabajo, y los nombres de las instrucciones preparadas deben ser únicos dentro del grupo de trabajo.
-
Para las instrucciones preparadas se requieren permisos de IAM. Para obtener más información, consulte Permitir acceso a instrucciones preparadas.
-
Las consultas con parámetros de ejecución en la consola de Athena están limitadas a un máximo de 25 signos de interrogación.
Consultar mediante parámetros de ejecución
Puede usar marcadores de posición de signo de interrogación en cualquier consulta DML para crear una consulta parametrizada sin crear primero una instrucción preparada. Para ejecutar estas consultas, puede utilizar la consola de Athena o la AWS CLI o el SDK de AWS y declarar las variables en el argumento execution-parameters
.
Ejecución de consultas con parámetros de ejecución en la consola de Athena
Cuando ejecuta una consulta parametrizada que tiene parámetros de ejecución (signos de interrogación) en la consola de Athena, se le solicitan los valores en el orden en que aparecen los signos de interrogación en la consulta.
Para ejecutar una consulta que tiene parámetros de ejecución
-
Ingrese una consulta con marcadores de posición de signo de interrogación en el editor de Athena, como se muestra en el ejemplo siguiente.
SELECT * FROM "my_database"."my_table" WHERE year = ? and month= ? and day= ?
-
Elija Run (Ejecutar).
-
En el cuadro de diálogo Enter parameters (Ingresar parámetros), ingrese un valor en orden para cada uno de los signos de interrogación de la consulta.
-
Cuando haya terminado de ingresar los parámetros, seleccione Run (Ejecutar). El editor muestra los resultados de la consulta para los valores de parámetros que ha ingresado.
En este caso, puede elegir una de las siguientes opciones:
-
Ingrese valores de parámetros diferentes para la misma consulta y, a continuación, elija Run again (Ejecutar de nuevo).
-
Para borrar todos los valores que ha introducido a la vez, seleccione Clear (Eliminar).
-
Para editar la consulta directamente (por ejemplo, para agregar o eliminar signos de interrogación), cierre el cuadro de diálogo Enter parameters (Ingresar parámetros) primero.
-
Para guardar la consulta parametrizada para su uso posterior, seleccione Save (Guardar) o Save as (Guardar como) y, a continuación, asigne un nombre a la consulta. Para obtener más información acerca del uso de consultas guardadas, consulte Uso de consultas guardadas.
Para su comodidad, el cuadro de diálogo Enter parameters (Ingresar parámetros) recuerda los valores que ha ingresado anteriormente para consultar siempre que utilice la misma pestaña en el editor de consultas.
Ejecución de consultas con parámetros de ejecución con la AWS CLI
Para utilizar la AWS CLI para ejecutar consultas con parámetros de ejecución, utilice el comando start-query-execution
y proporcione una consulta parametrizada en el argumento query-string
. A continuación, en el argumento execution-parameters
, proporcione los valores de los parámetros de ejecución. En el siguiente ejemplo, se ilustra esta técnica.
aws athena start-query-execution --query-string "SELECT * FROM table WHERE x = ? AND y = ?" --query-execution-context "Database"="default" --result-configuration "OutputLocation"="s3://..." --execution-parameters "1" "2"
Consultas con instrucciones preparadas
Puede utilizar una instrucción preparada para la ejecución repetida de la misma consulta con parámetros de consulta diferentes. Una instrucción preparada contiene marcadores de posición de parámetros cuyos valores se proporcionan en el momento de la ejecución.
El número máximo de instrucciones preparadas en un grupo de trabajo es 1000.
Instrucciones SQL
Puede utilizar las instrucciones SQL PREPARE
, EXECUTE
y DEALLOCATE
PREPARE
para ejecutar consultas parametrizadas en el editor de consultas de la consola de Athena.
-
Para especificar parámetros en los que normalmente usaría valores literales, utilice signos de interrogación en la instrucción
PREPARE
. -
Para reemplazar los parámetros con valores al ejecutar la consulta, utilice la cláusula
USING
en la instrucciónEXECUTE
. -
Para eliminar una instrucción preparada de las instrucciones preparadas en un grupo de trabajo, utilice la instrucción
DEALLOCATE PREPARE
.
En las siguientes secciones, se proporciona información adicional acerca de cada una de estas instrucciones.
PREPARE
Prepara una instrucción que se ejecutará en un momento posterior. Las instrucciones preparadas se guardan en el grupo de trabajo actual con el nombre que especifique. La instrucción puede incluir parámetros en lugar de literales que se reemplazan cuando se ejecuta la consulta. Los parámetros que se van a reemplazar por valores se indican con signos de interrogación.
Sintaxis
PREPARE
statement_name
FROMstatement
En la tabla siguiente se describen estos parámetros.
Parámetro | Descripción |
---|---|
statement_name |
Nombre de la instrucción a preparar. El nombre debe ser único dentro del grupo de trabajo. |
statement |
Una consulta SELECT , CTAS o INSERT
INTO . |
Ejemplos de PREPARE
En los siguientes ejemplos se muestra el uso de la instrucción PREPARE
. Los signos de interrogación indican los valores que debe proporcionar la instrucción EXECUTE
cuando se ejecuta la consulta.
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://
my_output_bucket
/' WITH (format='PARQUET')
EXECUTE
Ejecuta una instrucción preparada. Los valores de los parámetros se especifican en la cláusula USING
.
Sintaxis
EXECUTE
statement_name
[USINGvalue1
[ ,value2
, ... ] ]
statement_name
es el nombre de la instrucción preparada. value1
y value2
son los valores que se especificarán para los parámetros de la instrucción.
Ejemplos de EXCECUTE
En el siguiente ejemplo se ejecuta la instrucción preparada my_select1
, que no contiene parámetros.
EXECUTE my_select1
En el siguiente ejemplo se ejecuta la instrucción preparada my_select2
, que contiene un único parámetro.
EXECUTE my_select2 USING 2012
En el siguiente ejemplo se ejecuta la instrucción preparada my_select3
, que contiene dos parámetros.
EXECUTE my_select3 USING 346078, 12
En el siguiente ejemplo se proporciona un valor de cadena para un parámetro de la instrucción preparada my_insert
.
EXECUTE my_insert USING 'usa'
En el siguiente ejemplo se proporciona un valor numérico para el parámetro productid
de la instrucción preparada my_unload
.
EXECUTE my_unload USING 12
DEALLOCATE PREPARE
Elimina la instrucción preparada con el nombre especificado de la lista de instrucciones preparadas del grupo de trabajo actual.
Sintaxis
DEALLOCATE PREPARE
statement_name
statement_name
es el nombre de la instrucción preparada que se debe eliminar.
Ejemplo
En el siguiente ejemplo, se elimina la instrucción preparada my_select1
del grupo de trabajo actual.
DEALLOCATE PREPARE my_select1
Ejecución de declaraciones preparadas sin la cláusula USING en la consola de Athena
Si ejecuta una sentencia preparada existente con la sintaxis EXECUTE
prepared_statement
en el editor de consultas, Athena abre el cuadro de diálogo Enter parameters (Ingresar parámetros) para que pueda introducir los valores que normalmente aparecerían en la cláusula USING
de la instrucción EXECUTE
... USING
.
Para ejecutar una sentencia preparada mediante el cuadro de diálogo Enter parameters (Ingresar parámetros)
-
En el editor de consultas, en lugar de utilizar la sintaxis
EXECUTE prepared_statement USING
Value1
,
Value2
...
, utilice la sintaxisEXECUTE
prepared_statement
. -
Elija Run (Ejecutar). Aparece el cuadro de diálogo Enter parameters (Ingresar parámetros).
-
Ingrese los valores en orden en el cuadro de diálogo Execution parameters (Ingresar parámetros). Como el texto original de la consulta no está visible, debe recordar el significado de cada parámetro posicional o tener la declaración preparada disponible como referencia.
-
Elija Run (Ejecutar).
Creación de instrucciones preparadas mediante AWS CLI
Para utilizar la AWS CLI para crear una instrucción preparada, puede utilizar uno de los siguientes comandos de athena
:
-
Utilice el comando
create-prepared-statement
y proporcione una sentencia de consulta que tenga parámetros de ejecución. -
Utilice
start-query-execution
y proporcione una cadena de consulta que utilice la sintaxisPREPARE
.
Uso de create-prepared-statement
En un comando create-prepared-statement
, defina el texto de la consulta en el argumento query-statement
, como en el siguiente ejemplo.
aws athena create-prepared-statement --statement-name PreparedStatement1 --query-statement "SELECT * FROM table WHERE x = ?" --work-group athena-engine-v2
Uso de start-query-execution y la sintaxis PREPARE
Use el comando start-query-execution
. Coloque la instrucción PREPARE
en el argumento query-string
, como en el siguiente ejemplo:
aws athena start-query-execution --query-string "PREPARE PreparedStatement1 FROM SELECT * FROM table WHERE x = ?" --query-execution-context '{"Database": "default"}' --result-configuration '{"OutputLocation": "s3://..."}'
Ejecución de instrucciones preparadas mediante AWS CLI
Para ejecutar una declaración preparada con la AWS CLI, puede proporcionar valores para los parámetros mediante uno de los métodos siguientes:
-
Utilice el argumento
execution-parameters
. -
Utilice la sintaxis SQL
EXECUTE ... USING
en el argumentoquery-string
.
Uso del argumento execution-parameters
En este enfoque, se utiliza start-query-execution
y debe proporcionarse el nombre de una sentencia preparada existente en el argumento query-string
. A continuación, en el argumento execution-parameters
, proporcione los valores de los parámetros de ejecución. El siguiente ejemplo muestra este método.
aws athena start-query-execution --query-string "Execute PreparedStatement1" --query-execution-context "Database"="default" --result-configuration "OutputLocation"="s3://..." --execution-parameters "1" "2"
Uso de EXECUTE… Uso de sintaxis SQL
Para ejecutar una sentencia preparada existente con la sintaxis EXECUTE ... USING
, se utiliza el comando start-query-execution
y debe colocarse el nombre de la sentencia preparada y los valores de los parámetros en el argumento query-string
, como en el siguiente ejemplo:
aws athena start-query-execution --query-string "EXECUTE PreparedStatement1 USING 1" --query-execution-context '{"Database": "default"}' --result-configuration '{"OutputLocation": "s3://..."}'
Véase también
Consulte las siguientes publicaciones relacionadas en el blog de macrodatos de AWS.