Cette page est une présentation de la prise en charge du langage de manipulation de données (LMD) pour les tables partitionnées.
Pour en savoir plus sur le LMD, consultez :
- Présentation du LMD
- Syntaxe LMD
- Mettre à jour des données de table à l'aide du langage de manipulation de données
Tables utilisées dans les exemples
Les définitions de schémas JSON suivantes représentent les tables utilisées dans les exemples de cette page.
mytable
: table partitionnée par date d'ingestion
[ {"name": "field1", "type": "INTEGER"}, {"name": "field2", "type": "STRING"} ]
mytable2
: table standard (non partitionnée)
[ {"name": "id", "type": "INTEGER"}, {"name": "ts", "type": "TIMESTAMP"} ]
mycolumntable
: table partitionnée à l'aide de la colonne ts
TIMESTAMP
[ {"name": "field1", "type": "INTEGER"}, {"name": "field2", "type": "STRING"} {"name": "field3", "type": "BOOLEAN"} {"name": "ts", "type": "TIMESTAMP"} ]
Insérer des données
Utilisez une instruction LMD INSERT
pour ajouter des lignes à une table partitionnée.
Insérer des données dans des tables partitionnées par date d'ingestion
Lorsque vous utilisez une instruction LMD pour ajouter des lignes à une table partitionnée par date d'ingestion, il est possible d'indiquer la partition à laquelle elles doivent être ajoutées. La partition est référencée à l'aide de la pseudo-colonne _PARTITIONTIME
.
Par exemple, l'instruction INSERT
ci-dessous ajoute une ligne à la partition du 1er mai 2017 de mytable
(“2017-05-01”
).
INSERT INTO project_id.dataset.mytable (_PARTITIONTIME, field1, field2) SELECT TIMESTAMP("2017-05-01"), 1, "one"
Seuls les horodatages correspondant aux limites de dates exactes peuvent être utilisés. Par exemple, l'instruction LMD suivante renvoie une erreur :
INSERT INTO project_id.dataset.mytable (_PARTITIONTIME, field1, field2) SELECT TIMESTAMP("2017-05-01 21:30:00"), 1, "one"
Insérer des données dans des tables partitionnées
La procédure d'insertion de données dans une table partitionnée à l'aide du LMD est la même que pour insérer des données dans une table non partitionnée.
Par exemple, l'instruction INSERT
ci-dessous ajoute des lignes à la table partitionnée mycolumntable
en sélectionnant des données dans mytable2
(table non partitionnée).
INSERT INTO project_id.dataset.mycolumntable (ts, field1) SELECT ts, id FROM project_id.dataset.mytable2
Supprimer des données
Utilisez une instruction LMD DELETE
pour supprimer des lignes d'une table partitionnée.
Supprimer des données dans des tables partitionnées par date d'ingestion
L'instruction DELETE
ci-dessous supprime toutes les lignes de la partition du 1er juin 2017 ("2017-06-01"
) de mytable
, où field1
est égal à 21
. La partition est référencée à l'aide de la pseudo-colonne _PARTITIONTIME
.
DELETE project_id.dataset.mytable WHERE field1 = 21 AND _PARTITIONTIME = "2017-06-01"
Supprimer des données dans des tables partitionnées
La procédure de suppression de données dans une table partitionnée à l'aide du LMD est la même que pour supprimer des données dans une table non partitionnée.
Par exemple, l'instruction DELETE
ci-dessous supprime toutes les lignes de la partition du 1er juin 2017 ("2017-06-01"
) de mycolumntable
, où field1
est égal à 21
.
DELETE project_id.dataset.mycolumntable WHERE field1 = 21 AND DATE(ts) = "2017-06-01"
Mettre à jour des données
Utilisez une instruction UPDATE
pour mettre à jour des lignes dans une table partitionnée.
Mettre à jour des données dans des tables partitionnées par date d'ingestion
L'instruction UPDATE
ci-dessous déplace les lignes d'une partition vers une autre.
Les lignes de la partition du 1er mai 2017 (“2017-05-01”
) de mytable
, où field1
est égal à 21
, sont déplacées vers la partition du 1er juin 2017 (“2017-06-01”
).
UPDATE project_id.dataset.mytable SET _PARTITIONTIME = "2017-06-01" WHERE _PARTITIONTIME = "2017-05-01" AND field1 = 21
Mettre à jour des données dans des tables partitionnées
La procédure de mise à jour de données dans une table partitionnée à l'aide du LMD est la même que pour mettre à jour les données d'une table non partitionnée. Par exemple, l'instruction UPDATE
ci-dessous déplace des lignes d'une partition vers une autre. Les lignes de la partition du 1er mai 2017 (“2017-05-01”
) de mytable
, où field1
est égal à 21
, sont déplacées vers la partition du 1er juin 2017 (“2017-06-01”
).
UPDATE project_id.dataset.mycolumntable SET ts = "2017-06-01" WHERE DATE(ts) = "2017-05-01" AND field1 = 21
LMD dans les tables partitionnées par heure, par mois et par année
Vous pouvez utiliser des instructions LMD pour modifier une table partitionnée par heure, par mois ou par année. Indiquez la plage d'heures, de mois ou d'années des dates, horodatages et dates/heures pertinents, comme dans l'exemple suivant pour les tables partitionnées par mois :
bq query --nouse_legacy_sql 'DELETE FROM my_dataset.my_table WHERE TIMESTAMP_TRUNC(ts_column, MONTH) = "2020-01-01 00:00:00";'
Autre exemple pour les tables partitionnées avec une colonne DATETIME
:
bq query --nouse_legacy_sql 'DELETE FROM my_dataset.my_table WHERE dt_column BETWEEN DATETIME("2020-01-01") AND DATETIME("2020-05-01");'
Utiliser une instruction MERGE
Utilisez une instruction LMD MERGE
pour combiner les opérations INSERT
, UPDATE
et DELETE
d'une table partitionnée en une seule instruction, et les exécuter de manière atomique.
Restreindre les partitions lors de l'utilisation d'une instruction MERGE
Lors de l'exécution d'une instruction MERGE
sur une table partitionnée, les partitions impliquées dans l'instruction peuvent être limitées à l'aide de la pseudo-colonne _PARTITIONTIME
(pour les tables partitionnées par date d'ingestion) ou de la colonne de date, d'horodatage ou de date et heure (pour les tables partitionnées). La restriction des partitions réduit les coûts et améliore les performances des requêtes.
Les conditions de restriction des partitions peuvent être utilisées dans les cas suivants : filtre de sous-requête, filtre de type search_condition
ou filtre de type merge_condition
.
Chacun des exemples ci-dessous interroge une table partitionnée par date d'ingestion à l'aide de la pseudo-colonne _PARTITIONTIME
.
Utiliser une sous-requête pour filtrer les données sources
Les partitions peuvent être restreintes à l'aide d'un filtre de sous-requête. Par exemple, dans l'instruction MERGE
suivante, seules les lignes de la partition '2018-01-01'
de la table source sont analysées.
MERGE dataset.target T USING (SELECT * FROM dataset.source WHERE _PARTITIONTIME = '2018-01-01') S ON T.c1 = S.c1 WHEN MATCHED THEN DELETE
Utiliser un filtre dans la condition search_condition
d'une clause when_clause
L'optimiseur de requêtes tente d'utiliser un filtre dans une condition search_condition
pour restreindre les partitions. Par exemple, dans l'instruction MERGE
ci-dessous, seules les lignes des partitions suivantes sont analysées dans la table cible : '2018-01-01'
, '2018-01-02'
et '2018-01-03'
.
MERGE dataset.target T USING dataset.source S ON T.c1 = S.c1 WHEN MATCHED AND T._PARTITIONTIME = '2018-01-01' THEN UPDATE SET c1 = S.c1 WHEN MATCHED AND T._PARTITIONTIME = '2018-01-02' THEN UPDATE SET c1 = c1 + 10 WHEN NOT MATCHED BY SOURCE AND T._PARTITIONTIME = '2018-01-03' THEN DELETE
Dans l'exemple suivant, la clause WHEN NOT MATCHED BY SOURCE
nécessite toutes les données de la table cible. Par conséquent, toutes les partitions sont analysées et les octets lus dans toutes les partitions vous sont facturés.
MERGE dataset.target T USING dataset.source S ON T.c1 = S.c1 WHEN MATCHED AND T._PARTITIONTIME = '2018-01-01' THEN UPDATE SET c1 = S.c1 WHEN NOT MATCHED BY SOURCE THEN UPDATE SET c1 = c1 + 1
En général, lorsque les clauses WHEN NOT MATCHED
et WHEN NOT MATCHED BY SOURCE
sont utilisées en même temps, BigQuery part du principe qu'il existe une liaison FULL OUTER JOIN
entre les tables source et cible. En règle générale, les partitions ne peuvent pas être restreintes dans une liaison FULL OUTER
JOIN
. Toutefois, si un faux prédicat constant est utilisé, la condition de filtre peut être utilisée la restriction de partitions. La requête suivante utilise la restriction de partitions pour analyser uniquement la partition '2018-01-01'
dans les tables cible et source.
MERGE dataset.target T USING dataset.source S ON FALSE WHEN NOT MATCHED AND _PARTITIONTIME = '2018-01-01' THEN INSERT(c1) VALUES(c1) WHEN NOT MATCHED BY SOURCE AND _PARTITIONTIME = '2018-01-01' THEN DELETE
Utiliser un filtre dans une condition merge_condition
L'optimiseur de requêtes tente d'utiliser un filtre dans une condition merge_condition
pour restreindre les partitions. Par exemple, la requête suivante analyse uniquement la partition '2018-01-01'
dans les tables cible et source.
MERGE dataset.target T USING dataset.source S ON T.c1 = S.c1 AND T._PARTITIONTIME = '2018-01-01' AND S._PARTITIONTIME = '2018-01-01' WHEN MATCHED THEN UPDATE SET c1 = S.c1
Dans cet exemple, la condition merge_condition
sert de prédicat pour joindre les tables source et cible. L'optimiseur de requêtes peut être en mesure d'utiliser le pushdown de prédicat ou non (selon le type de jointure).
Dans l'exemple suivant, l'instruction MERGE
n'autorise pas la restriction de partitions, car le filtre de partition est un prédicat dans une condition de jointure qui ne peut pas être appliquée directement sur la table.
MERGE dataset.target T USING dataset.source S ON T.c1 = S.c1 AND T._PARTITIONTIME = '2018-01-01' WHEN NOT MATCHED BY SOURCE THEN UPDATE SET c1 = S.c1
Limites
Pour en savoir plus sur les limites du LMD, consultez la section Limites de la page Langage de manipulation de données.
Quotas
Pour en savoir plus sur les quotas LMD, consultez la section Instructions LMD de la page Quotas et limites.
Tarifs
Pour en savoir plus sur la tarification du LMD, consultez Tarifs LMD pour les tables partitionnées.
Étapes suivantes
- Découvrez comment créer et utiliser des tables partitionnées par date d'ingestion.
- Découvrez comment créer et utiliser des tables partitionnées.
- Découvrez comment interroger des tables partitionnées.
- Découvrez une présentation du LMD.
- Découvrez comment composer des instructions LMD à l'aide de la syntaxe LMD.