Query two related tables (Joins)

I'm not familiar with Hive, but I'd suggest that you create a temporary table with the same schema as Table1, and fill it with Table2 data (with timestamp conversion). This could eventually be a view, if supported.

Comparing the content of two tables is then possible with queries such as:

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)

I suggest you not to use "string" data type for your CREATED_TIME and timestamp because it makes comparisons Harder. Instead of that use Date or TimeStamp.

And for your question: I think the big problem here is using strings alone!

I'm oracle user but there should be something like this in Hive:

To_date({string},{Format})

as you used

UNIX_TIMESTAMP({string})

Another thing: when you have strucs, you shouls address fields like this: Table2.PURCHASED_ITEM[{address}].product_id and not Table2.product_id which is unknown.

and one more suggestion:

Trunc({Date},{Format ex: 'SS' for sseconds})

when your CREATED_TIME and your time_stamp are not exactly in the same time ticks(may be 0.001 seconds difference because of difference insert time if you insert Now or Sysdate for each of them) you better truncate the date to seconds or Milli-seconds or whatever you think is better.

One more thing: Use NVL() or Convert null values here as well, becuase if you have such problems it is also possible to have null values in your table which causes problems in your queries, NVL() function will convert null to something you like.

Hope this helps.