Otimizar a computação em consultas

Este documento apresenta as práticas recomendadas para otimizar o desempenho da sua consulta.

Após a conclusão da consulta, é possível ver o plano de consulta no Console do Google Cloud. Também é possível solicitar detalhes da execução usando as visualizações INFORMATION_SCHEMA.JOBS* ou o método jobs.get da API REST.

O plano de consulta inclui detalhes sobre os estágios e as etapas da consulta. Esses detalhes podem ajudar a identificar formas de melhorar o desempenho da consulta. Por exemplo, se você perceber um estágio que grava muito mais resultados do que outros, pode ser necessário filtrar anteriormente na consulta.

Para saber mais sobre o plano de consulta e ver exemplos de como as informações dele podem ajudar a melhorar o desempenho da consulta, consulte Receber insights de desempenho da consulta. Depois de abordar os insights de desempenho da consulta, é possível otimizar ainda mais a consulta realizando as seguintes tarefas:

Reduzir dados processados

É possível reduzir os dados que precisam ser processados usando as opções descritas nas seções a seguir.

Evite SELECT *

Prática recomendada: controle a projeção consultando apenas as colunas necessárias.

Projeção se refere ao número de colunas lidas pela consulta. Projetar colunas em excesso incorre em E/S adicional (desperdiçada) e materialização (gravar resultados).

  • Use as opções de visualização de dados. Se você estiver testando ou explorando dados, use uma das opções de visualização de dados em vez de SELECT *.
  • Consulte colunas específicas. A aplicação de uma cláusula LIMIT a uma consulta SELECT * não afeta a quantidade de dados lidos. Você será cobrado pela leitura de todos os bytes na tabela inteira, e a consulta é contabilizada na sua cota de nível gratuito. Em vez disso, consulte apenas as colunas de que você precisa. Por exemplo, use SELECT * EXCEPT para excluir uma ou mais colunas dos resultados.
  • Usar tabelas particionadas. Se você precisar fazer a consulta em todas as colunas de uma tabela, mas apenas em um subconjunto de dados, faça o seguinte:

  • Use SELECT * EXCEPT. Consultar um subconjunto de dados ou usar SELECT * EXCEPT pode reduzir muito a quantidade de dados lidos por uma consulta. Além da economia de custos, o desempenho é melhorado reduzindo-se o montante de E/S de dados e de materialização obrigatória para os resultados da consulta.

    SELECT * EXCEPT (col1, col2, col5)
FROM mydataset.newtable

Evite tabelas de caracteres curinga em excesso

Prática recomendada: ao consultar tabelas curinga, você precisa usar o prefixo mais granular.

Use caracteres curinga para consultar várias tabelas usando instruções SQL concisas. As tabelas de caracteres curinga são uma união de tabelas correspondente a uma expressão de caractere curinga. As tabelas curinga são úteis quando o conjunto de dados contém os seguintes recursos:

  • várias tabelas nomeadas de maneira semelhante com esquemas compatíveis;
  • Tabelas fragmentadas

Ao consultar uma tabela com caracteres curinga, especifique um caractere curinga (*) após o prefixo da tabela em comum. Por exemplo, FROM bigquery-public-data.noaa_gsod.gsod194* consulta todas as tabelas dos anos 1940.

Os prefixos mais detalhados têm um desempenho melhor do que os mais curtos. Por exemplo, FROM bigquery-public-data.noaa_gsod.gsod194* tem desempenho melhor do que FROM bigquery-public-data.noaa_gsod.* porque menos tabelas correspondem ao caractere curinga.

Evitar tabelas fragmentadas por data

Prática recomendada: não use tabelas fragmentadas por data (também chamadas de tabelas com nome de data) no lugar de tabelas particionadas por tempo.

Tabelas particionadas têm melhor desempenho do que as tabelas com nome de data. Quando você cria tabelas fragmentadas por data, o BigQuery precisa manter uma cópia do esquema e dos metadados para cada tabela com nome de data. Além disso, quando as tabelas com nome de data são usadas, pode ser necessário que o BigQuery verifique as permissões de cada tabela consultada. Essa prática também sobrecarrega a consulta e afeta o desempenho dela.

Evitar a fragmentação excessiva de tabelas

