Como usar a ferramenta de linha de comando bq

O bq é uma ferramenta baseada em Python que permite o acesso ao BigQuery pela linha de comando.

Instalação

Instalar o Google Cloud SDK

Uso geral

Posicionamento da sinalização

O bq é compatível com dois tipos de sinalização: a comum e a de comando. Use-as nesta ordem:

bq --common_flags <bq_command> --command-specific_flags <command_arguments>
  • Sinalizações comuns são aplicadas a todos os comandos como --project_id ou --apilog. Execute bq --helpshort para obter uma lista das sinalizações comuns mais utilizadas.
  • Sinalizações específicas de comando são aplicadas a um determinado comando. Por exemplo, -p, -j e -d no comando ls para listar projetos, jobs e conjuntos de dados, respectivamente.

A colocação de sinalizações comuns depois do comando geralmente resulta em falha.

Por exemplo: bq --format=json ls -d

Ajuda e depuração

  • Para ver uma lista completa de comandos, execute bq help
  • Para obter ajuda sobre um comando específico, execute bq help <command>
  • Para ver as solicitações reais enviadas e recebidas, o que é útil para depurar seus aplicativos, adicione a sinalização --apilog=<file> para salvar um registro das operações em arquivo. O uso de - em vez de um caminho de arquivo imprimirá o registro no console. O bq funciona por meio de chamadas REST à API, o que pode ser útil de ver. Também é útil anexar esse registro ao relatar problemas.
  • Para ajudar na solução de problemas, use a sinalização --format=prettyjson ao receber o status do job. Ela resulta no objeto de erro, incluindo a propriedade reason, que você pode usar para pesquisar etapas de solução de erros. Por exemplo, bq --format=prettyjson show -j <job id>

Como configurar valores padrão de sinalizações comuns

Na primeira vez que o bq é executado, o ID padrão do projeto é solicitado. Ele é armazenado no arquivo .bigqueryrc.

Para atualizá-lo ou adicionar valores de sinalização comuns, modifique o arquivo .bigqueryrc. Adicione valores padrão, um por linha, como flagname=flagvalue. Configure apenas padrões de sinalização comuns nesse arquivo. Quando você tenta executar o bq com sinalizações específicas de comando, isso causa um erro. Esse arquivo é lido toda vez que essa ferramenta é executada no modo de uso único, ou seja, as alterações são aplicadas imediatamente. No entanto, ao executá-la no modo interativo (bq shell), é necessário reiniciar o shell antes de importar as alterações.

cat ~/.bigqueryrc
retorna:
project_id = 1092187650
dataset_id=mydataset

Execução de operações assíncronas

Com a ferramenta bq, você executa comandos assíncronos atualizando o contador de status na linha de comando. Se um comando demorar para ser executado, você poderá solicitar o ID do trabalho quando este começar a rodar e retornar o controle imediatamente, e pedir para o bq se conectar com aquele trabalho mais tarde. Dessa forma, a linha de comando é liberada para outras tarefas. Isso é útil apenas para jobs de longa duração como consultas ou carregamentos.

Para iniciar um trabalho assíncrono e solicitar o código, chame o comando bq com a sinalização --nosync, que retornará o código do job. Para receber os resultados do job ou imprimir seu status, transmita esse código no comando bq wait <job_id>.

No exemplo a seguir, você inicia um job de carregamento assíncrono, lista os conjuntos de dados no banco de dados atual e faz uma chamada para receber os resultados do job.

$ bq --nosync load mydataset.names2010 gs://bigquerybucket/yob2010.txt name:string,gender:string,count:integer
Successfully started load job job_f0afc92f7a9043849e5cae7b2e7ea2f9
$ bq ls
   datasetId
 -------------
  olddataset
  mydataset
$ bq wait job_f0afc92f7a9043849e5cae7b2e7ea2f9

Execução do bq em modo interativo

Execute o bq em modo interativo. Nesse caso, não é preciso acrescentar "bq" na frente dos comandos. Para iniciar o modo interativo, execute bq shell. O prompt é o código do projeto padrão. Para sair do modo interativo, digite "exit".

