Erste Schritte mit Spanner in Go-Datenbank/SQL

Mit Sammlungen den Überblick behalten Sie können Inhalte basierend auf Ihren Einstellungen speichern und kategorisieren.

import (
	"context"
	"database/sql"
	"fmt"
	"io"

	_ "github.com/googleapis/go-sql-spanner"
)

func CreateTables(ctx context.Context, w io.Writer, databaseName string) error {
	db, err := sql.Open("spanner", databaseName)
	if err != nil {
		return err
	}
	defer db.Close()

	// Create two tables in one batch on Spanner.
	conn, err := db.Conn(ctx)
	defer conn.Close()

	// Start a DDL batch on the connection.
	// This instructs the connection to buffer all DDL statements until the
	// command `run batch` is executed.
	if _, err := conn.ExecContext(ctx, "start batch ddl"); err != nil {
		return err
	}
	if _, err := conn.ExecContext(ctx,
		`CREATE TABLE Singers (
				SingerId   INT64 NOT NULL,
				FirstName  STRING(1024),
				LastName   STRING(1024),
				SingerInfo BYTES(MAX)
			) PRIMARY KEY (SingerId)`); err != nil {
		return err
	}
	if _, err := conn.ExecContext(ctx,
		`CREATE TABLE Albums (
				SingerId     INT64 NOT NULL,
				AlbumId      INT64 NOT NULL,
				AlbumTitle   STRING(MAX)
			) PRIMARY KEY (SingerId, AlbumId),
			INTERLEAVE IN PARENT Singers ON DELETE CASCADE`); err != nil {
		return err
	}
	// `run batch` sends the DDL statements to Spanner and blocks until
	// all statements have finished executing.
	if _, err := conn.ExecContext(ctx, "run batch"); err != nil {
		return err
	}

	fmt.Fprintf(w, "Created Singers & Albums tables in database: [%s]\n", databaseName)
	return nil
}

import (
	"context"
	"database/sql"
	"fmt"
	"io"

	_ "github.com/googleapis/go-sql-spanner"
)

func CreateConnection(ctx context.Context, w io.Writer, databaseName string) error {
	// The dataSourceName should start with a fully qualified Spanner database name
	// in the format `projects/my-project/instances/my-instance/databases/my-database`.
	// Additional properties can be added after the database name by
	// adding one or more `;name=value` pairs.

	dsn := fmt.Sprintf("%s;numChannels=8", databaseName)
	db, err := sql.Open("spanner", dsn)
	if err != nil {
		return err
	}
	defer db.Close()

	row := db.QueryRowContext(ctx, "select 'Hello world!' as hello")
	var msg string
	if err := row.Scan(&msg); err != nil {
		return err
	}
	fmt.Fprintf(w, "Greeting from Spanner: %s\n", msg)
	return nil
}

import (
	"context"
	"database/sql"
	"fmt"
	"io"

	_ "github.com/googleapis/go-sql-spanner"
)

func WriteDataWithDml(ctx context.Context, w io.Writer, databaseName string) error {
	db, err := sql.Open("spanner", databaseName)
	if err != nil {
		return err
	}
	defer db.Close()

	// Add 4 rows in one statement.
	// The database/sql driver supports positional query parameters.
	res, err := db.ExecContext(ctx,
		"INSERT INTO Singers (SingerId, FirstName, LastName) "+
			"VALUES (?, ?, ?), (?, ?, ?), "+
			"       (?, ?, ?), (?, ?, ?)",
		12, "Melissa", "Garcia",
		13, "Russel", "Morales",
		14, "Jacqueline", "Long",
		15, "Dylan", "Shaw")
	if err != nil {
		return err
	}
	c, err := res.RowsAffected()
	if err != nil {
		return err
	}
	fmt.Fprintf(w, "%v records inserted\n", c)

	return nil
}

import (
	"context"
	"database/sql"
	"fmt"
	"io"

	"cloud.google.com/go/spanner"
	spannerdriver "github.com/googleapis/go-sql-spanner"
)

