设置和查询 Spanner Graph

本文档介绍了如何使用 Google Cloud 控制台和客户端库设置和查询 Spanner Graph 数据库，以对复杂的数据关系进行建模、存储和分析。

以下主题可帮助您了解如何：

• 创建 Spanner 实例。

• 使用 Spanner Graph 架构创建数据库。

• 插入图表数据。

• 查询您创建的图表。

• 清理资源。

如需了解 Spanner 价格的详细信息，请参阅 Spanner 价格。

如需尝试 Codelab，请参阅Spanner Graph 使用入门。

准备工作

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.

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

Go to project selector

Verify that billing is enabled for your Google Cloud project.

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

Go to project selector

Verify that billing is enabled for your Google Cloud project.

1. 启用 Spanner API：
   前往“启用 Spanner API”

4. 如需获得创建实例和数据库所需的权限，请让您的管理员为您授予项目的 Cloud Spanner Admin (roles/spanner.admin) IAM 角色。

5. 如需获得查询 Spanner Graph 所需的权限（如果您未获得 Cloud Spanner Admin 角色），请让您的管理员为您授予项目的 Cloud Spanner Database Reader (roles/spanner.databaseReader) IAM 角色。

创建 Spanner 实例

首次使用 Spanner 时，必须创建一个实例，该实例会为 Spanner 数据库分配资源。本部分介绍如何使用 Google Cloud 控制台创建实例。

1. 在 Google Cloud 控制台中，前往 Spanner 页面。

   转到 Spanner

2. 选择或创建 Google Cloud 项目（如果您尚未执行此操作）。

3. 执行下列其中一项操作：

   1. 如果您之前未创建过 Spanner 实例，请在欢迎使用 Spanner 页面上点击创建预配实例。

   2. 如果您已创建 Spanner 实例，请在实例页面上点击创建实例。

4. 在选择版本页面上，选择企业 Plus 版或企业版。

   Spanner Graph 仅适用于企业版或企业 Plus 版。如需比较不同版本，请点击版本对比。如需了解详情，请参阅 Spanner 版本概览。

5. 点击继续。

6. 在实例名称中，输入实例名称，例如 test-instance。

7. 在实例 ID 中，保留或更改实例 ID。实例 ID 默认为实例名称，但您可以更改它。实例名称和实例 ID 可以相同，也可以不同。

8. 点击继续。

9. 在选择配置中，执行以下操作：

   1. 保持选择区域级。

   2. 在选择配置中，选择一个区域。Spanner 会在您选择的区域中存储和复制实例。

   3. 点击继续。

10. 在配置计算容量中，执行以下操作：

    1. 在选择单元中，选择处理单元 (PU)。

    2. 在选择扩缩模式中，保持手动分配处于选中状态，并在数量中保留 1,000 个处理单元。

11. 点击创建。 Google Cloud 控制台会显示您创建的实例的概览页面。

使用架构创建图表数据库

本部分介绍了如何使用 Spanner Graph 架构创建 Spanner Graph 数据库。您可以使用 Google Cloud 控制台或客户端库。

控制台

1. 在 Google Cloud 控制台中，前往 Spanner 实例页面。

   前往 Spanner 实例

2. 点击您创建的实例，例如 Test Instance。

3. 在概览中，点击实例名称下的创建数据库。

4. 在数据库名称中，输入数据库名称。例如 example-db。

5. 在选择数据库方言中，选择 Google 标准 SQL。PostgreSQL 方言不支持 Spanner Graph。

6. 在 DDL 模板编辑器标签页中输入以下架构。该架构包含两个节点表定义（Person 和 Account）和两个边缘表定义（PersonOwnAccount 和 AccountTransferAccount）。Spanner Graph 使用关系型表来定义图表，因此您会在架构中看到关系型表和图表语句。如需详细了解 Spanner Graph 架构，请参阅 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
     );
   

7. 保留显示加密选项的默认设置。

8. 点击创建。 Google Cloud 控制台将显示您创建的数据库的概览页面。

客户端库

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";
}

插入图表数据

创建数据库后，您可以向其中添加数据。本部分介绍了如何使用 Google Cloud 控制台的图形界面或通过客户端库以程序化方式将数据插入图表中。

控制台

1. 在 Spanner Studio 页面上，点击 add 新标签页或使用编辑器标签页。

2. 将以下图表数据插入语句复制并粘贴到节点和边缘中。

   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. 点击运行。运行完成后，结果标签页会显示插入了 3 行。

客户端库

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";
}

下图显示了插入项中的人员、账号、账号所有权和账号转移情况：



运行图表查询

将数据插入图表后，您可以查询该图表以查找模式和关系。本部分介绍了如何使用Google Cloud 控制台的图形界面或通过客户端库以程序化方式运行查询。

控制台

1. 在数据库概览页面上，点击导航菜单中的 Spanner Studio。

2. 在 Spanner Studio 页面上，点击 add 新标签页或使用编辑器标签页。

3. 在查询编辑器中输入以下查询。该查询会查找 Dana 向其汇款的所有人，以及这些汇款的金额。

   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. 点击运行。

   结果标签页显示账号 20 的所有者 Dana 的以下路径：

   • 汇款给 Alex 拥有的 Account {id:7}。

   • 汇款给 Lee 拥有的 Account {id:16}。

     

客户端库

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";
}

清理

本部分介绍了如何清理您创建的资源，以避免您的 Cloud Billing 账号产生费用。如果您打算在后续步骤部分中探索其他 Spanner Graph 示例，请暂时不要执行以下步骤。删除 Spanner 实例也会删除该实例中的所有数据库。

删除数据库

1. 在 Google Cloud 控制台中，前往 Spanner 实例。

   转到 Spanner

2. 点击实例名称，例如 Test Instance。

3. 点击数据库名称，例如 example-db。

4. 在数据库详细信息页面上，点击删除数据库。

5. 输入数据库名称，然后点击删除，以确认您要删除该数据库。

删除实例

1. 在 Google Cloud 控制台中，前往 Spanner 实例页面。

   前往 Spanner 实例

2. 点击要删除的实例的名称，例如 Test Instance。

3. 点击删除实例。

4. 输入实例名称，然后点击删除，以确认您要删除该实例。

后续步骤

• 如需详细了解 Spanner Graph，请使用codelab。
• 了解 Spanner Graph架构。
• 创建、更新或删除 Spanner Graph 架构。
• 插入、更新或删除 Spanner Graph 数据。
• Spanner Graph 查询概览。
• 迁移到 Spanner Graph。