Mettre à jour des données de table à l'aide du langage de manipulation de données

Cette page explique comment mettre à jour et supprimer des données dans les tables BigQuery à l'aide du langage de manipulation de données (LMD). Elle ne couvre pas l'ajout de lignes aux tables existantes avec le LMD. Pour plus d'informations à ce sujet, consultez l'instruction INSERT dans la documentation de référence concernant la syntaxe LMD.

Notez que le LMD de BigQuery présente quelques limites. Le langage LMD dispose également de sa propre tarification.

Remarque : Vous devez utiliser le langage SQL standard pour faire fonctionner le LMD. Pour activer le SQL standard, consultez la section Changer de dialecte SQL.

Mettre à jour des données

Suivez les instructions ci-dessous en utilisant cet exemple de fichier. Il présente une table comportant une colonne d'adresses IP que vous souhaitez masquer pour l'anonymisation :

• userSessionsData.json (télécharger)

Les étapes suivantes permettent de charger les exemples de données dans une table et de mettre à jour les valeurs de la colonne ip_address :

Étape 1. Chargez le fichier JSON dans une table UserSessions.

Étape 2 : Pour masquer le dernier octet de la colonne ip_address de chaque ligne, exécutez la requête LMD ci-dessous :

UPDATE sample_db.UserSessions
SET ip_address = REGEXP_REPLACE(ip_address, r"(\.[0-9]+)$", ".0")
WHERE TRUE

Java

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Java décrite dans le guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery Java.

Afficher sur GitHub Commentaires

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.FormatOptions;
import com.google.cloud.bigquery.Job;
import com.google.cloud.bigquery.JobId;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.TableDataWriteChannel;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableResult;
import com.google.cloud.bigquery.WriteChannelConfiguration;
import java.io.IOException;
import java.io.OutputStream;
import java.nio.channels.Channels;
import java.nio.file.FileSystems;
import java.nio.file.Files;
import java.nio.file.Path;
import java.util.UUID;

// Sample to update data in BigQuery tables using DML query
public class UpdateTableDml {

  public static void main(String[] args) throws IOException, InterruptedException {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    updateTableDml(datasetName, tableName);
  }

  public static void updateTableDml(String datasetName, String tableName)
      throws IOException, InterruptedException {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      // Load JSON file into UserSessions table
      TableId tableId = TableId.of(datasetName, tableName);

      WriteChannelConfiguration writeChannelConfiguration =
          WriteChannelConfiguration.newBuilder(tableId)
              .setFormatOptions(FormatOptions.json())
              .build();

      // Imports a local JSON file into a table.
      Path jsonPath =
          FileSystems.getDefault().getPath("src/test/resources", "userSessionsData.json");

      // The location and JobName must be specified; other fields can be auto-detected.
      String jobName = "jobId_" + UUID.randomUUID().toString();
      JobId jobId = JobId.newBuilder().setLocation("us").setJob(jobName).build();

      try (TableDataWriteChannel writer = bigquery.writer(jobId, writeChannelConfiguration);
          OutputStream stream = Channels.newOutputStream(writer)) {
        Files.copy(jsonPath, stream);
      }

      // Get the Job created by the TableDataWriteChannel and wait for it to complete.
      Job job = bigquery.getJob(jobId);
      Job completedJob = job.waitFor();
      if (completedJob == null) {
        System.out.println("Job not executed since it no longer exists.");
        return;
      } else if (completedJob.getStatus().getError() != null) {
        System.out.println(
            "BigQuery was unable to load local file to the table due to an error: \n"
                + job.getStatus().getError());
        return;
      }

      System.out.println(
          job.getStatistics().toString() + " userSessionsData json uploaded successfully");

      // Write a DML query to modify UserSessions table
      // To create DML query job to mask the last octet in every row's ip_address column
      String dmlQuery =
          String.format(
              "UPDATE `%s.%s` \n"
                  + "SET ip_address = REGEXP_REPLACE(ip_address, r\"(\\.[0-9]+)$\", \".0\")\n"
                  + "WHERE TRUE",
              datasetName, tableName);

      QueryJobConfiguration dmlQueryConfig = QueryJobConfiguration.newBuilder(dmlQuery).build();

      // Execute the query.
      TableResult result = bigquery.query(dmlQueryConfig);

      // Print the results.
      result.iterateAll().forEach(rows -> rows.forEach(row -> System.out.println(row.getValue())));

      System.out.println("Table updated successfully using DML");
    } catch (BigQueryException e) {
      System.out.println("Table update failed \n" + e.toString());
    }
  }
}

Python

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Python décrite dans le guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery Python.

Afficher sur GitHub Commentaires

import pathlib
from typing import Dict, Optional

from google.cloud import bigquery
from google.cloud.bigquery import enums

def load_from_newline_delimited_json(
    client: bigquery.Client,
    filepath: pathlib.Path,
    project_id: str,
    dataset_id: str,
    table_id: str,
) -> None:
    full_table_id = f"{project_id}.{dataset_id}.{table_id}"
    job_config = bigquery.LoadJobConfig()
    job_config.source_format = enums.SourceFormat.NEWLINE_DELIMITED_JSON
    job_config.schema = [
        bigquery.SchemaField("id", enums.SqlTypeNames.STRING),
        bigquery.SchemaField("user_id", enums.SqlTypeNames.INTEGER),
        bigquery.SchemaField("login_time", enums.SqlTypeNames.TIMESTAMP),
        bigquery.SchemaField("logout_time", enums.SqlTypeNames.TIMESTAMP),
        bigquery.SchemaField("ip_address", enums.SqlTypeNames.STRING),
    ]

    with open(filepath, "rb") as json_file:
        load_job = client.load_table_from_file(
            json_file, full_table_id, job_config=job_config
        )

    # Wait for load job to finish.
    load_job.result()

