Mulai menggunakan Spanner di database/sql Go

Tujuan

Tutorial ini akan memandu Anda melakukan langkah-langkah berikut menggunakan driver database/sql Spanner:

• Membuat instance dan database Spanner.
• Menulis, membaca, dan menjalankan kueri SQL pada data dalam database.
• Memperbarui skema database.
• Mengupdate data menggunakan transaksi baca-tulis.
• Menambahkan indeks sekunder ke database.
• Gunakan indeks untuk membaca dan menjalankan kueri SQL pada data.
• Mengambil data menggunakan transaksi hanya baca.

Biaya

Tutorial ini menggunakan Spanner, yang merupakan komponenGoogle Cloudyang dapat ditagih. Untuk mengetahui informasi tentang biaya penggunaan Spanner, lihat Harga.

Sebelum memulai

Selesaikan langkah-langkah yang dijelaskan dalam Penyiapan, yang mencakup pembuatan dan penetapan project Google Cloud default, mengaktifkan penagihan, mengaktifkan Cloud Spanner API, dan menyiapkan OAuth 2.0 untuk mendapatkan kredensial autentikasi guna menggunakan Cloud Spanner API.

Secara khusus, pastikan Anda menjalankan gcloud auth application-default login untuk menyiapkan lingkungan pengembangan lokal dengan kredensial autentikasi.

Catatan: Jika Anda tidak berencana untuk menyimpan resource yang Anda buat dalam tutorial ini, pertimbangkan untuk membuat project Google Cloud baru, bukan memilih project yang sudah ada. Setelah menyelesaikan tutorial, Anda dapat menghapus project, yang akan menghapus semua resource yang terkait dengan project tersebut.

Menyiapkan lingkungan database/sql lokal Anda

1. Download dan instal Go di mesin pengembangan Anda jika belum diinstal.

2. Clone repositori contoh ke komputer lokal Anda:

   git clone https://github.com/googleapis/go-sql-spanner.git
   

3. Ubah ke direktori yang berisi kode contoh Spanner:

   cd go-sql-spanner/snippets
   

Membuat instance

Saat pertama kali menggunakan Spanner, Anda harus membuat instance, yaitu alokasi resource yang digunakan oleh database Spanner. Saat membuat instance, Anda memilih konfigurasi instance, yang menentukan tempat data Anda disimpan, serta jumlah node yang akan digunakan, yang menentukan jumlah resource penyajian dan penyimpanan dalam instance Anda.

Lihat Membuat instance untuk mempelajari cara membuat instance Spanner menggunakan salah satu metode berikut. Anda dapat memberi nama instance Anda test-instance untuk menggunakannya dengan topik lain dalam dokumen ini yang mereferensikan instance bernama test-instance.

• Google Cloud CLI
• Konsol Google Cloud
• Library klien (C++, C#, Go, Java, Node.js, PHP, Python, Ruby, atau Rust)

Melihat contoh file

Repositori contoh berisi contoh yang menunjukkan cara menggunakan Spanner dengan database/sql.

Lihat file getting_started_guide.go, yang menunjukkan cara menggunakan Spanner. Kode ini menunjukkan cara membuat dan menggunakan database baru. Data ini menggunakan contoh skema yang ditampilkan di halaman Skema dan model data.

Buat database

GoogleSQL

gcloud spanner databases create example-db --instance=test-instance

PostgreSQL

gcloud spanner databases create example-db --instance=test-instance \
  --database-dialect=POSTGRESQL

Anda akan melihat:

Creating database...done.

Buat tabel

Kode berikut membuat dua tabel dalam database.

Catatan: Contoh kode berikutnya menggunakan kedua tabel ini. Jika Anda tidak mengeksekusi kode ini, buat tabel menggunakan konsol Google Cloud atau gcloud CLI. Untuk mengetahui informasi selengkapnya, lihat contoh skema.

GoogleSQL

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
}

PostgreSQL

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

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

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

	// Create two tables in one batch on Spanner PostgreSQL.
	conn, err := db.Conn(ctx)
	if err != nil {
		return err
	}
	defer func() { _ = 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 (
				singer_id   bigint not null primary key,
				first_name  varchar(1024),
				last_name   varchar(1024),
				singer_info bytea
			)`); err != nil {
		return err
	}
	if _, err := conn.ExecContext(ctx,
		`create table albums (
				singer_id     bigint not null,
				album_id      bigint not null,
				album_title   varchar,
				primary key (singer_id, album_id)
			)
			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
}

