SELECT - Amazon Athena
SinopsisParámetrosObtención de las ubicaciones de archivos para los datos de origen en Simple Storage Service (Amazon S3) Incluir las comillas simples en caracteres de escapeRecursos adicionales de

SELECT

Recupera filas de datos de cero o más tablas.

nota

En este tema se proporciona un resumen de información de referencia. La información completa sobre el uso de SELECT y el lenguaje SQL está fuera del alcance de esta documentación. Para obtener información sobre el uso de SQL específico de Athena, consulte Consideraciones y limitaciones de las consultas SQL en Amazon Athena y Ejecución de consultas SQL en Amazon Athena. Para ver un ejemplo de cómo crear una base de datos, crear una tabla y ejecutar una consulta SELECT en una tabla en Athena, consulte Introducción.

Sinopsis

[ WITH with_query [, ...] ]
SELECT [ ALL | DISTINCT ] select_expression [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY [ ALL | DISTINCT ] grouping_element [, ...] ]
[ HAVING condition ]
[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
[ ORDER BY expression [ ASC | DESC ] [ NULLS FIRST | NULLS LAST] [, ...] ]
[ OFFSET count [ ROW | ROWS ] ]
[ LIMIT [ count | ALL ] ]
nota

Las palabras reservadas en las instrucciones SELECT de SQL deben ponerse entre comillas dobles. Para obtener más información, consulte Palabras reservadas para incluir en las instrucciones SQL SELECT.

Parámetros

[ WITH with_query [, ....] ]

Puede utilizar WITH para aplanar consultas anidadas o para simplificar subconsultas.

Se admite el uso de la cláusula WITH para crear consultas recursivas con la versión 3 del motor de Athena. La profundidad máxima de recursión es 10.

La cláusula WITH precede a la lista SELECT de una consulta y define una o varias subconsultas para utilizarlas en la consulta SELECT.

Cada subconsulta define una tabla temporal, similar a la definición de una vista, a la que puede hacer referencia en la cláusula FROM. Las tablas se utilizan solo cuando se ejecuta la consulta.

La sintaxis de with_query es:

subquery_table_name [ ( column_name [, ...] ) ] AS (subquery)

Donde:

  • subquery_table_name es un nombre único para una tabla temporal que define los resultados de la subconsulta de cláusula WITH. Cada subquery debe tener un nombre de tabla al que se pueda hacer referencia en la cláusula FROM.

  • column_name [, ...] es una lista opcional de los nombres de columna de salida. La cantidad de nombres de columnas debe ser igual o menor que la cantidad de columnas definidas por la subquery.

  • subquery es cualquier declaración de consulta.

[ ALL | DISTINCT ] select_expression

select_expression determina las filas que deben seleccionarse. Con la opción select_expression se puede usar uno de los siguientes formatos:

expression [ [ AS ] column_alias ] [, ...]
row_expression.* [ AS ( column_alias [, ...] ) ]
relation.*
*

  • La sintaxis expression [ [ AS ] column_alias ] especifica una columna de salida. La sintaxis opcional [AS] column_alias especifica un nombre de encabezado personalizado que se utilizará en la columna de la salida.

  • Para row_expression.* [ AS ( column_alias [, ...] ) ], row_expression es una expresión arbitraria de tipo ROW. Los campos de la fila definen las columnas de salida que se incluirán en el resultado.

  • Para relation.*, las columnas de relation se incluyen en el resultado. Esta sintaxis no permite el uso de alias de columna.

  • El asterisco * especifica que todas las columnas deben incluirse en el conjunto de resultados.

  • En el conjunto de resultados, el orden de las columnas es el mismo que el orden de su especificación según la expresión seleccionada. Si una expresión seleccionada devuelve varias columnas, el orden de las columnas sigue el orden utilizado en la relación de origen o la expresión de tipo fila.

  • Cuando se especifican alias de columna, estos sustituyen a los nombres de columna o campos de fila preexistentes. Si la expresión seleccionada no tiene nombres de columna, en la salida se muestran los nombres de columna anónimos indexados a cero (_col0, _col1 y _col2, ...).

  • ALL es el valor predeterminado. El uso de ALL se trata de la misma manera que si se hubiera omitido; todas las filas de todas las columnas se seleccionan y se conservan los duplicados.

  • Utilice DISTINCT para devolver solo valores únicos cuando una columna contiene valores duplicados.

FROM from_item [, ...]

Indica la entrada de datos de la consulta, donde from_item puede ser una vista, una construcción JOIN o una subconsulta, tal y como se describe a continuación.

from_item puede ser:

  • table_name [ [ AS ] alias [ (column_alias [, ...]) ] ]

    Donde table_name es el nombre de la tabla de destino en la que se seleccionan las filas, alias es el nombre que se debe dar a la salida de la instrucción SELECT y column_alias define las columnas para el alias especificado.

- O BIEN -

  • join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]

    Donde join_type es uno de los valores siguientes:

    • [ INNER ] JOIN

    • LEFT [ OUTER ] JOIN

    • RIGHT [ OUTER ] JOIN

    • FULL [ OUTER ] JOIN

    • CROSS JOIN

    • ON join_condition | USING (join_column [, ...]) Donde con join_condition puede especificar nombres de columnas para claves de combinación en varias tablas y el uso de join_column requiere que join_column exista en ambas tablas.

