Partitionierte Datenbearbeitungssprache

Die partitionierte Datenbearbeitungssprache (Partitioned Data Manipulation Language, DML) wurde für die folgenden Arten von Aktualisierungs- und Löschvorgängen von Bulk-Daten entwickelt:

  • Regelmäßige Bereinigung und automatische Speicherbereinigung. Beispiele: Alte Zeilen löschen oder Spalten auf NULL setzen.
  • Backfilling neuer Spalten mit Standardwerten. Beispiel: Mit einer UPDATE-Anweisung können Sie für den Wert einer neuen Spalte False festlegen, für die aktuell NULL gilt.

Partitionierte DML ist nicht für die Transaktionsverarbeitung in kleinem Maßstab geeignet. Wenn Sie eine Anweisung für einige wenige Zeilen ausführen möchten, verwenden Sie transaktionale DML-Anweisungen mit identifizierbaren Primärschlüsseln. Weitere Informationen finden Sie unter DML verwenden.

Wenn Sie eine große Anzahl von Blind-Schreibvorgängen ausführen müssen, aber keine atomare Transaktion erforderlich ist, können Sie Ihre Spanner-Tabellen mit Batch-Schreibvorgängen im Bulk ändern. Weitere Informationen finden Sie unter Daten mit Batch-Schreibvorgängen ändern.

In Statistiktabellen in Ihrer Spanner-Datenbank finden Sie Informationen zu aktiven partitionierten DML-Abfragen und ihrem Fortschritt. Weitere Informationen finden Sie unter Statistiken zu aktiven partitionierten DML-Anweisungen.

DML und partitionierte DML

Spanner unterstützt zwei Ausführungsmodi für DML-Anweisungen:

  • DML, die für die Transaktionsverarbeitung geeignet ist. Weitere Informationen finden Sie unter DML verwenden.

  • Partitionierte DML, die umfangreiche, datenbankweite Vorgänge mit minimaler Auswirkung auf die gleichzeitige Transaktionsverarbeitung ermöglicht. Bei diesen Vorgängen wird der Schlüsselbereich partitioniert und die Anweisung wird in separaten, weniger umfangreichen Transaktionen auf die Partitionen angewendet. Weitere Informationen finden Sie unter Partitionierte DML verwenden.

Die folgende Tabelle zeigt einen Vergleich der beiden Ausführungsmodi.

DML Partitionierte DML
Zeilen, die nicht mit der WHERE-Klausel übereinstimmen, können gesperrt werden. Nur Zeilen, die mit der WHERE-Klausel übereinstimmen, werden gesperrt.
Die Transaktionsgröße ist begrenzt. Spanner unterstützt Transaktionslimits und Limits für die Nebenläufigkeit pro Transaktion.
Anweisungen müssen nicht idempotent sein. DML-Anweisung müssen idempotent sein, um konsistente Ergebnisse zu gewährleisten.
Transaktionen können mehrere DML- und SQL-Anweisungen enthalten. Partitionierte Transaktionen können nur eine DML-Anweisung enthalten.
Es gibt keine Einschränkungen hinsichtlich der Komplexität von Anweisungen. Anweisungen müssen vollständig partitionierbar sein.
Sie erstellen Lese- und Schreibtransaktionen im Clientcode. Spanner erstellt die Transaktionen.

Partitionierbar und idempotent

Wenn eine partitionierte DML-Anweisung ausgeführt wird, haben Zeilen in einer Partition keinen Zugriff auf Zeilen in anderen Partitionen. Sie können nicht auswählen, wie Spanner die Partitionen erstellt. Partitionierung gewährleistet Skalierbarkeit, setzt jedoch voraus, dass partitionierte DML-Anweisungen vollständig partitionierbar sind. Das heißt, es muss möglich sein, die partitionierte DML-Anweisung als Vereinigung mehrerer Anweisungen auszudrücken, wobei jede Anweisung auf genau eine Zeile der Tabelle und keine anderen Tabellen zugreift. So sind beispielsweise DML-Anweisung, die auf mehrere Tabellen zugreifen oder sich selbst verknüpfen (Self-Join), nicht partitionierbar. Wenn die DML-Anweisung nicht partitionierbar ist, gibt Spanner den Fehler BadUsage aus.

Diese DML-Anweisungen sind vollständig partitionierbar, da jede Anweisung auf genau eine Zeile in der Tabelle angewendet werden kann:

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

DELETE FROM Albums WHERE MarketingBudget > 10000;

Diese DML-Anweisung kann nicht vollständig partitioniert werden, da sie auf mehrere Tabellen zugreift:

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

