Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Plain View
Hive >> mail # user >> Oracle to Hive


+
Raj Hadoop 2013-07-10, 21:30
Copy link to this message
-
Re: Oracle to Hive
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 <[EMAIL PROTECTED]>
To: Hive <[EMAIL PROTECTED]>
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