Plano de consulta e cronograma

Incorporado a jobs de consulta, o BigQuery inclui informações de cronologia e plano de consulta diagnóstica. Isso é semelhante às informações fornecidas por instruções como EXPLAIN em outros sistemas analíticos e de banco de dados. Essas informações podem ser recuperadas das respostas da API de métodos como jobs.get.

Para consultas de longa duração, o BigQuery atualizará essas estatísticas periodicamente. Essas atualizações ocorrem independentemente da taxa em que o status do job é pesquisado, mas não costumam ser com mais frequência do que a cada 30 segundos. Além disso, os jobs de consulta que não usam recursos de execução, como solicitações de simulação ou resultados que podem ser exibidos a partir de resultados armazenados em cache, não incluirão as informações de diagnóstico adicionais, embora outras estatísticas possam ser presente.

Contexto

Quando o BigQuery executa uma consulta, ele converte o SQL em um gráfico de execução que consiste em estágios. Os estágios são compostos de etapas, as operações elementares que executam a lógica da consulta. O BigQuery usa uma arquitetura paralela altamente distribuída que executa estágios em paralelo para reduzir a latência. Os estágios se comunicam entre si usando o embaralhamento, uma arquitetura de memória distribuída rápida.

O plano de consulta usa os termos unidades de trabalho e workers para descrever o paralelismo de estágio. Em outros locais do BigQuery, é possível encontrar o termo slot, que é uma representação abstrata de várias facetas da execução de consulta, incluindo recursos de computação, memória e E/S. Os slots executam as unidades de trabalho individuais de um estágio em paralelo. As estatísticas de jobs de nível superior fornecem o custo de consulta individual usando totalSlotMs com base nessa contabilização abstrata.

Outra propriedade importante da execução de consultas é que o BigQuery pode modificar o plano de consulta enquanto uma consulta está em execução. Por exemplo, o BigQuery introduz estágios de repartição para melhorar a distribuição de dados entre os workers de consulta, o que melhora o paralelismo e reduz a latência da consulta.

Além do plano de consulta, os jobs de consulta também exibem uma linha do tempo de execução, que fornece uma contabilização das unidades de trabalho concluídas, pendentes e ativas. Como uma consulta pode ter vários cenários com workers ativos simultaneamente, o cronograma se destina a mostrar o progresso geral dela.

Acessar o gráfico de execução com o console do Google Cloud

No console doGoogle Cloud , é possível ver detalhes do plano de consulta de uma consulta concluída clicando no botão Detalhes da execução.

Informações do plano de consulta

Na resposta da API, os planos de consulta são representados como uma lista de estágios de consulta. Cada item na lista mostra estatísticas da visão geral por estágio, informações detalhadas da etapa e classificações de duração dos estágios. Nem todos os detalhes são renderizados no console do Google Cloud , mas todos podem estar presentes nas respostas da API.

Entender o gráfico de execução

No console do Google Cloud , é possível ver os detalhes do plano de consulta clicando na guia Gráfico de execução.

O painel Gráfico de execução é organizado da seguinte forma:

• No centro, está o gráfico de execução. Ele mostra as etapas como nós e a memória de embaralhamento trocada entre as etapas como arestas.
• O painel à esquerda tem o mapa de calor do texto da consulta. Ela mostra o texto principal da consulta executada, além de todas as visualizações referenciadas.
• O painel direito tem os detalhes da consulta ou do estágio.

Como navegar no gráfico de execução

O gráfico de execução aplica um esquema de cores aos nós com base no tempo de slot, em que os nós com um vermelho mais escuro levam mais tempo de slot em relação ao restante dos estágios no gráfico.

Para navegar pelo gráfico de execução, você pode:

• Clique e mantenha pressionado o plano de fundo do gráfico para se deslocar para diferentes áreas dele.
• Use a roda de rolagem do mouse para aumentar e diminuir o zoom do gráfico.
• Clique e mantenha pressionado o minimapa no canto superior direito para se deslocar para diferentes áreas do gráfico.

Clique em uma fase no gráfico para ver os detalhes dela. Os detalhes da fase têm:

• Estatísticas. Consulte Visão geral do estágio para detalhes sobre as estatísticas.
• Detalhes da etapa. As etapas descrevem as operações individuais que executam a lógica da consulta.

Detalhes da etapa

Os estágios são compostos por etapas, as operações individuais que executam a lógica da consulta. As etapas têm subetapas que descrevem o que a etapa fez em pseudocódigo. As subetapas usam variáveis para descrever as relações entre as etapas. As variáveis começam com um cifrão seguido por um número exclusivo. Os números de variáveis não são compartilhados entre as etapas.

A imagem a seguir mostra as etapas de um estágio:

Veja um exemplo das etapas de um estágio:

  READ
  $30:l_orderkey, $31:l_quantity
  FROM lineitem

  AGGREGATE
  GROUP BY $100 := $30
  $70 := SUM($31)

  WRITE
  $100, $70
  TO __stage00_output
  BY HASH($100)

