BigQuery para usuários de armazenamento de dados

Atualizado em setembro de 2017

Neste artigo, explicamos como usar o BigQuery como um armazenamento de dados, mapeando conceitos comuns de armazenamento de dados aos do BigQuery e descrevendo como executar tarefas padrão de armazenamento de dados no BigQuery.

Comparação do modelo de serviço

Na tabela a seguir, os conceitos de armazenamento de dados padrão são associados aos do BigQuery.

Armazenamento de dados BigQuery
Armazenamento de dados O serviço do BigQuery subtitui a configuração típica de um armazenamento de dados tradicional. Ou seja, ele serve como um ambiente coletivo para todos os dados analíticos de uma organização.
Data mart Conjuntos de dados são coleções de tabelas que podem ser dividas com linhas de negócios ou com um domínio de análise. Cada conjunto de dados está ligado a um projeto do Google Cloud Platform (GCP).
Data lake O data lake pode conter arquivos do Cloud Storage ou do Google Drive, ou ainda dados transacionais no Cloud Bigtable. O BigQuery pode definir um esquema e fazer consultas diretamente em dados externos como fontes de dados federados.
Tabelas e visualizações Tabelas e visualizações funcionam do mesmo modo tanto no BigQuery quanto no data warehouse tradicional.
Concessões O Cloud Identity and Access Management (Cloud IAM) é usado para conceder permissões para realizar ações específicas no BigQuery.

Conjuntos de dados

As tabelas de dados são organizadas pelo BigQuery em unidades chamadas conjuntos de dados. Esses conjuntos estão no escopo do projeto do seu GCP. Quando você faz referência a uma tabela na linha de comando, nas consultas SQL ou no código, você se refere a ela usando o formato a seguir:

project.dataset.table

Esses diversos escopos — projeto, conjunto de dados e tabela — pode ajudar a estruturar suas informações de maneira lógica. De acordo com as necessidades do seu negócio, use também diversos conjuntos de dados para separar tabelas de diferentes domínios de análise e escopo de projeto para isolar conjuntos de dados.

Veja a seguir uma visão geral da estrutura do BigQuery:

Visão geral estrutural do BigQuery

Como provisionar e dimensionar sistemas

Não é necessário provisionar recursos antes de usar o BigQuery, diferente de muitos sistemas RDBMS. porque ele aloca dinamicamente armazenamento e recursos de consulta com base nos padrões de uso.

  • Os recursos de armazenamento são alocados na medida em que são utilizados, e desalocados conforme são removidos os dados ou as tabelas.
  • Os recursos de consulta são alocados de acordo com o tipo e a complexidade da consulta. Cada uma usa uma quantidade de slots, que representam unidades de cálculos que compreendem uma quantidade específica de CPU e RAM.

O BigQuery não tem um uso mínimo obrigatório. O serviço aloca e cobra pelos recursos de acordo com seu uso real. Por padrão, todos os clientes do BigQuery têm acesso a 2.000 slots para operações de consulta. Também é possível reservar um número fixo de slots para seu projeto. Para detalhes sobre qual abordagem usar, consulte a seção Custos.

Gerenciamento de armazenamento

Internamente, o BigQuery armazena dados em um formato colunar proprietário chamado Capacitor, que tem vários benefícios para cargas de trabalho do armazenamento de dados. O BigQuery usa um formato proprietário para poder aprimorá-lo em conjunto com o mecanismo de consulta, que aproveita o conhecimento profundo do layout dos dados para otimizar a execução da consulta. O BigQuery usa padrões de acesso a consultas para determinar o número ideal de fragmentos físicos e como eles são codificados.

Os dados são armazenados fisicamente no sistema de arquivos distribuídos do Google, chamado Colossus, que garante durabilidade usando a codificação de apagamento para armazenar blocos redundantes de dados em vários discos físicos. Além disso, os dados são replicados para vários data centers.

Também é possível consultar dados armazenados no Big Query, incluindo arquivos armazenados no Cloud Storage, Google Drive ou Cloud Bigtable, usando fontes de dados federados. No entanto, essas fontes não são otimizadas para operações do BigQuery. Por isso, talvez elas não tenham um desempenho tão bom quanto os dados armazenados no BigQuery.

Manutenção

O BigQuery é um serviço totalmente gerenciado, o que significa que a equipe de engenharia do BigQuery cuida da atualização e manutenção por você. As atualizações não devem exigir tempo de inatividade ou prejudicar o desempenho do sistema.

Muitos sistemas tradicionais exigem processos de vácuo que consomem muitos recursos para serem executados em vários intervalos para reorganizar e classificar os blocos de dados e recuperar espaço. O BigQuery não tem nada equivalente ao processo de vácuo, porque o mecanismo de armazenamento gerencia e otimiza continuamente a forma como os dados são armazenados e replicados. Além disso, como o BigQuery não usa índices em tabelas, não é necessário recriar índices.

Backup e recuperação

