Ce document explique comment créer et utiliser des tables partitionnées par une colonne DATE
, TIMESTAMP
ou. DATETIME
. Pour plus d'informations sur les tables partitionnées par date d'ingestion, consultez la page Créer et utiliser des tables partitionnées par date d'ingestion. Pour en savoir plus sur les tables partitionnées par plages d'entiers, consultez la page Créer et utiliser des tables partitionnées par plages d'entiers.
Après avoir créé une table partitionnée, vous pouvez :
- contrôler l'accès aux données de votre 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 sont soumises aux limites suivantes :
- La colonne de partitionnement doit être une colonne scalaire de type
DATE
,TIMESTAMP
ouDATETIME
. Le mode de la colonne peut être de typeREQUIRED
ouNULLABLE
, mais pasREPEATED
(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.
Les tables partitionnées par heure, par mois et par an sont soumises à d'autres restrictions :
- La commande
bq partition
de l'outil de ligne de commandebq
n'est pas compatible.
Créer des tables partitionnées
Vous pouvez créer une table partitionnée de plusieurs manières :
- En utilisant Cloud Console
- En utilisant une instruction LDD
CREATE TABLE
avec une clausePARTITION BY
contenant un élémentpartition expression
- En exécutant la commande
bq
de l'outil de ligne de commandebq mk
- De manière automatisée, en appelant la méthode API
tables.insert
- À partir des résultats de requête
- 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.
Partitionnement quotidien et partitionnement horaire, mensuel ou annuel
Lorsque vous utilisez une colonne TIMESTAMP
, DATETIME
ou DATE
pour partitionner des données, vous pouvez créer des partitions avec une précision quotidienne, horaire, mensuelle ou annuelle, en fonction de vos données et de vos besoins.
Le partitionnement quotidien est le type de partitionnement par défaut et, lorsqu'il est utilisé avec le clustering, il répond à la majorité des cas d'utilisation de BigQuery. Le partitionnement quotidien est notamment le meilleur choix lorsque vos données sont réparties sur une large plage de dates ou si des données sont ajoutées en continu au fil du temps. Si vos données couvrent une large plage de dates, le partitionnement quotidien vous permet de rester en dessous des limites de partition de votre table.
Choisissez plutôt 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). Avec le partitionnement horaire, vous pouvez traiter les données avec une précision au niveau de l'heure, par exemple, lorsque vous ajoutez, tronquez ou supprimez des données d'une partition particulière.
Choisissez le partitionnement mensuel ou annuel si vos tables contiennent une quantité de données par jour relativement faible 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). Utilisez le partitionnement mensuel ou annuel avec un clustering sur votre colonne d'horodatage, de date ou de date et d'heure pour obtenir les meilleures performances dans ces scénarios. Pour en savoir plus et obtenir des exemples, consultez la page Partitionnement par unité de temps avec clustering.
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 effectuer les opérations suivantes :
- 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.
Après avoir créé la table partitionnée, vous pouvez y effectuer les opérations suivantes :
- Charger des données
- Écrire les résultats des requêtes
- Copier des données
Pour créer une table partitionnée vide avec une définition de schéma :
Console
Dans la section Ressources du panneau de navigation, développez votre projet et sélectionnez un ensemble de données.
À droite de la fenêtre, dans le panneau de détails, cliquez sur Créer une table.
Dans la section Source du panneau Créer une table :
- Pour Créer une table à partir de, sélectionnez Table vide.
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).
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.
Utilisez l'option Ajouter un champ pour saisir manuellement le schéma.
Pour le champ Paramètres de partitionnement et de clustering, cliquez sur Aucun partitionnement, sélectionnez Partitionner par champ, puis choisissez la colonne
DATE
,TIMESTAMP
ouDATETIME
. Cette option n'est pas disponible si le schéma ne contient pas de colonneDATE
,TIMESTAMP
ouDATETIME
.(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.(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.
Cliquez sur Créer une table.
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 :
Ouvrez la page BigQuery dans Cloud Console.
Cliquez sur Saisir une nouvelle requête.
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
qui est partitionnée quotidiennement par la colonnetransaction_date
DATE
et dont le délai d'expiration de partition est de trois jours.CREATE TABLE mydataset.newtable (transaction_id INT64, transaction_date DATE) PARTITION BY transaction_date OPTIONS ( partition_expiration_days=3, description="a table partitioned by transaction_date" )
La requête suivante crée la même table, mais est partitionnée toutes les heures. Elle emploie
TIMESTAMP_TRUNC
pour délimiter l'horodatage au niveau de l'heure :CREATE TABLE mydataset.newtable (transaction_id INT64, transaction_ts TIMESTAMP) PARTITION BY TIMESTAMP_TRUNC(transaction_ts, HOUR) OPTIONS ( partition_expiration_days=3, description="a table partitioned by transaction_ts" )
La requête suivante crée la même table, mais est partitionnée toutes les heures. Elle emploie
TIMESTAMP_TRUNC
pour délimiter l'horodatage au niveau du mois :CREATE TABLE mydataset.newtable (transaction_id INT64, transaction_ts TIMESTAMP) PARTITION BY TIMESTAMP_TRUNC(transaction_ts, MONTH) OPTIONS ( partition_expiration_days=3, description="a table partitioned by transaction_ts" )
La requête suivante crée la même table, mais est partitionnée chaque année. Elle emploie
TIMESTAMP_TRUNC
pour délimiter l'horodatage au niveau de l'année :CREATE TABLE mydataset.newtable (transaction_id INT64, transaction_ts TIMESTAMP) PARTITION BY TIMESTAMP_TRUNC(transaction_ts, YEAR) OPTIONS ( partition_expiration_days=3, description="a table partitioned by transaction_ts" )
Cliquez sur Exécuter. Lorsque la requête est terminée, la table apparaît dans le volet Ressources.
bq
Exécutez la commande bq mk
avec l'option --table
(ou le raccourci -t
), l'option --schema
et l'option --time_partitioning_field
. Vous pouvez fournir la définition du schéma de la table en ligne ou fournir un fichier de schéma JSON.
Les paramètres facultatifs incluent --expiration
, --description
, --time_partitioning_expiration
, --destination_kms_key
, --require_partition_filter
, --time_partitioning_type
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 cette option, consultez la page Protéger des données avec des clés Cloud Key Management Service.
Entrez la commande suivante pour créer une table partitionnée vide, avec une définition de schéma :
bq mk --table \ --expiration integer1 \ --schema schema \ --time_partitioning_field column \ --time_partitioning_type unit_time \ --time_partitioning_expiration integer2 \ --[no]require_partition_filter \ --description "description" \ --label key:value, key:value \ project_id:dataset.table
Remplacez les éléments suivants :
integer1
est la 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 partitionnée par unité de temps, 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 et toutes les partitions après le délai indiqué.schema
est une définition de schéma intégrée au format field:data_type, field:data_type ou le chemin d'accès au fichier de schéma JSON sur votre machine locale.column
est le nom de la colonneTIMESTAMP
,DATETIME
ouDATE
utilisée pour créer les partitions.unit_time
estDAY
,HOUR
,MONTH
ouYEAR
, selon la précision souhaitée du partitionnement par unité de temps. La valeur par défaut estDAY
sitime_partitioning_type
n'est pas spécifié.integer2
est la durée de vie par défaut (en secondes) des partitions de la table. Il n'y a pas de valeur minimale. Le délai d'expiration correspond à la date de la partition plus la valeur entière. Le délai d'expiration de la partition est indépendant du délai d'expiration de la table, mais il ne l'ignore pas. Si vous définissez un délai d'expiration de partition plus long que celui de la table, le délai d'expiration de la table est prioritaire.description
est la description de la table entre guillemets.key:value
est la pairekey:value
qui représente un libellé. Vous pouvez entrer plusieurs libellés en utilisant une liste séparée par des virgules.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.
Lorsque vous spécifiez le schéma sur l'outil de ligne de commande bq
, 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 prennent la valeur par défaut 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 partitionnée par heure nommée mypartitionedtable
dans mydataset
dans votre projet par défaut. Le délai d'expiration du partitionnement est défini sur 86 400 secondes (un jour), le délai d'expiration de la table sur 2 592 000 secondes (un mois de 30 jours), la description sur This is my partitioned table
et le libellé sur organization:development
. La commande utilise le raccourci -t
au lieu de --table
.
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.
Le schéma est spécifié de manière intégrée comme suit : ts:TIMESTAMP,column1:STRING,column2:INTEGER,coumn4:STRING
. Le champ TIMESTAMP
ts
spécifié est utilisé pour partitionner les données par heure. Notez que le partitionnement horaire nécessite une colonne TIMESTAMP
ou DATETIME
plutôt qu'une colonne DATE
.
bq mk -t \
--expiration 2592000 \
--schema 'ts:TIMESTAMP,column1:STRING,column2:INTEGER,coumn4:STRING' \
--time_partitioning_field ts \
--time_partitioning_type HOUR \
--time_partitioning_expiration 86400 \
--require_partition_filter \
--description "This is my partitioned table" \
--label org:dev \
mydataset.mypartitionedtable
Saisissez la commande suivante pour créer une table partitionnée par jour nommée mypartitionedtable
dans myotherproject
, et non dans votre projet par défaut. Le délai d'expiration du partitionnement est défini sur 259 200 secondes (trois jours), la description sur This is my partitioned table
et le libellé 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'expirera jamais, mais les partitions expireront dans trois jours.
Le schéma est spécifié dans un fichier JSON local : /tmp/myschema.json
.
La définition de schéma inclut un champ TIMESTAMP
nommé ts
, utilisé pour partitionner les données par jour.
bq mk -t \
--expiration 2592000 \
--schema /tmp/myschema.json \
--time_partitioning_field ts \
--time_partitioning_type DAY \
--time_partitioning_expiration 86400 \
--description "This is my partitioned table" \
--label org:dev \
myotherproject:mydataset.mypartitionedtable
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 timePartitioning
et schema
.
Go
Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Go 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 Go.
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.
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.
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.
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, écrivez les résultats dans une nouvelle table de destination. Vous pouvez créer une table partitionnée en interrogeant une table partitionnée ou une table non partitionnée. Vous ne pouvez pas modifier une table standard existante en une table partitionnée avec des résultats de requêtes.
Lorsque vous créez une table partitionnée à partir d'un résultat de requête, vous devez utiliser le SQL standard. Actuellement, l'ancien SQL ne permet pas d'interroger les tables partitionnées ou d'écrire les résultats de requêtes dans des tables partitionnées.
Les décorateurs de partition vous permettent d'écrire les résultats de requêtes sur une partition spécifique. Par exemple, pour écrire des résultats sur la partition du 1er mai 2016, servez-vous du décorateur de partition suivant :
table_name$20160501
Lors de l'écriture des résultats de requêtes sur une partition spécifique à l'aide d'un décorateur de partition, les données en cours d'écriture sur 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.
Exemple :
La requête suivante récupère des données datant du 1er février 2018 et les écrit dans la partition $20180201
de la table mytable
. La table contient deux colonnes : une colonne TIMESTAMP
nommée TS
et une colonne INT64
nommée a
.
bq query \
--nouse_legacy_sql \
--destination_table=mytable$20180201 \
'SELECT
TIMESTAMP("2018-02-01") AS TS,
2 AS a'
La requête suivante récupère des données à partir du 31 janvier 2018 et tente de les écrire dans la partition $20180201
de mytable
. Cette requête échoue, car les données que vous tentez d'écrire ne correspondent pas à la date de la partition.
bq query \
--nouse_legacy_sql \
--destination_table=T$20180201 \
'SELECT
TIMESTAMP("2018-01-31") as TS,
2 as a'
Pour plus d'informations sur la façon d'ajouter ou de reformuler (remplacer) des données dans des tables partitionnées, consultez la page Ajouter et écraser des données de tables partitionnées. Pour plus d'informations sur la façon d'interroger des tables partitionnées, consultez la page Interroger des tables partitionnées.
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 --time_partitioning_field
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 \ --time_partitioning_field column \ --time_partitioning_type unit_time --use_legacy_sql=false \ 'query'
Remplacez les éléments suivants :
location
est le nom du site. L'option--location
est facultative. Par exemple, si vous utilisez BigQuery dans la région de Tokyo, définissez la valeur de l'option surasia-northeast1
. Vous pouvez définir une valeur par défaut correspondant à l'emplacement à l'aide du fichier.bigqueryrc
.project_id
est l'ID de votre projet.dataset
correspond au nom de l'ensemble de données qui contiendra la nouvelle table partitionnée.table
correspond au nom de la table partitionnée que vous créez en utilisant les résultats de la requête.column
correspond au nom de la colonneTIMESTAMP
ouDATE
permettant de créer les partitions.unit_time
estDAY
,HOUR
,MONTH
ouYEAR
, selon la précision souhaitée pour le partitionnement par unité de temps. La valeur par défaut estDAY
sitime_partitioning_type
n'est pas spécifié.query
est une requête en syntaxe SQL standard. 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.
Par exemple :
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. La requête récupère les données d'une table non partitionnée dans l'ensemble de données public NHTSA Traffic Fatality.
La colonne timestamp_of_crash
TIMESTAMP
de la table permet de créer les partitions.
bq query \ --destination_table mydataset.mypartitionedtable \ --time_partitioning_field timestamp_of_crash \ --use_legacy_sql=false \ 'SELECT state_number, state_name, day_of_crash, month_of_crash, year_of_crash, latitude, longitude, manner_of_collision, number_of_fatalities, timestamp_of_crash FROM `bigquery-public-data`.nhtsa_traffic_fatalities.accident_2016 LIMIT 100'
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 myotherproject
, et non dans votre projet par défaut. La requête récupère les données d'une table non partitionnée dans l'ensemble de données public NHTSA Traffic Fatality.
La colonne TIMESTAMP
timestamp_of_crash
de la table permet de créer les partitions.
bq query \ --destination_table myotherproject:mydataset.mypartitionedtable \ --time_partitioning_field timestamp_of_crash \ --use_legacy_sql=false \ 'SELECT state_number, state_name, day_of_crash, month_of_crash, year_of_crash, latitude, longitude, manner_of_collision, number_of_fatalities, timestamp_of_crash FROM `bigquery-public-data`.nhtsa_traffic_fatalities.accident_2016 LIMIT 100'
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 timePartitioning
.
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, pour charger toutes les données générées le 1er mai 2016 dans la partition 20160501
, utilisez le décorateur de partition suivant :
table_name$20160501
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.
Partitionnement par unité de temps avec clustering
Le partitionnement par unité de temps peut être utilisé avec le clustering. Une table partitionnée par unité de temps avec clustering commence par partitionner ses données en fonction des limites temporelles de la colonne de partitionnement, (jour, heur, mois ou année), puis met en cluster les données en fonction des limites des colonnes de partitionnement.
Par exemple, cette commande crée une table avec une colonne partitionnée quotidiennement et un cluster.
bq mk --time_partitioning_type=DAY \ --time_partitioning_field=ts_column \ --clustering_fields=column1,column2 \ mydataset.mytable2 "ts_column:TIMESTAMP,column1:INTEGER,column2:STRING"
Lorsque vous récupérez le format de la table, vous constatez que le partitionnement sur la base de l'horodatage quotidien et le clustering sont actifs :
bq show --format=prettyjson mydataset.mytable2 ... "clustering": { "fields": [ "column1", "column2" ] }, ... "timePartitioning": { "field": "ts_column", "type": "DAY" }, ...
Si vous dépassez le nombre maximal de partitions par table ou si vous avez trop peu de données réparties sur de nombreuses partitions et que vous les transformez trop souvent, envisagez d'utiliser un partitionnement sur la base d'une unité de temps plus longue avec un clustering sur la même colonne de partitionnement. Il s'agit de la méthode recommandée pour utiliser des tables partitionnées tout en respectant les limites de partitionnement.
Par exemple, cette commande crée une table partitionnée et mise en cluster quotidiennement sur la même colonne :
bq mk --time_partitioning_type=DAY \ --time_partitioning_field=ts_column \ --clustering_fields=ts_column,column1 \ mydataset.mytable2 "ts_column:TIMESTAMP,column1:INTEGER,column2:STRING"
Voici un autre exemple pour la table ci-dessus, mais avec une unité de temps plus longue pour l'intervalle de partitionnement :
bq mk --time_partitioning_type=MONTH \ --time_partitioning_field=ts_column \ --clustering_fields=ts_column,column1 \ mydataset.mytable2 "ts_column:TIMESTAMP,column1:INTEGER,column2:STRING"
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 à l'un des niveaux suivants (classés ici par ordre de plage de ressources autorisée, 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 l'accès 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 de plusieurs manières :
- En utilisant Cloud Console
- En exécutant la commande
bq show
de l'outil de ligne de commandebq
- 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
Ouvrez la page "BigQuery" dans Cloud Console.
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.
Cliquez sur Details (Détails) sous l'éditeur de requête. Cet onglet affiche la description de la table et ses informations.
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
Remplacez les éléments suivants :
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": "1563236533535", "description": "This is my partitioned table", "etag": "/ABcDEo7f8GHijKL2mnOpQr==", "expirationTime": "1565828533000", "id": "myproject:mydataset.mypartitionedtable", "kind": "bigquery#table", "labels": { "org": "dev" }, "lastModifiedTime": "1563236533576", "location": "US", "numBytes": "0", "numLongTermBytes": "0", "numRows": "0", "requirePartitionFilter": true, "schema": { "fields": [ { "name": "ts", "type": "TIMESTAMP" }, { "name": "column1", "type": "STRING" }, { "name": "column2", "type": "INTEGER" }, { "name": "column3", "type": "STRING" } ] }, "selfLink": "https://bigquery.googleapis.com/bigquery/v2/projects/myproject/datasets/mydataset/tables/mypartitionedtable", "tableReference": { "datasetId": "mydataset", "projectId": "myproject", "tableId": "mypartitionedtable" }, "timePartitioning": { "expirationMs": "86400000", "field": "ts", "requirePartitionFilter": true, "type": "DAY" }, "type": "TABLE" }
Saisissez la commande suivante pour n'afficher que les informations de schéma sur mytable
dans mydataset
. mydataset
se trouve dans myotherproject
, et non dans votre projet par défaut.
bq show --schema --format=prettyjson myotherproject:mydataset.mytable
Le résultat doit se présenter sous la forme suivante :
[ { "name": "ts", "type": "TIMESTAMP" }, { "name": "column1", "type": "STRING" }, { "name": "column2", "type": "INTEGER" }, { "name": "column3", "type": "STRING" } ]
API
Appelez la méthode bigquery.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 les tables d'un ensemble de données (y compris les tables partitionnées) de plusieurs manières :
- En utilisant Cloud Console
- En exécutant la commande
bq ls
de l'outil de ligne de commandebq
- 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
Ouvrez la page "BigQuery" dans Cloud Console.
Dans la section Ressources du panneau de navigation, développez votre projet et sélectionnez votre ensemble de données.
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.
bq
Exécutez la commande bq ls
. L'option --format
peut être utilisée pour contrôler le résultat. Si vous répertoriez des tables dans un projet autre que votre projet par défaut, ajoutez l'ID du projet à l'ensemble de données, en respectant le format suivant : project_id:dataset
.
bq ls --format=pretty project_id:dataset
Remplacez les éléments suivants :
project_id
est l'ID de votre projet.dataset
est le nom de l'ensemble de données.
Lorsque vous exécutez la commande, le champ Type
affiche TABLE
ou VIEW
. Pour les tables partitionnées, le champ Time Partitioning
affiche DAY
, la colonne utilisée afin de créer les partitions. Si un délai d'expiration de partition est spécifié, il est exprimé en millisecondes.
Exemple :
+-------------------------+-------+----------------------+---------------------------------------------------+ | tableId | Type | Labels | Time Partitioning | +-------------------------+-------+----------------------+---------------------------------------------------+ | mytable | TABLE | department:shipping | DAY (field: source_date, expirationMs: 86400000) | | myview | VIEW | | | +-------------------------+-------+----------------------+---------------------------------------------------+
Exemples :
Saisissez la commande suivante pour répertorier les tables de l'ensemble de données mydataset
dans votre projet par défaut.
bq ls --format=pretty mydataset
Saisissez la commande suivante pour répertorier les tables de l'ensemble de données mydataset
dans myotherproject
.
bq ls --format=pretty myotherproject:mydataset
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 dans Cloud Console, en exécutant la commande bq query
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
Ouvrez la page "BigQuery" dans Cloud Console.
Cliquez sur le bouton Saisir une nouvelle requête.
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__]
Remplacez les éléments suivants :
dataset
est l'ensemble de données qui contient la table.table
est le nom de la table.
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__]'
Remplacez les éléments suivants :
location
est le nom du site. L'option--location
est facultative. Par exemple, si vous utilisez BigQuery dans la région de Tokyo, définissez la valeur de l'option surasia-northeast1
. Vous pouvez définir une valeur par défaut correspondant à l'emplacement à l'aide du 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 à l'aide de l'une des méthodes suivantes :
- En utilisant Cloud Console
- En exécutant la commande
bq query
de l'outil de ligne de commandebq
- 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 date d'ingestion 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 | 20160314 | 1517190224120 | 1517190224997 | | myproject | mydataset | mytable | 20160315 | 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 | +--------------+--------------------+ | 20160102 | 1471632556179 | | 20160101 | 1471632538142 | | 20160103 | 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 | +--------------+----------------------------+ | 20160103 | 2016-08-19 18:49:30.463000 | | 20160102 | 2016-08-19 18:49:16.179000 | | 20160101 | 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 découvrir comment créer et utiliser des tables partitionnées par date d'ingestion, consultez la page Créer et utiliser des tables partitionnées par date d'ingestion.
- Pour apprendre à créer et à utiliser des tables partitionnées par plages d'entiers, consultez la page Créer et utiliser des tables partitionnées par plages d'entiers.
- 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.