Exclude values from same column depending on date, using Hive SQL

Question

I need to extract a set of IDs from a table using Hive. The table from which I am to extract the data is partitioned by date. What I need are distinct IDs that appear in the table eight days ago but are not in the table for dates that represent the last seven days. I have tried using a subquery:

SELECT DISTINCT id
FROM my_table
WHERE date = '2016-07-14'
  AND id NOT IN (
    SELECT DISTINCT id
    FROM my_table
    WHERE date BETWEEN '2016-07-15' AND '2016-07-21'
  );

However, I am getting an error message containing Unsupported language features in query (entire error message is too long to post here). Since I cannot use this approach in Hive SQL, what are my options here?

Have you tried to wrap first query into subquery? If this not help you can also try to replace NOT IN with LEFT JOIN + second_query.id is null — leftjoin
@leftjoin Not sure what you mean by "wrap first query into subquery". Care to write an answer? — Marko Popovic

leftjoin leftjoin · Accepted Answer · 2016-07-22T10:26:35

The same functionality can be done using LEFT JOIN:

SELECT a.ID 
FROM
        (
        SELECT DISTINCT ID
        FROM my_table
        WHERE date = '2016-07-14'
        )a 
          LEFT JOIN (
                     SELECT DISTINCT ID
                     FROM my_table
                     WHERE date BETWEEN '2016-07-15' AND '2016-07-21'
                   ) s on a.ID=s.ID
WHERE s.ID IS NULL;

Exclude values from same column depending on date, using Hive SQL

1 Answers