Introdução ao carregamento de dados

Neste documento, você terá uma visão geral sobre o carregamento de dados no BigQuery.

Visão geral

Há várias maneiras de ingerir dados no BigQuery:

  • Carregar em lote um conjunto de registros de dados.
  • Transmitir registros individuais ou lotes de registros.
  • Use consultas para gerar novos dados e anexar ou substituir os resultados em uma tabela.
  • Use um aplicativo ou serviço de terceiros.

Carregamento em lote

Com o carregamento em lote, os dados de origem são carregados em uma tabela do BigQuery em uma única operação em lote. Por exemplo, a fonte dos dados pode ser um arquivo CSV, um banco de dados externo ou um conjunto de arquivos de registros. Os jobs de extração, transformação e carregamento (ETL) tradicionais se enquadram nessa categoria.

As opções para carregamento em lote no BigQuery incluem:

  • Jobs de carregamento. Carregue dados do Cloud Storage ou de um arquivo local criando um job de carregamento. Os registros podem estar nos formatos Avro, CSV, JSON, ORC ou Parquet.
  • SQL. A instrução SQL LOAD DATA carrega dados de um ou mais arquivos em uma tabela nova ou atual. É possível usar a instrução LOAD DATA para carregar arquivos Avro, CSV, JSON, ORC ou Parquet.
  • Serviço de transferência de dados do BigQuery. Use o serviço de transferência de dados do BigQuery para automatizar o carregamento de dados do Google Software como serviço (SaaS, na sigla em inglês) ou de aplicativos e serviços de terceiros.
  • API BigQuery Storage Write. A API Storage Write permite processar em lote um número arbitrariamente grande de registros e confirmá-los em uma única operação atômica. Se a operação de confirmação falhar, será possível repetir a operação com segurança. Ao contrário dos jobs de carregamento do BigQuery, a API Storage Write não requer a preparação de dados para armazenamento intermediário, como o Cloud Storage.
  • Outros serviços gerenciados. Usar outros serviços gerenciados para exportar dados de um armazenamento de dados externo e importá-los para o BigQuery. Por exemplo, é possível carregar dados das exportações do Firestore.

Ao escolher um método de carregamento em lote, a maioria dos padrões baseados em arquivos precisa usar o job de carregamento ou a instrução SQL LOAD DATA para carregar dados em lote. Outros serviços geralmente usam o serviço de transferência de dados do BigQuery ou exportam dados dos serviços do Google.

O carregamento em lote pode ser feito como uma operação única ou em uma programação recorrente. Por exemplo, é possível:

  • É possível executar transferências do serviço de transferência de dados do BigQuery com base em uma programação.
  • É possível usar um serviço de orquestração, como o Cloud Composer, para programar jobs de carregamento.
  • Use um cron job para carregar dados em um cronograma.

Streaming

Com o streaming, você envia continuamente lotes menores de dados em tempo real, portanto, os dados ficam disponíveis para consulta assim que chegam. As opções para streaming no BigQuery incluem:

  • API Storage Write. A API Storage Write é compatível com a ingestão de streaming de alta capacidade com semântica de entrega apenas uma vez.
  • Dataflow. Use o Dataflow com o SDK do Apache Beam para configurar um pipeline de streaming que grava no BigQuery. Para mais informações, consulte Conector de E/S do BigQuery na documentação do Apache Beam e no tutorial Fazer streaming do Pub/Sub para o BigQuery.
  • Datastream. O Datastream usa a funcionalidade de captura de dados alterados do BigQuery e a API Storage Write para replicar dados e atualizações de esquema de bancos de dados operacionais diretamente no BigQuery. Siga este guia de início rápido para consultar um exemplo de replicação de um banco de dados do Cloud SQL para PostgreSQL no BigQuery.
  • BigQuery Connector para SAP. O BigQuery Connector para SAP permite a replicação quase em tempo real dos dados do SAP diretamente no BigQuery. Para mais informações, consulte o Guia de planejamento do BigQuery Connector para SAP.
  • Pub/Sub. O Pub/Sub é um serviço de mensagens que pode ser usado para coordenar análises de streaming e pipelines de integração de dados. É possível usar assinaturas do BigQuery para gravar mensagens diretamente em uma tabela existente do BigQuery.

Dados gerados

É possível usar o SQL para gerar dados e armazenar os resultados no BigQuery. As opções para gerar dados incluem:

  • Use instruções de linguagem de manipulação de dados (DML, na sigla em inglês) para executar inserções em massa em uma tabela existente ou armazenar os resultados da consulta em uma nova tabela.

  • Use uma instrução CREATE TABLE ... AS para criar uma nova tabela a partir do resultado de uma consulta.

  • Execute uma consulta e salve os resultados em uma tabela. É possível anexar os resultados a uma tabela existente ou gravar em uma nova tabela. Para mais informações, consulte Como gravar resultados de consulta.

