Créer et utiliser des tables en cluster

Ce document décrit comment créer et utiliser des tables en cluster dans BigQuery. Pour une présentation de la disponibilité des tables en cluster dans BigQuery, consultez la page Présentation des tables en cluster.

Créer des tables en cluster

Vous pouvez créer une table en cluster en utilisant les méthodes suivantes :

• Créer une table à partir d'un résultat de requête :

  • Exécutez une instruction LDD CREATE TABLE AS SELECT.
  • Exécutez une requête qui crée une table de destination en cluster.

• Utilisez une instruction LDD CREATE TABLE avec une clause CLUSTER BY contenant un élément clustering_column_list.

• Exécutez la commande bq mk de l'outil de ligne de commande bq.

• Appelez la méthode API tables.insert.

• Charger les données dans BigQuery.

• Utilisez les bibliothèques client.

Nommer les tables

Lorsque vous créez une table dans BigQuery, le nom de la table doit être unique pour chaque ensemble de données. Le nom de la table peut :

• contenir des caractères comportant jusqu'à 1 024 octets UTF-8 ;
• contenir des caractères Unicode des catégories L (lettre), M (marque), N (nombre), Pc (ponctuation de type connecteur, y compris trait de soulignement), Pd (ponctuation de type tiret), Zs (espace). Pour en savoir plus, consultez la section décrivant les catégories générales.

Voici des exemples de noms de table valides : table 01, ग्राहक, 00_お客様, étudiant-01.

Mises en garde :

• Les noms de table sont sensibles à la casse par défaut. mytable et MyTable peuvent coexister dans le même ensemble de données, sauf si elles font partie d'un ensemble de données avec la sensibilité à la casse désactivée.
• Certains noms de tables et préfixes de noms de tables sont réservés. Si vous recevez une erreur indiquant que le nom ou le préfixe de votre table est réservé, sélectionnez-en un autre et réessayez.

• Si vous incluez plusieurs opérateurs point (.) dans une séquence, les opérateurs en double sont implicitement supprimés.

  Par exemple, ceci :project_name....dataset_name..table_name

  Devient : project_name.dataset_name.table_name

Autorisations requises

Pour créer une table, vous devez disposer des autorisations IAM suivantes :

• bigquery.tables.create
• bigquery.tables.updateData
• bigquery.jobs.create

En outre, vous pouvez avoir besoin de l'autorisation bigquery.tables.getData pour accéder aux données que vous écrivez dans la table.

Chacun des rôles IAM prédéfinis suivants inclut les autorisations dont vous avez besoin pour créer une table :

