CODEWITHSUNDEEP
google-cloud-platformgoogle-cloud-spanner
Dave
Dave

Reputation: 143

Spanner - delete one row or all rows from a table

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.

Upvotes: 0

Views: 5316

Answers (2)

James Smith
James Smith

Reputation: 31

The documentation states that Google Spanner is compliant with ANSI SQL-2011. But I did not find any examples of DELETE, UPDATE, and INSERT in Cloud Spanner.

My understanding of SQL-2011, is that it definitely includes support for DELETE, INSERT and UPDATE.

In that case, adding rows or deleting them will become a single SQL statement, and the Java Client connecting using JDBC.

Upvotes: 1

Robert
Robert

Reputation: 296

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

Upvotes: 2

Related Questions