Alter column data type in Hive

2
votes

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 string

sale_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>

3
hivebigdata
The message seems pretty clear. You can't change an integer to a date. Perhaps you should create the new version of the table and copy the data into it. - Gordon Linoff
sale_date string , it is a string data type . I want to change to Date. Kindly check and help. - user2949241

3 Answers

5
votes

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

2
votes

you can't change the existing string type data to date type. but we can able to solve this issues in 2 ways.

  1. 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.
  2. 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

-1
votes

Do the following steps: step-1) check all the dates in field "sale_date" are valid dates or not. If yes then go to step-2

step-2) Check the date format, for DATE datatype format should be 'yyyy-MM-dd'. If the date format is 'yyyy-MM-dd HH:mm:ss' then you should try the following syntax:

alter table sales_staging CHANGE sale_date sale_date TIMESTAMP;