Otimizar consultas

Use as técnicas de otimização de consulta descritas nesta seção para fazer com que as consultas sejam executadas mais rapidamente ou como soluções alternativas para consultas que excedam os limites de recursos no Athena.

Otimizar junções

Há muitas estratégias diferentes para executar junções em um mecanismo de consulta distribuído. Duas das mais comuns são as junções hash distribuídas e as consultas com condições de junção complexas.

Em uma junção de hash distribuída, coloque tabelas grandes à esquerda e tabelas pequenas à direita

O tipo mais comum de junção usa uma comparação de igualdade como condição de junção. O Athena executa esse tipo de junção como uma junção hash distribuída.

Em uma junção hash distribuída, o mecanismo cria uma tabela de pesquisa (tabela hash) com base em um dos lados da junção. Esse lado é chamado de lado da compilação. Os registros do lado da compilação são distribuídos entre os nós. Cada nó cria uma tabela de pesquisa para o respectivo subconjunto. O outro lado da junção, denominado lado de teste, é então transmitido pelos nós. Os registros do lado de teste são distribuídos pelos nós da mesma forma que no lado da compilação. Isso permite que cada nó realize a junção pesquisando os registros correspondentes em sua própria tabela de pesquisa.

Quando as tabelas de pesquisa criadas do lado da compilação da junção não cabem na memória, as consultas podem falhar. Mesmo que o tamanho total do lado da compilação seja menor que a memória disponível, as consultas poderão falhar se a distribuição dos registros tiver uma distorção significativa. Em um caso extremo, todos os registros poderiam ter o mesmo valor para a condição de junção e caber na memória em um único nó. Mesmo uma consulta com menos distorção poderá falhar se um conjunto de valores for enviado ao mesmo nó e a soma dos valores ultrapassar a memória disponível. Os nós têm a capacidade de vazar registros para o disco, mas o vazamento retarda a execução da consulta e pode ser insuficiente para evitar que a consulta falhe.

O Athena tenta reordenar as junções para usar a relação maior como lado de teste e a relação menor como o lado da compilação. No entanto, por não gerenciar os dados em tabelas, o Athena tem informações limitadas e muitas vezes deve presumir que a primeira tabela é a maior e a segunda tabela é a menor.

Ao gravar junções com condições de junção baseadas em igualdade, suponha que a tabela à esquerda da palavra-chave JOIN seja o lado de teste e que a tabela à direita seja o lado da compilação. Verifique se a tabela direita, o lado da compilação, é a menor das tabelas. Se não for possível diminuir o lado da compilação da junção o suficiente para caber na memória, considere executar várias consultas que unam subconjuntos da tabela de compilação.

Usar EXPLAIN para analisar consultas com junções complexas

Consultas com condições de junção complexas (por exemplo, consultas que usam LIKE, > ou outros operadores) geralmente demandam muito computacionalmente. Na pior das hipóteses, cada registro de um lado da junção deve ser comparado a todos os registros do outro lado da junção. Como o tempo de execução aumenta com o quadrado do número de registros, as consultas correm o risco de exceder o tempo máximo de execução.

Para descobrir como o Athena executará sua consulta com antecedência, você pode usar a instrução EXPLAIN. Para ter mais informações, consulte Usar EXPLAIN e EXPLAIN ANALYZE no Athena e Noções básicas dos resultados da instrução EXPLAIN do Athena.

Reduzir o escopo das funções da janela ou removê-las

Por serem operações que fazem uso intensivo de recursos, as funções de janela podem fazer com que as consultas sejam executadas lentamente ou até mesmo falhem com a mensagem Query exhausted resources at this scale factor. As funções de janela mantêm todos os registros em que operam na memória para calcular o resultado. Quando a janela é muito grande, a função de janela pode ficar sem memória.

Para garantir que suas consultas sejam executadas dentro dos limites de memória disponíveis, reduza o tamanho das janelas em que suas funções de janela operam. Para fazer isso, adicione uma cláusula PARTITIONED BY ou restrinja o escopo das cláusulas de particionamento existentes.

Usar de funções que não são de janela

Às vezes, consultas com funções de janela podem ser gravadas sem funções de janela. Por exemplo, em vez de usar row_number para encontrar os principais registros N, você pode usar ORDER BY e LIMIT. Em vez de usar row_number ou rank para desduplicar registros, você pode usar funções agregadas como max_by, min_by e arbitrary.

