This document shows you how to manage data in a Spanner 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 a Spanner Graph, you must set up a Spanner 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
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. 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
Python
Java
Go
C++
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
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 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
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
}
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 .
What's next
- Read the Spanner Graph queries overview .
- Learn best practices for tuning Spanner Graph queries .
