commit タイムスタンプ

このトピックでは、Cloud Spanner で実行する挿入オペレーションと更新オペレーションごとに commit タイムスタンプを書き込む方法について説明します。この機能を使用するには、TIMESTAMP 列に allow_commit_timestamp オプションを設定し、各トランザクションの一部としてタイムスタンプを書き込みます。

概要

TrueTime テクノロジに基づく commit タイムスタンプは、トランザクションがデータベースに commit された時刻を表します。allow_commit_timestamp 列オプションを使用すると、commit タイムスタンプを列にアトミックに格納できます。テーブルに格納された commit タイムスタンプを使用すると、変異の正確な順序を確認し、変更履歴のような機能を構築できます。

データベースに commit タイムスタンプを挿入するには、2 つのことを行います。

  • TIMESTAMP 列を作成し、スキーマ定義で列オプション allow_commit_timestamptrue に設定します。たとえば、Performances テーブルの行が最後に更新された時刻を保存する場合は、LastUpdateTime という名前の 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}"

  • トランザクションの commit タイムスタンプを保存する列の挿入オペレーションや更新オペレーションで、プレースホルダ文字列 spanner.commit_timestamp()(またはクライアント ライブラリ定数)を使用します。たとえば、パフォーマンスを上げるために新しい行を挿入する場合は、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: 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"

Cloud Spanner がトランザクションを commit すると、commit タイムスタンプが LastUpdateTime 列に書き込まれます。ここで LastUpdateTime を使用すると、Performances テーブルの更新履歴を作成できます。

commit タイムスタンプの値は一意であるとは限りません。重複しない一連のフィールドに書き込むトランザクションの場合、同じタイムスタンプが存在する可能性があります。重複する一連のフィールドに書き込むトランザクションの場合は、タイムスタンプは一意になります。

Cloud Spanner の commit タイムスタンプはマイクロ秒単位の精度で、TIMESTAMP 列に保存されるときにナノ秒に変換されます。

commit タイムスタンプ列の作成と削除

commit タイムスタンプのサポートを追加または削除するには、allow_commit_timestamp オプションを使用します。これにより、次のことができます。

  • 新しい列を新規または既存のテーブルに commit タイムスタンプをサポートす新しい列を作成する。
  • 既存のタイムスタンプ列を変更して commit タイムスタンプをサポートする。
  • 列から commit タイムスタンプのサポートを削除する。

キーとインデックス

commit タイムスタンプ列は、主キー列または主キー以外の列として使用できます。主キーは ASC または DESC で定義します。

  • ASC(デフォルト)- キーを昇順にすると、特定の時間以降を照会する場合に便利です。
  • DESC - キーを降順にすると、最終行がテーブルの先頭になります。これにより、最新のレコードにすばやくアクセスできます。

親テーブルと子テーブルの主キーには、同じ allow_commit_timestamp オプションを設定する必要があります。主キーの間で異なるオプションを指定すると、Cloud Spanner がエラーを返します。スキーマを作成または更新している間は、このオプションが一致していない可能性があります。

テーブルの主キーの最初の部分またはセカンダリ インデックスの主キーの最初の部分に commit タイムスタンプ列を使用しないでください。commit タイムスタンプ列を主キーの最初の部分として使用すると、ホットスポットが作成され、データ処理のパフォーマンスが低下します。書き込み率が低い場合でもパフォーマンスに問題が生じる可能性があります。インデックスが作成されていないキー以外の列に commit タイムスタンプを有効にする場合は、パフォーマンスのオーバーヘッドは発生しません。

commit タイムスタンプ列を作成する

次の例では、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}"

オプションを追加すると、次のようにタイムスタンプ列が変化します。

  • 挿入と更新で spanner.commit_timestamp() プレースホルダ文字列(またはクライアント ライブラリの定数)を使用できます。
  • 列には過去の値しか保存できません。詳細については、タイムスタンプに独自の値を指定するをご覧ください。

allow_commit_timestamp オプションでは大文字と小文字が区別されます。

既存のテーブルに commit タイムスタンプ列を追加する

commit タイムスタンプ列を既存のテーブル追加するには、ALTER TABLE 文を使用します。

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

タイムスタンプ列を commit タイムスタンプ列に変換する

既存のタイムスタンプ列を commit タイムスタンプ列に変換できます。ただし、変換を行う前に、Cloud Spanner で既存のタイムスタンプ値が過去の値であることを検証する必要があります。次に例を示します。

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

SET OPTIONS を含む ALTER TABLE ステートメントで、列のデータ型または NULL アノテーションを変更することはできません。詳細については、データ定義言語をご覧ください。

