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

Você vai usar um conjunto de dados de e-commerce com milhões de registros do Google Analytics referentes à Google Merchandise Store e carregados no BigQuery. Você tem uma cópia do conjunto de dados usado neste laboratório e analisará os campos e linhas disponíveis para extrair insights.

Neste laboratório, você vai consultar conjuntos de dados particionados e criar suas próprias partições para melhorar o desempenho das consultas e reduzir custos.

Configuração

Para cada laboratório, você recebe um novo projeto do Google Cloud e um conjunto de recursos por um determinado período e sem custos financeiros.

1. Faça login no Qwiklabs em uma janela anônima.

2. Confira o tempo de acesso do laboratório (por exemplo, 1:15:00) e finalize todas as atividades nesse prazo.
   Não é possível pausar o laboratório. Você pode reiniciar o desafio, mas vai precisar refazer todas as etapas.

3. Quando tudo estiver pronto, clique em Começar o laboratório.

4. Anote as credenciais (Nome de usuário e Senha). É com elas que você vai fazer login no Console do Google Cloud.

5. Clique em Abrir Console do Google.

6. Clique em Usar outra conta, depois copie e cole as credenciais deste laboratório nos locais indicados.
   Se você usar outras credenciais, vai receber mensagens de erro ou cobranças.

7. Aceite os termos e pule a página de recursos de recuperação.

Abra o BigQuery no Console do Cloud

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

Você vai 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 lista as atualizações da interface.

2. Clique em Concluído.

Tarefa 1: Criar um conjunto de dados

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

1. Faça isso no seu projeto clicando no ícone Ver ações ao lado do ID do projeto na seção Explorer e selecione CRIAR CONJUNTO DE DADOS.

2. Defina o ID do conjunto de dados como ecommerce. Não altere as outras opções ("Local dos dados", "Expiração da tabela padrão").

3. Clique em Criar conjunto de dados.

Tarefa 2: Criar tabelas com partições 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ê melhora o desempenho das consultas e controla os custos, já que reduz o número de bytes lidos por cada consulta.

Agora você vai criar uma nova tabela e vincular uma coluna de data ou carimbo de data/hora como uma partição. Antes disso, precisamos analisar 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. Na Consulta sem título, adicione a consulta abaixo. 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 vai processar 1,74 GB quando executada".

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

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. Na Consulta sem título, adicione a consulta abaixo:

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

Os resultados da consulta vão informar quantos dados a consulta vai 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. Dessa forma, não precisamos analisar registros em partições irrelevantes para a consulta.

Crie uma nova tabela particionada por data

1. Clique em + Consulta SQL para adicionar 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.

4. Confirme se você vê o seguinte na seção Informações da tabela:

• Particionado por: dia
• Particionamento em: date_formatted

Observação: as partições dentro de tabelas particionadas na sua conta do Qwiklabs expiram automaticamente 60 dias após a data especificada na coluna. Se você tiver uma conta pessoal do GCP 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.

Tarefa 3: Visualizar 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, cerca de 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ê vai encontrar a mensagem Esta consulta vai processar 0 B quando executada.

Por que não houve processamento?

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 também 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.

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

1. No painel à esquerda, 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.

5. Em seguida, copie e cole a consulta abaixo em Consulta sem título:

#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 >= '2021' ORDER BY date DESC -- Where has it rained/snowed recently LIMIT 10

6. Observe que o caractere curinga * da tabela é usado na cláusula FROM para limitar a quantidade de tabelas mencionadas no filtro TABLE_SUFFIX.

7. Veja também que, apesar da adição de LIMIT 10, a quantidade total de dados verificados (em torno de 141,6 MB) não foi reduzida porque ainda não há partições.

8. Clique em Executar.

9. 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 >= '2021'

Confirme 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 veja 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 (em inglês), 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 mais antiga tem no máximo 60 dias

Atualize a cláusula ORDER BY para mostrar as partições mais antigas primeiro. Use a data que você vê ali.

• 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.

Finalize o laboratório

Clique em Terminar o laboratório após a conclusão. O Google Cloud Ensina remove os recursos usados e limpa a conta por você.

Você vai poder avaliar sua experiência no laboratório. Basta selecionar o número de estrelas, digitar um comentário e clicar em Enviar.

O número de estrelas indica o seguinte:

• 1 estrela = muito insatisfeito
• 2 estrelas = insatisfeito
• 3 estrelas = neutro
• 4 estrelas = satisfeito
• 5 estrelas = muito satisfeito

Feche a caixa de diálogo se não quiser enviar feedback.

Para enviar seu feedback, fazer sugestões ou correções, use a guia Suporte.

Copyright 2020 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.