如果记录不是0，我如何将查询结果分配给查询中的单个变量并返回错误

Question

如果记录存在，我如何将查询结果计数(*(值分配给单个变量并返回错误

select*from(select count(*(over(partition By production，column1，column2，column3生产订单(按desc(为重复来自表1(选项卡其中duplicate_count>1.

Answer 1

您可以使用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

仅在Hive中，如果没有shell，则不能使用相同的变量，但可以使用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;