As etapas do exemplo descrevem o seguinte:

• A fase leu as colunas l_orderkey e l_quantity da tabela lineitem e armazenou os valores nas variáveis $30 e $31, respectivamente.
• O estágio agregou as variáveis $30 e $31, armazenando as agregações nas variáveis $100 e $70, respectivamente.
• A etapa gravou os resultados das variáveis $100 e $70 para embaralhar. O estágio ordenou os resultados na memória de embaralhamento com base em US $100.

Consulte Interpretar e otimizar etapas para ver todos os detalhes sobre o tipo de etapas e como otimizá-las.

O BigQuery pode truncar subetapas quando o gráfico de execução da consulta for complexo o suficiente para que o fornecimento de subetapas completas cause problemas de tamanho de payload ao recuperar informações da consulta.

Mapa de calor do texto da consulta

O BigQuery pode mapear algumas etapas do estágio para partes do texto da consulta. O mapa de calor do texto da consulta mostra todo o texto da consulta correspondente com as etapas do estágio. Ele destaca o texto da consulta com base no tempo total de slot das fases em que as etapas têm texto de consulta mapeado.

A imagem a seguir mostra o texto da consulta destacado:

Manter o ponteiro sobre uma parte mapeada do texto da consulta mostra uma dica que lista todas as etapas do estágio que são mapeadas para o texto da consulta, além do tempo de slot do estágio. Ao clicar em um texto de consulta mapeado, você seleciona a fase no gráfico de execução e abre os detalhes dela no painel direito.

Uma única parte do texto da consulta pode ser mapeada para vários estágios. A dica lista cada estágio mapeado e o tempo de slot dele. Clicar no texto da consulta destaca os estágios correspondentes e esmaece o restante do gráfico. Em seguida, clique em uma etapa específica para ver os detalhes dela.

A imagem a seguir mostra como o texto da consulta se relaciona aos detalhes da etapa:

Na seção Detalhes da etapa de um estágio, se uma etapa for mapeada para o texto da consulta, ela terá um ícone de código. Ao clicar no ícone de código, a parte mapeada do texto da consulta à esquerda é destacada.

É importante lembrar que a cor do mapa de calor é baseada no tempo de slot de todo o estágio. Como o BigQuery não mede o tempo de slot das etapas, o mapa de calor não representa o tempo de slot real para essa parte específica do texto da consulta mapeada. Na maioria dos casos, uma fase executa apenas uma etapa complexa, como uma mesclagem ou agregação. Portanto, a cor do mapa de calor é adequada. No entanto, quando um estágio é composto por etapas que realizam várias operações complexas, a cor do mapa de calor pode representar em excesso o tempo de slot real no mapa de calor. Nesses casos, é importante entender as outras etapas que compõem o estágio para ter uma compreensão mais completa do desempenho da consulta.

Se uma consulta usar visualizações e as etapas do estágio tiverem mapeamentos para o texto de consulta de uma visualização, o mapa de calor do texto de consulta vai mostrar o nome da visualização e o texto de consulta da visualização com os mapeamentos. No entanto, se a visualização for excluída ou se você perder a permissão do IAM bigquery.tables.get na visualização, o mapa de calor do texto da consulta não mostrará os mapeamentos das etapas do estágio para a visualização.

Visão geral do cenário

Os campos de visão geral de cada estágio podem incluir o seguinte:

Campo da API	Descrição
id	Código numérico exclusivo do cenário.
name	Nome simples de resumo do cenário. O steps dentro do estágio fornece detalhes adicionais sobre as etapas de execução.
status	Status de execução do cenário. Os estados possíveis incluem PENDING, RUNNING, COMPLETE, FAILED e CANCELLED.
inputStages	Uma lista dos códigos que formam o gráfico de dependência do cenário. Por exemplo, um cenário JOIN geralmente precisa de dois cenários dependentes que preparam os dados nos lados esquerdo e direito do relacionamento JOIN.
startMs	Carimbo de data/hora, em milissegundos da época, que representa quando o primeiro trabalhador no cenário iniciou a execução.
endMs	Carimbo de data/hora, em milissegundos de época, que representa quando o último trabalhador concluiu a execução.
steps	Lista mais detalhada de etapas de execução no cenário. Consulte a próxima seção para mais informações.
recordsRead	Tamanho de entrada do cenário como número de registros, em todos os trabalhadores do cenário.
recordsWritten	Tamanho de saída do cenário como número de registros, em todos os trabalhadores do cenário.
parallelInputs	Número de unidades de trabalho carregáveis em paralelo do cenário. Dependendo do estágio e da consulta, isso pode representar o número de segmentos colunares em uma tabela ou o número de partições em um embaralhamento intermediário.
completedParallelInputs	Número de unidades de trabalho dentro do cenário que foram concluídas. Para algumas consultas, nem todas as entradas dentro de um cenário precisam ser concluídas para que o cenário seja concluído.
shuffleOutputBytes	Representa o total de bytes gravados em todos os trabalhadores em um cenário de consulta.
shuffleOutputBytesSpilled	Consultas que transmitem dados significativos entre cenários podem precisar recorrer à transmissão baseada em disco. A estatística de bytes espalhados comunica o volume de dados espalhados no disco. Depende de um algoritmo de otimização para que não seja determinístico para qualquer consulta.

