Menyiapkan dan membuat kueri Grafik Spanner

Catatan: Fitur ini tersedia dengan edisi Spanner Enterprise dan edisi Enterprise Plus. Untuk mengetahui informasi selengkapnya, lihat ringkasan edisi Spanner.

Dokumen ini menunjukkan cara menyiapkan dan membuat kueri Spanner Graph menggunakan konsol Google Cloud dan library klien. Topik berikut membantu Anda mempelajari cara:

• Buat instance Spanner.

• Buat database dengan skema Spanner Graph.

• Sisipkan data grafik.

• Buat kueri grafik yang Anda buat.

• Membersihkan resource.

Untuk mempelajari detail harga Spanner, lihat Harga Spanner.

Untuk mencoba codelab, lihat Memulai Spanner Graph.

Sebelum memulai

1. Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

2. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

   Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

   Go to project selector

3. Make sure that billing is enabled for your Google Cloud project.

4. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

   Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

   Go to project selector

5. Make sure that billing is enabled for your Google Cloud project.

6. Spanner API akan diaktifkan secara otomatis. Jika tidak, aktifkan secara manual:
   Mengaktifkan Spanner API

4. Untuk mendapatkan izin yang diperlukan guna membuat instance dan database, minta administrator untuk memberi Anda peran IAM Cloud Spanner Admin (roles/spanner.admin) di project Anda.

Membuat instance

Catatan: Untuk menjelajahi Spanner menggunakan instance uji coba gratis selama 90 hari, lihat Membuat instance uji coba gratis Spanner.

Saat pertama kali menggunakan Spanner, Anda harus membuat instance, yang merupakan alokasi resource yang digunakan oleh database Spanner. Bagian ini menunjukkan cara membuat instance menggunakan konsol Google Cloud.

1. Di konsol Google Cloud, buka halaman Spanner.

   Buka Spanner

2. Pilih atau buat project Google Cloud jika Anda belum melakukannya.

3. Lakukan salah satu hal berikut:

   1. Jika Anda belum pernah membuat instance Spanner, di halaman Selamat datang di Spanner, klik Buat instance yang disediakan.

   2. Jika Anda telah membuat instance Spanner, di halaman Instances, klik Create instance.

4. Di halaman Select an edition, pilih Enterprise Plus atau Enterprise.

   Spanner Graph hanya tersedia di edisi Enterprise atau edisi Enterprise Plus. Untuk membandingkan berbagai edisi, klik Bandingkan edisi. Untuk mengetahui informasi selengkapnya, lihat ringkasan edisi Spanner.

5. Klik Lanjutkan.

6. Di Nama instance, masukkan nama instance, misalnya, test-instance.

7. Di Instance ID, pertahankan atau ubah ID instance. ID instance Anda secara default adalah nama instance, tetapi Anda dapat mengubahnya. Nama instance dan ID instance Anda dapat sama atau berbeda.

8. Klik Lanjutkan.

9. Di Pilih konfigurasi, lakukan hal berikut:

   1. Biarkan Regional dipilih.

   2. Di Pilih konfigurasi, pilih region. Region yang Anda pilih adalah tempat instance Anda disimpan dan direplikasi.

   3. Klik Lanjutkan.

10. Di Configure compute capacity, lakukan hal berikut:

    1. Di Pilih unit, pilih Unit pemrosesan (PU).

    2. Di Pilih mode penskalaan, tetap pilih Alokasi manual dan di Jumlah, tetapkan 1.000 unit pemrosesan.

11. Klik Create. Konsol Google Cloud akan menampilkan halaman Ringkasan untuk instance yang Anda buat.

Membuat database dengan skema Spanner Graph

Bagian ini menunjukkan cara menggunakan Konsol Google Cloud dan library klien untuk membuat database dengan skema Spanner Graph.

Konsol

1. Di konsol Google Cloud, buka halaman Spanner Instances.

Buka instance Spanner

1. Klik instance yang Anda buat, misalnya, Test Instance.

2. Di Ringkasan, klik Buat database di bagian nama instance Anda.

3. Di Nama database, masukkan nama database. Contoh, example-db.

4. Di Select database dialect, pilih Google Standard SQL. Spanner Graph tidak tersedia dalam dialek PostgreSQL. Halaman pembuatan database Anda sekarang terlihat seperti ini:

   

