Transformer des données avec le langage de manipulation de données (LMD)

Le langage de manipulation de données (LMD) de BigQuery vous permet de mettre à jour, d'insérer et de supprimer des données dans vos tables BigQuery.

Vous pouvez exécuter des instructions LMD comme vous le feriez pour une instruction SELECT, avec les conditions suivantes :

  • Vous devez utiliser GoogleSQL. Pour activer GoogleSQL, consultez la section Changer de dialecte SQL.
  • Vous ne pouvez pas spécifier de table de destination pour la requête.

Pour en savoir plus sur le calcul du nombre d'octets traités par une instruction LMD, consultez la section Calcul de la taille des requêtes à la demande.

Limites

  • Chaque instruction LMD initie une transaction implicite, ce qui signifie que les modifications apportées par l'instruction sont automatiquement validées à la fin de chaque instruction LMD réussie.

  • Les lignes récemment écrites à l'aide de la méthode de diffusion tabledata.insertall ne peuvent pas être modifiées avec le langage de manipulation de données, à l'aide des instructions UPDATE, DELETE, MERGE ou TRUNCATE par exemple. Les écritures récentes sont celles qui se sont produites au cours des 30 dernières minutes. Vous pouvez modifier toutes les autres lignes de la table à l'aide des instructions UPDATE, DELETE, MERGE ou TRUNCATE. La disponibilité des données diffusées pour les opérations de copie peut prendre jusqu'à 90 minutes.

    Vous pouvez également modifier les lignes récemment écrites à l'aide de l'API Storage Write avec les instructions UPDATE, DELETE ou MERGE. Pour en savoir plus, consultez Utiliser le langage de manipulation de données (LMD) avec des données récemment diffusées.

  • Les sous-requêtes liées dans when_clause, search_condition, merge_update_clause ou merge_insert_clause ne sont pas compatibles avec les instructions MERGE.

  • Les requêtes contenant des instructions LMD ne peuvent pas utiliser une table générique comme cible de la requête. Par exemple, une table générique peut être utilisée dans la clause FROM d'une requête UPDATE, mais une table générique ne peut pas être utilisée comme cible de l'opération UPDATE.

Instructions LMD

Les sections suivantes décrivent les différents types d'instructions LMD et comment les utiliser.

Instruction INSERT

Utilisez l'instruction INSERT pour ajouter des lignes à une table existante. L'exemple suivant insère de nouvelles lignes dans la table dataset.Inventory avec des valeurs spécifiées explicitement.

INSERT dataset.Inventory (product, quantity)
VALUES('whole milk', 10),
      ('almond milk', 20),
      ('coffee beans', 30),
      ('sugar', 0),
      ('matcha', 20),
      ('oat milk', 30),
      ('chai', 5)

/+-------------------+----------+
 |      product      | quantity |
 +-------------------+----------+
 | almond milk       |       20 |
 | chai              |        5 |
 | coffee beans      |       30 |
 | matcha            |       20 |
 | oat milk          |       30 |
 | sugar             |        0 |
 | whole milk        |       10 |
 +-------------------+----------+/

Pour en savoir plus sur les instructions INSERT, consultez la section INSERT instruction.

Instruction DELETE

Utilisez l'instruction DELETE pour supprimer des lignes dans une table. L'exemple suivant supprime toutes les lignes de la table dataset.Inventory dont la valeur quantity est 0.

DELETE dataset.Inventory
WHERE quantity = 0

/+-------------------+----------+
 |      product      | quantity |
 +-------------------+----------+
 | almond milk       |       20 |
 | chai              |        5 |
 | coffee beans      |       30 |
 | matcha            |       20 |
 | oat milk          |       30 |
 | whole milk        |       10 |
 +-------------------+----------+/

Pour supprimer toutes les lignes d'une table, utilisez plutôt l'instruction TRUNCATE TABLE. Pour en savoir plus sur les instructions DELETE, consultez la section Instruction DELETE.

Instruction TRUNCATE

Utilisez l'instruction TRUNCATE pour supprimer toutes les lignes d'une table, mais conservez les métadonnées de cette table, y compris son schéma, sa description et ses libellés. L'exemple suivant supprime toutes les lignes de la table dataset.Inventory.

TRUNCATE dataset.Inventory

Pour supprimer des lignes spécifiques dans une table, utilisez plutôt l'instruction DELETE. Pour en savoir plus sur l'instruction TRUNCATE, consultez la section Instruction TRUNCATE.

