Cassandra script to change date to Instant type

In Cassandra, there is a separate timestamp type to keep such information. Internally, it stores the data as 8-byte long value representing time in milliseconds. This value is accessed via driver, and could be transformed into the value of type specific to the programming languages used. If you're accessing these values via cqlsh you will need them printed as 2020-05-18T14:18:45.878Z, but in reality it's the still long type under the hood.

To perform such conversion you need 2 things:

1. You need to add another column with timestamp type - you can't change type of the existing column
2. You need to use some tool to perform such conversion, but this is really depends on your requirements. You can do it, for example:

• Spark - just do something like this using the Spark Cassandra Connector

val data = { spark.read.format("org.apache.spark.sql.cassandra")
    .options(Map( "table" -> "", "keyspace" -> ""))
    .load().withColumnRenamed("text_column", "date_column")}
data.write.format("org.apache.spark.sql.cassandra")
   .options(Map("table" -> "", "keyspace" -> "")).mode("append").save()

• DSBulk. You can unload data from your database onto the disk, and then load back, but use timestamp column instead of the text column by providing the custom mapping with -m option. There is a serie of blog posts about DSBulk, that could provide more information & examples: 1, 2, 3, 4, 5, 6