Initializing a list by a value from another column

user_59241 0
Viewed 25

I have a table in Cassandra DB, with some columns, for example:

id (text), ..., data (text).

For migration purposes, I need to copy the value of "data" into a new column: data_list (list<text>). How can I initialize the data_list column by the value in data column?

I tried:

update t1 set data_list[0] = data where ...;
update t1 set data_list = data where ...;
update t1 set data_list = [ data ] where ...;
update t1 set data_list [0] = (select data from t1 where ...) where ...;

None of the above worked.

Is this possible?

cassandra
1 Answers

No, it’s not possible to do only with CQL - you need to have some code or tool to do that - it should scan the whole database, read the data and put them into destination column. Besides trying to write your own code, that it’s usually hard to write correctly you can use:

  • DSBulk - you can unload data into CSV or JSON file, convert the data into specific representation by using sed or something like, and load data into the new column. But transformation step is something that you will need to implement, and it could be hard to debug if you have data with quotes, etc.
  • Spark + Spark Cassandra Connector (even in the local mode) - although it's still a piece of code, it would be easier to implement from my point of view. Just start pyspark with options specified in the documentation, read data from Cassandra, transform, and store them back into Cassandra. Something like this (not tested):
import pyspark.sql.functions as F

df = spark.read\
    .format("org.apache.spark.sql.cassandra")\
    .options(table="tbl", keyspace="ks")\
    .load()

df_with_list = df.select("id", "other_primary_key_columns....", 
    F.array(F.col("data")).alias("data_list"))

df_with_list.write\
    .format("org.apache.spark.sql.cassandra")\
    .mode('append')\
    .options(table="tbl", keyspace="ks")\
    .save()

The key piece here is F.array(F.col("data")) that creates an array column from the data column

Alex_Ott_186270
Related