Por exemplo, digamos que você tenha um conjunto de dados com atualizações de um sensor. O sensor informa periodicamente o status da bateria e inclui alguns metadados, como localização. Para saber o último status da bateria de cada sensor e sua localização, você pode usar esta consulta:

SELECT sensor_id,
       arbitrary(location) AS location,
       max_by(battery_status, updated_at) AS battery_status
FROM sensor_readings
GROUP BY sensor_id

Os metadados, como a localização, são os mesmos para cada registro, então você pode usar a função arbitrary para escolher qualquer valor do grupo.

Para obter o último status da bateria, você pode usar a função max_by. A função max_by escolhe o valor de uma coluna do registro em que o valor máximo de outra coluna foi encontrado. Nesse caso, ela retorna o status da bateria do registro com a hora da última atualização dentro do grupo. Essa consulta é executada mais rapidamente e usa menos memória do que uma consulta equivalente com função de janela.

Otimizar agregações

Ao realizar uma agregação, o Athena distribui os registros entre os nós de processamento usando as colunas na cláusula GROUP BY. Para tornar a tarefa de combinar registros com grupos o mais eficiente possível, os nós tentam manter os registros na memória, mas os vazam para o disco, se necessário.

Também é uma boa ideia evitar incluir colunas redundantes nas cláusulas GROUP BY. Como menos colunas exigem menos memória, uma consulta que usa menos colunas para descrever um grupo é mais eficiente. As colunas numéricas também usam menos memória do que as strings. Por exemplo, ao agregar um conjunto de dados que tenha um ID numérico de categoria e um nome de categoria, use somente a coluna ID da categoria na cláusula GROUP BY.

Às vezes, as consultas incluem colunas na cláusula GROUP BY para contornar o fato de que uma coluna deve ser parte da cláusula GROUP BY ou ser uma expressão agregada. Se essa regra não for seguida, você poderá receber uma mensagem de erro como esta:

EXPRESSION_NOT_AGGREGATE: line 1:8: 'category' must be an aggregate expression or appear in GROUP BY clause

Para evitar a necessidade de adicionar colunas redundantes à cláusula GROUP BY, use a função arbitrary, como no exemplo a seguir.

SELECT country_id,
       arbitrary(country_name) AS country_name,
       COUNT(*) AS city_count
FROM world_cities
GROUP BY country_id

A função ARBITRARY retorna um valor de arbitrary do grupo. A função é útil quando você sabe que todos os registros do grupo têm o mesmo valor para uma coluna, mas o valor não identifica o grupo.

Otimizar as principais N consultas

A cláusula ORDER BY retorna os resultados de uma consulta em ordem de classificação. O Athena usa a classificação distribuída para executar a operação de classificação paralelamente em vários nós.

Se você não precisar estritamente que seu resultado seja classificado, evite adicionar uma cláusula ORDER BY. Além disso, evite adicionar ORDER BY a consultas internas se não for estritamente necessário. Em muitos casos, o planejador de consultas poderá remover a classificação redundante, mas isso não é garantido. Uma exceção a essa regra é se uma consulta interna estiver realizando uma operação principal N, como encontrar o N mais recente ou os valores de N mais comuns.

Quando o Athena vê ORDER BY junto com LIMIT, ele entende que você está executando uma consulta principal N e usa as operações dedicadas adequadamente.

Incluir somente as colunas obrigatórias

Caso não precise estritamente de uma coluna, não a inclua na consulta. Quanto menos dados a consulta precisar processar, mais rápido ela será executada. Isso reduz a quantidade de memória necessária e a quantidade de dados que devem ser enviados entre os nós. Se você estiver usando um formato de arquivo colunar, reduzir o número de colunas também reduzirá a quantidade de dados lidos do Amazon S3.

O Athena não tem limite específico para o número de colunas de um resultado, mas a forma como as consultas são executadas limita o possível tamanho combinado das colunas. O tamanho combinado das colunas inclui os nomes e tipos.

Por exemplo, o seguinte erro é causado por uma relação que excede o limite de tamanho de um descritor de relação:

GENERIC_INTERNAL_ERROR: io.airlift.bytecode.CompilationException

Para resolver esse problema, reduza o número de colunas na consulta ou crie subconsultas e use JOIN para recuperar uma quantidade menor de dados. Se você tiver consultas que executam SELECT * na consulta mais externa, altere * para uma lista com somente as colunas necessárias.