Classificação de cronologia por estágio

Os estágios de consulta fornecem classificações de duração dos estágios, tanto em termos absolutos quanto relativos. Como cada estágio de execução representa o trabalho realizado por um ou mais workers independentes, as informações são fornecidas no tempo médio e no pior cenário. Esses tempos representam o desempenho médio de todos os workers em um estágio, assim como o desempenho mais lento de cauda longa dos workers em uma determinada classificação. Além disso, os tempos médio e máximo são desmembrados nas representações absoluta e relativa. Para estatísticas com base em proporção, os dados são fornecidos como uma fração do maior tempo gasto por qualquer worker em qualquer segmento.

O console do Google Cloud apresenta a cronologia dos cenários usando as representações de cronologia relativa.

As informações de cronologia de cenário são relatadas da seguinte maneira:

Cronologia relativa	Cronologia absoluta	Numerador de proporção
waitRatioAvg	waitMsAvg	Tempo que o trabalho médio passou aguardando ser programado.
waitRatioMax	waitMsMax	Tempo que o trabalho mais lento passou aguardando ser programado.
readRatioAvg	readMsAvg	Tempo que o trabalho médio passou lendo dados de entrada.
readRatioMax	readMsMax	Tempo que o trabalho mais lento passou lendo dados de entrada.
computeRatioAvg	computeMsAvg	Tempo que o worker médio passou ligado à CPU.
computeRatioMax	computeMsMax	Tempo que o worker mais lento passou ligado à CPU.
writeRatioAvg	writeMsAvg	Tempo que o trabalho médio passou gravando dados de saída.
writeRatioMax	writeMsMax	Tempo que o worker mais lento passou gravando dados de saída.

Visão geral da etapa

As etapas contêm as operações que cada worker executa em uma fase, apresentadas como uma lista ordenada de operações. Cada operação de etapa tem uma categoria, e algumas operações fornecem informações mais detalhadas. As categorias de operação presentes no plano de consulta incluem:

Categoria da etapa	Descrição
READ	Uma leitura de uma ou mais colunas de uma tabela de entrada ou de um embaralhamento intermediário. Somente as primeiras dezesseis colunas lidas serão retornadas nos detalhes da etapa.
WRITE	Uma gravação de uma ou mais colunas em uma tabela de saída ou em um embaralhamento intermediário. Para saídas particionadas HASH de um estágio, isso também inclui as colunas usadas como a chave de partição.
COMPUTE	Avaliação de expressões e funções SQL.
FILTER	Usado pelas cláusulas WHERE, OMIT IF e HAVING.
SORT	Operação ORDER BY que inclui as chaves de coluna e a ordem de classificação.
AGGREGATE	Implementa agregações para cláusulas como GROUP BY ou COUNT, entre outras.
LIMIT	Implementa a cláusula LIMIT.
JOIN	Implementa mesclagens para cláusulas como JOIN, entre outras. Inclui o tipo de mesclagem e, possivelmente, as condições de mesclagem.
ANALYTIC_FUNCTION	Uma invocação de uma função de janela (também conhecida como "função analítica").
USER_DEFINED_FUNCTION	Uma invocação para uma função definida pelo usuário.
UPDATE	Modifica as linhas de uma tabela de destino para uma instrução UPDATE. Inclui as colunas modificadas e a tabela de destino.
DELETE	Remove linhas de uma tabela de destino para uma instrução DELETE. Inclui as colunas referenciadas e a tabela de destino.
MERGE	Modifica linhas em uma tabela de destino para uma instrução MERGE. Inclui as colunas modificadas e a tabela de destino.
EXPORT	Grava colunas de saída em uma tabela de destino para instruções de linguagem de manipulação de dados (DML) ou exportações de resultados de consulta.

Interpretar e otimizar etapas

As seções a seguir explicam como interpretar as etapas em um plano de consulta e fornecem maneiras de otimizar suas consultas.

READ etapa

A etapa READ significa que uma fase está acessando dados para processamento. Os dados podem ser lidos diretamente das tabelas referenciadas em uma consulta ou da memória de embaralhamento. Quando os dados de uma etapa anterior são lidos, o BigQuery lê os dados da memória de embaralhamento. A quantidade de dados verificados afeta o custo ao usar slots sob demanda e o desempenho ao usar reservas.

Possíveis problemas de desempenho

