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"}
    ]

Dans les exemples où figure COLUMN_ID, remplacez cet espace réservé par le nom de la colonne sur laquelle vous souhaitez intervenir.

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"

Utiliser DML DELETE pour supprimer des partitions

Si une instruction DELETE éligible couvre toutes les lignes d'une partition, BigQuery supprime la partition entière. Cette suppression s'effectue sans analyser d'octets ni consommer d'emplacements. L'exemple d'instruction DELETE suivant couvre la totalité de la partition d'un filtre dans la pseudo-colonne _PARTITIONDATE :

DELETE mydataset.mytable
WHERE _PARTITIONDATE IN ('2076-10-07', '2076-03-06');

Disqualifications fréquentes

Les requêtes présentant les caractéristiques suivantes ne peuvent pas bénéficier de l'optimisation :

L'éligibilité à l'optimisation peut varier en fonction du type de partitionnement, des métadonnées de stockage sous-jacentes et des prédicats de filtre. Il est recommandé d'effectuer un test pour vérifier que la requête ne génère aucun octet traité.

Transaction multi-instructions

Cette optimisation fonctionne dans une transaction multi-instructions. L'exemple de requête suivant remplace une partition par les données d'une autre table dans une transaction unique, sans analyser la partition pour l'instruction DELETE.

DECLARE REPLACE_DAY DATE;
BEGIN TRANSACTION;

-- find the partition which we want to replace
SET REPLACE_DAY = (SELECT MAX(d) FROM mydataset.mytable_staging);

-- delete the entire partition from mytable
DELETE FROM mydataset.mytable WHERE part_col = REPLACE_DAY;

-- insert the new data into the same partition in mytable
INSERT INTO mydataset.mytable
SELECT * FROM mydataset.mytable_staging WHERE part_col = REPLACE_DAY;

COMMIT TRANSACTION;

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, vous pouvez limiter les partitions analysées en incluant la colonne de partitionnement dans un filtre de sous-requête, un filtre search_condition ou un filtre merge_condition. La restriction peut se produire lors de l'analyse de la table source, de la table cible ou des deux.

Chacun des exemples ci-dessous interroge une table partitionnée par date d'ingestion en utilisant la pseudo-colonne _PARTITIONTIME comme filtre.

Utiliser une sous-requête pour filtrer les données sources

Dans l'instruction MERGE suivante, la sous-requête de la clause USING filtre la pseudo-colonne _PARTITIONTIME dans la table source.

MERGE dataset.target T
USING (SELECT * FROM dataset.source WHERE _PARTITIONTIME = '2018-01-01') S
ON T.COLUMN_ID = S.COLUMN_ID
WHEN MATCHED THEN
  DELETE

Dans le plan d'exécution de la requête, la sous-requête s'exécute en premier. Seules les lignes de la partition '2018-01-01' de la table source sont analysées. Voici l'étape pertinente dans le plan de requête :

READ $10:name, $11:_PARTITIONTIME
FROM temp.source
WHERE equal($11, 1514764800.000000000)

Utiliser un filtre dans la condition search_condition d'une clause when_clause

Si une condition search_condition contient un filtre, l'optimiseur de requêtes tente de restreindre les partitions. Par exemple, dans l'instruction MERGE suivante, chaque clause WHEN MATCHED et WHEN NOT MATCHED contient un filtre dans la pseudo-colonne _PARTITIONTIME.

MERGE dataset.target T
USING dataset.source S
ON T.COLUMN_ID = S.COLUMN_ID
WHEN MATCHED AND T._PARTITIONTIME = '2018-01-01' THEN
  UPDATE SET COLUMN_ID = S.COLUMN_ID
WHEN MATCHED AND T._PARTITIONTIME = '2018-01-02' THEN
  UPDATE SET COLUMN_ID = S.COLUMN_ID + 10
WHEN NOT MATCHED BY SOURCE AND T._PARTITIONTIME = '2018-01-03' THEN
  DELETE

