· 7 years ago · Sep 17, 2018, 09:32 AM
1Joining two Tables in Hive using HiveQL(Hadoop) [closed]
2CREATE EXTERNAL TABLE IF NOT EXISTS TestingTable1 (This is the MAIN table through which comparisons need to be made)
3(
4BUYER_ID BIGINT,
5ITEM_ID BIGINT,
6CREATED_TIME STRING
7)
8
9**BUYER_ID** | **ITEM_ID** | **CREATED_TIME**
10--------------+------------------+-------------------------
11 1015826235 220003038067 *2001-11-03 19:40:21*
12 1015826235 300003861266 2001-11-08 18:19:59
13 1015826235 140002997245 2003-08-22 09:23:17
14 1015826235 *210002448035* 2001-11-11 22:21:11
15
16CREATE EXTERNAL TABLE IF NOT EXISTS TestingTable2
17(
18USER_ID BIGINT,
19PURCHASED_ITEM ARRAY<STRUCT<PRODUCT_ID: BIGINT,TIMESTAMPS:STRING>>
20)
21
22**USER_ID** **PURCHASED_ITEM**
231015826235 [{"product_id":220003038067,"timestamps":"1004941621"}, {"product_id":300003861266,"timestamps":"1005268799"}, {"product_id":140002997245,"timestamps":"1061569397"},{"product_id":200002448035,"timestamps":"1005542471"}]
24
25**BUYER_ID** | **ITEM_ID** | **CREATED_TIME** | **PRODUCT_ID** | **TIMESTAMPS**
26--------------+------------------+--------------------------------+------------------------+----------------------
271015826235 *210002448035* 2001-11-11 22:21:11 200002448035 1005542471
281015826235 220003038067 *2001-11-03 19:40:21* 220003038067 1004941621
29
30select * from
31 (select * from
32 (select user_id, prod_and_ts.product_id as product_id, prod_and_ts.timestamps as timestamps
33 from testingtable2 LATERAL VIEW
34 explode(purchased_item) exploded_table as prod_and_ts)
35 prod_and_ts
36 LEFT OUTER JOIN testingtable1
37 ON ( prod_and_ts.user_id = testingtable1.buyer_id AND testingtable1.item_id = prod_and_ts.product_id
38 AND prod_and_ts.timestamps = UNIX_TIMESTAMP (testingtable1.created_time)
39 )
40 where testingtable1.buyer_id IS NULL)
41 set_a LEFT OUTER JOIN testingtable1
42 ON (set_a.user_id = testingtable1.buyer_id AND
43 ( set_a.product_id = testingtable1.item_id OR set_a.timestamps = UNIX_TIMESTAMP(testingtable1.created_time) )
44 );
45
46select * from (select t2.buyer_id, t2.item_id, t2.created_time as created_time, subq.user_id, subq.product_id, subq.timestamps as timestamps
47from
48(select user_id, prod_and_ts.product_id as product_id, prod_and_ts.timestamps as timestamps from testingtable2 lateral view explode(purchased_item) exploded_table as prod_and_ts) subq JOIN testingtable1 t2 on t2.buyer_id = subq.user_id
49AND subq.timestamps = unix_timestamp(t2.created_time)
50WHERE (subq.product_id <> t2.item_id)
51union all
52select t2.buyer_id, t2.item_id as item_id, t2.created_time, subq.user_id, subq.product_id as product_id, subq.timestamps
53from
54(select user_id, prod_and_ts.product_id as product_id, prod_and_ts.timestamps as timestamps from testingtable2 lateral view explode(purchased_item) exploded_table as prod_and_ts) subq JOIN testingtable1 t2 on t2.buyer_id = subq.user_id
55 and subq.product_id = t2.item_id
56 WHERE (subq.timestamps <> unix_timestamp(t2.created_time))) unionall;
57
58SELECT *
59FROM SO_Table1HIVE A
60FULL OUTER JOIN SO_Table2HIVE B ON A.BUYER_ID = B.[USER_ID] AND (B.t1time = A.Created_TIME OR B.PRODUCTID = A.ITEM_ID)
61
621015826235 420003038067 2011-11-03 19:40:21.000
631015826235 720003038067 2004-11-03 19:40:21.000
64
651015826235 {"product_id":520003038067,"timestamps":"10...
661015826235 {"product_id":620003038067,"timestamps":"10...
67
681015826235 420003038067 2011-11-03 19:40:21.000 1015826235 520003038067
691015826235 420003038067 2011-11-03 19:40:21.000 1015826235 620003038067
701015826235 720003038067 2004-11-03 19:40:21.000 1015826235 520003038067
711015826235 720003038067 2004-11-03 19:40:21.000 1015826235 620003038067
72
73BUYER_ID ITEM_ID CREATED_TIME USER_ID PRODUCTID timestamps
74----------------------------------------------------------------------
75NULL NULL NULL 1015826235 520003038067 2009-11-11 22:21:11.000
76NULL NULL NULL 1015826235 620003038067 2008-11-11 22:21:11.000
771015826235 420003038067 2011-11-03 19:40:21.000 NULL NULL NULL
781015826235 720003038067 2004-11-03 19:40:21.000 NULL NULL NULL
79
80SELECT *
81FROM (
82 SELECT BUYER_ID,ITEM_ID,CREATED_TIME,PRODUCT_ID,TIMESTAMPS
83 FROM testingtable2 LATERAL VIEW
84 explode(purchased_item) exploded_table as prod_and_ts)
85 prod_and_ts
86 INNER JOIN table2 A ON A.BUYER_ID = prod_and_ts.[USER_ID] AND prod_and_ts.timestamps = UNIX_TIMESTAMP (table2.created_time)
87 WHERE prod_and_ts.product_id <> A.ITEM_ID
88 UNION ALL
89 SELECT BUYER_ID,ITEM_ID,CREATED_TIME,PRODUCT_ID,TIMESTAMPS
90 FROM testingtable2 LATERAL VIEW
91 explode(purchased_item) exploded_table as prod_and_ts)
92 prod_and_ts
93 INNER JOIN table2 A ON A.BUYER_ID = prod_and_ts.[USER_ID] AND prod_and_ts.product_id = A.ITEM_ID
94 WHERE prod_and_ts.timestamps <> UNIX_TIMESTAMP (table2.created_time)
95) X
96
97SELECT *
98FROM(
99 SELECT *
100 FROM SO_Table1HIVE A
101 INNER JOIN SO_Table2HIVE B ON A.BUYER_ID = B.[USER_ID] AND B.t1time = A.Created_TIME
102 WHERE B.PRODUCTID <> A.ITEM_ID
103 UNION ALL
104 SELECT *
105 FROM SO_Table1HIVE A
106 INNER JOIN SO_Table2HIVE B ON A.BUYER_ID = B.[USER_ID] AND B.PRODUCTID = A.ITEM_ID
107 WHERE B.t1time <> A.Created_TIME
108 ) X
109
110SELECT *
111FROM SO_Table1HIVE A
112FULL OUTER JOIN SO_Table2HIVE B ON A.BUYER_ID = B.[USER_ID] AND (B.t1time = A.Created_TIME OR B.PRODUCTID = A.ITEM_ID)
113
114SELECT *
115FROM SO_Table1HIVE A
116RIGHT JOIN SO_Table2HIVE B ON A.BUYER_ID = B.[USER_ID] AND (B.t1time = A.Created_TIME OR B.PRODUCTID = A.ITEM_ID)
117WHERE A.BUYER_ID IS NULL
118UNION ALL
119SELECT *
120FROM SO_Table1HIVE A
121LEFT JOIN SO_Table2HIVE B ON A.BUYER_ID = B.[USER_ID] AND (B.t1time = A.Created_TIME OR B.PRODUCTID = A.ITEM_ID)
122WHERE B.[USER_ID] IS NULL
123
124SELECT *
125FROM SO_Table1HIVE A
126JOIN SO_Table2HIVE B ON A.BUYER_ID = B.[USER_ID]
127WHERE B.t1time NOT IN(SELECT Created_TIME FROM SO_Table1HIVE)
128AND A.Created_TIME NOT IN(SELECT t1time FROM SO_Table2HIVE)
129AND B.PRODUCTID NOT IN(SELECT ITEM_ID FROM SO_Table1HIVE)
130AND A.ITEM_ID NOT IN(SELECT PRODUCTID FROM SO_Table2HIVE)
131
132SELECT
133 user_id,
134 prod_and_ts.product_id as product_id,
135 prod_and_ts.timestamps as timestamps
136 FROM
137 TestingTable2
138 LATERAL VIEW explode(purchased_item) exploded_table as prod_and_ts
139
140FROM (
141 FROM (
142 SELECT
143 buyer_id,
144 item_id,
145 created_time,
146 id
147 FROM (
148 SELECT
149 buyer_id,
150 item_id,
151 created_time,
152 't1' as id
153 FROM
154 TestingTable1 t1
155 UNION ALL
156 SELECT
157 user_id as buyer_id,
158 prod_and_ts.product_id as item_id,
159 prod_and_ts.timestamps as created_time,
160 't2' as id
161 FROM
162 TestingTable2
163 LATERAL VIEW explode(purchased_item) exploded_table as prod_and_ts
164 )t
165 )x
166 MAP
167 buyer_id,
168 item_id,
169 created_time,
170 id
171 USING '/bin/cat'
172 AS
173 buyer_id,
174 item_id,
175 create_time,
176 id
177 CLUSTER BY
178 buyer_id
179 ) map_output
180 REDUCE
181 buyer_id,
182 item_id,
183 create_time,
184 id
185 USING 'my_custom_reducer'
186 AS
187 buyer_id,
188 item_id,
189 create_time,
190 product_id,
191 timestamps;