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 utilização. Assim, você pode se concentrar na análise dos dados para encontrar insights relevantes.

Você usará um conjunto de dados de comércio eletrônico com milhões de registros do Google Analytics referentes à Google Merchandise Store e carregados no BigQuery. Com uma cópia do conjunto de dados, você analisará os campos e linhas disponíveis para extrair insights.

Neste laboratório, você irá consultar conjuntos de dados particionados e criar suas próprias partições de conjunto de dados para melhorar o desempenho da consulta e reduzir custos.

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: crie 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: crie tabelas com partições de 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

Os resultados da consulta informam quantos dados a consulta processará.

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. 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: visualize dados processados com 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: crie 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, -- Stations may have multiple names prcp FROM `bigquery-public-data.noaa_gsod.gsod*` AS weather WHERE prcp < 99.9 -- Filter unknown values AND prcp > 0 -- Filter stations/days with no precipitation AND _TABLE_SUFFIX >= '2018' ORDER BY date DESC -- Where has it rained/snowed recently 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, -- Stations may have multiple names prcp FROM `bigquery-public-data.noaa_gsod.gsod*` AS weather WHERE prcp < 99.9 -- Filter unknown values AND prcp > 0 -- Filter 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: confirme se 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.

Termine a Quest

Este laboratório autoguiado faz parte da Quest BigQuery for Data Warehousing. Uma Quest é uma série de laboratórios relacionados que formam um programa de aprendizado. Ao concluir uma Quest, você ganha um selo como reconhecimento da sua conquista. Você pode publicar os selos com um link para eles no seu currículo on-line ou nas redes sociais. Inscreva-se nesta Quest ou em outra que tenha este laboratório para receber os créditos de conclusão imediatamente. Consulte o catálogo do Google Cloud Ensina para ver todas as Quests disponíveis.

Comece o próximo laboratório

Continue sua Quest em Desafios e solução de problemas da mesclagem de dados ou confira estas sugestões:

• Como trabalhar com dados do tipo JSON, ARRAY e STRUCT no BigQuery
• Estimar tarifas de táxi com um modelo de previsão do BigQuery ML

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ê já tem uma conta do Google Analytics e quer consultar seus próprios conjuntos de dados no BigQuery? Siga este guia de exportação.

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 25 de setembro de 2023

Laboratório testado em 25 de setembro de 2023

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.