Remove headers in output in beeline

Question

I am trying beeline cli to query hive table and store output result as variable. Using beeline command:

beeline -u connection_string -n user_name -w password_file \
-e "select count(*) from db.table_name"

Using this command, I getting the current output result as:

+---------------+--+ 
| record_count  | 
+---------------+--+ 
| 80785         | 
+---------------+--+

While I need result as: Record count:80785

Another command I am using is:

beeline -u connection_string -n user_name -w password_file \
-e "select * from db.table_name;” > result.csv

Which again displaying result in tabular format data separated by |.

Basically beeline, by default, is returning the header( table_name.column_name) then the data in tabular format. Whereas, I want to eliminate this and get results like hive CLI.

Indrajeet Gour · Accepted Answer · 2017-12-27T12:00:02.970

You can use argument --showHeader=false --outputformat=tsv2 to illuminate this.

using THIS FORMAT YOUR command will be like

beeline --showHeader=false --outputformat=tsv2 \
-u connection_string -n user_name -w password_file \
 -e "select count(*) from db.table_name"

Consider If tsv2 used

id  value   comment
1   Value1  Test comment 1
2   Value2  Test comment 2
3   Value3  Test comment 3

If dsv used (the delimiter is |)

id|value|comment
1|Value1|Test comment 1
2|Value2|Test comment 2
3|Value3|Test comment 3

Your data will look like this. Do remember these three are enabled with single quotes around the value if values are having special char or new line within. Quoting can be disabled by setting the disable.quoting.for.sv to true.

SOME MORE OPTION USING CSV AND TSV

csv, tsv These two formats differ only with the delimiter between values, which is comma for csv and tab for tsv.

when csv is used, data will look like this

'id','value','comment'
'1','Value1','Test comment 1'
'2','Value2','Test comment 2'
'3','Value3','Test comment 3'

And when tsv is used, then

'id'    'value' 'comment'
'1' 'Value1'    'Test comment 1'
'2' 'Value2'    'Test comment 2'
'3' 'Value3'    'Test comment 3'

Just beware while using csv or tsv, you will have single quote surrounded by value always and you can not get rid of it, which could lead to some problem in few cases.

Hope the above details explanation would cover all the possible cases you wanted to cover.

For more explanation visit the Apache Beeline Wiki page. cheers!!

Can you please explain `tsv2 ` stands for, and the advantages using this. — Sandeep Singh, Dec 27 '17 at 09:48
Here count is only an example, I need solution that can be used in all select query. — Sandeep Singh, Dec 27 '17 at 10:19
@SandeepSingh tsv gives `'col1' 'col2' 'col3' ...` whereas tsv2 gives `col1 col2 col3 ...` (imagine my spaces are actually TAB characters!). ...So **tsv2** is what most developers think of as the "proper" tab-separated value format. — DouglasDD, Jun 04 '21 at 11:38

score 1 · Answer 2 · answered Dec 27 '17 at 07:13

1

Beeline has a bunch of command line options.

In your case, you can use :

beeline --outputformat=dsv --showheader=false \
-e "select count(*) from db_name.table_name"

answered Dec 27 '17 at 07:13

philantrovert

9,904
3
37
61

Can you please explain `dsv` stands for and advantages using this – Sandeep Singh Dec 27 '17 at 09:49
`dsv` stands for delimiter separated value and it has no specific advantage in your case except for the fact that it'll suppress the table-like structure that beeline prints with hyphens and plus => `+-----+` – philantrovert Dec 27 '17 at 09:53

Remove headers in output in beeline

2 Answers2