Datas/horas de confirmação em bases de dados com dialeto GoogleSQL

Este tópico descreve como escrever uma data/hora de confirmação para cada operação de inserção e atualização que realizar com o Spanner. Para usar esta funcionalidade, defina a opção allow_commit_timestamp numa coluna TIMESTAMP e, em seguida, escreva a data/hora como parte de cada transação.

Vista geral

A data/hora de confirmação, com base na tecnologia TrueTime, é a data/hora em que uma transação é confirmada na base de dados. A opção de coluna allow_commit_timestamp permite-lhe armazenar atomicamente a data/hora de confirmação numa coluna. Usando as datas/horas de confirmação armazenadas em tabelas, pode determinar a ordem exata das mutações e criar funcionalidades como registos de alterações.

Para inserir datas/horas de confirmação na sua base de dados, conclua os seguintes passos:

  1. Crie uma coluna com o tipo TIMESTAMP com a opção de coluna allow_commit_timestamp definida como true na definição do esquema. Por exemplo:

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

  2. Se estiver a fazer inserções ou atualizações com DML, use a função PENDING_COMMIT_TIMESTAMP para escrever a data/hora de confirmação.

    Se estiver a fazer inserções ou atualizações com mutações, use a string de marcador de posição spanner.commit_timestamp() em inserções ou atualizações na coluna de data/hora de confirmação. Também pode usar a constante de data/hora de confirmação fornecida pela biblioteca de cliente. Por exemplo, esta constante no cliente Java é Value.COMMIT_TIMESTAMP.

Quando o Spanner confirma a transação usando estes marcadores de posição como valores de coluna, a data/hora de confirmação real é escrita na coluna especificada (por exemplo, a coluna LastUpdateTime). Em seguida, pode usar este valor da coluna para criar um histórico de atualizações da tabela.

Não é garantido que os valores de data/hora de confirmação sejam exclusivos. As transações que escrevem em conjuntos de campos não sobrepostos podem ter a mesma data/hora. As transações que escrevem em conjuntos de campos sobrepostos têm informações de data/hora únicas.

As indicações de tempo de confirmação do Spanner têm um nível de detalhe de microssegundos e são convertidas em nanosegundos quando armazenadas em colunas TIMESTAMP.

Crie e elimine uma coluna de indicação de tempo de confirmação

Use a opção de coluna allow_commit_timestamp para adicionar e remover suporte para datas/horas de confirmação:

  • Quando criar uma nova tabela para especificar que uma coluna suporta datas/horas de confirmação.
  • Quando alterar uma tabela existente:
    • Para adicionar uma nova coluna que suporte as datas/horas de confirmação,
    • Para alterar uma coluna TIMESTAMP existente de modo a suportar as datas/horas de confirmação:
    • para alterar uma coluna TIMESTAMP existente de modo a remover o suporte da indicação de tempo de confirmação

Chaves e índices

Pode usar uma coluna de data/hora de confirmação como uma coluna de chave principal ou como uma coluna sem chave. As chaves primárias podem ser definidas como ASC ou DESC.

  • ASC (predefinição) – As chaves ascendentes são ideais para responder a consultas a partir de um momento específico.
  • DESC – As chaves descendentes mantêm as linhas mais recentes na parte superior da tabela. Oferecem acesso rápido aos registos mais recentes.

A opção allow_commit_timestamp tem de ser consistente nas chaves primárias das tabelas principais e secundárias. Se a opção não for consistente nas chaves primárias, o Spanner devolve um erro. A opção só pode ser inconsistente quando está a criar ou atualizar o esquema.

A utilização de datas/horas de confirmação nos seguintes cenários cria hotspots que reduzem o desempenho dos dados:

  • Coluna de data/hora de confirmação como a primeira parte da chave principal de uma tabela:

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

  • A primeira parte da chave primária de um índice secundário:

    CREATE INDEX UsersByLastAccess ON Users(LastAccess)

    ou

    CREATE INDEX UsersByLastAccessAndName ON Users(LastAccess, FirstName)

Os pontos críticos reduzem o desempenho dos dados, mesmo com taxas de gravação baixas. Não existe sobrecarga de desempenho se as datas/horas de confirmação estiverem ativadas em colunas não principais que não estejam indexadas.

Crie uma coluna de data/hora de confirmação

O LDD seguinte cria uma tabela com uma coluna que suporta carimbos de data/hora de confirmação.

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

A adição da opção altera a coluna de indicação de tempo da seguinte forma:

  • Pode usar a string de marcador de posição spanner.commit_timestamp() (ou uma constante fornecida pela biblioteca de cliente) para inserções e atualizações.
  • A coluna só pode conter valores no passado. Para mais informações, consulte o artigo Fornecer o seu próprio valor para a data/hora.

A opção allow_commit_timestamp é sensível a maiúsculas e minúsculas.

Adicione uma coluna de data/hora de confirmação a uma tabela existente

Para adicionar uma coluna de data/hora de confirmação a uma tabela existente, use a declaração ALTER TABLE. Por exemplo, para adicionar uma coluna LastUpdateTime à tabela Performances, use a seguinte declaração:

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