O BigQuery trata de backup e recuperação de desastres no serviço. Além disso, como ele mantém um histórico completo de 7 dias de alterações nas tabelas, o BigQuery permite consultar uma captura instantânea pontual dos dados. É possível reverter as alterações facilmente sem precisar solicitar uma recuperação dos backups. (Quando uma tabela é excluída explicitamente, o histórico é liberado depois de 2 dias. No momento da redação deste artigo, o recurso de snapshot instantâneo é compatível apenas com o SQL legado.)

Como gerenciar fluxos de trabalho

Nesta seção, discutimos tarefas administrativas, a organização de conjuntos de dados, a concessão de permissões e o trabalho de integração no BigQuery. A seção também inclui como gerenciar cargas de trabalho simultâneas, monitorar a integridade do armazenamento de dados e auditar o acesso do usuário.

Como organizar conjuntos de dados

É possível segmentar conjuntos de dados em projetos separados com base na classe de dados ou unidade de negócios, ou ainda consolidá-los em projetos comuns para simplificar.

Assim, é possível convidar um analista de dados para colaborar em um conjunto de dados em qualquer papel limitado definido por você. Quando um analista de dados faz login na interface da web do BigQuery, ele vê apenas os conjuntos de dados que foram compartilhados com ele nos projetos. As atividades que ele pode executar nos conjuntos de dados variam de acordo com o papel dele em cada conjunto.

Como conceder permissões

Em um sistema RDBMS, é possível atribuir permissões para ver ou modificar tabelas. Basta criar atribuições de SQL e aplicá-las a um usuário específico no sistema de banco de dados. Além disso, alguns sistemas RDBMS permitem atribuir permissões a usuários em um diretório externo como LDAP. O modelo do BigQuery para gerenciar usuários e permissões é semelhante a esse último modelo.

O BigQuery fornece papéis pré-definidos para controlar o acesso aos recursos. Também é possível criar papéis personalizados do Cloud IAM que tenham um conjunto específico de permissões e atribuir esses papéis a usuários ou grupos. É possível atribuit um papel a um endereço de e-mail do Google ou a um Grupo do G Suite.

Um aspecto importante do funcionamento do armazenamento de dados é a permissão de acesso compartilhado, mas controlado, dos mesmos dados a diferentes grupos de usuários. Por exemplo, os departamentos de finanças, RH e marketing acessam as mesmas tabelas, mas o nível de acesso de cada um deles é diferente. As ferramentas tradicionais de armazenamento de dados tornam isso possível reforçando a segurança na linha. Os mesmos resultados podem ser obtidos no BigQuery definindo visualizações autorizadas e permissões na linha.

Integração

Tradicionalmente, a integração de novos analistas de dados envolvia um tempo significativo. Para permitir que os analistas executassem consultas simples, você precisava mostrar onde estavam as fontes dos dados e configurar conexões e ferramentas ODBC, além de direitos de acesso. O GCP permite acelerar muito o tempo que leva para um analista ser produtivo.

Para integrar um analista no GCP, você concede acesso aos projetos relevantes, apresenta-os ao Console do Google Cloud Platform e à interface da web do BigQuery e compartilha algumas consultas para ajudá-los a se familiarizar com os dados:

  • O Console do GCP fornece uma visualização centralizada de todos os recursos no ambiente do GCP. O recurso mais relevante para analistas de dados pode ser os intervalos do Cloud Storage, onde eles podem colaborar em arquivos.
  • A interface do usuário da web do BigQuery apresenta a lista de conjuntos de dados aos quais o analista tem acesso. Os analistas podem executar tarefas no Console do GCP de acordo com o papel concedido, como visualizar metadados, visualizar dados, executar, salvar e compartilhar consultas.

Como gerenciar cargas de trabalho e simultaneidade

O BigQuery limita a taxa máxima de solicitações de entrada e aplica cotas apropriadas por projeto. As políticas específicas variam de acordo com a disponibilidade do recurso, o perfil do usuário, o histórico de uso do serviço e outros fatores. Para saber mais, consulte a política de cotas do BigQuery.

O BigQuery oferece dois tipos de prioridades de consulta: interativas e em lote. Por padrão, o BigQuery executa consultas interativas, o que significa que elas são executadas assim que possível. As consultas interativas contam para cotas de consulta. As consultas em lote são enfileiradas e executadas assim que os recursos ociosos estiverem disponíveis, geralmente em alguns minutos.

O BigQuery não é compatível com a priorização refinada de consultas interativas ou em lote. Devido à velocidade e á escala em que o BigQuery opera, muitos problemas tradicionais de carga de trabalho não se aplicam. Caso você precise priorizar consultas explicitamente, separe as cargas de trabalho confidenciais em um projeto com um número explícito de slots reservados. Entre em contato com seu representante do Google para que ele ajude você a se tornar um cliente de taxa fixa.

Como monitorar e auditar

