Créer et utiliser des tables partitionnées par plages d'entiers

Ce document décrit comment créer et utiliser des tables partitionnées par une colonne d'entiers. Pour plus d'informations sur les autres types de tables partitionnées, consultez la page Créer et utiliser des tables partitionnées par date et horodatage ou Créer et utiliser des tables partitionnées par date d'ingestion.

Pour plus d'informations sur les partitions par plages d'entiers, consultez Tables partitionnées par plages d'entiers.

Après avoir créé une table partitionnée par plages d'entiers, vous pouvez :

• contrôler l'accès à vos données de table ;
• obtenir des informations sur vos tables partitionnées ;
• répertorier les tables partitionnées dans un ensemble de données ;
• Obtenir des métadonnées de tables partitionnées à l'aide de métatables

Pour plus d'informations sur la gestion des tables partitionnées, telle que la mise à jour de leurs propriétés, leur copie et leur suppression, consultez la page Gérer des tables partitionnées.

Limites

Les tables partitionnées par plages d'entiers sont soumises aux limitations suivantes :

• La colonne de partitionnement doit être de type INTEGER. Le mode de la colonne peut être de type REQUIRED ou NULLABLE, mais pas REPEATED (basé sur des tableaux).
• La colonne de partitionnement doit être un champ de niveau supérieur. Vous ne pouvez pas utiliser un champ feuille d'un élément RECORD (STRUCT) comme colonne de partitionnement.
• Vous ne pouvez pas utiliser l'ancien SQL pour interroger des tables partitionnées ou pour écrire des résultats de requêtes dans des tables partitionnées.

Créer des tables partitionnées

Vous pouvez créer une table partitionnée par durée d'ingestion dans BigQuery de l'une des manières suivantes :

• Utiliser Cloud Console
• À l'aide d'une instruction LDD CREATE TABLE avec une clause PARTITION BY RANGE_BUCKET contenant une expression partition expression
• En utilisant la commande bq mk de l'outil de ligne de commande bq
• De façon automatisée, en appelant la méthode API tables.insert
• À partir des résultats de requêtes
• Lorsque vous chargez des données

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 jusqu'à 1 024 caractères ;
• 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.

Par exemple, tous les noms de table suivants sont valides : table-01, ग्राहक, 00_お客様, étudiant.

Autorisations requises

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

• Autorisations bigquery.tables.create pour créer la table
• Autorisations bigquery.tables.updateData pour écrire des données dans la table à l'aide d'une tâche de chargement, de requête ou de copie
• Autorisations bigquery.jobs.create pour exécuter une tâche de requête, de chargement ou de copie qui écrit des données dans la table

Des autorisations supplémentaires, par exemple bigquery.tables.getData, peuvent être nécessaires pour accéder aux données que vous écrivez dans la table.

Les rôles IAM prédéfinis suivants incluent les autorisations bigquery.tables.create et bigquery.tables.updateData :

• bigquery.dataEditor
• bigquery.dataOwner
• bigquery.admin

Les rôles IAM prédéfinis suivants incluent les autorisations bigquery.jobs.create :

• bigquery.user
• bigquery.jobUser
• 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 correspondant au rôle bigquery.dataOwner donne à l'utilisateur la possibilité de créer et de mettre à jour des tables dans l'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.

Créer une table partitionnée vide, avec une définition de schéma

Vous ne pouvez pas créer une table partitionnée vide sans définition de schéma. Le schéma est requis pour identifier la colonne utilisée afin de créer les partitions.

Lorsque vous créez une table partitionnée vide avec une définition de schéma, vous pouvez :

• fournir le schéma en ligne avec l'outil de ligne de commande bq ;
• spécifier un fichier de schéma JSON avec l'outil de ligne de commande bq ;
• fournir le schéma dans une ressource de table lorsque vous appelez la méthode API tables.insert.

Pour en savoir plus sur la spécification d'un schéma de table, consultez la page Spécifier un schéma.

Une fois la table partitionnée créée, vous pouvez y :

• charger des données ;
• écrire les résultats de requêtes ;
• copier des données.

Pour créer une table partitionnée vide avec une définition de schéma :

Console

1. Ouvrez la page BigQuery dans Cloud Console.

   Accéder à BigQuery