func WriteDataWithMutations(ctx context.Context, w io.Writer, databaseName string) error {
	db, err := sql.Open("spanner", databaseName)
	if err != nil {
		return err
	}
	defer db.Close()

	// Get a connection so that we can get access to the Spanner specific
	// connection interface SpannerConn.
	conn, err := db.Conn(ctx)
	if err != nil {
		return err
	}
	defer conn.Close()

	singerColumns := []string{"SingerId", "FirstName", "LastName"}
	albumColumns := []string{"SingerId", "AlbumId", "AlbumTitle"}
	mutations := []*spanner.Mutation{
		spanner.Insert("Singers", singerColumns, []interface{}{int64(1), "Marc", "Richards"}),
		spanner.Insert("Singers", singerColumns, []interface{}{int64(2), "Catalina", "Smith"}),
		spanner.Insert("Singers", singerColumns, []interface{}{int64(3), "Alice", "Trentor"}),
		spanner.Insert("Singers", singerColumns, []interface{}{int64(4), "Lea", "Martin"}),
		spanner.Insert("Singers", singerColumns, []interface{}{int64(5), "David", "Lomond"}),
		spanner.Insert("Albums", albumColumns, []interface{}{int64(1), int64(1), "Total Junk"}),
		spanner.Insert("Albums", albumColumns, []interface{}{int64(1), int64(2), "Go, Go, Go"}),
		spanner.Insert("Albums", albumColumns, []interface{}{int64(2), int64(1), "Green"}),
		spanner.Insert("Albums", albumColumns, []interface{}{int64(2), int64(2), "Forever Hold Your Peace"}),
		spanner.Insert("Albums", albumColumns, []interface{}{int64(2), int64(3), "Terrified"}),
	}
	// Mutations can be written outside an explicit transaction using SpannerConn#Apply.
	if err := conn.Raw(func(driverConn interface{}) error {
		spannerConn, ok := driverConn.(spannerdriver.SpannerConn)
		if !ok {
			return fmt.Errorf("unexpected driver connection %v, expected SpannerConn", driverConn)
		}
		_, err = spannerConn.Apply(ctx, mutations)
		return err
	}); err != nil {
		return err
	}
	fmt.Fprintf(w, "Inserted %v rows\n", len(mutations))

	return nil
}

import (
	"context"
	"database/sql"
	"fmt"
	"io"

	_ "github.com/googleapis/go-sql-spanner"
)

func QueryData(ctx context.Context, w io.Writer, databaseName string) error {
	db, err := sql.Open("spanner", databaseName)
	if err != nil {
		return err
	}
	defer db.Close()

	rows, err := db.QueryContext(ctx,
		`SELECT SingerId, AlbumId, AlbumTitle
		FROM Albums
		ORDER BY SingerId, AlbumId`)
	defer rows.Close()
	if err != nil {
		return err
	}
	for rows.Next() {
		var singerId, albumId int64
		var title string
		err = rows.Scan(&singerId, &albumId, &title)
		if err != nil {
			return err
		}
		fmt.Fprintf(w, "%v %v %v\n", singerId, albumId, title)
	}
	if rows.Err() != nil {
		return rows.Err()
	}
	return rows.Close()
}

import (
	"context"
	"database/sql"
	"fmt"
	"io"

	_ "github.com/googleapis/go-sql-spanner"
)

func QueryDataWithParameter(ctx context.Context, w io.Writer, databaseName string) error {
	db, err := sql.Open("spanner", databaseName)
	if err != nil {
		return err
	}
	defer db.Close()

	rows, err := db.QueryContext(ctx,
		`SELECT SingerId, FirstName, LastName
		FROM Singers
		WHERE LastName = ?`, "Garcia")
	defer rows.Close()
	if err != nil {
		return err
	}
	for rows.Next() {
		var singerId int64
		var firstName, lastName string
		err = rows.Scan(&singerId, &firstName, &lastName)
		if err != nil {
			return err
		}
		fmt.Fprintf(w, "%v %v %v\n", singerId, firstName, lastName)
	}
	if rows.Err() != nil {
		return rows.Err()
	}
	return rows.Close()
}

