Como executar jobs de consulta interativa e em lote

Neste documento, descrevemos como executar jobs de consulta interativa (sob demanda) e em lote.

Permissões necessárias

Para executar um job de consulta, é necessário ter, no mínimo, as permissões bigquery.jobs.create. Para que esse job seja concluído com sucesso, você também precisa ter acesso às tabelas ou visualizações referenciadas pela consulta. O acesso às tabelas ou visualizações pode ser concedido nos seguintes níveis, listados em ordem de intervalo de recursos permitidos (do maior para o menor):

em um alto nível na hierarquia de recursos do Google Cloud, como projeto, pasta ou organização
no nível do conjunto de dados.
no nível da tabela

Os seguintes papéis predefinidos do IAM incluem as permissões bigquery.jobs.create:

bigquery.user
bigquery.jobUser
bigquery.admin

Além disso, se um usuário tiver permissões bigquery.datasets.create ao criar um conjunto de dados, será concedido o acesso bigquery.dataOwner. O acesso bigquery.dataOwner permite que o usuário consulte tabelas e visualizações no conjunto de dados.

Para mais informações sobre os papéis do IAM no BigQuery, consulte Papéis e permissões predefinidos.

Como executar consultas interativas

Por padrão, o BigQuery executa jobs de consulta interativa (sob demanda), o que significa que a consulta é executada o mais rápido possível. Essas consultas são realizadas de acordo com o limite de taxa simultânea e o limite de taxa diária.

Os resultados da consulta são sempre salvos em tabelas temporárias ou permanentes. Você pode optar por anexar ou substituir dados em uma tabela existente ou criar uma nova tabela, se não houver nenhuma com o mesmo nome.

Para executar uma consulta interativa que grava em uma tabela temporária:

Console

Abra a página do BigQuery no Console do Cloud.
Acessar a página do BigQuery
Clique em Escrever nova consulta.
Insira uma consulta SQL do BigQuery válida na área de texto do Editor de consultas.
Opcional: para alterar o local de processamento de dados, clique em Mais e, em seguida, clique em Configurações de consulta. Em Local de processamento, clique em Seleção automática e escolha o local dos dados. Por fim, clique em Salvar para atualizar as configurações da consulta.
Clique em Executar.

Isso cria um job de consulta que grava a saída em uma tabela temporária.

bq

Insira o comando bq query e inclua o texto da consulta.

Opcional: forneça a sinalização --location e defina o valor como seu local.

É possível especificar as seguintes sinalizações opcionais. A lista inclui algumas das sinalizações mais comuns. Para uma lista completa de sinalizações de comando query, consulte bq query na referência da ferramenta de linha de comando bq.

Especifique:

sinalização --destination_table para criar uma tabela permanente com base nos resultados da consulta. Para gravar os resultados da consulta em uma tabela que não esteja em seu projeto padrão, adicione o ID do projeto ao nome do conjunto de dados no seguinte formato: project_id:dataset. Se --destination_table não for especificado, será gerado um job de consulta que grava a saída em uma tabela temporária (cache).
sinalização --append_table para anexar os resultados da consulta a uma tabela de destino.
sinalização --destination_kms_key para usar uma chave do Cloud Key Management Service para criptografar os dados da tabela de destino.
sinalização --use_legacy_sql=false para usar a sintaxe SQL padrão. É possível definir uma sintaxe padrão para a ferramenta de linha de comando bq usando o arquivo .bigqueryrc.
Sinalização --label para aplicar um rótulo ao job de consulta no formulário key:value. Repita essa sinalização para especificar vários rótulos.
Sinalização --max_rows ou -n para especificar o número de linhas a serem retornadas nos resultados da consulta.
sinalização --maximum_bytes_billed para limitar os bytes faturados da consulta. Se a consulta ultrapassar o limite, ela falhará (sem gerar cobranças). Se não especificado, os bytes faturados são definidos como o padrão do projeto.
sinalização --udf_resource para carregar e avaliar um arquivo de código a ser usado como um recurso de função definido pelo usuário. É possível especificar um URI do Cloud Storage ou o caminho para um arquivo de código local. Repita a sinalização para especificar vários arquivos.

Digite o seguinte comando para executar uma consulta interativa usando a sintaxe padrão do SQL:

bq --location=location query \
--use_legacy_sql=false \
'query'

Em que:

location é o nome do local onde a consulta é processada. A sinalização --location é opcional. Por exemplo, se você estiver usando BigQuery na região de Tóquio, defina o valor da sinalização como asia-northeast1. É possível definir um valor padrão para o local usando o arquivo .bigqueryrc;
query é uma consulta na sintaxe SQL padrão.

Exemplos:

Digite o seguinte comando para gravar resultados de consulta interativa em uma tabela de destino chamada mytable em mydataset. O conjunto de dados está no projeto padrão. A consulta recupera dados do conjunto de dados públicos USA Name Data.

bq query \
--destination_table mydataset.mytable \
--use_legacy_sql=false \
'SELECT
  name,
  number
FROM
  `bigquery-public-data.usa_names.usa_1910_current`
WHERE
  gender = "M"
ORDER BY
  number DESC'

Digite o seguinte comando para gravar resultados de consulta interativa em uma tabela de destino chamada mytable em mydataset. O conjunto de dados está em myotherproject, e não no projeto padrão. A consulta recupera dados de uma tabela não particionada, o conjunto de dados públicos USA Name Data.

bq query \
--destination_table myotherproject:mydataset.mytable \
--use_legacy_sql=false \
'SELECT
  name,
  number
FROM
  `bigquery-public-data.usa_names.usa_1910_current`
WHERE
  gender = "M"
ORDER BY
  number DESC'

API

Para executar uma consulta usando a API, insira um novo job e preencha a propriedade jobs#configuration.query. Especifique seu local na propriedade location, na seção jobReference do recurso do job.

Pesquise os resultados chamando getQueryResults. Pesquisar até jobComplete igual a true. Verifique se há erros e avisos na lista errors.

C#

Antes de testar essa amostra, siga as instruções de configuração para C# no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery C# (em inglês).

Ver no GitHub Feedback


using Google.Cloud.BigQuery.V2;
using System;

public class BigQueryQuery
{
    public void Query(
        string projectId = "your-project-id"
    )
    {
        BigQueryClient client = BigQueryClient.Create(projectId);
        string query = @"
            SELECT name FROM `bigquery-public-data.usa_names.usa_1910_2013`
            WHERE state = 'TX'
            LIMIT 100";
        BigQueryJob job = client.CreateQueryJob(
            sql: query,
            parameters: null,
            options: new QueryOptions { UseQueryCache = false });
        // Wait for the job to complete.
        job.PollUntilCompleted();
        // Display the results
        foreach (BigQueryRow row in client.GetQueryResults(job.Reference))
        {
            Console.WriteLine($"{row["name"]}");
        }
    }
}

Go

Antes de testar essa amostra, siga as instruções de configuração para Go no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API do BigQuery para Go.

Ver no GitHub Feedback

import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/bigquery"
	"google.golang.org/api/iterator"
)

// queryBasic demonstrates issuing a query and reading results.
func queryBasic(w io.Writer, projectID string) error {
	// projectID := "my-project-id"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	q := client.Query(
		"SELECT name FROM `bigquery-public-data.usa_names.usa_1910_2013` " +
			"WHERE state = \"TX\" " +
			"LIMIT 100")
	// Location must match that of the dataset(s) referenced in the query.
	q.Location = "US"
	// Run the query and print results when the query job is completed.
	job, err := q.Run(ctx)
	if err != nil {
		return err
	}
	status, err := job.Wait(ctx)
	if err != nil {
		return err
	}
	if err := status.Err(); err != nil {
		return err
	}
	it, err := job.Read(ctx)
	for {
		var row []bigquery.Value
		err := it.Next(&row)
		if err == iterator.Done {
			break
		}
		if err != nil {
			return err
		}
		fmt.Fprintln(w, row)
	}
	return nil
}

Java

Antes de testar essa amostra, siga as instruções de configuração para Java no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery em Java.

Ver no GitHub (em inglês) Feedback

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.TableResult;

public class SimpleQuery {

  public static void runSimpleQuery() {
    // TODO(developer): Replace this query before running the sample.
    String query = "SELECT corpus FROM `bigquery-public-data.samples.shakespeare` GROUP BY corpus;";
    simpleQuery(query);
  }

  public static void simpleQuery(String query) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      // Create the query job.
      QueryJobConfiguration queryConfig = QueryJobConfiguration.newBuilder(query).build();