[ WHERE condition ]

Filtra los resultados de acuerdo con la condition que especifique, donde condition por lo general tiene la siguiente sintaxis.

column_name operator value [[[AND | OR] column_name operator value] ...]

El operador puede ser uno de los comparadores =, >, <, >=, <=, <>, !=.

Las siguientes expresiones de subconsulta también se pueden utilizar en la cláusula WHERE.

  • [NOT] BETWEEN integer_A AND integer_B: especifica un rango entre dos enteros, como en el ejemplo siguiente. Si el tipo de datos de la columna es varchar, primero se debe convertir la columna a números enteros.

    SELECT DISTINCT processid FROM "webdata"."impressions"
WHERE cast(processid as int) BETWEEN 1500 and 1800
ORDER BY processid

  • [NOT] LIKE value: busca el patrón especificado. Utilice el signo de porcentaje (%) como carácter comodín, como en el ejemplo siguiente.

    SELECT * FROM "webdata"."impressions"
WHERE referrer LIKE '%.org'

  • [NOT] IN (value[, value[, ...]): especifica una lista de valores posibles para una columna, como en el ejemplo siguiente.

    SELECT * FROM "webdata"."impressions"
WHERE referrer IN ('example.com','example.net','example.org')
[ GROUP BY [ ALL | DISTINCT ] grouping_expressions [, ...] ]

Divide la salida de la instrucción SELECT en filas con valores coincidentes.

ALL y DISTINCT determinan si los conjuntos de agrupación duplicados producen cada uno filas de salida diferenciadas. Si se omite, el sistema presupone que se utiliza ALL.

grouping_expressions le permite realizar operaciones de agrupación complejas. Puede utilizar operaciones de agrupación complejas para realizar análisis que requieran la agregación de varios conjuntos de columnas en una sola consulta.

El elemento grouping_expressions puede ser cualquier función, como SUM, AVG o COUNT, realizado en columnas de entrada.

Las expresiones GROUP BY pueden agrupar la salida por nombres de columna de entrada que no se muestran en la salida de la instrucción SELECT.

Todas las expresiones de salida deben ser funciones o columnas agregadas que existan en la cláusula GROUP BY.

Puede utilizar una sola consulta para realizar análisis que requieran la agregación de varios conjuntos de columnas.

Athena admite agregaciones complejas usando GROUPING SETS, CUBE y ROLLUP. GROUP BY GROUPING SETS especifica varias listas de columnas en las que se agruparán. GROUP BY CUBE genera todos los conjuntos de agrupación posibles para un conjunto determinado de columnas. GROUP BY ROLLUP genera todos los subtotales posibles para un conjunto determinado de columnas. Las operaciones de agrupación complejas no admiten agrupación en expresiones compuestas por columnas de entrada. Solo se admiten nombres de columnas.

A menudo puede utilizar UNION ALL para obtener los mismos resultados que estas operaciones GROUP BY, pero las consultas que utilizan GROUP BY tienen la ventaja de leer los datos una vez, mientras que UNION ALL lee los datos subyacentes tres veces y puede producir resultados incoherentes cuando el origen de los datos está sujeto a cambios.

[ HAVING condition ]

Se usa con funciones agregadas y la cláusula GROUP BY. Controla qué grupos se seleccionan, con lo que se eliminan los grupos que no cumplen el valor de condition. Este filtro se aplica después de calcular los grupos y los agregados.

[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] union_query] ]

