Consultar o conjunto de dados da Wikipédia no BigQuery

1. 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 nem precisar de um administrador de banco de dados. O BigQuery usa SQL conhecido 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.

Neste codelab, você vai analisar o conjunto de dados da Wikipédia usando o BigQuery.

O que você vai aprender

• Como usar o BigQuery
• Como carregar um conjunto de dados do mundo real no BigQuery
• Como escrever uma consulta para extrair insights de um conjunto de dados grande

O que é necessário

• um projeto do Google Cloud;
• Um navegador, como o Google Chrome ou o Firefox

Pesquisa

Como você usará este tutorial?

Somente leitura Ler e fazer os exercícios

Como você classificaria sua experiência com o Google Cloud?

Iniciante Intermediário Proficiente

2. Configuração e requisitos

Ativar o BigQuery

Se você ainda não tem uma Conta do Google, crie uma.

1. Faça login no console do Google Cloud e acesse o BigQuery. Também é possível abrir a IU da Web do BigQuery digitando o URL a seguir no seu navegador.

https://console.cloud.google.com/bigquery

2. Aceite os Termos de Serviço.
3. Antes de usar o BigQuery, crie um projeto. Siga as instruções dos prompts para criar um novo projeto.

Escolha um nome e anote o ID do projeto.

O código do projeto é um nome exclusivo em todos os projetos do Google Cloud. Faremos referência a ele mais adiante neste codelab como PROJECT_ID.

Este codelab usa recursos do BigQuery nos limites do sandbox do BigQuery. Não é necessário ter uma conta de faturamento. Se você quiser remover os limites da sandbox depois, inscreva-se no teste sem custo financeiro do Google Cloud para adicionar uma conta de faturamento.

Você vai carregar o conjunto de dados da Wikipédia na próxima seção.

3. crie um conjunto de dados

Primeiro, crie um conjunto de dados no projeto. Um conjunto de dados é composto por várias tabelas.

1. Para criar um conjunto de dados, clique no nome do projeto no painel de recursos e em Criar conjunto de dados:

2. Insira lab como o ID do conjunto de dados:

3. Clique em Criar conjunto de dados para criar um conjunto de dados vazio.

4. Carregar dados com o programa de linha de comando bq

Ativar o Cloud Shell

1. No Console do Cloud, clique em Ativar o Cloud Shell.

Se você nunca iniciou o Cloud Shell, vai ver uma tela intermediária abaixo da dobra com a descrição dele. Se esse for o caso, clique em Continuar e você não a verá novamente. Esta é a aparência dessa tela única:

Leva apenas alguns instantes para provisionar e se conectar ao Cloud Shell.

Essa máquina virtual contém todas as ferramentas de desenvolvimento necessárias. Ela oferece um diretório principal persistente de 5 GB, além de ser executada no Google Cloud. Isso aprimora o desempenho e a autenticação da rede. Praticamente todo o seu trabalho neste codelab pode ser feito em um navegador ou no seu Chromebook.

Depois de se conectar ao Cloud Shell, você já estará autenticado e o projeto já estará configurado com seu ID do projeto.

2. Execute o seguinte comando no Cloud Shell para confirmar que você está autenticado:

gcloud auth list

Resposta ao comando

 Credentialed Accounts
ACTIVE  ACCOUNT
*       <my_account>@<my_domain.com>

To set the active account, run:
    $ gcloud config set account `ACCOUNT`

Observação: a ferramenta de linha de comando gcloud é a poderosa e unificada do Google Cloud. Ela vem pré-instalado no Cloud Shell. Essa ferramenta aceita preenchimento com tabulação. Para mais informações, consulte a visão geral da ferramenta de linha de comando gcloud.

3. Execute o comando a seguir no Cloud Shell para confirmar se o comando gcloud sabe sobre seu projeto:

gcloud config list project

Resposta ao comando

[core]
project = <PROJECT_ID>

Se o projeto não estiver configurado, configure-o usando este comando:

gcloud config set project <PROJECT_ID>

Resposta ao comando

Updated property [core/project].

Carregar dados no BigQuery

Para sua conveniência, alguns dos dados de 10 de abril de 2019 do conjunto de dados de visualizações de página da Wikimedia estão disponíveis no Google Cloud Storage em gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-140000.gz. O arquivo de dados é um arquivo CSV GZip. É possível carregar esse arquivo diretamente usando o utilitário de linha de comando bq. Como parte do comando de carga, você também descreve o esquema do arquivo.

Encontre o ID do projeto no Console de APIs ou na linha de comando: "gcloud config list" e procure o valor de "project".

Além disso, ao executar o bq pela primeira vez, ele pode pedir para você selecionar um projeto padrão. Depois de selecionar, execute o comando acima novamente para carregar os dados.

bq load \
  --source_format CSV \
  --field_delimiter " " \
  --allow_jagged_rows \
  --quote "" \
  --max_bad_records 3 \
  $GOOGLE_CLOUD_PROJECT:lab.pageviews_20190410_140000 \
  gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-140000.gz \
  wiki,title,requests:integer,zero:integer

O carregamento de dados pode levar alguns minutos. Aguarde a conclusão.

