question Re: How to dump the output to a file from Beeline? in Support Questions

How to dump the output to a file from Beeline?

Alexraj84 — Mon, 04 Apr 2016 18:54:42 GMT

Hi,

I am trying to dump the output of a beeline query (below) to a file but it prints all the logs along with the output in the file.

beeline -u jdbc:hive2://somehost_ip/ -f hive.hql >op.txt

Here is the output

0: jdbc:hive2://10.211.1.5:10000/> use db;
0: jdbc:hive2://10.211.1.5:10000/> select count(*) from sample_table;
+------+--+
| _c0  |
+------+--+
| 131  |
+------+--+
0: jdbc:hive2://10.211.1.5:10000/>
0: jdbc:hive2://10.211.1.5:10000/>
0: jdbc:hive2://10.211.1.5:10000/>

Can someone let me know how to get the output alone in a file as we get using Hive?

Re: How to dump the output to a file from Beeline?

agillan — Mon, 04 Apr 2016 19:43:02 GMT

Hi @Alex Raj, to clarify, do you just want the "131" output to a file? You can try this in your hql:

INSERT OVERWRITE DIRECTORY '/user/user1/results' select count(*) from sample_table;

This will write the output of your query into the results directory on HDFS.

Re: How to dump the output to a file from Beeline?

bleonhardi — Mon, 04 Apr 2016 20:51:01 GMT

You have different options.

1) You can control up to a point how the beeline output is made and then just save it to a file with linux. For example

beeline --outputformat=csv2 xxx > output.csv ( see the relevant parameters from the beeline help below )

2) For more control and better performance I wrote a little Java tool once. Its really only a couple lines of jdbc code.

3) and finally as Ana wrote. Yopu can just write a table into an external table in HDFS and specify the output format you want.

create external table test ROW FORMAT delimited fields terminated by '|' location "/tmp/myfolder" as select * from mytable;

you can then get that output in the local file system with

hadoop fs -getmerge /tmp/myfolder myoutput.csv

 --showHeader=[true/false]       show column names in query results
   --headerInterval=ROWS;          the interval between which heades are displayed
   --fastConnect=[true/false]      skip building table/column list for tab-completion
   --autoCommit=[true/false]       enable/disable automatic transaction commit
   --verbose=[true/false]          show verbose error messages and debug info
   --showWarnings=[true/false]     display connection warnings
   --showNestedErrs=[true/false]   display nested errors
   --numberFormat=[pattern]        format numbers using DecimalFormat pattern
   --force=[true/false]            continue running script even after errors
   --maxWidth=MAXWIDTH             the maximum width of the terminal
   --maxColumnWidth=MAXCOLWIDTH    the maximum width to use when displaying columns
   --silent=[true/false]           be more silent
   --autosave=[true/false]         automatically save preferences
   --outputformat=[table/vertical/csv2/tsv2/dsv/csv/tsv]  format mode for result display
                                   Note that csv, and tsv are deprecated - use csv2, tsv2 inste

Re: How to dump the output to a file from Beeline?

Alexraj84 — Sun, 18 Aug 2019 12:30:00 GMT

Hi @Ana Gillan, thank you for your answer. This method is perfect to dump the output into a file however the HQL I write has some echo statements which will be printed before the result and this is why I wanted to redirect the output in the SQL style. However after reading @Benjamin Leonhardi 's answer I got a clue from it. Here is the modified query that will print a neat output without any logs included. Thanks to @Benjamin Leonhardi.

beeline -u jdbc:hive2://somehost_ip/ --silent=true -f hive.hql >op.txt

But in the output file there are still couple of Blank lines printed. Here is the screen shot of the output.

In the output file there are two blank lines at the beginning and three trailing blank lines. If this can be fixed it would be great.

Re: How to dump the output to a file from Beeline?

Alexraj84 — Tue, 05 Apr 2016 12:31:55 GMT

Hi @Benjamin Leonhardi, Thanks for your answer. I got a clue from it that has made task to attain the output almost close to my expectation. If you go through my comments in @Ana Gillan 's answer I have described my issue and if you can get an answer for it, I am done for the day 🙂

Re: How to dump the output to a file from Beeline?

bleonhardi — Tue, 05 Apr 2016 17:25:26 GMT

How about this?

beeline -u jdbc:hive2://localhost:10000/default --silent=true --outputformat=csv2 -e "select * from sample_07 limit 10" > out.txt

[root@sandbox ~]# cat out.txt

sample_07.code,sample_07.description,sample_07.total_emp,sample_07.salary

00-0000,All Occupations,134354250,40690

11-0000,Management occupations,6003930,96150

11-1011,Chief executives,299160,151370

11-1021,General and operations managers,1655410,103780

11-1031,Legislators,61110,33880

11-2011,Advertising and promotions managers,36300,91100

11-2021,Marketing managers,165240,113400

11-2022,Sales managers,322170,106790

11-2031,Public relations managers,47210,97170

11-3011,Administrative services managers,239360,76370

Re: How to dump the output to a file from Beeline?

Alexraj84 — Sun, 18 Aug 2019 12:29:52 GMT