• roles/bigquery.dataEditor
• roles/bigquery.dataOwner
• roles/bigquery.admin (inclut l'autorisation bigquery.jobs.create)
• roles/bigquery.user (inclut l'autorisation bigquery.jobs.create)
• roles/bigquery.jobUser (inclut l'autorisation bigquery.jobs.create)

En outre, si vous disposez de l'autorisation bigquery.datasets.create, vous pouvez créer et mettre à jour des tables dans les ensembles de données que vous créez.

Pour en savoir plus sur les rôles et les autorisations IAM dans BigQuery, consultez la page Rôles prédéfinis et autorisations.

Créer une table en cluster vide avec une définition de schéma

Lors de la création d'une table dans BigQuery, des colonnes de clustering doivent être spécifiées. Une fois la table créée, vous pouvez modifier les colonnes de clustering. Pour en savoir plus, consultez la section Modifier la spécification du clustering.

Les colonnes de clustering doivent être des colonnes uniques de premier niveau. Elles doivent correspondre à l'un des types de données simples suivants :

• DATE
• BOOLEAN
• GEOGRAPHY
• INTEGER
• NUMERIC
• BIGNUMERIC
• STRING
• TIMESTAMP
• RANGE (bêta)

Un maximum de quatre colonnes de clustering peut être spécifié. Si plusieurs colonnes sont spécifiées, leur ordre détermine l'ordre des données. Par exemple, si la table est mise en cluster selon les colonnes a, b et c, les données sont triées dans le même ordre : la colonne a en premier, puis la colonne b et enfin la colonne c. Il est recommandé de faire apparaître en premier la colonne la plus fréquemment filtrée ou agrégée.

L'ordre des colonnes de clustering affecte également les performances et le tarif des requêtes. Pour en savoir plus sur les bonnes pratiques concernant l'interrogation de tables en cluster, consultez la page Interroger des tables en cluster.

Pour créer une table en cluster vide avec une définition de schéma, procédez comme suit :

Console

1. Dans la console Google Cloud, accédez à la page BigQuery.

   Accéder à BigQuery

2. Dans le volet Explorateur, développez votre projet, puis sélectionnez un ensemble de données.
3. Dans la section Informations sur l'ensemble de données, cliquez sur Créer une table.
4. Dans le panneau Créer une table, spécifiez les détails suivants :
1. Dans la section Source, sélectionnez Table vide dans la liste Créer une table à partir de.
2. Dans la section Destination, spécifiez les détails suivants :
   1. Pour Ensemble de données, sélectionnez l'ensemble de données dans lequel vous souhaitez créer la table.
   2. Dans le champ Table, saisissez le nom de la table que vous souhaitez créer.
   3. Vérifiez que le champ Type de table est défini sur Table native.
3. Dans la section Schéma, saisissez la définition du schéma. Vous pouvez saisir les informations de schéma manuellement à l'aide de l'une des méthodes suivantes :
   • Option 1 : Cliquez sur Modifier sous forme de texte et collez le schéma sous la forme d'un tableau JSON. Lorsque vous utilisez un tableau JSON, vous générez le schéma en utilisant le même processus que pour la création d'un fichier de schéma JSON. Vous pouvez afficher le schéma d'une table existante au format JSON en saisissant la commande suivante :

         bq show --format=prettyjson dataset.table
         

   • Option 2 : Cliquez sur  Ajouter un champ et saisissez le schéma de la table. Spécifiez le nom, le type et le mode de chaque champ.
4. Dans le champ Ordre de clustering, saisissez entre un et quatre noms de colonnes séparés par des virgules.
5. Facultatif : dans la section Options avancées si vous souhaitez utiliser une clé de chiffrement gérée par le client, sélectionnezUtiliser une clé de chiffrement gérée par le client (CMEK). Par défaut, BigQuery chiffre le contenu client stocké au repos à l'aide d'une clé gérée par Google.
6. Cliquez sur Créer une table.

SQL

Exécutez la commande instruction LDD CREATE TABLE avec l'option CLUSTER BY. L'exemple suivant crée une table en cluster nommée myclusteredtable dans mydataset.

1. Dans la console Google Cloud, accédez à la page BigQuery.

   Accéder à BigQuery

2. Dans l'éditeur de requête, saisissez l'instruction suivante :

   CREATE TABLE mydataset.myclusteredtable
   (
     customer_id STRING,
     transaction_amount NUMERIC
   )
   CLUSTER BY
     customer_id
     OPTIONS (
       description = 'a table clustered by customer_id');
   

3. Cliquez sur Exécuter.

Pour en savoir plus sur l'exécution des requêtes, consultez Exécuter une requête interactive.

bq

Exécutez la commande bq mk avec les options suivantes :

• --table (ou le raccourci -t).
• --schema. Vous pouvez fournir la définition de schéma de la table de manière intégrée ou utiliser un fichier de schéma JSON.
• --clustering_fields. Un maximum de quatre colonnes de clustering peut être spécifié.

Les paramètres facultatifs incluent --expiration, --description, --time_partitioning_type, --time_partitioning_field, --time_partitioning_expiration, --destination_kms_key et --label.

Si vous créez une table dans un projet autre que votre projet par défaut, ajoutez l'ID du projet au nom de l'ensemble de données, en respectant le format suivant : project_id:dataset.

--destination_kms_key n'est pas présenté ici. Pour en savoir plus sur l'utilisation de --destination_kms_key, consultez la page Clés de chiffrement gérées par le client.

Entrez la commande suivante pour créer une table en cluster vide avec une définition de schéma :

bq mk \
    --table \
    --expiration INTEGER1 \
    --schema SCHEMA \
    --clustering_fields CLUSTER_COLUMNS \
    --description "DESCRIPTION" \
    --label KEY:VALUE,KEY:VALUE \
    PROJECT_ID:DATASET.TABLE

Remplacez les éléments suivants :

• INTEGER1 : durée de vie par défaut (en secondes) de la table. La valeur minimale est de 3 600 secondes (une heure). Le délai d'expiration correspond à l'heure UTC actuelle plus la valeur entière. Si vous définissez le délai d'expiration de la table lorsque vous créez une table, le paramètre d'expiration de la table par défaut de l'ensemble de données est ignoré. La définition de cette valeur supprime la table après le délai spécifié.
• SCHEMA : définition de schéma spécifiée sur la ligne de commande au format COLUMN:DATA_TYPE,COLUMN:DATA_TYPE, ou chemin d'accès au fichier de schéma JSON sur votre ordinateur local.
• CLUSTER_COLUMNS : liste de quatre colonnes de clustering au maximum, séparées par des virgules. La liste ne peut pas contenir d'espaces.
• DESCRIPTION : description de la table, entre guillemets.
• KEY:VALUE : paire clé-valeur qui représente un libellé. Vous pouvez saisir plusieurs libellés en utilisant une liste de libellés séparés par des virgules.
• PROJECT_ID : ID de votre projet.
• DATASET : un ensemble de données de votre projet.
• TABLE : nom de la table que vous créez.

Lorsque vous spécifiez le schéma sur la ligne de commande, vous ne pouvez pas inclure de type RECORD (STRUCT) ni de description de colonne. Vous ne pouvez pas non plus spécifier le mode de la colonne. Tous les modes par défaut sont définis sur NULLABLE. Pour inclure des descriptions, des modes et des types RECORD, fournissez plutôt un fichier de schéma JSON.

Exemples :

Saisissez la commande suivante pour créer une table en cluster nommée myclusteredtable dans mydataset, et non dans votre projet par défaut. Le délai d'expiration de la table est défini sur 2 592 000 (un mois de 30 jours), la description est définie sur This is my clustered table et le libellé est défini sur organization:development. La commande utilise le raccourci -t au lieu de --table.

Le schéma est spécifié de manière intégrée comme suit : timestamp:timestamp,customer_id:string,transaction_amount:float. Le champ de clustering spécifié customer_id est utilisé pour mettre en cluster la table.

bq mk \
    -t \
    --expiration 2592000 \
    --schema 'timestamp:timestamp,customer_id:string,transaction_amount:float' \
    --clustering_fields customer_id \
    --description "This is my clustered table" \
    --label org:dev \
    mydataset.myclusteredtable

Saisissez la commande suivante pour créer une table en cluster nommée myclusteredtable dans myotherproject, et non dans votre projet par défaut. La description est définie sur This is my clustered table, et le libellé est défini sur organization:development. La commande utilise le raccourci -t au lieu de --table. Cette commande ne spécifie pas de délai d'expiration de table. Si l'ensemble de données possède un délai d'expiration de table par défaut, celui-ci est appliqué. Si l'ensemble de données n'a pas de délai d'expiration de table par défaut, la table n'expire jamais.

Le schéma est spécifié dans un fichier JSON local : /tmp/myschema.json. Le champ customer_id permet de mettre en cluster la table.

bq mk \
    -t \
    --expiration 2592000 \
    --schema /tmp/myschema.json \
    --clustering_fields=customer_id \
    --description "This is my clustered table" \
    --label org:dev \
    myotherproject:mydataset.myclusteredtable

Une fois la table créée, vous pouvez mettre à jour sa description et ses libellés.

Terraform

Utilisez la ressource google_bigquery_table.

Remarque : Pour créer des objets BigQuery à l'aide de Terraform, vous devez activer l'API Cloud Resource Manager.

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.

L'exemple suivant crée une table nommée mytable qui est mise en cluster sur les colonnes ID et Created :

resource "google_bigquery_dataset" "default" {
  dataset_id                      = "mydataset"
  default_partition_expiration_ms = 2592000000  # 30 days
  default_table_expiration_ms     = 31536000000 # 365 days
  description                     = "dataset description"
  location                        = "US"
  max_time_travel_hours           = 96 # 4 days

  labels = {
    billing_group = "accounting",
    pii           = "sensitive"
  }
}

resource "google_bigquery_table" "default" {
  dataset_id          = google_bigquery_dataset.default.dataset_id
  table_id            = "mytable"
  deletion_protection = false # set to "true" in production

  clustering = ["ID", "Created"]

  schema = <<EOF
[
  {
    "name": "ID",
    "type": "INT64",
    "description": "Item ID"
  },
  {
    "name": "Item",
    "type": "STRING",
    "mode": "NULLABLE"
  },
 {
   "name": "Created",
   "type": "TIMESTAMP"
 }
]
EOF

}

Pour appliquer votre configuration Terraform dans un projet Google Cloud, suivez les procédures des sections suivantes.

Préparer Cloud Shell

1. Lancez Cloud Shell.

2. Définissez le projet Google Cloud par défaut dans lequel vous souhaitez appliquer vos configurations Terraform.

   Vous n'avez besoin d'exécuter cette commande qu'une seule fois par projet et vous pouvez l'exécuter dans n'importe quel répertoire.

   export GOOGLE_CLOUD_PROJECT=PROJECT_ID

   Les variables d'environnement sont remplacées si vous définissez des valeurs explicites dans le fichier de configuration Terraform.

Préparer le répertoire

Chaque fichier de configuration Terraform doit avoir son propre répertoire (également appelé module racine).

1. Dans Cloud Shell, créez un répertoire et un nouveau fichier dans ce répertoire. Le nom du fichier doit comporter l'extension .tf, par exemple main.tf. Dans ce tutoriel, le fichier est appelé main.tf.

   mkdir DIRECTORY && cd DIRECTORY && touch main.tf

2. Si vous suivez un tutoriel, vous pouvez copier l'exemple de code dans chaque section ou étape.

   Copiez l'exemple de code dans le fichier main.tf que vous venez de créer.

   Vous pouvez également copier le code depuis GitHub. Cela est recommandé lorsque l'extrait Terraform fait partie d'une solution de bout en bout.

3. Examinez et modifiez les exemples de paramètres à appliquer à votre environnement.
4. Enregistrez les modifications.
5. Initialisez Terraform. Cette opération n'est à effectuer qu'une seule fois par répertoire.

   terraform init

   Vous pouvez également utiliser la dernière version du fournisseur Google en incluant l'option -upgrade :

   terraform init -upgrade

Appliquer les modifications

1. Examinez la configuration et vérifiez que les ressources que Terraform va créer ou mettre à jour correspondent à vos attentes :

   terraform plan

   Corrigez les modifications de la configuration si nécessaire.

2. Appliquez la configuration Terraform en exécutant la commande suivante et en saisissant yes lorsque vous y êtes invité :

   terraform apply

   Attendez que Terraform affiche le message "Apply completed!" (Application terminée).

3. Ouvrez votre projet Google Cloud pour afficher les résultats. Dans la console Google Cloud, accédez à vos ressources dans l'interface utilisateur pour vous assurer que Terraform les a créées ou mises à jour.

Remarque : Les exemples Terraform partent généralement du principe que les API requises sont activées dans votre projet Google Cloud.

API

Appelez la méthode tables.insert avec une ressource de table définie qui spécifie les propriétés clustering.fields et schema.

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.

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.

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 create.
# table_id = "your-project.your_dataset.your_table_name"

schema = [
    bigquery.SchemaField("full_name", "STRING"),
    bigquery.SchemaField("city", "STRING"),
    bigquery.SchemaField("zipcode", "INTEGER"),
]

table = bigquery.Table(table_id, schema=schema)
table.clustering_fields = ["city", "zipcode"]
table = client.create_table(table)  # Make an API request.
print(
    "Created clustered table {}.{}.{}".format(
        table.project, table.dataset_id, table.table_id
    )
)

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.

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.

import (
	"context"
	"fmt"
	"time"

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

// createTableClustered demonstrates creating a BigQuery table with advanced properties like
// partitioning and clustering features.
func createTableClustered(projectID, datasetID, tableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydatasetid"
	// tableID := "mytableid"
	ctx := context.Background()

	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	sampleSchema := bigquery.Schema{
		{Name: "timestamp", Type: bigquery.TimestampFieldType},
		{Name: "origin", Type: bigquery.StringFieldType},
		{Name: "destination", Type: bigquery.StringFieldType},
		{Name: "amount", Type: bigquery.NumericFieldType},
	}
	metaData := &bigquery.TableMetadata{
		Schema: sampleSchema,
		TimePartitioning: &bigquery.TimePartitioning{
			Field:      "timestamp",
			Expiration: 90 * 24 * time.Hour,
		},
		Clustering: &bigquery.Clustering{
			Fields: []string{"origin", "destination"},
		},
	}
	tableRef := client.Dataset(datasetID).Table(tableID)
	if err := tableRef.Create(ctx, metaData); err != nil {
		return err
	}
	return nil
}

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.

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.

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Clustering;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.StandardTableDefinition;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;
import com.google.cloud.bigquery.TimePartitioning;
import com.google.common.collect.ImmutableList;

public class CreateClusteredTable {
  public static void runCreateClusteredTable() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    createClusteredTable(datasetName, tableName);
  }

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

      TimePartitioning partitioning = TimePartitioning.of(TimePartitioning.Type.DAY);

      Schema schema =
          Schema.of(
              Field.of("name", StandardSQLTypeName.STRING),
              Field.of("post_abbr", StandardSQLTypeName.STRING),
              Field.of("date", StandardSQLTypeName.DATE));

      Clustering clustering =
          Clustering.newBuilder().setFields(ImmutableList.of("name", "post_abbr")).build();

      StandardTableDefinition tableDefinition =
          StandardTableDefinition.newBuilder()
              .setSchema(schema)
              .setTimePartitioning(partitioning)
              .setClustering(clustering)
              .build();
      TableInfo tableInfo = TableInfo.newBuilder(tableId, tableDefinition).build();

      bigquery.create(tableInfo);
      System.out.println("Clustered table created successfully");
    } catch (BigQueryException e) {
      System.out.println("Clustered table was not created. \n" + e.toString());
    }
  }
}

Créer une table en cluster à partir d'un résultat de requête

Deux options s'offrent à vous pour créer une table en cluster à partir d'un résultat de requête :

• En écrivant les résultats dans une nouvelle table de destination et en spécifiant les colonnes de clustering.
• En utilisant une instruction LDD CREATE TABLE AS SELECT. Pour en savoir plus sur cette méthode, consultez la section Créer une table en cluster à partir d'un résultat de requête de la page Utiliser des instructions de langage de définition des données.

Vous pouvez créer une table en cluster en interrogeant une table partitionnée ou une table non partitionnée. Vous ne pouvez pas transformer une table existante en une table en cluster à l'aide de résultats de requête.

Lorsque vous créez une table en cluster à partir d'un résultat de requête, vous devez utiliser le langage SQL standard. Actuellement, l'ancien SQL ne permet pas d'interroger les tables en cluster ou d'écrire les résultats de requêtes dans des tables en cluster.

SQL

Pour créer une table en cluster à partir d'un résultat de requête, utilisez l'instruction LDD CREATE TABLE avec l'option CLUSTER BY. L'exemple suivant crée une table mise en cluster par customer_id en interrogeant une table existante, non mise en cluster :

1. Dans la console Google Cloud, accédez à la page BigQuery.

   Accéder à BigQuery

2. Dans l'éditeur de requête, saisissez l'instruction suivante :

   CREATE TABLE mydataset.clustered_table
   (
     customer_id STRING,
     transaction_amount NUMERIC
   )
   CLUSTER BY
     customer_id
   AS (
     SELECT * FROM mydataset.unclustered_table
   );
   

3. Cliquez sur Exécuter.

Pour en savoir plus sur l'exécution des requêtes, consultez Exécuter une requête interactive.

bq

Entrez la commande suivante pour créer une nouvelle table de destination en cluster à partir d'un résultat de requête :

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

Remplacez les éléments suivants :

• LOCATION : nom de votre emplacement. L'option --location est facultative. Par exemple, si vous utilisez BigQuery dans la région de Tokyo, vous pouvez définir la valeur de l'option sur asia-northeast1. Vous pouvez définir une valeur par défaut pour l'emplacement à l'aide du fichier .bigqueryrc.
• QUERY : requête en syntaxe GoogleSQL. Il n'est actuellement pas possible d'utiliser l'ancien SQL pour interroger des tables en cluster ou pour écrire des résultats de requête dans des tables en cluster. La requête peut contenir une instruction LDD CREATE TABLE spécifiant les options de création de la table en cluster. L'instruction LDD peut être utilisée à la place des indicateurs de ligne de commande individuels.

Exemples :

Saisissez la commande suivante pour écrire les résultats de la requête dans une table de destination en cluster nommée myclusteredtable au sein de l'ensemble de données mydataset. mydataset se trouve dans votre projet par défaut. La requête extrait les données d'une table non partitionnée : mytable. La colonne customer_id de la table permet de mettre en cluster la table. La colonne timestamp de la table permet de créer une table partitionnée.

bq query --use_legacy_sql=false \
    'CREATE TABLE
       mydataset.myclusteredtable
     PARTITION BY
       DATE(timestamp)
     CLUSTER BY
       customer_id
     AS (
       SELECT
         *
       FROM
         `mydataset.mytable`
     );'

API

Pour enregistrer des résultats de requête dans une table en cluster, appelez la méthode jobs.insert, configurez un job query et incluez une instruction LDD CREATE TABLE qui crée la table en cluster.

Spécifiez votre emplacement dans la propriété location de la section jobReference de la ressource de tâche.

Créer une table en cluster lors du chargement de données

Vous pouvez créer une table en cluster en spécifiant les colonnes de clustering lorsque vous chargez des données dans une nouvelle table. Vous n'avez pas besoin de créer une table vide avant de charger des données. Vous pouvez créer la table en cluster et charger les données en même temps.

Pour en savoir plus sur le chargement des données, consultez la page Introduction au chargement de données dans BigQuery.

Pour définir le clustering lors de la définition d'une tâche de chargement, procédez comme suit :

SQL

Utilisez l'instruction LOAD DATA. L'exemple suivant charge des données AVRO pour créer une table partitionnée en fonction du champ transaction_date et mise en cluster par le champ customer_id. Il configure également les partitions pour qu'elles expirent au bout de trois jours.

1. Dans la console Google Cloud, accédez à la page BigQuery.

   Accéder à BigQuery

2. Dans l'éditeur de requête, saisissez l'instruction suivante :

   LOAD DATA INTO mydataset.mytable
   PARTITION BY transaction_date
   CLUSTER BY customer_id
     OPTIONS (
       partition_expiration_days = 3)
   FROM FILES(
     format = 'AVRO',
     uris = ['gs://bucket/path/file.avro']);
   

3. Cliquez sur Exécuter.

Pour en savoir plus sur l'exécution des requêtes, consultez Exécuter une requête interactive.

API

Pour définir la configuration de clustering lors de la création d'une table via une tâche de chargement, vous pouvez insérer les propriétés Clustering pour la table.

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.

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.

import (
	"context"
	"fmt"

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

// importClusteredTable demonstrates creating a table from a load job and defining partitioning and clustering
// properties.
func importClusteredTable(projectID, destDatasetID, destTableID 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()

	gcsRef := bigquery.NewGCSReference("gs://cloud-samples-data/bigquery/sample-transactions/transactions.csv")
	gcsRef.SkipLeadingRows = 1
	gcsRef.Schema = bigquery.Schema{
		{Name: "timestamp", Type: bigquery.TimestampFieldType},
		{Name: "origin", Type: bigquery.StringFieldType},
		{Name: "destination", Type: bigquery.StringFieldType},
		{Name: "amount", Type: bigquery.NumericFieldType},
	}
	loader := client.Dataset(destDatasetID).Table(destTableID).LoaderFrom(gcsRef)
	loader.TimePartitioning = &bigquery.TimePartitioning{
		Field: "timestamp",
	}
	loader.Clustering = &bigquery.Clustering{
		Fields: []string{"origin", "destination"},
	}
	loader.WriteDisposition = bigquery.WriteEmpty

	job, err := loader.Run(ctx)
	if err != nil {
		return err
	}
	status, err := job.Wait(ctx)
	if err != nil {
		return err
	}

	if status.Err() != nil {
		return fmt.Errorf("job completed with error: %v", status.Err())
	}
	return nil
}

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.

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.

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Clustering;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.FormatOptions;
import com.google.cloud.bigquery.Job;
import com.google.cloud.bigquery.JobInfo;
import com.google.cloud.bigquery.LoadJobConfiguration;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TimePartitioning;
import com.google.common.collect.ImmutableList;

public class LoadTableClustered {

  public static void runLoadTableClustered() throws Exception {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String sourceUri = "/path/to/file.csv";
    loadTableClustered(datasetName, tableName, sourceUri);
  }

  public static void loadTableClustered(String datasetName, String tableName, String sourceUri)
      throws Exception {
    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);

      Schema schema =
          Schema.of(
              Field.of("name", StandardSQLTypeName.STRING),
              Field.of("post_abbr", StandardSQLTypeName.STRING),
              Field.of("date", StandardSQLTypeName.DATE));

      TimePartitioning partitioning = TimePartitioning.of(TimePartitioning.Type.DAY);

      Clustering clustering =
          Clustering.newBuilder().setFields(ImmutableList.of("name", "post_abbr")).build();

      LoadJobConfiguration loadJobConfig =
          LoadJobConfiguration.builder(tableId, sourceUri)
              .setFormatOptions(FormatOptions.csv())
              .setSchema(schema)
              .setTimePartitioning(partitioning)
              .setClustering(clustering)
              .build();

      Job loadJob = bigquery.create(JobInfo.newBuilder(loadJobConfig).build());

      // Load data from a GCS parquet file into the table
      // Blocks until this load table job completes its execution, either failing or succeeding.
      Job completedJob = loadJob.waitFor();

      // Check for errors
      if (completedJob == null) {
        throw new Exception("Job not executed since it no longer exists.");
      } else if (completedJob.getStatus().getError() != null) {
        // You can also look at queryJob.getStatus().getExecutionErrors() for all
        // errors, not just the latest one.
        throw new Exception(
            "BigQuery was unable to load into the table due to an error: \n"
                + loadJob.getStatus().getError());
      }
      System.out.println("Data successfully loaded into clustered table during load job");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Data not loaded into clustered table during load job \n" + e.toString());
    }
  }
}

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.

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.

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 create.
# table_id = "your-project.your_dataset.your_table_name"