• Verificação grande de tabela não particionada:se a consulta precisar apenas de uma pequena parte dos dados, isso pode indicar que uma verificação de tabela é ineficiente. O particionamento pode ser uma boa estratégia de otimização.
• Verificação de uma tabela grande com uma proporção de filtro pequena:isso sugere que o filtro não está reduzindo efetivamente os dados verificados. Revise as condições do filtro.
• Bytes de embaralhamento transferidos para o disco:isso sugere que os dados não estão armazenados de maneira eficaz usando técnicas de otimização, como clustering, que podem manter dados semelhantes em clusters.

Otimizar

• Filtragem segmentada:use cláusulas WHERE estrategicamente para filtrar dados irrelevantes o mais cedo possível na consulta. Isso reduz a quantidade de dados que precisam ser processados pela consulta.
• Particionamento e clustering:o BigQuery usa o particionamento e o clustering de tabelas para localizar segmentos de dados específicos com eficiência. Verifique se as tabelas estão particionadas e em cluster com base nos padrões de consulta típicos para minimizar os dados verificados durante as etapas de READ.
• Selecione as colunas relevantes:evite usar instruções SELECT *. Em vez disso, selecione colunas específicas ou use SELECT * EXCEPT para evitar a leitura de dados desnecessários.
• Visualizações materializadas:as visualizações materializadas podem pré-computar e armazenar agregações usadas com frequência, o que pode reduzir a necessidade de ler tabelas base durante as etapas READ para consultas que usam essas visualizações.

COMPUTE etapa

Na etapa COMPUTE, o BigQuery realiza as seguintes ações nos dados:

• Avalia expressões nas cláusulas SELECT, WHERE, HAVING e outras da consulta, incluindo cálculos, comparações e operações lógicas.
• Executa funções SQL integradas e funções definidas pelo usuário.
• Filtra linhas de dados com base em condições na consulta.

Otimizar

O plano de consulta pode revelar gargalos na etapa COMPUTE. Procure estágios com cálculos extensos ou um alto número de linhas processadas.

• Correlacione a etapa COMPUTE com o volume de dados:se uma etapa mostrar uma computação significativa e processar um grande volume de dados, ela pode ser uma boa candidata para otimização.
• Dados distorcidos:para estágios em que o máximo de computação é significativamente maior do que a média, isso indica que o estágio gastou uma quantidade desproporcional de tempo processando algumas fatias de dados. Considere analisar a distribuição de dados para ver se há desvio de dados.
• Considere os tipos de dados:use tipos de dados apropriados para suas colunas. Por exemplo, o uso de números inteiros, datas e horas e carimbos de data/hora em vez de strings pode melhorar o desempenho.

WRITE etapa

As etapas de WRITE ocorrem para dados intermediários e saída final.

• Gravação na memória de embaralhamento:em uma consulta de vários estágios, a etapa WRITE geralmente envolve o envio dos dados processados para outro estágio para processamento adicional. Isso é comum para a memória de embaralhamento, que combina ou agrega dados de várias fontes. Os dados gravados durante essa fase costumam ser um resultado intermediário, não a saída final.
• Saída final:o resultado da consulta é gravado no destino ou em uma tabela temporária.

Particionamento de hash

Quando uma fase no plano de consulta grava dados em uma saída particionada por hash, o BigQuery grava as colunas incluídas na saída e a coluna escolhida como a chave de partição.

Otimizar

Embora a etapa WRITE em si não seja otimizada diretamente, entender o papel dela pode ajudar a identificar possíveis gargalos em etapas anteriores:

• Minimizar os dados gravados:concentre-se em otimizar as etapas anteriores com filtragem e agregação para reduzir a quantidade de dados gravados durante essa etapa.

• Particionamento:a gravação se beneficia muito do particionamento de tabelas. Se os dados gravados estiverem confinados a partições específicas, o BigQuery poderá realizar gravações mais rápidas.

  Se a instrução DML tiver uma cláusula WHERE com uma condição estática em uma coluna de partição de tabela, o BigQuery vai modificar apenas as partições de tabela relevantes.

• Compensações de desnormalização:às vezes, a desnormalização pode levar a conjuntos de resultados menores na etapa intermediária WRITE. No entanto, há desvantagens, como o aumento do uso do armazenamento e desafios de consistência de dados.

JOIN etapa

Na etapa JOIN, o BigQuery combina dados de duas fontes. As junções podem incluir condições de junção. As mesclagens exigem muitos recursos. Ao mesclar dados grandes no BigQuery, as chaves de mesclagem são embaralhadas de forma independente para se alinharem no mesmo slot, de modo que a mesclagem seja realizada localmente em cada slot.

O plano de consulta para a etapa JOIN normalmente revela os seguintes detalhes:

• Padrão de mesclagem:indica o tipo de mesclagem usado. Cada tipo define quantas linhas das tabelas mescladas são incluídas no conjunto de resultados.
• Colunas de mesclagem:são as colunas usadas para corresponder linhas entre as fontes de dados. A escolha das colunas é crucial para o desempenho da mesclagem.

Padrões de mesclagem

• Mesclagem de transmissão:quando uma tabela, normalmente a menor, cabe na memória de um único nó de trabalho ou slot, o BigQuery pode transmiti-la para todos os outros nós para realizar a mesclagem com eficiência. Procure JOIN EACH WITH ALL nos detalhes da etapa.
• Mesclagem de hash:quando as tabelas são grandes ou uma mesclagem de transmissão não é adequada, uma mesclagem de hash pode ser usada. O BigQuery usa operações de hash e embaralhamento para embaralhar as tabelas esquerda e direita, de modo que as chaves correspondentes acabem no mesmo slot para realizar uma mesclagem local. As mesclagens de hash são uma operação cara, porque os dados precisam ser movidos, mas elas permitem a correspondência eficiente de linhas entre hashes. Procure JOIN EACH WITH EACH nos detalhes da etapa.
• Mesclagem automática:um antipadrão SQL em que uma tabela é mesclada com ela mesma.
• Correlação:um antipadrão SQL que pode causar problemas de desempenho significativos porque gera dados de saída maiores do que as entradas.
• Mesclagem distorcida:a distribuição de dados na chave de mesclagem em uma tabela é muito distorcida e pode levar a problemas de desempenho. Procure casos em que o tempo máximo de computação seja muito maior do que o tempo médio de computação no plano de consulta. Para mais informações, consulte Mesclagem de alta cardinalidade e Distorção de partição.

Depuração

• Grande volume de dados:se o plano de consulta mostrar uma quantidade significativa de dados processados durante a etapa JOIN, investigue a condição e as chaves de mesclagem. Considere filtrar ou usar chaves de mesclagem mais seletivas.
• Distribuição de dados distorcida:analise a distribuição de dados das chaves de mesclagem. Se uma tabela estiver muito distorcida, use estratégias como dividir a consulta ou fazer uma pré-filtragem.
• Mesclagens de alta cardinalidade:mesclagens que produzem significativamente mais linhas do que o número de linhas de entrada à esquerda e à direita podem reduzir drasticamente o desempenho da consulta. Evite mesclagens que produzam um número muito grande de linhas.
• Ordenação incorreta da tabela:verifique se você escolheu o tipo de mesclagem adequado, como INNER ou LEFT, e ordenou as tabelas da maior para a menor com base nos requisitos da consulta.

Otimizar

• Chaves de mesclagem seletivas:para chaves de mesclagem, use INT64 em vez de STRING quando possível. As comparações STRING são mais lentas do que as comparações INT64 porque comparam cada caractere em uma string. Os números inteiros exigem apenas uma comparação.
• Filtrar antes de mesclar:aplique filtros de cláusula WHERE em tabelas individuais antes da mesclagem. Isso reduz a quantidade de dados envolvidos na operação de mesclagem.
• Evite funções em colunas de mesclagem:evite chamar funções em colunas de mesclagem. Em vez disso, padronize os dados da tabela durante o processo de ingestão ou pós-ingestão usando pipelines de SQL de ELT. Essa abordagem elimina a necessidade de modificar as colunas de mesclagem dinamicamente, o que permite mesclagens mais eficientes sem comprometer a integridade de dados.
• Evite mesclagens automáticas:elas são usadas com frequência para calcular relacionamentos dependentes de linha. No entanto, as mesclagens automáticas podem quadruplicar o número de linhas de saída, o que pode causar problemas de desempenho. Em vez de depender de mesclagens automáticas, considere usar funções de janela (analíticas).
• Tabelas grandes primeiro:embora o otimizador de consulta SQL possa determinar qual tabela deve ficar em qual lado da mesclagem, ordene as tabelas mescladas adequadamente. Recomenda-se colocar a tabela maior primeiro, seguida da menor, e depois por ordem decrescente de tamanho.
• Desnormalização:em alguns casos, a desnormalização estratégica de tabelas (adição de dados redundantes) pode eliminar as junções. No entanto, essa abordagem tem desvantagens de armazenamento e consistência de dados.
• Particionamento e clustering:particionar tabelas com base em chaves de mesclagem e fazer o clustering de dados colocalizados pode acelerar significativamente as mesclagens, permitindo que o BigQuery segmente partições de dados relevantes.
• Otimização de mesclagens distorcidas:para evitar problemas de desempenho associados a mesclagens distorcidas, pré-filtre os dados da tabela o quanto antes ou divida a consulta em duas ou mais consultas.

AGGREGATE etapa

Na etapa AGGREGATE, o BigQuery agrega e agrupa dados.

Depuração

• Detalhes da fase:verifique o número de linhas de entrada e saída da agregação e o tamanho do embaralhamento para determinar a redução de dados alcançada pela etapa de agregação e se houve embaralhamento de dados.
• Tamanho do embaralhamento:um tamanho grande de embaralhamento pode indicar que uma quantidade significativa de dados foi movida entre os nós de trabalho durante a agregação.
• Verifique a distribuição de dados:verifique se os dados estão distribuídos de maneira uniforme entre as partições. A distribuição de dados distorcida pode levar a cargas de trabalho desequilibradas na etapa de agregação.
• Agregações de avaliações:analise as cláusulas de agregação para confirmar se elas são necessárias e eficientes.

Otimizar

• Clustering:agrupe as tabelas em colunas usadas com frequência em GROUP BY, COUNT ou outras cláusulas de agregação.
• Particionamento:escolha uma estratégia de partição que se alinhe aos seus padrões de consulta. Considere usar tabelas particionadas por tempo de processamento para reduzir a quantidade de dados verificados durante a agregação.
• Agregação antecipada:se possível, realize as agregações antes no pipeline de consulta. Isso pode reduzir a quantidade de dados que precisam ser processados durante a agregação.
• Otimização de embaralhamento:se o embaralhamento for um gargalo, busque maneiras de minimizá-lo. Por exemplo, desnormalizar tabelas ou usar clustering para colocalizar dados relevantes.

Casos extremos

• Agregações DISTINCT:consultas com agregações DISTINCT podem ser computacionalmente caras, especialmente em grandes conjuntos de dados. Considere alternativas como APPROX_COUNT_DISTINCT para resultados aproximados.
• Grande número de grupos:se a consulta produzir um grande número de grupos, ela poderá consumir uma quantidade significativa de memória. Nesses casos, pense em limitar o número de grupos ou usar uma estratégia de agregação diferente.

REPARTITION etapa

REPARTITION e COALESCE são técnicas de otimização que o BigQuery aplica diretamente aos dados embaralhados na consulta.

• REPARTITION: essa operação visa reequilibrar a distribuição de dados entre os nós de trabalho. Suponha que, após a mistura, um nó de trabalho acabe com uma quantidade de dados desproporcionalmente grande. A etapa REPARTITION redistribui os dados de maneira mais uniforme, impedindo que um único worker se torne um gargalo. Isso é especialmente importante para operações computacionalmente intensivas, como mesclagens.
• COALESCE:essa etapa acontece quando você tem muitos buckets pequenos de dados após o embaralhamento. A etapa COALESCE combina esses buckets em buckets maiores, reduzindo a sobrecarga associada ao gerenciamento de vários pequenos dados. Isso pode ser especialmente útil ao lidar com conjuntos de resultados intermediários muito pequenos.

Se você encontrar as etapas REPARTITION ou COALESCE no plano de consulta, isso não significa necessariamente que há um problema com a consulta. Geralmente, isso é um sinal de que o BigQuery está otimizando proativamente a distribuição de dados para melhorar o desempenho. No entanto, se você vir essas operações repetidamente, isso pode indicar que seus dados estão inerentemente distorcidos ou que sua consulta está causando uma mistura excessiva de dados.

Otimizar

Para reduzir o número de etapas de REPARTITION, tente o seguinte:

• Distribuição de dados:verifique se as tabelas estão particionadas e agrupadas em cluster de maneira eficaz. Dados bem distribuídos reduzem a probabilidade de desequilíbrios significativos após o embaralhamento.
• Estrutura da consulta:analise a consulta para identificar possíveis fontes de desvio de dados. Por exemplo, há filtros ou mesclagens altamente seletivos que resultam em um pequeno subconjunto de dados sendo processado em um único worker?
• Estratégias de mesclagem:teste diferentes estratégias de mesclagem para ver se elas levam a uma distribuição de dados mais equilibrada.

Para reduzir o número de etapas de COALESCE, tente o seguinte:

• Estratégias de agregação:considere realizar agregações antes no pipeline de consulta. Isso pode ajudar a reduzir o número de pequenos conjuntos de resultados intermediários que podem causar etapas COALESCE.
• Volume de dados:se você estiver lidando com conjuntos de dados muito pequenos, o COALESCE pode não ser uma preocupação significativa.

Não otimize demais. A otimização prematura pode tornar as consultas mais complexas sem gerar benefícios significativos.

UPDATE, DELETE e MERGE passos

As etapas UPDATE, DELETE e MERGE aparecem em fases que executam instruções de linguagem de manipulação de dados (DML) em uma tabela de destino.

Os detalhes da etapa geralmente incluem as seguintes subetapas:

• Colunas:as colunas lidas ou gravadas na tabela de destino.
• Tabela de destino (FROM ou INTO): a tabela de destino modificada pela instrução, usando FROM para as etapas UPDATE e DELETE e INTO para as etapas MERGE.

EXPORT etapa

A etapa EXPORT grava os dados de saída em uma tabela de destino. Essa etapa geralmente aparece nos estágios finais das instruções DML, como INSERT, UPDATE, DELETE e MERGE, e em consultas que materializam resultados em uma tabela de destino.

Os detalhes da etapa geralmente incluem as seguintes subetapas:

• Colunas de saída:a lista de variáveis gravadas na tabela de destino.
• Tabela de destino (TO): a tabela de destino que recebe as linhas exportadas.

Explicação para consultas federadas

