Syntaxe du langage de manipulation de données

Le langage de manipulation de données (LMD) de Cloud Spanner vous permet de mettre à jour, de supprimer et d'insérer des données dans des tables Cloud Spanner.

Pour en savoir plus sur l'utilisation des instructions LMD, consultez la page Insérer, mettre à jour et supprimer des données à l'aide du langage de manipulation de données. Vous pouvez également modifier des données à l'aide de mutations.

Tables utilisées dans les exemples

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

Notation utilisée dans la syntaxe

  • Les crochets [ ] indiquent les clauses facultatives.
  • Les parenthèses ( ) correspondent à des parenthèses littérales.
  • La barre verticale | correspond à un opérateur OR logique.
  • Les accolades { } renferment un ensemble d'options.
  • Une virgule suivie de points de suspension indique que l'élément précédent peut être répété dans une liste séparée par des virgules. item [, ...] correspond à un ou plusieurs éléments, tandis que [item, ...] représente zéro ou plusieurs éléments.
  • Une virgule , correspond à la virgule littérale.
  • Les chevrons <> correspondent à des chevrons littéraux.
  • Le signe deux-points : indique une définition.
  • Les mots en majuscules (INSERT, par exemple) correspondent à des mots clés.

Instruction INSERT

Utilisez l'instruction INSERT pour ajouter des lignes à une table. L'instruction INSERT permet d'insérer une ou plusieurs lignes spécifiées par des expressions de valeurs, ou bien zéro, une ou plusieurs lignes générées par une requête. Elle renvoie le nombre de lignes insérées dans la table.

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

Les instructions INSERT doivent être conformes aux règles suivantes :

  • Les noms de colonne peuvent être répertoriés dans n'importe quel ordre.
  • Les noms en double ne sont pas autorisés dans la liste des colonnes.
  • Le nombre de colonnes doit correspondre au nombre de valeurs.
  • Cloud Spanner met en correspondance en fonction de la position les valeurs de la clause VALUES ou de la requête SELECT avec la liste de colonnes.
  • Chaque valeur doit avoir un type compatible avec la colonne associée.
  • Les valeurs doivent respecter toutes les contraintes du schéma (telles que les index secondaires uniques).
  • Toutes les colonnes non nulles doivent apparaître dans la liste des colonnes et avoir une valeur non nulle définie.

Si une instruction n'est pas conforme aux règles, Cloud Spanner génère une erreur, et toute l'instruction échoue.

Si l'instruction tente d'insérer une ligne en double, comme défini par la clé primaire, l'instruction entière échoue.

Compatibilité des types de valeurs

Les valeurs que vous ajoutez dans une instruction INSERT doivent être compatibles avec le type de la colonne cible. Le type d'une valeur est compatible avec le type de la colonne cible si la valeur est conforme à l'un des critères suivants :

  • Le type de valeur correspond exactement au type de colonne. Par exemple, l'insertion d'une valeur de type INT64 dans une colonne de type INT64 est compatible.
  • Cloud Spanner peut convertir implicitement le type de la valeur pour qu'il corresponde à celui de la cible.

Valeurs par défaut

Utilisez le mot clé DEFAULT pour insérer la valeur par défaut d'une colonne. Cloud Spanner attribue la valeur par défaut NULL aux colonnes qui ne figurent pas dans la liste des colonnes.

Exemples d'instructions INSERT

Instruction INSERT utilisant des valeurs littérales

L'exemple ci-dessous permet d'ajouter trois lignes à la table Singers.

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

Voici les trois nouvelles lignes de la table :

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

Instruction INSERT utilisant une instruction SELECT

L'exemple ci-dessous montre comment copier les données d'une table dans une autre en utilisant une instruction SELECT comme entrée :

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

Si la table Singers n'incluait aucune ligne et si la table AckworthSingers en comportait trois, la table Singers comprend maintenant trois lignes :

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

L'exemple ci-dessous montre comment utiliser UNNEST pour afficher une table qui correspond à l'entrée de la commande INSERT.

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

Une fois que ces trois lignes ont été ajoutées à la table Singers de l'exemple précédent, cette dernière inclut six lignes :

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

Instruction INSERT utilisant une sous-requête

L'exemple ci-dessous montre comment insérer une ligne dans une table, où l'une des valeurs est calculée à l'aide d'une sous-requête :

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

Les tables ci-dessous affichent les données avant l'exécution de l'instruction.

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

La table ci-dessous affiche les données après l'exécution de l'instruction.

Singers

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

Pour inclure plusieurs colonnes, vous devez utiliser plusieurs sous-requêtes :

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

Instruction DELETE

Utilisez l'instruction DELETE pour supprimer des lignes d'une table.

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

Clause WHERE

La clause WHERE est requise. Cette exigence permet d'empêcher la suppression accidentelle de toutes les lignes d'une table. Pour supprimer toutes les lignes d'une table, définissez l'élément condition sur true :

DELETE FROM target_name WHERE true;

La clause WHERE peut contenir toute instruction SQL valide, y compris une sous-requête faisant référence à d'autres tables.

Alias

La clause WHERE utilise un alias implicite pour désigner target_name. Cet alias vous permet de référencer les colonnes de target_name sans les qualifier avec target_name. Par exemple, si votre instruction commence par DELETE FROM Singers, vous pouvez accéder à toutes les colonnes de Singers qui sont spécifiées dans la clause WHERE. Dans l'exemple ci-dessous, FirstName est une colonne de la table Singers :