Converta uma coluna de data/hora numa coluna de data/hora de confirmação

Pode converter uma coluna de data/hora existente numa coluna de data/hora de confirmação, mas, para isso, o Spanner tem de validar se os valores de data/hora existentes são no passado. Por exemplo:

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

Não é possível alterar o tipo de dados nem a anotação NULL de uma coluna numa declaração ALTER TABLE que inclua SET OPTIONS. Para obter detalhes, consulte o artigo Linguagem de definição de dados.

Remova a opção de indicação de tempo de confirmação

Se quiser remover o suporte de data/hora de confirmação de uma coluna, use a opção allow_commit_timestamp=null numa declaração ALTER TABLE. O comportamento da data/hora de confirmação é removido, mas a coluna continua a ser uma data/hora. A alteração da opção não altera nenhuma outra característica da coluna, como o tipo ou a capacidade de ser nula (NOT NULL). Por exemplo:

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

Escrever uma data/hora de confirmação com uma declaração DML

Use a função PENDING_COMMIT_TIMESTAMP para escrever a data/hora de confirmação numa declaração DML. O Spanner seleciona a data/hora de confirmação quando a transação é confirmada.

A seguinte declaração DML atualiza a coluna LastUpdateTime na tabela Performances com a data/hora de confirmação:

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

O exemplo de código seguinte usa a função PENDING_COMMIT_TIMESTAMP para escrever a data/hora de confirmação na coluna 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"

As datas/horas de confirmação só podem ser escritas em colunas anotadas com a opção allow_commit_timestamp=true.

Se tiver mutações em linhas em várias tabelas, tem de especificar spanner.commit_timestamp() (ou a constante da biblioteca de cliente) para a coluna de data/hora de confirmação em cada tabela.

Consultar uma coluna de data/hora de confirmação

O exemplo seguinte consulta a coluna de data/hora de confirmação da tabela.

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

Indique o seu próprio valor para a coluna de data/hora de confirmação

Pode fornecer o seu próprio valor para a coluna de data/hora de confirmação, em vez de transmitir spanner.commit_timestamp() (ou a constante da biblioteca de cliente) como o valor da coluna. O valor tem de ser uma data/hora no passado. Esta restrição garante que a escrita de datas/horas é uma operação barata e rápida. O servidor devolve um erro FailedPrecondition se for especificado um registo de data/hora futuro.

Crie um registo de alterações

Suponhamos que quer criar um registo de alterações de todas as mutações que ocorrem numa tabela e, em seguida, usar esse registo de alterações para auditoria. Um exemplo seria uma tabela que armazena o histórico de alterações a documentos de processamento de texto. A data/hora de confirmação facilita a criação do histórico de alterações, porque as datas/horas podem aplicar a ordenação das entradas do histórico de alterações. Pode criar um registo de alterações que armazene o histórico de alterações a um determinado documento através de um esquema como o do exemplo seguinte:

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;

Para criar um registo de alterações, insira uma nova linha em DocumentHistory na mesma transação em que insere ou atualiza uma linha em Document. Na inserção da nova linha em DocumentHistory, use o marcador de posição spanner.commit_timestamp() (ou a constante da biblioteca de cliente) para indicar ao Spanner que escreva a data/hora de confirmação na coluna Ts. A intercalação da tabela DocumentsHistory com a tabela Documents permite a localidade dos dados e inserções e atualizações mais eficientes. No entanto, também adiciona a restrição de que as linhas principal e secundária têm de ser eliminadas em conjunto. Para manter as linhas em DocumentHistory depois de eliminar as linhas em Documents, não entrelace as tabelas.

Otimize as consultas de dados recentes com indicações de tempo de confirmação

As datas/horas de confirmação permitem uma otimização do Spanner que pode reduzir a E/S de consultas quando são obtidos dados escritos após uma determinada hora.

Para ativar esta otimização, a cláusula WHERE de uma consulta tem de incluir uma comparação entre a coluna de data/hora de confirmação de uma tabela e uma hora específica que indicar, com os seguintes atributos:

  • Indique a hora específica como uma expressão constante: um literal, um parâmetro ou uma função cujos próprios argumentos são avaliados como constantes.

  • Compare se a data/hora de confirmação é mais recente do que a hora indicada através dos operadores > ou >=.

  • Opcionalmente, adicione mais restrições à cláusula WHERE com AND. A extensão da cláusula com OR desqualifica a consulta desta otimização.

Por exemplo, considere a seguinte tabela Performances, que inclui uma coluna de data/hora de confirmação:

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

Esta consulta beneficia da otimização da data/hora de confirmação descrita anteriormente, porque tem uma comparação maior ou igual entre a coluna de data/hora de confirmação da tabela e uma expressão constante. Neste caso, trata-se de um literal:

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

A seguinte consulta também se qualifica para a otimização, uma vez que tem uma comparação do tipo "maior que" entre a data/hora de confirmação e uma função cujos argumentos são todos avaliados como constantes durante a execução da consulta:

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

O que se segue?