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 obtenir la liste des instructions LMD BigQuery et des exemples d'utilisation, consultez la section Instructions de langage de manipulation de données en langage GoogleSQL.

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 (preview). 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.

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.

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:TABLE, limit is 20.

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

DML statement has been queued for too long

Conflits des instructions 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.

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

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.