如何在Cassandra数据库表中将int列转换为float / double列 [英] How to convert int column to float/double column in Cassandra database table

问题描述

我在生产中使用cassandra数据库。在

中有一个cassandra表的列字段，例如coin_deducted是int数据类型。

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

我需要转换float / double数据类型的coin_deducted。
但是我试图通过使用alter

table命令来更改数据类型，但是cassandra抛出了不兼容的问题，而

将int转换为float。有没有办法做到这一点？

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?

例如：当前显示如下：

   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?

推荐答案

仅当旧类型和新类型兼容时才可以更改列的类型。从文档中：

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;

它会告诉您类型不兼容。这也是合乎逻辑的，因为float是比int（具有十进制）更广泛的类型，并且数据库不知道要放在十进制空间中的内容。以下是兼容类型的列表，可以对其进行更改

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.

解决方案1 ​​

您可以在应用程序上进行操作级别，在该表中再创建一列float并创建后台作业，该作业将遍历所有记录并将您的 int 值复制到新的 float 列。

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.

我们创建了 cassandra迁移工具用于此类情况下的DATA和SCHEMA迁移，您可以将其添加为依赖项并可以编写SCHEMA迁移，这将添加新列，并添加将在后台启动并复制的DATA迁移从旧列到新列的值。这是链接到Java示例应用程序以查看用法。

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.

解决方案2

如果您没有应用程序级别，只想这样做在CQL中，您可以使用 COPY 命令提取数据转换为CSV，使用浮点数创建新表，对CSV中的int值进行手动排序并将数据返回到新表中。

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.

这篇关于如何在Cassandra数据库表中将int列转换为float / double列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！