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 declarações DML, consulte Como inserir, atualizar e excluir dados usando a linguagem de manipulação de dados. Você também pode 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 { } envolvem 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.
  • Colchetes angulares <> indicam colchetes 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 o valor do tipo INT64 é inserido em uma coluna com o mesmo tipo INT64.
  • O Cloud Spanner pode implicitamente forçar 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 exemplo a seguir 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 Singers:

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.

Cantores

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.

Cantores

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 a 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çar com DELETE FROM Singers, você poderá acessar quaisquer colunas de Singers na cláusula WHERE. Neste exemplo, FirstName é uma coluna na tabela Singers:

DELETE FROM Singers
WHERE FirstName = 'Alice'

Você também pode 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 SINGERS exclui todos os cantores com o primeiro nome 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.

Cantores

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.

Cantores

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 da tabela a ser atualizada;
  • 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, você pode 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 implicitamente forçar 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 a 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, você poderá 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'

Você também pode 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 alterando 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 seguinte declaração DML 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.

Esta página foi útil? Conte sua opinião sobre:

Enviar comentários sobre…

Documentação do Cloud Spanner