2. Dans la section Ressources du panneau de navigation, développez votre projet et sélectionnez un ensemble de données.

3. À droite de la fenêtre, dans le panneau de détails, cliquez sur Créer une table.

4. Dans la section Source du panneau Créer une table :

   • Pour Créer une table à partir de, sélectionnez Table vide.

5. Dans la section Destination :

   • Pour le champ Nom de l'ensemble de données, sélectionnez l'ensemble de données approprié, puis saisissez le nom de la table que vous créez dans le champ Nom de la table.
   • Vérifiez que le champ Table type (Type de table) est défini sur Native table (Table native).

6. Dans la section Schema (Schéma), saisissez la définition du schéma.

   • Indiquez manuellement les informations de schéma de l'une des manières suivantes :

     • Activez l'option Modifier sous forme de texte et saisissez le schéma de la table sous forme de tableau JSON.

       Remarque : Vous pouvez afficher le schéma d'une table existante au format JSON en saisissant la commande suivante dans l'outil de ligne de commande bq : bq show --format=prettyjson dataset.table.

     • Utilisez l'option Ajouter un champ pour saisir manuellement le schéma.

7. Sous Partition and cluster settings (Paramètres de partitionnement et de clustering), dans la liste déroulante Partition, choisissez une colonne de type INTEGER.

8. Indiquez des valeurs pour start, end et interval :

   • start est la valeur de début inclusive du partitionnement par plages.
   • end est la valeur de fin exclusive du partitionnement par plages.
   • interval est la largeur de chaque plage au sein de la partition.

9. (Facultatif) Pour le champ Filtre de partitionnement, cochez la case Demander un filtre de partitionnement pour obliger les utilisateurs à inclure une clause WHERE spécifiant les partitions à interroger. Ce type de filtre peut contribuer à réduire les coûts et à améliorer les performances. Pour en savoir plus, consultez la section Interroger des tables partitionnées.

10. (Facultatif) Cliquez sur Options avancées et, pour le champ Chiffrement, cliquez sur Clé gérée par le client pour utiliser une clé Cloud Key Management Service. Si vous conservez le paramètre Clé gérée par Google, BigQuery chiffre les données au repos.

11. Cliquez sur Create table.

Remarque : Lorsque vous créez une table partitionnée vide à l'aide de Cloud Console, vous ne pouvez pas ajouter de libellé, de description, de délai d'expiration de table ou de délai d'expiration de partition. Vous pouvez ajouter ces propriétés facultatives lorsque vous créez une table à l'aide de l'API ou de l'outil de ligne de commande bq.

Une fois la table créée, vous pouvez mettre à jour son délai d'expiration, sa description et ses libellés. Vous ne pouvez pas ajouter de délai d'expiration de partition après la création d'une table à l'aide de Cloud Console.

SQL

Les instructions LDD (langage de définition de données) vous permettent de créer et de modifier des tables et des vues à l'aide de la syntaxe de requête SQL standard.

En savoir plus sur l'utilisation des instructions de langage de définition de données

Pour créer une table partitionnée à l'aide d'une instruction LDD dans Cloud Console :

1. Ouvrez la page BigQuery dans Cloud Console.

   Accéder à BigQuery

2. Cliquez sur Saisir une nouvelle requête.

3. Saisissez votre instruction LDD CREATE TABLE dans la zone de texte de l'éditeur de requête.

   La requête suivante crée une table nommée newtable avec une partition par plages d'entiers dans la colonne customer_id commençant par 0 et finissant par 100, avec un intervalle de 10.

    CREATE TABLE
      mydataset.newtable
    PARTITION BY
      RANGE_BUCKET(customer_id, GENERATE_ARRAY(0, 100, 10))
    AS SELECT 1 AS customer_id, DATE "2019-10-01" AS date1
    

4. Cliquez sur Exécuter. Lorsque la requête est terminée, la table apparaît dans le volet Ressources.

bq

Exécutez la commande mk avec l'option --range_partitioning :

bq mk \
--range_partitioning=column_name,start,end,interval \
project_id:dataset.table \
"column_name:integer,value:integer"

Où :

