Aufteilungspunkte erstellen und verwalten

import (
	"context"
	"fmt"
	"io"
	"time"

	database "cloud.google.com/go/spanner/admin/database/apiv1"
	"cloud.google.com/go/spanner/admin/database/apiv1/databasepb"
	adminpb "cloud.google.com/go/spanner/admin/database/apiv1/databasepb"
	"google.golang.org/protobuf/types/known/structpb"
	"google.golang.org/protobuf/types/known/timestamppb"
)

// Adds split points to table and index
// AddSplitPoins API - https://pkg.go.dev/cloud.google.com/go/spanner/admin/database/apiv1#DatabaseAdminClient.AddSplitPoints
func addSplitpoints(w io.Writer, dbName string) error {
	ctx := context.Background()

	dbAdminClient, err := database.NewDatabaseAdminClient(ctx)
	if err != nil {
		return err
	}
	defer dbAdminClient.Close()

	// Database is assumed to exist - https://cloud.google.com/spanner/docs/getting-started/go#create_a_database
	// Singers table is assumed to be present
	ddl := []string{
		"CREATE INDEX IF NOT EXISTS SingersByFirstLastName ON Singers(FirstName, LastName)",
	}
	op, err := dbAdminClient.UpdateDatabaseDdl(ctx, &adminpb.UpdateDatabaseDdlRequest{
		Database:   dbName,
		Statements: ddl,
	})

	if err != nil {
		return fmt.Errorf("addSplitPoints: waiting for UpdateDatabaseDdlRequest failed: %w", err)
	}

	// Wait for the UpdateDatabaseDdl operation to finish.
	if err := op.Wait(ctx); err != nil {
		return fmt.Errorf("addSplitPoints: waiting for UpdateDatabaseDdlRequest to finish failed: %w", err)
	}
	fmt.Fprintf(w, "Added indexes for Split testing\n")

	splitTableKey := databasepb.SplitPoints_Key{
		KeyParts: &structpb.ListValue{
			Values: []*structpb.Value{
				structpb.NewStringValue("42"),
			},
		},
	}

	splitForTable := databasepb.SplitPoints{
		Table: "Singers",
		Keys:  []*databasepb.SplitPoints_Key{&splitTableKey},
	}

	splitIndexKey := databasepb.SplitPoints_Key{
		KeyParts: &structpb.ListValue{
			Values: []*structpb.Value{
				structpb.NewStringValue("John"),
				structpb.NewStringValue("Doe"),
			},
		},
	}

	splitForindex := databasepb.SplitPoints{
		Index: "SingersByFirstLastName",
		Keys:  []*databasepb.SplitPoints_Key{&splitIndexKey},
	}

	splitIndexKeyWithTableKeyPart := databasepb.SplitPoints_Key{
		KeyParts: &structpb.ListValue{
			Values: []*structpb.Value{
				structpb.NewStringValue("38"),
			},
		},
	}

	splitIndexKeyWithIndexKeyPart := databasepb.SplitPoints_Key{
		KeyParts: &structpb.ListValue{
			Values: []*structpb.Value{
				structpb.NewStringValue("Jane"),
				structpb.NewStringValue("Doe"),
			},
		},
	}

	// the index key part is first and table keypart is second in the split definition
	splitForindexWithTableKey := databasepb.SplitPoints{
		Index: "SingersByFirstLastName",
		Keys:  []*databasepb.SplitPoints_Key{&splitIndexKeyWithIndexKeyPart, &splitIndexKeyWithTableKeyPart},
	}

	splitTableKeyWithExpire := databasepb.SplitPoints_Key{
		KeyParts: &structpb.ListValue{
			Values: []*structpb.Value{
				structpb.NewStringValue("30"),
			},
		},
	}

	splitForTableWithExpire := databasepb.SplitPoints{
		Table: "Singers",
		Keys:  []*databasepb.SplitPoints_Key{&splitTableKeyWithExpire},
		// A timestamp in the past means immediate expiration.
		// The maximum value can be 30 days in the future.
		// Defaults to 10 days in the future if not specified.
		//
		// Setting the expiration time to next day
		ExpireTime: timestamppb.New(time.Now().Add(24 * time.Hour)),
	}

	// Add split points to table and index
	req := databasepb.AddSplitPointsRequest{
		Database:    dbName,
		SplitPoints: []*databasepb.SplitPoints{&splitForTable, &splitForindex, &splitForindexWithTableKey, &splitForTableWithExpire},
	}

	res, err := dbAdminClient.AddSplitPoints(ctx, &req)
	if err != nil {
		return fmt.Errorf("addSplitpoints: failed to add split points: %w", err)
	}

	fmt.Fprintf(w, "Added split points %s", res)
	return nil
}