Es kann vorkommen, dass Spanner partitionierte DML-Anweisung aufgrund von Wiederholungen auf Netzwerkebene für einige Partitionen mehrmals ausführt. Daher kann eine Anweisung mehrmals für eine Zeile ausgeführt werden. Die Anweisung muss daher idempotent sein, um konsistente Ergebnisse zu erzielen. Anweisungen sind idempotent, wenn die mehrfache Ausführung für eine Zeile immer zum selben Ergebnis führt.

Diese DML-Anweisung ist idempotent:

UPDATE Singers SET MarketingBudget = 1000 WHERE true;

Diese DML-Anweisung ist nicht idempotent:

UPDATE Singers SET MarketingBudget = 1.5 * MarketingBudget WHERE true;

Zeilen aus übergeordneten Tabellen mit indexierten untergeordneten Tabellen löschen

Wenn Sie mit einer partitionierten DML-Anweisung Zeilen in einer übergeordneten Tabelle löschen, kann der Vorgang mit dem Fehler: The transaction contains too many mutationsfehlschlagen. Dies tritt auf, wenn die übergeordnete Tabelle verschachtelte untergeordnete Tabellen mit einem globalen Index enthält. Mutationen an den Zeilen der untergeordneten Tabelle selbst werden nicht auf das Mutationslimit der Transaktion angerechnet. Die entsprechenden Mutationen an den Indexeinträgen werden jedoch angerechnet. Wenn eine große Anzahl von Indexeinträgen der untergeordneten Tabelle betroffen ist, kann die Transaktion das Mutationslimit überschreiten.

Um diesen Fehler zu vermeiden, löschen Sie die Zeilen in zwei separaten partitionierten DML-Anweisungen:

  1. Führen Sie einen partitionierten Löschvorgang für die untergeordneten Tabellen aus.
  2. Führen Sie einen partitionierten Löschvorgang für die übergeordnete Tabelle aus.

Dieser zweistufige Prozess trägt dazu bei, dass die Anzahl der Mutationen innerhalb der zulässigen Limits für jede Transaktion bleibt. Alternativ können Sie den globalen Index für die untergeordnete Tabelle löschen, bevor Sie die übergeordneten Zeilen löschen.

Zeilensperren

Spanner erwirbt Sperren nur für Zeilen, die Kandidaten für Aktualisierungs- oder Löschvorgänge sind. Dieses Verhalten unterscheidet sich von der DML-Ausführung. Hier können auch Zeilen, die nicht mit der WHERE-Klausel übereinstimmen, eine Lesesperre erhalten.

Ausführung und Transaktionen

Ob eine DML-Anweisung partitioniert ist oder nicht, hängt von der Clientbibliotheksmethode ab, die Sie für die Ausführung auswählen. Jede Clientbibliothek stellt für die DML-Ausführung und die partitionierte DML-Ausführung separate Methoden bereit.

Pro Aufruf der Clientbibliotheksmethode können Sie nur eine partitionierte DML-Anweisung ausführen.

Spanner wendet partitionierte DML-Anweisungen nicht atomar auf die gesamte Tabelle an. Auf die einzelnen Partitionen werden sie jedoch atomisch angewendet.

Partitionierte DML-Anweisungen unterstützen kein Commit oder Rollback. Spanner führt die DML-Anweisung sofort aus und wendet sie an.

  • Wenn Sie den Vorgang abbrechen, bricht Spanner die ausgeführten Partitionen ab und startet die verbleibenden Partitionen nicht. Spanner führt für bereits ausgeführte Partitionen kein Rollback aus.
  • Wenn die Ausführung der Anweisung einen Fehler verursacht, wird die Ausführung für alle Partitionen beendet und Spanner gibt diesen Fehler für den gesamten Vorgang zurück. Fehler werden beispielsweise bei Verstößen gegen Datentype einschränkungen, gegen UNIQUE INDEX und gegen ON DELETE NO ACTION ausgelöst. Je nachdem, wann die Ausführung fehlgeschlagen ist, wurde die Anweisung möglicherweise für einige Partitionen erfolgreich ausgeführt und für andere Partitionen nie ausgeführt.

Bei einer erfolgreichen Ausführung wurde die partitionierte DML-Anweisung von Spanner für jede Partition des Schlüsselbereichs mindestens einmal ausgeführt.

Anzahl geänderter Zeilen

Eine partitionierte DML-Anweisung gibt die Zahl der Zeilen aus, die mindestens geändert wurden. Diese Zahl muss nicht exakt sein. Es gibt keine Garantie dafür, dass Spanner alle geänderten Zeilen gezählt hat.

Transaktionslimits

Spanner erstellt die Partitionen und Transaktionen, die zum Ausführen einer partitionierten DML-Anweisung erforderlich sind. Es gelten Transaktionslimits und Gleichzeitigkeitslimits pro Transaktion. Spanner versucht, die Transaktionen innerhalb dieser Limits zu halten.