Jalankan contoh dengan perintah berikut:

GoogleSQL

go run getting_started_guide.go createtables projects/PROJECT_ID/instances/test-instance/databases/example-db

PostgreSQL

go run getting_started_guide.go createtablespg projects/PROJECT_ID/instances/test-instance/databases/example-db

Langkah berikutnya adalah menulis data ke database Anda.

Membuat koneksi

Sebelum dapat melakukan pembacaan atau penulisan, Anda harus membuat sql.DB. sql.DB berisi kumpulan koneksi yang dapat digunakan untuk berinteraksi dengan Spanner. Nama database dan properti koneksi lainnya ditentukan dalam nama sumber data database/sql.

GoogleSQL

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
}

PostgreSQL

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

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

func CreateConnectionPostgreSQL(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 func() { _ = db.Close() }()

	// The Spanner database/sql driver supports both PostgreSQL-style query
	// parameters ($1, $2, ...) and positional query parameters (?, ?, ...).
	row := db.QueryRowContext(ctx, "select $1 as hello", "Hello world!")
	var msg string
	if err := row.Scan(&msg); err != nil {
		return err
	}
	_, _ = fmt.Fprintf(w, "Greeting from Spanner PostgreSQL: %s\n", msg)
	return nil
}

Menulis data dengan DML

Anda dapat menyisipkan data menggunakan Bahasa Pengolahan Data (DML) dalam transaksi baca-tulis.

Anda menggunakan fungsi ExecContext untuk menjalankan pernyataan DML.

GoogleSQL

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
}

PostgreSQL

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

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

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

	// Add 4 rows in one statement.
	// The database/sql driver supports positional query parameters.
	res, err := db.ExecContext(ctx,
		"insert into singers (singer_id, first_name, last_name) "+
			"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
}

Jalankan contoh dengan perintah berikut:

GoogleSQL

go run getting_started_guide.go dmlwrite projects/PROJECT_ID/instances/test-instance/databases/example-db

PostgreSQL

go run getting_started_guide.go dmlwritepg projects/PROJECT_ID/instances/test-instance/databases/example-db

Hasilnya menunjukkan:

4 records inserted.

Catatan: Ada batasan untuk ukuran commit. Lihat batas CRUD untuk mengetahui informasi selengkapnya.

Menulis data dengan mutasi

Anda juga dapat menyisipkan data menggunakan mutasi.

Mutation adalah container untuk operasi mutasi. Mutation mewakili urutan penyisipan, pembaruan, dan penghapusan yang diterapkan Spanner secara atomik ke berbagai baris dan tabel dalam database Spanner.

Gunakan Mutation.InsertOrUpdate() untuk membuat mutasi INSERT_OR_UPDATE, yang menambahkan baris baru atau memperbarui nilai kolom jika baris sudah ada. Atau, gunakan metode Mutation.Insert() untuk membuat mutasi INSERT, yang menambahkan baris baru.

Gunakan fungsi conn.Raw untuk mendapatkan referensi ke koneksi Spanner yang mendasarinya. Fungsi SpannerConn.Apply menerapkan mutasi secara atomik ke database.

Kode berikut menunjukkan cara menulis data menggunakan mutasi:

GoogleSQL

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
}

PostgreSQL

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

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