5. Salin dan tempel skema berikut ke tab editor DDL Templates. Skema ini berisi dua definisi tabel node, Person dan Account, dan dua definisi tabel tepi, PersonOwnAccount dan AccountTransferAccount. Spanner Graph menggunakan tabel relasional untuk menentukan grafik, sehingga Anda melihat tabel relasional dan pernyataan grafik dalam skema. Untuk mempelajari skema Spanner Graph lebih lanjut, lihat ringkasan skema Spanner Graph.

   CREATE TABLE Person (
     id               INT64 NOT NULL,
     name             STRING(MAX),
     birthday         TIMESTAMP,
     country          STRING(MAX),
     city             STRING(MAX),
   ) PRIMARY KEY (id);
   
   CREATE TABLE Account (
     id               INT64 NOT NULL,
     create_time      TIMESTAMP,
     is_blocked       BOOL,
     nick_name        STRING(MAX),
   ) PRIMARY KEY (id);
   
   CREATE TABLE PersonOwnAccount (
     id               INT64 NOT NULL,
     account_id       INT64 NOT NULL,
     create_time      TIMESTAMP,
     FOREIGN KEY (account_id) REFERENCES Account (id)
   ) PRIMARY KEY (id, account_id),
     INTERLEAVE IN PARENT Person ON DELETE CASCADE;
   
   CREATE TABLE AccountTransferAccount (
     id               INT64 NOT NULL,
     to_id            INT64 NOT NULL,
     amount           FLOAT64,
     create_time      TIMESTAMP NOT NULL,
     order_number     STRING(MAX),
     FOREIGN KEY (to_id) REFERENCES Account (id)
   ) PRIMARY KEY (id, to_id, create_time),
     INTERLEAVE IN PARENT Account ON DELETE CASCADE;
   
   CREATE OR REPLACE PROPERTY GRAPH FinGraph
     NODE TABLES (Account, Person)
     EDGE TABLES (
       PersonOwnAccount
         SOURCE KEY (id) REFERENCES Person (id)
         DESTINATION KEY (account_id) REFERENCES Account (id)
         LABEL Owns,
       AccountTransferAccount
         SOURCE KEY (id) REFERENCES Account (id)
         DESTINATION KEY (to_id) REFERENCES Account (id)
         LABEL Transfers
     );
   

6. Jangan buat perubahan apa pun di Tampilkan opsi enkripsi.

7. Klik Create. Konsol Google Cloud menampilkan halaman Ringkasan untuk database yang Anda buat.

Library klien

Python

def create_database_with_property_graph(instance_id, database_id):
    """Creates a database, tables and a property graph for sample data."""
    from google.cloud.spanner_admin_database_v1.types import spanner_database_admin

    spanner_client = spanner.Client()
    database_admin_api = spanner_client.database_admin_api

    request = spanner_database_admin.CreateDatabaseRequest(
        parent=database_admin_api.instance_path(spanner_client.project, instance_id),
        create_statement=f"CREATE DATABASE `{database_id}`",
        extra_statements=[
            """CREATE TABLE Person (
            id               INT64 NOT NULL,
            name             STRING(MAX),
            birthday         TIMESTAMP,
            country          STRING(MAX),
            city             STRING(MAX),
        ) PRIMARY KEY (id)""",
            """CREATE TABLE Account (
            id               INT64 NOT NULL,
            create_time      TIMESTAMP,
            is_blocked       BOOL,
            nick_name        STRING(MAX),
        ) PRIMARY KEY (id)""",
            """CREATE TABLE PersonOwnAccount (
            id               INT64 NOT NULL,
            account_id       INT64 NOT NULL,
            create_time      TIMESTAMP,
            FOREIGN KEY (account_id)
                REFERENCES Account (id)
        ) PRIMARY KEY (id, account_id),
        INTERLEAVE IN PARENT Person ON DELETE CASCADE""",
            """CREATE TABLE AccountTransferAccount (
            id               INT64 NOT NULL,
            to_id            INT64 NOT NULL,
            amount           FLOAT64,
            create_time      TIMESTAMP NOT NULL,
            order_number     STRING(MAX),
            FOREIGN KEY (to_id) REFERENCES Account (id)
        ) PRIMARY KEY (id, to_id, create_time),
        INTERLEAVE IN PARENT Account ON DELETE CASCADE""",
            """CREATE OR REPLACE PROPERTY GRAPH FinGraph
            NODE TABLES (Account, Person)
            EDGE TABLES (
                PersonOwnAccount
                    SOURCE KEY(id) REFERENCES Person(id)
                    DESTINATION KEY(account_id) REFERENCES Account(id)
                    LABEL Owns,
                AccountTransferAccount
                    SOURCE KEY(id) REFERENCES Account(id)
                    DESTINATION KEY(to_id) REFERENCES Account(id)
                    LABEL Transfers)""",
        ],
    )

    operation = database_admin_api.create_database(request=request)

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

    print(
        "Created database {} on instance {}".format(
            database.name,
            database_admin_api.instance_path(spanner_client.project, instance_id),
        )
    )

Java

