Présentation des tables partitionnées

Cette page présente la prise en charge des tables partitionnées dans BigQuery.

Une table partitionnée est une table spéciale divisée en segments, appelés partitions, qui facilitent la gestion et l'interrogation de vos données. En divisant une grande table en partitions plus petites, vous pouvez améliorer les performances des requêtes et maîtriser les coûts en réduisant le nombre d'octets lus par une requête.

Vous pouvez partitionner des tables BigQuery par :

Tables partitionnées par date d'ingestion

Lorsque vous créez une table partitionnée par date d'ingestion, BigQuery charge automatiquement les données dans des partitions quotidiennes basées sur la date, qui reflètent la date d'ingestion ou d'arrivée des données. Les identifiants de pseudo-colonne et de suffixe vous permettent de reformuler (remplacer) et de rediriger les données vers les partitions correspondant à un jour donné.

Les tables partitionnées par date d'ingestion comprennent une pseudo-colonne nommée _PARTITIONTIME et réservée à l'horodatage des données chargées dans la table. Les requêtes portant sur des tables partitionnées par date peuvent restreindre la quantité de données lues en spécifiant des filtres _PARTITIONTIME qui représentent l'emplacement d'une partition. Toutes les données de la partition spécifiée sont lues par la requête, mais le filtre de prédicat _PARTITIONTIME limite le nombre de partitions analysées.

Lorsque vous créez une table partitionnée par date l'ingestion, les partitions ont la même définition de schéma que la table. Si vous avez besoin de charger dans une partition des données dont le schéma diffère de celui de la table, vous devez mettre à jour le schéma de la table préalablement au chargement. Vous pouvez également utiliser les options de mise à jour du schéma pour modifier le schéma de la table dans le cadre d'une tâche de chargement ou de requête.

Tables partitionnées par date, horodatage ou date/heure

BigQuery accepte également les tables partitionnées basées sur une colonne DATE, TIMESTAMP ou DATETIME spécifique. Les données écrites dans une table partitionnée par date/horodatage/date et heure sont automatiquement envoyées à la partition appropriée en fonction de la valeur de l'unité de temps (exprimée en heure UTC pour TIMESTAMP) spécifiée dans la colonne de partitionnement.

Si la table est partitionnée en fonction d'une colonne DATE, vous pouvez créer des partitions avec une précision quotidienne, mensuelle ou annuelle. Chaque partition contient une plage de valeurs dont le début correspond au début d'un jour, d'un mois ou d'une année, et l'intervalle à un jour, un mois ou une année en fonction de la précision du partitionnement. Si la table est partitionnée en fonction d'une colonne TIMESTAMP ou DATETIME, vous pouvez créer des partitions avec n'importe quel type de précision d'unité de temps, y compris HOUR.

Les tables partitionnées par date/horodatage/date et heure ne nécessitent pas de pseudo-colonne _PARTITIONTIME. Les requêtes portant sur des tables partitionnées par date/horodatage/date et heure peuvent spécifier des filtres de prédicat basés sur la colonne de partitionnement afin de réduire la quantité de données analysées.

Lorsque vous créez des tables partitionnées par date/horodatage/date et heure, deux partitions spéciales sont créées :

  • La partition __NULL__, qui représente les lignes comportant la valeur NULL dans la colonne de partitionnement
  • La partition __UNPARTITIONED__, qui représente les données se trouvant en dehors de la plage de dates autorisée

À l'exception des partitions __NULL__ et __UNPARTITIONED__, toutes les données de la colonne de partitionnement correspondent à la date de l'identifiant de partition. Cela permet à une requête de déterminer quelles partitions ne contiennent pas de données satisfaisant aux conditions de filtrage. Les requêtes qui filtrent les données sur la colonne de partitionnement peuvent restreindre les valeurs et éliminer complètement les partitions inutiles.

Partitionnement par date/horodatage/date et heure et segmentation

Au lieu de partitionner les tables par date/horodatage/date et heure, vous pouvez les segmenter via une stratégie de dénomination basée sur la date de type [PREFIX]_YYYYMMDD. On parle dans ce cas de création de tables segmentées par date. En utilisant le langage SQL standard ou l'ancien SQL, vous pouvez spécifier une requête comportant un opérateur UNION afin de limiter les tables analysées par cette requête.

Les tables partitionnées par date/horodatage/date et heure offrent de meilleures performances que les tables segmentées par date. Lorsque vous créez des tables nommées par date, BigQuery doit conserver une copie du schéma et des métadonnées pour chacune de ces tables. De plus, leur utilisation peut obliger BigQuery à valider les autorisations pour chaque table interrogée. Cette pratique peut également alourdir le traitement des requêtes et affecter les performances. Il est donc recommandé d'utiliser des tables partitionnées par date/horodatage/date et heure plutôt que des tables segmentées par date.

Comparaison des options de partitionnement

Le tableau suivant compare les tables segmentées et les tables partitionnées par date/horodatage/date et heure.

