设置和查询 Spanner Graph

本文档介绍了如何使用 Google Cloud 控制台和客户端库设置和查询 Spanner 图。以下主题可帮助您了解如何:

如需了解 Spanner 价格详情,请参阅 Spanner 价格

如需试用 Codelab,请参阅开始使用 Spanner Graph

准备工作

  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.

    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.

    Go to project selector

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

  6. Spanner API 应自动启用。如果未启用,请手动启用:

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

创建实例

首次使用 Spanner 时,您必须创建一个实例,实例是 Spanner 数据库使用的资源分配单位。本部分介绍如何使用 Google Cloud 控制台创建实例。

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

    转到 Spanner

  2. 选择或创建 Google Cloud 项目(如果您尚未创建)。

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

    1. 如果您之前未创建过 Spanner 实例,请在 Welcome to Spanner(欢迎使用 Spanner)页面上点击 Create a provisioned instance(创建预配实例)。

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

  4. 选择版本页面上,选择企业 Plus 版企业版

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

  5. 点击继续

  6. 实例名称中,输入实例名称,例如 test-instance

  7. 实例 ID 中,保留或更改实例 ID。实例 ID 默认为实例名称,但您可以对其进行更改。实例名称和实例 ID 可以相同,也可以不同。

  8. 点击继续

  9. 选择配置中,执行以下操作:

    1. 保持区域级的选择状态。

    2. 选择配置中,选择一个区域。您选择的区域是您的实例的存储和复制位置。

    3. 点击继续

  10. 配置计算容量中,执行以下操作:

    1. 选择单元中,选择处理单元 (PU)

    2. 选择伸缩模式中,保持手动分配的选择状态,并在数量中保持 1000 个处理单元。

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

使用 Spanner Graph 架构创建数据库

本部分介绍了如何使用 Google Cloud 控制台和客户端库创建具有 Spanner 图架构的数据库。

控制台

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

转到 Spanner 实例

  1. 点击您创建的实例,例如 Test Instance

  2. 概览中,点击实例名称下方的创建数据库

  3. 数据库名称中,输入数据库名称。例如 example-db

  4. 选择数据库方言中,选择 Google 标准 SQL。PostgreSQL 方言不支持 Spanner Graph。数据库创建页面现在如下所示:

    更新了数据库创建页面。

  5. 将以下架构复制并粘贴到 DDL 模板编辑器标签页中。该架构包含两个节点表定义(PersonAccount)以及两个边表定义(PersonOwnAccountAccountTransferAccount)。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
      );
    
  6. 请勿在显示加密选项中进行任何更改。

  7. 点击创建。 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 控制台和客户端库将数据插入 Spanner 图。

控制台

  1. Spanner Studio 页面上,点击 New tab(新建标签页),或使用“Editor”(编辑器)标签页。

  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 控制台或客户端库运行 Spanner 图架构查询。

控制台

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

  2. Spanner Studio 页面上,点击 New tab(新建标签页),或使用“Editor”标签页。

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

    结果标签页会显示从 Dana 到 Account {id:20} 的以下路径:

    • 发送给 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";
}

清理

后续步骤中的许多示例都使用了您在本文档中设置的资源。如果您想继续使用以下示例之一处理 Spanner 图,请暂时不要执行这些清理步骤。本部分介绍了如何使用 Google Cloud 控制台清理资源。

否则,为避免您的 Cloud Billing 账号产生额外费用,请删除您在设置过程中创建的数据库和实例。删除实例会自动删除在该实例中创建的所有数据库。

删除数据库

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

    转到 Spanner 实例

  2. 点击包含要删除的数据库的实例的名称,例如测试实例

  3. 点击要删除的数据库的名称,例如 example-db

  4. 数据库详细信息页面上,点击“删除”图标 Delete database

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

删除实例

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

    转到 Spanner 实例

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

  3. 点击删除实例

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

后续步骤