Create and manage data placements

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 instance 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 instance 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 instance 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 instance 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')"

Set the default leader for a placement

You can set the default leader region of a placement if its location is in a dual-region or multi-region. The new leader region must be one of the two read-write regions within the dual-region or multi-region placement location. For more information, see the Dual-region available configurations and Multi-region available configurations tables.

If you don't set a leader region, your placement uses the default leader region as specified by its location. For a list of the leader region for each dual-region or multi-region location, see the Dual-region available configurations and Multi-region available configurations tables. The default leader region is denoted with an L. For example, the default leader region of nam8 is in Los Angeles(us-west2). The following instructions explain how to set it to Oregon(us-west1).

Console

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

    Instances

  2. Select the instance with user-created instance 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 nam8placement in the instance partition nam8-partition with the default leader location set as us-west1:

    GoogleSQL 

    CREATE PLACEMENT `nam8placement`
  OPTIONS (instance_partition="nam8-partition", default_leader="us-west1");

    PostgreSQL 

    CREATE PLACEMENT nam8placement WITH (instance_partition='nam8-partition', default_leader='us-west1');

    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 table nam8placement in the instance partition nam8-partition with the default leader location set as us-west1:

GoogleSQL 

gcloud spanner databases ddl update example-db \
  --instance=test-instance \
  --ddl="CREATE PLACEMENT nam8placement \
     OPTIONS (instance_partition='nam8-partition', default_leader='us-west1')"

PostgreSQL 

gcloud spanner databases ddl update example-db \
  --instance=test-instance \
  --ddl="CREATE PLACEMENT nam8placement WITH (instance_partition='nam8-partition', default_leader='us-west1')"

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 instance 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 instance 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 instance 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