Para monitorar o BigQuery, use o Stackdriver, que conta com vários gráficos e alertas definidos com base nas métricas do BigQuery. Por exemplo, é possível monitorar a taxa de transferência do sistema usando a métrica "Tempo de consulta" ou visualizar tendências de demanda de consulta com base na métrica "Slots alocados". Quando for necessário planejar uma consulta exigente, use a métrica "Slots disponíveis". Para permanecer proativo em relação à integridade do sistema, crie alertas baseados nos limites definidos por você. O Stackdriver oferece um portal de autoatendimento baseado em web. É possível controlar o acesso ao portal usando contas do Stackdriver.

O BigQuery automaticamente cria registros de auditoria relacionados às ações do usuário. Os registros de auditoria podem ser exportados para outro conjunto de dados do BigQuery em um lote ou como um fluxo de dados, para que você possa usar sua ferramenta de análise preferida para visualizar os registros. Para saber mais, consulte Como analisar registros de auditoria usando o BigQuery.

Como gerenciar dados

Nesta seção, discutimos questões de elaboração de esquemas, desnormalização, como o particionamento funciona e métodos para carregar dados no BigQuery. A seção termina com uma análise sobre como lidar com mudanças no armazenamento sem que haja tempo de inatividade.

Como elaborar esquemas

Siga estas diretrizes gerais para criar o esquema ideal para o BigQuery:

  • Tabelas com mais de 10 gigabytes precisam ser desnormalizadas, exceto se você tiver fortes evidências de que os custos de manipulação de dados e operação UPDATE e DELETE superam os benefícios de consultas ideais.
  • Tabelas com menos de 10 gigabytes devem estar sempre normalizadas, a menos que a tabela raramente passe pelas operações UPDATE e DELETE.
  • Aproveite ao máximo os campos aninhados e repetidos de tabelas desnormalizadas.

Desnormalização

O método convencional de desnormalizar dados envolve gravar um fato, com todas as suas dimensões, em uma estrutura de tabela simples. Por exemplo, para transações de vendas, grave cada fato em um registro junto com as respectivas dimensões, como informações do pedido e do cliente.

Como alternativa, o método recomendado para desnormalizar dados aproveita o suporte nativo do BigQuery para estruturas aninhadas e repetidas nos dados de entrada do JSON ou Avro. Uma representação mais natural dos dados subjacentes é possibilitada pela expressão de registros usando estruturas aninhadas e repetidas. No caso do pedido de vendas, a parte externa de uma estrutura JSON contém as informações do pedido e do cliente, enquanto a parte interna da estrutura contém os itens de linha individuais do pedido, representados como elementos repetidos e aninhados.

{
  "orderID": "ORDER",
  "custID": "EMAIL",
  "custName": "NAME",
  "timestamp": "TIME",
  "location": "LOCATION",
  "purchasedItems": [
    {
      "sku": "SKU",
      "description": "DESCRIPTION",
      "quantity": "QTY",
      "price": "PRICE"
    },
    {
      "sku": "SKU",
      "description": "DESCRIPTION",
      "quantity": "QTY",
      "price": "PRICE"
    }
  ]
}

Expressar registros usando campos aninhados e repetidos simplifica o carregamento de dados usando arquivos JSON ou Avro. Depois de criar esse esquema, é possível executar as operações SELECT, INSERT, UPDATE e DELETE em quaisquer campos individuais usando uma notação com pontos, como Order.Item.SKU. Para ver exemplos, consulte a documentação do BigQuery.

Vantagens da desnormalização

O BigQuery é essencialmente um mecanismo analítico. Ele é compatível com ações de DML, mas não foi feito para ser usado como um armazenamento de processamento de transações online (OLTP). A discussão sobre a Como alterar dados fornece diretrizes para lidar com as alterações, eliminando o tempo de inatividade da análise e fornecendo o desempenho ideal do processamento analítico online (OLAP). Embora estruturas de dados normalizadas ou parcialmente normalizadas, como esquema em estrela ou floco de neve, sejam adequadas para operações de atualização/exclusão, elas não são ideais para cargas de trabalho de OLAP. Ao executar operações de OLAP em tabelas normalizadas, várias tabelas precisam ser mescladas para executar as agregações necessárias. O BigQuery permite mesclar e, às vezes, isso é recomendado para tabelas menores. No entanto, isso geralmente não é tão eficiente quanto estruturas desnormalizadas.

No gráfico a seguir, o desempenho da consulta usando mesclas é comparado ao desempenho de filtros simples no que diz respeito ao tamanho da tabela. O desempenho da consulta deteriora de maneira muito mais acentuada quando há mesclas.

Tempo de consulta usando mesclas versus filtros

Desvantagens da desnormalização

Esquemas desnormalizados não são ideais para armazenamento, mas o baixo custo de armazenamento do BigQuery resolve preocupações relacionadas à ineficiência do armazenamento. Compare os custos com os ganhos na velocidade da consulta para ver por que o armazenamento não é um fator significativo.