UNION, INTERSECT y EXCEPT combinan los resultados de más de una instrucción SELECT en una sola consulta. ALL o DISTINCT determinan el carácter único de las filas que se incluyen en el conjunto de resultados final.

UNION combina las filas resultantes de la primera consulta con las filas resultantes de la segunda consulta. Para eliminar duplicados, UNION construye una tabla hash, que consume memoria. Para un mejor rendimiento, considere utilizar UNION ALL si la consulta no requiere la eliminación de duplicados. Si hay varias cláusulas UNION, dichas cláusulas se procesan de izquierda a derecha a menos que utilice paréntesis para definir explícitamente el orden de procesamiento.

INTERSECT devuelve solo las filas que están presentes en los resultados de la primera y la segunda consulta.

EXCEPT devuelve las filas de los resultados de la primera consulta, y excluye las filas encontradas por la segunda consulta.

ALL hace que se incluyan todas las filas, incluso si dichas filas son idénticas.

DISTINCT hace que solo se incluyan filas únicas en el conjunto de resultados combinados.

[ ORDER BY expression [ ASC | DESC ] [ NULLS FIRST | NULLS LAST] [, ...] ]

Ordena un conjunto de resultados por una o varias salidas expression.

Cuando la cláusula contiene varias expresiones, el conjunto de resultados se ordena de acuerdo con la primera expression. A continuación, se aplica la segunda expression a las filas que tienen valores coincidentes de la primera expresión y así sucesivamente.

Cada expression puede especificar columnas de salida de SELECT o un número ordinal para una columna de salida por posición, a partir de uno.

ORDER BY se evalúa como el último paso después de cualquier cláusula GROUP BY o HAVING. ASC y DESC determinan si los resultados se ordenan de forma ascendente o descendente. El orden es ascendente de manera predeterminada (ASC). El orden de los valores nulos predeterminado es NULLS LAST, sea cual sea el orden de clasificación, ascendente o descendente.

[ OFFSET count [ ROW | ROWS ] ]

Utilice la cláusula OFFSET para descartar varias filas iniciales del conjunto de resultados. Si la cláusula ORDER BY está presente, la cláusula OFFSET se evalúa sobre un conjunto de resultados ordenado. El conjunto permanece ordenado después de descartar las filas omitidas. Si la consulta no tiene la cláusula ORDER BY, es arbitrario qué filas se descartan. Si el recuento especificado por OFFSET es igual o superior al tamaño del conjunto de resultados, el resultado final estará vacío.

LIMIT [ count | ALL ]

Restringe el número de filas del conjunto de resultados a count. LIMIT ALL equivale a omitir la cláusula LIMIT. Si la consulta no contiene ninguna cláusula ORDER BY, los resultados son arbitrarios.

TABLESAMPLE [ BERNOULLI | SYSTEM ] (porcentaje)

Operador opcional para seleccionar filas de una tabla basado en un método de muestreo.

