Carregar e consultar dados com a ferramenta de linha de comando bq para BigQuery

1. Introdução

O BigQuery é um banco de dados de análise NoOps totalmente gerenciado e de baixo custo desenvolvido pelo Google. Com o BigQuery, é possível consultar terabytes de dados sem um administrador de banco de dados ou infraestrutura. O BigQuery usa SQL conhecido e um modelo de cobrança em que você paga apenas pelo que usa. Assim, você pode se concentrar na análise dos dados para encontrar informações relevantes. Neste codelab, você vai usar a ferramenta de linha de comando bq para carregar um arquivo CSV local em uma nova tabela do BigQuery.

O que você vai aprender

Como usar a ferramenta de linha de comando bq para BigQuery
Como carregar arquivos de dados locais em uma tabela do BigQuery

O que é necessário

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

2. Começar a configuração

Ativar o BigQuery

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

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

Aceite os Termos de Serviço.
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 dentro dos 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.

Cloud Shell

Você vai usar o Cloud Shell, um ambiente de linha de comando executado no Google 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. crie um conjunto de dados

Crie um conjunto de dados para conter as tabelas.

O que é um conjunto de dados?

Um conjunto de dados do BigQuery é uma coleção de tabelas. Todas as tabelas em um conjunto de dados são armazenadas no mesmo local de dados. Também é possível anexar controles de acesso personalizados para limitar o acesso a um conjunto de dados e às tabelas dele.

crie um conjunto de dados

No Cloud Shell, use o comando bq mk para criar um conjunto de dados chamado "bq_load_codelab".

bq mk bq_load_codelab

Ver propriedades do conjunto de dados

Verifique se você criou o conjunto de dados visualizando as propriedades dele com o comando bq show.

bq show bq_load_codelab

A resposta será semelhante a esta:

Dataset my-project:bq_load_codelab

   Last modified           ACLs          Labels
 ----------------- -------------------- --------
  15 Jun 14:12:49   Owners:
                      projectOwners,
                      your-email@example.com
                    Writers:
                      projectWriters
                    Readers:
                      projectReaders

4. Criar o arquivo de dados

O BigQuery pode carregar dados de vários formatos, incluindo JSON delimitado por nova linha, Avro e CSV. Para simplificar, você vai usar CSV.

Criar um arquivo CSV

No Cloud Shell, crie um arquivo CSV vazio.

touch customer_transactions.csv

Abra o arquivo CSV no editor de código do Cloud Shell executando o comando "cloudshell edit", que vai abrir uma nova janela do navegador com um editor de código e um painel do Cloud Shell.

cloudshell edit customer_transactions.csv

No editor de código, insira alguns valores separados por vírgula para carregar no BigQuery.

ID,Zipcode,Timestamp,Amount,Feedback,SKU
c123,78757,2018-02-14 17:01:39Z,1.20,4.7,he4rt5
c456,10012,2018-03-14 15:09:26Z,53.60,3.1,ppiieee
c123,78741,2018-04-01 05:59:47Z,5.98,2.0,ch0c0

Para salvar o arquivo CSV, clique em Arquivo > Editar.

5. Carregar dados

Use o comando bq load para carregar o arquivo CSV em uma tabela do BigQuery.

bq load \
    --source_format=CSV \
    --skip_leading_rows=1 \
    bq_load_codelab.customer_transactions \
    ./customer_transactions.csv \
    id:string,zip:string,ttime:timestamp,amount:numeric,fdbk:float,sku:string

Você usou as seguintes opções:

O --source_format=CSV usa o formato de dados CSV ao analisar o arquivo de dados.
--skip_leading_rows=1 ignora a primeira linha do arquivo CSV porque ela é uma linha de cabeçalho.
Bq_load_codelab.customer_transactions—the first positional argument—define em qual tabela os dados devem ser carregados.
./customer_transactions.csv, o segundo argumento posicional, define qual arquivo carregar. Além de arquivos locais, o comando "bq load" pode carregar arquivos do Cloud Storage com gs://my_bucket/path/to/file URIs.
Um esquema, que pode ser definido em um arquivo de esquema JSON ou como uma lista separada por vírgulas. (Você usou uma lista separada por vírgulas para simplificar.)