static void createDatabaseWithPropertyGraph(
    DatabaseAdminClient dbAdminClient, InstanceName instanceName, String databaseId) {
  CreateDatabaseRequest createDatabaseRequest =
      CreateDatabaseRequest.newBuilder()
          .setCreateStatement("CREATE DATABASE `" + databaseId + "`")
          .setParent(instanceName.toString())
          .addAllExtraStatements(
              Arrays.asList(
                  "CREATE TABLE Person ("
                      + "  id               INT64 NOT NULL,"
                      + "  name             STRING(MAX),"
                      + "  birthday         TIMESTAMP,"
                      + "  country          STRING(MAX),"
                      + "  city             STRING(MAX),"
                      + ") PRIMARY KEY (id)",
                  "CREATE TABLE Account ("
                      + "  id               INT64 NOT NULL,"
                      + "  create_time      TIMESTAMP,"
                      + "  is_blocked       BOOL,"
                      + "  nick_name        STRING(MAX),"
                      + ") PRIMARY KEY (id)",
                  "CREATE TABLE PersonOwnAccount ("
                      + "  id               INT64 NOT NULL,"
                      + "  account_id       INT64 NOT NULL,"
                      + "  create_time      TIMESTAMP,"
                      + "  FOREIGN KEY (account_id)"
                      + "  REFERENCES Account (id)"
                      + ") PRIMARY KEY (id, account_id),"
                      + "INTERLEAVE IN PARENT Person ON DELETE CASCADE",
                  "CREATE TABLE AccountTransferAccount ("
                      + "  id               INT64 NOT NULL,"
                      + "  to_id            INT64 NOT NULL,"
                      + "  amount           FLOAT64,"
                      + "  create_time      TIMESTAMP NOT NULL,"
                      + "  order_number     STRING(MAX),"
                      + "  FOREIGN KEY (to_id) REFERENCES Account (id)"
                      + ") PRIMARY KEY (id, to_id, create_time),"
                      + "INTERLEAVE IN PARENT Account ON DELETE CASCADE",
                  "CREATE OR REPLACE PROPERTY GRAPH FinGraph "
                      + "NODE TABLES (Account, Person)"
                      + "EDGE TABLES ("
                      + "  PersonOwnAccount"
                      + "    SOURCE KEY(id) REFERENCES Person(id)"
                      + "    DESTINATION KEY(account_id) REFERENCES Account(id)"
                      + "    LABEL Owns,"
                      + "  AccountTransferAccount"
                      + "    SOURCE KEY(id) REFERENCES Account(id)"
                      + "    DESTINATION KEY(to_id) REFERENCES Account(id)"
                      + "    LABEL Transfers)"))
          .build();
  try {
    // Initiate the request which returns an OperationFuture.
    com.google.spanner.admin.database.v1.Database db =
        dbAdminClient.createDatabaseAsync(createDatabaseRequest).get();
    System.out.println("Created database [" + db.getName() + "]");
  } catch (ExecutionException e) {
    // If the operation failed during execution, expose the cause.
    System.out.println("Encountered exception" + e.getCause());
    throw (SpannerException) e.getCause();
  } catch (InterruptedException e) {
    // Throw when a thread is waiting, sleeping, or otherwise occupied,
    // and the thread is interrupted, either before or during the activity.
    throw SpannerExceptionFactory.propagateInterrupt(e);
  }
}

Go

