Carimbos de data/hora de confirmação

Neste tópico, você verá como gravar um carimbo de data/hora de commit para cada operação de inserção e atualização executada com o Cloud Spanner. Para usar esse recurso, defina a opção allow_commit_timestamp em uma coluna TIMESTAMP. Em seguida, grave o carimbo de data/hora como parte de cada transação.

Visão geral

O carimbo de data/hora de commit, com base na tecnologia TrueTime, é a hora em que uma transação é confirmada no banco de dados. A opção da coluna allow_commit_timestamp permite armazenar de maneira atômica o carimbo de data/hora de commit em uma coluna. Usando os carimbos de data/hora de commit armazenados em tabelas, você pode determinar a ordem exata de mutações e recursos de versão como registros de alterações.

Você faz duas coisas para inserir carimbos de data/hora de commit no banco de dados:

  • Crie uma coluna TIMESTAMP com a opção de coluna allow_commit_timestamp definida como true na definição do esquema. Por exemplo, se quisesse armazenar a hora em que uma linha na tabela Performances foi atualizada pela última vez, você poderia criar uma tabela com uma coluna de carimbo de data/hora de commit chamada LastUpdateTime:

    C#

    // Initialize request connection string for database creation.
    string connectionString =
        $"Data Source=projects/{projectId}/instances/{instanceId}"
        + $"/databases/{databaseId}";
    using (var connection = new SpannerConnection(connectionString))
    {
        // Define create table statement for table with
        // commit timestamp column.
        string createTableStatement =
        @"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";
        // Make the request.
        var cmd = connection.CreateDdlCommand(createTableStatement);
        await cmd.ExecuteNonQueryAsync();
    }

    Go

    func createTableWithTimestamp(ctx context.Context, w io.Writer, adminClient *database.DatabaseAdminClient, database string) error {
    	op, err := adminClient.UpdateDatabaseDdl(ctx, &adminpb.UpdateDatabaseDdlRequest{
    		Database: database,
    		Statements: []string{
    			`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`,
    		},
    	})
    	if err != nil {
    		return err
    	}
    	if err := op.Wait(ctx); err != nil {
    		return err
    	}
    	fmt.Fprintf(w, "Created Performances table in database [%s]\n", database)
    	return nil
    }
    

    Java

    static void createTableWithTimestamp(DatabaseAdminClient dbAdminClient, DatabaseId id) {
      OperationFuture<Void, UpdateDatabaseDdlMetadata> op =
          dbAdminClient.updateDatabaseDdl(
              id.getInstanceId().getInstance(),
              id.getDatabase(),
              Arrays.asList(
                  "CREATE TABLE Performances (\n"
                      + "  SingerId     INT64 NOT NULL,\n"
                      + "  VenueId      INT64 NOT NULL,\n"
                      + "  EventDate    Date,\n"
                      + "  Revenue      INT64, \n"
                      + "  LastUpdateTime TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true)\n"
                      + ") PRIMARY KEY (SingerId, VenueId, EventDate),\n"
                      + "  INTERLEAVE IN PARENT Singers ON DELETE CASCADE"),
              null);
      try {
        // Initiate the request which returns an OperationFuture.
        op.get();
        System.out.println("Created Performances table in database: [" + id + "]");
      } catch (ExecutionException e) {
        // If the operation failed during execution, expose the cause.
        throw (SpannerException) e.getCause();
      } catch (InterruptedException e) {
        // Throw when a thread is waiting, sleeping, or otherwise occupied,
        // and the thread is interrupted, either before or during the activity.
        throw SpannerExceptionFactory.propagateInterrupt(e);
      }
    }

    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
      const instance = spanner.instance(instanceId);
      const database = instance.database(databaseId);
    
      // Note: Cloud Spanner interprets Node.js numbers as FLOAT64s, so they
      // must be converted to strings before being inserted as INT64s
      const request = [
        `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`,
      ];
    
      // Creates a table in an existing database
      const [operation] = await database.updateSchema(request);
    
      console.log(`Waiting for operation on ${databaseId} to complete...`);
    
      await operation.promise();
    
      console.log(`Created table Performances in database ${databaseId}.`);
    

    PHP

    use Google\Cloud\Spanner\SpannerClient;
    
    /**
     * Creates a table with a commit timestamp column.
     * Example:
     * ```
     * create_table_with_timestamp_column($instanceId, $databaseId);
     * ```
     *
     * @param string $instanceId The Spanner instance ID.
     * @param string $databaseId The Spanner database ID.
     */
    function create_table_with_timestamp_column($instanceId, $databaseId)
    {
        $spanner = new SpannerClient();
        $instance = $spanner->instance($instanceId);
        $database = $instance->database($databaseId);
    
        $operation = $database->updateDdl(
            "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"
        );
    
        print('Waiting for operation to complete...' . PHP_EOL);
        $operation->pollUntilComplete();
    
        printf('Created Performances table in database %s on instance %s' . PHP_EOL,
            $databaseId, $instanceId);
    }

    Python

    def create_table_with_timestamp(instance_id, database_id):
        """Creates a table with a COMMIT_TIMESTAMP column."""
    
        spanner_client = spanner.Client()
        instance = spanner_client.instance(instance_id)
        database = instance.database(database_id)
    
        operation = database.update_ddl([
            """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"""
        ])
    
        print('Waiting for operation to complete...')
        operation.result()
    
        print('Created Performances table on database {} on instance {}'.format(
            database_id, instance_id))

    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.database instance_id, database_id
    
    job = client.update statements: [
      "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"
    ]
    
    puts "Waiting for update database operation to complete"
    
    job.wait_until_done!
    
    puts "Created table Performances in #{database_id}"

  • Use a string de marcador spanner.commit_timestamp() (ou a constante da biblioteca de cliente) em operações de inserção ou atualização da coluna em que você quer armazenar o carimbo de data/hora de commit da transação. Por exemplo, ao inserir uma nova linha de um desempenho, você especifica a string de marcador (ou constante) como o valor de LastUpdateTime:

    C#

    string connectionString =
    $"Data Source=projects/{projectId}/instances/{instanceId}"
    + $"/databases/{databaseId}";
    List<Performance> performances = new List<Performance> {
        new Performance {singerId = 1, venueId = 4, eventDate = DateTime.Parse("2017-10-05"),
            revenue = 11000},
        new Performance {singerId = 1, venueId = 19, eventDate = DateTime.Parse("2017-11-02"),
            revenue = 15000},
        new Performance {singerId = 2, venueId = 42, eventDate = DateTime.Parse("2017-12-23"),
            revenue = 7000},
    };
    // Create connection to Cloud Spanner.
    using (var connection = new SpannerConnection(connectionString))
    {
        await connection.OpenAsync();
        // Insert rows into the Performances table.
        var cmd = connection.CreateInsertCommand("Performances",
            new SpannerParameterCollection {
                {"SingerId", SpannerDbType.Int64},
                {"VenueId", SpannerDbType.Int64},
                {"EventDate", SpannerDbType.Date},
                {"Revenue", SpannerDbType.Int64},
                {"LastUpdateTime", SpannerDbType.Timestamp},
        });
        await Task.WhenAll(performances.Select(performance =>
        {
            cmd.Parameters["SingerId"].Value = performance.singerId;
            cmd.Parameters["VenueId"].Value = performance.venueId;
            cmd.Parameters["EventDate"].Value = performance.eventDate;
            cmd.Parameters["Revenue"].Value = performance.revenue;
            cmd.Parameters["LastUpdateTime"].Value = SpannerParameter.CommitTimestamp;
            return cmd.ExecuteNonQueryAsync();
        }));
        Console.WriteLine("Inserted data.");
    }

    Go

    func writeWithTimestamp(ctx context.Context, w io.Writer, client *spanner.Client) error {
    	performanceColumns := []string{"SingerId", "VenueId", "EventDate", "Revenue", "LastUpdateTime"}
    	m := []*spanner.Mutation{
    		spanner.InsertOrUpdate("Performances", performanceColumns, []interface{}{1, 4, "2017-10-05", 11000, spanner.CommitTimestamp}),
    		spanner.InsertOrUpdate("Performances", performanceColumns, []interface{}{1, 19, "2017-11-02", 15000, spanner.CommitTimestamp}),
    		spanner.InsertOrUpdate("Performances", performanceColumns, []interface{}{2, 42, "2017-12-23", 7000, spanner.CommitTimestamp}),
    	}
    	_, err := client.Apply(ctx, m)
    	return err
    }
    

    Java

    static final List<Performance> PERFORMANCES =
        Arrays.asList(
            new Performance(1, 4, "2017-10-05", 11000),
            new Performance(1, 19, "2017-11-02", 15000),
            new Performance(2, 42, "2017-12-23", 7000));
    static void writeExampleDataWithTimestamp(DatabaseClient dbClient) {
      List<Mutation> mutations = new ArrayList<>();
      for (Performance performance : PERFORMANCES) {
        mutations.add(
            Mutation.newInsertBuilder("Performances")
                .set("SingerId")
                .to(performance.singerId)
                .set("VenueId")
                .to(performance.venueId)
                .set("EventDate")
                .to(performance.eventDate)
                .set("Revenue")
                .to(performance.revenue)
                .set("LastUpdateTime")
                .to(Value.COMMIT_TIMESTAMP)
                .build());
      }
      dbClient.write(mutations);
    }

    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);
    
      // Instantiate Spanner table objects
      const performancesTable = database.table('Performances');
    
      const data = [
        {
          SingerId: '1',
          VenueId: '4',
          EventDate: '2017-10-05',
          Revenue: '11000',
          LastUpdateTime: 'spanner.commit_timestamp()',
        },
        {
          SingerId: '1',
          VenueId: '19',
          EventDate: '2017-11-02',
          Revenue: '15000',
          LastUpdateTime: 'spanner.commit_timestamp()',
        },
        {
          SingerId: '2',
          VenueId: '42',
          EventDate: '2017-12-23',
          Revenue: '7000',
          LastUpdateTime: 'spanner.commit_timestamp()',
        },
      ];
    
      // Inserts rows into the Singers table
      // Note: Cloud Spanner interprets Node.js numbers as FLOAT64s, so
      // they must be converted to strings before being inserted as INT64s
      try {
        await performancesTable.insert(data);
        console.log('Inserted data.');
      } catch (err) {
        console.error('ERROR:', err);
      } finally {
        // Close the database when finished
        database.close();
      }
    

    PHP

    use Google\Cloud\Spanner\SpannerClient;
    
    /**
     * Inserts sample data into a table with a commit timestamp column.
     *
     * The database and table must already exist and can be created using
     * `create_table_with_timestamp_column`.
     * Example:
     * ```
     * insert_data_with_timestamp_column($instanceId, $databaseId);
     * ```
     *
     * @param string $instanceId The Spanner instance ID.
     * @param string $databaseId The Spanner database ID.
     */
    function insert_data_with_timestamp_column($instanceId, $databaseId)
    {
        $spanner = new SpannerClient();
        $instance = $spanner->instance($instanceId);
        $database = $instance->database($databaseId);
    
        $operation = $database->transaction(['singleUse' => true])
            ->insertBatch('Performances', [
                ['SingerId' => 1, 'VenueId' => 4, 'EventDate' => '2017-10-05', 'Revenue' => 11000, 'LastUpdateTime' => $spanner->commitTimestamp()],
                ['SingerId' => 1, 'VenueId' => 19, 'EventDate' => '2017-11-02', 'Revenue' => 15000, 'LastUpdateTime' => $spanner->commitTimestamp()],
                ['SingerId' => 2, 'VenueId' => 42, 'EventDate' => '2017-12-23', 'Revenue' => 7000, 'LastUpdateTime' => $spanner->commitTimestamp()],
            ])
            ->commit();
    
        print('Inserted data.' . PHP_EOL);
    }

    Python

    def insert_data_with_timestamp(instance_id, database_id):
        """Inserts data with a COMMIT_TIMESTAMP field into a table. """
    
        spanner_client = spanner.Client()
        instance = spanner_client.instance(instance_id)
    
        database = instance.database(database_id)
    
        with database.batch() as batch:
            batch.insert(
                table='Performances',
                columns=(
                    'SingerId', 'VenueId', 'EventDate',
                    'Revenue', 'LastUpdateTime',),
                values=[
                    (1, 4, "2017-10-05", 11000, spanner.COMMIT_TIMESTAMP),
                    (1, 19, "2017-11-02", 15000, spanner.COMMIT_TIMESTAMP),
                    (2, 42, "2017-12-23", 7000, spanner.COMMIT_TIMESTAMP)])
    
        print('Inserted data.')

    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
    
    # Get commit_timestamp
    commit_timestamp = client.commit_timestamp
    
    client.commit do |c|
      c.insert "Performances", [
        { SingerId: 1, VenueId:  4, EventDate: "2017-10-05", Revenue: 11000, LastUpdateTime: commit_timestamp },
        { SingerId: 1, VenueId: 19, EventDate: "2017-11-02", Revenue: 15000, LastUpdateTime: commit_timestamp },
        { SingerId: 2, VenueId: 42, EventDate: "2017-12-23", Revenue: 7000 , LastUpdateTime: commit_timestamp }
      ]
    end
    
    puts "Inserted data"

