hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Himanish Kushary <himan...@gmail.com>
Subject Help with Join involving Non-Equality condition
Date Thu, 16 Aug 2012 18:07:24 GMT
Hi,

We have two tables in the following structure :

Table1 :

|  id   |        packcreatetime         |   packid |
----------------------------------------------------------------------
| 505  |	2012-07-16 11:51:12	| 111024   |
| 505  |	2012-07-18 11:52:13	| 111025   |
| 505  |	2012-07-19 11:53:14   | 111026   |
| 504  |      2012-07-17  23:50:13  |  101020  |
------------------------------------------------------------------------

Table-2

| id   |   requesttime
----------------------------------------
| 505 | 2012-07-18 12:09:47
| 505 | 2012-07-19 12:09:59
| 505 | 2012-07-19 12:09:56
| 505 | 2012-07-17 12:06:40
| 505 | 2012-07-17 12:06:40
| 505 | 2012-07-17 12:09:15
| 504 | 2012-07-18 00:03:18
| 504 | 2012-07-18 00:15:41

We want to find out the packid from Table1 where the  is corresponding
in Table2 and the requesttime(in Table2) is between the
packcreatetime of two relevant records(in Table1)

So for the above example the final output will be:

| id   |   requesttime            |   packid
-------------------------------------------------------
| 505 | 2012-07-18 12:09:47 |  111025
| 505 | 2012-07-19 12:09:59 |  111026
| 505 | 2012-07-19 12:09:56 |  111026
| 505 | 2012-07-17 12:06:40 |  111024
| 505 | 2012-07-17 12:06:40 |  111024
| 505 | 2012-07-17 12:09:15 |  111024
| 504 | 2012-07-18 00:03:18 |  101020
| 504 | 2012-07-18 00:15:41 |  101020


As we cannot use >= , <= in Hive joins the between logic cannot be
implemented in joins, is there any way to accomplish this or do we
need to write custom M/R code for this.Looking forward for any
suggestions to accomplish this.

--
Thanks & Regards
Himanish

Mime
View raw message