1

If I have two tables that are identical in structure, how can I move a set of rows from 1 table to the other while deleting records?

I want something similar like that in Cassandra:

BEGIN;
INSERT INTO persons_table select * from customer_table where person_name = 'tom';
DELETE FROM customer_table where person_name = 'tom';
COMMIT;
Saidamir Botirov
  • 53
  • 6

2 Answers2

3

The simplest solution that I may think about is to use Spark, but it won't be atomic. The code would be something like this (could be executed in the spark-shell):

Start shell with (assuming that it's Spark 2.4):

spark-shell --packages com.datastax.spark:spark-cassandra-connector_2.11:2.5.1

then do:

val data = spark.read.format(".format("org.apache.spark.sql.cassandra")\
  .options(Map( "table" -> "orig_table", "keyspace" -> "ks")).load()
val filtered = data.filter($"person_name" === "tom")
filtered.write.format("org.apache.spark.sql.cassandra")\
  .options(Map("table" -> "new_table", "keyspace" -> "ks")).save()
filtered.rdd.deleteFromCassandra("ks", "orig_table")
Alex Ott
  • 80,552
  • 8
  • 87
  • 132
1

The best solution I can think of is just use a cql library in your preferred language and do it in a programmatic way. Another solution might be Medusa. I am not familiar with the backup structure, but there might be the possibility to backup the table and change the name of the table in the backup files and import it into Cassandra again. I would try this in a test setup first.

Citrullin
  • 2,269
  • 14
  • 29
  • If it the same cluster and same table structure , you can just copy files, and call nodetool refresh on every node. But here, he needs to remove data after copying, and copy only specific data – Alex Ott Aug 27 '20 at 21:01