How to get column name in Hive when outputting the query (and not table_name.column_name)

Question

Using Hive I would like to get the result of a query inside a csv file, I would also like to get the header.

To achieve this I'm executing:

hive -e '
    use database;
    set hive.cli.print.header=true;
    select * from table;
' > /home/centos/user/result.csv

But the first line of the csv is:

table.field1_name,table.field2_name,table.field3_name

How can I get insead:

field1_name,field2_name,field3_name

score 15 · Answer 1 · answered Mar 17 '16 at 07:49

15

I just needed to add:

set hive.resultset.use.unique.column.names=false;

answered Mar 17 '16 at 07:49

user3636476

1,357
2
11
22

2

You could improve your answer by adding an explanation of why this works. – simbabque Mar 17 '16 at 09:40

score 0 · Answer 2 · answered Mar 17 '16 at 07:43

0

sed will do the job.

Suppose fun.csv contains the following:

table.field1_name,table.field2_name,table.field3_name

Then I do the following in the command line:

sed -e 's/table.//g' < fun.csv > fun2.csv

Now fun2.csv has:

field1_name,field2_name,field3_name

answered Mar 17 '16 at 07:43

xxks-kkk

2,336
3
28
48

yeah I know I can do this, but i suppose a hive option is availabe – user3636476 Mar 17 '16 at 07:48
@user3636476 glad you find solution but one liner wouldn't hurt I guess :) – xxks-kkk Mar 17 '16 at 08:01

score 0 · Answer 3 · answered Feb 21 '17 at 12:40

0

you can use select column1,column2 from tablename instead of select * from tablename, in that case tablename will not be added into the column name. In that case need not give sed command.

answered Feb 21 '17 at 12:40

Jegan Baskar

1

score -1 · Answer 4 · edited May 23 '17 at 12:31

-1

I use

hive -e 'set hive.cli.print.header=true; select * from database.table;' | sed 's/[\t]/,/g' > /your_path_to_csv

It works well for me.

Reference: Hive query output delimiter

edited May 23 '17 at 12:31

Community

1
1

answered Jun 07 '16 at 21:46

Yang Song

1

How to get column name in Hive when outputting the query (and not table_name.column_name)

4 Answers4