DELETE FROM Singers WHERE FirstName = 'Alice';

Vous pouvez également créer un alias explicite à l'aide du mot clé AS facultatif. Pour en savoir plus sur les alias, consultez la page Syntaxe des requêtes.

Exemples d'instructions DELETE

Instruction DELETE utilisant une clause WHERE

L'instruction DELETE suivante permet de supprimer tous les chanteurs dont le prénom est Alice :

DELETE FROM Singers WHERE FirstName = 'Alice';

La table ci-dessous affiche les données avant l'exécution de l'instruction.

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

La table ci-dessous affiche les données après l'exécution de l'instruction.

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

Instruction DELETE utilisant une sous-requête

L'instruction suivante permet de supprimer de la table SINGERS tous les chanteurs dont le prénom ne figure pas dans AckworthSingers :

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

La table ci-dessous affiche les données avant l'exécution de l'instruction.

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

La table ci-dessous affiche les données après l'exécution de l'instruction.

Singers

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

Instruction UPDATE

Utilisez l'instruction UPDATE pour mettre à jour les lignes existantes d'une table.

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

update_item: path_expression = expression | path_expression = DEFAULT

Où :

  • target_name correspond au nom d'une table à mettre à jour.
  • La clause SET est une liste de fonctions update_item à exécuter sur chaque ligne pour laquelle la condition WHERE est "true".
  • path_expression est un nom de colonne.
  • expression est une expression de mise à jour. L'expression peut correspondre à un littéral, ou bien à une expression ou une sous-requête SQL.

Les instructions UPDATE doivent respecter les règles suivantes :

  • Une colonne ne peut apparaître qu'une seule fois dans la clause SET.
  • Les colonnes de la clause SET peuvent être répertoriées dans n'importe quel ordre.
  • Chaque valeur doit avoir un type compatible avec la colonne associée.
  • Les valeurs doivent respecter toutes les contraintes du schéma (telles que des index secondaires uniques ou les colonnes n'autorisant pas les valeurs nulles).
  • Les mises à jour comportant des jointures ne sont pas acceptées.
  • Vous ne pouvez pas mettre à jour les colonnes de clé primaire.

Si une instruction n'est pas conforme aux règles, Cloud Spanner génère une erreur, et toute l'instruction échoue.

Les colonnes non incluses dans la clause SET ne sont pas modifiées.

Les mises à jour de colonnes sont effectuées simultanément. Par exemple, vous pouvez permuter deux valeurs de colonnes à l'aide d'une seule clause SET :

SET x = y, y = x

Compatibilité des types de valeurs

Les valeurs mises à jour avec une instruction UPDATE doivent être compatibles avec le type de la colonne cible. Le type d'une valeur est compatible avec le type de la colonne cible si la valeur est conforme à l'un des critères suivants :

  • Le type de valeur correspond exactement au type de colonne. Par exemple, le type de valeur est INT64 et le type de colonne est INT64.
  • Cloud Spanner peut convertir implicitement le type de la valeur pour qu'il corresponde à celui de la cible.

Valeurs par défaut

Le mot clé DEFAULT définit la valeur d'une colonne sur NULL.

Clause WHERE

La clause WHERE est requise. Cette exigence permet d'empêcher la mise à jour accidentelle de toutes les lignes d'une table. Pour mettre à jour toutes les lignes d'une table, définissez l'élément condition sur true.

La clause WHERE peut contenir toute expression booléenne SQL valide, y compris une sous-requête faisant référence à d'autres tables.

Alias

La clause WHERE utilise un alias implicite pour désigner target_name. Cet alias vous permet de référencer les colonnes de target_name sans les qualifier avec target_name. Par exemple, si votre instruction commence par UPDATE Singers, vous pouvez accéder à toutes les colonnes de Singers qui sont spécifiées dans la clause WHERE. Dans l'exemple ci-dessous, FirstName et LastName sont des colonnes de la table Singers :

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

Vous pouvez également créer un alias explicite à l'aide du mot clé AS facultatif. Pour en savoir plus sur les alias, consultez la page Syntaxe des requêtes.

Exemples d'instructions UPDATE

Instruction UPDATE utilisant des valeurs littérales

L'exemple ci-dessous permet de mettre à jour la table Singers en modifiant la colonne BirthDate dans l'une des lignes.

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

La table ci-dessous affiche les données avant l'exécution de l'instruction.

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

La table ci-dessous affiche les données après l'exécution de l'instruction.

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

Instruction UPDATE appliquée à des colonnes de type ARRAY

L'exemple ci-dessous permet de mettre à jour une colonne de type ARRAY.

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

La table ci-dessous affiche les données avant l'exécution de l'instruction.

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

La table ci-dessous affiche les données après l'exécution de l'instruction.

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

Paramètres STRUCT liés

Vous pouvez utiliser des paramètres STRUCT liés dans la clause WHERE d'une instruction LMD. L'exemple de code ci-dessous permet de mettre à jour l'élément LastName dans les lignes filtrées en fonction des colonnes FirstName et 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."

Horodatages de commit

Utilisez la fonction PENDING_COMMIT_TIMESTAMP pour écrire des horodatages de commit dans une colonne de type TIMESTAMP. L'option allow_commit_timestamp doit être définie sur true pour la colonne. L'instruction LMD suivante met à jour la colonne LastUpdated de la table Singers avec l'horodatage de commit :

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

Pour en savoir plus sur l'utilisation des horodatages de commit dans le langage LMD, consultez la section Écrire des horodatages de commit.