Cette page explique comment créer, modifier et supprimer une séquence dans Spanner à l'aide d'instructions LDD (langage de définition de données). Vous pouvez également découvrir comment utiliser une séquence dans une valeur par défaut pour renseigner une colonne de clé primaire.
Consultez la documentation de référence complète sur la syntaxe LDD des séquences pour les bases de données de dialecte GoogleSQL et les bases de données de dialecte PostgreSQL.
Configurer les options de séquence
Lorsque vous créez ou modifiez une séquence, vous pouvez configurer son comportement à l'aide des options de séquence. En GoogleSQL, utilisez la clause OPTIONS. Dans PostgreSQL, utilisez des clauses standards. Les paramètres compatibles incluent les suivants :
sequence_kind: type de séquence. Le seul type valide estbit_reversed_positive.start_with_counter: commence le compteur interne de la séquence avec unINT64positif pour GoogleSQL oubigintpour PostgreSQL.skip_range_minetskip_range_max: spécifie une plage de valeurs à ignorer, inclusivement. Vous pouvez définir l'un de ces paramètres ou les deux.
Par GoogleSQL, la commande suivante crée une séquence GoogleSQL standard qui utilise ces options :
CREATE SEQUENCE IF NOT EXISTS customer_garage_seq_seed OPTIONS (
sequence_kind = 'bit_reversed_positive',
start_with_counter = 1000,
skip_range_min = 100,
skip_range_max = 200);
Créer une séquence
L'exemple de code suivant crée une séquence Seq, l'utilise dans la valeur par défaut de la clé primaire de la table Customers et insère trois nouvelles lignes dans la table 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
}
Java
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);
}
}
}
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);
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
));
}
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)
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"
endPostgreSQL
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
}
Java
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);
}
}
}
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);
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
));
}
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)
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"
endModifier une séquence
L'exemple de code suivant modifie la séquence Seq pour ignorer une plage de valeurs comprise entre 1 000 et 5 millions. Il insère ensuite trois nouvelles lignes dans la table 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
}
Java
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);
}
}
}
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);
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
));
}
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)
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"
endPostgreSQL
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
}
Java
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);
}
}
}
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);
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
));
}
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)
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"
endSupprimer une séquence
L'exemple de code suivant modifie la table Customers pour supprimer la séquence Seq de la valeur par défaut de la clé primaire, puis supprime la séquence 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
}
Java
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);
}
}
}
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);
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
);
}
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
)
)
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"
endPostgreSQL
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
}
Java
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);
}
}
}
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);
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
);
}
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
)
)
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"
endUtiliser des séquences avec le partitionnement géographique
Vous pouvez utiliser des séquences Spanner pour générer automatiquement des clés primaires pour les tables d'emplacement et les tables entrelacées. Cette fonctionnalité est indépendante de la structure sous-jacente de la table (standard ou géopartitionnée). Pour en savoir plus, consultez l'article Simplifier les bonnes pratiques à grande échelle avec des clés générées automatiquement dans Spanner.
Si vous utilisez le partitionnement géographique, nous vous recommandons d'utiliser des UUID plutôt que des séquences si la latence de queue des insertions est un problème pour votre application. Pour en savoir plus sur la latence, consultez la section Latence de ce document. Bien que les séquences Spanner soient idéales pour migrer des schémas ou du code d'application existants, les clés UUID sont recommandées pour les applications en raison de leur interopérabilité et de leur flexibilité.
Le processus d'application des séquences est le même pour les tables standards et les tables géopartitionnées. Pour illustrer comment les séquences peuvent faciliter la génération automatique de colonnes à l'aide du partitionnement géographique, prenons l'exemple suivant :
Exemple
Spanner n'accepte que les séquences par bit inversé, qui évitent les hotspots lors de l'attribution de parties de clé.
L'exemple suivant crée une séquence appelée UserIdSequence, crée une table appelée Locations avec une clé d'emplacement et crée une table enfant appelée Users entrelacée dans Locations, où la colonne UserId utilise la séquence pour sa valeur par défaut :
GoogleSQL
-- Sequence for generating unique UserIds
CREATE SEQUENCE UserIdSequence
OPTIONS (
sequence_kind = 'bit_reversed_positive'
);
CREATE TABLE Locations (
Location STRING(MAX) NOT NULL PLACEMENT KEY
) PRIMARY KEY (Location);
CREATE TABLE Users (
Location STRING(MAX) NOT NULL,
UserId INT64 NOT NULL DEFAULT (GET_NEXT_SEQUENCE_VALUE(SEQUENCE UserIdSequence)),
Name STRING(MAX),
Email STRING(MAX)
) PRIMARY KEY (Location, UserId),
INTERLEAVE IN PARENT Locations ON DELETE CASCADE;
PostgreSQL
-- Sequence for generating unique UserIds
CREATE SEQUENCE UserIdSequence BIT_REVERSED_POSITIVE;
CREATE TABLE Locations (
Location varchar(1024) NOT NULL PLACEMENT KEY,
PRIMARY KEY (Location)
);
CREATE TABLE Users (
Location varchar(1024) NOT NULL,
UserId bigint NOT NULL DEFAULT nextval('UserIdSequence'),
Name text,
Email text,
PRIMARY KEY (Location, UserId)
) INTERLEAVE IN PARENT Locations ON DELETE CASCADE;
Lorsque vous insérez une ligne sans spécifier UserId, Spanner récupère la valeur unique suivante de UserIdSequence et l'utilise pour UserId. Vous pouvez également utiliser la clause THEN RETURN (GoogleSQL) ou la clause RETURNING (PostgreSQL) dans l'instruction INSERT pour récupérer le UserId généré :
GoogleSQL
INSERT INTO Users (Location, Name, Email)
VALUES ('us', 'John Doe', 'john@example.com')
THEN RETURN UserId;
PostgreSQL
INSERT INTO Users (Location, Name, Email)
VALUES ('us', 'John Doe', 'john@example.com')
RETURNING UserId;
Latence
Lorsque vous insérez des valeurs dans des tables partitionnées géographiquement à l'aide de séquences, vous pouvez entraîner une latence élevée en raison d'une coordination occasionnelle avec les tables système de l'emplacement par défaut, qui stocke le compteur de séquence. L'augmentation exacte de la latence de queue dépend de la distance entre le client et la partition par défaut, qui peut être de plusieurs centaines de millisecondes (par exemple, si le client se trouve en Europe et que la partition par défaut se trouve aux États-Unis).