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.

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

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

Antes de probar esta muestra, sigue las instrucciones de configuración para Python incluidas en la guía de inicio rápido de BigQuery sobre cómo usar bibliotecas cliente. Si deseas obtener más información, consulta la documentación de referencia de la API de Python de BigQuery. 

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)

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

  1. Abre Cloud Console.

  2. En el panel Explorador, expande tu proyecto y selecciona un conjunto de datos.

  3. Expande la opción Acciones y haz clic en Abrir.

  4. En el panel de detalles, haz clic en Crear tabla  (Create table).

  5. Para Crear tabla desde (Create table from), selecciona Subir (Upload).

  6. En Seleccionar archivo (Select file), busca y selecciona el archivo que descargaste.

    Buscar archivos

  7. Selecciona JSON (delimitado por saltos de línea) en Formato de archivo (File format).

  8. Elige un Nombre de tabla adecuado.

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

  10. 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)

Seguridad de las tablas

Para controlar el acceso a las tablas en BigQuery, consulta Introducción a los controles de acceso a tablas.

¿Qué sigue?