Esta página foi traduzida pela API Cloud Translation.

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 ele, é possível consultar terabytes de dados sem um administrador de banco de dados ou infraestrutura. O BigQuery usa um SQL conhecido e um modelo de cobrança com base no uso. Assim, você pode se concentrar na análise dos dados para encontrar informações relevantes. Neste codelab, você 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 tiver 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, 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 com os 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.

Cloud Shell

Você vai usar o Cloud Shell, um ambiente de linha de comando em execução no Google Cloud.

Ativar o Cloud Shell

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.

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

3. Criar 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 dos dados. Você também pode 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 as 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, use 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 de edição cloudshell, 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

Salve o arquivo CSV clicando em Arquivo > Editar.

5. Carregar dados

Use o comando "bq load" para carregar seu 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:

--source_format=CSV usa o formato de dados CSV ao analisar o arquivo de dados.
--skip_leading_rows=1 pula a primeira linha do arquivo CSV por ser 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 dos 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. Para simplificar, você usou uma lista separada por vírgulas.

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 foi realizada.
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 nota de uma pesquisa de feedback sobre a transação.
Sku:string: um identificador para o 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, consulte-os usando a interface da Web do BigQuery, o comando bq ou a API. Suas consultas podem mesclar seus dados com 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 agrupe seu conjunto de dados com o conjunto de dados de CEPs 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 deve 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 seu conjunto de dados privado. Saiba mais lendo esta versão comentada da mesma consulta:

#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 de uma tabela no BigQuery e a consultou.

Conteúdo abordado

Como usar a ferramenta de linha de comando bq para interagir com o BigQuery.
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, crime e outros tipos de dados em TIL com o BigQuery.