Aplicativos de terceiros

Alguns aplicativos e serviços de terceiros fornecem conectores que podem ingerir dados no BigQuery. Os detalhes de como configurar e gerenciar o pipeline de ingestão dependem do aplicativo. Por exemplo, para carregar dados de fontes externas no armazenamento do BigQuery, use o Carregador de dados da Informatica ou o Data pipelines da Fivetran. Para mais informações, consulte Carregar dados usando um aplicativo de terceiros.

Como escolher um método de ingestão de dados

Confira algumas considerações ao escolher um método de ingestão de dados.

Origem de dados. A fonte ou o formato dos dados pode determinar se é mais simples de implementar e manter o carregamento em lote. Considere os seguintes pontos:

  • Se o serviço de transferência de dados do BigQuery for compatível com a fonte, a transferência dos dados diretamente para ele será a solução mais simples de implementar.

  • Para dados de fontes de terceiros que não são compatíveis com o serviço de transferência de dados do BigQuery, transforme-os em um formato compatível com o carregamento em lote e use esse método.

  • Se os dados vierem do Spark ou do Hadoop, considere usar conectores do BigQuery para simplificar o processamento de dados.

  • Para arquivos locais, considere o carregamento em lote, especialmente se o BigQuery for compatível com o formato do arquivo sem exigir uma etapa de transformação ou limpeza de dados.

  • Para dados do aplicativo, como eventos de aplicativos ou um fluxo de registros, pode ser mais fácil transmitir os dados em tempo real do que implementar carregamento de lote.

Alteração lenta versus dados em constante mudança. Se você precisar ingerir e analisar dados quase em tempo real, considere fazer streaming dos dados. Com o streaming, os dados estarão disponíveis para consulta assim que cada registro chegar. Evite usar instruções DML para enviar um grande número de atualizações ou inserções de linha individuais. Para dados atualizados com frequência, muitas vezes é melhor transmitir um registro de alterações e usar uma visualização para receber os resultados mais recentes. Outra opção é usar o Cloud SQL como banco de dados de processamento de transações on-line (OLTP, na sigla em inglês) e consultas federadas para unir os dados no BigQuery.

Se os dados de origem forem alterados lentamente ou você não precisar de resultados atualizados continuamente, use um job de carregamento. Por exemplo, se você usa os dados para executar um relatório diário ou por hora, os jobs de carregamento podem ser mais baratos e usar menos recursos do sistema.

Outro cenário são os dados que chegam com pouca frequência ou em resposta a um evento. Nesse caso, use o Dataflow para fazer streaming dos dados ou use o Cloud Functions para chamar a API de streaming em resposta a um gatilho.

Confiabilidade da solução: O BigQuery tem um Contrato de nível de serviço (SLA, na sigla em inglês). No entanto, você também precisa considerar a confiabilidade da solução específica que está implementando. Considere os seguintes pontos:

  • Com formatos mal posicionados, como JSON ou CSV, dados inválidos podem falhar em um job de carregamento inteiro. Considere se você precisa de uma etapa de limpeza de dados antes de carregar e como responder a erros. Considere também o uso de um formato fortemente tipado, como Avro, ORC ou Parquet.
  • Os jobs periódicos de carregamento exigem programação usando o Cloud Composer, o cron ou outra ferramenta. O componente de programação pode ser um ponto de falha na solução.
  • Com o streaming, é possível verificar o sucesso de cada registro e relatar um erro rapidamente. Considere gravar mensagens com falha em uma fila de mensagens não processadas para análise e processamento posteriores. Para mais informações sobre erros de streaming do BigQuery, consulte Solução de problemas de inserções de streaming.
  • Os jobs de streaming e carregamento estão sujeitos a cotas. Para informações sobre como lidar com erros de cota, consulte Solução de problemas de erros de cota do BigQuery.
  • As soluções de terceiros podem ser diferentes na configuração, confiabilidade, garantia de pedidos e outros fatores. Portanto, considere essas soluções antes de adotar uma solução.

Latência. Avalie a quantidade de dados carregados e a frequência com que você precisa que os dados estejam disponíveis. O streaming oferece a menor latência de dados disponível para análise. Os jobs de carregamento periódicos têm uma latência maior porque os novos dados só estão disponíveis após a conclusão de cada um deles.