import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerException;
import com.google.cloud.spanner.SpannerOptions;
import com.google.cloud.spanner.admin.database.v1.DatabaseAdminClient;
import com.google.protobuf.ListValue;
import com.google.protobuf.Value;
import com.google.spanner.admin.database.v1.DatabaseName;
import com.google.spanner.admin.database.v1.SplitPoints;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class DatabaseAddSplitPointsSample {

  /***
   * Assume DDL for the underlying database:
   * <pre>{@code
   * CREATE TABLE Singers (
   * SingerId INT64 NOT NULL,
   * FirstName STRING(1024),
   * LastName STRING(1024),
   *  SingerInfo BYTES(MAX),
   * ) PRIMARY KEY(SingerId);
   *
   *
   * CREATE INDEX SingersByFirstLastName ON Singers(FirstName, LastName);
   * }</pre>
   */

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

  static void addSplitPoints(String projectId, String instanceId, String databaseId)
      throws IOException {
    try (Spanner spanner =
            SpannerOptions.newBuilder().setProjectId(projectId).build().getService();
        DatabaseAdminClient databaseAdminClient = spanner.createDatabaseAdminClient()) {
      List<com.google.spanner.admin.database.v1.SplitPoints> splitPoints = new ArrayList<>();

      // table key
      com.google.spanner.admin.database.v1.SplitPoints splitPointForTable =
          SplitPoints.newBuilder()
              .setTable("Singers")
              .addKeys(
                  com.google.spanner.admin.database.v1.SplitPoints.Key.newBuilder()
                      .setKeyParts(
                          ListValue.newBuilder()
                              .addValues(Value.newBuilder().setStringValue("42").build())
                              .build()))
              .build();

      // index key without table key part
      com.google.spanner.admin.database.v1.SplitPoints splitPointForIndex =
          SplitPoints.newBuilder()
              .setIndex("SingersByFirstLastName")
              .addKeys(
                  com.google.spanner.admin.database.v1.SplitPoints.Key.newBuilder()
                      .setKeyParts(
                          ListValue.newBuilder()
                              .addValues(Value.newBuilder().setStringValue("John").build())
                              .addValues(Value.newBuilder().setStringValue("Doe").build())
                              .build()))
              .build();

      // index key with table key part, first key is the index key and second is the table key
      com.google.spanner.admin.database.v1.SplitPoints splitPointForIndexWitTableKey =
          SplitPoints.newBuilder()
              .setIndex("SingersByFirstLastName")
              .addKeys(
                  com.google.spanner.admin.database.v1.SplitPoints.Key.newBuilder()
                      .setKeyParts(
                          ListValue.newBuilder()
                              .addValues(Value.newBuilder().setStringValue("Jane").build())
                              .addValues(Value.newBuilder().setStringValue("Doe").build())
                              .build()))
              .addKeys(
                  com.google.spanner.admin.database.v1.SplitPoints.Key.newBuilder()
                      .setKeyParts(
                          ListValue.newBuilder()
                              .addValues(Value.newBuilder().setStringValue("38").build())
                              .build()))
              .build();

      splitPoints.add(splitPointForTable);
      splitPoints.add(splitPointForIndex);
      splitPoints.add(splitPointForIndexWitTableKey);
      databaseAdminClient.addSplitPoints(
          DatabaseName.of(projectId, instanceId, databaseId), splitPoints);

    } catch (Exception e) {
      // If the operation failed during execution, expose the cause.
      throw (SpannerException) e.getCause();
    }
  }
}

