Where clause with dates in hive

Question

The where clause in the below hive query is not working

 select
 e.num as badge
from dbo.events as e
 where  TO_DATE(e.event_time_utc) > TO_DATE(select event_date from DL_EDGE_LRF_facilities.card_swipes_lastpulldate)

both event_time_utc and event_date fields are defined as strings and event_time_utc has timestamp values like '2017-09-18 20:10:19.000000' and event_date has only one date value like '2018-01-25' i am getting an error like "cannot recognize input near 'select' 'event_date' 'from' in function specification " when i run the query, Please help

jose_bacoy jose_bacoy · Accepted Answer · 2018-02-01T15:26:41

@user86683; hive does not recognize the syntax since it does not allow in-query in the inequality condition (>). You may try this query and let me know the result.

select e.num as badge
from dbo.events as e, DL_EDGE_LRF_facilities.card_swipes_lastpulldate c
where  TO_DATE(e.event_time_utc) > TO_DATE(c.event_date)

You will get a warning but you may ignore it since the table for event_date has only one record.

Warning: Map Join MAPJOIN[10][bigTable=e] in task 'Map 1' is a cross product
Query ID = xxx_20180201102128_aaabb2235-ee69275cbec1
Total jobs = 1
Launching Job 1 out of 1
Status: Running (Executing on YARN cluster with App id application_09fdf345)

Hope this helps. Thanks.

Where clause with dates in hive

1 Answers