Consulta de dados

Este documento descreve várias maneiras de consultar dados usando o dialeto SQL do BigQuery, incluindo execução de consultas síncronas, assíncronas, interativas e em lote. Ele também explica como usar armazenamento em cache de consultas, retornar resultados de consultas extensas e consultar meta-tabelas.

Execução de consultas síncronas

A consulta síncrona retorna uma resposta somente depois que é concluída. A resposta inclui os resultados da consulta em formato de tabela, que pode ser temporária ou permanente.

Para enviar uma solicitação de consulta que seja retornada antes de sua conclusão, consulte Execução de consultas assíncronas.

Por padrão, as consultas síncronas gravam os resultados em tabelas temporárias. Para executar uma consulta síncrona com gravação em tabela temporária:

IU da Web

  1. Acesse a IU da Web do BigQuery. Acessar a IU da Web do BigQuery
  2. Clique no botão Escrever consulta.
  3. Insira uma consulta SQL válida do BigQuery na área de texto Nova consulta.
  4. Clique no botão Executar consulta.

Uma consulta síncrona é gerada com a saída gravada em uma tabela temporária.

Linha de comando

Por padrão, a ferramenta de linha de comando bq executa a consulta de maneira síncrona. Para informações sobre como executar uma consulta, veja Executar uma consulta.

C#

Para saber mais sobre instalação e criação de um cliente do BigQuery, consulte as Bibliotecas de cliente do BigQuery.

public BigQueryResults SyncQuery(string projectId, string datasetId, string tableId,
    string query, double timeoutMs, BigQueryClient client)
{
    var table = client.GetTable(projectId, datasetId, tableId);
    BigQueryJob job = client.CreateQueryJob(query,
        new CreateQueryJobOptions { UseQueryCache = false });
    // Get the query result, waiting for the timespan specified in milliseconds.
    BigQueryResults result = client.GetQueryResults(job.Reference.JobId,
        new GetQueryResultsOptions { Timeout = TimeSpan.FromMilliseconds(timeoutMs) });
    return result;
}

Go

Para saber mais sobre instalação e criação de um cliente do BigQuery, consulte as Bibliotecas de cliente do BigQuery.

func Query(proj, q string) ([][]bigquery.Value, error) {
	ctx := context.Background()

	client, err := bigquery.NewClient(ctx, proj)
	if err != nil {
		return nil, err
	}

	query := client.Query(q)
	iter, err := query.Read(ctx)
	if err != nil {
		return nil, err
	}

	var rows [][]bigquery.Value

	for {
		var row []bigquery.Value
		err := iter.Next(&row)
		if err == iterator.Done {
			return rows, nil
		}
		if err != nil {
			return nil, err
		}
		rows = append(rows, row)
	}
}

Java

Para saber mais sobre instalação e criação de um cliente do BigQuery, consulte as Bibliotecas de cliente do BigQuery.

public static void run(
    final PrintStream out,
    final String queryString,
    final long waitTime,
    final boolean useLegacySql) throws IOException {
  BigQuery bigquery =
      new BigQueryOptions.DefaultBigqueryFactory().create(BigQueryOptions.getDefaultInstance());

  QueryRequest queryRequest =
      QueryRequest.newBuilder(queryString)
          .setMaxWaitTime(waitTime)
          // Use standard SQL syntax or legacy SQL syntax for queries.
          // See: https://cloud.google.com/bigquery/sql-reference/
          .setUseLegacySql(useLegacySql)
          .build();
  QueryResponse response = bigquery.query(queryRequest);

  if (response.hasErrors()) {
    throw new RuntimeException(
        response
            .getExecutionErrors()
            .stream()
            .<String>map(err -> err.getMessage())
            .collect(Collectors.joining("\n")));
  }

  QueryResult result = response.getResult();
  Iterator<List<FieldValue>> iter = result.iterateAll();
  while (iter.hasNext()) {
    List<FieldValue> row = iter.next();
    out.println(row.stream().map(val -> val.toString()).collect(Collectors.joining(",")));
  }
}

Node.js

Para saber mais sobre instalação e criação de um cliente do BigQuery, consulte as Bibliotecas de cliente do BigQuery.

function syncQuery (sqlQuery) {
  // Instantiates a client
  const bigquery = BigQuery();

  // Query options list: https://cloud.google.com/bigquery/docs/reference/v2/jobs/query
  const options = {
    query: sqlQuery,
    timeoutMs: 10000, // Time out after 10 seconds.
    useLegacySql: false // Use standard SQL syntax for queries.
  };

  // Runs the query
  return bigquery.query(options)
    .then((results) => {
      const rows = results[0];
      console.log('Rows:');
      rows.forEach((row) => console.log(row));
      return rows;
    });
}

PHP

Para saber mais sobre instalação e criação de um cliente do BigQuery, consulte as Bibliotecas de cliente do BigQuery.

use Google\Cloud\BigQuery\BigQueryClient;

/**
 * Run a BigQuery query.
 * Example:
 * ```
 * $query = 'SELECT TOP(corpus, 10) as title, COUNT(*) as unique_words ' .
 *          'FROM [publicdata:samples.shakespeare]';
 * run_query($projectId, $query, true);
 * ```.
 *
 * @param string $projectId The Google project ID.
 * @param string $query     A SQL query to run.
 * @param bool $useLegacySql Specifies whether to use BigQuery's legacy SQL
 *        syntax or standard SQL syntax for this query.
 */
