SELECT - Amazon Athena
ResumoParâmetrosObter os locais de arquivos dos dados de origem no Amazon S3 Caractere de escape para aspas simplesRecursos adicionais

SELECT

Recupera linhas de dados de zero ou mais tabelas.

nota

Este tópico fornece informações resumidas para referência. Informações abrangentes sobre o uso de SELECT e a linguagem SQL estão além do escopo desta documentação. Para obter informações sobre como usar o SQL específico do Athena, consulte Considerações e limitações das consultas SQL no Amazon Athena e Executar consultas SQL no Amazon Athena. Para ver um exemplo de como criar um banco de dados, criar uma tabela e executar uma consulta SELECT na tabela do Athena, consulte Conceitos básicos.

Resumo

[ 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

Palavras reservadas em instruções SQL SELECT devem ficar entre aspas duplas. Para ter mais informações, consulte Palavras-chave reservadas para escape e em instruções de SQL SELECT.

Parâmetros

[ WITH with_query [, ....] ]

Você pode usar WITH para nivelar consultas aninhadas ou para simplificar subconsultas.

O uso da cláusula WITH para criar consultas recursivas é possível a partir da versão 3 do mecanismo Athena. A profundidade de recursão máxima é 10.

A cláusula WITH precede a lista SELECT em uma consulta e define uma ou mais subconsultas a serem usadas dentro da consulta SELECT.

Cada subconsulta define uma tabela temporária, semelhante a uma definição de exibição, que você pode referenciar na cláusula FROM. As tabelas são usadas apenas quando a consulta é executada.

A sintaxe with_query é:

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

Em que:

  • subquery_table_name é um nome exclusivo para uma tabela temporária que define os resultados da subconsulta de cláusula WITH. Cada subquery deve ter um nome de tabela que possa ser referenciado na cláusula FROM.

  • column_name [, ...] é uma lista opcional de nomes de coluna de saída. O número de nomes de coluna deve ser igual a ou menor que o número de colunas definido por subquery.

  • subquery é uma instrução da consulta qualquer.

[ ALL | DISTINCT ] select_expression

select_expression determina as linhas a serem selecionadas. Uma select_expression pode usar um dos seguintes formatos:

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

  • A sintaxe expression [ [ AS ] column_alias ] especifica uma coluna de saída. A sintaxe opcional [AS] column_alias especifica um nome de título personalizado a ser usado para a coluna na saída.

  • Para row_expression.* [ AS ( column_alias [, ...] ) ], row_expression é uma expressão arbitrária do tipo de dados ROW. Os campos da linha definem as colunas de saída a serem incluídas no resultado.

  • Para relation.*, as colunas de relation são incluídas no resultado. Essa sintaxe não permite o uso de aliases de coluna.

  • O asterisco * especifica que todas as colunas sejam incluídas no conjunto de resultados.

  • No conjunto de resultados, a ordem das colunas é igual à ordem de sua especificação pela expressão de seleção. Se uma expressão de seleção retornar várias colunas, a ordem das colunas segue a ordem usada na relação de origem ou na expressão do tipo de linha.

  • Quando os aliases de coluna são especificados, os aliases substituem os nomes de campos de coluna ou linha pré-existentes. Se a expressão de seleção não tiver nomes de coluna, nomes de colunas anônimas com índice zero (_col0,_col1 e _col2, ...) serão exibidos na saída.

  • ALL é o padrão. Usar ALL será tratado da mesma maneira como se tivesse sido omitido. Todas as linhas de todas as colunas são selecionadas, e as duplicações são mantidas.

  • Use DISTINCT para retornar somente valores distintos quando uma coluna contém valores duplicados.

FROM from_item [, ...]

Indica a entrada para a consulta, em que from_item pode ser uma exibição, um construto de união ou uma subconsulta conforme descrito abaixo.

O from_item pode ser:

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

    Em que table_name é o nome da tabela de destino da qual selecionar linhas, alias é o nome para indicar a saída da instrução SELECT e column_alias define as colunas para o alias especificado.

-OU-

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

    Em que join_type é um dos:

    • [ INNER ] JOIN

    • LEFT [ OUTER ] JOIN

    • RIGHT [ OUTER ] JOIN

    • FULL [ OUTER ] JOIN

    • CROSS JOIN

    • ON join_condition | USING (join_column [, ...]) Em que usar join_condition permite especificar nomes de coluna para chaves de união em várias tabelas e usar join_column exige que join_column exista em ambas as tabelas.

[ WHERE condição ]

Filtra os resultados de acordo com a condition que você especificar, em que condition costuma ter a sintaxe abaixo.

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

O operador pode ser um dos comparadores =, >, <, >=, <=, <>, !=.

As expressões de subconsulta a seguir também podem ser usadas na cláusula WHERE.

  • [NOT] BETWEEN integer_A AND integer_B: especifica um intervalo entre dois inteiros, como no exemplo a seguir. Se o tipo de dados da coluna for varchar, a coluna deverá ser convertida para inteiro primeiro.

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

  • [NOT] LIKE value: pesquisa o padrão especificado. Use o sinal de porcentagem (%) como caractere curinga, conforme mostrado no exemplo a seguir.

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

  • [NOT] IN (value[, value[, ...]): especifica uma lista de valores possíveis para uma coluna, como no exemplo a seguir.

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

Divide a saída da instrução SELECT em linhas com valores correspondentes.

ALL e DISTINCT determinam se conjuntos de agrupamentos duplicados produzem linhas de saída distintas. Se omitido, ALL será pressuposto.

grouping_expressions permite realizar operações de agrupamento complexas. Você pode usar operações de agrupamento complexas para realizar uma análise que exija agregação de vários conjuntos de colunas em uma única consulta.

O elemento grouping_expressions pode ser qualquer função, como SUM, AVG ou COUNT, executada nas colunas de entrada.

As expressões GROUP BY podem agrupar a saída por nomes de coluna de entrada não exibidos na saída da instrução SELECT.

Todas as expressões de saída devem ser funções agregadas ou colunas presentes na cláusula GROUP BY.

Você pode usar uma única consulta para realizar uma análise que exija a agregação de vários conjuntos de colunas.

O Athena aceita agregações complexas que usam GROUPING SETS, CUBE e ROLLUP. GROUP BY GROUPING SETS especifica várias listas de colunas para agrupamento. GROUP BY CUBE gera todos os conjuntos de agrupamento possíveis para um determinado conjunto de colunas. GROUP BY ROLLUP gera todos os subtotais possíveis para um determinado conjunto de colunas. As operações de agrupamento complexas não permitem agrupamento de expressões compostas de colunas de entrada. Somente nomes de coluna são permitidos.

Você normalmente pode usar UNION ALL para obter os mesmos resultados dessas operações GROUP BY, mas consultas que usam GROUP BY têm a vantagem de ler os dados uma vez, e UNION ALL lê os dados subjacentes três vezes e podem produzir resultados inconsistentes quando a fonte de dados está sujeita a alterações.

[ HAVING condition ]

Usada com funções de agregação e a cláusula GROUP BY. Controla quais grupos são selecionados, eliminando grupos que não atendam a condition. A filtragem ocorrerá depois de grupos, e as agregações serão calculadas.

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

UNION, INTERSECT e EXCEPT combinam os resultados de mais de uma instrução SELECT em uma única consulta. ALL ou DISTINCT controla a exclusividade das linhas incluídas no conjunto final de resultados.

UNION combina as linhas resultantes da primeira consulta com as linhas resultantes da segunda consulta. Para eliminar duplicatas, UNION cria uma tabela de hash, que consome memória. Para melhor performance, considere usar UNION ALL se a consulta não exigir eliminação de duplicatas. Várias cláusulas UNION são processadas da esquerda para a direita, a menos que você use parênteses para definir explicitamente a ordem de processamento.

INTERSECT retorna apenas as linhas que estão presentes nos resultados da primeira e da segunda consultas.

EXCEPT retorna as linhas dos resultados da primeira consulta, excluindo as linhas encontradas pela segunda consulta.

ALL faz com que todas as linhas sejam incluídas, mesmo se elas forem idênticas.

DISTINCT faz com que apenas as linhas exclusivas sejam incluídas no conjunto de resultados combinados.

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

Classifica um conjunto de resultados por um ou mais expression de saída.

Quando a cláusula contém várias expressões, o conjunto de resultados é classificado de acordo com o primeiro expression. Em seguida, o segundo expression é aplicado a linhas que tenham valores correspondentes da primeira expressão, e assim por diante.

Cada expression pode especificar colunas de saída de SELECT ou um número ordinal para uma coluna de saída por posição, a partir de um.

ORDER BY é avaliada como a última etapa após qualquer cláusula GROUP BY ou HAVING. ASC e DESC determinam se os resultados são classificados em ordem crescente ou decrescente. A ordem de classificação padrão é a ordem decrescente (ASC). A ordem nula padrão é NULLS LAST, independentemente da ordem de classificação crescente ou decrescente.

[ Contagem de DESLOCAMENTO [ LINHA | LINHAS ] ]

Use a cláusula OFFSET para descartar várias linhas iniciais do conjunto de resultados. Se a cláusula ORDER BY estiver presente, a cláusula OFFSET será avaliada em um conjunto de resultados classificados e o conjunto permanecerá classificado após as linhas ignoradas serem descartadas. Se a consulta não tiver cláusula ORDER BY, a definição de quais linhas serão descartadas é arbitrária. Se a contagem especificada por OFFSET for igual ou exceder o tamanho do conjunto de resultados, o resultado final será vazio.

LIMIT [ count | ALL ]

Restringe o número de linhas no conjunto de resultados a count. LIMIT ALL é igual à omissão da cláusula LIMIT. Se a consulta não tiver a cláusula ORDER BY, os resultados serão arbitrários.

TABLESAMPLE [ BERNOULLI | SYSTEM ] (porcentagem)

Operador operacional para selecionar linhas de uma tabela com base em um método de amostragem.

BERNOULLI seleciona cada linha para estar no exemplo da tabela com uma probabilidade de percentage. Todos os blocos físicos da tabela são examinados, e determinadas linhas são ignoradas com base em uma comparação entre o percentage de exemplo e um valor aleatório calculado no runtime

Com SYSTEM, a tabela é dividida em segmentos lógicos de dados, e a tabela mostra um exemplo dessa granularidade.

Todas as linhas de um determinado segmento são selecionadas, ou o segmento é ignorado com base em uma comparação entre o percentage de exemplo e um valor aleatório calculado no runtime. A amostragem de SYSTEM depende do conector. Esse método não garante probabilidades de amostragem independentes.

[ UNNEST (array_or_map) [WITH ORDINALITY] ]

Expande uma matriz ou um mapa para uma relação. As matrizes são expandidas para uma única coluna. Os mapas são expandidos para duas colunas (chave, valor).

Você pode usar UNNEST com vários argumentos, que são expandidos para várias colunas com o máximo de linhas do maior argumento de cardinalidade.

Outras colunas são preenchidas com nulos.

A cláusula WITH ORDINALITY adiciona uma coluna de ordinalidade ao final.

UNNEST costuma ser usado com um JOIN e pode fazer referência a colunas de relações no lado esquerdo do JOIN.

Obter os locais de arquivos dos dados de origem no Amazon S3

Para ver o local do arquivo do Amazon S3 referente aos dados em uma linha da tabela, você pode usar "$path" em uma consulta SELECT, como no seguinte exemplo:

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

Essa consulta retorna um resultado semelhante a este:

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

Para retornar uma lista classificada e exclusiva dos caminhos de nome de arquivo do S3 para os dados em uma tabela, você pode usar SELECT DISTINCT e ORDER BY, como no exemplo a seguir.

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

Para retornar somente os nomes de arquivo sem o caminho, você pode especificar "$path" como um parâmetro para a função regexp_extract, conforme mostrado no exemplo a seguir.

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

Para retornar os dados de um arquivo específico, especifique o arquivo na cláusula WHERE, como no exemplo a seguir.

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

Para obter mais informações e exemplos, consulte o artigo da Central de Conhecimento Como posso ver o arquivo de origem do Amazon S3 para uma linha em uma tabela do Athena?

nota

No Athena, as colunas ocultas de metadados do Hive ou do Iceberg $bucket, $file_modified_time, $file_size e $partition não são compatíveis para visualizações.

Caractere de escape para aspas simples

Para inserir caracteres de escape em aspas simples, preceda-as com outras aspas simples, conforme o exemplo a seguir. Não confunda isso com aspas duplas. 

Select 'O''Reilly'
Resultados

O'Reilly

Recursos adicionais

Para obter mais informações sobre como usaras instruções SELECT no Athena, consulte os recursos abaixo.

Para obter informações sobre este tópico consulte esta referência
Executar consultas no Athena Executar consultas SQL no Amazon Athena
Usar SELECT para criar uma tabela Criar uma tabela com base em resultados de consultas (CTAS)
Inserir dados de uma consulta SELECT em outra tabela INSERT INTO
Usar funções integradas nas instruções SELECT Funções no Amazon Athena
Usar funções definidas pelo usuário nas instruções SELECT Consultar com funções definidas pelo usuário
Consultar metadados do catálogo de dados Consultar o AWS Glue Data Catalog