cloud spannerの練習

README.md

Run with handy-spanner

$ git clone git@github.com:gcpug/handy-spanner.git
$ docker build -t handy-spanner handy-spanner
$ docker run --rm -it -p 9999:9999 handy-spanner
$ go build -o  clound_spanner_practice  clound_spanner_practice.go
$ SPANNER_EMULATOR_HOST=localhost:9999 ./ clound_spanner_practice

clound_spanner_practice.go

package main

import (
	"context"
	"fmt"
	"log"
	"os"

	"google.golang.org/grpc"

	"cloud.google.com/go/spanner"
	admindb "cloud.google.com/go/spanner/admin/database/apiv1"
	"google.golang.org/api/iterator"
	"google.golang.org/api/option"
	adminpb "google.golang.org/genproto/googleapis/spanner/admin/database/v1"
)

func main() {
	ctx := context.Background()

	// This database must exist.
	databaseName := "projects/your-project-id/instances/your-instance-id/databases/your-database-id"

	client, err := spanner.NewClient(ctx, databaseName)
	if err != nil {
		log.Fatalf("Failed to create client %v", err)
	}
	defer client.Close()

	adminClient, err := newAdminClient(ctx)
	if err != nil {
		log.Fatalf("Failed to create admin client %v", err)
	}
	defer adminClient.Close()

	// Create table
	{
		op, err := adminClient.UpdateDatabaseDdl(ctx, &adminpb.UpdateDatabaseDdlRequest{
			Database: databaseName,
			Statements: []string{
				`CREATE TABLE Singers (
					SingerId   INT64 NOT NULL,
					FirstName  STRING(1024),
					LastName   STRING(1024),
					SingerInfo BYTES(MAX)
				) PRIMARY KEY (SingerId)`,
				`CREATE INDEX SingersByFirstName ON Singers(FirstName)`},
		})
		if err != nil {
			log.Fatal(err)
		}
		if err := op.Wait(ctx); err != nil {
			log.Fatal(err)
		}
	}

	// Insert
	client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
		stmt := spanner.Statement{
			SQL: `INSERT Singers (SingerId, FirstName, LastName) VALUES
							(12, 'Melissa', 'Garcia'),
							(13, 'Russell', 'Morales'),
							(14, 'Jacqueline', 'Long'),
							(15, 'Dylan', 'Shaw')`,
		}
		rowCount, err := txn.Update(ctx, stmt)
		if err != nil {
			return err
		}
		fmt.Printf("%d record(s) inserted.\n", rowCount)
		return err
	})

	// Select
	{
		stmt := spanner.Statement{
			SQL: `SELECT SingerId, FirstName, LastName FROM Singers
						WHERE LastName = @lastName`,
			Params: map[string]interface{}{
				"lastName": "Garcia",
			},
		}
		iter := client.Single().Query(ctx, stmt)
		defer iter.Stop()
		for {
			row, err := iter.Next()
			if err == iterator.Done {
				fmt.Println("Done")
				return
			}
			if err != nil {
				log.Fatal(err)
				return
			}
			var singerID int64
			var firstName, lastName string
			if err := row.Columns(&singerID, &firstName, &lastName); err != nil {
				log.Fatal(err)
				return
			}
			fmt.Printf("Found! %d %s %s\n", singerID, firstName, lastName)
		}
	}
}

func newAdminClient(ctx context.Context) (*admindb.DatabaseAdminClient, error) {
	var opts []option.ClientOption

	emulatorAddr := os.Getenv("SPANNER_EMULATOR_HOST")
	if emulatorAddr != "" {
		opts = append(
			opts,
			option.WithEndpoint(emulatorAddr),
			option.WithGRPCDialOption(grpc.WithInsecure()),
			option.WithoutAuthentication(),
		)
	}

	adminClient, err := admindb.NewDatabaseAdminClient(ctx, opts...)
	if err != nil {
		return nil, err
	}

	return adminClient, nil
}