I have millions of records in Spanner table and I would like to delete rows from Spanner using some query condition. For Eg: delete from spanner table where id > 2000. I'm not able to run this query in Spanner UI, because of Spanner 20k mutation limit in single op's. So is there any way I could delete this record from spanner table by doing some tweaks in api level code or do we have a work around for this type of use-case.
2 Answers
According to the official documentation Deleting rows in a table, I think you should consider Particioned DML execution model:
If you want to delete a large amount of data, you should use Partitioned DML, because Partitioned DML handles transaction limits and is optimized to handle large-scale deletions
Partitioned DML enables large-scale, database-wide operations with minimal impact on concurrent transaction processing by partitioning the key space and running the statement over partitions in separate, smaller-scoped transactions.
You can use gcloud command line as :
gcloud spanner databases execute-sql <database_id> --instance=<instance_id> --enable-partitioned-dml --sql="delete from YourTable where id > 2000"
NOTE: SQL query must be fully partitionable and idempotent
Related