Запросить две связанные таблицы (присоединяется)
Это первая таблица в Hive. Она содержит информацию о предмете, который мы покупаем.
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
, что означает, что мне нужно увидеть, соответствует ли USER_ID
из Table2
и BUYER_ID
из Table1
(так как они оба одинаковы), тогда PURCHASED_ITEM
в таблице 2, которая представляет собой массив PRODUCT_ID (то же, что и ITEM_ID), и TIMESTAMPS (то же, что и CREATED_TIME) должны быть такими же, как ITEM_ID
и CREATED_TIME
в Table1
для этого конкретного USER_ID (BUYER_ID), а также иногда это возможно, что они (означает PURCHASED_ITEM
и ITEM_ID
, CREATED_TIME
) не совпадают или некоторые PRODUCT_ID и TIMESTAMPS отсутствуют в Table2
после сравнения с Table1
.
Под этим я подразумеваю, что подсчет PRODUCT_ID
и TIMESTAMPS
в Table2
должен быть таким же, как счетчик ITEM_ID
и CREATED_TIME
в таблице1 для этого конкретного BUYER_ID (USER_ID), и контент должен быть таким же. Если они не совпадают, или запись отсутствует в Table2
, тогда мне нужно распечатать результат, в этом случае ITEM_ID
и CREATED_TIME
отсутствует в Table2
или PRODUCT_ID
и TIMESTAMPS
не совпадают после по сравнению с Table1
.
Итак, например, в таблице 1 в настоящее время для этого BUYER_ID 1015826235
у меня есть 5 ITEM_ID
и 5 CREATED_TIME
, поэтому в таблице 2 я должен иметь 5 PRODUCT_ID
и 5 TIMESTAMPS
точно так же, как Table1 для одного и того же USER_ID(BUYER_ID)
в одной строке. Если это не то же самое или запись отсутствует, мне нужно распечатать результат, который показывает, что это отсутствует или эти данные неверны.
Итак, чтобы сделать это более понятным -
PURCHASED_ITEM
представляет собой массив Struct в Table2
и содержит две вещи PRODUCT_ID
и TIMESTAMPS
.
Если соответствие USER_ID
и BUYER_ID
, то PRODUCT_ID
в Table2
должно совпадать с ITEM_ID
в Table1
и TIMESTAMPS
в Table2
должно быть сопоставлено с CREATED_TIME
в Table1
.
ОБНОВЛЕНО
HiveQL SQL Query Question: -
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.
Ответы
Ответ 1
Я предлагаю вам не использовать "строковый" тип данных для CREATED_TIME и timestamp, потому что он делает сравнения более жесткими. Вместо этого используйте Date или TimeStamp.
И для вашего вопроса: я думаю, что большая проблема здесь заключается в использовании строк в одиночку!
Я оракул, но в Hive должно быть что-то вроде этого:
To_date({string},{Format})
как вы использовали
UNIX_TIMESTAMP({string})
Другое дело: когда у вас есть структуры, вы выделяете такие поля: Table2.PURCHASED_ITEM [{address}]. product_id, а не Table2.product_id, который неизвестен.
и еще одно предложение:
Trunc({Date},{Format ex: 'SS' for sseconds})
когда ваш CREATED_TIME и ваш time_stamp не являются точно в одно и то же время (может быть разница 0,001 секунды из-за разницы времени вставки, если вы вставляете Now или Sysdate для каждого из них), вы лучше усекаете дату в секундах или миллисекундах или что бы вы ни думали, лучше.
Еще одна вещь: используйте NVL() или конвертируйте нулевые значения здесь, потому что, если у вас есть такие проблемы, также возможно иметь нулевые значения в вашей таблице, что вызывает проблемы в ваших запросах, функция NVL() преобразует значение null к чему-то, что вам нравится.
Надеюсь, что это поможет.
Ответ 2
Я не знаком с Hive, но я бы предложил создать временную таблицу с той же схемой, что и в таблице1, и заполнить ее данными Table2 (с преобразованием временной метки). В конечном итоге это может быть представление, если оно поддерживается.
Сравнение содержимого двух таблиц возможно с запросами, такими как:
SELECT * FROM Table1 WHERE (ITEM_ID,CREATED_TIME,BUYER_ID) NOT IN (SELECT * FROM Table2bis)
SELECT * FROM Table2bis WHERE (ITEM_ID,CREATED_TIME,BUYER_ID) NOT IN (SELECT * FROM Table1)