How to delete all rows in Cassandra Keyspace

Question

I need to delete all rows in Cassandra but with Amazon Keyspace isn't possible to execute TRUNCATE tbl_name because the TRUNCATE api isn't supported yet.

Now the few ideas that come in my mind are a little bit tricky:

Solution A

• select all the rows
• cycle all the rows and delete it (one by one or in a batch)

Solution B

• DROP TABLE
• CREATE TABLE with the structure of the old table

Do you have any idea to keep the process simplest?

Tnx in advance

Answer 1

Solution B should be fine in absence of TRUNCATE. In older versions (version prior to 2.1) of Cassandra recreating table with the same name was a problem. Refer article Datastax FAQ Blog. But since then issue has been resolved via CASSANDRA-5202.

If data in table is not required anymore it is better to drop the table and recreate it. Moreover it will be very tedious task if table contains big amount of data.

Answer 2

If the data is not required. Option B - drop the table and recreate. You can pass in the capacity on create table statment using custom table properties.

CREATE TABLE my_keyspace.my_table (
     id text,
     division text,
     project text,
     role text,
     manager_id text,
     PRIMARY KEY (id,division))
      WITH CUSTOM_PROPERTIES=
              {'capacity_mode':
                     {'throughput_mode'       : 'PROVISIONED', 
                      'read_capacity_units'   : 10, 
                      'write_capacity_units'  : 20},
               'point_in_time_recovery': {'status': 'enabled'}}
            AND TAGS={'pii' :'true', 
                      'prod':'true'
                     };

Option C. If you require the data you can also leverage on-demand capacity mode which is pay-per request mode. With no request you only have to pay for storage. You can change modes once a day.

ALTER TABLE my_keyspace.my_table
WITH CUSTOM_PROPERTIES=
              {'capacity_mode': {'throughput_mode': 'PAY_PER_REQUEST'}}