Data Manipulation Language partizionato

Il Data Manipulation Language partizionato (DML partizionato) è progettato per i seguenti tipi di aggiornamenti ed eliminazioni collettivi:

  • Pulizia periodica e garbage collection. Ad esempio, eliminare le righe precedenti o impostare le colonne su NULL.
  • Riempimento delle nuove colonne con valori predefiniti. Ad esempio, utilizzare un'istruzione UPDATE per impostare il valore di una nuova colonna su False quando è attualmente NULL.

Il DML partizionato non è adatto all'elaborazione delle transazioni su piccola scala. Se vuoi eseguire un'istruzione su alcune righe, utilizza i DML transazionali con chiavi primarie identificabili. Per saperne di più, consulta Utilizzo di DML.

Se devi eseguire il commit di un numero elevato di scritture cieche, ma non hai bisogno di una transazione atomica, puoi modificare collettivamente le tabelle Spanner utilizzando la scrittura batch. Per saperne di più, consulta Modificare i dati utilizzando le scritture batch.

Puoi ottenere approfondimenti sulle query DML partizionate attive e sui relativi progressi dalle tabelle delle statistiche nel database Spanner. Per saperne di più, consulta Statistiche DML partizionate attive.

DML e DML partizionato

Spanner supporta due modalità di esecuzione per le istruzioni DML:

  • DML, adatto all'elaborazione delle transazioni. Per saperne di più, consulta Utilizzo di DML.

  • DML partizionato, che consente operazioni su larga scala a livello di database con un impatto minimo sull'elaborazione delle transazioni simultanee mediante il partizionamento dello spazio delle chiavi e l'esecuzione dell'istruzione sulle partizioni in transazioni separate con ambito più piccolo. Per saperne di più, consulta Utilizzo di DML partizionato.

La tabella seguente evidenzia alcune delle differenze tra le due modalità di esecuzione.

DML DML partizionato
Le righe che non corrispondono alla clausola WHERE potrebbero essere bloccate. Vengono bloccate solo le righe che corrispondono alla clausola WHERE.
Si applicano i limiti di dimensione delle transazioni. Spanner gestisce i limiti delle transazioni e i limiti di concorrenza per transazione.
Le istruzioni non devono essere idempotenti. Un'istruzione DML deve essere idempotente per garantire risultati coerenti.
Una transazione può includere più istruzioni DML e SQL. Una transazione partizionata può includere una sola istruzione DML.
Non esistono restrizioni sulla complessità delle istruzioni. Le istruzioni devono essere completamente partizionabili.
Crei transazioni di lettura/scrittura nel codice client. Spanner crea le transazioni.

Partizionabile e idempotente

Quando viene eseguita un'istruzione DML partizionata, le righe di una partizione non hanno accesso alle righe di altre partizioni e non puoi scegliere in che modo Spanner crea le partizioni. Il partizionamento garantisce la scalabilità, ma significa anche che le istruzioni DML partizionate devono essere completamente partizionabili. Ovvero, l'istruzione DML partizionata deve essere esprimibile come l'unione di un insieme di istruzioni, in cui ogni istruzione accede a una singola riga della tabella e ogni istruzione non accede ad altre tabelle. Ad esempio, un'istruzione DML che accede a più tabelle o esegue un self-join non è partizionabile. Se l'istruzione DML non è partizionabile, Spanner restituisce l'errore BadUsage.

Queste istruzioni DML sono completamente partizionabili, perché ogni istruzione può essere applicata a una singola riga della tabella:

UPDATE Singers SET LastName = NULL WHERE LastName = '';

DELETE FROM Albums WHERE MarketingBudget > 10000;

Questa istruzione DML non è completamente partizionabile, perché accede a più tabelle:

# Not fully partitionable
DELETE FROM Singers WHERE
SingerId NOT IN (SELECT SingerId FROM Concerts);

Spanner potrebbe eseguire un'istruzione DML partizionata più volte su alcune partizioni a causa di nuovi tentativi a livello di rete. Di conseguenza, un'istruzione potrebbe essere eseguita più di una volta su una riga. L'istruzione deve quindi essere idempotente per produrre risultati coerenti. Un'istruzione è idempotente se la sua esecuzione più volte su una singola riga porta allo stesso risultato.

Questa istruzione DML è idempotente:

UPDATE Singers SET MarketingBudget = 1000 WHERE true;

Questa istruzione DML non è idempotente:

UPDATE Singers SET MarketingBudget = 1.5 * MarketingBudget WHERE true;

Eliminare righe dalle tabelle principali con tabelle secondarie indicizzate

