54

I'm looking for a way to delete all of the rows from a given column family in cassandra.

This is the equivalent of TRUNCATE TABLE in SQL.

Ike Walker
  • 64,401
  • 14
  • 110
  • 109

6 Answers6

84

You can use the truncate thrift call, or the TRUNCATE <table> command in CQL.

http://www.datastax.com/docs/1.0/references/cql/TRUNCATE

Community
  • 1
  • 1
the paul
  • 8,972
  • 1
  • 36
  • 53
  • 1
    Thanks for that. I ended up using `drop column family` instead and recreating the column family, but it's good to know what syntax exists. – Ike Walker May 09 '12 at 17:24
  • 1
    Just to add to this, dropping the column family does not actually delete the data on disk. You have to manually rm the tables. As such, I have found it useful to always create new column families with unique names by indicating the date they were created, i.e. users_2013_09_29, etc. – Cory Dolphin Sep 29 '13 at 17:11
  • 2
    The only downside of `truncate` is that all nodes of your cluster must be up. If that's not the case, you could use `drop columnfamily` , and then create it again. – Tom Jan 28 '14 at 10:40
  • If you are using python and thrift based pycassa (pycassaShell cli), you could use COLUMN_FAMILY.remove('row_key') and will delete all data and key (e.g row_key) in in Column family COLUMN_FAMILY. – NullException Feb 11 '14 at 19:50
  • 1
    is there a way to truncate all the column families (tables) in your database without deleting the keyspace? – Charlie Parker Mar 26 '14 at 18:41
9

You can also do this via Cassandra CQL.

$ cqlsh
Connected to Test Cluster at localhost:9160.
[cqlsh 4.1.1 | Cassandra 2.0.6 | CQL spec 3.1.1 | Thrift protocol 19.39.0]
Use HELP for help.
cqlsh> TRUNCATE my_keyspace.my_column_family;
cevaris
  • 5,671
  • 2
  • 49
  • 34
6

Its very simple in Astyanax. Just a Single Line statement

/* keyspace variable is Keyspace Type */
keyspace.truncateColumnFamily(ColumnFamilyName);
abhi
  • 4,762
  • 4
  • 29
  • 49
3

If you are using Hector it is easy as well:

cluster.truncate("our keyspace name here", "your column family name here");
Eugen
  • 2,292
  • 3
  • 29
  • 43
3

If you are using cqlsh, then you can either do it in 2 ways

  1. use keyspace; and then truncate column_family;
  2. truncate keyspace.column_family;

If you want to use DataStax Java driver, you can look at - http://www.datastax.com/drivers/java/1.0/com/datastax/driver/core/querybuilder/QueryBuilder.html or http://www.datastax.com/drivers/java/2.0/com/datastax/driver/core/querybuilder/Truncate.html

depending on your version.

Aaron
  • 55,518
  • 11
  • 116
  • 132
Sumod
  • 3,806
  • 9
  • 50
  • 69
  • `ServerError: 'TRUNCATE' is not supported yet.` https://social.msdn.microsoft.com/Forums/azure/en-US/eb34a988-01a6-442c-a591-33f4dc2d7e6f/not-able-to-perform-truncate?forum=azurecosmosdb – Abhijit Sarkar Oct 16 '20 at 21:38
0

if you are working on cluster setup, truncate can only be used when all the nodes of the cluster are UP.

By using truncate, we will miss the data(we are not sure with the importance of the data)

So the very safe way as well a trick to delete data is to use COPY command,

1) backup data using copy cassandra cmd
copy tablename to 'path'

2) duplicate the file using linux cp cmd
cp 'src path' 'dst path'

3) edit duplicate file in dst path, delete all lines expect first line.
save the file.

4) use copy cassandra cmd to import
copy tablename from 'dst path'

Swam Guru
  • 473
  • 5
  • 9