Inserir, atualizar e excluir dados usando a linguagem de manipulação de dados (DML, na sigla em inglês)

Nesta página, descrevemos como inserir, atualizar e excluir dados do Spanner usando o instruções de linguagem de manipulação de dados (DML, na sigla em inglês). É possível executar instruções DML usando as bibliotecas de cliente, o Console do Google Cloud e a ferramenta de linha de comando gcloud. É possível executar as instruções DML particionadas usando as bibliotecas de cliente e a ferramenta de linha de comando gcloud.

Para conferir a referência completa da sintaxe de DML, consulte Sintaxe da linguagem de manipulação de dados para bancos de dados com dialeto do GoogleSQL ou Linguagem de manipulação de dados do PostgreSQL para bancos de dados com dialeto do PostgreSQL.

Observação :para conhecer o Spanner usando uma instância de teste gratuito de 90 dias, consulte Criar uma instância de teste gratuito do Spanner.

Usar a DML

A DML é compatível com instruções INSERT, UPDATE e DELETE na Console do Google Cloud, Google Cloud CLI e bibliotecas de cliente.

Bloqueio

As instruções DML são executadas dentro de transações de leitura e gravação. Quando o Spanner lê dados, adquire bloqueios de leitura compartilhados em partes limitadas dos intervalos de linhas que você lê. Especificamente, ele adquire esses bloqueios apenas nas colunas acessadas. Os bloqueios podem incluir dados que não atendam à condição de filtro da cláusula WHERE.

Quando o Spanner modifica dados usando instruções DML, ele adquire bloqueios exclusivos no dados específicos que você está modificando. Além disso, ele adquire bloqueios compartilhados da mesma maneira que quando você lê dados. Se a solicitação incluir intervalos de linhas grandes ou uma tabela inteira, os bloqueios compartilhados poderão impedir que outras transações sejam concluídas em paralelo.

Para modificar os dados da forma mais eficiente possível, use uma cláusula WHERE que permita Spanner para ler apenas as linhas necessárias. É possível atingir essa meta com um filtro na chave principal ou na chave de um índice secundário. A cláusula WHERE limita o escopo dos bloqueios compartilhados e permite que o Spanner processe a atualização de maneira mais eficiente.

Por exemplo, suponha que um dos músicos da tabela Singers altere seu nome e você precise atualizar essa informação no banco de dados. É possível executar a seguinte instrução DML mas força o Spanner a verificar a tabela inteira e adquire os bloqueios compartilhados que que cobrem toda a tabela. Como resultado, o Spanner precisa ler mais dados do que o necessário e as transações simultâneas não conseguem modificar os dados em paralelo:

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

Para tornar a atualização mais eficiente, inclua a coluna SingerId na cláusula WHERE. A coluna SingerId é a única coluna de chave primária da tabela Singers:

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

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

Se não houver um índice em FirstName ou LastName, será necessário verificar toda a tabela para encontrar os cantores de destino. Se você não quiser adicionar para tornar a atualização mais eficiente, inclua a coluna SingerId na cláusula WHERE.

A coluna SingerId é a única coluna de chave primária para Singers. Para encontrá-lo, execute SELECT em uma transação separada de somente leitura antes da transação de atualização:

  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;

Simultaneidade

O Spanner executa sequencialmente todas as instruções SQL (SELECT, INSERT, UPDATE e DELETE) em uma transação. Elas não são executadas simultaneamente. A única exceção é que o Spanner pode executar várias instruções SELECT simultaneamente, porque são operações somente leitura.

Limites de transação

Uma transação que inclui declarações DML tem os mesmos limites que qualquer outra transação. Se você tiver alterações em grande escala, considere o uso de DML particionada.

• Se as instruções DML em uma transação resultarem em mais de 80.000 mutações, a instrução DML que envia o transação acima do limite retorna um erro BadUsage com uma mensagem "sobre" muitas mutações.

• Se as instruções DML de uma transação resultarem em uma transação maior que 100 MB, a instrução DML que faz a transação superar o limite retorna um erro BadUsage com uma mensagem sobre a transação exceder o limite de tamanho.

