Como gerenciar dados de entrada e fontes de dados

Ao avaliar os dados de entrada, leve em consideração a E/S obrigatória. Quantos bytes a consulta lê? Você está limitando corretamente o montante de dados de entrada? Os dados estão em um armazenamento nativo do BigQuery ou em uma fonte de dados externa? O montante de dados lidos por uma consulta e a fonte dos dados afetam o desempenho e o custo da consulta.

Você pode examinar como os dados de entrada são lidos por uma consulta usando a explicação do plano de consulta.

As práticas recomendadas a seguir dão uma orientação sobre como controlar os dados de entrada e escolher uma fonte de dados.

Projeção de controle - evite SELECT *

Prática recomendada: projeção de controle - consulte 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).

Usar SELECT * é a maneira mais cara de consultar dados. Quando você usa a opção SELECT *, o BigQuery faz uma leitura completa de cada coluna na tabela.

Para fazer testes ou analisar dados, use uma das opções de visualização de dados em vez de SELECT *.

Aplicar uma cláusula LIMIT a uma consulta SELECT * não afeta a quantidade de dados lidos. A cobrança ocorre em relação à leitura de todos os bytes em toda a tabela, e a consulta é contabilizada na sua cota de nível livre.

Em vez disso, consulte apenas as colunas de que você precisa. Por exemplo, use SELECT * EXCEPT para excluir uma ou mais colunas dos resultados.

Se você precisar fazer a consulta em todas as colunas de uma tabela, mas apenas em um subconjunto de dados, pense em:

  • materializar os resultados em uma tabela de destino e realizar a consulta nessa tabela;
  • particionar as tabelas por data e consultar a partição relevante. Por exemplo, WHERE _PARTITIONDATE="2017-01-01" verifica apenas a partição de 1º de janeiro de 2017.

Consultar um subconjunto de dados ou usar SELECT * EXCEPT pode reduzir muito o montante 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.

Eliminar consultas particionadas

Prática recomendada: ao consultar uma tabela particionada, use a pseudocoluna _PARTITIONTIME para filtrar as partições.

Ao consultar tabelas particionadas, use a pseudocoluna _PARTITIONTIME. Filtre os dados usando _PARTITIONTIME para especificar uma data ou intervalo de datas. Por exemplo, a cláusula WHERE a seguir usa a pseudocoluna _PARTITIONTIME para especificar partições entre 1º de janeiro 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, o que reduz o montante de dados de entrada. Filtrar as partições melhora o desempenho da consulta e reduz os custos.

Desnormalize dados sempre que possível

Prática recomendada: o BigQuery apresenta um desempenho melhor quando os dados são desnormalizados. Em vez de preservar um esquema relacional, como um esquema em estrela ou floco de neve, desnormalize os dados e aproveite os campos aninhados e repetidos para melhorar o desempenho. Os campos aninhados e repetidos podem ser vinculados sem impactar o desempenho na tentativa de preservar um esquema relacional (normalizado).

A economia de armazenamento pelo uso de dados normalizados tem menos efeito nos sistemas modernos. Os ganhos de desempenho da desnormalização de dados compensam os aumentos nos custos de armazenamento. As junções exigem coordenação de dados (largura de banda de comunicação). A desnormalização localiza os dados em slots individuais. Dessa forma, a execução pode ser feita em paralelo.

Para manter vínculos enquanto desnormaliza os dados, use campos aninhados e repetidos em vez de nivelar completamente os dados. Quando os dados relacionais estão completamente nivelados, a comunicação de rede (embaralhamento) pode afetar negativamente o desempenho da consulta.

Por exemplo, desnormalizar um esquema de pedidos sem usar campos aninhados e repetidos pode exigir que você agrupe dados por um campo como order_id (quando há um vínculo de um para muitos). Devido ao embaralhamento envolvido, o agrupamento dos dados é menos eficiente do que a desnormalização dos dados usando campos aninhados e repetidos.

Em algumas circunstâncias, a desnormalização dos dados e o uso de campos aninhados e repetidos não aumentam o desempenho. Evite a desnormalização nestes casos de uso:

  • Você tem um esquema em estrela com dimensões que mudam frequentemente.
  • O BigQuery complementa um sistema de processamento de transações on-line (OLTP, na sigla em inglês) com mutação no nível da linha, mas não pode substituí-lo.

Como usar campos aninhados e repetidos

O BigQuery não exige uma desnormalização totalmente uniforme. Você pode usar campos aninhados e repetidos para manter relacionamentos.

  • Dados de aninhamento (STRUCT)

    • Aninhar dados permite representar entidades externas in-line.
    • A consulta de dados aninhados usa uma sintaxe de "ponto" para referenciar campos de folha, algo semelhante à sintaxe que usa junção.
    • Os dados aninhados são representados como um tipo STRUCT em SQL padrão.
  • Dados repetidos (ARRAY)

    • Criar um campo de tipo RECORD com o modo definido como REPEATED permite preservar um relacionamento de um para muitos inline (desde que o relacionamento não seja de alta cardinalidade).
    • Com dados repetidos, a reprodução aleatória não é necessária.
    • Os dados repetidos são representados como ARRAY. É possível usar uma função ARRAY em SQL padrão ao consultar os dados repetidos.
  • Dados aninhados e repetidos (ARRAY de STRUCTs)

    • O aninhamento e a repetição se complementam.
    • Por exemplo, em uma tabela de registros de transação, seria possível incluir uma matriz de STRUCTs de item de linha.

Use corretamente fontes de dados externas

Prática recomendada: se o desempenho da consulta for prioridade máxima, não use uma fonte de dados externa.

Consultar tabelas em armazenamento gerenciado do BigQuery normalmente é muito mais rápido do que consultar tabelas externas no Cloud Storage, no Google Drive ou no Cloud Bigtable.

Use uma fonte de dados externa nestes casos de uso:

  • Realização de operações de extração, transformação e carga (ETL, na sigla em inglês) ao carregar dados
  • Mudança frequente de dados
  • Cargas periódicas, como processamento recorrente de dados do Cloud Bigtable

Evite tabelas de caracteres curinga em excesso

Prática recomendada: ao consultar tabelas de caracteres curinga, use o prefixo mais granular possível.

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 de caracteres curinga serão úteis se seu conjunto de dados contiver:

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

Ao consultar uma tabela de 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 da década de 1940.

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

Esta página foi útil? Conte sua opinião sobre:

Enviar comentários sobre…

Precisa de ajuda? Acesse nossa página de suporte.