As consultas federadas permitem enviar uma instrução de consulta a uma fonte de dados externa usando a função EXTERNAL_QUERY. As consultas federadas estão sujeitas à técnica de otimização conhecida como pushdowns SQL, e o plano de consulta mostra as operações enviadas para a fonte de dados externa, se houver. Por exemplo, se você executar a seguinte consulta:

SELECT id, name
FROM EXTERNAL_QUERY("<connection>", "SELECT * FROM company")
WHERE country_code IN ('ee', 'hu') AND name like '%TV%'

O plano de consulta mostrará as seguintes etapas da fase:

$1:id, $2:name, $3:country_code
FROM table_for_external_query_$_0(
  SELECT id, name, country_code
  FROM (
    /*native_query*/
    SELECT * FROM company
  )
  WHERE in(country_code, 'ee', 'hu')
)
WHERE and(in($3, 'ee', 'hu'), like($2, '%TV%'))
$1, $2
TO __stage00_output

Nesse plano, table_for_external_query_$_0(...) representa a função EXTERNAL_QUERY. Entre os parênteses, é possível ver a consulta que a fonte de dados externa executa. Com base nisso, é possível notar que:

• Uma fonte de dados externa retorna apenas três colunas selecionadas.
• Uma fonte de dados externa retorna apenas as linhas em que country_code é 'ee' ou 'hu'.
• O operador LIKE não é pushdown e é avaliado pelo BigQuery.

Para fins de comparação, se não houver push-downs, o plano de consulta mostrará as seguintes etapas do estágio:

$1:id, $2:name, $3:country_code
FROM table_for_external_query_$_0(
  SELECT id, name, description, country_code, primary_address, secondary address
  FROM (
    /*native_query*/
    SELECT * FROM company
  )
)
WHERE and(in($3, 'ee', 'hu'), like($2, '%TV%'))
$1, $2
TO __stage00_output

Desta vez, uma fonte de dados externa retorna todas as colunas e linhas da tabela company, e o BigQuery realiza a filtragem.

Metadados do cronograma

O cronograma da consulta informa a evolução em momentos específicos no tempo, fornecendo visualizações instantâneas do progresso geral da consulta. O cronograma é representado por uma série de amostras que informam os seguintes detalhes:

Campo	Descrição
elapsedMs	Milissegundos decorridos desde o início da execução da consulta.
totalSlotMs	Uma representação cumulativa dos milissegundos de slot usados pela consulta.
pendingUnits	Total de unidades de trabalho programadas que aguardam execução.
activeUnits	Total de unidades de trabalho ativas sendo processadas por trabalhadores.
completedUnits	Total de unidades de trabalho que foram concluídas durante a execução dessa consulta.

Um exemplo de consulta

A consulta a seguir conta o número de linhas no conjunto de dados público de Shakespeare e tem uma segunda contagem condicional que restringe os resultados às linhas que fazem referência ao termo "Hamlet":

SELECT
  COUNT(1) as rowcount,
  COUNTIF(corpus = 'hamlet') as rowcount_hamlet
FROM `publicdata.samples.shakespeare`

Clique em Detalhes da execução para ver o plano de consulta:

Os indicadores de cor mostram os tempos relativos de todas as etapas em todos os estágios.

Se quiser saber mais sobre as etapas dos estágios de execução, clique em para expandir os detalhes de cada um:

Nesse exemplo, o maior tempo em qualquer segmento foi aquele em que o único worker no Estágio 01 passou aguardando a conclusão do Estágio 00. Isso ocorre porque o Estágio 01 era dependente da entrada do Estágio 00 e não podia ser iniciado até que o primeiro estágio gravasse a saída no embaralhamento intermediário.

Error Reporting

É possível que os jobs de consulta falhem no meio da execução. Como as informações do plano são atualizadas periodicamente, é possível observar, no gráfico de execução, onde ocorreu a falha. No console do Google Cloud , os estágios bem-sucedidos ou com falha têm uma marca de seleção ou um ponto de exclamação ao lado do nome.

Para mais informações sobre como interpretar e corrigir erros, consulte o Guia de solução de problemas.

Representação de amostra de API

As informações do plano de consulta são incorporadas às informações de resposta do job e podem ser recuperadas chamando jobs.get. Por exemplo, o trecho a seguir de uma resposta JSON para um job que retorna a consulta de amostra hamlet mostra o plano de consulta e as informações da linha do tempo.