// Import the Google Cloud client library for Spanner.
const {Spanner} = require('@google-cloud/spanner');

/**
 * TODO(developer): Uncomment these variables before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance-id';
// const databaseId = 'my-database-id';

// Create a Spanner database admin client.
const spanner = new Spanner({projectId});
const databaseAdminClient = spanner.getDatabaseAdminClient();

try {
  // Add split points to table and index
  // first is a table level split that takes table primary key value
  // second is index level split with index key parts
  // third is index level split having index key part and table key part
  // Assume the following table and index structure
  // CREATE TABLE Singers (
  // SingerId INT64 NOT NULL,
  // FirstName STRING(1024),
  // LastName STRING(1024),
  // SingerInfo BYTES(MAX),
  // ) PRIMARY KEY(SingerId);
  //
  // CREATE INDEX SingersByFirstLastName ON Singers(FirstName, LastName);
  const request = [
    'CREATE INDEX IF NOT EXISTS SingersByFirstLastName ON Singers(FirstName, LastName)',
  ];

  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('Added the SingersByFirstLastName index.');

  databaseAdminClient.addSplitPoints({
    database: databaseAdminClient.databasePath(
      projectId,
      instanceId,
      databaseId,
    ),
    splitPoints: [
      {
        table: 'Singers',
        keys: [{keyParts: {values: [{stringValue: '42'}]}}],
      },
      {
        index: 'SingersByFirstLastName',
        keys: [
          {
            keyParts: {
              values: [{stringValue: 'John'}, {stringValue: 'Doe'}],
            },
          },
        ],
      },
      {
        index: 'SingersByFirstLastName',
        keys: [
          {
            keyParts: {
              values: [{stringValue: 'Jane'}, {stringValue: 'Doe'}],
            },
          },
          {keyParts: {values: [{stringValue: '38'}]}},
        ],
      },
    ],
  });
  console.log('Added Split Points');
} catch (err) {
  console.error('ERROR:', err);
}

def add_split_points(instance_id, database_id):
    """Adds split points to table and index."""

    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 INDEX IF NOT EXISTS SingersByFirstLastName ON Singers(FirstName, LastName)"],
    )

    operation = database_admin_api.update_database_ddl(request)

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

    print("Added the SingersByFirstLastName index.")

    addSplitPointRequest = spanner_database_admin.AddSplitPointsRequest(
        database=database_admin_api.database_path(
            spanner_client.project, instance_id, database_id
        ),
        # Table split
        # Index split without table key part
        # Index split with table key part: first key is the index key and second the table key
        split_points=[
            spanner_database_admin.SplitPoints(
                table="Singers",
                keys=[
                    spanner_database_admin.SplitPoints.Key(
                        key_parts=struct_pb2.ListValue(
                            values=[struct_pb2.Value(string_value="42")]
                        )
                    )
                ],
            ),
            spanner_database_admin.SplitPoints(
                index="SingersByFirstLastName",
                keys=[
                    spanner_database_admin.SplitPoints.Key(
                        key_parts=struct_pb2.ListValue(
                            values=[
                                struct_pb2.Value(string_value="John"),
                                struct_pb2.Value(string_value="Doe"),
                            ]
                        )
                    )
                ],
            ),
            spanner_database_admin.SplitPoints(
                index="SingersByFirstLastName",
                keys=[
                    spanner_database_admin.SplitPoints.Key(
                        key_parts=struct_pb2.ListValue(
                            values=[
                                struct_pb2.Value(string_value="Jane"),
                                struct_pb2.Value(string_value="Doe"),
                            ]
                        )
                    ),
                    spanner_database_admin.SplitPoints.Key(
                        key_parts=struct_pb2.ListValue(
                            values=[struct_pb2.Value(string_value="38")]
                        )
                    ),

                ],
            ),
        ],
    )

    operation = database_admin_api.add_split_points(addSplitPointRequest)

    print("Added split points.")