job_config = bigquery.LoadJobConfig(
    skip_leading_rows=1,
    source_format=bigquery.SourceFormat.CSV,
    schema=[
        bigquery.SchemaField("timestamp", bigquery.SqlTypeNames.TIMESTAMP),
        bigquery.SchemaField("origin", bigquery.SqlTypeNames.STRING),
        bigquery.SchemaField("destination", bigquery.SqlTypeNames.STRING),
        bigquery.SchemaField("amount", bigquery.SqlTypeNames.NUMERIC),
    ],
    time_partitioning=bigquery.TimePartitioning(field="timestamp"),
    clustering_fields=["origin", "destination"],
)

job = client.load_table_from_uri(
    ["gs://cloud-samples-data/bigquery/sample-transactions/transactions.csv"],
    table_id,
    job_config=job_config,
)

job.result()  # Waits for the job to complete.

table = client.get_table(table_id)  # Make an API request.
print(
    "Loaded {} rows and {} columns to {}".format(
        table.num_rows, len(table.schema), table_id
    )
)

Contrôler l'accès aux tables en cluster

Pour configurer l'accès aux tables et aux vues, vous pouvez attribuer un rôle IAM à une entité située aux niveaux suivants, classés par ordre de ressources autorisées (de la plus grande à la plus petite) :