function run_query($projectId, $query, $useLegacySql)
{
    $bigQuery = new BigQueryClient([
        'projectId' => $projectId,
    ]);
    $queryResults = $bigQuery->runQuery(
        $query,
        ['useLegacySql' => $useLegacySql]);

    if ($queryResults->isComplete()) {
        $i = 0;
        $rows = $queryResults->rows();
        foreach ($rows as $row) {
            printf('--- Row %s ---' . PHP_EOL, ++$i);
            foreach ($row as $column => $value) {
                printf('%s: %s' . PHP_EOL, $column, $value);
            }
        }
        printf('Found %s row(s)' . PHP_EOL, $i);
    } else {
        throw new Exception('The query failed to complete');
    }
}

Python

Para saber mais sobre instalação e criação de um cliente do BigQuery, consulte as Bibliotecas de cliente do BigQuery.

def sync_query(query):
    client = bigquery.Client()
    query_results = client.run_sync_query(query)

    # Use standard SQL syntax for queries.
    # See: https://cloud.google.com/bigquery/sql-reference/
    query_results.use_legacy_sql = False

    query_results.run()

    # Drain the query results by requesting a page at a time.
    page_token = None

    while True:
        rows, total_rows, page_token = query_results.fetch_data(
            max_results=10,
            page_token=page_token)

        for row in rows:
            print(row)

        if not page_token:
            break

Ruby

Para saber mais sobre instalação e criação de um cliente do BigQuery, consulte as Bibliotecas de cliente do BigQuery.

# project_id   = "your google cloud project id"
# query_string = "query string to execute (using bigquery query syntax)"

require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new project: project_id

query_results = bigquery.query query_string

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

Armazenamento de resultados síncronos em tabela permanente

Para salvar os resultados da consulta em uma tabela permanente:

IU da Web

  1. Acesse a IU da Web do BigQuery.
    Acessar a IU da Web do BigQuery

  2. Clique no botão Escrever consulta.

  3. Insira uma consulta SQL válida do BigQuery na área de texto Nova consulta.

  4. Clique no botão Mostrar opções.

  5. Clique no botão Selecionar tabela na seção Tabela de destino.

  6. Insira o código da tabela e clique em OK.

  7. Clique no botão Executar consulta.

Uma consulta síncrona é gerada com a saída gravada em uma tabela permanente.

Caso você se esqueça de especificar uma tabela de destino antes de executar a consulta, se preferir, poderá copiar a tabela temporária em uma tabela permanente clicando no botão Salvar como tabela na janela de resultados.

Linha de comando


Use a sinalização --destination_table para criar uma tabela permanente com base nos resultados da consulta. Por exemplo, a seguinte consulta cria uma tabela permanente denominada happyhalloween no conjunto de dados mydataset:

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

API

Para salvar os resultados da consulta em uma tabela permanente, execute a consulta de maneira assíncrona e especifique uma tabela permanente.

Armazenamento de resultados síncronos em visualização

Para informações sobre como criar visualizações, consulte Usar visualizações.

Execução de consultas assíncronas

A consulta assíncrona retorna uma resposta imediata, geralmente antes de sua conclusão. Periodicamente, você verifica se a consulta foi concluída usando uma chamada separada à API.

Se você preferir aguardar o término da consulta antes de retornar uma resposta, use a consulta síncrona.

As consultas assíncronas sempre são salvas em uma tabela, seja ela nova, existente ou temporária. Você pode anexar ou substituir os dados em uma tabela existente ou criar uma nova tabela, se não existir nenhuma com o mesmo nome.

Por padrão, as consultas assíncronas gravam os resultados em tabelas temporárias. Para executar uma consulta assíncrona com gravação em tabela temporária, use a ferramenta de linha de comando ou a API do BigQuery:

Linha de comando

Use a sinalização --no-sync para executar uma consulta de maneira assíncrona. Para mais informações sobre como executar uma consulta assíncrona usando a ferramenta bq, consulte Como executar operações assíncronas.

C#

Para saber mais sobre instalação e criação de um cliente do BigQuery, consulte as Bibliotecas de cliente do BigQuery.

public BigQueryResults AsyncQuery(string projectId, string datasetId, string tableId,
    string query, BigQueryClient client)
{
    var table = client.GetTable(projectId, datasetId, tableId);
    BigQueryJob job = client.CreateQueryJob(query,
        new CreateQueryJobOptions { UseQueryCache = false });

    // Wait for the job to complete.
    job.PollUntilCompleted();

    // Then we can fetch the results, either via the job or by accessing
    // the destination table.
    return client.GetQueryResults(job.Reference.JobId);
}

Go

Para saber mais sobre instalação e criação de um cliente do BigQuery, consulte as Bibliotecas de cliente do BigQuery.

q := client.Query(fmt.Sprintf(`
	SELECT name, count
	FROM [%s.%s]
`, datasetID, tableID))
job, err := q.Run(ctx)
if err != nil {
	return err
}

// Wait until async querying is done.
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.Println(row)
}

Java

Para saber mais sobre instalação e criação de um cliente do BigQuery, consulte as Bibliotecas de cliente do BigQuery.

QueryRequest request = QueryRequest.of(query);
QueryResponse response = bigquery.query(request);
// Wait for things to finish
while (!response.jobCompleted()) {
  Thread.sleep(1000);
  response = bigquery.getQueryResults(response.getJobId());
}
if (response.hasErrors()) {
  // handle errors
}
QueryResult result = response.getResult();
Iterator<List<FieldValue>> rowIterator = result.iterateAll();
while (rowIterator.hasNext()) {
  List<FieldValue> row = rowIterator.next();
  // do something with the data
}