Um desafio de trabalhar com esquemas desnormalizados é manter a integridade dos dados. Dependendo da frequência da mudança e do grau de difusão, a manutenção da integridade dos dados pode exigir um tempo maior da máquina e, às vezes, mais tempo humano para testes e verificações.

Como particionar tabelas

O BigQuery é nativamente compatível com o particionamento de tabelas por data. O particionamento é ativado durante o processo de criação da tabela. Novas partições são criadas pelo BigQuery, por data, automaticamente, sem necessidade de manutenção extra. Além disso, é possível especificar um prazo de validade para os dados nas partições.

Novos dados inseridos em uma tabela particionada são gravados na partição bruta no momento da inserção. Para controlar explicitamente em qual partição os dados são carregados, o job de carregamento pode especificar uma partição de data específica.

Como carregar dados

Antes que os dados possam ser carregados no BigQuery para cargas de trabalho analíticas, eles costumam ser armazenados em um produto do Cloud Storage e em um formato nativo da origem dele. Durante as etapas iniciais da migração para o GCP, o padrão comum é usar as ferramentas atuais de extração, transformação e carregamento (ETL) para transformar dados no esquema ideal para o BigQuery. Depois que os dados forem transformados, eles serão transferidos para o Cloud Storage como arquivos CSV, JSON ou Avro e, a partir daí, carregados no BigQuery usando jobs de carregamento ou streaming. Uma alternativa é transferir arquivos para o Cloud Storage no esquema nativo do armazenamento de dados local atual, carregado em um conjunto de tabelas temporárias no BigQuery e depois transformado no esquema ideal para o BigQuery usando os comandos SQL do BigQuery. Essas duas abordagens podem ser vistas aqui:

Primeira abordagem de carregamento de dados

Segunda abordagem de carregamento de dados

À medida que expande sua presença no GCP, você provavelmente capturará os dados de origem diretamente no Cloud Bigtable, Cloud Datastore ou Cloud Spanner e usará o Cloud Dataflow para dados ETL no BigQuery em lote ou streams.

Capture o código-fonte diretamente

Como usar jobs de carregamento

Esta seção pressupõe que seus dados estejam no Cloud Storage como uma coleção de arquivos em um formato de arquivo compatível. Para mais informações sobre cada formato, bem como requisitos e recursos específicos a serem considerados quando escolher um deles, consulte Formatos de dados do BigQuery.

Além do CSV, também é possível usar arquivos de dados com delimitadores diferentes de vírgulas, usando a sinalização --field_delimiter. Para detalhes, consulte sinalizações de carregamento bq.

O BigQuery é compatível com arquivos compactados gzip. No entanto, carregar arquivos compactados não é tão rápido quanto carregar arquivos não compactados. Para cenários sensíveis ao tempo ou em que a transferência de arquivos não compactados para o Cloud Storage sofre influência da largura da banda ou do tempo, execute um teste rápido de carregamento para verificar qual alternativa funciona melhor.

Como jobs de carregamento são assíncronos, não é necessário manter uma conexão com o cliente enquanto o job está sendo executado. Mais importante, jobs de carregamento não afetam outros recursos do BigQuery.

O job de carregamento cria uma tabela de destino caso ela ainda não exista.

O BigQuery determina o esquema de dados desta forma:

  • Se os seus dados estão no formato Avro, que é autodescritivo, o esquema pode ser determinado de modo direto pelo BigQuery.
  • Se os dados estão no formato JSON ou CSV, o BigQuery pode detectar automaticamente o esquema, embora seja recomendada uma verificação manual.

Especifique um esquema explicitamente transmitindo-o como um argumento para o job de carregamento. A mesma tabela pode ser anexada a jobs de carregamento em andamento usando o mesmo processo do carregamento inicial, mas isso não requer que o esquema seja transmitido com o job.

Caso seus arquivos CSV sempre tenham uma linha de cabeçalho que precisa ser ignorada após o carregamento inicial e a criação da tabela, use a sinalização --skip_leading_rows para ignorar a linha. Para detalhes, consulte sinalizações de carregamento bq.

O BigQuery define limites diários para o número e o tamanho dos jobs de carregamento que podem ser executados por projeto e por tabela. Além disso, o BigQuery configura limites nos tamanhos dos registros e arquivos de carregamento individuais. Para detalhes, consulte a Política de cotas.

Para iniciar os jobs de carregamento, use a interface da web do BigQuery. Para automatizar o processo, configure um Cloud Functions para ouvir um evento do Cloud Storage associado à chegada de novos arquivos em um determinado intervalo e iniciar o job de carregamento do BigQuery.

Como usar inserção de streaming

Para uma abordagem alternativa e complementar, transmita os dados diretamente no BigQuery. Dados de streaming são disponibilizados e podem ser consultados imediatamente com os dados de tabela atuais em tempo real.

Para situações que podem se beneficiar de informações em tempo real como detecção de fraude ou monitoramento de métricas do sistema, o streaming pode ser um diferenciador significativo. No entanto, diferentemente dos jobs de carregamento gratuitos no BigQuery, dados de streaming são cobrados. Assim, é importante usar o streaming em situações em que os benefícios valem mais que os custos.