bq shell
Welcome to BigQuery! (Type help for more information.)
10000000021> ls
   datasetId
 -------------
  mydataset
10000000021> exit
Goodbye.

Sinalizações comuns úteis

As sinalizações comuns são usadas entre o bq e o comando. Para uma lista completa de sinalizações, chame bq --help.

Veja as sinalizações mais úteis:

  • --apilog - Ative o registro de todas as solicitações e respostas do servidor. Se nenhuma string é fornecida (--apilog=), o registro é enviado para o stdout. Caso contrário, ele é salvo no arquivo (--apilog=filename).
  • --format [none|json|prettyjson|csv|sparse|pretty] - O formato de saída.

Lista de projetos, conjuntos de dados e tabelas

Para listar objetos, use o comando bq ls com a sintaxe a seguir:

bq ls [<project_id>:][<dataset_id>]

<project_id> e <dataset_id> são opcionais se você tem esses valores definidos no arquivo .bigqueryrc. No entanto, os valores transmitidos para bq ls modificarão os definidos em .bigqueryrc. Um ID de projeto não é um nome amigável; um ID de projeto é geralmente apenas uma sequência de dígitos.

Mais informações:

Trabalho com projetos

Listar projetos

Para listar todos os projetos:

bq ls -p

Configurar um projeto padrão

Quando você executa o bq pela primeira vez, um ID de projeto padrão é solicitado e salvo no arquivo .bigqueryrc. Edite esse arquivo para alterar o valor do projeto padrão.

Consultar o projeto padrão

Execute cat ~/.bigqueryrc para consultar os seus valores padrão. Como alternativa, quando você executa no modo interativo, o código do projeto padrão é listado no prompt.

Trabalho com conjuntos de dados

Criar um conjunto de dados

Use o comando bq mk para criar um conjunto de dados:

bq mk [DATASET_ID]

em que [DATASET_ID] é um código de conjunto de dados válido que:

  • contém apenas letras, números ou sublinhados [a-zA-Z0-9_];
  • é único dentro do projeto.

Códigos de conjuntos de dados diferenciam maiúsculas de minúsculas. Por exemplo, my_dataset e My_Dataset são códigos diferentes que coexistem no mesmo projeto.

Configurar um conjunto de dados padrão

Especifique um conjunto de dados padrão para que não tenha que qualificar as tabelas ou outras ações com um código de conjunto quando precisar dele. Para isso, adicione a seguinte linha ao arquivo .bigqueryrc:

dataset_id=[DATASET_ID]

Veja um modo rápido de fazer isso. Basta substituir [DATASET_ID] pelo código do seu conjunto de dados:

echo dataset_id=[DATASET_ID] >> ~/.bigqueryrc

Listar conjuntos de dados

A sintaxe varia dependendo do tipo de projeto, se é padrão ou definido pelo conjunto de dados:

# List datasets in the default project:
bq ls

# List datasets in another project:
bq ls [PROJECT_ID]:

# List datasets when you have a default dataset defined:
bq ls -d
  OR
bq ls :

Trabalho com tabelas

Receber informações de tabelas

bq show <project_id>:<dataset_id>.<table_id>

Exemplo

bq show publicdata:samples.shakespeare
    tableId      Last modified                  Schema
 ------------- ----------------- ------------------------------------
  shakespeare   01 Sep 13:46:28   |- word: string (required)
                                  |- word_count: integer (required)
                                  |- corpus: string (required)
                                  |- corpus_date: integer (required)

Visualizar dados de tabelas

bq head [-n <rows>] <project_id>:<dataset_id>.<table_id>

Exemplo

bq head -n 10 publicdata:samples.shakespeare
+--------------+------------+--------------+-------------+
|     word     | word_count |    corpus    | corpus_date |
+--------------+------------+--------------+-------------+
| brave        |          6 | 1kinghenryiv |        1597 |
| profession   |          1 | 1kinghenryiv |        1597 |
| treason      |          2 | 1kinghenryiv |        1597 |
| Ned          |          9 | 1kinghenryiv |        1597 |
| answered     |          1 | 1kinghenryiv |        1597 |
| Perceived    |          1 | 1kinghenryiv |        1597 |
| 'You         |          1 | 1kinghenryiv |        1597 |
| degenerate   |          1 | 1kinghenryiv |        1597 |
| neighbouring |          1 | 1kinghenryiv |        1597 |
| grandam      |          1 | 1kinghenryiv |        1597 |
+--------------+------------+--------------+-------------+