Prática recomendada: evite criar fragmentos de tabela em demasia. Se você estiver fragmentando tabelas por data, use tabelas particionadas por tempo.

A fragmentação de tabelas refere-se a dividir grandes conjuntos de dados em tabelas separadas e adicionar um sufixo a cada nome da tabela. Se você estiver fragmentando tabelas por data, use tabelas particionadas por tempo.

Devido ao baixo custo do armazenamento do BigQuery, você não precisa otimizar suas tabelas por causa do custo, como faria em um banco de dados relacional. Criar um grande número de fragmentos de tabela tem impactos de desempenho que superam os benefícios de custo.

As tabelas fragmentadas exigem que o BigQuery mantenha o esquema, os metadados e as permissões para cada fragmento. Devido à sobrecarga extra necessária para manter informações em cada fragmento, o desempenho da consulta poderá ser afetado se houver fragmentação em excesso.

A quantidade e a origem dos dados lidos por uma consulta podem afetar o desempenho e o custo dela.

Elimine consultas particionadas

Prática recomendada: ao consultar uma tabela particionada para filtrar por partições em tabelas particionadas, use as seguintes colunas:

  • Para tabelas particionadas por tempo de processamento, use a pseudocoluna _PARTITIONTIME.
  • Para tabelas particionadas, como baseadas em coluna de tempo e intervalo de números inteiros, use a coluna de particionamento.

Para tabelas particionadas por unidade de tempo, filtrar os dados com _PARTITIONTIME ou coluna de particionamento permite especificar uma data ou um intervalo de datas. Por exemplo, a cláusula WHERE a seguir usa a pseudocoluna _PARTITIONTIME para especificar partições entre 1º e 31 de janeiro de 2016:

WHERE _PARTITIONTIME
BETWEEN TIMESTAMP("20160101")
AND TIMESTAMP("20160131")

A consulta processa dados apenas nas partições indicadas pelo intervalo de datas. Filtrar suas partições melhora o desempenho da consulta e reduz os custos.

Reduzir os dados antes de usar um JOIN

Prática recomendada: reduza a quantidade de dados processados antes de uma cláusula JOIN realizando agregações com antecedência.

O uso de uma cláusula GROUP BY com funções agregadas é computacionalmente caro, porque esses tipos de consultas usam shuffle. Como essas consultas usam muitos recursos computacionais, use uma cláusula GROUP BY somente quando necessário.

Para consultas com GROUP BY e JOIN, execute a agregação anteriormente na consulta para reduzir a quantidade de dados processados. Por exemplo, a consulta a seguir executa uma JOIN em duas tabelas grandes sem nenhuma filtragem antes:

WITH
  users_posts AS (
  SELECT *
  FROM
    `bigquery-public-data`.stackoverflow.comments AS c
  JOIN
    `bigquery-public-data`.stackoverflow.users AS u
  ON
    c.user_id = u.id
  )
SELECT
  user_id,
  ANY_VALUE(display_name) AS display_name,
  ANY_VALUE(reputation) AS reputation,
  COUNT(text) AS comments_count
FROM users_posts
GROUP BY user_id
ORDER BY comments_count DESC
LIMIT 20;

Esta consulta pré-agrega a contagem de comentários, o que reduz a quantidade de dados lidos para o JOIN:

WITH
  comments AS (
  SELECT
    user_id,
    COUNT(text) AS comments_count
  FROM
    `bigquery-public-data`.stackoverflow.comments
  WHERE
    user_id IS NOT NULL
  GROUP BY user_id
  ORDER BY comments_count DESC
  LIMIT 20
  )
SELECT
  user_id,
  display_name,
  reputation,
  comments_count
FROM comments
JOIN
  `bigquery-public-data`.stackoverflow.users AS u
ON
  user_id = u.id
ORDER BY comments_count DESC;

Use a cláusula WHERE

Prática recomendada: use uma cláusula WHERE para limitar a quantidade de dados que uma consulta retorna. Quando possível, use as colunas BOOL, INT, FLOAT ou DATE na cláusula WHERE.

As operações nas colunas BOOL, INT, FLOAT e DATE geralmente são mais rápidas do que as operações nas colunas STRING ou BYTE. Quando possível, use uma coluna que utilize um desses tipos de dados na cláusula WHERE para reduzir a quantidade de dados retornados pela consulta.

