3

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.

miles212
  • 383
  • 3
  • 20

2 Answers2

4

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

SagarKC
  • 85
  • 1
  • 8
2

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.

enter image description here

marian.vladoi
  • 7,663
  • 1
  • 15
  • 29
  • I'm doing partitioned DML for a bulk delete and I'm getting the limit saying I'm doing too many mutations at once – gsouf Apr 22 '20 at 15:54
  • It's not mentioned in the doc. But it was because I was deleting a table with interleaved tables. I had to remove rows from interleaved table before deleting the parent table. – gsouf Apr 22 '20 at 16:15