Inserire, aggiornare ed eliminare dati utilizzando il Data Manipulation Language (DML)

Questa pagina descrive come inserire, aggiornare ed eliminare i dati di Spanner utilizzando le istruzioni DML (Data Manipulation Language). Puoi eseguire istruzioni DML utilizzando le librerie client, la console Google Cloud e lo strumento a riga di comando gcloud. Puoi eseguire istruzioni DML partizionate utilizzando le librerie client e lo strumento a riga di comando gcloud.

Per il riferimento completo alla sintassi DML, consulta Sintassi del linguaggio di manipolazione dei dati per i database in dialetto GoogleSQL o Lingua di manipolazione dei dati PostgreSQL per i database in dialetto PostgreSQL

Nota: per esplorare Spanner utilizzando un'istanza di prova gratuita di 90 giorni, consulta Creare un'istanza di prova gratuita di Spanner.

Utilizzare DML

Il linguaggio DML supporta gli statement INSERT, UPDATE e DELETE nella console Google Cloud, in Google Cloud CLI e nelle librerie client.

Chiusura della serratura

Esegui istruzioni DML all'interno di transazioni di lettura/scrittura. Quando Spanner legge i dati, acquisisce i blocchi di lettura condivisi su porzioni limitate degli intervalli di righe letti. Nello specifico, acquisisce questi blocchi solo sulle colonne a cui accedi. Le chiusure possono includere dati che nonsoddisfano la condizione di filtro della clausola WHERE.

Quando Spanner modifica i dati utilizzando le istruzioni DML, acquisisce i blocchi esclusivi sui dati specifici che stai modificando. Inoltre, acquisisce i blocchi condivisi nello stesso modo in cui accade quando leggi i dati. Se la richiesta include intervalli di righe di grandi dimensioni o un'intera tabella, i blocchi condivisi potrebbero impedire ad altre transazioni di progredire in parallelo.

Per modificare i dati nel modo più efficiente possibile, utilizza una clausola WHERE che consenta a Spanner di leggere solo le righe necessarie. Puoi raggiungere questo obiettivo con un filtro sulla chiave primaria o sulla chiave di un indice secondario. La clausola WHERE limita l'ambito dei blocchi condivisi e consente a Spanner di elaborare l'aggiornamento in modo più efficiente.

Ad esempio, supponiamo che uno dei musicisti nella tabella Singers cambi il suo nome e tu debba aggiornare il nome nel database. Potresti eseguire la seguente istruzione DML, ma questa forza Spanner a eseguire la scansione dell'intera tabella e acquisisce i blocchi condivisi che coprono l'intera tabella. Di conseguenza, Spanner deve leggere più dati del necessario e le transazioni concorrenti non possono modificare i dati in parallelo:

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

Per rendere l'aggiornamento più efficiente, includi la colonna SingerId nella clausola WHERE. La colonna SingerId è l'unica colonna della chiave primaria per la tabella Singers:

-- ANTI-PATTERN: SENDING AN UPDATE THAT MUST SCAN THE ENTIRE TABLE

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

Se non è presente un indice su FirstName o LastName, devi eseguire la scansione dell'intera tabella per trovare i cantanti target. Se non vuoi aggiungere un indice secondario per rendere più efficiente l'aggiornamento, includi la colonna SingerId nella clausola WHERE.

La colonna SingerId è l'unica colonna di chiave primaria per la tabella Singers. Per trovarlo, esegui SELECT in una transazione di sola lettura distinta prima della transazione di aggiornamento:

  SELECT SingerId
  FROM Singers
  WHERE FirstName = "Marc" AND LastName = "Richards"

  -- Recommended: Including a seekable filter in the where clause

  UPDATE Singers SET FirstName = "Marcel"
  WHERE SingerId = 1;

Contemporaneità

Spanner esegue in sequenza tutte le istruzioni SQL (SELECT, INSERT, UPDATE e DELETE) all'interno di una transazione. Non vengono eseguiti contemporaneamente. L'unica eccezione è che Spanner potrebbe eseguire più istruzioni SELECT contemporaneamente, perché sono operazioni di sola lettura.

Limiti per le transazioni

Una transazione che include istruzioni DML ha gli stessi limiti di qualsiasi altra transazione. Se hai modifiche su larga scala, valuta la possibilità di utilizzare DML partizionato.

• Se le istruzioni DML in una transazione generano più di 80.000 mutazioni, l'istruzione DML che fa superare il limite alla transazione restituisce un errore BadUsage con un messaggio relativo a un numero eccessivo di mutazioni.

• Se le istruzioni DML in una transazione generano una transazione più grande di 100 MB, l'istruzione DML che spinge la transazione oltre il limite restituisce un errore BadUsage con un messaggio relativo al superamento del limite di dimensioni della transazione.

Le mutazioni eseguite utilizzando la DML non vengono restituite al client. Vengono uniti alla richiesta di commit al momento del commit e concorrono al raggiungimento dei limiti di dimensione massimi. Anche se le dimensioni della richiesta di commit inviata sono ridotte, la transazione potrebbe comunque superare il limite di dimensioni consentito.

Esegui istruzioni nella console Google Cloud

Nota: Spanner Studio (in precedenza Query nella console Google Cloud) supporta le operazioni SQL, DML e DDL in un unico editor. Per ulteriori informazioni, consulta Gestire i dati utilizzando la console Google Cloud.

Per eseguire un'istruzione DML nella console Google Cloud:

1. Vai alla pagina Istanze di Spanner.

   Vai alla pagina delle istanze

2. Seleziona il progetto nell'elenco a discesa della barra degli strumenti.

3. Fai clic sul nome dell'istanza contenente il database per andare alla pagina Dettagli istanza.

4. Nella scheda Panoramica, fai clic sul nome del database. Viene visualizzata la pagina Dettagli database.

5. Fai clic su Spanner Studio.

6. Inserisci un'istruzione DML. Ad esempio, l'istruzione seguente aggiunge una nuova riga alla tabella Singers.

   INSERT Singers (SingerId, FirstName, LastName)
   VALUES (1, 'Marc', 'Richards')
   

7. Fai clic su Esegui query. La console Google Cloud mostra il risultato.

Nota: quando fai clic su Esegui query, la transazione viene eseguita automaticamente.

Esegui istruzioni con Google Cloud CLI

Per eseguire istruzioni DML, utilizza il comando gcloud spanner databases execute-sql. L'esempio seguente aggiunge una nuova riga alla tabella Singers.

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

Modificare i dati utilizzando la libreria client

Per eseguire istruzioni DML utilizzando la libreria client:

• Crea una transazione di lettura/scrittura.
• Chiama il metodo della libreria client per l'esecuzione della DML e passa l'istruzione DML.
• Utilizza il valore restituito del metodo di esecuzione DML per ottenere il numero di righe inserite, aggiornate o eliminate.

Il seguente esempio di codice inserisce una nuova riga nella tabella Singers.

C++

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

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 std::move(insert).status();
        rows_inserted = insert->RowsModified();
        return spanner::Mutations{};
      });
  if (!commit_result) throw std::move(commit_result).status();
  std::cout << "Rows inserted: " << rows_inserted;
  std::cout << "Insert was successful [spanner_dml_standard_insert]\n";
}

C#

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

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

