Saving all output to "!!{outputDirectory}!!/join_1to1.q.raw". Enter "record" with no arguments to stop it. >>> !run !!{qFileDirectory}!!/join_1to1.q >>> >>> CREATE TABLE join_1to1_1(key1 int, key2 int, value int); No rows affected >>> LOAD DATA LOCAL INPATH '../data/files/in5.txt' INTO TABLE join_1to1_1; No rows affected >>> >>> CREATE TABLE join_1to1_2(key1 int, key2 int, value int); No rows affected >>> LOAD DATA LOCAL INPATH '../data/files/in6.txt' INTO TABLE join_1to1_2; No rows affected >>> >>> >>> set hive.outerjoin.supports.filters=false; No rows affected >>> >>> set hive.join.emit.interval=5; No rows affected >>> >>> SELECT * FROM join_1to1_1 a join join_1to1_2 b on a.key1 = b.key1 ORDER BY a.key1 ASC, a.key2 ASC, a.value ASC, b.key1 ASC, b.key2 ASC, b.value ASC; 'key1','key2','value','key1','key2','value' '5','10005','66','5','10005','66' '20','10020','66','20','10020','66' '25','10025','88','25','10025','66' '30','10030','66','30','10030','88' '35','10035','88','35','10035','88' '40','10040','66','40','10040','66' '40','10040','66','40','10040','88' '40','10040','88','40','10040','66' '40','10040','88','40','10040','88' '50','10050','66','50','10050','66' '50','10050','66','50','10050','66' '50','10050','66','50','10050','88' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','88' '50','10050','88','50','10050','88' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' 30 rows selected >>> SELECT * FROM join_1to1_1 a full outer join join_1to1_2 b on a.key1 = b.key1 ORDER BY a.key1 ASC, a.key2 ASC, a.value ASC, b.key1 ASC, b.key2 ASC, b.value ASC; 'key1','key2','value','key1','key2','value' '','','','','','66' '','','','','10050','66' '','','','10','10010','66' '','','66','','','' '','10050','66','','','' '5','10005','66','5','10005','66' '15','10015','66','','','' '20','10020','66','20','10020','66' '25','10025','88','25','10025','66' '30','10030','66','30','10030','88' '35','10035','88','35','10035','88' '40','10040','66','40','10040','66' '40','10040','66','40','10040','88' '40','10040','88','40','10040','66' '40','10040','88','40','10040','88' '50','10050','66','50','10050','66' '50','10050','66','50','10050','66' '50','10050','66','50','10050','88' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','88' '50','10050','88','50','10050','88' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' 36 rows selected >>> SELECT * FROM join_1to1_1 a full outer join join_1to1_2 b on a.key1 = b.key1 and a.value = 66 and b.value = 66 ORDER BY a.key1 ASC, a.key2 ASC, a.value ASC, b.key1 ASC, b.key2 ASC, b.value ASC; 'key1','key2','value','key1','key2','value' '','','','','','66' '','','','','10050','66' '','','','10','10010','66' '','','','25','10025','66' '','','','80','10040','66' '','','','80','10040','66' '','','66','','','' '','10050','66','','','' '5','10005','66','5','10005','66' '15','10015','66','','','' '20','10020','66','20','10020','66' '30','10030','66','','','' '40','10040','66','40','10040','66' '50','10050','66','50','10050','66' '50','10050','66','50','10050','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '70','10040','66','','','' '70','10040','66','','','' 21 rows selected >>> SELECT * FROM join_1to1_1 a full outer join join_1to1_2 b on a.key1 = b.key1 and a.key2 = b.key2 ORDER BY a.key1 ASC, a.key2 ASC, a.value ASC, b.key1 ASC, b.key2 ASC, b.value ASC; 'key1','key2','value','key1','key2','value' '','','','','','66' '','','','','10050','66' '','','','10','10010','66' '','','66','','','' '','10050','66','','','' '5','10005','66','5','10005','66' '15','10015','66','','','' '20','10020','66','20','10020','66' '25','10025','88','25','10025','66' '30','10030','66','30','10030','88' '35','10035','88','35','10035','88' '40','10040','66','40','10040','66' '40','10040','66','40','10040','88' '40','10040','88','40','10040','66' '40','10040','88','40','10040','88' '50','10050','66','50','10050','66' '50','10050','66','50','10050','66' '50','10050','66','50','10050','88' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','88' '50','10050','88','50','10050','88' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' 36 rows selected >>> SELECT * FROM join_1to1_1 a full outer join join_1to1_2 b on a.key1 = b.key1 and a.key2 = b.key2 and a.value = 66 and b.value = 66 ORDER BY a.key1 ASC, a.key2 ASC, a.value ASC, b.key1 ASC, b.key2 ASC, b.value ASC; 'key1','key2','value','key1','key2','value' '','','','','','66' '','','','','10050','66' '','','','10','10010','66' '','','','25','10025','66' '','','','80','10040','66' '','','','80','10040','66' '','','66','','','' '','10050','66','','','' '5','10005','66','5','10005','66' '15','10015','66','','','' '20','10020','66','20','10020','66' '30','10030','66','','','' '40','10040','66','40','10040','66' '50','10050','66','50','10050','66' '50','10050','66','50','10050','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '70','10040','66','','','' '70','10040','66','','','' 21 rows selected >>> >>> set hive.join.emit.interval=2; No rows affected >>> SELECT * FROM join_1to1_1 a join join_1to1_2 b on a.key1 = b.key1 ORDER BY a.key1 ASC, a.key2 ASC, a.value ASC, b.key1 ASC, b.key2 ASC, b.value ASC; 'key1','key2','value','key1','key2','value' '5','10005','66','5','10005','66' '20','10020','66','20','10020','66' '25','10025','88','25','10025','66' '30','10030','66','30','10030','88' '35','10035','88','35','10035','88' '40','10040','66','40','10040','66' '40','10040','66','40','10040','88' '40','10040','88','40','10040','66' '40','10040','88','40','10040','88' '50','10050','66','50','10050','66' '50','10050','66','50','10050','66' '50','10050','66','50','10050','88' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','88' '50','10050','88','50','10050','88' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' 30 rows selected >>> SELECT * FROM join_1to1_1 a full outer join join_1to1_2 b on a.key1 = b.key1 ORDER BY a.key1 ASC, a.key2 ASC, a.value ASC, b.key1 ASC, b.key2 ASC, b.value ASC; 'key1','key2','value','key1','key2','value' '','','','','','66' '','','','','10050','66' '','','','10','10010','66' '','','66','','','' '','10050','66','','','' '5','10005','66','5','10005','66' '15','10015','66','','','' '20','10020','66','20','10020','66' '25','10025','88','25','10025','66' '30','10030','66','30','10030','88' '35','10035','88','35','10035','88' '40','10040','66','40','10040','66' '40','10040','66','40','10040','88' '40','10040','88','40','10040','66' '40','10040','88','40','10040','88' '50','10050','66','50','10050','66' '50','10050','66','50','10050','66' '50','10050','66','50','10050','88' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','88' '50','10050','88','50','10050','88' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' 36 rows selected >>> SELECT * FROM join_1to1_1 a full outer join join_1to1_2 b on a.key1 = b.key1 and a.value = 66 and b.value = 66 ORDER BY a.key1 ASC, a.key2 ASC, a.value ASC, b.key1 ASC, b.key2 ASC, b.value ASC; 'key1','key2','value','key1','key2','value' '','','','','','66' '','','','','10050','66' '','','','10','10010','66' '','','','25','10025','66' '','','','80','10040','66' '','','','80','10040','66' '','','66','','','' '','10050','66','','','' '5','10005','66','5','10005','66' '15','10015','66','','','' '20','10020','66','20','10020','66' '30','10030','66','','','' '40','10040','66','40','10040','66' '50','10050','66','50','10050','66' '50','10050','66','50','10050','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '70','10040','66','','','' '70','10040','66','','','' 21 rows selected >>> SELECT * FROM join_1to1_1 a full outer join join_1to1_2 b on a.key1 = b.key1 and a.key2 = b.key2 ORDER BY a.key1 ASC, a.key2 ASC, a.value ASC, b.key1 ASC, b.key2 ASC, b.value ASC; 'key1','key2','value','key1','key2','value' '','','','','','66' '','','','','10050','66' '','','','10','10010','66' '','','66','','','' '','10050','66','','','' '5','10005','66','5','10005','66' '15','10015','66','','','' '20','10020','66','20','10020','66' '25','10025','88','25','10025','66' '30','10030','66','30','10030','88' '35','10035','88','35','10035','88' '40','10040','66','40','10040','66' '40','10040','66','40','10040','88' '40','10040','88','40','10040','66' '40','10040','88','40','10040','88' '50','10050','66','50','10050','66' '50','10050','66','50','10050','66' '50','10050','66','50','10050','88' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','88' '50','10050','88','50','10050','88' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' 36 rows selected >>> SELECT * FROM join_1to1_1 a full outer join join_1to1_2 b on a.key1 = b.key1 and a.key2 = b.key2 and a.value = 66 and b.value = 66 ORDER BY a.key1 ASC, a.key2 ASC, a.value ASC, b.key1 ASC, b.key2 ASC, b.value ASC; 'key1','key2','value','key1','key2','value' '','','','','','66' '','','','','10050','66' '','','','10','10010','66' '','','','25','10025','66' '','','','80','10040','66' '','','','80','10040','66' '','','66','','','' '','10050','66','','','' '5','10005','66','5','10005','66' '15','10015','66','','','' '20','10020','66','20','10020','66' '30','10030','66','','','' '40','10040','66','40','10040','66' '50','10050','66','50','10050','66' '50','10050','66','50','10050','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '70','10040','66','','','' '70','10040','66','','','' 21 rows selected >>> >>> set hive.join.emit.interval=1; No rows affected >>> SELECT * FROM join_1to1_1 a join join_1to1_2 b on a.key1 = b.key1 ORDER BY a.key1 ASC, a.key2 ASC, a.value ASC, b.key1 ASC, b.key2 ASC, b.value ASC; 'key1','key2','value','key1','key2','value' '5','10005','66','5','10005','66' '20','10020','66','20','10020','66' '25','10025','88','25','10025','66' '30','10030','66','30','10030','88' '35','10035','88','35','10035','88' '40','10040','66','40','10040','66' '40','10040','66','40','10040','88' '40','10040','88','40','10040','66' '40','10040','88','40','10040','88' '50','10050','66','50','10050','66' '50','10050','66','50','10050','66' '50','10050','66','50','10050','88' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','88' '50','10050','88','50','10050','88' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' 30 rows selected >>> SELECT * FROM join_1to1_1 a full outer join join_1to1_2 b on a.key1 = b.key1 ORDER BY a.key1 ASC, a.key2 ASC, a.value ASC, b.key1 ASC, b.key2 ASC, b.value ASC; 'key1','key2','value','key1','key2','value' '','','','','','66' '','','','','10050','66' '','','','10','10010','66' '','','66','','','' '','10050','66','','','' '5','10005','66','5','10005','66' '15','10015','66','','','' '20','10020','66','20','10020','66' '25','10025','88','25','10025','66' '30','10030','66','30','10030','88' '35','10035','88','35','10035','88' '40','10040','66','40','10040','66' '40','10040','66','40','10040','88' '40','10040','88','40','10040','66' '40','10040','88','40','10040','88' '50','10050','66','50','10050','66' '50','10050','66','50','10050','66' '50','10050','66','50','10050','88' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','88' '50','10050','88','50','10050','88' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' 36 rows selected >>> SELECT * FROM join_1to1_1 a full outer join join_1to1_2 b on a.key1 = b.key1 and a.value = 66 and b.value = 66 ORDER BY a.key1 ASC, a.key2 ASC, a.value ASC, b.key1 ASC, b.key2 ASC, b.value ASC; 'key1','key2','value','key1','key2','value' '','','','','','66' '','','','','10050','66' '','','','10','10010','66' '','','','25','10025','66' '','','','80','10040','66' '','','','80','10040','66' '','','66','','','' '','10050','66','','','' '5','10005','66','5','10005','66' '15','10015','66','','','' '20','10020','66','20','10020','66' '30','10030','66','','','' '40','10040','66','40','10040','66' '50','10050','66','50','10050','66' '50','10050','66','50','10050','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '70','10040','66','','','' '70','10040','66','','','' 21 rows selected >>> SELECT * FROM join_1to1_1 a full outer join join_1to1_2 b on a.key1 = b.key1 and a.key2 = b.key2 ORDER BY a.key1 ASC, a.key2 ASC, a.value ASC, b.key1 ASC, b.key2 ASC, b.value ASC; 'key1','key2','value','key1','key2','value' '','','','','','66' '','','','','10050','66' '','','','10','10010','66' '','','66','','','' '','10050','66','','','' '5','10005','66','5','10005','66' '15','10015','66','','','' '20','10020','66','20','10020','66' '25','10025','88','25','10025','66' '30','10030','66','30','10030','88' '35','10035','88','35','10035','88' '40','10040','66','40','10040','66' '40','10040','66','40','10040','88' '40','10040','88','40','10040','66' '40','10040','88','40','10040','88' '50','10050','66','50','10050','66' '50','10050','66','50','10050','66' '50','10050','66','50','10050','88' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','88' '50','10050','88','50','10050','88' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' 36 rows selected >>> SELECT * FROM join_1to1_1 a full outer join join_1to1_2 b on a.key1 = b.key1 and a.key2 = b.key2 and a.value = 66 and b.value = 66 ORDER BY a.key1 ASC, a.key2 ASC, a.value ASC, b.key1 ASC, b.key2 ASC, b.value ASC; 'key1','key2','value','key1','key2','value' '','','','','','66' '','','','','10050','66' '','','','10','10010','66' '','','','25','10025','66' '','','','80','10040','66' '','','','80','10040','66' '','','66','','','' '','10050','66','','','' '5','10005','66','5','10005','66' '15','10015','66','','','' '20','10020','66','20','10020','66' '30','10030','66','','','' '40','10040','66','40','10040','66' '50','10050','66','50','10050','66' '50','10050','66','50','10050','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '70','10040','66','','','' '70','10040','66','','','' 21 rows selected >>> >>> >>> >>> set hive.outerjoin.supports.filters=true; No rows affected >>> >>> set hive.join.emit.interval=5; No rows affected >>> >>> SELECT * FROM join_1to1_1 a join join_1to1_2 b on a.key1 = b.key1 ORDER BY a.key1 ASC, a.key2 ASC, a.value ASC, b.key1 ASC, b.key2 ASC, b.value ASC; 'key1','key2','value','key1','key2','value' '5','10005','66','5','10005','66' '20','10020','66','20','10020','66' '25','10025','88','25','10025','66' '30','10030','66','30','10030','88' '35','10035','88','35','10035','88' '40','10040','66','40','10040','66' '40','10040','66','40','10040','88' '40','10040','88','40','10040','66' '40','10040','88','40','10040','88' '50','10050','66','50','10050','66' '50','10050','66','50','10050','66' '50','10050','66','50','10050','88' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','88' '50','10050','88','50','10050','88' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' 30 rows selected >>> SELECT * FROM join_1to1_1 a full outer join join_1to1_2 b on a.key1 = b.key1 ORDER BY a.key1 ASC, a.key2 ASC, a.value ASC, b.key1 ASC, b.key2 ASC, b.value ASC; 'key1','key2','value','key1','key2','value' '','','','','','66' '','','','','10050','66' '','','','10','10010','66' '','','66','','','' '','10050','66','','','' '5','10005','66','5','10005','66' '15','10015','66','','','' '20','10020','66','20','10020','66' '25','10025','88','25','10025','66' '30','10030','66','30','10030','88' '35','10035','88','35','10035','88' '40','10040','66','40','10040','66' '40','10040','66','40','10040','88' '40','10040','88','40','10040','66' '40','10040','88','40','10040','88' '50','10050','66','50','10050','66' '50','10050','66','50','10050','66' '50','10050','66','50','10050','88' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','88' '50','10050','88','50','10050','88' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' 36 rows selected >>> SELECT * FROM join_1to1_1 a full outer join join_1to1_2 b on a.key1 = b.key1 and a.value = 66 and b.value = 66 ORDER BY a.key1 ASC, a.key2 ASC, a.value ASC, b.key1 ASC, b.key2 ASC, b.value ASC; 'key1','key2','value','key1','key2','value' '','','','','','66' '','','','','10050','66' '','','','10','10010','66' '','','','30','10030','88' '','','','35','10035','88' '','','','40','10040','88' '','','','40','10040','88' '','','','50','10050','88' '','','','50','10050','88' '','','','50','10050','88' '','','','70','10040','88' '','','','70','10040','88' '','','','70','10040','88' '','','','70','10040','88' '','','66','','','' '','10050','66','','','' '5','10005','66','5','10005','66' '15','10015','66','','','' '20','10020','66','20','10020','66' '25','10025','88','','','' '30','10030','66','','','' '35','10035','88','','','' '40','10040','66','','','' '40','10040','66','40','10040','66' '40','10040','88','','','' '40','10040','88','','','' '50','10050','66','','','' '50','10050','66','50','10050','66' '50','10050','66','50','10050','66' '50','10050','88','','','' '50','10050','88','','','' '50','10050','88','','','' '50','10050','88','','','' '50','10050','88','','','' '50','10050','88','','','' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '70','10040','66','','','' '70','10040','66','','','' '70','10040','66','','','' '70','10040','66','','','' '80','10040','88','','','' '80','10040','88','','','' '80','10040','88','','','' '80','10040','88','','','' 47 rows selected >>> SELECT * FROM join_1to1_1 a full outer join join_1to1_2 b on a.key1 = b.key1 and a.key2 = b.key2 ORDER BY a.key1 ASC, a.key2 ASC, a.value ASC, b.key1 ASC, b.key2 ASC, b.value ASC; 'key1','key2','value','key1','key2','value' '','','','','','66' '','','','','10050','66' '','','','10','10010','66' '','','66','','','' '','10050','66','','','' '5','10005','66','5','10005','66' '15','10015','66','','','' '20','10020','66','20','10020','66' '25','10025','88','25','10025','66' '30','10030','66','30','10030','88' '35','10035','88','35','10035','88' '40','10040','66','40','10040','66' '40','10040','66','40','10040','88' '40','10040','88','40','10040','66' '40','10040','88','40','10040','88' '50','10050','66','50','10050','66' '50','10050','66','50','10050','66' '50','10050','66','50','10050','88' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','88' '50','10050','88','50','10050','88' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' 36 rows selected >>> SELECT * FROM join_1to1_1 a full outer join join_1to1_2 b on a.key1 = b.key1 and a.key2 = b.key2 and a.value = 66 and b.value = 66 ORDER BY a.key1 ASC, a.key2 ASC, a.value ASC, b.key1 ASC, b.key2 ASC, b.value ASC; 'key1','key2','value','key1','key2','value' '','','','','','66' '','','','','10050','66' '','','','10','10010','66' '','','','30','10030','88' '','','','35','10035','88' '','','','40','10040','88' '','','','40','10040','88' '','','','50','10050','88' '','','','50','10050','88' '','','','50','10050','88' '','','','70','10040','88' '','','','70','10040','88' '','','','70','10040','88' '','','','70','10040','88' '','','66','','','' '','10050','66','','','' '5','10005','66','5','10005','66' '15','10015','66','','','' '20','10020','66','20','10020','66' '25','10025','88','','','' '30','10030','66','','','' '35','10035','88','','','' '40','10040','66','','','' '40','10040','66','40','10040','66' '40','10040','88','','','' '40','10040','88','','','' '50','10050','66','','','' '50','10050','66','50','10050','66' '50','10050','66','50','10050','66' '50','10050','88','','','' '50','10050','88','','','' '50','10050','88','','','' '50','10050','88','','','' '50','10050','88','','','' '50','10050','88','','','' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '70','10040','66','','','' '70','10040','66','','','' '70','10040','66','','','' '70','10040','66','','','' '80','10040','88','','','' '80','10040','88','','','' '80','10040','88','','','' '80','10040','88','','','' 47 rows selected >>> >>> set hive.join.emit.interval=2; No rows affected >>> SELECT * FROM join_1to1_1 a join join_1to1_2 b on a.key1 = b.key1 ORDER BY a.key1 ASC, a.key2 ASC, a.value ASC, b.key1 ASC, b.key2 ASC, b.value ASC; 'key1','key2','value','key1','key2','value' '5','10005','66','5','10005','66' '20','10020','66','20','10020','66' '25','10025','88','25','10025','66' '30','10030','66','30','10030','88' '35','10035','88','35','10035','88' '40','10040','66','40','10040','66' '40','10040','66','40','10040','88' '40','10040','88','40','10040','66' '40','10040','88','40','10040','88' '50','10050','66','50','10050','66' '50','10050','66','50','10050','66' '50','10050','66','50','10050','88' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','88' '50','10050','88','50','10050','88' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' 30 rows selected >>> SELECT * FROM join_1to1_1 a full outer join join_1to1_2 b on a.key1 = b.key1 ORDER BY a.key1 ASC, a.key2 ASC, a.value ASC, b.key1 ASC, b.key2 ASC, b.value ASC; 'key1','key2','value','key1','key2','value' '','','','','','66' '','','','','10050','66' '','','','10','10010','66' '','','66','','','' '','10050','66','','','' '5','10005','66','5','10005','66' '15','10015','66','','','' '20','10020','66','20','10020','66' '25','10025','88','25','10025','66' '30','10030','66','30','10030','88' '35','10035','88','35','10035','88' '40','10040','66','40','10040','66' '40','10040','66','40','10040','88' '40','10040','88','40','10040','66' '40','10040','88','40','10040','88' '50','10050','66','50','10050','66' '50','10050','66','50','10050','66' '50','10050','66','50','10050','88' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','88' '50','10050','88','50','10050','88' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' 36 rows selected >>> SELECT * FROM join_1to1_1 a full outer join join_1to1_2 b on a.key1 = b.key1 and a.value = 66 and b.value = 66 ORDER BY a.key1 ASC, a.key2 ASC, a.value ASC, b.key1 ASC, b.key2 ASC, b.value ASC; 'key1','key2','value','key1','key2','value' '','','','','','66' '','','','','10050','66' '','','','10','10010','66' '','','','30','10030','88' '','','','35','10035','88' '','','','40','10040','88' '','','','40','10040','88' '','','','50','10050','88' '','','','50','10050','88' '','','','50','10050','88' '','','','70','10040','88' '','','','70','10040','88' '','','','70','10040','88' '','','','70','10040','88' '','','66','','','' '','10050','66','','','' '5','10005','66','5','10005','66' '15','10015','66','','','' '20','10020','66','20','10020','66' '25','10025','88','','','' '30','10030','66','','','' '35','10035','88','','','' '40','10040','66','','','' '40','10040','66','40','10040','66' '40','10040','88','','','' '40','10040','88','','','' '50','10050','66','','','' '50','10050','66','50','10050','66' '50','10050','66','50','10050','66' '50','10050','88','','','' '50','10050','88','','','' '50','10050','88','','','' '50','10050','88','','','' '50','10050','88','','','' '50','10050','88','','','' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '70','10040','66','','','' '70','10040','66','','','' '70','10040','66','','','' '70','10040','66','','','' '80','10040','88','','','' '80','10040','88','','','' '80','10040','88','','','' '80','10040','88','','','' 47 rows selected >>> SELECT * FROM join_1to1_1 a full outer join join_1to1_2 b on a.key1 = b.key1 and a.key2 = b.key2 ORDER BY a.key1 ASC, a.key2 ASC, a.value ASC, b.key1 ASC, b.key2 ASC, b.value ASC; 'key1','key2','value','key1','key2','value' '','','','','','66' '','','','','10050','66' '','','','10','10010','66' '','','66','','','' '','10050','66','','','' '5','10005','66','5','10005','66' '15','10015','66','','','' '20','10020','66','20','10020','66' '25','10025','88','25','10025','66' '30','10030','66','30','10030','88' '35','10035','88','35','10035','88' '40','10040','66','40','10040','66' '40','10040','66','40','10040','88' '40','10040','88','40','10040','66' '40','10040','88','40','10040','88' '50','10050','66','50','10050','66' '50','10050','66','50','10050','66' '50','10050','66','50','10050','88' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','88' '50','10050','88','50','10050','88' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' 36 rows selected >>> SELECT * FROM join_1to1_1 a full outer join join_1to1_2 b on a.key1 = b.key1 and a.key2 = b.key2 and a.value = 66 and b.value = 66 ORDER BY a.key1 ASC, a.key2 ASC, a.value ASC, b.key1 ASC, b.key2 ASC, b.value ASC; 'key1','key2','value','key1','key2','value' '','','','','','66' '','','','','10050','66' '','','','10','10010','66' '','','','30','10030','88' '','','','35','10035','88' '','','','40','10040','88' '','','','40','10040','88' '','','','50','10050','88' '','','','50','10050','88' '','','','50','10050','88' '','','','70','10040','88' '','','','70','10040','88' '','','','70','10040','88' '','','','70','10040','88' '','','66','','','' '','10050','66','','','' '5','10005','66','5','10005','66' '15','10015','66','','','' '20','10020','66','20','10020','66' '25','10025','88','','','' '30','10030','66','','','' '35','10035','88','','','' '40','10040','66','','','' '40','10040','66','40','10040','66' '40','10040','88','','','' '40','10040','88','','','' '50','10050','66','','','' '50','10050','66','50','10050','66' '50','10050','66','50','10050','66' '50','10050','88','','','' '50','10050','88','','','' '50','10050','88','','','' '50','10050','88','','','' '50','10050','88','','','' '50','10050','88','','','' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '70','10040','66','','','' '70','10040','66','','','' '70','10040','66','','','' '70','10040','66','','','' '80','10040','88','','','' '80','10040','88','','','' '80','10040','88','','','' '80','10040','88','','','' 47 rows selected >>> >>> set hive.join.emit.interval=1; No rows affected >>> SELECT * FROM join_1to1_1 a join join_1to1_2 b on a.key1 = b.key1 ORDER BY a.key1 ASC, a.key2 ASC, a.value ASC, b.key1 ASC, b.key2 ASC, b.value ASC; 'key1','key2','value','key1','key2','value' '5','10005','66','5','10005','66' '20','10020','66','20','10020','66' '25','10025','88','25','10025','66' '30','10030','66','30','10030','88' '35','10035','88','35','10035','88' '40','10040','66','40','10040','66' '40','10040','66','40','10040','88' '40','10040','88','40','10040','66' '40','10040','88','40','10040','88' '50','10050','66','50','10050','66' '50','10050','66','50','10050','66' '50','10050','66','50','10050','88' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','88' '50','10050','88','50','10050','88' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' 30 rows selected >>> SELECT * FROM join_1to1_1 a full outer join join_1to1_2 b on a.key1 = b.key1 ORDER BY a.key1 ASC, a.key2 ASC, a.value ASC, b.key1 ASC, b.key2 ASC, b.value ASC; 'key1','key2','value','key1','key2','value' '','','','','','66' '','','','','10050','66' '','','','10','10010','66' '','','66','','','' '','10050','66','','','' '5','10005','66','5','10005','66' '15','10015','66','','','' '20','10020','66','20','10020','66' '25','10025','88','25','10025','66' '30','10030','66','30','10030','88' '35','10035','88','35','10035','88' '40','10040','66','40','10040','66' '40','10040','66','40','10040','88' '40','10040','88','40','10040','66' '40','10040','88','40','10040','88' '50','10050','66','50','10050','66' '50','10050','66','50','10050','66' '50','10050','66','50','10050','88' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','88' '50','10050','88','50','10050','88' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' 36 rows selected >>> SELECT * FROM join_1to1_1 a full outer join join_1to1_2 b on a.key1 = b.key1 and a.value = 66 and b.value = 66 ORDER BY a.key1 ASC, a.key2 ASC, a.value ASC, b.key1 ASC, b.key2 ASC, b.value ASC; 'key1','key2','value','key1','key2','value' '','','','','','66' '','','','','10050','66' '','','','10','10010','66' '','','','30','10030','88' '','','','35','10035','88' '','','','40','10040','88' '','','','40','10040','88' '','','','50','10050','88' '','','','50','10050','88' '','','','50','10050','88' '','','','70','10040','88' '','','','70','10040','88' '','','','70','10040','88' '','','','70','10040','88' '','','66','','','' '','10050','66','','','' '5','10005','66','5','10005','66' '15','10015','66','','','' '20','10020','66','20','10020','66' '25','10025','88','','','' '30','10030','66','','','' '35','10035','88','','','' '40','10040','66','','','' '40','10040','66','40','10040','66' '40','10040','88','','','' '40','10040','88','','','' '50','10050','66','','','' '50','10050','66','50','10050','66' '50','10050','66','50','10050','66' '50','10050','88','','','' '50','10050','88','','','' '50','10050','88','','','' '50','10050','88','','','' '50','10050','88','','','' '50','10050','88','','','' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '70','10040','66','','','' '70','10040','66','','','' '70','10040','66','','','' '70','10040','66','','','' '80','10040','88','','','' '80','10040','88','','','' '80','10040','88','','','' '80','10040','88','','','' 47 rows selected >>> SELECT * FROM join_1to1_1 a full outer join join_1to1_2 b on a.key1 = b.key1 and a.key2 = b.key2 ORDER BY a.key1 ASC, a.key2 ASC, a.value ASC, b.key1 ASC, b.key2 ASC, b.value ASC; 'key1','key2','value','key1','key2','value' '','','','','','66' '','','','','10050','66' '','','','10','10010','66' '','','66','','','' '','10050','66','','','' '5','10005','66','5','10005','66' '15','10015','66','','','' '20','10020','66','20','10020','66' '25','10025','88','25','10025','66' '30','10030','66','30','10030','88' '35','10035','88','35','10035','88' '40','10040','66','40','10040','66' '40','10040','66','40','10040','88' '40','10040','88','40','10040','66' '40','10040','88','40','10040','88' '50','10050','66','50','10050','66' '50','10050','66','50','10050','66' '50','10050','66','50','10050','88' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','66' '50','10050','88','50','10050','88' '50','10050','88','50','10050','88' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '70','10040','66','70','10040','88' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' '80','10040','88','80','10040','66' 36 rows selected >>> SELECT * FROM join_1to1_1 a full outer join join_1to1_2 b on a.key1 = b.key1 and a.key2 = b.key2 and a.value = 66 and b.value = 66 ORDER BY a.key1 ASC, a.key2 ASC, a.value ASC, b.key1 ASC, b.key2 ASC, b.value ASC; 'key1','key2','value','key1','key2','value' '','','','','','66' '','','','','10050','66' '','','','10','10010','66' '','','','30','10030','88' '','','','35','10035','88' '','','','40','10040','88' '','','','40','10040','88' '','','','50','10050','88' '','','','50','10050','88' '','','','50','10050','88' '','','','70','10040','88' '','','','70','10040','88' '','','','70','10040','88' '','','','70','10040','88' '','','66','','','' '','10050','66','','','' '5','10005','66','5','10005','66' '15','10015','66','','','' '20','10020','66','20','10020','66' '25','10025','88','','','' '30','10030','66','','','' '35','10035','88','','','' '40','10040','66','','','' '40','10040','66','40','10040','66' '40','10040','88','','','' '40','10040','88','','','' '50','10050','66','','','' '50','10050','66','50','10050','66' '50','10050','66','50','10050','66' '50','10050','88','','','' '50','10050','88','','','' '50','10050','88','','','' '50','10050','88','','','' '50','10050','88','','','' '50','10050','88','','','' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '60','10040','66','60','10040','66' '70','10040','66','','','' '70','10040','66','','','' '70','10040','66','','','' '70','10040','66','','','' '80','10040','88','','','' '80','10040','88','','','' '80','10040','88','','','' '80','10040','88','','','' 47 rows selected >>> >>> !record