Search code examples
hivetimestamphiveqlalter-table

Hive- alter a column values and insert new values in place of old column values in the table

I am newbie in Hive. I have a table in Hive with Unix timestamp values in a column. I am trying to alter the column values with normal timestamp values.

I have tried this. I am able to convert the unix timestamp values to normal timestamp values.

"SELECT from_unixtime(cast(ts as bigint)) FROM june11http" 

Results
--------

2010-06-10 20:01:07

2010-06-10 20:01:10

2010-06-10 20:01:12

2010-06-10 20:01:14

2010-06-10 20:01:10

2010-06-10 20:01:15

.......

Now I want to replace the original values in the 'ts' column with the above values.

I tried with the following query:

"ALTER TABLE june11http CHANGE ts  ts from_unixtime(cast(ts as bigint)".

I see syntactical errors. Can anyone suggest ways to do this?

Solution

  • Because correct syntax is: 

    ALTER TABLE name CHANGE column_name new_name new_type

    You should specify new_type instead performing data transformation.

    Try to create new table using CTAS query, it should work.