Mutações realizadas usando DML não são retornadas ao cliente. Elas são mescladas na solicitação de confirmação quando ela é confirmada e contam para os limites de tamanho máximo. Mesmo que o tamanho da solicitação de confirmação enviada seja pequeno, a transação ainda poderá exceder o limite de tamanho permitido.

Executar instruções no console do Google Cloud

Observação: o Spanner Studio (anteriormente chamado de Query no console do Google Cloud) é compatível com operações SQL, DML e DDL em um único editor. Para mais informações, consulte Gerenciar dados usando o console do Google Cloud.

Use as etapas a seguir para executar uma instrução DML na console do Google Cloud.

1. Acesse a página Instâncias do Spanner.

   Acesse a página "Instâncias".

2. Selecione o projeto na lista suspensa na barra de ferramentas.

3. Clique no nome da instância que contém seu banco de dados para ir para a página Detalhes da instância.

4. Na guia Visão geral, clique no nome do seu banco de dados. A página Detalhes do banco de dados é exibida.

5. Clique em Spanner Studio.

6. Digite uma instrução DML. Por exemplo, a instrução a seguir adiciona uma nova linha à tabela Singers.

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

7. Clique em Executar consulta. O console do Google Cloud exibe o resultado.

Observação: quando você clica em Executar consulta, a transação é confirmada automaticamente.

Executar instruções com a Google Cloud CLI

Para executar instruções DML, use o comando gcloud spanner databases execute-sql. O exemplo a seguir adiciona uma nova linha à tabela Singers.

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

Modificar dados usando a biblioteca de cliente

Para executar instruções DML com a biblioteca de cliente:

• Crie uma transação de leitura/gravação.
• Chame o método da biblioteca de cliente para a execução da DML e transmita a declaração DML.
• Use o valor de retorno do método de execução da DML para receber o número de linhas inseridas, atualizadas ou excluídas.

O exemplo de código a seguir insere uma nova linha na tabela Singers.

C++

Use a função ExecuteDml() para executar uma instrução 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#

Use o método ExecuteNonQueryAsync() para executar uma instrução 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;
    }
}

Go

Use o método Update() para executar uma instrução 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

Use o método executeUpdate() para executar uma instrução 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

Use o método runUpdate() para executar uma instrução 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

Use o método executeUpdate() para executar uma instrução 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

Use o método execute_update() para executar uma instrução 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

Use o método execute_update() para executar uma instrução 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."

O exemplo de código a seguir atualiza a coluna MarketingBudget da tabela Albums com base em uma cláusula 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;
    }
}

Go

import (
	"context"
	"fmt"
	"io"

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

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

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

Java

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

O exemplo de código a seguir exclui todas as linhas da tabela Singers em que a coluna 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;
    }
}

Go

import (
	"context"
	"fmt"
	"io"

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

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

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

Java

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

O exemplo a seguir, apenas para bancos de dados do dialeto do GoogleSQL, usa um STRUCT com parâmetros vinculados para atualizar o LastName em linhas filtradas por 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;
    }
}

Go

