how to run beeline and hive query from a bash shell script

Question

I am able to run below steps manually in order after logging in to unix bash shell.

echo "Connecting beeline" 

beeline
!connect jdbc:hive2://a301-1234-1234.stm.XXX.com:10000/default;;ssl=true;sslTrustStore=/app/bds/cloudera_truststore.jks;sslTrustPassword=;principal=hive/_HOST@BDS.XXXX.COM

INSERT OVERWRITE DIRECTORY "/dev/ref/HIVE_EXPORT/" ROW FORMAT DELIMITED FIELDS TERMINATED BY "," ESCAPED BY "\\" SELECT * FROM test_ref_st.Daily_report  limit 10;

hadoop fs -get('/dev/ref/HIVE_EXPORT/000000_0', '/user/rj/hiveExtract.csv')
echo "Query result extracted "

I need to run all above steps in sequence via a shell script test1.sh like

bash-4.2$ sh -x test1.sh

then it is only running till beeline and remaining commands are not being run. Current output:

bash-4.2$ sh test1.sh

Picked up JAVA_TOOL_OPTIONS:
Beeline version 1.1.0-cdh5.16.2 by Apache Hive
beeline>

score 3 · Accepted Answer · answered Jul 07 '20 at 23:08

3

Bash is processing your script line by line. It runs beeline and waits for your input. You can use heredoc to write to stdin from your script:

beeline <<EOF
!connect jdbc:hive2://a301-1234-1234.stm.XXX.com:10000/default;;ssl=true;sslTrustStore=/app/bds/cloudera_truststore.jks;sslTrustPassword=;principal=hive/_HOST@BDS.XXXX.COM
INSERT OVERWRITE DIRECTORY "/dev/ref/HIVE_EXPORT/" ROW FORMAT DELIMITED FIELDS TERMINATED BY "," ESCAPED BY "\\" SELECT * FROM test_ref_st.Daily_report  limit 10;
EOF

answered Jul 07 '20 at 23:08

Piotr Kubiak

68
6

Still not working getting error: Error: Error while compiling statement: FAILED: ParseException line 1:266 character '' not supported here line 1:154 character '\' not supported here line 1:266 character '' not supported here (state=42000,code=40000) – Rohit Jul 08 '20 at 15:12

score 2 · Answer 2 · answered Jul 07 '20 at 23:52

Using !connect will open up the beeline shell console. For using beeline CLI command you can do the following in SHELL:

#!/bin/bash

HIVE_CONN=jdbc:hive2://a301-1234-1234.stm.XXX.com:10000/default;;  ## limited for simplicity

echo "executing query using beeline"
beeline -u $HIVE_CONN -e "INSERT OVERWRITE DIRECTORY "/dev/ref/HIVE_EXPORT/" ROW FORMAT DELIMITED FIELDS TERMINATED BY "," ESCAPED BY "\\" SELECT * FROM test_ref_st.Daily_report  limit 10;"

... 
rest of your code

-e stand for the query you want to execute

how to run beeline and hive query from a bash shell script

2 Answers2

Linked