Sintaxis del lenguaje de manipulación de datos

El lenguaje de manipulación de datos (DML) de Cloud Spanner te permite actualizar, insertar y borrar datos en las tablas de Cloud Spanner.

Para obtener información acerca de cómo usar Declaraciones DML, consulta cómo insertar, actualizar y borrar datos mediante el lenguaje de manipulación de datos. También puedes modificar los datos mediante mutaciones.

Tablas usadas en ejemplos

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

Notación utilizada en la sintaxis

  • Los corchetes [ ] indican cláusulas opcionales.
  • Los paréntesis ( ) indican paréntesis literales.
  • La barra vertical | indica un OR lógico.
  • Las llaves { } encierran un conjunto de opciones.
  • Una coma seguida de una elipsis indica que el elemento anterior puede repetirse en una lista separada por comas. item [, ...] indica uno o más elementos, y [item, ...] indica cero o más elementos.
  • Una coma , indica la coma literal.
  • Los corchetes angulares <> indican corchetes angulares literales.
  • Los dos puntos : indican una definición.
  • Las palabras mayúsculas, como INSERT, son palabras clave.

Instrucción INSERT

Usa la instrucción INSERT para agregar filas nuevas a una tabla. La instrucción INSERT puede insertar una o más filas especificadas por expresiones de valor, o cero o más filas producidas por una consulta. La instrucción muestra la cantidad de filas insertadas en la tabla.

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

Las instrucciones INSERT deben cumplir con estas reglas:

  • Los nombres de las columnas pueden estar en cualquier orden.
  • No se permiten nombres duplicados en la lista de columnas.
  • La cantidad de columnas debe coincidir con la cantidad de valores.
  • Cloud Spanner hace coincidir los valores de la cláusula VALUES o la consulta de selección posicionalmente con la lista de columnas.
  • Cada valor debe ser compatible con su columna asociada.
  • Los valores deben cumplir con cualquier restricción en el esquema, por ejemplo, índices secundarios únicos.
  • Todas las columnas que no sean nulas deben aparecer en la lista de columnas y tener un valor no nulo especificado.

Si una instrucción no cumple con las reglas, Cloud Spanner genera un error y falla toda la instrucción.

Si la instrucción intenta insertar una fila duplicada, según lo determinado por la clave primaria, falla toda la instrucción.

Compatibilidad de tipo de valor

Los valores que agregas en una instrucción INSERT deben ser compatibles con el tipo de columna de destino. El tipo de valor es compatible con el tipo de columna de destino si el valor cumple con uno de los siguientes criterios:

  • El tipo de valor coincide exactamente con el tipo de columna. Por ejemplo, insertar un valor de tipo INT64 en una columna que tenga el tipo INT64 es compatible.
  • Cloud Spanner puede forzar implícitamente el valor en el tipo de destino.

Valores predeterminados

Usa la palabra clave DEFAULT para insertar el valor predeterminado de una columna. Cloud Spanner asigna el valor predeterminado de NULL a las columnas que no se incluyen en la lista de columnas.

Ejemplos de INSERT

INSERT con valores literales

En el siguiente ejemplo, se agregan tres filas a la tabla Singers.

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

Estas son las tres filas nuevas de la tabla:

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

INSERT con una instrucción SELECT

En el siguiente ejemplo, se muestra cómo copiar los datos de una tabla a otra con una instrucción SELECT como entrada:

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

Si la tabla Singers no tenía filas y la tabla AckworthSingers tenía tres filas, ahora hay tres filas en la tabla Singers:

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

En el siguiente ejemplo, se muestra cómo usar UNNEST para mostrar una tabla que sea la entrada del comando INSERT.

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

Después de agregar estas tres filas adicionales a la tabla del ejemplo anterior, hay seis filas en la tabla 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 con una subconsulta

En el ejemplo siguiente, se muestra cómo insertar una fila en una tabla, en la que uno de los valores se calcula mediante una subconsulta:

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

En las siguientes tablas, se muestran los datos antes de que se ejecute la instrucción.

Cantantes

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

En la siguiente tabla, se muestran los datos después de que se ejecuta la instrucción.

Cantantes

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

Para incluir varias columnas, debes incluir varias subconsultas:

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

Instrucción DELETE

Usa la instrucción DELETE para borrar filas de una tabla.

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

Cláusula WHERE

La cláusula WHERE es obligatoria. Este requisito puede ayudar a evitar la eliminación accidental de todas las filas de una tabla. Para borrar todas las filas de una tabla, establece condition en true:

DELETE FROM target_name WHERE true;

La cláusula WHERE puede contener cualquier instrucción DE SQL válida, incluida una subconsulta que haga referencia a otras tablas.

Alias

La cláusula WHERE tiene un alias implícito para target_name. Este alias te permite hacer referencia a columnas en target_name sin calificarlas con target_name. Por ejemplo, si tu instrucción comienza con DELETE FROM Singers, entonces puedes acceder a cualquier columna de Singers en la cláusula WHERE. En este ejemplo, FirstName es una columna en la tabla Singers:

DELETE FROM Singers WHERE FirstName = 'Alice';

También puedes crear un alias explícito con la palabra clave AS opcional. Para obtener más detalles sobre los alias, consulta Sintaxis de consulta.