• À un niveau élevé dans la hiérarchie des ressources Google Cloud, tel qu'au niveau du projet, du dossier ou de l'organisation
• Au niveau de l'ensemble de données
• Au niveau de la table ou de la vue

Vous pouvez également restreindre l'accès aux données dans les tables à l'aide des méthodes suivantes :

• Sécurité au niveau des colonnes
• Masquage des données de colonne
• Sécurité au niveau des lignes

L'accès avec n'importe quelle ressource protégée par IAM est cumulatif. Par exemple, si une entité n'a pas accès au niveau supérieur (un projet, par exemple), vous pouvez lui accorder l'accès au niveau de l'ensemble de données afin qu'elle ait accès aux tables et aux vues de l'ensemble de données. De même, si l'entité n'a pas accès au niveau supérieur ou au niveau de l'ensemble de données, vous pouvez lui accorder l'accès au niveau de la table ou de la vue.

L'attribution de rôles IAM à un niveau supérieur dans la hiérarchie des ressources Google Cloud (au niveau du projet, du dossier ou de l'organisation) permet à l'entité d'accéder à un vaste ensemble de ressources. Par exemple, si vous attribuez un rôle à une entité au niveau du projet, elle obtient des autorisations qui s'appliquent à tous les ensembles de données du projet.