public class InsertUsingDmlCoreAsyncSample
{
    public async Task<int> InsertUsingDmlCoreAsync(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("INSERT Singers (SingerId, FirstName, LastName) VALUES (10, 'Virginia', 'Watson')");
        int rowCount = await cmd.ExecuteNonQueryAsync();

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

Vai

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

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

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

static void insertUsingDml(DatabaseClient dbClient) {
  dbClient
      .readWriteTransaction()
      .run(transaction -> {
        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

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

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

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

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(string $instanceId, string $databaseId): void
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

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

Python

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

# 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 INTO Singers (SingerId, FirstName, LastName) "
        " VALUES (10, 'Virginia', 'Watson')"
    )

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

database.run_in_transaction(insert_singers)

Ruby

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

# 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'esempio di codice seguente aggiorna la colonna MarketingBudget della tabella Albums in base a una clausola 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 std::move(update).status();
        return spanner::Mutations{};
      });
  if (!commit_result) throw std::move(commit_result).status();
  std::cout << "Update was successful [spanner_dml_standard_update]\n";
}

C#

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

public class UpdateUsingDmlCoreAsyncSample
{
    public async Task<int> UpdateUsingDmlCoreAsync(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 = MarketingBudget * 2 WHERE SingerId = 1 and AlbumId = 1");
        int rowCount = await cmd.ExecuteNonQueryAsync();

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

Vai

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(transaction -> {
        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(string $instanceId, string $databaseId): void
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $database->runTransaction(function (Transaction $t) {
        $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."

Il seguente esempio di codice elimina tutte le righe della tabella Singers in cui la colonna FirstName è 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 std::move(dele).status();
    return spanner::Mutations{};
  });
  if (!commit_result) throw std::move(commit_result).status();
  std::cout << "Delete was successful [spanner_dml_standard_delete]\n";
}

C#

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

public class DeleteUsingDmlCoreAsyncSample
{
    public async Task<int> DeleteUsingDmlCoreAsync(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 FirstName = 'Alice'");
        int rowCount = await cmd.ExecuteNonQueryAsync();

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

Vai

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(transaction -> {
        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(string $instanceId, string $databaseId): void
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $database->runTransaction(function (Transaction $t) {
        $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."

Il seguente esempio, solo per i database in dialetto GoogleSQL, utilizza un STRUCT con parametri vincolati per aggiornare il LastName nelle righe filtrate per FirstName e LastName.

GoogleSQL

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 std::move(dml_result).status();
        rows_modified = dml_result->RowsModified();
        return spanner::Mutations{};
      });
  if (!commit_result) throw std::move(commit_result).status();
  std::cout << rows_modified
            << " update was successful [spanner_dml_structs]\n";
}

C#

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

public class UpdateUsingDmlWithStructCoreAsyncSample
{
    public async Task<int> 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}";

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

        using var 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...");
        return rowCount;
    }
}

Vai

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(transaction -> {
        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(string $instanceId, string $databaseId): void
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $database->runTransaction(function (Transaction $t) {
        $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."

Modifica i dati con le istruzioni DML che restituiscono dati

La clausola THEN RETURN (database in dialetto GoogleSQL) o la clausola RETURNING (database in dialetto PostgreSQL) è progettata per gli scenari in cui vuoi recuperare i dati dalle righe modificate. Questa opzione è particolarmente utile quando vuoi visualizzare valori non specificati nelle istruzioni DML, nei valori predefiniti o nelle colonne generate.

Per eseguire istruzioni DML che restituiscono dati utilizzando la libreria client:

• Crea una transazione di lettura/scrittura.
• Chiama il metodo della libreria client per l'esecuzione della query e passa l'istruzione DML restituita per ottenere i risultati.

Il seguente esempio di codice inserisce una nuova riga nella tabella Singers e restituisce la colonna FullName generata dei record inseriti.

GoogleSQL

C++

void InsertUsingDmlReturning(google::cloud::spanner::Client client) {
  // Insert records into SINGERS table and return the generated column
  // FullName of the inserted records using `THEN RETURN FullName`.
  auto commit = client.Commit(
      [&client](google::cloud::spanner::Transaction txn)
          -> google::cloud::StatusOr<google::cloud::spanner::Mutations> {
        auto sql = google::cloud::spanner::SqlStatement(R"""(
            INSERT INTO Singers (SingerId, FirstName, LastName)
              VALUES (12, 'Melissa', 'Garcia'),
                     (13, 'Russell', 'Morales'),
                     (14, 'Jacqueline', 'Long'),
                     (15, 'Dylan', 'Shaw')
              THEN RETURN FullName
        )""");
        using RowType = std::tuple<std::string>;
        auto rows = client.ExecuteQuery(std::move(txn), std::move(sql));
        // Note: This mutator might be re-run, or its effects discarded, so
        // changing non-transactional state (e.g., by producing output) is,
        // in general, not something to be imitated.
        for (auto& row : google::cloud::spanner::StreamOf<RowType>(rows)) {
          if (!row) return std::move(row).status();
          std::cout << "FullName: " << std::get<0>(*row) << "\n";
        }
        std::cout << "Inserted row(s) count: " << rows.RowsModified() << "\n";
        return google::cloud::spanner::Mutations{};
      });
  if (!commit) throw std::move(commit).status();
}

C#

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

public class InsertUsingDmlReturningAsyncSample
{
    public async Task<List<string>> InsertUsingDmlReturningAsync(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();

        // Insert records into the SINGERS table and return the
        // generated column FullName of the inserted records using
        // 'THEN RETURN FullName'.
        // It is also possible to return all columns of all the
        // inserted records by using 'THEN RETURN *'.
        using var cmd = connection.CreateDmlCommand(
            @"INSERT INTO Singers(SingerId, FirstName, LastName) VALUES
            (6, 'Melissa', 'Garcia'), 
            (7, 'Russell', 'Morales'), 
            (8, 'Jacqueline', 'Long'), 
            (9, 'Dylan', 'Shaw') THEN RETURN FullName");

        var reader = await cmd.ExecuteReaderAsync();
        var insertedSingerNames = new List<string>();
        while (await reader.ReadAsync())
        {
            insertedSingerNames.Add(reader.GetFieldValue<string>("FullName"));
        }

        Console.WriteLine($"{insertedSingerNames.Count} row(s) inserted...");
        return insertedSingerNames;
    }
}

Vai

import (
	"context"
	"fmt"
	"io"

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

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

	// Insert records into the SINGERS table and returns the
	// generated column FullName of the inserted records using
	// 'THEN RETURN FullName'.
	// It is also possible to return all columns of all the
	// inserted records by using 'THEN RETURN *'.
	_, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
		stmt := spanner.Statement{
			SQL: `INSERT INTO Singers (SingerId, FirstName, LastName)
			        VALUES (21, 'Melissa', 'Garcia'),
			               (22, 'Russell', 'Morales'),
			               (23, 'Jacqueline', 'Long'),
			               (24, 'Dylan', 'Shaw')
			        THEN RETURN FullName`,
		}
		iter := txn.Query(ctx, stmt)
		defer iter.Stop()
		for {
			row, err := iter.Next()
			if err == iterator.Done {
				break
			}
			if err != nil {
				return err
			}
			var fullName string
			if err := row.Columns(&fullName); err != nil {
				return err
			}
			fmt.Fprintf(w, "%s\n", fullName)
		}
		fmt.Fprintf(w, "%d record(s) inserted.\n", iter.RowCount)
		return nil
	})
	return err
}

Java

import com.google.cloud.spanner.DatabaseClient;
import com.google.cloud.spanner.DatabaseId;
import com.google.cloud.spanner.ResultSet;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import com.google.cloud.spanner.Statement;

public class InsertUsingDmlReturningSample {

  static void insertUsingDmlReturning() {
    // TODO(developer): Replace these variables before running the sample.
    final String projectId = "my-project";
    final String instanceId = "my-instance";
    final String databaseId = "my-database";
    insertUsingDmlReturning(projectId, instanceId, databaseId);
  }

  static void insertUsingDmlReturning(String projectId, String instanceId, String databaseId) {
    try (Spanner spanner =
        SpannerOptions.newBuilder()
            .setProjectId(projectId)
            .build()
            .getService()) {
      final DatabaseClient dbClient =
          spanner.getDatabaseClient(DatabaseId.of(projectId, instanceId, databaseId));
      // Insert records into the SINGERS table and returns the
      // generated column FullName of the inserted records using
      // ‘THEN RETURN FullName’.
      // It is also possible to return all columns of all the
      // inserted records by using ‘THEN RETURN *’.
      dbClient
          .readWriteTransaction()
          .run(
              transaction -> {
                String sql =
                    "INSERT INTO Singers (SingerId, FirstName, LastName) VALUES "
                        + "(12, 'Melissa', 'Garcia'), "
                        + "(13, 'Russell', 'Morales'), "
                        + "(14, 'Jacqueline', 'Long'), "
                        + "(15, 'Dylan', 'Shaw') THEN RETURN FullName";

                // readWriteTransaction.executeQuery(..) API should be used for executing
                // DML statements with RETURNING clause.
                try (ResultSet resultSet = transaction.executeQuery(Statement.of(sql))) {
                  while (resultSet.next()) {
                    System.out.println(resultSet.getString(0));
                  }
                  System.out.printf(
                      "Inserted row(s) count: %d\n", resultSet.getStats().getRowCountExact());
                }
                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,
});

function insertUsingDmlReturning(instanceId, databaseId) {
  // 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 [rows, stats] = await transaction.run({
        sql: 'INSERT Singers (SingerId, FirstName, LastName) VALUES (@id, @firstName, @lastName) THEN RETURN FullName',
        params: {
          id: 18,
          firstName: 'Virginia',
          lastName: 'Watson',
        },
      });

      const rowCount = Math.floor(stats[stats.rowCount]);
      console.log(
        `Successfully inserted ${rowCount} record into the Singers table.`
      );
      rows.forEach(row => {
        console.log(row.toJSON().FullName);
      });

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

PHP

use Google\Cloud\Spanner\SpannerClient;

/**
 * Inserts sample data into the given database using DML returning.
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function insert_dml_returning(string $instanceId, string $databaseId): void
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    // Insert records into SINGERS table and returns the generated column
    // FullName of the inserted records using ‘THEN RETURN FullName’. It is also
    // possible to return all columns of all the inserted records by using
    // ‘THEN RETURN *’.

    $sql = 'INSERT INTO Singers (SingerId, FirstName, LastName) '
        . "VALUES (12, 'Melissa', 'Garcia'), "
        . "(13, 'Russell', 'Morales'), "
        . "(14, 'Jacqueline', 'Long'), "
        . "(15, 'Dylan', 'Shaw') "
        . 'THEN RETURN FullName';

    $transaction = $database->transaction();
    $result = $transaction->execute($sql);
    foreach ($result->rows() as $row) {
        printf(
            '%s inserted.' . PHP_EOL,
            $row['FullName'],
        );
    }
    printf(
        'Inserted row(s) count: %d' . PHP_EOL,
        $result->stats()['rowCountExact']
    );
    $transaction->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)

# Insert records into the SINGERS table and returns the
# generated column FullName of the inserted records using
# 'THEN RETURN FullName'.
# It is also possible to return all columns of all the
# inserted records by using 'THEN RETURN *'.
def insert_singers(transaction):
    results = transaction.execute_sql(
        "INSERT INTO Singers (SingerId, FirstName, LastName) VALUES "
        "(21, 'Luann', 'Chizoba'), "
        "(22, 'Denis', 'Patricio'), "
        "(23, 'Felxi', 'Ronan'), "
        "(24, 'Dominik', 'Martyna') "
        "THEN RETURN FullName"
    )
    for result in results:
        print("FullName: {}".format(*result))
    print("{} record(s) inserted.".format(results.stats.row_count_exact))

database.run_in_transaction(insert_singers)

Ruby

require "google/cloud/spanner"

##
# This is a snippet for showcasing how to use DML return feature with insert
# operation.
#
# @param project_id  [String] The ID of the Google Cloud project.
# @param instance_id [String] The ID of the spanner instance.
# @param database_id [String] The ID of the database.
#
def spanner_insert_dml_returning project_id:, instance_id:, database_id:
  spanner = Google::Cloud::Spanner.new project: project_id
  client = spanner.client instance_id, database_id

  client.transaction do |transaction|
    # Insert records into the SINGERS table and returns the generated column
    # FullName of the inserted records using ‘THEN RETURN FullName’.
    # It is also possible to return all columns of all the inserted records
    # by using ‘THEN RETURN *’.
    results = transaction.execute_query "INSERT INTO Singers (SingerId, FirstName, LastName)
                                         VALUES (12, 'Melissa', 'Garcia'), (13, 'Russell', 'Morales'), (14, 'Jacqueline', 'Long'), (15, 'Dylan', 'Shaw')
                                         THEN RETURN FullName"
    results.rows.each do |row|
      puts "Inserted singers with FullName: #{row[:FullName]}"
    end
    puts "Inserted row(s) count: #{results.row_count}"
  end
end

PostgreSQL

C++

void InsertUsingDmlReturning(google::cloud::spanner::Client client) {
  // Insert records into SINGERS table and return the generated column
  // FullName of the inserted records using `RETURNING FullName`.
  auto commit = client.Commit(
      [&client](google::cloud::spanner::Transaction txn)
          -> google::cloud::StatusOr<google::cloud::spanner::Mutations> {
        auto sql = google::cloud::spanner::SqlStatement(R"""(
            INSERT INTO Singers (SingerId, FirstName, LastName)
                VALUES (12, 'Melissa', 'Garcia'),
                       (13, 'Russell', 'Morales'),
                       (14, 'Jacqueline', 'Long'),
                       (15, 'Dylan', 'Shaw')
                RETURNING FullName
        )""");
        using RowType = std::tuple<std::string>;
        auto rows = client.ExecuteQuery(std::move(txn), std::move(sql));
        for (auto& row : google::cloud::spanner::StreamOf<RowType>(rows)) {
          if (!row) return std::move(row).status();
          std::cout << "FullName: " << std::get<0>(*row) << "\n";
        }
        std::cout << "Inserted row(s) count: " << rows.RowsModified() << "\n";
        return google::cloud::spanner::Mutations{};
      });
  if (!commit) throw std::move(commit).status();
}

C#

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

public class InsertUsingDmlReturningAsyncPostgresSample
{
    public async Task<List<string>> InsertUsingDmlReturningAsyncPostgres(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();

        // Insert records into SINGERS table and return the
        // generated column FullName of the inserted records
        // using 'RETURNING FullName'.
        // It is also possible to return all columns of all the
        // inserted records by using 'RETURNING *'.
        using var cmd = connection.CreateDmlCommand(
            @"INSERT INTO Singers(SingerId, FirstName, LastName) VALUES
            (6, 'Melissa', 'Garcia'), 
            (7, 'Russell', 'Morales'), 
            (8, 'Jacqueline', 'Long'), 
            (9, 'Dylan', 'Shaw') RETURNING FullName");

        var reader = await cmd.ExecuteReaderAsync();
        var insertedSingerNames = new List<string>();
        while (await reader.ReadAsync())
        {
            insertedSingerNames.Add(reader.GetFieldValue<string>("fullname"));
        }

        Console.WriteLine($"{insertedSingerNames.Count} row(s) inserted...");
        return insertedSingerNames;
    }
}

Vai

import (
	"context"
	"fmt"
	"io"

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

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

	// Insert records into the SINGERS table and returns the
	// generated column FullName of the inserted records using
	// 'RETURNING FullName'.
	// It is also possible to return all columns of all the
	// inserted records by using 'RETURNING *'.
	_, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
		stmt := spanner.Statement{
			SQL: `INSERT INTO Singers (SingerId, FirstName, LastName)
			        VALUES (21, 'Melissa', 'Garcia'),
			               (22, 'Russell', 'Morales'),
			               (23, 'Jacqueline', 'Long'),
			               (24, 'Dylan', 'Shaw')
			        RETURNING FullName`,
		}
		iter := txn.Query(ctx, stmt)
		defer iter.Stop()
		for {
			row, err := iter.Next()
			if err == iterator.Done {
				break
			}
			if err != nil {
				return err
			}
			var fullName string
			if err := row.Columns(&fullName); err != nil {
				return err
			}
			fmt.Fprintf(w, "%s\n", fullName)
		}
		fmt.Fprintf(w, "%d record(s) inserted.\n", iter.RowCount)
		return nil
	})
	return err
}

Java

import com.google.cloud.spanner.DatabaseClient;
import com.google.cloud.spanner.DatabaseId;
import com.google.cloud.spanner.ResultSet;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import com.google.cloud.spanner.Statement;

public class PgInsertUsingDmlReturningSample {

  static void insertUsingDmlReturning() {
    // TODO(developer): Replace these variables before running the sample.
    final String projectId = "my-project";
    final String instanceId = "my-instance";
    final String databaseId = "my-database";
    insertUsingDmlReturning(projectId, instanceId, databaseId);
  }

  static void insertUsingDmlReturning(String projectId, String instanceId, String databaseId) {
    try (Spanner spanner =
        SpannerOptions.newBuilder()
            .setProjectId(projectId)
            .build()
            .getService()) {
      final DatabaseClient dbClient =
          spanner.getDatabaseClient(DatabaseId.of(projectId, instanceId, databaseId));
      // Insert records into SINGERS table and returns the
      // generated column FullName of the inserted records
      // using ‘RETURNING FullName’.
      // It is also possible to return all columns of all the
      // inserted records by using ‘RETURNING *’.
      dbClient
          .readWriteTransaction()
          .run(
              transaction -> {
                String sql =
                    "INSERT INTO Singers (SingerId, FirstName, LastName) VALUES "
                        + "(12, 'Melissa', 'Garcia'), "
                        + "(13, 'Russell', 'Morales'), "
                        + "(14, 'Jacqueline', 'Long'), "
                        + "(15, 'Dylan', 'Shaw') RETURNING FullName";

                // readWriteTransaction.executeQuery(..) API should be used for executing
                // DML statements with RETURNING clause.
                try (ResultSet resultSet = transaction.executeQuery(Statement.of(sql))) {
                  while (resultSet.next()) {
                    System.out.println(resultSet.getString(0));
                  }
                  System.out.printf(
                      "Inserted row(s) count: %d\n", resultSet.getStats().getRowCountExact());
                }
                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,
});

function pgInsertUsingDmlReturning(instanceId, databaseId) {
  // 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 [rows, stats] = await transaction.run({
        sql: 'INSERT Into Singers (SingerId, FirstName, LastName) VALUES ($1, $2, $3) RETURNING FullName',
        params: {
          p1: 18,
          p2: 'Virginia',
          p3: 'Watson',
        },
      });

      const rowCount = Math.floor(stats[stats.rowCount]);
      console.log(
        `Successfully inserted ${rowCount} record into the Singers table.`
      );
      rows.forEach(row => {
        console.log(row.toJSON().fullname);
      });

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

PHP

use Google\Cloud\Spanner\SpannerClient;

/**
 * Inserts sample data into the given postgresql database using DML returning.
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function pg_insert_dml_returning(string $instanceId, string $databaseId): void
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    // Insert records into SINGERS table and returns the generated column
    // FullName of the inserted records using ‘RETURNING FullName’. It is also
    // possible to return all columns of all the inserted records by using
    // ‘RETURNING *’.

    $sql = 'INSERT INTO Singers (Singerid, FirstName, LastName) '
      . "VALUES (12, 'Melissa', 'Garcia'), "
      . "(13, 'Russell', 'Morales'), "
      . "(14, 'Jacqueline', 'Long'), "
      . "(15, 'Dylan', 'Shaw') "
      . 'RETURNING FullName';

    $transaction = $database->transaction();
    $result = $transaction->execute($sql);
    foreach ($result->rows() as $row) {
        printf(
            '%s inserted.' . PHP_EOL,
            $row['fullname'],
        );
    }
    printf(
        'Inserted row(s) count: %d' . PHP_EOL,
        $result->stats()['rowCountExact']
    );
    $transaction->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)

# Insert records into the SINGERS table and returns the
# generated column FullName of the inserted records using
# 'RETURNING FullName'.
# It is also possible to return all columns of all the
# inserted records by using 'RETURNING *'.
def insert_singers(transaction):
    results = transaction.execute_sql(
        "INSERT INTO Singers (SingerId, FirstName, LastName) VALUES "
        "(21, 'Luann', 'Chizoba'), "
        "(22, 'Denis', 'Patricio'), "
        "(23, 'Felxi', 'Ronan'), "
        "(24, 'Dominik', 'Martyna') "
        "RETURNING FullName"
    )
    for result in results:
        print("FullName: {}".format(*result))
    print("{} record(s) inserted.".format(results.stats.row_count_exact))

database.run_in_transaction(insert_singers)

Ruby

require "google/cloud/spanner"

##
# This is a snippet for showcasing how to use DML return feature with insert
# operation in PostgreSql.
#
# @param project_id  [String] The ID of the Google Cloud project.
# @param instance_id [String] The ID of the spanner instance.
# @param database_id [String] The ID of the database.
#
def spanner_postgresql_insert_dml_returning project_id:, instance_id:, database_id:
  spanner = Google::Cloud::Spanner.new project: project_id
  client = spanner.client instance_id, database_id

  client.transaction do |transaction|
    # Insert records into SINGERS table and returns the generated column
    # FullName of the inserted records using ‘RETURNING FullName’.
    # It is also possible to return all columns of all the inserted
    # records by using ‘RETURNING *’.
    results = transaction.execute_query "INSERT INTO Singers (SingerId, FirstName, LastName)
                                         VALUES (12, 'Melissa', 'Garcia'), (13, 'Russell', 'Morales'), (14, 'Jacqueline', 'Long'), (15, 'Dylan', 'Shaw')
                                         RETURNING FullName"
    results.rows.each do |row|
      puts "Inserted singers with FullName: #{row[:fullname]}"
    end
    puts "Inserted row(s) count: #{results.row_count}"
  end
end

L'esempio di codice seguente aggiorna la colonna MarketingBudget della tabella Albums in base a una clausola WHERE e restituisce la colonna MarketingBudget modificata dei record aggiornati.

GoogleSQL

C++

void UpdateUsingDmlReturning(google::cloud::spanner::Client client) {
  // Update MarketingBudget column for records satisfying a particular
  // condition and return the modified MarketingBudget column of the
  // updated records using `THEN RETURN MarketingBudget`.
  auto commit = client.Commit(
      [&client](google::cloud::spanner::Transaction txn)
          -> google::cloud::StatusOr<google::cloud::spanner::Mutations> {
        auto sql = google::cloud::spanner::SqlStatement(R"""(
            UPDATE Albums SET MarketingBudget = MarketingBudget * 2
              WHERE SingerId = 1 AND AlbumId = 1
              THEN RETURN MarketingBudget
        )""");
        using RowType = std::tuple<absl::optional<std::int64_t>>;
        auto rows = client.ExecuteQuery(std::move(txn), std::move(sql));
        // Note: This mutator might be re-run, or its effects discarded, so
        // changing non-transactional state (e.g., by producing output) is,
        // in general, not something to be imitated.
        for (auto& row : google::cloud::spanner::StreamOf<RowType>(rows)) {
          if (!row) return std::move(row).status();
          std::cout << "MarketingBudget: ";
          if (std::get<0>(*row).has_value()) {
            std::cout << *std::get<0>(*row);
          } else {
            std::cout << "NULL";
          }
          std::cout << "\n";
        }
        std::cout << "Updated row(s) count: " << rows.RowsModified() << "\n";
        return google::cloud::spanner::Mutations{};
      });
  if (!commit) throw std::move(commit).status();
}

C#

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

public class UpdateUsingDmlReturningAsyncSample
{
    public async Task<List<long>> UpdateUsingDmlReturningAsync(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();

        // Update MarketingBudget column for records satisfying
        // a particular condition and return the modified
        // MarketingBudget column of the updated records using
        // 'THEN RETURN MarketingBudget'.
        // It is also possible to return all columns of all the
        // updated records by using 'THEN RETURN *'.
        using var cmd = connection.CreateDmlCommand("UPDATE Albums SET MarketingBudget = MarketingBudget * 2 WHERE SingerId = 1 and AlbumId = 1 THEN RETURN MarketingBudget");
        var reader = await cmd.ExecuteReaderAsync();
        var updatedMarketingBudgets = new List<long>();
        while (await reader.ReadAsync())
        {
            updatedMarketingBudgets.Add(reader.GetFieldValue<long>("MarketingBudget"));
        }

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

Vai

import (
	"context"
	"fmt"
	"io"

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

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

	// Update MarketingBudget column for records satisfying
	// a particular condition and returns the modified
	// MarketingBudget column of the updated records using
	// 'THEN RETURN MarketingBudget'.
	// It is also possible to return all columns of all the
	// updated records by using 'THEN RETURN *'.
	_, 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
				THEN RETURN MarketingBudget`,
		}
		iter := txn.Query(ctx, stmt)
		defer iter.Stop()
		for {
			row, err := iter.Next()
			if err == iterator.Done {
				break
			}
			if err != nil {
				return err
			}
			var marketingBudget int64
			if err := row.Columns(&marketingBudget); err != nil {
				return err
			}
			fmt.Fprintf(w, "%d\n", marketingBudget)
		}
		fmt.Fprintf(w, "%d record(s) updated.\n", iter.RowCount)
		return nil
	})
	return err
}

Java

import com.google.cloud.spanner.DatabaseClient;
import com.google.cloud.spanner.DatabaseId;
import com.google.cloud.spanner.ResultSet;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import com.google.cloud.spanner.Statement;

public class UpdateUsingDmlReturningSample {

  static void updateUsingDmlReturning() {
    // TODO(developer): Replace these variables before running the sample.
    final String projectId = "my-project";
    final String instanceId = "my-instance";
    final String databaseId = "my-database";
    updateUsingDmlReturning(projectId, instanceId, databaseId);
  }

  static void updateUsingDmlReturning(String projectId, String instanceId, String databaseId) {
    try (Spanner spanner =
        SpannerOptions.newBuilder()
            .setProjectId(projectId)
            .build()
            .getService()) {
      final DatabaseClient dbClient =
          spanner.getDatabaseClient(DatabaseId.of(projectId, instanceId, databaseId));
      // Update MarketingBudget column for records satisfying
      // a particular condition and returns the modified
      // MarketingBudget column of the updated records using
      // ‘THEN RETURN MarketingBudget’.
      // It is also possible to return all columns of all the
      // updated records by using ‘THEN RETURN *’.
      dbClient
          .readWriteTransaction()
          .run(
              transaction -> {
                String sql =
                    "UPDATE Albums "
                        + "SET MarketingBudget = MarketingBudget * 2 "
                        + "WHERE SingerId = 1 and AlbumId = 1 "
                        + "THEN RETURN MarketingBudget";

                // readWriteTransaction.executeQuery(..) API should be used for executing
                // DML statements with RETURNING clause.
                try (ResultSet resultSet = transaction.executeQuery(Statement.of(sql))) {
                  while (resultSet.next()) {
                    System.out.printf("%d\n", resultSet.getLong(0));
                  }
                  System.out.printf(
                      "Updated row(s) count: %d\n", resultSet.getStats().getRowCountExact());
                }
                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,
});

function updateUsingDmlReturning(instanceId, databaseId) {
  // 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 [rows, stats] = await transaction.run({
        sql: 'UPDATE Albums SET MarketingBudget = 2000000 WHERE SingerId = 1 and AlbumId = 1 THEN RETURN MarketingBudget',
      });

      const rowCount = Math.floor(stats[stats.rowCount]);
      console.log(
        `Successfully updated ${rowCount} record into the Albums table.`
      );
      rows.forEach(row => {
        console.log(row.toJSON().MarketingBudget);
      });

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

PHP

use Google\Cloud\Spanner\SpannerClient;

/**
 * Update the given database using DML returning.
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function update_dml_returning(string $instanceId, string $databaseId): void
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $transaction = $database->transaction();

    // Update MarketingBudget column for records satisfying a particular
    // condition and returns the modified MarketingBudget column of the updated
    // records using ‘THEN RETURN MarketingBudget’. It is also possible to return
    // all columns of all the updated records by using ‘THEN RETURN *’.

    $result = $transaction->execute(
        'UPDATE Albums '
        . 'SET MarketingBudget = MarketingBudget * 2 '
        . 'WHERE SingerId = 1 and AlbumId = 1 '
        . 'THEN RETURN MarketingBudget'
    );
    foreach ($result->rows() as $row) {
        printf('MarketingBudget: %s' . PHP_EOL, $row['MarketingBudget']);
    }
    printf(
        'Updated row(s) count: %d' . PHP_EOL,
        $result->stats()['rowCountExact']
    );
    $transaction->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)

# Update MarketingBudget column for records satisfying
# a particular condition and returns the modified
# MarketingBudget column of the updated records using
# 'THEN RETURN MarketingBudget'.
# It is also possible to return all columns of all the
# updated records by using 'THEN RETURN *'.
def update_albums(transaction):
    results = transaction.execute_sql(
        "UPDATE Albums "
        "SET MarketingBudget = MarketingBudget * 2 "
        "WHERE SingerId = 1 and AlbumId = 1 "
        "THEN RETURN MarketingBudget"
    )
    for result in results:
        print("MarketingBudget: {}".format(*result))
    print("{} record(s) updated.".format(results.stats.row_count_exact))

database.run_in_transaction(update_albums)

Ruby

require "google/cloud/spanner"

##
# This is a snippet for showcasing how to use DML return feature with update
# operation.
#
# @param project_id  [String] The ID of the Google Cloud project.
# @param instance_id [String] The ID of the spanner instance.
# @param database_id [String] The ID of the database.
#
def spanner_update_dml_returning project_id:, instance_id:, database_id:
  spanner = Google::Cloud::Spanner.new project: project_id
  client = spanner.client instance_id, database_id

  client.transaction do |transaction|
    # Update MarketingBudget column for records satisfying a particular
    # condition and returns the modified MarketingBudget column of the
    # updated records using ‘THEN RETURN MarketingBudget’.
    #
    # It is also possible to return all columns of all the updated records
    # by using ‘THEN RETURN *’.
    results = transaction.execute_query "UPDATE Albums SET MarketingBudget = MarketingBudget * 2
                                         WHERE SingerId = 1 and AlbumId = 1
                                         THEN RETURN MarketingBudget"
    results.rows.each do |row|
      puts "Updated Album with MarketingBudget: #{row[:MarketingBudget]}"
    end
    puts "Updated row(s) count: #{results.row_count}"
  end
end

PostgreSQL

C++

void UpdateUsingDmlReturning(google::cloud::spanner::Client client) {
  // Update MarketingBudget column for records satisfying a particular
  // condition and return the modified MarketingBudget column of the
  // updated records using `RETURNING MarketingBudget`.
  auto commit = client.Commit(
      [&client](google::cloud::spanner::Transaction txn)
          -> google::cloud::StatusOr<google::cloud::spanner::Mutations> {
        auto sql = google::cloud::spanner::SqlStatement(R"""(
            UPDATE Albums SET MarketingBudget = MarketingBudget * 2
              WHERE SingerId = 1 AND AlbumId = 1
              RETURNING MarketingBudget
        )""");
        using RowType = std::tuple<absl::optional<std::int64_t>>;
        auto rows = client.ExecuteQuery(std::move(txn), std::move(sql));
        for (auto& row : google::cloud::spanner::StreamOf<RowType>(rows)) {
          if (!row) return std::move(row).status();
          std::cout << "MarketingBudget: ";
          if (std::get<0>(*row).has_value()) {
            std::cout << *std::get<0>(*row);
          } else {
            std::cout << "NULL";
          }
          std::cout << "\n";
        }
        std::cout << "Updated row(s) count: " << rows.RowsModified() << "\n";
        return google::cloud::spanner::Mutations{};
      });
  if (!commit) throw std::move(commit).status();
}

C#

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

public class UpdateUsingDmlReturningAsyncPostgresSample
{
    public async Task<List<long>> UpdateUsingDmlReturningAsyncPostgres(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();

        // Update MarketingBudget column for records satisfying
        // a particular condition and return the modified
        // MarketingBudget column of the updated records using
        // 'RETURNING MarketingBudget'.
        // It is also possible to return all columns of all the
        // updated records by using 'RETURNING *'.
        using var cmd = connection.CreateDmlCommand("UPDATE Albums SET MarketingBudget = MarketingBudget * 2 WHERE SingerId = 14 and AlbumId = 20 RETURNING MarketingBudget");

        var reader = await cmd.ExecuteReaderAsync();
        var updatedMarketingBudgets = new List<long>();
        while (await reader.ReadAsync())
        {
            updatedMarketingBudgets.Add(reader.GetFieldValue<long>("marketingbudget"));
        }

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

Vai

import (
	"context"
	"fmt"
	"io"

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

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

	// Update MarketingBudget column for records satisfying
	// a particular condition and returns the modified
	// MarketingBudget column of the updated records using
	// 'RETURNING MarketingBudget'.
	// It is also possible to return all columns of all the
	// updated records by using 'RETURNING *'.
	_, 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
				RETURNING MarketingBudget`,
		}
		iter := txn.Query(ctx, stmt)
		defer iter.Stop()
		for {
			row, err := iter.Next()
			if err == iterator.Done {
				break
			}
			if err != nil {
				return err
			}
			var marketingBudget int64
			if err := row.Columns(&marketingBudget); err != nil {
				return err
			}
			fmt.Fprintf(w, "%d\n", marketingBudget)
		}
		fmt.Fprintf(w, "%d record(s) updated.\n", iter.RowCount)
		return nil
	})
	return err
}

Java

import com.google.cloud.spanner.DatabaseClient;
import com.google.cloud.spanner.DatabaseId;
import com.google.cloud.spanner.ResultSet;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import com.google.cloud.spanner.Statement;

public class PgUpdateUsingDmlReturningSample {

  static void updateUsingDmlReturning() {
    // TODO(developer): Replace these variables before running the sample.
    final String projectId = "my-project";
    final String instanceId = "my-instance";
    final String databaseId = "my-database";
    updateUsingDmlReturning(projectId, instanceId, databaseId);
  }

  static void updateUsingDmlReturning(String projectId, String instanceId, String databaseId) {
    try (Spanner spanner =
        SpannerOptions.newBuilder()
            .setProjectId(projectId)
            .build()
            .getService()) {
      final DatabaseClient dbClient =
          spanner.getDatabaseClient(DatabaseId.of(projectId, instanceId, databaseId));
      // Update MarketingBudget column for records satisfying
      // a particular condition and returns the modified
      // MarketingBudget column of the updated records using
      // ‘RETURNING MarketingBudget’.
      // It is also possible to return all columns of all the
      // updated records by using ‘RETURNING *’.
      dbClient
          .readWriteTransaction()
          .run(
              transaction -> {
                String sql =
                    "UPDATE Albums "
                        + "SET MarketingBudget = MarketingBudget * 2 "
                        + "WHERE SingerId = 1 and AlbumId = 1 "
                        + "RETURNING MarketingBudget";

                // readWriteTransaction.executeQuery(..) API should be used for executing
                // DML statements with RETURNING clause.
                try (ResultSet resultSet = transaction.executeQuery(Statement.of(sql))) {
                  while (resultSet.next()) {
                    System.out.printf("%d\n", resultSet.getLong(0));
                  }
                  System.out.printf(
                      "Updated row(s) count: %d\n", resultSet.getStats().getRowCountExact());
                }
                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,
});

function pgUpdateUsingDmlReturning(instanceId, databaseId) {
  // 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 [rows, stats] = await transaction.run({
        sql: 'UPDATE singers SET FirstName = $1, LastName = $2 WHERE singerid = $3 RETURNING FullName',
        params: {
          p1: 'Virginia1',
          p2: 'Watson1',
          p3: 18,
        },
      });

      const rowCount = Math.floor(stats[stats.rowCount]);
      console.log(
        `Successfully updated ${rowCount} record into the Singers table.`
      );
      rows.forEach(row => {
        console.log(row.toJSON().fullname);
      });

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

PHP

use Google\Cloud\Spanner\SpannerClient;

/**
 * Update the given postgresql database using DML returning.
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function pg_update_dml_returning(string $instanceId, string $databaseId): void
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $transaction = $database->transaction();

    // Update MarketingBudget column for records satisfying a particular
    // condition and returns the modified MarketingBudget column of the updated
    // records using ‘RETURNING MarketingBudget’. It is also possible to return
    // all columns of all the updated records by using ‘RETURNING *’.

    $result = $transaction->execute(
        'UPDATE Albums '
        . 'SET MarketingBudget = MarketingBudget * 2 '
        . 'WHERE SingerId = 1 and AlbumId = 1'
        . 'RETURNING MarketingBudget'
    );
    foreach ($result->rows() as $row) {
        printf('MarketingBudget: %s' . PHP_EOL, $row['marketingbudget']);
    }
    printf(
        'Updated row(s) count: %d' . PHP_EOL,
        $result->stats()['rowCountExact']
    );
    $transaction->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)

# Update MarketingBudget column for records satisfying
# a particular condition and returns the modified
# MarketingBudget column of the updated records using
# 'RETURNING MarketingBudget'.
# It is also possible to return all columns of all the
# updated records by using 'RETURNING *'.
def update_albums(transaction):
    results = transaction.execute_sql(
        "UPDATE Albums "
        "SET MarketingBudget = MarketingBudget * 2 "
        "WHERE SingerId = 1 and AlbumId = 1 "
        "RETURNING MarketingBudget"
    )
    for result in results:
        print("MarketingBudget: {}".format(*result))
    print("{} record(s) updated.".format(results.stats.row_count_exact))

database.run_in_transaction(update_albums)

Ruby

require "google/cloud/spanner"

##
# This is a snippet for showcasing how to use DML return feature with update
# operation in PostgreSql.
#
# @param project_id  [String] The ID of the Google Cloud project.
# @param instance_id [String] The ID of the spanner instance.
# @param database_id [String] The ID of the database.
#
def spanner_postgresql_update_dml_returning project_id:, instance_id:, database_id:
  spanner = Google::Cloud::Spanner.new project: project_id
  client = spanner.client instance_id, database_id

  client.transaction do |transaction|
    # Update MarketingBudget column for records satisfying a particular
    # condition and returns the modified MarketingBudget column of the
    # updated records using ‘RETURNING MarketingBudget’.
    # It is also possible to return all columns of all the updated records
    # by using ‘RETURNING *’.
    results = transaction.execute_query "UPDATE Albums SET MarketingBudget = MarketingBudget * 2
                                         WHERE SingerId = 1 and AlbumId = 1
                                         RETURNING MarketingBudget"
    results.rows.each do |row|
      puts "Updated Albums with MarketingBudget: #{row[:marketingbudget]}"
    end
    puts "Updated row(s) count: #{results.row_count}"
  end
end

Il seguente esempio di codice elimina tutte le righe della tabella Singers in cui la colonna FirstName è Alice e restituisce le colonne SingerId e FullName dei record eliminati.

GoogleSQL

C++

void DeleteUsingDmlReturning(google::cloud::spanner::Client client) {
  // Delete records from SINGERS table satisfying a particular condition
  // and return the SingerId and FullName column of the deleted records
  // using `THEN RETURN SingerId, FullName'.
  auto commit = client.Commit(
      [&client](google::cloud::spanner::Transaction txn)
          -> google::cloud::StatusOr<google::cloud::spanner::Mutations> {
        auto sql = google::cloud::spanner::SqlStatement(R"""(
            DELETE FROM Singers
              WHERE FirstName = 'Alice'
              THEN RETURN SingerId, FullName
        )""");
        using RowType = std::tuple<std::int64_t, std::string>;
        auto rows = client.ExecuteQuery(std::move(txn), std::move(sql));
        // Note: This mutator might be re-run, or its effects discarded, so
        // changing non-transactional state (e.g., by producing output) is,
        // in general, not something to be imitated.
        for (auto& row : google::cloud::spanner::StreamOf<RowType>(rows)) {
          if (!row) return std::move(row).status();
          std::cout << "SingerId: " << std::get<0>(*row) << " ";
          std::cout << "FullName: " << std::get<1>(*row) << "\n";
        }
        std::cout << "Deleted row(s) count: " << rows.RowsModified() << "\n";
        return google::cloud::spanner::Mutations{};
      });
  if (!commit) throw std::move(commit).status();
}

C#

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

public class DeleteUsingDmlReturningAsyncSample
{
    public async Task<List<string>> DeleteUsingDmlReturningAsync(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();

        // Delete records from SINGERS table satisfying a
        // particular condition and return the SingerId
        // and FullName column of the deleted records using
        // 'THEN RETURN SingerId, FullName'.
        // It is also possible to return all columns of all the
        // deleted records by using 'THEN RETURN *'.
        using var cmd = connection.CreateDmlCommand("DELETE FROM Singers WHERE FirstName = 'Alice' THEN RETURN SingerId, FullName");
        var reader = await cmd.ExecuteReaderAsync();
        var deletedSingerNames = new List<string>();
        while (await reader.ReadAsync())
        {
            deletedSingerNames.Add(reader.GetFieldValue<string>("FullName"));
        }

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

Vai

import (
	"context"
	"fmt"
	"io"

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

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

	// Delete records from SINGERS table satisfying a
	// particular condition and returns the SingerId
	// and FullName column of the deleted records using
	// 'THEN RETURN SingerId, FullName'.
	// It is also possible to return all columns of all the
	// deleted records by using 'THEN RETURN *'.
	_, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
		stmt := spanner.Statement{
			SQL: `DELETE FROM Singers WHERE FirstName = 'Alice'
			        THEN RETURN SingerId, FullName`,
		}
		iter := txn.Query(ctx, stmt)
		defer iter.Stop()
		for {
			row, err := iter.Next()
			if err == iterator.Done {
				break
			}
			if err != nil {
				return err
			}
			var (
				singerID int64
				fullName string
			)
			if err := row.Columns(&singerID, &fullName); err != nil {
				return err
			}
			fmt.Fprintf(w, "%d %s\n", singerID, fullName)
		}
		fmt.Fprintf(w, "%d record(s) deleted.\n", iter.RowCount)
		return nil
	})
	return err
}

Java

import com.google.cloud.spanner.DatabaseClient;
import com.google.cloud.spanner.DatabaseId;
import com.google.cloud.spanner.ResultSet;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import com.google.cloud.spanner.Statement;

public class DeleteUsingDmlReturningSample {

  static void deleteUsingDmlReturningSample() {
    // TODO(developer): Replace these variables before running the sample.
    final String projectId = "my-project";
    final String instanceId = "my-instance";
    final String databaseId = "my-database";
    deleteUsingDmlReturningSample(projectId, instanceId, databaseId);
  }

  static void deleteUsingDmlReturningSample(
      String projectId, String instanceId, String databaseId) {
    try (Spanner spanner =
        SpannerOptions.newBuilder()
            .setProjectId(projectId)
            .build()
            .getService()) {
      final DatabaseClient dbClient =
          spanner.getDatabaseClient(DatabaseId.of(projectId, instanceId, databaseId));
      // Delete records from SINGERS table satisfying a
      // particular condition and returns the SingerId
      // and FullName column of the deleted records using
      // ‘THEN RETURN SingerId, FullName’.
      // It is also possible to return all columns of all the
      // deleted records by using ‘THEN RETURN *’.
      dbClient
          .readWriteTransaction()
          .run(
              transaction -> {
                String sql =
                    "DELETE FROM Singers WHERE FirstName = 'Alice' THEN RETURN SingerId, FullName";

                // readWriteTransaction.executeQuery(..) API should be used for executing
                // DML statements with RETURNING clause.
                try (ResultSet resultSet = transaction.executeQuery(Statement.of(sql))) {
                  while (resultSet.next()) {
                    System.out.printf("%d %s\n", resultSet.getLong(0), resultSet.getString(1));
                  }
                  System.out.printf(
                      "Deleted row(s) count: %d\n", resultSet.getStats().getRowCountExact());
                }
                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,
});

function deleteUsingDmlReturning(instanceId, databaseId) {
  // 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 [rows, stats] = await transaction.run({
        sql: 'DELETE FROM Singers WHERE SingerId = 18 THEN RETURN FullName',
      });

      const rowCount = Math.floor(stats[stats.rowCount]);
      console.log(
        `Successfully deleted ${rowCount} record from the Singers table.`
      );
      rows.forEach(row => {
        console.log(row.toJSON().FullName);
      });

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

PHP

use Google\Cloud\Spanner\SpannerClient;

/**
 * Delete data from the given database using DML returning.
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function delete_dml_returning(string $instanceId, string $databaseId): void
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $transaction = $database->transaction();

    // Delete records from SINGERS table satisfying a particular condition and
    // returns the SingerId and FullName column of the deleted records using
    // 'THEN RETURN SingerId, FullName'. It is also possible to return all columns
    //  of all the deleted records by using 'THEN RETURN *'.

    $result = $transaction->execute(
        "DELETE FROM Singers WHERE FirstName = 'Alice' "
        . 'THEN RETURN SingerId, FullName',
    );
    foreach ($result->rows() as $row) {
        printf(
            '%d %s.' . PHP_EOL,
            $row['SingerId'],
            $row['FullName']
        );
    }
    printf(
        'Deleted row(s) count: %d' . PHP_EOL,
        $result->stats()['rowCountExact']
    );
    $transaction->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)

# Delete records from SINGERS table satisfying a
# particular condition and returns the SingerId
# and FullName column of the deleted records using
# 'THEN RETURN SingerId, FullName'.
# It is also possible to return all columns of all the
# deleted records by using 'THEN RETURN *'.
def delete_singers(transaction):
    results = transaction.execute_sql(
        "DELETE FROM Singers WHERE FirstName = 'David' "
        "THEN RETURN SingerId, FullName"
    )
    for result in results:
        print("SingerId: {}, FullName: {}".format(*result))
    print("{} record(s) deleted.".format(results.stats.row_count_exact))

database.run_in_transaction(delete_singers)

Ruby

require "google/cloud/spanner"

##
# This is a snippet for showcasing how to use DML return feature with delete
# operation.
#
# @param project_id  [String] The ID of the Google Cloud project.
# @param instance_id [String] The ID of the spanner instance.
# @param database_id [String] The ID of the database.
#
def spanner_delete_dml_returning project_id:, instance_id:, database_id:
  spanner = Google::Cloud::Spanner.new project: project_id
  client = spanner.client instance_id, database_id

  client.transaction do |transaction|
    # Delete records from SINGERS table satisfying a particular condition and
    # returns the SingerId and FullName column of the deleted records using
    # ‘THEN RETURN SingerId, FullName’.
    # It is also possible to return all columns of all the deleted records
    # by using ‘THEN RETURN *’.
    results = transaction.execute_query "DELETE FROM Singers WHERE FirstName = 'Alice' THEN RETURN SingerId, FullName"
    results.rows.each do |row|
      puts "Deleted singer with SingerId: #{row[:SingerId]}, FullName: #{row[:FullName]}"
    end
    puts "Deleted row(s) count: #{results.row_count}"
  end
end

PostgreSQL

C++

void DeleteUsingDmlReturning(google::cloud::spanner::Client client) {
  // Delete records from SINGERS table satisfying a particular condition
  // and return the SingerId and FullName column of the deleted records
  // using `RETURNING SingerId, FullName'.
  auto commit = client.Commit(
      [&client](google::cloud::spanner::Transaction txn)
          -> google::cloud::StatusOr<google::cloud::spanner::Mutations> {
        auto sql = google::cloud::spanner::SqlStatement(R"""(
            DELETE FROM Singers
              WHERE FirstName = 'Alice'
              RETURNING SingerId, FullName
        )""");
        using RowType = std::tuple<std::int64_t, std::string>;
        auto rows = client.ExecuteQuery(std::move(txn), std::move(sql));
        for (auto& row : google::cloud::spanner::StreamOf<RowType>(rows)) {
          if (!row) return std::move(row).status();
          std::cout << "SingerId: " << std::get<0>(*row) << " ";
          std::cout << "FullName: " << std::get<1>(*row) << "\n";
        }
        std::cout << "Deleted row(s) count: " << rows.RowsModified() << "\n";
        return google::cloud::spanner::Mutations{};
      });
  if (!commit) throw std::move(commit).status();
}

C#

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

public class DeleteUsingDmlReturningAsyncPostgresSample
{
    public async Task<List<string>> DeleteUsingDmlReturningAsyncPostgres(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();

        // Delete records from SINGERS table satisfying a
        // particular condition and return the SingerId
        // and FullName column of the deleted records using
        // 'RETURNING SingerId, FullName'.
        // It is also possible to return all columns of all the
        // deleted records by using 'RETURNING *'.
        using var cmd = connection.CreateDmlCommand("DELETE FROM Singers WHERE FirstName = 'Lata' RETURNING SingerId, FullName");
        var reader = await cmd.ExecuteReaderAsync();
        var deletedSingerNames = new List<string>();
        while (await reader.ReadAsync())
        {
            deletedSingerNames.Add(reader.GetFieldValue<string>("fullname"));
        }

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

Vai

import (
	"context"
	"fmt"
	"io"

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

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

	// Delete records from SINGERS table satisfying a
	// particular condition and returns the SingerId
	// and FullName column of the deleted records using
	// 'RETURNING SingerId, FullName'.
	// It is also possible to return all columns of all the
	// deleted records by using 'RETURNING *'.
	_, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
		stmt := spanner.Statement{
			SQL: `DELETE FROM Singers WHERE FirstName = 'Alice'
			        RETURNING SingerId, FullName`,
		}
		iter := txn.Query(ctx, stmt)
		defer iter.Stop()
		for {
			row, err := iter.Next()
			if err == iterator.Done {
				break
			}
			if err != nil {
				return err
			}
			var (
				singerID int64
				fullName string
			)
			if err := row.Columns(&singerID, &fullName); err != nil {
				return err
			}
			fmt.Fprintf(w, "%d %s\n", singerID, fullName)
		}
		fmt.Fprintf(w, "%d record(s) deleted.\n", iter.RowCount)
		return nil
	})
	return err
}

Java

import com.google.cloud.spanner.DatabaseClient;
import com.google.cloud.spanner.DatabaseId;
import com.google.cloud.spanner.ResultSet;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import com.google.cloud.spanner.Statement;

public class PgDeleteUsingDmlReturningSample {

  static void deleteUsingDmlReturningSample() {
    // TODO(developer): Replace these variables before running the sample.
    final String projectId = "my-project";
    final String instanceId = "my-instance";
    final String databaseId = "my-database";
    deleteUsingDmlReturningSample(projectId, instanceId, databaseId);
  }

  static void deleteUsingDmlReturningSample(
      String projectId, String instanceId, String databaseId) {
    try (Spanner spanner =
        SpannerOptions.newBuilder()
            .setProjectId(projectId)
            .build()
            .getService()) {
      final DatabaseClient dbClient =
          spanner.getDatabaseClient(DatabaseId.of(projectId, instanceId, databaseId));
      // Delete records from SINGERS table satisfying a
      // particular condition and returns the SingerId
      // and FullName column of the deleted records using
      // ‘RETURNING SingerId, FullName’.
      // It is also possible to return all columns of all the
      // deleted records by using ‘RETURNING *’.
      dbClient
          .readWriteTransaction()
          .run(
              transaction -> {
                String sql =
                    "DELETE FROM Singers WHERE FirstName = 'Alice' RETURNING SingerId, FullName";

                // readWriteTransaction.executeQuery(..) API should be used for executing
                // DML statements with RETURNING clause.
                try (ResultSet resultSet = transaction.executeQuery(Statement.of(sql))) {
                  while (resultSet.next()) {
                    System.out.printf("%d %s\n", resultSet.getLong(0), resultSet.getString(1));
                  }
                  System.out.printf(
                      "Deleted row(s) count: %d\n", resultSet.getStats().getRowCountExact());
                }
                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,
});

function pgDeleteUsingDmlReturning(instanceId, databaseId) {
  // 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 [rows, stats] = await transaction.run({
        sql: 'DELETE FROM Singers WHERE SingerId = 18 RETURNING FullName',
      });

      const rowCount = Math.floor(stats[stats.rowCount]);
      console.log(
        `Successfully deleted ${rowCount} record from the Singers table.`
      );
      rows.forEach(row => {
        console.log(row.toJSON().fullname);
      });

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

PHP

use Google\Cloud\Spanner\SpannerClient;

/**
 * Delete data from the given postgresql database using DML returning.
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function pg_delete_dml_returning(string $instanceId, string $databaseId): void
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $transaction = $database->transaction();

    // Delete records from SINGERS table satisfying a particular condition and
    // returns the SingerId and FullName column of the deleted records using
    // ‘RETURNING SingerId, FullName’. It is also possible to return all columns
    //  of all the deleted records by using ‘RETURNING *’.

    $result = $transaction->execute(
        "DELETE FROM Singers WHERE FirstName = 'Alice' "
        . 'RETURNING SingerId, FullName',
    );
    foreach ($result->rows() as $row) {
        printf(
            '%d %s.' . PHP_EOL,
            $row['singerid'],
            $row['fullname']
        );
    }
    printf(
        'Deleted row(s) count: %d' . PHP_EOL,
        $result->stats()['rowCountExact']
    );
    $transaction->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)

# Delete records from SINGERS table satisfying a
# particular condition and returns the SingerId
# and FullName column of the deleted records using
# 'RETURNING SingerId, FullName'.
# It is also possible to return all columns of all the
# deleted records by using 'RETURNING *'.
def delete_singers(transaction):
    results = transaction.execute_sql(
        "DELETE FROM Singers WHERE FirstName = 'David' "
        "RETURNING SingerId, FullName"
    )
    for result in results:
        print("SingerId: {}, FullName: {}".format(*result))
    print("{} record(s) deleted.".format(results.stats.row_count_exact))

database.run_in_transaction(delete_singers)

Ruby

require "google/cloud/spanner"

##
# This is a snippet for showcasing how to use DML return feature with delete
# operation in PostgreSql.
#
# @param project_id  [String] The ID of the Google Cloud project.
# @param instance_id [String] The ID of the spanner instance.
# @param database_id [String] The ID of the database.
#
def spanner_postgresql_delete_dml_returning project_id:, instance_id:, database_id:
  spanner = Google::Cloud::Spanner.new project: project_id
  client = spanner.client instance_id, database_id

  client.transaction do |transaction|
    # Delete records from SINGERS table satisfying a particular condition and
    # returns the SingerId and FullName column of the deleted records using
    # ‘RETURNING SingerId, FullName’.
    # It is also possible to return all columns of all the deleted records
    # by using ‘RETURNING *’.
    results = transaction.execute_query "DELETE FROM singers WHERE firstname = 'Alice' RETURNING SingerId, FullName"
    results.rows.each do |row|
      puts "Deleted singer with SingerId: #{row[:singerid]}, FullName: #{row[:fullname]}"
    end
    puts "Deleted row(s) count: #{results.row_count}"
  end
end

Leggere i dati scritti nella stessa transazione

Le modifiche apportate utilizzando le istruzioni DML sono visibili alle istruzioni successive nella stessa transazione. Questo è diverso dall'utilizzo delle mutazioni, in cui le modifiche non sono visibili fino all'commit della transazione.

Spanner controlla le limitazioni dopo ogni istruzione DML. Questo è diverso dall'utilizzo delle mutazioni, in cui Spanner mette in buffer le mutazioni nel client fino al commit e controlla i vincoli al momento del commit. La valutazione dei vincoli dopo ogni istruzione consente a Spanner di garantire che i dati restituiti da un'istruzione DML siano coerenti con lo schema.

L'esempio seguente aggiorna una riga nella tabella Singers, quindi esegue un'istruzione SELECT per stampare i nuovi valori.

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 std::move(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));
        // Note: This mutator might be re-run, or its effects discarded, so
        // changing non-transactional state (e.g., by producing output) is,
        // in general, not something to be imitated.
        for (auto const& row : spanner::StreamOf<RowType>(rows)) {
          if (!row) return std::move(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::move(commit_result).status();
  std::cout << "Write then read succeeded [spanner_dml_write_then_read]\n";
}

C#

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

public class WriteAndReadUsingDmlCoreAsyncSample
{
    public async Task<int> WriteAndReadUsingDmlCoreAsync(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 createDmlCmd = connection.CreateDmlCommand(@"INSERT Singers (SingerId, FirstName, LastName) VALUES (11, 'Timothy', 'Campbell')");
        int rowCount = await createDmlCmd.ExecuteNonQueryAsync();
        Console.WriteLine($"{rowCount} row(s) inserted...");

        // Read newly inserted record.
        using var createSelectCmd = connection.CreateSelectCommand(@"SELECT FirstName, LastName FROM Singers WHERE SingerId = 11");
        using var reader = await createSelectCmd.ExecuteReaderAsync();
        while (await reader.ReadAsync())
        {
            Console.WriteLine($"{reader.GetFieldValue<string>("FirstName")}  {reader.GetFieldValue<string>("LastName")}");
        }
        return rowCount;
    }
}

Vai

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(transaction -> {
        // 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(string $instanceId, string $databaseId): void
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $database->runTransaction(function (Transaction $t) {
        $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 INTO 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

Ottieni il piano di query

Puoi recuperare un piano di query utilizzando la console Google Cloud, le librerie client e lo strumento a riga di comando gcloud.

Utilizza DML partizionato

Il DML partizionato è progettato per aggiornamenti e eliminazioni collettivi, in particolare per la pulizia e il backfill periodici.

Esegui istruzioni con Google Cloud CLI

Per eseguire un'istruzione DML partizionata, utilizza il comando gcloud spanner databases execute-sql con l'opzione --enable-partitioned-dml. L'esempio seguente aggiorna le righe della tabellaAlbums.

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

Nota: Spanner non supporta --query-mode=PLAN e --query-mode=PROFILE per la DML partizionata.

Modificare i dati utilizzando la libreria client

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

L'esempio di codice seguente 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."

Utilizzare DML batch

Se devi evitare la latenza aggiuntiva dovuta a più richieste seriali, utilizza la DML batch per inviare più istruzioni INSERT, UPDATE o DELETE in una singola transazione:

C++

Utilizza la funzione ExecuteBatchDml() per eseguire un elenco di istruzioni DML.

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 std::move(result).status();
        // Note: This mutator might be re-run, or its effects discarded, so
        // changing non-transactional state (e.g., by producing output) is,
        // in general, not something to be imitated.
        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::move(commit_result).status();
  std::cout << "Update was successful [spanner_dml_batch_update]\n";
}

C#

Utilizza il metodo connection.CreateBatchDmlCommand() per creare il comando batch, il metodo Add per aggiungere istruzioni DML ed esegui le istruzioni con il metodo ExecuteNonQueryAsync().

using Google.Cloud.Spanner.Data;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

public class UpdateUsingBatchDmlCoreAsyncSample
{
    public async Task<int> UpdateUsingBatchDmlCoreAsync(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();

        SpannerBatchCommand cmd = connection.CreateBatchDmlCommand();

        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.");
        return affectedRows.Count();
    }
}

Vai

Utilizza il metodo BatchUpdate() per eseguire un array di oggetti Statement DML.

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

Utilizza il metodo transaction.batchUpdate() per eseguire un ArrayList di più oggetti Statement DML.

static void updateUsingBatchDml(DatabaseClient dbClient) {
  dbClient
      .readWriteTransaction()
      .run(transaction -> {
        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

Utilizza transaction.batchUpdate() per eseguire un elenco di istruzioni DML.

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

Utilizza executeUpdateBatch() per creare un elenco di istruzioni DML, quindi utilizza commit() per eseguirle.

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

Utilizza transaction.batch_update() per eseguire più stringhe di istruzioni DML.

from google.rpc.code_pb2 import OK

# 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):
    status, row_cts = transaction.batch_update([insert_statement, update_statement])

    if status.code != OK:
        # Do handling here.
        # Note: the exception will still be raised when
        # `commit` is called by `run_in_transaction`.
        return

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

database.run_in_transaction(update_albums)

Ruby

Utilizza transaction.batch_update per eseguire più stringhe di istruzioni DML.

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