Instruction UPDATE

Utilisez l'instruction UPDATE pour mettre à jour les lignes existantes d'une table. L'instruction UPDATE doit également inclure le mot clé WHERE pour spécifier une condition. L'exemple suivant réduit de 10 la valeur quantity des lignes pour les produits contenant la chaîne milk.

UPDATE dataset.Inventory
SET quantity = quantity - 10,
WHERE product LIKE '%milk%'

/+-------------------+----------+
 |      product      | quantity |
 +-------------------+----------+
 | almond milk       |       10 |
 | chai              |        5 |
 | coffee beans      |       30 |
 | matcha            |       20 |
 | oat milk          |       20 |
 | whole milk        |        0 |
 +-------------------+----------+/

Les instructions UPDATE peuvent également inclure des clauses FROM pour inclure des tables jointes. Pour en savoir plus sur les instructions UPDATE, consultez la section UPDATE instruction.

Instruction MERGE

L'instruction MERGE combine les opérations INSERT, UPDATE et DELETE en une seule instruction et les exécute de manière atomique pour fusionner les données d'une table dans une autre. Pour en savoir plus et voir des exemples sur l'MERGE instruction, consultez la section MERGE instruction.

Tâches simultanées

BigQuery gère la simultanéité des instructions LMD qui ajoutent, modifient ou suppriment des lignes dans une table.

Instructions LMD INSERT simultanées

Au cours d'une période de 24 heures, les 1 500 premières instructions INSERT s'exécutent immédiatement après leur envoi. Une fois cette limite atteinte, les instructions INSERT simultanées qui écrivent dans une table sont limitées à 10. Des instructions INSERT supplémentaires sont ajoutées à une file d'attente PENDING. Jusqu'à 100 instructions INSERT peuvent être placées en file d'attente sur une table à tout moment. Lorsqu'une instruction INSERT se termine, l'instruction INSERT suivante est supprimée de la file d'attente et exécutée.

Si vous devez exécuter des instructions DML INSERT plus fréquemment, envisagez de diffuser des données dans votre table à l'aide de l'API Storage Write.

Instructions LMD UPDATE, DELETE et MERGE simultanées

Les instructions LMD UPDATE, DELETE et MERGE sont appelées des instructions LMD en mutation. Si vous envoyez une ou plusieurs instructions LMD en mutation sur une table alors que d'autres tâches LMD en mutation y sont toujours en cours d'exécution (ou en attente), BigQuery en exécute jusqu'à deux simultanément, après quoi jusqu'à 20 instructions sont placées en file d'attente à l'état PENDING. Une fois l'exécution de la tâche précédente terminée, la tâche suivante en attente est retirée de la file d'attente et exécutée. Les instructions LMD en mutation placées en file d'attente partagent une file d'attente par table, d'une longueur maximale de 20. Les instructions supplémentaires, au-delà de la longueur maximale de file d'attente définie pour chaque table, échouent avec le message d'erreur suivant : Resources exceeded during query execution: Too many DML statements outstanding against table PROJECT_ID:DATASET.TABLE, limit is 20.

Les jobs LMD interactifs prioritaires qui sont placés dans la file d'attente pendant plus de sept heures échouent avec le message d'erreur suivant :

DML statement has been queued for too long

Conflits des instruction LMD

L'exécution simultanée d'instructions LMD en mutation sur une table génère des conflits entre les instructions LMD lorsque celles-ci tentent de muter la même partition. Les instructions aboutissent tant qu'elles ne modifient pas la même partition. BigQuery tente de réexécuter jusqu'à trois fois les instructions ayant échoué.

  • Une instruction LMD INSERT qui insère des lignes dans une table n'entre pas en conflit avec une autre instruction LMD exécutée simultanément.

  • Une instruction LMD MERGE n'entre pas en conflit avec d'autres instructions LMD exécutées simultanément tant que l'instruction insère uniquement des lignes et ne supprime ni ne met à jour les lignes existantes. Cela peut inclure des instructions MERGE avec des clauses UPDATE ou DELETE, à condition que celles-ci ne soient pas appelées lors de l'exécution de la requête.

LMD ultraprécis

Le LMD ultraprécis est une amélioration des performances conçue pour optimiser l'exécution des instructions UPDATE, DELETE et MERGE (également appelées instructions LMD en mutation).

Considérations sur les performances

