Joining two tables in hive

Question

I have table where I have partitioned date by year and month and date

'ABC' Partition by

(year='2011', month='08', day='01')

I want to run a query something like

select * from ABC where dt>='2011-03-01' and dt<='2012-02-01';

How can I run this query with above partitioning scheme in terms of year, month and day?

Answer 1

You might consider creating an external table that is partitioned by 'yyyy-mm-dd', and uses the same locations as your existing table. You won't have to copy any data, and you'll have the flexibility of both partitioning formats.

Answer 2

select * from ABC where year='2011' and month >= '03'
UNION
select * from ABC where year='2012' and month = '01'
UNION
select * from ABC where year='2012' and month='02' and day='01';

The above query should solve the purpose but it's really neither flexible nor well-readable. Like Matt suggested, a better partitioning format would be of a single string variable in yyyy-MM-dd format as the partitioning column. However, you might have to make a copy of the data if you change the partitioning scheme for year, month, day to dt. In my opinion though, it's totally worth it.