比较两个配置单元表之间的计数 [英] To compare count between two hive table
问题描述
我正在尝试计算两个表之间的比较.由于减号运算符在蜂巢中不起作用,因此不会发生.您能否提供一些简单的方法来进行两个表之间的计数比较.
I am trying to do count comparision between two table . As minus operator does not work in hive , it is not happening. Could you please give some simple way to do count comparision between two tables.
select 'Call Detail - Hive T1 to HDFS Staging - Data Compare',
case when cnt>0 then 'Fail' Else 'Pass' end
from
(select count(*) cnt from (
(select
count(*) from students1 s1)-
(select count(*) from students2 s2)
) as tbl1
) as tbl2;
抛出错误:
失败:ParseException行81:0无法识别源中'(''(''select'附近的输入
FAILED: ParseException line 81:0 cannot recognize input near '(' '(' 'select' in from source
推荐答案
如果没有按列分组,请使用cross join
.在这种情况下,它将产生包含两个计数的一行:
Use cross join
if you have no group by columns. In this case it will produce one row with both counts:
select s.cnt-s1.cnt diff, case when abs(s.cnt-s1.cnt) > 0 then 'Fail' Else 'Pass' end result
from
(select count(*) cnt from students1 s1) s
cross join
(select count(*) cnt from students2 s2) s1
如果要添加一些按列分组以比较更详细的谷物,请在按列分组上使用FULL JOIN
:
If you will add some group by columns to compare on more detailed grain, then use FULL JOIN
on group by columns:
select s.col1 s_col1, s1.col1 s1_col1, s.cnt-s1.cnt diff, case when abs(s.cnt-s1.cnt) > 0 then 'Fail' Else 'Pass' end result
from
(select count(*) cnt, col1 from students1 s1 group by col1) s
full join
(select count(*) cnt, col1 from students2 s2 group by col1) s1
on s.col1 = s1.col1
此查询将返回已计算差异的联接行,并且两个表中都不联接行.
This query will return joined rows with difference calculated and also not joined rows from both tables.
这篇关于比较两个配置单元表之间的计数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!