Você usou algumas opções avançadas para carregar o arquivo de visualizações de página:

• Defina --source_format CSV para indicar que o arquivo precisa ser analisado como um arquivo CSV. Essa etapa é opcional porque o CSV é o formato padrão.
• Defina --field_delimiter " " para indicar que um único espaço é usado para delimitar campos.
• Defina --allow_jagged_rows para incluir as linhas com menos colunas e ignorar os erros ao carregar o arquivo CSV.
• Defina --quote "" para indicar que as strings não estão entre aspas.
• Defina --max_bad_records 3 para ignorar no máximo três erros ao analisar o arquivo CSV.

Saiba mais sobre a linha de comando bq na documentação.

5. Visualizar o conjunto de dados

No console do BigQuery, abra uma das tabelas que você acabou de carregar.

1. Expanda o projeto.
2. Expanda o conjunto de dados.
3. Selecione a tabela.

O esquema da tabela pode ser visto na guia Esquema. 4. Para saber quantos dados há na tabela, acesse a guia Detalhes:

5. Abra a guia Visualização para conferir uma seleção de linhas da tabela.

6. Criar uma consulta

1. Clique em Escrever nova consulta:

Isso abre o Editor de consultas:

2. Para encontrar o número total de visualizações da Wikimedia entre 14h e 15h do dia 10 de abril de 2019, escreva esta consulta:

SELECT SUM(requests)
FROM `lab.pageviews_20190410_140000`

3. Clique em Executar:

Em alguns segundos, o resultado é listado na parte de baixo e também informa quantos dados foram processados:

Essa consulta processou 123,9 MB, mesmo que a tabela tenha 691,4 MB. O BigQuery processa apenas os bytes das colunas usadas na consulta. Portanto, a quantidade total de dados processados pode ser significativamente menor do que o tamanho da tabela. Com o clustering e o particionamento, a quantidade de dados processados pode ser ainda menor.

Você pode processar/consultar até 1 TB de dados sem custo financeiro por mês. Consulte a página de preços do BigQuery para mais detalhes.

7. Consultas mais avançadas

Encontrar visualizações de página da Wikipédia

O conjunto de dados da Wikimedia contém visualizações de página para todos os projetos da Wikimedia (incluindo Wikipédia, Wikcionário, Wikilivros e Wikiquote). Para restringir a consulta apenas às páginas da Wikipédia em inglês, adicione uma instrução WHERE:

SELECT SUM(requests), wiki
FROM `lab.pageviews_20190410_140000`
WHERE wiki = "en"
GROUP BY wiki

Ao consultar uma coluna adicional, wiki, a quantidade de dados processados aumentou de 124 MB para 204 MB.

O BigQuery é compatível com muitas das cláusulas SQL conhecidas, como CONTAINS, GROUP BY, ORDER BY e várias funções de agregação. Além disso, você também pode usar expressões regulares para consultar campos de texto. Tente uma destas opções:

SELECT title, SUM(requests) requests
FROM `lab.pageviews_20190410_140000`
WHERE
  wiki = "en"
  AND REGEXP_CONTAINS(title, 'Red.*t')
GROUP BY title
ORDER BY requests DESC

Saiba mais sobre a sintaxe do BigQuery na documentação de referência de consultas do BigQuery.

Consultar várias tabelas

É possível selecionar um intervalo de tabelas para formar a união usando uma tabela curinga.

1. Primeiro, crie uma segunda tabela para consultar carregando as visualizações de página da próxima hora em uma nova tabela:

bq load \
  --source_format CSV \
  --field_delimiter " " \
  --quote "" \
  $GOOGLE_CLOUD_PROJECT:lab.pageviews_20190410_150000 \
  gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-150000.gz \
  wiki,title,requests:integer,zero:integer

2. No Editor de consultas, consulte as duas tabelas carregadas usando "pageviews_2019" como prefixo:

SELECT title, SUM(requests) requests
FROM `lab.pageviews_2019*`
WHERE
  wiki = "en"
  AND REGEXP_CONTAINS(title, 'Red.*t')
GROUP BY title
ORDER BY requests DESC

É possível filtrar as tabelas de forma mais seletiva com a pseudocoluna _TABLE_SUFFIX. Essa consulta limita as tabelas correspondentes a 10 de abril.

SELECT title, SUM(requests) requests
FROM `lab.pageviews_2019*`
WHERE
  _TABLE_SUFFIX BETWEEN '0410' AND '0410_9999999'
  AND wiki = "en"
  AND REGEXP_CONTAINS(title, 'Red.*t')
GROUP BY title
ORDER BY requests DESC

8. Limpar

Se quiser, exclua o conjunto de dados criado com o comando bq rm. Use a flag -r para remover todas as tabelas que ele contém.

bq rm -r lab

9. Parabéns!

Você usou o BigQuery e o SQL para consultar o conjunto de dados de visualizações de página da Wikipédia no mundo real. Você tem o poder de consultar conjuntos de dados na escala de petabytes!

Saiba mais

• Confira como outras pessoas usam o BigQuery no subreddit do BigQuery.
• Encontre conjuntos de dados públicos disponíveis no BigQuery.
• Aprenda a carregar dados no BigQuery.