You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user@hive.apache.org by Raj Hadoop <ha...@yahoo.com> on 2013/07/10 23:30:04 UTC

Oracle to Hive

 
All,
 
Can anyone give me tips on how to convert the following Oracle SQL to a Hive query.
 
 
SELECT a.c100, a.c300, b.c400
  FROM t1 a JOIN t2 b ON a.c200 = b.c200
 WHERE a.c100 IN (SELECT DISTINCT a.c100
                             FROM t1 a JOIN t2 b ON a.c200 = b.c200
                            WHERE b.c400 >= SYSDATE - 1)
   AND b.c400 >= SYSDATE - 1
   AND a.c300 = 0
 
 
The SYSDATE can be replaced by date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd') , 1) in Hive.
 
But I wanted to know the rest of the query. Any pointers or tips so that I can start on my own.
 
Thanks in advance.
 
Regards,
Raj

Re: Oracle to Hive

Posted by Michael Malak <mi...@yahoo.com>.

Untested:

SELECT a.c100, a.c300, b.c400
  FROM t1 a
  JOIN t2 b
  ON a.c200 = b.c200
  JOIN (SELECT DISTINCT a.c100
          FROM t1 a2
          JOIN t2 b2
          ON a2.c200 = b2.c200
        WHERE b2.c400 >= SYSDATE - 1) a3
  ON a.c100 = a3.c100
  WHERE b.c400 >= SYSDATE - 1
   AND a.c300 = 0



________________________________
 From: Raj Hadoop <ha...@yahoo.com>
To: Hive <us...@hive.apache.org> 
Sent: Wednesday, July 10, 2013 3:30 PM
Subject: Oracle to Hive
 


 
All,
 
Can anyone give me tips on how to convert the following Oracle SQL to a Hive query.
 
 
SELECT a.c100, a.c300, b.c400
  FROM t1 a JOIN t2 b ON a.c200 = b.c200
 WHERE a.c100 IN (SELECT DISTINCT a.c100
                             FROM t1 a JOIN t2 b ON a.c200 = b.c200
                            WHERE b.c400 >= SYSDATE - 1)
   AND b.c400 >= SYSDATE - 1
   AND a.c300 = 0
 
 
The SYSDATE can be replaced by  date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd') , 1) in Hive.
 
But I wanted to know the rest of the query. Any pointers or tips so that I can start on my own.
 
Thanks in advance.
 
Regards,
Raj