Hive change column name without knowing column data type

1
votes

I want to change the column name of a Hive table without changing it's datatype.

I tried below query but it requires datatype which I don't know.

ALTER TABLE test CHANGE a a1 INT;

I would like to prefix SALES_ before all my columns irrespective of their column types.

Input Table

emp_id(int) emp_name(string) salary(double)

Output Table

sales_emp_id(int) sales_emp_name(string) sales_salary(double)

Thanks in advance.

2
hadoophive
can you explain which is the scenario you would need this operation for? - Coder123

2 Answers

1
votes

Well, altering the column name in hive using alter table command require its datatype. For this purpose you may perform the below commands,

1)Create a new table with the your new column names)

create table newTable (sales_emp_id int ,sales_emp_name string, sales_salary double) ;

2)Insert into new table from old table

insert into newTable select * from oldtable;

3)Now,you may drop your old table.

  drop table oldtable;

The above code may be used if creating a new table sounds ok for you.

Well if you use a shell script , something like below:

    while read line;do
    SOURCE_TABLENAME= `echo $line| awk -F" " '{print $1}'`
    TARGET_TABLENAME= `echo $line| awk -F" " '{print $2}'`
    LOC=`echo "$line"| awk -F" " '{print $3}'`
    PREFIX="emp_"
    S=`hive -e "desc $SOURCE_TABLENAME"`

    VAL=echo $S |sed 's/\(\(\w\w*\W*\)\{2\}\)/\1\n/g' | sed 's/$/,/g' | sed -e 's/^/$PREFIX/'

    STATEMENT="CREATE TABLE $SOURCE_TABLENAME (`echo $VAL) as select * from $SOURCE_TABLENAME LOCATION $LOC`"
hive -e "drop table $SOURCE_TABLENAME"
    done <   INPUT_FILE.txt

INPUT_FILE.txt

source_table target_table location (all inputs separated by space)

0
votes

Without creating new table, you can use the REPLACE function in hive to change all the column names. The command looks like this

ALTER TABLE table_name REPLACE COLUMNS (sales_emp_id INT,sales_emp_name STRING,sales_salary DOUBLE);

Now you can use the describe command to check the column names

describe table_name;