Parcourir les données de table

Ce document explique comment parcourir les données de table et les résultats de requête à l'aide de l'API REST de BigQuery.

Paginer des résultats à l'aide de l'API

Toutes les méthodes *collection*.list renvoient des résultats paginés sous certaines conditions. Le nombre de résultats par page est contrôlé par la propriété maxResults.

Méthode Critères de pagination Valeur maxResults par défaut Valeur maxResults maximale Valeur maxFieldValues maximale
Tabledata.list Renvoie des résultats paginés si la réponse est d'une taille supérieure à 10 Mo1 de données ou contient un nombre de lignes supérieur à maxResults. 100 000 Illimité Illimité
Toutes les autres méthodes *collection*.list Renvoie des résultats paginés si le nombre de lignes de la réponse est supérieur à maxResults et inférieur aux limites maximales. 10 000 Illimité 300 000

Si le résultat dépasse la limite d'octets ou de champs, il est tronqué pour respecter la limite. Si une ligne est supérieure à la limite d'octets ou de champ définie, tabledata.list peut renvoyer jusqu'à 100 Mo de données1, ce qui est conforme à la limite maximale de taille de ligne pour les résultats de requête.

1 La taille des lignes est approximative, car elle est basée sur la représentation interne des données de ligne. La limite maximale de taille de ligne est appliquée à certaines étapes de l'exécution de la tâche de requête.

jobs.getQueryResult peut renvoyer 2 Mo de données, sauf si un volume supérieur est explicitement demandé via l'assistance.

Une page est un sous-ensemble du nombre total de lignes. Si vos résultats correspondent à plus d'une page de données, les données de résultats contiendront une propriété pageToken. Pour extraire la page de résultats suivante, effectuez un autre appel list et incluez la valeur du jeton en tant que paramètre d'URL nommé pageToken.

La méthode tabledata.list, qui permet de paginer les données d'une table, utilise une valeur de décalage de ligne ou un jeton de page. Pour en savoir plus, consultez la section Parcourir les données d'une table.

Vous trouverez ci-dessous des exemples de pagination sur des données de table BigQuery.

C#

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour C# décrite dans le guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery en langage C#.


using Google.Api.Gax;
using Google.Apis.Bigquery.v2.Data;
using Google.Cloud.BigQuery.V2;
using System;
using System.Collections.Generic;
using System.Linq;

public class BigQueryBrowseTable
{
    public void BrowseTable(
        string projectId = "your-project-id"
    )
    {
        BigQueryClient client = BigQueryClient.Create(projectId);
        TableReference tableReference = new TableReference()
        {
            TableId = "shakespeare",
            DatasetId = "samples",
            ProjectId = "bigquery-public-data"
        };
        // Load all rows from a table
        PagedEnumerable<TableDataList, BigQueryRow> result = client.ListRows(
            tableReference: tableReference,
            schema: null
        );
        // Print the first 10 rows
        foreach (BigQueryRow row in result.Take(10))
        {
            Console.WriteLine($"{row["corpus"]}: {row["word_count"]}");
        }
    }
}

Java

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Java décrite dans le guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery en langage Java.

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQuery.TableDataListOption;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableResult;

// Sample to directly browse a table with optional paging
public class BrowseTable {

  public static void runBrowseTable() {
    // TODO(developer): Replace these variables before running the sample.
    String table = "MY_TABLE_NAME";
    String dataset = "MY_DATASET_NAME";
    browseTable(dataset, table);
  }

  public static void browseTable(String dataset, String table) {
    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();

      // Identify the table itself
      TableId tableId = TableId.of(dataset, table);

      // Page over 100 records. If you don't need pagination, remove the pageSize parameter.
      TableResult result = bigquery.listTableData(tableId, TableDataListOption.pageSize(100));

      // Print the records
      result
          .iterateAll()
          .forEach(
              row -> {
                row.forEach(fieldValue -> System.out.print(fieldValue.toString() + ", "));
                System.out.println();
              });

      System.out.println("Query ran successfully");
    } catch (BigQueryException e) {
      System.out.println("Query failed to run \n" + e.toString());
    }
  }
}

