BigQuery para usuários de armazenamento de dados

Atualizado em junho de 2021

Neste documento, você verá como usar o BigQuery como um armazenamento de dados. Ele mapeia conceitos comuns de armazenamento de dados para os do BigQuery e descreve como executar tarefas padrão de armazenamento de dados no BigQuery. Este documento destina-se a pessoas que gerenciam armazenamentos de dados e sistemas de Big Data.

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 substitui 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 divididas com linhas de negócios ou com um algum domínio de análise. Cada conjunto de dados está ligado a um projeto do Google Cloud.
Data lake O data lake pode conter arquivos do Cloud Storage ou do Google Drive, ou ainda dados transacionais no Bigtable ou no Cloud SQL. O BigQuery pode definir um esquema e fazer consultas diretamente em dados externos como fontes de dados federados. A API BigQuery Storage oferece leituras paralelas de alta largura de banda e é compatível com frameworks de processamento comuns, como o Spark e o Pandas.

Recursos do BigQuery

O BigQuery tem uma estrutura hierárquica. Os níveis estão descritos no diagrama a seguir:

Projeto A: conjunto de dados, abrangendo tabela, visualizações, script/função. Projeto B: jobs,
consulta, carregamento, cópia, exportação.

Projetos

Todos os recursos do Google Cloud que você alocar e usar precisam pertencer a um projeto. Um projeto é a entidade organizadora do que você cria com o Google Cloud. No contexto do BigQuery, um projeto é um contêiner para todos os recursos do BigQuery. Como o BigQuery separa armazenamento e computação, os projetos que armazenam e consultam os dados podem ser diferentes.

Conjuntos de dados

Os conjuntos de dados são os contêineres de nível superior usados na organização das tabelas e visualizações do BigQuery. Eles frequentemente mapeiam para esquemas em bancos de dados relacionais e armazenamentos de dados padrão.

Os conjuntos de dados têm como escopo o projeto do Cloud. Quando você faz referência a uma tabela na linha de comando, nas consultas SQL ou no código, você se refere a ela da seguinte maneira:

project.dataset.table

Um conjunto de dados está vinculado a um local. Os locais do conjunto de dados são os seguintes:

  • Regional: um lugar geográfico específico, como Londres.
  • Multirregional: uma grande área geográfica, como os Estados Unidos, que contém dois ou mais lugares geográficos.

Só é possível definir o local de um conjunto de dados no momento da criação dele. Uma consulta pode conter tabelas ou visualizações de conjuntos de dados diferentes no mesmo local.

O uso desses vários escopos (projeto, conjunto de dados, tabela e local) pode ajudar a estruturar suas informações de maneira lógica e geográfica.

Tabelas

As tabelas do BigQuery são estruturas de colunas de linha que armazenam seus dados. Todas as tabelas são definidas por um esquema, que descreve nomes, tipos de dados e outras informações da coluna. É possível especificar o esquema de uma tabela quando ela é criada. Como alternativa, é possível criar uma tabela sem um esquema e especificar o esquema no job de consulta ou de carregamento que primeiro o preencher com dados. O BigQuery tem os seguintes tipos de tabela:

  • Tabelas nativas: compatíveis com o armazenamento do BigQuery nativo.
  • Tabelas externas: compatíveis com armazenamento externo ao BigQuery.
  • Visualizações: tabelas virtuais definidas por uma consulta SQL.

Para mais informações, consulte Gerenciamento de armazenamento.

Jobs

Jobs são ações executadas pelo BigQuery em seu nome para carregar, exportar, consultar ou copiar dados. Os jobs não são vinculados ao mesmo projeto em que os dados estão armazenados. No entanto, o local em que o job pode ser executado está vinculado ao local do conjunto de dados. Por exemplo, se você carregar dados de um bucket do Cloud Storage em um conjunto de dados do BigQuery localizado em Singapura, o bucket regional ou multirregional do Cloud Storage também precisará estar localizado em Singapura. Como alternativa, se o conjunto de dados estiver localizado em uma região europeia, não será possível consultá-lo a partir de outras regiões, como os EUA. Isso garante que seus requisitos de localidade de dados sejam atendidos.

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 fazer reservas de slots para o projeto. Para detalhes sobre qual abordagem usar, consulte 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 eliminação para armazenar blocos redundantes de dados em vários discos físicos (links em inglês). Além disso, os dados são replicados para vários data centers.

