This document describes how to mutate data in Spanner Graph. Mutating graph data includes inserting, updating, and deleting nodes and edges in Spanner Graph.
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. For more information, see the Spanner Graph schema overview.
Before you begin
Set up and query Spanner Graph.
Insert nodes or edges
To insert nodes or edges, use the Google Cloud console, the Google Cloud CLI, or the Spanner Client Libraries to insert rows into node or edge tables.
In the Google Cloud console and in the Google Cloud CLI, you can use SQL Data Manipulation Language (DML) to insert. 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
graph:
Console
In the Google Cloud console, enter the following DML statement and click Run Query:
-- 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
- Execute statements with the gcloud CLI.
- In the gcloud CLI, run the following commands:
gcloud spanner databases execute-sql example-db --instance=test-instance \ --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 example-db --instance=test-instance \ --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 example-db --instance=test-instance \ --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 example-db --instance=test-instance \ --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)"
Update nodes or edges
To update existing nodes or edges, 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 SQL Data Manipulation Language (DML) to update. In the Spanner Client Library, you can use either DML or Mutation APIs.
The following examples update an Account node and a Transfer edge in the
graph.
Console
In the Google Cloud console, enter the following DML statement and click Run Query:
-- 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 example-db --instance=test-instance \ --sql="UPDATE Account SET is_blocked = false WHERE id = 2"gcloud spanner databases execute-sql example-db --instance=test-instance \ --sql="UPDATE AccountTransferAccount SET amount = 300 WHERE id = 1 AND to_id = 2"
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 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
}
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 Google Cloud console and the gcloud CLI, you can use SQL Data Manipulation Language (DML) to delete. In the Spanner Client Library, you can use DML or Mutation APIs.
Before you delete a node, make sure no edges exist that refer to the node. If these types of edges exist, you might get referential integrity violation errors. 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
- Run statements in the Google Cloud console.
- In the Google Cloud console, enter the following DML statement and click Run Query:
-- Delete Transfer edge
DELETE FROM AccountTransferAccount
WHERE id = 1 AND to_id = 2;
-- Delete Account node
DELETE FROM Account WHERE id = 2;
gcloud
- Execute statements with the gcloud CLI.
- In the gcloud CLI, run the following commands:
gcloud spanner databases execute-sql example-db --instance=test-instance \ --sql="DELETE FROM AccountTransferAccount WHERE id = 1 AND to_id = 2"gcloud spanner databases execute-sql example-db --instance=test-instance \ --sql="DELETE FROM Account WHERE id = 2"
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
}
Options for mutating graph data
You can automatically mutate graph data in the following ways:
- Automatically delete edges in the graph by using the ON DELETE CASCADE action.
- Automatically delete nodes and edges in the graph using the TTL policy. For more information, see TTL on nodes and edges.
To efficiently bulk update and delete nodes and edges in the graph, use partitioned DML.