Sans le LMD ultraprécis, les mutations LMD sont effectuées au niveau du groupe de fichiers, ce qui peut entraîner des réécritures de données inefficaces, en particulier pour les mutations éparses. Cela peut entraîner une consommation d'emplacements supplémentaire et des temps d'exécution plus longs.

Le LMD ultraprécis est une amélioration des performances conçue pour optimiser ces instructions LMD en mutation en introduisant une approche plus précise qui vise à réduire la quantité de données à réécrire au niveau du groupe de fichiers. Cette approche peut réduire considérablement le temps de traitement, les E/S et le temps d'emplacement consommés pour les tâches LMD en mutation.

Voici quelques considérations sur les performances à prendre en compte lors de l'utilisation du LMD ultraprécis :

  • Les opérations LMD ultraprécises traitent les données supprimées selon une approche hybride qui répartit les coûts de réécriture sur de nombreuses mutations de table. Chaque opération LMD peut traiter une partie des données supprimées, puis décharger le traitement des données supprimées restantes vers un processus de récupération de mémoire en arrière-plan. Pour en savoir plus, consultez la section Remarques concernant les données supprimées.
  • Les tables avec des opérations LMD en mutation fréquentes peuvent subir une latence accrue pour les requêtes SELECT et les tâches LMD suivantes. Pour évaluer l'impact de l'activation de cette fonctionnalité, comparez les performances d'une séquence réaliste d'opérations LMD et de lectures ultérieures.
  • L'activation du LMD ultraprécis ne réduit pas la quantité d'octets analysés de l'instruction LMD en mutation elle-même.

Activer le LMD ultraprécis

Pour activer le LMD ultraprécis, définissez l' enable_fine_grained_mutations option de table sur TRUE lorsque vous exécutez une instruction LDD CREATE TABLE ou ALTER TABLE.

Pour créer une table avec le LMD ultraprécis, utilisez l' CREATE TABLEinstruction :

CREATE TABLE mydataset.mytable (
  product STRING,
  inventory INT64)
OPTIONS(enable_fine_grained_mutations = TRUE);

Pour modifier une table existante avec le LMD ultraprécis, utilisez l' ALTER TABLE instruction :

ALTER TABLE mydataset.mytable
SET OPTIONS(enable_fine_grained_mutations = TRUE);

Pour modifier toutes les tables existantes d'un ensemble de données avec le LMD ultraprécis, utilisez l' ALTER TABLE instruction : 

FOR record IN
 (SELECT CONCAT(table_schema, '.', table_name) AS table_path
 FROM mydataset.INFORMATION_SCHEMA.TABLES)
DO
 EXECUTE IMMEDIATE
   "ALTER TABLE " || record.table_path || " SET OPTIONS(enable_fine_grained_mutations = TRUE)";
END FOR;

Une fois l'option enable_fine_grained_mutations définie sur TRUE, les instructions LMD en mutation sont exécutées avec les fonctionnalités du LMD ultraprécis activées et utilisent la syntaxe d'instruction LMD existante .

Pour déterminer si une table a été activée avec le LMD ultraprécis, interrogez la INFORMATION_SCHEMA.TABLES vue. L'exemple suivant vérifie quelles tables d'un ensemble de données ont été activées avec cette fonctionnalité :

SELECT
  table_schema AS datasetId,
  table_name AS tableId,
  is_fine_grained_mutations_enabled
FROM
  DATASET_NAME.INFORMATION_SCHEMA.TABLES;

Remplacez DATASET_NAME par le nom de l'ensemble de données dans lequel vérifier si des tables ont le LMD ultraprécis activé.

Désactiver le LMD ultraprécis

Pour désactiver le LMD ultraprécis d'une table existante, utilisez l'instruction ALTER TABLE.

ALTER TABLE mydataset.mytable
SET OPTIONS(enable_fine_grained_mutations = FALSE);

Lorsque vous désactivez le LMD ultraprécis, le traitement complet de toutes les données supprimées peut prendre un certain temps. Consultez la section Remarques concernant les données supprimées. Par conséquent, les limites du LMD ultraprécis peuvent persister jusqu'à ce que cela se produise.

Tarifs

L'activation du LMD ultraprécis pour une table peut entraîner des coûts supplémentaires. Ces coûts incluent les éléments suivants :