Quando o Cloud Spanner confirma a transação, o carimbo de data/hora de commit é gravado na coluna LastUpdateTime. Em seguida, você pode usar LastUpdateTime para criar um histórico de atualizações na tabela Performances.

Os valores de carimbo de data/hora de commit não têm garantia de exclusividade. As transações gravadas em conjuntos de campos não sobrepostos podem ter o mesmo carimbo de data/hora. As transações gravadas em conjuntos de campos sobrepostos têm carimbos de data/hora exclusivos.

Os carimbos de data/hora de commit do Cloud Spanner têm granularidade de microssegundos e são convertidos em nanossegundos quando armazenados em colunas TIMESTAMP.

Como criar e excluir uma coluna de carimbo de data/hora de commit

Use a opção allow_commit_timestamp para adicionar e remover suporte para carimbos de data/hora de commit:

  • Para criar uma nova coluna, em uma tabela nova ou existente, que aceita carimbos de data/hora de commit.
  • Para alterar uma coluna de carimbo de data/hora existente para aceitar carimbos de data/hora de commit.
  • Para remover suporte do carimbo de data/hora de commit de uma coluna.

Chaves e índices

Use uma coluna de carimbo de data/hora de commit como uma coluna de chave principal ou como uma coluna sem chave. As chaves principais podem ser definidas como ASC ou DESC.

  • ASC (padrão): as chaves crescentes são ideais para responder a consultas de uma hora específica.
  • DESC: as chaves decrescentes mantêm as linhas mais recentes no topo da tabela. Elas dão acesso rápido aos registros mais recentes.