import (
	"context"
	"fmt"
	"io"
	"regexp"

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

func createDatabaseWithPropertyGraph(ctx context.Context, w io.Writer, dbId string) error {
	// dbId is of the form:
	// 	projects/YOUR_PROJECT_ID/instances/YOUR_INSTANCE_ID/databases/YOUR_DATABASE_NAME
	matches := regexp.MustCompile("^(.*)/databases/(.*)$").FindStringSubmatch(dbId)
	if matches == nil || len(matches) != 3 {
		return fmt.Errorf("Invalid database id %s", dbId)
	}

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

	var instance = matches[1]
	var dbName = matches[2]

	// The schema defintion for a database with a property graph comprises table
	// definitions one or more `CREATE PROPERTY GRAPH` statements to define the
	// property graph(s).
	//
	// Here, tables are created for 'Person's and 'Account's. The property graph
	// definition says that these entities form nodes in the graph. Similarly,
	// there are 'PersonOwnAccount' and 'AccountTransferAccount' relationship
	// tables defined. The property graph definition maps these to edges in the graph.
	var schema_statements = []string{
		`CREATE TABLE Person (
			id               INT64 NOT NULL,
			name             STRING(MAX),
			birthday         TIMESTAMP,
			country          STRING(MAX),
			city             STRING(MAX),
		) PRIMARY KEY (id)`,
		`CREATE TABLE Account (
			id               INT64 NOT NULL,
			create_time      TIMESTAMP,
			is_blocked       BOOL,
			nick_name        STRING(MAX),
		) PRIMARY KEY (id)`,
		`CREATE TABLE PersonOwnAccount (
			id               INT64 NOT NULL,
			account_id       INT64 NOT NULL,
			create_time      TIMESTAMP,
			FOREIGN KEY (account_id)
				REFERENCES Account (id)
		) PRIMARY KEY (id, account_id),
		INTERLEAVE IN PARENT Person ON DELETE CASCADE`,
		`CREATE TABLE AccountTransferAccount (
			id               INT64 NOT NULL,
			to_id            INT64 NOT NULL,
			amount           FLOAT64,
			create_time      TIMESTAMP NOT NULL,
			order_number     STRING(MAX),
			FOREIGN KEY (to_id) REFERENCES Account (id)
		) PRIMARY KEY (id, to_id, create_time),
		INTERLEAVE IN PARENT Account ON DELETE CASCADE`,
		`CREATE OR REPLACE PROPERTY GRAPH FinGraph
			NODE TABLES (Account, Person)
			EDGE TABLES (
				PersonOwnAccount
					SOURCE KEY(id) REFERENCES Person(id)
					DESTINATION KEY(account_id) REFERENCES Account(id)
					LABEL Owns,
				AccountTransferAccount
					SOURCE KEY(id) REFERENCES Account(id)
					DESTINATION KEY(to_id) REFERENCES Account(id)
					LABEL Transfers)`,
	}

	op, err := adminClient.CreateDatabase(ctx, &adminpb.CreateDatabaseRequest{
		Parent:          instance,
		CreateStatement: "CREATE DATABASE `" + dbName + "`",
		ExtraStatements: schema_statements,
	})
	if err != nil {
		return err
	}
	if _, err := op.Wait(ctx); err != nil {
		return err
	}
	fmt.Fprintf(w, "Created database [%s]\n", dbId)
	return nil
}

C++

void CreateDatabaseWithPropertyGraph(
    google::cloud::spanner_admin::DatabaseAdminClient client,
    std::string const& project_id, std::string const& instance_id,
    std::string const& database_id) {
  google::cloud::spanner::Database database(project_id, instance_id,
                                            database_id);
  google::spanner::admin::database::v1::CreateDatabaseRequest request;
  request.set_parent(database.instance().FullName());
  request.set_create_statement("CREATE DATABASE `" + database.database_id() +
                               "`");
  request.add_extra_statements(R"""(
    CREATE TABLE Person (
      id               INT64 NOT NULL,
      name             STRING(MAX),
      birthday         TIMESTAMP,
      country          STRING(MAX),
      city             STRING(MAX),
    ) PRIMARY KEY (id))""");
  request.add_extra_statements(R"""(
    CREATE TABLE Account (
      id               INT64 NOT NULL,
      create_time      TIMESTAMP,
      is_blocked       BOOL,
      nick_name        STRING(MAX),
    ) PRIMARY KEY (id))""");
  request.add_extra_statements(R"""(
    CREATE TABLE PersonOwnAccount (
      id               INT64 NOT NULL,
      account_id       INT64 NOT NULL,
      create_time      TIMESTAMP,
      FOREIGN KEY (account_id)
      REFERENCES Account (id)
    ) PRIMARY KEY (id, account_id),
      INTERLEAVE IN PARENT Person ON DELETE CASCADE)""");
  request.add_extra_statements(R"""(
    CREATE TABLE AccountTransferAccount (
      id               INT64 NOT NULL,
      to_id            INT64 NOT NULL,
      amount           FLOAT64,
      create_time      TIMESTAMP NOT NULL,
      order_number     STRING(MAX),
      FOREIGN KEY (to_id) REFERENCES Account (id)
    ) PRIMARY KEY (id, to_id, create_time),
      INTERLEAVE IN PARENT Account ON DELETE CASCADE)""");
  request.add_extra_statements(R"""(
    CREATE OR REPLACE PROPERTY GRAPH FinGraph
      NODE TABLES (Account, Person)
      EDGE TABLES (
        PersonOwnAccount
          SOURCE KEY(id) REFERENCES Person(id)
          DESTINATION KEY(account_id) REFERENCES Account(id)
          LABEL Owns,
        AccountTransferAccount
          SOURCE KEY(id) REFERENCES Account(id)
          DESTINATION KEY(to_id) REFERENCES Account(id)
          LABEL Transfers))""");
  auto db = client.CreateDatabase(request).get();
  if (!db) throw std::move(db).status();
  std::cout << "Database " << db->name() << " created with property graph.\n";
}

Menyisipkan data grafik

Bagian ini menunjukkan cara menggunakan konsol Google Cloud dan library klien untuk menyisipkan data ke dalam Grafik Spanner.

Konsol

1. Di halaman Spanner Studio, klik Tab baru atau gunakan tab editor.

2. Salin dan tempel pernyataan penyisipan data grafik berikut ke dalam node dan tepi.

   INSERT INTO Account
     (id, create_time, is_blocked, nick_name)
   VALUES
     (7,"2020-01-10 06:22:20.222",false,"Vacation Fund"),
     (16,"2020-01-27 17:55:09.206",true,"Vacation Fund"),
     (20,"2020-02-18 05:44:20.655",false,"Rainy Day Fund");
   
   INSERT INTO Person
     (id, name, birthday, country, city)
   VALUES
     (1,"Alex","1991-12-21 00:00:00","Australia","Adelaide"),
     (2,"Dana","1980-10-31 00:00:00","Czech_Republic","Moravia"),
     (3,"Lee","1986-12-07 00:00:00","India","Kollam");
   
   INSERT INTO AccountTransferAccount
     (id, to_id, amount, create_time, order_number)
   VALUES
     (7,16,300,"2020-08-29 15:28:58.647","304330008004315"),
     (7,16,100,"2020-10-04 16:55:05.342","304120005529714"),
     (16,20,300,"2020-09-25 02:36:14.926","103650009791820"),
     (20,7,500,"2020-10-04 16:55:05.342","304120005529714"),
     (20,16,200,"2020-10-17 03:59:40.247","302290001255747");
   
   INSERT INTO PersonOwnAccount
     (id, account_id, create_time)
   VALUES
     (1,7,"2020-01-10 06:22:20.222"),
     (2,20,"2020-01-27 17:55:09.206"),
     (3,16,"2020-02-18 05:44:20.655");
   

3. Klik Run. Setelah operasi selesai, tab Hasil akan menampilkan bahwa 3 baris telah disisipkan.

Library klien

Python

def insert_data(instance_id, database_id):
    """Inserts sample data into the given database.

    The database and tables must already exist and can be created using
    `create_database_with_property_graph`.
    """
    spanner_client = spanner.Client()
    instance = spanner_client.instance(instance_id)
    database = instance.database(database_id)

    with database.batch() as batch:
        batch.insert(
            table="Account",
            columns=("id", "create_time", "is_blocked", "nick_name"),
            values=[
                (7, "2020-01-10T06:22:20.12Z", False, "Vacation Fund"),
                (16, "2020-01-27T17:55:09.12Z", True, "Vacation Fund"),
                (20, "2020-02-18T05:44:20.12Z", False, "Rainy Day Fund"),
            ],
        )

        batch.insert(
            table="Person",
            columns=("id", "name", "birthday", "country", "city"),
            values=[
                (1, "Alex", "1991-12-21T00:00:00.12Z", "Australia", " Adelaide"),
                (2, "Dana", "1980-10-31T00:00:00.12Z", "Czech_Republic", "Moravia"),
                (3, "Lee", "1986-12-07T00:00:00.12Z", "India", "Kollam"),
            ],
        )

        batch.insert(
            table="AccountTransferAccount",
            columns=("id", "to_id", "amount", "create_time", "order_number"),
            values=[
                (7, 16, 300.0, "2020-08-29T15:28:58.12Z", "304330008004315"),
                (7, 16, 100.0, "2020-10-04T16:55:05.12Z", "304120005529714"),
                (16, 20, 300.0, "2020-09-25T02:36:14.12Z", "103650009791820"),
                (20, 7, 500.0, "2020-10-04T16:55:05.12Z", "304120005529714"),
                (20, 16, 200.0, "2020-10-17T03:59:40.12Z", "302290001255747"),
            ],
        )

        batch.insert(
            table="PersonOwnAccount",
            columns=("id", "account_id", "create_time"),
            values=[
                (1, 7, "2020-01-10T06:22:20.12Z"),
                (2, 20, "2020-01-27T17:55:09.12Z"),
                (3, 16, "2020-02-18T05:44:20.12Z"),
            ],
        )

    print("Inserted data.")

Java

/** Class to contain sample Person data. */
static class Person {

  final long id;
  final String name;
  final Timestamp birthday;
  final String country;
  final String city;

  Person(long id, String name, Timestamp birthday, String country, String city) {
    this.id = id;
    this.name = name;
    this.birthday = birthday;
    this.country = country;
    this.city = city;
  }
}

/** Class to contain sample Account data. */
static class Account {

  final long id;
  final Timestamp createTime;
  final boolean isBlocked;
  final String nickName;

  Account(long id, Timestamp createTime, boolean isBlocked, String nickName) {
    this.id = id;
    this.createTime = createTime;
    this.isBlocked = isBlocked;
    this.nickName = nickName;
  }
}

/** Class to contain sample Transfer data. */
static class Transfer {

  final long id;
  final long toId;
  final double amount;
  final Timestamp createTime;
  final String orderNumber;

  Transfer(long id, long toId, double amount, Timestamp createTime, String orderNumber) {
    this.id = id;
    this.toId = toId;
    this.amount = amount;
    this.createTime = createTime;
    this.orderNumber = orderNumber;
  }
}

/** Class to contain sample Ownership data. */
static class Own {

  final long id;
  final long accountId;
  final Timestamp createTime;

  Own(long id, long accountId, Timestamp createTime) {
    this.id = id;
    this.accountId = accountId;
    this.createTime = createTime;
  }
}

static final List<Account> ACCOUNTS =
    Arrays.asList(
        new Account(
            7, Timestamp.parseTimestamp("2020-01-10T06:22:20.12Z"), false, "Vacation Fund"),
        new Account(
            16, Timestamp.parseTimestamp("2020-01-27T17:55:09.12Z"), true, "Vacation Fund"),
        new Account(
            20, Timestamp.parseTimestamp("2020-02-18T05:44:20.12Z"), false, "Rainy Day Fund"));

static final List<Person> PERSONS =
    Arrays.asList(
        new Person(
            1,
            "Alex",
            Timestamp.parseTimestamp("1991-12-21T00:00:00.12Z"),
            "Australia",
            " Adelaide"),
        new Person(
            2,
            "Dana",
            Timestamp.parseTimestamp("1980-10-31T00:00:00.12Z"),
            "Czech_Republic",
            "Moravia"),
        new Person(
            3, "Lee", Timestamp.parseTimestamp("1986-12-07T00:00:00.12Z"), "India", "Kollam"));

static final List<Transfer> TRANSFERS =
    Arrays.asList(
        new Transfer(
            7, 16, 300.0, Timestamp.parseTimestamp("2020-08-29T15:28:58.12Z"), "304330008004315"),
        new Transfer(
            7, 16, 100.0, Timestamp.parseTimestamp("2020-10-04T16:55:05.12Z"), "304120005529714"),
        new Transfer(
            16,
            20,
            300.0,
            Timestamp.parseTimestamp("2020-09-25T02:36:14.12Z"),
            "103650009791820"),
        new Transfer(
            20, 7, 500.0, Timestamp.parseTimestamp("2020-10-04T16:55:05.12Z"), "304120005529714"),
        new Transfer(
            20,
            16,
            200.0,
            Timestamp.parseTimestamp("2020-10-17T03:59:40.12Z"),
            "302290001255747"));

static final List<Own> OWNERSHIPS =
    Arrays.asList(
        new Own(1, 7, Timestamp.parseTimestamp("2020-01-10T06:22:20.12Z")),
        new Own(2, 20, Timestamp.parseTimestamp("2020-01-27T17:55:09.12Z")),
        new Own(3, 16, Timestamp.parseTimestamp("2020-02-18T05:44:20.12Z")));

static void insertData(DatabaseClient dbClient) {
  List<Mutation> mutations = new ArrayList<>();
  for (Account account : ACCOUNTS) {
    mutations.add(
        Mutation.newInsertBuilder("Account")
            .set("id")
            .to(account.id)
            .set("create_time")
            .to(account.createTime)
            .set("is_blocked")
            .to(account.isBlocked)
            .set("nick_name")
            .to(account.nickName)
            .build());
  }
  for (Person person : PERSONS) {
    mutations.add(
        Mutation.newInsertBuilder("Person")
            .set("id")
            .to(person.id)
            .set("name")
            .to(person.name)
            .set("birthday")
            .to(person.birthday)
            .set("country")
            .to(person.country)
            .set("city")
            .to(person.city)
            .build());
  }
  for (Transfer transfer : TRANSFERS) {
    mutations.add(
        Mutation.newInsertBuilder("AccountTransferAccount")
            .set("id")
            .to(transfer.id)
            .set("to_id")
            .to(transfer.toId)
            .set("amount")
            .to(transfer.amount)
            .set("create_time")
            .to(transfer.createTime)
            .set("order_number")
            .to(transfer.orderNumber)
            .build());
  }
  for (Own own : OWNERSHIPS) {
    mutations.add(
        Mutation.newInsertBuilder("PersonOwnAccount")
            .set("id")
            .to(own.id)
            .set("account_id")
            .to(own.accountId)
            .set("create_time")
            .to(own.createTime)
            .build());
  }

  dbClient.write(mutations);
}

Go

import (
	"context"
	"io"
	"time"

	"cloud.google.com/go/spanner"
)

func parseTime(rfc3339Time string) time.Time {
	t, _ := time.Parse(time.RFC3339, rfc3339Time)
	return t
}

func insertGraphData(w io.Writer, db string) error {
	ctx := context.Background()
	client, err := spanner.NewClient(ctx, db)
	if err != nil {
		return err
	}
	defer client.Close()

	// Values are inserted into the node and edge tables corresponding to
	// using Spanner 'Insert' mutations.
	// The tables and columns comply with the schema defined for the
	// property graph 'FinGraph', comprising 'Person' and 'Account' nodes,
	// and 'PersonOwnAccount' and 'AccountTransferAccount' edges.
	personColumns := []string{"id", "name", "birthday", "country", "city"}
	accountColumns := []string{"id", "create_time", "is_blocked", "nick_name"}
	ownColumns := []string{"id", "account_id", "create_time"}
	transferColumns := []string{"id", "to_id", "amount", "create_time", "order_number"}
	m := []*spanner.Mutation{
		spanner.Insert("Account", accountColumns,
			[]interface{}{7, parseTime("2020-01-10T06:22:20.12Z"), false, "Vacation Fund"}),
		spanner.Insert("Account", accountColumns,
			[]interface{}{16, parseTime("2020-01-27T17:55:09.12Z"), true, "Vacation Fund"}),
		spanner.Insert("Account", accountColumns,
			[]interface{}{20, parseTime("2020-02-18T05:44:20.12Z"), false, "Rainy Day Fund"}),
		spanner.Insert("Person", personColumns,
			[]interface{}{1, "Alex", parseTime("1991-12-21T00:00:00.12Z"), "Australia", " Adelaide"}),
		spanner.Insert("Person", personColumns,
			[]interface{}{2, "Dana", parseTime("1980-10-31T00:00:00.12Z"), "Czech_Republic", "Moravia"}),
		spanner.Insert("Person", personColumns,
			[]interface{}{3, "Lee", parseTime("1986-12-07T00:00:00.12Z"), "India", "Kollam"}),
		spanner.Insert("AccountTransferAccount", transferColumns,
			[]interface{}{7, 16, 300.0, parseTime("2020-08-29T15:28:58.12Z"), "304330008004315"}),
		spanner.Insert("AccountTransferAccount", transferColumns,
			[]interface{}{7, 16, 100.0, parseTime("2020-10-04T16:55:05.12Z"), "304120005529714"}),
		spanner.Insert("AccountTransferAccount", transferColumns,
			[]interface{}{16, 20, 300.0, parseTime("2020-09-25T02:36:14.12Z"), "103650009791820"}),
		spanner.Insert("AccountTransferAccount", transferColumns,
			[]interface{}{20, 7, 500.0, parseTime("2020-10-04T16:55:05.12Z"), "304120005529714"}),
		spanner.Insert("AccountTransferAccount", transferColumns,
			[]interface{}{20, 16, 200.0, parseTime("2020-10-17T03:59:40.12Z"), "302290001255747"}),
		spanner.Insert("PersonOwnAccount", ownColumns,
			[]interface{}{1, 7, parseTime("2020-01-10T06:22:20.12Z")}),
		spanner.Insert("PersonOwnAccount", ownColumns,
			[]interface{}{2, 20, parseTime("2020-01-27T17:55:09.12Z")}),
		spanner.Insert("PersonOwnAccount", ownColumns,
			[]interface{}{3, 16, parseTime("2020-02-18T05:44:20.12Z")}),
	}
	_, err = client.Apply(ctx, m)
	return err
}

C++

void InsertData(google::cloud::spanner::Client client) {
  namespace spanner = ::google::cloud::spanner;

  auto insert_accounts =
      spanner::InsertMutationBuilder(
          "Account", {"id", "create_time", "is_blocked", "nick_name"})
          .EmplaceRow(7, spanner::Value("2020-01-10T06:22:20.12Z"), false,
                      "Vacation Fund")
          .EmplaceRow(16, spanner::Value("2020-01-27T17:55:09.12Z"), true,
                      "Vacation Fund")
          .EmplaceRow(20, spanner::Value("2020-02-18T05:44:20.12Z"), false,
                      "Rainy Day Fund")
          .Build();

  auto insert_persons =
      spanner::InsertMutationBuilder(
          "Person", {"id", "name", "birthday", "country", "city"})
          .EmplaceRow(1, "Alex", spanner::Value("1991-12-21T00:00:00.12Z"),
                      "Australia", "Adelaide")
          .EmplaceRow(2, "Dana", spanner::Value("1980-10-31T00:00:00.12Z"),
                      "Czech_Republic", "Moravia")
          .EmplaceRow(3, "Lee", spanner::Value("1986-12-07T00:00:00.12Z"),
                      "India", "Kollam")
          .Build();

  auto insert_transfers =
      spanner::InsertMutationBuilder(
          "AccountTransferAccount",
          {"id", "to_id", "amount", "create_time", "order_number"})
          .EmplaceRow(7, 16, 300.0, spanner::Value("2020-08-29T15:28:58.12Z"),
                      "304330008004315")
          .EmplaceRow(7, 16, 100.0, spanner::Value("2020-10-04T16:55:05.12Z"),
                      "304120005529714")
          .EmplaceRow(16, 20, 300.0, spanner::Value("2020-09-25T02:36:14.12Z"),
                      "103650009791820")
          .EmplaceRow(20, 7, 500.0, spanner::Value("2020-10-04T16:55:05.12Z"),
                      "304120005529714")
          .EmplaceRow(20, 16, 200.0, spanner::Value("2020-10-17T03:59:40.12Z"),
                      "302290001255747")
          .Build();

  auto insert_ownerships =
      spanner::InsertMutationBuilder("PersonOwnAccount",
                                     {"id", "account_id", "create_time"})
          .EmplaceRow(1, 7, spanner::Value("2020-01-10T06:22:20.12Z"))
          .EmplaceRow(2, 20, spanner::Value("2020-01-27T17:55:09.12Z"))
          .EmplaceRow(3, 16, spanner::Value("2020-02-18T05:44:20.12Z"))
          .Build();

  auto commit_result = client.Commit(spanner::Mutations{
      insert_accounts, insert_persons, insert_transfers, insert_ownerships});
  if (!commit_result) throw std::move(commit_result).status();
  std::cout << "Insert was successful [spanner_insert_graph_data]\n";
}

Grafik berikut menunjukkan orang, akun, kepemilikan akun, dan transfer akun dari sisipan:

Menjalankan kueri grafik

Bagian ini menunjukkan cara menggunakan Konsol Google Cloud atau library klien untuk menjalankan kueri skema Spanner Graph.

Konsol

1. Di halaman Ringkasan database, klik Spanner Studio di menu navigasi.

2. Di halaman Spanner Studio, klik Tab baru atau gunakan tab editor.

3. Masukkan kueri berikut di editor kueri. Kueri ini menemukan semua orang yang menerima transfer uang dari Dana, dan jumlah transfer tersebut.

   GRAPH FinGraph
   MATCH
     (from_person:Person {name: "Dana"})-[:Owns]->
     (from_account:Account)-[transfer:Transfers]->
     (to_account:Account)<-[:Owns]-(to_person:Person)
   RETURN
     from_person.name AS from_account_owner,
     from_account.id AS from_account_id,
     to_person.name AS to_account_owner,
     to_account.id AS to_account_id,
     transfer.amount AS amount
   

4. Klik Run.

   Tab Results menampilkan jalur berikut dari Dana melalui Account {id:20}:

   • Kepada Account {id:7} yang dimiliki oleh Alex.

   • Ke Account {id:16} milik Lee.

     

Library klien

Python

def query_data(instance_id, database_id):
    """Queries sample data from the database using GQL."""
    spanner_client = spanner.Client()
    instance = spanner_client.instance(instance_id)
    database = instance.database(database_id)

    with database.snapshot() as snapshot:
        results = snapshot.execute_sql(
            """Graph FinGraph
            MATCH (a:Person)-[o:Owns]->()-[t:Transfers]->()<-[p:Owns]-(b:Person)
            RETURN a.name AS sender, b.name AS receiver, t.amount, t.create_time AS transfer_at"""
        )

        for row in results:
            print("sender: {}, receiver: {}, amount: {}, transfer_at: {}".format(*row))

Java

static void query(DatabaseClient dbClient) {
  try (ResultSet resultSet =
      dbClient
          .singleUse() // Execute a single query against Cloud Spanner.
          .executeQuery(
              Statement.of(
                  "Graph FinGraph MATCH"
                      + " (a:Person)-[o:Owns]->()-[t:Transfers]->()<-[p:Owns]-(b:Person)RETURN"
                      + " a.name AS sender, b.name AS receiver, t.amount, t.create_time AS"
                      + " transfer_at"))) {
    while (resultSet.next()) {
      System.out.printf(
          "%s %s %f %s\n",
          resultSet.getString(0),
          resultSet.getString(1),
          resultSet.getDouble(2),
          resultSet.getTimestamp(3));
    }
  }
}

Go

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

	"cloud.google.com/go/spanner"

	"google.golang.org/api/iterator"
)

