Sintaxe da linguagem de manipulação de dados

Com a linguagem de manipulação de dados (DML, na sigla em inglês) do Cloud Spanner, é possível atualizar, inserir e excluir dados das tabelas do Cloud Spanner.

Para mais informações sobre como utilizar instruções DML, consulte Como inserir, atualizar e excluir dados usando a linguagem de manipulação de dados. Também é possível modificar dados usando mutações.

Tabelas usadas nos exemplos

CREATE TABLE Singers (
  SingerId   INT64 NOT NULL,
  FirstName  STRING(1024),
  LastName   STRING(1024),
  BirthDate  DATE,
  LastUpdated TIMESTAMP,
) PRIMARY KEY(SingerId);

CREATE TABLE Albums (
  SingerId        INT64 NOT NULL,
  AlbumId         INT64 NOT NULL,
  AlbumTitle      STRING(MAX),
  MarketingBudget INT64,
) PRIMARY KEY(SingerId, AlbumId),
  INTERLEAVE IN PARENT Singers ON DELETE CASCADE;

CREATE TABLE Songs (
  SingerId  INT64 NOT NULL,
  AlbumId   INT64 NOT NULL,
  TrackId   INT64 NOT NULL,
  SongName  STRING(MAX),
  Duration  INT64,
  SongGenre STRING(25),
) PRIMARY KEY(SingerId, AlbumId, TrackId),
  INTERLEAVE IN PARENT Albums ON DELETE CASCADE;

CREATE TABLE Concerts (
  VenueId      INT64 NOT NULL,
  SingerId     INT64 NOT NULL,
  ConcertDate  DATE NOT NULL,
  BeginTime    TIMESTAMP,
  EndTime      TIMESTAMP,
  TicketPrices ARRAY<INT64>,
) PRIMARY KEY(VenueId, SingerId, ConcertDate);

CREATE TABLE AckworthSingers (
  SingerId   INT64 NOT NULL,
  FirstName  STRING(1024),
  LastName   STRING(1024),
  BirthDate  DATE,
) PRIMARY KEY(SingerId);

Notação usada na sintaxe

  • Colchetes [ ] indicam cláusulas opcionais.
  • Parênteses ( ) indicam parênteses literais.
  • A barra vertical | indica um OR lógico.
  • Chaves { } delimitam um conjunto de opções.
  • Uma vírgula seguida por reticências indica que o item anterior pode se repetir em uma lista separada por vírgulas. item [, ...] indica um ou mais itens, e [item, ...] indica zero ou mais itens.
  • Uma vírgula , indica a vírgula literal.
  • Parênteses angulares <> indicam parênteses angulares literais.
  • Dois-pontos : indicam uma definição.
  • Palavras maiúsculas, como INSERT, são palavras-chave.

Instrução INSERT

Use a instrução INSERT para adicionar novas linhas a uma tabela. A instrução INSERT pode inserir uma ou mais linhas especificadas por expressões de valor ou zero ou mais linhas produzidas por uma consulta. A instrução retorna o número de linhas inseridas na tabela.

INSERT [INTO] target_name
 (column_name_1 [, ..., column_name_n] )
 input

input:
 VALUES (row_1_column_1_expr [, ..., row_1_column_n_expr ] )
        [, ..., (row_k_column_1_expr [, ..., row_k_column_n_expr ] ) ]
| select_query

expr: value_expression | DEFAULT

As instruções INSERT precisam obedecer a estas regras:

  • Os nomes das colunas podem estar em qualquer ordem.
  • Não são permitidos nomes duplicados na lista de colunas.
  • O número de colunas precisa corresponder ao número de valores.
  • O Cloud Spanner corresponde em termos de posição os valores na cláusula VALUES ou a consulta selecionada com a lista de colunas.
  • Cada valor precisa ser compatível com a coluna associada.
  • Os valores precisam obedecer às restrições no esquema, por exemplo, índices secundários exclusivos.
  • Todas as colunas não nulas precisam aparecer na lista de colunas e ter um valor não nulo especificado.

Se uma instrução não estiver em conformidade com as regras, o Cloud Spanner gerará um erro e a instrução inteira falhará.