Também é possível consultar dados armazenados no BigQuery, incluindo arquivos armazenados no Cloud Storage, Google Drive ou 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ê. Os upgrades geralmente não exigem tempo de inatividade ou afetam 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 gerenciamento de vácuo ou índice, 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

Gerenciar backups e disponibilidade sempre foi uma tarefa complexa e cara para os administradores de banco de dados. A necessidade de licenças e hardware extras pode aumentar muito os custos. O BigQuery trata de backup e recuperação de desastres no serviço. Ao manter um histórico completo de sete dias das alterações nas tabelas, o BigQuery permite consultar um snapshot pontual dos dados usando decoradores de tabela ,SYSTEM_TIME AS OF na cláusula FROM. É 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. Além disso, o comando cp oferece snapshots instantâneos de tabela na região.

Os conjuntos de dados do BigQuery podem ser regionais ou multirregionais. Para conjuntos de dados regionais, por exemplo, um conjunto de dados localizado na região us-central1, nenhuma cópia do conjunto de dados é mantida fora da região. Se você considerar a falta de backups fora de uma região arriscada para seus negócios, crie e programe cópias entre regiões usando o serviço de transferência de dados do BigQuery. Para conjuntos de dados multirregionais localizados em grandes áreas geográficas, como a Europa (UE), uma cópia é armazenada automaticamente em outra região do Google Cloud.

Se uma região falhar, alguns dados recentes serão perdidos. Para mais informações, consulte a documentação do BigQuery sobre disponibilidade e durabilidade.

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 no console da Web do BigQuery, ele vê apenas os recursos do BigQuery que foram compartilhados com ele entre os 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, atribua permissões a usuários em um diretório externo como LDAP em alguns sistemas RDBMS. O modelo do BigQuery para gerenciar usuários e permissões é semelhante a esse último modelo.

O Cloud Identity é o provedor de identidade central integrado do Google Cloud que permite a autenticação do usuário. Ele faz parte do gerenciamento de identidade e acesso (IAM, na sigla em inglês). Além da autenticação, o IAM oferece controle centralizado para autorizar identidades com permissões específicas para o BigQuery e os conjuntos de dados dele. É possível usar papéis predefinidos ou criar papéis personalizados para controlar o acesso. Para acesso de usuário não humano aos recursos do BigQuery, crie uma conta de serviço e atribua a ela o papel necessário. Um exemplo de caso de uso para essa abordagem é a concessão de acesso a scripts de carregamento de dados programados.

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. Para tabelas que têm dados confidenciais em determinadas colunas, é possível usar tags de política de dados com os papéis do IAM para aplicar a segurança no nível da coluna. Para mais informações sobre governança de dados, consulte Como migrar armazenamentos de dados para o BigQuery: governança de dados.

Integração

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

Para integrar um analista no Google Cloud, conceda acesso aos projetos relevantes (em inglês), apresente-os ao Console do Google Cloud Platform e ao console da Web do BigQuery e compartilhe algumas consultas para ajudá-los a se familiarizar com os dados:

  • O Console do Cloud (em inglês) fornece uma visualização centralizada de todos os recursos no ambiente do Google Cloud. O recurso mais relevante para analistas de dados pode ser os buckets do Cloud Storage, onde eles podem colaborar em arquivos.
  • O console 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 Cloud de acordo com o papel concedido, como visualizar metadados, visualizar dados, executar, salvar e compartilhar consultas.

A descoberta de dados é uma grande preocupação para empresas, na integração de usuários novos e experientes. É muito importante encontrar os dados necessários. É igualmente importante proteger os dados confidenciais e autorizar o acesso a eles. É possível usar o Data Catalog para fornecer automaticamente recursos como pesquisa de metadados e prevenção contra perda de dados. Para mais informações sobre a descoberta de dados, consulte Descoberta de dados.

Como gerenciar cargas de trabalho e simultaneidade

Nesta seção, discutiremos os controles disponíveis para gerenciar cargas de trabalho, o número de consultas simultâneas que podem ser feitas e a programação de jobs.

