Actualiza datos de tablas con el lenguaje de manipulación de datos

En esta página, se ofrece información sobre cómo actualizar y borrar datos en las tablas de BigQuery mediante el lenguaje de manipulación de datos (DML). Sin embargo, no se explica cómo agregar filas a tablas existentes mediante DML. Si deseas obtener información sobre cómo agregar filas mediante DML, consulta Declaración INSERT en la referencia de sintaxis de DML.

Ten en cuenta que el uso de DML en BigQuery tiene algunas limitaciones. DML también cuenta con sus propios precios.

Actualiza datos

Sigue las instrucciones que aparecen a continuación y usa este archivo de muestra, que representa una tabla con una columna de direcciones IP que te gustaría enmascarar para su anonimización.

userSessionsData.json (descargar)

En los siguientes pasos, se cargan los datos de muestra en una tabla y se actualizan los valores en la columna ip_address:

Paso 1. Carga el archivo JSON en una tabla UserSessions.

Paso 2. Para enmascarar el último octeto de la columna ip_address de cada fila, ejecuta la siguiente consulta de DML:

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

Java

Antes de probar este ejemplo, sigue las instrucciones de configuración para Java que se encuentran en la guía de inicio rápido de BigQuery sobre cómo usar bibliotecas cliente. Para obtener más información, consulta la documentación de referencia de la API de BigQuery para Java.

Ver en GitHub Comentarios

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 runUpdateTableDML() 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());
    }
  }
}

Borra datos

Sigue las instrucciones que aparecen a continuación y usa los archivos de muestra, que representan un conjunto de datos con múltiples tablas de estadísticas sobre sesiones de usuario y una tabla de usuarios que deben borrarse.

En los siguientes pasos, se cargan los datos en tres tablas y, luego, se borran los usuarios enumerados en la tabla DeletedUsers.

Paso 1. Carga los archivos JSON en las tablas DeletedUsers, Users y UserSessions, respectivamente.

Console

Abre Cloud Console.
En el panel Explorador, expande tu proyecto y selecciona un conjunto de datos.

Nota: La experiencia predeterminada es la vista previa de Cloud Console. Si hiciste clic en Hide preview features para ir a Cloud Console con disponibilidad general, realiza el siguiente paso: en la sección Recursos del panel de navegación, expande tu proyecto y selecciona un conjunto de datos.
En el panel de detalles, haz clic en Crear tabla (Create table).
Para Crear tabla desde (Create table from), selecciona Subir (Upload).
En Seleccionar archivo (Select file), busca y selecciona el archivo que descargaste.
Selecciona JSON (delimitado por saltos de línea) en Formato de archivo (File format).
Elige un Nombre de tabla adecuado.
En Esquema, haz clic en Agregar campo y, luego, ingresa un Nombre para cada columna en la tabla y selecciona el Tipo adecuado.
- Haz clic en Agregar campo y repite esta acción hasta haber ingresado todas las columnas de la tabla.
Haz clic en Crear tabla.

Los esquemas de las tablas de muestra son los siguientes:

DeletedUsers
- Nombre id, tipo INTEGER
Users
- Nombre id, tipo INTEGER
- Nombre date_joined, tipo TIMESTAMP
UserSessions
- Nombre id, tipo STRING
- Nombre user_id, tipo INTEGER
- Nombre login_time, tipo TIMESTAMP
- Nombre logout_time, tipo TIMESTAMP
- Nombre ip_address, tipo STRING

bq

Si quieres usar la herramienta de línea de comandos de bq para crear las tablas, usa el comando bq load. Proporciona la marca --location y configura el valor según tu ubicación. La marca --location es opcional. Por ejemplo, si usas BigQuery en la región asia-northeast1 (Tokio), el comando de carga tendrá el siguiente aspecto:

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

Para crear la tabla DeleteUsers, ingresa este comando:

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

Para crear la tabla Users, ingresa este comando:

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

Para crear la tabla UserSessions, ingresa este comando:

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

Paso 2. A fin de borrar la información sobre los usuarios en la tabla DeletedUsers, ejecuta las siguientes consultas de DML:

Borrar de UsersSessions

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

Borrar de Users

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

Próximos pasos

Consulta la página de referencia de DML.
Consulta la sintaxis y las muestras de DML en la página de sintaxis de DML.