java – 查询两个相关表(Joins)

CREATE EXTERNAL TABLE IF NOT EXISTS Table1   (This is the MAIN table through which comparisons need to be made)
(
ITEM_ID BIGINT,CREATED_TIME STRING,BUYER_ID BIGINT
)

这是上面第一个表中的数据

**ITEM_ID**   **CREATED_TIME**    **BUYER_ID**
220003038067     2012-06-21        1015826235
300003861266     2012-06-21        1015826235
140002997245     2012-06-14        1015826235
200002448035     2012-06-08        1015826235
260003553381     2012-06-07        1015826235

这是Hive中的第二个表 – 它还包含有关我们正在购买的项目的信息.

CREATE EXTERNAL TABLE IF NOT EXISTS Table2
(
USER_ID BIGINT,PURCHASED_ITEM ARRAY<STRUCT<PRODUCT_ID: BIGINT,TIMESTAMPS:STRING>>
)

这是上表中的数据 –

**USER_ID**    **PURCHASED_ITEM**
1015826235     [{"product_id":220003038067,"timestamps":"1340321132000"},{"product_id":300003861266,"timestamps":"1340271857000"},{"product_id":140002997245,"timestamps":"1339694926000"},{"product_id":200002448035,"timestamps":"1339172659000"},{"product_id":260003553381,"timestamps":"1339072514000"}]

我将数据减少到只有一个BUYER_ID(USER_ID),使问题简单易懂.

问题陈述-

我需要将Table2与Table1进行比较,这意味着我需要看看Table2的USER_ID和Table1的BUYER_ID(因为它们都是一样的)得到匹配,然后在Table2中看到是PURCHASED_ITEM,它是PRODUCT_ID的数组(与ITEM_ID相同),TIMESAMPS(与CREATED_TIME相同)应该与表1中针对该特定USER_ID(BUYER_ID)的ITEM_ID和CREATED_TIME相同,并且有时它们(表示PURCHASED_ITEM和ITEM_ID,CREATED_TIME)可能不相同,或者表2中缺少某些PRODUCT_ID和TIMESTAMPS经表1比较.

这意味着表2中PRODUCT_ID和TIMESTAMPS的计数应与表1中针对特定BUYER_ID(USER_ID)的ITEM_ID和CREATED_TIME计数相同,内容应相同.如果它们不相同或从表2中缺少条目,则需要打印结果,表2中缺少此特定的ITEM_ID和CREATED_TIME,或者与表1进行比较后,PRODUCT_ID和TIMESTAMPS不相同.

所以例如在表1当前为这个BUYER_ID 1015826235我有5 ITEM_ID和5 CREATED_TIME,所以在表2中,我应该有一个行的同一USER_ID(BUYER_ID)的5个PRODUCT_ID和5个TIMESTAMPS与Table1完全相同.如果它不相同或输入缺失,那么我需要打印显示这是丢失的结果,或者这个数据是错误的.

所以只是为了让它更清楚 –

PURCHASED_ITEM是一个表2中的Struct数组,它包含两个东西PRODUCT_ID和TIMESTAMPS.

如果USER_ID和BUYER_ID匹配,则表2中的PRODUCT_ID应与表1中的ITEM_ID匹配,表2中的TIMESTAMPS应与表1中的CREATED_TIME匹配.

更新

HiveQL SQL查询问题： –

Q 1) Find all USER_ID from Table2 whose PRODUCT_ID or TIMESTAMP are not same with
ITEM_ID or CREATED_TIME after comparing with Table1 on BUYER_ID.

查询我写的第一个问题.查询是否正确？

A 1) select Table2.user_id from Table2 where Table1.user_id = Table2.buyer_id
and (Table1.item_id <> Table2.product_id or UNIX_TIMESTAMP(Table1.created_time) <>
Table2.timestamps)


Q 2) Find the `BUYER_ID(USER_ID)` and as well as those `ITEM_ID` and `CREATED_TIME`
which are missing from `Table2` after comparing from `Table1` on `BUYER_ID`.

A 2) Not sure.