This page describes how to create and manage data placements in Spanner.
For more information about how geo-partitioning works, see the Geo-partitioning overview.
Create a data placement
After you create your Spanner partitions and databases, create your placement.
Console
Go to the Instances page in the Google Cloud console.
Select the instance with user-created partition(s).
Select the database that you want to partition data.
In the navigation menu, click Spanner Studio.
In the Spanner Studio page, click
New tab or use the empty editor tab.Enter the
CREATE PLACEMENT
DDL statement.For example, you can run the following to create a placement table
europeplacement
in the partitioneurope-partition
:CREATE PLACEMENT `europeplacement` OPTIONS(instance_partition="europe-partition");
Optional: You can also use the Object Explorer pane to view, search, and interact with your Placement objects. For more information, see Explore your data.
Click Run.
gcloud
To create a placement with the gcloud CLI command, use
gcloud spanner databases ddl update
.
For example, create a placement in the partition europe-partition
:
gcloud spanner databases ddl update example-db \
--instance=test-instance \
--ddl="CREATE PLACEMENT europeplacement OPTIONS (instance_partition='europe-partition')"
Delete a data placement
You can't delete a placement. You must delete the database to delete the placement and its associated data. For more information, see Delete a database.
Create a table with a placement key
Console
- In the navigation menu, click Spanner Studio.
- In the Spanner Studio page, click New tab or use the empty editor tab.
Enter the
CREATE TABLE
DDL statement.For example, you can create a
Singers
table that uses a placement key to partition singer data:CREATE TABLE Singers ( SingerId INT64 NOT NULL, Name STRING(MAX) NOT NULL, ... Location STRING(MAX) NOT NULL PLACEMENT KEY ) PRIMARY KEY (SingerId);
gcloud
To create a table, use
gcloud spanner databases ddl update
.
For example, you can create a Singers
table that uses
a placement key to partition singer data:
gcloud spanner databases ddl update example-db \
--instance=test-instance \
--ddl="CREATE TABLE Singers ( SingerId INT64 NOT NULL, SingerName STRING(MAX) NOT NULL, Location STRING(MAX) NOT NULL PLACEMENT KEY ) PRIMARY KEY (SingerId)"
Edit a table with a placement key
You can't drop a placement key from a table. You also can't add a placement key
to a table after it has been created. However, you can use the
ALTER TABLE
DDL statement to change other fields in the
table, for example, adding and dropping non-placement key columns.
Delete a table with a placement key
Before you delete a table with a placement key, you must first:
- Delete all rows in the placement table.
- Wait for the
version_retention_period
for the database to pass. For more information, see Point-in-time recovery. Then, following these steps:
Console
- In the navigation menu, click Spanner Studio.
- In the Spanner Studio page, click New tab or use the empty editor tab.
Enter the
DROP TABLE
DDL statement.For example, drop the
Singers
table:DROP TABLE Singers;
gcloud
To drop a table, use
gcloud spanner databases ddl update
.
For example, drop the Singers
table:
gcloud spanner databases ddl update example-db \
--instance=test-instance \
--ddl="DROP TABLE Singers"
Insert a row in a placement table
Console
- In the navigation menu, click Spanner Studio.
- In the Spanner Studio page, click New tab or use the empty editor tab.
Enter the
INSERT INTO
DDL statement.For example, add a singer, Marc Richards, to the
Singers
table and partition it ineuropeplacement
:INSERT INTO Singers(SingerId, Name, Location) VALUES (1, 'Marc Richards', 'europeplacement')
gcloud
To write data to a table, use
gcloud spanner rows insert
.
For example, add a singer, Marc Richards, to the Singers
table and
partition it in europeplacement
:
gcloud spanner rows insert --table=Singers --database=example-db \
--instance=test-instance --data=SingerId=1,SingerName='Marc Richards',Location='europeplacement'
Update a row in a placement table
Console
- In the navigation menu, click Spanner Studio.
- In the Spanner Studio page, click New tab or use the empty editor tab.
Use DML or mutations to update data in a placement table.
For example, update the name of
singerid=1
in theSingers
table toCatalina Smith
:UPDATE Singers s SET s.name='Catalina Smith' WHERE s.id=1;
gcloud
To update data in a placement table,
use gcloud spanner rows update
.
For example, update the name of singerid=1
in the Singers
table to
Catalina Smith
:
gcloud spanner rows update --table=Singers --database=example-db \
--instance=test-instance --data=SingerId=1,SingerName='Catalina Smith'
Move a row in a placement table
Console
- Create a new partition and placement if you haven't already.
- In the navigation menu, click Spanner Studio.
- In the Spanner Studio page, click New tab or use the empty editor tab.
Use DML or mutations to move data to the new partition.
For example, move
singerid=1
in theSingers
table toasiaplacement
:UPDATE Singers s SET s.location='asiaplacement' WHERE s.id=1;
gcloud
After creating the partition and placement where you want to move your data,
use gcloud spanner rows update
.
For example, move singerid=1
in the Singers
table to asiaplacement
:
gcloud spanner rows update --table=Singers --database=example-db \
--instance=test-instance --data=SingerId=1,Location='asiaplacement'
Delete a row in a placement table
Console
gcloud
To delete data, use gcloud spanner rows delete
.
For example, delete singerid=1
in the Singers
table:
gcloud spanner rows delete --table=Singers --database=example-db \
--instance=test-instance --keys=1
Query data in a placement table
Console
- In the navigation menu, click Spanner Studio.
- In the Spanner Studio page, click New tab or use the empty editor tab.
Run your query.
For example, query the
Singers
table:SELECT * FROM Singers s WHERE s.SingerId=1;
gcloud
To query data, use gcloud spanner databases execute-sql
.
For example, query the Singers
table:
gcloud spanner databases execute-sql example-db \
--sql='SELECT * FROM Singers s WHERE s.SingerId=1'
What's next
Learn more about geo-partitioning.
Learn how to create and manage partitions.