Você usou o seguinte esquema na tabela "customer_transactions":

Id:string: um identificador de cliente
Zip:string: um CEP dos EUA.
Ttime:timestamp: a data e a hora em que a transação ocorreu
Amount:numeric: o valor de uma transação. Uma coluna numérica armazena dados em formato decimal, o que é útil para valores monetários.
Fdbk:float: a classificação de uma pesquisa de feedback sobre a transação.
Sku:string: um identificador do item comprado

Conferir os detalhes da tabela

Verifique se a tabela foi carregada mostrando as propriedades dela.

bq show bq_load_codelab.customer_transactions

Saída:

Table my-project:bq_load_codelab.customer_transactions

   Last modified          Schema          Total Rows   Total Bytes
 ----------------- --------------------- ------------ -------------
  15 Jun 15:13:55   |- id: string         3            159
                    |- zip: string
                    |- ttime: timestamp
                    |- amount: numeric
                    |- fdbk: float
                    |- sku: string

6. Consultar dados

Agora que os dados foram carregados, é possível consultá-los usando a IU da Web do BigQuery, o comando bq ou a API. Suas consultas podem unir seus dados a qualquer conjunto de dados (ou conjuntos de dados, desde que estejam no mesmo local) que você tenha permissão para ler.

Execute uma consulta SQL padrão que mescle seu conjunto de dados com o conjunto de dados de códigos postais dos EUA e some as transações por estado dos EUA. Use o comando bq query para executar a consulta.

bq query --nouse_legacy_sql '
SELECT SUM(c.amount) AS amount_total, z.state_code AS state_code
FROM `bq_load_codelab.customer_transactions` c
JOIN `bigquery-public-data.utility_us.zipcode_area` z
ON c.zip = z.zipcode
GROUP BY state_code
'

Esse comando vai gerar algo assim:

Waiting on bqjob_r26...05a15b38_1 ... (1s) Current status: DONE   
+--------------+------------+
| amount_total | state_code |
+--------------+------------+
|         53.6 | NY         |
|         7.18 | TX         |
+--------------+------------+

A consulta que você executou usou um conjunto de dados público e um particular. Leia esta versão comentada da mesma consulta para saber mais:

#standardSQL
SELECT
  /* Total of all transactions in the state. */
  SUM(c.amount) AS amount_total,

  /* State corresponding to the transaction's zipcode. */
  z.state_code AS state_code

/* Query the table you just constructed.
 * Note: If you omit the project from the table ID,
 *       the dataset is read from your project. */
FROM `bq_load_codelab.customer_transactions` c

/* Join the table to the zipcode public dataset. */
JOIN `bigquery-public-data.utility_us.zipcode_area` z

/* Find the state corresponding to the transaction's zipcode. */
ON c.zip = z.zipcode

/* Group over all transactions by state. */
GROUP BY state_code

7. Limpar

Exclua o conjunto de dados que você criou com o comando bq rm. Use a flag -r para remover todas as tabelas que ele contém.

bq rm -r bq_load_codelab

8. Parabéns!

Você fez upload e consultou uma tabela no BigQuery.

O que você abordou

Usar a ferramenta de linha de comando bq para interagir com o BigQuery.
Como mesclar seus dados e um conjunto de dados público com uma consulta do BigQuery.

A seguir

Saiba mais sobre:

A ferramenta de linha de comando bq
Outras maneiras de carregar dados no BigQuery.
Outros conjuntos de dados públicos disponíveis no BigQuery.
Clima, criminalidade e outros tipos de dados em TIL com BigQuery.