      // Execute the query.
      TableResult result = bigquery.query(queryConfig);

      // Print the results.
      result.iterateAll().forEach(rows -> rows.forEach(row -> System.out.println(row.getValue())));

      System.out.println("Query ran successfully");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Query did not run \n" + e.toString());
    }
  }
}

Node.js

Antes de testar essa amostra, siga as instruções de configuração para Node.js no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery Node.js.

Ver no GitHub (em inglês) Feedback

// Import the Google Cloud client library using default credentials
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();
async function query() {
  // Queries the U.S. given names dataset for the state of Texas.

  const query = `SELECT name
    FROM \`bigquery-public-data.usa_names.usa_1910_2013\`
    WHERE state = 'TX'
    LIMIT 100`;

  // For all options, see https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/query
  const options = {
    query: query,
    // Location must match that of the dataset(s) referenced in the query.
    location: 'US',
  };

  // Run the query as a job
  const [job] = await bigquery.createQueryJob(options);
  console.log(`Job ${job.id} started.`);

  // Wait for the query to finish
  const [rows] = await job.getQueryResults();

  // Print the results
  console.log('Rows:');
  rows.forEach(row => console.log(row));
}

PHP

Antes de testar esta amostra, siga as instruções de configuração para PHP no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery PHP (em inglês).

Ver no GitHub Feedback

use Google\Cloud\BigQuery\BigQueryClient;
use Google\Cloud\Core\ExponentialBackoff;

/** Uncomment and populate these variables in your code */
// $projectId = 'The Google project ID';
// $query = 'SELECT id, view_count FROM `bigquery-public-data.stackoverflow.posts_questions`';

$bigQuery = new BigQueryClient([
    'projectId' => $projectId,
]);
$jobConfig = $bigQuery->query($query);
$job = $bigQuery->startQuery($jobConfig);

$backoff = new ExponentialBackoff(10);
$backoff->execute(function () use ($job) {
    print('Waiting for job to complete' . PHP_EOL);
    $job->reload();
    if (!$job->isComplete()) {
        throw new Exception('Job has not yet completed', 500);
    }
});
$queryResults = $job->queryResults();

$i = 0;
foreach ($queryResults as $row) {
    printf('--- Row %s ---' . PHP_EOL, ++$i);
    foreach ($row as $column => $value) {
        printf('%s: %s' . PHP_EOL, $column, json_encode($value));
    }
}
printf('Found %s row(s)' . PHP_EOL, $i);

Python

Antes de testar essa amostra, siga as instruções de configuração para Python no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery em Python.

Ver no GitHub (em inglês) Feedback


from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

query = """
    SELECT name, SUM(number) as total_people
    FROM `bigquery-public-data.usa_names.usa_1910_2013`
    WHERE state = 'TX'
    GROUP BY name, state
    ORDER BY total_people DESC
    LIMIT 20
"""
query_job = client.query(query)  # Make an API request.

print("The query data:")
for row in query_job:
    # Row values can be accessed by field name or index.
    print("name={}, count={}".format(row[0], row["total_people"]))

Ruby

Antes de testar esta amostra, siga as instruções de configuração para Ruby no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de Referência da API BigQuery Ruby.

Ver no GitHub Feedback

require "google/cloud/bigquery"

def query
  bigquery = Google::Cloud::Bigquery.new
  sql = "SELECT name FROM `bigquery-public-data.usa_names.usa_1910_2013` " \
        "WHERE state = 'TX' " \
        "LIMIT 100"

  # Location must match that of the dataset(s) referenced in the query.
  results = bigquery.query sql do |config|
    config.location = "US"
  end

  results.each do |row|
    puts row.inspect
  end
end

Como executar consultas em lote

O BigQuery também oferece consultas em lote. O BigQuery enfileira cada consulta em lote em seu nome e inicia a consulta assim que os recursos inativos ficarem disponíveis no pool de recursos compartilhados do BigQuery. Isso geralmente ocorre em poucos minutos. Se o BigQuery não tiver iniciado a consulta em 24 horas, ele altera a prioridade do job para interativa.

As consultas em lote não contam para o limite de taxa simultânea, o que facilita a execução de muitas consultas de uma vez. As consultas em lote usam os mesmos recursos que as consultas interativas (sob demanda). Se você usa consultas de lote e interativas com preços fixos, compartilhe seus slots alocados.