A opção allow_commit_timestamp precisa ser consistente entre as chaves principais das tabelas pai e filho. Se a opção não for consistente entre as chaves principais, o Cloud Spanner retornará um erro. A única ocasião em que a opção pode ser inconsistente é quando você está criando ou atualizando o esquema.

Não use uma coluna de carimbo de data/hora de commit como a primeira parte da chave principal de uma tabela ou a primeira parte da chave principal de um índice secundário. Usar uma coluna de carimbo de data/hora de commit como a primeira parte de uma chave principal cria pontos de acesso e reduz o desempenho dos dados, mas podem ocorrer problemas de desempenho mesmo com taxas de gravação baixas. Não há sobrecarga de desempenho para ativar os carimbos de data/hora de commit em colunas que não sejam de chave não indexadas.

Como criar uma coluna de carimbo de data/hora de commit

O exemplo a seguir cria uma tabela com uma coluna que aceita carimbos de data/hora de commit.

C#

// Initialize request connection string for database creation.
string connectionString =
    $"Data Source=projects/{projectId}/instances/{instanceId}"
    + $"/databases/{databaseId}";
using (var connection = new SpannerConnection(connectionString))
{
    // Define create table statement for table with
    // commit timestamp column.
    string createTableStatement =
    @"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";
    // Make the request.
    var cmd = connection.CreateDdlCommand(createTableStatement);
    await cmd.ExecuteNonQueryAsync();
}