Node.js

Para saber mais sobre instalação e criação de um cliente do BigQuery, consulte as Bibliotecas de cliente do BigQuery.

function asyncQuery (sqlQuery) {
  // Instantiates a client
  const bigquery = BigQuery();

  // Query options list: https://cloud.google.com/bigquery/docs/reference/v2/jobs/query
  const options = {
    query: sqlQuery,
    useLegacySql: false // Use standard SQL syntax for queries.
  };

  let job;

  // Runs the query as a job
  return bigquery.startQuery(options)
    .then((results) => {
      job = results[0];
      console.log(`Job ${job.id} started.`);
      return job.promise();
    })
    .then(() => {
      console.log(`Job ${job.id} completed.`);
      return job.getQueryResults();
    })
    .then((results) => {
      const rows = results[0];
      console.log('Rows:');
      rows.forEach((row) => console.log(row));
      return rows;
    });
}

PHP

Para saber mais sobre instalação e criação de um cliente do BigQuery, consulte as Bibliotecas de cliente do BigQuery.

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

/**
 * Run a BigQuery query as a job.
 * Example:
 * ```
 * $query = 'SELECT TOP(corpus, 10) as title, COUNT(*) as unique_words ' .
 *          'FROM [publicdata:samples.shakespeare]';
 * run_query_as_job($projectId, $query, true);
 * ```.
 *
 * @param string $projectId The Google project ID.
 * @param string $query     A SQL query to run. *
 * @param bool $useLegacySql Specifies whether to use BigQuery's legacy SQL
 *        syntax or standard SQL syntax for this query.
 */
function run_query_as_job($projectId, $query, $useLegacySql)
{
    $bigQuery = new BigQueryClient([
        'projectId' => $projectId,
    ]);
    $job = $bigQuery->runQueryAsJob(
        $query,
        ['jobConfig' => ['useLegacySql' => $useLegacySql]]);
    $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();

    if ($queryResults->isComplete()) {
        $i = 0;
        $rows = $queryResults->rows();
        foreach ($rows as $row) {
            printf('--- Row %s ---' . PHP_EOL, ++$i);
            foreach ($row as $column => $value) {
                printf('%s: %s' . PHP_EOL, $column, $value);
            }
        }
        printf('Found %s row(s)' . PHP_EOL, $i);
    } else {
        throw new Exception('The query failed to complete');
    }
}

Python

Para saber mais sobre instalação e criação de um cliente do BigQuery, consulte as Bibliotecas de cliente do BigQuery.

def async_query(query):
    client = bigquery.Client()
    query_job = client.run_async_query(str(uuid.uuid4()), query)
    query_job.use_legacy_sql = False
    query_job.begin()

    wait_for_job(query_job)

    # Drain the query results by requesting a page at a time.
    query_results = query_job.results()
    page_token = None

    while True:
        rows, total_rows, page_token = query_results.fetch_data(
            max_results=10,
            page_token=page_token)

        for row in rows:
            print(row)

        if not page_token:
            break
def wait_for_job(job):
    while True:
        job.reload()  # Refreshes the state via a GET request.
        if job.state == 'DONE':
            if job.error_result:
                raise RuntimeError(job.errors)
            return
        time.sleep(1)

Ruby

Para saber mais sobre instalação e criação de um cliente do BigQuery, consulte as Bibliotecas de cliente do BigQuery.

# project_id   = "your google cloud project id"
# query_string = "query string to execute (using bigquery query syntax)"

require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new project: project_id

puts "Running query"
query_job = bigquery.query_job query_string

puts "Waiting for query to complete"
query_job.wait_until_done!

puts "Query results:"
query_job.query_results.each do |row|
  puts row.inspect
end

Armazenamento de resultados assíncronos em tabela permanente

Para armazenar os resultados de uma consulta assíncrona em uma tabela permanente, inclua o nome da tabela de destino.

Linha de comando


Use a sinalização --destination_table para criar uma tabela permanente com base nos resultados da consulta. Por exemplo, a seguinte consulta cria uma tabela permanente denominada happyhalloween no conjunto de dados mydataset:

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

API

Para salvar os resultados da consulta em uma tabela permanente, inclua um valor para a propriedade jobs#configuration.query.destinationTable.

Armazenamento de resultados assíncronos em visualização

Para informações sobre como criar visualizações, consulte Usar visualizações.

Execução de consultas parametrizadas

O BigQuery aceita parâmetros de consulta para impedir a injeção de SQL ao criar consultas usando entrada de usuário. Esse recurso está disponível somente com a sintaxe SQL padrão.

Para especificar um parâmetro nomeado, use o caractere @ seguido de um identificador, como @param_name. Por exemplo, esta consulta encontra todas as palavras em uma coletânea específica de Shakespeare com contagens que têm, no mínimo, o valor indicado.

SELECT word, word_count
FROM `bigquery-public-data.samples.shakespeare`
WHERE corpus = @corpus
AND word_count >= @min_word_count
ORDER BY word_count DESC;

Se preferir, use o valor do marcador ? para especificar um parâmetro de posição. Uma consulta pode usar parâmetros de posição ou nomeados, mas não os dois.

Linha de comando

Use --parameter para inserir valores para os parâmetros no formato "nome:tipo:valor". Um nome vazio gera um parâmetro de posição. O tipo pode ser omitido para assumir STRING.

É necessário usar a sinalização --parameter em conjunto com --use_legacy_sql=False para especificar a sintaxe SQL padrão.

