HiveQL (Hadoop)을 사용하여 Hive에서 두 개의 테이블 조인 [duplicate]

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

그리고 위의 첫 번째 표에있는 데이터입니다.

**BUYER_ID**  |  **ITEM_ID**     |      **CREATED_TIME**   
--------------+------------------+-------------------------
 1015826235      220003038067       *2001-11-03 19:40:21*
 1015826235      300003861266        2001-11-08 18:19:59
 1015826235      140002997245        2003-08-22 09:23:17
 1015826235     *210002448035*       2001-11-11 22:21:11

하이브의 두 번째 테이블입니다. 우리가 구매하는 품목에 대한 정보도 포함되어 있습니다.

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

그리고 위의 두 번째 테이블 (TestingTable2)의 데이터입니다.

**USER_ID**    **PURCHASED_ITEM**
1015826235     [{"product_id":220003038067,"timestamps":"1004941621"},    {"product_id":300003861266,"timestamps":"1005268799"},    {"product_id":140002997245,"timestamps":"1061569397"},{"product_id":200002448035,"timestamps":"1005542471"}]

아래 시나리오가 충족되도록 TestingTable2를 TestingTable1과 비교하십시오.

TestingTable1에서 비교 한 후 TestingTable1 CORRESPONDING TO BUYER_ID (USER_ID)에서 ITEM_ID 및 CREATED_TIME과 (와) 일치하지 않는 TestingTable2에서 PRODUCT_ID 및 TIMESTAMPS을 (를) 찾습니다.

따라서 TestingTable2 데이터를 보면이 TestingTable1의 ITEM_ID 210002448035가 TestingTable2 PRODUCT_ID-200002448035 데이터와 일치하지 않으며 타임 스탬프와 유사합니다. 그래서 HiveQL 쿼리를 사용하여 아래 결과를 보여주고 싶습니다.

**BUYER_ID**  |  **ITEM_ID**     |      **CREATED_TIME**          |  **PRODUCT_ID**    |     **TIMESTAMPS** 
--------------+------------------+--------------------------------+------------------------+----------------------
1015826235          *210002448035*           2001-11-11 22:21:11            200002448035           1005542471 
1015826235       220003038067           *2001-11-03 19:40:21*           220003038067          1004941621

누구든지 이걸 도와 줄 수 있어요. 내가 HiveQL을 처음 접했을 때 많은 문제가 발생했습니다.

업데이트 : -

이 쿼리를 작성했지만 원하는 방식으로 작동하지 않습니다.

select * from 
  (select * from 
     (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)
      prod_and_ts 
      LEFT OUTER JOIN testingtable1 
  ON ( prod_and_ts.user_id = testingtable1.buyer_id AND testingtable1.item_id =    prod_and_ts.product_id
     AND prod_and_ts.timestamps = UNIX_TIMESTAMP (testingtable1.created_time)
  )
  where testingtable1.buyer_id IS NULL) 
  set_a LEFT OUTER JOIN testingtable1 
  ON (set_a.user_id = testingtable1.buyer_id AND  
  ( set_a.product_id = testingtable1.item_id OR set_a.timestamps = UNIX_TIMESTAMP(testingtable1.created_time) )
 );

하나 더 업데이트

user1166147 의견에 따라. 나는 그의 쿼리에 따라 나의 쿼리를 썼다. 하이브에서 INNER JOIN은 단순히 JOIN으로 작성되었습니다.

이것은 내 아래 쿼리입니다.

