Insérer, mettre à jour et supprimer des données à l'aide du langage de manipulation de données

Cette page explique comment insérer, mettre à jour et supprimer des données Cloud Spanner à l'aide d'instructions LMD (langage de manipulation de données). Vous pouvez exécuter des instructions LMD via les bibliothèques clientes, Google Cloud Console et l'outil de ligne de commande gcloud. Vous pouvez exécuter des instructions en LMD partitionné via les bibliothèques clientes et l'outil de ligne de commande gcloud.

Pour obtenir une description complète de la syntaxe LMD, consultez la page Syntaxe du langage de manipulation de données.

Utiliser LMD

Le LMD accepte les instructions INSERT, UPDATE et DELETE dans Cloud Console, l'outil de ligne de commande gcloud et les bibliothèques clientes.

Verrouillage

Les instructions LMD sont exécutées dans les transactions de lecture-écriture. Lorsque Cloud Spanner lit des données, il acquiert des verrous en lecture partagés sur les parties limitées des plages de lignes que vous consultez. Plus précisément, il n'acquiert ces verrous que sur les colonnes auxquelles vous accédez. Les verrous peuvent inclure des données qui ne correspondent pas à la condition de filtre de la clause WHERE.

Lorsque Cloud Spanner modifie des données à l'aide d'instructions LMD, il acquiert des verrous exclusifs sur les données spécifiques que vous modifiez. De plus, il acquiert des verrous partagés de la même manière que lorsque vous consultez des données. Si votre requête inclut de grandes plages de lignes ou une table entière, les verrous partagés peuvent empêcher les autres transactions de se terminer en parallèle.

Pour modifier les données aussi efficacement que possible, utilisez une clause WHERE permettant à Cloud Spanner de ne lire que les lignes nécessaires. Pour ce faire, appliquez un filtre sur la clé primaire ou sur la clé d'un index secondaire. La clause WHERE limite le champ d'application des verrous partagés et permet à Cloud Spanner de traiter la modification plus efficacement.

Par exemple, imaginons que l'un des musiciens de la table Singers change de prénom et que vous deviez mettre à jour ce prénom dans votre base de données. Vous pouvez exécuter l'instruction LMD suivante, mais elle oblige Cloud Spanner à analyser l'ensemble de la table et à acquérir des verrous partagés qui la couvrent dans sa totalité. Par conséquent, Cloud Spanner doit lire davantage de données que nécessaire, et les transactions simultanées ne peuvent pas modifier les données en parallèle :

-- ANTI-PATTERN: SENDING AN UPDATE WITHOUT THE PRIMARY KEY COLUMN
-- IN THE WHERE CLAUSE

UPDATE Singers SET FirstName = "Marcel"
WHERE FirstName = "Marc" AND LastName = "Richards";

Pour optimiser l'opération de mise à jour, incluez la colonne SingerId dans la clause WHERE. La colonne SingerId est la seule colonne de clé primaire pour la table Singers :

-- RECOMMENDED: INCLUDING THE PRIMARY KEY COLUMN IN THE WHERE CLAUSE

UPDATE Singers SET FirstName = "Marcel"
WHERE FirstName = "Marc" AND LastName = "Richards" AND SingerId = 1;

Simultanéité

Cloud Spanner exécute de manière séquentielle toutes les instructions SQL ( SELECT, INSERT, UPDATE et DELETE) d'une transaction. Elles ne sont pas exécutées simultanément. Il existe une seule exception : Cloud Spanner peut exécuter plusieurs instructions SELECT simultanément, car il s'agit d'opérations en lecture seule.

Limites de transaction

Une transaction qui comprend des instructions LMD est soumise aux mêmes limites que toute autre transaction. Si vous devez effectuer des modifications à grande échelle, envisagez d'utiliser le LMD partitionné.

  • Si les instructions LMD d'une transaction entraînent plus de 20 000 mutations, l'instruction LMD qui pousse la transaction au-dessus de la limite renvoie une erreur BadUsage, avec un message indiquant que le nombre de mutations est trop élevé.

  • Si les instructions LMD d'une transaction entraînent une transaction supérieure à 100 Mo, l'instruction LMD qui pousse la transaction au-dessus de la limite renvoie une erreur BadUsage, avec un message indiquant que la transaction dépasse la limite de taille.

Les mutations effectuées à l'aide du LMD ne sont pas renvoyées au client. Elles sont fusionnées dans la requête commit quand elle est validée et sont prises en compte dans les tailles maximales. Même si la taille de la requête commit que vous envoyez est petite, la transaction peut dépasser la limite de taille autorisée.

Exécuter des instructions dans Cloud Console

Suivez la procédure ci-dessous pour exécuter une instruction LMD dans Cloud Console.

  1. Accédez à la page Instances de Cloud Spanner.

    Accéder à la page Instances

  2. Sélectionnez votre projet dans la liste déroulante de la barre d’outils.

  3. Cliquez sur le nom de l'instance contenant votre base de données pour accéder à la page Détails de l'instance.

  4. Dans l'onglet Vue d'ensemble, cliquez sur le nom de votre base de données. La page Détails de la base de données s'affiche.

  5. Cliquez sur Requête.

  6. Entrez une instruction LMD. Par exemple, l'instruction suivante ajoute une ligne à la table Singers.

    INSERT Singers (SingerId, FirstName, LastName)
    VALUES (1, 'Marc', 'Richards')
    
  7. Cliquez sur Run query. Cloud Console affiche le résultat.

Exécuter des instructions avec l'outil de ligne de commande gcloud

Pour exécuter des instructions LMD, utilisez la commande gcloud spanner databases execute-sql. L'exemple suivant ajoute une ligne à la table Singers.

gcloud spanner databases execute-sql example-db --instance=test-instance \
    --sql="INSERT Singers (SingerId, FirstName, LastName) VALUES (1, 'Marc', 'Richards')"

Modifier des données avec la bibliothèque cliente

Pour exécuter des instructions LMD à l'aide de la bibliothèque cliente :

  • Créez une transaction en lecture/écriture.
  • Appelez la méthode de la bibliothèque cliente pour une exécution LMD, et transmettez l'instruction LMD.
  • Utilisez la valeur affichée par la méthode d'exécution de l'instruction LMD pour obtenir le nombre de lignes insérées, mises à jour ou supprimées.

L'exemple de code suivant insère une ligne dans la table Singers.

