Writing a loop in a shell script to read output of HIVE Select

Question

I am new to writing shell scripts. I currently have some reports that we run in HIVE SQL and I am trying to automate them the best I can. Currently I use crontab within our UNIX environment to have these queries run automatically everyday. Right now I have to paste that data into excel then filter and create separate documents for each "end-user".

What I am trying to accomplish is this:

I have a column in my output query that shows the name of a company, CompanyA, CompanyB, CompanyC etc. Depending on the details in there it could be anywhere from 12-20 different "companies". I don't want to hard code a query for each one to create it's own output. What I would like to do, is have a query that selects each unique company in that field(agency_name) and then run my select statement that would say:

select * from output_results where agency_name = "name here" and then write this output to a csv named Balance_Detail_"NAME"_Date then loop through and run this query and create an output for each name it found in the agency_name field.

Sorry we can't write the code for you. What you're asking sounds very possible. I would first create a query that just generated of Company names that you'll be processing on this run of your script, saving those values as plainly as possible to a file. Then you can wrap your select statment above in a loop, while read co_name ; do printf "select .... $co_name ....\n" | sqlClient ...args... | awk '...post filtering...' > $co_name.txt < co_nameFileList.txt Would be the general outline, but will require a fair amout of work on your part to get it to work and then bullet-proof it for errors. — shellter
– shellter, Commented Jun 22, 2020 at 18:16
Thank you for this. I apologize if it seemed like I was looking to have it written for me. I was more just trying to understand if the thoughts I had in my head were possible and how to even start that process. Thank you for your suggestion. I will start looking into that and see how I get along. — Sotark
– Sotark, Commented Jun 22, 2020 at 18:49
Very good! Best to post a new Q or anything that is slowing you up, trying to keep it focused on one problem at a time. Best if you can include small sample set of data, required output from that sample, current output, code, and exact text of error messages. Use the {} tool from the Edit menu on mouse-selected text to get code/data/output/errMsg formatting. Try to make it so it is copy/pasteable by readers. Also recall that we won't have access to your databases, but include which client you are using from cmdline (no GUIs!). sqlplus or isql are common. Good luck. — shellter
– shellter, Commented Jun 22, 2020 at 22:41

leftjoin · Accepted Answer · 2020-06-24 05:45:25Z

3

Here is a template how you can loop query result and call another hive script and to save in a CSV. Needs debugging. And of course use your queries:

#!/bin/bash

dt=$(date +'%Y_%m_%d')

for NAME in $(hive -S -e "select distinct agency_name from ..... ") 
 do 
  #echo "Company name is $NAME"
  hive -S -e " select * from output_results where agency_name = $NAME" | sed 's/[[:space:]]\+/,/g' > Balance_Detail_${NAME}_${dt}
 done

edited Jun 24, 2020 at 5:45

answered Jun 23, 2020 at 8:37

leftjoin

38.5k8 gold badges64 silver badges126 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Writing a loop in a shell script to read output of HIVE Select

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