Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 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
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB