Replace query by reading the file

Shell Programming and Scripting
1

Hi Guys,

I am having below file which holds data like this
file.txt

name,id,flag
apple,1,Y
apple,2,N
mango,1,Y
mango,2,Y

I need to read the above file and frame a query like this

hive -s -e "create apple_view as select 1 from main_table;"
hive -s -e "create mango_view as select 1,2 from main_table;"

i need take the column id where the flag is Y so here its '1' for apple and 1,2 for mango

2 
 
[skrynesaver@busybox]$cat tmp.dat
name,id,flag
apple,1,Y
apple,2,N
mango,1,Y
mango,2,Y
[skrynesaver@busybox]$ perl -ne 'chomp;@r=split/,/; push @{$fields{$r[0]}},$r[1] if $r[2] eq "Y";}{for $fruit (keys %fields){print "hive -s -e \"create ${fruit}_view as select ", join",",@{$fields{$fruit}}," from main table\"\n"}' tmp.dat
hive -s -e "create mango_view as select 1,2, from main table"
hive -s -e "create apple_view as select 1, from main table"
[skrynesaver@busybox]$
1 Like
3

Thanks a lot,

Can this be achived in Shell scripting instead of perl

4

That's a shell command :wink: I can break it into digestible chunks if you wish

perl -ne '               #Call the Perl interpreter and iterate over every line in the files listed as arguments with the following script
chomp;                 # Strip newlines
@r=split/,/;           #Create an array r of each field (separated by ",") in the record
push @{$fields{$r[0]}},$r[1] if $r[2] eq "Y";  # if the falg is "Y" then add the id to an array stored in a hash and keyed on name
}{                                                                # Eskino's nose execute the remainder when you've finished processing the file(s)
for $fruit (keys %fields){                               # for each name field we stored an array for
print "hive -s -e \"create ${fruit}_view as select ", join",",@{$fields{$fruit}}," from main table\"\n"} # Print the required string for the named joining the id's with a ","
' tmp.dat            # for this file
1 Like
5

Thanks a lot, what i was trying to say is instead of using perl interpretor can i use any other unix interpreter and i would like to pass the fruit as parameter

6

Pure shell:

{ read
   while IFS=, read NAME ID FLAG
     do if [ ! "$LAST" = "$NAME" ]
          then  [ "$LAST" ] &&  printf " from main_table;\"\n"
                printf "hive -s -e \"create ${NAME}_view as select "
                SEP=" "
        fi
        [ "$FLAG" = "Y" ] && printf "%s%s" $SEP $ID
        SEP=","
        LAST=$NAME
     done
   printf " from main_table;\"\n"
} < file
hive -s -e "create apple_view as select 1 from main_table;"
hive -s -e "create mango_view as select 1,2 from main_table;"
7

Thanks Rudic,

In your code where you will be passing fruit as parameter , because i wanted to pass that as parameter so that it can extract that from file

8

I didn't see any parameter in your spec in post#1? You extracted ALL fruits from that file...

Howsoever, how would you pass that parameter, and to what? A function? A script? What if the parameter is not found?

9

I will use this code as a function and pass file and fruit as parameter

something like this

create_query()
{
file=$1
fruit=$2
}

if pass apple then
hive -s -e "create apple_view as select 1 from main_table;"
10

And you want to pass one single fruit only to the function, not two or even more? Had you said that from the beginning, much effort could have been avoided!

11

Try

create_query()  { unset FND SEP
                  read
                  while IFS=, read NAME ID FLAG
                    do  if [ "$1" = "$NAME" ]
                          then  if [ -z "$FND" ]
                                  then  printf "hive -s -e \"create ${NAME}_view as select "
                                fi
                                FND="Y"
                                if [ "$FLAG" = "Y" ]
                                  then  printf "%1s%s" $SEP $ID
                                        SEP=","
                                fi
                        fi
                    done
                  if [ "$FND" ]
                    then        printf " from main_table;\"\n"
                                return 0
                  fi
                  return 1
                } < $2
12

I am really sorry for that rudic,

i want to pass only one single fruit to the function and file will remain the same. I mean it would read the same file