Capacité Tables segmentées Tables partitionnées par date d'ingestion Tables partitionnées
Méthode de partitionnement Aucune : la segmentation des tables et leur interrogation à l'aide d'un opérateur UNION permettent de simuler le partitionnement. Partitionnement en fonction de la date d'ingestion ou d'arrivée des données. Les informations de partition peuvent être référencées à l'aide d'une pseudo-colonne. Partitionnement en fonction des données d'une colonne TIMESTAMP, DATE ou DATETIME.

Pour les tables partitionnées par heure, seules les colonnes TIMESTAMP et DATETIME sont compatibles avec le partitionnement.
Identifiants de partition Aucun Vous pouvez utiliser n'importe quelle date valide comprise entre 0001-01-01 et 9999-12-31, mais les instructions DML ne peuvent pas faire référence à des dates antérieures à 1970-01-01 ou postérieures à 2159-12-31. Une entrée valide de la colonne DATE, TIMESTAMP ou DATETIME liée. Actuellement, les valeurs de date antérieures à 1960-01-01 ou postérieures à 2159-12-31 sont placées dans une partition UNPARTITIONED partagée. Les valeurs NULL résident dans une partition NULL explicite.

Les identifiants de partitionnement doivent respecter les formats suivants :
  • yyyyMMddHH pour le partitionnement horaire.
  • yyyyMMdd pour le partitionnement quotidien.
  • yyyyMM pour le partitionnement mensuel.
  • yyyy pour le partitionnement annuel.
Réduction de la quantité de données à analyser Ne référencez que les segments dont vous avez besoin et limitez la quantité de données en excluant les colonnes inutiles de la requête. Utilisez la pseudo-colonne _PARTITIONTIME pour éliminer des partitions. Appliquez des filtres de prédicat à la colonne de partitionnement.
Nombre de partitions Le nombre de tables est illimité, mais les requêtes ne peuvent référencer que 1 000 tables. Jusqu'à 4 000 partitions Jusqu'à 4 000 partitions
Opérations de mise à jour Vous êtes limité à 1 000 mises à jour par jour. Une opération individuelle ne peut être validée que sur une seule partition : la dernière partition (par défaut), ou une partition spécifiée via un décorateur de partition tel que [TABLE]$[DATE]. Une opération individuelle peut effectuer un commit de données dans un maximum de 2 000 partitions distinctes.
Insertions en flux continu Vous pouvez diffuser des données en streaming dans n'importe quelle table segmentée, mais vous devez spécifier manuellement la segmentation. Les données sont d'abord placées dans la partition UNPARTITIONED, puis extraites à la date actuelle par défaut. À l'aide des suffixes de partition, vous pouvez également diffuser des données en streaming directement dans des partitions dans les 31 jours qui précèdent et les 16 jours qui suivent la date actuelle (heure UTC). Vous pouvez diffuser des données en streaming sur une période comprise entre les cinq années qui précèdent et l'année qui suit. Les données qui ne s'appliquent pas à cette période sont refusées. Les données comprises dans cette plage sont initialement placées dans la partition UNPARTITIONED. Lorsque les données non partitionnées sont suffisantes, BigQuery les repartitionne automatiquement.
Évaluation du fuseau horaire Défini par la sémantique utilisateur UTC UTC

Tables partitionnées par plages d'entiers

BigQuery accepte les tables partitionnées basées sur une colonne INTEGER spécifique, avec les valeurs de début, de fin et d'intervalle de votre choix. Les requêtes sur des tables partitionnées par plages d'entiers peuvent spécifier des filtres de prédicat basés sur la colonne de partitionnement afin de réduire la quantité de données analysées.

Pour créer une table partitionnée par plages d'entiers, vous devez fournir :

  • la colonne utilisée pour créer les partitions de la plage d'entiers ;
  • la valeur de début du partitionnement de la plage (incluse) ;
  • la valeur de fin du partitionnement de la plage (exclusive) ;
  • l'intervalle de chaque plage dans la partition.

Les valeurs situées en dehors de la plage de la table sont placées dans la partition UNPARTITIONED.

Par exemple, si vous créez une partition par plage d'entiers avec les valeurs suivantes :

Argument Valeur
nom de la colonne customer_id
start 0
end 100
interval 10

La table sera partitionnée dans la colonne customer_id par plages d'intervalle 10. Les valeurs 0 à 9 se trouveront dans une partition, les valeurs 10 à 19 dans une autre partition, et ainsi de suite jusqu'aux valeurs 90 à 99. Les valeurs non comprises entre 0 et 99 (telles que -1 ou 100) se trouveront dans la partition UNPARTITIONED. Les valeurs NULL se trouveront dans la partition NULL.

Lorsque vous créez des tables partitionnées par plages d'entiers, deux partitions spéciales sont créées :

  • La partition __NULL__, qui représente les lignes comportant la valeur NULL dans la colonne de partitionnement
  • La partition __UNPARTITIONED__, qui représente les données qui existent en dehors de la plage de début et d'intervalle d'entiers autorisée

