Hive Alter table change Column Name

Question

I am trying to rename a columnName in Hive. Is there a way to rename column name in Hive .

tableA (column1 ,_c1,_c2) to tableA(column1,column2,column3) ??

score 98 · Accepted Answer · answered Feb 26 '14 at 12:38

98

Change Column Name/Type/Position/Comment:

ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]

Example:

CREATE TABLE test_change (a int, b int, c int);

// will change column a's name to a1
ALTER TABLE test_change CHANGE a a1 INT;

answered Feb 26 '14 at 12:38

Remus Rusanu

288,378
40
442
569

38

when renaming column names which were automatically generated from the query, such as the _c1, _c1 from OP, you will have to enclose the name with backticks – libjack Feb 27 '14 at 15:20
1

Thanks, this comment is very useful. I looked many places for this solution and could not find it. For what is a back tick - http://askubuntu.com/questions/20034/differences-between-doublequotes-singlequotes-and-backticks-%C2%B4-%C2%B4-on-comm – ShikharDua Apr 17 '15 at 18:30
thanks to libjack. brackets don't work for me, but ` do. – fishautumn Jul 25 '15 at 06:21
7

Note that the column change will not change any underlying data if it is a parquet table. That is, if you have data in the table already, renaming a column will not make the data in that column accessible under the new name: select a from test_change; 1 alter table test_change change a a1 int; select a1 from test_change; null – mike_pdb Nov 12 '15 at 06:05
@mike_pdb: I'm not sure that is intended. I understand why it happens, and perhaps ORC has the same problem, but I'm not sure is 'by design'. Can you create a JIRA? https://issues.apache.org/jira – Remus Rusanu Nov 12 '15 at 17:31
@mike_pdb found https://issues.apache.org/jira/browse/HIVE-6938 which claims this is fixed in Hive 0.14. Which version did you test with? – Remus Rusanu Nov 12 '15 at 17:38
@RemusRusanu i'm using cloudera enterprise the hive version per their doc sheet is hive-1.1.0+cdh5.4.7+233. i will try to test on ORC later today. – mike_pdb Nov 12 '15 at 23:08

score 7 · Answer 2 · edited Apr 25 '16 at 10:28

Command works only if "use" -command has been first used to define the database where working in. Table column renaming syntax using DATABASE.TABLE throws error and does not work. Version: HIVE 0.12.

EXAMPLE:

hive> ALTER TABLE databasename.tablename CHANGE old_column_name new_column_name;

  MismatchedTokenException(49!=90)
        at org.antlr.runtime.BaseRecognizer.recoverFromMismatchedToken(BaseRecognizer.java:617)
        at org.antlr.runtime.BaseRecognizer.match(BaseRecognizer.java:115)
        at org.apache.hadoop.hive.ql.parse.HiveParser.alterStatementSuffixExchangePartition(HiveParser.java:11492)
        ...

hive> use databasename;

hive> ALTER TABLE tablename CHANGE old_column_name new_column_name;

OK

score 6 · Answer 3 · answered Jun 16 '14 at 11:13

alter table table_name change old_col_name new_col_name new_col_type;

Here is the example

hive> alter table test change userVisit userVisit2 STRING;      
    OK
    Time taken: 0.26 seconds
    hive> describe test;                                      
    OK
    uservisit2              string                                      
    category                string                                      
    uuid                    string                                      
    Time taken: 0.213 seconds, Fetched: 3 row(s)

score 2 · Answer 4 · answered Dec 25 '20 at 07:33

In the comments @libjack mentioned a point which is really important. I would like to illustrate more into it. First, we can check what are the columns of our table by describe <table_name>; command.

there is a double-column called _c1 and such columns are created by the hive itself when we moving data from one table to another. To address these columns we need to write it inside backticks

`_c1`

Finally, the ALTER command will be,

ALTER TABLE <table_namr> CHANGE `<system_genarated_column_name>` <new_column_name> <data_type>;

Hive Alter table change Column Name

4 Answers4

Linked