Go

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Go décrite dans le guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence sur l'API BigQuery en langage Go.

Par défaut, les bibliothèques clientes Cloud pour Go effectuent automatiquement la pagination. Vous n'avez donc pas besoin d'effectuer cette tâche vous-même. Exemple :

import (
	"context"
	"fmt"
	"io"

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

// browseTable demonstrates reading data from a BigQuery table directly without the use of a query.
// For large tables, we also recommend the BigQuery Storage API.
func browseTable(w io.Writer, projectID, datasetID, tableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydataset"
	// tableID := "mytable"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	table := client.Dataset(datasetID).Table(tableID)
	it := table.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
}

Node.js

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Node.js décrite dans le guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery Node.js.

Par défaut, les bibliothèques clientes Cloud pour Node.js effectuent automatiquement la pagination. Vous n'avez donc pas besoin d'effectuer cette tâche vous-même. Exemple :

// Import the Google Cloud client library using default credentials
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function browseTable() {
  // Retrieve a table's rows using manual pagination.

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = 'my_dataset'; // Existing dataset
  // const tableId = 'my_table'; // Table to create

  const query = `SELECT name, SUM(number) as total_people
    FROM \`bigquery-public-data.usa_names.usa_1910_2013\`
    GROUP BY name
    ORDER BY total_people
    DESC LIMIT 100`;

  // Create table reference.
  const dataset = bigquery.dataset(datasetId);
  const destinationTable = dataset.table(tableId);

  // For all options, see https://cloud.google.com/bigquery/docs/reference/rest/v2/Job#jobconfigurationquery
  const queryOptions = {
    query: query,
    destination: destinationTable,
  };

  // Run the query as a job
  const [job] = await bigquery.createQueryJob(queryOptions);

  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/jobs/getQueryResults
  const queryResultsOptions = {
    // Retrieve zero resulting rows.
    maxResults: 0,
  };

  // Wait for the job to finish.
  await job.getQueryResults(queryResultsOptions);

  function manualPaginationCallback(err, rows, nextQuery) {
    rows.forEach(row => {
      console.log(`name: ${row.name}, ${row.total_people} total people`);
    });

    if (nextQuery) {
      // More results exist.
      destinationTable.getRows(nextQuery, manualPaginationCallback);
    }
  }

  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tabledata/list
  const getRowsOptions = {
    autoPaginate: false,
    maxResults: 20,
  };

  // Retrieve all rows.
  destinationTable.getRows(getRowsOptions, manualPaginationCallback);
}
browseTable();

PHP

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour PHP dans le guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery PHP.

La pagination s'effectue automatiquement dans les bibliothèques clientes Cloud pour PHP à l'aide de la fonction de générateur rows, qui récupère la page de résultats suivante lors de l'itération.

use Google\Cloud\BigQuery\BigQueryClient;

/** Uncomment and populate these variables in your code */
// $projectId = 'The Google project ID';
// $datasetId = 'The BigQuery dataset ID';
// $tableId   = 'The BigQuery table ID';
// $maxResults = 10;

$maxResults = 10;
$startIndex = 0;

$options = [
    'maxResults' => $maxResults,
    'startIndex' => $startIndex
];
$bigQuery = new BigQueryClient([
    'projectId' => $projectId,
]);
$dataset = $bigQuery->dataset($datasetId);
$table = $dataset->table($tableId);
$numRows = 0;
foreach ($table->rows($options) as $row) {
    print('---');
    foreach ($row as $column => $value) {
        printf('%s: %s' . PHP_EOL, $column, $value);
    }
    $numRows++;
}

Python

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Python décrite dans le guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery Python.

Par défaut, les bibliothèques clientes Cloud pour Python effectuent automatiquement la pagination. Vous n'avez donc pas besoin d'effectuer cette tâche vous-même. Exemple :


from google.cloud import bigquery

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

# TODO(developer): Set table_id to the ID of the table to browse data rows.
# table_id = "your-project.your_dataset.your_table_name"

# Download all rows from a table.
rows_iter = client.list_rows(table_id)  # Make an API request.

# Iterate over rows to make the API requests to fetch row data.
rows = list(rows_iter)
print("Downloaded {} rows from table {}".format(len(rows), table_id))

# Download at most 10 rows.
rows_iter = client.list_rows(table_id, max_results=10)
rows = list(rows_iter)
print("Downloaded {} rows from table {}".format(len(rows), table_id))

# Specify selected fields to limit the results to certain columns.
table = client.get_table(table_id)  # Make an API request.
fields = table.schema[:2]  # First two columns.
rows_iter = client.list_rows(table_id, selected_fields=fields, max_results=10)
rows = list(rows_iter)
print("Selected {} columns from table {}.".format(len(rows_iter.schema), table_id))
print("Downloaded {} rows from table {}".format(len(rows), table_id))

# Print row data in tabular format.
rows = client.list_rows(table, max_results=10)
format_string = "{!s:<16} " * len(rows.schema)
field_names = [field.name for field in rows.schema]
print(format_string.format(*field_names))  # Prints column headers.
for row in rows:
    print(format_string.format(*row))  # Prints row data.

Ruby

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Ruby décrite dans le guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery en langage Ruby.

La pagination s'effectue automatiquement dans les bibliothèques clientes Cloud pour Ruby à l'aide de Table#data et de Data#next.

require "google/cloud/bigquery"

def browse_table
  bigquery = Google::Cloud::Bigquery.new project_id: "bigquery-public-data"
  dataset  = bigquery.dataset "samples"
  table    = dataset.table "shakespeare"

  # Load all rows from a table
  rows = table.data

  # Load the first 10 rows
  rows = table.data max: 10

  # Print row data
  rows.each { |row| puts row }
end

Demander des pages arbitraires et éviter les appels de liste redondants

Lorsque vous exécutez une pagination en arrière ou que vous accédez à des pages arbitraires en utilisant les valeurs pageToken mises en cache, il est possible que les données de vos pages aient été modifiées depuis la dernière consultation, mais rien n'indique clairement que c'est le cas. Pour limiter ce problème, vous pouvez utiliser la propriété ETag.

Toutes les méthodes collection.list (sauf Tabledata) renvoient une propriété ETag dans le résultat. Cette propriété est un hachage des résultats de la page, qui permet de vérifier si la page a été modifiée depuis la dernière requête. Lorsque vous adressez à BigQuery une requête contenant une valeur ETag, BigQuery compare cette valeur à la valeur ETag renvoyée par l'API et répond en fonction de la correspondance entre les deux valeurs. Voici comment vous pouvez exploiter les valeurs ETag pour éviter les appels de liste redondants :

  • Si vous souhaitez que les valeurs de liste ne soient renvoyées qu'en cas de changement :

    Si vous souhaitez seulement renvoyer une page de valeurs de liste en cas de changement de valeurs, vous pouvez effectuer un appel de liste avec un ETag précédemment stocké à l'aide de l'en-tête HTTP "if-none-match". Si l'ETag que vous fournissez ne correspond pas à l'ETag sur le serveur, BigQuery renvoie une page de nouvelles valeurs de liste. Si les ETag correspondent, BigQuery renvoie un résultat HTTP 304 "Not Modified" (Non modifié) sans aucune valeur. Par exemple, une page Web où les utilisateurs peuvent remplir des informations stockées dans BigQuery de façon récurrente. Vous pouvez éviter les appels de liste redondants à BigQuery si vos données ne sont pas modifiées en vous servant de l'en-tête "if-none-match" combiné avec des ETag.

  • Si vous souhaitez que les valeurs de liste ne soient renvoyées qu'en l'absence de changement :

    Si vous ne souhaitez renvoyer une page de valeurs de liste que dans le cas où les valeurs n'ont pas changé, vous pouvez spécifier l'en-tête HTTP "if-match". BigQuery vérifie la correspondance entre les valeurs ETag et renvoie la page de résultats si les résultats n'ont pas changé, ou renvoie un message d'erreur 412 "Precondition Failed" (Échec de la condition préalable) en cas de modification de la page.

Remarque : Bien que les valeurs ETag soient un excellent moyen d'éviter les appels de liste redondants, vous pouvez appliquer les mêmes méthodes pour déterminer si des objets ont été modifiés. Par exemple, vous pouvez exécuter une requête Get pour une table spécifique et utiliser les ETag pour déterminer si la table a été modifiée avant de renvoyer la réponse complète.

Parcourir des résultats de requêtes

Chaque requête écrit dans une table de destination. Si aucune table de destination n'est fournie, l'API BigQuery renseigne automatiquement la propriété de table de destination avec une référence à une table anonyme temporaire.

API

Lisez le champ jobs.config.query.destinationTable pour déterminer la table dans laquelle les résultats de la requête ont été écrits. Appelez la méthode tabledata.list pour lire les résultats de la requête.

Java

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Java décrite dans le guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery en langage Java.

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.TableId;
import com.google.cloud.bigquery.TableResult;

// Sample to run query with pagination.
public class QueryPagination {

  public static void runQueryPagination() {
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String query =
        "SELECT name, SUM(number) as total_people"
            + " FROM `bigquery-public-data.usa_names.usa_1910_2013`"
            + " GROUP BY name"
            + " ORDER BY total_people DESC"
            + " LIMIT 100";
    queryPagination(datasetName, tableName, query);
  }

  public static void queryPagination(String datasetName, String tableName, 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();

      TableId tableId = TableId.of(datasetName, tableName);
      QueryJobConfiguration queryConfig =
          QueryJobConfiguration.newBuilder(query)
              // save results into a table.
              .setDestinationTable(tableId)
              .build();

      bigquery.query(queryConfig);

      TableResult results =
          bigquery.listTableData(tableId, BigQuery.TableDataListOption.pageSize(20));

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

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

Node.js

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Node.js décrite dans le guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery en langage Node.js.

// Import the Google Cloud client library using default credentials
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function queryPagination() {
  // Run a query and get rows using automatic pagination.

  const query = `SELECT name, SUM(number) as total_people
  FROM \`bigquery-public-data.usa_names.usa_1910_2013\`
  GROUP BY name
  ORDER BY total_people DESC
  LIMIT 100`;

  // Run the query as a job.
  const [job] = await bigquery.createQueryJob(query);

  // Wait for job to complete and get rows.
  const [rows] = await job.getQueryResults();

  console.log('Query results:');
  rows.forEach(row => {
    console.log(`name: ${row.name}, ${row.total_people} total people`);
  });
}
queryPagination();

Python

La méthode QueryJob.result renvoie un itérable des résultats de la requête. Vous pouvez également procéder comme suit :

  1. Lisez la propriété QueryJob.destination. Si cette propriété n'est pas configurée, elle est définie par l'API sur une référence à une table anonyme temporaire.
  2. Obtenez le schéma de la table avec la méthode Client.get_table.
  3. Créez un itérable sur toutes les lignes de la table de destination à l'aide de la méthode Client.list_rows.

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Python décrite dans le guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery Python.


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`
    GROUP BY name
    ORDER BY total_people DESC
"""
query_job = client.query(query)  # Make an API request.
query_job.result()  # Wait for the query to complete.

# Get the destination table for the query results.
#
# All queries write to a destination table. If a destination table is not
# specified, the BigQuery populates it with a reference to a temporary
# anonymous table after the query completes.
destination = query_job.destination

# Get the schema (and other properties) for the destination table.
#
# A schema is useful for converting from BigQuery types to Python types.
destination = client.get_table(destination)

# Download rows.
#
# The client library automatically handles pagination.
print("The query data:")
rows = client.list_rows(destination, max_results=20)
for row in rows:
    print("name={}, count={}".format(row["name"], row["total_people"]))