Ao realizar o streaming de dados para as tabelas do BigQuery, você envia seus registros diretamente para o BigQuery usando a API BigQuery. Se você usa o Logging, o serviço de geração de registros do GCP, também poderá transmitir os registros do seu projeto GCP diretamente no BigQuery, incluindo registros de solicitação do App Engine e informações de registro personalizadas enviadas ao Logging.

Como lidar com mudanças

Vários armazenamentos de dados funcionam com contratos de nível de serviço (SLAs, na sigla em inglês) que exigem pouco ou nenhum tempo de inatividade. Enquanto o Google lida com o tempo de atividade do BigQuery, você controla a disponibilidade e a capacidade de resposta dos conjuntos de dados conforme sua abordagem para refletir as mudanças nos dados.

Todas as modificações de tabela no BigQuery são compatíveis com ACID. Isso se aplica a operações DML, consultas com tabelas de destino e jobs de carregamento. Uma tabela que passa por inserções, atualizações e exclusões enquanto processa as consultas de usuários lida com a simultaneidade de maneira eficiente e faz a transição de um estado para o outro de maneira atômica. Portanto, modificar uma tabela não requer tempo de inatividade. No entanto, seu processo interno pode exigir uma fase de teste e validação antes de disponibilizar dados recém-atualizados para análise. Além disso, como as operações DML competem com a carga de trabalho analítica por slots, pode ser melhor isolá-las. Por esses motivos, pode ser preferível introduzir tempos de inatividade. Este artigo usa o termo "tempo de inatividade de análise" para evitar confusão com o tempo de inatividade do serviço BigQuery.

É possível aplicar a maioria das técnicas antigas e comprovadas para lidar com o tempo de inatividade de análise. Esta seção apresenta mais detalhadamente alguns dos desafios e soluções conhecidos.

Janela de tempo variável

Um armazenamento de dados tradicional, diferentemente de um data lake, retém os dados apenas por um período fixo, por exemplo, os últimos 5 anos. A cada ciclo de atualização, novos dados são adicionados ao armazenamento e os dados mais antigos são descartados, mantendo a duração fixa. Na maioria das vezes, esse conceito foi usado para solucionar as limitações de tecnologias mais antigas.

O BigQuery foi desenvolvido para ser escalonado horizontalmente e pode ser expandido à medida que o tamanho do armazenamento aumenta. Portanto, não é necessário excluir dados mais antigos. Mantendo todo o histórico, é possível fornecer mais informações sobre seus negócios. Se o custo de armazenamento for uma preocupação, aproveite os preços de armazenamento de longo prazo do BigQuery arquivando dados mais antigos e usando-os para análises especiais quando necessário. Se você ainda tiver bons motivos para descartar dados mais antigos, use o suporte nativo do BigQuery para tabelas com partição de data e expiração da partição. Em outras palavras, o BigQuery pode excluir dados antigos automaticamente.

Como alterar esquemas

Durante o projeto e desenvolvimento de um armazenamento de dados, é comum ajustar os esquemas de tabela adicionando, atualizando ou eliminando colunas ou até mesmo adicionando ou eliminando tabelas inteiras. A menos que a alteração esteja na forma de uma coluna ou tabela adicionada, ela pode interromper consultas e relatórios salvos que fazem referência a uma tabela excluída, uma coluna renomeada e assim por diante.

Depois que o armazenamento de dados entra em produção, essas alterações passam por um rígido controle de alterações. É possível lidar com pequenas alterações de esquema durante um tempo de inatividade de análise, mas, na maioria das vezes, as alterações de esquema refletidas são agendadas como atualizações de versão. Você projeta, desenvolve e testa a atualização em paralelo enquanto a versão anterior do armazenamento de dados atende às cargas de trabalho de análise. É a mesma abordagem usada para aplicar alterações de esquema em um armazenamento de dados do BigQuery.

Dimensões com alterações lentas

Um esquema de dados normalizado minimiza o impacto das dimensões com alterações lentas (SCD, na sigla em inglês), isolando a alteração nas tabelas de dimensões. Isso é geralmente preferível em relação a um esquema desnormalizado, em que SCDs podem causar atualizações generalizadas na tabela fato simples. No entanto, conforme discutido na seção de design do esquema, a normalização deve ser usada com cuidado no BigQuery.

Quando se trata de SCDs, não existe uma única solução ideal para todos os casos. É importante entender a natureza da alteração e aplicar a solução ou combinação de soluções mais relevante para seu problema. No restante desta seção, descrevemos algumas soluções e como aplicá-las aos tipos de SCDs.

Técnica 1: alternância de visualizações

