CODEWITHSUNDEEP
hadoophivehiveql
sam
sam

Reputation: 23

hive -e with delimiter

I am trying to export the data from hive table as below but it is generating file with tab as delimiter. Is it possible to specify the delimiter as comma or tab when exporting it?

hive -e "
use default;
set hive.cli.print.header=true;
select * from test1;
    " > /temp/test.txt (or .csv)

Thanks J

Upvotes: 2

Views: 5652

Answers (3)

Joint Song
Joint Song

Reputation: 21

I have investigated for hours. Hive cli almost can not resolve it very well. You can set hive.cli.print.escape.crlf=true to escape '\r', '\n'. But you can not escape '\t'. And Hive cli is not under actively maintainance, use beeline as possible as you can.

hive.cli.print.escape.crlf: https://github.com/apache/hive/blob/master/common/src/java/org/apache/hadoop/hive/conf/HiveConf.java#L3694

Upvotes: 0

leftjoin
leftjoin

Reputation: 38325

Alternatively to @Prabhat Ratnala solution, which is OK, you can use this:

INSERT OVERWRITE LOCAL DIRECTORY '/temp/your_dir' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' SELECT * FROM test1;

Upvotes: 0

Prabhat Ratnala
Prabhat Ratnala

Reputation: 705

You can pipe your data and use a simple sed command to convert your data into required output format. Below sed command replaces tab with comma. Default output is Tab. You can change the output accordingly.

hive -e "use default;set hive.cli.print.header=true;select * from test1;" | sed 's/[\t]/,/g' >/temp/test.csv

Upvotes: 2

Related Questions