Quando utilizzi un'istruzione DML partizionata per eliminare righe in una tabella principale, l' operazione potrebbe non riuscire e generare l'errore: The transaction contains too many mutations. Questo si verifica se la tabella principale ha tabelle secondarie interleaved che contengono un indice globale. Le mutazioni alle righe della tabella secondaria non vengono conteggiate rispetto al limite di mutazione della transazione. Tuttavia, vengono conteggiate le mutazioni corrispondenti alle voci dell'indice. Se viene interessato un numero elevato di voci dell'indice della tabella secondaria, la transazione potrebbe superare il limite di mutazione.

Per evitare questo errore, elimina le righe in due istruzioni DML partizionate separate:

  1. Esegui un'eliminazione partizionata sulle tabelle secondarie.
  2. Esegui un'eliminazione partizionata sulla tabella principale.

Questo processo in due passaggi consente di mantenere il conteggio delle mutazioni entro i limiti consentiti per ogni transazione. In alternativa, puoi eliminare l'indice globale nella tabella secondaria prima di eliminare le righe principali.

Blocco delle righe

Spanner acquisisce un blocco solo se una riga è candidata per l'aggiornamento o l'eliminazione. Questo comportamento è diverso dall' esecuzione DML, che potrebbe bloccare in lettura le righe che non corrispondono alla clausola WHERE.

Esecuzione e transazioni

Se un'istruzione DML è partizionata o meno dipende dal metodo della libreria client scelto per l'esecuzione. Ogni libreria client fornisce metodi separati per l'esecuzione DML e l'esecuzione DML partizionata.

Puoi eseguire una sola istruzione DML partizionata in una chiamata al metodo della libreria client.

Spanner non applica le istruzioni DML partizionate in modo atomico all'intera tabella. Tuttavia, Spanner applica le istruzioni DML partizionate in modo atomico a ogni partizione.

Il DML partizionato non supporta il commit o il rollback. Spanner esegue e applica immediatamente l'istruzione DML.

  • Se annulli l'operazione, Spanner annulla le partizioni in esecuzione e non avvia le partizioni rimanenti. Spanner non esegue il rollback delle partizioni già eseguite.
  • Se l'esecuzione dell'istruzione genera un errore, l'esecuzione si interrompe in tutte le partizioni e Spanner restituisce l'errore per l'intera operazione. Alcuni esempi di errori sono violazioni dei vincoli del tipo di dati , violazioni di UNIQUE INDEX e violazioni di ON DELETE NO ACTION. A seconda del momento in cui l'esecuzione non è riuscita, l'istruzione potrebbe essere stata eseguita correttamente su alcune partizioni e potrebbe non essere mai stata eseguita su altre partizioni.

Se l'istruzione DML partizionata ha esito positivo, Spanner ha eseguito l'istruzione almeno una volta su ogni partizione dell'intervallo di chiavi.

Conteggio delle righe modificate

Un'istruzione DML partizionata restituisce un limite inferiore al numero di righe modificate. Potrebbe non essere un conteggio esatto del numero di righe modificate, perché non è garantito che Spanner conteggi tutte le righe modificate.

Limiti transazioni

Spanner crea le partizioni e le transazioni necessarie per eseguire un'istruzione DML partizionata. Si applicano i limiti delle transazioni o i limiti di concorrenza per transazione, ma Spanner tenta di mantenere le transazioni entro i limiti.

Spanner consente un massimo di 20.000 istruzioni DML partizionate simultanee per database.

Funzionalità non supportate

Spanner non supporta alcune funzionalità per il DML partizionato:

  • INSERT non è supportato.
  • Google Cloud Console: non puoi eseguire istruzioni DML partizionate nella Google Cloud console.
  • Piani di query e profilazione: Google Cloud CLI e le librerie client non supportano i piani di query e la profilazione.
  • Subquery che leggono da un'altra tabella o da una riga diversa della stessa tabella.

Per scenari complessi, come lo spostamento di una tabella o le trasformazioni che richiedono join tra tabelle, valuta la possibilità di utilizzare il connettore Dataflow.

Best practice

