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

GoogleSQL est le nouveau nom du langage SQL standard de Google. Nouveau nom, même dialecte SQL.

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. Pour savoir comment calculer le nombre d'octets traités par une instruction LMD, consultez la section Calcul de la taille des requêtes à la demande.

Tâches simultanées

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

Remarque : Les instructions LMD sont soumises à des limites de débit, telles que le taux maximal d'écritures de table. Vous risquez d'atteindre une limite de débit si vous envoyez simultanément un grand nombre de tâches à une table. Ces taux ne limitent pas le nombre total d'instructions LMD pouvant être exécutées. Si vous recevez un message d'erreur indiquant que vous avez dépassé une limite de débit, relancez l'opération avec un intervalle exponentiel entre les tentatives.

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

LMD précis

Preview

Cette fonctionnalité est soumise aux "Conditions des offres de pré-DG" dans la section "Conditions d'utilisation générales" des Conditions spécifiques au service. Vous pouvez traiter les données à caractère personnel pour cette fonctionnalité comme indiqué dans l'Avenant relatif au traitement des données dans le cloud, sous réserve des obligations et restrictions décrites dans le contrat en vertu duquel vous accédez à Google Cloud. Les fonctionnalités pré-DG sont disponibles "en l'état" et peuvent avoir une assistance limitée. Pour en savoir plus, consultez les descriptions des étapes de lancement.

Remarque :Pour envoyer des commentaires ou demander de l'aide concernant cette fonctionnalité, envoyez un e-mail à l'adresse [e-mail].

La LMD à grain fin 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). Si la LMD précise n'est pas activée, les mutations sont effectuées au niveau du groupe de fichiers, ce qui peut entraîner des réécritures de données inefficaces. La LMD précise introduit une approche plus détaillée visant à réduire la quantité de données à réécrire et à réduire la consommation globale d'emplacements.

Si vous souhaitez inscrire un projet à la version preview du LMD précis, remplissez le formulaire d'inscription au LMD précis BigQuery. Les projets sont inscrits de manière sélective en fonction d'une évaluation de vos charges de travail.

Activer la LMD précise

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

Pour créer une table avec une LMD précise, utilisez l'instruction CREATE TABLE:

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

Pour modifier une table existante à l'aide d'une instruction LMD précise, utilisez l'instruction ALTER TABLE:

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

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 LMD précises activées et utilisent la syntaxe des instructions LMD existante.

Pour désactiver la LMD précise sur une table, définissez enable_fine_grained_mutations sur FALSE à l'aide de l'instruction LDD ALTER TABLE.

Tarifs

L'activation de la LMD précise pour une table peut entraîner des coûts de stockage BigQuery supplémentaires pour stocker les métadonnées de mutation supplémentaires associées aux opérations de LMD précise. Le coût réel dépend de la quantité de données modifiées, mais dans la plupart des cas, il devrait être négligeable par rapport à la taille de la table elle-même.

Les projets configurés pour utiliser des reservations utilisent des emplacements pour traiter des instructions LMD précises, y compris tout traitement en arrière-plan des métadonnées de table ou de mutation.

Remarques concernant les données supprimées

Les opérations LMD précises traitent les données supprimées de manière hors connexion.

Les projets effectuant des opérations LMD précises sans processus d'attribution BACKGROUND suppriment les données à l'aide de la tarification à la demande. Dans ce cas, le traitement des données supprimées est effectué régulièrement à l'aide de ressources BigQuery internes.

Les projets effectuant des opérations LMD précises avec une attribution BACKGROUND traitent les données supprimées à l'aide d'emplacements et sont 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 données supprimées peut prendre plus de temps que prévu.

Limites

Les tables activées avec la LMD précise sont soumises aux limites suivantes:

• Vous ne pouvez pas utiliser la méthode tabledata.list pour lire le contenu d'une table avec le langage LMD précis activé. Utilisez plutôt l'API Storage Read pour lire les enregistrements de table à l'aide d'une API.
• Vous ne pouvez pas créer d'instantané de table ni de clone de table d'une table avec la LMD précise activée.
• Vous ne pouvez pas activer la LMD précise 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 avec la LMD précise activée.
• Les instructions LMD exécutées dans une transaction multi-instructions ne sont pas optimisées avec la LMD précise.

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.

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.

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