Hive - How to remove column header from result set

Question

I am trying to fix one issue which has created after hive query execution.

A new table named 'stock_data' which is holding stock price , stock symbol and all other details.

I had a tsv file ( tab separated file ) and used 'load data local inpath' command to load database table.

tsv file having column header like 'stock_name','stock_symbol' ....

Now, when I wanted to display stock symbol and with number of counts using below query :-

select stock_symbol,count(*) from stocks group by stock_symbol;

Output is loading column header

XOM 500
XRX 500
XTO 496
YPF 500
YUM 500
YZC 478
ZAP 494
ZF  494
stock_symbol    1

Time taken: 20.576 seconds, Fetched: 1735 row(s)

My question is :-

How to hide or remove 'stock_symbol 1' ( Last line ) from my result set ?

I tried to use below command before run :-

set hive.cli.print.header=false;

Did not work for me ...

Could anyone help me for the same ..

Thanks ..

You cna use a where condition. – Ankit Bajpai Aug 03 '15 at 10:51 — Ankit Bajpai, Aug 03 '15 at 10:51

score 1 · Answer 1 · answered Aug 03 '15 at 11:33

1

try below command from shell terminal

hive -s -e 'select stock_symbol,count(*) from stocks group by stock_symbol;' | tail -1

Note:use tail or head commands to terminate the last line...

answered Aug 03 '15 at 11:33

Kiran teja Avvaru

334
3
10

Thanks men, perfect. – Alejo Florez Jun 14 '19 at 17:29

score 0 · Answer 2 · answered Aug 03 '15 at 10:54

The problem would appear to be that your data was imported with the header as a data row. I might suggest that you just delete the value:

delete from stocks
    where stock_symbol = 'stock_symbol';

Otherwise, you can modify your queries and use WHERE to exclude it:

select stock_symbol, count(*)
from stocks
where stock_symbol <> 'stock_symbol'
group by stock_symbol;

score 0 · Answer 3 · edited May 23 '17 at 10:26

0

belive you are having the Input Dump which all having header, and thats what its reflecting in the count.

Please check the link Skip first line of csv while loading in hive table

the answer has been shared already :)

sample example meantion earlier also

CREATE TABLE temp 
  ( 
     name STRING, 
     id   INT 
  ) row format delimited fields terminated BY '\t' lines terminated BY '\n' 
tblproperties("skip.header.line.count"="1");

edited May 23 '17 at 10:26

Community

1
1

answered Aug 04 '15 at 12:08

Deb

473
3
13

yes you are right ..During insertion , header exist inside tsv file. Now I was trying to run query without showing header in output. – saikat123 Aug 05 '15 at 07:25

Hive - How to remove column header from result set

3 Answers3