I have a need for a hive query that I'm having difficulty figuring out.
I have a time series that looks like this:
time source word1 word2 ...etc
2012-02-01 23:43:16.9988243 0001 2B3B FAF0
2012-02-01 23:43:16.9993561 0002 2326 ABAA
2012-02-01 23:43:16.9998879 0002 2327 ABAA
I need a query such that, if a record in one source fulfills specific conditions, in addition to that record, it should also return one or more records in close proximity in time from the second source fulfilling a different set of conditions.
My attempt so far looks like this:
SELECT time
FROM messages C
JOIN messages D on
D.time
BETWEEN C.time - INTERVAL '0.001' SECOND
AND C.time + INTERVAL '0.001' SECOND
WHERE C.source = '0001'
AND D.Source = '0002'
AND C.word1 = '2B3B'
AND D.word2 = 'ABAA'
Which should return the first and second records in the sample data above (It shouldn't return the third, because the time is farther away than .001 seconds).
But the query doesn't work. The error message is
FAILED: SemanticException '0.001' encountered with 0 children