Go

func createTableWithTimestamp(ctx context.Context, w io.Writer, adminClient *database.DatabaseAdminClient, database string) error {
	op, err := adminClient.UpdateDatabaseDdl(ctx, &adminpb.UpdateDatabaseDdlRequest{
		Database: database,
		Statements: []string{
			`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`,
		},
	})
	if err != nil {
		return err
	}
	if err := op.Wait(ctx); err != nil {
		return err
	}
	fmt.Fprintf(w, "Created Performances table in database [%s]\n", database)
	return nil
}

Java

static void createTableWithTimestamp(DatabaseAdminClient dbAdminClient, DatabaseId id) {
  OperationFuture<Void, UpdateDatabaseDdlMetadata> op =
      dbAdminClient.updateDatabaseDdl(
          id.getInstanceId().getInstance(),
          id.getDatabase(),
          Arrays.asList(
              "CREATE TABLE Performances (\n"
                  + "  SingerId     INT64 NOT NULL,\n"
                  + "  VenueId      INT64 NOT NULL,\n"
                  + "  EventDate    Date,\n"
                  + "  Revenue      INT64, \n"
                  + "  LastUpdateTime TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true)\n"
                  + ") PRIMARY KEY (SingerId, VenueId, EventDate),\n"
                  + "  INTERLEAVE IN PARENT Singers ON DELETE CASCADE"),
          null);
  try {
    // Initiate the request which returns an OperationFuture.
    op.get();
    System.out.println("Created Performances table in database: [" + id + "]");
  } catch (ExecutionException e) {
    // If the operation failed during execution, expose the cause.
    throw (SpannerException) e.getCause();
  } catch (InterruptedException e) {
    // Throw when a thread is waiting, sleeping, or otherwise occupied,
    // and the thread is interrupted, either before or during the activity.
    throw SpannerExceptionFactory.propagateInterrupt(e);
  }
}

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
  const instance = spanner.instance(instanceId);
  const database = instance.database(databaseId);

  // Note: Cloud Spanner interprets Node.js numbers as FLOAT64s, so they
  // must be converted to strings before being inserted as INT64s
  const request = [
    `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`,
  ];

  // Creates a table in an existing database
  const [operation] = await database.updateSchema(request);

  console.log(`Waiting for operation on ${databaseId} to complete...`);

  await operation.promise();

  console.log(`Created table Performances in database ${databaseId}.`);

PHP

use Google\Cloud\Spanner\SpannerClient;