bq query --use_legacy_sql=False \
    --parameter=corpus::romeoandjuliet \
    --parameter=min_word_count:INT64:250 \
    'SELECT word, word_count
    FROM `bigquery-public-data.samples.shakespeare`
    WHERE corpus = @corpus
    AND word_count >= @min_word_count
    ORDER BY word_count DESC;'

API

Para usar parâmetros nomeados, defina jobs#configuration.query.parameterMode como NAMED (query#parameterMode para consultas síncronas).

Preencha jobs#configuration.query.queryParameters[] com a lista de parâmetros (query#queryParameters[] para consultas síncronas). Defina o nome de cada parâmetro correspondente ao @param_name usado na consulta.

Ative a sintaxe SQL padrão definindo useLegacySql como false.

{
  "query": "SELECT word, word_count FROM `bigquery-public-data.samples.shakespeare` WHERE corpus = @corpus AND word_count >= @min_word_count ORDER BY word_count DESC;",
  "queryParameters": [
    {
      "parameterType": {
        "type": "STRING"
      },
      "parameterValue": {
        "value": "romeoandjuliet"
      },
      "name": "corpus"
    },
    {
      "parameterType": {
        "type": "INT64"
      },
      "parameterValue": {
        "value": "250"
      },
      "name": "min_word_count"
    }
  ],
  "useLegacySql": false,
  "parameterMode": "NAMED"
}

Tente fazer isso no Google APIs Explorer.

Para usar parâmetros de posição, defina jobs#configuration.query.parameterMode como POSITIONAL.

Java

Para saber mais sobre instalação e criação de um cliente do BigQuery, consulte as Bibliotecas de cliente do BigQuery.

private static void runNamed(final String corpus, final long minWordCount)
    throws InterruptedException {
  BigQuery bigquery =
      new BigQueryOptions.DefaultBigqueryFactory().create(BigQueryOptions.getDefaultInstance());

  String queryString = "SELECT word, word_count\n"
      + "FROM `bigquery-public-data.samples.shakespeare`\n"
      + "WHERE corpus = @corpus\n"
      + "AND word_count >= @min_word_count\n"
      + "ORDER BY word_count DESC";
  QueryRequest queryRequest =
      QueryRequest.newBuilder(queryString)
          .addNamedParameter("corpus", QueryParameterValue.string(corpus))
          .addNamedParameter("min_word_count", QueryParameterValue.int64(minWordCount))
          // Standard SQL syntax is required for parameterized queries.
          // See: https://cloud.google.com/bigquery/sql-reference/
          .setUseLegacySql(false)
          .build();

  // Execute the query.
  QueryResponse response = bigquery.query(queryRequest);

  // Wait for the job to finish (if the query takes more than 10 seconds to complete).
  while (!response.jobCompleted()) {
    Thread.sleep(1000);
    response = bigquery.getQueryResults(response.getJobId());
  }

  if (response.hasErrors()) {
    throw new RuntimeException(
        response
            .getExecutionErrors()
            .stream()
            .<String>map(err -> err.getMessage())
            .collect(Collectors.joining("\n")));
  }

  QueryResult result = response.getResult();
  Iterator<List<FieldValue>> iter = result.iterateAll();

  while (iter.hasNext()) {
    List<FieldValue> row = iter.next();
    System.out.printf(
        "%s: %d\n",
        row.get(0).getStringValue(),
        row.get(1).getLongValue());
  }
}

Python

Para saber mais sobre instalação e criação de um cliente do BigQuery, consulte as Bibliotecas de cliente do BigQuery.

def print_results(query_results):
    """Print the query results by requesting a page at a time."""
    page_token = None

    while True:
        rows, total_rows, page_token = query_results.fetch_data(
            max_results=10,
            page_token=page_token)

        for row in rows:
            print(row)

        if not page_token:
            break
def sync_query_named_params(corpus, min_word_count):
    client = bigquery.Client()
    query_results = client.run_sync_query(
        """SELECT word, word_count
        FROM `bigquery-public-data.samples.shakespeare`
        WHERE corpus = @corpus
        AND word_count >= @min_word_count
        ORDER BY word_count DESC;
        """,
        query_parameters=(
            bigquery.ScalarQueryParameter('corpus', 'STRING', corpus),
            bigquery.ScalarQueryParameter(
                'min_word_count',
                'INT64',
                min_word_count)))
    query_results.use_legacy_sql = False
    query_results.run()
    print_results(query_results)

Uso de matrizes em consultas parametrizadas

Para usar um tipo de matriz em um parâmetro de consulta, defina o tipo como ARRAY<T>, onde T é o tipo dos elementos na matriz. Crie o valor como uma lista, entre colchetes, de elementos separados por vírgula, como [1, 2, 3].

Consulte a referência de tipos de dados para mais informações sobre o tipo de matriz.

Linha de comando

Esta consulta seleciona os nomes mais populares de bebês do sexo masculino nascidos nos Estados Unidos que começam com a letra W.

bq query --use_legacy_sql=False \
    --parameter='gender::M' \
    --parameter='states:ARRAY<STRING>:["WA", "WI", "WV", "WY"]' \
    'SELECT name, sum(number) as count
    FROM `bigquery-public-data.usa_names.usa_1910_2013`
    WHERE gender = @gender
    AND state IN UNNEST(@states)
    GROUP BY name
    ORDER BY count DESC
    LIMIT 10;'

Coloque a declaração de tipo de matriz entre aspas simples para que a saída do comando não seja acidentalmente redirecionada para um arquivo pelo caractere >.

