Mettre à jour des données de tables partitionnées à l'aide d'instructions 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 (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