commit タイムスタンプ オプションを削除する

列から commit タイムスタンプのサポートを削除する場合は、ALTER TABLE 文でオプション allow_commit_timestamp=null を使用します。commit タイムスタンプの動作は削除されますが、列はタイムスタンプのままです。オプションを変更しても、型や NULL 値(NOT NULL)など、列の他の特性は変更されません。次に例を示します。

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

行の挿入

行の挿入時に Cloud Spanner が commit タイムスタンプ値を書き込むようにするには、列リストに列を指定し、値として spanner.commit_timestamp() プレースホルダ文字列(またはクライアント ライブラリ定数)を渡す必要があります。次に例を示します。

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: 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"

commit タイムスタンプは、allow_commit_timestamp=true オプションでアノテーションを設定した列にのみ書き込まれます。

複数のテーブルの行に変異がある場合は、各テーブルの commit タイムスタンプ列に spanner.commit_timestamp()(またはクライアント ライブラリ定数)を指定する必要があります。

行の更新

行の更新時に Cloud Spanner が commit タイムスタンプ値を書き込むようにするには、列リストに列を指定し、値として spanner.commit_timestamp() プレースホルダ文字列(またはクライアント ライブラリ定数)を渡す必要があります。行の主キーは更新できません。主キーを更新するには、既存の行を削除して新しい行を作成します。

たとえば、LastUpdateTime という名前の commit タイムスタンプ列を更新するには、次のようにします。

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"

commit タイムスタンプは、allow_commit_timestamp=true オプションでアノテーションを設定した列にのみ書き込まれます。

複数のテーブルの行に変異がある場合は、各テーブルの commit タイムスタンプ列に spanner.commit_timestamp()(またはクライアント ライブラリ定数)を指定する必要があります。

DML ステートメントを使用した commit タイムスタンプの作成

DML ステートメントで commit タイムスタンプを作成するには、PENDING_COMMIT_TIMESTAMP 関数を使用します。トランザクションが commit されると、Cloud Spanner が commit タイムスタンプを選択します。

次の DML ステートメントは、commit タイムスタンプで Singers テーブルの LastUpdated 列を更新します。

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

commit タイムスタンプ列をクエリで取得する

次の例では、テーブルの commit タイムスタンプ列をクエリで取得します。

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

commit タイムスタンプ列に独自の値を指定する

列の値として spanner.commit_timestamp()(またはクライアント ライブラリ定数)を渡すのではなく、commit タイムスタンプ列に独自の値を指定することもできます。ただし、値は過去のタイムスタンプでなければなりません。タイムスタンプの作成コストを抑え、オペレーションを高速で実行するために、この制限が設定されています。CURRENT_TIMESTAMP SQL 関数で返された値と比較すると、過去の値かどうか簡単に確認できます。過去のタイムスタンプでない場合、サーバーは FailedPrecondition エラーを返します。

変更履歴の作成

テーブルで発生する変異の変更履歴を作成し、その履歴を監査で使用するとします。たとえば、ワープロ文書の変更履歴を格納するテーブルがあるとします。commit タイムスタンプを使用すると、タイムスタンプによって変更履歴の項目順が決まるため、変更履歴を簡単に作成できます。次のようなスキーマを使用して変更履歴を作成し、特定のドキュメントに対する変更履歴を保存します。

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()(またはクライアント ライブラリ定数)を使用し、commit タイムスタンプを列 Ts に書き込むように Cloud Spanner に指示します。DocumentsHistory テーブルと Documents テーブルをインターリーブすることで、データの局所性が維持され、挿入や更新を効率的に行うことができます。ただし、親の行と子の行を一緒に削除する必要があります。Documents の行を削除した後も DocumentHistory に行を残す場合には、テーブルをインターリーブしないでください。

最高のパフォーマンスを得るには、行のサイズを 4 GB 未満にする必要があります(行のサイズには、最上位の行と、すべてのそのインターリーブされた子とインデックス行が含まれます)。この例では、行サイズの制限のため、特定のドキュメントの DocumentHistory に含めることができる行数に制限があります。DocumentHistory の変更履歴が大きいと思われる場合は、DocumentHistory の最も古い行を削除するようにアプリを設計できます。DocumentHistory がインターリーブされたテーブルの代わりに最上位テーブルになるようにスキーマを設計することもできます。

次のステップ

commit タイムスタンプを使用して、Go で変更ログを作成する。

このページは役立ちましたか?評価をお願いいたします。

フィードバックを送信...

Cloud Spanner のドキュメント