Essa técnica é baseada em duas visualizações de dados: "principal" vs. "sombra". O truque é ocultar a tabela real e expor a visualização "principal" aos usuários. Em ciclos de atualização, a visualização "sombra" é criada/atualizada e passa por testes de correção de dados enquanto os usuários trabalham na visualização "principal". No momento da alternância, a visualização "principal" é trocada pela "sombra". A "principal" antiga passa a ser a "sombra" e pode ser eliminada até o próximo ciclo de atualização ou mantida por alguns fluxos de trabalho, dependendo das regras e processos definidos pela organização.

As duas visualizações podem ser baseadas em uma tabela comum e diferenciadas por uma coluna, por exemplo, "view_type" ou de acordo com tabelas distintas. O método anterior não é recomendado, porque as operações DML na visualização "sombra" da tabela poderiam diminuir as consultas do usuário na visualização "principal" sem oferecer benefícios reais.

Embora a alternância de visualizações elimine o tempo de inatividade de análise, ela tem um custo mais alto porque, durante o ciclo de atualização, há duas cópias dos dados. Mais importante, se os ciclos de atualização ocorrerem com uma frequência maior do que a cada 90 dias, essa abordagem poderá impedir que sua organização aproveite os preços de armazenamento de longo prazo. Os 90 dias são baseados na política de preços atual no momento da redação deste documento. Certifique-se de verificar a política mais recente.

Às vezes, diferentes segmentos de dados mudam no ritmo deles. Por exemplo, os dados de vendas na América do Norte são atualizados diariamente, enquanto os da Ásia-Pacífico são atualizados quinzenalmente. Nessas situações, é melhor particionar a tabela com base no fator determinante da alteração. Neste exemplo, o país. A alternância de visualizações é aplicada às partições afetadas, não a todo o armazenamento de dados. No momento da redação deste artigo, a partição só pode ser baseada em um atributo de dados personalizado, como o país, se os dados forem explicitamente divididos em várias tabelas.

Técnica 2: carregamento de partição no local

Quando a alteração nos dados pode ser isolada por uma partição e o tempo de inatividade de análise é tolerado, a alternância de visualizações pode ser um exagero. Em vez disso, os dados das partições afetadas podem ser preparados em outras tabelas do BigQuery ou exportados para arquivos no Cloud Storage, onde podem ser substituídos durante o tempo de inatividade de análise.

Para substituir dados em uma partição de destino por dados de uma consulta de outra tabela:

bq query --use_legacy_sql=false --replace \
         --destination_table 'flight_data.fact_flights_part$20140910' \
         'select * from `ods.load_flights_20140910`

Para substituir dados em uma partição de destino carregando a partir do Cloud Storage:

bq load  --replace \
         --source_format=NEWLINE_DELIMITED_JSON
         'flight_data.fact_flights_part$20140910' \
         gs://{bucket}/load_flights_20140910.json
Técnica 3: mascaramento de dados de atualização

Para uma dimensão pequena e que muda frequentemente, é recomendada a normalização. Nessa técnica, as atualizações da dimensão são preparadas em uma tabela ou visualização isolada, que é unida condicionalmente ao restante dos dados:

SELECT f.order_id as order_id, f.customer_id as customer_id,
    IFNULL(u.customer_first_name, f.customer_first_name) as customer_first_name,
    IFNULL(u.customer_last_name, f.customer_last_name) as customer_last_name
FROM fact_table f
LEFT OUTER JOIN pending_customer_updates u
ON f.customer_id = u.customer_id
SCD tipo 1: substituir valor do atributo

O SCD tipo 1 subtitui o valor de um atributo com dados novos sem manter o histórico. Por exemplo, se o produto "creme hidratante incrível" fazia parte da categoria "saúde e beleza" e agora é classificado em "cosméticos", a alteração é semelhante à seguinte:

Antes:

PRD_SK PRD_ID PRD_DESC PRD_CATEGORY
123 ABC creme hidratante incrível - 100 g saúde e beleza

Depois:

PRD_SK PRD_ID PRD_DESC PRD_CATEGORY
123 ABC creme hidratante incrível - 100 g saúde e beleza
cosméticos

Se o atributo estiver em uma tabela de dimensões normalizada, a alteração será muito isolada. Basta atualizar a linha afetada na tabela de dimensões. Para tabelas de dimensões menores com atualizações frequentes do tipo 1, use a Técnica 3: mascaramento de dados de atualização.

Se o atributo for incorporado à tabela fato de maneira desnormalizada, a alteração será bastante generalizada. Será necessário atualizar todas as linhas fato em que o atributo é repetido. Nesse caso, use a Técnica 2: carregamento de partição no local ou a Técnica 1: alternância de visualizações.

SCD tipo 2: alterar o valor do atributo e manter o histórico

Esse método rastreia dados históricos ilimitados criando vários registros para uma determinada chave natural, com chaves substitutas separadas. Por exemplo, a mesma alteração ilustrada em SCDs tipo 1 seria tratada conforme abaixo:

Antes:

PRD_SK PRD_ID PRD_DESC PRD_CATEGORY START_DATE END_DATE
123 ABC creme hidratante incrível - 100 g saúde e beleza 31 de janeiro de 2009 NULL