• column_name est la colonne utilisée pour créer les partitions de la plage d'entiers.
• start est la valeur de début inclusive du partitionnement par plages.
• end est la valeur de fin exclusive du partitionnement par plages.
• interval est la largeur de chaque plage au sein de la partition.
• project_id est l'ID de votre projet.
• dataset est un ensemble de données dans votre projet.
• table est le nom de la table partitionnée que vous créez.

Par exemple :

Saisissez la commande suivante pour créer une table partitionnée par plages d'entiers nommée mypartitionedtable dans mydataset, au sein de votre projet par défaut. Le modèle de partitionnement commence par 0 et finit par 100, avec un intervalle de 10.

L'option --require_partition_filter oblige les utilisateurs à inclure une clause WHERE spécifiant les partitions à interroger. Ce type de filtre peut contribuer à réduire les coûts et à améliorer les performances. Pour en savoir plus, consultez la page Interroger des tables partitionnées.

bq mk \
--require_partition_filter \
--range_partitioning=customer_id,0,100,10 \
mydataset.mypartitionedtable \
"customer_id:integer,value:integer"

Une fois la table créée, vous pouvez utiliser l'outil de ligne de commande bq pour mettre à jour le délai d'expiration de table, le délai d'expiration de partition, la description et les libellés de la table.

API

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

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.

Afficher sur GitHub Commentaires

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.RangePartitioning;
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;

