Tuesday, October 26, 2021

[SOLVED] converting list into array for values stored in a variable

Issue

I have a configuration file. the first column is job name and rest of the column are tables associated with that job. now i am reading the first row. it will check for the job name and tables associated with it in the . now these tables i want to loop through and create a HQL query. the issue with my code is tables are getting stored in a variable as list and when i an executing unset command it's throwing error. can anyone help me where i went wrong. below is the configuration file, code and required output.

job1,tbl1,tbl2,tbl3
job2.tbl4,tbl5,tbl6
job3,tbl7,tbl8,tbl9

Below is the code which i am trying

    #!/bin/bash

while read line; do
    job_name=$(echo "$line"|awk 'BEGIN{FS=","}{print $1}')
    echo "JobName is $job_name"
    tablestring=${line#"$job_name"}
    for table in ${tablestring//,/ }; do
        echo "$table"
    done
hql="select $table as table_name, max(cycle_Date) as AS_OF_dATE FROM DB1.$table "
unset tablestring[0]
for tbl in "${tablestring[@]}"
do
hql+=$'\n'
hql+="union all select $tbl as table_name, max(cycle_Date) as AS_OF_dATE FROM DB1.$tbl"
hql+=";"
echo "$hql" >query.hql
done
done < a.configuration

Below is the required output which i want to achieve in the first iteration for job 1 and successively for job 2 and so on..

  select tbl1 as table_name, max(cycle_Date) as AS_OF_dATE FROM DB1.tbl1
    union all select tbl2 as table_name, max(cycle_Date) as AS_OF_dATE FROM DB1.tbl2
    union all select tbl3 as table_name, max(cycle_Date) as AS_OF_dATE FROM DB1.tbl3;

Solution

So I would use this as the configuration file a.cfg

job[1]="jobname1 tbl1 tbl2 tbl3"
job[2]="jobname2 tbl4 tbl5 tbl6 tbl7"
job[3]="jobname3 tbl8 tbl9"

And this script

#!/bin/bash

TimeStamp=$(date +"%Y%m%d%H%M%S")

#Sourcing the configuration file. Here in the same directory as this script but you can provide the full path
. ./a.cfg

for i in ${!job[@]}; do
    #Creating the array jobtable. The job name is always index 0
    jobtable=( ${job[$i]} )
    #Using a unique name for each file
    filename=${TimeStamp}_${jobtable[0]}.hql
    echo "Processing ${jobtable[0]} using"
    for ((j=1;j<${#jobtable[@]};j++)); do
        echo "    ${jobtable[$j]}"
        #Creating the hql file
        if [[ $j -eq 1 ]]; then
            echo "select ${jobtable[$j]} as table_name, max(cycle_Date) as AS_OF_dATE FROM DB1.${jobtable[$j]} " >> $filename
        elif [[ $j -lt $((${#jobtable[@]}-1)) ]]; then
            echo " union all select ${jobtable[$j]} as table_name, max(cycle_Date) as AS_OF_dATE FROM DB1.${jobtable[$j]}" >> $filename
        else
            echo " union all select ${jobtable[$j]} as table_name, max(cycle_Date) as AS_OF_dATE FROM DB1.${jobtable[$j]};" >> $filename
        fi
    done
    #You can use the newly created hql file here using $filename. I would not delete the file though as it might be usefull to
    #troubleshoot if something is not right.
done

It does produce the hql files like this one

select tbl1 as table_name, max(cycle_Date) as AS_OF_dATE FROM DB1.tbl1
 union all select tbl2 as table_name, max(cycle_Date) as AS_OF_dATE FROM DB1.tbl2
 union all select tbl3 as table_name, max(cycle_Date) as AS_OF_dATE FROM DB1.tbl3;

EDIT : small edit to correct a typo.



Answered By - Andre Gelinas
at