Alter column data type in Hive
Solution 1
You can't give same name to column you wish to change datatype of. use like this
ALTER TABLE sales_staging CHANGE sale_date sale_date_new DATE;
refer this Apache Hive Wiki
Solution 2
you can't change the existing string type data to date type. but we can able to solve this issues in 2 ways.
- create another table with the same columns count but the data type is date where the column you need string to date, then use insert command to export old table data to new table by casting the string to date.
- add a new column to existing table with datatype as date. overwrite the table itself by casting the string to date into the new column.
ex:
I have orders table
describe orders;
order_id int
order_date string
order_customer_id int
order_status string
created another table ordersnew
describe ordersnew;
id int
odate date
cid int
ostatus string
now exported the orders data to ordersnew table
insert into ordersnew select order_id,cast(from_unixtime(unix_timestamp(substring(order_date,1,19), 'yyyy-MM-dd HH:mm:ss')) as timestamp) as strdate, order_customer_id,order_status from orders;
substring(order_date,1,19), 'yyyy-MM-dd HH:mm:ss' this is the place you have to check and alter your query as per your data.
please check here for date conversions
Related videos on Youtube
user2949241
Updated on July 09, 2022Comments
-
user2949241 almost 2 years
we need to alter the table column data type from string to date. While am trying to do am getting the below error. Could you please help.
hive> describe sales_staging;
OK
cust_id string prod_num string
qty int sale_date stringsale_id string Time taken: 0.151 seconds, Fetched: 5 row(s)
hive> alter table sales_staging CHANGE sale_date sale_date DATE ;
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 :sale_date
hive>