Observe que o objetivo dessa operação é apenas visualizar o conteúdo, e não é uma forma eficiente de extrair uma porção grande de uma tabela. Quando não é especificado de maneira explícita, o padrão do comando é retornar 100 linhas.

Listar tabelas

A sintaxe varia dependendo do tipo de projeto, se é padrão ou definido pelo conjunto de dados:

# In the default project:
  bq ls dataset_id

# In the default project and dataset:
bq ls

# In another project or dataset:
  bq ls [project_id:][dataset_id]

Criar uma tabela a partir de um arquivo

Carregue os seguintes formatos para criar uma tabela ou anexar dados a uma tabela existente:

  • arquivos CSV, JSON ou Avro não compactados do Google Cloud Storage
  • Os arquivos de esquemas não podem ser carregados do Google Cloud Storage. Eles precisam ser locais para que uma tabela usando arquivo de dados e de esquema seja criada.

  • arquivos CSV ou JSON (compactado com gzip) do Google Cloud Storage
  • arquivos de backup do Cloud Datastore no Google Cloud Storage
  • arquivos CSV, JSON ou Avro não compactados do seu disco

Para criar uma tabela, crie um novo esquema e carregue os arquivos em chamadas separadas. Outra opção é combinar as duas ações em uma única chamada com a seguinte sintaxe:

bq load <destination_table> <data_source_uri> <table_schema>
destination_table
O nome totalmente qualificado da tabela a ser criada ou anexada, se ela já existir.
data_source_uri
O arquivo de dados CSV de origem usado para preencher a tabela. Pode ser um arquivo local não compactado ou um URI totalmente qualificado do Google Cloud Storage que aponta para um arquivo compactado com gzip ou não compactado no formato gs://bucket/file. Por exemplo, todos estes tipos de arquivos são válidos: my_file.csv, gs://[BUCKET_NAME]/my_file.csv.gz ou my_file.csv.gz.

Carregue vários arquivos em uma tabela, especificando uma lista de arquivos separada por vírgulas ou usando um único caractere curinga (*). O curinga não pode ser usado como parte do nome do intervalo.

table_schema
Uma descrição do esquema de tabela a ser usado. Pode ser o nome de um arquivo local ou uma lista de pares column_name:datatype separada por vírgulas. Neste exemplo, usaremos uma lista separada por vírgulas. Tente usar a seguinte descrição de esquema para sua tabela: name:string,gender:string,count:integer, em que "name", "gender" e "count" são marcadores atribuídos às colunas da nova tabela.

Se desejar, você também pode especificar seu esquema em um arquivo separado e fornecer esse arquivo como o esquema da tabela. Seu arquivo de esquema deve conter um único objeto de matriz com entradas que especifiquem as seguintes propriedades:

  • "name": nome da coluna
  • "type": tipo de dados, por exemplo, string. Para uma lista completa dos tipos de dados permitidos, consulte Tipos de dados do BigQuery
  • "mode" (opcional): se o campo é nulo

Um arquivo de esquema de amostra é semelhante a:

[
  {"name": "name", "type": "string", "mode": "required"},
  {"name": "gender", "type": "string", "mode": "nullable"},
  {"name": "count", "type": "integer", "mode": "required"}
]

Consulte o guia início rápido da ferramenta de linha de comando bq para ver as instruções detalhadas de como criar e preencher uma tabela usando o bq.

Ao usar o comando bq load, especifique as seguintes sinalizações opcionais:

--source_format
Tipo: string
Descrição: o tipo de arquivo de origem, a saber: JSON, CSV, Avro ou backup do Cloud Datastore. O valor padrão é CSV.
Valores válidos:
  • CSV
  • NEWLINE_DELIMITED_JSON
  • AVRO
  • DATASTORE_BACKUP
