Does Cloud Spanner support a TRUNCATE TABLE command?

Question

I want to clear all the values from a table. It has a few secondary indexes. I tried to do this via committing a transaction with Mutation.delete("MyTable", KeySet.all()) (see docs here). But I got an error:

error:INVALID_ARGUMENT: io.grpc.StatusRuntimeException: INVALID_ARGUMENT: The transaction contains too many mutations.

How can I efficiently clear my table contents?

Answer 1

Cloud Spanner does not support such a truncate command. If your table had no secondary indexes, then you could specify KeySet.all() in your Delete as specified above, but this can fail if your table has secondary indexes and is large.

The best way to do what you want is to issue an updateDdl RPC including the following statements:

1) For each secondary index on MyTable, include a corresponding DROP INDEX statement

2) DROP TABLE MyTable

3) If necessary, re-create your table and indexes via the CREATE TABLE and CREATE INDEX statements, respectively.

Note that you are allowed and encouraged to include all of these statements in a single updateDdl RPC. The advantage of this is that it gives you atomic ("all-or-nothing") semantics.