Au cours de la phase de jointure, seules les partitions suivantes sont analysées dans la table cible : '2018-01-01', '2018-01-02' et '2018-01-03', c'est-à-dire l'union de tous les filtres search_condition.

Dans le plan d'exécution de la requête :

READ
$1:COLUMN_ID, $2:_PARTITIONTIME, $3:$file_temp_id, $4:$row_temp_id
FROM temp.target
WHERE or(equal($2, 1514764800.000000000), equal($2, 1514851200.000000000), equal($2, 1514937600.000000000))

Toutefois, dans l'exemple suivant, la clause WHEN NOT MATCHED BY SOURCE ne contient pas d'expression de filtre :

MERGE dataset.target T
USING dataset.source S
ON T.COLUMN_ID = S.COLUMN_ID
WHEN MATCHED AND T._PARTITIONTIME = '2018-01-01' THEN
  UPDATE SET COLUMN_ID = S.COLUMN_ID
WHEN NOT MATCHED BY SOURCE THEN
  UPDATE SET COLUMN_ID = COLUMN_ID + 1

Cette requête doit analyser l'intégralité de la table cible pour calculer la clause WHEN NOT MATCHED BY SOURCE. Par conséquent, aucune partition n'est restreinte.

Utiliser un faux prédicat constant dans une condition merge_condition

Si vous utilisez à la fois des clauses WHEN NOT MATCHED et WHEN NOT MATCHED BY SOURCE, BigQuery effectue généralement une jointure externe complète, qui ne peut pas être restreinte. Toutefois, si la condition de fusion utilise un faux prédicat constant, BigQuery peut utiliser la condition de filtre pour restreindre la partition. Pour en savoir plus sur l'utilisation de faux prédicats constants, consultez la description de la clause merge_condition dans la documentation de l'instruction MERGE.

L'exemple suivant n'analyse que 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(COLUMN_ID) VALUES(COLUMN_ID)
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. L'optimiseur de requêtes peut être en mesure de transférer le prédicat vers l'étape d'analyse de la table ou non, selon le type de jointure.

Dans l'exemple suivant, la condition merge_condition sert de prédicat pour joindre les tables source et cible. L'optimiseur de requêtes peut transférer ce prédicat lorsqu'il analyse les deux tables. Par conséquent, la requête n'analyse que la partition '2018-01-01' dans les tables cible et source.

MERGE dataset.target T
USING dataset.source S
ON T.COLUMN_ID = S.COLUMN_ID AND
  T._PARTITIONTIME = '2018-01-01' AND
  S._PARTITIONTIME = '2018-01-01'
WHEN MATCHED THEN
  UPDATE SET COLUMN_ID = NEW_VALUE

Dans l'exemple suivant, la condition merge_condition ne contient pas de prédicat pour la table source. Par conséquent, aucune restriction de partition ne peut être effectuée sur cette table. L'instruction contient un prédicat pour la table cible, mais elle utilise une clause WHEN NOT MATCHED BY SOURCE plutôt qu'une clause WHEN MATCHED. Cela signifie que la requête doit analyser l'intégralité de la table cible pour rechercher les lignes qui ne correspondent pas.

MERGE dataset.target T
USING dataset.source S
ON T.COLUMN_ID = S.COLUMN_ID AND T._PARTITIONTIME = '2018-01-01'
WHEN NOT MATCHED BY SOURCE THEN
  UPDATE SET COLUMN_ID = NEW_VALUE

Limites

Pour en savoir plus sur les limites du LMD, consultez la section Limites de la page de référence Langage de manipulation de données.

Quotas

Pour en savoir plus sur les quotas du LMD, consultez la section Instructions LMD de la page Quotas et limites.

Tarifs

Pour en savoir plus sur la tarification du LMD, consultez la section Tarifs LMD pour les tables partitionnées.

Sécurité des tables

Pour savoir comment contrôler l'accès aux tables dans BigQuery, consultez la page Présentation des contrôles d'accès aux tables.

Étapes suivantes