提交时间戳

本主题介绍了如何为使用 Cloud Spanner 执行的每个插入和更新操作编写提交时间戳。要使用此功能,请在 TIMESTAMP 列设置 allow_commit_timestamp 选项,然后写入时间戳,将其作为每个事务的一部分。

概览

基于 TrueTime 技术的提交时间戳是在数据库中提交事务的时间。allow_commit_timestamp 列选项允许您以原子方式将提交时间戳存储到列中。借助存储在表中的提交时间戳,您可以确定变更的确切顺序并构建更改日志等功能。

要在数据库中插入提交时间戳,请完成以下步骤:

  1. 创建一个带有类型 TIMESTAMP 的列,并在架构定义中将列选项 allow_commit_timestamp 设置为 true。例如:

    CREATE TABLE Performances (
        ...
        LastUpdateTime  TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true)
        ...
    ) PRIMARY KEY (...);
    
  2. 如果要使用 DML 执行插入或更新操作,请使用 PENDING_COMMIT_TIMESTAMP 函数来写入提交时间戳。

    如果要使用变更执行插入或更新操作,请使用占位符字符串 spanner.commit_timestamp()(或客户端库常量)插入或更新提交时间戳列。

当 Cloud Spanner 使用这些占位符作为列值提交事务时,实际提交时间戳将写入指定的列(例如:LastUpdateTime 列)。然后,您可以使用此列值来创建表的更新历史记录。

提交时间戳的值不保证是唯一的。写入不重叠字段集的事务可能具有相同的时间戳。写入重叠字段集的事务具有唯一的时间戳。

Cloud Spanner 提交时间戳以微秒为单位,当存储在 TIMESTAMP 列中时会转换为纳秒。

创建和删除提交时间戳列

使用 allow_commit_timestamp 列选项来添加和移除对提交时间戳的支持:

  • 创建新表以指定列支持提交时间戳,请执行以下操作。
  • 更改现有表时,请执行以下操作:
    • 添加支持提交时间戳的新列,
    • 更改现有 TIMESTAMP 列以支持提交时间戳,
    • 更改现有 TIMESTAMP 列以移除提交时间戳支持,

键和索引

您可以使用提交时间戳列作为主键列或非键列。主键可以定义为 ASCDESC

  • ASC(默认)- 升序键适用于解答从特定时间往前的查询。
  • DESC - 降序键将最新的行保留在表的顶部,可提供对最近记录的快速访问。

allow_commit_timestamp 选项在父表和子表的主键之间必须保持一致。如果该选项在主键之间不一致,Cloud Spanner 将返回错误。只有在创建或更新架构的时候,该选项可以不一致。

在以下情况下使用提交时间戳会引发热点,这会降低数据的性能:

  • 提交时间戳列是表的主键的第一部分:

    CREATE TABLE Users (
      LastAccess TIMESTAMP NOT NULL,
      UserId     INT64 NOT NULL,
      ...
    ) PRIMARY KEY (LastAccess, UserId);
    
  • 提交时间戳列是二级索引的主键的第一部分:

    CREATE INDEX UsersByLastAccess ON Users(LastAccess)
    

    CREATE INDEX UsersByLastAccessAndName ON Users(LastAccess, FirstName)
    

出现热点后,即使写入速率较低,也会降低数据的性能。如果在没有索引的非键列上启用提交时间戳,则不会产生任何性能开销。

创建一个提交时间戳列

以下 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)

添加选项会更改时间戳列,如下所示:

  • 可以使用 spanner.commit_timestamp() 占位符字符串(或由客户端库提供的常量)进行插入和更新。
  • 该列只能包含过去的值。如需了解详情,请参阅为时间戳提供自己的值

选项 allow_commit_timestamp 区分大小写。

将提交时间戳列添加到现有表中

要将提交时间戳列添加到现有表中,请使用 ALTER TABLE 语句。例如,要将 LastUpdateTime 列添加到 Performances 表中,请使用以下语句:

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

将时间戳列转换为提交时间戳列

您可以将现有时间戳列转换为提交时间戳列,但这样做需要 Cloud Spanner 验证现有时间戳的值是否为过去的时间。例如:

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

您不能更改包含 SET OPTIONSALTER TABLE 语句中某一列的数据类型或 NULL 注释。如需了解详情,请参阅数据定义语言

移除提交时间戳选项

如果您想从列中删除提交时间戳支持,请在 ALTER TABLE 语句中使用 allow_commit_timestamp=null 选项。提交时间戳操作被移除后,该列仍然是时间戳。更改选项不会更改该列的任何其他特性,例如类型或可为空性 (NOT NULL)。例如:

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

使用 DML 语句写入提交时间戳

使用 PENDING_COMMIT_TIMESTAMP 函数在 DML 语句中写入提交时间戳。Cloud Spanner 会在事务提交时选择提交时间戳。

以下 DML 语句使用提交时间戳更新 Singers 表中的 LastUpdated 列:

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

使用变更插入行

插入行时,只有将列包含在列列表中并传递 spanner.commit_timestamp() 占位符字符串(或客户端库常量)作为其值时,Cloud Spanner 才会写入提交时间戳值。例如:

C++

