CODEWITHSUNDEEP
csvhiveheader
user3636476
user3636476

Reputation: 1417

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

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

Upvotes: 4

Views: 5763

Answers (4)

Jegan Baskar
Jegan Baskar

Reputation: 1

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.

Upvotes: 0

Yang Song
Yang Song

Reputation: 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

Upvotes: -1

user3636476
user3636476

Reputation: 1417

I just needed to add:

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

Upvotes: 15

xxks-kkk
xxks-kkk

Reputation: 2608

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

Upvotes: 0

Related Questions