spanner-truncate is a tool to delete all rows from the tables in a Cloud Spanner database without deleting tables themselves.
Please feel free to report issues and send pull requests, but note that this application is not officially supported as part of the Cloud Spanner product.
- Delete rows from the database while keeping the underlying splits, which are typically for database pre-warming before the launch.
- Delete rows from the database without requiring strong IAM permissions for deleting tables or databases.
At a glance deleting all rows from the database looks an easy task, but there are several issues we could encounter when we want to delete rows from the real-world databases.
- If the table size is huge, a simple DELETE statement like
DELETE FROM table WHERE true
could easily exceed the transaction mutation limit. - Rows in interleaved tables which have
PARENT ON DELETE NO ACTION
must be deleted first before deleting the rows from the parent table, otherwise it will cause a constraint violation error. - Rows in the tables which reference other tables with
FOREIGN KEY
constraints must be deleted first before deleting rows in the referenced tables, otherwise it will cause a constraint violation error. - It would take a lot of time if we delete rows from the tables one by one.
To solve the preceding issues, this tool works as follows.
- Use Partitioned DML to delete all rows from the table to overcome the single transaction mutation limit.
- Delete rows from multiple tables in parallel to minimize the total time for deletion.
- Automatically discover the constraints between tables and delete rows from the tables in proper order without violating database constraints.
- This tool does not guarantee the atomicity of deletion. If you access the rows that are being deleted, you will get the inconsistent view of the database.
- This tool does not delete rows which were inserted while the tool was running.
GO111MODULE=on go get github.com/cloudspannerecosystem/spanner-truncate
Usage:
spanner-truncate [OPTIONS]
Application Options:
-p, --project= (required) GCP Project ID. [$SPANNER_PROJECT_ID]
-i, --instance= (required) Cloud Spanner Instance ID. [$SPANNER_INSTANCE_ID]
-d, --database= (required) Cloud Spanner Database ID. [$SPANNER_DATABASE_ID]
-q, --quiet Disable all interactive prompts.
-t, --tables= Comma separated table names to be truncated. Default to truncate all tables if not specified. If an interleaved table is specified, its descendants tables are also truncated.
-e, --exclude-tables Comma separated table names to be exempted from truncating. 'tables' and 'exclude-tables' cannot co-exist. If an interleaved table is specified, its ancestors tables are also excluded.
Help Options:
-h, --help Show this help message
Example:
$ spanner-truncate -p myproject -i myinstance -d mydb
Fetching table information from projects/myproject/instances/myinstance/databases/mydb
Albums
Concerts
Singers
Songs
Rows in these tables will be deleted. Do you want to continue? [Y/n] Y
Concerts: completed 13s [============================================>] 100% (1,200 / 1,200)
Singers: completed 13s [============================================>] 100% (6,000 / 6,000)
Albums: completed 12s [============================================>] 100% (1,800 / 1,800)
Songs: completed 11s [============================================>] 100% (3,600 / 3,600)
Done! All rows have been deleted successfully.
You can also use spanner-truncate as a Go library from your Go application. The entry point is Run function in truncate
package. If you have some subsequent processes using a client, you can use RunWithClient. You can pass the externally generated client to the function and avoids the use of redundant clients.