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.
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
(GoogleSQL, PostgreSQL) 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");
CREATE PLACEMENT europeplacement WITH (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.
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')"
gcloud spanner databases ddl update example-db \
--instance=test-instance \
--ddl="CREATE PLACEMENT europeplacement WITH (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
- 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
(GoogleSQL, PostgreSQL) 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, SingerName STRING(MAX) NOT NULL, ... Location STRING(MAX) NOT NULL PLACEMENT KEY ) PRIMARY KEY (SingerId);
CREATE TABLE Singers ( SingerId bigint PRIMARY KEY, SingerName varchar(1024), ... Location varchar(1024) NOT NULL PLACEMENT KEY );
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);"
gcloud spanner databases ddl update example-db \
--instance=test-instance \
--ddl="CREATE TABLE Singers ( SingerId bigint PRIMARY KEY, SingerName varchar(1024), Location varchar(1024) NOT NULL PLACEMENT KEY );"
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
(GoogleSQL,
PostgreSQL) DDL statement to change other fields in
the table, for example, by 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:
- 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
(GoogleSQL, PostgreSQL) DDL statement.For example, drop the
Singers
table:DROP TABLE Singers;
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
- 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
(GoogleSQL, PostgreSQL) DDL statement.For example, add a singer, Marc Richards, to the
Singers
table and partition it ineuropeplacement
:INSERT INTO Singers(SingerId, SingerName, Location) VALUES (1, 'Marc Richards', 'europeplacement')
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
- 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;
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
- 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;
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
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
- 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;
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.