Depois:

PRD_SK PRD_ID PRD_DESC PRD_CATEGORY START_DATE END_DATE
123 ABC creme hidratante incrível - 100 g saúde e beleza 31 de janeiro de 2009 18 de julho de 2017
124 ABC creme hidratante incrível - 100 g cosméticos 19 de julho de 2017 NULL

Se o atributo estiver em uma tabela de dimensões normalizada, a alteração será isolada. Basta atualizar a linha anterior e adicionar uma nova linha à tabela de dimensões. Para tabelas de dimensões menores com atualizações frequentes do tipo 1, use a Técnica 3: mascaramento de dados de atualização.

Se o atributo for incorporado à tabela fato de maneira desnormalizada, a situação pode ser mais favorável, contanto que você não mantenha datas explícitas de início e término, em vez de depender de datas de transações. Como o valor anterior permanece verdadeiro para a data e hora em que as transações anteriores ocorreram, não é necessário alterar as linhas anteriores da tabela fato. A tabela fato ficaria assim:

TRANSACTION_DATE PRD_SK PRD_ID PRD_DESC PRD_CATEGORY UNIDADES VALOR
18 de julho de 2017 123 ABC creme hidratante incrível - 100 g saúde e beleza 2 25,16
19 de julho de 2017 124 ABC creme hidratante incrível - 100 g cosméticos 1 13,50

Como consultar dados

O BigQuery é compatível com consultas SQL padrão e com ANSI SQL 2011. Uma descrição abrangente de todas as funções, operadores e capacidades de regex compatíveis é fornecida pela referência do SQL do BigQuery.

Como o BigQuery é compatível com campos aninhados e repetidos como parte do modelo de dados, seu suporte ao SQL foi estendido especificamente a esses tipos de campos. Por exemplo, ao usar o conjunto de dados público do GitHub, emita o comando UNNEST, que permite que você itere um campo repetido:

SELECT
  name, count(1) as num_repos
FROM
  `bigquery-public-data.github_repos.languages`, UNNEST(language)
GROUP BY name
ORDER BY num_repos
DESC limit 10

Consultas interativas

A interface da web do BigQuery permite fazer consultas interativas de conjuntos de dados e fornece uma visão consolidada dos conjuntos de dados nos projetos a que você tem acesso. Também são fornecidos diversos recursos como salvar e compartilhar consultas ad-hoc, ajustar e editar consultas de histórico, explorar tabelas e esquemas e coletar metadados de tabelas. Consulte a interface de usuário da web do BigQuery para saber mais.

captura de tela da interface do usuário da web do bigquery

Consultas automáticas

É comum automatizar a execução de consultas com base em um agendamento/evento e armazenar em cache os resultados para consumo posterior.

Se você estiver usando o Airflow para orquestrar outras atividades automatizadas e já tiver familiaridade com a ferramenta, use a API Apache Airflow para BigQuery. Esta postagem de blog (em inglês) mostra o processo de instalação do Airflow e como criar um fluxo de trabalho no BigQuery.

Para orquestrações mais simples, use cron jobs. Esta postagem de blog (em inglês) mostra como encapsular uma consulta como um aplicativo do App Engine e executá-la como um cron job programado.

Otimização de consultas

Cada vez que o BigQuery executa uma consulta, ele executa uma verificação completa de colunas. O BigQuery não usa e não é compatível com índices. Como o desempenho do BigQuery e os custos de consulta são baseados na quantidade de dados verificados durante a consulta, as consultas devem ser projetadas para que façam referência apenas às colunas relevantes para a consulta. Ao usar tabelas particionadas por data, confirme se apenas as partições relevantes foram verificadas. Isso pode ser feito usando filtros de partição baseados em PARTITIONTIME ou PARTITIONDATE.

Para compreender as características de desempenho depois da execução de uma consulta, leia a explicação do plano de consulta detalhada. A consulta é divida em etapas, pelo número de linhas de entrada/saída em cada etapa e o perfil de tempo em cada uma delas. Ao usar os resultados da explicação, você compreende e otimiza suas consultas.

captura de tela dos resultados do bigquery

Origens externas

Execute consultas em dados fora do BigQuery usando fontes de dados federados, mas essa abordagem tem implicações no desempenho. Use fontes de dados federados somente se eles precisarem ser mantidos externamente. Também é possível usar a federação de consultas para executar ETL de uma fonte externa para o BigQuery. Essa abordagem permite definir ETL usando a sintaxe familiar do SQL.

Funções definidas pelo usuário

O BigQuery também é compatível com funções definidas pelo usuário (UDFs, na sigla em inglês) para consultas que excedem a complexidade do SQL. UDFs permitem estender as funções internas do SQL. Elas pegam uma lista de valores, que podem ser matrizes ou estruturas, e retornam um valor único, que também pode ser uma matriz ou estrutura. UDFs são escritas em JavaScript e podem incluir recursos externos, como criptografia ou outras bibliotecas.

Compartilhamento de consulta

