Validez les codes temporels dans les bases de données SQL SQL.

Cet article explique comment écrire un horodatage de commit pour chaque opération d'insertion et de mise à jour que vous effectuez avec Spanner. Pour utiliser cette fonctionnalité, définissez l'option allow_commit_timestamp sur une colonne TIMESTAMP, puis écrivez l'horodatage dans le cadre de chaque transaction.

Présentation

L'horodatage de commit, basé sur la technologie TrueTime, correspond à l'heure à laquelle une transaction est validée dans la base de données. L'option de colonne allow_commit_timestamp vous permet de stocker de manière atomique l'horodatage de commit dans une colonne. À l'aide des horodatages de commit stockés dans les tables, vous pouvez déterminer l'ordre exact des mutations et créer des fonctionnalités telles que les journaux des modifications.

Pour insérer des horodatages de commit dans votre base de données, procédez comme suit :

  1. Créez une colonne de type TIMESTAMP avec l'option de colonne allow_commit_timestamp définie sur true dans la définition de schéma. Exemple :

    CREATE TABLE Performances (
    ...
    LastUpdateTime  TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true)
    ...
) PRIMARY KEY (...);

  2. Si vous effectuez des insertions ou des mises à jour en langage LMD, utilisez la fonction PENDING_COMMIT_TIMESTAMP pour écrire l'horodatage de commit.

    Si vous effectuez des insertions ou des mises à jour avec des mutations, utilisez la chaîne d'espace réservé spanner.commit_timestamp() pour les insertions ou les mises à jour de votre colonne d'horodatage de commit. Vous pouvez également utiliser la constante d'horodatage de commit fournie par la bibliothèque cliente. Par exemple, cette constante dans le client Java est Value.COMMIT_TIMESTAMP.

Lorsque Spanner effectue la transaction en utilisant ces espaces réservés comme valeurs de colonne, l'horodatage de commit réel est écrit dans la colonne spécifiée (par exemple, la colonne LastUpdateTime). Vous pouvez ensuite utiliser cette valeur de colonne pour créer un historique des mises à jour de la table.

Le caractère unique des valeurs d'horodatage de commit n'est pas garanti. Les transactions qui écrivent dans des ensembles de champs ne se chevauchant pas peuvent avoir le même horodatage. Les transactions qui écrivent dans des ensembles de champs qui se chevauchent ont des horodatages uniques.

Les horodatages de commit Spanner sont d'une précision de l'ordre de la microseconde et sont convertis en nanosecondes lorsqu'ils sont stockés dans des colonnes TIMESTAMP.

Créer et supprimer une colonne d'horodatage de commit

Utilisez l'option de colonne allow_commit_timestamp pour ajouter et supprimer la compatibilité avec les horodatages de commit :

  • Lors de la création d'une table, pour spécifier qu'une colonne accepte les horodatages de commit.
  • Lorsque vous modifiez une table existante :
    • pour ajouter une nouvelle colonne acceptant les horodatages de commit ;
    • pour modifier une colonne TIMESTAMP existante afin d'accepter les horodatages de commit ;
    • pour modifier une colonne TIMESTAMP existante afin de supprimer la compatibilité avec l'horodatage de commit.

Clés et index

Vous pouvez utiliser une colonne d'horodatage de commit en tant que colonne de clé primaire ou en tant que colonne non clé. Les clés primaires peuvent être définies comme ASC ou DESC.

  • ASC (valeur par défaut) : les clés croissantes sont idéales pour répondre aux requêtes à partir d'une heure spécifique.
  • DESC : les clés décroissantes conservent les dernières lignes en haut de la table. Elles fournissent un accès rapide aux derniers enregistrements.

L'option allow_commit_timestamp doit être cohérente entre les clés primaires des tables parents et enfants. Si l'option n'est pas cohérente entre les clés primaires, Spanner renvoie une erreur. Le seul moment où l'option peut être incohérente est lorsque vous créez ou mettez à jour le schéma.

