0

To export hive data to csv I use the following script:

note that this is just a example:

#!/bin/bash
hive -e "insert overwrite local directory '/tmp/'
row format delimited fields terminated by ','
select * from Mydatabase,Mytable limit 10"
> /tmp/table.csv

I get a csv file in the local path: /tmp/, it looks like :

2017-07-04 12:58:05.0,MB0_CLI_2,29353,0982,SAIE,MNIT-BM,-,-
2017-07-04 12:56:07.0,MBUD00_CLI_2,629353,662982,SAE,MBEINIT-BM,-,-
2017-07-04 12:56:46.0,MBUDLI_2,618,65530,SAISIE,MBUIT-BA,-,-
2017-07-04 12:56:13.0,82_CLI_3,65082954,47857,UNAGE,-,PARERS,-
2017-07-04 12:56:05.0,822I_3,654,857,UNDGE,-,PAIRS,-
2017-07-04 12:59:28.0,823,65084,6926461,UNDNED_AAGE,-,PLIERS,-
2017-07-04 12:59:05.0,82200_CLI_3,65954,69461,UNNED_AFAGE,-,PULIERS,-
2017-07-04 12:56:46.0,MBUDLI_2,618,65530,SAISIE,MBUIT-BA,-,-
2017-07-04 12:56:13.0,82_CLI_3,65082954,47857,UNAGE,-,PARERS,-
2017-07-04 12:56:46.0,MBUDLI_2,618,65530,SAISIE,MBUIT-BA,-,-

The problem is that the csv file doesn't import the head of each column

how can I export hive table into csv file with the head of columns?

HISI
  • 4,557
  • 4
  • 35
  • 51
  • https://stackoverflow.com/a/23944851/1319284 – kutschkem Mar 21 '18 at 12:46
  • @VamsiPrabhala I got this error : FAILED: ParseException line 1:0 cannot recognize input near 'hive' '.' 'cli' – HISI Mar 21 '18 at 12:50
  • 1
    You can put more command separated by semi-colon. I do it this way: hive -S -e 'set hive.execution.engine=tez; set hive.cli.print.header=true; select * from tableName' etc etc.. – jose_bacoy Mar 21 '18 at 12:51

1 Answers1

0

I do it this way. You can put more command by separating it by semi-colon. Sed is a command to change , to blank and tabs (default separator) to comma. The result is a csv file named; tableName.csv.

hive -e 'set hive.execution.engine=tez; set hive.cli.print.header=true; select * from <TableName>' | sed 's/,/ /g' | sed 's/[\t]/,/g'  > /tmp/tableName.csv
jose_bacoy
  • 12,227
  • 1
  • 20
  • 38