func queryGraphData(w io.Writer, db string) error {
	ctx := context.Background()
	client, err := spanner.NewClient(ctx, db)
	if err != nil {
		return err
	}
	defer client.Close()

	// Execute a Spanner query statement comprising a graph query. Graph queries
	// are characterized by 'MATCH' statements describing node and edge
	// patterns.
	//
	// This statement finds entities ('Account's) owned by all 'Person's 'b' to
	// which transfers have been made by entities ('Account's) owned by any
	// 'Person' 'a' in the graph called 'FinGraph'. It then returns the names of
	// all such 'Person's 'a' and 'b', and the amount and time of the transfer.
	stmt := spanner.Statement{SQL: `Graph FinGraph 
		 MATCH (a:Person)-[o:Owns]->()-[t:Transfers]->()<-[p:Owns]-(b:Person)
		 RETURN a.name AS sender, b.name AS receiver, t.amount, t.create_time AS transfer_at`}
	iter := client.Single().Query(ctx, stmt)
	defer iter.Stop()

	// The results are returned in tabular form. Iterate over the
	// result rows and print them.
	for {
		row, err := iter.Next()
		if err == iterator.Done {
			return nil
		}
		if err != nil {
			return err
		}
		var sender, receiver string
		var amount float64
		var transfer_at time.Time
		if err := row.Columns(&sender, &receiver, &amount, &transfer_at); err != nil {
			return err
		}
		fmt.Fprintf(w, "%s %s %f %s\n", sender, receiver, amount, transfer_at.Format(time.RFC3339))
	}
}

