Set up and query Spanner Graph using the Google Cloud console

Preview — Spanner Graph

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

Note: This feature is available with the Spanner Enterprise edition and Enterprise Plus edition. For more information, see the Spanner editions overview.

This document shows you how to set up and query Spanner Graph using the Google Cloud console. In this document, you will:

• Create a Spanner instance.
• Create a database.
• Create a graph schema.
• Insert graph data.
• Query the graph you created.

To learn about Spanner pricing details, see Spanner pricing.

To try out a codelab, see Getting started with Spanner Graph.

Before you begin

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. The Spanner API should be auto-enabled. If not, enable it manually:
   Enable Spanner API

4. To get the permissions that you need to create instances and databases, ask your administrator to grant you the Cloud Spanner Admin (roles/spanner.admin) IAM role on your project.

Create an instance

When you first use Spanner, you must create an instance, which is an allocation of resources that are used by Spanner databases in that instance.

1. In Google Cloud console, go to the Spanner page.

   Go to Spanner

2. Select or create a Google Cloud project if you haven't done so already.

3. On the Spanner page, click CREATE INSTANCE. If you've used Spanner before, you'll see the Spanner Instances page instead of the product page.

Note: To explore Spanner using a 90-day free trial instance, see Create a Spanner free trial instance.

1. In the Name your instance page, enter an instance name, for example, Test Instance. The instance ID is automatically entered based on the instance name, for example, as test-instance. Change it, if required.
2. Click Continue.
3. In the Configure your instance page, keep the default option Regional and select a configuration from the drop-down menu. Your instance configuration determines the geographic location where your instances are stored and replicated.
4. Click Continue.
5. In the Allocate compute capacity page, select Processing units (PUs) and keep the default value of 1000 processing units.
6. Click Create. The Google Cloud console displays the Overview page for the instance you created.

Create a database

1. In Google Cloud console, go to the Spanner Instances page.

   Go to Spanner instances

2. Click the instance you created, for example, Test Instance.

3. In the instance Overview page that opens, click Create database.

4. For the database name, enter a name, for example, example-db.

5. For the database dialect, choose Google Standard SQL. Spanner Graph isn't available in the PostgreSQL dialect. Your database creation page now looks like this:

   

6. Click Create.

   Google Cloud console displays the Overview page for the database you created.

Create a schema for your Spanner Graph database

1. In the navigation menu, click Spanner Studio.
2. In the Spanner Studio page, click New tab or use the editor tab.

3. Copy and paste the following schema into the editor tab. The schema contains two node table definitions, Person and Account, and two edge table definitions, PersonOwnAccount and AccountTransferAccount. Spanner Graph uses relational tables to define graphs, so you see both relational tables and graph statements in the schema. To learn more about the Spanner Graph schema, see Spanner Graph schema overview.

   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
     );
   

4. Click Run. The Results page shows that schema updates are running. When the update is complete, the page looks like the following:

   

5. To view the property graph schema you defined, click Show Equivalent DDL.

Insert graph data

Google Cloud console provides an interface for inserting, editing, and deleting data.

Insert data

1. In the Spanner Studio page, click New tab or use the editor tab.

2. Copy and paste the following graph data insert statements into the nodes and edges.

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

   The following graph shows the people, accounts, account ownership, and account transfers from the inserts:

   

3. Click Run.

   When the run is finished, the Results tab displays the result of inserted rows.

Run a graph query

1. On the database Overview page, click Spanner Studio in the navigation menu.
2. In the Spanner Studio page, click New tab or use the editor tab.

3. Enter the following query in the query editor. The query finds everyone that Dana transferred money to, and the amount of those transfers.

   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. Click Run.

   The Results tab displays the following paths from Dana through Account {id:20}:

   • To Account {id:7} owned by Alex.

   • To Account {id:16} owned by Lee.

     

Clean up

Many of the examples in What's next make use of the resources that you set up in this document. If you want to continue working with Spanner Graph using one of these examples, don't perform these cleanup steps yet.

Otherwise, to avoid additional charges to your Cloud Billing account, delete the database and the instance that you created during setup. Deleting an instance automatically deletes all databases created in the instance.

Delete the database

1. In Google Cloud console, go to the Spanner Instances page.

   Go to Spanner instances

2. Click the name of the instance that has the database that you want to delete, for example, Test Instance.

3. Click the name of the database that you want to delete, for example, example-db.

4. In the Database details page, click delete Delete database.

5. Confirm that you want to delete the database by entering the database name and clicking Delete.

Delete the instance

Warning: Deleting an instance permanently removes the instance and all its databases. You can't undo this later. Also, you can't create another free trial instance once you delete your first free trial instance. You can create one free trial instance per project lifecycle.

1. In Google Cloud console, go to the Spanner Instances page.

   Go to Spanner instances

2. Click the name of the instance that you want to delete, for example, Test Instance.

3. Click Delete instance.

4. Confirm that you want to delete the instance by entering the instance name and clicking Delete.

What's next

• Learn more about Spanner Graph using a codelab.
• Learn about the Spanner Graph schema.
• Create, update, or drop a Spanner Graph schema.
• Insert, update, or delete Spanner Graph data.
• Spanner Graph queries overview.
• Migrate to Spanner Graph.