import (
	"context"
	"database/sql"
	"fmt"
	"io"

	_ "github.com/googleapis/go-sql-spanner"
)

func AddColumn(ctx context.Context, w io.Writer, databaseName string) error {
	db, err := sql.Open("spanner", databaseName)
	if err != nil {
		return err
	}
	defer db.Close()

	_, err = db.ExecContext(ctx,
		`ALTER TABLE Albums
			ADD COLUMN MarketingBudget INT64`)
	if err != nil {
		return err
	}

	fmt.Fprint(w, "Added MarketingBudget column\n")
	return nil
}

import (
	"context"
	"database/sql"
	"fmt"
	"io"

	_ "github.com/googleapis/go-sql-spanner"
)

func DdlBatch(ctx context.Context, w io.Writer, databaseName string) error {
	db, err := sql.Open("spanner", databaseName)
	if err != nil {
		return err
	}
	defer db.Close()

	// Executing multiple DDL statements as one batch is
	// more efficient than executing each statement
	// individually.
	conn, err := db.Conn(ctx)
	defer conn.Close()

	if _, err := conn.ExecContext(ctx, "start batch ddl"); err != nil {
		return err
	}
	if _, err := conn.ExecContext(ctx,
		`CREATE TABLE Venues (
			VenueId     INT64 NOT NULL,
			Name        STRING(1024),
			Description JSON,
		) PRIMARY KEY (VenueId)`); err != nil {
		return err
	}
	if _, err := conn.ExecContext(ctx,
		`CREATE TABLE Concerts (
			ConcertId INT64 NOT NULL,
			VenueId   INT64 NOT NULL,
			SingerId  INT64 NOT NULL,
			StartTime TIMESTAMP,
			EndTime   TIMESTAMP,
			CONSTRAINT Fk_Concerts_Venues FOREIGN KEY
				(VenueId) REFERENCES Venues (VenueId),
			CONSTRAINT Fk_Concerts_Singers FOREIGN KEY
				(SingerId) REFERENCES Singers (SingerId),
		) PRIMARY KEY (ConcertId)`); err != nil {
		return err
	}
	// `run batch` sends the DDL statements to Spanner and blocks until
	// all statements have finished executing.
	if _, err := conn.ExecContext(ctx, "run batch"); err != nil {
		return err
	}

	fmt.Fprint(w, "Added Venues and Concerts tables\n")
	return nil
}

import (
	"context"
	"database/sql"
	"fmt"
	"io"

	"cloud.google.com/go/spanner"
	spannerdriver "github.com/googleapis/go-sql-spanner"
)

func UpdateDataWithMutations(ctx context.Context, w io.Writer, databaseName string) error {
	db, err := sql.Open("spanner", databaseName)
	if err != nil {
		return err
	}
	defer db.Close()

	// Get a connection so that we can get access to the Spanner specific
	// connection interface SpannerConn.
	conn, err := db.Conn(ctx)
	if err != nil {
		return err
	}
	defer conn.Close()

	cols := []string{"SingerId", "AlbumId", "MarketingBudget"}
	mutations := []*spanner.Mutation{
		spanner.Update("Albums", cols, []interface{}{1, 1, 100000}),
		spanner.Update("Albums", cols, []interface{}{2, 2, 500000}),
	}
	if err := conn.Raw(func(driverConn interface{}) error {
		spannerConn, ok := driverConn.(spannerdriver.SpannerConn)
		if !ok {
			return fmt.Errorf("unexpected driver connection %v, "+
				"expected SpannerConn", driverConn)
		}
		_, err = spannerConn.Apply(ctx, mutations)
		return err
	}); err != nil {
		return err
	}
	fmt.Fprintf(w, "Updated %v albums\n", len(mutations))

	return nil
}