Hi @Benjamin Leonhardi, still the same problem.

Here is what I get.

I am using CDH 5.3.6 with 0.13.1 beeline version.

Also I have another question if you don't mind answering, please. How to add a string in the output file while we execute the above command? Here is what I want to see in my output file

In Hive I can get this using '!echo' but the same does not work with beeline. I have tried '!sh echo' also but that does not seems to work. Much much appreciate your time.

Re: How to dump the output to a file from Beeline?

bleonhardi — Wed, 06 Apr 2016 18:42:41 GMT

No idea about the empty lines sorry, I don't have these lines as you can see. Just delete them with sed? ( google delete empty lines linux )

To add a string on top of a file you could just use linux as well from the outside i.e. :

echo "The count is:" > out.txt

beeline .... >>out.txt

the double ">>" appends to a file

Re: How to dump the output to a file from Beeline?

Yan — Sun, 12 Jun 2016 13:51:21 GMT

does 'INSERT OVERWRITE DIRECTORY' worked with beeline? i use it with hive , and it worked well, but when i switch to beeline, nothing output

Thank you

Re: How to dump the output to a file from Beeline?

ravimishrart201 — Mon, 08 May 2017 14:40:20 GMT

Hi Alex raj,

You are missing --outputformat=csv2 in the command , kindly mention it and the file format should be the csv. It will work. I have tried it. As, you have only one value so only one value will come in the csv file, try export more columns and rows then it will give you the better understanding.

beeline -u jdbc:hive2://somehost_ip/ --silent=true --outputformat=csv2 -f hive.hql >op.csv

Re: How to dump the output to a file from Beeline?

ravimishrart201 — Mon, 08 May 2017 14:42:16 GMT

Hi 苏斌,

I have not tried INSERT OVERWRITE DIRECTORY when i switch to beeline. Try to use this command:-

beeline -u jdbc:hive2://somehost_ip/ --silent=true --outputformat=csv2 -f hive.hql >op.csv

Re: How to dump the output to a file from Beeline?

Big — Sat, 20 Oct 2018 08:51:52 GMT

Hi Team,

I am trying to print output along with number records selected or number record affected messages in the output file but I am unable to print it.

I have tried with --verbose=true also but it is not helping.

<p>

beeline -u "jdbc:hive2://abc.nsroot.net:10000/;principal=hive/abc.nsroot.net@ABC.NSROOT.NET;ssl=true " -f /home/test1.hql > testresult1.txt

</p>

hql files has only one query "select * from mytable;"

can any one suggest me on this?

Thanks & regards,

Kamleshkumar Gujarathi

Re: How to dump the output to a file from Beeline?

neha1689 — Sat, 13 Apr 2019 18:55:35 GMT

@Benjamin Leonhardi I am facing a problem while fetching ouput in csv2 format. Since my data itself contains comma hence that specific column is encapsulated in special character "^@". Can you please help of how to avoid it or if I can get the output in a pipe delimited form?

Re: How to dump the output to a file from Beeline?

David M. — Tue, 22 Oct 2019 20:21:30 GMT

Also, just wanted to point out that, depending on the version of Hive being used, may default to buffering within the beeline client. Be sure to enable 'incremental' fetches of data from the Hive server when dealing with large result sets.

--incremental=[true/false]

Defaults to true from Hive 2.3 onwards, before it defaulted to false. When set to false, the entire result set is fetched and buffered before being displayed, yielding optimal display column sizing. When set to true, result rows are displayed immediately as they are fetched, yielding lower latency and memory usage at the price of extra display column padding. Setting --incremental=true is recommended if you encounter an OutOfMemoryException.

https://cwiki.apache.org/confluence/display/Hive/HiveServer2+Clients

Re: How to dump the output to a file from Beeline?

David M. — Tue, 22 Oct 2019 20:42:02 GMT

Another option for large data sets, if ordering doesn't matter, is to create an EXTERNAL table with the necessary delimiters and issue an INSERT statement into the table instead of a SELECT statement in beeline. To copy the data locally, issue a:

hdfs dfs -cat /my/table/*

"Order doesn't matter' because the cat application will not necessarily read the files in proper order. If an ORDER BY is included in the query, the contents of each file will be in order, but the files may be read out of order by the 'cat' application.

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL

https://hadoop.apache.org/docs/r2.8.0/hadoop-project-dist/hadoop-common/FileSystemShell.html#cat

Re: How to dump the output to a file from Beeline?

Ravi_bunn — Tue, 28 Apr 2020 14:22:10 GMT

Please try this, this will only output the stadard output of the not error or logs:

beeline -u jdbc:hive2://somehost_ip/ -f hive.hql 2> /dev/null >op.txt

Re: How to dump the output to a file from Beeline?

Ravi_bunn — Tue, 28 Apr 2020 14:28:21 GMT

Please check below command, here 2> /dev/null will consume all the logs and error. It will now allow standard output to be shown:

beeline -u jdbc:hive2://somehost_ip/ -f 2> /dev/null hive.hql >op.txt

if you like this please give me kudos. Thanks!!!