The oracle sql statement into a hive ql statement , how to solveexsits

Programs need to be upgraded , the original into parallel computing , use of hadoop, replaced the original hive sql sql problems , seeking to solve the great God .
This is the original sql

 select terminal_id
   from r_tmnl_run r
  where prio_ps_mode like '2%'
    and not exists (select ''
           from r_sp sp, e_mp_day_read_statis y
          where sp.sp_id = y.id
            and r.terminal_id = sp.terminal_id
            and y.data_quality_flag = '1'
            and sp.status_code = '02');
 

------ Solution ------------------------------------- -------


HIVE no IN, EXISTS this sentence , NOT out of the question
but you can work around that, give you an example
NOT EXISTS NOT IN is equivalent to
Suppose table A table B COLB COLA IN
HIVE SQL 's IN wording ( equivalent EXISTS)

 SELECT 表A.COLA FROM 表A left outer join 表B on 表A.COLA = 表B.COLB 
 WHERE 表B.COLB is not null
 

HIVE SQL 's NOT IN wording ( equivalent to the NOT EXISTS)

 SELECT 表A.COLA FROM 表A left outer join 表B on 表A.COLA = 表B.COLB
 WHERE 表B.COLB is null
 

As HIVE only do equivalents JOIN, so take advantage of a good table B.COLB judgment
work what can be done
------ Solution ------------------------------ --------------
hive support in the
hive does not exist wording as exist not.
------ For reference only -------------------------------------- -

Oh indeed , tested 0.10.0 supported