Spanner - delete one row or all rows from a table

Question

I would like to delete one row or all rows from a spanner table. The primary key of the table is an int64. I do not want to drop the table and recreate it. I am using the google supplied Java tutorial as a guideline (I am not a Java programmer - been a MS SQL Server developer since the product first came out). Here's what I have as my code. No rows are deleted and there is not an error thrown.

static void perfmonTestDelete(DatabaseClient dbClient) {
    LocalDateTime datetimeStart = LocalDateTime.now();
    LocalDateTime datetimeEnd;

    Mutation.delete("productPerfmon",KeySet.all());

    datetimeEnd = LocalDateTime.now();
    long diffInSeconds = java.time.Duration.between(datetimeStart, datetimeEnd).getSeconds();
        System.out.println("DurationInSeconds:" + diffInSeconds);

}

Here's the code to populate 10 rows in the table that I want to delete from:

static void perfmonTest(DatabaseClient dbClient) {
    LocalDateTime datetimeStart = LocalDateTime.now();
    LocalDateTime datetimeEnd;
    List<Mutation> mutations = new ArrayList<>();
    for (int i = 0; i < 10; i++) {
        mutations.add(Mutation.newInsertBuilder("productPerfmon")
              .set("product_id")
              .to(i)
              .set("product_code")
              .to("test")
              .set("product_code_v")
              .to("test_v")
              .build());
    }
    dbClient.write(mutations);

    datetimeEnd = LocalDateTime.now();
    long diffInSeconds = java.time.Duration.between(datetimeStart, datetimeEnd).getSeconds();
        System.out.println("DurationInSeconds:" + diffInSeconds);

}

Any help is appreciated.

Robert Robert · Accepted Answer · 2018-02-21T14:26:01

To delete one or multiple rows you need to use a singleKey or keyRange selector:

SpannerOptions options = SpannerOptions.newBuilder().build();
Spanner spanner = options.getService();
DatabaseClient dbClient = spanner.getDatabaseClient(DatabaseId.of(
        options.getProjectId(), "InstanceId", "databaseName"));

List<Mutation> mutations = new ArrayList<>();
// Single row by key
mutations.add(Mutation.delete("tableName", 
    KeySet.singleKey(Key.newBuilder().append("Key").build())));

// Multiple rows by range
mutations.add(Mutation.delete("tableName",
    KeySet.range(
        KeyRange.newBuilder()
            .setStart(Key.newBuilder().append("StartKeyIncl").build())
            .setEnd(Key.newBuilder().append("EndKeyNotIncl").build())
            .build())));
dbClient.write(mutations);

To delete all rows in your table you can do:

List<Mutation> mutations = new ArrayList<>();
mutations.add(Mutation.delete("tableName", KeySet.all()));
dbClient.write(mutations);

Spanner - delete one row or all rows from a table

2 Answers