Como usar o BigQuery com Python

1. Visão geral

O BigQuery é o data warehouse de análise de baixo custo, totalmente gerenciado e com escala em petabyte, desenvolvido pelo Google. O BigQuery é NoOps. Não há infraestrutura para gerenciar, e você não precisa de um administrador de banco de dados. Assim, é possível se concentrar na análise de dados para encontrar insights significativos, usar um SQL conhecido e aproveitar nosso modelo de pagamento por uso.

Neste codelab, você vai usar as bibliotecas de cliente do Google Cloud para Python e consultar os conjuntos de dados públicos do BigQuery com Python.

O que você vai aprender

Como usar o Cloud Shell
Como ativar a API BigQuery
Como autenticar solicitações de API
Como instalar a biblioteca de cliente do Python
Como consultar as obras de Shakespeare
Como consultar o conjunto de dados do GitHub
Como ajustar o armazenamento em cache e mostrar estatísticas

O que é necessário

Um projeto do Google Cloud
Um navegador, como o Chrome ou o Firefox
Familiaridade com o Python

Pesquisa

Como você vai usar este tutorial?

Apenas leitura

Ler e fazer os exercícios

Como você classificaria sua experiência com Python?

Iniciante

Intermediário

Proficiente

Como você classificaria sua experiência de uso dos serviços do Google Cloud?

Iniciante

Intermediário

Proficiente

2. Configuração e requisitos

Configuração de ambiente autoguiada

Faça login no Console do Google Cloud e crie um novo projeto ou reutilize um existente. Crie uma conta do Gmail ou do Google Workspace, se ainda não tiver uma.

O Nome do projeto é o nome de exibição para os participantes do projeto. Ele é uma string de caracteres que não é usada pelas APIs do Google e pode ser atualizada a qualquer momento.
O ID do projeto precisa ser exclusivo em todos os projetos do Google Cloud e não pode ser alterado após a definição. O Console do Cloud gera automaticamente uma string única, geralmente não importa o que seja. Na maioria dos codelabs, você precisará fazer referência ao ID do projeto, que geralmente é identificado como PROJECT_ID. Então, se você não gostar dele, gere outro ID aleatório ou crie um próprio e veja se ele está disponível. Em seguida, ele fica "congelado" depois que o projeto é criado.
Há um terceiro valor, um Número de projeto, que algumas APIs usam. Saiba mais sobre esses três valores na documentação.

Em seguida, você precisará ativar o faturamento no Console do Cloud para usar os recursos/APIs do Cloud. A execução deste codelab não será muito cara, se tiver algum custo. Para encerrar os recursos e não gerar cobranças além deste tutorial, siga as instruções de "limpeza" encontradas no final do codelab. Novos usuários do Google Cloud estão qualificados para o programa de US$ 300 de avaliação sem custos.

Inicie o Cloud Shell

Embora o Google Cloud e o Spanner possam ser operados remotamente do seu laptop, neste codelab usaremos o Google Cloud Shell, um ambiente de linha de comando executado no Cloud.