BERNOULLI selecciona cada fila que se muestra en la tabla con una probabilidad de percentage. Todos los bloques físicos de la tabla se analizan y determinadas filas se omiten basándose en una comparación entre el valor de percentage de muestra y un valor aleatorio calculado en el tiempo de ejecución.

Con SYSTEM, la tabla se divide en segmentos lógicos de datos y la tabla se muestrea con esta granularidad.

O bien se seleccionan todas las filas de un segmento en particular, o el segmento se omite basándose en la comparación del valor de percentage de la muestra con un valor aleatorio calculado en tiempo de ejecución. El muestreo de SYSTEM depende del conector. Este método no garantiza que las probabilidades de muestreo sean independientes.

[ UNNEST (array_or_map) [WITH ORDINALITY] ]

Amplía una matriz o un mapa a una relación. Las matrices se amplían a una sola columna. Los mapas se amplían a dos columnas (clave, valor).

Puede utilizar UNNEST con varios argumentos, que se amplían en varias columnas con tantas filas como el mayor argumento de cardinalidad.

Las demás columnas se rellenan con valores nulos.

La cláusula WITH ORDINALITY añade una columna de ordinalidad al final.

UNNEST suele utilizarse con JOIN y puede hacer referencia a columnas de relaciones del lado izquierdo de JOIN.

Obtención de las ubicaciones de archivos para los datos de origen en Simple Storage Service (Amazon S3)

Para ver la ubicación del archivo de Amazon S3 para los datos de una fila de tabla, puede utilizar "$path" en una consulta SELECT, como en el ejemplo siguiente:

SELECT "$path" FROM "my_database"."my_table" WHERE year=2019;

Esta consulta devuelve un resultado similar al siguiente:

s3://amzn-s3-demo-bucket/datasets_mytable/year=2019/data_file1.json

Para devolver una lista ordenada y única de las rutas de nombre de archivo S3 para los datos de una tabla, puede utilizar SELECT DISTINCT y ORDER BY, como en el ejemplo a continuación.

SELECT DISTINCT "$path" AS data_source_file
FROM sampledb.elb_logs
ORDER By data_source_file ASC

Para devolver solo los nombres de archivo sin la ruta, puede pasar "$path" como parámetro a una función regexp_extract, como en el siguiente ejemplo.

SELECT DISTINCT regexp_extract("$path", '[^/]+$') AS data_source_file
FROM sampledb.elb_logs
ORDER By data_source_file ASC

Para devolver los datos de un archivo específico, especifique el archivo en la cláusula WHERE, como en el siguiente ejemplo.

SELECT *,"$path" FROM my_database.my_table WHERE "$path" = 's3://amzn-s3-demo-bucket/my_table/my_partition/file-01.csv'

Para obtener más información y ejemplos, consulte el artículo del Centro de conocimientos ¿Cómo puedo ver el archivo de origen de Amazon S3 de una fila en una tabla de Athena?.

nota

En Athena, las columnas de metadatos ocultas de Hive o Iceberg $bucket, $file_modified_time, $file_size y $partition no son compatibles con las vistas.

Incluir las comillas simples en caracteres de escape

Para incluir una comilla simple en caracteres de escape, inserte delante otra comilla simple, como en el ejemplo siguiente. Esto no debe confundirse con las comillas dobles. 

Select 'O''Reilly'
Resultados

O'Reilly

Recursos adicionales de

Para obtener más información acerca del uso de instrucciones SELECT en Athena, consulte los siguientes recursos.

Para obtener información sobre este tema Consulte
Ejecución de consultas en Athena Ejecución de consultas SQL en Amazon Athena
Uso de SELECT para crear una tabla Creación de una tabla a partir de los resultados de una consulta (CTAS)
Insertar datos desde una consulta SELECT en otra tabla INSERT INTO
Uso de funciones integradas en instrucciones SELECT Funciones en Amazon Athena
Uso de funciones definidas por el usuario en instrucciones SELECT Consulta con funciones definidas por el usuario
Consulta de metadatos del catálogo de datos Consulta de AWS Glue Data Catalog