func WriteDataWithMutationsPostgreSQL(ctx context.Context, w io.Writer, databaseName string) error {
	db, err := sql.Open("spanner", databaseName)
	if err != nil {
		return err
	}
	defer func() { _ = 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 func() { _ = conn.Close() }()

	singerColumns := []string{"singer_id", "first_name", "last_name"}
	albumColumns := []string{"singer_id", "album_id", "album_title"}
	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
}

Jalankan contoh berikut menggunakan argumen write:

GoogleSQL

go run getting_started_guide.go write projects/PROJECT_ID/instances/test-instance/databases/example-db

PostgreSQL

go run getting_started_guide.go writepg projects/PROJECT_ID/instances/test-instance/databases/example-db

Catatan: Ada batasan untuk ukuran commit. Lihat batas CRUD untuk mengetahui informasi selengkapnya.

Membuat kueri data menggunakan SQL

Spanner mendukung antarmuka SQL untuk membaca data, yang dapat Anda akses di command line menggunakan Google Cloud CLI atau secara terprogram menggunakan driver database/sql Spanner.

Di command line

Jalankan pernyataan SQL berikut untuk membaca nilai semua kolom dari tabel Albums:

GoogleSQL

gcloud spanner databases execute-sql example-db --instance=test-instance \
    --sql='SELECT SingerId, AlbumId, AlbumTitle FROM Albums'

Sintaksis Pipe GoogleSQL

gcloud spanner databases execute-sql example-db --instance=test-instance \
    --sql='FROM Albums |> SELECT SingerId, AlbumId, AlbumTitle'

PostgreSQL

gcloud spanner databases execute-sql example-db --instance=test-instance \
    --sql='SELECT singer_id, album_id, album_title FROM albums'

Catatan: Untuk referensi GoogleSQL, lihat Sintaksis SQL standar di GoogleSQL atau Sintaksis pipe di GoogleSQL; untuk referensi PostgreSQL, lihat Struktur dan sintaksis leksikal PostgreSQL.

Hasilnya menunjukkan:

SingerId AlbumId AlbumTitle
1        1       Total Junk
1        2       Go, Go, Go
2        1       Green
2        2       Forever Hold Your Peace
2        3       Terrified

Menggunakan driver database/sql Spanner

Selain menjalankan pernyataan SQL di command line, Anda dapat mengeluarkan pernyataan SQL yang sama secara terprogram menggunakan driver database/sql Spanner.

Fungsi dan struct berikut digunakan untuk menjalankan kueri SQL:

• Fungsi QueryContext dalam struct DB: gunakan fungsi ini untuk menjalankan pernyataan SQL yang menampilkan baris, seperti kueri atau pernyataan DML dengan klausa THEN RETURN.
• Struct Rows: gunakan ini untuk mengakses data yang ditampilkan oleh pernyataan SQL.

Contoh berikut menggunakan fungsi QueryContext:

GoogleSQL

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()
}

PostgreSQL

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

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

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

	rows, err := db.QueryContext(ctx,
		`select singer_id, album_id, album_title
		from albums
		order by singer_id, album_id`)
	defer func() { _ = 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()
}

Jalankan contoh dengan perintah berikut:

GoogleSQL

go run getting_started_guide.go query projects/PROJECT_ID/instances/test-instance/databases/example-db

PostgreSQL

go run getting_started_guide.go querypg projects/PROJECT_ID/instances/test-instance/databases/example-db

Hasilnya menunjukkan:

1 1 Total Junk
1 2 Go, Go, Go
2 1 Green
2 2 Forever Hold Your Peace
2 3 Terrified

Membuat kueri menggunakan parameter SQL

Jika aplikasi Anda memiliki kueri yang sering dieksekusi, Anda dapat meningkatkan performanya dengan membuat parameter. Kueri parametrik yang dihasilkan dapat di-cache dan digunakan kembali, sehingga mengurangi biaya kompilasi. Untuk mengetahui informasi selengkapnya, lihat Menggunakan parameter kueri untuk mempercepat kueri yang sering dijalankan.

Berikut adalah contoh penggunaan parameter dalam klausa WHERE untuk mengkueri data yang berisi nilai tertentu untuk LastName.

Driver database/sql Spanner mendukung parameter kueri posisi dan bernama. ? dalam pernyataan SQL menunjukkan parameter kueri posisi. Teruskan nilai parameter kueri sebagai argumen tambahan ke fungsi QueryContext. Contoh:

GoogleSQL

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()
}

PostgreSQL

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

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

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

	rows, err := db.QueryContext(ctx,
		`select singer_id, first_name, last_name
		from singers
		where last_name = ?`, "Garcia")
	defer func() { _ = 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()
}

Jalankan contoh dengan perintah berikut:

GoogleSQL

go run getting_started_guide.go querywithparameter projects/PROJECT_ID/instances/test-instance/databases/example-db

PostgreSQL

go run getting_started_guide.go querywithparameterpg projects/PROJECT_ID/instances/test-instance/databases/example-db

Hasilnya menunjukkan:

12 Melissa Garcia

Memperbarui skema database

Asumsikan Anda perlu menambahkan kolom baru bernama MarketingBudget ke tabel Albums. Menambahkan kolom baru ke tabel yang ada memerlukan pembaruan pada skema database Anda. Spanner mendukung pembaruan skema ke database saat database terus melayani traffic. Pembaruan skema tidak memerlukan penghentian database dan tidak mengunci seluruh tabel atau kolom; Anda dapat terus menulis data ke database selama pembaruan skema. Baca selengkapnya tentang update skema yang didukung dan performa perubahan skema di Membuat update skema.