Para executar uma consulta em lote:

Console

Abra a página do BigQuery no Console do Cloud.
Acesse a página do BigQuery
Clique no botão Escrever nova consulta.
Insira uma consulta SQL válida na área de texto do Editor de consultas.
Clique no botão Mais e depois em Configurações de consulta.
Selecione a opção Lote na seção Prioridade do job.
Opcional: em Local de processamento, clique em Não especificado e escolha o local dos seus dados.
Clique em Salvar para atualizar as configurações da consulta.
Clique em Executar.

bq

Insira o comando bq query e inclua o texto da consulta. Especifique a sinalização -- batch para executar uma consulta em lote.

Opcional: forneça a sinalização --location e defina o valor como seu local.

Especifique:

sinalização --destination_table para criar uma tabela permanente com base nos resultados da consulta. Para gravar os resultados da consulta em uma tabela que não esteja em seu projeto padrão, adicione o ID do projeto ao nome do conjunto de dados no seguinte formato: project_id:dataset. Se --destination_table não for especificado, será gerado um job de consulta que grava a saída em uma tabela temporária (cache).
sinalização --append_table para anexar os resultados da consulta a uma tabela de destino.
sinalização --destination_kms_key para usar uma chave do Cloud Key Management Service para criptografar os dados da tabela de destino.
sinalização --use_legacy_sql=false para usar a sintaxe SQL padrão. É possível definir uma sintaxe padrão para a ferramenta de linha de comando bq usando o arquivo .bigqueryrc.
Sinalização --label para aplicar um rótulo ao job de consulta no formulário key:value. Repita essa sinalização para especificar vários rótulos.
Sinalização --max_rows ou -n para especificar o número de linhas a serem retornadas nos resultados da consulta.
sinalização --maximum_bytes_billed para limitar os bytes faturados da consulta. Se a consulta ultrapassar o limite, ela falhará (sem gerar cobranças). Se não especificado, os bytes faturados são definidos como o padrão do projeto.
Sinalização --udf_resource para carregar e avaliar um arquivo de código a ser usado como um recurso de função definido pelo usuário. É possível especificar um URI do Cloud Storage ou o caminho para um arquivo de código local. Repita a sinalização para especificar vários arquivos.

Digite o seguinte comando para executar uma consulta em lote usando a sintaxe SQL padrão:

bq --location=location query \
--batch \
--use_legacy_sql=false \
'query'

Em que:

location é o nome do local onde a consulta é processada. A sinalização --location é opcional. Por exemplo, se você estiver usando BigQuery na região de Tóquio, defina o valor da sinalização como asia-northeast1. É possível definir um valor padrão para o local usando o arquivo .bigqueryrc;
query é uma consulta na sintaxe SQL padrão.

Exemplos:

Digite o seguinte comando para gravar os resultados da consulta em lote em uma tabela de destino chamada mytable em mydataset. O conjunto de dados está no projeto padrão. A consulta recupera dados do conjunto de dados públicos USA Name Data.

bq query \
--batch \
--destination_table mydataset.mytable \
--use_legacy_sql=false \
'SELECT
  name,
  number
FROM
  `bigquery-public-data.usa_names.usa_1910_current`
WHERE
  gender = "M"
ORDER BY
  number DESC'

Digite o seguinte comando para gravar os resultados da consulta em lote em uma tabela de destino chamada mytable em mydataset. O conjunto de dados está em myotherproject, e não no projeto padrão. A consulta recupera dados de uma tabela não particionada, o conjunto de dados públicos do USA Name Data (em inglês).

bq query \
--batch \
--destination_table myotherproject:mydataset.mytable \
--use_legacy_sql=false \
'SELECT
  name,
  number
FROM
  `bigquery-public-data.usa_names.usa_1910_current`
WHERE
  gender = "M"
ORDER BY
  number DESC'

API

Para executar uma consulta usando a API, insira um novo job e preencha a propriedade de configuração do job query. Opcional: especifique seu local na propriedade location, na seção jobReference do recurso do job.

Ao preencher as propriedades do job de consulta, inclua a propriedade configuration.query.priority com o valor definido como BATCH.

Go

Ver no GitHub Feedback

import (
	"context"
	"fmt"
	"io"
	"time"

	"cloud.google.com/go/bigquery"
)

