Create, update, or drop a Spanner Graph schema

This document shows you how to create, update or drop a property graph in Spanner Graph using the example schema that you created in Set up and query Spanner Graph .

Spanner lets you make schema updates with no downtime. You can update the schema of an existing database in any of the following ways:

The Google Cloud console

Submit a command on the Spanner Studiopage.

To access the Spanner Studiopage, click Spanner Studiofrom the Database overview or Table overview page. For more information about accessing Spanner Studio, see Manage your data using the Google Cloud console .
The gcloud spanner command-line tool

Submit a command by using the gcloud spanner databases ddl update command.
The client libraries
The projects.instances.databases.updateDdl REST API
The UpdateDatabaseDdl RPC API

Create a property graph schema

For more information about property graph schemas, see the Spanner Graph schema overview .

To create a property graph schema, follow these steps:

Create node input tables Person and Account . These tables are used as input tables 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 the edge input tables PersonOwnAccount and AccountTransferAccount . These tables are used as input tables 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 the property graph using the example CREATE PROPERTY GRAPH statement.

The following example 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 
  
 );

This example follows these best practices:

For more information, see Best practices for graph schema design .

For information about common Spanner Graph schema errors, see Troubleshoot Spanner Graph .

Update a property graph schema

You can update a property graph schema in the following ways:

Add new node or edge definitions.
Update existing node or edge definitions.
Remove existing node or edge definitions.

In each case, you must recreate the property graph with the updated schema.

Add new node or edge definitions

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

Add new input tables.

Define the input tables associated with the new graph element definitions. See the following example, which adds two new input tables Company and 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 graph with CREATE OR REPLACE PROPERTY GRAPH . The following example updates the FinGraph schema by adding a new node definition Company and a new edge definition PersonInvestCompany :

  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 existing node or edge definitions

To update existing node or edge definitions, follow these steps:

Update the input table schema. The following example adds the mailing_address column to the input table Person .

  ALTER 
  
 TABLE 
  
 Person 
 ADD 
  
 COLUMN 
  
 mailing_address 
  
 STRING 
 ( 
 MAX 
 );

Update the property graph schema with CREATE OR REPLACE PROPERTY GRAPH . The following example adds a new property mailing_address to the Person node definition using the CREATE OR REPLACE PROPERTY GRAPH statement. In this example, the Person node definition automatically picks up the altered 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 
  
 );

You can also use the PROPERTIES clause() to customize the properties exposed from the input tables. For more information, see Customize labels and properties .

Remove existing node or edge definitions

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

The following example removes the Person node definition and the PersonOwnAccount edge definition.

  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

You can use the DROP PROPERTY GRAPH DDL statement to drop your graph schema.

The following example drops the FinGraph property graph schema:

  DROP 
  
 PROPERTY 
  
 GRAPH 
  
 FinGraph 
 ;