Ativar o Cloud Shell

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.

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`

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

3. Ativar a API

A API BigQuery deve estar ativada por padrão em todos os projetos do Google Cloud. Para verificar se isso é verdade, execute o seguinte comando no Cloud Shell: você precisa estar na lista do BigQuery:

gcloud services list

O BigQuery vai aparecer na lista:

NAME                              TITLE
bigquery.googleapis.com           BigQuery API

...

Se a API BigQuery não estiver ativada, use o seguinte comando no Cloud Shell para ativá-la:

gcloud services enable bigquery.googleapis.com

4. Autenticar as solicitações de API

Para fazer solicitações à API BigQuery, você precisa usar uma conta de serviço. Uma conta de serviço pertence ao seu projeto. Ela é usada pela biblioteca de cliente Python do Google Cloud para fazer solicitações da API BigQuery. Como qualquer outra conta de usuário, uma conta de serviço é representada por um endereço de e-mail. Nesta seção, você vai usar o SDK Cloud para criar uma conta de serviço e, em seguida, criar as credenciais necessárias para realizar a autenticação.

Primeiro, defina uma variável de ambiente PROJECT_ID:

export PROJECT_ID=$(gcloud config get-value core/project)

Em seguida, crie uma conta de serviço para acessar a API BigQuery usando:

gcloud iam service-accounts create my-bigquery-sa \
  --display-name "my bigquery service account"

Em seguida, crie credenciais que o código Python vai usar para fazer login como a nova conta de serviço. Crie e salve essas credenciais como um arquivo JSON ~/key.json usando o seguinte comando:

gcloud iam service-accounts keys create ~/key.json \
  --iam-account my-bigquery-sa@${PROJECT_ID}.iam.gserviceaccount.com

Por fim, defina a variável de ambiente GOOGLE_APPLICATION_CREDENTIALS, que é usada pela biblioteca de cliente Python do BigQuery, abordada na próxima etapa, para encontrar suas credenciais. A variável de ambiente deve ser definida para o caminho completo do arquivo JSON de credenciais que você criou. Para isso, use:

export GOOGLE_APPLICATION_CREDENTIALS=~/key.json

Saiba mais sobre autenticar a API BigQuery.

5. Configurar o controle de acesso

O BigQuery usa o Identity and Access Management (IAM) para gerenciar o acesso aos recursos. O BigQuery tem vários papéis predefinidos (usuário, dataOwner, dataViewer etc.) que podem ser atribuídos à conta de serviço criada na etapa anterior. Saiba mais sobre o controle de acesso na documentação do BigQuery.

Antes de consultar conjuntos de dados públicos, verifique se a conta de serviço tem pelo menos o papel roles/bigquery.user. No Cloud Shell, execute o comando a seguir para atribuir a função de usuário à conta de serviço:

gcloud projects add-iam-policy-binding ${PROJECT_ID} \
  --member "serviceAccount:my-bigquery-sa@${PROJECT_ID}.iam.gserviceaccount.com" \
  --role "roles/bigquery.user"

Execute o comando a seguir para verificar se a conta de serviço tem o papel de usuário:

gcloud projects get-iam-policy $PROJECT_ID

Você verá o seguinte:

bindings:
- members:
  - serviceAccount:my-bigquery-sa@<PROJECT_ID>.iam.gserviceaccount.com
  role: roles/bigquery.user
...

6. Instale a biblioteca de cliente

Instale a biblioteca de cliente do BigQuery para Python:

pip3 install --user --upgrade google-cloud-bigquery

Agora você pode programar com a API BigQuery.

7. Consultar as obras de Shakespeare

Um conjunto de dados público é qualquer conjunto de dados armazenado no BigQuery e disponibilizado para o público em geral. Há muitos outros conjuntos de dados públicos disponíveis para consulta. Embora alguns conjuntos de dados sejam hospedados pelo Google, a maioria é hospedada por terceiros. Para mais informações, consulte a página Conjuntos de dados públicos.

Além dos conjuntos de dados públicos, o BigQuery oferece um número limitado de tabelas de amostra que podem ser consultadas. Essas tabelas ficam no conjunto de dados bigquery-public-data:samples. A tabela shakespeare no conjunto de dados samples contém um índice de palavras das obras de Shakespeare. Ele informa o número de vezes que cada palavra aparece em cada acervo.

Nesta etapa, você vai consultar a tabela shakespeare.

Primeiro, no Cloud Shell, crie um aplicativo simples em Python que será usado para executar as amostras da API Translation.

mkdir bigquery-demo
cd bigquery-demo
touch app.py

Abra o editor de código no canto superior direito do Cloud Shell:

Navegue até o arquivo app.py na pasta bigquery-demo e substitua o código pelo seguinte.

from google.cloud import bigquery

client = bigquery.Client()

query = """
    SELECT corpus AS title, COUNT(word) AS unique_words
    FROM `bigquery-public-data.samples.shakespeare`
    GROUP BY title
    ORDER BY unique_words
    DESC LIMIT 10
"""
results = client.query(query)

for row in results:
    title = row['title']
    unique_words = row['unique_words']
    print(f'{title:<20} | {unique_words}')

Reserve um ou dois minutos para estudar o código e ver como a tabela está sendo consultada.

No Cloud Shell, execute o app:

python3 app.py

Uma lista de palavras e suas ocorrências vai aparecer:

hamlet               | 5318
kinghenryv           | 5104
cymbeline            | 4875
troilusandcressida   | 4795
kinglear             | 4784
kingrichardiii       | 4713
2kinghenryvi         | 4683
coriolanus           | 4653
2kinghenryiv         | 4605
antonyandcleopatra   | 4582

8. Consultar o conjunto de dados do GitHub

Para se familiarizar mais com o BigQuery, agora você vai executar uma consulta no conjunto de dados público do GitHub. Você encontra as mensagens de commit mais comuns no GitHub. Você também vai usar o console da Web do BigQuery para visualizar e executar consultas ad hoc.

Para ver como os dados são, abra o conjunto de dados do GitHub na interface da Web do BigQuery:

Abra a tabela github_repos.

Clique no botão "Visualizar" para ver como os dados aparecem:

Navegue até o arquivo app.py na pasta bigquery_demo e substitua o código pelo seguinte.

from google.cloud import bigquery

client = bigquery.Client()

query = """
    SELECT subject AS subject, COUNT(*) AS num_duplicates
    FROM bigquery-public-data.github_repos.commits
    GROUP BY subject
    ORDER BY num_duplicates
    DESC LIMIT 10
"""
results = client.query(query)

for row in results:
    subject = row['subject']
    num_duplicates = row['num_duplicates']
    print(f'{subject:<20} | {num_duplicates:>9,}')

Reserve um ou dois minutos para estudar o código e ver como a tabela está sendo consultada para as mensagens de commit mais comuns.

No Cloud Shell, execute o app:

python3 app.py

Você vai ver uma lista de mensagens de commit e suas ocorrências:

Update README.md     | 1,685,515
Initial commit       | 1,577,543
update               |   211,017
                     |   155,280
Create README.md     |   153,711
Add files via upload |   152,354
initial commit       |   145,224
first commit         |   110,314
Update index.html    |    91,893
Update README        |    88,862

9. Armazenamento em cache e estatísticas

O BigQuery armazena em cache os resultados das consultas. Como resultado, as consultas subsequentes levam menos tempo. É possível desativar o armazenamento em cache com opções de consulta. O BigQuery também acompanha estatísticas sobre consultas, como hora de criação, hora de término e total de bytes processados.

Nesta etapa, você vai desativar o armazenamento em cache e mostrar estatísticas sobre as consultas.

Navegue até o arquivo app.py na pasta bigquery_demo e substitua o código pelo seguinte.

from google.cloud import bigquery

client = bigquery.Client()

query = """
    SELECT subject AS subject, COUNT(*) AS num_duplicates
    FROM bigquery-public-data.github_repos.commits
    GROUP BY subject
    ORDER BY num_duplicates
    DESC LIMIT 10
"""
job_config = bigquery.job.QueryJobConfig(use_query_cache=False)
results = client.query(query, job_config=job_config)

for row in results:
    subject = row['subject']
    num_duplicates = row['num_duplicates']
    print(f'{subject:<20} | {num_duplicates:>9,}')

print('-'*60)
print(f'Created: {results.created}')
print(f'Ended:   {results.ended}')
print(f'Bytes:   {results.total_bytes_processed:,}')

Alguns pontos importantes sobre o código: Primeiro, o armazenamento em cache é desativado ao introduzir QueryJobConfig e definir use_query_cache como "false". Em segundo lugar, você acessou as estatísticas sobre a consulta no objeto de job.

No Cloud Shell, execute o app:

python3 app.py

Como antes, você vai ver uma lista de mensagens de commit e suas ocorrências. Além disso, você também vai encontrar algumas estatísticas sobre a consulta no final:

Update README.md     | 1,685,515
Initial commit       | 1,577,543
update               |   211,017
                     |   155,280
Create README.md     |   153,711
Add files via upload |   152,354
initial commit       |   145,224
first commit         |   110,314
Update index.html    |    91,893
Update README        |    88,862
------------------------------------------------------------
Created: 2020-04-03 13:30:08.801000+00:00
Ended:   2020-04-03 13:30:15.334000+00:00
Bytes:   2,868,251,894

10. Como carregar dados no BigQuery

Se quiser consultar seus próprios dados, carregue-os no BigQuery. O BigQuery é compatível com o carregamento de dados de várias fontes, incluindo o Cloud Storage, outros serviços do Google e outras fontes legíveis. Você pode até transmitir seus dados usando inserções de streaming. Para mais informações, consulte a página Como carregar dados no BigQuery.

Nesta etapa, você vai carregar um arquivo JSON armazenado no Cloud Storage em uma tabela do BigQuery. O arquivo JSON está localizado em gs://cloud-samples-data/bigquery/us-states/us-states.json

Se você quiser saber o conteúdo do arquivo JSON, use a ferramenta de linha de comando gsutil para fazer o download dele no Cloud Shell:

gsutil cp gs://cloud-samples-data/bigquery/us-states/us-states.json .

Ele contém a lista de estados dos EUA, e cada estado é um documento JSON em uma linha separada:

head us-states.json

{"name": "Alabama", "post_abbr": "AL"}
{"name": "Alaska", "post_abbr":  "AK"}
...

Para carregar esse arquivo JSON no BigQuery, navegue até o arquivo app.py na pasta bigquery_demo e substitua o código pelo seguinte:

from google.cloud import bigquery

client = bigquery.Client()

gcs_uri = 'gs://cloud-samples-data/bigquery/us-states/us-states.json'

dataset = client.create_dataset('us_states_dataset')
table = dataset.table('us_states_table')

job_config = bigquery.job.LoadJobConfig()
job_config.schema = [
    bigquery.SchemaField('name', 'STRING'),
    bigquery.SchemaField('post_abbr', 'STRING'),
]
job_config.source_format = bigquery.SourceFormat.NEWLINE_DELIMITED_JSON

load_job = client.load_table_from_uri(gcs_uri, table, job_config=job_config)

print('JSON file loaded to BigQuery')

Estude por um ou dois minutos como o código carrega o arquivo JSON e cria uma tabela com um esquema em um conjunto de dados.

No Cloud Shell, execute o app:

python3 app.py

Um conjunto de dados e uma tabela são criados no BigQuery.

Para verificar se o conjunto de dados foi criado, acesse o console do BigQuery. Um novo conjunto de dados e uma nova tabela vão aparecer. Mude para a guia de visualização da tabela para conferir seus dados:

11. Parabéns!

Você aprendeu a usar o BigQuery com Python.

Limpeza

Para evitar cobranças dos recursos usados neste tutorial na conta do Google Cloud, siga estas etapas:

No console do Cloud, acesse a página Gerenciar recursos.
Na lista de projetos, selecione seu projeto e clique em Excluir.
Na caixa de diálogo, digite o ID do projeto e clique em Encerrar para excluí-lo.

Saiba mais

Google BigQuery: https://cloud.google.com/bigquery/docs/
Python no Google Cloud: https://cloud.google.com/python/
Bibliotecas de cliente do Cloud para Python: https://googleapis.github.io/google-cloud-python/

Licença

Este conteúdo está sob a licença Atribuição 2.0 Genérica da Creative Commons.