CODEWITHSUNDEEP
hivehiveqlhadoop-partitioning
user2935539
user2935539

Reputation: 83

how to group by data from hive with specific partition?

I have the following:

hive>show partitions TABLENAME                                                           
pt=2012.07.28.08                                                                         
pt=2012.07.28.09                                                                         
pt=2012.07.28.10                                                                         
pt=2012.07.28.11                                                                         
hive> select pt,count(*) from TABLENAME group by pt;                                     
OK

Why can't the group by get the data?

Upvotes: 0

Views: 6133

Answers (3)

Aadithya_h
Aadithya_h

Reputation: 561

You can always achive the same using 2 select statements . For ex

Create table table1(
session_id string,
page_id string
)
partitioned by (metrics_date string);

Consider we are have loaded table for 2 partitions

hive>show partitions table1
metrics_date=2012.07.28.08
metrics_date=2012.07.28.09

select * from table1 ;

1212121212    google.com     2012.07.28.08 

1212121212    google.com     2012.07.28.09`
Getting number of rows per partition 
select metrics_date,count(*) from (
select * from table1 ) temp
group by metrics_date;

Upvotes: 1

USB
USB

Reputation: 6139

To get whole results along with group by ,You can use the below query.

SELECT pt,count(*) OVER (PARTITION BY pt) FROM TABLENAME;

This can be achiened through partition by.

Upvotes: 0

Raja Reddy
Raja Reddy

Reputation: 782

Check if the hive.mapred.mode is set to "strict", if so it'll not allow all partitions to scan for the submitted query. You can set it to nonstrict as below:

hive>set hive.mapred.mode=nonstrict;

I'm not sure whether this caused NO results out of your query, but trying to address it. Do share the results.

Note: You can check the default value for this parameter in hive-default.xml

Upvotes: 2

Related Questions