Otimizar operações de consulta

É possível otimizar suas operações de consulta usando as opções descritas nas seções a seguir.

Evitar transformar dados repetidamente

Prática recomendada: se você estiver usando o SQL para executar operações ETL, evite situações em que você está transformando repetidamente os mesmos dados.

Por exemplo, se estiver usando SQL para cortar strings ou extrair dados usando expressões regulares, terá mais desempenho para materializar os resultados transformados em uma tabela de destino. Funções como expressões regulares requerem cálculo adicional. Consultar a tabela de destino sem a sobrecarga adicional de transformação é muito mais eficiente.

Evitar várias avaliações dos mesmos CTEs

Prática recomendada: use linguagem procedural, variáveis, tabelas temporárias e tabelas que expiram automaticamente para manter os cálculos e usá-los posteriormente na consulta.

Quando a consulta contém expressões de tabela comuns (CTEs, na sigla em inglês) usadas em vários locais, elas podem ser avaliadas sempre que são referenciadas. O otimizador de consultas tenta detectar partes que poderiam ser executadas apenas uma vez, mas isso nem sempre é possível. Como resultado, usar uma CTE pode não ajudar a reduzir a complexidade da consulta interna e o consumo de recursos.

É possível armazenar o resultado de um CTE em uma variável escalar ou em uma tabela temporária, dependendo dos dados retornados pelo CTE.

Evite subconsultas e mesclagens repetidas

Prática recomendada: evite juntar repetidamente as mesmas tabelas e usar as mesmas subconsultas.

Em vez de juntar repetidamente os dados, pode ser mais eficiente usar dados repetidos aninhados para representar os relacionamentos. Os dados repetidos aninhados salvam o impacto de desempenho da largura de banda de comunicação exigida por uma junção. Isso também reduz os custos de E/S gerados pela leitura e gravação repetida dos mesmos dados. Para mais informações, consulte usar campos aninhados e repetidos.

Da mesma forma, repetir as mesmas subconsultas afeta o desempenho ao processar consultas repetidamente. Se você estiver usando as mesmas subconsultas em várias consultas, pense em materializar os resultados da subconsulta em uma tabela. Em seguida, use os dados materializados nas suas consultas.

A materialização dos resultados da subconsulta melhora o desempenho e reduz a quantidade geral dos dados que o BigQuery lê e grava. O pequeno custo de armazenar dados materializados supera o impacto no desempenho do processamento repetido de E/S e consultas.

Otimizar os padrões de união

Prática recomendada: para consultas que unem dados de várias tabelas, otimize os padrões de agrupamento começando pela tabela maior.

Ao criar uma consulta usando a cláusula JOIN, considere a ordem em que você está mesclando os dados. O otimizador de consulta GoogleSQL determina qual tabela deve ficar em qual lado da junção. Como prática recomendada, coloque a tabela com o maior número de linhas primeiro, seguida da tabela com o menor número de linhas e, em seguida, as tabelas restantes diminuindo o tamanho.

Quando você tem uma tabela grande no lado esquerdo do JOIN e uma pequena no lado direito do JOIN, uma união de transmissão é criada. Uma união de transmissão envia todos os dados para a tabela menor para cada slot que processa a tabela maior. Recomenda-se realizar primeiro a união de transmissão.

Para ver o tamanho das tabelas no JOIN, consulte Acessar informações sobre tabelas.

Otimizar a cláusula ORDER BY

Prática recomendada: ao usar a cláusula ORDER BY, siga as práticas recomendadas:

  • Use ORDER BY na consulta mais externa ou em cláusulas de janela. Coloque operações complexas no final da consulta. Colocar uma cláusula ORDER BY no meio de uma consulta afeta significativamente o desempenho, a menos que ela esteja sendo usada em uma função (analítica) de janela.

    Outra técnica para ordenar a consulta é colocar operações complexas no final da consulta, como expressões regulares e funções matemáticas. Essa técnica reduz os dados a serem processados antes que operações complexas sejam realizadas.

  • Use uma cláusula LIMIT. Se você estiver ordenando um número muito grande de valores, mas não precisar que todos eles sejam retornados, use uma cláusula LIMIT. Por exemplo, a consulta a seguir solicita um conjunto de resultados muito grande e gera um erro Resources exceeded. A consulta é classificada pela coluna title em mytable. A coluna title contém milhões de valores.

    SELECT