Otimizar consultas com o uso de aproximações

O Athena tem suporte para funções agregadas de aproximação para contar valores distintos, valores mais frequentes, percentis (incluindo medianas aproximadas) e criar histogramas. Use essas funções sempre que não for necessário obter valores exatos.

Diferentemente das operações COUNT(DISTINCT col), o approx_distinct usa muito menos memória e é executado mais rápido. Da mesma forma, usar numeric_histogram em vez de histogram utiliza métodos aproximados e, portanto, menos memória.

Otimizar LIKE

É possível usar LIKE para encontrar strings correspondentes, mas com strings longas, demanda uso intensivo de computação. Na maioria dos casos, a função regexp_like é uma alternativa mais rápida e também oferece mais flexibilidade.

Muitas vezes, é possível otimizar uma pesquisa ancorando a substring que você está procurando. Por exemplo, se você estiver procurando por um prefixo, é muito melhor usar “substr%” em vez de “substr%”. Ou, se você estiver usando regexp_like, “^substr”.

Usar UNION ALL em vez de UNION

UNION ALL e UNION são duas maneiras de combinar os resultados de duas consultas em um único resultado. UNION ALL concatena os registros da primeira consulta com a segunda e UNION faz o mesmo, mas também remove as duplicatas. UNION precisa processar todos os registros e encontrar as duplicatas, o que requer uso intensivo de memória e computação, mas UNION ALL é uma operação relativamente rápida. A menos que você precise desduplicar registros, use UNION ALL para obter a melhor performance.

Usar UNLOAD para grandes conjuntos de resultados

Quando se espera que os resultados da consulta sejam grandes (por exemplo, dezenas de milhares de linhas ou mais), use UNLOAD para exportar os resultados. Na maioria dos casos, isso é mais rápido do que executar uma consulta normal, e usar UNLOAD também oferece mais controle sobre a saída.

Quando a consulta acaba de ser executada, o Athena armazena o resultado como um único arquivo CSV não compactado no Amazon S3. Isso leva mais tempo que usar UNLOAD, não apenas porque o resultado não está compactado, mas também porque a operação não pode ser paralelizada. Por sua vez, UNLOAD grava os resultados diretamente dos nós de trabalho e faz uso total do paralelismo do cluster de computação. Além disso, é possível configurar UNLOAD para gravar os resultados em formato compactado e em outros formatos de arquivo, como JSON e Parquet.

Para ter mais informações, consulte UNLOAD.

Use CTAS ou o Glue ETL para materializar agregações usadas com frequência

“Materializar” uma consulta é uma forma de acelerar a performance da consulta armazenando resultados de consultas complexas pré-computados (por exemplo, agregações e junções) para reutilização em consultas subsequentes.

Se muitas de suas consultas incluírem as mesmas junções e agregações, você poderá materializar a subconsulta comum como uma nova tabela e executar consultas nessa tabela. É possível criar a nova tabela com Criar uma tabela com base em resultados de consultas (CTAS) ou com uma ferramenta ETL dedicada, como o Glue ETL.

Por exemplo, digamos que você tenha um painel com widgets que exibem diferentes aspectos de um conjunto de dados de pedidos. Cada widget tem sua própria consulta, mas todas as consultas compartilham as mesmas junções e filtros. Uma tabela de pedidos é unida a uma tabela de itens de linha, e há um filtro para exibir somente os últimos três meses. Se você identificar os atributos comuns dessas consultas, poderá criar uma nova tabela que os widgets possam usar. Isso reduz a duplicação e melhora a performance. A desvantagem é que você deverá manter a nova tabela atualizada.

Reutilizar resultados da consulta

É comum que a mesma consulta seja executada várias vezes em pouco tempo. Por exemplo, isso pode ocorrer quando várias pessoas abrem o mesmo painel de dados. Ao executar uma consulta, você pode pedir para o Athena reutilizar os resultados calculados anteriormente. Especifique a idade máxima dos resultados a serem reutilizados. Se a mesma consulta for executada anteriormente dentro desse período, o Athena retornará esses resultados em vez de executar a consulta novamente. Para obter mais informações, consulte Reutilização de resultados da consulta no Athena aqui no Guia do usuário do Amazon Athena e Reduce cost and improve query performance with Amazon Athena Query Result Reuse no blog de big data da AWS.