import (
	"context"
	"database/sql"
	"fmt"
	"io"

	_ "github.com/googleapis/go-sql-spanner"
)

func QueryNewColumn(ctx context.Context, w io.Writer, databaseName string) error {
	db, err := sql.Open("spanner", databaseName)
	if err != nil {
		return err
	}
	defer db.Close()

	rows, err := db.QueryContext(ctx,
		`SELECT SingerId, AlbumId, MarketingBudget
		FROM Albums
		ORDER BY SingerId, AlbumId`)
	defer rows.Close()
	if err != nil {
		return err
	}
	for rows.Next() {
		var singerId, albumId int64
		var marketingBudget sql.NullInt64
		err = rows.Scan(&singerId, &albumId, &marketingBudget)
		if err != nil {
			return err
		}
		budget := "NULL"
		if marketingBudget.Valid {
			budget = fmt.Sprintf("%v", marketingBudget.Int64)
		}
		fmt.Fprintf(w, "%v %v %v\n", singerId, albumId, budget)
	}
	if rows.Err() != nil {
		return rows.Err()
	}
	return rows.Close()
}

import (
	"context"
	"database/sql"
	"fmt"
	"io"

	_ "github.com/googleapis/go-sql-spanner"
)

func WriteWithTransactionUsingDml(ctx context.Context, w io.Writer, databaseName string) error {
	db, err := sql.Open("spanner", databaseName)
	if err != nil {
		return err
	}
	defer db.Close()

	// Transfer marketing budget from one album to another. We do it in a
	// transaction to ensure that the transfer is atomic.
	tx, err := db.BeginTx(ctx, &sql.TxOptions{})
	if err != nil {
		return err
	}
	// The Spanner database/sql driver supports both positional and named
	// query parameters. This query uses named query parameters.
	const selectSql = "SELECT MarketingBudget " +
		"FROM Albums " +
		"WHERE SingerId = @singerId and AlbumId = @albumId"
	// Get the marketing_budget of singer 2 / album 2.
	row := tx.QueryRowContext(ctx, selectSql,
		sql.Named("singerId", 2), sql.Named("albumId", 2))
	var budget2 int64
	if err := row.Scan(&budget2); err != nil {
		tx.Rollback()
		return err
	}
	const transfer = 20000
	// The transaction will only be committed if this condition still holds
	// at the time of commit. Otherwise, the transaction will be aborted.
	if budget2 >= transfer {
		// Get the marketing_budget of singer 1 / album 1.
		row := tx.QueryRowContext(ctx, selectSql,
			sql.Named("singerId", 1), sql.Named("albumId", 1))
		var budget1 int64
		if err := row.Scan(&budget1); err != nil {
			tx.Rollback()
			return err
		}
		// Transfer part of the marketing budget of Album 2 to Album 1.
		budget1 += transfer
		budget2 -= transfer
		const updateSql = "UPDATE Albums " +
			"SET MarketingBudget = @budget " +
			"WHERE SingerId = @singerId and AlbumId = @albumId"
		// Start a DML batch and execute it as part of the current transaction.
		if _, err := tx.ExecContext(ctx, "start batch dml"); err != nil {
			tx.Rollback()
			return err
		}
		if _, err := tx.ExecContext(ctx, updateSql,
			sql.Named("singerId", 1),
			sql.Named("albumId", 1),
			sql.Named("budget", budget1)); err != nil {
			_, _ = tx.ExecContext(ctx, "abort batch")
			tx.Rollback()
			return err
		}
		if _, err := tx.ExecContext(ctx, updateSql,
			sql.Named("singerId", 2),
			sql.Named("albumId", 2),
			sql.Named("budget", budget2)); err != nil {
			_, _ = tx.ExecContext(ctx, "abort batch")
			tx.Rollback()
			return err
		}
		// `run batch` sends the DML statements to Spanner.
		// The result contains the total affected rows across the entire batch.
		result, err := tx.ExecContext(ctx, "run batch")
		if err != nil {
			tx.Rollback()
			return err
		}
		if affected, err := result.RowsAffected(); err != nil {
			tx.Rollback()
			return err
		} else if affected != 2 {
			// The batch should update 2 rows.
			tx.Rollback()
			return fmt.Errorf("unexpected number of rows affected: %v", affected)
		}
	}
	// Commit the current transaction.
	if err := tx.Commit(); err != nil {
		return err
	}

	fmt.Fprintln(w, "Transferred marketing budget from Album 2 to Album 1")

	return nil
}