title
FROM
`my-project.mydataset.mytable`
ORDER BY
title;

    Para remover o erro, use uma consulta como esta:

    SELECT
title
FROM
`my-project.mydataset.mytable`
ORDER BY
title DESC
LIMIT
1000;

  • Use uma função de janela. Se você estiver ordenando um número muito grande de valores, use uma função de janela e limite os dados antes de chamar a função da janela. Por exemplo, a consulta a seguir lista os 10 usuários mais antigos do Stack Overflow e a classificação deles, com a conta mais antiga com a classificação mais baixa:

    SELECT
id,
reputation,
creation_date,
DENSE_RANK() OVER (ORDER BY creation_date) AS user_rank
FROM bigquery-public-data.stackoverflow.users
ORDER BY user_rank ASC
LIMIT 10;

    Essa consulta leva aproximadamente 15 segundos para ser executada. Essa consulta usa LIMIT no final da consulta, mas não na função de janela DENSE_RANK() OVER. Por isso, a consulta exige que todos os dados sejam ordenados em um único nó de trabalho.

    Em vez disso, é preciso limitar o conjunto de dados antes de calcular a função de janela para melhorar o desempenho:

    WITH users AS (
SELECT
id,
reputation,
creation_date,
FROM bigquery-public-data.stackoverflow.users
ORDER BY creation_date ASC
LIMIT 10)
SELECT
id,
reputation,
creation_date,
DENSE_RANK() OVER (ORDER BY creation_date) AS user_rank
FROM users
ORDER BY user_rank;

    A execução dessa consulta leva aproximadamente dois segundos e retorna os mesmos resultados da consulta anterior.

    Uma ressalva é que a função DENSE_RANK() classifica os dados em anos. Portanto, para classificar dados que se estendem por vários anos, essas consultas não fornecem resultados idênticos.

Dividir consultas complexas em consultas menores.

Prática recomendada: aproveite os recursos de consulta de várias instruções e os procedimentos armazenados para executar os cálculos projetados como uma consulta complexa. como múltiplas consultas menores e mais simples.

Consultas complexas, funções REGEX e subconsultas ou junções em camadas podem ser lentas e consumir muitos recursos. Tentar encaixar todos os cálculos em uma grande instrução SELECT, por exemplo, para torná-la uma visualização, às vezes é um antipadrão e pode resultar em uma consulta lenta e com uso intensivo de recursos. Em casos extremos, o plano de consulta interno fica tão complexo que o BigQuery não consegue executá-lo.

A divisão de uma consulta complexa permite materializar resultados intermediários em variáveis ou tabelas temporárias. Use esses resultados intermediários em outras partes da consulta. É cada vez mais útil quando esses resultados são necessários em mais de um local da consulta.

Muitas vezes, é possível expressar melhor a intenção real de partes da consulta, sendo as tabelas temporárias os pontos de materialização de dados.

Usar campos aninhados e repetidos

Para informações sobre como desnormalizar o armazenamento de dados usando campos aninhados e repetidos, consulte Usar campos aninhados e repetidos.

Usar tipos de dados INT64 em agrupamentos

Prática recomendada: use tipos de dados INT64 em agrupamentos em vez de tipos de dados STRING para reduzir os custos e melhorar o desempenho de comparação.

O BigQuery não indexa chaves primárias como bancos de dados tradicionais. Portanto, quanto maior a coluna de junção, mais tempo a comparação leva. Sendo assim, usar tipos de dados INT64 em junções é mais barato e mais eficiente do que usar tipos de dados STRING.

Reduzir resultados de consultas

É possível reduzir os resultados da consulta usando as opções descritas nas seções a seguir.

Materializar grandes conjuntos de resultados

Prática recomendada: pense cuidadosamente antes de materializar grandes conjuntos de resultados em uma tabela de destino. Gravar grandes conjuntos de resultados causa impactos de desempenho e custo.

O BigQuery limita os resultados em cache a aproximadamente 10 GB compactados. As consultas que retornam resultados maiores ultrapassam esse limite e frequentemente resultam no seguinte erro: Response too large.