/**
 * Creates a table with a commit timestamp column.
 * Example:
 * ```
 * create_table_with_timestamp_column($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function create_table_with_timestamp_column($instanceId, $databaseId)
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $operation = $database->updateDdl(
        "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"
    );

    print('Waiting for operation to complete...' . PHP_EOL);
    $operation->pollUntilComplete();

    printf('Created Performances table in database %s on instance %s' . PHP_EOL,
        $databaseId, $instanceId);
}

Python

def create_table_with_timestamp(instance_id, database_id):
    """Creates a table with a COMMIT_TIMESTAMP column."""

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

    operation = database.update_ddl([
        """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"""
    ])

    print('Waiting for operation to complete...')
    operation.result()

    print('Created Performances table on database {} on instance {}'.format(
        database_id, instance_id))

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.database instance_id, database_id

job = client.update statements: [
  "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"
]

puts "Waiting for update database operation to complete"

job.wait_until_done!

puts "Created table Performances in #{database_id}"

Adicionar a opção altera a coluna do carimbo de data/hora assim:

A opção allow_commit_timestamp diferencia maiúsculas de minúsculas.

Como adicionar uma coluna de carimbo de data/hora de commit a uma tabela existente

Para adicionar uma coluna de carimbo de data/hora de commit a uma tabela existente, use a instrução ALTER TABLE:

ALTER TABLE table_name ADD COLUMN column_name TIMESTAMP
    NOT NULL OPTIONS (allow_commit_timestamp=true)

Como converter uma coluna de carimbo de data/hora em uma coluna de carimbo de data/hora de commit

É possível converter uma coluna de carimbo de data/hora existente em uma coluna de carimbo de data/hora de commit, mas isso exige que o Cloud Spanner valide os valores do carimbo de data/hora existentes no passado. Por exemplo:

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

Não é possível alterar o tipo de dados ou a anotação NULL de uma coluna em uma instrução ALTER TABLE que inclua SET OPTIONS. Para detalhes, consulte Linguagem de definição de dados.

Como remover a opção de carimbo de data/hora de commit

Para remover o suporte do carimbo de data/hora de commit de uma coluna, use a opção allow_commit_timestamp=null em uma instrução ALTER TABLE. O comportamento do carimbo de data/hora de commit é removido, mas a coluna continua sendo um carimbo de data/hora. Alterar a opção não muda nenhuma outra característica da coluna, como tipo ou nulidade (NOT NULL). Por exemplo:

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

Como inserir uma linha

Ao inserir uma linha, o Cloud Spanner só gravará o valor do carimbo de data/hora de commit se você incluir a coluna na lista de colunas e passar a string de marcador spanner.commit_timestamp() (ou constante da biblioteca de cliente) como o valor dele. Por exemplo:

C#

string connectionString =
$"Data Source=projects/{projectId}/instances/{instanceId}"
+ $"/databases/{databaseId}";
List<Performance> performances = new List<Performance> {
    new Performance {singerId = 1, venueId = 4, eventDate = DateTime.Parse("2017-10-05"),
        revenue = 11000},
    new Performance {singerId = 1, venueId = 19, eventDate = DateTime.Parse("2017-11-02"),
        revenue = 15000},
    new Performance {singerId = 2, venueId = 42, eventDate = DateTime.Parse("2017-12-23"),
        revenue = 7000},
};
// Create connection to Cloud Spanner.
using (var connection = new SpannerConnection(connectionString))
{
    await connection.OpenAsync();
    // Insert rows into the Performances table.
    var cmd = connection.CreateInsertCommand("Performances",
        new SpannerParameterCollection {
            {"SingerId", SpannerDbType.Int64},
            {"VenueId", SpannerDbType.Int64},
            {"EventDate", SpannerDbType.Date},
            {"Revenue", SpannerDbType.Int64},
            {"LastUpdateTime", SpannerDbType.Timestamp},
    });
    await Task.WhenAll(performances.Select(performance =>
    {
        cmd.Parameters["SingerId"].Value = performance.singerId;
        cmd.Parameters["VenueId"].Value = performance.venueId;
        cmd.Parameters["EventDate"].Value = performance.eventDate;
        cmd.Parameters["Revenue"].Value = performance.revenue;
        cmd.Parameters["LastUpdateTime"].Value = SpannerParameter.CommitTimestamp;
        return cmd.ExecuteNonQueryAsync();
    }));
    Console.WriteLine("Inserted data.");
}

Go

func writeWithTimestamp(ctx context.Context, w io.Writer, client *spanner.Client) error {
	performanceColumns := []string{"SingerId", "VenueId", "EventDate", "Revenue", "LastUpdateTime"}
	m := []*spanner.Mutation{
		spanner.InsertOrUpdate("Performances", performanceColumns, []interface{}{1, 4, "2017-10-05", 11000, spanner.CommitTimestamp}),
		spanner.InsertOrUpdate("Performances", performanceColumns, []interface{}{1, 19, "2017-11-02", 15000, spanner.CommitTimestamp}),
		spanner.InsertOrUpdate("Performances", performanceColumns, []interface{}{2, 42, "2017-12-23", 7000, spanner.CommitTimestamp}),
	}
	_, err := client.Apply(ctx, m)
	return err
}

Java

static final List<Performance> PERFORMANCES =
    Arrays.asList(
        new Performance(1, 4, "2017-10-05", 11000),
        new Performance(1, 19, "2017-11-02", 15000),
        new Performance(2, 42, "2017-12-23", 7000));
static void writeExampleDataWithTimestamp(DatabaseClient dbClient) {
  List<Mutation> mutations = new ArrayList<>();
  for (Performance performance : PERFORMANCES) {
    mutations.add(
        Mutation.newInsertBuilder("Performances")
            .set("SingerId")
            .to(performance.singerId)
            .set("VenueId")
            .to(performance.venueId)
            .set("EventDate")
            .to(performance.eventDate)
            .set("Revenue")
            .to(performance.revenue)
            .set("LastUpdateTime")
            .to(Value.COMMIT_TIMESTAMP)
            .build());
  }
  dbClient.write(mutations);
}

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

  // Instantiate Spanner table objects
  const performancesTable = database.table('Performances');

  const data = [
    {
      SingerId: '1',
      VenueId: '4',
      EventDate: '2017-10-05',
      Revenue: '11000',
      LastUpdateTime: 'spanner.commit_timestamp()',
    },
    {
      SingerId: '1',
      VenueId: '19',
      EventDate: '2017-11-02',
      Revenue: '15000',
      LastUpdateTime: 'spanner.commit_timestamp()',
    },
    {
      SingerId: '2',
      VenueId: '42',
      EventDate: '2017-12-23',
      Revenue: '7000',
      LastUpdateTime: 'spanner.commit_timestamp()',
    },
  ];

  // Inserts rows into the Singers table
  // Note: Cloud Spanner interprets Node.js numbers as FLOAT64s, so
  // they must be converted to strings before being inserted as INT64s
  try {
    await performancesTable.insert(data);
    console.log('Inserted data.');
  } catch (err) {
    console.error('ERROR:', err);
  } finally {
    // Close the database when finished
    database.close();
  }

PHP

use Google\Cloud\Spanner\SpannerClient;

/**
 * Inserts sample data into a table with a commit timestamp column.
 *
 * The database and table must already exist and can be created using
 * `create_table_with_timestamp_column`.
 * Example:
 * ```
 * insert_data_with_timestamp_column($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function insert_data_with_timestamp_column($instanceId, $databaseId)
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $operation = $database->transaction(['singleUse' => true])
        ->insertBatch('Performances', [
            ['SingerId' => 1, 'VenueId' => 4, 'EventDate' => '2017-10-05', 'Revenue' => 11000, 'LastUpdateTime' => $spanner->commitTimestamp()],
            ['SingerId' => 1, 'VenueId' => 19, 'EventDate' => '2017-11-02', 'Revenue' => 15000, 'LastUpdateTime' => $spanner->commitTimestamp()],
            ['SingerId' => 2, 'VenueId' => 42, 'EventDate' => '2017-12-23', 'Revenue' => 7000, 'LastUpdateTime' => $spanner->commitTimestamp()],
        ])
        ->commit();

    print('Inserted data.' . PHP_EOL);
}

Python

def insert_data_with_timestamp(instance_id, database_id):
    """Inserts data with a COMMIT_TIMESTAMP field into a table. """

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

    database = instance.database(database_id)

    with database.batch() as batch:
        batch.insert(
            table='Performances',
            columns=(
                'SingerId', 'VenueId', 'EventDate',
                'Revenue', 'LastUpdateTime',),
            values=[
                (1, 4, "2017-10-05", 11000, spanner.COMMIT_TIMESTAMP),
                (1, 19, "2017-11-02", 15000, spanner.COMMIT_TIMESTAMP),
                (2, 42, "2017-12-23", 7000, spanner.COMMIT_TIMESTAMP)])

    print('Inserted data.')

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

# Get commit_timestamp
commit_timestamp = client.commit_timestamp

client.commit do |c|
  c.insert "Performances", [
    { SingerId: 1, VenueId:  4, EventDate: "2017-10-05", Revenue: 11000, LastUpdateTime: commit_timestamp },
    { SingerId: 1, VenueId: 19, EventDate: "2017-11-02", Revenue: 15000, LastUpdateTime: commit_timestamp },
    { SingerId: 2, VenueId: 42, EventDate: "2017-12-23", Revenue: 7000 , LastUpdateTime: commit_timestamp }
  ]
end

puts "Inserted data"

Os carimbos de data/hora de commit só podem ser gravados em colunas anotadas com a opção allow_commit_timestamp=true.

Se tiver mutações em linhas de várias tabelas, você precisará especificar spanner.commit_timestamp() (ou constante da biblioteca de cliente) para a coluna do carimbo de data/hora de commit em cada tabela.

Como atualizar uma linha

Ao atualizar uma linha, o Cloud Spanner só gravará o valor do carimbo de data/hora de commit se você incluir a coluna na lista de colunas e passar a string de marcador spanner.commit_timestamp() (ou constante da biblioteca de cliente) como o valor dele. Não atualize a chave principal de uma linha. Para atualizar a chave principal, exclua a linha existente e crie uma nova linha.

Por exemplo, para atualizar uma coluna do carimbo de data/hora de commit chamada LastUpdateTime:

C#

string connectionString =
$"Data Source=projects/{projectId}/instances/{instanceId}"
+ $"/databases/{databaseId}";
// Create connection to Cloud Spanner.
using (var connection = new SpannerConnection(connectionString))
{
    var cmd = connection.CreateUpdateCommand("Albums",
        new SpannerParameterCollection {
            {"SingerId", SpannerDbType.Int64},
            {"AlbumId", SpannerDbType.Int64},
            {"MarketingBudget", SpannerDbType.Int64},
            {"LastUpdateTime", SpannerDbType.Timestamp},
        });
    var cmdLookup =
        connection.CreateSelectCommand("SELECT * FROM Albums");
    using (var reader = await cmdLookup.ExecuteReaderAsync())
    {
        while (await reader.ReadAsync())
        {
            if (reader.GetFieldValue<int>("SingerId") == 1
                && reader.GetFieldValue<int>("AlbumId") == 1)
            {
                cmd.Parameters["SingerId"].Value =
                    reader.GetFieldValue<int>("SingerId");
                cmd.Parameters["AlbumId"].Value =
                    reader.GetFieldValue<int>("AlbumId");
                cmd.Parameters["MarketingBudget"].Value = 1000000;
                cmd.Parameters["LastUpdateTime"].Value =
                    SpannerParameter.CommitTimestamp;
                await cmd.ExecuteNonQueryAsync();
            }
            if (reader.GetInt64(0) == 2 && reader.GetInt64(1) == 2)
            {
                cmd.Parameters["SingerId"].Value =
                    reader.GetFieldValue<int>("SingerId");
                cmd.Parameters["AlbumId"].Value =
                    reader.GetFieldValue<int>("AlbumId");
                cmd.Parameters["MarketingBudget"].Value = 750000;
                cmd.Parameters["LastUpdateTime"].Value =
                    SpannerParameter.CommitTimestamp;
                await cmd.ExecuteNonQueryAsync();
            }
        }
    }
}
Console.WriteLine("Updated data.");

Go

func updateWithTimestamp(ctx context.Context, w io.Writer, client *spanner.Client) error {
	cols := []string{"SingerId", "AlbumId", "MarketingBudget", "LastUpdateTime"}
	_, err := client.Apply(ctx, []*spanner.Mutation{
		spanner.Update("Albums", cols, []interface{}{1, 1, 1000000, spanner.CommitTimestamp}),
		spanner.Update("Albums", cols, []interface{}{2, 2, 750000, spanner.CommitTimestamp}),
	})
	return err
}

Java

static void updateWithTimestamp(DatabaseClient dbClient) {
  // Mutation can be used to update/insert/delete a single row in a table. Here we use
  // newUpdateBuilder to create update mutations.
  List<Mutation> mutations =
      Arrays.asList(
          Mutation.newUpdateBuilder("Albums")
              .set("SingerId")
              .to(1)
              .set("AlbumId")
              .to(1)
              .set("MarketingBudget")
              .to(1000000)
              .set("LastUpdateTime")
              .to(Value.COMMIT_TIMESTAMP)
              .build(),
          Mutation.newUpdateBuilder("Albums")
              .set("SingerId")
              .to(2)
              .set("AlbumId")
              .to(2)
              .set("MarketingBudget")
              .to(750000)
              .set("LastUpdateTime")
              .to(Value.COMMIT_TIMESTAMP)
              .build());
  // This writes all the mutations to Cloud Spanner atomically.
  dbClient.write(mutations);
}

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

  // Update a row in the Albums table
  // Note: Cloud Spanner interprets Node.js numbers as FLOAT64s, so they
  // must be converted to strings before being inserted as INT64s
  const albumsTable = database.table('Albums');

  const data = [
    {
      SingerId: '1',
      AlbumId: '1',
      MarketingBudget: '1000000',
      LastUpdateTime: 'spanner.commit_timestamp()',
    },
    {
      SingerId: '2',
      AlbumId: '2',
      MarketingBudget: '750000',
      LastUpdateTime: 'spanner.commit_timestamp()',
    },
  ];

  try {
    await albumsTable.update(data);
    console.log('Updated data.');
  } catch (err) {
    console.error('ERROR:', err);
  } finally {
    // Close the database when finished
    database.close();
  }

PHP

use Google\Cloud\Spanner\SpannerClient;

/**
 * Updates sample data in a table with a commit timestamp column.
 *
 * Before executing this method, a new column MarketingBudget has to be added to the Albums
 * table by applying the DDL statement "ALTER TABLE Albums ADD COLUMN MarketingBudget INT64".
 *
 * In addition this update expects the LastUpdateTime column added by applying the DDL statement
 * "ALTER TABLE Albums ADD COLUMN LastUpdateTime TIMESTAMP OPTIONS (allow_commit_timestamp=true)"
 *
 * Example:
 * ```
 * update_data_with_timestamp_column($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function update_data_with_timestamp_column($instanceId, $databaseId)
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $operation = $database->transaction(['singleUse' => true])
        ->updateBatch('Albums', [
            ['SingerId' => 1, 'AlbumId' => 1, 'MarketingBudget' => 1000000, 'LastUpdateTime' => $spanner->commitTimestamp()],
            ['SingerId' => 2, 'AlbumId' => 2, 'MarketingBudget' => 750000, 'LastUpdateTime' => $spanner->commitTimestamp()],
        ])
        ->commit();

    print('Updated data.' . PHP_EOL);
}

Python

def update_data_with_timestamp(instance_id, database_id):
    """Updates Performances tables in the database with the COMMIT_TIMESTAMP
    column.

    This updates the `MarketingBudget` column which must be created before
    running this sample. 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

    In addition this update expects the LastUpdateTime column added by
    applying this DDL statement against your database:

        ALTER TABLE Albums 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.batch() as batch:
        batch.update(
            table='Albums',
            columns=(
                'SingerId', 'AlbumId', 'MarketingBudget', 'LastUpdateTime'),
            values=[
                (1, 1, 1000000, spanner.COMMIT_TIMESTAMP),
                (2, 2, 750000, spanner.COMMIT_TIMESTAMP)])

    print('Updated data.')

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"

Os carimbos de data/hora de commit só podem ser gravados em colunas anotadas com a opção allow_commit_timestamp=true.

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

Como gravar um carimbo de data/hora de confirmação usando uma declaração DML

Use a função PENDING_COMMIT_TIMESTAMP para gravar o carimbo de data/hora de confirmação em uma declaração DML. O Cloud Spanner seleciona o carimbo de data/hora de commit quando a transação é confirmada.

A declaração DML a seguir atualiza a coluna LastUpdated na tabela Singers com o carimbo de data/hora de confirmação:

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

Como consultar uma coluna do carimbo de data/hora de confirmação

O exemplo a seguir consulta a coluna do carimbo de data/hora de commit da tabela.

C#

string connectionString =
$"Data Source=projects/{projectId}/instances/{instanceId}"
+ $"/databases/{databaseId}";
// Create connection to Cloud Spanner.
using (var connection = new SpannerConnection(connectionString))
{
    var cmd =
        connection.CreateSelectCommand(
            "SELECT SingerId, AlbumId, "
            + "MarketingBudget, LastUpdateTime FROM Albums");
    using (var reader = await cmd.ExecuteReaderAsync())
    {
        while (await reader.ReadAsync())
        {
            string budget = string.Empty;
            if (reader["MarketingBudget"] != DBNull.Value)
            {
                budget = reader.GetFieldValue<string>("MarketingBudget");
            }
            string timestamp = string.Empty;
            if (reader["LastUpdateTime"] != DBNull.Value)
            {
                timestamp = reader.GetFieldValue<string>("LastUpdateTime");
            }
            Console.WriteLine("SingerId : "
            + reader.GetFieldValue<string>("SingerId")
            + " AlbumId : "
            + reader.GetFieldValue<string>("AlbumId")
            + $" MarketingBudget : {budget}"
            + $" LastUpdateTime : {timestamp}");
        }
    }
}

Go

func queryWithTimestamp(ctx context.Context, w io.Writer, client *spanner.Client) error {
	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.
  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($instanceId, $databaseId)
{
    $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(u'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

Como fornecer o próprio valor para a coluna do carimbo de data/hora de commit

Forneça o próprio valor para a coluna do carimbo de data/hora de commit, em vez de passar spanner.commit_timestamp() (ou constante da biblioteca de cliente) como o valor da coluna. O valor precisa ser um carimbo de data/hora no passado. Essa restrição garante que gravar carimbos de data/hora seja uma operação rápida e barata. Uma maneira fácil de confirmar se um valor está no passado é compará-lo com o valor retornado pela função SQL CURRENT_TIMESTAMP. O servidor retornará um erro FailedPrecondition se um carimbo de data/hora futuro for especificado.

Como criar um registro de alterações

Suponhamos que você queira criar um registro de alterações de cada mutação feita em uma tabela e use esse registro na auditoria. Um exemplo seria uma tabela que armazenasse o histórico de alterações em documentos de processamento de texto. O carimbo de data/hora de commit facilita a criação do registro de alterações, porque os carimbos de data/hora podem impor a ordem das entradas do registro de alterações. Crie um registro de alterações que armazene o histórico de alterações em um determinado documento usando um esquema como o seguinte exemplo:

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 registro de alterações, insira uma nova linha em DocumentHistory na mesma transação em que você insere ou atualiza uma linha em Document. Na inserção da nova linha em DocumentHistory, use o marcador spanner.commit_timestamp() (ou constante da biblioteca de cliente) para informar ao Cloud Spanner gravar o carimbo de data/hora de commit na coluna Ts. Intercalar a tabela DocumentsHistory com a tabela Documents permitirá conhecer a localidade dos dados, além de inserções e atualizações mais eficientes. No entanto, ela também adiciona a restrição de que as linhas pai e filho precisam ser excluídas juntas. Para manter as linhas em DocumentHistory depois que as linhas em Documents forem excluídas, não intercale as tabelas.

O tamanho de uma linha deve ser menor que 4 GB para garantir o melhor desempenho. Observe que o tamanho de uma linha inclui a linha de nível superior e todas as suas linhas filho e linhas de índice intercaladas. Neste exemplo, há um limite para o número máximo de linhas no DocumentHistory para um documento específico, devido ao limite para o tamanho das linhas. Se você prevê que o registro de alterações no DocumentHistory será grande, é possível projetar o aplicativo para excluir as linhas mais antigas no DocumentHistory. Como alternativa, é possível projetar o esquema para que DocumentHistory seja uma tabela de nível superior em vez de uma tabela intercalada.

A seguir

Use os carimbos de data/hora de confirmação para criar um registro de alterações com o Go.

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

Enviar comentários sobre…

Documentação do Cloud Spanner