Using cassandra's ttl() in where clause

user_5500320 0
Viewed 263

I'd like to ask if its possible to get rows from cassandra, that have ttl(time to live) bigger than 0. So in the next step i can update those rows with ttl 0. The goals is basically to change the ttl of all the columns for every entry in db to 0.

I've tried SELECT * FROM table where ttl(column1) > 0, but it seems its not possible to use ttl() function in where clause.

I also found a way where we can export all the rows to csv, delete the data in our table and import them again from csv with new ttl. That works but its dangerous because we have over million entries on production and we do not know how it will behave.

ttl cassandra
1 Answers

You can't do this with CQL only - you need to have support from some tool, for example:

  • DSBulk - you can unload all your data into CSV file, and load back with new TTL set (if you set it to 0, then just load data back). Here is a blog post that shows how to use DSBulk with TTL. But you can't have condition on the TTL, that's why you need to unload all your data
  • Spark with Spark Cassandra Connector (even in the local master mode). Version 2.5.0 supports TTL in the Dataframe API (earlier versions supported it only for RDD API) - for Spark 2.4 you need to correctly register functions. This could be done one time, directly in the spark-shell with something like this (you need to adjust your columns in the select & filter statements):
import org.apache.spark.sql.cassandra._
val data = spark.read.cassandraFormat("table", "keyspace").load
val ttlData = data.select(ttl("col1").as("col_ttl"), $"col2", $"col3").filter($"col_ttl" > 0)
ttlData.drop("col_ttl").write.cassandraFormat("table", "keyspace").mode("append").save
Alex_Ott_186270
Related