시퀀스 만들기 및 관리

이 페이지에서는 데이터 정의 언어(DDL) 문을 사용하여 Spanner에서 시퀀스를 만들고, 변경하고, 삭제하는 방법을 설명합니다. 기본값에서 시퀀스를 사용하여 기본 키 열을 채우는 방법도 확인할 수 있습니다.

GoogleSQL-dialect 데이터베이스 및 PostgreSQL-dialect 데이터베이스에 대한 전체 시퀀스 DDL 구문 참조를 확인하세요.

참고: 90일 무료 체험판 인스턴스를 사용하여 Spanner를 살펴보려면 Spanner 무료 체험판 인스턴스 만들기를 참조하세요.

시퀀스 만들기

다음 코드 예시는 Seq 시퀀스를 만들어서 Customers 테이블에서 기본 키의 기본값으로 사용하고, 3개의 새로운 행을 Customers 테이블에 삽입합니다.

GoogleSQL

C++

void CreateSequence(
    google::cloud::spanner_admin::DatabaseAdminClient admin_client,
    google::cloud::spanner::Client client, std::string const& project_id,
    std::string const& instance_id, std::string const& database_id) {
  google::cloud::spanner::Database database(project_id, instance_id,
                                            database_id);
  std::vector<std::string> statements;
  statements.emplace_back(R"""(
      CREATE SEQUENCE Seq
          OPTIONS (sequence_kind = 'bit_reversed_positive')
  )""");
  statements.emplace_back(R"""(
      CREATE TABLE Customers (
          CustomerId INT64 DEFAULT (GET_NEXT_SEQUENCE_VALUE(SEQUENCE Seq)),
          CustomerName STRING(1024)
      ) PRIMARY KEY (CustomerId)
  )""");
  auto metadata =
      admin_client.UpdateDatabaseDdl(database.FullName(), std::move(statements))
          .get();
  if (!metadata) throw std::move(metadata).status();
  std::cout << "Created `Seq` sequence and `Customers` table,"
            << " where the key column `CustomerId`"
            << " uses the sequence as a default value,"
            << " new DDL:\n"
            << metadata->DebugString();
  auto commit = client.Commit(
      [&client](google::cloud::spanner::Transaction txn)
          -> google::cloud::StatusOr<google::cloud::spanner::Mutations> {
        auto sql = google::cloud::spanner::SqlStatement(R"""(
            INSERT INTO Customers (CustomerName)
              VALUES ('Alice'),
                     ('David'),
                     ('Marc')
              THEN RETURN CustomerId
        )""");
        using RowType = std::tuple<std::int64_t>;
        auto rows = client.ExecuteQuery(std::move(txn), std::move(sql));
        // Note: This mutator might be re-run, or its effects discarded, so
        // changing non-transactional state (e.g., by producing output) is,
        // in general, not something to be imitated.
        for (auto& row : google::cloud::spanner::StreamOf<RowType>(rows)) {
          if (!row) return std::move(row).status();
          std::cout << "Inserted customer record with CustomerId: "
                    << std::get<0>(*row) << "\n";
        }
        std::cout << "Number of customer records inserted is: "
                  << rows.RowsModified() << "\n";
        return google::cloud::spanner::Mutations{};
      });
  if (!commit) throw std::move(commit).status();
}

C#

using Google.Cloud.Spanner.Admin.Database.V1;
using Google.Cloud.Spanner.Common.V1;
using Google.Cloud.Spanner.Data;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;

public class CreateSequenceSample
{
    public async Task<List<long>> CreateSequenceAsync(string projectId, string instanceId, string databaseId)
    {
        DatabaseAdminClient databaseAdminClient = DatabaseAdminClient.Create();

        DatabaseName databaseName = DatabaseName.FromProjectInstanceDatabase(projectId, instanceId, databaseId);
        string[] statements =
        {
            "CREATE SEQUENCE Seq OPTIONS (sequence_kind = 'bit_reversed_positive')",
            "CREATE TABLE Customers (CustomerId INT64 DEFAULT (GET_NEXT_SEQUENCE_VALUE(SEQUENCE Seq)), CustomerName STRING(1024)) PRIMARY KEY (CustomerId)"
        };
        var operation = await databaseAdminClient.UpdateDatabaseDdlAsync(databaseName, statements);

        var completedResponse = await operation.PollUntilCompletedAsync();

        if (completedResponse.IsFaulted)
        {
            throw completedResponse.Exception;
        }

        Console.WriteLine("Created Seq sequence and Customers table, where the key column CustomerId uses the sequence as a default value");

        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
        using var connection = new SpannerConnection(connectionString);
        await connection.OpenAsync();

        using var cmd = connection.CreateDmlCommand(
            @"INSERT INTO Customers (CustomerName) VALUES ('Alice'), ('David'), ('Marc') THEN RETURN CustomerId");

        var reader = await cmd.ExecuteReaderAsync();
        var customerIds = new List<long>();
        while (await reader.ReadAsync())
        {
            var customerId = reader.GetFieldValue<long>("CustomerId");
            Console.WriteLine($"Inserted customer record with CustomerId: {customerId}");
            customerIds.Add(customerId);
        }
        Console.WriteLine($"Number of customer records inserted is: {customerIds.Count}");
        return customerIds;
    }
}

Go

import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/spanner"
	database "cloud.google.com/go/spanner/admin/database/apiv1"
	adminpb "cloud.google.com/go/spanner/admin/database/apiv1/databasepb"
	"google.golang.org/api/iterator"
)

func createSequence(w io.Writer, db string) error {
	// db := "projects/my-project/instances/my-instance/databases/my-database"
	ctx := context.Background()
	adminClient, err := database.NewDatabaseAdminClient(ctx)
	if err != nil {
		return err
	}
	defer adminClient.Close()

	// List of DDL statements to be applied to the database.
	// Create a sequence, and then use the sequence as auto generated primary key in Customers table.
	ddl := []string{
		"CREATE SEQUENCE Seq OPTIONS (sequence_kind = 'bit_reversed_positive')",
		"CREATE TABLE Customers (CustomerId INT64 DEFAULT (GET_NEXT_SEQUENCE_VALUE(Sequence Seq)), CustomerName STRING(1024)) PRIMARY KEY (CustomerId)",
	}
	op, err := adminClient.UpdateDatabaseDdl(ctx, &adminpb.UpdateDatabaseDdlRequest{
		Database:   db,
		Statements: ddl,
	})
	if err != nil {
		return err
	}
	// Wait for the UpdateDatabaseDdl operation to finish.
	if err := op.Wait(ctx); err != nil {
		return fmt.Errorf("waiting for bit reverse sequence creation to finish failed: %w", err)
	}
	fmt.Fprintf(w, "Created Seq sequence and Customers table, where the key column CustomerId uses the sequence as a default value\n")

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

	// Inserts records into the Customers table.
	// The ReadWriteTransaction function returns the commit timestamp and an error.
	// The commit timestamp is ignored in this case.
	_, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
		stmt := spanner.Statement{
			SQL: `INSERT INTO Customers (CustomerName) VALUES ('Alice'), ('David'), ('Marc') THEN RETURN CustomerId`,
		}
		iter := txn.Query(ctx, stmt)
		defer iter.Stop()
		for {
			row, err := iter.Next()
			if err == iterator.Done {
				break
			}
			if err != nil {
				return err
			}
			var customerId int64
			if err := row.Columns(&customerId); err != nil {
				return err
			}
			fmt.Fprintf(w, "Inserted customer record with CustomerId: %d\n", customerId)
		}
		fmt.Fprintf(w, "Number of customer records inserted is: %d\n", iter.RowCount)
		return nil
	})
	return err
}

자바

import com.google.cloud.spanner.DatabaseClient;
import com.google.cloud.spanner.DatabaseId;
import com.google.cloud.spanner.ResultSet;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerExceptionFactory;
import com.google.cloud.spanner.SpannerOptions;
import com.google.cloud.spanner.Statement;
import com.google.cloud.spanner.admin.database.v1.DatabaseAdminClient;
import com.google.common.collect.ImmutableList;
import com.google.spanner.admin.database.v1.DatabaseName;
import java.util.Objects;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.TimeoutException;

public class CreateSequenceSample {

  static void createSequence() {
    // TODO(developer): Replace these variables before running the sample.
    final String projectId = "my-project";
    final String instanceId = "my-instance";
    final String databaseId = "my-database";
    createSequence(projectId, instanceId, databaseId);
  }

  static void createSequence(String projectId, String instanceId, String databaseId) {

    try (Spanner spanner =
        SpannerOptions.newBuilder().setProjectId(projectId).build().getService();
        DatabaseAdminClient databaseAdminClient = spanner.createDatabaseAdminClient()) {
      databaseAdminClient
          .updateDatabaseDdlAsync(
              DatabaseName.of(projectId, instanceId, databaseId),
              ImmutableList.of(
                  "CREATE SEQUENCE Seq OPTIONS (sequence_kind = 'bit_reversed_positive')",
                  "CREATE TABLE Customers (CustomerId INT64 DEFAULT "
                      + "(GET_NEXT_SEQUENCE_VALUE(SEQUENCE Seq)), CustomerName STRING(1024)) "
                      + "PRIMARY KEY (CustomerId)"))
          .get(5, TimeUnit.MINUTES);

      System.out.println(
          "Created Seq sequence and Customers table, where the key column CustomerId "
              + "uses the sequence as a default value");

      final DatabaseClient dbClient =
          spanner.getDatabaseClient(DatabaseId.of(projectId, instanceId, databaseId));

      Long insertCount =
          dbClient
              .readWriteTransaction()
              .run(
                  transaction -> {
                    try (ResultSet rs =
                        transaction.executeQuery(
                            Statement.of(
                                "INSERT INTO Customers (CustomerName) VALUES "
                                    + "('Alice'), ('David'), ('Marc') THEN RETURN CustomerId"))) {
                      while (rs.next()) {
                        System.out.printf(
                            "Inserted customer record with CustomerId: %d\n", rs.getLong(0));
                      }
                      return Objects.requireNonNull(rs.getStats()).getRowCountExact();
                    }
                  });
      System.out.printf("Number of customer records inserted is: %d\n", insertCount);
    } catch (ExecutionException e) {
      // If the operation failed during execution, expose the cause.
      throw SpannerExceptionFactory.asSpannerException(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);
    } catch (TimeoutException e) {
      // If the operation timed out propagate the timeout
      throw SpannerExceptionFactory.propagateTimeout(e);
    }
  }
}