Menambahkan kolom

Anda dapat menambahkan kolom di command line menggunakan Google Cloud CLI atau secara terprogram menggunakan driver database/sql Spanner.

Di command line

Gunakan perintah ALTER TABLE berikut untuk menambahkan kolom baru ke tabel:

GoogleSQL

gcloud spanner databases ddl update example-db --instance=test-instance \
    --ddl='ALTER TABLE Albums ADD COLUMN MarketingBudget INT64'

PostgreSQL

gcloud spanner databases ddl update example-db --instance=test-instance \
    --ddl='alter table albums add column marketing_budget bigint'

Anda akan melihat:

Schema updating...done.

Menggunakan driver database/sql Spanner

Gunakan fungsi ExecContext untuk mengubah skema:

GoogleSQL

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
}

PostgreSQL

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

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

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

	_, err = db.ExecContext(ctx,
		`alter table albums
			add column marketing_budget bigint`)
	if err != nil {
		return err
	}

	_, _ = fmt.Fprint(w, "Added marketing_budget column\n")
	return nil
}

Jalankan contoh dengan perintah berikut:

GoogleSQL

go run getting_started_guide.go addcolumn projects/PROJECT_ID/instances/test-instance/databases/example-db

PostgreSQL

go run getting_started_guide.go addcolumnpg projects/PROJECT_ID/instances/test-instance/databases/example-db

Hasilnya menunjukkan:

Added MarketingBudget column.

Menjalankan batch DDL

Sebaiknya jalankan beberapa modifikasi skema dalam satu batch. Gunakan perintah START BATCH DDL dan RUN BATCH untuk menjalankan batch DDL. Contoh berikut membuat dua tabel dalam satu batch:

GoogleSQL

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
}

PostgreSQL

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

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

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

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

	if _, err := conn.ExecContext(ctx, "start batch ddl"); err != nil {
		return err
	}
	if _, err := conn.ExecContext(ctx,
		`create table venues (
			venue_id    bigint not null primary key,
			name        varchar(1024),
			description jsonb
		)`); err != nil {
		return err
	}
	if _, err := conn.ExecContext(ctx,
		`create table concerts (
			concert_id bigint not null primary key,
			venue_id   bigint not null,
			singer_id  bigint not null,
			start_time timestamptz,
			end_time   timestamptz,
			constraint fk_concerts_venues foreign key
				(venue_id) references venues (venue_id),
			constraint fk_concerts_singers foreign key
				(singer_id) references singers (singer_id)
		)`); 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
}

Jalankan contoh dengan perintah berikut:

GoogleSQL

go run getting_started_guide.go ddlbatch projects/PROJECT_ID/instances/test-instance/databases/example-db

PostgreSQL

go run getting_started_guide.go ddlbatchpg projects/PROJECT_ID/instances/test-instance/databases/example-db

Hasilnya menunjukkan:

Added Venues and Concerts tables.

Menulis data ke kolom baru

Kode berikut menulis data ke kolom baru. Ini menetapkan MarketingBudget ke 100000 untuk row key Albums(1, 1) dan ke 500000 untuk row key Albums(2, 2).

GoogleSQL

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
}

PostgreSQL

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

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

func UpdateDataWithMutationsPostgreSQL(ctx context.Context, w io.Writer, databaseName string) error {
	db, err := sql.Open("spanner", databaseName)
	if err != nil {
		return err
	}
	defer func() { _ = 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 func() { _ = conn.Close() }()

	cols := []string{"singer_id", "album_id", "marketing_budget"}
	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
}

Jalankan contoh dengan perintah berikut:

GoogleSQL

go run getting_started_guide.go update projects/PROJECT_ID/instances/test-instance/databases/example-db

PostgreSQL

go run getting_started_guide.go updatepg projects/PROJECT_ID/instances/test-instance/databases/example-db

Hasilnya menunjukkan:

Updated 2 albums

Anda juga dapat menjalankan kueri SQL untuk mengambil nilai yang baru saja Anda tulis.

Contoh berikut menggunakan fungsi QueryContext untuk menjalankan kueri:

GoogleSQL

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()
}

PostgreSQL

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

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

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

	rows, err := db.QueryContext(ctx,
		`select singer_id, album_id, marketing_budget
		from albums
		order by singer_id, album_id`)
	defer func() { _ = 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()
}

