This document describes how to mutate data in Spanner Graph using the Google Cloud console, the Google Cloud CLI, and client libraries. 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
To complete the Google Cloud console steps and the code samples in this document, you must first follow the steps in Set up and query Spanner Graph to do the following:
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 GoogleSQL 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)"
Client libraries
Python
Java
Go
C++
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. In the Spanner client library, you can also use Mutation APIs .
Update nodes or edges with DML
The following examples update an Account
node and a Transfer
edge in the
graph using DML:
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"
Client libraries
Python
Java
Go
C++
Update nodes or edges with graph queries and DML
The following examples update an Account
node and a Transfer
edge in the
graph using Spanner Graph queries with DML:
Console
-
In the Google Cloud console, enter the following Spanner Graph query with a DML statement, then click Run Query:
-- 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
- 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"gcloud spanner databases execute-sql example-db --instance = test-instance \ --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 }"
Client libraries
Python
Java
Go
C++
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 GoogleSQL 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"
Client libraries
Python
Java
Go
C++
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 .
What's next
- Read the Spanner Graph queries overview .
- Learn best practices for tuning Spanner Graph queries .