À l'exception des partitions __NULL__ et __UNPARTITIONED__, toutes les données de la colonne de partitionnement correspondent à la plage de début et d'intervalle d'entiers autorisée. Cela permet à une requête de déterminer quelles partitions ne contiennent pas de données satisfaisant aux conditions de filtrage. Les requêtes qui filtrent les données sur la colonne de partitionnement peuvent restreindre les valeurs et éliminer complètement les partitions inutiles.

La limite du nombre de plages possibles entre les valeurs de début et de fin est de 10 000. Toutefois, le nombre de plages contenant des données est limité à 4 000 partitions par table, car chaque plage constitue une partition.

Partitionnement par plages d'entiers et clustering

Le partitionnement par plages d'entiers et le clustering permettent d'améliorer les performances et de réduire le coût des requêtes. Toutefois, les deux méthodes présentent des différences importantes et le choix dépend des cas d'utilisation.

Utilisez le partitionnement par plages d'entiers si vous souhaitez :

  • définir explicitement les plages utilisées pour partitionner la table. Vous indiquez comment les données seront partitionnées et quelles sont les données qui se trouvent dans chaque partition ;

  • connaître le coût des requêtes avant leur exécution. L'élimination des partitions est effectuée avant l'exécution de la requête, ce qui vous permet de connaître le coût après l'opération d'élimination au moyen d'une simulation. L'élimination du cluster est effectuée lors de l'exécution de la requête. Par conséquent, le coût n'est connu qu'une fois la requête terminée ;

  • traiter une partition, par exemple pour charger ou effacer des données dans une partition spécifique.

Utilisez le clustering si :

  • la façon dont les données seront regroupées vous importe peu du moment que vous obtenez une amélioration potentielle des performances et une réduction des coûts. BigQuery détermine automatiquement comment les données doivent être mises en cluster pour obtenir des performances et des économies de coût optimales.

  • vous avez besoin de plus de 4 000 partitions. BigQuery impose une limite de 4 000 partitions par table partitionnée. Le nombre de clusters dans une table n'est pas limité.

Notez que, pour profiter des avantages des deux méthodes, vous pouvez partitionner et regrouper les données sur la même colonne d'entiers. Les données seront d'abord partitionnées en fonction des plages d'entiers spécifiées. Dans chaque plage, si le volume de données est suffisamment important, les données seront également regroupées. Lorsque la plage est interrogée, le partitionnement définit une limite supérieure du coût de la requête en fonction de l'élimination de la partition. D'autres économies de coût sont possibles lors de l'exécution effective de la requête.

Utiliser le paramètre require_partitioning_filter

Avec le lancement du partitionnement par plages d'entiers, BigQuery accepte désormais plusieurs types de partitionnements :

  • Date d'ingestion
  • Date/Horodatage/Date et heure
  • Plage d'entiers

Afin de simplifier l'API BigQuery, nous avons supprimé le paramètre require_partitioning_filter au niveau du type de partitionnement pour le transférer au niveau de la table. Pour les besoins de rétrocompatibilité du partitionnement par date/horodatage/date et heure, le paramètre require_partitioning_filter peut toujours être spécifié au niveau de la partition. Il est également possible de le spécifier au niveau de la table. Concernant le partitionnement par plages d'entiers, vous ne pouvez spécifier require_partitioning_filter qu'au niveau de la table. L'outil de ligne de commande bq utilise déjà l'option au niveau de la table. La manière dont vous utilisez la commande bq n'est donc pas modifiée. Si vous faites appel à l'API BigQuery, vous devez choisir l'option require_partitioning_filter au niveau de la table.

Quotas et limites applicables aux tables partitionnées

Les tables partitionnées sont soumises à certaines limites dans BigQuery.

Ces quotas et limites s'appliquent également aux différents types de tâches que vous pouvez exécuter sur des tables partitionnées :

Pour plus d'informations sur tous les quotas et limites, consultez la page Quotas et limites.

Tarification des tables partitionnées

Lorsque vous créez et utilisez des tables partitionnées dans BigQuery, vos frais sont basés sur la quantité de données stockées dans les partitions et sur les requêtes que vous exécutez sur les données :

  • Pour en savoir plus sur la tarification relative au stockage, consultez la section Tarifs du stockage.
  • Pour en savoir plus sur la tarification relative aux requêtes, consultez la section Tarifs des requêtes.

De nombreuses opérations de table partitionnée sont gratuites, en particulier le chargement de données dans des partitions, la copie de partitions et l'exportation de données à partir de partitions. Bien que gratuites, ces opérations sont soumises aux quotas et limites de BigQuery. Pour en savoir plus sur toutes les opérations gratuites, consultez la section Opérations gratuites sur la page des tarifs.

Étapes suivantes