L'attribution d'un rôle au niveau de l'ensemble de données spécifie les opérations qu'une entité est autorisée à effectuer sur les tables et les vues de cet ensemble de données spécifique, même si cette entité n'a pas accès à un niveau supérieur. Pour en savoir plus sur la configuration des contrôles d'accès aux ensembles de données, consultez la page Contrôler l'accès aux ensembles de données.

L'attribution d'un rôle au niveau de la table ou de la vue spécifie les opérations qu'une entité est autorisée à effectuer sur des tables et des vues spécifiques, même si cette entité n'a pas accès à un niveau supérieur. Pour en savoir plus sur la configuration des contrôles d'accès au niveau des tables, consultez la page Contrôler l'accès aux tables et aux vues.

Vous pouvez également créer des rôles personnalisés IAM. Si vous créez un rôle personnalisé, les autorisations que vous accordez dépendent des opérations spécifiques que vous souhaitez autoriser l'entité à effectuer.

Vous ne pouvez pas définir une autorisation "deny" sur une ressource protégée par IAM.

Pour en savoir plus sur les rôles et les autorisations, consultez la page Comprendre les rôles dans la documentation IAM ainsi que la page rôles et autorisations IAM de BigQuery.

Utiliser des tables en cluster

Obtenir des informations sur des tables en cluster

