CODEWITHSUNDEEP
hadoophivedatabase-partitioning
TopCoder
TopCoder

Reputation: 4296

Joining two tables in hive

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?

Upvotes: 0

Views: 1266

Answers (2)

Mark Grover
Mark Grover

Reputation: 4080

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.

Upvotes: 0

Matt Tucker
Matt Tucker

Reputation: 21

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.

Upvotes: 2

Related Questions