API

Para usar um parâmetro com valor de matriz, defina jobs#configuration.query.queryParameters[].parameterType.type como ARRAY.

Se os valores de matriz forem escalares, defina jobs#configuration.query.queryParameters[].parameterType.arrayType.type como o tipo deles, por exemplo STRING. Se forem estruturas, defina o parâmetro acima como STRUCT e adicione as definições de campo necessárias a structTypes.

Por exemplo, esta consulta seleciona os nomes mais populares de bebês do sexo masculino nascidos nos Estados Unidos que começam com a letra W.

{
 "query": "SELECT name, sum(number) as count\nFROM `bigquery-public-data.usa_names.usa_1910_2013`\nWHERE gender = @gender\nAND state IN UNNEST(@states)\nGROUP BY name\nORDER BY count DESC\nLIMIT 10;",
 "queryParameters": [
  {
   "parameterType": {
    "type": "STRING"
   },
   "parameterValue": {
    "value": "M"
   },
   "name": "gender"
  },
  {
   "parameterType": {
    "type": "ARRAY",
    "arrayType": {
     "type": "STRING"
    }
   },
   "parameterValue": {
    "arrayValues": [
     {
      "value": "WA"
     },
     {
      "value": "WI"
     },
     {
      "value": "WV"
     },
     {
      "value": "WY"
     }
    ]
   },
   "name": "states"
  }
 ],
 "useLegacySql": false,
 "parameterMode": "NAMED"
}

Tente fazer isso no Google APIs Explorer.

Java

Para saber mais sobre instalação e criação de um cliente do BigQuery, consulte as Bibliotecas de cliente do BigQuery.

private static void runArray(String gender, String[] states)
    throws InterruptedException {
  BigQuery bigquery =
      new BigQueryOptions.DefaultBigqueryFactory().create(BigQueryOptions.getDefaultInstance());

  String queryString = "SELECT name, sum(number) as count\n"
      + "FROM `bigquery-public-data.usa_names.usa_1910_2013`\n"
      + "WHERE gender = @gender\n"
      + "AND state IN UNNEST(@states)\n"
      + "GROUP BY name\n"
      + "ORDER BY count DESC\n"
      + "LIMIT 10;";
  QueryRequest queryRequest =
      QueryRequest.newBuilder(queryString)
          .addNamedParameter("gender", QueryParameterValue.string(gender))
          .addNamedParameter(
              "states",
              QueryParameterValue.array(states, String.class))
          // Standard SQL syntax is required for parameterized queries.
          // See: https://cloud.google.com/bigquery/sql-reference/
          .setUseLegacySql(false)
          .build();

  // Execute the query.
  QueryResponse response = bigquery.query(queryRequest);

  // Wait for the job to finish (if the query takes more than 10 seconds to complete).
  while (!response.jobCompleted()) {
    Thread.sleep(1000);
    response = bigquery.getQueryResults(response.getJobId());
  }

  if (response.hasErrors()) {
    throw new RuntimeException(
        response
            .getExecutionErrors()
            .stream()
            .<String>map(err -> err.getMessage())
            .collect(Collectors.joining("\n")));
  }

  QueryResult result = response.getResult();
  Iterator<List<FieldValue>> iter = result.iterateAll();

  while (iter.hasNext()) {
    List<FieldValue> row = iter.next();
    System.out.printf("%s: %d\n", row.get(0).getStringValue(), row.get(1).getLongValue());
  }
}

Uso de timestamps em consultas parametrizadas

Para usar timestamp em um parâmetro de consulta, defina o tipo como TIMESTAMP. O valor deve estar no formato YYYY-MM-DD HH:MM:SS.DDDDDD time_zone.

Consulte a referência de tipos de dados para mais informações sobre o tipo de timestamp.

Linha de comando

Esta consulta adiciona uma hora ao valor do parâmetro de timestamp.

bq query --use_legacy_sql=False \
    --parameter='ts_value:TIMESTAMP:2016-12-07 08:00:00' \
    'SELECT TIMESTAMP_ADD(@ts_value, INTERVAL 1 HOUR);'

API

Para usar um parâmetro de timestamp, defina jobs#configuration.query.queryParameters[].parameterType.type como TIMESTAMP.

Esta consulta adiciona uma hora ao valor do parâmetro de timestamp.

{
  "query": "SELECT TIMESTAMP_ADD(@ts_value, INTERVAL 1 HOUR);",
  "queryParameters": [
    {
      "name": "ts_value",
      "parameterType": {
        "type": "TIMESTAMP"
      },
      "parameterValue": {
        "value": "2016-12-07 08:00:00"
      }
    }
  ],
  "useLegacySql": false,
  "parameterMode": "NAMED"
}

Tente fazer isso no Google APIs Explorer.

Java

Para saber mais sobre instalação e criação de um cliente do BigQuery, consulte as Bibliotecas de cliente do BigQuery.