Vous pouvez obtenir des informations sur les tables de plusieurs manières :

• Utiliser la console Google Cloud
• Utiliser la commande bq show de l'outil de ligne de commande bq
• Appeler la méthode API tables.get
• En interrogeant des vues INFORMATION_SCHEMA

Autorisations requises

Pour obtenir des informations sur les tables, vous devez au minimum disposer des autorisations bigquery.tables.get. Les rôles IAM prédéfinis suivants incluent les autorisations bigquery.tables.get :

• bigquery.metadataViewer
• bigquery.dataViewer
• bigquery.dataOwner
• bigquery.dataEditor
• bigquery.admin

En outre, si un utilisateur possède les autorisations bigquery.datasets.create, il obtient également un accès bigquery.dataOwner à l'ensemble de données qu'il crée. L'accès bigquery.dataOwner permet à l'utilisateur d'obtenir des informations sur les tables d'un ensemble de données.

Pour en savoir plus sur les rôles et les autorisations IAM dans BigQuery, consultez la page Rôles prédéfinis et autorisations.

Obtenir des informations sur une table en cluster

Pour afficher des informations sur une table en cluster, procédez comme suit :

Console

1. Dans Google Cloud Console, accédez au volet Ressources. Cliquez sur le nom de votre ensemble de données pour le développer, puis sur le nom de la table que vous voulez afficher.