import (
	"context"
	"database/sql"
	"fmt"
	"io"

	"cloud.google.com/go/spanner"
	spannerdriver "github.com/googleapis/go-sql-spanner"
)

func Tags(ctx context.Context, w io.Writer, databaseName string) error {
	db, err := sql.Open("spanner", databaseName)
	if err != nil {
		return err
	}
	defer db.Close()

	// Use the spannerdriver.BeginReadWriteTransaction function
	// to specify specific Spanner options, such as transaction tags.
	tx, err := spannerdriver.BeginReadWriteTransaction(ctx, db,
		spannerdriver.ReadWriteTransactionOptions{
			TransactionOptions: spanner.TransactionOptions{
				TransactionTag: "example-tx-tag",
			},
		})
	if err != nil {
		return err
	}

	// Pass in an argument of type spannerdriver.ExecOptions to supply
	// additional options for a statement.
	row := tx.QueryRowContext(ctx, "SELECT MarketingBudget "+
		"FROM Albums "+
		"WHERE SingerId=? and AlbumId=?",
		spannerdriver.ExecOptions{
			QueryOptions: spanner.QueryOptions{RequestTag: "query-marketing-budget"},
		}, 1, 1)
	var budget int64
	if err := row.Scan(&budget); err != nil {
		tx.Rollback()
		return err
	}

	// Reduce the marketing budget by 10% if it is more than 1,000.
	if budget > 1000 {
		budget = int64(float64(budget) - float64(budget)*0.1)
		if _, err := tx.ExecContext(ctx,
			`UPDATE Albums SET MarketingBudget=@budget 
               WHERE SingerId=@singerId AND AlbumId=@albumId`,
			spannerdriver.ExecOptions{
				QueryOptions: spanner.QueryOptions{RequestTag: "reduce-marketing-budget"},
			},
			sql.Named("budget", budget),
			sql.Named("singerId", 1),
			sql.Named("albumId", 1)); err != nil {
			tx.Rollback()
			return err
		}
	}
	// Commit the current transaction.
	if err := tx.Commit(); err != nil {
		return err
	}
	fmt.Fprintln(w, "Reduced marketing budget")

	return nil
}

import (
	"context"
	"database/sql"
	"fmt"
	"io"

	_ "github.com/googleapis/go-sql-spanner"
)

func ReadOnlyTransaction(ctx context.Context, w io.Writer, databaseName string) error {
	db, err := sql.Open("spanner", databaseName)
	if err != nil {
		return err
	}
	defer db.Close()

	// Start a read-only transaction by supplying additional transaction options.
	tx, err := db.BeginTx(ctx, &sql.TxOptions{ReadOnly: true})

	albumsOrderedById, err := tx.QueryContext(ctx,
		`SELECT SingerId, AlbumId, AlbumTitle
		FROM Albums
		ORDER BY SingerId, AlbumId`)
	defer albumsOrderedById.Close()
	if err != nil {
		return err
	}
	for albumsOrderedById.Next() {
		var singerId, albumId int64
		var title string
		err = albumsOrderedById.Scan(&singerId, &albumId, &title)
		if err != nil {
			return err
		}
		fmt.Fprintf(w, "%v %v %v\n", singerId, albumId, title)
	}

	albumsOrderedTitle, err := tx.QueryContext(ctx,
		`SELECT SingerId, AlbumId, AlbumTitle
		FROM Albums
		ORDER BY AlbumTitle`)
	defer albumsOrderedTitle.Close()
	if err != nil {
		return err
	}
	for albumsOrderedTitle.Next() {
		var singerId, albumId int64
		var title string
		err = albumsOrderedTitle.Scan(&singerId, &albumId, &title)
		if err != nil {
			return err
		}
		fmt.Fprintf(w, "%v %v %v\n", singerId, albumId, title)
	}

	// End the read-only transaction by calling Commit.
	return tx.Commit()
}