Untuk menjalankan kueri ini, jalankan perintah berikut:

GoogleSQL

go run getting_started_guide.go querymarketingbudget projects/PROJECT_ID/instances/test-instance/databases/example-db

PostgreSQL

go run getting_started_guide.go querymarketingbudgetpg projects/PROJECT_ID/instances/test-instance/databases/example-db

Anda akan melihat:

1 1 100000
1 2 null
2 1 null
2 2 500000
2 3 null

Memperbarui data

Anda dapat memperbarui data menggunakan DML dalam transaksi baca-tulis.

Panggil DB.BeginTx untuk menjalankan transaksi baca-tulis di database/sql.

GoogleSQL

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
}

PostgreSQL

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

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

func WriteWithTransactionUsingDmlPostgreSQL(ctx context.Context, w io.Writer, databaseName string) error {
	db, err := sql.Open("spanner", databaseName)
	if err != nil {
		return err
	}
	defer func() { _ = 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
	}
	const selectSql = "select marketing_budget " +
		"from albums " +
		"where singer_id = $1 and album_id = $2"
	// Get the marketing_budget of singer 2 / album 2.
	row := tx.QueryRowContext(ctx, selectSql, 2, 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, 1, 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 marketing_budget = $1 " +
			"where singer_id = $2 and album_id = $3"
		// 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, budget1, 1, 1); err != nil {
			_, _ = tx.ExecContext(ctx, "abort batch")
			_ = tx.Rollback()
			return err
		}
		if _, err := tx.ExecContext(ctx, updateSql, budget2, 2, 2); 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
}

Jalankan contoh dengan perintah berikut:

GoogleSQL

go run getting_started_guide.go writewithtransactionusingdml projects/PROJECT_ID/instances/test-instance/databases/example-db

PostgreSQL

go run getting_started_guide.go writewithtransactionusingdmlpg projects/PROJECT_ID/instances/test-instance/databases/example-db

Tag transaksi dan tag permintaan

Gunakan tag transaksi dan tag permintaan untuk memecahkan masalah transaksi dan kueri di Spanner. Anda dapat meneruskan opsi transaksi tambahan ke fungsi spannerdriver.BeginReadWriteTransaction.

Gunakan spannerdriver.ExecOptions untuk meneruskan opsi kueri tambahan untuk pernyataan SQL. Contoh:

GoogleSQL

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
}

PostgreSQL

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

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

func TagsPostgreSQL(ctx context.Context, w io.Writer, databaseName string) error {
	db, err := sql.Open("spanner", databaseName)
	if err != nil {
		return err
	}
	defer func() { _ = 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 marketing_budget "+
		"from albums "+
		"where singer_id=? and album_id=?",
		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 marketing_budget=$1 
               where singer_id=$2 and album_id=$3`,
			spannerdriver.ExecOptions{
				QueryOptions: spanner.QueryOptions{RequestTag: "reduce-marketing-budget"},
			}, budget, 1, 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
}

Jalankan contoh dengan perintah berikut:

GoogleSQL

go run getting_started_guide.go tags projects/PROJECT_ID/instances/test-instance/databases/example-db

PostgreSQL

go run getting_started_guide.go tagspg projects/PROJECT_ID/instances/test-instance/databases/example-db

Mengambil data menggunakan transaksi hanya baca

Misalkan Anda ingin menjalankan lebih dari satu pembacaan pada stempel waktu yang sama. Transaksi hanya baca mengamati awalan yang konsisten dari histori commit transaksi, sehingga aplikasi Anda selalu mendapatkan data yang konsisten. Tetapkan kolom TxOptions.ReadOnly ke true untuk menjalankan transaksi hanya baca.

Berikut ini menunjukkan cara menjalankan kueri dan melakukan pembacaan dalam transaksi hanya baca yang sama:

GoogleSQL

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()
}

PostgreSQL

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

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

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

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

	albumsOrderedById, err := tx.QueryContext(ctx,
		`select singer_id, album_id, album_title
		from albums
		order by singer_id, album_id`)
	defer func() { _ = 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 singer_id, album_id, album_title
		from albums
		order by album_title`)
	defer func() { _ = 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()
}

Jalankan contoh dengan perintah berikut:

GoogleSQL

go run getting_started_guide.go readonlytransaction projects/PROJECT_ID/instances/test-instance/databases/example-db

PostgreSQL

go run getting_started_guide.go readonlytransactionpg projects/PROJECT_ID/instances/test-instance/databases/example-db

Hasilnya menunjukkan:

    1 1 Total Junk
    1 2 Go, Go, Go
    2 1 Green
    2 2 Forever Hold Your Peace
    2 3 Terrified
    2 2 Forever Hold Your Peace
    1 2 Go, Go, Go
    2 1 Green
    2 3 Terrified
    1 1 Total Junk

Kueri berpartisi dan Data Boost

API partitionQuery membagi kueri menjadi bagian-bagian yang lebih kecil, atau partisi, dan menggunakan beberapa mesin untuk mengambil partisi secara paralel. Setiap partisi diidentifikasi oleh token partisi. PartitionQuery API memiliki latensi yang lebih tinggi daripada query API standar, karena hanya ditujukan untuk operasi massal seperti mengekspor atau memindai seluruh database.

Data Boost memungkinkan Anda menjalankan kueri analisis dan ekspor data dengan dampak yang hampir tidak ada ke workload yang ada di instance Spanner yang disediakan. Data Boost hanya mendukung kueri berpartisi.

Contoh berikut menunjukkan cara menjalankan kueri yang dipartisi dengan Data Boost menggunakan driver database/sql:

GoogleSQL

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
}