Esse erro geralmente ocorre quando você seleciona um grande número de campos de uma tabela com uma quantidade considerável de dados. Problemas ao gravar resultados em cache também podem ocorrer em consultas em estilo ETL que normalizam dados sem redução ou agregação.

Você pode superar a limitação de tamanho dos resultados armazenados em cache usando as seguintes opções:

  • usando filtros para limitar o conjunto de resultados;
  • usando uma cláusula LIMIT para reduzir o conjunto de resultados, especialmente se estiver usando uma cláusula ORDER BY;
  • gravando os dados de saída em uma tabela de destino.

Você pode navegar pelos resultados usando a API REST do BigQuery. Para mais informações, consulte Como fazer a paginação de dados da tabela.

Usar o BI Engine

Para acelerar ainda mais suas consultas SQL, armazenando em cache os dados que você usa com mais frequência, adicione uma reserva do BI Engine ao projeto em que as consultas estão sendo calculadas. O BigQuery BI Engine usa um mecanismo de consulta vetorial para acelerar o desempenho da consulta SELECT.

Evitar padrões anti-SQL

As práticas recomendadas a seguir apresentam uma orientação sobre como evitar os antipadrões de consulta que afetam o desempenho no BigQuery.

Evitar mesclagens automáticas

Prática recomendada: em vez de usar mesclagens automáticas, use uma função de janela (analítica).

Em geral, as mesclagens automáticas são usadas para calcular relacionamentos dependentes de linha. O resultado disso é uma possível quadruplicação do número de linhas de saída. Esse aumento nos dados de saída pode prejudicar o desempenho.

Para reduzir o número de bytes extras gerados pela consulta, use uma função de janela (analítica).

Evitar correlações

Prática recomendada: evite uniões que gerem mais saídas do que entradas. Quando um CROSS JOIN for necessário, agregue seus dados previamente.

As uniões cruzadas são consultas em que cada linha da primeira tabela é unida a cada linha na segunda tabela. Há chaves não únicas nos dois lados. O pior caso de saída é o número de linhas na tabela esquerda multiplicado pelo número de linhas na tabela direita. Em casos extremos, a consulta pode não terminar.

Se o job de consulta for concluído, a explicação do plano de consulta mostrará linhas de saída versus linhas de entrada. É possível confirmar um produto cartesiano (em inglês) modificando a consulta de maneira a imprimir o número de linhas em cada lado da cláusula JOIN, agrupado pela chave de união.

Para evitar problemas de desempenho resultantes de uniões que geram mais saídas do que entradas:

  • Use uma cláusula GROUP BY para agregar dados previamente.
  • Use uma função de janela. As funções de janela são muitas vezes mais eficientes que a utilização de uma união cruzada. Para mais informações, consulte Funções de janela.

Evitar instruções DML que atualizam ou inserem linhas individuais

Prática recomendada: evite instruções DML que atualizam ou inserem linhas únicas. Agrupe em lote suas atualizações e inserções.

O uso de instruções DML específicas de pontos é uma tentativa de tratar o BigQuery como um processamento de transações on-line (OLTP, na sigla em inglês). O BigQuery se concentra no processamento analítico on-line (OLAP, na sigla em inglês) usando varreduras de tabelas em vez de pesquisas de pontos. Se você precisa de comportamento semelhante ao do OLTP (atualizações ou inserções de linha única), considere um banco de dados projetado para comportar casos de uso de OLTP, como o Cloud SQL.

As instruções DML do BigQuery destinam-se a atualizações em massa. As declarações DML UPDATE e DELETE no BigQuery são orientadas para reescrever seus dados periodicamente, e não para mutações de uma única linha. A instrução DML INSERT deve ser utilizada com moderação. As inserções consomem as mesmas cotas de modificação que os jobs de carregamento. Se seu caso de uso envolver inserções frequentes de uma única linha, considere transmitir seus dados.

Se o agrupamento de suas declarações UPDATE resultar em muitas tuplas em consultas muito longas, você pode abordar o limite de comprimento da consulta de 256 KB. Para contornar esse limite, considere se as atualizações podem ser tratadas com base em um critério lógico em vez de uma série de substituições de tuplas diretas.

