CODEWITHSUNDEEP
hadoophive
Tim
Tim

Reputation: 659

how to select data from hive with specific partition?

everyone. here are the interactions with the hive:

hive> show partitions TABLENAME
pt=2012.07.28.08/is_complete=1
pt=2012.07.28.09/is_complete=1
pt=2012.07.28.10/is_complete=1
pt=2012.07.28.11/is_complete=1
hive> select * from TABLENAME where pt='2012.07.28.10/is_complete=1' limit 1;
OK
Time taken: 2.807 seconds
hive> select * from TABLENAME where pt='2012.07.28.10' limit 1;
OK
61806fd3-5535-42a1-9ca5-91676d0e783f    1.160.243.215.1343401203879.1   2012-07-28 23:36:37
Time taken: 3.8 seconds
hive>

My question is that why the first select can't get the data?

Upvotes: 15

Views: 75958

Answers (2)

Arun Goudar
Arun Goudar

Reputation: 381

If you are using Ambari, you can query as below

select * from TABLE NAME WHERE PARTITION NAME and AND ANOTHER PARTITION NAME LIMIT 10

Here partitions are associated with table so we query directly considering them as table(Simple analogy). Here "/" symbol tells its another folder directory. Each partitioned table data will store in associated directory. For ex if we have partitions like below 

year=2017/month=11/day=1/part=1

then we can use 

select * from TABLE NAME where year=2017 AND month=11 AND day=1 AND part=1 LIMIT 10;

Upvotes: 1

Paul M
Paul M

Reputation: 2046

"is_complete" is a column just like "pt" so the correct query is:

select * from TABLENAME where pt='2012.07.28.10' and is_complete='1' limit 1;

Upvotes: 25

Related Questions