def update_with_dml(
    client: bigquery.Client, project_id: str, dataset_id: str, table_id: str
) -> int:
    query_text = f"""
    UPDATE `{project_id}.{dataset_id}.{table_id}`
    SET ip_address = REGEXP_REPLACE(ip_address, r"(\\.[0-9]+)$", ".0")
    WHERE TRUE
    """
    query_job = client.query(query_text)

    # Wait for query job to finish.
    query_job.result()

    assert query_job.num_dml_affected_rows is not None

    print(f"DML query modified {query_job.num_dml_affected_rows} rows.")
    return query_job.num_dml_affected_rows

def run_sample(override_values: Optional[Dict[str, str]] = None) -> int:
    if override_values is None:
        override_values = {}

    client = bigquery.Client()
    filepath = pathlib.Path(__file__).parent / "user_sessions_data.json"
    project_id = client.project
    dataset_id = "sample_db"
    table_id = "UserSessions"
    load_from_newline_delimited_json(client, filepath, project_id, dataset_id, table_id)
    return update_with_dml(client, project_id, dataset_id, table_id)

Supprimer des données

Suivez les instructions ci-dessous en utilisant les exemples de fichiers. Ils présentent un ensemble de données comportant plusieurs tables de données analytiques concernant les sessions d'utilisateur et une table d'utilisateurs à supprimer.

• deletedUsersData.json (télécharger)
• usersData.json (télécharger)
• userSessionsData.json (télécharger)

Les étapes suivantes permettent de charger les données dans trois tables, puis de supprimer les utilisateurs répertoriés dans la table DeletedUsers.

Étape 1. Chargez les fichiers JSON dans les tables DeletedUsers, Users et UserSessions.

Console

1. Ouvrez Cloud Console.

2. Dans le panneau Explorateur, développez votre projet et sélectionnez un ensemble de données.

3. Développez l'option Actions puis cliquez sur Ouvrir.

4. Dans le panneau de détails, cliquez sur Créer une table.

5. Dans le champ Create table from (Créer une table à partir de), sélectionnez Upload (Importer).

6. Dans le champ Select file (Sélectionner un fichier), recherchez et sélectionnez le fichier que vous avez téléchargé.

   

7. Dans le champ Format de fichier, sélectionnez JSON (délimité par un retour à la ligne).

8. Choisissez un nom de table approprié.

9. Sous Schéma, cliquez sur Ajouter un champ et saisissez un nom pour chaque colonne du tableau, puis sélectionnez le type approprié.

   • Cliquez sur Ajouter un champ, et continuez jusqu'à ce que vous ayez saisi toutes les colonnes de votre table.

10. Cliquez sur Créer une table.

Les schémas des exemples de tables sont les suivants :

• DeletedUsers
  • Nom : id ; Type : INTEGER
• Users
  • Nom : id ; Type : INTEGER
  • Nom : date_joined ; Type : TIMESTAMP
• UserSessions
  • Nom : id ; Type : STRING
  • Nom : user_id ; Type : INTEGER
  • Nom : login_time ; Type : TIMESTAMP
  • Nom : logout_time ; Type : TIMESTAMP
  • Nom : ip_address ; Type : STRING

bq

Pour créer les tables à l'aide de l'outil de ligne de commande bq, utilisez la commande bq load. Spécifiez l'option --location et définissez la valeur correspondant à votre emplacement. L'option --location est facultative. Par exemple, si vous utilisez BigQuery dans la région asia-northeast1 (Tokyo), la commande de charge se présentera comme suit :

bq --location=asia-northeast1 load ...

Pour créer la table DeleteUsers, procédez comme suit :

bq --location=asia-northeast1 load \
--source_format=NEWLINE_DELIMITED_JSON \
sample_db.DeletedUsers \
deletedUsersData.json \
id:integer

Pour créer la table Users, procédez comme suit :

bq --location=asia-northeast1 load \
--source_format=NEWLINE_DELIMITED_JSON \
sample_db.Users \
usersData.json \
id:integer,date_joined:timestamp

Pour créer la table UserSessions, procédez comme suit :

bq --location=asia-northeast1 load \
--source_format=NEWLINE_DELIMITED_JSON \
sample_db.UserSessions \
userSessionsData.json \
id:string,user_id:integer,login_time:timestamp,logout_time:timestamp,ip_address:string

Étape 2 : Pour supprimer les informations sur les utilisateurs dans la table DeletedUsers, exécutez les requêtes LMD ci-dessous :

• Supprimer de UsersSessions

  DELETE FROM sample_db.UserSessions
  WHERE user_id in (SELECT id from sample_db.DeletedUsers)

• Supprimer de Users

  DELETE FROM sample_db.Users
  WHERE id in (SELECT id from sample_db.DeletedUsers)

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.

Étape suivante

• Consultez la page de référence sur le langage LMD.
• Reportez-vous à la page concernant la syntaxe LMD pour en savoir plus sur la syntaxe LMD et découvrir des exemples.
• Découvrez les transactions contenant plusieurs instructions, qui vous permettent d'exécuter plusieurs instructions LMD et de valider les résultats de manière atomique.