// queryBatch demonstrates issuing a query job using batch priority.
func queryBatch(w io.Writer, projectID, dstDatasetID, dstTableID string) error {
	// projectID := "my-project-id"
	// dstDatasetID := "mydataset"
	// dstTableID := "mytable"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	// Build an aggregate table.
	q := client.Query(`
		SELECT
  			corpus,
  			SUM(word_count) as total_words,
  			COUNT(1) as unique_words
		FROM ` + "`bigquery-public-data.samples.shakespeare`" + `
		GROUP BY corpus;`)
	q.Priority = bigquery.BatchPriority
	q.QueryConfig.Dst = client.Dataset(dstDatasetID).Table(dstTableID)

	// Start the job.
	job, err := q.Run(ctx)
	if err != nil {
		return err
	}
	// Job is started and will progress without interaction.
	// To simulate other work being done, sleep a few seconds.
	time.Sleep(5 * time.Second)
	status, err := job.Status(ctx)
	if err != nil {
		return err
	}

	state := "Unknown"
	switch status.State {
	case bigquery.Pending:
		state = "Pending"
	case bigquery.Running:
		state = "Running"
	case bigquery.Done:
		state = "Done"
	}
	// You can continue to monitor job progress until it reaches
	// the Done state by polling periodically.  In this example,
	// we print the latest status.
	fmt.Fprintf(w, "Job %s in Location %s currently in state: %s\n", job.ID(), job.Location(), state)

	return nil

}

Java

Para executar uma consulta em lote, defina a prioridade de consulta como QueryJobConfiguration.Priority.BATCH ao criar uma QueryJobConfiguration.

Ver no GitHub (em inglês) Feedback

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.TableResult;

// Sample to query batch in a table
public class QueryBatch {

  public static void runQueryBatch() {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "MY_PROJECT_ID";
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String query =
        "SELECT corpus"
            + " FROM `"
            + projectId
            + "."
            + datasetName
            + "."
            + tableName
            + " GROUP BY corpus;";
    queryBatch(query);
  }

  public static void queryBatch(String query) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      QueryJobConfiguration queryConfig =
          QueryJobConfiguration.newBuilder(query)
              // Run at batch priority, which won't count toward concurrent rate limit.
              .setPriority(QueryJobConfiguration.Priority.BATCH)
              .build();

      TableResult results = bigquery.query(queryConfig);

      results
          .iterateAll()
          .forEach(row -> row.forEach(val -> System.out.printf("%s,", val.toString())));

      System.out.println("Query batch performed successfully.");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Query batch not performed \n" + e.toString());
    }
  }
}

Node.js

Ver no GitHub Feedback

// Import the Google Cloud client library and create a client
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function queryBatch() {
  // Runs a query at batch priority.

  // Create query job configuration. For all options, see
  // https://cloud.google.com/bigquery/docs/reference/rest/v2/Job#jobconfigurationquery
  const queryJobConfig = {
    query: `SELECT corpus
            FROM \`bigquery-public-data.samples.shakespeare\`
            LIMIT 10`,
    useLegacySql: false,
    priority: 'BATCH',
  };

  // Create job configuration. For all options, see
  // https://cloud.google.com/bigquery/docs/reference/rest/v2/Job#jobconfiguration
  const jobConfig = {
    // Specify a job configuration to set optional job resource properties.
    configuration: {
      query: queryJobConfig,
    },
  };

  // Make API request.
  const [job] = await bigquery.createJob(jobConfig);

  const jobId = job.metadata.id;
  const state = job.metadata.status.state;
  console.log(`Job ${jobId} is currently in state ${state}`);
}

Python

Veja no GitHub Feedback

from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

job_config = bigquery.QueryJobConfig(
    # Run at batch priority, which won't count toward concurrent rate limit.
    priority=bigquery.QueryPriority.BATCH
)

sql = """
    SELECT corpus
    FROM `bigquery-public-data.samples.shakespeare`
    GROUP BY corpus;
"""

# Start the query, passing in the extra configuration.
query_job = client.query(sql, job_config=job_config)  # Make an API request.

# Check on the progress by getting the job's updated state. Once the state
# is `DONE`, the results are ready.
query_job = client.get_job(
    query_job.job_id, location=query_job.location
)  # Make an API request.

print("Job {} is currently in state {}".format(query_job.job_id, query_job.state))