void InsertDataWithTimestamp(google::cloud::spanner::Client client) {
  namespace spanner = ::google::cloud::spanner;
  auto commit_result = client.Commit(spanner::Mutations{
      spanner::InsertOrUpdateMutationBuilder(
          "Performances",
          {"SingerId", "VenueId", "EventDate", "Revenue", "LastUpdateTime"})
          .EmplaceRow(1, 4, absl::CivilDay(2017, 10, 5), 11000,
                      spanner::CommitTimestamp{})
          .EmplaceRow(1, 19, absl::CivilDay(2017, 11, 2), 15000,
                      spanner::CommitTimestamp{})
          .EmplaceRow(2, 42, absl::CivilDay(2017, 12, 23), 7000,
                      spanner::CommitTimestamp{})
          .Build()});
  if (!commit_result) {
    throw std::runtime_error(commit_result.status().message());
  }
  std::cout
      << "Update was successful [spanner_insert_data_with_timestamp_column]\n";
}

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


import (
	"context"

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

func writeWithTimestamp(db string) error {
	ctx := context.Background()

	client, err := spanner.NewClient(ctx, db)
	if err != nil {
		return err
	}
	defer client.Close()

	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: 11_000, LastUpdateTime: commit_timestamp },
    { SingerId: 1, VenueId: 19, EventDate: "2017-11-02", Revenue: 15_000, LastUpdateTime: commit_timestamp },
    { SingerId: 2, VenueId: 42, EventDate: "2017-12-23", Revenue: 7000, LastUpdateTime: commit_timestamp }
  ]
end

puts "Inserted data"

提交时间戳只能写入包含 allow_commit_timestamp=true 选项注释的列。

如果在多个表的行中存在变更,则必须为每个表的提交时间戳列指定 spanner.commit_timestamp()(或客户端库常量)。

使用变更更新行

更新行时,只有将列包含在列列表中并传递 spanner.commit_timestamp() 占位符字符串(或客户端库常量)作为其值时,Cloud Spanner 才会写入提交时间戳值。您无法更新某一行的主键。要更新主键,请删除现有行并创建一个新行。

例如,要更新名为 LastUpdateTime 的提交时间戳列,请执行以下操作:

C++

void UpdateDataWithTimestamp(google::cloud::spanner::Client client) {
  namespace spanner = ::google::cloud::spanner;
  auto commit_result = client.Commit(spanner::Mutations{
      spanner::UpdateMutationBuilder(
          "Albums",
          {"SingerId", "AlbumId", "MarketingBudget", "LastUpdateTime"})
          .EmplaceRow(1, 1, 1000000, spanner::CommitTimestamp{})
          .EmplaceRow(2, 2, 750000, spanner::CommitTimestamp{})
          .Build()});
  if (!commit_result) {
    throw std::runtime_error(commit_result.status().message());
  }
  std::cout
      << "Update was successful [spanner_update_data_with_timestamp_column]\n";
}

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


import (
	"context"
	"io"

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

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

	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"

提交时间戳只能写入包含 allow_commit_timestamp=true 选项注释的列。

如果在多个表的行中存在变更,则必须为每个表的提交时间戳列指定 spanner.commit_timestamp()(或客户端库常量)。

查询提交时间戳列

以下示例展示了如何查询表的提交时间戳列。

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(select);
  for (auto const& row : spanner::StreamOf<RowType>(rows)) {
    if (!row) throw std::runtime_error(row.status().message());
    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#

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


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($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

为提交时间戳列提供自己的值

您可以为提交时间戳列提供自己的值,而非传递 spanner.commit_timestamp()(或客户端库常量)作为列值。该值必须为过去的时间戳。此限制可确保写入时间戳的操作成本低且速度快。确认某个值为过去的时间戳的一种简单方法是将其与 CURRENT_TIMESTAMP SQL 函数的返回值进行比较。如果指定了未来的时间戳,服务器将返回 FailedPrecondition 错误。

创建更改日志

假设您希望创建一个关于发生在表中的每个变更的更改日志,然后使用该更改日志进行审核。例如一个用于存储字处理文档的更改记录的表。提交时间戳使得创建更改日志更加容易,因为时间戳可以强制对更改日志条目进行排序。您可以使用类似以下示例的架构构建一个更改日志,以便将更改记录存储到指定的文档中:

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;

要创建一个更新日志,请在为 Document 插入或更新某一行的相同事务中为 DocumentHistory 插入一个新行。在为 DocumentHistory 插入新行时,请使用占位符 spanner.commit_timestamp()(或客户端库常量),以告诉 Cloud Spanner 将提交时间戳写入列 Ts。将 DocumentsHistory 表与 Documents 表交错将实现数据局部性及更高效的插入和更新。但是,它也存在必须一起删除父行和子行的限制。若要在 DocumentHistory 中的行删除后保留 Documents 中的行,请不要交错表。

为获得最佳性能,行的大小应小于 4 GB(一个行的大小包含顶层行及其所有交错的子行和索引行)。在此示例中,由于行大小存在限制,特定文档的 DocumentHistory 中的行数也受到限制。如果您预计 DocumentHistory 的更改日志很大,则可以将应用设计为删除 DocumentHistory 中最早的行。或者,您也可以通过架构设计使得 DocumentHistory 成为顶层表而不是交错表。

后续步骤

使用提交时间戳来使用 Go 创建更改日志