// Sample to create a range partitioned table
public class CreateRangePartitionedTable {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    Schema schema =
        Schema.of(
            Field.of("integerField", StandardSQLTypeName.INT64),
            Field.of("stringField", StandardSQLTypeName.STRING),
            Field.of("booleanField", StandardSQLTypeName.BOOL),
            Field.of("dateField", StandardSQLTypeName.DATE));
    createRangePartitionedTable(datasetName, tableName, schema);
  }

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

      // Note: The field must be a top- level, NULLABLE/REQUIRED field.
      // The only supported type is INTEGER/INT64
      RangePartitioning partitioning =
          RangePartitioning.newBuilder()
              .setField("integerField")
              .setRange(
                  RangePartitioning.Range.newBuilder()
                      .setStart(1L)
                      .setInterval(2L)
                      .setEnd(10L)
                      .build())
              .build();

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

      bigquery.create(tableInfo);
      System.out.println("Range partitioned table created successfully");
    } catch (BigQueryException e) {
      System.out.println("Range partitioned table was not created. \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.

Afficher sur GitHub Commentaires

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

async function createTableRangePartitioned() {
  // Creates a new integer range partitioned table named "my_table"
  // in "my_dataset".

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = "my_dataset";
  // const tableId = "my_table";

  const schema = [
    {name: 'fullName', type: 'STRING'},
    {name: 'city', type: 'STRING'},
    {name: 'zipcode', type: 'INTEGER'},
  ];

  // To use integer range partitioning, select a top-level REQUIRED or
  // NULLABLE column with INTEGER / INT64 data type. Values that are
  // outside of the range of the table will go into the UNPARTITIONED
  // partition. Null values will be in the NULL partition.
  const rangePartition = {
    field: 'zipcode',
    range: {
      start: 0,
      end: 100000,
      interval: 10,
    },
  };

  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
  const options = {
    schema: schema,
    rangePartitioning: rangePartition,
  };

  // Create a new table in the dataset
  const [table] = await bigquery
    .dataset(datasetId)
    .createTable(tableId, options);

  console.log(`Table ${table.id} created with integer range partitioning: `);
  console.log(table.metadata.rangePartitioning);
}

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.

Afficher sur GitHub Commentaires

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.range_partitioning = bigquery.RangePartitioning(
    # To use integer range partitioning, select a top-level REQUIRED /
    # NULLABLE column with INTEGER / INT64 data type.
    field="zipcode",
    range_=bigquery.PartitionRange(start=0, end=100000, interval=10),
)
table = client.create_table(table)  # Make an API request.
print(
    "Created table {}.{}.{}".format(table.project, table.dataset_id, table.table_id)
)

Pour vérifier qu'une table est partitionnée dans une colonne d'entiers, examinez le schéma. Par exemple, exécutez la commande suivante :

bq show --format=prettyjson mydataset.mytable

Si une colonne d'entiers est partitionnée, la sortie de bq show contient des données rangePartitioning :

...
  "rangePartitioning": {
    "field": "customer_id",
    "range": {
      "end": "100",
      "interval": "10",
      "start": "0"
    }
  },
...

Écrire dans une table partitionnée par plages d'entiers

Les données écrites dans une table partitionnée par plages d'entiers sont automatiquement partitionnées. Cela inclut l'écriture dans la table via des tâches de chargement, des requêtes et des flux de données.

En cas d'écriture en flux, les données du tampon se trouvent dans la partition UNPARTITIONED. Une fois extraites, les données restent dans la partition UNPARTITIONED. Lorsque les données non partitionnées sont en quantité suffisante, elles sont à nouveau partitionnées dans des partitions spécifiques.

Voici un exemple permettant d'enregistrer les résultats de la requête dans une table partitionnée par plages d'entiers :

bq query --nouse_legacy_sql \
--destination_table=mydataset.mytable \
'SELECT value AS customer_id, value+1 AS value FROM UNNEST(GENERATE_ARRAY(-5, 110, 5)) AS value'

Interroger une table partitionnée par plages d'entiers

La table partitionnée par plages d'entiers ne peut être interrogée que via le langage SQL standard. Lorsqu'une table partitionnée par plages d'entiers est interrogée, et que des filtres sont appliqués à la colonne de partitionnement des entiers, ces filtres permettent de restreindre les partitions et de réduire le coût des requêtes.

La requête suivante porte sur les trois partitions qui commencent par 30, 40 et 50.

bq query --nouse_legacy_sql \
'SELECT * FROM mydataset.mytable WHERE customer_id BETWEEN 30 AND 50'

Voici un exemple de résultat de la commande bq query :

Waiting on bqjob_r4fce65fa3381528e_000001670994aeb6_1 ... (0s) Current status: DONE
+---------+-------+
| customer_id | value |
+---------+-------+
|      40 |    41 |
|      45 |    46 |
|      30 |    31 |
|      35 |    36 |
|      50 |    51 |
+---------+-------+

Dans cet exemple, chaque partition comporte deux lignes et chaque ligne deux colonnes d'entiers. Par conséquent, la requête doit traiter 3 * 2 * 2 * 8 = 96 octets. Vous pouvez examiner les informations ci-dessous sur la tâche :

bq show -j bqjob_r4fce65fa3381528e_000001670994aeb6_1

Voici un exemple de résultat de la commande bq show :

Job myproject:bqjob_r4fce65fa3381528e_000001670994aeb6_1

  Job Type    State      Start Time      Duration       User Email        Bytes Processed   Bytes Billed   Billing Tier   Labels
 ---------- --------- ----------------- ---------- --------------------- ----------------- -------------- -------------- --------
  query      SUCCESS   24 Sep 12:19:58   0:00:01    [e-mail]       96                10485760       1

Les instructions LMD sont acceptées. Exemple :

bq query --nouse_legacy_sql \
'DELETE FROM mydataset.mytable WHERE customer_id = 30'

Actuellement, la restriction de partitions n'est pas compatible avec les fonctions sur une colonne partitionnée par plages d'entiers. Par exemple, la requête suivante porte sur l'ensemble de la table.

bq query --nouse_legacy_sql \
'SELECT * FROM mydataset.mytable WHERE customer_id+1 BETWEEN 30 AND 50'

Décorateurs de table dans les tables partitionnées par plages d'entiers

À l'instar du partitionnement par date et par horodatage, des décorateurs de table peuvent être utilisés pour circonscrire une partition dans une table partitionnée par plages d'entiers. La clé permettant de circonscrire une partition par plages est le début de la plage.

L'exemple suivant interroge la partition par plages commençant par 0. Deux valeurs sont incluses : 0 et 5.

bq query 'SELECT * FROM mydataset.mytable$0'

+---------+-------+
| customer_id | value |
+---------+-------+
|       0 |     1 |
|       5 |     6 |
+---------+-------+

Partitionnement par plages d'entiers avec clustering

Vous pouvez utiliser le partitionnement par plages d'entiers avec clustering. Les données sont d'abord partitionnées dans la colonne partitionnée par plages d'entiers. Ensuite, les données de chaque partition sont regroupées dans les colonnes du clustering.

Par exemple, cette commande crée une table avec une colonne de plages d'entiers et un cluster.

bq mk \
--range_partitioning=customer_id,0,100,10 \
--clustering_fields=value \
mydataset.mytable_2 \
"customer_id:integer,value:integer"

Lorsque vous récupérez le format de la table, vous constatez que le partitionnement et le clustering de la plage sont actifs :

...
  "clustering": {
    "fields": [
      "value"
    ]
  },
...
  "rangePartitioning": {
    "field": "customer_id",
    "range": {
      "end": "100",
      "interval": "10",
      "start": "0"
    }
  },
...

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

Pour créer une table partitionnée à partir d'un résultat de requête, procédez comme suit :

Console

Vous ne pouvez pas spécifier d'options de partitionnement pour une table de destination lorsque vous interrogez des données à l'aide de Cloud Console.

bq

Saisissez la commande bq query, puis spécifiez l'option --destination_table pour créer une table permanente basée sur les résultats de la requête et l'option --range_partitioning pour créer une table de destination partitionnée.

Spécifiez l'option use_legacy_sql=false pour utiliser la syntaxe SQL standard. Pour écrire les résultats de la requête dans une table qui ne se trouve pas dans 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.

(Facultatif) Spécifiez l'option --location et définissez la valeur correspondant à votre emplacement.

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

bq --location=location query \
--destination_table project_id:dataset.table \
--range_partitioning column,start,end,interval \
--use_legacy_sql=false \
'query'

Où :

• location est le nom du site. 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 en utilisant le fichier .bigqueryrc.
• project_id est l'ID de votre projet.
• dataset est le nom de l'ensemble de données qui contiendra la nouvelle table partitionnée.
• table est le nom de la table partitionnée que vous créez en utilisant les résultats de la requête.
• column est la colonne INTEGER à utiliser pour la partition.
• start est la valeur de début inclusive du partitionnement par plages.
• end est la valeur de fin exclusive du partitionnement par plages.
• interval est la largeur de chaque plage au sein de la partition.
• query est une requête en syntaxe SQL standard. Actuellement, vous ne pouvez pas utiliser l'ancien SQL pour interroger des tables partitionnées ou pour écrire des résultats de requêtes dans des tables partitionnées.

Exemple :

Remarque : Cet exemple interroge un ensemble de données public hébergé aux États-Unis. Comme cet ensemble de données est stocké dans l'emplacement multirégional américain, l'ensemble de données contenant votre table de destination doit également se trouver aux États-Unis. Vous ne pouvez pas interroger un ensemble de données dans un emplacement déterminé et écrire les résultats dans une table de destination située dans un autre emplacement.

Saisissez la commande suivante pour écrire les résultats de la requête dans une table de destination partitionnée nommée mypartitionedtable dans mydataset. mydataset se trouve dans votre projet par défaut. Cette requête extrait les données d'une table non partitionnée, l'ensemble de données public Medicare. La colonne INTEGER provider_id de la table permet de créer les partitions.

bq query \
--destination_table mydataset.mypartitionedtable \
--use_legacy_sql=false \
--range_partitioning provider_id,10000,700000,10000 \
'SELECT
   provider_id, total_discharges, average_covered_charges
 FROM
   `bigquery-public-data`.medicare.inpatient_charges_2011
 LIMIT
   300'

API

Pour enregistrer les résultats de requête dans une table partitionnée permanente, appelez la méthode jobs.insert, configurez une tâche de requête (query) et ajoutez une valeur pour les propriétés destinationTable et rangePartitioning.

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

Créer une table partitionnée lorsque vous chargez des données

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

Lorsque vous chargez des données dans BigQuery, vous pouvez fournir le schéma de la table ou utiliser la détection automatique des schémas pour les formats de données compatibles.

Les décorateurs de partition vous permettent de charger des données dans une partition spécifique. Par exemple, considérons une table partitionnée par plages d'entiers dans la colonne customer_id, commençant par 0, finissant par 100 et avec un intervalle de 10. Pour charger toutes les données des numéros client compris entre 0 et 9, utilisez le décorateur de partition $0 suivant :

table_name$0

Lors du chargement de données dans une partition spécifique avec un décorateur de partition, les données en cours de chargement dans la partition doivent être conformes au schéma de partitionnement de la table. Toutes les lignes écrites sur la partition doivent avoir des valeurs comprises dans la date de la partition.

Pour en savoir plus sur le chargement des données, consultez la page intitulée Présentation du chargement de données dans BigQuery.

Contrôler l'accès aux tables partitionnées

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

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 celui au niveau de la table de 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 les pages suivantes :

• Comprendre les rôles dans la documentation IAM
• Rôles et autorisations BigQuery prédéfinis
• Contrôler l'accès aux ensembles de données
• Contrôler l'accès aux tables et aux vues
• Restreindre l'accès avec la sécurité au niveau des colonnes de BigQuery

Utiliser des tables partitionnées

Obtenir des informations sur les tables partitionnées

Vous pouvez obtenir des informations sur les tables à l'aide des opérations suivantes :

• Utiliser Cloud Console
• En utilisant la commande bq show de l'outil de ligne de commande bq
• En appelant la méthode API tables.get
• En utilisant les bibliothèques clientes

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 de récupérer les métadonnées d'une table.

Pour en savoir plus sur les rôles et les autorisations IAM dans BigQuery, consultez la page Contrôle des accès.

Obtenir des informations sur la table partitionnée

Pour afficher des informations sur une table partitionnée :

Console

1. Ouvrez la page BigQuery dans Cloud Console.

   Accéder à BigQuery

2. Dans la section Resources (Ressources) du panneau de navigation, développez votre projet et sélectionnez un ensemble de données pour répertorier les tables et les vues qu'il contient.

3. Cliquez sur Details (Détails) sous l'éditeur de requête. Cet onglet affiche la description de la table et ses informations.

   

4. Cliquez sur l'onglet Schema (Schéma) pour afficher la définition du schéma de la table. Notez que les tables partitionnées n'incluent pas la pseudo-colonne _PARTITIONTIME.

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

Où :

• project_id est l'ID de votre projet.
• dataset est le nom de l'ensemble de données.
• table est le nom de la table.

Exemples :

Saisissez la commande suivante pour afficher toutes les informations sur la table mytable dans mydataset. mydataset se trouve dans votre projet par défaut.

bq show --format=prettyjson mydataset.mytable

Saisissez la commande suivante pour afficher toutes les informations sur la table mytable dans mydataset. mydataset se trouve dans myotherproject, et non dans votre projet par défaut.

bq show --format=prettyjson myotherproject:mydataset.mytable

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

{
  "creationTime": "1569429717657",
  "etag": "AcSEmWFQdbYEGT0auTE9NA==",
  "id": "myproject:mydataset.newtable",
  "kind": "bigquery#table",
  "lastModifiedTime": "1569429717657",
  "location": "US",
  "numBytes": "16",
  "numLongTermBytes": "0",
  "numRows": "1",
  "rangePartitioning": {
    "field": "customer_id",
    "range": {
      "end": "100",
      "interval": "10",
      "start": "0"
    }
  },
  "schema": {
    "fields": [
      {
        "name": "customer_id",
        "type": "INTEGER"
      },
      {
        "name": "date1",
        "type": "DATE"
      }
    ]
  },
  "selfLink": "https://bigquery.googleapis.com/bigquery/v2/projects/myproject/datasets/mydataset/tables/newtable",
  "tableReference": {
    "datasetId": "mydataset,
    "projectId": "myproject",
    "tableId": "newtable"
  },
  "type": "TABLE"
}

API

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

Répertorier les tables partitionnées dans un ensemble de données

Vous pouvez répertorier des tables dans des ensembles de données (y compris des tables partitionnées) à l'aide de l'une des méthodes suivantes :

• Utiliser Cloud Console
• En utilisant 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

Autorisations requises

Pour répertorier les tables d'un ensemble de données, vous devez au minimum disposer des autorisations bigquery.tables.list. Les rôles IAM prédéfinis suivants incluent les autorisations bigquery.tables.list :

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

Pour en savoir plus sur les rôles et les autorisations IAM dans BigQuery, consultez la page Contrôle des accès.

Répertorier des tables partitionnées

Pour répertorier les tables d'un ensemble de données (y compris les tables partitionnées) :

Console

1. Ouvrez la page BigQuery dans Cloud Console.

   Accéder à BigQuery

2. Dans la section Ressources du panneau de navigation, développez votre projet et sélectionnez votre ensemble de données.

3. Faites défiler la liste pour voir les tables de l'ensemble de données. Les tables, les tables partitionnées, les modèles et les vues sont identifiés par des icônes différentes.

API

Pour répertorier les tables à l'aide de l'API, appelez la méthode tables.list.

Répertorier des partitions dans des tables partitionnées

Vous pouvez répertorier les partitions dans une table partitionnée en interrogeant la métatable __PARTITIONS_SUMMARY__ en ancien SQL.

Vous pouvez exécuter la requête à l'aide de Cloud Console ou de la commande bq query dans l'outil de ligne de commande bq, ou en appelant la méthode jobs.insert et en configurant une tâche query.

Autorisations requises

Pour exécuter une tâche de requête qui utilise la métatable __PARTITIONS_SUMMARY__, vous devez au minimum disposer des autorisations bigquery.jobs.create. Les rôles IAM prédéfinis suivants incluent les autorisations bigquery.jobs.create :

• bigquery.user
• bigquery.jobUser
• bigquery.admin

Vous devez également disposer des autorisations bigquery.tables.getData. Les rôles IAM prédéfinis suivants incluent les autorisations bigquery.tables.getData :

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

Pour en savoir plus sur les rôles IAM dans BigQuery, consultez la page Contrôle des accès.

Répertorier les partitions dans une table partitionnée

Vous pouvez répertorier des partitions dans une table partitionnée avec l'ancien SQL. Pour répertorier des partitions dans une table partitionnée :

Console

1. Ouvrez la page BigQuery dans Cloud Console.

   Accéder à BigQuery

2. Cliquez sur le bouton Saisir une nouvelle requête.

3. Saisissez le texte suivant dans la zone de l'éditeur de requête pour interroger la métatable __PARTITIONS_SUMMARY__ :

   #legacySQL
   SELECT
     partition_id
   FROM
     [dataset.table$__PARTITIONS_SUMMARY__]
   

   Où :

   • dataset est l'ensemble de données qui contient la table.
   • table est le nom de la table.

4. Cliquez sur Run (Exécuter).

bq

Saisissez la requête suivante à l'aide de la commande bq query :

bq --location=location query \
--use_legacy_sql=true \
'SELECT
  partition_id
FROM
  [dataset.table$__PARTITIONS_SUMMARY__]'

Où :

• location est le nom du site. 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 correspondant à l'emplacement en utilisant le fichier .bigqueryrc.
• dataset est l'ensemble de données qui contient la table.
• table est le nom de la table.

API

Appelez la méthode jobs.insert, puis configurez une tâche de requête (query) qui interroge la métatable __PARTITIONS_SUMMARY__ de la table.

Obtenir des métadonnées de tables à l'aide de métatables

Vous pouvez obtenir des informations concernant les tables partitionnées en utilisant des tables spéciales appelées métatables. Les métatables contiennent des métadonnées telles que la liste des tables et des vues d'un ensemble de données. Les métatables sont en lecture seule.

Actuellement, le service INFORMATION_SCHEMA ne permet pas d'obtenir des métadonnées de tables partitionnées.

Obtenir des métadonnées de partitions avec des métatables

La métatable __PARTITIONS_SUMMARY__ est une table spéciale dont le contenu représente des métadonnées relatives aux partitions d'une table partitionnée par date. La métatable __PARTITIONS_SUMMARY__ est en lecture seule.

Pour accéder aux métadonnées contenues dans les partitions d'une table partitionnée par date, utilisez la métatable __PARTITIONS_SUMMARY__ dans une instruction de requête SELECT. Vous pouvez exécuter la requête :

• Utiliser Cloud Console
• En exécutant la commande bq query de l'outil de ligne de commande bq
• En appelant la méthode API jobs.insert et en configurant une tâche de requête (query)
• En utilisant les bibliothèques clientes

Actuellement, le SQL standard n'est pas compatible avec le séparateur de décorateur de partition ($). Vous ne pouvez donc pas interroger __PARTITIONS_SUMMARY__ en SQL standard. Une requête en ancien SQL utilisant la métatable __PARTITIONS_SUMMARY__ ressemble à ce qui suit :

#legacySQL
SELECT
  column
FROM
  [dataset.table$__PARTITIONS_SUMMARY__]

Où :

• dataset est le nom de votre ensemble de données.
• table est le nom de la table partitionnée par date.
• column est l'un des éléments suivants :

Valeur	Description
project_id	Nom du projet.
dataset_id	Nom de l'ensemble de données.
table_id	Nom de la table partitionnée par date.
partition_id	Nom (date) de la partition.
creation_time	Heure à laquelle la partition a été créée, exprimée en millisecondes depuis le 1er janvier 1970 UTC.
last_modified_time	Heure à laquelle la partition a été modifiée pour la dernière fois, exprimée en millisecondes depuis le 1er janvier 1970 UTC.

Autorisations de partitionnement de métatables

Pour exécuter une tâche de requête qui utilise la métatable __PARTITIONS_SUMMARY__, vous devez au minimum disposer des autorisations bigquery.jobs.create. Les rôles IAM prédéfinis suivants incluent les autorisations bigquery.jobs.create :

• bigquery.user
• bigquery.jobUser
• bigquery.admin

Vous devez également disposer des autorisations bigquery.tables.getData. Les rôles IAM prédéfinis suivants incluent les autorisations bigquery.tables.getData :

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

Pour en savoir plus sur les rôles IAM dans BigQuery, consultez la page Contrôle des accès.

Exemples de partitionnement de métatables

La requête suivante récupère toutes les métadonnées de partition pour une table partitionnée par plages nommée mydataset.mytable.

Console

#legacySQL
SELECT
  *
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]

bq

bq query --use_legacy_sql=true '
SELECT
  *
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]'