참고: Java용 이전 클라이언트 라이브러리 인터페이스 코드 샘플은 GitHub에 보관처리됩니다.

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

async function createSequence(instanceId, databaseId) {
  // Gets a reference to a Cloud Spanner Database Admin Client object
  const databaseAdminClient = spanner.getDatabaseAdminClient();

  const request = [
    "CREATE SEQUENCE Seq OPTIONS (sequence_kind = 'bit_reversed_positive')",
    'CREATE TABLE Customers (CustomerId INT64 DEFAULT (GET_NEXT_SEQUENCE_VALUE(Sequence Seq)), CustomerName STRING(1024)) PRIMARY KEY (CustomerId)',
  ];

  // Creates a new table with sequence
  try {
    const [operation] = await databaseAdminClient.updateDatabaseDdl({
      database: databaseAdminClient.databasePath(
        projectId,
        instanceId,
        databaseId
      ),
      statements: request,
    });

    console.log('Waiting for operation to complete...');
    await operation.promise();

    console.log(
      'Created Seq sequence and Customers table, where the key column CustomerId uses the sequence as a default value.'
    );
  } catch (err) {
    console.error('ERROR:', err);
  }

  // Gets a reference to a Cloud Spanner instance and database
  const instance = spanner.instance(instanceId);
  const database = instance.database(databaseId);

  database.runTransaction(async (err, transaction) => {
    if (err) {
      console.error(err);
      return;
    }
    try {
      const [rows, stats] = await transaction.run({
        sql: "INSERT INTO Customers (CustomerName) VALUES ('Alice'), ('David'), ('Marc') THEN RETURN CustomerId",
      });

      rows.forEach(row => {
        console.log(
          `Inserted customer record with CustomerId: ${
            row.toJSON({wrapNumbers: true}).CustomerId.value
          }`
        );
      });

      const rowCount = Math.floor(stats[stats.rowCount]);
      console.log(`Number of customer records inserted is: ${rowCount}`);

      await transaction.commit();
    } catch (err) {
      console.error('ERROR:', err);
    } finally {
      // Close the database when finished.
      await database.close();
    }
  });
}
await createSequence(instanceId, databaseId);

참고: Node.js용 이전 클라이언트 라이브러리 인터페이스 코드 샘플은 GitHub에 보관처리됩니다.

PHP

use Google\Cloud\Spanner\Admin\Database\V1\Client\DatabaseAdminClient;
use Google\Cloud\Spanner\Admin\Database\V1\UpdateDatabaseDdlRequest;
use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\Result;