Cotas de serviço

As cotas de serviço são usadas para manter uma qualidade de serviço consistente ao usar o BigQuery e estão documentadas na política de cotas do BigQuery. Cada limite de cota tem um valor padrão para todos os consumidores. Por exemplo, é possível definir o número máximo de consultas simultâneas como 100 por padrão. Se você precisar aumentar ou diminuir esse número, faça isso com uma modificação de cota.

Cotas personalizadas

Se você tem vários projetos e usuários do BigQuery, é possível gerenciar custos ao solicitar uma cota personalizada que especifique um limite na quantidade de dados de consulta processados por dia. As cotas diárias são redefinidas à meia-noite, horário do Pacífico.

Priorização e programação de consultas

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 limite de taxa simultânea. As consultas em lote são enfileiradas e executadas quando os recursos ociosos estiverem disponíveis, geralmente em alguns minutos. Se o BigQuery não tiver iniciado a consulta em 24 horas, ele altera a prioridade do job para interativa. As consultas em lote não são contabilizadas na cota de limite de taxa simultânea.

O BigQuery implementa um algoritmo de programação justo para os casos em que consultas simultâneas podem usar mais slots do que os disponíveis para um projeto ou reserva. Devido à velocidade e a escala em que o BigQuery opera, muitos problemas tradicionais de carga de trabalho, como manter filas separadas para cargas de trabalho diferentes, não são aplicáveis. Caso você precise priorizar consultas explicitamente, separe as cargas de trabalho confidenciais em um projeto com uma reserva separada (em inglês).

Como monitorar e auditar

É possível monitorar o BigQuery usando o Monitoring, onde vários gráficos e alertas são 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 minuciosa, 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 Monitoring fornece um portal de autoatendimento baseado na Web.

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.

O BigQuery também fornece visualizações somente leitura de INFORMATION_SCHEMA que podem ser usadas para acessar os metadados dos recursos do BigQuery, como conjuntos de dados, tabelas e jobs. Essas visualizações podem ser usadas para diversas finalidades, como rastrear a data de validade da tabela ou entender a utilização de slots das suas consultas

Como gerenciar dados

Nesta seção, você verá considerações sobre o design de esquema, como o particionamento e o clustering funcionam 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

O BigQuery permite especificar o esquema para uma tabela ao carregar dados nela ou ao criar uma tabela vazia. O BigQuery é compatível com tipos de dados do SQL padrão, incluindo tipos simples como inteiros e tipos mais complexos como ARRAY e STRUCT.

O BigQuery é compatível com modelos de dados tradicionais baseados no esquema em estrela e em esquema em floco de neve (em inglês). Nesses modelos, as tabelas de fatos são mescladas com as tabelas de dimensão. O BigQuery também é compatível com operações INNER, [FULL|RIGHT|LEFT] OUTER e CROSS JOIN.

Em alguns casos, convém usar campos aninhados e repetidos para desnormalizar seus dados. Para fazer isso, use uma combinação de tipos de dados ARRAY e STRUCT para definir seu esquema.

Como particionar tabelas

As tabelas particionadas são divididas em segmentos baseados no valor de uma coluna de partição. Quando uma consulta especifica filtros na coluna da partição, somente os segmentos correspondentes são verificados. Essa disposição acelera a execução da consulta e reduz o custo de execução dela. É possível particionar uma tabela do BigQuery das seguintes maneiras:

  • Tempo de ingestão: o BigQuery carrega automaticamente os dados em partições diárias baseadas em datas que refletem a hora de ingestão ou chegada dos dados.
  • Particionamento baseado em coluna: a tabela é particionada com base no valor de uma coluna especificada. É possível usar os seguintes tipos nas colunas:

O particionamento é ativado durante o processo de criação da tabela. 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 em qual partição os dados são carregados, especifique uma partição específica no seu job de carregamento.

Tabelas de clustering

As tabelas em cluster são organizadas com base em uma ou mais colunas especificadas. O BigQuery é compatível com o clustering para tabelas particionadas e não particionadas. O clustering divide os segmentos da tabela em blocos classificados nos campos do clustering. Para consultas que filtram dados nas colunas em cluster, a quantidade de dados verificados é reduzida e o desempenho da consulta é aprimorado. Como a quantidade de dados verificados só pode ser determinada no ambiente de execução, o custo exato da execução de uma consulta não pode ser conhecido antecipadamente.

