更改配置单元中的列类型 [英] Change column type in hive

问题描述

我昨天刚开始学习配置单元，并且坚持改变配置单元中列的类型。我想问问，如果列类型的改变对他们有某种限制，因为我只能做特定类型的更改，例如我可以将int转换为double，将字符串转换为double，将double转换为字符串，但不能将字符串更改为int，double

  ALTER TABLE学生CHANGE rollno rollno int; 
 FAILED：执行错误，从org.apache.hadoop.hive.ql.exec.DDLTask返回代码1。无法更改表格。以下列的类型与其各自位置中的现有列不兼容：
 rollno 
 
 hive> DESCRIBE FORMATTED学生
> ; 
 OK 
＃col_name data_type注释
 
 rollno int 
名称字符串
 phno字符串
城市字符串
 
 ALTER TABLE学生CHANGE rollno rollno double; 
 OK 
所需时间：0.144秒
 
 ALTER TABLE学生CHANGE roll no rollno int; 
 
 FAILED：执行错误，从org.apache.hadoop.hive.ql.exec.DDLTask返回代码1。无法更改表格。以下列的类型与其各自位置中的现有列不兼容：
 
 rollno 
  

不能用字符串做。数据库（我曾在Teradata上工作过）通常情况下，数据库（通常是Teradata）将不允许将列数据类型从更高顺序修改为更低顺序，也不允许将数据类型与已经存储的数据冲突/将存储在表中。


Eg-1：我们可以修改 int 列为 double 列，因为新的修改数据类型是能够处理表中已有的数据。修改 double 列为 int 可能会产生问题，因为 int 列不能保存 double 数据。

Eg-2：强>任何（几乎）列都可以修改为字符串数据类型，因为此数据类型能够容纳各种各样的数据。任何类型为double，float，int的数据都可以描述为一个字符串，因此大部分时间都会接受这种转换。

Eg-3：另一个我遇到的情况是，有时 CHAR 到 CHAR 转换失败。何时和为什么？它只有在你试图降低长度（ CHAR（20）到 CHAR（10））时才会失败，但会当你尝试升级（ CHAR（10）到 CHAR（20））时会很好。如果表中的数据长度大于新的数据类型长度，此检查将保持数据的完整性，以防止数据截断。

我是a对于为什么Hive会接受从字符串到双倍的转换有点惊讶。我试过这个，当数据类型从字符串转换为double时，它强制为字符串值NULL。

I just started learning hive yesterday and I am stuck on changing the type of a column in hive. I wanted to ask if the changing of column type has some kind of restriction on them because I can only do specific type of changes like I can convert int to double, string to double, double to string but I cant change string to int, double to int.

ALTER TABLE student CHANGE rollno rollno int;
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Unable to alter table. The following columns have types incompatible with the existing columns in their respective positions :
rollno

hive> DESCRIBE FORMATTED student
> ;
OK
# col_name              data_type               comment             

rollno                  int                                         
name                    string                                      
phno                    string                                      
city                    string   

    ALTER TABLE student CHANGE rollno rollno double;
OK
Time taken: 0.144 seconds

ALTER TABLE student CHANGE rollno rollno int;

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Unable to alter table. The following columns have types incompatible with the existing columns in their respective positions :

rollno

Cant do with strings either. So please guide me towards the answer as I cant seem to find anything through google.

解决方案

Databases ( I worked on Teradata ) usually will not allow to modify a column datatype from a higher order to lower order or cases where the datatypes may clash with the data that is already stored/will be stored in the table.

Eg-1: We can modify an int column to a double column as the new modified datatype is capable of handing the already existing data in the table. where as modifying a double column to int might create issues because an int column is not capable of holding a double data.

Eg-2: Any(almost) column can be modified to String datatype, as this datatype is capable of holding a wide range of data. Any data with type double,float,int can be depicted as a string and hence this conversion will be accepted most of the time.

Eg-3: Another case that I came across is that a CHAR to CHAR conversion fails at times. When and Why? It fails only if you are trying to degrade the length( CHAR(20) to CHAR(10) ) but will go fine when you try to upgrade ( CHAR(10) to CHAR(20) ). This check will maintain data sanity where the data would not be let to truncate, in case the data length in the table is greater than the new datatype length.

I am a little surprised as to why Hive would accept the conversion from string to double. I tried this and it is forcing NULL for string values when the datatype is converted from string to double.

这篇关于更改配置单元中的列类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！