PostgreSQL

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

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

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

	// Run a partitioned query that uses Data Boost.
	rows, err := db.QueryContext(ctx,
		"select singer_id, first_name, last_name 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 func() { _ = 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
}

Jalankan contoh dengan perintah berikut:

GoogleSQL

go run getting_started_guide.go databoost projects/PROJECT_ID/instances/test-instance/databases/example-db

PostgreSQL

go run getting_started_guide.go databoostpg projects/PROJECT_ID/instances/test-instance/databases/example-db

DML yang dipartisi

Bahasa Pengolahan Data (DML) yang Dipartisi dirancang untuk jenis update dan penghapusan massal berikut:

• Pembersihan berkala dan pembersihan sampah memori.
• Mengisi ulang kolom baru dengan nilai default.

GoogleSQL

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
}

PostgreSQL

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

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

func PartitionedDmlPostgreSQL(ctx context.Context, w io.Writer, databaseName string) error {
	db, err := sql.Open("spanner", databaseName)
	if err != nil {
		return err
	}
	defer func() { _ = 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 marketing_budget column.
	res, err := conn.ExecContext(ctx, "update albums set marketing_budget=0 where marketing_budget 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
}

Jalankan contoh dengan perintah berikut:

GoogleSQL

go run getting_started_guide.go pdml projects/PROJECT_ID/instances/test-instance/databases/example-db

PostgreSQL

go run getting_started_guide.go pdmlpg projects/PROJECT_ID/instances/test-instance/databases/example-db

Pembersihan

Agar tidak menimbulkan biaya tambahan pada akun Penagihan Cloud Anda untuk resource yang digunakan dalam tutorial ini, hapus database dan hapus instance yang Anda buat.

Menghapus database

Jika Anda menghapus instance, semua database di dalamnya akan otomatis dihapus. Langkah ini menunjukkan cara menghapus database tanpa menghapus instance (Anda akan tetap dikenai biaya untuk instance tersebut).

Di command line

gcloud spanner databases delete example-db --instance=test-instance

Menggunakan konsol Google Cloud

1. Buka halaman Spanner Instances di konsol Google Cloud .

   Buka halaman Instances

2. Klik instance.

3. Klik database yang ingin Anda hapus.

4. Di halaman Database details, klik Delete.

5. Konfirmasi bahwa Anda ingin menghapus database, lalu klik Delete.

Menghapus instance

Menghapus instance akan otomatis menghapus semua database yang dibuat di instance tersebut.

Di command line

gcloud spanner instances delete test-instance

Menggunakan konsol Google Cloud

1. Buka halaman Spanner Instances di konsol Google Cloud .

   Buka halaman Instances

2. Klik instance Anda.

3. Klik Hapus.

4. Konfirmasi bahwa Anda ingin menghapus instance, lalu klik Delete.

Langkah berikutnya

• Pelajari cara mengakses Spanner dengan instance virtual machine.

• Pelajari kredensial otorisasi dan autentikasi di Mengautentikasi ke layanan Cloud menggunakan library klien.

• Pelajari lebih lanjut Praktik terbaik desain skema Spanner.