Présentation des tables partitionnées

Cette page présente les 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 :

  • Colonnes d'unités de temps : les tables sont partitionnées en fonction d'une colonne TIMESTAMP, DATE ou DATETIME dans la table.

  • Date d'ingestion : les tables sont partitionnées en fonction de l'horodatage lorsque BigQuery ingère les données.

  • Plage d'entiers : les tables sont partitionnées en fonction d'une colonne de nombres entiers.

Si une requête applique un filtrage basé sur la valeur de la colonne de partitionnement, BigQuery peut analyser les partitions correspondant au filtre et ignorer les partitions restantes. Ce processus est appelé élimination.

Partitionnement par colonnes d'unités de temps

Vous pouvez partitionner une table sur une colonne DATE, TIMESTAMP ou DATETIME de la table. Lorsque vous écrivez des données dans la table, BigQuery les enregistre automatiquement dans la partition appropriée en fonction des valeurs dans la colonne.

Pour les colonnes TIMESTAMP et DATETIME, les partitions peuvent avoir une précision horaire, quotidienne, mensuelle ou annuelle. Pour les colonnes DATE, les partitions peuvent avoir une précision quotidienne, mensuelle ou annuelle. Les limites des partitions sont basées sur l'heure UTC.

Par exemple, supposons que vous partitionnez une table sur une colonne DATETIME avec partitionnement mensuel. Si vous insérez les valeurs suivantes dans la table, les lignes seront écrites dans les partitions suivantes :

Valeur de la colonne Partition (par mois)
DATETIME("2019-01-01") 201901
DATETIME("2019-01-15") 201901
DATETIME("2019-04-30") 201904

En outre, deux partitions spéciales sont créées :

  • __NULL__ : contient les lignes avec des valeurs NULL dans la colonne de partitionnement.
  • __UNPARTITIONED__ : contient les lignes pour lesquelles la valeur de la colonne de partitionnement est antérieure au 01-01-1960 ou ultérieure au 31-12-2159.

Partitionnement par date d'ingestion

Lorsque vous créez une table partitionnée par date d'ingestion, BigQuery attribue automatiquement les lignes aux partitions en fonction de la date d'ingestion des données par BigQuery. Vous pouvez définir une précision horaire, quotidienne, mensuelle ou annuelle pour les partitions. Les limites des partitions sont basées sur l'heure UTC.