2. Cliquez sur Détails. Cette page affiche les détails de la table, y compris les colonnes de clustering.

   

SQL

Pour les tables en cluster, vous pouvez interroger la colonne CLUSTERING_ORDINAL_POSITION dans la vue INFORMATION_SCHEMA.COLUMNS pour rechercher le décalage avec un indice de 1 de la colonne dans les colonnes de clustering de la table :

1. Dans la console Google Cloud, accédez à la page BigQuery.

   Accéder à BigQuery

2. Dans l'éditeur de requête, saisissez l'instruction suivante :

   CREATE TABLE mydataset.data (column1 INT64, column2 INT64)
   CLUSTER BY column1, column2;
   SELECT
     column_name, clustering_ordinal_position
   FROM
     mydataset.INFORMATION_SCHEMA.COLUMNS;
   

3. Cliquez sur Exécuter.

Pour en savoir plus sur l'exécution des requêtes, consultez Exécuter une requête interactive.

La position ordinale de clustering est 1 pour column1 et 2 pour column2. D'autres métadonnées de table sont disponibles via les vues TABLES, TABLE_OPTIONS, COLUMNS et COLUMN_FIELD_PATH dans INFORMATION_SCHEMA.

bq

Exécutez la commande bq show pour afficher toutes les informations sur la table. L'option --schema permet de n'afficher que les informations de schéma de table. L'option --format peut être utilisée pour contrôler le résultat.

