Search code examples
hadoophivehadoop2beeline

Remove headers in output in beeline

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.

Solution

  • 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!!