Lire des données avec l'API BigQuery à l'aide de la pagination

Ce document explique comment lire les données de table et les résultats de requête avec l'API BigQuery à l'aide de la pagination.

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

Toutes les méthodes *collection*.list renvoient des résultats paginés sous certaines conditions. La propriété maxResults limite le nombre de résultats par page.

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 Mo¹ de données ou contient un nombre de lignes supérieur à `maxResults`.	Illimité	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ées¹, ce qui est conforme à la limite maximale de taille de ligne pour les résultats de requête. Il n'existe pas de taille minimale par page, et certaines pages peuvent renvoyer plus de lignes que d'autres.

¹ 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.getQueryResults peut renvoyer 20 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 contiennent 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.

Effectuer des itérations via les résultats des bibliothèques clientes

Les bibliothèques clientes cloud gèrent les détails de bas niveau de la pagination des API et offrent une expérience semblable à celle d'un itérateur qui simplifie l'interaction avec les éléments individuels dans les réponses de la page.

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

C#

Avant d'essayer cet exemple, suivez les instructions de configuration pour C# du 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 pour C#.

Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes.


using Google.Api.Gax;
using Google.Apis.Bigquery.v2.Data;
using Google.Cloud.BigQuery.V2;
using System;
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 cet exemple, suivez les instructions de configuration pour Java du 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 pour 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 cet exemple, suivez les instructions de configuration pour Go du 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 pour 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 cet exemple, suivez les instructions de configuration pour Node.js du 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 pour 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 cet exemple, suivez les instructions de configuration pour PHP du 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 pour 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 cet exemple, suivez les instructions de configuration pour Python du 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 pour 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 cet exemple, suivez les instructions de configuration pour Ruby du 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 pour 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. Vous pouvez utiliser les ETag pour éviter les appels de liste redondants comme suit :

Pour renvoyer des valeurs de liste si les valeurs ont changé.

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 code d'état HTTP 304 Not Modified et aucune valeur. Exemple : une page Web où les utilisateurs peuvent remplir des informations stockées dans BigQuery de façon récurrente. Si vos données ne sont pas modifiées, vous pouvez éviter les appels de liste redondants à BigQuery en utilisant l'en-tête "if-none-match" combiné avec des ETag.
Pour renvoyer des valeurs de liste si les valeurs n'ont pas changé.

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

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 main(String[] args) {
    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));

      // First Page
      results
          .getValues()
          .forEach(row -> row.forEach(val -> System.out.printf("%s,\n", val.toString())));

      while (results.hasNextPage()) {
        // Remaining Pages
        results = results.getNextPage();
        results
            .getValues()
            .forEach(row -> row.forEach(val -> System.out.printf("%s,\n", val.toString())));
      }

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

Pour définir le nombre de lignes renvoyées sur chaque page, utilisez une tâche GetQueryResults et définissez l'option pageSize sur l'objet QueryResultsOption que vous transmettez, comme illustré dans l'exemple suivant :

TableResult result = job.getQueryResults();
QueryResultsOption queryResultsOption = QueryResultsOption.pageSize(20);

TableResult result = job.getQueryResults(queryResultsOption);

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. Par ailleurs,

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.
Obtenez le schéma de la table avec la méthode Client.get_table.
Créez un itérable sur toutes les lignes de la table de destination à l'aide de la méthode Client.list_rows.


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"]))