O BigQuery agrupa automaticamente os dados recém-inseridos em segundo plano. O reclustering automático não afeta a capacidade da consulta ou o preço.

No fluxograma a seguir, descrevemos os melhores casos de uso para particionamento, clustering e particionamento, além de clustering em tabelas.

Fluxograma com opções repetidas na tabela a seguir.

O fluxograma anterior descreve as opções a seguir:

Caso de uso Recomendação
Você está usando o preço sob demanda e precisa de garantias rígidas de custo antes de executar consultas. Tabelas particionadas
O tamanho do seu segmento será menor que 1 GB após o particionamento da tabela. Tabelas em cluster
Você precisa de um grande número de partições além dos limites do BigQuery. Tabelas em cluster
Mutações frequentes nos dados modificam um grande número de partições. Tabelas em cluster
Você executa consultas com frequência para filtrar dados em determinadas colunas fixas. Partições e clustering

Visualizações materializadas

As visualizações materializadas são pré-computadas e armazenam em cache os resultados de uma consulta periodicamente para aumentar o desempenho e a eficiência. No BigQuery, uma visualização materializada é sempre consistente com a tabela base, incluindo tabelas de streaming do BigQuery. As visualizações materializadas são úteis para criar tabelas agregadas no seu armazenamento de dados.

Dados geoespaciais

Armazenamentos de dados geralmente contêm dados de localização. Esse tipo de dado pode ser usado de várias maneiras, desde o fornecimento de um sistema de logística de cadeia de suprimentos mais eficiente até o planejamento para um furacão em uma turbina eólica. O BigQuery GIS (sistema de informações geográficas) permite analisar e visualizar dados geoespaciais no BigQuery usando funções geográficas do SQL padrão. O BigQuery fornece o tipo de dados GEOGRAPHY, que permite carregar dados espaciais em GeoJSON, bem-sucedido binários conhecidos (WKB) e texto conhecido (WKT). O BigQuery também fornece várias funções geográficas que permitem analisar, transformar e operar em dados SIG. Para mais informações sobre como trabalhar com dados geoespaciais, consulte Como trabalhar com o BigQuery GIS.

Carregando dados

O BigQuery fornece modos de lote e de streaming para carregar dados. Ele também permite importar dados diretamente de determinados aplicativos SaaS usando o serviço de transferência de dados do BigQuery. Os carregamentos em lote permitem que você carregue grandes quantidades de dados sem afetar o desempenho da consulta e sem nenhum custo extra. Para casos de uso como carregar dados de alteração para detecção de fraudes, que exigem que os dados estejam disponíveis em tempo real, é possível fazer streaming de dados para o BigQuery.

cargas em lotes;

Para carregamentos em lote, os arquivos de dados são organizados em um bucket do Cloud Storage e importados para suas tabelas do BigQuery usando um job de carregamento. O BigQuery é compatível com muitos formatos abertos, como CSV, JSON, Avro, ORC e Parquet (em inglês). O BigQuery também tem suporte integrado para o Datastore e o Firestore.

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 mais informações, consulte Cotas e limites.

É possível iniciar jobs de carregamento por meio do console do BigQuery. Para automatizar o processo, configure um Cloud Functions para ouvir um evento do Cloud Storage que seja associados à chegada de novos arquivos em um determinado bucket e inicie o job de carregamento do BigQuery. Os pipelines de dados geralmente são usados para executar um procedimento de extração, transformação e carregamento (ETL, na sigla em inglês), que é executado fora do armazenamento de dados. O diagrama a seguir mostra o fluxo de eventos no pipeline.

Procedimento ETL do BigQuery.

Uma alternativa ao procedimento de ETL é um procedimento de extração, carregamento e transformação (ELT). Conforme mostrado no diagrama a seguir, em um procedimento ELT, os dados são carregados primeiro no armazenamento de dados e depois transformados no esquema desejado usando as operações SQL.

Pipelines de ELT do BigQuery.