Por exemplo, você pode carregar seu conjunto de registros de substituição em outra tabela e, em seguida, escrever a declaração DML para atualizar todos os valores na tabela original se as colunas não atualizadas corresponderem. Por exemplo, se os dados originais estivessem na tabela t e as atualizações estivessem na tabela u, a consulta teria a aparência a seguir:

UPDATE
  dataset.t t
SET
  my_column = u.my_column
FROM
  dataset.u u
WHERE
  t.my_key = u.my_key

Filtrar dados para dados distorcidos

Prática recomendada: se a sua consulta processa chaves que estão muito inclinadas para alguns valores, filtre os dados o quanto antes.

O desvio de partição, às vezes chamado de desvio de dados, ocorre quando os dados são divididos em partições de tamanhos muito desiguais. Isso cria um desequilíbrio no volume de dados enviados entre slots. Por não ser possível compartilhar partições entre slots, se uma partição for especialmente grande, ela poderá desacelerar ou até mesmo causar falha no slot que a processa.

As partições ficam grandes quando a chave de partição tem um valor que ocorre com mais frequência do que qualquer outro valor. Por exemplo, agrupar por um campo user_id onde há muitas entradas para guest ou NULL.

Quando os recursos de um slot ficam sobrecarregados, ocorre um erro resources exceeded. Alcançar o limite aleatório para um slot (2 TB na memória compactada) também faz com que essa aleatoriedade grave no disco e cause mais impactos ao desempenho. Os clientes com preços baseados em capacidade podem aumentar o número de slots alocados.

Se você examinar o gráfico de execução de consulta e identificar uma diferença significativa entre os tempos de computação médio e máximo, saberá que seus dados podem estar distorcidos.

Para evitar problemas de desempenho resultantes da distorção de dados, siga estas recomendações:

  • Use uma função agregada aproximada, como APPROX_TOP_COUNT, para determinar se os dados estão desviados.
  • Filtre seus dados o mais cedo possível.

Uniões desequilibradas

O desvio de dados também pode aparecer quando você usa cláusulas JOIN. Como o BigQuery mistura os dados em cada lado da junção, todos os dados com a mesma chave de união vão para o mesmo fragmento. Essa mistura pode sobrecarregar o slot.

Para evitar problemas de desempenho associados a junções desequilibradas, execute as seguintes tarefas:

  • Filtre previamente as linhas da tabela com a chave desequilibrada.
  • Se possível, divida a consulta em duas.

  • Use a instrução SELECT DISTINCT ao especificar uma subconsulta na cláusula WHERE para avaliar valores de campo exclusivos apenas uma vez.

    Por exemplo, em vez de usar a cláusula a seguir que contém uma instrução SELECT:

    table1.my_id NOT IN (
  SELECT my_id
  FROM table2
  )

    Use uma cláusula que contenha uma instrução SELECT DISTINCT:

    table1.my_id NOT IN (
  SELECT DISTINCT my_id
  FROM table2
  )

Usar nomes de alias para colunas com nomes semelhantes

Prática recomendada: use aliases de coluna e tabela ao trabalhar com colunas de nomes semelhantes em consultas, incluindo subconsultas.

Os aliases ajudam a identificar quais colunas e tabelas são referenciadas, além da referência inicial da coluna. O uso de aliases pode ajudá-lo a entender e resolver problemas na consulta SQL, incluindo encontrar as colunas usadas em subconsultas.

Especificar restrições no esquema da tabela

Quando os dados da tabela contiverem restrições, especifique as restrições no esquema da tabela. O mecanismo de consulta pode otimizar planos de consulta usando restrições de tabela.

Especificar restrições de chave primária e chave externa

Especifique as restrições de chave no esquema da tabela quando os dados dela atenderem aos requisitos de integridade de dados das restrições de chave primária ou chave externa. O mecanismo de consulta pode usar as restrições de chave para otimizar os planos de consulta. Encontre informações detalhadas na postagem do blog Mesclar otimizações com chaves primárias e chaves externas do BigQuery.

O BigQuery não verifica automaticamente a integridade de dados. Por isso, garanta que eles atendam às restrições especificadas no esquema da tabela. Se você não mantiver a integridade de dados em tabelas com restrições especificadas, os resultados da consulta poderão ser imprecisos.

A seguir