private static void runTimestamp() throws InterruptedException {
  BigQuery bigquery =
      new BigQueryOptions.DefaultBigqueryFactory().create(BigQueryOptions.getDefaultInstance());

  DateTime timestamp = new DateTime(2016, 12, 7, 8, 0, 0, DateTimeZone.UTC);

  String queryString = "SELECT TIMESTAMP_ADD(@ts_value, INTERVAL 1 HOUR);";
  QueryRequest queryRequest =
      QueryRequest.newBuilder(queryString)
          .addNamedParameter(
              "ts_value",
              QueryParameterValue.timestamp(
                  // Timestamp takes microseconds since 1970-01-01T00:00:00 UTC
                  timestamp.getMillis() * 1000))
          // Standard SQL syntax is required for parameterized queries.
          // See: https://cloud.google.com/bigquery/sql-reference/
          .setUseLegacySql(false)
          .build();

  // Execute the query.
  QueryResponse response = bigquery.query(queryRequest);

  // Wait for the job to finish (if the query takes more than 10 seconds to complete).
  while (!response.jobCompleted()) {
    Thread.sleep(1000);
    response = bigquery.getQueryResults(response.getJobId());
  }

  if (response.hasErrors()) {
    throw new RuntimeException(
        response
            .getExecutionErrors()
            .stream()
            .<String>map(err -> err.getMessage())
            .collect(Collectors.joining("\n")));
  }

  QueryResult result = response.getResult();
  Iterator<List<FieldValue>> iter = result.iterateAll();

  DateTimeFormatter formatter = ISODateTimeFormat.dateTimeNoMillis().withZoneUTC();
  while (iter.hasNext()) {
    List<FieldValue> row = iter.next();
    System.out.printf(
        "%s\n",
        formatter.print(
            new DateTime(
                // Timestamp values are returned in microseconds since 1970-01-01T00:00:00 UTC,
                // but org.joda.time.DateTime constructor accepts times in milliseconds.
                row.get(0).getTimestampValue() / 1000,
                DateTimeZone.UTC)));
  }
}

Uso de structs em consultas parametrizadas

Para usar um struct em um parâmetro de consulta, defina o tipo como STRUCT<T>, onde T define os campos e tipos no struct. As definições de campo são separadas por vírgulas e têm o formato field_name TF, onde TF é o tipo do campo. Por exemplo, STRUCT<x INT64, y STRING> define um struct com um campo denominado x do tipo INT64 e um segundo campo denominado y do tipo STRING.

Consulte a referência de tipos de dados para mais informações sobre o tipo de struct.

Linha de comando

Esta simples consulta demonstra o uso dos tipos estruturados retornando o valor do parâmetro.

bq query --use_legacy_sql=False \
    --parameter='struct_value:STRUCT<x INT64, y STRING>:{"x": 1, "y": "foo"}' \
    'SELECT @struct_value AS s;'

API

Para usar um parâmetro de struct, defina jobs#configuration.query.queryParameters.parameterType.type como STRUCT.

Adicione um objeto para cada campo do struct a jobs#configuration.query.queryParameters.parameterType.structTypes. Se os valores de struct forem escalares, defina o tipo como o tipo dos valores, por exemplo STRING. Se forem matrizes, defina o parâmetro acima como ARRAY e o campo arrayType aninhado como o tipo apropriado. Se os valores de struct forem estruturas, defina type como STRUCT e adicione structTypes conforme necessário.

Esta simples consulta demonstra o uso dos tipos estruturados retornando o valor do parâmetro.

{
  "query": "SELECT @struct_value AS s;",
  "queryParameters": [
    {
      "name": "struct_value",
      "parameterType": {
        "type": "STRUCT",
        "structTypes": [
          {
            "name": "x",
            "type": {
              "type": "INT64"
            }
          },
          {
            "name": "y",
            "type": {
              "type": "STRING"
            }
          }
        ]
      },
      "parameterValue": {
        "structValues": {
          "x": {
            "value": "1"
          },
          "y": {
            "value": "foo"
          }
        }
      }
    }
  ],
  "useLegacySql": false,
  "parameterMode": "NAMED"
}

Tente fazer isso no Google APIs Explorer.

Execução de consultas interativas ou em lote

Por padrão, o BigQuery executa consultas interativas, o que significa que elas são executadas assim que possível. Essas consultas são realizadas de acordo com o limite de taxa simultânea e o limite de taxa diária. Todos os exemplos em Execução de consultas síncronas são consultas interativas.

O BigQuery também oferece consultas em lote. O BigQuery coloca em fila cada consulta em lote em seu nome e inicia a consulta assim que os recursos inativos tornam-se disponíveis, normalmente em alguns minutos. Se o BigQuery não iniciou a consulta dentro de 24 horas, ele altera a prioridade do job para interativa. As consultas em lote não são realizadas de acordo com o limite de taxa simultânea, o que facilita a execução de muitas consultas de uma vez.

Para executar uma consulta em lote:

IU da Web

  1. Acesse a IU da Web do BigQuery.
    Acessar a IU da Web do BigQuery

  2. Clique no botão Escrever consulta.

  3. Insira uma consulta SQL válida do BigQuery na área de texto Nova consulta.

  4. Clique no botão Mostrar opções.

  5. Selecione a opção Em lote na seção Prioridade da consulta.

  6. Clique no botão Executar consulta.

Linha de comando


Use a sinalização --batch para executar uma consulta em lote. Por exemplo, a seguinte consulta mostra como iniciar uma consulta em lote assíncrona:

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

API

Inclua a propriedade configuration.query.priority com o valor definido como BATCH. Por exemplo, todos os exemplos específicos do idioma em Execução de consultas assíncronas usam consultas em lote.

Uso de resultados de consulta armazenados em cache

O BigQuery grava todos os resultados de consulta em uma tabela. A tabela (de destino) é explicitamente identificada pelo usuário ou é uma tabela temporária de resultados armazenados em cache. As tabelas temporárias de resultados armazenados em cache são mantidas por projeto e por usuário.