Se a instrução tentar inserir uma linha duplicada, conforme determinado pela chave primária, a instrução inteira falhará.

Compatibilidade de tipo de valor

Os valores adicionados com uma instrução INSERT precisam ser compatíveis com o tipo da coluna de destino. O tipo de um valor será compatível com o tipo da coluna de destino se o valor atender a um dos seguintes critérios:

  • Esse tipo corresponde exatamente ao tipo da coluna. Por exemplo, quando um valor do tipo INT64 é inserido em uma coluna com o mesmo tipo INT64.
  • O Cloud Spanner pode forçar implicitamente o valor no tipo de destino.

Valores padrão

Use a palavra-chave DEFAULT para inserir o valor padrão de uma coluna. O Cloud Spanner atribui o valor padrão de NULL a colunas que não estão incluídas na lista de colunas.

Exemplos de INSERT

INSERT usando valores literais

O seguinte exemplo adiciona três linhas à tabela Singers.

INSERT INTO Singers (SingerId, FirstName, LastName)
VALUES(1, 'Marc', 'Richards'),
      (2, 'Catalina', 'Smith'),
      (3, 'Alice', 'Trentor')

Estas são as três novas linhas na tabela:

SingerId FirstName LastName BirthDate
1 Marc Richards NULL
2 Catalina Smith NULL
3 Alice Trentor NULL

INSERT usando uma instrução SELECT

O exemplo a seguir mostra como copiar os dados de uma tabela para outra usando uma instrução SELECT como entrada:

INSERT INTO Singers (SingerId, FirstName, LastName)
SELECT SingerId, FirstName, LastName
FROM AckworthSingers

Se a tabela Singers não tivesse linhas e a tabela AckworthSingers tivesse três linhas, haveria agora três linhas na tabela :

SingerId FirstName LastName BirthDate
1 Marc Richards NULL
2 Catalina Smith NULL
3 Alice Trentor NULL

O exemplo a seguir mostra como usar UNNEST para retornar uma tabela que é a entrada para o comando INSERT.

INSERT INTO Singers (SingerId, FirstName, LastName)
SELECT *
FROM UNNEST ([(4, 'Lea', 'Martin'),
      (5, 'David', 'Lomond'),
      (6, 'Elena', 'Campbell')])

Depois de adicionar essas três linhas à tabela do exemplo anterior, haverá seis linhas na tabela Singers:

SingerId FirstName LastName BirthDate
1 Marc Richards NULL
2 Catalina Smith NULL
3 Alice Trentor NULL
4 Lea Martin NULL
5 David Lomond NULL
6 Elena Campbell NULL

INSERT usando uma subconsulta

Veja no exemplo a seguir como inserir uma linha em uma tabela na qual um dos valores é computado usando uma subconsulta:

INSERT INTO Singers (SingerId, FirstName)
VALUES (
    4,
    (SELECT FirstName FROM AckworthSingers WHERE SingerId = 4))

As tabelas a seguir mostram os dados antes de a instrução ser executada.

Singers

SingerId FirstName LastName BirthDate
1 Marc Richards NULL
2 Catalina Smith NULL

AckworthSingers

SingerId FirstName LastName BirthDate
4 Lea Martin NULL
5 David Lomond NULL

A tabela a seguir mostra os dados após a execução da instrução.

Singers

SingerId FirstName LastName BirthDate
1 Marc Richards NULL
2 Catalina Smith NULL
4 Lea NULL NULL

Para incluir várias colunas, inclua várias subconsultas:

INSERT INTO Singers (SingerId, FirstName, LastName)
VALUES (
    4,
    (SELECT FirstName FROM AckworthSingers WHERE SingerId = 4),
    (SELECT LastName  FROM AckworthSingers WHERE SingerId = 4))

Instrução DELETE

Use a instrução DELETE para excluir linhas de uma tabela.

DELETE [FROM] target_name [[AS] alias]
WHERE condition

Cláusula WHERE

A cláusula WHERE é obrigatória. Esse requisito pode ajudar a evitar a exclusão acidental de todas as linhas em uma tabela. Para excluir todas as linhas de uma tabela, defina condition como true:

DELETE FROM target_name WHERE true

