How to convert int column to float/double column in Cassandra database table

Question

I am using cassandra database in production.I have one column field in
a cassandra table e.g coin_deducted is int data type.

I need to convert coin_deducted in float/double data type. But I tried to change data type by using alter
table command but cassandra is throwing incompatible issue while
converting int to float. Is there any way to do this?

e.g: currently it is showing like:

   user_id | start_time | coin_deducted (int)                    
   122     | 26-01-01   | 12

I want to be 

  user_id | start_time | coin_deducted (float)                    
   122     | 26-01-01   | 12.0

Is it possible to copy entire one column field into new added column field in same table?

Nenad Bozic Nenad Bozic · Accepted Answer · 2016-01-22T07:42:18

Changing type of column is possible only if old type and new type are compatible. From documentation:

To change the storage type for a column, the type you are changing to and from must be compatible.

One more proof that this cannot be done is when you write statement:

ALTER TABLE table_name ALTER int_column TYPE float;

it will tell you that types are incompatible. This is also logical since float is broader type than int (has decimal) and database would not know what to put on decimal space. Here is a list of compatible types which can be altered one to another without problems.

Solution 1

You can do it on application level, create one more column in that table which is float and create background job which will loop through all records and copy your int value to new float column.

We created cassandra migration tool for DATA and SCHEMA migrations for cases like this, you add it as dependency and can write SCHEMA migration which will add new column and add DATA migration which will fire in background and copy values from old column to new column. Here is a link to Java example application to see usage.

Solution 2

If you do not have application level and want to do this purely in CQL you can use COPY command to extract data to CSV, create new table with float, sort manually int values in CSV and return data to new table.

How to convert int column to float/double column in Cassandra database table

1 Answers