GSP414

Informações gerais

O BigQuery é um banco de dados de análise NoOps, totalmente gerenciado e de baixo custo desenvolvido pelo Google. Com ele, você pode consultar muitos terabytes de dados sem ter que gerenciar uma infraestrutura ou precisar de um administrador de banco de dados. O BigQuery usa SQL e está disponível no modelo de pagamento por uso. Assim, você pode se concentrar na análise dos dados para encontrar insights relevantes.

Neste laboratório, você vai aprender a consultar e criar tabelas particionadas no BigQuery para melhorar o desempenho das consultas e reduzir o uso de recursos. Vamos usar um conjunto de dados de e-commerce com milhões de registros do Google Analytics referentes à Google Merchandise Store carregados no BigQuery.

Atividades

Neste laboratório, você vai aprender a:

• consultar tabelas particionadas;
• criar suas próprias tabelas particionadas.

Configuração e requisitos

Antes de clicar no botão Start Lab

Leia estas instruções. Os laboratórios são cronometrados e não podem ser pausados. O timer é iniciado quando você clica em Começar o laboratório e mostra por quanto tempo os recursos do Google Cloud vão ficar disponíveis.

Este laboratório prático permite que você realize as atividades em um ambiente real de nuvem, não em uma simulação ou demonstração. Você vai receber novas credenciais temporárias para fazer login e acessar o Google Cloud durante o laboratório.

Confira os requisitos para concluir o laboratório:

• Acesso a um navegador de Internet padrão (recomendamos o Chrome).

Observação: para executar este laboratório, use o modo de navegação anônima ou uma janela anônima do navegador. Isso evita conflitos entre sua conta pessoal e a conta de estudante, o que poderia causar cobranças extras na sua conta pessoal.

• Tempo para concluir o laboratório---não se esqueça: depois de começar, não será possível pausar o laboratório.

Observação: não use seu projeto ou conta do Google Cloud neste laboratório para evitar cobranças extras na sua conta.

Como iniciar seu laboratório e fazer login no console do Google Cloud

1. Clique no botão Começar o laboratório. Se for preciso pagar, você verá um pop-up para selecionar a forma de pagamento. No painel Detalhes do laboratório à esquerda, você verá o seguinte:

   • O botão Abrir Console do Cloud
   • Tempo restante
   • As credenciais temporárias que você vai usar neste laboratório
   • Outras informações se forem necessárias

2. Clique em Abrir Console do Google. O laboratório ativa recursos e depois abre outra guia com a página Fazer login.

   Dica: coloque as guias em janelas separadas lado a lado.

   Observação: se aparecer a caixa de diálogo Escolher uma conta, clique em Usar outra conta.

3. Caso seja preciso, copie o Nome de usuário no painel Detalhes do laboratório e cole esse nome na caixa de diálogo Fazer login. Clique em Avançar.

4. Copie a Senha no painel Detalhes do laboratório e a cole na caixa de diálogo Olá. Clique em Avançar.

   Importante: você precisa usar as credenciais do painel à esquerda. Não use suas credenciais do Google Cloud Ensina. Observação: se você usar sua própria conta do Google Cloud neste laboratório, é possível que receba cobranças adicionais.

5. Acesse as próximas páginas:

   • Aceite os Termos e Condições.
   • Não adicione opções de recuperação nem autenticação de dois fatores (porque essa é uma conta temporária).
   • Não se inscreva em testes gratuitos.

Depois de alguns instantes, o console do GCP vai ser aberto nesta guia.

Observação: para ver uma lista dos produtos e serviços do Google Cloud, clique no Menu de navegação no canto superior esquerdo.

Abrir o console do BigQuery

1. No Console do Google Cloud, selecione o menu de navegação > BigQuery:

Você verá a caixa de mensagem Olá! Este é o BigQuery no Console do Cloud. Ela tem um link para o guia de início rápido e as notas de versão.

2. Clique em OK.

O console do BigQuery vai abrir.

Tarefa 1: criar um conjunto de dados

1. Primeiro, você vai criar um conjunto de dados para armazenar suas tabelas.