"statistics": {
  "creationTime": "1576544129234",
  "startTime": "1576544129348",
  "endTime": "1576544129681",
  "totalBytesProcessed": "2464625",
  "query": {
    "queryPlan": [
      {
        "name": "S00: Input",
        "id": "0",
        "startMs": "1576544129436",
        "endMs": "1576544129465",
        "waitRatioAvg": 0.04,
        "waitMsAvg": "1",
        "waitRatioMax": 0.04,
        "waitMsMax": "1",
        "readRatioAvg": 0.32,
        "readMsAvg": "8",
        "readRatioMax": 0.32,
        "readMsMax": "8",
        "computeRatioAvg": 1,
        "computeMsAvg": "25",
        "computeRatioMax": 1,
        "computeMsMax": "25",
        "writeRatioAvg": 0.08,
        "writeMsAvg": "2",
        "writeRatioMax": 0.08,
        "writeMsMax": "2",
        "shuffleOutputBytes": "18",
        "shuffleOutputBytesSpilled": "0",
        "recordsRead": "164656",
        "recordsWritten": "1",
        "parallelInputs": "1",
        "completedParallelInputs": "1",
        "status": "COMPLETE",
        "steps": [
          {
            "kind": "READ",
            "substeps": [
              "$1:corpus",
              "FROM publicdata.samples.shakespeare"
            ]
          },
          {
            "kind": "AGGREGATE",
            "substeps": [
              "$20 := COUNT($30)",
              "$21 := COUNTIF($31)"
            ]
          },
          {
            "kind": "COMPUTE",
            "substeps": [
              "$30 := 1",
              "$31 := equal($1, 'hamlet')"
            ]
          },
          {
            "kind": "WRITE",
            "substeps": [
              "$20, $21",
              "TO __stage00_output"
            ]
          }
        ]
      },
      {
        "name": "S01: Output",
        "id": "1",
        "startMs": "1576544129465",
        "endMs": "1576544129480",
        "inputStages": [
          "0"
        ],
        "waitRatioAvg": 0.44,
        "waitMsAvg": "11",
        "waitRatioMax": 0.44,
        "waitMsMax": "11",
        "readRatioAvg": 0,
        "readMsAvg": "0",
        "readRatioMax": 0,
        "readMsMax": "0",
        "computeRatioAvg": 0.2,
        "computeMsAvg": "5",
        "computeRatioMax": 0.2,
        "computeMsMax": "5",
        "writeRatioAvg": 0.16,
        "writeMsAvg": "4",
        "writeRatioMax": 0.16,
        "writeMsMax": "4",
        "shuffleOutputBytes": "17",
        "shuffleOutputBytesSpilled": "0",
        "recordsRead": "1",
        "recordsWritten": "1",
        "parallelInputs": "1",
        "completedParallelInputs": "1",
        "status": "COMPLETE",
        "steps": [
          {
            "kind": "READ",
            "substeps": [
              "$20, $21",
              "FROM __stage00_output"
            ]
          },
          {
            "kind": "AGGREGATE",
            "substeps": [
              "$10 := SUM_OF_COUNTS($20)",
              "$11 := SUM_OF_COUNTS($21)"
            ]
          },
          {
            "kind": "WRITE",
            "substeps": [
              "$10, $11",
              "TO __stage01_output"
            ]
          }
        ]
      }
    ],
    "estimatedBytesProcessed": "2464625",
    "timeline": [
      {
        "elapsedMs": "304",
        "totalSlotMs": "50",
        "pendingUnits": "0",
        "completedUnits": "2"
      }
    ],
    "totalPartitionsProcessed": "0",
    "totalBytesProcessed": "2464625",
    "totalBytesBilled": "10485760",
    "billingTier": 1,
    "totalSlotMs": "50",
    "cacheHit": false,
    "referencedTables": [
      {
        "projectId": "publicdata",
        "datasetId": "samples",
        "tableId": "shakespeare"
      }
    ],
    "statementType": "SELECT"
  },
  "totalSlotMs": "50"
},

Como usar informações de execução

Os planos de consulta do BigQuery fornecem informações sobre como o serviço executa consultas, mas a natureza gerenciada do serviço limita se alguns detalhes são diretamente úteis. Muitas otimizações acontecem automaticamente ao usar o serviço, o que pode ser diferente de outros ambientes em que o ajuste, o provisionamento e o monitoramento podem exigir uma equipe dedicada e experiente.

Para técnicas específicas que podem melhorar a execução e o desempenho de consultas, consulte a documentação de práticas recomendadas. As estatísticas do plano de consulta e do cronograma podem ajudar você a entender se determinados estágios dominam a utilização de recursos. Por exemplo, um estágio JOIN que gera muito mais linhas de saída do que de entrada pode indicar uma oportunidade para fazer a filtragem mais cedo na consulta.

Além disso, as informações de cronograma podem ajudar a identificar se determinada consulta é lenta isoladamente ou devido aos efeitos de outras consultas que disputam os mesmos recursos. Se o número de unidades ativas permanece limitado durante todo o ciclo de vida da consulta, mas a quantidade de unidades de trabalho consultadas permanece alta, isso pode indicar que, nesses casos, reduzir o número de consultas simultâneas melhora significativamente o tempo de execução geral de determinadas consultas.

Observação: parte do processamento de consultas ocorre fora do contexto de qualquer etapa. Em alguns casos, a latência ou o uso de slot significativo pode ser acumulado antes do envio do primeiro estágio ou após a conclusão do último. Os exemplos incluem algumas formas de remoção de partições e várias operações de metadados.