Quando você executa uma consulta duplicada, o BigQuery tenta reutilizar os resultados armazenados em cache. Quando os resultados da consulta são recuperados de uma tabela de resultados armazenados em cache, a propriedade statistics.query.cacheHit de estatísticas do job é retornada como true, e você não é cobrado pela consulta. Embora não haja cobrança em consultas que usam resultados armazenados em cache, elas estão sujeitas às políticas de cota do BigQuery. Além de reduzir custos, essas consultas são significativamente mais rápidas, pois o BigQuery não precisa computar o conjunto de resultados.

Todos os resultados de consultas, incluindo as interativas e em lote, são armazenados em cache em tabelas temporárias por aproximadamente 24 horas, com algumas exceções. Os resultados de consultas não são armazenados em cache:

  • Quando uma tabela de destino é especificada na configuração do job, na IU da Web, na linha de comando ou na API.

  • Se qualquer uma das tabelas referenciadas ou visualizações lógicas foi alterada desde que os resultados foram armazenados em cache.

  • Quando qualquer uma das tabelas referenciadas pela consulta recebeu recentemente inserções de streaming (um buffer de streaming é anexado à tabela), mesmo que não chegue nenhuma linha nova.

  • Se a consulta usa funções que não são deterministas, por exemplo, funções de data e hora, como CURRENT_TIMESTAMP() e NOW(), e outras funções, como CURRENT_USER(), retornam valores diferentes dependendo de quando a consulta é executada.

  • Se os resultados armazenados em cache expirarem. A duração comum do cache é de 24 horas, mas os resultados são de esforço máximo e podem ser invalidados antes.

Para que os resultados da consulta permaneçam na tabela de resultados armazenados em cache, o conjunto de resultados precisa ser menor que o tamanho máximo da resposta. Para mais informações sobre como gerenciar conjuntos grandes de resultados, consulte Como retornar resultados extensos de consulta.

As tabelas de resultados armazenados em cache têm restrições de uso, devido ao status especial delas. Você não pode apontar para tabelas de resultados armazenados em cache com declarações DML. Embora a semântica atual permita isso, o uso de resultados armazenados em cache como entrada para jobs dependentes não é recomendado. Por exemplo, não convém enviar jobs de consulta que recuperam resultados da tabela de cache. Em vez disso, grave os resultados em uma tabela de destino nomeada. Para permitir uma limpeza fácil, recursos como a propriedade defaultTableExpirationMs de nível de conjunto de dados poderão expirar os dados automaticamente após o prazo especificado.

Desativação da recuperação de resultados armazenados em cache

A opção Usar resultados armazenados em cache reutiliza os resultados de uma execução anterior da mesma consulta, exceto quando as tabelas que estão sendo consultadas são alteradas. O uso de resultados armazenados em cache é útil somente no caso de consultas repetidas. Para novas consultas, a opção Usar resultados armazenados em cache não tem nenhum efeito, embora seja ativada por padrão.

Quando você repete uma consulta com a opção Usar resultados armazenados em cache desativada, o resultado existente armazenado em cache é substituído. Para isso, o BigQuery precisa computar o resultado da consulta, e você é cobrado por ela. Esse recurso é útil principalmente em cenários comparativos de mercado.

Para desativar a recuperação de resultados armazenados em cache e forçar a avaliação ativa de um job de consulta, defina a propriedade configuration.query.useQueryCache do job como false.

Para desativar a opção Usar resultados armazenados em cache:

IU da Web

  1. Acesse a IU da Web do BigQuery.
    Acessar a IU da Web do BigQuery

  2. Clique no botão Escrever consulta.

  3. Insira uma consulta SQL válida do BigQuery na área de texto Nova consulta.

  4. Clique em Mostrar opções.

  5. Desmarque Usar resultados armazenados em cache.

Linha de comando


Use a sinalização nouse_cache para substituir o cache de consulta. O exemplo a seguir força o BigQuery a processar a consulta sem usar os resultados armazenados em cache existentes:

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

API

Para processar uma consulta sem usar os resultados armazenados em cache existentes, defina a propriedade useQueryCache como false.

Garantia de uso do cache

Se você usa a função jobs.insert() para executar uma consulta, pode forçar o job de consulta a falhar, a menos que os resultados armazenados em cache possam ser usados definindo a propriedade createDisposition da configuração do job como CREATE_NEVER.

Se o resultado da consulta não existir no cache, será retornado o erro NOT_FOUND.

Verificação de uso do cache

Há duas maneiras de determinar se o BigQuery retornou um resultado usando o cache:

  • Se você usa a IU da Web do BigQuery, a string de resultado não inclui informações sobre o número de bytes processados e exibe a palavra "cached" (armazenado em cache).

  • Se você usa a API do BigQuery, a propriedade cacheHit no resultado da pesquisa é definida como true.

Como retornar resultados extensos de consulta

Normalmente, as consultas têm um tamanho máximo de resposta. Se você pretende executar uma consulta que possa retornar resultados extensos, defina allowLargeResults como true na configuração do job. A configuração de resultados extensos requer que você especifique uma tabela de destino. Pode haver cobranças de armazenamento relacionadas à tabela de destino.

As consultas com resultados extensos estão sujeitas a estas limitações:

  • Você precisa especificar uma tabela de destino.
  • Não é possível especificar uma cláusula ORDER BY, TOP ou LIMIT de nível superior. Se fizer isso, negará o benefício de usar allowLargeResults, pois a saída da consulta não poderá mais ser computada em paralelo.
  • As funções de janela poderão retornar resultados de consulta extensos somente se usadas em conjunto com uma cláusula PARTITION BY.

Para permitir resultados de consulta extensos:

IU da Web

  1. Acesse a IU da Web do BigQuery.
    Acessar a IU da Web do BigQuery

  2. Clique no botão Escrever consulta.

  3. Insira uma consulta SQL válida do BigQuery na área de texto Nova consulta.

  4. Clique em Mostrar opções.

  5. Em Tabela de destino, clique em Selecionar tabela e digite um nome no campo Código da tabela.

  6. Em Tamanho dos resultados, marque Permitir resultados extensos.

Linha de comando


Use as sinalizações allow_large_results e destination_table para criar uma tabela de destino e manter o conjunto grande de resultados:

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

API

Para permitir resultados extensos, defina a propriedade configuration.query.allowLargeResults como true e especifique a tabela de destino usando configuration.query.destinationTable.

Consulta em subconjuntos de tabelas por meio de decoradores

Normalmente, o BigQuery realiza uma verificação completa de colunas ao executar uma consulta. Você pode usar decoradores de tabela para executar uma consulta em um subconjunto de dados com melhor custo-benefício. Para mais informações, consulte decoradores de tabela.

Uso de meta-tabelas

O BigQuery oferece algumas tabelas especiais cujo conteúdo representa metadados, como nomes das tabelas. As "meta-tabelas" são somente leitura. Normalmente, você as utiliza fazendo referência a elas em uma declaração SELECT.

É possível usar as meta-tabelas em outras operações de API além do job de consulta, como tables.get ou tabledata.list. Elas não permitem tables.insert e não podem ser usadas como tabelas de destino, e também não aceitam decoradores. As meta-tabelas não aparecem em tables.list do conjunto de dados.

Metadados sobre tabelas em conjunto de dados

Você pode acessar os metadados sobre as tabelas em um conjunto de dados usando a meta-tabela __TABLES__ ou __TABLES_SUMMARY__.

Use a seguinte sintaxe para consultar uma meta-tabela:

    SELECT [FIELD] FROM [DATASET].__TABLES__;

Onde DATASET é o nome do conjunto de dados e FIELD é um dos seguintes:

Campo Descrição
project_id Nome do projeto.
dataset_id Nome do conjunto de dados.
table_id Nome da tabela.
creation_time A hora de criação da tabela em milissegundos desde 1º de janeiro de 1970 (UTC).
last_modified_time A hora da alteração mais recente da tabela em milissegundos desde 1º de janeiro de 1970 (UTC).
row_count Número de linhas na tabela.
size_bytes Tamanho total da tabela, medido em bytes.
type Um número inteiro que representa o tipo de tabela: normal (1) ou visualização (2).

Exemplo

A consulta a seguir recupera os metadados sobre as tabelas no conjunto de dados publicdata:samples.

    SELECT * FROM publicdata:samples.__TABLES__;

Retorna:

+------------+------------+-----------------+---------------+--------------------+-----------+--------------+------+
| project_id | dataset_id |    table_id     | creation_time | last_modified_time | row_count |  size_bytes  | type |
+------------+------------+-----------------+---------------+--------------------+-----------+--------------+------+
| publicdata | samples    | github_nested   | 1348782587310 |      1348782587310 |   2541639 |   1694950811 |    1 |
| publicdata | samples    | github_timeline | 1335915950690 |      1335915950690 |   6219749 |   3801936185 |    1 |
| publicdata | samples    | gsod            | 1335916040125 |      1440625349328 | 114420316 |  17290009238 |    1 |
| publicdata | samples    | natality        | 1335916045005 |      1440625330604 | 137826763 |  23562717384 |    1 |
| publicdata | samples    | shakespeare     | 1335916045099 |      1440625429551 |    164656 |      6432064 |    1 |
| publicdata | samples    | trigrams        | 1335916127449 |      1445684180324 |  68051509 | 277168458677 |    1 |
| publicdata | samples    | wikipedia       | 1335916132870 |      1445689914564 | 313797035 |  38324173849 |    1 |
+------------+------------+-----------------+---------------+--------------------+-----------+--------------+------+

Dimensionamento para um número grande de tabelas

__TABLES__ apresenta mais informações que Tables.list, mas Tables.list é mais ágil em um conjunto de dados com muitas tabelas.

__TABLES_SUMMARY__ é uma meta-tabela que é mais ágil que __TABLES__ porque não contém os campos dependentes de dados: last_modified_time, row_count e size_bytes.

Geralmente, __TABLES__ e __TABLES_SUMMARY__ são razoavelmente ágeis em conjuntos de dados com no máximo algumas milhares de tabelas. Para conjuntos de dados maiores, elas se tornam cada vez mais lentas e podem exceder os recursos disponíveis.

Tabelas temporárias e permanentes

O BigQuery salva todos os resultados de consulta em uma tabela, que pode ser permanente ou temporária:

  • Uma tabela temporária é uma tabela nomeada aleatoriamente salva em um conjunto de dados especial, ela tem duração de aproximadamente 24 horas. As tabelas temporárias não estão disponíveis para compartilhamento e não são visíveis por nenhum método de lista padrão ou outros métodos de manipulação de tabela.

  • Uma tabela permanente pode ser uma nova ou existente em qualquer conjunto de dados no qual você tenha privilégios de GRAVAÇÃO.

Limites adicionais

Além das cotas de consulta padrão, os seguintes limites são aplicados à consulta de dados.

  • Máximo de tabelas por consulta: 1.000
  • Tamanho máximo da consulta: 256 KB
Esta página foi útil? Conte sua opinião sobre:

Enviar comentários sobre…

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