C++

Vous utilisez la fonction ExecuteDml() pour exécuter une instruction LMD.

void DmlStandardInsert(google::cloud::spanner::Client client) {
  using ::google::cloud::StatusOr;
  namespace spanner = ::google::cloud::spanner;
  std::int64_t rows_inserted;
  auto commit_result = client.Commit(
      [&client, &rows_inserted](
          spanner::Transaction txn) -> StatusOr<spanner::Mutations> {
        auto insert = client.ExecuteDml(
            std::move(txn),
            spanner::SqlStatement(
                "INSERT INTO Singers (SingerId, FirstName, LastName)"
                "  VALUES (10, 'Virginia', 'Watson')"));
        if (!insert) return insert.status();
        rows_inserted = insert->RowsModified();
        return spanner::Mutations{};
      });
  if (!commit_result) {
    throw std::runtime_error(commit_result.status().message());
  }
  std::cout << "Rows inserted: " << rows_inserted;
  std::cout << "Insert was successful [spanner_dml_standard_insert]\n";
}

C#

L'exécution d'une instruction LMD s'effectue via la méthode ExecuteNonQueryAsync().

public static async Task InsertUsingDmlCoreAsync(
    string projectId,
    string instanceId,
    string databaseId)
{
    string connectionString =
        $"Data Source=projects/{projectId}/instances/{instanceId}"
        + $"/databases/{databaseId}";

    // Create connection to Cloud Spanner.
    using (var connection =
        new SpannerConnection(connectionString))
    {
        await connection.OpenAsync();

        SpannerCommand cmd = connection.CreateDmlCommand(
           "INSERT Singers (SingerId, FirstName, LastName) "
           + "VALUES (10, 'Virginia', 'Watson')");
        int rowCount = await cmd.ExecuteNonQueryAsync();
        Console.WriteLine($"{rowCount} row(s) inserted...");
    }
}

Go

L'exécution d'une instruction LMD s'effectue via la méthode Update().


import (
	"context"
	"fmt"
	"io"

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

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

	_, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
		stmt := spanner.Statement{
			SQL: `INSERT Singers (SingerId, FirstName, LastName)
					VALUES (10, 'Virginia', 'Watson')`,
		}
		rowCount, err := txn.Update(ctx, stmt)
		if err != nil {
			return err
		}
		fmt.Fprintf(w, "%d record(s) inserted.\n", rowCount)
		return nil
	})
	return err
}

Java

L'exécution d'une instruction LMD s'effectue via la méthode executeUpdate().

static void insertUsingDml(DatabaseClient dbClient) {
  dbClient
      .readWriteTransaction()
      .run(
          new TransactionCallable<Void>() {
            @Override
            public Void run(TransactionContext transaction) throws Exception {
              String sql =
                  "INSERT INTO Singers (SingerId, FirstName, LastName) "
                      + " VALUES (10, 'Virginia', 'Watson')";
              long rowCount = transaction.executeUpdate(Statement.of(sql));
              System.out.printf("%d record inserted.\n", rowCount);
              return null;
            }
          });
}

Node.js

L'exécution d'une instruction LMD s'effectue via la méthode runUpdate().

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

database.runTransaction(async (err, transaction) => {
  if (err) {
    console.error(err);
    return;
  }
  try {
    const [rowCount] = await transaction.runUpdate({
      sql:
        'INSERT Singers (SingerId, FirstName, LastName) VALUES (10, @firstName, @lastName)',
      params: {
        firstName: 'Virginia',
        lastName: 'Watson',
      },
    });

    console.log(
      `Successfully inserted ${rowCount} record into the Singers table.`
    );

    await transaction.commit();
  } catch (err) {
    console.error('ERROR:', err);
  } finally {
    // Close the database when finished.
    database.close();
  }
});

PHP

L'exécution d'une instruction LMD s'effectue via la méthode executeUpdate().

use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\Transaction;