select * from (select t2.buyer_id, t2.item_id, t2.created_time as created_time, subq.user_id, subq.product_id, subq.timestamps as timestamps 
from
(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 
AND subq.timestamps = unix_timestamp(t2.created_time)
WHERE (subq.product_id <> t2.item_id)
union all
select t2.buyer_id, t2.item_id as item_id, t2.created_time, subq.user_id, subq.product_id as product_id, subq.timestamps
from 
(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 
    and subq.product_id = t2.item_id 
    WHERE (subq.timestamps <> unix_timestamp(t2.created_time))) unionall;

그리고 위의 쿼리를 실행 한 후에 나는 제로 결과를 다시 얻고 있습니다.

최종 업데이트 : -

나의 나쁜, 나는 테이블에 정확한 데이터를 가지고 있지 않았다. 그래서 나는 결과를 다시 얻지 못했다. 예, 위의 실제 쿼리가 작동합니다.

해결법

==============================
1.편집 - 파트 1 오케이 - 어떤 이유로 나는 자신을 설명하려고합니다. 그래서 SQL 태그 때문에이 질문에 비틀 거리며 하이브를 보았고, 보지 않고 건너 뛰기 시작했습니다. 하지만 하루가 지나서야 당신은 아무런 대답도 얻지 못했음을 알았습니다. 나는 보았습니다 - 원래의 쿼리에서 SQL 논리 수정이 필요하다는 것을 알았고 도움이 될 것이라는 것을 알았 기 때문에 아무도 대답하지 않았기 때문에 게시했습니다. 나는이 마지막 질문을 다루려고 노력할 것입니다.하지만 그 후에는 나에게 충고를 지키고 있습니다. 나쁜 조언을 줄 수도 있습니다. 행운을 빕니다! 나는 노력했다! 이제 답을 얻는 것 같아요. 그래서 ...

편집 - 파트 1 오케이 - 어떤 이유로 나는 자신을 설명하려고합니다. 그래서 SQL 태그 때문에이 질문에 비틀 거리며 하이브를 보았고, 보지 않고 건너 뛰기 시작했습니다. 하지만 하루가 지나서야 당신은 아무런 대답도 얻지 못했음을 알았습니다. 나는 보았습니다 - 원래의 쿼리에서 SQL 논리 수정이 필요하다는 것을 알았고 도움이 될 것이라는 것을 알았 기 때문에 아무도 대답하지 않았기 때문에 게시했습니다. 나는이 마지막 질문을 다루려고 노력할 것입니다.하지만 그 후에는 나에게 충고를 지키고 있습니다. 나쁜 조언을 줄 수도 있습니다. 행운을 빕니다! 나는 노력했다! 이제 답을 얻는 것 같아요. 그래서 ...

TSQL에서 아래의 단일 쿼리로이 전체 문제를 해결할 수 있습니다.
```
SELECT * 
FROM SO_Table1HIVE A
FULL OUTER JOIN SO_Table2HIVE B ON A.BUYER_ID = B.[USER_ID] AND (B.t1time = A.Created_TIME OR B.PRODUCTID = A.ITEM_ID)
```
일치하는 buyer_id / user_id를 포함하여 모든 것을 반환합니다. 다른 테이블의 제품이나 시간에 일치하지 않는 buyer_id / user_id 행은 일치하지 않지만 다른 테이블의 필드에는 NULLS가있는 별도의 행으로 반환됩니다. 나는 이것들을 어떤 식 으로든 매치시키지 않을 것입니다 - 아래에 설명 된대로 정확한 정보가 제공되지 않습니다.

END EDIT PART 1

Hive에서 OR로 전체 외부 조인을 수행 할 수없는 경우 원래 조건을 충족시키는 가장 간단한 방법은 UNION ALL 2 내부 조인입니다. 쿼리 중 하나에서 일치하는 user_ids에 참여하는 것 외에도 PRODUCT_ID에 가입하고 WHITE에서 CREATED_TIME과 (과) 일치하지 않는 TIMESTAMPS를 찾습니다. 두 번째 쿼리에서 일치하는 user_ids에 가입하는 것 외에도 시간에 가입하고 일치하지 않는 제품을 WHERE에서 찾습니다.

편집 부분 2 - 코멘트 질문에 대한 업데이트 추가 기준

마지막 기준을 이해하면 어느 테이블에서나 일치하는 user_id = buyer_id가 있지만 일치하는 레코드가 없습니다. OR 조건이있는 FULL OUTER JOIN이 레코드를 반환하지만 레코드를 서로 관련시키는 데 필요한 정보가 충분하지 않습니다. 우리는 그것들을 쉽게 식별 할 수 있지만 그들을 서로 묶을 방법이 없습니다. 그렇게하거나 두 테이블 모두에서 일치하지 않는 레코드가 두 개 이상인 경우 각각에 대해 여러 항목이 있습니다.

더 많은 정보없이 (그리고 아마 함께) 그들을 묶으려고 노력한 모든 쿼리는 추측과 부정확 할 것입니다.

예를 들어 첫 번째 표에서 user_id를 제외하고 두 번째 일치하는 항목이없는 2 (가짜 샘플) 레코드가있는 경우 :
```
1015826235  420003038067    2011-11-03 19:40:21.000
1015826235  720003038067    2004-11-03 19:40:21.000
```
그리고 table2에서 -이 일치하지 않는 :
```
1015826235  {"product_id":520003038067,"timestamps":"10...
1015826235  {"product_id":620003038067,"timestamps":"10...
```
당신은 그것들을 식별 할 수 있지만, 당신이 더 많은 기준없이 그들을 일치 시키면 당신은 2 대신에 4를 얻습니다.
```
1015826235  420003038067    2011-11-03 19:40:21.000 1015826235 520003038067
1015826235  420003038067    2011-11-03 19:40:21.000 1015826235 620003038067
1015826235  720003038067    2004-11-03 19:40:21.000 1015826235 520003038067
1015826235  720003038067    2004-11-03 19:40:21.000 1015826235 620003038067
```
내 제안은 단순히 아래에있는 것처럼 그들을 식별하고 보여줄 것입니다.
```
BUYER_ID        ITEM_ID      CREATED_TIME           USER_ID PRODUCTID   timestamps  
----------------------------------------------------------------------
NULL            NULL         NULL                   1015826235  520003038067    2009-11-11 22:21:11.000
NULL            NULL         NULL                   1015826235  620003038067    2008-11-11 22:21:11.000
1015826235      420003038067 2011-11-03 19:40:21.000    NULL    NULL    NULL    
1015826235      720003038067 2004-11-03 19:40:21.000    NULL    NULL    NULL    
```
수정 끝 부분 2 - 수정 질문에 대한 업데이트 추가 기준 - 1 부

TSQL로 작업 중이므로 구문에 대한 정확한 쿼리는 테스트 할 수 없지만 조인의 개념은 동일하므로 원하는 결과가 반환됩니다. 나는 당신의 질문을 받아 구문을 시도하고 필요에 따라 수정했다. 나는 TSQL에서 테스트했다. HiveQL에서이 기능을 사용하고 기능을 향상시킬 수 있습니다. 이를 수행하는 다른 방법이 있습니다. 그러나 이것이 가장 간단하며 HiveQL로 변환됩니다.

제거하면이 부분이 나옵니다. 나중에 포함됩니다.

(필요에 따라 다시 구문을 수정하십시오.) **
```
SELECT *
FROM (
    SELECT BUYER_ID,ITEM_ID,CREATED_TIME,PRODUCT_ID,TIMESTAMPS 
    FROM testingtable2 LATERAL VIEW
        explode(purchased_item) exploded_table as prod_and_ts)
        prod_and_ts 
    INNER JOIN table2 A  ON A.BUYER_ID = prod_and_ts.[USER_ID] AND prod_and_ts.timestamps = UNIX_TIMESTAMP (table2.created_time) 
    WHERE prod_and_ts.product_id <> A.ITEM_ID
    UNION ALL
    SELECT BUYER_ID,ITEM_ID,CREATED_TIME,PRODUCT_ID,TIMESTAMPS 
    FROM testingtable2 LATERAL VIEW
            explode(purchased_item) exploded_table as prod_and_ts)
            prod_and_ts 
    INNER JOIN table2 A  ON A.BUYER_ID = prod_and_ts.[USER_ID] AND prod_and_ts.product_id = A.ITEM_ID
    WHERE  prod_and_ts.timestamps <> UNIX_TIMESTAMP (table2.created_time) 
) X
```
그리고 참조 용 내 테이블 이름으로 테스트 한 TSQL 버전이 있습니다.
```
SELECT * 
FROM(
    SELECT *
    FROM SO_Table1HIVE A
    INNER JOIN SO_Table2HIVE B ON A.BUYER_ID = B.[USER_ID] AND B.t1time = A.Created_TIME 
    WHERE B.PRODUCTID <> A.ITEM_ID
    UNION ALL
    SELECT * 
    FROM SO_Table1HIVE A
    INNER JOIN SO_Table2HIVE B ON A.BUYER_ID = B.[USER_ID] AND B.PRODUCTID = A.ITEM_ID  
    WHERE B.t1time <> A.Created_TIME  
 ) X
```
* 편집 부분 3 - 코멘트 질문에 대한 업데이트 추가 기준 - 2 부

TSQL에서 전체 쿼리 (유니온 없음)는 조인에 OR 조건이있는 FULL OUTER JOIN을 사용하여 실행할 수 있습니다
```
SELECT * 
FROM SO_Table1HIVE A
FULL OUTER JOIN SO_Table2HIVE B ON A.BUYER_ID = B.[USER_ID] AND (B.t1time = A.Created_TIME OR B.PRODUCTID = A.ITEM_ID)
```
단순히 위의 작업을 수행 할 수 없다면 새 조건에 대한 SQL 논리에서 두 테이블에서 일치하지 않는 테이블을 가져 와서 다른 테이블에 NULL로 표시하려면 RIGHT JOIN 및 LEFT JOIN을 사용하십시오. RIGHT JOIN은 첫 번째 테이블에서 두 번째 테이블과 일치하는 테이블을 가져오고 두 번째 테이블에서는 LEFT를 반대로 수행합니다. 새 쿼리를 UNION에 추가하십시오.

TSQL 예제 - 절전 모드 수정
```
SELECT * 
FROM SO_Table1HIVE A
RIGHT JOIN SO_Table2HIVE B ON A.BUYER_ID = B.[USER_ID] AND (B.t1time = A.Created_TIME    OR B.PRODUCTID = A.ITEM_ID)
WHERE A.BUYER_ID IS NULL 
UNION ALL
SELECT * 
FROM SO_Table1HIVE A
LEFT JOIN SO_Table2HIVE B ON A.BUYER_ID = B.[USER_ID] AND (B.t1time = A.Created_TIME OR    B.PRODUCTID = A.ITEM_ID)
WHERE B.[USER_ID] IS NULL
```
또는, 그들을 잡아서 중복으로 일치 시키려면 UNION에 추가하십시오 :

모두
```
SELECT * 
FROM SO_Table1HIVE A
JOIN SO_Table2HIVE B ON A.BUYER_ID = B.[USER_ID] 
WHERE B.t1time NOT IN(SELECT Created_TIME FROM SO_Table1HIVE)
AND A.Created_TIME  NOT IN(SELECT t1time FROM SO_Table2HIVE) 
AND B.PRODUCTID NOT IN(SELECT ITEM_ID FROM SO_Table1HIVE)
AND A.ITEM_ID NOT IN(SELECT PRODUCTID FROM SO_Table2HIVE) 
```
다시, 행운을 빌어 요!
==============================
2.아마도 Hive 변환 기능을 사용해야하고 두 테이블의 레코드 간 일치를 수행하는 사용자 지정 감속기가 있어야합니다. t1과 t2는 t1이 단순히 TestingTable1이고 t2가

아마도 Hive 변환 기능을 사용해야하고 두 테이블의 레코드 간 일치를 수행하는 사용자 지정 감속기가 있어야합니다. t1과 t2는 t1이 단순히 TestingTable1이고 t2가
```
   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
```
너의 또 다른 질문에 나를 설명해 주었다.
```
FROM (
   FROM (
      SELECT
         buyer_id,
         item_id,
         created_time,
         id 
      FROM (
         SELECT
            buyer_id,
            item_id,
            created_time,
            't1' as id
         FROM
            TestingTable1 t1
         UNION ALL
         SELECT
            user_id as buyer_id,
            prod_and_ts.product_id as item_id,
            prod_and_ts.timestamps as created_time,
            't2' as id
         FROM 
            TestingTable2
            LATERAL VIEW explode(purchased_item) exploded_table as prod_and_ts
         )t
      )x
      MAP
         buyer_id,
         item_id,
         created_time,
         id
      USING '/bin/cat'
      AS
         buyer_id,
         item_id,
         create_time,
         id
      CLUSTER BY
         buyer_id
      ) map_output
   REDUCE 
      buyer_id,
      item_id,
      create_time,
      id
   USING 'my_custom_reducer'
   AS
      buyer_id,
      item_id,
      create_time,
      product_id,
      timestamps;
```
위 쿼리는 2 개의 별개 부분을 가지고 있습니다. 첫 번째 부분은 "MAP"이고 다른 부분은 "REDUCE"입니다. 이 두 부분 사이에는 자동으로 하이브를 돌보는 단계 인 shuffle (CLUSTER BY buyer_id로 표시)이 있습니다. 쿼리의 Map 부분은 테이블을 읽고 식별자 (레코드가 오는 테이블을 나타내는 ID)도 전달합니다. Shuffle 단계는 buyer_id 당 모든 레코드를 그룹화합니다. Reduce 단계에서는 주어진 buyer_id에 대한 모든 레코드를 가져와 일치 조건을 만족하는 레코드 만 출력합니다. 일치 기준에 따라 직접 감속기를 작성해야합니다. 원하는 언어로 작성할 수 있습니다. 동일한 buyer_id를 가진 모든 레코드가 동일한 감속기 스크립트로 이동한다는 것은 보장됩니다.

더 쉬운 방법이 있을지 모르지만 이것은 내가 지금 생각할 수있는 방법입니다. 행운을 빕니다! 왜 내가이 방법을 선택했는지에 대한 더 깊은 이해를 얻으려면, 최근의 나의 대답을보십시오.

from https://stackoverflow.com/questions/11336950/joining-two-tables-in-hive-using-hiveqlhadoop by cc-by-sa and MIT license

'HADOOP' 카테고리의 다른 글

[HADOOP] 원인 : ERROR XSDB6 : Derby의 다른 인스턴스가 이미 데이터베이스를 부팅했을 수 있습니다. (0)	2019.06.02
[HADOOP] thread "main"의 예외 java.lang.NoClassDefFoundError : org / apache / hadoop / hbase / HBaseConfiguration (0)	2019.06.01
[HADOOP] 네임 노드 파일 수량 제한 (0)	2019.06.01
[HADOOP] 작은 파일 및 HDFS 블록 (0)	2019.06.01
[HADOOP] Hadoop 프로그래밍 세계의 키워드 문맥은 무엇입니까? (0)	2019.06.01

복붙노트

[HADOOP] HiveQL (Hadoop)을 사용하여 Hive에서 두 개의 테이블 조인 [duplicate]

HiveQL (Hadoop)을 사용하여 Hive에서 두 개의 테이블 조인 [duplicate]

해결법

2.아마도 Hive 변환 기능을 사용해야하고 두 테이블의 레코드 간 일치를 수행하는 사용자 지정 감속기가 있어야합니다. t1과 t2는 t1이 단순히 TestingTable1이고 t2가

'HADOOP' 카테고리의 다른 글

티스토리툴바