2. No painel "Explorer", perto do ID do projeto, clique em Ver ações e depois em Criar conjunto de dados.

3. Defina o ID do conjunto de dados como e-commerce.

Não altere as outras opções (Local dos dados, Expiração da tabela padrão).

4. Clique em Criar conjunto de dados.

Clique em Verificar meu progresso para conferir o objetivo.

Crie um conjunto de dados chamado ecommerce

Tarefa 2: criar tabelas particionadas por data

Uma tabela particionada é uma tabela dividida em segmentos, chamados de partições, que facilitam a consulta e o gerenciamento dos dados. Ao dividir uma tabela grande em partições menores, você pode melhorar o desempenho da consulta e controlar os custos reduzindo o número de bytes lidos por consulta.

Agora você vai precisar criar uma nova tabela e vincular uma coluna de data ou carimbo de data/hora como uma partição. Antes disso, precisamos explorar os dados na tabela não particionada.

Consulte a análise da página da Web para coletar uma amostra dos visitantes em 2017

1. Clique em Escrever nova consulta e adicione a consulta abaixo:

#standardSQL SELECT DISTINCT fullVisitorId, date, city, pageTitle FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE date = '20170708' LIMIT 5

Antes de executá-la, observe a quantidade total de dados que será processada, conforme indicado ao lado do ícone do validador de consultas: "Esta consulta processará 1,74 GB quando executada".

2. Clique em Executar.

A consulta retorna cinco resultados.

Consulte a análise da página da Web para coletar uma amostra dos visitantes em 2018

Modifique a consulta para analisar os visitantes em 2018.

1. Clique em ESCREVER NOVA CONSULTA para limpar os dados no Editor de consultas e adicione a nova consulta abaixo. O parâmetro WHERE date foi alterado para 20180708:

#standardSQL SELECT DISTINCT fullVisitorId, date, city, pageTitle FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE date = '20180708' LIMIT 5

O validador da consulta informa o volume de dados que será processado pela consulta.

2. Clique em Executar.

Observe que a consulta processará 1,74 GB mesmo sem retornar resultados. Por quê? O mecanismo de consulta examina todos os registros no conjunto de dados para identificar se eles atendem à condição de correspondência de data na cláusula WHERE. Ele precisa comparar a data de cada registro com a condição "20180708".

Além disso, "LIMIT 5" não reduz a quantidade total de dados processados, o que é um equívoco comum.

Casos de uso comuns para tabelas particionadas por data

Examinar todo o conjunto de dados várias vezes para comparar as linhas com uma condição WHERE é um desperdício de tempo. Principalmente se você precisa analisar apenas registros de um período específico como:

• todas as transações do ano passado;
• todas as interações de visitantes nos últimos sete dias;
• todos os produtos vendidos no último mês.

Em vez de analisar o conjunto de dados inteiro e filtrar por um campo de data, como fizemos nas consultas anteriores, agora vamos criar uma tabela particionada por data. Assim, é possível ignorar completamente a análise de registros em partições irrelevantes para nossa consulta.

Criar uma nova tabela particionada por data

1. Clique em ESCREVER NOVA CONSULTA, adicione a consulta abaixo e clique em Executar:

#standardSQL CREATE OR REPLACE TABLE ecommerce.partition_by_day PARTITION BY date_formatted OPTIONS( description="a table partitioned by date" ) AS SELECT DISTINCT PARSE_DATE("%Y%m%d", date) AS date_formatted, fullvisitorId FROM `data-to-insights.ecommerce.all_sessions_raw`

Nessa consulta, observe a nova opção PARTITION BY seguida por um campo. Você pode usar DATE e TIMESTAMP para fazer a partição. A função PARSE_DATE inclui o campo da data (que é uma string neste caso) e a classifica com o tipo DATE correto para particionamento.

2. Clique no conjunto de dados ecommerce e selecione a nova tabela partition_by_day:

3. Clique na guia Detalhes.

Confirme se os seguintes dados são mostrados:

• Particionado por: dia
• Particionamento em: date_formatted