Vous pouvez utiliser des réservations BigQuery pour allouer des ressources de calcul BigQuery dédiées au traitement des tâches de données supprimées déchargées. Les réservations vous permettent de définir un plafond du coût d'exécution de ces opérations. Cette approche est particulièrement utile, et souvent recommandée, pour les très grandes tables avec des opérations LMD en mutation ultraprécises fréquentes, qui entraîneraient des coûts à la demande élevés en raison du grand nombre d'octets traités lors de l'exécution de chaque tâche de traitement des données supprimées déchargées.

Les tâches de traitement des données supprimées déchargées du LMD ultraprécis sont considérées comme des tâches en arrière-plan et nécessitent l'utilisation du BACKGROUND type d'attribution de réservation, plutôt que du QUERY type d'attribution de réservation. Les projets qui effectuent des opérations LMD ultraprécises sans attribution BACKGROUND utilisent la tarification à la demande pour traiter les tâches de données supprimées déchargées.

Opération Tarifs à la demande Tarifs en fonction de la capacité
Instructions LMD en mutation Utilisez le dimensionnement LMD standard pour déterminer les calculs des octets analysés à la demande.

L'activation du LMD ultraprécis ne réduit pas la quantité d'octets analysés de l' instruction LMD elle-même.

 Consommez les emplacements attribués avec un type QUERY lors de l'exécution de l'instruction.
Tâches de traitement des données supprimées déchargées Utilisez le dimensionnement LMD standard pour déterminer les calculs des octets analysés à la demande lorsque les tâches de traitement des données supprimées sont exécutées. Consommez les emplacements attribués avec un BACKGROUND type lorsque les tâches de traitement des données supprimées sont exécutées.

Remarques concernant les données supprimées

Les opérations LMD ultraprécises utilisent une approche hybride pour gérer les données supprimées, en combinant le traitement en ligne avec la récupération de mémoire déchargée afin de répartir les coûts de réécriture et d'optimiser les performances sur plusieurs instructions LMD en mutation émises sur une table.

Lors de l'exécution d'une instruction LMD en mutation, BigQuery tente d'effectuer une partie de la récupération de mémoire pertinente à partir des instructions LMD précédentes en ligne. Toutes les données supprimées qui ne sont pas gérées en ligne sont déchargées vers un processus en arrière-plan pour un nettoyage ultérieur.

Les projets qui effectuent des opérations LMD ultraprécises avec une attribution BACKGROUND traitent les tâches de récupération de mémoire déchargées à l'aide d'emplacements. Le traitement des données supprimées est soumis à la disponibilité des ressources de la réservation configurée. Si les ressources disponibles dans la réservation configurée sont insuffisantes, le traitement des opérations de récupération de mémoire déchargées peut prendre plus de temps que prévu.

Les projets qui effectuent des opérations LMD ultraprécises en utilisant la tarification à la demande ou sans attribution BACKGROUND traitent les tâches de récupération de mémoire déchargées à l'aide de ressources BigQuery internes et sont facturés aux tarifs à la demande. Pour en savoir plus, reportez-vous à la page Tarifs.

Le calendrier des tâches de récupération de mémoire déchargées est déterminé par la fréquence de l'activité LMD sur la table et la disponibilité des ressources, si vous utilisez une attribution BACKGROUND :

  • Pour les tables avec des opérations LMD en mutation continues, chaque LMD traite une partie de la charge de travail de récupération de mémoire afin de garantir des performances de lecture et d'écriture cohérentes. Par conséquent, la récupération de mémoire est traitée régulièrement lors de l'exécution des LMD suivants.
  • Si aucune activité LMD ultérieure ne se produit sur une table, la récupération de mémoire déchargée est automatiquement déclenchée une fois que les données supprimées ont atteint 5 jours.
  • Dans de rares cas, le traitement complet des données supprimées peut prendre plus de temps.

Pour identifier les tâches de traitement des données supprimées du LMD ultraprécis déchargées, interrogez la INFORMATION_SCHEMA.JOBS vue : 

SELECT
  *
FROM
  region-us.INFORMATION_SCHEMA.JOBS
WHERE
  job_id LIKE "%fine_grained_mutation_garbage_collection%"

Limites

