Manage a Spanner Graph schema

This document provides a comprehensive guide on managing Spanner Graph property schemas, detailing the processes for creating, updating, and dropping schemas using DDL statements.

For more information about property graph schemas, see the Spanner Graph schema overview. If you encounter errors when you create a property graph schema, see Troubleshoot Spanner Graph.

Create a property graph schema

To create a property graph schema, do the following:

Create the node input tables.
Create the edge input tables.
Define the property graph.

When you create a property graph schema, be sure to consider the best practices.

The following sections show how to create an example property graph schema:

Create node input tables

The following creates two node input tables, Person and Account, which serve as input for the node definitions in the example property 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 edge input tables

The following code creates two edge input tables, PersonOwnAccount and AccountTransferAccount, as input for the edge definitions in the example property graph:

  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;

Define a property graph

The following code defines the property graph using the CREATE PROPERTY GRAPH statement. This statement defines a property graph named FinGraph with Account and Person nodes, and PersonOwnAccount and AccountTransferAccount edges:

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

Update a property graph schema

After you create a property graph schema, you update it by using the CREATE OR REPLACE PROPERTY GRAPH statement. This statement applies the changes by recreating the graph schema with the desired update.

You can make the following changes to a property graph schema:

Add a node or edge definition: Create the new input tables for the nodes and edges, and then use the CREATE OR REPLACE PROPERTY GRAPH statement to add the new definitions to the graph.
Update a node or edge definition: Update the underlying input table with new node and edge definitions. Then, use the CREATE OR REPLACE PROPERTY GRAPH statement to update the definitions in the graph.
Remove a node or edge definition: Use the CREATE OR REPLACE PROPERTY GRAPH statement and omit the definitions that you want to remove from the graph.

Add new node or edge definitions

To add a new node and a new edge definition, follow these steps:

Add a new node definition input table, Company, and a new edge definition input table, PersonInvestCompany.

CREATE TABLE Company (
  id INT64 NOT NULL,
  name STRING(MAX)
) PRIMARY KEY (id);

CREATE TABLE PersonInvestCompany (
  id INT64 NOT NULL,
  company_id INT64 NOT NULL,
  FOREIGN KEY (company_id) REFERENCES Company (id)
) PRIMARY KEY (id, company_id),
  INTERLEAVE IN PARENT Person ON DELETE CASCADE;

Update the FinGraph schema by adding the new Company node definition and the new PersonInvestCompany edge definition.

CREATE OR REPLACE PROPERTY GRAPH FinGraph
  NODE TABLES (
    Person,
    Account,
    Company
  )
  EDGE TABLES (
    AccountTransferAccount
      SOURCE KEY (id) REFERENCES Account
      DESTINATION KEY (to_id) REFERENCES Account
      LABEL Transfers,
    PersonOwnAccount
      SOURCE KEY (id) REFERENCES Person
      DESTINATION KEY (account_id) REFERENCES Account
      LABEL Owns,
    PersonInvestCompany
      SOURCE KEY (id) REFERENCES Person
      DESTINATION KEY (company_id) REFERENCES Company
      LABEL Invests
  );

Update node or edge definitions

To update an existing node or edge definition, you first alter the underlying input table, and then use the CREATE OR REPLACE PROPERTY GRAPH statement to apply the schema changes to the graph. To customize the properties exposed from the input tables, use the PROPERTIES clause. For more information, see Customize labels and properties.

The following steps show how to update the underlying table of a schema, then apply the update to the schema.

Add the mailing_address column to the Person underlying input table.

ALTER TABLE Person
ADD COLUMN mailing_address STRING(MAX);

Apply the changes to the Person table to the schema. Use the CREATE OR REPLACE PROPERTY GRAPH statement. The Person node definition reflects the updated Person table definition because the input table schema changed.

CREATE OR REPLACE PROPERTY GRAPH FinGraph
  NODE TABLES (
    Person,
    Account
  )
  EDGE TABLES (
    AccountTransferAccount
      SOURCE KEY (id) REFERENCES Account
      DESTINATION KEY (to_id) REFERENCES Account
      LABEL Transfers,
    PersonOwnAccount
      SOURCE KEY (id) REFERENCES Person
      DESTINATION KEY (account_id) REFERENCES Account
      LABEL Owns
  );

Remove node or edge definitions

To remove existing node or edge definitions, recreate the property graph without those node or edge tables.

The following removes the Person node definition and the PersonOwnAccount edge definition by omitting them in the CREATE OR REPLACE PROPERTY GRAPH statement.

  CREATE OR REPLACE PROPERTY GRAPH FinGraph
    NODE TABLES (
      Account
    )
    EDGE TABLES (
      AccountTransferAccount
        SOURCE KEY (id) REFERENCES Account
        DESTINATION KEY (to_id) REFERENCES Account
        LABEL Transfers
    );

Drop a property graph schema

To drop a graph schema from the underlying input tables, use the DROP PROPERTY GRAPH DDL statement. You can't delete the data from the underlying table when you drop a schema.

The following code drops the FinGraph property graph schema:

DROP PROPERTY GRAPH FinGraph;