L'utilisation d'horodatages de commit dans les scénarios suivants engendre des problèmes de hotspotting et réduit les performances de traitement des données :

  • Colonne d'horodatage de commit en tant que première partie de la clé primaire d'une table :

    CREATE TABLE Users (
  LastAccess TIMESTAMP NOT NULL,
  UserId     INT64 NOT NULL,
  ...
) PRIMARY KEY (LastAccess, UserId);

  • Première partie de la clé primaire d'un index secondaire :

    CREATE INDEX UsersByLastAccess ON Users(LastAccess)

    ou

    CREATE INDEX UsersByLastAccessAndName ON Users(LastAccess, FirstName)

Les hotspots réduisent les performances de traitement des données, même avec des vitesses d'écriture faibles. L'activation de l'horodatage de commit sur des colonnes non clés non indexées n'impacte pas les performances.

Créer une colonne d'horodatage de commit

Le LDD suivant crée une table avec une colonne compatible avec les horodatages de commit.

CREATE TABLE Performances (
    SingerId        INT64 NOT NULL,
    VenueId         INT64 NOT NULL,
    EventDate       Date,
    Revenue         INT64,
    LastUpdateTime  TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true)
) PRIMARY KEY (SingerId, VenueId, EventDate),
  INTERLEAVE IN PARENT Singers ON DELETE CASCADE

L'ajout de l'option modifie la colonne d'horodatage comme suit :

  • Vous pouvez utiliser la chaîne d'espace réservé spanner.commit_timestamp() (ou une constante fournie par la bibliothèque cliente) pour les insertions et les mises à jour.
  • La colonne ne peut contenir que des valeurs antérieures au moment actuel. Pour plus d'informations, consultez la section Fournir votre propre valeur pour la colonne d'horodatage.

L'option allow_commit_timestamp est sensible à la casse.

Ajouter une colonne d'horodatage de commit à une table existante

Pour ajouter une colonne d'horodatage de commit à une table existante, utilisez l'instruction ALTER TABLE. Par exemple, pour ajouter une colonne LastUpdateTime à la table Performances, utilisez l'instruction suivante :

ALTER TABLE Performances ADD COLUMN LastUpdateTime TIMESTAMP
    NOT NULL OPTIONS (allow_commit_timestamp=true)

Convertir une colonne d'horodatage en colonne d'horodatage de commit

Vous pouvez convertir une colonne d'horodatage existante en colonne d'horodatage de commit, mais pour cela, Spanner doit vérifier que les valeurs d'horodatage existantes sont passées. Exemple :

ALTER TABLE Performances ALTER COLUMN LastUpdateTime
    SET OPTIONS (allow_commit_timestamp=true)

Vous ne pouvez pas modifier le type de données ou l'annotation NULL d'une colonne dans une instruction ALTER TABLE incluant SET OPTIONS. Pour plus d'informations, consultez la page Langage de définition de données.

Supprimer l'option d'horodatage de commit

Si vous souhaitez supprimer la compatibilité d'une colonne avec l'horodatage de commit, utilisez l'option allow_commit_timestamp=null dans une instruction ALTER TABLE. Le comportement lié à l'horodatage de commit est supprimé, mais la colonne reste toujours un horodatage. La modification de cette option n'altère pas les autres caractéristiques de la colonne, telles que le type ou l'option de valeur vide (NOT NULL). Par exemple :

ALTER TABLE Performances ALTER COLUMN LastUpdateTime
    SET OPTIONS (allow_commit_timestamp=null)

Écrire un horodatage de commit à l'aide d'une instruction LMD

Pour écrire l'horodatage de commit dans une instruction LMD, utilisez la fonction PENDING_COMMIT_TIMESTAMP. Spanner sélectionne l'horodatage de commit lors du commit de la transaction.

L'instruction LMD suivante met à jour la colonne LastUpdateTime de la table Performances avec l'horodatage de commit :

