Running hive query using shell script

Question

I have a hive query that is working fine. but when I want to run this in a shell script I am unable to make it to work.

Below is the shell script.

#!/bin/bash    

start_date='2019-08-01'
end_date='2019-08-31'

while IFS='|' read -r table_name val;
do
     hive -e "set hive.cli.print.header=true;select source_to_date, target_count from testing.log_final where project_name= '${val}' and source_to_date between '${start_date}' and '${end_date}' order by source_to_date;" | sed 's/[\t]/,/g'  > /x/home/SUER/btree/"${table_name}".csv

done < /x/home/"$USER"/bt_tables.txt

Contents of bt_tables.txt:

merchants|102_merchants_project
payments|103_payments_project

Query that works fine:

hive -e "set hive.cli.print.header=true;select source_to_date, target_count from testing.log_final where project_name= '102_merchants_project' and source_to_date between '2019-08-01' and '2019-08-31' order by source_to_date;" | sed 's/[\t]/,/g'  > /x/home/SUER/btree/merchants.csv

What am I doing wrong here?

@howie There is no error but the file created is empty. I am expecting data in the file — nmr
– nmr, Commented Sep 17, 2019 at 1:50
You mean **/x/home/SUER/btree/merchants.csv ** is empty ? Do you try without sed ? — howie
– howie, Commented Sep 17, 2019 at 1:54
@howie yes That file is empty and the query is also not returning any results. Looks like the while loop is not working as expected — nmr
– nmr, Commented Sep 17, 2019 at 1:56
add echo to check what hive command is being built, like this: echo "hive -e set hive.cli.print.header=true; d select source_to_date, target_count from testing.log_final where project_name='${val}' and source_to_date between '${start_date}' and '${end_date}' order by source_to_date;" — leftjoin
– leftjoin, Commented Sep 17, 2019 at 7:20

laurent · Accepted Answer · 2019-09-17 08:55:31Z

Your given command may be ok, but you are redirecting in your output file, not appending.

See man bash

Redirecting Output
   Redirection  of output causes the file whose name results from the expansion of word to be opened for writing
   on file descriptor n, or the standard output (file descriptor 1) if n is not specified.  If the file does not
   exist it is created; if it does exist it is truncated to zero size.

   The general format for redirecting output is:

          [n]>word

You can use append redirect output with >> or write to your file after the end of while execution

Appending Redirected Output
   Redirection of output in this fashion causes the file whose name results from the expansion  of  word  to  be
   opened  for appending on file descriptor n, or the standard output (file descriptor 1) if n is not specified.
   If the file does not exist it is created.

   The general format for appending output is:

          [n]>>word

In your case :

while IFS='|' read -r table_name val;
do
     hive -e "cmd"  >> /x/home/SUER/btree/"${table_name}".csv
done < /x/home/"$USER"/bt_tables.txt

or

while IFS='|' read -r table_name val;
do
     hive -e "cmd"
done < /x/home/"$USER"/bt_tables.txt >/x/home/SUER/btree/"${table_name}".csv

IHTH

Collectives™ on Stack Overflow

Running hive query using shell script

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related