C++

void QueryData(google::cloud::spanner::Client client) {
  namespace spanner = ::google::cloud::spanner;

  spanner::SqlStatement select(R"""(
    Graph FinGraph
    MATCH (a:Person)-[o:Owns]->()-[t:Transfers]->()<-[p:Owns]-(b:Person)
    RETURN a.name AS sender,
           b.name AS receiver,
           t.amount,
          t.create_time AS transfer_at
  )""");
  using RowType =
      std::tuple<std::string, std::string, double, spanner::Timestamp>;
  auto rows = client.ExecuteQuery(std::move(select));
  for (auto& row : spanner::StreamOf<RowType>(rows)) {
    if (!row) throw std::move(row).status();
    std::cout << "sender: " << std::get<0>(*row) << "\t";
    std::cout << "receiver: " << std::get<1>(*row) << "\t";
    std::cout << "amount: " << std::get<2>(*row) << "\t";
    std::cout << "transfer_at: " << std::get<3>(*row) << "\n";
  }

  std::cout << "Query completed for [spanner_query_graph_data]\n";
}

Pembersihan

Banyak contoh di Langkah berikutnya yang menggunakan resource yang Anda siapkan dalam dokumen ini. Jika Anda ingin terus menggunakan Spanner Graph menggunakan salah satu contoh ini, jangan lakukan langkah-langkah pembersihan ini terlebih dahulu. Bagian ini menunjukkan cara menggunakan Konsol Google Cloud untuk membersihkan resource.