Utilização:
bq load [--source_format=NEWLINE_DELIMITED_JSON|CSV|AVRO|DATASTORE_BACKUP]  <destination_table> <data_source_uri> [<table_schema>]
--field_delimiter, -F
Tipo: string
Descrição: o caractere que indica o limite entre as colunas do arquivo de entrada. Por padrão, é a vírgula.
Valores válidos: no BigQuery, a string delimitadora é convertida para a codificação ISO-8859-1, e o primeiro byte da string codificada é usado para dividir os dados no estado binário bruto. Também é possível usar a sequência de escape "\t" para especificar um separador de tabulação. O valor padrão é a vírgula (',').
Utilização:
bq load -F '|'  <destination_table> <data_source_uri> [<table_schema>]
bq load --field_delimiter='|' <destination_table> <data_source_uri> [<table_schema>]
--encoding, -E
Tipo: string
Descrição: a codificação de caracteres usada pelo arquivo de entrada.
Valores válidos:
  • UTF-8
  • ISO-8859-1
Utilização:
bq load -E ISO-8859-1 <destination_table> <data_source_uri> [<table_schema>]
bq load --encoding=UTF-8 <destination_table> <data_source_uri> [<table_schema>]
--max_bad_records
Tipo: integer
Descrição: o número máximo de linhas inválidas que são ignoradas antes de o carregamento do job ser cancelado e de as atualizações serem canceladas. Quando o valor é maior do que 0, o job é concluído, desde que o número de registros inválidos não ultrapasse esse valor. Isso é útil quando se quer carregar arquivos com registros corrompidos. O valor padrão desse parâmetro é 0, ou seja, todas as linhas precisam ser válidas.
Valores válidos: qualquer número inteiro
Utilização:
bq load --max_bad_records=3 <destination_table> <data_source_uri> [<table_schema>]
--skip_leading_rows
Tipo: integer
Descrição: pular um determinado número de linhas do início. Isso é útil para pular as linhas de cabeçalho do arquivo CSV de origem. O valor padrão desse parâmetro é 0, ou seja, todas as linhas de dados são consideradas.
Valores válidos: qualquer número inteiro
Utilização:
bq load --skip_leading_rows=1 <destination_table> <data_source_uri> [<table_schema>]
--[no]autodetect
Tipo: boolean
Descrição: ativar a detecção automática de esquema e opções para formatos como CSV e JSON que não são autodescritivos. O valor padrão é --noautodetect.
Valores válidos: --autodetect e --noautodetect
Utilização:
bq load --autodetect <destination_table> <data_source_uri>

Codificações de caracteres

Por padrão, o serviço BigQuery espera que todos os dados de origem estejam em formato UTF-8. Como opção, se você tem arquivos CSV no formato ISO-8859-1, especifique a codificação de maneira explícita ao importar os dados. A conversão para o formato UTF-8 será feito no BigQuery durante o processo de importação. Atualmente, só é possível importar dados que estejam em formato ISO-8859-1 ou UTF-8. Quando especificar a codificação de caracteres de seus dados, tenha em mente o seguinte:

  • Se você não especificar uma codificação ou se especificar explicitamente que os dados estão em formato UTF-8, mas fornecer um arquivo CSV que não esteja em formato UTF-8, o BigQuery tentará converter seu arquivo CSV em UTF-8.

    Geralmente, seus dados serão importados com sucesso, mas talvez não correspondam byte a byte com o que você espera. Para evitar isso, especifique a codificação correta e execute o processo de importação novamente.

  • Os delimitadores devem estar no formato ISO-8859-1.

    Geralmente, é uma boa prática usar um delimitador padrão, como, por exemplo, um tab, uma barra vertical ou uma vírgula.

  • Se o BigQuery não consegue converter um caractere, ele é convertido no caractere substituto padrão em formato Unicode: �.
  • Os arquivos JSON precisam estar no formato UTF-8 sempre.

Copiar uma tabela existente

Para copiar uma tabela, execute o comando bq cp:

bq cp <source_table> <destination_table>

Veja um exemplo desse comando:

bq cp dataset.mytable dataset2.mynewtable

Também é possível copiar tabelas de diferentes projetos especificando o código do projeto no caminho de origem e de destino:

bq cp 123456789123:dataset.mytable 0987654321098:dataset2.mynewtable

Observe que, para copiar uma tabela, é preciso especificar uma tabela de destino exclusiva. Por exemplo, se você já tem uma tabela chamada mynewtable no dataset2, o comando acima falha e a ferramenta de linha de comando gera uma exceção. Para anexar ou substituir uma tabela já existente, inicie um job de cópia com essa finalidade usando a API.

Criar uma tabela a partir de uma consulta

Execute uma consulta e especifique a sinalização --destination_table

bq query --destination_table=mydataset.mytable "SELECT name,count FROM mydataset.babynames WHERE gender = 'M' ORDER BY count DESC LIMIT 6"

Criar uma tabela vazia

Crie uma tabela vazia usando o comando bq mk com a sinalização -t.

bq mk --schema name:string,value:integer -t mydataset.newtable

em que mydataset é o nome de um conjunto de dados existente, e newtable é o nome da nova tabela. Nesse exemplo, há uma lista de campos separada por vírgulas para o valor de --schema. Como alternativa, armazene o esquema em um arquivo e atribua o nome dele como o valor de --schema.

Trabalho com consultas

Executar uma consulta

Use o seguinte comando para executar uma consulta:

bq query <query_string>
  • A string da consulta precisa estar entre aspas duplas.
  • Não é necessário finalizar com um ; (ponto e vírgula).
  • Se os valores padrão já estão definidos, não é preciso especificar o código do projeto ou do conjunto de dados.
  • Use a sinalização comum --format para especificar outros formatos de saída como JSON ou CSV.
  • Se a execução da consulta demorar muito, execute-a de maneira assíncrona na linha de comando.
  • Para salvar os resultados em uma tabela, use a sinalização --destination_table.

Como exemplo, usaremos um conjunto de dados público de nomes de bebê, descrito na seção Criar uma nova tabela, na página do guia de início rápido da ferramenta de linha de comando.

bq ls -d
   datasetId
 -------------
  mydataset
bq ls :mydataset
     tableId
 ---------------
  babynames
bq query "SELECT name,count FROM mydataset.babynames WHERE gender = 'M' ORDER BY count DESC LIMIT 6"
Waiting on job_a4e77f793e7b4d5bbc1fd69244d9e792 ... (0s) Current status: DONE
+----------+-------+
|   name   | COUNT |
+----------+-------+
| Zachary  | 22731 |
| Alfred   | 20477 |
| Gregory  | 17179 |
| Ned      | 16860 |
| Ulrich   | 15300 |
| Thomas   | 14995 |
+----------+-------+

Também é possível executar consultas em lote com a sinalização --batch.

O exemplo abaixo mostra como iniciar uma consulta em lote assíncrona:

bq --nosync query --batch "SELECT name,count FROM mydataset.babynames WHERE gender = 'M' ORDER BY count DESC LIMIT 6"

Ativação do SQL padrão

Para ativar o SQL padrão em uma consulta, configure a sinalização --use_legacy_sql como false. Por exemplo, a consulta a seguir é executada com o SQL padrão:

bq query --use_legacy_sql=false "SELECT word FROM publicdata.samples.shakespeare"

Trabalho com resultados da consulta

Permitir resultados extensos

Se os resultados da consulta excedem o tamanho máximo da resposta, configure a permissão para resultados extensos e grave o conjunto de resultados em uma tabela de destino. Use as sinalizações --allow_large_results e --destination_table para criar uma tabela de destino que armazene esse conjunto:

bq query --destination_table '[DATASET].[TABLE_NAME]' --allow_large_results "[QUERY]"

Para saber mais sobre como permitir resultados extensos, consulte Como retornar resultados extensos de consulta.

Esta página foi útil? Conte sua opinião sobre:

Enviar comentários sobre…

Precisa de ajuda? Acesse nossa página de suporte.