import (
	"context"
	"database/sql"
	"fmt"
	"io"
	"slices"

	"cloud.google.com/go/spanner"
	spannerdriver "github.com/googleapis/go-sql-spanner"
)

func DataBoost(ctx context.Context, w io.Writer, databaseName string) error {
	db, err := sql.Open("spanner", databaseName)
	if err != nil {
		return err
	}
	defer db.Close()

	// Run a partitioned query that uses Data Boost.
	rows, err := db.QueryContext(ctx,
		"SELECT SingerId, FirstName, LastName from Singers",
		spannerdriver.ExecOptions{
			PartitionedQueryOptions: spannerdriver.PartitionedQueryOptions{
				// AutoPartitionQuery instructs the Spanner database/sql driver to
				// automatically partition the query and execute each partition in parallel.
				// The rows are returned as one result set in undefined order.
				AutoPartitionQuery: true,
			},
			QueryOptions: spanner.QueryOptions{
				// Set DataBoostEnabled to true to enable DataBoost.
				// See https://cloud.google.com/spanner/docs/databoost/databoost-overview
				// for more information.
				DataBoostEnabled: true,
			},
		})
	defer rows.Close()
	if err != nil {
		return err
	}
	type Singer struct {
		SingerId  int64
		FirstName string
		LastName  string
	}
	var singers []Singer
	for rows.Next() {
		var singer Singer
		err = rows.Scan(&singer.SingerId, &singer.FirstName, &singer.LastName)
		if err != nil {
			return err
		}
		singers = append(singers, singer)
	}
	// Queries that use the AutoPartition option return rows in undefined order,
	// so we need to sort them in memory to guarantee the output order.
	slices.SortFunc(singers, func(a, b Singer) int {
		return int(a.SingerId - b.SingerId)
	})
	for _, s := range singers {
		fmt.Fprintf(w, "%v %v %v\n", s.SingerId, s.FirstName, s.LastName)
	}

	return nil
}

import (
	"context"
	"database/sql"
	"fmt"
	"io"

	_ "github.com/googleapis/go-sql-spanner"
)

func PartitionedDml(ctx context.Context, w io.Writer, databaseName string) error {
	db, err := sql.Open("spanner", databaseName)
	if err != nil {
		return err
	}
	defer db.Close()

	conn, err := db.Conn(ctx)
	if err != nil {
		return err
	}
	// Enable Partitioned DML on this connection.
	if _, err := conn.ExecContext(ctx, "SET AUTOCOMMIT_DML_MODE='PARTITIONED_NON_ATOMIC'"); err != nil {
		return fmt.Errorf("failed to change DML mode to Partitioned_Non_Atomic: %v", err)
	}
	// Back-fill a default value for the MarketingBudget column.
	res, err := conn.ExecContext(ctx, "UPDATE Albums SET MarketingBudget=0 WHERE MarketingBudget IS NULL")
	if err != nil {
		return err
	}
	affected, err := res.RowsAffected()
	if err != nil {
		return fmt.Errorf("failed to get affected rows: %v", err)
	}

	// Partitioned DML returns the minimum number of records that were affected.
	fmt.Fprintf(w, "Updated at least %v albums\n", affected)

	// Closing the connection will return it to the connection pool. The DML mode will automatically be reset to the
	// default TRANSACTIONAL mode when the connection is returned to the pool, so we do not need to change it back
	// manually.
	_ = conn.Close()

	return nil
}