Langage de manipulation de données

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 le langage SQL standard. Pour l'activer, reportez-vous à la section Changer de dialecte SQL.
  • Vous ne pouvez pas spécifier de table de destination. Par exemple, dans Cloud Console, la table de destination doit être définie sur Aucune table sélectionnée.

    Cloud Console n'affichant aucune table de destination sélectionnée.

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 transactions contenant plusieurs instructions ne sont pas acceptées.

  • Les lignes qui ont été écrites récemment dans une table à l'aide de la diffusion par flux (avec la méthode tabledata.insertall ou l'API Storage Write) ne peuvent pas être modifiées avec les instructions UPDATE, DELETE ou MERGE. Les écritures dites récentes sont généralement celles des 30 dernières minutes. Notez que vous pouvez modifier toutes les autres lignes de la table à l'aide des instructions UPDATE, DELETE ou MERGE.

  • 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.

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 insérées (INSERT) dans une table s'exécutent simultanément. Une fois cette limite atteinte, les instructions INSERT simultanées qui écrivent dans une table sont limitées à 10. Au-delà de la dixième tâche, toutes les tâches LMD INSERT sont placées en file d'attente à l'état PENDING (en attente). Une fois 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. Actuellement, jusqu'à 100 instructions LMD INSERT peuvent être placées en file d'attente sur une table à tout moment.

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. Actuellement, 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.

Les tâches LMD interactives prioritaires qui sont placées dans la file d'attente pendant plus de six heures échouent.

Conflits des instructions LMD

L'exécution simultanée d'instructions LMD en mutation sur une table peut échouer en raison de conflits dus aux modifications apportées. BigQuery retente ces exécutions en échec.

  • 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 qui ne contient qu'une clause INSERT sans clause UPDATE ou DELETE n'entre pas en conflit avec une autre instruction LMD exécutée simultanément.

  • Une instruction LMD MERGE avec des clauses UPDATE ou DELETE n'entre pas en conflit avec d'autres instructions LMD exécutées simultanément tant que l'instruction MERGE ne conduit pas à la mise à jour ou à la suppression de lignes existantes. Par exemple, une instruction MERGE qui n'insère que les nouvelles lignes n'entre pas en conflit avec d'autres instructions LMD exécutées simultanément.

Tarifs

Pour plus d'informations sur la tarification du LMD, consultez la section Tarifs pour les requêtes LMD de la page Tarifs.

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 s = "some string" WHERE id BETWEEN 54 AND 75
    

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

    UPDATE s = "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.

Étape suivante

  • Pour obtenir des informations et des exemples sur la syntaxe LMD, consultez la page Syntaxe LMD.
  • Pour plus d'informations sur l'utilisation des instructions LMD dans les requêtes programmées, consultez la page Planifier des requêtes.