Applica le seguenti best practice per migliorare il rendimento delle istruzioni DML partizionate:

  • Evita la concorrenza elevata: l'esecuzione simultanea di un numero elevato di istruzioni DML partizionate (ad esempio, più di 100) potrebbe causare conflitti di blocco nelle tabelle di sistema interne, peggiorando il rendimento. Anziché eseguire un numero elevato di istruzioni simultanee, utilizza una singola istruzione DML partizionata.
  • Utilizza PDML_MAX_PARALLELISM: per aumentare la velocità effettiva di una singola istruzione DML partizionata, soprattutto nelle tabelle con molte suddivisioni, imposta un valore più alto per l' PDML_MAX_PARALLELISM hint dell'istruzione. In questo modo, la singola istruzione può utilizzare più parallelismo internamente. L'impostazione di un valore più alto per PDML_MAX_PARALLELISM comporta un maggiore utilizzo di risorse di calcolo, quindi devi cercare di bilanciare l'utilizzo di risorse di calcolo e l'aumento della velocità di elaborazione.
  • Consenti a Spanner di gestire il partizionamento: evita di eseguire manualmente lo sharding dei dati (ad esempio, utilizzando intervalli di chiave primaria) ed eseguire istruzioni DML partizionate separate su ogni shard. Il DML partizionato è progettato per partizionare in modo efficiente il lavoro su tutta la tabella. Lo sharding personalizzato spesso aumenta il sovraccarico e potrebbe peggiorare i conflitti.
  • Comprendi l'ambito del partizionamento: le operazioni DML partizionate vengono parallelizzate su tutte le suddivisioni dell'intero database, non solo sulle suddivisioni contenenti i dati della tabella da modificare. Ciò significa che per i database con un numero elevato di suddivisioni, potrebbe esserci un sovraccarico anche se la tabella di destinazione è piccola o i dati modificati sono localizzati. Il DML partizionato potrebbe non essere la scelta più efficiente per modificare una porzione molto piccola di un database di grandi dimensioni.
  • Valuta alternative per eliminazioni piccole e frequenti: per i casi d'uso che prevedono eliminazioni frequenti di un numero ridotto di righe note, l'utilizzo di istruzioni DML all'interno delle transazioni o dell'API BatchWrite potrebbe offrire un rendimento migliore e un sovraccarico inferiore rispetto all'utilizzo del DML partizionato.

Esempi

Il seguente esempio di codice aggiorna la colonna MarketingBudget della tabella Albums.

C++

Utilizza la funzione ExecutePartitionedDml() per eseguire un'istruzione DML partizionata.

void DmlPartitionedUpdate(google::cloud::spanner::Client client) {
  namespace spanner = ::google::cloud::spanner;
  auto result = client.ExecutePartitionedDml(
      spanner::SqlStatement("UPDATE Albums SET MarketingBudget = 100000"
                            "  WHERE SingerId > 1"));
  if (!result) throw std::move(result).status();
  std::cout << "Updated at least " << result->row_count_lower_bound
            << " row(s) [spanner_dml_partitioned_update]\n";
}

C#

Utilizza il metodo ExecutePartitionedUpdateAsync() per eseguire un'istruzione DML partizionata.


using Google.Cloud.Spanner.Data;
using System;
using System.Threading.Tasks;

public class UpdateUsingPartitionedDmlCoreAsyncSample
{
    public async Task<long> UpdateUsingPartitionedDmlCoreAsync(string projectId, string instanceId, string databaseId)
    {
        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";

        using var connection = new SpannerConnection(connectionString);
        await connection.OpenAsync();

        using var cmd = connection.CreateDmlCommand("UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1");
        long rowCount = await cmd.ExecutePartitionedUpdateAsync();

        Console.WriteLine($"{rowCount} row(s) updated...");
        return rowCount;
    }
}

Vai

Utilizza il metodo PartitionedUpdate() per eseguire un'istruzione DML partizionata.


import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/spanner"
)

func updateUsingPartitionedDML(w io.Writer, db string) error {
	ctx := context.Background()
	client, err := spanner.NewClient(ctx, db)
	if err != nil {
		return err
	}
	defer client.Close()

	stmt := spanner.Statement{SQL: "UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1"}
	rowCount, err := client.PartitionedUpdate(ctx, stmt)
	if err != nil {
		return err
	}
	fmt.Fprintf(w, "%d record(s) updated.\n", rowCount)
	return nil
}

Java

Utilizza il metodo executePartitionedUpdate() per eseguire un'istruzione DML partizionata.

static void updateUsingPartitionedDml(DatabaseClient dbClient) {
  String sql = "UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1";
  long rowCount = dbClient.executePartitionedUpdate(Statement.of(sql));
  System.out.printf("%d records updated.\n", rowCount);
}

Node.js

Utilizza il metodo runPartitionedUpdate() per eseguire un'istruzione DML partizionata.

// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Creates a client
const spanner = new Spanner({
  projectId: projectId,
});

// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

try {
  const [rowCount] = await database.runPartitionedUpdate({
    sql: 'UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1',
  });
  console.log(`Successfully updated ${rowCount} records.`);
} catch (err) {
  console.error('ERROR:', err);
} finally {
  // Close the database when finished.
  database.close();
}

PHP

Utilizza il metodo executePartitionedUpdate() per eseguire un'istruzione DML partizionata.

use Google\Cloud\Spanner\SpannerClient;

