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, é possível consultar muitos terabytes de dados sem ter que gerenciar uma infraestrutura nem precisar de um administrador de banco de dados. O BigQuery usa um 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ê 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 real no BigQuery
• Como escrever uma consulta para receber insights sobre um grande conjunto de dados

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 tiver 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, você precisa criar 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 dentro dos limites do sandbox do BigQuery. Não é necessário ter uma conta de faturamento. Se você quiser remover os limites do sandbox mais tarde, inscreva-se no teste sem custo financeiro do Google Cloud para adicionar uma conta de faturamento.

O conjunto de dados da Wikipédia será carregado na próxima seção.

3. Criar um conjunto de dados

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

1. Para criar um conjunto de dados, clique no nome do projeto no painel de recursos e, em seguida, clique 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 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 antes, uma tela intermediária (abaixo da dobra) será exibida com a descrição dele. Se esse for o caso, clique em Continuar (e você não verá mais esse aviso). Esta é a aparência dessa tela única:

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

Essa máquina virtual tem 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 seguinte comando no Cloud Shell para confirmar que 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 do 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 com formato GZip. É possível carregar esse arquivo diretamente usando o utilitário de linha de comando bq. Como parte do comando de carregamento, 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 que você selecione um projeto padrão. Depois de selecioná-lo, execute novamente o comando acima 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. Esta etapa é opcional porque 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 acabou de carregar.

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

Confira o esquema da tabela na guia Esquema. 4. Descubra a quantidade de dados na tabela navegando até 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:

O Editor de consultas será exibido:

2. Encontre o número total de visualizações da Wikimedia entre 14h e 15h em 10 de abril de 2019 escrevendo esta consulta:

SELECT SUM(requests)
FROM `lab.pageviews_20190410_140000`

3. Clique em Executar:

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

Essa consulta processou 123,9 MB, embora a tabela tenha 691,4 MB. O BigQuery só processa os bytes das colunas usadas na consulta, então a quantidade total de dados processados pode ser significativamente menor que o tamanho da tabela. Com clustering e particionamento, é possível reduzir ainda mais a quantidade de dados processados.

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áginas na Wikipédia

O conjunto de dados do Wikimedia contém visualizações de páginas para todos os projetos Wikimedia (incluindo Wikipédia, Wiktionary, Wikibooks e Wikiquotes). Restrinja a consulta a páginas da Wikipédia em inglês adicionando uma instrução WHERE:

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

Observe que, 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, também é possível usar expressões regulares para consultar campos de texto. Tente uma:

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 consulta do BigQuery.

Consultar várias tabelas

Você pode selecionar um intervalo de tabelas para formar a união usando uma tabela curinga.

1. Primeiro, crie uma segunda tabela de consulta 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, faça a consulta nas duas tabelas carregadas consultando as tabelas com "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 é limitada às 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 sinalização -r para remover todas as tabelas que ela contém.

bq rm -r lab

9. Parabéns!

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

Saiba mais

• Confira o subreddit do BigQuery para saber como outras pessoas usam o BigQuery hoje mesmo.
• Encontre conjuntos de dados públicos disponíveis no BigQuery.
• Saiba como carregar dados no BigQuery.