Com o BigQuery, os colaboradores podem salvar e compartilhar consultas entre membros da equipe. Esse recurso é especialmente útil no exercício de exploração de dados ou como meio de se manter atualizado sobre um novo conjunto de dados ou padrão de consulta. Para mais informações, consulte Como salvar e compartilhar consultas.

Como analisar dados

Nesta seção, apresentamos várias maneiras de se conectar ao BigQuery e analisar os dados. Para aproveitar ao máximo o BigQuery como um mecanismo analítico, armazene os dados no armazenamento do BigQuery. No entanto, para seu caso de uso específico, pode ser melhor analisar fontes externas por si próprias ou mescladas com dados no armazenamento do BigQuery.

Ferramentas prontas para uso

No momento da redação deste artigo, o Google Data Studio está disponível na versão beta, assim como muitas ferramentas de parceiros já integradas ao BigQuery. Essas ferramentas podem ser usadas para extrair análises do BigQuery e criar visualizações sofisticadas e interativas de dados.

Caso você precise escolher uma ferramenta, há uma comparação abrangente de fornecedores no Relatório do quadrante mágico da Gartner e no relatório de pontuação G2 da G2 Crowd. O relatório da Gartner está disponível em muitos sites de parceiros, como o Tableau.

logotipos de parceiros

Desenvolvimento personalizado

Para criar aplicativos e plataformas personalizados para complementar o BigQuery, use as bibliotecas de cliente disponíveis para as linguagens de programação mais comuns ou use a API REST do BigQuery diretamente.

Para um exemplo concreto, consulte este tutorial, que usa bibliotecas Python para se conectar ao BigQuery e gerar painéis interativos personalizados.

Conectores de terceiros

Para se conectar com o BigQuery de um aplicativo que não está nativamente integrado a ele no nível da API, use os drivers JDBC e ODBC do BigQuery. Os drivers fornecem uma ponte para o BigQuery interagir com aplicativos legados ou que não podem ser facilmente modificados, como os do Microsoft Excel. A interação com o BigQuery usando SQL é compatível com ODBC e JDBC, mas os drivers não são tão expressivos quanto lidar com a API diretamente.

Custos

A maioria dos armazenamentos de dados atende a várias entidades comerciais dentro da organização. Um desafio comum é analisar o custo de operação por entidade comercial. Para saber como diminuir e atribuir custos de consumo, consulte Visualizar o faturamento do GCP usando o BigQuery e o Data Studio.

Há três dimensões de custos principais para o BigQuery: custos de carregamento, armazenamento e consulta. Cada dimensão é detalhada nesta seção.

Como armazenar dados

O preço de armazenamento é cobrado proporcionalmente por MB/s.

Se uma tabela não é editada por 90 dias consecutivos, o preço do armazenamento cai automaticamente em 50 por cento, para US$ 0,01 por GB por mês. Não há degradação de desempenho, durabilidade, disponibilidade ou outra funcionalidade quando uma tabela é considerada como de armazenamento de longo prazo. Quando os dados de uma tabela são modificados, o BigQuery redefine o cronômetro na tabela e qualquer dado na tabela retorna ao preço normal de armazenamento. Ações que não manipulam os dados diretamente, como consultar e criar visualizações, não redefinem o cronômetro.

Para mais detalhes, consulte Preço de armazenamento do BigQuery.

Como carregar dados

É possível carregar dados no BigQuery usando um job de carregamento convencional, sem nenhum custo. Após o carregamento dos dados, você paga pelo armazenamento, conforme discutido acima.

As inserções de streaming são cobradas de acordo com a quantidade de dados transmitidos. Para mais detalhes, consulte os custos de inserções de streaming em Preço de armazenamento do BigQuery.

Como consultar dados

Dois modelos de preço são oferecidos por consulta pelo BigQuery: sob demanda e taxa fixa.

Preços sob demanda

No modelo sob demanda, o BigQuery cobra pela quantidade de dados acessada durante a execução da consulta. Como o BigQuery usa o formato de armazenamento em colunas, somente as colunas relevantes à sua consulta são acessadas. Se você gerar relatórios apenas semanalmente ou mensalmente e tiver realizado consultas com menos de 1 TB de dados, o custo das consultas na sua fatura provavelmente será muito baixo. Para mais detalhes sobre como as consultas são cobradas, consulte Preços de consulta do BigQuery.

Para ajudar a determinar a quantidade de dados que uma consulta específica deve verificar previamente, use o validador de consulta na interface de usuário da web. No caso de desenvolvimento personalizado, defina a sinalização dryRun na solicitação da API e o BigQuery não executará o job. Em vez disso, retorne com estatísticas sobre o job, como quantos bytes seriam processados. Consulte a API de consulta para saber mais.

API de consulta

Preço fixo

Para clientes que preferem mais consistência nos gastos mensais, é possível ativar a taxa fixa. Para saber mais, consulte Taxa fixa do BigQuery.

A seguir

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

Enviar comentários sobre…