Por padrão, os jobs de carregamento usam um pool compartilhado de slots. Um job de carregamento pode aguardar em um estado pendente até que os slots estejam disponíveis, especialmente se você carregar uma quantidade muito grande de dados. Se isso criar tempos de espera inaceitáveis, você poderá comprar slots dedicados em vez de usar o pool de slots compartilhados. Para mais informações, consulte Introdução às reservas.

O desempenho da consulta para origens de dados externas pode não ser tão alto quanto o desempenho das consultas para dados armazenados no BigQuery. Se a minimização da latência da consulta for importante, recomendamos carregar os dados no BigQuery.

Formato de processamento de dados. Escolha um formato de ingestão de dados com base nos seguintes fatores:

  • Suporte a esquemas. As exportações Avro, ORC, Parquet e Firestore são formatos autodescritivos. O esquema de tabela é criado automaticamente pelo BigQuery com base nos dados de origem. Em dados JSON e CSV, forneça um esquema explícito ou use a detecção automática de esquema.

  • Dados simples ou campos aninhados e repetidos. Avro, CSV, JSON, ORC e Parquet aceitam dados simples. As exportações Avro, JSON, ORC, Parquet e Firestore também são compatíveis com dados que têm campos repetidos e aninhados. Dados aninhados e repetidos são úteis para expressar hierarquia. Campos aninhados e repetidos também reduzem a duplicação de dados ao carregar os dados.

  • Novas linhas incorporadas. Quando você carrega dados de arquivos JSON, as linhas precisam ser delimitadas. No BigQuery, é esperado que arquivos JSON delimitados por nova linha tenham um único registro por linha.

  • Codificação. O BigQuery é compatível com a codificação UTF-8 para dados simples e aninhados ou repetidos. Ele também é compatível com a codificação ISO-8859-1 para dados simples apenas de arquivos CSV.

Carregar dados aninhados e repetidos

Você pode carregar dados em campos aninhados e repetidos nos seguintes formatos:

  • Avro
  • JSON (delimitado por nova linha)
  • ORC
  • Parquet
  • Exportações do Datastore
  • Exportações do Firestore

Para saber mais sobre como especificar campos repetidos e aninhados no seu esquema quando estiver carregando dados, consulte Como especificar campos repetidos e aninhados.

Carregar dados de outros serviços do Google

Alguns serviços do Google exportam dados para o BigQuery usando consultas, exportações ou transferências programadas. Para mais informações sobre os serviços compatíveis com exportações para o BigQuery, consulte Carregar dados dos serviços do Google.

Outros serviços do Google são compatíveis com exportações de dados iniciadas pelo serviço de transferência de dados do BigQuery. Para mais informações sobre os serviços compatíveis com exportações iniciadas pelo serviço de transferência de dados do BigQuery, consulte serviço de transferência de dados do BigQuery.

Cota

Para informações sobre cotas, consulte as seguintes seções:

Alternativas ao carregamento de dados

Não é preciso carregar dados antes de executar consultas nas seguintes situações:

Conjuntos de dados públicos
Conjuntos de dados públicos são conjuntos de dados armazenados no BigQuery e compartilhados publicamente. Para mais informações, consulte Conjuntos de dados públicos do BigQuery.
Conjuntos de dados compartilhados
É possível compartilhar conjuntos de dados armazenados no BigQuery. Se alguém tiver compartilhado um conjunto de dados com você, é possível executar consultas nele sem a necessidade de carregar os dados.
Fontes de dados externas
O BigQuery também pode executar consultas em determinadas formas de dados externos, sem carregá-los no armazenamento do BigQuery. Com essa abordagem, é possível aproveitar os recursos analíticos do BigQuery sem mover os dados armazenados em outro lugar. Para saber informações sobre os benefícios e as limitações dessa abordagem, consulte fontes de dados externas.
Arquivos de geração de registros
O Cloud Logging é uma opção para exportar arquivos de registros para o BigQuery. Consulte Configurar e gerenciar coletores para mais informações.

Monitorar o uso de jobs de carregamento

É possível monitorar o uso de jobs de carregamento de duas maneiras:

  • Usar o Cloud Monitoring. Saiba mais em Métricas do BigQuery. Mais especificamente, é possível monitorar a quantidade de dados e o número de linhas enviados para uma tabela específica. Se os jobs de carregamento fizerem upload de dados para uma tabela específica, isso poderá ser um proxy para monitorar o uso de dados dos uploads.

  • Usar INFORMATION_SCHEMA.JOBS_BY_PROJECT. É possível usar a visualização do INFORMATION_SCHEMA.JOBS_BY_PROJECT para saber o número de jobs de carregamento por tabela por dia.

Exemplo de caso de uso:

Os exemplos a seguir explicam os métodos a serem usados com base no caso de uso e como usá-los com outras soluções de análise de dados.