Si vous souhaitez obtenir des informations sur une table se trouvant dans un projet autre que celui par défaut, ajoutez l'ID du projet au nom de l'ensemble de données, en respectant le format suivant : project_id:dataset.

bq show \
    --schema \
    --format=prettyjson \
    PROJECT_ID:DATASET.TABLE

Remplacez les éléments suivants :

• PROJECT_ID : ID de votre projet
• DATASET : nom de l'ensemble de données
• TABLE : nom de la table

Exemples :

Saisissez la commande suivante pour afficher toutes les informations sur la table myclusteredtable de l'ensemble de données mydataset. mydataset se trouve dans votre projet par défaut.

bq show --format=prettyjson mydataset.myclusteredtable

Le résultat doit se présenter sous la forme suivante :

{
  "clustering": {
    "fields": [
      "customer_id"
    ]
  },
...
}

API

Appelez la méthode bigquery.tables.get et définissez tous les paramètres pertinents.

Répertorier les tables en cluster dans un ensemble de données

Vous pouvez répertorier des tables en cluster dans des ensembles de données de plusieurs manières :

• Utiliser la console Google Cloud
• Utiliser la commande bq ls de l'outil de ligne de commande bq
• En appelant la méthode API tables.list
• En utilisant les bibliothèques clientes
• En interrogeant la colonne CLUSTERING_ORDINAL_POSITION dans la vue INFORMATION_SCHEMA.COLUMNS

Les autorisations requises pour répertorier les tables en cluster et les étapes pour y arriver sont les mêmes que pour les tables standards. Pour découvrir comment répertorier des tables, consultez la section Répertorier les tables dans un ensemble de données.

Modifier la spécification du clustering

Vous pouvez modifier ou supprimer les spécifications de clustering d'une table, ou modifier l'ensemble des colonnes en cluster dans une table en cluster. Cette méthode de mise à jour de l'ensemble de colonnes en cluster est utile pour les tables qui utilisent des insertions en flux continu, car elles ne peuvent pas facilement être échangées par d'autres méthodes.

Suivez ces étapes pour appliquer une nouvelle spécification de clustering à des tables non partitionnées ou partitionnées.

1. Dans l'outil bq, mettez à jour la spécification de clustering de votre table pour qu'elle corresponde au nouveau clustering :

    bq update --clustering_fields=CLUSTER_COLUMN DATASET.ORIGINAL_TABLE 

   Remplacez les éléments suivants :

   • CLUSTER_COLUMN : colonne sur laquelle vous effectuez le clustering, par exemple mycolumn
   • DATASET : nom de l'ensemble de données contenant la table, par exemple mydataset
   • ORIGINAL_TABLE : nom de votre table d'origine, par exemple mytable

   Vous pouvez également appeler la méthode API tables.update ou tables.patch pour modifier la spécification du clustering.

2. Pour mettre en cluster toutes les lignes conformément à la nouvelle spécification de clustering, exécutez l'instruction UPDATE suivante :

   UPDATE DATASET.ORIGINAL_TABLE SET CLUSTER_COLUMN=CLUSTER_COLUMN WHERE true
   

   Remarque : Si une nouvelle spécification de clustering est appliquée à une table en stockage à long terme, la table reprend la tarification du stockage actif. Pour en savoir plus, consultez les tarifs de stockage.

Sécurité des tables

Pour savoir comment contrôler l'accès aux tables dans BigQuery, consultez la page Présentation des contrôles d'accès aux tables.

Étapes suivantes

• Pour en savoir plus sur l'interrogation de tables en cluster, consultez la page Interroger des tables en cluster.
• Pour une présentation de la compatibilité des tables partitionnées dans BigQuery, consultez la page Présentation des tables partitionnées.
• Pour apprendre à créer des tables partitionnées, consultez la page Créer des tables partitionnées.
• Pour obtenir une présentation de INFORMATION_SCHEMA, consultez la page Présentation de BigQuery INFORMATION_SCHEMA.