UPDATE Performances SET LastUpdateTime = PENDING_COMMIT_TIMESTAMP()
   WHERE SingerId=1 AND VenueId=2 AND EventDate="2015-10-21"

L'exemple de code suivant utilise la fonction PENDING_COMMIT_TIMESTAMP pour écrire l'horodatage de commit dans la colonne LastUpdateTime.

C++

void DmlStandardUpdateWithTimestamp(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 LastUpdateTime = PENDING_COMMIT_TIMESTAMP()"
                "  WHERE SingerId = 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_with_timestamp]\n";
}

C#


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

public class UpdateUsingDmlWithTimestampCoreAsyncSample
{
    public async Task<int> UpdateUsingDmlWithTimestampCoreAsync(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 LastUpdateTime = PENDING_COMMIT_TIMESTAMP() WHERE SingerId = 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 updateUsingDMLWithTimestamp(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 LastUpdateTime = PENDING_COMMIT_TIMESTAMP()
				WHERE SingerId = 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 updateUsingDmlWithTimestamp(DatabaseClient dbClient) {
  dbClient
      .readWriteTransaction()
      .run(transaction -> {
        String sql =
            "UPDATE Albums "
                + "SET LastUpdateTime = PENDING_COMMIT_TIMESTAMP() WHERE SingerId = 1";
        long rowCount = transaction.executeUpdate(Statement.of(sql));
        System.out.printf("%d records 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 LastUpdateTime = PENDING_COMMIT_TIMESTAMP()
        WHERE SingerId = 1`,
    });

    console.log(`Successfully updated ${rowCount} records.`);
    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;

/**
 * Update data with a DML statement using timestamps.
 *
 * 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_timestamp(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 LastUpdateTime = PENDING_COMMIT_TIMESTAMP() WHERE SingerId = 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 LastUpdateTime = PENDING_COMMIT_TIMESTAMP() "
        "WHERE SingerId = 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 LastUpdateTime = PENDING_COMMIT_TIMESTAMP() WHERE SingerId = 1"
  )
end

puts "#{row_count} records updated."

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

commit_timestamp = client.commit_timestamp

client.commit do |c|
  c.update "Albums", [
    { SingerId: 1, AlbumId: 1, MarketingBudget: 100_000, LastUpdateTime: commit_timestamp },
    { SingerId: 2, AlbumId: 2, MarketingBudget: 750_000, LastUpdateTime: commit_timestamp }
  ]
end

puts "Updated data"

Les horodatages de commit ne peuvent être écrits que dans des colonnes annotées avec l'option allow_commit_timestamp=true.

Si des mutations sont présentes sur des lignes dans plusieurs tables, vous devez spécifier spanner.commit_timestamp() (ou la constante de la bibliothèque cliente) pour la colonne d'horodatage de commit de chaque table.

Interroger une colonne d'horodatage de commit

L'exemple suivant interroge la colonne d'horodatage de commit de la table.

C++

void QueryDataWithTimestamp(google::cloud::spanner::Client client) {
  namespace spanner = ::google::cloud::spanner;

  spanner::SqlStatement select(
      "SELECT SingerId, AlbumId, MarketingBudget, LastUpdateTime"
      "  FROM Albums"
      " ORDER BY LastUpdateTime DESC");
  using RowType =
      std::tuple<std::int64_t, std::int64_t, absl::optional<std::int64_t>,
                 absl::optional<spanner::Timestamp>>;

  auto rows = client.ExecuteQuery(std::move(select));
  for (auto& row : spanner::StreamOf<RowType>(rows)) {
    if (!row) throw std::move(row).status();
    std::cout << std::get<0>(*row) << " " << std::get<1>(*row);
    auto marketing_budget = std::get<2>(*row);
    if (!marketing_budget) {
      std::cout << " NULL";
    } else {
      std::cout << ' ' << *marketing_budget;
    }
    auto last_update_time = std::get<3>(*row);
    if (!last_update_time) {
      std::cout << " NULL";
    } else {
      std::cout << ' ' << *last_update_time;
    }
    std::cout << "\n";
  }
}

C#


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

public class QueryDataWithTimestampColumnAsyncSample
{
    public class Album
    {
        public int SingerId { get; set; }
        public int AlbumId { get; set; }
        public DateTime? LastUpdateTime { get; set; }
        public long? MarketingBudget { get; set; }
    }

    public async Task<List<Album>> QueryDataWithTimestampColumnAsync(string projectId, string instanceId, string databaseId)
    {
        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";

        using var connection = new SpannerConnection(connectionString);
        using var cmd = connection.CreateSelectCommand("SELECT SingerId, AlbumId, MarketingBudget, LastUpdateTime FROM Albums ORDER BY LastUpdateTime DESC");

        var albums = new List<Album>();
        using var reader = await cmd.ExecuteReaderAsync();
        while (await reader.ReadAsync())
        {
            albums.Add(new Album
            {
                SingerId = reader.GetFieldValue<int>("SingerId"),
                AlbumId = reader.GetFieldValue<int>("AlbumId"),
                LastUpdateTime = reader.IsDBNull(reader.GetOrdinal("LastUpdateTime")) ? (DateTime?)null : reader.GetFieldValue<DateTime>("LastUpdateTime"),
                MarketingBudget = reader.IsDBNull(reader.GetOrdinal("MarketingBudget")) ? 0 : reader.GetFieldValue<long>("MarketingBudget")
            });
        }
        return albums;
    }
}

Go


import (
	"context"
	"fmt"
	"io"
	"strconv"

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

func queryWithTimestamp(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: `SELECT SingerId, AlbumId, MarketingBudget, LastUpdateTime
				FROM Albums ORDER BY LastUpdateTime DESC`}
	iter := client.Single().Query(ctx, stmt)
	defer iter.Stop()
	for {
		row, err := iter.Next()
		if err == iterator.Done {
			return nil
		}
		if err != nil {
			return err
		}
		var singerID, albumID int64
		var marketingBudget spanner.NullInt64
		var lastUpdateTime spanner.NullTime
		if err := row.ColumnByName("SingerId", &singerID); err != nil {
			return err
		}
		if err := row.ColumnByName("AlbumId", &albumID); err != nil {
			return err
		}
		if err := row.ColumnByName("MarketingBudget", &marketingBudget); err != nil {
			return err
		}
		budget := "NULL"
		if marketingBudget.Valid {
			budget = strconv.FormatInt(marketingBudget.Int64, 10)
		}
		if err := row.ColumnByName("LastUpdateTime", &lastUpdateTime); err != nil {
			return err
		}
		timestamp := "NULL"
		if lastUpdateTime.Valid {
			timestamp = lastUpdateTime.String()
		}
		fmt.Fprintf(w, "%d %d %s %s\n", singerID, albumID, budget, timestamp)
	}
}

Java

static void queryMarketingBudgetWithTimestamp(DatabaseClient dbClient) {
  // Rows without an explicit value for MarketingBudget will have a MarketingBudget equal to
  // null. A try-with-resource block is used to automatically release resources held by
  // ResultSet.
  try (ResultSet resultSet =
      dbClient
          .singleUse()
          .executeQuery(
              Statement.of(
                  "SELECT SingerId, AlbumId, MarketingBudget, LastUpdateTime FROM Albums"
                      + " ORDER BY LastUpdateTime DESC"))) {
    while (resultSet.next()) {
      System.out.printf(
          "%d %d %s %s\n",
          resultSet.getLong("SingerId"),
          resultSet.getLong("AlbumId"),
          // We check that the value is non null. ResultSet getters can only be used to retrieve
          // non null values.
          resultSet.isNull("MarketingBudget") ? "NULL" : resultSet.getLong("MarketingBudget"),
          resultSet.isNull("LastUpdateTime") ? "NULL" : resultSet.getTimestamp("LastUpdateTime"));
    }
  }
}

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

const query = {
  sql: `SELECT SingerId, AlbumId, MarketingBudget, LastUpdateTime
          FROM Albums ORDER BY LastUpdateTime DESC`,
};

// Queries rows from the Albums table
try {
  const [rows] = await database.run(query);

  rows.forEach(row => {
    const json = row.toJSON();

    console.log(
      `SingerId: ${json.SingerId}, AlbumId: ${
        json.AlbumId
      }, MarketingBudget: ${
        json.MarketingBudget ? json.MarketingBudget : null
      }, LastUpdateTime: ${json.LastUpdateTime}`
    );
  });
} catch (err) {
  console.error('ERROR:', err);
} finally {
  // Close the database when finished
  database.close();
}

PHP

use Google\Cloud\Spanner\SpannerClient;

/**
 * Queries sample data from a database with a commit timestamp column.
 *
 * This sample uses the `MarketingBudget` column. 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
 *
 * This sample also uses the 'LastUpdateTime' commit timestamp column. You can
 * add the column by running the `add_timestamp_column` sample or by running
 * this DDL statement against your database:
 *
 * 		ALTER TABLE Albums ADD COLUMN LastUpdateTime TIMESTAMP OPTIONS (allow_commit_timestamp=true)
 *
 * Example:
 * ```
 * query_data_with_timestamp_column($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function query_data_with_timestamp_column(string $instanceId, string $databaseId): void
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $results = $database->execute(
        'SELECT SingerId, AlbumId, MarketingBudget, LastUpdateTime ' .
        ' FROM Albums ORDER BY LastUpdateTime DESC'
    );

    foreach ($results as $row) {
        if ($row['MarketingBudget'] == null) {
            $row['MarketingBudget'] = 'NULL';
        }
        if ($row['LastUpdateTime'] == null) {
            $row['LastUpdateTime'] = 'NULL';
        }
        printf('SingerId: %s, AlbumId: %s, MarketingBudget: %s, LastUpdateTime: %s' . PHP_EOL,
            $row['SingerId'], $row['AlbumId'], $row['MarketingBudget'], $row['LastUpdateTime']);
    }
}

Python

def query_data_with_timestamp(instance_id, database_id):
    """Queries sample data from the database using SQL.

    This updates the `LastUpdateTime` column which must be created before
    running this sample. You can add the column by running the
    `add_timestamp_column` sample or by running this DDL statement
    against your database:

        ALTER TABLE Performances ADD COLUMN LastUpdateTime TIMESTAMP
        OPTIONS (allow_commit_timestamp=true)

    """
    spanner_client = spanner.Client()
    instance = spanner_client.instance(instance_id)

    database = instance.database(database_id)

    with database.snapshot() as snapshot:
        results = snapshot.execute_sql(
            "SELECT SingerId, AlbumId, MarketingBudget FROM Albums "
            "ORDER BY LastUpdateTime DESC"
        )

    for row in results:
        print("SingerId: {}, AlbumId: {}, MarketingBudget: {}".format(*row))

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

client.execute("SELECT SingerId, AlbumId, MarketingBudget, LastUpdateTime
                FROM Albums ORDER BY LastUpdateTime DESC").rows.each do |row|
  puts "#{row[:SingerId]} #{row[:AlbumId]} #{row[:MarketingBudget]} #{row[:LastUpdateTime]}"
end

Indiquez votre propre valeur pour la colonne d'horodatage de commit

Vous pouvez fournir votre propre valeur pour la colonne d'horodatage de commit au lieu de transmettre spanner.commit_timestamp() (ou la constante de la bibliothèque cliente) en tant que valeur de colonne. La valeur doit être un horodatage antérieur au moment actuel. Cette restriction garantit que l'écriture d'horodatages est une opération rapide et peu coûteuse. Le serveur renvoie une erreur FailedPrecondition si un horodatage futur est spécifié.

Créer un journal de modifications

Supposons que vous souhaitiez créer un journal des modifications pour chaque mutation qui se produit dans une table, puis l'utiliser pour un audit. Prenons l'exemple d'une table qui stocke l'historique des modifications apportées aux documents de traitement de texte. L'horodatage de commit facilite la création du journal des modifications, car les horodatages peuvent imposer le classement des modifications des entrées. Vous pouvez créer un journal des modifications qui stocke l'historique des modifications apportées à un document donné à l'aide d'un schéma semblable à l'exemple suivant :

CREATE TABLE Documents (
  UserId     INT64 NOT NULL,
  DocumentId INT64 NOT NULL,
  Contents   STRING(MAX) NOT NULL,
) PRIMARY KEY (UserId, DocumentId);

CREATE TABLE DocumentHistory (
  UserId     INT64 NOT NULL,
  DocumentId INT64 NOT NULL,
  Ts         TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true),
  Delta      STRING(MAX),
) PRIMARY KEY (UserId, DocumentId, Ts),
  INTERLEAVE IN PARENT Documents ON DELETE NO ACTION;

Pour créer un journal des modifications, insérez une nouvelle ligne dans la table DocumentHistory, dans la même transaction que celle dans laquelle vous insérez ou mettez à jour une ligne dans la table Document. Lors de l'insertion de la nouvelle ligne dans DocumentHistory, utilisez l'espace réservé spanner.commit_timestamp() (ou la constante de la bibliothèque cliente) pour indiquer à Spanner d'écrire l'horodatage de commit dans la colonne Ts. Si vous entrelacez la table DocumentsHistory avec la table Documents, la localité des données, les insertions et les mises à jour seront plus efficaces. Cependant, ce procédé ajoute également une contrainte : les lignes parents et enfants doivent être supprimées ensemble. Pour garder les lignes dans la table DocumentHistory après la suppression des lignes de la table Documents, n'entrelacez pas les tables.

Optimiser les requêtes de données récentes avec les horodatages de commit

Les horodatages de commit permettent une optimisation Spanner qui permet de réduire les E/S de requête lors de la récupération de données écrites après un certain délai.

Pour activer cette optimisation, la clause WHERE d'une requête doit inclure une comparaison entre la colonne d'horodatage de commit d'une table et une heure spécifique que vous indiquez, avec les attributs suivants:

  • Indiquez l'heure spécifique sous forme d'expression constante: un littéral, un paramètre ou une fonction dont les propres arguments renvoient des constantes.

  • Déterminez si l'horodatage de commit est plus récent que l'heure donnée, via les opérateurs > ou >=.

  • Vous pouvez également ajouter des restrictions supplémentaires à la clause WHERE avec AND. L'extension de la clause avec OR disqualifie la requête de cette optimisation.

Prenons l'exemple de la table Performances suivante, qui inclut une colonne d'horodatage de commit:

CREATE TABLE Performances (
    SingerId INT64 NOT NULL,
    VenueId INT64 NOT NULL,
    EventDate DATE,
    Revenue INT64,
    LastUpdateTime TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true)
) PRIMARY KEY (SingerId, VenueId, EventDate);

Cette requête bénéficie de l'optimisation de l'horodatage de commit décrite précédemment, car elle comporte une comparaison de type "supérieur ou égal à" entre la colonne d'horodatage de commit de la table et une expression constante, dans ce cas, un littéral:

SELECT * FROM Performances WHERE LastUpdateTime >= "2022-05-01";

La requête suivante est également éligible à l'optimisation, car elle comporte une comparaison de type "supérieur à" entre l'horodatage de commit et une fonction dont les arguments renvoient tous des constantes lors de l'exécution de la requête:

SELECT * FROM Performances
  WHERE LastUpdateTime > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY);

Étapes suivantes