A cláusula WHERE pode conter qualquer instrução SQL válida, incluindo uma subconsulta que se refere a outras tabelas.

Aliases

A cláusula WHERE tem um alias implícito para target_name. Esse alias permite referenciar colunas em target_name sem qualificá-las com target_name. Por exemplo, se sua instrução começou com DELETE FROM Singers, será possível acessar qualquer coluna de Singers na cláusula WHERE. Neste exemplo, FirstName é uma coluna na tabela Singers:

DELETE FROM Singers
WHERE FirstName = 'Alice'

Também é possível criar um alias explícito usando a palavra-chave AS opcional. Para mais detalhes sobre aliases, consulte Sintaxe de consulta.

Exemplos de DELETE

DELETE com cláusula WHERE

A seguinte instrução DELETE exclui todos os cantores com o primeiro nome Alice.

DELETE FROM Singers
WHERE FirstName = 'Alice'

A tabela a seguir mostra os dados antes de a instrução ser executada.

SingerId FirstName LastName BirthDate
1 Marc Richards NULL
2 Catalina Smith NULL
3 Alice Trentor NULL

A tabela a seguir mostra os dados após a execução da instrução.

SingerId FirstName LastName BirthDate
1 Marc Richards NULL
2 Catalina Smith NULL

DELETE com subconsulta

A seguinte instrução exclui qualquer cantor em SINGERS que não tenha o primeiro nome em AckworthSingers.

DELETE FROM Singers
WHERE FirstName NOT IN (SELECT FirstName from AckworthSingers)

A tabela a seguir mostra os dados antes de a instrução ser executada.

Singers

SingerId FirstName LastName BirthDate
1 Marc Richards NULL
2 Catalina Smith NULL
3 Alice Trentor NULL
4 Lea Martin NULL
5 David Lomond NULL
6 Elena Campbell NULL

AckworthSingers

SingerId FirstName LastName BirthDate
4 Lea Martin NULL
5 David Lomond NULL
6 Elena Campbell NULL

A tabela a seguir mostra os dados após a execução da instrução.

Singers

SingerId FirstName LastName BirthDate
4 Lea Martin NULL
5 David Lomond NULL
6 Elena Campbell NULL

Instrução UPDATE

Use a instrução UPDATE para atualizar linhas existentes em uma tabela.

UPDATE target_name [[AS] alias]
SET update_item [, ...]
WHERE condition

update_item: path_expression = expression | path_expression = DEFAULT

Em que:

  • target_name é o nome de uma tabela para atualizar;
  • a cláusula SET é uma lista de update_items para executar em cada linha em que a condição WHERE é verdadeira;
  • path_expression é um nome de coluna;
  • expression é uma expressão de atualização. A expressão pode ser literal, uma expressão SQL ou uma subconsulta SQL.

Nas instruções UPDATE, siga estas regras:

  • Uma coluna pode aparecer apenas uma vez na cláusula SET.
  • As colunas na cláusula SET podem ser listadas em qualquer ordem.
  • Cada valor precisa ser compatível com a coluna associada.
  • Os valores precisam obedecer às restrições no esquema, como índices secundários exclusivos ou colunas não anuláveis.
  • Atualizações com mesclas não são aceitas.
  • Não é possível atualizar colunas de chave primária.

Se uma instrução não estiver em conformidade com as regras, o Cloud Spanner gerará um erro e a instrução inteira falhará.

Colunas não incluídas na cláusula SET não são modificadas.

Atualizações de coluna são executadas simultaneamente. Por exemplo, é possível trocar dois valores de coluna usando uma única cláusula SET:

SET x = y, y = x

Compatibilidade de tipo de valor

Valores atualizados com uma instrução UPDATE precisam ser compatíveis com o tipo da coluna de destino. O tipo de um valor será compatível com o tipo da coluna de destino se o valor atender a um dos seguintes critérios:

  • Esse tipo corresponde exatamente ao tipo da coluna. Por exemplo, o tipo de valor é INT64, e o tipo de coluna é INT64.
  • O Cloud Spanner pode forçar implicitamente o valor no tipo de destino.

Valores padrão

A palavra-chave DEFAULT define o valor de uma coluna como NULL.

