Como atualizar dados de tabela usando a linguagem de manipulação de dados

Nesta página, você aprenderá a atualizar e excluir dados em tabelas do BigQuery usando a linguagem de manipulação de dados (DML). A adição de linhas em tabelas atuais com a DML não foi incluída neste documento. Para informações sobre como adicionar linhas usando a DML, consulte a instrução INSERT na referência de sintaxe da DML.

algumas limitações de DML no BigQuery. Além disso, a DML tem os próprios preços.

Como atualizar dados

Acompanhe as instruções a seguir usando este arquivo de amostra, que representa uma tabela com uma coluna de endereços IP que você quer mascarar para anonimização:

Nas etapas a seguir, os dados de amostra são carregados em uma tabela e os valores são atualizados na coluna ip_address:

Etapa 1: carregue o arquivo JSON em uma tabela UserSessions.

Etapa 2: para mascarar o último octeto na coluna ip_address de cada linha, execute a seguinte consulta DML:

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

Java

Antes de testar essa amostra, siga as instruções de configuração para Java no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery 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 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());
    }
  }
}

Como excluir dados

Acompanhe as instruções a seguir usando os arquivos de amostra, que representam um conjunto de dados com várias tabelas analíticas referentes a sessões do usuário e uma tabela de usuários para exclusão.

Nas etapas a seguir, os dados são carregados em três tabelas e os usuários listados na tabela "DeletedUsers" são excluídos.

Etapa 1: carregue os arquivos JSON nas tabelas "DeletedUsers", "Users" e "UserSessions", respectivamente.

Console

  1. Abra o Console do Cloud.
  2. Selecione o conjunto de dados na lista Recursos.
  3. Clique em Criar tabela.

    Criar tabela

  4. Em Criar tabela de, selecione Fazer upload.

  5. Em Selecionar arquivo, procure e selecione o arquivo que você salvou.

    Procurar arquivos

  6. Selecione JSON (delimitado por nova linha) em Formato de arquivo.

  7. Escolha uma opção apropriada para o Nome da tabela.

  8. Em Esquema, clique em Adicionar campo, insira um Nome em cada coluna da tabela e selecione o Tipo apropriado.

    • Clique em Adicionar campo e repita até ter inserido todas as colunas na tabela.
  9. Clique em Criar tabela.

Os esquemas das tabelas de amostra são:

  • DeletedUsers
    • Nome id, Tipo INTEGER
  • Users
    • Nome id, Tipo INTEGER
    • Nome date_joined, Tipo TIMESTAMP
  • UserSessions
    • Nome id, Tipo STRING
    • Nome user_id, Tipo INTEGER
    • Nome login_time, Tipo TIMESTAMP
    • Nome logout_time, Tipo TIMESTAMP
    • Nome ip_address, Tipo STRING

bq

Para usar a ferramenta de linha de comando bq para criar as tabelas, use o comando bq load. Forneça a sinalização --location e defina o valor como seu local. A sinalização --location é opcional. Por exemplo, se você estiver usando BigQuery na região asia-northeast1 (Tóquio), o comando de carregamento será semelhante a este:

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

Para criar a tabela DeleteUsers

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

Para criar a tabela Users

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

Para criar a tabela UserSessions

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

Etapa 2: para excluir as informações sobre os usuários na tabela "DeletedUsers", faça estas consultas da DML:

  • Excluir do UsersSessions

    DELETE FROM sample_db.UserSessions
    WHERE user_id in (SELECT id from sample_db.DeletedUsers)
    
  • Excluir do Users

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

A seguir