import (
	"context"
	"fmt"
	"io"

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

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

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

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

Java

static void updateUsingDmlWithStruct(DatabaseClient dbClient) {
  Struct name =
      Struct.newBuilder().set("FirstName").to("Timothy").set("LastName").to("Campbell").build();
  Statement s =
      Statement.newBuilder(
              "UPDATE Singers SET LastName = 'Grant' "
                  + "WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) "
                  + "= @name")
          .bind("name")
          .to(name)
          .build();
  dbClient
      .readWriteTransaction()
      .run(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."

Modificar dados com as instruções DML retornadas

A cláusula THEN RETURN (bancos de dados do dialeto GoogleSQL) ou cláusula RETURNING (bancos de dados do dialeto PostgreSQL) é destinada a cenários em que você quer buscar dados de linhas modificadas. Isso é muito útil quando você quer visualizar valores não especificados em um arquivo DML valores padrão ou colunas geradas.

Para executar instruções DML de retorno usando a biblioteca de cliente:

• Crie uma transação de leitura/gravação.
• Chame o método da biblioteca de cliente para a execução da consulta e transmita a instrução DML retornada para receber os resultados.

O exemplo de código a seguir insere uma nova linha na tabela Singers, e ela retorna a coluna gerada FullName dos registros inseridos.

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

Go

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

Go

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

O exemplo de código a seguir atualiza a coluna MarketingBudget da tabela Albums com base em uma cláusula WHERE e retorna a coluna MarketingBudget modificada dos registros atualizados.

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

Go

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

Go

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

O exemplo de código a seguir exclui todas as linhas da tabela Singers em que a coluna FirstName é Alice e retorna as colunas SingerId e FullName dos registros excluídos.

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

Go

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

Go

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

Ler dados gravados na mesma transação

As alterações que você faz usando instruções DML são visíveis para declarações subsequentes na mesma transação. Isso é diferente de usar mutações, em que as alterações não são visíveis até que a transação seja confirmada.

O Spanner verifica as restrições após cada instrução DML. Isso é diferente de usar mutações, em que o Spanner armazena mutações na cliente até o commit e verifica as restrições no momento do commit. Avaliando restrições após cada instrução permite que o Spanner garanta que os dados que uma instrução DML retorna são consistentes com o esquema.

O exemplo a seguir atualiza uma linha na tabela Singers e depois executa uma instrução SELECT para imprimir os novos valores.

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

Go

import (
	"context"
	"fmt"
	"io"

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

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

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

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

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

Java

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

Conferir o plano de consulta

É possível extrair um plano de consulta usando o console do Google Cloud, as bibliotecas de cliente e a ferramenta de linha de comando gcloud.

Usar DML particionada

A DML particionada foi projetada para atualizações e exclusões em massa, particularmente limpeza periódica e preenchimento.

Executar instruções com a Google Cloud CLI

Para executar uma instrução DML particionada, use o comando gcloud spanner databases execute-sql com a opção --enable-partitioned-dml. O exemplo a seguir atualiza linhas na tabela Albums.

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

Observação: o Spanner não é compatível com --query-mode=PLAN e --query-mode=PROFILE para DML particionada.

Modificar dados usando a biblioteca de cliente

O exemplo de código a seguir atualiza a coluna MarketingBudget da tabela Albums.

C++

Use a função ExecutePartitionedDml() para executar uma instrução DML particionada.

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#

Use o método ExecutePartitionedUpdateAsync() para executar uma instrução DML particionada.

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

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

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

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

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

Go

Use o método PartitionedUpdate() para executar uma instrução DML particionada.

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

Use o método executePartitionedUpdate() para executar uma instrução DML particionada.

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

Use o método runPartitionedUpdate() para executar uma instrução DML particionada.

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

Use o método executePartitionedUpdate() para executar uma instrução DML particionada.

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

Use o método execute_partitioned_dml() para executar uma instrução DML particionada.

# 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

Use o método execute_partitioned_update() para executar uma instrução DML particionada.

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

O exemplo de código a seguir exclui linhas da tabela Singers com base na coluna 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;
    }
}

Go

import (
	"context"
	"fmt"
	"io"

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

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

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

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

Java

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

Node.js

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

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

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

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

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

PHP

use Google\Cloud\Spanner\SpannerClient;

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

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

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

Python

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

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

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

Ruby

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

require "google/cloud/spanner"

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

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

puts "#{row_count} records deleted."

Usar DML em lote

Caso precise evitar a latência extra incorrida de várias solicitações em série, use a DML em lote para enviar diversas instruções INSERT, UPDATE ou DELETE em uma única transação:

C++

Use a função ExecuteBatchDml() para executar uma lista de instruções 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#

Use o método connection.CreateBatchDmlCommand() para criar seu comando em lote. Use o método Add para adicionar instruções DML. Execute as instruções com o método 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();
    }
}

Go

Use o método BatchUpdate() para executar uma matriz de objetos 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

Use o método transaction.batchUpdate() para executar um ArrayList de vários objetos 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

Use transaction.batchUpdate() para executar uma lista de instruções 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

Use executeUpdateBatch() para criar uma lista de instruções DML. Depois, use commit() para executar as instruções.

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

Use transaction.batch_update() para executar várias strings de instrução 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

Use transaction.batch_update para executar várias strings de instrução 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."