Aggiornare i dati della tabella utilizzando il linguaggio di manipolazione dei dati

Questa pagina illustra come aggiornare ed eliminare i dati nelle tabelle BigQuery utilizzando il linguaggio di manipolazione dei dati (DML). Questa pagina non riguarda l'aggiunta di righe a tabelle esistenti utilizzando DML. Per informazioni sull'aggiunta di righe utilizzando DML, consulta l'istruzione INSERT nel riferimento alla sintassi DML.

Tieni presente che DML in BigQuery ha alcune limitazioni. DML ha anche i propri prezzi.

Nota: devi utilizzare SQL standard per la funzionalità DML. Per abilitare l'SQL standard, consulta Cambiare i dialetti SQL.

Aggiornamento dei dati in corso...

Segui le istruzioni riportate di seguito utilizzando questo file di esempio, che rappresenta una tabella con una colonna di indirizzi IP che vuoi mascherare per l'anonimizzazione:

• userSessionsData.json (scarica)

I seguenti passaggi caricano i dati di esempio in una tabella e aggiorna i valori nella colonna ip_address:

Passaggio 1: Carica il file JSON in una tabella UserSessions.

Passaggio 2: Per mascherare l'ultimo ottetto in ogni colonna ip_address della riga, esegui la seguente query DML:

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

Java

Prima di provare questo esempio, segui le istruzioni di configurazione di Java nella Guida di BigQuery per l'utilizzo delle librerie client. Per ulteriori informazioni, consulta la documentazione di riferimento dell'API Java di BigQuery.

Visualizza su GitHub Feedback

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

Prima di provare questo esempio, segui le istruzioni di configurazione di Python nella guida rapida di BigQuery che utilizza le librerie client. Per maggiori informazioni, consulta la documentazione di riferimento dell'API Python BigQuery.

Visualizza su GitHub Feedback

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)

Eliminazione di dati

Segui le istruzioni riportate di seguito utilizzando i file di esempio, che rappresentano un set di dati con più tabelle di dati e analisi per le sessioni utente e una tabella di utenti da eliminare.

• deletedUsersData.json (scarica)
• usersData.json (scarica)
• userSessionsData.json (scarica)

I seguenti passaggi caricano i dati in tre tabelle, quindi elimina gli utenti elencati nella tabella deletedUsers.

Passaggio 1: Carica i file JSON rispettivamente nelle tabelle Users, Utenti e UserSession.

Console

1. Apri Cloud Console.

2. Nel riquadro Explorer, espandi il progetto e seleziona un set di dati.

3. Espandi l'opzione Azioni e fai clic su Apri.

4. Nel riquadro dei dettagli, fai clic su Crea tabella .

5. Per Crea tabella da, seleziona Carica.

6. In Seleziona file, cerca e seleziona il file che hai scaricato.

   

7. Seleziona JSON (newline delimitati) per il Formato file.

8. Scegli un Nome tabella appropriato.

9. In Schema fai clic su Aggiungi campo e inserisci un Nome per ogni colonna nella tabella e seleziona il Tipo appropriato.

   • Fai clic su Aggiungi campo e ripeti questi passaggi fino a inserire tutte le colonne nella tabella.

10. Fai clic su Crea tabella.

Gli schemi per le tabelle di esempio sono:

• Utente eliminato
  • Nome id, tipo INTEGER
• Utenti
  • Nome id, tipo INTEGER
  • Nome date_joined, tipo TIMESTAMP
• Sessioni utente
  • 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

Per utilizzare lo strumento a riga di comando bq per creare le tabelle, utilizza il comando bq load. Fornisci il flag --location e imposta il valore sulla tua località. Il flag --location è facoltativo. Ad esempio, se utilizzi BigQuery nell'area geografica asia-northeast1 (Tokyo), il comando load dovrebbe avere il seguente aspetto:

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

Per creare la tabella DeleteUsers

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

Per creare la tabella Users

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

Per creare la tabella 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

Passaggio 2: Per eliminare le informazioni sugli utenti nella tabella deletedUsers. Esegui le seguenti query DML:

• Elimina da UsersSessions

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

• Elimina da Users

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

Sicurezza tavoli

Per controllare l'accesso alle tabelle in BigQuery, consulta Introduzione ai controlli di accesso alla tabella.

Passaggi successivi

• Consulta la pagina Riferimento DML.
• Consulta la sintassi e gli esempi DML nella pagina Sintassi DML.
• Scopri le transazioni multi-affermazione, che ti consentono di eseguire diverse istruzioni DML e di eseguire il commit dei risultati in modo atomico.