/**
 * Creates a sequence.
 *
 * Example:
 * ```
 * create_sequence($projectId, $instanceId, $databaseId);
 * ```
 *
 * @param string $projectId The Google Cloud project ID.
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function create_sequence(string $projectId, string $instanceId, string $databaseId): void
{
    $databaseAdminClient = new DatabaseAdminClient();
    $spanner = new SpannerClient();

    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);
    $databaseName = DatabaseAdminClient::databaseName($projectId, $instanceId, $databaseId);

    $request = new UpdateDatabaseDdlRequest([
        'database' => $databaseName,
        'statements' => [
            "CREATE SEQUENCE Seq OPTIONS (sequence_kind = 'bit_reversed_positive')",
            'CREATE TABLE Customers (CustomerId INT64 DEFAULT (GET_NEXT_SEQUENCE_VALUE(' .
            'Sequence Seq)), CustomerName STRING(1024)) PRIMARY KEY (CustomerId)'
        ]
    ]);

    $operation = $databaseAdminClient->updateDatabaseDdl($request);

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

    printf(
        'Created Seq sequence and Customers table, where ' .
        'the key column CustomerId uses the sequence as a default value' .
        PHP_EOL
    );

    $transaction = $database->transaction();
    $res = $transaction->execute(
        'INSERT INTO Customers (CustomerName) VALUES ' .
        "('Alice'), ('David'), ('Marc') THEN RETURN CustomerId"
    );
    $rows = $res->rows(Result::RETURN_ASSOCIATIVE);

    foreach ($rows as $row) {
        printf('Inserted customer record with CustomerId: %d %s',
            $row['CustomerId'],
            PHP_EOL
        );
    }
    $transaction->commit();

    printf(sprintf(
        'Number of customer records inserted is: %d %s',
        $res->stats()['rowCountExact'],
        PHP_EOL
    ));
}

참고: PHP용 이전 클라이언트 라이브러리 인터페이스 코드 샘플은 GitHub에 보관처리됩니다.

Python

def create_sequence(instance_id, database_id):
    """Creates the Sequence and insert data"""

    from google.cloud.spanner_admin_database_v1.types import spanner_database_admin

    spanner_client = spanner.Client()
    database_admin_api = spanner_client.database_admin_api

    request = spanner_database_admin.UpdateDatabaseDdlRequest(
        database=database_admin_api.database_path(
            spanner_client.project, instance_id, database_id
        ),
        statements=[
            "CREATE SEQUENCE Seq OPTIONS (sequence_kind = 'bit_reversed_positive')",
            """CREATE TABLE Customers (
            CustomerId     INT64 DEFAULT (GET_NEXT_SEQUENCE_VALUE(Sequence Seq)),
            CustomerName      STRING(1024)
            ) PRIMARY KEY (CustomerId)""",
        ],
    )

    operation = database_admin_api.update_database_ddl(request)

    print("Waiting for operation to complete...")
    operation.result(OPERATION_TIMEOUT_SECONDS)

    print(
        "Created Seq sequence and Customers table, where the key column CustomerId uses the sequence as a default value on database {} on instance {}".format(
            database_id, instance_id
        )
    )

    def insert_customers(transaction):
        results = transaction.execute_sql(
            "INSERT INTO Customers (CustomerName) VALUES "
            "('Alice'), "
            "('David'), "
            "('Marc') "
            "THEN RETURN CustomerId"
        )
        for result in results:
            print("Inserted customer record with Customer Id: {}".format(*result))
        print(
            "Number of customer records inserted is {}".format(
                results.stats.row_count_exact
            )
        )

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

    database.run_in_transaction(insert_customers)

참고: Python용 이전 클라이언트 라이브러리 인터페이스 코드 샘플은 GitHub에 보관처리됩니다.

Ruby

require "google/cloud/spanner"

##
# This is a snippet for showcasing how to create a sequence.
#
# @param project_id  [String] The ID of the Google Cloud project.
# @param instance_id [String] The ID of the spanner instance.
# @param database_id [String] The ID of the database.
#
def spanner_create_sequence project_id:, instance_id:, database_id:
  db_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin

  database_path = db_admin_client.database_path project: project_id,
                                                instance: instance_id,
                                                database: database_id


  job = db_admin_client.update_database_ddl database: database_path, statements: [
    "CREATE SEQUENCE Seq OPTIONS (sequence_kind = 'bit_reversed_positive')",
    "CREATE TABLE Customers (CustomerId INT64 DEFAULT (GET_NEXT_SEQUENCE_VALUE(Sequence Seq)), CustomerName STRING(1024)) PRIMARY KEY (CustomerId)"
  ]

  puts "Waiting for operation to complete..."
  job.wait_until_done!
  puts "Created Seq sequence and Customers table, where the key column CustomerId uses the sequence as a default value"
end

PostgreSQL

C++

void CreateSequence(
    google::cloud::spanner_admin::DatabaseAdminClient admin_client,
    google::cloud::spanner::Database const& database,
    google::cloud::spanner::Client client) {
  std::vector<std::string> statements;
  statements.emplace_back(R"""(
      CREATE SEQUENCE Seq BIT_REVERSED_POSITIVE
  )""");
  statements.emplace_back(R"""(
      CREATE TABLE Customers (
          CustomerId    BIGINT DEFAULT NEXTVAL('Seq'),
          CustomerName  CHARACTER VARYING(1024),
          PRIMARY KEY (CustomerId)
      )
  )""");
  auto metadata =
      admin_client.UpdateDatabaseDdl(database.FullName(), std::move(statements))
          .get();
  if (!metadata) throw std::move(metadata).status();
  std::cout << "Created `Seq` sequence and `Customers` table,"
            << " where the key column `CustomerId`"
            << " uses the sequence as a default value,"
            << " new DDL:\n"
            << metadata->DebugString();
  auto commit = client.Commit(
      [&client](google::cloud::spanner::Transaction txn)
          -> google::cloud::StatusOr<google::cloud::spanner::Mutations> {
        auto sql = google::cloud::spanner::SqlStatement(R"""(
            INSERT INTO Customers (CustomerName)
                VALUES ('Alice'),
                       ('David'),
                       ('Marc')
                RETURNING CustomerId
        )""");
        using RowType = std::tuple<std::int64_t>;
        auto rows = client.ExecuteQuery(std::move(txn), std::move(sql));
        // Note: This mutator might be re-run, or its effects discarded, so
        // changing non-transactional state (e.g., by producing output) is,
        // in general, not something to be imitated.
        for (auto& row : google::cloud::spanner::StreamOf<RowType>(rows)) {
          if (!row) return std::move(row).status();
          std::cout << "Inserted customer record with CustomerId: "
                    << std::get<0>(*row) << "\n";
        }
        std::cout << "Number of customer records inserted is: "
                  << rows.RowsModified() << "\n";
        return google::cloud::spanner::Mutations{};
      });
  if (!commit) throw std::move(commit).status();
}

C#

using Google.Cloud.Spanner.Common.V1;
using System.Threading.Tasks;
using System;
using Google.Cloud.Spanner.Admin.Database.V1;
using Google.Cloud.Spanner.Data;
using System.Collections.Generic;

public class CreateSequencePostgresqlSample
{
    public async Task<List<long>> CreateSequencePostgresqlSampleAsync(string projectId, string instanceId, string databaseId)
    {
        DatabaseAdminClient databaseAdminClient = DatabaseAdminClient.Create();

        DatabaseName databaseName = DatabaseName.FromProjectInstanceDatabase(projectId, instanceId, databaseId);
        string[] statements =
        {
            "CREATE SEQUENCE Seq BIT_REVERSED_POSITIVE ;",
            "CREATE TABLE Customers (CustomerId BIGINT DEFAULT nextval('Seq'), CustomerName character varying(1024), PRIMARY KEY (CustomerId))"
        };
        var operation = await databaseAdminClient.UpdateDatabaseDdlAsync(databaseName, statements);

        var completedResponse = await operation.PollUntilCompletedAsync();
        if (completedResponse.IsFaulted)
        {
            throw completedResponse.Exception;
        }

        Console.WriteLine("Created Seq sequence and Customers table, where the key column CustomerId uses the sequence as a default value");

        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
        using var connection = new SpannerConnection(connectionString);
        await connection.OpenAsync();

        using var cmd = connection.CreateDmlCommand(
            @"INSERT INTO Customers (CustomerName) VALUES ('Alice'), ('David'), ('Marc') RETURNING CustomerId");

        var reader = await cmd.ExecuteReaderAsync();
        var customerIds = new List<long>();
        while (await reader.ReadAsync())
        {
            var customerId = reader.GetFieldValue<long>("customerid");
            Console.WriteLine($"Inserted customer record with CustomerId: {customerId}");
            customerIds.Add(customerId);
        }
        Console.WriteLine($"Number of customer records inserted is: {customerIds.Count}");
        return customerIds;
    }
}

Go

import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/spanner"
	database "cloud.google.com/go/spanner/admin/database/apiv1"
	adminpb "cloud.google.com/go/spanner/admin/database/apiv1/databasepb"
	"google.golang.org/api/iterator"
)

func pgCreateSequence(w io.Writer, db string) error {
	// db := "projects/my-project/instances/my-instance/databases/my-database"
	ctx := context.Background()
	adminClient, err := database.NewDatabaseAdminClient(ctx)
	if err != nil {
		return err
	}
	defer adminClient.Close()

	// List of DDL statements to be applied to the database.
	// Create a sequence, and then use the sequence as auto generated primary key in Customers table.
	ddl := []string{
		"CREATE SEQUENCE Seq BIT_REVERSED_POSITIVE",
		"CREATE TABLE Customers (CustomerId BIGINT DEFAULT nextval('Seq'), CustomerName character varying(1024), PRIMARY KEY (CustomerId))",
	}
	op, err := adminClient.UpdateDatabaseDdl(ctx, &adminpb.UpdateDatabaseDdlRequest{
		Database:   db,
		Statements: ddl,
	})
	if err != nil {
		return err
	}
	// Wait for the UpdateDatabaseDdl operation to finish.
	if err := op.Wait(ctx); err != nil {
		return fmt.Errorf("waiting for bit reverse sequence creation to finish failed: %w", err)
	}
	fmt.Fprintf(w, "Created Seq sequence and Customers table, where its key column CustomerId uses the sequence as a default value\n")

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

	// Inserts records into the Customers table.
	// The ReadWriteTransaction function returns the commit timestamp and an error.
	// The commit timestamp is ignored in this case.
	_, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
		stmt := spanner.Statement{
			SQL: `INSERT INTO Customers (CustomerName) VALUES ('Alice'), ('David'), ('Marc') RETURNING CustomerId`,
		}
		iter := txn.Query(ctx, stmt)
		defer iter.Stop()
		for {
			row, err := iter.Next()
			if err == iterator.Done {
				break
			}
			if err != nil {
				return err
			}
			var customerId int64
			if err := row.Columns(&customerId); err != nil {
				return err
			}
			fmt.Fprintf(w, "Inserted customer record with CustomerId: %d\n", customerId)
		}
		fmt.Fprintf(w, "Number of customer records inserted is: %d\n", iter.RowCount)
		return nil
	})
	return err
}

자바

import com.google.cloud.spanner.DatabaseClient;
import com.google.cloud.spanner.DatabaseId;
import com.google.cloud.spanner.ResultSet;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerExceptionFactory;
import com.google.cloud.spanner.SpannerOptions;
import com.google.cloud.spanner.Statement;
import com.google.cloud.spanner.admin.database.v1.DatabaseAdminClient;
import com.google.common.collect.ImmutableList;
import com.google.spanner.admin.database.v1.DatabaseName;
import java.util.Objects;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.TimeoutException;

public class PgCreateSequenceSample {

  static void pgCreateSequence() {
    // TODO(developer): Replace these variables before running the sample.
    final String projectId = "my-project";
    final String instanceId = "my-instance";
    final String databaseId = "my-database";
    pgCreateSequence(projectId, instanceId, databaseId);
  }

  static void pgCreateSequence(String projectId, String instanceId, String databaseId) {
    try (Spanner spanner =
        SpannerOptions.newBuilder().setProjectId(projectId).build().getService();
        DatabaseAdminClient databaseAdminClient = spanner.createDatabaseAdminClient()) {
      databaseAdminClient
          .updateDatabaseDdlAsync(DatabaseName.of(projectId, instanceId, databaseId).toString(),
              ImmutableList.of(
                  "CREATE SEQUENCE Seq BIT_REVERSED_POSITIVE;",
                  "CREATE TABLE Customers (CustomerId BIGINT DEFAULT nextval('Seq'), "
                      + "CustomerName character varying(1024), PRIMARY KEY (CustomerId))"))
          .get(5, TimeUnit.MINUTES);

      System.out.println(
          "Created Seq sequence and Customers table, where the key column "
              + "CustomerId uses the sequence as a default value");

      final DatabaseClient dbClient =
          spanner.getDatabaseClient(DatabaseId.of(projectId, instanceId, databaseId));

      Long insertCount =
          dbClient
              .readWriteTransaction()
              .run(
                  transaction -> {
                    try (ResultSet rs =
                        transaction.executeQuery(
                            Statement.of(
                                "INSERT INTO Customers (CustomerName) VALUES "
                                    + "('Alice'), ('David'), ('Marc') RETURNING CustomerId"))) {
                      while (rs.next()) {
                        System.out.printf(
                            "Inserted customer record with CustomerId: %d\n", rs.getLong(0));
                      }
                      return Objects.requireNonNull(rs.getStats()).getRowCountExact();
                    }
                  });
      System.out.printf("Number of customer records inserted is: %d\n", insertCount);
    } catch (ExecutionException e) {
      // If the operation failed during execution, expose the cause.
      throw SpannerExceptionFactory.asSpannerException(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);
    } catch (TimeoutException e) {
      // If the operation timed out propagate the timeout
      throw SpannerExceptionFactory.propagateTimeout(e);
    }
  }
}

참고: Java용 이전 클라이언트 라이브러리 인터페이스 코드 샘플은 GitHub에 보관처리됩니다.

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

async function createSequence(instanceId, databaseId) {
  // Gets a reference to a Cloud Spanner Database Admin Client object
  const databaseAdminClient = spanner.getDatabaseAdminClient();

  const request = [
    'CREATE SEQUENCE Seq BIT_REVERSED_POSITIVE',
    "CREATE TABLE Customers (CustomerId BIGINT DEFAULT nextval('Seq'), CustomerName character varying(1024), PRIMARY KEY (CustomerId))",
  ];

  // Creates a new table with sequence
  try {
    const [operation] = await databaseAdminClient.updateDatabaseDdl({
      database: databaseAdminClient.databasePath(
        projectId,
        instanceId,
        databaseId
      ),
      statements: request,
    });

    console.log('Waiting for operation to complete...');
    await operation.promise();

    console.log(
      'Created Seq sequence and Customers table, where the key column CustomerId uses the sequence as a default value'
    );
  } catch (err) {
    console.error('ERROR:', err);
  }

  // Gets a reference to a Cloud Spanner instance and database
  const instance = spanner.instance(instanceId);
  const database = instance.database(databaseId);

  database.runTransaction(async (err, transaction) => {
    if (err) {
      console.error(err);
      return;
    }
    try {
      const [rows, stats] = await transaction.run({
        sql: "INSERT INTO Customers (CustomerName) VALUES ('Alice'), ('David'), ('Marc') RETURNING CustomerId",
      });

      rows.forEach(row => {
        console.log(
          `Inserted customer record with CustomerId: ${
            row.toJSON({wrapNumbers: true}).customerid.value
          }`
        );
      });

      const rowCount = Math.floor(stats[stats.rowCount]);
      console.log(`Number of customer records inserted is: ${rowCount}`);

      await transaction.commit();
    } catch (err) {
      console.error('ERROR:', err);
    } finally {
      // Close the spanner client when finished.
      // The databaseAdminClient does not require explicit closure. The closure of the Spanner client will automatically close the databaseAdminClient.
      spanner.close();
    }
  });
}
await createSequence(instanceId, databaseId);

참고: Node.js용 이전 클라이언트 라이브러리 인터페이스 코드 샘플은 GitHub에 보관처리됩니다.

PHP

use Google\Cloud\Spanner\Admin\Database\V1\Client\DatabaseAdminClient;
use Google\Cloud\Spanner\Admin\Database\V1\UpdateDatabaseDdlRequest;
use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\Result;

/**
 * Creates a sequence.
 * Example:
 * ```
 * pg_create_sequence($projectId, $instanceId, $databaseId);
 * ```
 *
 * @param string $projectId The Google Cloud Project ID.
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function pg_create_sequence(
    string $projectId,
    string $instanceId,
    string $databaseId
): void {
    $databaseAdminClient = new DatabaseAdminClient();
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);
    $transaction = $database->transaction();
    $operation = $databaseAdminClient->updateDatabaseDdl(new UpdateDatabaseDdlRequest([
        'database' => DatabaseAdminClient::databaseName($projectId, $instanceId, $databaseId),
        'statements' => [
            'CREATE SEQUENCE Seq BIT_REVERSED_POSITIVE',
            "CREATE TABLE Customers (
            CustomerId           BIGINT DEFAULT nextval('Seq'), 
            CustomerName         CHARACTER VARYING(1024), 
            PRIMARY KEY (CustomerId))"
        ]
    ]));

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

    printf(
        'Created Seq sequence and Customers table, where ' .
        'the key column CustomerId uses the sequence as a default value' .
        PHP_EOL
    );

    $res = $transaction->execute(
        'INSERT INTO Customers (CustomerName) VALUES ' .
        "('Alice'), ('David'), ('Marc') RETURNING CustomerId"
    );
    $rows = $res->rows(Result::RETURN_ASSOCIATIVE);

    foreach ($rows as $row) {
        printf('Inserted customer record with CustomerId: %d %s',
            $row['customerid'],
            PHP_EOL
        );
    }
    $transaction->commit();

    printf(sprintf(
        'Number of customer records inserted is: %d %s',
        $res->stats()['rowCountExact'],
        PHP_EOL
    ));
}

참고: PHP용 이전 클라이언트 라이브러리 인터페이스 코드 샘플은 GitHub에 보관처리됩니다.

Python

def create_sequence(instance_id, database_id):
    """Creates the Sequence and insert data"""

    from google.cloud.spanner_admin_database_v1.types import \
        spanner_database_admin

    spanner_client = spanner.Client()
    database_admin_api = spanner_client.database_admin_api

    request = spanner_database_admin.UpdateDatabaseDdlRequest(
        database=database_admin_api.database_path(
            spanner_client.project, instance_id, database_id
        ),
        statements=[
            "CREATE SEQUENCE Seq BIT_REVERSED_POSITIVE",
            """CREATE TABLE Customers (
        CustomerId  BIGINT DEFAULT nextval('Seq'),
        CustomerName  character varying(1024),
        PRIMARY KEY (CustomerId)
        )""",
        ],
    )
    operation = database_admin_api.update_database_ddl(request)
    print("Waiting for operation to complete...")
    operation.result(OPERATION_TIMEOUT_SECONDS)

    print(
        "Created Seq sequence and Customers table, where the key column CustomerId uses the sequence as a default value on database {} on instance {}".format(
            database_id, instance_id
        )
    )

    def insert_customers(transaction):
        results = transaction.execute_sql(
            "INSERT INTO Customers (CustomerName) VALUES "
            "('Alice'), "
            "('David'), "
            "('Marc') "
            "RETURNING CustomerId"
        )
        for result in results:
            print("Inserted customer record with Customer Id: {}".format(*result))
        print(
            "Number of customer records inserted is {}".format(
                results.stats.row_count_exact
            )
        )

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

    database.run_in_transaction(insert_customers)

참고: Python용 이전 클라이언트 라이브러리 인터페이스 코드 샘플은 GitHub에 보관처리됩니다.

Ruby

require "google/cloud/spanner"

##
# This is a snippet for showcasing how to create a sequence using postgresql.
#
# @param project_id  [String] The ID of the Google Cloud project.
# @param instance_id [String] The ID of the spanner instance.
# @param database_id [String] The ID of the database.
#
def spanner_postgresql_create_sequence project_id:, instance_id:, database_id:
  db_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin

  database_path = db_admin_client.database_path project: project_id,
                                                instance: instance_id,
                                                database: database_id


  job = db_admin_client.update_database_ddl database: database_path, statements: [
    "CREATE SEQUENCE Seq BIT_REVERSED_POSITIVE",
    "CREATE TABLE Customers (CustomerId BIGINT DEFAULT nextval('Seq'), CustomerName character varying(1024), PRIMARY KEY (CustomerId))"
  ]

  puts "Waiting for operation to complete..."
  job.wait_until_done!
  puts "Created Seq sequence and Customers table, where its key column CustomerId uses the sequence as a default value"
end

시퀀스 변경하기

다음 코드 예시에서는 Seq 시퀀스를 변경하여 1,000에서 5백만 사이의 값 범위를 건너뜁니다. 그런 후 3개의 새로운 행을 Customers 테이블에 삽입합니다.

GoogleSQL

C++

void AlterSequence(
    google::cloud::spanner_admin::DatabaseAdminClient admin_client,
    google::cloud::spanner::Client client, std::string const& project_id,
    std::string const& instance_id, std::string const& database_id) {
  google::cloud::spanner::Database database(project_id, instance_id,
                                            database_id);
  std::vector<std::string> statements;
  statements.emplace_back(R"""(
      ALTER SEQUENCE Seq
          SET OPTIONS (skip_range_min = 1000, skip_range_max = 5000000)
  )""");
  auto metadata =
      admin_client.UpdateDatabaseDdl(database.FullName(), std::move(statements))
          .get();
  if (!metadata) throw std::move(metadata).status();
  std::cout << "Altered `Seq` sequence"
            << "  to skip an inclusive range between 1000 and 5000000,"
            << " new DDL:\n"
            << metadata->DebugString();
  auto commit = client.Commit(
      [&client](google::cloud::spanner::Transaction txn)
          -> google::cloud::StatusOr<google::cloud::spanner::Mutations> {
        auto sql = google::cloud::spanner::SqlStatement(R"""(
            INSERT INTO Customers (CustomerName)
              VALUES ('Lea'),
                     ('Catalina'),
                     ('Smith')
              THEN RETURN CustomerId
        )""");
        using RowType = std::tuple<std::int64_t>;
        auto rows = client.ExecuteQuery(std::move(txn), std::move(sql));
        // Note: This mutator might be re-run, or its effects discarded, so
        // changing non-transactional state (e.g., by producing output) is,
        // in general, not something to be imitated.
        for (auto& row : google::cloud::spanner::StreamOf<RowType>(rows)) {
          if (!row) return std::move(row).status();
          std::cout << "Inserted customer record with CustomerId: "
                    << std::get<0>(*row) << "\n";
        }
        std::cout << "Number of customer records inserted is: "
                  << rows.RowsModified() << "\n";
        return google::cloud::spanner::Mutations{};
      });
  if (!commit) throw std::move(commit).status();
}

C#

using Google.Cloud.Spanner.Admin.Database.V1;
using Google.Cloud.Spanner.Common.V1;
using Google.Cloud.Spanner.Data;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;

public class AlterSequenceSample
{
    public async Task<List<long>> AlterSequenceSampleAsync(string projectId, string instanceId, string databaseId)
    {
        DatabaseAdminClient databaseAdminClient = DatabaseAdminClient.Create();

        DatabaseName databaseName = DatabaseName.FromProjectInstanceDatabase(projectId, instanceId, databaseId);
        string[] statements =
        {
            "ALTER SEQUENCE Seq SET OPTIONS (skip_range_min = 1000, skip_range_max = 5000000)"
        };
        var operation = await databaseAdminClient.UpdateDatabaseDdlAsync(databaseName, statements);

        var completedResponse = await operation.PollUntilCompletedAsync();
        if (completedResponse.IsFaulted)
        {
            throw completedResponse.Exception;
        }
        Console.WriteLine("Altered Seq sequence to skip an inclusive range between 1000 and 5000000");

        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
        using var connection = new SpannerConnection(connectionString);
        await connection.OpenAsync();

        using var cmd = connection.CreateDmlCommand(
            @"INSERT INTO Customers (CustomerName) VALUES ('Alice'), ('David'), ('Marc') THEN RETURN CustomerId");

        var reader = await cmd.ExecuteReaderAsync();
        var customerIds = new List<long>();
        while (await reader.ReadAsync())
        {
            long customerId = reader.GetFieldValue<long>("CustomerId");
            Console.WriteLine($"Inserted customer record with CustomerId: {customerId}");
            customerIds.Add(customerId);
        }
        Console.WriteLine($"Number of customer records inserted is: {customerIds.Count}");

        return customerIds;
    }
}

Go

import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/spanner"
	database "cloud.google.com/go/spanner/admin/database/apiv1"
	adminpb "cloud.google.com/go/spanner/admin/database/apiv1/databasepb"
	"google.golang.org/api/iterator"
)

func alterSequence(w io.Writer, db string) error {
	// db := "projects/my-project/instances/my-instance/databases/my-database"
	ctx := context.Background()
	adminClient, err := database.NewDatabaseAdminClient(ctx)
	if err != nil {
		return err
	}
	defer adminClient.Close()

	// List of DDL statements to be applied to the database.
	// Alter the sequence to skip range [1000-5000000] for new keys.
	ddl := []string{
		"ALTER SEQUENCE Seq SET OPTIONS (skip_range_min = 1000, skip_range_max = 5000000)",
	}
	op, err := adminClient.UpdateDatabaseDdl(ctx, &adminpb.UpdateDatabaseDdlRequest{
		Database:   db,
		Statements: ddl,
	})
	if err != nil {
		return err
	}
	// Wait for the UpdateDatabaseDdl operation to finish.
	if err := op.Wait(ctx); err != nil {
		return fmt.Errorf("waiting for bit reverse sequence skip range to finish failed: %w", err)
	}
	fmt.Fprintf(w, "Altered Seq sequence to skip an inclusive range between 1000 and 5000000\n")

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

	// Inserts records into the Customers table.
	// The ReadWriteTransaction function returns the commit timestamp and an error.
	// The commit timestamp is ignored in this case.
	_, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
		stmt := spanner.Statement{
			SQL: `INSERT INTO Customers (CustomerName) VALUES ('Lea'), ('Catalina'), ('Smith') THEN RETURN CustomerId`,
		}
		iter := txn.Query(ctx, stmt)
		defer iter.Stop()
		for {
			row, err := iter.Next()
			if err == iterator.Done {
				break
			}
			if err != nil {
				return err
			}
			var customerId int64
			if err := row.Columns(&customerId); err != nil {
				return err
			}
			fmt.Fprintf(w, "Inserted customer record with CustomerId: %d\n", customerId)
		}
		fmt.Fprintf(w, "Number of customer records inserted is: %d\n", iter.RowCount)
		return nil
	})
	return err
}

자바

import com.google.cloud.spanner.DatabaseClient;
import com.google.cloud.spanner.DatabaseId;
import com.google.cloud.spanner.ResultSet;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerExceptionFactory;
import com.google.cloud.spanner.SpannerOptions;
import com.google.cloud.spanner.Statement;
import com.google.cloud.spanner.admin.database.v1.DatabaseAdminClient;
import com.google.common.collect.ImmutableList;
import com.google.spanner.admin.database.v1.DatabaseName;
import java.util.Objects;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.TimeoutException;

public class AlterSequenceSample {

  static void alterSequence() {
    // TODO(developer): Replace these variables before running the sample.
    final String projectId = "my-project";
    final String instanceId = "my-instance";
    final String databaseId = "my-database";
    alterSequence(projectId, instanceId, databaseId);
  }

  static void alterSequence(String projectId, String instanceId, String databaseId) {
    try (Spanner spanner =
        SpannerOptions.newBuilder().setProjectId(projectId).build().getService();
        DatabaseAdminClient databaseAdminClient = spanner.createDatabaseAdminClient()) {

      databaseAdminClient
          .updateDatabaseDdlAsync(DatabaseName.of(projectId, instanceId, databaseId),
              ImmutableList.of(
                  "ALTER SEQUENCE Seq SET OPTIONS "
                      + "(skip_range_min = 1000, skip_range_max = 5000000)"))
          .get(5, TimeUnit.MINUTES);

      System.out.println(
          "Altered Seq sequence to skip an inclusive range between 1000 and 5000000");

      final DatabaseClient dbClient =
          spanner.getDatabaseClient(DatabaseId.of(projectId, instanceId, databaseId));

      Long insertCount =
          dbClient
              .readWriteTransaction()
              .run(
                  transaction -> {
                    try (ResultSet rs =
                        transaction.executeQuery(
                            Statement.of(
                                "INSERT INTO Customers (CustomerName) VALUES "
                                    + "('Lea'), ('Catalina'), ('Smith') "
                                    + "THEN RETURN CustomerId"))) {
                      while (rs.next()) {
                        System.out.printf(
                            "Inserted customer record with CustomerId: %d\n", rs.getLong(0));
                      }
                      return Objects.requireNonNull(rs.getStats()).getRowCountExact();
                    }
                  });
      System.out.printf("Number of customer records inserted is: %d\n", insertCount);
    } catch (ExecutionException e) {
      // If the operation failed during execution, expose the cause.
      throw SpannerExceptionFactory.asSpannerException(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);
    } catch (TimeoutException e) {
      // If the operation timed out propagate the timeout
      throw SpannerExceptionFactory.propagateTimeout(e);
    }
  }
}

참고: Java용 이전 클라이언트 라이브러리 인터페이스 코드 샘플은 GitHub에 보관처리됩니다.

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

async function alterSequence(instanceId, databaseId) {
  // Gets a reference to a Cloud Spanner Database Admin Client object
  const databaseAdminClient = spanner.getDatabaseAdminClient();

  const request = [
    'ALTER SEQUENCE Seq SET OPTIONS (skip_range_min = 1000, skip_range_max = 5000000)',
  ];

  try {
    const [operation] = await databaseAdminClient.updateDatabaseDdl({
      database: databaseAdminClient.databasePath(
        projectId,
        instanceId,
        databaseId
      ),
      statements: request,
    });

    console.log('Waiting for operation to complete...');
    await operation.promise();

    console.log(
      'Altered Seq sequence to skip an inclusive range between 1000 and 5000000.'
    );
  } catch (err) {
    console.error('ERROR:', err);
  }

  // Gets a reference to a Cloud Spanner instance and database
  const instance = spanner.instance(instanceId);
  const database = instance.database(databaseId);

  database.runTransaction(async (err, transaction) => {
    if (err) {
      console.error(err);
      return;
    }
    try {
      const [rows, stats] = await transaction.run({
        sql: "INSERT INTO Customers (CustomerName) VALUES ('Lea'), ('Catalina'), ('Smith') THEN RETURN CustomerId",
      });

      rows.forEach(row => {
        console.log(
          `Inserted customer record with CustomerId: ${
            row.toJSON({wrapNumbers: true}).CustomerId.value
          }`
        );
      });

      const rowCount = Math.floor(stats[stats.rowCount]);
      console.log(`Number of customer records inserted is: ${rowCount}`);

      await transaction.commit();
    } catch (err) {
      console.error('ERROR:', err);
    } finally {
      // Close the database when finished.
      await database.close();
    }
  });
}
await alterSequence(instanceId, databaseId);

참고: Node.js용 이전 클라이언트 라이브러리 인터페이스 코드 샘플은 GitHub에 보관처리됩니다.

PHP

use Google\Cloud\Spanner\Admin\Database\V1\Client\DatabaseAdminClient;
use Google\Cloud\Spanner\Admin\Database\V1\UpdateDatabaseDdlRequest;
use Google\Cloud\Spanner\Result;
use Google\Cloud\Spanner\SpannerClient;

/**
 * Alters a sequence.
 * Example:
 * ```
 * alter_sequence($projectId, $instanceId, $databaseId);
 * ```
 *
 * @param string $projectId The Google Cloud project ID.
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function alter_sequence(string $projectId, string $instanceId, string $databaseId): void
{
    $databaseAdminClient = new DatabaseAdminClient();
    $spanner = new SpannerClient();

    $databaseName = DatabaseAdminClient::databaseName($projectId, $instanceId, $databaseId);
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);
    $transaction = $database->transaction();

    $statements = [
         'ALTER SEQUENCE Seq SET OPTIONS ' .
        '(skip_range_min = 1000, skip_range_max = 5000000)'
    ];
    $request = new UpdateDatabaseDdlRequest([
        'database' => $databaseName,
        'statements' => $statements
    ]);

    $operation = $databaseAdminClient->updateDatabaseDdl($request);

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

    printf(
        'Altered Seq sequence to skip an inclusive range between 1000 and 5000000' .
        PHP_EOL
    );

    $res = $transaction->execute(
        'INSERT INTO Customers (CustomerName) VALUES ' .
        "('Lea'), ('Catalina'), ('Smith') THEN RETURN CustomerId"
    );
    $rows = $res->rows(Result::RETURN_ASSOCIATIVE);

    foreach ($rows as $row) {
        printf('Inserted customer record with CustomerId: %d %s',
            $row['CustomerId'],
            PHP_EOL
        );
    }
    $transaction->commit();

    printf(sprintf(
        'Number of customer records inserted is: %d %s',
        $res->stats()['rowCountExact'],
        PHP_EOL
    ));
}

참고: PHP용 이전 클라이언트 라이브러리 인터페이스 코드 샘플은 GitHub에 보관처리됩니다.

Python

def alter_sequence(instance_id, database_id):
    """Alters the Sequence and insert data"""

    from google.cloud.spanner_admin_database_v1.types import spanner_database_admin

    spanner_client = spanner.Client()
    database_admin_api = spanner_client.database_admin_api

    request = spanner_database_admin.UpdateDatabaseDdlRequest(
        database=database_admin_api.database_path(
            spanner_client.project, instance_id, database_id
        ),
        statements=[
            "ALTER SEQUENCE Seq SET OPTIONS (skip_range_min = 1000, skip_range_max = 5000000)",
        ],
    )

    operation = database_admin_api.update_database_ddl(request)

    print("Waiting for operation to complete...")
    operation.result(OPERATION_TIMEOUT_SECONDS)

    print(
        "Altered Seq sequence to skip an inclusive range between 1000 and 5000000 on database {} on instance {}".format(
            database_id, instance_id
        )
    )

    def insert_customers(transaction):
        results = transaction.execute_sql(
            "INSERT INTO Customers (CustomerName) VALUES "
            "('Lea'), "
            "('Cataline'), "
            "('Smith') "
            "THEN RETURN CustomerId"
        )
        for result in results:
            print("Inserted customer record with Customer Id: {}".format(*result))
        print(
            "Number of customer records inserted is {}".format(
                results.stats.row_count_exact
            )
        )

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

    database.run_in_transaction(insert_customers)

참고: Python용 이전 클라이언트 라이브러리 인터페이스 코드 샘플은 GitHub에 보관처리됩니다.

Ruby

require "google/cloud/spanner"

##
# This is a snippet for showcasing how to alter a sequence.
#
# @param project_id  [String] The ID of the Google Cloud project.
# @param instance_id [String] The ID of the spanner instance.
# @param database_id [String] The ID of the database.
#
def spanner_alter_sequence project_id:, instance_id:, database_id:
  db_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin

  database_path = db_admin_client.database_path project: project_id,
                                                instance: instance_id,
                                                database: database_id


  job = db_admin_client.update_database_ddl database: database_path, statements: [
    "ALTER SEQUENCE Seq SET OPTIONS (skip_range_min = 1000, skip_range_max = 5000000)"
  ]

  puts "Waiting for operation to complete..."
  job.wait_until_done!
  puts "Altered Seq sequence to skip an inclusive range between 1000 and 5000000"
end

PostgreSQL

C++

void AlterSequence(
    google::cloud::spanner_admin::DatabaseAdminClient admin_client,
    google::cloud::spanner::Database const& database,
    google::cloud::spanner::Client client) {
  std::vector<std::string> statements;
  statements.emplace_back(R"""(
      ALTER SEQUENCE Seq SKIP RANGE 1000 5000000
  )""");
  auto metadata =
      admin_client.UpdateDatabaseDdl(database.FullName(), std::move(statements))
          .get();
  if (!metadata) throw std::move(metadata).status();
  std::cout << "Altered `Seq` sequence"
            << "  to skip an inclusive range between 1000 and 5000000,"
            << " new DDL:\n"
            << metadata->DebugString();
  auto commit = client.Commit(
      [&client](google::cloud::spanner::Transaction txn)
          -> google::cloud::StatusOr<google::cloud::spanner::Mutations> {
        auto sql = google::cloud::spanner::SqlStatement(R"""(
            INSERT INTO Customers (CustomerName)
                VALUES ('Lea'),
                       ('Catalina'),
                       ('Smith')
                RETURNING CustomerId
        )""");
        using RowType = std::tuple<std::int64_t>;
        auto rows = client.ExecuteQuery(std::move(txn), std::move(sql));
        // Note: This mutator might be re-run, or its effects discarded, so
        // changing non-transactional state (e.g., by producing output) is,
        // in general, not something to be imitated.
        for (auto& row : google::cloud::spanner::StreamOf<RowType>(rows)) {
          if (!row) return std::move(row).status();
          std::cout << "Inserted customer record with CustomerId: "
                    << std::get<0>(*row) << "\n";
        }
        std::cout << "Number of customer records inserted is: "
                  << rows.RowsModified() << "\n";
        return google::cloud::spanner::Mutations{};
      });
  if (!commit) throw std::move(commit).status();
}

C#

using Google.Cloud.Spanner.Admin.Database.V1;
using Google.Cloud.Spanner.Common.V1;
using Google.Cloud.Spanner.Data;
using Google.LongRunning;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;

public class AlterSequencePostgresqlSample
{
    public async Task<List<long>> AlterSequencePostgresqlSampleAsync(string projectId, string instanceId, string databaseId)
    {
        DatabaseAdminClient databaseAdminClient = DatabaseAdminClient.Create();
        DatabaseName databaseName = DatabaseName.FromProjectInstanceDatabase(projectId, instanceId, databaseId);
        string[] statements =
        {
            "ALTER SEQUENCE Seq SKIP RANGE 1000 5000000;"
        };
        var operation = await databaseAdminClient.UpdateDatabaseDdlAsync(databaseName, statements);
        var completedResponse = await operation.PollUntilCompletedAsync();
        if (completedResponse.IsFaulted)
        {
            throw completedResponse.Exception;
        }
        Console.WriteLine("Altered Seq sequence to skip an inclusive range between 1000 and 5000000");

        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
        using var connection = new SpannerConnection(connectionString);
        await connection.OpenAsync();

        using var cmd = connection.CreateDmlCommand(
            @"INSERT INTO Customers (CustomerName) VALUES ('Alice'), ('David'), ('Marc') RETURNING CustomerId");

        var reader = await cmd.ExecuteReaderAsync();
        var customerIds = new List<long>();
        while (await reader.ReadAsync())
        {
            var customerId = reader.GetFieldValue<long>("customerid");
            Console.WriteLine($"Inserted customer record with CustomerId: {customerId}");
            customerIds.Add(customerId);
        }
        Console.WriteLine($"Number of customer records inserted is: {customerIds.Count}");
        return customerIds;
    }
}

Go

import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/spanner"
	database "cloud.google.com/go/spanner/admin/database/apiv1"
	adminpb "cloud.google.com/go/spanner/admin/database/apiv1/databasepb"
	"google.golang.org/api/iterator"
)

func pgAlterSequence(w io.Writer, db string) error {
	// db := "projects/my-project/instances/my-instance/databases/my-database"
	ctx := context.Background()
	adminClient, err := database.NewDatabaseAdminClient(ctx)
	if err != nil {
		return err
	}
	defer adminClient.Close()

	// List of DDL statements to be applied to the database.
	// Alter the sequence to skip range [1000-5000000] for new keys.
	ddl := []string{
		"ALTER SEQUENCE Seq SKIP RANGE 1000 5000000",
	}
	op, err := adminClient.UpdateDatabaseDdl(ctx, &adminpb.UpdateDatabaseDdlRequest{
		Database:   db,
		Statements: ddl,
	})
	if err != nil {
		return err
	}
	// Wait for the UpdateDatabaseDdl operation to finish.
	if err := op.Wait(ctx); err != nil {
		return fmt.Errorf("waiting for bit reverse sequence skip range to finish failed: %w", err)
	}
	fmt.Fprintf(w, "Altered Seq sequence to skip an inclusive range between 1000 and 5000000\n")

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

	// Inserts records into the Customers table.
	// The ReadWriteTransaction function returns the commit timestamp and an error.
	// The commit timestamp is ignored in this case.
	_, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
		stmt := spanner.Statement{
			SQL: `INSERT INTO Customers (CustomerName) VALUES ('Lea'), ('Catalina'), ('Smith') RETURNING CustomerId`,
		}
		iter := txn.Query(ctx, stmt)
		defer iter.Stop()
		for {
			row, err := iter.Next()
			if err == iterator.Done {
				break
			}
			if err != nil {
				return err
			}
			var customerId int64
			if err := row.Columns(&customerId); err != nil {
				return err
			}
			fmt.Fprintf(w, "Inserted customer record with CustomerId: %d\n", customerId)
		}
		fmt.Fprintf(w, "Number of customer records inserted is: %d\n", iter.RowCount)
		return nil
	})
	return err
}

자바

import com.google.cloud.spanner.DatabaseClient;
import com.google.cloud.spanner.DatabaseId;
import com.google.cloud.spanner.ResultSet;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerExceptionFactory;
import com.google.cloud.spanner.SpannerOptions;
import com.google.cloud.spanner.Statement;
import com.google.cloud.spanner.admin.database.v1.DatabaseAdminClient;
import com.google.common.collect.ImmutableList;
import com.google.spanner.admin.database.v1.DatabaseName;
import java.util.Objects;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.TimeoutException;

public class PgAlterSequenceSample {

  static void pgAlterSequence() {
    // TODO(developer): Replace these variables before running the sample.
    final String projectId = "my-project";
    final String instanceId = "my-instance";
    final String databaseId = "my-database";
    pgAlterSequence(projectId, instanceId, databaseId);
  }

  static void pgAlterSequence(String projectId, String instanceId, String databaseId) {
    try (Spanner spanner =
        SpannerOptions.newBuilder().setProjectId(projectId).build().getService();
        DatabaseAdminClient databaseAdminClient = spanner.createDatabaseAdminClient()) {

      databaseAdminClient
          .updateDatabaseDdlAsync(
              DatabaseName.of(projectId, instanceId, databaseId),
              ImmutableList.of("ALTER SEQUENCE Seq SKIP RANGE 1000 5000000"))
          .get(5, TimeUnit.MINUTES);
      System.out.println(
          "Altered Seq sequence to skip an inclusive range between 1000 and 5000000");
      final DatabaseClient dbClient =
          spanner.getDatabaseClient(DatabaseId.of(projectId, instanceId, databaseId));
      Long insertCount =
          dbClient
              .readWriteTransaction()
              .run(
                  transaction -> {
                    try (ResultSet rs =
                        transaction.executeQuery(
                            Statement.of(
                                "INSERT INTO Customers (CustomerName) VALUES "
                                    + "('Lea'), ('Catalina'), ('Smith') RETURNING CustomerId"))) {
                      while (rs.next()) {
                        System.out.printf(
                            "Inserted customer record with CustomerId: %d\n", rs.getLong(0));
                      }
                      return Objects.requireNonNull(rs.getStats()).getRowCountExact();
                    }
                  });
      System.out.printf("Number of customer records inserted is: %d\n", insertCount);
    } catch (ExecutionException e) {
      // If the operation failed during execution, expose the cause.
      throw SpannerExceptionFactory.asSpannerException(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);
    } catch (TimeoutException e) {
      // If the operation timed out propagate the timeout
      throw SpannerExceptionFactory.propagateTimeout(e);
    }
  }
}

참고: Java용 이전 클라이언트 라이브러리 인터페이스 코드 샘플은 GitHub에 보관처리됩니다.

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

async function alterSequence(instanceId, databaseId) {
  // Gets a reference to a Cloud Spanner Database Admin Client object
  const databaseAdminClient = spanner.getDatabaseAdminClient();

  const request = ['ALTER SEQUENCE Seq SKIP RANGE 1000 5000000'];

  try {
    const [operation] = await databaseAdminClient.updateDatabaseDdl({
      database: databaseAdminClient.databasePath(
        projectId,
        instanceId,
        databaseId
      ),
      statements: request,
    });

    console.log('Waiting for operation to complete...');
    await operation.promise();

    console.log(
      'Altered Seq sequence to skip an inclusive range between 1000 and 5000000.'
    );
  } catch (err) {
    console.error('ERROR:', err);
  }

  // Gets a reference to a Cloud Spanner instance and database
  const instance = spanner.instance(instanceId);
  const database = instance.database(databaseId);

  database.runTransaction(async (err, transaction) => {
    if (err) {
      console.error(err);
      return;
    }
    try {
      const [rows, stats] = await transaction.run({
        sql: "INSERT INTO Customers (CustomerName) VALUES ('Lea'), ('Catalina'), ('Smith') RETURNING CustomerId",
      });

      rows.forEach(row => {
        console.log(
          `Inserted customer record with CustomerId: ${
            row.toJSON({wrapNumbers: true}).customerid.value
          }`
        );
      });

      const rowCount = Math.floor(stats[stats.rowCount]);
      console.log(`Number of customer records inserted is: ${rowCount}`);

      await transaction.commit();
    } catch (err) {
      console.error('ERROR:', err);
    } finally {
      // Close the spanner client when finished.
      // The databaseAdminClient does not require explicit closure. The closure of the Spanner client will automatically close the databaseAdminClient.
      spanner.close();
    }
  });
}
await alterSequence(instanceId, databaseId);

참고: Node.js용 이전 클라이언트 라이브러리 인터페이스 코드 샘플은 GitHub에 보관처리됩니다.

PHP

use Google\Cloud\Spanner\Admin\Database\V1\Client\DatabaseAdminClient;
use Google\Cloud\Spanner\Admin\Database\V1\UpdateDatabaseDdlRequest;
use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\Result;

/**
 * Alters a sequence.
 * Example:
 * ```
 * pg_alter_sequence($projectId, $instanceId, $databaseId);
 * ```
 *
 * @param string $projectId The Google Cloud project ID.
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function pg_alter_sequence(
    string $projectId,
    string $instanceId,
    string $databaseId
): void {
    $databaseAdminClient = new DatabaseAdminClient();
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);
    $transaction = $database->transaction();
    $databaseName = DatabaseAdminClient::databaseName($projectId, $instanceId, $databaseId);
    $statement = 'ALTER SEQUENCE Seq SKIP RANGE 1000 5000000';
    $request = new UpdateDatabaseDdlRequest([
        'database' => $databaseName,
        'statements' => [$statement]
    ]);

    $operation = $databaseAdminClient->updateDatabaseDdl($request);

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

    printf(
        'Altered Seq sequence to skip an inclusive range between 1000 and 5000000' .
        PHP_EOL
    );

    $res = $transaction->execute(
        'INSERT INTO Customers (CustomerName) VALUES ' .
        "('Lea'), ('Catalina'), ('Smith') RETURNING CustomerId"
    );
    $rows = $res->rows(Result::RETURN_ASSOCIATIVE);

    foreach ($rows as $row) {
        printf('Inserted customer record with CustomerId: %d %s',
            $row['customerid'],
            PHP_EOL
        );
    }
    $transaction->commit();

    printf(sprintf(
        'Number of customer records inserted is: %d %s',
        $res->stats()['rowCountExact'],
        PHP_EOL
    ));
}

참고: PHP용 이전 클라이언트 라이브러리 인터페이스 코드 샘플은 GitHub에 보관처리됩니다.

Python

def alter_sequence(instance_id, database_id):
    """Alters the Sequence and insert data"""

    from google.cloud.spanner_admin_database_v1.types import \
        spanner_database_admin

    spanner_client = spanner.Client()
    database_admin_api = spanner_client.database_admin_api

    request = spanner_database_admin.UpdateDatabaseDdlRequest(
        database=database_admin_api.database_path(
            spanner_client.project, instance_id, database_id
        ),
        statements=["ALTER SEQUENCE Seq SKIP RANGE 1000 5000000"],
    )
    operation = database_admin_api.update_database_ddl(request)

    print("Waiting for operation to complete...")
    operation.result(OPERATION_TIMEOUT_SECONDS)

    print(
        "Altered Seq sequence to skip an inclusive range between 1000 and 5000000 on database {} on instance {}".format(
            database_id, instance_id
        )
    )

    def insert_customers(transaction):
        results = transaction.execute_sql(
            "INSERT INTO Customers (CustomerName) VALUES "
            "('Lea'), "
            "('Cataline'), "
            "('Smith') "
            "RETURNING CustomerId"
        )
        for result in results:
            print("Inserted customer record with Customer Id: {}".format(*result))
        print(
            "Number of customer records inserted is {}".format(
                results.stats.row_count_exact
            )
        )

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

    database.run_in_transaction(insert_customers)

참고: Python용 이전 클라이언트 라이브러리 인터페이스 코드 샘플은 GitHub에 보관처리됩니다.

Ruby

require "google/cloud/spanner"

##
# This is a snippet for showcasing how to alter a sequence using postgresql.
#
# @param project_id  [String] The ID of the Google Cloud project.
# @param instance_id [String] The ID of the spanner instance.
# @param database_id [String] The ID of the database.
#
def spanner_postgresql_alter_sequence project_id:, instance_id:, database_id:
  db_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin

  database_path = db_admin_client.database_path project: project_id,
                                                instance: instance_id,
                                                database: database_id


  job = db_admin_client.update_database_ddl database: database_path, statements: [
    "ALTER SEQUENCE Seq SKIP RANGE 1000 5000000"
  ]

  puts "Waiting for operation to complete..."
  job.wait_until_done!
  puts "Altered Seq sequence to skip an inclusive range between 1000 and 5000000"
end

시퀀스 삭제하기

다음 코드 예시에서는 기본 키의 기본값에서 Seq 시퀀스를 삭제하도록 Customers 테이블을 변경한 후 Seq 시퀀스를 삭제합니다.

GoogleSQL

C++

void DropSequence(
    google::cloud::spanner_admin::DatabaseAdminClient admin_client,
    std::string const& project_id, std::string const& instance_id,
    std::string const& database_id) {
  google::cloud::spanner::Database database(project_id, instance_id,
                                            database_id);
  std::vector<std::string> statements;
  statements.emplace_back(R"""(
      ALTER TABLE Customers ALTER COLUMN CustomerId DROP DEFAULT
  )""");
  statements.emplace_back(R"""(
      DROP SEQUENCE Seq
  )""");
  auto metadata =
      admin_client.UpdateDatabaseDdl(database.FullName(), std::move(statements))
          .get();
  if (!metadata) throw std::move(metadata).status();
  std::cout << "Altered `Customers` table to"
            << " drop DEFAULT from `CustomerId` column,"
            << " and dropped the `Seq` sequence,"
            << " new DDL:\n"
            << metadata->DebugString();
}

C#

using Google.Cloud.Spanner.Admin.Database.V1;
using Google.Cloud.Spanner.Common.V1;
using System;
using System.Threading.Tasks;

public class DropSequenceSample
{
    public async Task DropSequenceSampleAsync(string projectId, string instanceId, string databaseId)
    {
        DatabaseAdminClient databaseAdminClient = DatabaseAdminClient.Create();

        DatabaseName databaseName = DatabaseName.FromProjectInstanceDatabase(projectId, instanceId, databaseId);
        string[] statements =
        {
            "ALTER TABLE Customers ALTER COLUMN CustomerId DROP DEFAULT",
            "DROP SEQUENCE Seq"
        };
        var operation = await databaseAdminClient.UpdateDatabaseDdlAsync(databaseName, statements);

        var completedResponse = await operation.PollUntilCompletedAsync();
        if (completedResponse.IsFaulted)
        {
            throw completedResponse.Exception;
        }
        Console.WriteLine("Altered Customers table to drop DEFAULT from CustomerId column and dropped the Seq sequence");
    }
}

Go

import (
	"context"
	"fmt"
	"io"

	database "cloud.google.com/go/spanner/admin/database/apiv1"
	adminpb "cloud.google.com/go/spanner/admin/database/apiv1/databasepb"
)

func dropSequence(w io.Writer, db string) error {
	// db := "projects/my-project/instances/my-instance/databases/my-database"
	ctx := context.Background()
	adminClient, err := database.NewDatabaseAdminClient(ctx)
	if err != nil {
		return err
	}
	defer adminClient.Close()

	// List of DDL statements to be applied to the database.
	// Drop the DEFAULT from CustomerId column and drop the sequence.
	ddl := []string{
		"ALTER TABLE Customers ALTER COLUMN CustomerId DROP DEFAULT",
		"DROP SEQUENCE Seq",
	}
	op, err := adminClient.UpdateDatabaseDdl(ctx, &adminpb.UpdateDatabaseDdlRequest{
		Database:   db,
		Statements: ddl,
	})
	if err != nil {
		return err
	}
	// Wait for the UpdateDatabaseDdl operation to finish.
	if err := op.Wait(ctx); err != nil {
		return fmt.Errorf("waiting for bit reverse sequence drop to finish failed: %w", err)
	}
	fmt.Fprintf(w, "Altered Customers table to drop DEFAULT from CustomerId column and dropped the Seq sequence\n")
	return nil
}

자바

import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerExceptionFactory;
import com.google.cloud.spanner.SpannerOptions;
import com.google.cloud.spanner.admin.database.v1.DatabaseAdminClient;
import com.google.common.collect.ImmutableList;
import com.google.spanner.admin.database.v1.DatabaseName;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.TimeoutException;

public class DropSequenceSample {

  static void dropSequence() {
    // TODO(developer): Replace these variables before running the sample.
    final String projectId = "my-project";
    final String instanceId = "my-instance";
    final String databaseId = "my-database";
    dropSequence(projectId, instanceId, databaseId);
  }

  static void dropSequence(String projectId, String instanceId, String databaseId) {
    try (Spanner spanner =
        SpannerOptions.newBuilder().setProjectId(projectId).build().getService();
        DatabaseAdminClient databaseAdminClient = spanner.createDatabaseAdminClient()) {
      databaseAdminClient
          .updateDatabaseDdlAsync(DatabaseName.of(projectId, instanceId, databaseId),
              ImmutableList.of(
                  "ALTER TABLE Customers ALTER COLUMN CustomerId DROP DEFAULT",
                  "DROP SEQUENCE Seq"))
          .get(5, TimeUnit.MINUTES);
      System.out.println(
          "Altered Customers table to drop DEFAULT from CustomerId column "
              + "and dropped the Seq sequence");
    } catch (ExecutionException e) {
      // If the operation failed during execution, expose the cause.
      throw SpannerExceptionFactory.asSpannerException(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);
    } catch (TimeoutException e) {
      // If the operation timed out propagate the timeout
      throw SpannerExceptionFactory.propagateTimeout(e);
    }
  }
}

참고: Java용 이전 클라이언트 라이브러리 인터페이스 코드 샘플은 GitHub에 보관처리됩니다.

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

async function dropSequence(instanceId, databaseId) {
  // Gets a reference to a Cloud Spanner Database Admin Client object
  const databaseAdminClient = spanner.getDatabaseAdminClient();

  const request = [
    'ALTER TABLE Customers ALTER COLUMN CustomerId DROP DEFAULT',
    'DROP SEQUENCE Seq',
  ];

  // Drop sequence from DDL
  try {
    const [operation] = await databaseAdminClient.updateDatabaseDdl({
      database: databaseAdminClient.databasePath(
        projectId,
        instanceId,
        databaseId
      ),
      statements: request,
    });

    console.log('Waiting for operation to complete...');
    await operation.promise();

    console.log(
      'Altered Customers table to drop DEFAULT from CustomerId column and dropped the Seq sequence.'
    );
  } catch (err) {
    console.error('ERROR:', err);
  } finally {
    // Close the spanner client when finished.
    // The databaseAdminClient does not require explicit closure. The closure of the Spanner client will automatically close the databaseAdminClient.
    spanner.close();
  }
}
await dropSequence(instanceId, databaseId);

참고: Node.js용 이전 클라이언트 라이브러리 인터페이스 코드 샘플은 GitHub에 보관처리됩니다.

PHP

use Google\Cloud\Spanner\Admin\Database\V1\Client\DatabaseAdminClient;
use Google\Cloud\Spanner\Admin\Database\V1\UpdateDatabaseDdlRequest;

/**
 * Drops a sequence.
 * Example:
 * ```
 * drop_sequence($projectId, $instanceId, $databaseId);
 * ```
 *
 * @param string $projectId The Google Cloud project ID.
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function drop_sequence(
    string $projectId,
    string $instanceId,
    string $databaseId
): void {
    $databaseAdminClient = new DatabaseAdminClient();
    $databaseName = DatabaseAdminClient::databaseName($projectId, $instanceId, $databaseId);

    $request = new UpdateDatabaseDdlRequest([
        'database' => $databaseName,
        'statements' => [
            'ALTER TABLE Customers ALTER COLUMN CustomerId DROP DEFAULT',
            'DROP SEQUENCE Seq'
        ]
    ]);

    $operation = $databaseAdminClient->updateDatabaseDdl($request);

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

    printf(
        'Altered Customers table to drop DEFAULT from CustomerId ' .
        'column and dropped the Seq sequence' .
        PHP_EOL
    );
}

참고: PHP용 이전 클라이언트 라이브러리 인터페이스 코드 샘플은 GitHub에 보관처리됩니다.

Python

def drop_sequence(instance_id, database_id):
    """Drops the Sequence"""

    from google.cloud.spanner_admin_database_v1.types import spanner_database_admin

    spanner_client = spanner.Client()
    database_admin_api = spanner_client.database_admin_api

    request = spanner_database_admin.UpdateDatabaseDdlRequest(
        database=database_admin_api.database_path(
            spanner_client.project, instance_id, database_id
        ),
        statements=[
            "ALTER TABLE Customers ALTER COLUMN CustomerId DROP DEFAULT",
            "DROP SEQUENCE Seq",
        ],
    )

    operation = database_admin_api.update_database_ddl(request)

    print("Waiting for operation to complete...")
    operation.result(OPERATION_TIMEOUT_SECONDS)

    print(
        "Altered Customers table to drop DEFAULT from CustomerId column and dropped the Seq sequence on database {} on instance {}".format(
            database_id, instance_id
        )
    )

참고: Python용 이전 클라이언트 라이브러리 인터페이스 코드 샘플은 GitHub에 보관처리됩니다.

Ruby

require "google/cloud/spanner"

##
# This is a snippet for showcasing how to drop a sequence.
#
# @param project_id  [String] The ID of the Google Cloud project.
# @param instance_id [String] The ID of the spanner instance.
# @param database_id [String] The ID of the database.
#
def spanner_drop_sequence project_id:, instance_id:, database_id:
  db_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin

  database_path = db_admin_client.database_path project: project_id,
                                                instance: instance_id,
                                                database: database_id


  job = db_admin_client.update_database_ddl database: database_path, statements: [
    "ALTER TABLE Customers ALTER COLUMN CustomerId DROP DEFAULT",
    "DROP SEQUENCE Seq"
  ]

  puts "Waiting for operation to complete..."
  job.wait_until_done!
  puts "Altered Customers table to drop DEFAULT from CustomerId column and dropped the Seq sequence"
end

PostgreSQL

C++

void DropSequence(
    google::cloud::spanner_admin::DatabaseAdminClient admin_client,
    google::cloud::spanner::Database const& database) {
  std::vector<std::string> statements;
  statements.emplace_back(R"""(
      ALTER TABLE Customers ALTER COLUMN CustomerId DROP DEFAULT
  )""");
  statements.emplace_back(R"""(
      DROP SEQUENCE Seq
  )""");
  auto metadata =
      admin_client.UpdateDatabaseDdl(database.FullName(), std::move(statements))
          .get();
  if (!metadata) throw std::move(metadata).status();
  std::cout << "Altered `Customers` table to"
            << " drop DEFAULT from `CustomerId` column,"
            << " and dropped the `Seq` sequence,"
            << " new DDL:\n"
            << metadata->DebugString();
}

C#

using Google.Cloud.Spanner.Admin.Database.V1;
using Google.Cloud.Spanner.Common.V1;
using System;
using System.Threading.Tasks;

public class DropSequenceSample
{
    public async Task DropSequenceSampleAsync(string projectId, string instanceId, string databaseId)
    {
        DatabaseAdminClient databaseAdminClient = DatabaseAdminClient.Create();

        DatabaseName databaseName = DatabaseName.FromProjectInstanceDatabase(projectId, instanceId, databaseId);
        string[] statements =
        {
            "ALTER TABLE Customers ALTER COLUMN CustomerId DROP DEFAULT",
            "DROP SEQUENCE Seq"
        };
        var operation = await databaseAdminClient.UpdateDatabaseDdlAsync(databaseName, statements);

        var completedResponse = await operation.PollUntilCompletedAsync();
        if (completedResponse.IsFaulted)
        {
            throw completedResponse.Exception;
        }
        Console.WriteLine("Altered Customers table to drop DEFAULT from CustomerId column and dropped the Seq sequence");
    }
}

Go

import (
	"context"
	"fmt"
	"io"

	database "cloud.google.com/go/spanner/admin/database/apiv1"
	adminpb "cloud.google.com/go/spanner/admin/database/apiv1/databasepb"
)

func dropSequence(w io.Writer, db string) error {
	// db := "projects/my-project/instances/my-instance/databases/my-database"
	ctx := context.Background()
	adminClient, err := database.NewDatabaseAdminClient(ctx)
	if err != nil {
		return err
	}
	defer adminClient.Close()

	// List of DDL statements to be applied to the database.
	// Drop the DEFAULT from CustomerId column and drop the sequence.
	ddl := []string{
		"ALTER TABLE Customers ALTER COLUMN CustomerId DROP DEFAULT",
		"DROP SEQUENCE Seq",
	}
	op, err := adminClient.UpdateDatabaseDdl(ctx, &adminpb.UpdateDatabaseDdlRequest{
		Database:   db,
		Statements: ddl,
	})
	if err != nil {
		return err
	}
	// Wait for the UpdateDatabaseDdl operation to finish.
	if err := op.Wait(ctx); err != nil {
		return fmt.Errorf("waiting for bit reverse sequence drop to finish failed: %w", err)
	}
	fmt.Fprintf(w, "Altered Customers table to drop DEFAULT from CustomerId column and dropped the Seq sequence\n")
	return nil
}

자바

import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerExceptionFactory;
import com.google.cloud.spanner.SpannerOptions;
import com.google.cloud.spanner.admin.database.v1.DatabaseAdminClient;
import com.google.common.collect.ImmutableList;
import com.google.spanner.admin.database.v1.DatabaseName;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.TimeoutException;

public class PgDropSequenceSample {

  static void pgDropSequence() {
    // TODO(developer): Replace these variables before running the sample.
    final String projectId = "my-project";
    final String instanceId = "my-instance";
    final String databaseId = "my-database";
    pgDropSequence(projectId, instanceId, databaseId);
  }

  static void pgDropSequence(String projectId, String instanceId, String databaseId) {

    try (Spanner spanner =
        SpannerOptions.newBuilder().setProjectId(projectId).build().getService();
        DatabaseAdminClient databaseAdminClient = spanner.createDatabaseAdminClient()) {
      databaseAdminClient
          .updateDatabaseDdlAsync(
              DatabaseName.of(projectId, instanceId, databaseId),
              ImmutableList.of(
                  "ALTER TABLE Customers ALTER COLUMN CustomerId DROP DEFAULT",
                  "DROP SEQUENCE Seq"))
          .get(5, TimeUnit.MINUTES);
      System.out.println(
          "Altered Customers table to drop DEFAULT from "
              + "CustomerId column and dropped the Seq sequence");
    } catch (ExecutionException e) {
      // If the operation failed during execution, expose the cause.
      throw SpannerExceptionFactory.asSpannerException(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);
    } catch (TimeoutException e) {
      // If the operation timed out propagate the timeout
      throw SpannerExceptionFactory.propagateTimeout(e);
    }
  }
}

참고: Java용 이전 클라이언트 라이브러리 인터페이스 코드 샘플은 GitHub에 보관처리됩니다.

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

async function dropSequence(instanceId, databaseId) {
  // Gets a reference to a Cloud Spanner Database Admin Client object
  const databaseAdminClient = spanner.getDatabaseAdminClient();

  const request = [
    'ALTER TABLE Customers ALTER COLUMN CustomerId DROP DEFAULT',
    'DROP SEQUENCE Seq',
  ];

  // Drop sequence from DDL
  try {
    const [operation] = await databaseAdminClient.updateDatabaseDdl({
      database: databaseAdminClient.databasePath(
        projectId,
        instanceId,
        databaseId
      ),
      statements: request,
    });

    console.log('Waiting for operation to complete...');
    await operation.promise();

    console.log(
      'Altered Customers table to drop DEFAULT from CustomerId column and dropped the Seq sequence.'
    );
  } catch (err) {
    console.error('ERROR:', err);
  } finally {
    // Close the spanner client when finished.
    // The databaseAdminClient does not require explicit closure. The closure of the Spanner client will automatically close the databaseAdminClient.
    spanner.close();
  }
}
await dropSequence(instanceId, databaseId);

참고: Node.js용 이전 클라이언트 라이브러리 인터페이스 코드 샘플은 GitHub에 보관처리됩니다.

PHP

use Google\Cloud\Spanner\Admin\Database\V1\Client\DatabaseAdminClient;
use Google\Cloud\Spanner\Admin\Database\V1\UpdateDatabaseDdlRequest;

/**
 * Drops a sequence.
 * Example:
 * ```
 * pg_drop_sequence($instanceId, $databaseId);
 * ```
 *
 * @param string $projectId Your Google Cloud project ID.
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function pg_drop_sequence(
    string $projectId,
    string $instanceId,
    string $databaseId
): void {
    $databaseAdminClient = new DatabaseAdminClient();
    $databaseName = DatabaseAdminClient::databaseName($projectId, $instanceId, $databaseId);
    $statements = [
        'ALTER TABLE Customers ALTER COLUMN CustomerId DROP DEFAULT',
        'DROP SEQUENCE Seq'
    ];

    $request = new UpdateDatabaseDdlRequest([
        'database' => $databaseName,
        'statements' => $statements
    ]);

    $operation = $databaseAdminClient->updateDatabaseDdl($request);

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

    printf(
        'Altered Customers table to drop DEFAULT from CustomerId ' .
        'column and dropped the Seq sequence' .
        PHP_EOL
    );
}

참고: PHP용 이전 클라이언트 라이브러리 인터페이스 코드 샘플은 GitHub에 보관처리됩니다.

Python

def drop_sequence(instance_id, database_id):
    """Drops the Sequence"""

    from google.cloud.spanner_admin_database_v1.types import \
        spanner_database_admin

    spanner_client = spanner.Client()
    database_admin_api = spanner_client.database_admin_api

    request = spanner_database_admin.UpdateDatabaseDdlRequest(
        database=database_admin_api.database_path(
            spanner_client.project, instance_id, database_id
        ),
        statements=[
            "ALTER TABLE Customers ALTER COLUMN CustomerId DROP DEFAULT",
            "DROP SEQUENCE Seq",
        ],
    )
    operation = database_admin_api.update_database_ddl(request)

    print("Waiting for operation to complete...")
    operation.result(OPERATION_TIMEOUT_SECONDS)

    print(
        "Altered Customers table to drop DEFAULT from CustomerId column and dropped the Seq sequence on database {} on instance {}".format(
            database_id, instance_id
        )
    )

참고: Python용 이전 클라이언트 라이브러리 인터페이스 코드 샘플은 GitHub에 보관처리됩니다.

Ruby

require "google/cloud/spanner"

##
# This is a snippet for showcasing how to drop a sequence using postgresql.
#
# @param project_id  [String] The ID of the Google Cloud project.
# @param instance_id [String] The ID of the spanner instance.
# @param database_id [String] The ID of the database.
#
def spanner_postgresql_drop_sequence project_id:, instance_id:, database_id:
  db_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin

  database_path = db_admin_client.database_path project: project_id,
                                                instance: instance_id,
                                                database: database_id


  job = db_admin_client.update_database_ddl database: database_path, statements: [
    "ALTER TABLE Customers ALTER COLUMN CustomerId DROP DEFAULT",
    "DROP SEQUENCE Seq"
  ]

  puts "Waiting for operation to complete..."
  job.wait_until_done!
  puts "Altered Customers table to drop DEFAULT from CustomerId column and dropped the Seq sequence"
end