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 
 ;