Jika tidak, untuk menghindari tagihan tambahan ke akun Penagihan Cloud, hapus database dan instance yang Anda buat selama penyiapan. Menghapus instance akan otomatis menghapus semua database yang dibuat dalam instance.

Menghapus database

1. Di konsol Google Cloud, buka halaman Spanner Instances.

   Buka instance Spanner

2. Klik nama instance yang memiliki database yang ingin Anda hapus, misalnya, Test Instance.

3. Klik nama database yang ingin Anda hapus, misalnya, example-db.

4. Di halaman Detail database, klik hapus Hapus database.

5. Konfirmasi bahwa Anda ingin menghapus database dengan memasukkan nama database dan mengklik Hapus.

Menghapus instance

Peringatan: Menghapus instance akan menghapus instance tersebut beserta semua databasenya secara permanen. Tindakan ini tidak dapat diurungkan. Selain itu, Anda tidak dapat membuat instance uji coba gratis lain setelah menghapus instance uji coba gratis pertama. Anda dapat membuat satu instance uji coba gratis per siklus proses project.

1. Di konsol Google Cloud, buka halaman Spanner Instances.

   Buka instance Spanner

2. Klik nama instance yang ingin Anda hapus, misalnya, Test Instance.

3. Klik Hapus instance.

4. Konfirmasi bahwa Anda ingin menghapus instance dengan memasukkan nama instance dan mengklik Hapus.

Langkah selanjutnya

• Pelajari Spanner Graph lebih lanjut menggunakan codelab.
• Pelajari skema Spanner Graph.
• Membuat, memperbarui, atau menghapus skema Grafik Spanner.
• Menyisipkan, memperbarui, atau menghapus data Spanner Graph.
• Ringkasan kueri Spanner Graph.
• Migrate to Spanner Graph.