Une table partitionnée par date d'ingestion comprend une pseudo-colonne nommée _PARTITIONTIME. La valeur de cette colonne correspond à la date d'ingestion de chaque ligne, tronquée à la limite de la partition (par exemple, à l'heure ou au jour près). Par exemple, supposons que vous créez une table partitionnée par date d'ingestion avec un partitionnement horaire et que vous envoyez des données aux heures suivantes :

Date d'ingestion _PARTITIONTIME Partition (par heure)
2021-05-07 17:22:00 07-05-2021 17:00:00 2021050717
2021-05-07 17:40:00 07-05-2021 17:00:00 2021050717
2021-05-07 18:31:00 07-05-2021 18:00:00 2021050718

Comme la table de cet exemple utilise le partitionnement horaire, la valeur de _PARTITIONTIME est tronquée à l'heure près. BigQuery utilise cette valeur pour déterminer la partition appropriée pour les données.

Vous pouvez également écrire des données dans une partition spécifique. Par exemple, vous pouvez charger des données de l'historique ou ajuster les fuseaux horaires. Vous pouvez utiliser n'importe quelle date valide comprise entre le 01-01-0001 et le 31-12-9999. Toutefois, les instructions LMD ne peuvent pas faire référence à des dates antérieures au 01-01-1970 ou postérieures à 31-12-2159. Pour en savoir plus, consultez la section Écrire des données dans une partition spécifique.

Au lieu d'utiliser _PARTITIONTIME, vous pouvez également utiliser _PARTITIONDATE. La pseudo-colonne _PARTITIONDATE contient la date UTC correspondant à la valeur de la pseudo-colonne _PARTITIONTIME.

Partitionnement par plages d'entiers

Vous pouvez partitionner une table en fonction de plages de valeurs dans une colonne INTEGER spécifique. Pour créer une table partitionnée par plages d'entiers, vous devez fournir :

  • La colonne de partitionnement.
  • La valeur de début pour le partitionnement de la plage (inclusive).
  • La valeur de fin pour le partitionnement de la plage (exclusive).
  • L'intervalle de chaque plage dans la partition.

Par exemple, supposons que vous créez une partition par plages d'entiers avec la spécification suivante :

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

La table est partitionnée en fonction de la colonne customer_id par plages d'intervalle de 10. Les valeurs 0 à 9 sont placées dans une partition, les valeurs 10 à 19 sont placées dans la partition suivante, et ainsi de suite jusqu'à 99. Les valeurs situées en dehors de cette plage sont placées dans une partition nommée __UNPARTITIONED__. Les lignes dans lesquelles customer_id est défini sur NULL sont incluses dans une partition nommée __NULL__.

Choisir le partitionnement quotidien, horaire, mensuel ou annuel.

Lorsque vous partitionnez une table par colonnes d'unités de temps ou par date d'ingestion, vous pouvez choisir le type de partitionnement : quotidien, horaire, mensuel ou annuel.

  • Le partitionnement quotidien est le type de partitionnement par défaut. Le partitionnement quotidien est un bon choix lorsque vos données sont réparties sur une grande plage de dates ou si des données sont ajoutées en permanence au fil du temps.

  • Choisissez le partitionnement horaire si vos tables contiennent un volume élevé de données couvrant une courte période (généralement moins de six mois de valeurs d'horodatage). Si vous choisissez le partitionnement horaire, assurez-vous que le nombre de partitions reste dans les limites de partition.

  • Choisissez le partitionnement mensuel ou annuel si vos tables contiennent une quantité de données relativement faible pour chaque jour mais s'appliquent à une grande plage de dates. Cette option de partitionnement est également recommandée si votre workflow nécessite de mettre à jour ou d'ajouter fréquemment des lignes qui couvrent une grande plage de dates (par exemple, plus de 500 dates). Dans de tels scénarios, utilisez le partitionnement mensuel ou annuel avec un clustering sur la colonne de partitionnement pour obtenir des performances optimales. Pour plus d'informations, consultez la section Partitionnement et clustering de cette page.

Partitionnement et clustering

Le partitionnement et le clustering peuvent améliorer les performances et réduire le coût des requêtes.

Utilisez le clustering dans les cas suivants :

  • Vous n'avez pas besoin de garanties de coûts strictes avant d'exécuter la requête.
  • Vous avez besoin de plus de précision que le partitionnement seul ne le permet. Pour bénéficier des avantages du clustering en plus des avantages du partitionnement, vous pouvez utiliser la même colonne pour le partitionnement et le clustering.
  • Vos requêtes utilisent généralement des filtres ou des agrégations sur plusieurs colonnes spécifiques.
  • La cardinalité du nombre de valeurs dans une colonne ou dans un groupe de colonnes est importante.

Utilisez le partitionnement dans les cas suivants :

  • Vous souhaitez 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.

  • Vous avez besoin d'une gestion au niveau de la partition. Par exemple, vous souhaitez définir un délai d'expiration de partition, charger des données dans une partition spécifique ou supprimer des partitions.

  • Vous souhaitez spécifier le mode de partitionnement des données et les données de chaque partition. Par exemple, vous souhaitez définir la précision temporelle ou définir les plages utilisées pour partitionner la table pour le partitionnement par plages d'entiers.

Préférez le clustering au partitionnement dans les cas suivants :

  • Le partitionnement génère une petite quantité de données par partition (environ moins de 1 Go).
  • Le partitionnement entraîne un grand nombre de partitions au-delà des limites imposées sur les tables partitionnées.
  • Suite au partitionnement, vos opérations de mutation modifient fréquemment la majorité des partitions de la table (par exemple, toutes les quelques minutes).

Vous pouvez également combiner le partitionnement avec le clustering. Les données sont d'abord partitionnées, puis les données de chaque partition sont mises en cluster sur la base des colonnes de clustering.

Lorsque la table 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 peuvent s'appliquer lors de l'exécution effective de la requête grâce à l'élimination de cluster.

Partitionnement et segmentation

La segmentation des tables est la pratique qui consiste à stocker les données dans plusieurs tables en utilisant un préfixe de nom tel que [PREFIX]_YYYYMMDD.

Nous recommandons le partitionnement plutôt que la segmentation des tables car les tables partitionnées sont plus performantes. Avec les tables segmentées, BigQuery doit conserver une copie du schéma et des métadonnées pour chaque table. BigQuery devra peut-être également valider les autorisations pour chaque table interrogée. Cette pratique alourdit le traitement des requêtes et affecte les performances.

Si vous avez déjà créé des tables segmentées par date, vous pouvez les convertir en tables partitionnées par date d'ingestion. Pour plus d'informations, consultez la section Convertir des tables segmentées par date en tables partitionnées par date d'ingestion.

Limites

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.

Les tables partitionnées par colonnes d'unités de temps sont soumises aux limitations suivantes :

  • La colonne de partitionnement doit être une colonne scalaire de type DATE, TIMESTAMP ou DATETIME. 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.

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.

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.

Sécurité des tables partitionnées

Le contrôle d'accès des tables partitionnées est identique à celui des tables standards. Pour plus d'informations, consultez la page Présentation des contrôles d'accès aux tables.

Étapes suivantes