/**
 * Updates sample data in the database by partition with a DML statement.
 *
 * This updates the `MarketingBudget` column which must be created before
 * running this sample. You can add the column by running the `add_column`
 * sample or by running this DDL statement against your database:
 *
 *     ALTER TABLE Albums ADD COLUMN MarketingBudget INT64
 *
 * Example:
 * ```
 * update_data($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function update_data_with_partitioned_dml(string $instanceId, string $databaseId): void
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $rowCount = $database->executePartitionedUpdate(
        'UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1'
    );

    printf('Updated %d row(s).' . PHP_EOL, $rowCount);
}

Python

Utilizza il metodo execute_partitioned_dml() per eseguire un'istruzione DML partizionata.

# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"

spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)

row_ct = database.execute_partitioned_dml(
    "UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1"
)

print("{} records updated.".format(row_ct))

Ruby

Utilizza il metodo execute_partitioned_update() per eseguire un'istruzione DML partizionata.

# project_id  = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new project: project_id
client  = spanner.client instance_id, database_id

row_count = client.execute_partition_update(
  "UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1"
)

puts "#{row_count} records updated."

Il seguente esempio di codice elimina le righe dalla tabella Singers, in base alla colonna SingerId.

C++

void DmlPartitionedDelete(google::cloud::spanner::Client client) {
  namespace spanner = ::google::cloud::spanner;
  auto result = client.ExecutePartitionedDml(
      spanner::SqlStatement("DELETE FROM Singers WHERE SingerId > 10"));
  if (!result) throw std::move(result).status();
  std::cout << "Deleted at least " << result->row_count_lower_bound
            << " row(s) [spanner_dml_partitioned_delete]\n";
}

C#


using Google.Cloud.Spanner.Data;
using System;
using System.Threading.Tasks;

public class DeleteUsingPartitionedDmlCoreAsyncSample
{
    public async Task<long> DeleteUsingPartitionedDmlCoreAsync(string projectId, string instanceId, string databaseId)
    {
        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";

        using var connection = new SpannerConnection(connectionString);
        await connection.OpenAsync();

        using var cmd = connection.CreateDmlCommand("DELETE FROM Singers WHERE SingerId > 10");
        long rowCount = await cmd.ExecutePartitionedUpdateAsync();

        Console.WriteLine($"{rowCount} row(s) deleted...");
        return rowCount;
    }
}

Vai


import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/spanner"
)

func deleteUsingPartitionedDML(w io.Writer, db string) error {
	ctx := context.Background()
	client, err := spanner.NewClient(ctx, db)
	if err != nil {
		return err
	}
	defer client.Close()

	stmt := spanner.Statement{SQL: "DELETE FROM Singers WHERE SingerId > 10"}
	rowCount, err := client.PartitionedUpdate(ctx, stmt)
	if err != nil {
		return err

	}
	fmt.Fprintf(w, "%d record(s) deleted.", rowCount)
	return nil
}

Java

static void deleteUsingPartitionedDml(DatabaseClient dbClient) {
  String sql = "DELETE FROM Singers WHERE SingerId > 10";
  long rowCount = dbClient.executePartitionedUpdate(Statement.of(sql));
  System.out.printf("%d records deleted.\n", rowCount);
}

Node.js

// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Creates a client
const spanner = new Spanner({
  projectId: projectId,
});

// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

try {
  const [rowCount] = await database.runPartitionedUpdate({
    sql: 'DELETE FROM Singers WHERE SingerId > 10',
  });
  console.log(`Successfully deleted ${rowCount} records.`);
} catch (err) {
  console.error('ERROR:', err);
} finally {
  // Close the database when finished.
  database.close();
}

PHP

use Google\Cloud\Spanner\SpannerClient;

/**
 * Delete sample data in the database by partition with a DML statement.
 *
 * This updates the `MarketingBudget` column which must be created before
 * running this sample. You can add the column by running the `add_column`
 * sample or by running this DDL statement against your database:
 *
 *     ALTER TABLE Albums ADD COLUMN MarketingBudget INT64
 *
 * Example:
 * ```
 * update_data($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function delete_data_with_partitioned_dml(string $instanceId, string $databaseId): void
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $rowCount = $database->executePartitionedUpdate(
        'DELETE FROM Singers WHERE SingerId > 10'
    );

    printf('Deleted %d row(s).' . PHP_EOL, $rowCount);
}

Python

# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)

row_ct = database.execute_partitioned_dml("DELETE FROM Singers WHERE SingerId > 10")

print("{} record(s) deleted.".format(row_ct))

Ruby

# project_id  = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new project: project_id
client  = spanner.client instance_id, database_id

row_count = client.execute_partition_update(
  "DELETE FROM Singers WHERE SingerId > 10"
)

puts "#{row_count} records deleted."

Passaggi successivi