Create and manage data placements

Preview — Geo-partitioning

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

Note: This feature is available with the Spanner Enterprise Plus edition. For more information, see the Spanner editions overview.

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

1. Go to the Instances page in the Google Cloud console.

   Instances

2. Select the instance with user-created partition(s).

3. Select the database that you want to partition data.

4. In the navigation menu, click Spanner Studio.

5. In the Spanner Studio page, click New tab or use the empty editor tab.

6. Enter the CREATE PLACEMENT (GoogleSQL, PostgreSQL) DDL statement.

   For example, you can run the following to create a placement table europeplacement in the partition europe-partition:

   GoogleSQL

   CREATE PLACEMENT `europeplacement` OPTIONS (instance_partition="europe-partition");
   

   PostgreSQL

   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.

7. 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:

GoogleSQL

gcloud spanner databases ddl update example-db \
  --instance=test-instance \
  --ddl="CREATE PLACEMENT europeplacement OPTIONS (instance_partition='europe-partition')"

PostgreSQL

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

Console

1. In the navigation menu, click Spanner Studio.
2. In the Spanner Studio page, click New tab or use the empty editor tab.

3. 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:

   GoogleSQL

   CREATE TABLE Singers (
     SingerId INT64 NOT NULL,
     SingerName STRING(MAX) NOT NULL,
     ...
     Location STRING(MAX) NOT NULL PLACEMENT KEY
   ) PRIMARY KEY (SingerId);
   

   PostgreSQL

   CREATE TABLE Singers (
     SingerId bigint PRIMARY KEY,
     SingerName varchar(1024),
     ...
     Location varchar(1024) NOT NULL PLACEMENT KEY
   );
   

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:

GoogleSQL

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);"

PostgreSQL

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:

1. Delete all rows in the placement table.
2. Wait for the version_retention_period for the database to pass. For more information, see Point-in-time recovery. Then, following these steps:

Console

1. In the navigation menu, click Spanner Studio.
2. In the Spanner Studio page, click New tab or use the empty editor tab.

3. Enter the DROP TABLE (GoogleSQL, PostgreSQL) 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

1. In the navigation menu, click Spanner Studio.
2. In the Spanner Studio page, click New tab or use the empty editor tab.

3. Enter the INSERT INTO (GoogleSQL, PostgreSQL) DDL statement.

   For example, add a singer, Marc Richards, to the Singers table and partition it in europeplacement:

   INSERT INTO Singers(SingerId, SingerName, 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

1. In the navigation menu, click Spanner Studio.
2. In the Spanner Studio page, click New tab or use the empty editor tab.

3. Use DML or mutations to update data in a placement table.

   For example, update the name of singerid=1 in the Singers table to Catalina 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

1. Create a new partition and placement if you haven't already.
2. In the navigation menu, click Spanner Studio.
3. In the Spanner Studio page, click New tab or use the empty editor tab.

4. Use DML or mutations to move data to the new partition.

   For example, move singerid=1 in the Singers table to asiaplacement:

   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

1. In the navigation menu, click Spanner Studio.
2. In the Spanner Studio page, click New tab or use the empty editor tab.

3. Use DML or mutations to delete data.

   For example, delete singerid=1 in the Singers table:

   DELETE FROM Singers s
   WHERE s.id=1;
   

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

1. In the navigation menu, click Spanner Studio.
2. In the Spanner Studio page, click New tab or use the empty editor tab.

3. 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.