/**
 * Inserts sample data into the given database with a DML statement.
 *
 * The database and table must already exist and can be created using
 * `create_database`.
 * Example:
 * ```
 * insert_data($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function insert_data_with_dml($instanceId, $databaseId)
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $database->runTransaction(function (Transaction $t) use ($spanner) {
        $rowCount = $t->executeUpdate(
            "INSERT Singers (SingerId, FirstName, LastName) "
            . " VALUES (10, 'Virginia', 'Watson')");
        $t->commit();
        printf('Inserted %d row(s).' . PHP_EOL, $rowCount);
    });
}

Python

L'exécution d'une instruction LMD s'effectue via la méthode execute_update().

# 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)

def insert_singers(transaction):
    row_ct = transaction.execute_update(
        "INSERT Singers (SingerId, FirstName, LastName) "
        " VALUES (10, 'Virginia', 'Watson')"
    )

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

database.run_in_transaction(insert_singers)

Ruby

L'exécution d'une instruction LMD s'effectue via la méthode execute_update().

# 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 = 0

client.transaction do |transaction|
  row_count = transaction.execute_update(
    "INSERT INTO Singers (SingerId, FirstName, LastName) VALUES (10, 'Virginia', 'Watson')"
  )
end

puts "#{row_count} record inserted."

L'exemple de code suivant met à jour la colonne MarketingBudget de la table Albums en fonction d'une clause WHERE.

C++

void DmlStandardUpdate(google::cloud::spanner::Client client) {
  using ::google::cloud::StatusOr;
  namespace spanner = ::google::cloud::spanner;
  auto commit_result = client.Commit(
      [&client](spanner::Transaction txn) -> StatusOr<spanner::Mutations> {
        auto update = client.ExecuteDml(
            std::move(txn),
            spanner::SqlStatement(
                "UPDATE Albums SET MarketingBudget = MarketingBudget * 2"
                " WHERE SingerId = 1 AND AlbumId = 1"));
        if (!update) return update.status();
        return spanner::Mutations{};
      });
  if (!commit_result) {
    throw std::runtime_error(commit_result.status().message());
  }
  std::cout << "Update was successful [spanner_dml_standard_update]\n";
}

C#

public static async Task UpdateUsingDmlCoreAsync(
    string projectId,
    string instanceId,
    string databaseId)
{
    string connectionString =
        $"Data Source=projects/{projectId}/instances/{instanceId}"
        + $"/databases/{databaseId}";

    // Create connection to Cloud Spanner.
    using (var connection =
        new SpannerConnection(connectionString))
    {
        await connection.OpenAsync();

        SpannerCommand cmd = connection.CreateDmlCommand(
           "UPDATE Albums SET MarketingBudget = MarketingBudget * 2 "
           + "WHERE SingerId = 1 and AlbumId = 1");
        int rowCount = await cmd.ExecuteNonQueryAsync();
        Console.WriteLine($"{rowCount} row(s) updated...");
    }
}

Go


import (
	"context"
	"fmt"
	"io"

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

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

	_, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
		stmt := spanner.Statement{
			SQL: `UPDATE Albums
				SET MarketingBudget = MarketingBudget * 2
				WHERE SingerId = 1 and AlbumId = 1`,
		}
		rowCount, err := txn.Update(ctx, stmt)
		if err != nil {
			return err
		}
		fmt.Fprintf(w, "%d record(s) updated.\n", rowCount)
		return nil
	})
	return err
}

Java

static void updateUsingDml(DatabaseClient dbClient) {
  dbClient
      .readWriteTransaction()
      .run(
          new TransactionCallable<Void>() {
            @Override
            public Void run(TransactionContext transaction) throws Exception {
              String sql =
                  "UPDATE Albums "
                      + "SET MarketingBudget = MarketingBudget * 2 "
                      + "WHERE SingerId = 1 and AlbumId = 1";
              long rowCount = transaction.executeUpdate(Statement.of(sql));
              System.out.printf("%d record updated.\n", rowCount);
              return null;
            }
          });
}

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

database.runTransaction(async (err, transaction) => {
  if (err) {
    console.error(err);
    return;
  }
  try {
    const [rowCount] = await transaction.runUpdate({
      sql: `UPDATE Albums SET MarketingBudget = MarketingBudget * 2
        WHERE SingerId = 1 and AlbumId = 1`,
    });

    console.log(`Successfully updated ${rowCount} record.`);
    await transaction.commit();
  } catch (err) {
    console.error('ERROR:', err);
  } finally {
    // Close the database when finished.
    database.close();
  }
});

PHP

use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\Transaction;

/**
 * Updates sample data in the database with a DML statement.
 *
 * This requires 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_dml($instanceId, $databaseId)
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $database->runTransaction(function (Transaction $t) use ($spanner) {
        $rowCount = $t->executeUpdate(
            "UPDATE Albums "
            . "SET MarketingBudget = MarketingBudget * 2 "
            . "WHERE SingerId = 1 and AlbumId = 1");
        $t->commit();
        printf('Updated %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)

def update_albums(transaction):
    row_ct = transaction.execute_update(
        "UPDATE Albums "
        "SET MarketingBudget = MarketingBudget * 2 "
        "WHERE SingerId = 1 and AlbumId = 1"
    )

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

database.run_in_transaction(update_albums)

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 = 0

client.transaction do |transaction|
  row_count = transaction.execute_update(
    "UPDATE Albums
     SET MarketingBudget = MarketingBudget * 2
     WHERE SingerId = 1 and AlbumId = 1"
  )
end

puts "#{row_count} record updated."

L'exemple de code suivant supprime toutes les lignes de la table Singers pour lesquelles la colonne FirstName contient Alice.

C++

void DmlStandardDelete(google::cloud::spanner::Client client) {
  using ::google::cloud::StatusOr;
  namespace spanner = ::google::cloud::spanner;
  auto commit_result = client.Commit([&client](spanner::Transaction txn)
                                         -> StatusOr<spanner::Mutations> {
    auto dele = client.ExecuteDml(
        std::move(txn),
        spanner::SqlStatement("DELETE FROM Singers WHERE FirstName = 'Alice'"));
    if (!dele) return dele.status();
    return spanner::Mutations{};
  });
  if (!commit_result) {
    throw std::runtime_error(commit_result.status().message());
  }
  std::cout << "Delete was successful [spanner_dml_standard_delete]\n";
}

C#

public static async Task DeleteUsingDmlCoreAsync(
    string projectId,
    string instanceId,
    string databaseId)
{
    string connectionString =
        $"Data Source=projects/{projectId}/instances/{instanceId}"
        + $"/databases/{databaseId}";

    // Create connection to Cloud Spanner.
    using (var connection =
        new SpannerConnection(connectionString))
    {
        await connection.OpenAsync();

        SpannerCommand cmd = connection.CreateDmlCommand(
           "DELETE FROM Singers WHERE FirstName = 'Alice'");
        int rowCount = await cmd.ExecuteNonQueryAsync();
        Console.WriteLine($"{rowCount} row(s) deleted...");
    }
}

Go


import (
	"context"
	"fmt"
	"io"

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

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

	_, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
		stmt := spanner.Statement{SQL: `DELETE FROM Singers WHERE FirstName = 'Alice'`}
		rowCount, err := txn.Update(ctx, stmt)
		if err != nil {
			return err
		}
		fmt.Fprintf(w, "%d record(s) deleted.\n", rowCount)
		return nil
	})
	return err
}

Java

static void deleteUsingDml(DatabaseClient dbClient) {
  dbClient
      .readWriteTransaction()
      .run(
          new TransactionCallable<Void>() {
            @Override
            public Void run(TransactionContext transaction) throws Exception {
              String sql = "DELETE FROM Singers WHERE FirstName = 'Alice'";
              long rowCount = transaction.executeUpdate(Statement.of(sql));
              System.out.printf("%d record deleted.\n", rowCount);
              return null;
            }
          });
}

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

database.runTransaction(async (err, transaction) => {
  if (err) {
    console.error(err);
    return;
  }
  try {
    const [rowCount] = await transaction.runUpdate({
      sql: "DELETE FROM Singers WHERE FirstName = 'Alice'",
    });

    console.log(`Successfully deleted ${rowCount} record.`);
    await transaction.commit();
  } catch (err) {
    console.error('ERROR:', err);
  } finally {
    // Close the database when finished.
    database.close();
  }
});

PHP

use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\Transaction;

/**
 * Deletes sample data in the database with a DML statement.
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function delete_data_with_dml($instanceId, $databaseId)
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $database->runTransaction(function (Transaction $t) use ($spanner) {
        $rowCount = $t->executeUpdate(
            "DELETE FROM Singers WHERE FirstName = 'Alice'");
        $t->commit();
        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)

def delete_singers(transaction):
    row_ct = transaction.execute_update(
        "DELETE FROM Singers WHERE FirstName = 'Alice'"
    )

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

database.run_in_transaction(delete_singers)

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 = 0

client.transaction do |transaction|
  row_count = transaction.execute_update(
    "DELETE FROM Singers WHERE FirstName = 'Alice'"
  )
end

puts "#{row_count} record deleted."

L'exemple suivant utilise un objet STRUCT avec des paramètres de liaison pour mettre à jour l'élément LastName dans les lignes filtrées par FirstName et LastName.

C++

void DmlStructs(google::cloud::spanner::Client client) {
  namespace spanner = ::google::cloud::spanner;
  std::int64_t rows_modified = 0;
  auto commit_result =
      client.Commit([&client, &rows_modified](spanner::Transaction const& txn)
                        -> google::cloud::StatusOr<spanner::Mutations> {
        auto singer_info = std::make_tuple("Marc", "Richards");
        auto sql = spanner::SqlStatement(
            "UPDATE Singers SET FirstName = 'Keith' WHERE "
            "STRUCT<FirstName String, LastName String>(FirstName, LastName) "
            "= @name",
            {{"name", spanner::Value(std::move(singer_info))}});
        auto dml_result = client.ExecuteDml(txn, std::move(sql));
        if (!dml_result) return dml_result.status();
        rows_modified = dml_result->RowsModified();
        return spanner::Mutations{};
      });
  if (!commit_result) {
    throw std::runtime_error(commit_result.status().message());
  }
  std::cout << rows_modified
            << " update was successful [spanner_dml_structs]\n";
}

C#

public static async Task UpdateUsingDmlWithStructCoreAsync(
    string projectId,
    string instanceId,
    string databaseId)
{
    var nameStruct = new SpannerStruct
    {
        { "FirstName", SpannerDbType.String, "Timothy" },
        { "LastName", SpannerDbType.String, "Campbell" },
    };
    string connectionString =
        $"Data Source=projects/{projectId}/instances/{instanceId}"
        + $"/databases/{databaseId}";

    // Create connection to Cloud Spanner.
    using (var connection =
        new SpannerConnection(connectionString))
    {
        await connection.OpenAsync();

        SpannerCommand cmd = connection.CreateDmlCommand(
            "UPDATE Singers SET LastName = 'Grant' "
           + "WHERE STRUCT<FirstName STRING, LastName STRING>"
           + "(FirstName, LastName) = @name");
        cmd.Parameters.Add("name", nameStruct.GetSpannerDbType(), nameStruct);
        int rowCount = await cmd.ExecuteNonQueryAsync();
        Console.WriteLine($"{rowCount} row(s) updated...");
    }
}

Go


import (
	"context"
	"fmt"
	"io"

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

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

	_, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
		type name struct {
			FirstName string
			LastName  string
		}
		var singerInfo = name{"Timothy", "Campbell"}

		stmt := spanner.Statement{
			SQL: `Update Singers Set LastName = 'Grant'
				WHERE STRUCT<FirstName String, LastName String>(Firstname, LastName) = @name`,
			Params: map[string]interface{}{"name": singerInfo},
		}
		rowCount, err := txn.Update(ctx, stmt)
		if err != nil {
			return err
		}
		fmt.Fprintf(w, "%d record(s) inserted.\n", rowCount)
		return nil
	})
	return err
}

Java

static void updateUsingDmlWithStruct(DatabaseClient dbClient) {
  Struct name =
      Struct.newBuilder().set("FirstName").to("Timothy").set("LastName").to("Campbell").build();
  Statement s =
      Statement.newBuilder(
              "UPDATE Singers SET LastName = 'Grant' "
                  + "WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) "
                  + "= @name")
          .bind("name")
          .to(name)
          .build();
  dbClient
      .readWriteTransaction()
      .run(
          new TransactionCallable<Void>() {
            @Override
            public Void run(TransactionContext transaction) throws Exception {
              long rowCount = transaction.executeUpdate(s);
              System.out.printf("%d record updated.\n", rowCount);
              return null;
            }
          });
}

Node.js

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

const nameStruct = Spanner.struct({
  FirstName: 'Timothy',
  LastName: 'Campbell',
});

/**
 * 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);

database.runTransaction(async (err, transaction) => {
  if (err) {
    console.error(err);
    return;
  }
  try {
    const [rowCount] = await transaction.runUpdate({
      sql: `UPDATE Singers SET LastName = 'Grant'
      WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) = @name`,
      params: {
        name: nameStruct,
      },
    });

    console.log(`Successfully updated ${rowCount} record.`);
    await transaction.commit();
  } catch (err) {
    console.error('ERROR:', err);
  } finally {
    // Close the database when finished.
    database.close();
  }
});

PHP

use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\Database;
use Google\Cloud\Spanner\Transaction;
use Google\Cloud\Spanner\StructType;
use Google\Cloud\Spanner\StructValue;

/**
 * Update data with a DML statement using Structs.
 *
 * The database and table must already exist and can be created using
 * `create_database`.
 * Example:
 * ```
 * insert_data($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function update_data_with_dml_structs($instanceId, $databaseId)
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $database->runTransaction(function (Transaction $t) use ($spanner) {
        $nameValue = (new StructValue)
            ->add('FirstName', 'Timothy')
            ->add('LastName', 'Campbell');
        $nameType = (new StructType)
            ->add('FirstName', Database::TYPE_STRING)
            ->add('LastName', Database::TYPE_STRING);

        $rowCount = $t->executeUpdate(
            "UPDATE Singers SET LastName = 'Grant' "
             . "WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) "
             . "= @name",
            [
                'parameters' => [
                    'name' => $nameValue
                ],
                'types' => [
                    'name' => $nameType
                ]
            ]);
        $t->commit();
        printf('Updated %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)

record_type = param_types.Struct(
    [
        param_types.StructField("FirstName", param_types.STRING),
        param_types.StructField("LastName", param_types.STRING),
    ]
)
record_value = ("Timothy", "Campbell")

def write_with_struct(transaction):
    row_ct = transaction.execute_update(
        "UPDATE Singers SET LastName = 'Grant' "
        "WHERE STRUCT<FirstName STRING, LastName STRING>"
        "(FirstName, LastName) = @name",
        params={"name": record_value},
        param_types={"name": record_type},
    )
    print("{} record(s) updated.".format(row_ct))

database.run_in_transaction(write_with_struct)

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 = 0
name_struct = { FirstName: "Timothy", LastName: "Campbell" }

client.transaction do |transaction|
  row_count = transaction.execute_update(
    "UPDATE Singers SET LastName = 'Grant'
     WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) = @name",
    params: { name: name_struct }
  )
end

puts "#{row_count} record updated."

Lire des données écrites dans la même transaction

Les modifications que vous effectuez à l'aide d'instructions LMD sont visibles pour les instructions suivantes dans la même transaction. Cette méthode est différente des mutations, avec lesquelles les modifications ne sont visibles que lors du commit de la transaction.

Cloud Spanner vérifie les contraintes après chaque instruction LMD. Ce comportement est différent de celui applicable aux mutations : Cloud Spanner place ces mutations en tampon dans le client jusqu'au commit, puis vérifie les contraintes au moment du commit. En évaluant les contraintes après chaque instruction, Cloud Spanner peut garantir que les données affichées par une instruction LMD sont cohérentes avec le schéma.

L'exemple ci-dessous permet de mettre à jour une ligne de la table Singers, puis d'exécuter une instruction SELECT pour afficher les nouvelles valeurs.

C++

void DmlWriteThenRead(google::cloud::spanner::Client client) {
  namespace spanner = ::google::cloud::spanner;
  using ::google::cloud::StatusOr;

  auto commit_result = client.Commit(
      [&client](spanner::Transaction txn) -> StatusOr<spanner::Mutations> {
        auto insert = client.ExecuteDml(
            txn, spanner::SqlStatement(
                     "INSERT INTO Singers (SingerId, FirstName, LastName)"
                     "  VALUES (11, 'Timothy', 'Campbell')"));
        if (!insert) return insert.status();
        // Read newly inserted record.
        spanner::SqlStatement select(
            "SELECT FirstName, LastName FROM Singers where SingerId = 11");
        using RowType = std::tuple<std::string, std::string>;
        auto rows = client.ExecuteQuery(std::move(txn), std::move(select));
        for (auto const& row : spanner::StreamOf<RowType>(rows)) {
          if (!row) return row.status();
          std::cout << "FirstName: " << std::get<0>(*row) << "\t";
          std::cout << "LastName: " << std::get<1>(*row) << "\n";
        }
        return spanner::Mutations{};
      });
  if (!commit_result) {
    throw std::runtime_error(commit_result.status().message());
  }
  std::cout << "Write then read suceeded [spanner_dml_write_then_read]\n";
}

C#

public static async Task WriteAndReadUsingDmlCoreAsync(
    string projectId,
    string instanceId,
    string databaseId)
{
    string connectionString =
        $"Data Source=projects/{projectId}/instances/{instanceId}"
        + $"/databases/{databaseId}";

    // Create connection to Cloud Spanner.
    using (var connection =
        new SpannerConnection(connectionString))
    {
        await connection.OpenAsync();

        SpannerCommand cmd = connection.CreateDmlCommand(
           @"INSERT Singers (SingerId, FirstName, LastName)
              VALUES (11, 'Timothy', 'Campbell')");
        int rowCount = await cmd.ExecuteNonQueryAsync();
        Console.WriteLine($"{rowCount} row(s) inserted...");
        // Read newly inserted record.
        cmd = connection.CreateSelectCommand(
            @"SELECT FirstName, LastName FROM Singers
              WHERE SingerId = 11");
        using (var reader = await cmd.ExecuteReaderAsync())
        {
            while (await reader.ReadAsync())
            {
                Console.WriteLine(
                    reader.GetFieldValue<string>("FirstName") + " "
                    + reader.GetFieldValue<string>("LastName"));
            }
        }
    }
}

Go


import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/spanner"
	"google.golang.org/api/iterator"
)

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

	_, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
		// Insert Record
		stmt := spanner.Statement{
			SQL: `INSERT Singers (SingerId, FirstName, LastName)
				VALUES (11, 'Timothy', 'Campbell')`,
		}
		rowCount, err := txn.Update(ctx, stmt)
		if err != nil {
			return err
		}
		fmt.Fprintf(w, "%d record(s) inserted.\n", rowCount)

		// Read newly inserted record
		stmt = spanner.Statement{SQL: `SELECT FirstName, LastName FROM Singers WHERE SingerId = 11`}
		iter := txn.Query(ctx, stmt)
		defer iter.Stop()

		for {
			row, err := iter.Next()
			if err == iterator.Done || err != nil {
				break
			}
			var firstName, lastName string
			if err := row.ColumnByName("FirstName", &firstName); err != nil {
				return err
			}
			if err := row.ColumnByName("LastName", &lastName); err != nil {
				return err
			}
			fmt.Fprintf(w, "Found record name with %s, %s", firstName, lastName)
		}
		return err
	})
	return err
}

Java

static void writeAndReadUsingDml(DatabaseClient dbClient) {
  dbClient
      .readWriteTransaction()
      .run(
          new TransactionCallable<Void>() {
            @Override
            public Void run(TransactionContext transaction) throws Exception {
              // Insert record.
              String sql =
                  "INSERT INTO Singers (SingerId, FirstName, LastName) "
                      + " VALUES (11, 'Timothy', 'Campbell')";
              long rowCount = transaction.executeUpdate(Statement.of(sql));
              System.out.printf("%d record inserted.\n", rowCount);
              // Read newly inserted record.
              sql = "SELECT FirstName, LastName FROM Singers WHERE SingerId = 11";
              // We use a try-with-resource block to automatically release resources held by
              // ResultSet.
              try (ResultSet resultSet = transaction.executeQuery(Statement.of(sql))) {
                while (resultSet.next()) {
                  System.out.printf(
                      "%s %s\n",
                      resultSet.getString("FirstName"), resultSet.getString("LastName"));
                }
              }
              return null;
            }
          });
}

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

database.runTransaction(async (err, transaction) => {
  if (err) {
    console.error(err);
    return;
  }
  try {
    await transaction.runUpdate({
      sql: `INSERT Singers (SingerId, FirstName, LastName)
        VALUES (11, 'Timothy', 'Campbell')`,
    });

    const [rows] = await transaction.run({
      sql: 'SELECT FirstName, LastName FROM Singers',
    });
    rows.forEach(row => {
      const json = row.toJSON();
      console.log(`${json.FirstName} ${json.LastName}`);
    });

    await transaction.commit();
  } catch (err) {
    console.error('ERROR:', err);
  } finally {
    // Close the database when finished.
    database.close();
  }
});

PHP

use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\Transaction;

/**
 * Writes then reads data inside a Transaction with a DML statement.
 *
 * The database and table must already exist and can be created using
 * `create_database`.
 * Example:
 * ```
 * insert_data($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function write_read_with_dml($instanceId, $databaseId)
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $database->runTransaction(function (Transaction $t) use ($spanner) {
        $rowCount = $t->executeUpdate(
            "INSERT Singers (SingerId, FirstName, LastName) "
            . " VALUES (11, 'Timothy', 'Campbell')");

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

        $results = $t->execute("SELECT FirstName, LastName FROM Singers WHERE SingerId = 11");

        foreach ($results as $row) {
            printf('%s %s' . PHP_EOL, $row['FirstName'], $row['LastName']);
        }

        $t->commit();
    });
}

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)

def write_then_read(transaction):
    # Insert record.
    row_ct = transaction.execute_update(
        "INSERT Singers (SingerId, FirstName, LastName) "
        " VALUES (11, 'Timothy', 'Campbell')"
    )
    print("{} record(s) inserted.".format(row_ct))

    # Read newly inserted record.
    results = transaction.execute_sql(
        "SELECT FirstName, LastName FROM Singers WHERE SingerId = 11"
    )
    for result in results:
        print("FirstName: {}, LastName: {}".format(*result))

database.run_in_transaction(write_then_read)

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 = 0

client.transaction do |transaction|
  row_count = transaction.execute_update(
    "INSERT INTO Singers (SingerId, FirstName, LastName) VALUES (11, 'Timothy', 'Campbell')"
  )
  puts "#{row_count} record updated."
  transaction.execute("SELECT FirstName, LastName FROM Singers WHERE SingerId = 11").rows.each do |row|
    puts "#{row[:FirstName]} #{row[:LastName]}"
  end
end

Écrire des horodatages de commit

Pour écrire l'horodatage de commit dans une instruction LMD, utilisez la fonction PENDING_COMMIT_TIMESTAMP. Cloud Spanner sélectionne l'horodatage de commit lorsque la transaction est enregistrée.

L'exemple de code suivant utilise la fonction PENDING_COMMIT_TIMESTAMP pour écrire l'horodatage de commit dans la colonne LastUpdateTime.

C++

void DmlStandardUpdateWithTimestamp(google::cloud::spanner::Client client) {
  using ::google::cloud::StatusOr;
  namespace spanner = ::google::cloud::spanner;
  auto commit_result = client.Commit(
      [&client](spanner::Transaction txn) -> StatusOr<spanner::Mutations> {
        auto update = client.ExecuteDml(
            std::move(txn),
            spanner::SqlStatement(
                "UPDATE Albums SET LastUpdateTime = PENDING_COMMIT_TIMESTAMP() "
                "WHERE SingerId = 1"));
        if (!update) return update.status();
        return spanner::Mutations{};
      });
  if (!commit_result) {
    throw std::runtime_error(commit_result.status().message());
  }
  std::cout << "Update was successful "
            << "[spanner_dml_standard_update_with_timestamp]\n";
}

C#

public static async Task UpdateUsingDmlWithTimestampCoreAsync(
    string projectId,
    string instanceId,
    string databaseId)
{
    string connectionString =
        $"Data Source=projects/{projectId}/instances/{instanceId}"
        + $"/databases/{databaseId}";

    // Create connection to Cloud Spanner.
    using (var connection =
        new SpannerConnection(connectionString))
    {
        await connection.OpenAsync();

        SpannerCommand cmd = connection.CreateDmlCommand(
           "UPDATE Albums "
           + "SET LastUpdateTime = PENDING_COMMIT_TIMESTAMP() "
           + "WHERE SingerId = 1");
        int rowCount = await cmd.ExecuteNonQueryAsync();
        Console.WriteLine($"{rowCount} row(s) updated...");
    }
}

Go


import (
	"context"
	"fmt"
	"io"

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

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

	_, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
		stmt := spanner.Statement{
			SQL: `UPDATE Albums
				SET LastUpdateTime = PENDING_COMMIT_TIMESTAMP()
				WHERE SingerId = 1`,
		}
		rowCount, err := txn.Update(ctx, stmt)
		if err != nil {
			return err
		}
		fmt.Fprintf(w, "%d record(s) updated.\n", rowCount)
		return nil
	})
	return err
}

Java

static void updateUsingDmlWithTimestamp(DatabaseClient dbClient) {
  dbClient
      .readWriteTransaction()
      .run(
          new TransactionCallable<Void>() {
            @Override
            public Void run(TransactionContext transaction) throws Exception {
              String sql =
                  "UPDATE Albums "
                      + "SET LastUpdateTime = PENDING_COMMIT_TIMESTAMP() WHERE SingerId = 1";
              long rowCount = transaction.executeUpdate(Statement.of(sql));
              System.out.printf("%d records updated.\n", rowCount);
              return null;
            }
          });
}

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

database.runTransaction(async (err, transaction) => {
  if (err) {
    console.error(err);
    return;
  }
  try {
    const [rowCount] = await transaction.runUpdate({
      sql: `UPDATE Albums
        SET LastUpdateTime = PENDING_COMMIT_TIMESTAMP()
        WHERE SingerId = 1`,
    });

    console.log(`Successfully updated ${rowCount} records.`);
    await transaction.commit();
  } catch (err) {
    console.error('ERROR:', err);
  } finally {
    // Close the database when finished.
    database.close();
  }
});

PHP

use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\Transaction;

/**
 * Update data with a DML statement using timestamps.
 *
 * The database and table must already exist and can be created using
 * `create_database`.
 * Example:
 * ```
 * insert_data($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function update_data_with_dml_timestamp($instanceId, $databaseId)
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $database->runTransaction(function (Transaction $t) use ($spanner) {
        $rowCount = $t->executeUpdate(
            "UPDATE Albums "
            . "SET LastUpdateTime = PENDING_COMMIT_TIMESTAMP() WHERE SingerId = 1");
        $t->commit();
        printf('Updated %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)

def update_albums(transaction):
    row_ct = transaction.execute_update(
        "UPDATE Albums "
        "SET LastUpdateTime = PENDING_COMMIT_TIMESTAMP() "
        "WHERE SingerId = 1"
    )

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

database.run_in_transaction(update_albums)

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 = 0

client.transaction do |transaction|
  row_count = transaction.execute_update(
    "UPDATE Albums SET LastUpdateTime = PENDING_COMMIT_TIMESTAMP() WHERE SingerId = 1"
  )
end

puts "#{row_count} records updated."

Obtenir le plan de requête

Vous pouvez récupérer un plan de requête à l'aide de Cloud Console, des bibliothèques clientes et de l'outil de ligne de commande gcloud.

Utiliser le LMD partitionné

Le LMD partitionné est conçu pour les mises à jour et les suppressions groupées, notamment les nettoyages et les remplissages périodiques.

Exécuter des instructions avec l'outil de ligne de commande gcloud

Pour exécuter une instruction en LMD partitionné, utilisez la commande gcloud spanner databases execute-sql avec l'option --enable-partitioned-dml. L'exemple suivant met à jour les lignes de la table Albums.

gcloud spanner databases execute-sql example-db \
    --instance=test-instance --enable-partitioned-dml \
    --sql='UPDATE Albums SET MarketingBudget = 0 WHERE MarketingBudget IS NULL'

Modifier des données avec la bibliothèque cliente

L'exemple de code suivant met à jour la colonne MarketingBudget de la table Albums.

C++

Vous utilisez la fonction ExecutePartitionedDml() pour exécuter une instruction DML partitionnée.

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::runtime_error(result.status().message());
  std::cout << "Update was successful [spanner_dml_partitioned_update]\n";
}

C#

Utilisez la méthode ExecutePartitionedUpdateAsync() pour exécuter une instruction en mode LMD partitionné.

public static async Task UpdateUsingPartitionedDmlCoreAsync(
    string projectId,
    string instanceId,
    string databaseId)
{
    string connectionString =
        $"Data Source=projects/{projectId}/instances/{instanceId}"
        + $"/databases/{databaseId}";

    // Create connection to Cloud Spanner.
    using (var connection =
        new SpannerConnection(connectionString))
    {
        await connection.OpenAsync();

        SpannerCommand cmd = connection.CreateDmlCommand(
            "UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1"
        );
        long rowCount = await cmd.ExecutePartitionedUpdateAsync();
        Console.WriteLine($"{rowCount} row(s) updated...");
    }
}

Go

Utilisez la méthode PartitionedUpdate() pour exécuter une instruction en mode LMD partitionné.


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

Utilisez la méthode executePartitionedUpdate() pour exécuter une instruction en mode LMD partitionné.

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

Utilisez la méthode runPartitionedUpdate() pour exécuter une instruction en mode LMD partitionné.

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

Utilisez la méthode executePartitionedUpdate() pour exécuter une instruction en mode LMD partitionné.

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($instanceId, $databaseId)
{
    $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

Utilisez la méthode execute_partitioned_dml() pour exécuter une instruction en mode LMD partitionné.

# 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

Utilisez la méthode execute_partitioned_update() pour exécuter une instruction en mode LMD partitionné.

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

L'exemple de code suivant supprime les lignes de la table Singers, en fonction de la colonne 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::runtime_error(result.status().message());
  std::cout << "Delete was successful [spanner_dml_partitioned_delete]\n";
}

C#

public static async Task DeleteUsingPartitionedDmlCoreAsync(
    string projectId,
    string instanceId,
    string databaseId)
{
    string connectionString =
        $"Data Source=projects/{projectId}/instances/{instanceId}"
        + $"/databases/{databaseId}";

    // Create connection to Cloud Spanner.
    using (var connection =
        new SpannerConnection(connectionString))
    {
        await connection.OpenAsync();

        SpannerCommand cmd = connection.CreateDmlCommand(
            "DELETE FROM Singers WHERE SingerId > 10"
        );
        long rowCount = await cmd.ExecutePartitionedUpdateAsync();
        Console.WriteLine($"{rowCount} row(s) deleted...");
    }
}

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($instanceId, $databaseId)
{
    $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."

Utiliser le LMD par lots

Si vous devez éviter le temps de latence supplémentaire occasionné par l'envoi de plusieurs requêtes en série, utilisez le LMD par lots pour envoyer plusieurs instructions INSERT, UPDATE ou DELETE au sein d'une même transaction :

C++

Utilisez la fonction ExecuteBatchDml() pour exécuter une liste d'instructions LMD.

void DmlBatchUpdate(google::cloud::spanner::Client client) {
  namespace spanner = ::google::cloud::spanner;

  auto commit_result =
      client.Commit([&client](spanner::Transaction const& txn)
                        -> google::cloud::StatusOr<spanner::Mutations> {
        std::vector<spanner::SqlStatement> statements = {
            spanner::SqlStatement("INSERT INTO Albums"
                                  " (SingerId, AlbumId, AlbumTitle,"
                                  " MarketingBudget)"
                                  " VALUES (1, 3, 'Test Album Title', 10000)"),
            spanner::SqlStatement("UPDATE Albums"
                                  " SET MarketingBudget = MarketingBudget * 2"
                                  " WHERE SingerId = 1 and AlbumId = 3")};
        auto result = client.ExecuteBatchDml(txn, statements);
        if (!result) return result.status();
        for (std::size_t i = 0; i < result->stats.size(); ++i) {
          std::cout << result->stats[i].row_count << " rows affected"
                    << " for the statement " << (i + 1) << ".\n";
        }
        // Batch operations may have partial failures, in which case
        // ExecuteBatchDml returns with success, but the application should
        // verify that all statements completed successfully
        if (!result->status.ok()) return result->status;
        return spanner::Mutations{};
      });
  if (!commit_result) {
    throw std::runtime_error(commit_result.status().message());
  }
  std::cout << "Update was successful [spanner_dml_batch_update]\n";
}

C#

Utilisez la méthode connection.CreateBatchDmlCommand() pour créer votre commande par lot, puis la méthode Add pour ajouter des instructions LMD. Enfin, exécutez l'ensemble à l'aide de la méthode ExecuteNonQueryAsync().

public static async Task UpdateUsingBatchDmlCoreAsync(
    string projectId,
    string instanceId,
    string databaseId)
{
    string connectionString =
        $"Data Source=projects/{projectId}/instances/{instanceId}"
        + $"/databases/{databaseId}";

    // Create connection to Cloud Spanner.
    using (var connection =
        new SpannerConnection(connectionString))
    {
        await connection.OpenAsync();

        SpannerBatchCommand cmd = connection.CreateBatchDmlCommand();
        //var cmd = new SpannerBatchCommand(connection);

        cmd.Add("INSERT INTO Albums "
            + "(SingerId, AlbumId, AlbumTitle, MarketingBudget) "
            + "VALUES (1, 3, 'Test Album Title', 10000)");

        cmd.Add("UPDATE Albums "
            + "SET MarketingBudget = MarketingBudget * 2 "
            + "WHERE SingerId = 1 and AlbumId = 3");

        IEnumerable<long> affectedRows = await cmd.ExecuteNonQueryAsync();
        Console.WriteLine(
            $"Executed {affectedRows.Count()} "
            + "SQL statements using Batch DML.");
    }
}

Go

Utilisez la méthode BatchUpdate() pour exécuter un tableau d'objets constitué d'instructions LMD Statement.


import (
	"context"
	"fmt"
	"io"

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

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

	_, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
		stmts := []spanner.Statement{
			{SQL: `INSERT INTO Albums
				(SingerId, AlbumId, AlbumTitle, MarketingBudget)
				VALUES (1, 3, 'Test Album Title', 10000)`},
			{SQL: `UPDATE Albums
				SET MarketingBudget = MarketingBudget * 2
				WHERE SingerId = 1 and AlbumId = 3`},
		}
		rowCounts, err := txn.BatchUpdate(ctx, stmts)
		if err != nil {
			return err
		}
		fmt.Fprintf(w, "Executed %d SQL statements using Batch DML.\n", len(rowCounts))
		return nil
	})
	return err
}

Java

Utilisez la méthode transaction.batchUpdate() pour exécuter un objet ArrayList constitué d'instructions LMD Statement.

static void updateUsingBatchDml(DatabaseClient dbClient) {
  dbClient
      .readWriteTransaction()
      .run(
          new TransactionCallable<Void>() {
            @Override
            public Void run(TransactionContext transaction) throws Exception {
              List<Statement> stmts = new ArrayList<Statement>();
              String sql =
                  "INSERT INTO Albums "
                      + "(SingerId, AlbumId, AlbumTitle, MarketingBudget) "
                      + "VALUES (1, 3, 'Test Album Title', 10000) ";
              stmts.add(Statement.of(sql));
              sql =
                  "UPDATE Albums "
                      + "SET MarketingBudget = MarketingBudget * 2 "
                      + "WHERE SingerId = 1 and AlbumId = 3";
              stmts.add(Statement.of(sql));
              long[] rowCounts;
              try {
                rowCounts = transaction.batchUpdate(stmts);
              } catch (SpannerBatchUpdateException e) {
                rowCounts = e.getUpdateCounts();
              }
              for (int i = 0; i < rowCounts.length; i++) {
                System.out.printf("%d record updated by stmt %d.\n", rowCounts[i], i);
              }
              return null;
            }
          });
}

Node.js

Utilisez transaction.batchUpdate() pour exécuter une liste d'instructions LMD.

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

const insert = {
  sql: `INSERT INTO Albums (SingerId, AlbumId, AlbumTitle, MarketingBudget)
    VALUES (1, 3, "Test Album Title", 10000)`,
};

const update = {
  sql: `UPDATE Albums SET MarketingBudget = MarketingBudget * 2
    WHERE SingerId = 1 and AlbumId = 3`,
};

const dmlStatements = [insert, update];

try {
  await database.runTransactionAsync(async transaction => {
    const [rowCounts] = await transaction.batchUpdate(dmlStatements);
    await transaction.commit();
    console.log(
      `Successfully executed ${rowCounts.length} SQL statements using Batch DML.`
    );
  });
} catch (err) {
  console.error('ERROR:', err);
  throw err;
} finally {
  // Close the database when finished.
  database.close();
}

PHP

Utilisez executeUpdateBatch() pour créer une liste d'instructions LMD, puis utilisez commit() pour exécuter les instructions.

use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\Transaction;

/**
 * Updates sample data in the database with Batch DML.
 *
 * This requires 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_with_batch_dml($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function update_data_with_batch_dml($instanceId, $databaseId)
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $batchDmlResult = $database->runTransaction(function (Transaction $t) {
        $result = $t->executeUpdateBatch([
            [
                'sql' => "INSERT INTO Albums "
                . "(SingerId, AlbumId, AlbumTitle, MarketingBudget) "
                . "VALUES (1, 3, 'Test Album Title', 10000)"
            ],
            [
                'sql' => "UPDATE Albums "
                . "SET MarketingBudget = MarketingBudget * 2 "
                . "WHERE SingerId = 1 and AlbumId = 3"
            ],
        ]);
        $t->commit();
        $rowCounts = count($result->rowCounts());
        printf('Executed %s SQL statements using Batch DML.' . PHP_EOL,
            $rowCounts);
    });
}

Python

Utilisez transaction.batch_update() pour exécuter plusieurs chaînes correspondant à des instructions LMD.

# 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)

insert_statement = (
    "INSERT INTO Albums "
    "(SingerId, AlbumId, AlbumTitle, MarketingBudget) "
    "VALUES (1, 3, 'Test Album Title', 10000)"
)

update_statement = (
    "UPDATE Albums "
    "SET MarketingBudget = MarketingBudget * 2 "
    "WHERE SingerId = 1 and AlbumId = 3"
)

def update_albums(transaction):
    row_cts = transaction.batch_update([insert_statement, update_statement])

    print("Executed {} SQL statements using Batch DML.".format(len(row_cts)))

database.run_in_transaction(update_albums)

Ruby

Utilisez transaction.batch_update pour exécuter plusieurs chaînes correspondant à des instructions LMD.

# 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_counts = nil
client.transaction do |transaction|
  row_counts = transaction.batch_update do |b|
    b.batch_update "INSERT INTO Albums "\
      "(SingerId, AlbumId, AlbumTitle, MarketingBudget) "\
      "VALUES (1, 3, 'Test Album Title', 10000)"
    b.batch_update "UPDATE Albums "\
      "SET MarketingBudget = MarketingBudget * 2 "\
      "WHERE SingerId = 1 and AlbumId = 3"
  end
end

statement_count = row_counts.count

puts "Executed #{statement_count} SQL statements using Batch DML."