Ejemplos de DELETE

DELETE con la cláusula WHERE

La siguiente instrucción DELETE borra a todos los cantantes cuyo nombre sea Alice.

DELETE FROM Singers WHERE FirstName = 'Alice';

En la siguiente tabla, se muestran los datos antes de que se ejecute la instrucción.

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

En la siguiente tabla, se muestran los datos después de que se ejecuta la instrucción.

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

DELETE con subconsulta

La siguiente instrucción borra a cualquier cantante de SINGERS cuyo nombre de pila no esté en AckworthSingers.

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

En la siguiente tabla, se muestran los datos antes de que se ejecute la instrucción.

Cantantes

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

En la siguiente tabla, se muestran los datos después de que se ejecuta la instrucción.

Cantantes

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

Instrucción UPDATE

Usa la instrucción UPDATE para actualizar las filas existentes en una tabla.

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

update_item: path_expression = expression | path_expression = DEFAULT

Donde:

  • target_name es el nombre de una tabla para la actualización.
  • La cláusula SET es una lista de elementos update_items para realizar en cada fila donde la condición WHERE es verdadera.
  • path_expression es un nombre de columna.
  • expression es una expresión de actualización. La expresión puede ser literal, una expresión de SQL o una subconsulta de SQL.

Las instrucciones UPDATE deben cumplir con las siguientes reglas:

  • Una columna puede aparecer solo una vez en la cláusula SET.
  • Las columnas de la cláusula SET se pueden enumerar en cualquier orden.
  • Cada valor debe ser compatible con su columna asociada.
  • Los valores deben cumplir con cualquier restricción del esquema, como los índices secundarios únicos o las columnas que no admiten nulos.
  • Las actualizaciones con combinaciones no son compatibles.
  • No puedes actualizar las columnas de clave primaria.

Si una instrucción no cumple con las reglas, Cloud Spanner genera un error y falla toda la instrucción.

Las columnas que no se incluyen en la cláusula SET no se modifican.

Las actualizaciones de las columnas se realizan de forma simultánea. Por ejemplo, puedes cambiar dos valores de columna con una sola cláusula SET:

SET x = y, y = x

Compatibilidad de tipo de valor

Los valores actualizados con una instrucción UPDATE deben ser compatibles con el tipo de columna de destino. El tipo de valor es compatible con el tipo de columna de destino si el valor cumple con uno de los siguientes criterios:

  • El tipo de valor coincide exactamente con el tipo de columna. Por ejemplo, el tipo de valor es INT64 y el tipo de columna es INT64.
  • Cloud Spanner puede forzar implícitamente el valor en el tipo de destino.

Valores predeterminados

La palabra clave DEFAULT establece el valor de una columna en NULL.

Cláusula WHERE

La cláusula WHERE es obligatoria. Este requisito puede ayudar a evitar la actualización accidental de todas las filas de una tabla. Para actualizar todas las filas de una tabla, establece condition en true:

La cláusula WHERE puede contener cualquier expresión booleana de SQL válida, incluida una subconsulta que haga referencia a otras tablas.

Alias

La cláusula WHERE tiene un alias implícito para target_name. Este alias te permite hacer referencia a columnas en target_name sin calificarlas con target_name. Por ejemplo, si tu instrucción comienza con UPDATE Singers, entonces puedes acceder a cualquier columna de Singers en la cláusula WHERE. En este ejemplo, FirstName y LastName son columnas en la tabla Singers:

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

También puedes crear un alias explícito con la palabra clave AS opcional. Para obtener más detalles sobre los alias, consulta Sintaxis de consulta.

Ejemplos de UPDATE

UPDATE con valores literales

En el siguiente ejemplo, se actualiza la tabla Singers cuando se actualiza la columna BirthDate en una de las filas.

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

En la siguiente tabla, se muestran los datos antes de que se ejecute la instrucción.

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

En la siguiente tabla, se muestran los datos después de que se ejecuta la instrucción.

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

Columnas UPDATE ARRAY

En el siguiente ejemplo, se actualiza una columna ARRAY.

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

En la siguiente tabla, se muestran los datos antes de que se ejecute la instrucción.

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

En la siguiente tabla, se muestran los datos después de que se ejecuta la instrucción.

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

Puedes usar los parámetros STRUCT vinculados en la cláusula WHERE de una Declaración de DML. En el siguiente ejemplo de código, se actualizan los valores LastName en las filas filtradas por FirstName y 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


import (
	"context"
	"fmt"
	"io"

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

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

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

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

Java

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

Node.js

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

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

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

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

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

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

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

PHP

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

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

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

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

Python

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

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

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

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

database.run_in_transaction(write_with_struct)

Ruby

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

require "google/cloud/spanner"

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

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

puts "#{row_count} record updated."

Marcas de tiempo de confirmación

Usa la función PENDING_COMMIT_TIMESTAMP para escribir las marcas de tiempo de confirmación en una columna TIMESTAMP. La columna debe tener la opción allow_commit_timestamp establecida en true. La siguiente Declaración DML actualiza la columna LastUpdated en la tabla Singers con la marca de tiempo de confirmación:

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

Para obtener más información sobre cómo usar las marcas de tiempo de confirmación en DML, consulta cómo escribir marcas de tiempo de confirmación.