Shell Script to read hive table and send email

UNIX for Beginners Q & A
1

HI Team,
I am working on reading hive table and send email in email body using shell script, can you please help on fixing the errors: I have 6 columns in my hive table and trying to send the email in the mail body.
below script:

#!/bin/bash
hive -e 'SELECT count(*) from db.table' > output.txt
sed -i 's/\t/,/g' $tmp/output.txt

nawk BEGIN{
FS=","
print  "MIME-Version: 1.0"
print  "Content-Type: text/html"
print  "Content-Disposition: inline"
print  "<HTML>""<TABLE border="1"><TH>TB1</TH><TH>TB2</TH><TH>TB3</TH><TH>TB4</TH><TH>TB5</TH><TH>TB6</TH>" }
 {
printf "<TR>"
for(i=1;i<=NF;i++)
printf "<TD>%s</TD>", $i
print "</TR>"
 }
END{
print "</TABLE></BODY></HTML>"
 }
file-to-convert.csv > file.html
echo "output.txt" | mailx -s "output" -a output.txt email.com

--- Post updated at 04:21 PM ---

getting below error:

Java HotSpot(TM) 64-Bit Server VM warning: Using the ParNew young collector with the Serial old collector is deprecated and will likely be removed in a future release
OK
Time taken: 4.722 seconds, Fetched: 5 row(s)
Waiting for Tez session and AM to be ready...
sed: can't read $tmp/output.csv: No such file or directory
./countemail.sh: line 18: nawk: command not found
./countemail.sh: line 19: output1.csv: command not found
2

I am missing something here...
./countemail.sh: : Is that the script you gave us ?
Since when a csv file is a script?

 file-to-convert.csv > file.html
3

yes countemail.sh is the script and below is the code:

--- Post updated at 07:51 PM ---

complete script

#!/bin/bash
hive -e 'select * FROM db.table' > output1.csv
sed -i 's/\t/,/g' output1.csv
nawk BEGIN{
FS=","
print  "MIME-Version: 1.0"
print  "Content-Type: text/html"
print  "Content-Disposition: inline"
print  "<HTML>""<TABLE border="1"><TH>project_name</TH><TH>schema_name</TH><TH>table_name</TH><TH>partition_col_name</TH>"}
 {
printf "<TR>"
for(i=1;i<=NF;i++)
printf "<TD>%s</TD>", $i
print "</TR>"
 }
END{
print "</TABLE></BODY></HTML>"
 }''
output1.csv > file.html
echo "file.html" | mailx -s "output" -a file.html email
4

Firstly the posted script seems to be missing single quote at the start of the awk code, and two single quotes at the end.

Also your system is reporting that nawk is not installed nawk is usually required on Solaris systems, you don't report your OS but my guess is a non-Solaris system. Try using awk or gawk instead.

Try:

#!/bin/bash
hive -e 'select * FROM db.table' > output1.csv
sed -i 's/\t/,/g' output1.csv
awk 'BEGIN{
FS=","
print  "MIME-Version: 1.0"
print  "Content-Type: text/html"
print  "Content-Disposition: inline"
print  "<HTML>""<TABLE border="1"><TH>project_name</TH><TH>schema_name</TH><TH>table_name</TH><TH>partition_col_name</TH>"}
 {
printf "<TR>"
for(i=1;i<=NF;i++)
printf "<TD>%s</TD>", $i
print "</TR>"
 }
END{
print "</TABLE></BODY></HTML>"
 }' output1.csv > file.html
echo "file.html" | mailx -s "output" -a file.html email
1 Like
5

Thank you very much, this code worked. Appreciate your help.