How can i assign query result to single variable from query and return error if records are other than 0

1
votes

How can i assign query result count(*) value to single variable and return error if records exist

select * from(select count(*) over (partition By productid, column1,column2,column3 order by productid desc) as duplicate_count from table1)tab where duplicate_count> 1;

1
hivehiveqlhue

1 Answers

0
votes

You can use shell:

dups=$(hive -S -e "select nvl(count(*),0) from(select count(*) over (partition By productid, column1,column2,column3) as duplicate_count from table1)tab where duplicate_count> 1")
if [ "$dups" != "0" ]; then
    echo "Failed, duplicates found: $dups"
    #Do something here, log or send messege, etc
    exit 1
fi

In Hive only, without shell you cannot use variable for the same but you can generate exception using assert_true()

select assert_true(nvl(count(*),0)=0) --will raise exception if not true
  from
(select count(*) over (partition By productid, column1,column2,column3) as duplicate_count 
  from table1)tab 
where duplicate_count> 1;