Les tables activées avec le LMD ultraprécis sont soumises aux limites suivantes :

  • Pour les grandes tables avec des partitions fréquemment mutées dépassant 2 To, le LMD ultraprécis n'est pas recommandé. Ces tables peuvent subir une pression de mémoire supplémentaire pour les requêtes suivantes, ce qui peut entraîner une latence de lecture supplémentaire ou des erreurs de requête.
  • Une seule instruction LMD en mutation peut s'exécuter à la fois sur une table pour laquelle le LMD ultraprécis est activé. Les tâches suivantes sont mises en file d'attente à l'état PENDING. Pour en savoir plus sur le comportement simultané du LMD en mutation, consultez la section Instructions LMD UPDATE, DELETE et MERGE simultanées.
  • Une table activée avec le LMD ultraprécis ne peut pas avoir de partitions supprimées individuellement ou remplacées. Pour supprimer ou remplacer des données dans une partition, vous devez utiliser une instruction LMD en mutation, telle que DELETE, UPDATE, MERGE ou TRUNCATE.
  • Vous ne pouvez pas utiliser la tabledata.list méthode pour lire le contenu d'une table pour laquelle le LMD ultraprécis est activé. Interrogez plutôt la table avec une instruction SELECT pour lire les enregistrements de la table.
  • Vous ne pouvez pas prévisualiser une table pour laquelle le LMD ultraprécis est activé à l'aide de la console BigQuery.
  • Vous ne pouvez pas copier une table pour laquelle le LMD ultraprécis est activé après avoir exécuté une instruction UPDATE, DELETE ou MERGE.
  • Vous ne pouvez pas créer d'instantané de table ni de clone de table pour une table pour laquelle le LMD ultraprécis est activé après avoir exécuté une instruction UPDATE, DELETE ou MERGE.
  • Vous ne pouvez pas activer le LMD ultraprécis sur une table dans un ensemble de données répliqué, et vous ne pouvez pas répliquer un ensemble de données contenant une table pour laquelle le LMD ultraprécis est activé.
  • Les instructions LMD exécutées dans une transaction multi-instructions ne sont pas optimisées avec le LMD ultraprécis.
  • Vous ne pouvez pas activer le LMD ultraprécis sur les tables temporaires créées avec l'instruction CREATE TEMP TABLE.
  • Les métadonnées reflétées dans les INFORMATION_SCHEMA.TABLE_STORAGE vues et INFORMATION_SCHEMA.PARTITIONS vues peuvent inclure temporairement des données récemment supprimées à l'aide du LMD ultraprécis jusqu'à ce que les tâches de récupération de mémoire en arrière-plan soient terminées.

Bonnes pratiques

Pour des performances optimales, Google recommande de respecter les préconisations suivantes :

  • Évitez d'envoyer un grand nombre de mises à jour ou d'insertions de lignes individuelles. À la place, regroupez les opérations LMD lorsque cela est possible. Pour plus d'informations, consultez la section Instructions LMD qui mettent à jour ou insèrent des lignes uniques.

  • Si des mises à jour ou des suppressions se produisent généralement sur des données plus anciennes ou dans une plage de dates donnée, envisagez de partitionner vos tables. Le partitionnement garantit que les modifications sont limitées à des partitions spécifiques de la table.

  • Évitez de partitionner les tables si la quantité de données dans chaque partition est petite et que chaque mise à jour modifie une grande partie des partitions.

  • Si vous mettez souvent à jour des lignes dans lesquelles une ou plusieurs colonnes sont comprises dans une plage de valeurs étroite, pensez à utiliser des tables en cluster. Le clustering garantit que les modifications sont limitées à des ensembles spécifiques de blocs, ce qui réduit la quantité de données à lire et à écrire. Voici un exemple d'instruction UPDATE qui filtre sur une plage de valeurs de colonne :

    UPDATE mydataset.mytable
SET string_col = 'some string'
WHERE id BETWEEN 54 AND 75;

    Voici un exemple similaire qui filtre sur une petite liste de valeurs de colonnes :

    UPDATE mydataset.mytable
SET string_col = 'some string'
WHERE id IN (54, 57, 60);

    Envisagez de mettre en cluster la colonne id dans ces cas de figure.

  • Si vous avez besoin des fonctionnalités OLTP, envisagez d'utiliser des requêtes fédérées Cloud SQL, qui permettent à BigQuery d'interroger les données résidant dans Cloud SQL.

  • Pour résoudre et éviter l'erreur de quota Too many DML statements outstanding against table, suivez les conseils concernant cette erreur sur la page Dépannage de BigQuery.

Pour découvrir les bonnes pratiques d'optimisation des performances des requêtes, consultez la page Présentation de l'optimisation des performances des requêtes.

Étape suivante