Spanner unterstützt pro Datenbank maximal 20.000 partitionierte DML-Anweisungen gleichzeitig.

Nicht unterstützte Funktionen

Spanner unterstützt einige Funktionen für partitionierte DML nicht:

  • INSERT wird nicht unterstützt.
  • Google Cloud Console: Partitionierte DML-Anweisungen können in der Google Cloud Console nicht ausgeführt werden.
  • Abfragepläne und Profilerstellung: Die Google Cloud CLI und die Clientbibliotheken unterstützen keine Abfragepläne und keine Profilerstellung.
  • Unterabfragen, die aus einer anderen Tabelle oder einer anderen Zeile derselben Tabelle lesen.

Für komplexe Szenarien wie das Verschieben einer Tabelle oder Transformationen, die Joins über Tabellen hinweg erfordern, sollten Sie den Dataflow-Connector verwenden.

Best Practices

Wenden Sie die folgenden Best Practices an, um die Leistung Ihrer partitionierten DML-Anweisungen zu verbessern:

  • Hohe Parallelität vermeiden:Wenn Sie eine große Anzahl partitionierter DML-Anweisungen gleichzeitig ausführen (z. B. mehr als 100), kann dies zu Konflikten bei Sperren für interne Systemtabellen führen und die Leistung beeinträchtigen. Verwenden Sie anstelle einer großen Anzahl gleichzeitiger Anweisungen eine einzelne partitionierte DML-Anweisung.
  • Verwenden Sie PDML_MAX_PARALLELISM: Um den Durchsatz einer einzelnen partitionierten DML-Anweisung zu erhöhen, insbesondere bei Tabellen mit vielen Splits, legen Sie einen höheren Wert für den PDML_MAX_PARALLELISM Anweisungshinweis fest. Dadurch kann die einzelne Anweisung intern mehr Parallelität nutzen. Wenn Sie einen höheren Wert für PDML_MAX_PARALLELISM festlegen, wird mehr Rechenleistung verwendet. Sie sollten daher versuchen, ein Gleichgewicht zwischen Rechenleistung und erhöhter Verarbeitungsgeschwindigkeit zu finden.
  • Partitionierung von Spanner verarbeiten lassen:Vermeiden Sie es, Ihre Daten manuell zu sharden (z. B. mit Primärschlüsselbereichen) und separate partitionierte DML-Anweisungen für jeden Shard auszuführen. Partitionierte DML wurde entwickelt, um die Arbeit effizient auf die gesamte Tabelle zu verteilen. Benutzerdefiniertes Sharding erhöht oft den Overhead und kann Konflikte verschlimmern.
  • Umfang der Partitionierung verstehen:Partitionierte DML-Vorgänge werden parallel auf alle Splits in der gesamten Datenbank verteilt, nicht nur auf die Splits, die Daten für die zu ändernde Tabelle enthalten. Das bedeutet, dass bei Datenbanken mit einer großen Anzahl von Splits Overhead entstehen kann, auch wenn die Zieltabelle klein ist oder die geänderten Daten lokalisiert sind. Partitionierte DML ist möglicherweise nicht die effizienteste Wahl, um einen sehr kleinen Teil einer großen Datenbank zu ändern.
  • Alternativen für häufige, kleine Löschvorgänge in Betracht ziehen:Bei Anwendungsfällen in denen häufig eine kleine Anzahl bekannter Zeilen gelöscht wird, bieten DML Anweisungen in Transaktionen oder die BatchWrite API möglicherweise eine bessere Leistung und einen geringeren Overhead als die Verwendung von partitionierter DML.

Beispiele

Mit den folgenden Codebeispielen wird die Spalte MarketingBudget der Tabelle Albums aktualisiert.

C++

Mit der Funktion ExecutePartitionedDml() führen Sie eine partitionierte DML-Anweisung aus.

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#

Mit der Methode ExecutePartitionedUpdateAsync() führen Sie eine partitionierte DML-Anweisung aus.


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;
    }
}

Go

Mit der Methode PartitionedUpdate() führen Sie eine partitionierte DML-Anweisung aus.


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

Mit der Methode executePartitionedUpdate() führen Sie eine partitionierte DML-Anweisung aus.

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

Mit der Methode runPartitionedUpdate() führen Sie eine partitionierte DML-Anweisung aus.

// 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

Mit der Methode executePartitionedUpdate() führen Sie eine partitionierte DML-Anweisung aus.

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

Mit der Methode execute_partitioned_dml() führen Sie eine partitionierte DML-Anweisung aus.

# 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

Mit der Methode execute_partitioned_update() führen Sie eine partitionierte DML-Anweisung aus.

# 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."

Im folgenden Codebeispiel werden Zeilen aus der Tabelle Singers anhand der Spalte SingerId gelöscht.

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;
    }
}

Go


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

Nächste Schritte