Cláusula WHERE

A cláusula WHERE é obrigatória. Esse requisito pode ajudar a evitar a atualização acidental de todas as linhas em uma tabela. Para atualizar todas as linhas em uma tabela, defina condition como true.

A cláusula WHERE pode conter qualquer expressão SQL booleana válida, incluindo uma subconsulta que se refere a outras tabelas.

Aliases

A cláusula WHERE tem um alias implícito para target_name. Esse alias permite referenciar colunas em target_name sem qualificá-las com target_name. Por exemplo, se sua instrução começar com UPDATE Singers, será possível acessar qualquer coluna de Singers na cláusula WHERE. Neste exemplo, FirstName e LastName são colunas na tabela Singers:

UPDATE Singers
SET BirthDate = '1990-10-10'
WHERE FirstName = 'Marc' AND LastName = 'Richards'

Também é possível criar um alias explícito usando a palavra-chave AS opcional. Para mais detalhes sobre aliases, consulte Sintaxe de consulta.

Exemplos de UPDATE

UPDATE com valores literais

O exemplo a seguir atualiza a tabela Singers atualizando a coluna BirthDate em uma das linhas.

UPDATE Singers
SET BirthDate = '1990-10-10'
WHERE FirstName = 'Marc' AND LastName = 'Richards'

A tabela a seguir mostra os dados antes de a instrução ser executada.

SingerId FirstName LastName BirthDate
1 Marc Richards NULL
2 Catalina Smith NULL
3 Alice Trentor NULL

A tabela a seguir mostra os dados após a execução da instrução.

SingerId FirstName LastName BirthDate
1 Marc Richards 1990-10-10
2 Catalina Smith NULL
3 Alice Trentor NULL

Colunas UPDATE ARRAY

O exemplo a seguir atualiza uma coluna ARRAY.

UPDATE Concerts
SET TicketPrices = [25, 50, 100]
WHERE VenueId = 1

A tabela a seguir mostra os dados antes de a instrução ser executada.

VenueId SingerId ConcertDate BeginTime EndTime TicketPrices
1 1 NULL NULL NULL NULL
1 2 NULL NULL NULL NULL
2 3 NULL NULL NULL NULL

A tabela a seguir mostra os dados após a execução da instrução.

VenueId SingerId ConcertDate BeginTime EndTime TicketPrices
1 1 2018-01-01 NULL NULL [25, 50, 100]
1 2 2018-01-01 NULL NULL [25, 50, 100]
2 3 2018-01-01 NULL NULL NULL

Parâmetros STRUCT vinculados

É possível usar parâmetros STRUCT vinculados na cláusula WHERE de uma instrução DML. O exemplo de código a seguir atualiza o LastName nas linhas filtradas por FirstName e LastName.

C#

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

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

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

Go


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

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

Java

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

Node.js

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

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

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

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

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

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

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

PHP

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

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

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

        $rowCount = $t->executeUpdate(
            "UPDATE Singers SET LastName = 'Grant' "
             . "WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) "
             . "= @name",
            [
                'parameters' => [
                    'name' => $nameValue
                ],
                'types' => [
                    'name' => $nameType
                ]
            ]);
        $t->commit();
        printf('Updated %d row(s).' . PHP_EOL, $rowCount);
    });
}

Python

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

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

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

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

database.run_in_transaction(write_with_struct)

Ruby

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

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new project: project_id
client  = spanner.client instance_id, database_id
row_count = 0
name_struct = { FirstName: "Timothy", LastName: "Campbell" }

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

puts "#{row_count} record updated."

Carimbos de data/hora de confirmação

Use a função PENDING_COMMIT_TIMESTAMP para gravar carimbos de data/hora de confirmação em uma coluna TIMESTAMP. A coluna precisa ter a opção allow_commit_timestamp definida como true. A instrução DML a seguir atualiza a coluna LastUpdated na tabela Singers com o carimbo de data/hora de confirmação:

UPDATE Singers SET LastUpdated = PENDING_COMMIT_TIMESTAMP() WHERE SingerId = 1

Para mais informações sobre o uso de carimbos de data/hora de confirmação em DML, consulte Como gravar carimbos de data/hora de confirmação.