Use os pipelines de ETL em execução no Dataflow para carregar dados automaticamente no BigQuery usando o conector de E/S do BigQuery fornecido. SDK do Apache Beam Como alternativa, também é possível usar os pipelines criados com o Apache Spark para carregar dados automaticamente no BigQuery usando o conector do BigQuery para Spark.

Inserções de streaming

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 Cloud Logging, também pode transmitir os registros do seu projeto do Cloud diretamente para o BigQuery, incluindo registros de solicitação do App Engine e informações de registro personalizadas enviadas para o Cloud Logging.

Também é possível fazer streaming de dados de eventos dos sistemas de mensagens com pipelines em execução noDataflow como Pub/Sub e Apache Kafka usando o STREAMING_INSERTS método do E/S do BigQuery fornecido no SDK do Apache Beam.

Como as empresas começam a usar mais serviços do Google Cloud, geralmente elas escolhem capturar dados de origem diretamente no Bigtable, no Cloud SQL, ou Cloud Spannere use o Dataflow para extrair, transformar e carregar dados no BigQuery em lotes ou streams. O diagrama a seguir mostra como configurar pipelines de ETL em lote e stream usando o Dataflow.

Pipelines em lote e de streaming de ETL configurados com o Dataflow.

Importar de aplicativos SaaS

O serviço de transferência de dados do BigQuery permite importar dados de fontes de aplicativos do Google, como Google Ads, Campaign Manager, Google Ad Manager e YouTube. Ele também é compatível com fontes de dados externas, como o Amazon S3, e armazenamentos de dados, como Teradata e Amazon Redshift. Você também pode usar os conectores fornecidos pelos nossos parceiros para vários outros sistemas do Google Cloud Marketplace.

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. O serviço do BigQuery tem um SLA de 99,99% de tempo de atividade, mas você controla a disponibilidade e a capacidade de resposta dos conjuntos de dados com sua abordagem de refletir a alteração nos dados.

Todas as modificações de tabela no BigQuery, incluindo operações DML, consultas com tabelas de destino e jobs de carregamento, são compatíveis com ACID. 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 são menos eficientes em bancos de dados analíticos, talvez você prefira armazená-las em lotes. É possível aplicar a maioria das técnicas conhecidas para processar alterações de dados. 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. Geralmente, esse conceito era 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 integrado no BigQuery para tabelas particionadas por 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 outros elementos associados.