Observação: as partições dentro de tabelas particionadas na sua conta do laboratório expiram automaticamente 60 dias após a data especificada na coluna. Se você tiver uma conta pessoal do Google Cloud com faturamento ativado, poderá ter tabelas particionadas que não expiram. Para este laboratório, as consultas restantes serão executadas em tabelas particionadas que já foram criadas.

Clique em Verificar meu progresso para conferir o objetivo.

Criar uma nova tabela particionada por data

Tarefa 3: analisar os resultados das consultas em uma tabela particionada

1. Execute a consulta abaixo e anote o total de bytes que serão processados:

#standardSQL SELECT * FROM `data-to-insights.ecommerce.partition_by_day` WHERE date_formatted = '2016-08-01'

Desta vez, 25 KB, ou 0,025 MB, são processados. Isso é apenas uma fração do que você consultou.

2. Execute a consulta abaixo e observe o total de bytes que serão processados:

#standardSQL SELECT * FROM `data-to-insights.ecommerce.partition_by_day` WHERE date_formatted = '2018-07-08'

Você verá a mensagem Esta consulta processará 0 B quando executada.

Tarefa 4: criar uma tabela particionada com expiração automática

As tabelas particionadas que expiram automaticamente são usadas para atender a exigências de leis de privacidade de dados. Elas podem ser usadas para evitar armazenamento desnecessário (que será cobrado em um ambiente de produção). Se você quiser criar uma janela contínua de dados, adicione uma data de expiração para apagar a partição depois que terminar de usá-la.

Examinar as tabelas de dados meteorológicos da NOAA disponíveis

1. No menu à esquerda, em "Explorer", clique em + Adicionar e selecione Conjuntos de dados públicos.

2. Procure GSOD NOAA e selecione o conjunto de dados.

3. Clique em Ver conjunto de dados.

4. Navegue pelas tabelas no conjunto de dados noaa_gsod, que são fragmentadas manualmente e não particionadas:

Seu objetivo é criar uma tabela que:

• consulte dados meteorológicos a partir de 2018;
• tenha filtros para incluir apenas os dias com algum tipo de precipitação (chuva, neve etc.);
• armazene somente cada partição de dados por 90 dias a contar da data da partição (janela contínua).

1. Primeiro, copie e cole esta consulta:

#standardSQL SELECT DATE(CAST(year AS INT64), CAST(mo AS INT64), CAST(da AS INT64)) AS date, (SELECT ANY_VALUE(name) FROM `bigquery-public-data.noaa_gsod.stations` AS stations WHERE stations.usaf = stn) AS station_name, -- As estações podem ter mais de um nome prcp FROM `bigquery-public-data.noaa_gsod.gsod*` AS weather WHERE prcp < 99.9 -- Filtra valores desconhecidos AND prcp > 0 -- Filtra estações/dias sem precipitação AND _TABLE_SUFFIX >= '2018' ORDER BY date DESC -- Onde houve chuva/neve recentemente LIMIT 10 Observação: o caractere curinga de tabela usado na cláusula FROM para limitar a quantidade de tabelas referidas no filtro TABLE_SUFFIX. Observação: adicionar LIMIT 10 ao código não reduz a quantidade total de dados verificados (cerca de 1,83 GB), já que ainda não há partições.

2. Clique em Executar.

3. Confirme se a data está formatada corretamente e se o campo de precipitação mostra valores diferentes de zero.

Tarefa 5: agora é sua vez de criar uma tabela particionada

• Modifique a consulta anterior para criar uma tabela com as especificações abaixo:

  • Nome: "ecommerce.days_with_rain"
  • Campo de data: sua condição PARTITION BY
  • OPTIONS: partition_expiration_days = 60
  • Descrição de tabela: "weather stations with precipitation, partitioned by day"

Sua consulta deve ficar assim:

#standardSQL CREATE OR REPLACE TABLE ecommerce.days_with_rain PARTITION BY date OPTIONS ( partition_expiration_days=60, description="weather stations with precipitation, partitioned by day" ) AS SELECT DATE(CAST(year AS INT64), CAST(mo AS INT64), CAST(da AS INT64)) AS date, (SELECT ANY_VALUE(name) FROM `bigquery-public-data.noaa_gsod.stations` AS stations WHERE stations.usaf = stn) AS station_name, -- As estações podem ter mais de um nome prcp FROM `bigquery-public-data.noaa_gsod.gsod*` AS weather WHERE prcp < 99.9 -- Filtra valores desconhecidos AND prcp > 0 -- Filtra AND _TABLE_SUFFIX >= '2018'

Clique em Verificar meu progresso para conferir o objetivo.

Agora é sua vez: crie uma tabela particionada

Verificar se a expiração da partição de dados está funcionando

Para confirmar que apenas os dados com até 60 dias estão sendo armazenados, execute a consulta DATE_DIFF e saiba quais partições expiram após esse período.

Veja abaixo uma consulta que acompanha a precipitação média para a estação meteorológica de NOAA em Wakayama, Japão, que apresenta um índice pluviométrico significativo.

• Adicione e execute esta consulta:

#standardSQL # avg monthly precipitation SELECT AVG(prcp) AS average, station_name, date, CURRENT_DATE() AS today, DATE_DIFF(CURRENT_DATE(), date, DAY) AS partition_age, EXTRACT(MONTH FROM date) AS month FROM ecommerce.days_with_rain WHERE station_name = 'WAKAYAMA' #Japan GROUP BY station_name, date, today, month, partition_age ORDER BY date DESC; # most recent days first

Tarefa 6: confirmar se a partition_age com a data mais antiga tem no máximo 60 dias

Atualize a cláusula ORDER BY para mostrar as partições mais antigas primeiro.

• Adicione e execute esta consulta:

#standardSQL # avg monthly precipitation SELECT AVG(prcp) AS average, station_name, date, CURRENT_DATE() AS today, DATE_DIFF(CURRENT_DATE(), date, DAY) AS partition_age, EXTRACT(MONTH FROM date) AS month FROM ecommerce.days_with_rain WHERE station_name = 'WAKAYAMA' #Japan GROUP BY station_name, date, today, month, partition_age ORDER BY partition_age DESC Observação: é possível que os resultados sejam diferentes quando você executar a consulta no futuro, porque os dados meteorológicos e as partições são atualizados continuamente.

Parabéns!

Você criou e consultou tabelas particionadas no BigQuery.

Próximas etapas / Saiba mais

• Se quiser aprender a criar tabelas particionadas por tempo de ingestão e que não estão vinculadas a uma determinada coluna de data ou carimbo de data/hora, consulte a seção sobre tabelas particionadas da documentação do BigQuery e exemplos.
• Você tem uma conta do Google Analytics e quer consultar seus próprios conjuntos de dados no BigQuery? Siga este guia de exportação.
• Confira outros laboratórios do BigQuery:
  • Desafios e solução de problemas da mesclagem de dados
  • Como trabalhar com dados dos tipos JSON, matriz e struct no BigQuery
  • Estimar tarifas de táxi com um modelo de previsão do BigQuery ML

Treinamento e certificação do Google Cloud

Esses treinamentos ajudam você a aproveitar as tecnologias do Google Cloud ao máximo. Nossas aulas incluem habilidades técnicas e práticas recomendadas para ajudar você a alcançar rapidamente o nível esperado e continuar sua jornada de aprendizado. Oferecemos treinamentos que vão do nível básico ao avançado, com opções de aulas virtuais, sob demanda e por meio de transmissões ao vivo para que você possa encaixá-las na correria do seu dia a dia. As certificações validam sua experiência e comprovam suas habilidades com as tecnologias do Google Cloud.

Manual atualizado em 3 de fevereiro de 2024

Laboratório testado em 1º de janeiro de 2024

Copyright 2024 Google LLC. Todos os direitos reservados. Google e o logotipo do Google são marcas registradas da Google LLC. Todos os outros nomes de produtos e empresas podem ser marcas registradas das respectivas empresas a que estão associados.