Mettre à jour des données de tables partitionnées à l'aide de LMD

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 :

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 (une 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-05-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-05-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

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 date ou timestamp (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 de 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 en utilisant 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 a besoin de 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. Normalement, 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 pour 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 analysera 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 informations de quotas de 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.

Cette page vous a-t-elle été utile ? Évaluez-la :

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see our Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Dernière mise à jour : avril 5, 2019