Visão geral

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.

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

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.

Abrir 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 IU.

2. Clique em Concluído.

Tarefa 1: criar um conjunto de dados

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

1. No painel à esquerda, na seção Explorer, clique no ícone Ver ações ao lado do nome do projeto (começa com qwiklabs-gcp-...), e clique em 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). Clique em CRIAR CONJUNTO DE DADOS.

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 uma consulta.

Agora você 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 obter uma amostra dos visitantes em 2017

1. No Editor de consultas, 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 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 obter uma amostra dos visitantes em 2018

Modifique a consulta para analisar os visitantes em 2018.

1. No Editor de consultas, adicione o seguinte:

#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 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 todo o conjunto de dados e filtrar um campo de data como fizemos nas consultas anteriores, agora criaremos uma tabela particionada por data. Assim, é possível ignorar completamente a análise de registros em partições irrelevantes para nossa consulta.

Crie uma nova tabela particionada por data

1. Clique em + (consulta SQL) e adicione a consulta abaixo. Em seguida, 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 partiton_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 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: 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, 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ê verá a mensagem Esta consulta processará 0 B quando executada.

Por que não houve processamento?

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 Marcar um projeto com estrela por nome > Digite o nome do projeto.

2. Digite bigquery-public-data e clique em Marcar com estrela.

3. Abra bigquery-public-data e procure por noaa_gsod.

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

1. Primeiro, copie e cole a consulta abaixo no Editor de consultas:

#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 length(_TABLE_SUFFIX) = 4 AND CAST(_TABLE_SUFFIX AS int64) >= 2018 AND prcp > 0 -- Filter stations/days with no precipitation AND CAST(_TABLE_SUFFIX AS int64) >= 2018 ORDER BY date DESC -- Where has it rained/snowed recently LIMIT 10

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

Além disso, apesar da adição de LIMIT 10, a quantidade total de dados verificados (em torno de 457,5 MB) não foi reduzida porque 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 será semelhante a esta:

#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 length(_TABLE_SUFFIX) = 4 AND CAST(_TABLE_SUFFIX AS int64) >= 2018 AND prcp > 0 -- Filter AND CAST(_TABLE_SUFFIX AS int64) >= 2018

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

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

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

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

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