La sortie ressemble à ceci :

+----------------+------------+----------------+--------------+---------------+--------------------+
|   project_id   | dataset_id |    table_id    | partition_id | creation_time | last_modified_time |
+----------------+------------+----------------+--------------+---------------+--------------------+
| myproject      | mydataset  | mytable        | 10000        | 1517190224120 | 1517190224997      |
| myproject      | mydataset  | mytable        | 20000        | 1517190224120 | 1517190224997      |
+----------------+------------+----------------+--------------+---------------+--------------------+

La requête suivante répertorie les heures auxquelles les partitions de mydataset.mytable ont été modifiées pour la dernière fois.

Console

#legacySQL
SELECT
  partition_id,
  last_modified_time
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]

bq

bq query --use_legacy_sql=true '
SELECT
  partition_id,
  last_modified_time
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]'

La sortie ressemble à ceci :

+--------------+--------------------+
| partition_id | last_modified_time |
+--------------+--------------------+
| 100000       |      1471632556179 |
| 20000        |      1471632538142 |
| 30000        |      1471632570463 |
+--------------+--------------------+

Pour afficher le champ last_modified_time dans un format plus lisible, utilisez la fonction FORMAT_UTC_USEC. Exemple :

Console

#legacySQL
SELECT
  partition_id,
  FORMAT_UTC_USEC(last_modified_time*1000) AS last_modified
FROM
  [mydataset.table1$__PARTITIONS_SUMMARY__]

bq

bq query --use_legacy_sql=true '
SELECT
  partition_id,
  FORMAT_UTC_USEC(last_modified_time*1000) AS last_modified
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]'

La sortie ressemble à ceci :

+--------------+----------------------------+
| partition_id |       last_modified        |
+--------------+----------------------------+
| 10000        | 2016-08-19 18:49:30.463000 |
| 20000        | 2016-08-19 18:49:16.179000 |
| 30000        | 2016-08-19 18:48:58.142000 |
+--------------+----------------------------+

Étapes suivantes

• 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 et utiliser des tables partitionnées par date/horodatage, consultez Créer et utiliser des tables partitionnées par date et horodatage.
• Pour découvrir comment créer et utiliser des tables partitionnées par temps d'ingestion, consultez la page Créer et utiliser des tables partitionnées par temps d'ingestion.
• Pour savoir comment gérer et mettre à jour des tables partitionnées, consultez la page Gérer des tables partitionnées.
• Pour en savoir plus sur l'interrogation des tables partitionnées, consultez la page Interroger des tables partitionnées.