CODEWITHSUNDEEP
sqlhadoophiverename
Nishant Kelkar
Nishant Kelkar

Reputation: 412

Hive - Renaming Fields With Same Name As A Datatype

I have a Hive table like such: 

CREATE TABLE mytest (name int, timestamp bigint, donation int);

I am using Hive 0.12. Note the field "timestamp". Incidentally, Hive 0.12+ introduced a new data type called timestamp. Say I want to rename this field to time_stamp

I tried these:

  1. ALTER TABLE mytest CHANGE timestamp time_stamp BIGINT;
  2. ALTER TABLE mytest CHANGE COLUMN timestamp time_stamp BIGINT;
  3. ALTER TABLE mytest CHANGE [timestamp] time_stamp BIGINT;
  4. ALTER TABLE mytest CHANGE `timestamp` time_stamp BIGINT;

However, all of them give me the following error:

FAILED: ParseException line 1:38 mismatched input 'CHANGE' expecting KW_EXCHANGE near 'mytest' in alter exchange partition

I am dead sure this is because of the fact that my field name is the same as a data type name. How can I alter the schema for mytest without having to do the following?

CREATE mytest_cpy AS SELECT mytest.name, mytest.timestamp AS time_stamp, 
mytest.donation FROM mytest; 

DROP TABLE mytest; 

ALTER TABLE mytest_cpy RENAME TO mytest;

Thanks! Any/all help is appreciated!

Upvotes: 4

Views: 2256

Answers (3)

Rijul
Rijul

Reputation: 1445

Although this post have answers but still:

If you try to give column name which is Reserved Keywords for HIVE then error will occur.

For overriding the reserved keywords use can use backtick(`) this is the character below your tilde key or left to 1 key.

Upvotes: 0

apc
apc

Reputation: 141

Use backticks for any unusal column names, either containing weird symbols or the same as data types. This works for Hive 0.14:

ALTER TABLE mytest CHANGE COLUMN `timestamp` time_stamp BIGINT;

Upvotes: 2

MtwStark
MtwStark

Reputation: 4048

in MS SQL use:

EXECUTE sp_rename 'dbo.mytest.timestamp', 'time_stamp', 'COLUMN'

in MYSQL try:

USE database_name;
ALTER TABLE mytest CHANGE timestamp time_stamp BIGINT;

or:

ALTER TABLE database_name.mytest CHANGE timestamp time_stamp BIGINT;

Upvotes: 0

Related Questions