Inserting, updating, and deleting data using the Google Cloud CLI

This page describes how to insert, update, and delete data using the gcloud command-line tool.

Modifying data using DML

To execute Data Manipulation Language (DML) statements, use the gcloud spanner databases execute-sql command . The following example adds a new row to the Singers table.

gcloud spanner databases execute-sql example-db --instance=test-instance \
    --sql="INSERT Singers (SingerId, FirstName, LastName) VALUES (1, 'Marc', 'Richards')"

To execute a Partitioned DML statement, use the gcloud spanner databases execute-sql command with the --enable-partitioned-dml option. The following example updates rows in the Albums table.

gcloud spanner databases execute-sql example-db \
    --instance=test-instance --enable-partitioned-dml \
    --sql='UPDATE Albums SET MarketingBudget = 0 WHERE MarketingBudget IS NULL'

For the Spanner DML reference, see Data Manipulation Language syntax.

Modifying rows using the rows command group

Use the gcloud spanner rows command group to modify data in a database:

Insert new rows into a table.
Update columns in existing rows in a table.
Delete rows from a table.

The rows command group recognizes literals for all valid column types.

Insert a row in a table

To insert a new row in a table, you must include values for the key columns and any other required columns:

gcloud spanner rows insert --instance=INSTANCE_ID --database=DATABASE_ID \
    --table=TABLE_NAME \
    --data=COL_NAME_1=COL_VALUE_1,COL_NAME_2=COL_VALUE_2,COL_NAME_3=COL_VALUE_3,...,COL_NAME_N=COL_VALUE_N

The following example inserts a new row in the Singers table:

gcloud spanner rows insert --instance=test-instance --database=example-db \
    --table=Singers \
    --data=SingerId=1,FirstName='Marc',LastName='Richards'

Update a row in a table

To update a row in a table, you must include values for the key columns and the columns you want to update:

gcloud spanner rows update --instance=INSTANCE_ID --database=DATABASE_ID \
    --table=TABLE_NAME \
    --data=COL_NAME_1=COL_VALUE_1,COL_NAME_2=COL_VALUE_2,COL_NAME_3=COL_VALUE_3,...,COL_NAME_N=COL_VALUE_N

The following example updates a row in the Singers table:

gcloud spanner rows update --instance=test-instance --database=example-db \
    --table=Singers \
    --data=SingerId=1,FirstName='Marc',LastName='Richards'

You cannot change the key values using the update command. To update a key value, you must create a new row and delete the existing row.

Delete a row from a table

To delete a row, you must specify the values for the primary key columns:

gcloud spanner rows delete --instance=INSTANCE_ID --database=DATABASE_ID  \
    --table=TABLE_NAME \
    --keys=KEY_VALUE_1,KEY_VALUE_2,KEY_VALUE_3

The following example deletes a row from the `Singers` table:

gcloud spanner rows delete --instance=test-instance --database=example-db \
    --table=Singers \
    --keys=1

Specify ARRAY values

To insert or update values in an ARRAY column, put the data in a YAML file and use the --flags-file option.

For example, this YAML file specifies the array [1,2,3] for the Numbers column:

# stats.yaml
--data:
    Id: 1
    Locked: True
    Numbers:
        - 1
        - 2
        - 3

To insert a row with the YAML data, use the --flags-file option:

gcloud spanner rows insert --instance=test-instance --database=example-db \
     --table=Stats \
     --flags-file stats.yaml

For a NULL array, don't include a value for Numbers in the file:

# stats.yaml
--data:
    Id: 1
    Locked: True

For an empty array, define the array as []:

# stats.yaml
--data:
    Id: 1
    Locked: True
    Numbers: []

Specify commit timestamps

To insert or update a value automatically in a commit timestamp column, pass spanner.commit_timestamp() as the value of the column. The following example writes the commit timestamp in the LastUpdated column when the row is inserted.

gcloud spanner rows insert --instance=test-instance --database=example-db \
    --table=Singers \
    --data=SingerId=1,LastUpdated='spanner.commit_timestamp()'

The following example writes a specific timestamp value in the LastUpdated column:

gcloud spanner rows update --instance=test-instance --database=example-db \
    --table=Singers \
    --data=SingerId=1,LastUpdated=2017-01-02T12:34:00.45Z