Depois que o armazenamento de dados entra em produção, essas alterações passam por um rígido controle de alterações. Na maioria das vezes, as alterações de esquema são programadas como upgrades 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) (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.

Não há uma solução comum para todos os casos de dimensões com alterações lentas. É 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.

SCD tipo 1: substituir

O SCD tipo 1 subtitui o valor de um atributo com dados novos sem manter o histórico. Essa abordagem é particularmente útil quando as tabelas de dimensão espelham tabelas primárias operacionais. 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á isolada. Basta atualizar a linha afetada na tabela de dimensões.

Para alterações pouco frequentes em linhas específicas, é possível usar a instrução UPDATE DML.

update mydataset.dimension_table set PRD_CATEGORY="cosmetics" where PRD_SK="123"

Pode haver casos em que você quer sincronizar periodicamente a dimensão com a tabela principal operacional. Um padrão comum é mesclar periodicamente os despejos do banco de dados operacional na tabela de dimensões do BigQuery. É possível carregar os novos dados em uma tabela temporária ou criar uma tabela externa que aponte para os dados.

Tabela de dimensões:

PRD_SK PRD_ID PRD_DESC PRD_CATEGORY
123 ABC creme hidratante incrível — 100 g saúde e beleza
124 PQR loção incrível - 50 g saúde e beleza

Tabela temporária:

PRD_SK PRD_ID PRD_DESC PRD_CATEGORY
123 ABC creme hidratante incrível — 100 g cosméticos
124 PQR loção incrível - 50 g cosméticos
125 XYZ Camiseta acme - xG roupas

Agora é possível executar uma consulta de mesclagem para atualizar a tabela de dimensões e, em seguida, descartar a tabela temporária.

MERGE my-dataset.dimension_table as MAIN using
my-dataset.temporary_table as TEMP
on MAIN.PRD_SK = TEMP.PRD_SK
when matched then
UPDATE SET
MAIN.PRD_CATEGORY = TEMP.PRD_CATEGORY
when not matched then
INSERT VALUES(TEMP.PRD_SK, TEMP. PRD_ID, TEMP. PRD_SK, TEMP.
PRD_CATEGORY)

Tabela de dimensões do resultado:

PRD_SK PRD_ID PRD_DESC PRD_CATEGORY
123 ABC creme hidratante incrível — 100 g saúde e beleza
cosméticos
124 PQR loção incrível - 50 g saúde e beleza
cosméticos
125 XYZ Camiseta acme - xG roupas

SCD tipo 2: manter o histórico de linhas

Esse método rastreia dados históricos ilimitados criando vários registros para uma determinada chave natural, com chaves substitutas separadas (em inglês). 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

Você pode criar uma visualização ou materializada na parte superior da tabela e usá-la em suas consultas de análise.

create view products_current as
select PRD_SK, PRD_ID, PRD_DESC, PRD_CATEGORY, PRD_START_DATE
from my-dataset.dimension_table
where END_DATE IS NULL

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 aparecerá da seguinte maneira:

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

SCD tipo 3: manter o histórico adicionando colunas

Esse método rastreia dados históricos limitados usando colunas separadas para preservar o histórico limitado. Como o BigQuery aceita campos aninhados e repetidos, é possível manter o histórico na mesma coluna usando um tipo de matriz em ordem crescente pelo valor START_DATE. Assim como no SCD tipo 2, é possível criar uma visualização ou visualização materializada sobre a tabela para facilitar a realização de consultas.

Tabela base:

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

Crie a visualização para escolher o nome da última categoria de produto na matriz PRD_CATEGORY:

create view my-dataset.products_current as
select PRD_SK, PRD_ID, PRD_DESC,
PRD_CATEGORY.ordinal[array_length(PRD_CATEGORY)] as PRD_CAT
from my-dataset.dimension_table;

Visualizar:

PRD_SK PRD_ID PRD_DESC PRD_CAT
123 ABC creme hidratante incrível — 100 g cosméticos

Replicação quase em tempo real

Quando você precisar que os dados atualizados do banco de dados operacional estejam disponíveis para análise quase em tempo real, use a replicação de banco de dados para o BigQuery usando a captura de dados de alteração (CDC, na sigla em inglês).

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 expressão regular compatíveis são aceitas na 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

O console 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 o console da Web do BigQuery para saber mais.

Recursos do console da Web do BigQuery

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 em que não é prático expressar a função em uma instrução SQL. UDFs permitem estender as funções internas do SQL. eles usam uma lista de valores, que podem ser do tipo ARRAY ou STRUCT, e retornam um valor único, que também pode ser do tipo ARRAY ou STRUCT. As UDFs podem ser escritas em SQL padrão e JavaScript. Nas UDFs de JavaScript, é possível incluir recursos externos, como criptografia ou outras bibliotecas. Recomendamos que você use UDFs de SQL padrão porque elas têm melhor desempenho do que as UDFs de JavaScript. Para ver alguns exemplos de algumas UDFs comumente criadas e mantidas pela equipe de serviços profissionais do Google Cloud, consulte a página do GitHub bigquery-utils.

Scripts e procedimentos armazenados

Os usuários corporativos geralmente executam lógicas complexas dentro de armazenamentos de dados. Com os scripts do BigQuery, é possível escrever scripts SQL padrão, que fornecem o uso de variáveis e instruções de controle, e executá-los dentro do seu armazenamento de dados do BigQuery. Os procedimentos armazenados permitem salvar esses scripts para execução no BigQuery em casos de uso futuros. Assim como acontece com as visualizações, é possível compartilhar um procedimento armazenado com outras pessoas na sua organização, mantendo uma versão canônica do procedimento. Você encontra scripts de exemplo e procedimentos armazenados na página do BigQuery bigquery-utils.

Consultas automáticas

É comum automatizar a execução de consultas com base em uma programação ou um evento e armazenar em cache os resultados para consumo posterior. Use as consultas programadas do BigQuery para executar periodicamente a linguagem de definição de dados (DDL, na sigla em inglês) e a Linguagem de manipulação de dados (DML, na sigla em inglês).

Para orquestrações simples, como a automatização de jobs de carregamento de um bucket do Cloud Storage, use um gatilho do Cloud Storage para executar uma Função do Cloud , que executa um job do BigQuery. Para jobs programados, é possível acionar a Função do Cloud com o Cloud Scheduler. Para fluxos de trabalho mais complexos, use o Cloud Composer para orquestrar outras atividades automatizadas usando os operadores do Airflow do Airflow.

API BigQuery Storage

É comum que as empresas tenham pipelines que precisem ler uma grande quantidade de dados do BigQuery. A API BigQuery Storage permite ler streams paralelos de dados estruturados serializados. Essa abordagem ajuda a superar as limitações de desempenho da leitura de linhas paginadas e da sobrecarga de exportação de dados para o Cloud Storage.

Os pipelines atuais criados com o Apache Beam ou o Apache Spark (em inglês) podem usar a API BigQuery Storage com pouca ou nenhuma configuração adicional.

Otimização de consultas

Para entender as características de desempenho depois da execução de uma consulta, consulte a explicação do plano de consulta detalhada. A consulta é dividida 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.

Entrada e saída de consultas.

Reduzir a verificação de dados

O BigQuery não usa nem é compatível com índices. Cada vez que ele executa uma consulta, ele executa uma verificação completa de colunas. Como o desempenho do BigQuery e os custos de consulta são baseados na quantidade de dados verificados durante a consulta, recomendamos que você projete suas consultas para que elas façam referência apenas às colunas relevantes para a consulta. Ao usar tabelas particionadas, apenas verifique as partições relevantes. É possível evitar a verificação indesejada usando filtros de partição baseados na coluna de partição. Se você tiver consultas que filtram com frequência colunas específicas, pense em fazer o clustering na tabela. Se você tiver que executar com frequência uma consulta agregada para processamento extra, pense em materializar a consulta. Essa abordagem reduz o requisito de computação e a quantidade de dados verificados.

Reduza os requisitos de computação

Recomendamos que você evite usar funções JavaScript definidas pelo usuário. Sempre que possível, use UDFs de SQL padrão. Outra maneira de acelerar as consultas é usar agregações aproximadas, como APPROX_COUNT_DISTINCT em vez de COUNT(DISTINCT).

Melhore o desempenho da mesclagem

É comum as empresas precisarem unir várias tabelas, especialmente quando os armazenamentos de dados têm um esquema em estrela ou um floco de neve. Uma tabela de fatos geralmente é maior que tabelas de dimensão. No esquema de floco de neve, como as dimensões são normalizadas, você pode ter tabelas de dimensões ainda menores. É uma prática recomendada começar com a tabela de fatos à esquerda e mesclá-la com as tabelas de dimensões menores à direita em ordem decrescente de tamanho. Quando você tem uma tabela grande no lado esquerdo do JOIN e uma pequena no lado direito do JOIN, uma união de transmissão é criada. Uma união de transmissão envia todos os dados para a tabela menor para cada slot que processa a tabela maior.

Para mais informações, consulte Como migrar o armazenamento de dados para o BigQuery: otimização de desempenho.

Origens externas

Para casos de uso em que você quer mesclar uma tabela operacional pequena e de constante mudança com as tabelas do BigQuery, o BigQuery é compatível com fontes de dados externas comoCloud Bigtable eCloud SQL para criar um anexo da VLAN de monitoramento. Essa abordagem garante que os dados não precisem ser recarregados sempre que forem atualizados.

Como o BigQuery aceita a consulta de dados em muitos formatos, como Avro, Parquet e ORC, é possível usá-los para transformar e carregar dados no BigQuery doGoogle Drive ou Cloud Storage em uma única passagem. Também é possível consultar dados do seu data lake atual no Cloud Storage do BigQuery que segue o layout particionado padrão do Hive. Por exemplo, uma tabela em um data lake empresarial é armazenada em um bucket do Cloud Storage no formato Parquet com o seguinte padrão de particionamento do Hive:

gs://my_bucket/my_table/{dt:DATE}/{val:STRING}

Para fazer a consulta, o usuário pode criar uma tabela externa no BigQuery com o padrão de particionamento do Hive. Quando o usuário executa consultas nessa tabela, o BigQuery respeita o esquema de partição do Hive e reduz os dados verificados.

Isso é especialmente útil quando você está migrando seu armazenamento de dados para o BigQuery de maneira gradual, porque é possível migrar todas as consultas para o BigQuery sem mover os dados.

Para mais informações sobre fontes de dados externas no BigQuery, consulte Introdução a fontes de dados externas.

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

Google Data Studio, Looker e muitas ferramentas de parceiros já integradas O BigQuery pode ser usado para extrair análises do BigQuery e criar visualizações sofisticadas e interativas de dados. Se você estiver familiarizado com as interfaces de planilhas, poderá acessar, analisar, visualizar e compartilhar dados no BigQuery a partir do Planilhas usando o Planilhas conectadas.

Se você precisar escolher uma ferramenta, veja uma comparação abrangente de fornecedores no relatório do Quadrante Mágico da Gartner e em Relatório de pontuação do G2 da G2 Crowd. O relatório da Gartner está disponível em muitos sites de parceiros, como o Tableau (em inglês).

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 o API REST do BigQuery diretamente. Por exemplo, consulte Como criar painéis interativos personalizados com Bokeh e BigQuery, que usam 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 Google Cloud usando o BigQuery e o Data Studio(em inglês).

Há três dimensões de custos principais para o BigQuery: custos de carregamento, armazenamento e consulta. O projeto que tem o conjunto de dados do BigQuery é cobrado com as taxas de armazenamento mensais padrão. O projeto que inicia a consulta ou a carga é cobrado pelo custo de computação. Cada dimensão é detalhada nesta seção.

Como armazenar dados

O preço de armazenamento é rateado por Mbps.

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 tabelas particionadas, o mesmo modelo se aplica a segmentos de partição individuais.

Para mais informações, consulte Preços de armazenamento do BigQuery.

Carregando 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 na seção anterior.

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 usando reservas.

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 informações 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 no console 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.

Custo de consulta.

Reservas do BigQuery

Para despesas mensais mais consistentes, é possível ativar os preços fixos por meio do BigQuery Reservations. Com essa opção, é possível adquirir compromissos de capacidade para um número específico de slots do BigQuery para sua organização e atribuí-los a projetos específicos.

É possível assumir compromissos mensais ou anuais. Também é possível fazer compromissos de slots flexíveis, o que permite comprar slots extras por pelo menos 60 segundos. Ao comprar slots, é possível atribuí-los a buckets diferentes, chamados de reservas. As reservas criam uma alocação nomeada de slots. Para usar os slots que você comprou, atribua projetos, pastas ou organizações às reservas. Cada nível na hierarquia de recursos herda a atribuição do nível acima, a menos que você modifique essa configuração.

É possível usar o BigQuery Reservations para isolar a capacidade do compromisso entre cargas de trabalho, equipes ou departamentos criando outras reservas e atribuindo projetos a essas reservas.

No primeiro cenário de exemplo mostrado na imagem a seguir, são necessários 1.000 slots para dois tipos de carga de trabalho: ciência de dados (DS) e Business Intelligence (BI). No segundo cenário de exemplo, são necessários 1.000 slots para executar jobs ELT a cada hora por 15 minutos.

Exemplo de reserva de slot.

No primeiro cenário para jobs do DS e jobs de BI, use compromissos e reservas da seguinte maneira:

  • Crie um compromisso de 1.000 slots por mês ou por ano.
  • Crie uma reserva de slot do DS 500 e atribua todos os projetos relevantes do Google Cloud à reserva do DS.
  • Crie uma reserva de BI de 500 slots e atribua projetos conectados às ferramentas de BI a ela.

Em um segundo cenário para jobs de ELT, você usaria compromissos e reservas da seguinte maneira:

  • Crie uma reserva de slot flexível de 1.000 slots.
  • Criar uma reserva ELT com 1000 slots e atribuir o projeto relevante à reserva ELT.
  • Ao concluir os jobs do ELT, você exclui a atribuição, a reserva do ELT e o compromisso.

A seguir