Transmitir dados usando a API Storage Write

Suponha que haja um pipeline processando dados de eventos de registros de endpoint. Os eventos são gerados continuamente e precisam estar disponíveis para consulta no BigQuery o mais rápido possível. Como a atualização de dados é fundamental para esse caso de uso, a API Storage Write é a melhor opção para ingerir dados no BigQuery. Uma arquitetura recomendada para manter esses endpoints enxutos é o envio de eventos para o Pub/Sub, de onde são consumidos por um pipeline de streaming do Dataflow que faz streaming diretamente para o BigQuery.

Uma das principais preocupações de confiabilidade dessa arquitetura é como lidar com uma falha ao inserir um registro no BigQuery. Se cada registro for importante e não puder ser perdido, os dados precisarão ser armazenados em buffer antes de tentar inserir. Na arquitetura recomendada acima, o Pub/Sub pode desempenhar o papel de buffer com recursos de retenção de mensagens. O pipeline do Dataflow precisa ser configurado para repetir inserções de streaming do BigQuery com espera exponencial truncada. Depois que a capacidade do Pub/Sub como buffer se esgotar, como no caso de indisponibilidade prolongada do BigQuery ou uma falha na rede, os dados precisarão ser mantidos no cliente, que vai precisar de um mecanismo para retomar a inserção de registros persistentes depois que a disponibilidade for restaurada. Saiba mais sobre como lidar com essa situação na postagem do blog Guia de confiabilidade do Google Pub/Sub.

Outro caso de falha a ser resolvido é o de um registro contaminado. Um registro contaminado é um registro rejeitado pelo BigQuery por ter falhado na inserção com um erro não repetível ou um registro que não foi inserido com sucesso após o número máximo de novas tentativas. Os dois tipos de registro precisam ser armazenados em uma "fila de mensagens inativas" pelo pipeline do Dataflow para uma investigação mais detalhada.

Se uma semântica for necessária exatamente uma vez, crie um fluxo de gravação em tipo confirmado, com deslocamentos de registro fornecidos pelo cliente. Isso evita duplicações, já que a operação de gravação só será realizada se o valor de deslocamento corresponder ao próximo deslocamento de anexo. Não fornecer um deslocamento significa que os registros são anexados à atual ponta do stream e repetir um anexo com falha pode fazer com que o registro apareça mais de uma vez no stream.

Se as garantias de "exatamente uma vez" não forem necessárias, grave no stream padrão. Isso permitirá maior capacidade e não será contabilizado no limite de cota na criação de streams de gravação.

Estime a capacidade da sua rede e verifique antecipadamente se você tem uma cota adequada para disponibilizar essa capacidade.

Se a carga de trabalho estiver gerando ou processando dados a uma taxa muito desigual, tente suavizar os picos de carga no cliente e fazer streaming no BigQuery com uma capacidade constante. Isso pode simplificar o planejamento da capacidade. Se isso não for possível, prepare-se para lidar com erros do tipo 429 (recurso esgotado) se e quando sua capacidade exceder a cota durante picos curtos.

Processamento de dados em lote

Suponha que haja um pipeline de processamento em lote durante a noite que precise ser concluído até um prazo fixo. Os dados precisam estar disponíveis até esse prazo para passarem por outro processo em lote a fim de gerar relatórios que serão enviados a um regulador. Esse caso de uso é comum em setores regulamentados, como o financeiro.

O carregamento de dados em lote com jobs é a abordagem correta para esse caso de uso, porque a latência não é um problema desde que o prazo seja cumprido. Verifique se os buckets do Cloud Storage atendem aos requisitos de local para carregar dados no conjunto de dados do BigQuery.

O resultado de um job de carga do BigQuery é atômico; ou todos os registros são inseridos ou nenhum deles. Como prática recomendada, ao inserir todos os dados em um único job de carregamento, crie uma nova tabela usando a disposição WRITE_TRUNCATE do recurso JobConfigurationLoad. Isso é importante ao repetir um job de carregamento com falha, já que o cliente pode não conseguir distinguir entre os jobs que falharam e a falha causada, por exemplo, na comunicação do estado de sucesso de volta ao cliente.

Partindo do princípio que os dados a serem ingeridos já foram copiados para o Cloud Storage, tentar novamente com espera exponencial é suficiente para resolver falhas de ingestão.

É recomendável que um job em lote noturno não atinja a cota padrão de 1.500 carregamentos por tabela por dia, mesmo com novas tentativas. Ao carregar dados gradualmente, a cota padrão é suficiente para executar um job de carregamento a cada cinco minutos e ainda tem espaço para, em média, pelo menos uma nova tentativa por job.

A seguir