Manage Spanner Graph data
This document shows you how to manage data in a graph by inserting, updating, and deleting nodes and edges. Spanner Graph maps data from tables to graph nodes and edges. To mutate data in a graph, you must mutate data in the corresponding input tables. You can use the Google Cloud console, the Google Cloud CLI, or Spanner client libraries to manage graph data.
Set up your Spanner Graph
Before you can manage data in Spanner Graph, you must set up a graph by doing the following:
Create a database in your Spanner instance.
Insert graph data into your database.
The examples in these sections use the instance and database that you created when you set up your Spanner Graph with the previous steps.
Insert nodes or edges
To insert nodes or edges into node or edge tables, use the Google Cloud console, the gcloud CLI, or the Spanner client libraries.
In the Google Cloud console and in the gcloud CLI, you can use GoogleSQL Data Manipulation Language (DML). In the Spanner client library, you can use DML or Mutation APIs.
Before you insert an edge, make sure that the source and destination nodes connected by the edge exist. If you insert an edge when the source or destination node connected by the edge doesn't exist, you might get referential integrity violation errors. For more information, see Missing source node violates INTERLEAVE IN relationship and Missing destination node violates foreign key constraint.
The following examples insert Account nodes and Transfer edges into the
database you created in Set up your Spanner Graph.
Console
In the Google Cloud console, run the following DML statement. For more information, see Run statements in the Google Cloud console.
-- Insert 2 Account nodes.
INSERT INTO Account (id, create_time, is_blocked)
VALUES (1, CAST('2000-08-10 08:18:48.463959-07:52' AS TIMESTAMP), false);
INSERT INTO Account (id, create_time, is_blocked)
VALUES (2, CAST('2000-08-12 07:13:16.463959-03:41' AS TIMESTAMP), true);
-- Insert 2 Transfer edges.
INSERT INTO AccountTransferAccount (id, to_id, create_time, amount)
VALUES (1, 2, CAST('2000-09-11 03:11:18.463959-06:36' AS TIMESTAMP), 100);
INSERT INTO AccountTransferAccount (id, to_id, create_time, amount)
VALUES (1, 1, CAST('2000-09-12 04:09:34.463959-05:12' AS TIMESTAMP), 200);
gcloud
In the gcloud CLI, run the following commands. For more information, see Execute statements with the gcloud CLI.
gcloud spanner databases execute-sql DATABASE-NAME --instance=INSTANCE-NAME \ --sql="INSERT INTO Account (id, create_time, is_blocked) VALUES (1, CAST('2000-08-10 08:18:48.463959-07:52' AS TIMESTAMP), false)"gcloud spanner databases execute-sql DATABASE-NAME --instance=INSTANCE-NAME \ --sql="INSERT INTO Account (id, create_time, is_blocked) VALUES (2, CAST('2000-08-12 07:13:16.463959-03:41' AS TIMESTAMP), true)"gcloud spanner databases execute-sql DATABASE-NAME --instance=INSTANCE-NAME \ --sql="INSERT INTO AccountTransferAccount (id, to_id, create_time, amount) VALUES (1, 2, CAST('2000-09-11 03:11:18.463959-06:36' AS TIMESTAMP), 100)"gcloud spanner databases execute-sql DATABASE-NAME --instance=INSTANCE-NAME \ --sql="INSERT INTO AccountTransferAccount (id, to_id, create_time, amount) VALUES (1, 1, CAST('2000-09-12 04:09:34.463959-05:12' AS TIMESTAMP), 200)"
Replace the following:
- DATABASE_NAME: the name of your database.
- INSTANCE_NAME: the name of your instance.
Client libraries
Update nodes or edges
To update existing nodes or edges, use the Google Cloud console, the gcloud CLI, or the Spanner client libraries.
You can update existing nodes or edges using a GoogleSQL Data Manipulation Language (DML) statement, or Spanner Graph queries with a DML statement. If you use a Spanner client library, you can also use Mutation APIs.
Update nodes or edges with DML
The following examples use DML to update the Account node and Transfer edge
that you added in Insert nodes or edges.
Console
In the Google Cloud console, run the following DML statement. For more information, see Run statements in the Google Cloud console.
-- Update Account node
UPDATE Account SET is_blocked = false WHERE id = 2;
-- Update Transfer edge
UPDATE AccountTransferAccount
SET amount = 300
WHERE id = 1 AND to_id = 2;
gcloud
- Execute statements with the gcloud CLI.
- In the gcloud CLI, run the following commands:
gcloud spanner databases execute-sql DATABASE-NAME --instance=INSTANCE-NAME \ --sql="UPDATE Account SET is_blocked = false WHERE id = 2"gcloud spanner databases execute-sql DATABASE-NAME --instance=INSTANCE-NAME \ --sql="UPDATE AccountTransferAccount SET amount = 300 WHERE id = 1 AND to_id = 2"
Replace the following:
- DATABASE_NAME: the name of your database.
- INSTANCE_NAME: the name of your instance.
Client libraries
Update nodes or edges with graph queries and DML
The following examples use Spanner Graph queries with DML to update the
Account node and Transfer edge that you added in
Insert nodes or edges.
Console
In the Google Cloud console, run the following DML statement. For more information, see Run statements in the Google Cloud console.
-- Use Graph pattern matching to identify Account nodes to update:
UPDATE Account SET is_blocked = false
WHERE id IN {
GRAPH FinGraph
MATCH (a:Account WHERE a.id = 1)-[:Transfers]->{1,2}(b:Account)
RETURN b.id
}
gcloud
In the gcloud CLI, run the following commands. For more information, see Execute statements with the gcloud CLI.
gcloud spanner databases execute-sql DATABASE-NAME --instance=INSTANCE_NAME \ --sql="UPDATE Account SET is_blocked = false"gcloud spanner databases execute-sql DATABASE-NAME --instance=INSTANCE-NAME \ --sql="UPDATE AccountTransferAccount SET amount = 300 WHERE id = 1 AND to_id = 2" --sql=" WHERE id IN { GRAPH FinGraph MATCH (a:Account WHERE a.id = 1)-[:Transfers]->{1,2}(b:Account) RETURN b.id }"
Replace the following:
- DATABASE_NAME: the name of your database.
- INSTANCE_NAME: the name of your instance.
Client libraries
Delete nodes or edges
To delete existing nodes or edges, use the Google Cloud console, the gcloud CLI, or the Spanner client libraries.
In the the Google Cloud console and the gcloud CLI, you use GoogleSQL Data Manipulation Language (DML) to delete. In the Spanner client library, you can use DML or Mutation APIs to delete nodes or edges.
To prevent referential integrity violation errors, make sure no edges refer to a node when you delete the node. For more information, see Orphaned outgoing edge violates parent-child relationship and Orphaned incoming edge violates parent-child relationship.
The following examples delete a Transfer edge and an Account node from the
graph.
Console
In the Google Cloud console, run the following DML statement. For more information, see Run statements in the Google Cloud console.
-- Delete Transfer edge
DELETE FROM AccountTransferAccount
WHERE id = 1 AND to_id = 2;
-- Delete Account node
DELETE FROM Account WHERE id = 2;
gcloud
In the gcloud CLI, run the following commands. For more information, see Execute statements with the gcloud CLI.
gcloud spanner databases execute-sql DATABASE-NAME --instance=INSTANCE-NAME \ --sql="DELETE FROM AccountTransferAccount WHERE id = 1 AND to_id = 2"gcloud spanner databases execute-sql DATABASE-NAME --instance=INSTANCE-NAME \ --sql="DELETE FROM Account WHERE id = 2"
Replace the following:
- DATABASE_NAME: the name of your database.
- INSTANCE_NAME: the name of your instance.
Client libraries
You can combine Spanner Graph queries with your DML statement, as shown in the following example:
-- Use Graph pattern matching to identify Account nodes to delete:
DELETE FROM AccountTransferAccount
WHERE id IN {
GRAPH FinGraph
MATCH (a:Account WHERE a.id = 1)-[:Transfers]->(b:Account)
RETURN b.id
}
Automated and bulk data operations
In addition to using DML to insert, update, and delete individual nodes and edges, you can also use the following methods to manage your Spanner Graph data:
You can automate deletion of edges in a graph by using the ON DELETE CASCADE action.
You can automate deletion of nodes and edges in the graph using a TTL policy. For more information, see TTL on nodes and edges.
Efficiently bulk update and delete nodes and edges in the graph using partitioned DML.